קווים מנחים ודוגמאות לנוסחאות מערך

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

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

באפשרותך להשתמש בנוסחאות מערך כדי לבצע משימות מורכבות, כגון:

  • צור במהירות ערכות נתונים לדוגמה.

  • ספירת מספר התווים הכלולים בטווח תאים.

  • סכום מספרים המקיימים תנאים מסוימים בלבד, כגון הערכים הנמוכים ביותר בטווח, או מספרים הקיימים בין גבול עליון וגבול תחתון.

  • סיכום כל ערך N בטווח ערכים.

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

הורד את הדוגמאות שלנו

הורד חוברת עבודה לדוגמה עם כל דוגמאות נוסחת המערך במאמר זה.

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

  • נוסחת מערך מרובת-תאים

    פונקציית מערך מרובה תאים בתא H10 =F10:F19*G10:G19 לחישוב מספר המכוניות שנמכרות לפי מחיר יחידה

  • כאן אנו מחשבים את סך המכירות של קופות וsedans עבור כל איש מכירות על-ידי הזנת =F10:F19*G10:G19 בתא H10.

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

  • נוסחת מערך של תא יחיד

    נוסחת מערך של תא יחיד לחישוב סכום כולל עם =SUM(F10:F19*G10:G19)

    בתא H20 של חוברת העבודה לדוגמה, הקלד או העתק והדבק =SUM(F10:F19*G10:G19)ולאחר מכן הקש Enter.

    במקרה זה, Excel את הערכים במערך (טווח התאים F10 עד G19) ולאחר מכן משתמש בפונקציה SUM כדי להוסיף את הסכומים יחד. התוצאה היא סך כולל של ‎$1,590,000‎ במכירות.

    דוגמה זו ממחישה את העוצמה האפשרית של נוסחה מסוג זה. לדוגמה, נניח שיש לך 1,000 שורות של נתונים. באפשרותך לסכם חלק מהנתונים, או את כולם, על-ידי יצירת נוסחת מערך בתא יחיד במקום לגרור את הנוסחה כלפי מטה 1,000 שורות. כמו כן, שים לב שנוסחת התא היחיד בתא H20 אינה תלויה לחלוטין בנוסחה מרובת התאים (הנוסחה בתאים H10 עד H19). זהו יתרון נוסף של השימוש בנוסחאות מערך — גמישות. באפשרותך לשנות את הנוסחאות האחרות ב- עמודה H מבלי להשפיע על הנוסחה ב- H20. זה יכול להיות גם תרגול טוב שיש סכומים בלתי תלויים כמו זה, כפי שהוא עוזר לאמת את מידת הדיוק של התוצאות שלך.

  • נוסחאות מערך דינאמיות מציעות גם יתרונות אלה:

    • עקביות    אם תלחץ על אחד מהתאים מ- H10 כלפי מטה, תראה את אותה נוסחה. עקביות זו יכולה להבטיח דיוק רב יותר.

    • בטיחות    לא ניתן להחליף רכיב בנוסחת מערך מרובה תאים. לדוגמה, לחץ על תא H11 והקש Delete. Excel לא תשנה את פלט המערך. כדי לשנות אותו, עליך לבחור את התא הימני העליון במערך או בתא H10.

    • קבצים קטנים יותר    באפשרותך להשתמש לעתים קרובות בנוסחת מערך יחידה במקום בכמה נוסחאות ביניים. לדוגמה, הדוגמה של מכירות רכב משתמשת בנוסחת מערך אחת לחישוב התוצאות עמודה E. אם השתמשת בנוסחאות רגילות כגון =F10*G10, F11*G11, F12*G12 וכן הלאה, היית משתמש ב- 11 נוסחאות שונות לחישוב אותן תוצאות. זה לא עניין גדול, אבל מה אם היו לך אלפי שורות לסכום? לאחר מכן, זה יכול לחולל שינוי גדול.

    • יעילות    פונקציות מערך יכולות להיות דרך יעילה לבניית נוסחאות מורכבות. נוסחת המערך =SUM(F10:F19*G10:G19) זהה לזה: =SUM(F10*G10,F11*G11,F12*G12,F12,F1 3*G13,F14*G14,F15*G15,F16*G16,F17*G17,F18*G18,F19*G19).

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

    • #SPILL! שגיאה    מערכים דינאמיים הציגו את #SPILL!, המציין שטווח ההשפכה המיועד חסום מסיבה כלשהי. כאשר תפתור את החסימה, הנוסחה תשפוך באופן אוטומטי.

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

={1,2,3,4,5} או ={"January","February","March"}

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

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

  • יצירת קבוע אופקי

    השתמש בחוברת העבודה מהדוגמה הקודמת, או צור חוברת עבודה חדשה. בחר תא ריק כלשהו והזן =SEQUENCE(1,5). הפונקציה SEQUENCE בונה שורה אחת ב- 5 עמודה מערך זהה לערך ={1,2,3,4,5}. התוצאה הבאה מוצגת:

    יצירת קבוע מערך אופקי עם =SEQUENCE(1,5) או ={1,2,3,4,5}

  • יצירת קבוע אנכי

    בחר תא ריק כלשהו עם חדר מתחתיו והזן =SEQUENCE(5), או ={1;2;3;4;5}. התוצאה הבאה מוצגת:

    יצירת קבוע מערך אנכי באמצעות =SEQUENCE(5), או ={1;2;3;4;5}

  • יצירת קבוע דו-ממדי

    בחר תא ריק כלשהו עם חדר ימין ומתחתיו והזן =SEQUENCE(3,4). ניתן לראות את התוצאה הבאה:

    יצירת קבוע מערך של 3 עמודה ב- 4 שורות עם =SEQUENCE(3,4)

    באפשרותך גם להזין: או ={1,2,3,4;5,6,7,8;9,10,11,12}, אך כדאי לשים לב לנקודה שבה אתה שם נקודה-פסיק לעומת פסיקים.

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

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

בתא D9, הזנתי =SEQUENCE(1,5,3,1), אך ניתן גם להזין 3, 4, 5, 6 ו- 7 בתאים A9:H9. אין שום דבר מיוחד בבחירה של מספר מסוים זה, בחרנו משהו שונה מ- 1-5 עבור הה השונות.

בתא E11, הזן =SUM(D9:H9*SEQUENCE(1,5)), או =SUM(D9:H9*{1,2,3,4,5}). הנוסחאות מחזירות 85.

