הבלוג של מני: איך עוקפים 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

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