איך עוקפים NESTED IF באקסל - בעזרת CHOOSE

Bypassing Excel’s Nested IF with the

CHOOSE Function

                                                                                                         

השנה מתקרבת לסיומה ואנו רוצים לתגמל את העובדים שלנו בבונוס מתאים:

העובדים מתוגמלים לפי דרוג משרה (מ-1 עד 5)

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

         

          דרוג 1 - לא זכאי לבונוס

          דרוג 2 - זכאי לבונוס בסך 100 ש"ח

         דרוג 3 - זכאי לבונוס בסך 500 ש"ח

         דרוג 4 - זכאי לבונוס בסך 1000 ש"ח

          דרוג 5 - זכאי לבונוס בסך 1000 ש"ח

                                                                  

          פתרון מקובל בבעיות כאלה הוא השמוש ב- Nested IF או כמו שהוא קרוי בעברית  IF מקונן:

הנוסחה (בתא C2) "שולפת"  מטבלת הפרמטרים (E3:F7) את סכום הבונוס המתאים לדרוג המשרה (בתא B2):

         

לאחר ההקלדה (בתא C2), נקליק קליק כפול בפינה הימנית התחתונה

של התא:

והתוצאה:
הנוסחה תועתק לכל התאים הרלבנטיים בעמודה C - (C3:C24)

                         

            

פתרון אלטרנטיבי:

פתרון קצר יותר אפשרי על-ידי שמוש בפונקציה: CHOOSE

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

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

ב. המספר שאותו מחפשים חייב, כאמור, להיות שלם וחיובי. אם קיים בחברה קוד דרוג 0 ׁ(או קוד דרוג שלילי) - הנוסחה תחזיר שגיאה: #VALUE!

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

אם B2 = 1, הנוסחה תחזיר את הערך הראשון (F3=0)

אם B2 = 2, הנוסחה תחזיר את הערך השני (F4=100)

אם B2 = 3, הנוסחה תחזיר את הערך השלישי (F5=500)

וכו'

לסכום, הצגנו שני פתרונות לבעית הבונוסים:

האחד, פתרון בשיטת ה- Nested IF

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

בכך דומה פתרון זה לפתרונות ה: IF-INDEX וה: IF-VLOOKUP שהוצגו בפוסט הקודם.

מקַוֶה שתפיקו תועלת מההסבר J J J

איך עוקפים NESTED IF באקסל

Bypassing Excel’s Nested IF
with INDEX or VLOOKUP

אקסל: שתי שיטות במקום IF מקונן

                                                                                                         

לפנינו בתאים (A2:E31) נתוני מכירות של סוכני החברה בינואר 2021 

תמונה מס.1 : נתוני מכירות הסוכנים לפני חִשוב הבונוס

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

         

מי שמכר ב-3000 ש"ח (או יותר) אבל פחות מ-4000 ש"ח -
יקבל בונוס בסך 30%

       מי שמכר ב-2000 ש"ח (או יותר) - יקבל בונוס בסך 20%

       מי שמכר ב -1000 ש"ח (או יותר) - יקבל בונוס בסך 10%

מי שמכר בסכום שאינו עולה על 1000 ש"ח

