יום שבת, 10 באוגוסט 2013

5 שיטות למציאת הרבעון לפי תאריך

5 שיטות למציאת הרבעון לפי תאריך

השנה, כידוע, מחולקת ל-12 חודשים. לצרכים פיננסיים ואחרים, מקובל לחלק את השנה לרבעונים. כל חודש שייך לרבעון, למשל: חודש פברואר (02) שייך לרבעון הראשון, חודש אפריל (04) שייך לרבעון השני, חודש ספטמבר (09) שייך לרבעון השלישי וחודש דצמבר (12) שייך לרבעון הרביעי והאחרון של השנה.

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

נניח שבתא C2 מופיע התאריך: 21/07/2013 
כיצד נאמר ל"אקסל" למצוא את הרבעון שאליו משתייך התאריך?

לשם כך, נציג יותר מ- 5 שיטות המשתמשות ביותר מ-5 פונקציות שונות.

שיטה מס. 1: שמוש בפונקציה CHOOSE

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

הפונקציה CHOOSE - בוחרת ערך מתוך טבלת ערכים [ארגומנט 2] לפי מספר מבוקש [ארגומנט 1]
הפונקציה מקבלת 2 ארגומנטים: 
ארגומנט 1: Index Num - מספר שלם בין 1 ו-254.
ארגומנט 2 :   Value1, Value2, Value3…- רשימת ערכים (לכל היותר 254 ערכים), המייצגים ערך המקביל לארגומנט 1.

נשמע מסובך, אבל זה די פשוט:
לדוגמא: נניח שיש לנו מספר כלשהו (בין 1 ל-7) ואנו רוצים לבטא את היום בשבוע שהמספר הזה מייצג. כלומר: 1 - יום ראשון, 2 - יום שני, 3 - יום שלישי וכו'.
אז הנוסחא שלנו תהיה:
                        תמונה 2: הפונקציה CHOOSE למציאת שם היום בשבוע (1)

מכיון שערך ארגומנט מס. 1 הוא: 2, נקבל את הערך השניבארגומנט מס. 2, כלומר: Monday

תמונה 3: הפונקציה CHOOSE למציאת שם היום בשבוע (2)

וכעת, לבעיה שלנו: רצינו, כזכור, למצוא את הרבעון שאליו משתייך התאריך: 21/07/2013.
הנוסחא שלנו (המשתמשת בפונקציה: CHOOSE), מורכבת מ:
הארגומנט הראשון: MONTH(C2)
MONTH(C2)  - נוסחא המחשבת את החודש מתוך התאריך. תוצאת הנוסחא היא: 7.
הארגומנט השני: טבלה של 12 ערכים (המקבילה ל-12 חודשי השנה) אשר קובעת לאיזה רבעון שייך כל חודש:


תמונה 4: טבלת הרבעונים לפי חודשים


במקרה שלנו, חודש יולי (7) הוא במקום השביעי בטבלת הערכים. במקום זה בטבלה נמצא הערך: 3, לכן חודש יולי משתייך לרבעון השלישי בשנה.

                                    תמונה 5: החודש השביעי שייך לרבעון השלישי

שיטה מס. 2: שמוש בפונקציה CEILING

                        תמונה 6: הפונקציה CEILING למציאת הרבעון

הפונקציה: CEILING - מעגלת מספר כלפי מעלה
היא מקבלת שני ארגומנטים:

ארגומנט 1: המספר שיש לעגל
ארגומנט 2: המכפלה שאליה רוצים לעגל.

הסבר:
הארגומנט הראשון הוא תוצאת הנוסחא: MONTH(C2)/3
כלומר, אנו מחלקים ב-3 את מספר החודש (7) והתוצאה:2.333333
הארגומנט השני הוא 1: אנו מבקשים מ"אקסל" שיעגל את התוצאה של ארגומנט 1 (2.33333) למכפלה הקרובה ביותר של המספר 1 - כלומר,למספר 3.
הפונקציה: FLOOR דומה מאד לפונקציה CEILING - אבל בעוד ש: CEILING מעגלת כלפי מעלה (כפי שמרמז שמה), FLOOR מעגלת כלפי מטה ואם ניתן לה את אותם ערכים בארגומנטים - התוצאה תהיה 2.

שיטה מס. 3: שמוש בפונקציה ROUNDUP

                        תמונה 7: הפונקציה ROUNDUP למציאת הרבעון


הפונקציה: ROUNDUP, בדומה ל: CEILING, מעגלת מספר כלפי מעלה.היא מקבלת שני ארגומנטים:
ארגומנט 1: המספר שיש לעגל
ארגומנט 2: מספר הספרות העשרוניות שרוצים לעגל.
הסבר:
הארגומנט הראשון הוא תוצאת הנוסחא: MONTH(C2)/3
כלומר, אנו מחלקים ב-3 את מספר החודש (7) והתוצאה:2.333333
הארגומנט השני הוא 0: אנו מבקשים מ"אקסל" שיעגל את התוצאה של ארגומנט 1 (2.33333) כלפי מעלה, למספר בעל 0 ספרות עשרוניות - כלומר,למספר 3.

