כיצד לעבוד עם מערכי פונקציות באקסל

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

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

אוצר מילים

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

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

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

סוגי מערכי פונקציות באקסל

באופן כללי, ניתן לפרט את הסוגים הבאים של מערכי פונקציות ב- Excel:

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

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

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

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

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

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

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

ראשית, עלינו לבחור את הטווח עליו תוחל הנוסחה בעתיד. במקרה שלנו, מדובר בקבוצת תאים שמתחילה ב-E3 ומסתיימת ב-E8.

לאחר מכן שמים את הסמן בשורת הנוסחאות, ונכתוב שם =C3:C8*D3:D8.

המרת נוסחה לפורמט מערך. עליך ללחוץ על Ctrl + Shift + Enter.

לאחר מכן, אנו מקבלים טבלה עם סיכומי ביניים מוכנים.

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

חָשׁוּב! אי אפשר לשנות את מה שנכתב על ידי נוסחת מערך. אל תדאג בקשר לזה. כל המידע מוגן בצורה מאובטחת.

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

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

1. בחר את התא שיכיל את תוצאת החישוב באמצעות נוסחת המערך.
2. הזן שם את הנוסחה. במקרה שלנו, נסכם את הערכים מטווח היעד, אז נשתמש בנוסחה SUM(C3:C8*D3:D8).
3. באמצעות השילוב Ctrl + Shift + Enter, אנו הופכים את הנוסחה הסטנדרטית לכזו שעובדת עם מערכי נתונים.

כתוצאה מכך, אנו מקבלים את התוצאה הבאה.

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

בואו נסתכל מקרוב על התחביר של נוסחה זו. 

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

רכיבים של נוסחאות מערך:

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

כדי להקל על ההבנה, הנה תמונה קטנה.

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

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

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

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

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

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

התוצאה היא הנוסחה המוצגת בצילום המסך.

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

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

1. Excel בודק תחילה את כל המנהלים והמוצרים כדי לראות אם הם תואמים את הקריטריונים הנתונים. אם כן, הפונקציה מחזירה 1, אם לא, 0.
   

   

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

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

דוגמה: קבלת מכירה

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

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

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

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

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

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

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

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

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

חָשׁוּב! סוגריים אלה אינם טקסט. לא ניתן להזין אותם באופן ידני. אם אתה צריך לערוך את הנוסחה, אז בסוף הקלט אתה שוב צריך ללחוץ על השילוב Ctrl + Shift + Enter במקום פשוט ללחוץ על מקש Enter.

דוגמה: החלפת שורות ועמודות

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

תארו לעצמכם שיש לנו מערך דו מימדי כזה, הממוקם אנכית.

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

לאחר מכן, עליך להזין את הנוסחה =TRANSPעל ידי הזנת A1:B8 כארגומנט הפונקציה.

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

עריכת נוסחת מערך

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

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

שינוי התוכן של מערך

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

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

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

מערכי פונקציות

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

מַפעִיל SUM

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

מַפעִיל TRANSP

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

מַפעִיל MOBR

פונקציה זו מאפשרת ליצור מטריצה ​​הפוכה. משמש כאשר המשוואה מכילה מספר אלמונים.