Bypassing Excel’s Nested IF
with IF-INDEX or IF-VLOOKUP
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,
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
INT(E3/1000)*1000
2. הטבלה שבה יש לחפש את הנתון המבוקש ($K$2:$L$4)
3. העמודה שבה יש לחפש את הנתון המבוקש:
תמיד 2
הפונקציה מחזירה את הערך המתאים (אחוז)
שאותו מכפילים בסכום המכירות
לסכום, הצגנו שני פתרונות עדיפים על ה- Nested IF:
האחד, שלוב של IF ו- INDEX
האחד, שלוב של IF ו- INDEX
השני, שלוב של IF ו-
VLOOKUP
היתרונות של פתרונות אלה לעומת ה- Nested IF:
א.
לא
משתנים אם נוסיף קריטריונים לבונוס
ב.
הנוסחאות
יותר קצרות ויותר ברורות וקריאות
מקַוֶה שתפיקו תועלת מההסבר J J J
תגובה זו הוסרה על ידי מנהל הבלוג.
השבמחק