מערכים באקסל – כמו שלא הכרתם…

רובנו שמענו על מערכים באקסל. בעבר הם היו הדבר המפחיד הזה שבשבילו צריך ללחוץ על שילוב המקשים Ctrl+Shift+Enter,
שמוסיף סוגריים מסולסלים מסביב לנוסחה, ושלרוב האנשים נראה כל כך מורכב, עד שהם אפילו לא טרחו ללמוד אותו.
כשהגיעה גרסת 365, היא פישטה את נושא המערכים בצורה משמעותית, וכיום ניתן לכתוב נוסחאות מערך בצורה טבעית, בלי לדעת בכלל שהן נוסחאות מערך, ומה היה נדרש בעבר כדי לכתוב אותן.
עם זאת, הכרת נושא כתיבת המערכים באקסל יכול לעזור לנו גם היום, ואת זה נלמד במאמר.

מהו מערך?

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

תחביר

התחביר הבסיסי של מערך דורש מאתנו להקיף את הערכים בסוגריים מסולסלים – {}, כאשר בין עמודה לעמודה, מפריד פסיק, כפי שניתן לראות בדוגמה הבאה:

={1,2}

בדוגמה הזו ביקשנו מאקסל ליצור מערך שמכיל שתי עמודות, כאשר הערך הראשון הוא 10 והערך השני הוא 20.
וכך זה נראה:

הסימן שמפריד בין שורות הוא ; (נקודה פסיק), כפי שתוכלו לראות בדוגמה הבאה:

כלומר- ביקשנו ליצור מערך בן שתי עמודות ושתי שורות. בשורה הראשונה הערכים 10 ו-20, מופרדים לעמודות באמצעות הפסיק,
לאחר מכן הוספנו נקודה-פסיק לציין מעבר שורה, וגם לשם הזנו שני ערכים: 30 ו-40.
ובואו נראה דוגמה נוספת:
הנוסחה:

={"Name","Grade";"Dan",70;"Ron",100;"John",90}

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

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

Sort על פי שני ערכים

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

התחביר הבסיסי של הפונקציה SORT הוא

=SORT(Array, [Sort Index], [Sort Order])

כלומר- מהו הטווח, מהו מספר העמודה שעל פיה נרצה למיין, ומהו סדר המיון (עולה או יורד)
העניין הוא שאנחנו יכולים לציין רק עמודה אחת עבור המיון, ואילו אנחנו רוצים למיין גם על פי אגף וגם על פי משכורת.
אז קודם כל, כדאי להכיר את הפונקציה SORTBY שמאפשרת למיין על פי מספר פרמטרים, ולא רק אחד.
אבל אפשר להשתמש גם בפונקציה SORT כדי למיין על פי שתי העמודות, אם נשתמש במערך, באופן הבא:

=SORT(A2:D21,{2,4},{1,-1})

כלומר- ביקשנו למיין את הטווח שמתפרש בין A2:D21, ולתוך מערך הכנסנו את מספר האינדקס של שתי העמודות הרצויות:
עמודה מס' 2 שמציינת את האגף, ועמודה מספר 4, שמציינת את המשכורת.
באותו האופן קבענו את סדר המיון – 1 עבור עמודת המיון הראשונה, שהיא האגף, ומינוס 1 עבור עמודת המיון השנייה, שהיא המשכורת.
כדי להוסיף גם את הכותרות, אשתמש בפונקציה VSTACK, שמצרפת את שני הטווחים זה תחת זה:

=VSTACK(A1:D1,SORT(A2:D21,{2,4},{1,-1}))

וזו התוצאה שהתקבלה:

סכימת N הערכים הגבוהים ביותר

כדי לדעת מהו הערך ה-N בגודלו במערך, נשתמש בפונקציה LARGE, וזה התחביר שלה:

=LARGE(Array,K)

כלומר- עלינו לסמן את הטווח הרצוי, ולהגיד מהו המיקום הרצוי. כדי לקבל את הערך הגבוה ביותר, לדוגמה, נכתוב 1, וכדי לקבל את הערך השני הגבוה ביותר נכתוב 2 וכן הלאה.
ובמקרה שלנו, כדי לקבל את הערך הגבוה ביותר, נכתוב את הפונקציה הבאה:

=LARGE(D2:D21,1)

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

=SUM(LARGE(D2:D21,1)+LARGE(D2:D21,2)+LARGE(D2:D21,3))

אבל אפשר לפשט את הנוסחה באמצעות מערך, באופן הבא:

=SUM(LARGE(D2:D21,{1,2,3}))

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

=SUM(LARGE(D2:D21,SEQUENCE(3)))

כלומר, השתמשתי בפונקציה SEQUENCE, שהיא פונקציה מתוך פונקציות המערך החדשות של אקסל 365, שיוצרים מערכים בלי להשתמש בסוגריים מסולסלים, וזאת כדי לייצר מערך של 3 מספרים עוקבים.
את המערך שהתקבל הכנסתי לתוך הפונקציה LARGE, ואת התוצאות הקפתי בפונקציה SUM. קיבלתי את אותה התוצאה בדיוק.