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

在 Excel 中按週數或工作日求和值

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

如何在Excel中按週數求和值?
如何在Excel中按工作日求和值?


如何在Excel中按週數求和值?

如下面的屏幕截圖所示,為了根據給定週數所屬的日期對 Amount 列中的值求和,我們提供了兩個公式。

通用公式

=SUMIFS( value_range, helper_column_range, week_number)

=SUMPRODUCT(--(WEEKNUM(date_range+0,1)=week_number),value_range)

參數

值_範圍:要根據周數求和的值範圍;
Helper_column_range:從日期中提取的周數範圍;
週數:用作求和值標準的周數。

如何使用這個公式?

使用 SUMIFS 公式和輔助列

要使用 SUMIFS 公式,首先,您需要應用 週數 函數創建一個輔助列來計算 Date 列中日期的相應週數。

=WEEKNUM (serial_num, [return_type])

WEEKNUM 函數返回給定日期在一年中的周數。

1. 在本例中,我選擇金額列中第一個值旁邊的單元格 (F5)。

2.輸入以下公式,然後按 Enter 獲取第一個日期的周數的鍵。 選擇結果單元格,然後向下拖動其自動填充句柄以獲取其他週數。 看截圖:

=WEEKNUM(C5)

3. 選擇要輸出總金額的單元格,在其中輸入以下公式,然後按 Enter 鑰匙。 選擇此結果單元格,向下拖動其自動填充句柄以獲取其他結果。

=SUMIFS($E$5:$E$14, $F$5:$F$14, H6)

使用 SUMPRODUCT 公式

使用 SUMPROFUCT 公式解決此問題時,無需創建輔助列。

1.選擇一個空白單元格,在其中輸入以下公式,然後按 Enter 鍵。

=SUMPRODUCT(--(WEEKNUM($C$5:$C$14+0,1)=G6),$E$5:$E$14)

2. 選擇結果單元格,拖動它 自動填充句柄 下來根據您的需要獲得其他結果。

公式說明

=SUMIFS($E$5:$E$14, $F$5:$F$14, H6)

使用 WEEKNUM 函數計算週數後,如果輔助列 F5:F14 中的周數與 H5 中的給定週匹配,則 SUMIFS 函數對范圍 E14:E6 中的值求和。

=SUMPRODUCT(--(WEEKNUM($C$5:$C$14+0,1)=G6),$E$5:$E$14)

週數($C$5:$C$14+0,1): WEEKNUM 函數計算 C5:C14 範圍內日期的每個星期數,並返回這樣的數組:{1;1;5;6;6;9;11;11;13;14};
{1;1;5;6;6;9;11;11;13;14}=G6: 這裡將數組中的每個週數與 G6 中給定的周數進行比較,並返回一個新數組:{TRUE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE};
--{真;真;假;假;假;假;假;假;假;假}:這兩個減號將“TRUE”和“FALSE”轉換為 1 和 0。 在這裡你會得到一個新數組 {1;1;0;0;0;0;0;0;0;0};
{1;1;0;0;0;0;0;0;0;0},{1428;2010;2288;1831;2069;1728;1325;1469;1023;1100}: 數組中的每對條目相乘並返回 {1428;2010;0;0;0;0;0;0;0;0};
SUMPRODUCT({1428;2010;0;0;0;0;0;0;0;0}):SUMPRODUCT 函數對數組中的所有數字求和並返回最終結果 1428+2010=3438。

如何在Excel中按工作日求和值?

如下面的屏幕截圖所示,要在 Excel 中按特定工作日對 Amount 列中的值求和,您可以將 SUMPRODUCT 函數與 WEEKDAY 函數一起應用來完成。

在這種情況下,由於我們需要對每個工作日的總金額求和,因此在應用公式之前,我們創建了一個數字 1 到 7 的輔助列,以分別表示星期一到星期日。

通用公式

=SUMPRODUCT((WEEKDAY(date_range,2)=weekday_number)*value_range)

參數

日期範圍:要評估給定工作日的日期範圍;
值_範圍:要基於工作日求和的值範圍;
2:數字2代表數字1(星期一)到7(星期日);
工作日號碼:用作求和值標準的工作日數。 它可以是單元格引用或 1 到 7 之間的數字。

如何使用這個公式?

1. 選擇數字 1 單元格旁邊的一個單元格。 這裡我選擇I6。

2. 複製或輸入以下公式,然後按 Enter 鑰匙。 選擇結果單元格,然後向下拖動其自動填充句柄以獲取其他工作日的結果。

=SUMPRODUCT((WEEKDAY($C$5:$C$14,2)=H6)*$E$5:$E$14)

注意:如果您只需要根據一個指定的工作日(例如星期五)對值進行求和,則可以直接在公式中輸入數字 5,如下所示。

=SUMPRODUCT((WEEKDAY($C$5:$C$14,2)=5)*$E$5:$E$14)

公式說明

=SUMPRODUCT((WEEKDAY($C$5:$C$14,2)=H6)*$E$5:$E$14)

工作日($C$5:$C$14,2): WEEKDAY 函數返回 1 到 7 之間的整數來表示從星期一到星期日的星期幾,範圍 C5:C14: {5;7;1;3;6;7;1;5;5;6};
{5;7;1;3;6;7;1;5;5;6}=H6: 這裡將數組中的每一天與 H6 中給定的星期幾進行比較,並返回一個新數組:{FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE};
{假;假;真;假;假;假;真;假;假;假}*$E$5:$E$14:這裡將TRUE和False轉換為1和0,然後乘以E5:E14中的值返回數組:{0;0;2288;0;0;0;1325;0;0;0};
SUMPRODUCT{0;0;2288;0;0;0;1325;0;0;0}:SUMPRODUCT 函數對數組中的所有數字求和並返回最終結果 2288+1325=3613。

相關功能

Excel WEEKNUM 函數
Excel SUMIFS 函數返回一年中給定日期的周數,從 1 月 XNUMX 日開始計算週數。

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

Excel WEEKDAY 函數
Excel EOMONTH 函數返回一個從 1 到 7 的整數,以表示 Excel 中給定日期的星期幾。


相關公式

按月匯總值(帶或不帶年份)
本教程詳細演示了兩個公式,以幫助在 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   字符
推薦地點