נוסחת מערך היא נוסחה שיכולה לבצע חישובים מרובים בפריט אחד או יותר במערך. ניתן להתייחס למערך כשורה או עמודה של ערכים, או שילוב של שורות ועמודות של ערכים. נוסחאות מערך יכולות להחזיר תוצאות מרובות או תוצאה יחידה.
החל מעדכון ספטמבר 2018 עבור Microsoft 365, כל נוסחה שיכולה להחזיר תוצאות מרובות תשפוך אותן באופן אוטומטי כלפי מטה או לאורך התאים הסמוכים. שינוי זה באופן הפעולה מלווה גם בכמה פונקציות מערך דינאמי חדשות. יש להזין נוסחאות מערך דינאמיות בתא יחיד, בין שהן משתמשות בפונקציות קיימות או בפונקציות מערך דינאמי, ולאחר מכן לאשר אותן על-ידי הקשה על Enter. בגירסאות קודמות, נוסחאות מערך מדור קודם דורשות לבחור תחילה את טווח הפלט כולו ולאחר מכן לאשר את הנוסחה באמצעות Ctrl+Shift+Enter. הן נקראות בדרך כלל נוסחאות CSE .
ניתן להשתמש בנוסחאות מערך לביצוע משימות מורכבות, כגון:
- צור במהירות ערכות נתונים לדוגמה.
- ספירת מספר התווים הכלולים בטווח תאים.
- סיכום רק של המספרים התואמים לתנאים מסוימים, כגון הערכים הקטנים ביותר בטווח, או מספרים שנמצאים בין גבול עליון ותחתון.
- סיכום של כל ערך N בטווח של ערכים.
הדוגמאות הבאות מדגימות את אופן היצירה של נוסחאות מערך מרובות-תאים ונוסחאות מערך של תא יחיד. במידת האפשר, כללנו דוגמאות עם כמה מפונקציות המערך הדינאמי, וכן נוסחאות מערך קיימות שהוזנו הן כמערכים דינאמיים והן כמערכים מדור קודם.
הורד את הדוגמאות שלנו
הורד חוברת עבודה לדוגמה עם כל הדוגמאות של נוסחאות המערך במאמר זה.
מערכים מרובי-תאים ומערכים של תא יחיד
תרגיל זה מראה את אופן השימוש בנוסחאות מערך מרובות-תאים ושל תא יחיד לחישוב סידרה של תוצאות מכירות. סידרת הפעולות הראשונה משתמשת בנוסחה מרובת-תאים לחישוב סידרה של סכומי ביניים. הסידרה השניה משתמשת בנוסחת תא יחיד לחישוב סך כולל.
נוסחת מערך מרובת-תאים
כאן אנו מחשבים את סך כל המכירות של רכבי קופה וסדאן עבור כל איש מכירות על-ידי הזנת =F10:F19*G10:G19 בתא H10.
בעת הקשה על Enter, התוצאות זולגות כלפי מטה לתאים H10:H19. שים לב שטווח הזליגה מסומן באמצעות גבול בעת בחירת תא כלשהו בטווח הזליגה. ייתכן שתבחין גם שהנוסחאות בתאים H10:H19 מעומעמות. הן מיועדות לעיון בלבד, ולכן אם ברצונך להתאים את הנוסחה, יהיה עליך לבחור את תא H10, שבו שוכנת נוסחת האב.נוסחת מערך של תא יחיד
בתא 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,F13*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(5), או ={1; 2; 3; 4; 5}. התוצאה הבאה מוצגת:
-
יצירת קבוע דו-ממדי
בחר תא ריק עם מקום משמאל ומתחתיו והזן =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.
הפונקציה 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. ניתן להשתמש במספרים בתבנית של מספר שלם, מספר עשרוני ותבניות מדעיות. אם אתה כולל טקסט, עליך לתחום אותו במרכאות ("טקסט").
- קבועי מערך אינם יכולים להכיל נוסחאות, פונקציות או מערכים נוספים. במילים אחרות, הם יכולים להכיל רק טקסט או מספרים המופרדים באמצעות פסיקים או תווי נקודה-פסיק. Excel מציג הודעת אזהרה כאשר מוזנת נוסחה כגון {1,2,A1:D4} או {1,2,SUM(Q2:Z8)}. כמו כן, ערכים מספריים אינם יכולים להכיל סימני אחוז, סימני דולר, פסיקים או סוגריים.
הענקת שמות לקבועי מערך
אחת הדרכים הטובות ביותר להשתמש בקבועי מערך היא להעניק להם שמות. קל יותר להשתמש בקבועים בעלי שם והם יכולים להסתיר חלק מהמורכבות של נוסחאות מערך מפני משתמשים אחרים. כדי להעניק שם לקבוע מערך ולהשתמש בו בנוסחה, בצע את הפעולות הבאות:
מעבר לנוסחאות,>שמות מוגדרים,>הגדרת שם. בתיבה שם , הקלד רבעון1. בתיבה מפנה אל, הזן את הקבוע הבא (זכור להקליד את הסוגריים המסולסלים באופן ידני):
={"ינואר","פברואר","מרץ"}
תיבת הדו-שיח אמורה להיראות כך:
לחץ על 'אישור', בחר שורה עם שלושה תאים ריקים והזן =Quarter1.
התוצאה הבאה מוצגת:
אם ברצונך שהתוצאות יזלגו אנכית במקום אופקית, באפשרותך להשתמש בפונקציה=TRANSPOSE(Quarter1).
אם ברצונך להציג רשימה של 12 חודשים, כפי שאתה עשוי להשתמש בעת בניית דוח כספי, באפשרותך לבסס את השנה הנוכחית באמצעות הפונקציה SEQUENCE. היתרון החשוב ביותר של פונקציה זו הוא שלמרות שרק החודש מוצג, יש תאריך חוקי מאחוריו שניתן להשתמש בו בחישובים אחרים. ניתן למצוא דוגמאות אלה בקבוע המערך בעל שם ובגליונות העבודה של ערכת נתונים לדוגמה מהירה בחוברת העבודה לדוגמה.
=TEXT(DATE(YEAR(TODAY()),SEQUENCE(1,12),1),"MMM")
פעולה זו משתמשת בפונקציה DATE כדי ליצור תאריך בהתבסס על השנה הנוכחית, הפונקציה SEQUENCE יוצרת קבוע מערך מ- 1 עד 12 עבור ינואר עד דצמבר, ולאחר מכן הפונקציה TEXT ממירה את תבנית התצוגה ל- "mmm" (ינו, פברואר, מרץ וכולי). אם רצית להציג את שם החודש המלא, כגון ינואר, השתמש ב- "mmmm".
כאשר אתה משתמש בקבוע בעל שם כנוסחת מערך, זכור להזין את סימן השוויון, כגון =Quarter1, ולא רק Quarter1. אם לא תעשה כן, 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 -
איתור השורש הריבועי של פריטים בריבוע במערך
הזן =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,140,150,160,170,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:B18נוצר עם: =INT(RANDARRAY(10,1)*100). שים לב שהפונקציה RANDARRAY היא פונקציה נדיפה, כך שתקבל ערכה חדשה של מספרים אקראיים בכל פעם ש- Excel יבצע חישוב.
הזן =SMALL(B9#,SEQUENCE(D9), =SMALL(B9:B18,{1; 2; 3})
נוסחה זו משתמשת בקבוע מערך להערכת הפונקציה SMALL שלוש פעמים ומחזירה את 3 האיברים הקטנים ביותר במערך הכלול בתאים 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. דוגמה זו מראה לך כיצד לסכם את הערכים בטווח בשם 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, אשר מייצג את התאים 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, ופונקציות מערך דורשות מערכים של תוצאות. ניתן לעקוף בעיה זו באמצעות הלוגיקה שהוצגה בדוגמה הקודמת. במילים אחרות, אתה מבצע פעולות מתמטיות, כגון חיבור או כפל בערכים התואמים לתנאי 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 שנכתבו על-ידי קולין וילקוקס, ואומצו מפרקים 14 ו- 15 ב'נוסחאות' של Excel 2002, ספר שנכתב על-ידי ג'ון וולקנבך, לשעבר MVP של Excel.
זקוק לעזרה נוספת?
תמיד תוכל לשאול מומחה בקהילה הטכנולוגית של Excel או לקבל תמיכה בקהילות.
למידע נוסף
מערכים דינאמיים ואופן הפעולה של מערכים זולגים