יום שבת, 17 ביוני 2017

איך עוקפים NESTED IF באקסל - בעזרת CHOOSE



Bypassing Excel’s Nested IF with the
CHOOSE Function

                                                                                                         

השנה מתקרבת לסיומה ואנו רוצים לתגמל את העובדים שלנו בבונוס מתאים:
העובדים מתוגמלים לפי דרוג משרה (מ-1 עד 5)



בעמודה C - אנו רוצים למצוא את הבונוס המתאים לעובד, לפי דרוג המשרה, כדלקמן:
         
          דרוג 1 - לא זכאי לבונוס
          דרוג 2 - זכאי לבונוס בסך 100 ש"ח
         דרוג 3 - זכאי לבונוס בסך 500 ש"ח
         דרוג 4 - זכאי לבונוס בסך 1000 ש"ח
          דרוג 5 - זכאי לבונוס בסך 1000 ש"ח
                                                                  
          פתרון מקובל בבעיות כאלה הוא השמוש ב- Nested IF או כמו שהוא קרוי בעברית  IF מקונן:


הנוסחה (בתא C2) "שולפת"  מטבלת הפרמטרים (E3:F7) את סכום הבונוס המתאים לדרוג המשרה (בתא B2):
         


לאחר ההקלדה (בתא C2), נקליק קליק כפול בפינה הימנית התחתונה
של התא:

והתוצאה:
הנוסחה תועתק לכל התאים הרלבנטיים בעמודה
C - (C3:C24)



                         
            


פתרון אלטרנטיבי:

פתרון קצר יותר אפשרי על-ידי שמוש בפונקציה: CHOOSE





הפונקציה בוחנת את המספר בתא B2 ]הארגומנט הראשון] ולפי ערכו (קוד הדרוג שהוא תמיד מספר שלם וחיובי) – מחזירה את התוצאה מתוך טבלת הפרמטרים.

יש לציין ש:
א. שיטה זו טובה רק למספר מצומצם של ערכים. מספר גדול מאד של ערכים (קודי דרוג בדוגמה שלנו) הופך את הנוסחה לארוכה ולמסורבלת.
ב. המספר שאותו מחפשים חייב, כאמור, להיות שלם וחיובי. אם קיים בחברה קוד דרוג 0 ׁ(או קוד דרוג שלילי) - הנוסחה תחזיר שגיאה: #VALUE!
ג. מספר הערכים לאחר הארגומנט הראשון חייב להתאים לערכים האפשריים בארגומנט זה.
במקרה שלנו: מכיון שקודי הדרוג האפשריים הם בתחום 1-5, יהיו בנוסחה (לאחר הארגומנט הראשון) 5 ערכים, כל אחד בהתאמה לקוד הדרוג:
אם B2 = 1, הנוסחה תחזיר את הערך הראשון (F3=0)
אם B2 = 2, הנוסחה תחזיר את הערך השני (F4=100)
אם B2 = 3, הנוסחה תחזיר את הערך השלישי (F5=500)
וכו'






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

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




יום ראשון, 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