יום ראשון, 11 ביוני 2017

איך עוקפים 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








תגובה 1: