יום ראשון, 16 בספטמבר 2012

VLOOKUP ברבוע - "תוכנה" לנִהול הזמנת חדר במלון

VLOOKUP ברבוע  - "תוכנה" לנִהול הזמנת חדר במלון

שולי מנהלת משרד נסיעות המטפל, בין השאר, בהזמנות לבתי-מלון באילת.

היא פנתה אלי יום אחד ובקשה שאכתוב לה תוכנה המאפשרת את ניהול ההזמנות.
הבעיה שהציגה לפני היא כזו:

יש לה רשימת מלונות באילת. לכל מלון תעריף רגיל ותעריף קיץ.
אם לקוח מזמין חדר במלון בתקופה שאיננה מוגדרת כתקופת הקיץ - יש לחייב אותו בתעריף הרגיל בלבד. אך אם הלקוח מעונין להזמין חדר בתקופת הקיץ - יש להוסיף לחיוב את תעריף הקיץ.

ה"תוכנה" שפיתחתי עבורה נכתבה כולה ב"אקסל". היא מכילה 4 טבלאות דינמיות ונוסחא אחת - השואבת נתונים מתוך הטבלאות לפי 2 פרמטרים.

הנתונים לפרמטרים נלקחים ממה שמקליד הפקיד ברשימת ההזמנות (בדוגמא שלנו: A17:C21. הרשימה תלך ותגדל, כמובן,  ככל שתִוָּסֵפְנָה הזמנות).

בכל שורה ברשימה הוא מקליד שני נתונים:
1. תאריך ה- check-in (בתא: A17)
2. המלון שנבחר מתוך רשימת המלונות (בתא: B17)


רשימת הזמנות

התוצאה מחושבת בתא C17.
בדוגמא שלנו, הפקיד הקליד את התאריך: 12/07/2012 ואת שם המלון: "הילטון". המחיר המחושב ליום הוא 430 ש"ח. (הסבר על שיטת החִשּוּב - בהמשך)


א. הסבר על הטבלאות

טבלה 1: טבלת קודי תעריפי המלון


עבור כל מלון בטבלה (עמודה A) מופיעים שני קודי תעריף:
            א. קוד תעריף רגיל (עמודה B)
            ב. קוד תוספת קיץ (עמודה C)

טבלה 2: טבלת תעריפים רגילים


בטבלה זו נמצא את התעריף הרגיל לפי קוד התעריף מטבלה 1.
קוד התעריף מופיע בעמודה E והתעריף עצמו (מחיר ליום) - בעמודה F



למשל:
אם בטבלה 1 עבור מלון "הילטון" קוד התעריף הוא A - אזי התעריף הרגיל ליום במלון זה הוא: 230 ש"ח ליום (הסכום המתאים לקוד תעריף Aבטבלה 2).
אם בטבלה 1 עבור מלון "אגמים" קוד התעריף הוא H - אזי התעריף הרגיל ליום במלון זה הוא: 600 ש"ח ליום (הסכום המתאים לקוד תעריף Hבטבלה 2).

טבלה 3: טבלת תעריפי קיץ


בטבלה זו נמצא את תוספת הקיץ לפי קוד תוספת הקיץ מטבלה 1.



למשל:
אם בטבלה 1 עבור מלון "הילטון" קוד תוספת הקיץ הוא Q - אזי תוספת הקיץ ליום במלון זה היא: 200 ש"ח ליום (הסכום המתאים לקוד תעריף Qבטבלה 3).
אם בטבלה 1 עבור מלון "אגמים" קוד תוספת הקיץ הוא B - אזי תוספת הקיץ ליום במלון זה היא: 100 ש"ח ליום (הסכום המתאים לקוד תעריף Bבטבלה 3).

טבלה 4: טבלת תקופת הקיץ



טבלה זו מגדירה את תקופת הקיץ, כדי לקבוע אם יש לגבות תוספת קיץ עבור ההזמנה. 
אם תאריך ה- check-in המבוקש חל בתקופה המוגדרת כתקופת הקיץ, אזי יש להוסיף לתעריף הרגיל את תוספת תעריף הקיץ (ראה טבלה 3).
אחרת - התעריף ליום עבור ההזמנה יהיה זהה לתעריף הרגיל בלבד.

טבלה זו, בנגוד לקודמותיה, אינה משמשת בנוסחא לחפוש ערך אלא כדי לקבוע אם יש לגבות מהלקוח תוספת עבור תעריף קיץ.

ב. הסבר על הנוסחא

=VLOOKUP(VLOOKUP($B17,$A$3:$C$7,2,0),$E$3:$F$7,2,0)+VLOOKUP(VLOOKUP($B17,$A$3:$C$7,3,0),$E$11:$F$13,2,0)*AND($A17>=$A$11,$A17<=$B$11)




הנוסחא מורכבת משני חלקים:

חלק אחד מחשב את התעריף הרגיל


והחלק השני מחשב את תוספת הקיץ.


שני החלקים מחוברים בסימן (+)

ב.1. חלק א-חישוב התעריף הרגיל:
אנו משתמשים כאן פעמייםבפונקציה VLOOKUP (מה שקראתי VLOOKUP  ברבוע.. או VLOOKUP של VLOOKUP)

