תוצאה שלילית בחשוב הפרש שעות ב"אקסל"

חִשּוּב הפרש שלילי של שעות ב"אקסל"- אפשרי או לא?

לפעמים נרצה לחשב הפרש בין שעות.

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

למשל: השעה 23:00 - מיוצגת כ- 0.958333 (=23/24), השעה 15:00 (3:00אחה"צ) מיוצגת כ- 0.625 (= 15/24) וכדומה.

לכן, כדי לחשב את ההפרש בין השעה בתא: B3(15:00) ובין השעה בתא: B2 (23:00) -

נחסיר את [B3] מ [B2] ונקבל התוצאה ב [B4] 8:00 
(0.958333-0.625000=0.333333). 8 שעות הן בדיוק שליש יממה (8/24=1/3 )

ל"אקסל" אין שום בעיה גם בחישוב הפרשים של שעות שמספרן גדול ממספר השעות ביממה, כי אז הוא מאחסן את המספר כשבר הגדול מ- 1.

למשל: השעה 29:00 - מיוצגת כ- 1.208333 (=29/24)

לכן, כדי לחשב את ההפרש בין השעה בתא: B3(15:00) ובין השעה בתא: B2 (29:00)

- נחסיר את [B3] מ [B2] ונקבל התוצאה ב [B4] 14:00 
(1.208333-0.625000=0.583333)

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

(כאשר שעת הסיום קטנה משעת ההתחלה, כי שעת הסיום ביום שלמחרת)

למשל: להחסיר 23:00 מ- 15:00?
(שעת ההתחלה: 23:00 ושעת הסיום 15:00 למחרת)

כאן אנו נתקלים בבעיה: "אקסל" "מסרב" לחשב מספר שלילי של שעות. LLL

הוא מציג שעות שליליות כ- ######

ובכן, האם יש פתרון לבעיה?

לשמחתנו, ישנם לפחות שלשה פתרונות. JJJ

פתרון מס. 1 - שנוי בהגדרות אקסל

הפתרון מתבסס על שנוי הגדרות במנגנון הקונפיגורציה: "אפשרויות אקסל".

"אקסל" משתמש (כברירת מחדל) במערכת תאריכים החל מ-1 בינואר שנת 1900.

אבל "אקסל" מאפשר שמוש במערכת אלטרנטיבית המשתמשת בתאריכים החל משנת 1904. שמוש במערכת זו, מאפשר חִשּוּב מספר שלילי של שעות.

כיצד נבצע את השינוי כדי לאפשר חישוב מספר שעות שלילי?

לשם כך, עליך לבצע את הפעולות הבאות:

             1. בחר בכפתור "לחצן אופיס"

             

2. בחר ב"אפשרויות אקסל"

3. במסך שנפתח: "אפשרויות אקסל" בחר ב: "מתקדם"

4. בקבוצה: "בעת חישוב חוברת עבודה זו", סמן את התיבה: "השתמש במערכת תאריכים של 1904"

5. לחץ על כפתור "אישור" לסיום

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

למשל:

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

ראה הסבר בקישור הבא:

http://support.microsoft.com/kb/214330

פתרון מס. 2 - הצגת ההפרש כטכסט

הנוסחא:

=IFERROR(TEXT(G1-G2,J1),TEXT(1- (G1-G2),J1))

 

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

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

"אקסל" מחשב את ההפרש, הופך את התוצאה לטכסט ועורך את הטכסט בפורמט המוגדר בתא J1 : ("H::MM").

בחלק הסגול בנוסחא – ההפרש הוא שלילי: שעת הסיום קטנה משעת ההתחלה – תאריך הסיום חל ביום שלמחרת.

"אקסל" מחשב את ההפרש, מחסר את ההפרש (שהוא שבר) מ: 1 (כזכור 1 מייצג את השלם: היממה כולה), הופך את התוצאה לטכסט ועורך את הטכסט בפורמט המוגדר בתא
J1 : ("H::MM").

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

פתרון מס. 3 - פתרון חישובי

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

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

הפתרון: נשתמש בפונקציה: MOD

נניח ששעת ההתחלה (בתא [B3]) היא: 23:00 ושעת הסיום (בתא [B2]) היא: 15:00

הנוסחא תהיה:

                   =MOD(B2-B3,1)

והתוצאה: 16:00

שעת סיום קטנה משעת התחלה: התחלנו לפני חצות וסיימנו אחרי חצות

הפונקציה: MOD מחשבת את השארית בחלוקת מספר (מונה,  n=number) במספר אחר (מכנה, d=denominator).

הנוסחא: MOD(n,d) נִתֶנֶת לבִטוי באמצעות הנוסחא: n - d*INT(n/d)

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

אם המספר הוא שבר שלילי - הוא מעוגל למספר -1

אם המספר הוא שבר חיובי - הוא מעוגל למספר 0

בדוגמא שלנו, n הוא ההפרש בין שעת הסיום ושעת ההתחלה:

 n=B2-B3=15/24-23/24=-8/24=-0.3333,  כלומר: n=-0.3333

ואם - d=1, אז התוצאה תהיה:

-0.3333 - 1*INT(-0.3333/1) = -0.3333 -1*(-1) = 0.3333 + 1 = 0.6667

כלומר: 0.6667 = 2/3 =16/24) 16/24) = 16 שעות

והכלל: כאשר ההפרש בין שתי שעות הוא שבר שלילי - n 

אזי התוצאה (של הפונקציה MOD) היא שבר חיובי, 

אשר אם נחבר אותו לערך המוחלט של n - נקבל 1.

לדוגמא:

אם n = -1/3 , תוצאת הפונקציה MOD(n, 1) תהיה 2/3 (1 - |1/3| = 2/3)

אם n = -1/4, תוצאת הפונקציה MOD(n, 1) תהיה 3/4 (3/41 - |1/4| = )

וכו'.

ומה קורה כאשר שעת הסיום גבוהה משעת ההתחלה?

שעת סיום גדולה משעת התחלה: התחלנו וסיימנו לפני חצות 

בדוגמא שלנו, n הוא ההפרש בין שעת הסיום ושעת ההתחלה:

 n=B2-B3=23/24-15/24=8/24=0.3333,  כלומר: n= 0.3333

ואם - d=1, אז התוצאה תהיה:

0.3333 - 1*INT(0.3333/1) = 0.3333 -1*(0) = 0.3333 - 0 = 0.3333

כלומר: 0.3333 = 1/3 =8/24) 8/24)= 8 שעות

והכלל: כאשר ההפרש בין שתי השעות יהיה שבר חיובי - n 

אזי תוצאת הפונקציה  MOD תהיה n, כלומר: זהה להפרש השעות

♠♠♠

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

הצגנו גם שלוש דרכים לפתרון הבעיה:

א. באמצעות מערכת תאריכים חלופית (מובנית ב"אקסל")

ב. באמצעות נוסחא.

ג. הראינו כי הדרך "הנכונה" לחשוב הפרשי שעות ב"אקסל" היא באמצעות הפונקציה: MODאשר תמיד מחזירה ערך חיובי (=שבר חיובי) להפרש בין שתי שעות, גם אם שני המועדים אינם חלים באותה יממה.

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

הערות, הצעות לשִפּוּר, בקשות .... תתקבלנה בברכה CCC