跳到主要內容

在 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 人工智慧助手:基於以下內容徹底改變數據分析: 智慧執行   |  生成代碼  |  建立自訂公式  |  分析數據並產生圖表  |  呼叫 Kutools 函數...
熱門特色: 尋找、突出顯示或識別重複項  |  刪除空白行  |  合併列或儲存格而不遺失數據  |  沒有公式的回合 ...
超VLookup: 多重標準  |  多重價值  |  跨多頁  |  模糊查詢...
副詞。 下拉清單: 簡易下拉列表  |  依賴下拉列表  |  多選下拉列表...
欄目經理: 新增特定數量的列  |  移動列  |  切換隱藏列的可見性狀態  將列與 選擇相同和不同的單元格 ...
特色功能: 網格焦點  |  設計圖  |  大方程式酒吧  |  工作簿和工作表管理器 | 資源庫 (自動文字)  |  日期選擇器  |  合併工作表  |  加密/解密單元格  |  按清單發送電子郵件  |  超級濾鏡  |  特殊過濾器 (過濾粗體/斜體/刪除線...)...
前 15 個工具集12 文本 工具 (添加文本, 刪除字符 ...)  |  50+ 圖表 類型 (甘特圖 ...)  |  40+ 實用 公式 (根據生日計算年齡 ...)  |  19 插入 工具 (插入二維碼, 從路徑插入圖片 ...)  |  12 轉化 工具 (數字到單詞, 貨幣兌換 ...)  |  7 合併與拆分 工具 (高級合併行, 拆分 Excel 儲存格 ...)  |  ... 和更多

Kutools for Excel 擁有超過 300 個功能, 確保只需點擊一下即可獲得您所需要的...

產品描述


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

  • 一秒鐘即可在數十個打開的文檔之間切換!
  • 每天為您減少數百次鼠標單擊,告別鼠標手。
  • 查看和編輯多個文檔時,將您的工作效率提高 50%。
  • 為 Office(包括 Excel)帶來高效的選項卡,就像 Chrome、Edge 和 Firefox 一樣。
Comments (0)
No ratings yet. Be the first to rate!
There are no comments posted here yet
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations