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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

  • כאן אנו מחשב את סך כל המכירות של קופה ומכוניות עבור כל איש מכירות על-ידי הזנת = 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 כלפי מטה, תראה את אותה נוסחה. עקביות זו יכולה להבטיח דיוק רב יותר.

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

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

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

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

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

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

= {1, 2, 3, 4, 5} או = {"ינואר", "פבואר", "March"}

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

הפונקציה רצף מקימה את המקבילה של קבוע המערך {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, 5 * 3, 6 * 4, 7 * 5 )

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

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

= Sum (רצף (1, 5, 3, 1) * רצף (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)}‎. כמו כן, ערכים מספריים אינם יכולים להכיל סימני אחוז, סימני דולר, פסיקים או סוגריים.

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

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

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

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

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

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

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

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

אם ברצונך שהתוצאות ישפכו באופן אנכי במקום אופקית, תוכל להשתמש ב- = בצעחילוף(רבעון1).

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

= TEXT (DATE (YEAR (TODAY ()), רצף (1, 12), 1), "mmm")

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

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

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

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

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

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

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

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

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

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

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

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

    הזן = בצע חילוף (רצף (1, 5)), או = בצע חילוף ({1, 2, 3, 4, 5})

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

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

    Enter = ' בצע חילוף ' (רצף (5, 1)), או = בצע חילוף ({1; 2; 3; 4; 5})

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

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

    הזן = בצע חילוף (רצף (3, 4)), או = בצע חילוף ({1, 2, 3, 4; 5, 6, 7, 8; 9, 10, 11, 12}})

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

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

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

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

    הזן = רצף (3, 6, 10, 10), או = {10, 20, 30, 40, 50, 60; 90100110120; 130140150160170180}

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

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

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

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

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

    • 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, רצף (D9))

    הזן = small (B9, רצף (D9), = SMALL (B9: B18, {1; 2; 3})

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

    = SUM (SMALL (B9, רצף (D9))

    = AVERAGE (SMALL (B9, רצף (D9))

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

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

    הזן = גדול (B9, ROW (עקיף ("1:3")), או = גדול (B9: B18, ROW (עקיף ("1:3")))

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

    ‎=ROW(1:10)‎

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

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

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

    = רצף (10)

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

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

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

    השתמש במערכים כדי לטפל בשגיאות. לדוגמה, = SUM (IF (ISERROR (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 בטווח שנקרא Sales.

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

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

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

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

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

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

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

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

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

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

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

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

  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:

    = גדול (A1: A10, ROW (עקיף ("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 מפיקה תמיד את מערך המספרים השלמים הרצוי.

בוא נבחן את הנוסחה שבה השתמשת מוקדם יותר – = גדול (A5: שורה, שורה (עקיף ("1:3"))) – החל מהסוגריים הפנימיים והעבודה כלפי חוץ: הפונקציה indirect מחזירה קבוצה של ערכי טקסט, במקרה זה הערכים 1 עד 3. הפונקציה ROW בתורו יוצרת מערך טורי בן שלושה תאים. הפונקציה LARGE משתמשת בערכים בטווח התאים A5: שלוש פעמים, והיא מוערכת שלוש פעמים, פעם אחת עבור כל הפניה המוחזרת על-ידי הפונקציה 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 שנכתבו על-ידי קולין וילקוקס, והותאמו מתוך פרקים 14 ו-15 מתוך Excel 2002, ספר שנכתב על-ידי יוחנן Walkenbach, MVP של Excel לשעבר.

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

ניתן לשאול תמיד מומחה ב- קהילת Tech Community עבור Excel, לקבל תמיכה בקהילת Answers או להציע תכונה חדשה או שיפור ב- User Voice עבור Excel.

למידע נוסף

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

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

הפונקציה FILTER

הפונקציה RANDARRAY

הפונקציה SEQUENCE

הפונקציה SINGLE

הפונקציה SORT

הפונקציה SORTBY

הפונקציה UNIQUE

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

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

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

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

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

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

×