(או מי שמכר ב-4000 ש"ח או יותר) - לא יקבל כל בונוס 

פתרון מקובל בבעיות כאלה הוא השמוש ב- Nested IF או כמו שהוא קרוי בעברית  IF מקונן:

אם סכום המכירות > 4000, חשב בונוס לפי: סכום מכירות*30%

אחרת,

אם סכום המכירות < 3000, חשב בונוס לפי: סכום מכירות*20%

אחרת,

אם סכום המכירות < 2000, חשב בונוס לפי: סכום מכירות*10%

אחרת,

אם סכום המכירות < 1000 או לא קטן מ-4000, אל תעניק בונוס   

בכל הדוגמאות, אנו משתמשים בטבלת פרמטרים.

                                                    תמונה מס. 2: טבלת פרמטרים

מומלץ להשתמש בטבלת פרמטרים מ-3 סיבות:

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

ב.    אם בעתיד יוחלט לשנות את ערכי הבונוס (סכומי סף ו/או אחוזים)

ג.     חיוני ביותר בשיטות האלטרנטיביות שאני מציג.

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

הנוסחה הבאה מחשבת (בתא F2) את הבונוס עבור סכום המכירות (בתא E2).

                תמונה מס.3: הנוסחה לחשוב הבונוס בשיטת Nested IF

כפי שהסברתי לעיל, זוהי נוסחת Nested IF המתבססת על טבלת הפרמטרים (J2:K5)         

תמונה מס.4: חשוב הבונוס בשיטת Nested IF

    

יש לשים לב לעובדה שאם נחלק בונוסים לסוכנים שמכרו בסכומים גבוהים יותר, (למשל: 40% לסוכנים מכרו בסך 4000 ויותר, 50% לסוכנים שמכרו בסך 5000 ויותר וכו') - אז הנוסחה תהיה עוד יותר מסובכת וארוכה...

                                     

                                     

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

                                               

א.    שיטה א: הפונקציה INDEX

INDEXתמונה מס.5: הנוסחה לחִשוב הבונוס בעזרת 

הנוסחה מוצאת בטבלת הפרמטרים ($J$2:$K$5) את האחוז המתאים לסכום המכירות ומכפילה בסכום המכירות כדי לקבל את סכום הבונוס

הנוסחה משתמשת בפונקציה INDEX  המקבלת 3 ארגומנטים:

1. הטבלה שבה יש לחפש את הנתון המבוקש ($J$2:$K$5)

2. השורה שבה יש לחפש את הנתון המבוקש.

הבטוי INT(E3/1000) הוא מספר שלם (מעוגל כלפי מטה) של סכום המכירות מחולק ב- 1000

למשל: אם E2=1051.00, אז INT(E2/1000)=1. לכן, 1 הוא מספר השורה שבה יש לחפש את האחוז המבוקש.
לכן, במקרה שלנו, אנו מחפשים את השורה מס. 2 בטבלת הפרמטרים
אנו מוסיפים 1 לתוצאה, כדי שלא נקבל ערך שורה לא חוקי (כשהסכום נמוך מ- 1000.00)

3. העמודה שבה יש לחפש את הנתון המבוקש [ארגומנט אופציונלי]

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

את הפונקציה INDEX אנו "עוטפים" בפונקציה IFERROR שמחזירה 0

אם לא נמצא % מתאים לסכום המבוקש (כאשר סכום המכירות של הסוכן הוא 4000 או יותר.

                        תמונה מס.6: חִשוב הבונוס בעזרת INDEX

ב.    הפונקציה VLOOKUP

תמונה מס.7: הנוסחה לחִשוב הבונוס בעזרת VLOOKUP

הנוסחה משתמשת בשלוב הפונקציות : VLOOKUP, ROUNDDOWN 

הפונקציה VLOOKUP מקבלת 3 ארגומנטים:

1. הסכום שיש לחפש בטבלה. חלוקת סכום המכירות ב- 1000 ועִגול התוצאה כלפי מטה למספר שלם באמצעות הפונקציה ROUNDDOWN:
ROUNDDOWN(E2,-3). הפרמטר השני (-3)  מעגל את המספר לאלף הקרוב.

2. הטבלה שבה יש לחפש את הנתון המבוקש ($K$2:$L$5)

3. העמודה שבה יש לחפש את הנתון המבוקש: תמיד 2

4. שיטת החִפוש: מדויק (False או 0)

הפונקציה מחזירה את הערך המתאים (אחוז) שאותו מכפילים בסכום המכירות
את הVLOOKUP  אנו "עוטפים" בפונקציה IFERROR שמחזירה 0

אם לא נמצא % מתאים לסכום המבוקש (כאשר סכום המכירות של הסוכן הוא 4000 או יותר.

                                        תמונה מס.8: חִשוב הבונוס בעזרת VLOOKUP

 

לסכום, הצגנו שני פתרונות עדיפים על ה- Nested IF:
האחד, הפונקציה INDEX

השני, הפונקציה VLOOKUP

היתרונות של פתרונות אלה לעומת ה- Nested IF:

א.   לא משתנים אם נוסיף קריטריונים לבונוס

ב.    הנוסחאות יותר קצרות ויותר ברורות וקריאות

מקַוֶה שתפיקו תועלת מההסבר J J J