נוסחת מערך – ספירה ללא "ספירה"
באחד הפוסטים הקודמים, הראיתי שיטה למיין עמודות ב"אקסל" מבלי להשתמש בכלי המיון הפנימי של "אקסל" (Sort), הנמצא בלשונית "נתונים" (Data).
היום אראה שיטה כיצד ניתן לספור מופעים של נתונים בתחום נתון (Range) מבלי להשתמש ב"ספירה".
למשל כשנרצה לדעת: כמה מספרים מופיעים בתחום, כמה תאים ריקים מופיעים בתחום, כמה תאים בתחום מכילים טכסט וכדומה.
באופן רגיל, כדי למצוא תשובות לשאלות הנ"ל, משתמשים בפונקציות מ"משפחת" COUNT, המבצעות את החישובים הנ"ל. מדובר בפונקציות הסופרות את:
1) הערכים המספריים (ב"אקסל": גם שבר, גם אחוזים וגם תאריך הם מספרים) הפונקציה COUNT
2) התאים שאינם ריקים- הפונקציה COUNTA
3) התאים הריקים -הפונקציה COUNTBLANK
4) התאים שעונים על תנאי מסוים/מספר תנאים - הפונקציות COUNTIF/IFS
בתמונה שלפנינו, בתחום D3:D12, ישנם מספר ערכים (מספריים וטכסטואליים) וכן מספר תאים ריקים.
תמונה מס. 1: שמוש ב-4 פונקציות מ"משפחת" COUNT
בעמודה Hמופיעות 4 נוסחאות, המשתמשות ב-4 פונקציות מ"משפחת" COUNT, ובעמודה G מוצגות התוצאות של כל אחת מהנוסחאות על התחום (D3:D12):
1) הפונקציה COUNTA [H3] מחזירה את התוצאה: 8 - בתחום המבוקש נמצאו 8 תאים שאינם ריקים.
2) הפונקציה COUNT [H5] מחזירה את התוצאה: 3 - בתחום המבוקש נמצאו 3 תאים שבהם ערכים מספריים.
3) הפונקציה COUNTBLANK [H7] מחזירה את התוצאה: 2 - בתחום המבוקש נמצאו 2 תאים ריקים.
4) הפונקציה COUNTIF [H9] מחזירה את התוצאה: 2 - בתחום המבוקש נמצאו 2 מספרים הגדולים מ-6.
כעת אציג פתרון חלופי: נוסחאות המבצעות ספירה מבלי להשתמש בפונקציות הספירה של "אקסל".
כל הנוסחאות שאציג הן נוסחאות מערך (Array Formulae) ולכן בסיום הקלדתן יש להקיש: CTRL+SHIFT+ENTER במקום הקשת: ENTER בנוסחא רגילה.
ראשית, במקום להשתמש בשם מפורש של תחום עדיף לתת לו שם (Named Range). לכן, לתחום: D3:D12 נקרא בשם MyRange.
היתרונות בשמוש ב- Named Range:
1) קל יותר לקרוא ולהבין נוסחאות
2) אם הנוסחא מועתקת לתאים אחרים, אין צורך לשנות את הכתובות היחסיות (relative references).
נציג שתי שיטות למתן שם לתחום תאים:
שיטה א':
1. סמן את תחום התאים (D3:D12)
2. הקלד את השם ב"תיבת השם" (Name Box) והקש: Enter
תמונה מס. 2: שיטה מס. 1 למתן שם לתחום תאים
שיטה ב':
1. סמן את תחום התאים (D3:D12)
2. בחר ב: Formula*Define Names*Define Name
(בממשק עברי בחר ב: נוסחאות*שמות מוגדרים*הגדר שם)
תמונה מס.3: שיטה מס. 2 למתן שם לתחום תאים - שלב א'
3. בחלון: New Name הקש את שם התחום (Name): MyRange, בחר בתחום עליו חל שם זה (כל חוברת העבודה): Workbook ואשר בלחיצה על כפתור OK.
בכל אחת משתי השיטות שהצגתי, נקבל Named Range (שם מוגדר) לתחום התאים: D3:D12.
בשם זה (MyRange) נוכל להשתמש במקום לנקוב ב"שם המפורש" (D3:D12).
כעת, לאחר שהגדרנו את ה- Named Range עבור תחום התאים המבוקש, נוכל להציג את נוסחאות המערך אשר תחלפנה את הנוסחאות המקוריות (אל תשכחו שבסיום הקלדת נוסחת מערך יש להקיש את צרוף המקשים CTRL+SHIFT+ENTER):
1. במקום הנוסחא:
=COUNTA(D3:D12)
נכתֹב את נוסחת המערך:
=SUM(IF(ISBLANK(MyRange),0,1))
הסבר: הנוסחא סוכמת (SUM) את כל התאים בתחום MyRange שאינם ריקים. היא בודקת אם כל תא ותא בתחום הוא ריק (ISBLANK) (באמצעות הפונקציה: IF).
על כל תא ריק (כלומר כשהתנאי מתקיים), היא מוסיפה את הערך: 0.
על כל תא שאיננו ריק (כלומר כשהתנאי אינו מתקיים), היא מוסיפה את הערך: 1.
התוצאה: 8
2. במקום הנוסחא:
=COUNT (D3:D12)
נכתֹב את נוסחת המערך:
=SUM(IF(ISNUMBER(MyRange),1,0))
הסבר: הנוסחא סוכמת (SUM) את כל התאים בתחום MyRange שיש בהם ערך מספרי. היא בודקת בכל תא ותא בתחום אם הערך שבו הוא מספר (ISNUMBER) (באמצעות הפונקציה: IF).
על כל תא שיש בו ערך מספרי (כלומר כשהתנאי מתקיים), היא מוסיפה את הערך: 1.
על כל תא שאין בו ערך מספרי (כלומר כשהתנאי אינו מתקיים), היא מוסיפה את הערך: 0.
התוצאה: 3
(כפי שהסברתי לעיל, ב"אקסל" גם שבר, אחוזים או תאריך נחשבים לערך מספרי).
3. במקום הנוסחא:
=COUNTBLANK (D3:D12)
נכתֹב את נוסחת המערך:
=SUM(IF(ISBLANK(MyRange),1,0))
הסבר: הנוסחא סוכמת (SUM) את כל התאים הריקים בתחום MyRange. היא בודקת בכל תא ותא בתחום אם הוא ריק (ISBLANK) (באמצעות הפונקציה: IF).
על כל תא ריק (כלומר כשהתנאי מתקיים), היא מוסיפה את הערך: 1.
על כל תא שאיננו ריק (כלומר כשהתנאי אינו מתקיים), היא מוסיפה את הערך: 0.
התוצאה: 2
נוסחא זו מאד דומה לנוסחא מס. 1, אלא שכאן אנו סוכמים את הערכים הריקים:
התנאי: ISBLANK(MyRange),1,0- מוסיפים 1 כשהתא ריק
אבל בנוסחא מס. 1, סכמנו את כל הערכים שאינם ריקים
התנאי: ISBLANK(MyRange),0,1 - מוסיפים 1 כשהתא אינו ריק
4. במקום הנוסחא:
=COUNTIF (D3:D12,”>6”)
נכתֹב את נוסחת המערך:
=SUM(IF(MyRange>6,IF(ISNUMBER(MyRange),1,0)))
הסבר: הנוסחא כאן קצת יותר מורכבת מקודמותיה.
כאן אנו רוצים לספור רק ערכים מספריים הגדולים מ: 6. אבל ב"אקסל" גםטכסט ערכו גדול מ-6. לכן, עלינו לשלב שני תנאים:
א. ערך גדול מ-6 - IF(MyRange>6)
ב. ערך מספרי - IF(ISNUMBER(MyRange),1,0)
קיום שני התנאים יחד (גם גדול מ-6 וגם מספר) - נותן לנו את התוצאה הרצויה: 2
להלן, כל נוסחאות המערך המחליפות את נוסחאות "משפחת" COUNT ותוצאותיהן:
תמונה מס.5: נוסחאות המערך המחליפות את הנוסחאות המקוריות
לסִכּוּם, הראינו כיצד אפשר להשתמש בנוסחאות מערך במקום שמוש בפונקציות ה- COUNT: COUNT, COUNTA, COUNTBLANK, COUNTIF
אני מקוה שהפוסט הזה ישמש אתכם ב"אקסל" ותוכלו להפיק ממנו תועלת.
אין תגובות:
הוסף רשומת תגובה