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

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

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

כדי להפוך משתמש power של Excel, עליך לדעת כיצד להשתמש בנוסחאות מערך, שאותם באפשרותך לבצע חישובים שלא ניתן לבצע באמצעות נוסחאות שאינה מערך. המאמר הבא מבוסס על סידרה של עמודות Excel Power User שנכתבו על-ידי Colin Wilcox והמותאמת מתוך פרקים 14 ו- 15 של Excel 2002 נוסחאות, ספר שנכתבו על-ידי John Walkenbach, MVP Excel.

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

לעתים קרובות, מתייחסים לנוסחאות מערך כנוסחאות CSE ‏(Ctrl+Shift+Enter) משום שבמקום פשוט להקיש Enter, עליך להקיש Ctrl+Shift+Enter כדי להשלים את הנוסחה.

מדוע להשתמש בנוסחאות מערך?

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

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

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

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

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

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

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

נסה זאת!

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

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

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

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

    איש
    מכירות

    סוג
    רכב

    מספר
    יחידות שנמכרו

    מחיר
    יחידה

    סך כל
    המכירות

    איטון

    סדאן

    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)‎

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

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

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

אם אתה עובד ב- Excel, ודא כי גיליון1 פעיל ולאחר מכן בחר את התאים E2:E11. הקש F2 והקלד את הנוסחה ‎=C2:C11*D2:D11 בתא הנוכחי, E2. אם תקיש Enter, תראה שפעולה זו מזינה את הנוסחה בתא E2 בלבד ומציגה 165000. במקום זאת, לאחר הקלדת הנוסחה, הקש Ctrl+Shift+Enter במקום Enter. כעת תראה תוצאות בתאים E2:E11. שים לב גם שהנוסחה מופיעה בתור {‎=C2:C11*D2:D11} בשורת הנוסחאות. כך ניתן לדעת שמדובר בנוסחת מערך. פעולה זו מוצגת להלן.

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

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

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

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

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

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

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

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

  • עקביות    אם תלחץ על תא כלשהו מ- 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 מציג הודעה המודיעה לך כי אין באפשרותך לשנות חלק ממערך.

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

  • כדי למחוק נוסחת מערך, בחר את הנוסחה כולה (לדוגמה, ‎=C2:C11*D2:D11‏), הקש Delete ולאחר מכן הקש Ctrl+Shift+Enter.

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

הרחבת נוסחת מערך

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

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

זכור לעשות זאת בתוכנית שולחן העבודה של Excel (לאחר שתוריד את חוברת העבודה למחשב).

הרחבת נוסחת מערך
  1. העתק טבלה זו במלואה לתא A1 בגליון עבודה של Excel.

    איש
    מכירות

    סוג
    רכב

    מספר
    יחידות שנמכרו

    מחיר
    יחידה

    סך כל
    המכירות

    איטון

    סדאן

    5

    33000

    165000

    קופה

    4

    37000

    148000

    הרפז

    סדאן

    6

    24000

    144000

    קופה

    8

    21000

    168000

    חנן

    סדאן

    3

    29000

    87000

    קופה

    1

    31000

    31000

    כץ

    סדאן

    9

    24000

    216000

    קופה

    5

    37000

    185000

    מעוז

    סדאן

    6

    33000

    198000

    קופה

    8

    31000

    248000

    סתוי

    סדאן

    2

    27000

    קופה

    3

    30000

    קופמן

    סדאן

    4

    22000

    קופה

    1

    41000

    ראובני

    סדאן

    5

    32000

    קופה

    3

    36000

    סכום כולל

  2. בחר את התא E18, הזן את הנוסחה של הסכום הכולל ‎=SUM(C2:C17*D2:D17)‎ בתא A20 והקש Ctrl+Shift+Enter.
    התשובה אמורה להיות 2,131,000.

  3. בחר את טווח התאים שמכיל את נוסחת המערך הנוכחית (E2:E11) ואת התאים הריקים (E12:E17) שנמצאים ליד הנתונים החדשים. במילים אחרות, בחר את תאים E2:E17.

  4. הקש F2 כדי לעבור למצב עריכה.

  5. בשורת הנוסחאות, שנה את C11 ל- C17, שנה את D11 ל- D17 ולאחר מכן הקש Ctrl+Shift+Enter‏.
    Excel מעדכן את הנוסחה בתאים E2 עד E11 ומציב מופע של הנוסחה בתאים החדשים, E12 עד E17.

  6. הקלד את נוסחת המערך ‎= SUM(C2:C17*D2*D17)‎ בתא F17 כך שתפנה לתאים משורה 2 עד שורה 17, ולאחר מכן הקש Ctrl+Shift+Enter כדי להזין את נוסחת המערך.
    הסכום הכולל החדש אמור להיות 2,131,000.

