נוסחת מערך היא נוסחה ה יכולה לבצע חישובים מרובים על פריט אחד או יותר במערך. ניתן לחשוב על מערך כשורה או עמודה של ערכים, או שילוב של שורות ועמודות של ערכים. נוסחאות מערך יכולות להחזיר תוצאות מרובות או תוצאה בודדת.
החל מהעדכון של ספטמבר 2018 עבור Microsoft 365, כל נוסחה שעשויה להחזיר תוצאות מרובות תשפוך אותן באופן אוטומטי כלפי מטה או לתאים השכנים. שינוי זה באופן הפעולה מלווה גם במספר פונקציות מערך דינאמי חדשות. נוסחאות מערך דינאמי, בין אם הן משתמשות בפונקציות קיימות או בפונקציות המערך הדינאמי, צריכות להזין רק בתא בודד ולאחר מכן לאשר על-ידי הקשה על Enter. קודם לכן, נוסחאות מערך מדור קודם דורשות בחירה תחילה של טווח הפלט כולו ולאחר מכן אישור הנוסחה באמצעות Ctrl+Shift+Enter. הם נקראים בדרך כלל נוסחאות CSE .
באפשרותך להשתמש בנוסחאות מערך כדי לבצע משימות מורכבות, כגון:
-
צור במהירות ערכות נתונים לדוגמה.
-
ספירת מספר התווים הכלולים בטווח תאים.
-
סיכום מספרים שעומדים בתנאים מסוימים בלבד, כגון הערכים נמוך ביותר בטווח, או מספרים השייכים לגבול עליון וגבול תחתון.
-
סיכום כל ערך N בטווח של ערכים.
הדוגמאות הבאות מראים כיצד ליצור נוסחאות מערך מרובות-תאים ותא יחיד. כאשר הדבר אפשרי, כללנו דוגמאות לחלק מפונקציות המערך הדינאמי, וכן נוסחאות מערך קיימות שהוזנו כמערכים דינאמיים ומערכים מדור קודם.
הורד את הדוגמאות שלנו
הורד חוברת עבודה לדוגמה עם כל דוגמאות נוסחת המערך במאמר זה.
תרגיל זה מראה את אופן השימוש בנוסחאות מערך מרובות-תאים ושל תא יחיד לחישוב סידרה של תוצאות מכירות. סידרת הפעולות הראשונה משתמשת בנוסחה מרובת-תאים לחישוב סידרה של סכומי ביניים. הסידרה השניה משתמשת בנוסחת תא יחיד לחישוב סך כולל.
-
נוסחת מערך מרובת-תאים
-
כאן אנו מחשבים את Total Sales of coupes and sedans עבור כל איש מכירות על-ידי הזנת =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. מומלץ גם לקבל סכומים בלתי תלויים כמו אלה, מכיוון שהיא עוזרת לאמת את מידת הדיוק של התוצאות.
-
נוסחאות מערך דינאמי מציעות גם את היתרונות הבאים:
-
עקביות אם תלחץ על אחד מהתאים מאופק 10 כלפי מטה, תראה את אותה נוסחה. עקביות זו יכולה להבטיח דיוק רב יותר.
-
בטיחות לא ניתן להחליף רכיב בנוסחת מערך מרובת-תאים. לדוגמה, לחץ על תא H11 והקש Delete. Excel לא ישנה את פלט המערך. כדי לשנות אותו, עליך לבחור את התא הימני העליון במערך, או בתא H10.
-
קבצים קטנים יותר באפשרותך להשתמש לעתים קרובות בנוסחת מערך יחידה במקום בכמה נוסחאות ביניים. לדוגמה, הדוגמה למכירות רכב משתמשת בנוסחת מערך אחת לחישוב התוצאות בעמודה E. אם השתמשת בנוסחאות רגילות כגון =F10*G10, F11*G11, F12*G12 וכן הלאה, היית משתמש ב- 11 נוסחאות שונות לחישוב אותן תוצאות. זה לא עניין גדול, אבל מה אם היו לך אלפי שורות בסך הכל? אז זה יכול לעשות הבדל גדול.
-
יעילות פונקציות מערך יכולות להיות דרך יעילה לבניית נוסחאות מורכבות. נוסחת המערך =SUM(F10:F19*G10:G19) זהה לנוסחה הבאה: =SUM(F10*G10,F11*G11,F12*G12,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(5), או ={1; 2; 3; 4; 5}. 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, ולא רק ברבעון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
-
חיפוש השורש הריבועי של פריטים בריבוע במערך
הזן =SQRT(SEQUENCE(1,12)^2), או =SQRT({1,2,3,4; 5,6,7,8; 9,10,11,12}^2)
-
ביצוע חילוף של שורה חד-ממדית
Enter =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:B18has עם: =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 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) |
-
כדי לראות את סה"כ המכירות של קופות וסedans עבור כל איש מכירות, בחר את התאים E2:E11, הזן את הנוסחה =C2:C11*D2:D11 ולאחר מכן הקש Ctrl+Shift+Enter.
-
כדי לראות את הסכום הכולל של כל המכירות, בחר את תא F11, הזן את הנוסחה =SUM(C2:C11*D2:D11) ולאחר מכן הקש Ctrl+Shift+Enter.
בעת הקשה על Ctrl+Shift+Enter, Excel תקיף את הנוסחה בסוגריים מסולסלים ({ }) ומוסיף מופע של הנוסחה בכל תא בטווח שנבחר. פעולה זו מתבצעת במהירות רבה, ולכן מה שאתה רואה בעמודה E הוא סכום המכירות הכולל עבור כל סוג רכב עבור כל איש מכירות. אם תבחר E2, E3, E4 וכן הלאה, תראה אותה נוסחה מוצגת: {=C2:C11*D2:D11}.
-
יצירת נוסחת מערך של תא יחיד
בתא 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.
ההליכים הבאים יעזרו לך לתרגל יצירת קבועים אופקיים, אנכיים ודו-ממדיים.
יצירת קבוע אופקי
-
בגליון עבודה ריק, בחר את התאים A1 עד E1.
-
בשורת הנוסחאות, הזן את הנוסחה הבאה ולאחר מכן הקש Ctrl+Shift+Enter:
={1,2,3,4,5}
במקרה זה, עליך להקליד את הסוגריים המסולסלים לפתיחה ולסיום ({ }), ו- Excel יוסיף את הערכה השניה עבורך.
התוצאה הבאה מוצגת.
יצירת קבוע אנכי
-
בחוברת העבודה, בחר עמודה בת 5 תאים.
-
בשורת הנוסחאות, הזן את הנוסחה הבאה ולאחר מכן הקש Ctrl+Shift+Enter:
={1;2;3;4;5}
התוצאה הבאה מוצגת.
יצירת קבוע דו-ממדי
-
בחוברת העבודה, בחר בלוק של תאים ברוחב 4 עמודות ובגובה 3 שורות.
-
בשורת הנוסחאות, הזן את הנוסחה הבאה ולאחר מכן הקש Ctrl+Shift+Enter:
={1,2,3,4;5,6,7,8;9,10,11,12}
ניתן לראות את התוצאה הבאה:
שימוש בקבועים בנוסחאות
להלן דוגמה פשוטה המשתמשת בקבועים:
-
בחוברת העבודה לדוגמה, צור גליון עבודה חדש.
-
בתא A1, הקלד 3, ולאחר מכן הקלד 4 בתא B1, 5 בתא C1, 6 בתא D1 ו- 7 בתא E1.
-
בתא 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.
-
בתיבה מפנה אל, הזן את הקבוע הבא (זכור להקליד את הסוגריים המסולסלים באופן ידני):
={"ינואר","פברואר","מרץ"}
תוכן תיבת הדו-שיח אמור כעת להיראות כך:
-
לחץ על אישור, ולאחר מכן בחר שורה בת 3 תאים ריקים.
-
הקלד את הנוסחה הבאה ולאחר מכן הקש Ctrl+Shift+Enter.
=רבעון1
התוצאה הבאה מוצגת.
כאשר אתה משתמש בקבוע בעל שם כנוסחת מערך, זכור להזין את סימן השוויון. אם לא תעשה כן, Excel יפרש את המערך כמחרוזת של טקסט והנוסחה שלך לא תפעל כצפוי. לסיום, זכור שבאפשרותך להשתמש בשילובים של טקסט ומספרים.
חפש את הבעיות הבאות כאשר קבועי המערך לא עובדים:
-
ייתכן שרכיבים מסוימים אינם מופרדים באמצעות התו הנכון. אם אתה מושמט פסיק או נקודה-פסיק, או אם אתה מציב תו כזה במקום הלא נכון, ייתכן ש קבוע המערך לא ייווצר כראוי, או שתוצג הודעת אזהרה.
-
ייתכן שבחרת טווח תאים שאינו תואם למספר הרכיבים בקבוע. לדוגמה, אם תבחר עמודה בת 6 תאים לשימוש עם קבוע של 5 תאים, ערך השגיאה #N/A יופיע בתא הריק. מצד שני, אם תבחר תאים מעטים מדי, Excel ישמיט את הערכים שאין להם תא מתאים.
הדוגמאות הבאות מציגות כמה מהדרכים שבהן ניתן להשתמש בקבועי מערך בנוסחאות מערך. חלק מהדוגמאות משתמשות בפונקציה TRANSPOSE כדי להמיר שורות לעמודות ולהיפך.
הכפלת כל אחד מהפריטים במערך
-
צור גליון עבודה חדש ולאחר מכן בחר בלוק של תאים ריקים ברוחב 4 עמודות ובגובה 3 שורות.
-
הקלד את הנוסחה הבאה ולאחר מכן הקש Ctrl+Shift+Enter:
={1,2,3,4;5,6,7,8;9,10,11,12}*2
ריבוע הפריטים במערך
-
בחר בלוק של תאים ריקים ברוחב 4 עמודות ובגובה 3 שורות.
-
הקלד את נוסחת המערך הבאה ולאחר מכן הקש 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
ביצוע חילוף של שורה חד-ממדית
-
בחר עמודה בת 5 תאים ריקים.
-
הקלד את הנוסחה הבאה ולאחר מכן הקש Ctrl+Shift+Enter:
=TRANSPOSE({1,2,3,4,5})
למרות שהזנת קבוע מערך אופקי, הפונקציה TRANSPOSE ממירה את קבוע המערך לעמודה.
ביצוע חילוף של עמודה חד-ממדית
-
בחר שורה בת 5 תאים ריקים.
-
הזן את הנוסחה הבאה ולאחר מכן הקש Ctrl+Shift+Enter:
=TRANSPOSE({1;2;3;4;5})
למרות שהזנת קבוע מערך אנכי, הפונקציה TRANSPOSE ממירה את הקבוע לשורה.
ביצוע חילוף של קבוע דו-ממדי
-
בחר בלוק של תאים ברוחב 3 עמודות ובגובה 4 שורות.
-
הזן את הקבוע הבא ולאחר מכן הקש Ctrl+Shift+Enter:
=TRANSPOSE({1,2,3,4;5,6,7,8;9,10,11,12})
הפונקציה TRANSPOSE ממירה כל שורה לסידרה של עמודות.
סעיף זה מספק דוגמאות לנוסחאות מערך בסיסיות.
יצירת מערכים וקבועי מערך מתוך ערכים קיימים
הדוגמה הבאה מסבירה כיצד להשתמש בנוסחאות מערך ליצירת קישורים בין טווחי תאים בגליונות עבודה שונים. כמו כן, ניתן לראות בה כיצד ליצור קבוע מערך מאותה סדרת ערכים.
יצירת מערך מתוך ערכים קיימים
-
בגליון עבודה ב- Excel, בחר את התאים C8:E10, והזן נוסחה זו:
={10,20,30;40,50,60;70,80,90}
זכור להקליד } (סוגר מסולסל פותח) לפני שתקליד 10 ו- { (סוגר מסולסל סוגר) לאחר שתקליד 90, משום אתה יוצר מערך של מספרים.
-
הקש Ctrl+Shift+Enter, המזין מערך זה של מספרים בטווח התאים C8:E10 באמצעות נוסחת מערך. בגליון העבודה, הטווח C8 עד E10 אמור להיראות כך:
10
20
30
40
50
60
70
80
90
-
בחר את טווח התאים C1 עד E3.
-
הזן את הנוסחה הבאה בשורת הנוסחאות ולאחר מכן הקש Ctrl+Shift+Enter:
=C8:E10
מערך תאים של 3x3 מופיע בתאים C1 עד E3 עם אותם ערכים שאתה רואה ב- C8 עד E10.
יצירת קבוע מערך מתוך ערכים קיימים
-
כאשר התאים C1:C3 נבחרו, הקש F2 כדי לעבור למצב עריכה.
-
הקש F9 כדי להמיר את ההפניות לתאים לערכים. Excel ממיר את הערכים לקבוע מערך. הנוסחה אמורה להיות כעת ={10,20,30; 40,50,60; 70,80,90}.
-
הקש Ctrl+Shift+Enter כדי להזין את קבוע המערך כנוסחת מערך.
ספירת תווים בטווח תאים
הדוגמאות הבאות מראות כיצד ניתן למנות את מספר התווים, לרבות רווחים, בטווח של תאים.
-
העתק טבלה זו במלואה והדבק אותה בגליון עבודה בתא A1.
נתונים
זוהי
קבוצה של תאים אשר
מתכנסים
כדי ליצור
משפט אחד.
מספר תווים כולל ב- A2:A6
=SUM(LEN(A2:A6))
התוכן של התא הארוך ביותר (A3)
=INDEX(A2:A6,MATCH(MAX(LEN(A2:A6)),LEN(A2:A6),0),1)
-
בחר את תא A8 ולאחר מכן הקש Ctrl+Shift+Enter כדי לראות את מספר התווים הכולל בתאים A2:A6 (66).
-
בחר את תא A10 ולאחר מכן הקש Ctrl+Shift+Enter כדי לראות את התוכן של התאים הארוך ביותר A2:A6 (תא A3).
הנוסחה הבאה משמשת בתא A8 סופרת את מספר התווים הכולל (66) בתאים A2 עד A6.
=SUM(LEN(A2:A6))
במקרה זה, הפונקציה LEN מחזירה את האורך של כל מחרוזת טקסט בכל אחד מהתאים בטווח. לאחר מכן , הפונקציה SUM מחברת ערכים אלה יחד ומציגה את התוצאה (66).
איתור n הערכים הקטנים ביותר בטווח
דוגמה זו מראה כיצד ניתן לאתר את שלושת הערכים הקטנים ביותר בטווח של תאים.
-
הזן מספר מספרים אקראיים בתאים A1:A11.
-
בחר את התאים C1 עד C3. קבוצת תאים זו תכיל את התוצאות שיוחזרו על-ידי נוסחת המערך.
-
הזן את הנוסחה הבאה ולאחר מכן הקש 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.
-
בחר את התאים D1 עד D3.
-
בשורת הנוסחאות, הזן נוסחה זו ולאחר מכן הקש 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 Formulas, ספר שנכתב על-ידי John Walkbach, MVP לשעבר של Excel.
זקוק לעזרה נוספת?
תוכל תמיד לשאול מומחה ב- Excel Tech Community או לקבל תמיכה בקהילת Answers.
למידע נוסף
מערכים דינאמיים ואופן הפעולה של מערכים זולגים
