ספור את יום השבוע בין התאריכים

כדי לספור ימי חול (שני, שישי, ראשון וכו ') בין שני תאריכים ניתן להשתמש בנוסחת מערך המשתמשת במספר פונקציות: SUMPRODUCT, WEEKDAY, ROW ו- INDIRECT. בדוגמה המוצגת, הנוסחה בתא E6 היא

= SUMPRODUCT (--( WEEKDAY ( ROW ( INDIRECT (start&':'&end)))=dow))

בגרסה הגנרית של הנוסחה, הַתחָלָה = תאריך התחלה, סוֹף = תאריך סיום, ו דאו = יום בשבוע.

להצטיין אם הצהרה עם תנאים מרובים

בבסיס, נוסחה זו משתמשת ב- פונקציית WEEKDAY כדי לבדוק מספר תאריכים כדי לראות אם הם נוחתים ביום נתון בשבוע (dow) ואת פונקציית SUMPRODUCT לסכם את הסכום הכולל.

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

הטריק לנוסחה זו הוא להבין זאת תאריכים ב- Excel הם רק מספרים סידוריים שמתחילים ב -1 בינואר 1900. לדוגמה, 1 בינואר 2016 הוא המספר הסידורי 42370, וה -8 בינואר הוא 42377. תאריכים ב- Excel נראים רק כמו תאריכים כאשר מוחל פורמט מספר תאריכים.

תאריך הנוסחה להצטיין ליום בשבוע

אז נשאלת השאלה - איך אתה יכול לבנות מַעֲרָך תאריכים שתוכל להזין בפונקציית WEEKDAY כדי לברר את ימי השבוע המתאימים?

התשובה היא להשתמש שׁוּרָה עם עקיף מתפקד כך:

 
= SUMPRODUCT (--( WEEKDAY ( ROW ( INDIRECT (B6&':'&C6)))=D6))

INDIRECT מאפשרת לפרש את התאריכים המחוברים '42370: 42377' כמספרי שורות. ואז הפונקציה ROW מחזירה מערך כזה:

 
 ROW ( INDIRECT (date1&':'&date2))

הפונקציה WEEKDAY מעריכה מספרים אלה כתאריכים ומחזיר מערך זה:

 
{4237042371423724237342374423754237642377}

אשר נבדק מול היום בשבוע הנתון (6 במקרה זה, מ- D6). לאחר המרת תוצאות הבדיקה ל- 1s ו- 0s עם המקף הכפול, מערך זה מעובד על ידי SUMPRODUCT:

 
{67123456}

מה שמחזיר 2.

עם רצף

עם החדש פונקציית SEQUENCE , נוסחה זו יכולה להתפשט באופן כזה:

כיצד לעשות מקדם מתאם ב- Excel

 
{10000001}

בגרסה זו אנו משתמשים ב- SEQUENCE ליצירת מערך התאריכים ישירות, ללא צורך ב- INDIRECT או ROW.