
כדי לספור ימי חול (שני, שישי, ראשון וכו ') בין שני תאריכים ניתן להשתמש בנוסחת מערך המשתמשת במספר פונקציות: 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.
נוסחאות מערך דינמי זמינים ב- משרד 365 רק. הסופר דייב בראנס