הפונקציה VLOOKUP היא פונקצית חִפּוּש.  היא מקבלת 4 ארגומנטים ומחזירה את תוצאת החִפּוּש. (בסוגריים: ערך הארגומנט). בחפוש הראשון,אנו מבקשים למצוא בטבלה 1 את קוד התעריף של מלון "הילטון".
ארגומנט 1. מה לחפש - המלון המבוקש (הילטון) [תא: B17]
ארגומנט 2. היכן לחפש - בטבלה מס. 1 - טבלת קודי תעריף ($A$3:$C$7)
ארגומנט 3. באיזו עמודה נמצאת התוצאה (קוד התעריף) - בעמודה B, העמודה השניה בטבלה (2)
ארגומנט 4. האם אנו רוצים תוצאה מדויקת או מקורבת - בחפוש קוד תמיד נבקש תוצאה מדויקת - (0)
ותוצאת החפוש: הערך A (בתא: B3) - שהוא קוד התעריף של מלון "הילטון".

וכעת לקטע המעניין: תוצאת החפוש של ה- VLOOKUP הראשון,

(כלומר: קוד התעריף A)  תשמש אותנו ב- VLOOKUP השני.


כעת יתבצע החפוש בטבלה 2 - טבלת תעריפים רגילים:
ארגומנט 1. מה לחפש - קוד תעריף (A) [תוצאת החפוש של ה- VLOOKUP הראשון]
ארגומנט 2. היכן לחפש - בטבלה מס. 2 - טבלת תעריפים רגילים ($E$3:$F$7)
ארגומנט 3. באיזו עמודה נמצאת התוצאה (סכום התעריף) - בעמודה F, העמודה השניה בטבלה (2)
ארגומנט 4. האם אנו רוצים תוצאה מדויקת או מקורבת - בחפוש תעריף תמיד נבקש תוצאה מדויקת - (0)

ותוצאת החפוש: הערך 230 - שהוא סכום התעריף הרגיל של מלון "הילטון".

ב.2. חלק ב-חישוב תעריף קיץ:
גם כאן נשתמש פעמייםבפונקציה VLOOKUP
ראשית, נחפש בטבלת קודי תעריפים (טבלה מס. 1) את קוד תוספת הקיץ שמתאים למלון שבחרנו
ארגומנט 1. מה לחפש - ללא שנוי: המלון המבוקש (הילטון) [תא: B17]
ארגומנט 2. היכן לחפש - ללא שנוי: התחום ($A$3:$C$7)
ארגומנט 3. באיזו עמודה נמצאת התוצאה (קוד התוספת) - בעמודה C, העמודה השלישית בטבלה (3)
ארגומנט 4. האם אנו רוצים תוצאה מדויקת או מקורבת - בחפוש קוד תמיד נבקש תוצאה מדויקת - (0)
תוצאת החפוש: הערך Q (בתא: C3) - שהוא קוד תוספת הקיץ של מלון "הילטון".

כעת יתבצע החפוש בטבלה 3 - טבלת תוספת קיץ:
1. מה לחפש - קוד תעריף (Q) [כאמור, תוצאת החפוש של ה- VLOOKUPהראשון, כמו בחלק הראשון]
2. היכן לחפש - בטבלה מס. 3 - טבלת תעריפי  קיץ ($E$11:$F$13)
3. באיזו עמודה נמצאת התוצאה (סכום התעריף) - בעמודה F, העמודה השניה בטבלה (2)
4. האם אנו רוצים תוצאה מדויקת או מקורבת - בחפוש תעריף תמיד נבקש תוצאה מדויקת - (0)
הפונקציה מחזירה את הערך 200 - שהוא סכום תוספת הקיץ של מלון "הילטון".
                                                           
כעת, כשמצאנו את תוספת הקיץ - נשאר לברר רק דבר אחד. כיצד נוכל לדעת מתי יש לחייב את הלקוח בתוספת הקיץ ומתי - אין לחייב אותו בתוספת זו?

הפתרון לכך פשוט: עלינו למצוא אם תאריך ה- check-in של הלקוח חל בתקופה המוגדרת כתקופת הקיץ (כלומר, לפי טבלה 4: האם תאריך זה חל בין 01.04.2012 ובין 30.09.2012).

הבדיקה נעשית בעזרת הפונקציה: AND. כדי שיתקיים התנאי (תאריך חל בתקופה) הוא חייב להיות לא קטן (>=) מתאריך תחילת תקופת הקיץ (בתא A11) וגם לא גדול (=>) מתאריך סיום תקופת הקיץ (בתא B11).

כפי שאפשר לראות, תוצאת בדיקה זו נותנת רק אחת משתי האפשרויות: התאריך חל בתקופה (TRUE), התאריך אינו חל בתקופה (FALSE).

ב"אקסל" TRUE ערכו הבוליאני = 1, ו- FALSE - ערכו = 0.

לכן, את הסכום שקבלנו כתוצאת תוספת הקיץ (באמצעות ה- VLOOKUP ברבוע) נכפיל בערך הבוליאני של בדיקת התאריך (1 או 0).