השתמש בקבועי מערך בנוסחאות. בדוגמה זו, השתמשנו ב- =SUM(D9:H(*SEQUENCE(1,5))

הפונקציה SEQUENCE בונה את המקבילה של קבוע המערך {1,2,3,4,5}. מאחר Excel מבצע פעולות בביטויים המוקפים בסוגריים תחילה, שני הרכיבים הבאים המשתתפים ב- הם ערכי התאים ב- D9:H9 ואופרטור הכפל (*). בשלב זה, הנוסחה מכפילה את הערכים במערך המאוחסן בערכים המתאימים בקבוע. מדובר במקבילה של:

=SUM(D9*1,E9*2,F9*3,G9*4,H9*5), או =SUM(3*1,4*2,5*3,6*4,7*5)

לבסוף, הפונקציה SUM מוסיפה את הערכים ומחזירה 85.

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

=SUM(SEQUENCE(1,5,3,1)*SEQUENCE(1,5)), או =SUM({3,4,5,6,7}*{1,2,3,4,5})

רכיבים לשימוש בקבועי מערך

  • קבועי מערך יכולים להכיל מספרים, טקסט, ערכים לוגיים (כגון TRUE ו- FALSE), ערכי שגיאה כגון #N/A. באפשרותך להשתמש במספרים בתבניות מספר שלם, עשרוני ומדעי. אם תכלול טקסט, עליך להקיף אותו במרכאות ("text").

  • קבועי מערך אינם יכולים להכיל נוסחאות, פונקציות או מערכים נוספים. במילים אחרות, הם יכולים להכיל רק טקסט או מספרים המופרדים באמצעות פסיקים או תווי נקודה-פסיק. Excel מציג הודעת אזהרה כאשר מוזנת נוסחה כגון {1,2,A1:D4} או ‎{1,2,SUM(Q2:Z8)}‎. כמו כן, ערכים מספריים אינם יכולים להכיל סימני אחוז, סימני דולר, פסיקים או סוגריים.

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

עבור אל נוסחאות > שמות מוגדרים > הגדר שם. בתיבה שם, הקלד Quarter1. בתיבה מפנה אל, הזן את הקבוע הבא (זכור להקליד את הסוגריים המסולסלים באופן ידני):

‎={"ינואר","פברואר","מרץ"}‎

תיבת הדו-שיח אמורה להיראות כך כעת:

הוספת קבוע מערך בעל שם מנוסחאות > שמות מוגדרים > מנהל השמות > חדש

לחץ עלאישור ולאחר מכן בחר שורה כלשהי עם שלושה תאים ריקים והזן =Quarter1.

התוצאה הבאה מוצגת:

השתמש בקבוע מערך בעל שם בנוסחה, כגון =Quarter1, כאשר Quarter1 הוגדר כ- ={"January","February","March"}

אם ברצונך שהתוצאות יתפזרו אנכית במקום אופקית, באפשרותך להשתמש ב- =TRANSPOSE(Quarter1).

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

=TEXT(DATE(YEAR(TODAY()),SEQUENCE(1,12),1),"mmm")

שימוש בשילוב של הפונקציות TEXT , DATE , YEAR , TODAY ו- SEQUENCE כדי לבנות רשימה דינאמית של 12 חודשים

פעולה זו משתמשת בפונקציה DATE כדי ליצור תאריך המבוסס על השנה הנוכחית, הפונקציה SEQUENCE יוצרת קבוע מערך מ- 1 עד 12 עבור ינואר עד דצמבר, לאחר מכן הפונקציה TEXT ממירה את תבנית התצוגה ל- "mmm" (ינואר, פברואר, מרץ וכדומה). אם רצית להציג את שם החודש המלא, כגון ינואר, השתמש ב- "mmmm".

בעת שימוש בקבוע בעל שם כנוסחת מערך, זכור להזין את סימן השוויון, כמו ב- =Quarter1, ולא רק ברבעון1. אם לא תעשה כן, Excel יפרש את המערך כמחרוזת של טקסט והנוסחה שלך לא תפעל כצפוי. לבסוף, זכור כי באפשרותך להשתמש בשילובים של פונקציות, טקסט ומספרים. הכל תלוי ביצירה הרצויה.

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

  • ריבוי כל פריט במערך

    הזן =SEQUENCE(1,12)*2, או ={1,2,3,4;5,6,7,8;9,10,11,12}*2

    באפשרותך גם לחלק ב- (/), להוסיף עם (+) ולהיפחת ב- (-).

  • ריבוע הפריטים במערך

    הזן =SEQUENCE(1,12)^2, או ={1,2,3,4;5,6,7,8;9,10,11,12}^2

  • חיפוש השורש הריבועי של פריטים בריבוע במערך

    Enter =SQRT(SEQUENCE(1,12)^2), או =SQRT({1,2,3,4;5,6,7,8;9,10,11,12}^2)

  • ביצוע חילוף של שורה חד-ממדית

    הזן =TRANSPOSE(SEQUENCE(1,5)), או =TRANSPOSE({1,2,3,4,5})

    למרות שהזנת קבוע מערך אופקי, הפונקציה TRANSPOSE ממירה את קבוע המערך לעמודה.

  • ביצוע חילוף של עמודה חד-ממדית

    הזן =TRANSPOSE(SEQUENCE(5,1)), או =TRANSPOSE({1;2;3;4;5})

    למרות שהזנת קבוע מערך אנכי, הפונקציה TRANSPOSE ממירה את הקבוע לשורה.

  • ביצוע חילוף של קבוע דו-ממדי

    הזן =TRANSPOSE(SEQUENCE(3,4)), או =TRANSPOSE({1,2,3,4;5,6,7,8;9,10,11,12})

    הפונקציה ‏TRANSPOSE ממירה כל שורה לסידרה של עמודות.

סעיף זה מספק דוגמאות לנוסחאות מערך בסיסיות.

  • יצירת מערך מתוך ערכים קיימים

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

    הזן =SEQUENCE(3,6,10,10), או ={10,20,30,40,50,60;70,80,90,100,110,120;130,150,160,170,180,180}

    הקפד להקליד { (סוגר מסולסל פותח) לפני שתקליד 10, ו- } (סוגר סוגריים מסולסלים סוגריים) לאחר הקלדת 180, מכיוון שאתה יוצר מערך של מספרים.

    לאחר מכן, הזן =D9#, או =D9:I11 בתא ריק. מערך תאים של 3 x 6 מופיע עם אותם ערכים שאתה רואה ב- D9:D11. סימן ה- # נקרא אופרטור הטווח הנשפך, Excel הדרך של ההפניה לטווח המערך כולו במקום להקליד אותו.

    שימוש באופרטור הטווח הנשפך (#) כדי להפנות אל מערך קיים

  • יצירת קבוע מערך מתוך ערכים קיימים

    באפשרותך לקבל את התוצאות של נוסחת מערך ששופכה ולהמיר אותה לחלקי הרכיבים שלה. בחר תא D9 ולאחר מכן הקש F2 כדי לעבור למצב עריכה. לאחר מכן, הקש F9 כדי להמיר את ההפניות לתאים לערכים, Excel מכן ממיר אותם קבוע מערך. בעת הקשה על Enter, הנוסחה, =D9#, אמורה להיות כעת ={10,20,30;40,50,60;70,80,90}.

  • ספירת תווים בטווח תאים

    הדוגמה הבאה מראה לך כיצד לספור את מספר התווים בטווח תאים. פעולה זו כוללת רווחים.

    ספירת מספר התווים הכולל בטווח ומערכים אחרים לעבודה עם מחרוזות טקסט

    =SUM(LEN(C9:C13))

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

    =AVERAGE(LEN(C9:C13))

  • תוכן התא הארוך ביותר בטווח C9:C13

    =INDEX(C9:C13,MATCH(MAX(LEN(C9:C13)),LEN(C9:C13),0),1)

    נוסחה זו פועלת רק כאשר טווח נתונים מכיל עמודת תאים אחת.

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

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

    MAX(LEN(C9:C13)

    ומחרוזת זו שוכנת במערך זה:

    LEN(C9:C13)

    ארגומנט סוג ההתאמה במקרה זה הוא 0. סוג ההתאמה יכול להיות ערך 1, 0 או -1.

    • 1 - החזרת הערך הגדול ביותר קטן או שווה לערך בדיקת המידע

    • 0 - החזרת הערך הראשון שווה בדיוק לערך בדיקת המידע

    • -1 - החזרת הערך הקטן ביותר גדול או שווה לערך בדיקת המידע שצוין

    • אם לא תציין סוג התאמה, Excel יניח שמדובר ב- 1.

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

    אם רצית לקבל את התוכן של מחרוזת הטקסט הקטנה ביותר, היית מחליף את MAX בדוגמה שלעיל ב- MIN.

  • איתור ‏n הערכים הקטנים ביותר בטווח

    דוגמה זו מראה כיצד למצוא את שלושת הערכים הקטנים ביותר בטווח תאים, כאשר מערך של נתונים לדוגמה בתאים B9:B18has נוצר עם: =INT(RANDARRAY(10,1)*100). שים לב ש- RANDARRAY היא פונקציה תנודתית, כך תשוב לקבל ערכה חדשה של מספרים אקראיים בכל פעם Excel מחשבת.

    Excel מערך כדי למצוא את הערך ה- N הקטן ביותר: =SMALL(B9#,SEQUENCE(D9))

    הזן =SMALL(B9#,SEQUENCE(D9), =SMALL(B9:B18,{1;2;3})

    נוסחה זו משתמשת בקבוע מערך כדי להעריך את הפונקציה SMALL שלוש פעמים ולהחזיר את שלושת החברים הקטנים ביותר במערך הכלולים בתאים B9:B18, כאשר 3 הוא ערך משתנה בתא D9. כדי למצוא ערכים נוספים, באפשרותך להגדיל את הערך בפונקציה SEQUENCE או להוסיף ארגומנטים נוספים בקבוע. ניתן גם להשתמש בפונקציות נוספות עם נוסחה זו, כגון SUM או AVERAGE. לדוגמה:

    =SUM(SMALL(B9#,SEQUENCE(D9))

    =AVERAGE(SMALL(B9#,SEQUENCE(D9))

  • איתור n הערכים הגדולים ביותר בטווח

    כדי למצוא את הערכים הגדולים ביותר בטווח, באפשרותך להחליף את הפונקציה SMALL בפונקציה LARGE. כמו כן, בדוגמה הבאה נעשה שימוש בפונקציות ROW ו- INDIRECT.

    הזן =LARGE(B9#,ROW(INDIRECT("1:3"))), או =LARGE(B9:B18,ROW(INDIRECT("1:3")))

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

    ‎=ROW(1:10)‎

    הנוסחה יוצרת עמודה של 10 מספרים שלמים רציפים. כדי לראות בעיות אפשריות, הוסף שורה מעל הטווח שמכיל את נוסחת המערך (כלומר, מעל שורה 1)‏. Excel את ההפניות לשורות, והנוסחה יוצרת כעת מספר שלם מ- 2 עד 11. לפתרון בעיה זו, יש להוסיף לנוסחה את הפונקציה INDIRECT‏:

    ‎=ROW(INDIRECT("1:10"))‎

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

    =SEQUENCE(10)

    בוא נבחן את הנוסחה שבה השתמשת קודם לכן — =LARGE(B9#,ROW(INDIRECT("1:3"))) - החל מה סוגריים פנימיים ופועלים כלפי חוץ: הפונקציה INDIRECT מחזירה ערכה של ערכי טקסט, במקרה זה הערכים 1 עד 3. הפונקציה ROW יוצרת מערך של שלושה עמודה. הפונקציה LARGE משתמשת בערכים בטווח התאים B9:B18, והיא מוערכת שלוש פעמים, פעם אחת עבור כל הפניה המוחזרת על-ידי הפונקציה ROW. אם ברצונך למצוא ערכים נוספים, עליך להוסיף טווח תאים גדול יותר לפונקציה INDIRECT. לבסוף, כמו בדוגמאות SMALL, באפשרותך להשתמש בנוסחה זו עם פונקציות אחרות, כגון SUM ו- AVERAGE.

  • סיכום טווח המכיל ערכי שגיאה

    הפונקציה SUM Excel אינה פועלים בעת ניסיון לסכם טווח המכיל ערך שגיאה, כגון #VALUE! או #N/A. דוגמה זו מראה לך כיצד לסכם את הערכים בטווח בשם נתונים המכיל שגיאות:

    השתמש במערכים כדי לטפל בשגיאות. לדוגמה, =SUM(IF(ISERROR(Data),",Data) תסכום את הטווח בשם נתונים גם אם הוא כולל שגיאות, #VALUE! או #NA!.

  • ‎=SUM(IF(ISERROR(Data),"",Data))‎

    הנוסחה יוצרת מערך חדש המכיל את הערכים המקוריים למעט ערכי שגיאה. החל מהפונקציות הפנימיות וכלפי חוץ, הפונקציה ISERROR מחפשת שגיאות בטווח התאים (Data). הפונקציה ‏IF מחזירה ערך ספציפי אם תנאי שאתה מציין מוערך כ- TRUE, וערך אחר אם התנאי מוערך כ- FALSE. במקרה זה, הפונקציה מחזירה מחרוזות ריקות (""‏) עבור כל ערכי השגיאה מכיוון שהם מוערכים כ- TRUE, ומחזירה את יתר הערכים מהטווח (Data) מכיוון שהם מוערכים כ- FALSE, כלומר אינם מכילים ערכי שגיאה. לאחר מכן, הפונקציה SUM מחשבת את הסכום הכולל עבור המערך המסונן.

  • ספירת ערכי השגיאה בטווח

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

    ‎=SUM(IF(ISERROR(Data),1,0))‎

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

    ‎=SUM(IF(ISERROR(Data),1))‎

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

    ‎=SUM(IF(ISERROR(Data)*1))‎

    גירסה זו עובדת מכיוון ש- TRUE*1=1 ו- FALSE*1=0.

ייתכן שיהיה עליך לסכם ערכים בהתבסס על תנאים.

באפשרותך להשתמש במערכים כדי לחשב בהתבסס על תנאים מסוימים. =SUM(IF(Sales>0,Sales)) תסכם את כל הערכים ש- 0 גדול מ- 0 בטווח שנקרא Sales.

לדוגמה, נוסחת מערך זו מסכמת רק את התאים השלם החיוביים בטווח בשם Sales, המייצג את התאים E9:E24 בדוגמה שלעיל:

‎=SUM(IF(Sales>0,Sales))‎

הפונקציה IF יוצרת מערך של ערכים חיוביים ושואים. כעיקרון, הפונקציה SUM מתעלמת מהערכים השקריים מכיוון ש- ‎0+0=0‎. טווח התאים שבו אתה משתמש בנוסחה יכול להיות מורכב מכל מספר של שורות ועמודות.

ניתן גם לסכם ערכים התואמים ליותר מתנאי אחד. לדוגמה, נוסחת מערך זו מחשבת ערכים ש- 0 AND קטן מ- 2500:

=SUM((Sales>0)*(Sales<2500)*(Sales))

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

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

=SUM(IF((Sales>0)+(Sales<2500),Sales))

לא ניתן להשתמש בפונקציות ‏AND ו- OR בנוסחאות מערך באופן ישיר מכיוון שפונקציות אלה מחזירות תוצאה יחידה, TRUE או FALSE, ופונקציות מערך דורשות מערכים של תוצאות. ניתן לעקוף בעיה זו באמצעות הלוגיקה שהוצגה בדוגמה הקודמת. במילים אחרות, אתה מבצע פעולות מתמטיות, כגון הוספה או כפל על ערכים היפגשו בתנאי OR או AND.

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

‎=AVERAGE(IF(Sales<>0,Sales))‎

הפונקציה ‏IF יוצרת מערך של ערכים שאינם שווים ל- 0 ולאחר מכן מעבירה ערכים אלה לפונקציה ‏AVERAGE.

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

‎=SUM(IF(MyData=YourData,0,1))‎

הנוסחה יוצרת מערך חדש בגודל זהה לזה של הטווחים שביניהם נערכת ההשוואה. הפונקציה ‏IF ממלאת את המערך בערך 0 ובערך 1‏ ‏(0 עבור אי התאמות ו- 1 עבור תאים זהים). לאחר מכן, הפונקציה SUM מחזירה את סיכום הערכים במערך.

ניתן לפשט את הנוסחה כך:

=SUM(1*(MyData<>YourData))

בדומה לנוסחה הסופרת את ערכי השגיאה בטווח, נוסחה זו עובדת מכיוון ש- TRUE*1=1 ו- FALSE*1=0.

נוסחת מערך זו מחזירה את מספר השורה של הערך המקסימלי בטווח בן עמודה אחת הנקרא Data‏:

‎=MIN(IF(Data=MAX(Data),ROW(Data),""))‎

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

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

‎=ADDRESS(MIN(IF(Data=MAX(Data),ROW(Data),"")),COLUMN(Data))‎

תמצא דוגמאות דומות בחוברת העבודה לדוגמה בגליון העבודה הבדלים בין ערכות נתונים.

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

  • נוסחת מערך מרובת-תאים

העתק את הטבלה כולה שלהלן והדבק אותה בתא A1 בגליון עבודה ריק.

איש מכירות

סוג רכב

מספר שנמכר

מחיר יחידה

סה"כ מכירות

איטון

סדאן

5

33000

קופה

4

37000

הרפז

סדאן

6

24000

קופה

8

21000

חנן

סדאן

3

29000

קופה

1

31000

כץ

סדאן

9

24000

קופה

5

37000

מעוז

סדאן

6

33000

קופה

8

31000

נוסחה (סכום כולל)

סכום כולל

‎'=SUM(C2:C11*D2:D11)‎

‎=SUM(C2:C11*D2:D11)‎

  1. כדי לראות את סך המכירות של קופות וsedans עבור כל איש מכירות, בחר תאים E2:E11, הזן את הנוסחה =C2:C11*D2:D11ולאחר מכן הקש Ctrl+Shift+Enter.

  2. כדי לראות את הסכום הכולל של כל המכירות, בחר את התא F11, הזן את הנוסחה =SUM(C2:C11*D2:D11)ולאחר מכן הקש Ctrl+Shift+Enter.

בעת הקשה על Ctrl+Shift+Enter, Excel מקיף את הנוסחה עם גשר מסולסל ({ }) ומוסיף מופע של הנוסחה בכל תא בטווח שנבחר. פעולה זו מתבצעת במהירות רבה, ולכן מה שאתה רואה בעמודה E הוא סכום המכירות הכולל עבור כל סוג רכב עבור כל איש מכירות. אם תבחר E2, ‏E3, ‏E4 וכן הלאה, תראה אותה נוסחה מוצגת: {‎=C2:C11*D2:D11}

הסכומים הכוללים בעמודה E מחושבים על-ידי נוסחת מערך

  • יצירת נוסחת מערך של תא יחיד

בתא D13 של חוברת העבודה, הקלד את הנוסחה הבאה ולאחר מכן הקש Ctrl+Shift+Enter:

‎=SUM(C2:C11*D2:D11)‎

במקרה זה, Excel את הערכים במערך (טווח התאים C2 עד D11) ולאחר מכן משתמש בפונקציה SUMכדי להוסיף את הסכומים יחד. התוצאה היא סך כולל של ‎$1,590,000‎ במכירות. דוגמה זו ממחישה את העוצמה האפשרית של נוסחה מסוג זה. לדוגמה, נניח שיש לך 1,000 שורות של נתונים. באפשרותך לסכם חלק מהנתונים, או את כולם, על-ידי יצירת נוסחת מערך בתא יחיד במקום לגרור את הנוסחה כלפי מטה 1,000 שורות.

כמו כן, שים לב שנוסחת התא היחיד בתא D13 אינה תלויה לחלוטין בנוסחה מרובת התאים (הנוסחה בתאים E2 עד E11). זהו יתרון נוסף של השימוש בנוסחאות מערך — גמישות. באפשרותך לשנות את הנוסחאות ב- עמודה E או למחוק עמודה לחלוטין, מבלי להשפיע על הנוסחה ב- D13.

נוסחאות מערך מציעות גם את היתרונות הבאים:

  • עקביות    אם תלחץ על תא כלשהו מ- E2 כלפי מטה, תראה אותה נוסחה. עקביות זו יכולה להבטיח דיוק רב יותר.

  • בטיחות    לא ניתן להחליף רכיב בנוסחת מערך מרובת-תאים. לדוגמה, לחץ על תא E3 והקש Delete. יהיה עליך לבחור את כל טווח התאים (E2 עד E11) ולשנות את הנוסחה עבור המערך כולו, או להשאיר את המערך כפי שהוא. כאמצעי בטיחות נוסף, עליך להקיש Ctrl+Shift+Enter כדי לאשר כל שינוי בנוסחה.

  • קבצים קטנים יותר    באפשרותך להשתמש לעתים קרובות בנוסחת מערך יחידה במקום בכמה נוסחאות ביניים. לדוגמה, חוברת העבודה עושה שימוש בנוסחת מערך אחת לחישוב התוצאות בעמודה E. אם היית משתמש בנוסחאות רגילות (כגון ‎=C2*D2‏, C3*D3‏, C4*D4...), היה עליך להשתמש ב- 11 נוסחאות שונות לחישוב אותן תוצאות.

על פי רוב, נוסחאות מערך עושות שימוש בתחביר נוסחאות רגיל. כל הנוסחאות מתחילות בסימן שוויון (=) וניתן להשתמש במרבית הפונקציות המוכללות ב- Excel בנוסחאות המערך. ההבדל העיקרי הוא בעת שימוש בנוסחת מערך, עליך להקיש Ctrl+Shift+Enter כדי להזין את הנוסחה. בעת ביצוע פעולה זו, Excel תוחם את נוסחת המערך בסוגריים מסולסלים — אם תקליד את הסוגריים המסולסלים בעצמך, הנוסחה תומר למחרוזת טקסט ולא תפעל.

פונקציות מערך יכולות להיות דרך יעילה לבניית נוסחאות מורכבות. נוסחת המערך ‎=SUM(C2:C11*D2:D11)‎ זהה לזו: ‎=SUM(C2*D2,C3*D3,‏ C4*D4,C5*D5,C6*D6,C7*D7,C8*D8,C9*D9,C10*D10,C11*D11).

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

בכל פעם שאתה עובד עם נוסחאות מרובות-תאים, עליך גם לזכור:

  • לבחור את טווח התאים להכלת התוצאות לפני הזנת הנוסחה. ביצעת פעולה זו בעת יצירת נוסחת המערך מרובת-התאים כאשר בחרת את תאים E2 עד E11.

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

  • באפשרותך להעביר או למחוק נוסחת מערך שלמה, אך לא להעביר או למחוק חלק ממנה. במילים אחרות, כדי לכווץ נוסחת מערך, עליך למחוק תחילה את הנוסחה הקיימת ולאחר מכן להתחיל מחדש.

  • כדי למחוק נוסחת מערך, בחר את טווח הנוסחאות כולו (לדוגמה, E2:E11), ולאחר מכן הקש Delete.

  • לא ניתן להוסיף תאים ריקים לתוך, או למחוק תאים מנוסחת מערך מרובה תאים.

לפעמים, ייתכן שיהיה עליך להרחיב נוסחת מערך. בחר את התא הראשון בטווח מערך קיים, והמשך עד שתבחר את הטווח כולו שברצונך להרחיב את הנוסחה שלו. הקש F2 כדי לערוך את הנוסחה ולאחר מכן הקש CTRL+SHIFT+ENTER כדי לאשר את הנוסחה לאחר התאמת טווח הנוסחאות. המפתח הוא לבחור את הטווח כולו, החל מהתא הימני העליון במערך. התא הימני העליון הוא התא שנערך.

נוסחאות מערך הן נהדרות, אך עלולים להיות להן כמה חסרונות:

  • מדי פעם תוכל לשכוח להקיש Ctrl+Shift+Enter. זה עלול לקרות אפילו למנוסים ביותר במשתמשי Excel. עליך לזכור להקיש שילוב מקשים זה בכל פעם שאתה מזין או עורך נוסחת מערך.

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

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

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

‎={1,2,3,4,5}‎

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

אם אתה מפריד את הפריטים באמצעות פסיקים, אתה יוצר מערך אופקי (שורה). אם אתה מפריד את הפריטים באמצעות תווי נקודה-פסיק, אתה יוצר מערך אנכי (עמודה). כדי ליצור מערך דו-ממדי, עליך להפריד את הפריטים בכל שורה באמצעות פסיקים ולהפריד את השורות באמצעות תווי נקודה-פסיק.

הנה מערך בשורה בודדת: {1,2,3,4}. להלן מערך בעמודה בודדת: {‎1;2‎;3‎;4}. ולהלן מערך של שתי שורות וארבע עמודות: {1,2,3,4;5,6,7,8}‎. במערך שתי השורות, השורה הראשונה היא 1, 2, 3 ו- 4, והשורה השניה היא 5, 6, 7 ו- 8. תו נקודה-פסיק בודד מפריד בין שתי השורות, בין 4 ו- 5.

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

ההליכים הבאים יעזרו לך לתרגל יצירת קבועים אופקיים, אנכיים ודו-ממדיים.

יצירת קבוע אופקי

  1. בגליון עבודה ריק, בחר את התאים A1 עד E1.

  2. בשורת הנוסחאות, הזן את הנוסחה הבאה ולאחר מכן הקש Ctrl+Shift+Enter:

    ‎={1,2,3,4,5}‎

    במקרה זה, עליך להקליד את הסוגר הפוחת וסגירתו ({ }) Excel להוסיף את הערכה השניה בשבילך.

    התוצאה הבאה מוצגת.

    קבוע מערך אופקי בנוסחה

יצירת קבוע אנכי

  1. בחוברת העבודה, בחר עמודה בת 5 תאים.

  2. בשורת הנוסחאות, הזן את הנוסחה הבאה ולאחר מכן הקש Ctrl+Shift+Enter:

    ‎={1;2;3;4;5}‎

    התוצאה הבאה מוצגת.

    קבוע מערך אנכי בנוסחת מערך

יצירת קבוע דו-ממדי

  1. בחוברת העבודה, בחר בלוק של תאים ברוחב 4 עמודות ובגובה 3 שורות.

  2. בשורת הנוסחאות, הזן את הנוסחה הבאה ולאחר מכן הקש Ctrl+Shift+Enter:

    ‎={1,2,3,4;5,6,7,8;9,10,11,12}‎

    ניתן לראות את התוצאה הבאה:

    קבוע מערך דו-ממדי בנוסחת מערך

שימוש בקבועים בנוסחאות

להלן דוגמה פשוטה המשתמשת בקבועים:

  1. בחוברת העבודה לדוגמה, צור גליון עבודה חדש.

  2. בתא A1, הקלד 3, ולאחר מכן הקלד 4 בתא B1,‏ 5 בתא C1‏, 6 בתא D1 ו- 7 בתא E1.

  3. בתא A3, הקלד את הנוסחה הבאה ולאחר מכן הקש Ctrl+Shift+Enter:

    ‎=SUM(A1:E1*{1,2,3,4,5})‎

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

    נוסחת מערך עם קבוע מערך

    הערך 85 מופיע בתא A3.

בסעיף הבא נסביר את אופן הפעולה של הנוסחה.

הנוסחה שהזנת זה עתה כוללת כמה חלקים.

תחביר של נוסחת מערך עם קבוע מערך

1. פונקציה

2. מערך מאוחסן

3. אופרטור

4. קבוע מערך

הרכיב האחרון בסוגריים הוא קבוע המערך: {1,2,3,4,5}. זכור ש- Excel אינו תוחם קבועי מערך בסוגריים מסולסלים; אתה אחראי להקליד אותם. זכור גם שאחרי הוספת קבוע לנוסחת מערך, הקש Ctrl+Shift+Enter כדי להזין את הנוסחה.

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

‎=SUM(A1*1,B1*2,C1*3,D1*4,E1*5)‎

לסיום, הפונקציה SUM מחברת את הערכים והסכום 85 מופיע בתא A3.

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

‎=SUM({3,4,5,6,7}*{1,2,3,4,5})‎

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

‎=SUM(A1:E1*{1,2,3,4,5})‎

קבועי מערך יכולים להכיל מספרים, טקסט, ערכים לוגיים (כגון TRUE ו- FALSE) וערכי שגיאה (כגון ‎#N/A). ניתן להשתמש במספרים בתבנית של מספר שלם, מספר עשרוני ותבניות מדעיות. אם אתה כולל טקסט, עליך לתחום את הטקסט במרכאות (").

קבועי מערך אינם יכולים להכיל נוסחאות, פונקציות או מערכים נוספים. במילים אחרות, הם יכולים להכיל רק טקסט או מספרים המופרדים באמצעות פסיקים או תווי נקודה-פסיק. Excel מציג הודעת אזהרה כאשר מוזנת נוסחה כגון {1,2,A1:D4} או ‎{1,2,SUM(Q2:Z8)}‎. כמו כן, ערכים מספריים אינם יכולים להכיל סימני אחוז, סימני דולר, פסיקים או סוגריים.

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

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

  2. בתיבה שם, הקלד רבעון1.

  3. בתיבה מפנה אל, הזן את הקבוע הבא (זכור להקליד את הסוגריים המסולסלים באופן ידני):

    ‎={"ינואר","פברואר","מרץ"}‎

    תוכן תיבת הדו-שיח אמור כעת להיראות כך:

    תיבת הדו-שיח 'עריכת שם' עם נוסחה

  4. לחץ על אישור, ולאחר מכן בחר שורה בת 3 תאים ריקים.

  5. הקלד את הנוסחה הבאה ולאחר מכן הקש Ctrl+Shift+Enter.

    ‎=רבעון1‎

    התוצאה הבאה מוצגת.

    מערך בעל שם המוזן כנוסחה

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

חפש את הבעיות הבאות כאשר קבועי המערך לא עובדים:

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

  • ייתכן שבחרת טווח תאים שאינו תואם למספר הרכיבים בקבוע. לדוגמה, אם תבחר עמודה בת 6 תאים לשימוש עם קבוע של 5 תאים, ערך השגיאה ‎#N/A יופיע בתא הריק. מצד שני, אם תבחר תאים מעטים מדי, Excel ישמיט את הערכים שאין להם תא מתאים.

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

הכפלת כל אחד מהפריטים במערך

  1. צור גליון עבודה חדש ולאחר מכן בחר בלוק של תאים ריקים ברוחב 4 עמודות ובגובה 3 שורות.

  2. הקלד את הנוסחה הבאה ולאחר מכן הקש Ctrl+Shift+Enter:

    ‎={1,2,3,4;5,6,7,8;9,10,11,12}*2‎

ריבוע הפריטים במערך

  1. בחר בלוק של תאים ריקים ברוחב 4 עמודות ובגובה 3 שורות.

  2. הקלד את נוסחת המערך הבאה ולאחר מכן הקש Ctrl+Shift+Enter:

    ‎={1,2,3,4;5,6,7,8;9,10,11,12}*{1,2,3,4;5,6,7,8;9,10,11,12}‎

    לחלופין, הזן את נוסחת המערך הבאה, אשר עושה שימוש באופרטור (^):

    ‎={1,2,3,4;5,6,7,8;9,10,11,12}^2‎

ביצוע חילוף של שורה חד-ממדית

  1. בחר עמודה בת 5 תאים ריקים.

  2. הקלד את הנוסחה הבאה ולאחר מכן הקש Ctrl+Shift+Enter:

    ‎=TRANSPOSE({1,2,3,4,5})‎

    למרות שהזנת קבוע מערך אופקי, הפונקציה TRANSPOSE ממירה את קבוע המערך לעמודה.

ביצוע חילוף של עמודה חד-ממדית

  1. בחר שורה בת 5 תאים ריקים.

  2. הזן את הנוסחה הבאה ולאחר מכן הקש Ctrl+Shift+Enter:

    ‎=TRANSPOSE({1;2;3;4;5})‎

למרות שהזנת קבוע מערך אנכי, הפונקציה TRANSPOSE ממירה את הקבוע לשורה.

ביצוע חילוף של קבוע דו-ממדי

  1. בחר בלוק של תאים ברוחב 3 עמודות ובגובה 4 שורות.

  2. הזן את הקבוע הבא ולאחר מכן הקש Ctrl+Shift+Enter:

    ‎=TRANSPOSE({1,2,3,4;5,6,7,8;9,10,11,12})‎

    הפונקציה ‏TRANSPOSE ממירה כל שורה לסידרה של עמודות.

סעיף זה מספק דוגמאות לנוסחאות מערך בסיסיות.

יצירת מערכים וקבועי מערך מתוך ערכים קיימים

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

יצירת מערך מתוך ערכים קיימים

  1. בגליון עבודה ב- Excel, בחר את התאים C8:E10, והזן נוסחה זו:

    ‎={10,20,30;40,50,60;70,80,90}‎

    זכור להקליד } (סוגר מסולסל פותח) לפני שתקליד 10 ו- { (סוגר מסולסל סוגר) לאחר שתקליד 90, משום אתה יוצר מערך של מספרים.

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

    10

    20

    30

    40

    50

    60

    70

    80

    90

  3. בחר את טווח התאים C1 עד E3.

  4. הזן את הנוסחה הבאה בשורת הנוסחאות ולאחר מכן הקש Ctrl+Shift+Enter:

    ‎=C8:E10

    מערך תאים 3x3 מופיע בתאים C1 עד E3 עם אותם ערכים שאתה רואה ב- C8 עד E10.

יצירת קבוע מערך מתוך ערכים קיימים

  1. כאשר התאים C1:C3 נבחרים, הקש F2 כדי לעבור למצב עריכה. 

  2. הקש F9 כדי להמיר את ההפניות לתאים לערכים. Excel ממיר את הערכים לקבוע מערך. הנוסחה אמורה להיות כעת ={10,20,30;40,50,60;70,80,90}.

  3. הקש Ctrl+Shift+Enter כדי להזין את קבוע המערך כנוסחת מערך.

ספירת תווים בטווח תאים

הדוגמאות הבאות מראות כיצד ניתן למנות את מספר התווים, לרבות רווחים, בטווח של תאים.

  1. העתק טבלה זו במלואה והדבק אותה בגליון עבודה בתא A1.

    נתונים

    זוהי

    קבוצה של תאים אשר

    מתכנסים

    כדי ליצור

    משפט אחד.

    מספר תווים כולל ב- A2:A6

    ‎=SUM(LEN(A2:A6))‎

    התוכן של התא הארוך ביותר (A3)

    ‎=INDEX(A2:A6,MATCH(MAX(LEN(A2:A6)),LEN(A2:A6),0),1)‎

  2. בחר תא A8 ולאחר מכן הקש Ctrl+Shift+Enter כדי לראות את מספר התווים הכולל בתאים A2:A6 (66).

  3. בחר את התא A10 ולאחר מכן הקש Ctrl+Shift+Enter כדי לראות את התוכן של התאים הארוך ביותר A2:A6 (תא A3).

הנוסחה הבאה משמשת בתא A8 סופרת את מספר התווים הכולל (66) בתאים A2 עד A6.

‎=SUM(LEN(A2:A6))‎

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

איתור ‏n הערכים הקטנים ביותר בטווח

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

  1. הזן מספרים אקראיים בתאים A1:A11.

  2. בחר תאים C1 עד C3. קבוצת תאים זו תכיל את התוצאות שיוחזרו על-ידי נוסחת המערך.

  3. הזן את הנוסחה הבאה ולאחר מכן הקש Ctrl+Shift+Enter:

    =SMALL(A1:A11,{1;2;3})

נוסחה זו משתמשת בקבוע מערך כדי להעריך את הפונקציה SMALL שלוש פעמים ולהחזיר את החברים הקטנים ביותר (1), החברים השניים הקטנים ביותר (2) ושלישיים הקטנים ביותר (3) במערך הכלולים בתאים A1:A10 כדי למצוא ערכים נוספים, עליך להוסיף ארגומנטים נוספים בקבוע. ניתן גם להשתמש בפונקציות נוספות עם נוסחה זו, כגון SUM או AVERAGE. לדוגמה:

=SUM(SMALL(A1:A10,{1,2,3})

=AVERAGE(SMALL(A1:A10,{1,2,3})

איתור n הערכים הגדולים ביותר בטווח

כדי לאתר את הערכים הגדולים ביותר בטווח, באפשרותך להחליף את הפונקציה SMALL בפונקציה LARGE. כמו כן, בדוגמה הבאה נעשה שימוש בפונקציות ROW ו- INDIRECT.

  1. בחר תאים D1 עד D3.

  2. בשורת הנוסחאות, הזן נוסחה זו ולאחר מכן הקש Ctrl+Shift+Enter:

    =LARGE(A1:A10,ROW(INDIRECT("1:3")))

בשלב זה, כדאי להכיר את פונקציות ROW ו- INDIRECT. באפשרותך להשתמש בפונקציה ‏ROW ליצירת מערך של מספרים שלמים רציפים. לדוגמה, בחר נוסחה עמודה של 10 תאים בחוברת העבודה של התרגול, הזן נוסחת מערך זו ולאחר מכן הקש Ctrl+Shift+Enter:

‎=ROW(1:10)‎

הנוסחה יוצרת עמודה של 10 מספרים שלמים רציפים. כדי לראות בעיות אפשריות, הוסף שורה מעל הטווח שמכיל את נוסחת המערך (כלומר, מעל שורה 1)‏. Excel יכוונן את ההפניות לשורות והנוסחה תפיק מספרים שלמים מ- 2 עד 11. לפתרון בעיה זו, יש להוסיף לנוסחה את הפונקציה INDIRECT‏:

‎=ROW(INDIRECT("1:10"))‎

הפונקציה ‏INDIRECT משתמשת במחרוזות טקסט כארגומנטים שלה (ולכן הטווח 1:10 תחום במרכאות כפולות). Excel לא מכוונן ערכי טקסט בעת הוספת שורות או מעביר את נוסחת המערך למיקום אחר. כתוצאה מכך, הפונקציה ROW מפיקה תמיד את מערך המספרים השלמים הרצוי.

בוא נחפש את הנוסחה שבה השתמשת קודם לכן — =LARGE(A5:A14,ROW(INDIRECT("1:3"))) - החל מהסו סוגריים פנימיים ופועלים כלפי חוץ: הפונקציה INDIRECT מחזירה ערכה של ערכי טקסט, במקרה זה הערכים 1 עד 3. הפונקציה ROW יוצרת מערך טורי של שלושה תאים. הפונקציה LARGE משתמשת בערכים בטווח התאים A5:A14, והיא מוערכת שלוש פעמים, פעם אחת עבור כל הפניה המוחזרת על-ידי הפונקציה ROW. הערכים 3200, 2700 ו- 2000 מוחזרים אל המערך של שלושה תאים בעמודה. אם ברצונך למצוא ערכים נוספים, עליך להוסיף טווח תאים גדול יותר לפונקציה INDIRECT.

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

איתור מחרוזת הטקסט הארוכה ביותר בטווח של תאים

חזור לדוגמה של מחרוזת הטקסט הקודמת, הזן את הנוסחה הבאה בתא ריק והקש Ctrl+Shift+Enter:

‎=INDEX(A2:A6,MATCH(MAX(LEN(A2:A6)),LEN(A2:A6),0),1)‎

הטקסט "קבוצת תאים" מופיע.

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

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

(MAX(LEN(A2:A6))

ומחרוזת זו שוכנת במערך זה:

LEN(A2:A6)

ארגומנט סוג ההתאמה הוא 0‏. סוג ההתאמה יכול להיות מורכב מערך של 1‏, 0 או ‎-1. אם תציין 1, הפונקציה MATCH תחזיר את הערך הגדול ביותר השווה לערך בדיקת המידע או קטן ממנו. אם תציין 0, הפונקציה MATCH תחזיר את הערך הראשון השווה בדיוק לערך בדיקת המידע. אם תציין ‎-1, הפונקציה MATCH תאתר את הערך הקטן ביותר השווה לערך בדיקת המידע המוגדר או גדול ממנו. אם לא תציין סוג התאמה, Excel יניח שמדובר ב- 1.

לסיום, הפונקציה INDEX לוקחת את הארגומנטים הבאים: מערך, וכן מספר שורה ומספר עמודה באותו מערך. טווח התאים A2:A6 מספק את המערך, הפונקציה MATCH מספקת את כתובת התא, והארגומנט הסופי ( 1 )מצייןשהערך מגיע מהערך עמודה במערך.

סעיף זה מספק דוגמאות של נוסחאות מערך מתקדמות.

סיכום טווח המכיל ערכי שגיאה

הפונקציה ‏SUM ב- Excel אינה פועלת כאשר אתה מנסה לסכם טווח המכיל ערך שגיאה, כגון ‎#N/A. דוגמה זו מראה לך כיצד לסכם את הערכים בטווח בשם Data המכיל שגיאות.

‎=SUM(IF(ISERROR(Data),"",Data))‎

הנוסחה יוצרת מערך חדש המכיל את הערכים המקוריים למעט ערכי שגיאה. החל מהפונקציות הפנימיות וכלפי חוץ, הפונקציה ISERROR מחפשת שגיאות בטווח התאים (Data). הפונקציה ‏IF מחזירה ערך ספציפי אם תנאי שאתה מציין מוערך כ- TRUE, וערך אחר אם התנאי מוערך כ- FALSE. במקרה זה, הפונקציה מחזירה מחרוזות ריקות (""‏) עבור כל ערכי השגיאה מכיוון שהם מוערכים כ- TRUE, ומחזירה את יתר הערכים מהטווח (Data) מכיוון שהם מוערכים כ- FALSE, כלומר אינם מכילים ערכי שגיאה. לאחר מכן, הפונקציה SUM מחשבת את הסכום הכולל עבור המערך המסונן.

ספירת ערכי השגיאה בטווח

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

‎=SUM(IF(ISERROR(Data),1,0))‎

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

‎=SUM(IF(ISERROR(Data),1))‎

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

‎=SUM(IF(ISERROR(Data)*1))‎

גירסה זו עובדת מכיוון ש- TRUE*1=1 ו- FALSE*1=0.

סיכום ערכים בהתבסס על תנאים

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

‎=SUM(IF(Sales>0,Sales))‎

הפונקציה ‏IF יוצרת מערך של ערכים חיוביים וערכים שקריים. כעיקרון, הפונקציה SUM מתעלמת מהערכים השקריים מכיוון ש- ‎0+0=0‎. טווח התאים שבו אתה משתמש בנוסחה יכול להיות מורכב מכל מספר של שורות ועמודות.

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

‎=SUM((Sales>0)*(Sales<=5)*(Sales))‎

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

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

‎=SUM(IF((Sales<5)+(Sales>15),Sales))‎

הפונקציה ‏IF מאתרת את כל הערכים הקטנים מ- 5 וגדולים מ- 15 ולאחר מכן מעבירה ערכים אלה לפונקציה ‏SUM.

לא ניתן להשתמש בפונקציות ‏AND ו- OR בנוסחאות מערך באופן ישיר מכיוון שפונקציות אלה מחזירות תוצאה יחידה, TRUE או FALSE, ופונקציות מערך דורשות מערכים של תוצאות. ניתן לעקוף בעיה זו באמצעות הלוגיקה שהוצגה בדוגמה הקודמת. במילים אחרות, אתה מבצע פעולות מתמטיות, כגון חיבור או כפל, בערכים התואמים לתנאי OR או AND.

חישוב ממוצע שאינו כולל אפסים

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

‎=AVERAGE(IF(Sales<>0,Sales))‎

הפונקציה ‏IF יוצרת מערך של ערכים שאינם שווים ל- 0 ולאחר מכן מעבירה ערכים אלה לפונקציה ‏AVERAGE.

ספירת ההבדלים בין שני טווחי תאים

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

‎=SUM(IF(MyData=YourData,0,1))‎

הנוסחה יוצרת מערך חדש בגודל זהה לזה של הטווחים שביניהם נערכת ההשוואה. הפונקציה ‏IF ממלאת את המערך בערך 0 ובערך 1‏ ‏(0 עבור אי התאמות ו- 1 עבור תאים זהים). לאחר מכן, הפונקציה SUM מחזירה את סיכום הערכים במערך.

ניתן לפשט את הנוסחה כך:

=SUM(1*(MyData<>YourData))

בדומה לנוסחה הסופרת את ערכי השגיאה בטווח, נוסחה זו עובדת מכיוון ש- TRUE*1=1 ו- FALSE*1=0.

איתור המיקום של הערך המקסימלי בטווח

נוסחת מערך זו מחזירה את מספר השורה של הערך המקסימלי בטווח בן עמודה אחת הנקרא Data‏:

‎=MIN(IF(Data=MAX(Data),ROW(Data),""))‎

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

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

‎=ADDRESS(MIN(IF(Data=MAX(Data),ROW(Data),"")),COLUMN(Data))‎

אישור

חלקים ממאמר זה התבססו על סידרה של עמודות של Excel Power User שנכתבו על-ידי קולין וילקוקס, והותגלו מפרקים 14 ו- 15 של נוסחאות Excel 2002, ספר שנכתב על-ידי ג'ון ויקנבאך, מנהל Excel MVP לשעבר.

זקוק לעזרה נוספת?

תוכל תמיד לשאול מומחה ב- Excel Tech Community או לקבל תמיכה בקהילת Answers.

למידע נוסף

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

נוסחאות מערך דינאמי לעומת נוסחאות מערך CSE מדור קודם

הפונקציה FILTER

הפונקציה RANDARRAY

הפונקציה SEQUENCE

הפונקציה SORT

הפונקציה SORTBY

הפונקציה UNIQUE

שגיאות ‎#SPILL!‎ ב- Excel

אופרטור חיתוך רמזי: @

מבט כולל על נוסחאות

זקוק לעזרה נוספת?

הרחב את הכישורים שלך
סייר בהדרכה
קבל תכונות חדשות לפני כולם
הצטרף למשתתפי Microsoft insider

האם מידע זה היה שימושי?

עד כמה אתה מרוצה מאיכות התרגום?

מה השפיע על החוויה שלך?

יש לך משוב נוסף? (אופציונלי)

תודה על המשוב!

×