נוסחה אחת, תוצאות רבות: רשימות שדולפות לתאים שמתחתיהם.
Excel מודרני ומהירות · 5 תרגילים קצרים בגיליון אלקטרוני חי: כל נוסחה מוקלדת ידנית ולענות על שאלות שדורשות לנחש תוצאות או לאתר באגים. כל תשובה נבדקת מיד.
עם מערכים דינמיים, נוסחה אחת יכולה להחזיר ערכים רבים, והם ממלאים את התאים מתחתיה או לצדה באופן אוטומטי. זה נקרא דליפה. כך עבודות ישנות רבות של כמה שלבים, כמו בניית רשימה ממוינת של ערכים ייחודיים, הופכות לנוסחה אחת.
נוסחה אחת, תאים רבים
נניח רשימת מחסה לבעלי חיים: A2:A7 מכיל את סוג החיה (Dog, Cat, Dog, Rabbit, Cat, Dog) ו-B2:B7 מכיל את הגיל (3, 5, 2, 1, 7, 4). מקלידים =UNIQUE(A2:A7) ב-D2, ו-Excel ממלא את D2:D4 ב-Dog, Cat ו-Rabbit. נכתבה נוסחה אחת בלבד. המסגרת הצבעונית סביב D2:D4 היא טווח הדליפה.
אפשר להתייחס לכל הדליפה בעזרת הסימן #: =COUNTA(D2#) סופר כמה פריטים יש עכשיו, גם אם הנתונים ישתנו בהמשך.
גילאים מהגדול לקטן (7, 5, 4, 3, 2, 1). בעלי חיים מעל גיל 3 (Cat, Cat, Dog). סוגי חיות ייחודיים בסדר אלפביתי (Cat, Dog, Rabbit).
SORT לוקחת את הטווח, את העמודה למיון, ו-1 לסדר עולה או -1 לסדר יורד. כדי למיין את הטבלה כולה לפי גיל, נותנים לה את שתי העמודות: =SORT(A2:B7,2,-1). UNIQUE מחזירה כל ערך פעם אחת. FILTER משאירה את השורות שבהן המבחן הוא TRUE.
לשני תנאים, מכפילים את המבחנים, וזה אומר AND: =FILTER(B2:B7,(A2:A7="Dog")*(B2:B7>2)) מחזיר את הגילאים 3 ו-4. אם שום דבר לא מתאים, FILTER נותנת #CALC!. מוסיפים ארגומנט שלישי כדי לבחור הודעה מותאמת: =FILTER(A2:A7,B2:B7>10,"אין").
שרשור הפונקציות
מכיוון שכל פונקציה מחזירה רשימה, אפשר להזין את אחת לתוך הבאה. נחזור לרשימת המחסה (A2:A7 סוג החיה, B2:B7 הגיל). כדי לקבל רק בעלי חיים שגילם מעל 2, מהגדול לקטן, עם שתי העמודות מוצגות, מקננים את הסינון בתוך מיון:
=SORT(FILTER(A2:B7,B2:B7>2),2,-1)
הסינון משאיר ארבע שורות (Dog 3, Cat 5, Cat 7, Dog 4). המיון מסדר אותן לפי העמודה השנייה, הגדול ביותר ראשון: Cat 7, Cat 5, Dog 4, Dog 3.
קוראים נוסחאות מקוננות מבפנים החוצה: קודם הסינון, אחר כך המיון. אם שרשרת ארוכה קשה לעקוב אחריה, כדאי לבנות אותה בשלבים בתאים נפרדים ולשלב אותם לנוסחה אחת אחרי שכל שלב נכון.
השגיאה #SPILL!
#SPILL! פירושו שהתאים שאליהם התוצאה רוצה להגיע אינם ריקים. מנקים את התא החוסם, או מזיזים את הנוסחה. אותה שגיאה מופיעה אם מכניסים נוסחה שדולפת לתוך טבלת Excel, כי טבלאות לא יכולות להכיל דליפות. כדאי לשמור נוסחאות כאלה ליד הטבלה ולא בתוכה.
קבצים ישנים וסימן ה-@
בחוברת עבודה שנוצרה בגרסה ישנה של Excel אפשר לראות סימן @ בתוך נוסחה. הוא מסמן את ההתנהגות הישנה של ערך בודד, שנקראת חיתוך מרומז (implicit intersection). Excel מוסיף אותו כדי שהקובץ ימשיך לתת בדיוק את התשובות שתמיד נתן. בנוסחה חדשה בדרך כלל לא צריך אותו: כותבים את הנוסחה בלי @ ונותנים לה לדלוף.
טיפים
מערכים דינמיים קיימים ב-Excel 2021, ב-Excel 2024 וב-Microsoft 365. גרסאות ישנות מציגות #NAME?.
אסור להקליד לתוך טווח דליפה. אפשר לערוך את הנוסחה רק בתא הפינתי הראשון של הטווח. כדי לשמור ערכים קבועים, מעתיקים את הדליפה ומדביקים אותה כערכים.
אפשר לשלב אותן: =COUNTA(UNIQUE(A2:A7)) מחזיר 3, מספר סוגי החיות הייחודיים.