Note: The other languages of the website are Google-translated. Back to English

在 Excel 中按月(帶或不帶年份)匯總值

本教程詳細演示了兩個公式,以幫助在 Excel 中按特定月份和年份匯總值或按特定月份匯總值忽略年份。

如何按月和年對值求和?
如何按月匯總值忽略年份?


如何按月和年對值求和?

如下面的屏幕截圖所示,要根據同年(2019 年)的 XNUMX 月計算總金額,您可以使用帶有 SUMIFS 和 EOMONTH 函數的公式。

通用公式

=SUMIFS(value_range,date_range,">="&date,date_range,"<="&EOMONTH(date,0))

參數

值_範圍:要求和的值範圍;
日期範圍:要評估月份和年份的日期範圍;
日期:代表您要基於的一個月的第一天。 它可以是單元格引用或用雙引號括起來的確切日期。

如何使用這個公式?

在這種情況下,$D$3:$D$11 是range1,China 是位於$G1 中的criteria3,$E$3:$E$11 是range2,H$2 是包含criteria2 的單元格引用。

1. 選擇一個空白單元格輸出結果。 在這種情況下,我選擇 H6。

2. 將以下公式複製或輸入到所選單元格中,然後按 Enter 鑰匙。 選擇結果單元格,向下拖動自動填充句柄以獲取其他月份和年份的總和。

=SUMIFS($E$5:$E$10,$B$5:$B$10,">="&G6,$B$5:$B$10,"<="&EOMONTH(G6,0))

備註:在 G6:G7 範圍內,我在單元格中以“m/d/yyyy”格式輸入實際日期,然後手動將日期格式化為“mmm-yyyy”以僅顯示月份和年份。 如果您將月份和年份作為文本手動輸入到單元格中,您將不會得到正確的結果。

公式說明

=SUMIFS($E$5:$E$10,$B$5:$B$10,">="&G6,$B$5:$B$10,"<="&EOMONTH(G6,0))

$E$5:$E$10,$B$5:$B$10,">="&G6: 這裡匹配日期範圍 E5:E10 中大於或等於 G6 中一個月的第一天的日期。
$B$5:$B$10,"<="&EOMONTH(G6,0):EOMONTH 函數返回 G6 中日期提供的月份最後一天的序列號。 如上所述,此處匹配小於或等於該月最後一天的日期範圍 E5:E10 中的日期。
然後 SUMIFS 函數將按月的總金額與上述兩個條件相加。

如何按月匯總值忽略年份?

使用上述公式,您只能按月對同一年的值求和。 要按月對不同年份的值求和,您可以應用基於 SUMPROFUCT 和 MONTH 函數的公式。

通用公式

=SUMPRODUCT((MONTH(date_range)=month)*value_range)

參數

日期範圍:要評估月份的日期範圍;
:它是一個數字,表示您將根據其對值求和的月份;
值_範圍:要求和的值範圍;

如何使用這個公式?

1. 選擇一個空白單元格。 這裡我選擇H6。

2. 將以下公式複製或輸入其中,然後按 Enter 鍵即可得到結果。

=SUMPRODUCT((MONTH($B$5:$B$10)=10)*$E$5:$E$10)

備註:在公式中,數字10代表十月。 數字1到12分別代表一月到十二月,您可以根據需要進行更改。

公式說明

=SUMPRODUCT((MONTH($B$5:$B$10)=10)*$E$5:$E$10)

月 ($B$5:$B$10)=10:MONTH 函數從 B5:B10:{10;10;12;12;10;10} 範圍內的每個日期單元格中提取月份數字,並將數組中的每個數字與數字 10:{10;10; 12;12;10;10}=10,最後返回一個真假數組{TRUE;TRUE;FALSE;FALSE;TRUE;TRUE}。
{TRUE; TRUE; FALSE; FALSE; TRUE; TRUE} * $ E $ 5:$ E $ 10:此數組乘以與 1428 月相關的每個金額,您將得到一個像這樣的新數組 {2010;0;0;2069;1728;XNUMX};
SUMPRODUCT{1428;2010;0;0;2069;1728}:SUMPRODUCT 函數最終將數組中的所有數字相加,返回結果為 7235。

相關功能

Excel SUMIFS功能
Excel SUMIFS 函數有助於根據多個條件對單元格值求和

Excel SUMPRODUCT函數
Excel SUMPRODUCT 函數可用於將兩個或多個列或數組相乘,然後得到乘積的總和。

Excel EOMONTH 函數
Excel EOMONTH 函數獲取在給定開始日期之前或之後特定月數的日期

Excel MONTH 函數
Excel MONTH 函數從 Excel 中的日期格式中提取月份數


相關公式

按給定日期的周對值求和
本教程討論如何按給定日期所在的星期對值求和

按weeknum 或weekday 求和值
本教程逐步演示公式,以幫助您根據 Excel 中指定的周數或工作日對特定範圍內的值進行求和。

對某個範圍內的每 n 行或每列求和
本教程介紹了基於 SUM 和 OFFSET 函數的兩個公式,用於對 Excel 中指定範圍內的每 n 行或每列求和。

對范圍內的每第 n 行或每列求和
本教程將向您展示如何創建基於 SUMPRODUCT、MOD 和 COLUMN 函數的公式,以對 Excel 中某個範圍內的每第 n 行或每列求和。


最佳辦公效率工具

Kutools for Excel-幫助您從人群中脫穎而出

您想快速,完美地完成日常工作嗎? Kutools for Excel具有300個強大的高級功能(合併工作簿,按顏色求和,拆分單元格內容,轉換日期等),並為您節省80%的時間。

  • 專為1500個工作方案而設計,可幫助您解決80%的Excel問題。
  • 每天減少數千次鍵盤和鼠標的點擊,減輕您疲倦的眼睛和手的疲勞。
  • 在3分鐘內成為Excel專家。 不再需要記住任何痛苦的公式和VBA代碼。
  • 30天無限制免費試用。 60天退款保證。 免費升級和支持2年。
Excel功能區(已安裝Kutools for Excel)

Office選項卡-在Microsoft Office(包括Excel)中啟用選項卡式閱讀和編輯

  • 一秒鐘即可在數十個打開的文檔之間切換!
  • 每天為您減少數百次鼠標單擊,告別鼠標手。
  • 查看和編輯多個文檔時,可將您的工作效率提高50%。
  • 像Chrome,Firefox和新的Internet Explorer一樣,為Office(包括Excel)帶來高效選項卡。
Excel的屏幕截圖(已安裝Office選項卡)
按評論排序
留言 (0)
還沒有評分。 成為第一位評論!
這裡還沒有評論
留下你的意見
以訪客身份發帖
×
評價此帖子:
0   字符
推薦地點