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

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

Bypassing Excel’s Nested IF
with 
IF-INDEX
or IF-VLOOKUP
                                                                                                         
לפנינו (בתאים (A2:E32 טבלת מכירות של סוכנים בחודש ינואר 2017.




בעמודה F - אנו רוצים להעניק בונוס לכל סוכן בהתאם לכמות המכירות שלו, לפי האלגוריתם הבא:
         
          מי שמכר ביותר מ-3000 ש"ח - יקבל בונוס בסך 30%
          מי שמכר ביותר מ-2000 ש"ח - יקבל בונוס בסך 20%
         מי שמכר ביותר מ-1000 ש"ח - יקבל בונוס בסך 10%
         מי שמכר בסכום שאינו עולה על 1000 ש"ח - לא יקבל כל בונוס
                                                                  
                                                                  
          פתרון מקובל בבעיות כאלה הוא השמוש ב- Nested IF או כמו שהוא קרוי בעברית  IF מקונן:
אם סכום המכירות > 3000, חשב עמלה לפי סכום מכירות*30% [K4]
אחרת,
אם סכום המכירות > 2000, חשב עמלה לפי סכום מכירות*20% [K3]
אחרת,
אם סכום המכירות > 1000, חשב עמלה לפי סכום מכירות*10% [K2]
אחרת,
אל תעניק בונוס, עמלה = 0





הנוסחה הבאה מחשבת (בתא F3) את סכום העמלה עבור כמות המכירות (בתא E3).




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



לאחר ההקלדה (בתא F3), נגרור את הנוסחה כלפי מטה על-ידי קליק כפול בפינה הימנית התחתונה של התא והנוסחה תועתק לכל התאים בעמודה F הסמוכים לטבלה המקורית (F2:F32)




והתוצאה:




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

                                     


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

הפתרון הקצר והאלגנטי:
במקום Nested IF אפשר להשתמש בשלוב של IF ו- INDEX
או: שלוב IF ו- VLOOKUP
א.   שלוב של IF ו- INDEX




הנוסחה אומרת: האם סכום המכירות המחולק ב-1000 > 1?
אם כן, מצא בטבלת העזר ($K$2:$L$4) את האחוז המתאים לסכום המכירות
והכפל אותו בסכום המכירות
הנוסחה משתמשת בשלוב הפונקציות : INDEX, IF 
הפונקציה INDEX מקבלת 3 ארגומנטים:
1. הטבלה שבה יש לחפש את הנתון המבוקש ($K$2:$L$4)
2. השורה שבה יש לחפש את הנתון המבוקש.
הבטוי INT(E3/1000) הוא מספר שלם (מעוגל כלפי מטה) של סכום המכירות מחולק ב- 1000
למשל: אם E3=1051.00, אז INT(E3/1000)=1. לכן, 1 הוא מספר השורה שבה יש לחפש את האחוז המבוקש.
3. העמודה שבה יש לחפש את הנתון המבוקש: תמיד 2
הפונקציה מחזירה את הערך המתאים (אחוז) שאותו מכפילים בסכום המכירות





ב.    שלוב של IF ו- VLOOKUP



הנוסחה אומרת: האם סכום המכירות המחולק ב-1000 > 1?
אם כן, מצא בטבלת העזר ($K$2:$L$4) את האחוז המתאים לסכום המכירות
והכפל אותו בסכום המכירות
הנוסחה משתמשת בשלוב הפונקציות : VLOOKUP, IF 
הפונקציה VLOOKUP מקבלת 3 ארגומנטים:
1. הסכום שיש לחפש בטבלה. הסכום (באלפים) מעוגל לאלף הקרוב (כלפי מטה): חלוקת סכום המכירות ב- 1000 ועִגול התוצאה כלפי מטה למספר שלם באמצעות הפונקציה INT:
INT(E3/1000)*1000
2. הטבלה שבה יש לחפש את הנתון המבוקש ($K$2:$L$4)
3. העמודה שבה יש לחפש את הנתון המבוקש: תמיד 2
הפונקציה מחזירה את הערך המתאים (אחוז) שאותו מכפילים בסכום המכירות







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



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








תגובה 1:

  1. שלום,
    לא הצלחתי להבין על מה ולמה יש לסרבל את הנוסחה ע"י חילוק ומכפלה ב-1000 כשניתן בקלות להוסיף עוד נדבך לטבלת העזר בעבור מכירות קטנות מ- 1000.
    הצעתי מוצגת בתמונה שבקישור.
    תודה.
    https://postimg.org/image/wthaq2t7t/

    השבמחק