אם התאריך חל בקיץ, תוספת הקיץ מוכפלת ב- 1ונוספת לתעריף הרגיל.
אם התאריך אינו חל בקיץ, תוספת הקיץ מוכפלת ב- 0 והוספת 0 לסכום- לא משנה את התוצאה. הלקוח יחויב רק בתעריף הרגיל.

בדוגמא שלנו [הזמנה בשורה 17: תאריך check-in= 12/07/2012, מלון=הילטון]
1. מצאנו שתעריף רגיל של מלון הילטון הוא - 230 ש"ח.
2. מצאנו שתוספת הקיץ עבור מלון הילטון היא 200 ש"ח.
3. מכיון שתאריך ה- check-in חל בקיץ, הלקוח ישלם עבור יום במלון הילטון:
230 + 200* 1 = 430

דוגמא נוספת [ההזמנה בשורה 18: תאריך check-in= 14/03/2012, מלון=אגמים]
1. תעריף רגיל של מלון אגמים הוא - 600 ₪
2. תוספת הקיץ של מלון אגמים היא - 100 ₪.
3. מכיון שתאריך ה- check-in אינו חל בקיץ, הלקוח ישלם עבור יום במלון אגמים:
600 + 100 * 0 = 600

הטבלה הבאה מסכמת את שלבי התהליך:


♠♠♠

לסכום, הראינו כיצד אפשר להשתמש ב"אקסל" לנהול הזמנת חדרים בבתי-מלון. מובן ששיטה זו אינה יכולה להוות תחליף למערכת נהול הזמנות, אבל היא מספקת כלי פשוט וקל לבצוע בהזמנת חדרים בבתי-מלון שונים לפי תעריפים שונים ולפי פרמטרים אחרים לחיוב (בשיטה שלנו: תעריף הקיץ הנקבע לפי תאריך ה- check-in)

שיטה זו היא שיטה גמישה, כי היא מאפשרת שינויים מיידיים - הנעשים בטבלאות בלבד - מבלי לשנות את הנוסחא. לטבלת קודי תעריף אפשר להוסיף מלונות חדשים, עם תעריפים חדשים וקודים חדשים. אפשר גם לשנות את הגדרת תקופת הקיץ: את משך התקופה ו/או את תאריך ההתחלה ותאריך הסיום.

יתרון נוסף: הפקיד אינו נדרש להקליד את שם המלון בהזמנה. שמות המלונות מופיעים כרשימה נפתחת (drop-down list) באימות נתונים (Data Validation) והפקיד יכול לבחור את המלון הרצוי.

[הסבר על "אימות נתונים"- בקרוב]

                                                «««
אני מקוה שתפיקו תועלת מן המידע המופיע כאן.
             

2 תגובות:

  1. היי מני,
    לא מצאתי את המקום בו ניתן לשאול שאלות כלליות לגבי אקסל, אז אני אשאל כאן.
    האם קיימת נוסחה באקסל שמאפשרת לעגל מספרים לרמת העשיריות ?
    דוגמה:
    147.74 יעוגל ל 150
    142.74 יעוגל אף הוא ל150
    באופן שביחידות יהיה תמיד 0
    תודה

    גבי

    השבמחק
  2. היי גבי,
    התשובה פשוטה מאד:

    1) נניח שבתא C1 מופיע המספר: 147.74
    הפונקציה: ROUNDUP מעגלת את המספר כלפי מעלה.
    אם נרצה לעגל כלפי מעלה ל: 148 - נכתֹב : (ROUNDUP(C1,0= (בתא C2) והתוצאה שתתקבל תהיה: 148
    אם נרצה לעגל כלפי מעלה ל: 150 - נכתֹב : (ROUNDUP(C1,-1= (בתא C3) והתוצאה שתתקבל תהיה: 150

    2) נניח שבתא D1 מופיע המספר: 142.74
    הפונקציה: ROUNDUP מעגלת את המספר כלפי מעלה.
    אם נרצה לעגל כלפי מעלה ל: 143 - נכתֹב : (ROUNDUP(D1,0= (בתא D2) והתוצאה שתתקבל תהיה: 143
    אם נרצה לעגל כלפי מעלה ל: 150 - נכתֹב : (ROUNDUP(D1,-1= (בתא D3) והתוצאה שתתקבל תהיה: 150

    3) נניח שבתא E1 מופיע המספר: 142.11
    הפונקציה: ROUNDUP מעגלת את המספר כלפי מעלה.
    אם נרצה לעגל כלפי מעלה ל: 143 - נכתֹב : (ROUNDUP(E1,0= (בתא E2) והתוצאה שתתקבל תהיה: 143
    אם נרצה לעגל כלפי מעלה ל: 150 - נכתֹב : (ROUNDUP(E1,-1= (בתא E3) והתוצאה שתתקבל תהיה: 150

    אפשר לבצע עגול כלפי מטה (ROUNDDOWN) וגם עגול רגיל (ROUND) - אך על כך אסביר בפעם אחרת.

    שבת שלום

    השבמחק