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