סיכום ערכים לפי שנה ב-Excel

נניח שיש לך טווח נתונים שמכיל רשימת תאריכים וסכומי מכירות, וכעת ברצונך לקבל את הסכום הכולל על בסיס שנים נתונות כפי שמוצג בתמונה למטה. ב-Excel, הפונקציות הרגילות SUMIFS ו-SUMPRODUCT יכולות לעזור לפתור משימה זו במהירות ובקלות. מדריך זה יציג כיצד להשתמש בשתי פונקציות אלו בצורה מפורטת.

• סיכום ערכים לפי שנה ב-Excel עם פונקציות SUMIFS ו-DATE
• סיכום ערכים לפי שנה ב-Excel עם פונקציית SUMPRODUCT

סיכום ערכים לפי שנה ב-Excel עם פונקציות SUMIFS ו-DATE

כדי לסכם את הערכים הכוללים על בסיס שנה נתונה, עליך לציין את התאריך הראשון והאחרון של השנה באמצעות פונקציית DATE, ולאחר מכן להשתמש בפונקציית SUMIFS כדי לסכם את כל הערכים בין התאריכים שצוינו. הנוסחה הכללית היא:

• sum_range: טווח הערכים שצריך לסכם;
• date_range: טווח התאריכים שצריך לבדוק עבור השנה המסוימת;
• year: השנה המסוימת שברצונך לסכם ערכים על בסיסה. (year, 1,1 מציין את היום הראשון בשנה, ו year,12,31 מציין את היום האחרון בשנה.)

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

הסבר לנוסחה זו:

=SUMIFS($C$2:$C$12,$B$2:$B$12,">="&DATE(E2,1,1),$B$2:$B$12,"<="&DATE(E2,12,31))

• $C$2:$C$12: זהו "sum_range" שמכיל את הערכים שאנו רוצים לסכם.
• $B$2:$B$12,">="&DATE(E2,1,1): בודק את התאריכים בטווח B2:B12 שהם גדולים או שווים ל-01/01/2020 – היום הראשון בשנה בתא E2.
• $B$2:$B$12,"<="&DATE(E2,12,31): בודק את התאריכים בטווח B2:B12 שהם קטנים או שווים ל-31/12/2020 – היום האחרון בשנה בתא E2.
• SUMIFS($C$2:$C$12,$B$2:$B$12,">="&DATE(E2,1,1),$B$2:$B$12,"<="&DATE(E2,12,31)): לאחר עמידה בתנאים, הערכים בטווח C2:C12 יימצאו ויסוכמו.

סיכום ערכים לפי שנה ב-Excel עם פונקציית SUMPRODUCT

כאן, נוסחה פשוטה נוספת שנוצרה על ידי פונקציית SUMPRODUCT יכולה גם לעזור לך, הנוסחה הכללית היא:

• date_range: טווח התאריכים שצריך לבדוק עבור השנה המסוימת;
• year: השנה המסוימת שברצונך לסכם ערכים על בסיסה;
• sum_range: טווח הערכים שצריך לסכם.

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

הסבר לנוסחה זו:

=SUMPRODUCT((YEAR($B$2:$B$12)=E2)*$C$2:$C$12)

• YEAR($B$2:$B$12)=E2: פונקציית YEAR מחזירה את מספר השנה מכל תא תאריך בטווח B2:B12. הביטוי הזה בודק אם השנה ב-E2 שווה לשנה בטווח B2:B12. תקבל מערך כזה: {TRUE;FALSE;TRUE;TRUE;FALSE;TRUE;TRUE;FALSE;FALSE;TRUE;FALSE}.
• (YEAR($B$2:$B$12)=E2)*$C$2:$C$12={TRUE;FALSE;TRUE;TRUE;FALSE;TRUE;TRUE;FALSE;FALSE;TRUE;FALSE}*$C$2:$C$12: בביטוי זה, ה-TRUEs וה-FALSEs יופרדו ל-1s ו-0s, והמערך הזה יוכפל בכל סכום בטווח C2:C12, ותקבל מערך חדש כזה: {347;0;398;430;0;379;412;0;0;420;0}.
• SUMPRODUCT((YEAR($B$2:$B$12)=E2)*$C$2:$C$12)= SUMPRODUCT({347;0;398;430;0;379;412;0;0;420;0}): פונקציית SUMPRODUCT סופסוף מסכמת את כל המספרים במערך זה ומחזירה את התוצאה כ-2386.

הפונקציה הקשורה ששימשה:

מאמרים נוספים: