נוסחאות מערכים דינאמיים ואופן הפעולה של מערכים זולגים

נוסחאות Excel המחזירות קבוצת ערכים המכונה גם מערך, מחזירות ערכים אלה לתאים שכנים. אופן פעולה זה נקרא זליגה.

נוסחאות שיכולות להחזיר מערכים בגודל משתנה נקראות נוסחאות מערך דינאמי. ניתן לכנות נוסחאות המחזירות מערכים שזולגים בהצלחה בשם 'נוסחאות מערך זולג'.

להלן כמה הערות שיעזרו לך להבין נוסחאות מסוג זה ולהשתמש בהן.

מהי המשמעות של זליגה?

הערה: נוסחאות מערך ישנות יותר, שנקראות נוסחאות מערך מדור קודם, מחזירות תמיד תוצאה בגודל קבוע – הן זולגות תמיד לאותו מספר תאים. אופן פעולת הזליגה המתואר בנושא זה אינו חל על נוסחאות מערך מדור קודם.

זליגה פירושה שנוסחה מחזירה ערכים מרובים בתור תוצאה, וערכים אלה מוצבים בתאים שכנים. לדוגמה, הנוסחה =SORT(D2:D11,1,-1), שממיינת מערך בסדר יורד, תחזיר מערך תואם בגובה של 10 שורות. אך יש להזין את הנוסחה רק בתא הימני העליון, או F2 במקרה זה, והתוצאה שלה תזלוג באופן אוטומטי מטה, עד לתא F11.

מיין את הערכים בתאים D2:D11 באמצעות =SORT(D2:D11,1,-1)

נקודות מרכזיות

בעת הקשה על Enter כדי לאשר את הנוסחה, Excel יגדיל עבורך באופן דינאמי את טווח הפלט וימקם את התוצאות בכל התאים שבטווח זה.
אם אתה כותב נוסחת מערך דינאמי כדי לבצע פעולה על רשימת נתונים, מומלץ למקם אותה בטבלת Excel ולאחר מכן להשתמש בהפניות מובנות כדי להפנות לנתונים. זאת משום שהפניות מובנות מותאמות באופן אוטומטי בעת הוספה או הסרה של שורות מהטבלה.
נוסחאות מערך זולג אינן נתמכות בטבלאות Excel עצמן, לכן עליך למקם אותן ברשת מחוץ לטבלה. טבלאות הן מתאימות מאוד לאחסון שורות ועמודות של נתונים בלתי תלויים.
לאחר שתזין נוסחת מערך זולג, כאשר תבחר תא כלשהו באזור הזליגה, Excel יציג גבול מסומן מסביב לטווח. הגבול ייעלם בעת בחירת תא מחוץ לאזור הזליגה.
רק התא הראשון באזור הזליגה ניתן לעריכה. אם תבחר תא אחר באזור הזליגה, הנוסחה תהיה גלויה בשורת הנוסחאות, אך הטקסט לא יהיה ניתן לעריכה. אם עליך לעדכן את הנוסחה, עליך לבחור את התא הימני העליון בטווח המערך, לשנות אותו לפי הצורך, ולאחר מכן Excel יעדכן באופן אוטומטי את שאר אזור הזליגה כאשר תקיש Enter.
חפיפת נוסחאות - לא ניתן להזין נוסחאות מערך אם יש משהו שחוסם את טווח הפלט. אם תופעה זו מתרחשת, Excel יחזיר שגיאת ‎#SPILL!‎ המציינת שקיימת חסימה. אם תסיר את החסימה, הנוסחה תזלוג כצפוי. בדוגמה שלהלן, טווח הפלט של הנוסחה חופף לטווח אחר המכיל נתונים, והוא מוצג כאשר התאים החופפים מסומנים בגבול מנוקד עם ערכים המציינים שלא ניתן לבצע זליגה. הסר את הנתונים החוסמים או העתק אותם למקום אחר, והנוסחה תזלוג כצפוי.
נוסחאות מערך מדור קודם שהוזנו באמצעות CTRL+SHIFT+ENTER (CSE) עדיין נתמכות משיקולי תאימות לאחור, אך אין להשתמש בהן עוד. אם תרצה, תוכל להמיר נוסחאות מערך מדור קודם לנוסחאות מערך דינאמי על-ידי איתור התא הראשון בטווח המערך, העתקת הטקסט של הנוסחה, מחיקת כל הטווח של המערך מדור קודם ולאחר מכן הזנה מחדש של הנוסחה בתא הימני העליון. לפני שתשדרג נוסחאות מערך מדור קודם לנוסחאות מערך דינאמי, עליך להיות מודע להבדלי חישוב מסוימים בין שתי הנוסחאות.
Excel כולל תמיכה מוגבלת עבור מערכים דינאמיים בין חוברות עבודה, ותרחיש זה נתמך רק כאשר שתי חוברות העבודה פתוחות. אם תסגור את חוברת העבודה המהווה מקור, כל נוסחאות המערך הדינאמי המקושרות יחזירו שגיאת ‎#REF!‎ כאשר יעברו רענון.