שעות חופפות

[פובי 2018]

שעות חופפות

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

 

[iyyi]

כמה הערות

ראשית צריך להגדיר מה הן שעות חופפות. יש שתי אפשרויות:

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

• ההתחלה של התקופה קטנה או שווה להתחלה של התקופות הנבדקות, וגם הסיום של התקופה גדול מההתחלה של התקופות הנבדקות.
• ההתחלה של התקופה קטנה מהסיום של התקופות הנבדקות וגם הסיום של התקופה גדול או שווה לתקופות הנבדקות.

יכול להיות שהדבר הוגדר בשאלה המקורית אבל לי אין מידע על כך.

שנית, לדעתי כדאי ליצור שתי עמודות חדשות - אחת המחברת את התאריך עם שעת ההתחלה, והשניה - מחברת את התאריך עם שעת הסיום.
יש לכך שני יתרונות מרכזיים:

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

בקובץ המצורף מודגמות הנוסחאות לחפיפה מלאה ולחפיפה חלקית.

 

[פובי 2018]

תודה רבה על התגובה וההסבר המפורט

 

[פובי 2018]

שעות חופפות עם תנאים

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

 

[iyyi]

אם את רוצה להוסיף תנאים את צריכה לשלב אותם בנוסחת ...

ה-SUMPRODUCT.
חשוב לשים לב לדברים הבאים:

1. חפיפה חלקית כוללת בתוכה את המצבים של חפיפה מלאה. לכן אם את מעוניינת בחפיפה חלקית אפשר להסתפק רק בנוסחה שלה.
2. אם משנים את המיקום של עמודות השעות צריך להתייחס לכך בנוסחה. שעת ההתחלה עברה לעמודה C ולכן הבדיקות לגבי שעת התחלה צריכות להתייחס לעמודה זאת. כנ"ל לגבי שעת הסיום.
3. התנאים של ת.ז והיום בשבוע תופסים לגבי שתי הבדיקות של החפיפה החלקית ולכן צריך להוסיף בנוסחה סוגריים בהכפלה של החיבור של שני המרכיבים.

 

[פובי 2018]

תודה iyyi, יבורכו ידיך ושכלך