חסרונות של השימוש בנוסחאות מערך

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

  • מדי פעם אתה עלול לשכוח להקיש 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. השתמש בחוברת העבודה מהדוגמה הקודמת, או צור חוברת עבודה חדשה.

  2. בחר את התאים A1 עד E1.

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

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

    במקרה זה, עליך להקליד תווי סוגריים מסולסלים פותחים וסוגרים ({ }).

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

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

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

  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 כדי לעבור למצב עריכה.
    נוסחת המערך עדיין צריכה להיות ‎= C8:E10.

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

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

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

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

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

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

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

נתונים

זוהי

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

מתכנסים

כדי ליצור

משפט אחד.

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

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

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

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

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

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

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

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

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

  1. בחר את התאים A16 עד A18.
    קבוצת תאים זו תכיל את התוצאות שיוחזרו על-ידי נוסחת המערך.

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

    ‎=SMALL(A5:A14,{1;2;3})‎

הערכים 400‏‏, 475‏ ו- 500‏ מופיעים בתאים A16 עד A18, בהתאמה.

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

‎=SUM(SMALL(A 5 :A1 4 ,{1;2;3}))‎

‎=AVERAGE(SMALL(A 5 :A1 4 ,{1;2;3}))‎

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

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

  1. בחר את התאים A1 עד A3.

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

    ‎=LARGE(A5:A14,ROW(INDIRECT("1:3")))‎

הערכים 3200‏‏, 2700‏ ו- 2000‏ מופיעים בתאים A1 עד A3, בהתאמה.

בשלב זה, כדאי להכיר את פונקציות ROW ו- INDIRECT. באפשרותך להשתמש בפונקציה ‏ROW ליצירת מערך של מספרים שלמים רציפים. לדוגמה, בחר עמודה ריקה בת 10 תאים בחוברת העבודה לתרגול, הזן את נוסחת המערך שלהלן בתאים A5:A14 ולאחר מכן הקש 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 מפיקה מערך עמודה בן 3 תאים. הפונקציה LARGE משתמשת בערכים בטווח התאים A5:A14 ומוערכת שלוש פעמים, פעם אחת עבור כל הפניה המוחזרת על-ידי הפונקציה ROW. הערכים 3200‏, 2700 ו- 2000 מוחזרים למערך העמודה בן שלושת התאים. אם ברצונך לאתר ערכים נוספים, עליך להוסיף טווח תאים גדול יותר לפונקציה INDIRECT.

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

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

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

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

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

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

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

‎(MAX(LEN( A6 : A9 ))‎

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

LEN( A6:A9 )‎

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

לסיום, הפונקציה INDEX לוקחת את הארגומנטים הבאים: מערך, וכן מספר שורה ומספר עמודה באותו מערך. טווח התאים A6:A9 מספק את המערך, הפונקציה 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 Tech Community, לקבל תמיכה בקהילת Answers או להציע תכונה חדשה או שיפור ב- Excel User Voice.

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

למידע נוסף

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

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

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

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

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

×