כשם שהפונקציה FLOOR היא "אחותה התאומה" של הפונקציה CEILING, כך אפשר לומר שהפונקציה ROUNDDOWN  היא "אחותה התאומה" של ROUNDUP  



שיטה מס. 4: שמוש בפונקציה INT

                        תמונה 8: הפונקציה INT למציאת הרבעון

הפונקציה INT מקבלת מספר ומעגלת אותו כלפי מטה למספר השלם הקרוב ביותר
הפונקציה מקבלת רק ארגומנט 1 : המספר שיש לעגל    
הסבר על הנוסחא:
=INT((MONTH(C2)-1)/3)+1
למעשה, יש כאן חמשה שלבים:

1.      מוצאים את  החודש MONTH(C2) - והתוצאה: 7
2.      מחסירים 1 ומקבלים: 7-1=6        
3.      מחלקים ב- 3 והתוצאה: 6/3= 2
4.      מהפונקציה INT מקבלים את המספר השלם הקרוב ביותר: 2
5.      מוסיפים 1 והתוצאה הסופית: 3  -  כלומר הרבעון השלישי
נשתמש ב-3 החודשים הראשונים, כדי להסביר את שלבי הנוסחא:
שלושת החודשים הראשונים (ינואר, פברואר, מרץ) מספרם הוא: 1, 2, 3
1.      אם נחסיר 1 – נקבל את המספרים: 0, 1, 2
2.      תוצאת חילוק המספרים: 0, 1, 2 ב- 3 היא: 0,  0.333, 0.666
3.      הפונקציה INT משלימה למספר השלם הקרוב ביותר (כלפי מטה), ולכן נקבל: 0, 0, 0
4.      כעת נוסיף 1 ונקבל: 1, 1, 1 - כלומר: שלושת החודשים הראשונים שייכים לרבעון הראשון.


שיטה מס. 5: שמוש בפונקציות MROUND ו- CEILING

                      תמונה 9: הפונקציות MROUND ו-CEILING  למציאת הרבעון

שיטה זו משלבת שתי פונקציות: CEILING  ו- MROUND 
בפונקציה CEILING כבר השתמשנו בשיטה מס. 2. שם עִגַּלנו את המספר כלפי מעלה, למספר השלם הקרוב ביותר, כאשר הארגומנט השני היה: 1 - בקשנו מ"אקסל" לעגל את המספר כלפי מעלה למכפלה הקרובה ביותר של המספר 1.
כעת, נבקש מ"אקסל" לעגל למכפלה הקרובה ביותר של 0.5.
אם תוצאת הנוסחא MONTH(C2)/3 היא: 7/3 = 2.3333,
אזי "אקסל" יעגל את התוצאה ל: 2.50


הפונקציה MROUND(שגם היא משמשת לעִגּוּל מספרים) מקבלת 2 ארגומנטים:
ארגומנט 1: המספר שרוצים לעגל - 2.50 (תוצאת הפונקציה CEILING)
ארגומנט 2: המכפלה שאליה רוצים לעגל. אנו מבקשים מ"אקסל" שיעגל את התוצאה של ארגומנט 1 (2.50) למכפלה הקרובה ביותר של המספר 1 - כלומר,למספר 3.
יש לשים לב שהפונקציה איננה מעגלת כלפי מעלה, אם השבר קטן מ- 0.50 (!). לכן, השתמשנו בפונקציה CEILINGכדי להפוך את: 2.333 (7/3) למספר: 2.50 ובפונקציה: MROUND כדי לעגל את 2.50 ל- 3.

                                                        ☼☼☼

לשיטה זו, שבה אנו משתמשים ב-2 פונקציות עגול, יש כמובן וריאציות נוספות, המניבות תוצאות זהות.
למשל:
שלוב של הפונקציות: ROUNDו- CEILING


תמונה 10: שלוב הפונקציות: ROUND ו-CEILING

שלוב של הפונקציות: ROUNDו- ROUNDUP


תמונה 11: שלוב הפונקציות: ROUND ו-ROUNDUP

ואפילו שלוב של הפונקציות "התאומות": ROUNDUP ו- ROUNDDOWN


תמונה 12: שלוב הפונקציות: ROUNDUP ו-ROUNDDOWN


לסכום, הדגמנו כאן מספר שיטות למציאת הרבעון בשנה לפי חודש. השתמשנו במגוון גדול של פונקציות ב"אקסל" בלווי הסברים.
מקוה שתדעו לנצל את הידע לשמושכם...


♣♣♣

תגובה 1:

  1. ניתן לקצר, במעט, את נוסחת ה-INT.
    INT((MONTH(C2)+2)/3)=
    ______________
    מיכאל (מיקי) אבידן
    MVP - Microsoft® Most Valuable Professional (2009-2018)

    השבמחק