Skip to main content

Kutools for Office — 一套工具,五種功能。完成更多工作。

如何在 Excel 中計算每週平均值?

Author Kelly Last modified

之前,我們已經分享了如何使用 Excel 中的數據透視表來計算每日、每月、每季和每小時的平均值。然而,這種方法並不能直接支持按週計算平均值。在商業和數據分析場景中,每週平均值經常被需要——例如總結每週銷售額、追蹤項目工時或監控每週開支。計算每週平均值讓您可以觀察趨勢,並根據比單純的日或月數據更有意義的時間段來做出決策。在以下部分,我們將介紹一些實用的方法,利用輔助列、Excel 內置函數、高級外掛如 Kutools for Excel 以及 VBA 自動化和數據透視表分組來計算每週平均值。這些解決方案可以根據您的數據量、自動化需求和對 Excel 工具的個人偏好來應用。


使用 Excel 函數計算每週平均值

假設您有一個類似下面截圖中的購買表格。這個例子詳細說明了如何使用 Excel 的輔助列和內置函數來計算每週平均值。此方法適合大多數喜歡逐步手動操作並且希望完全了解計算過程的用戶。

Purchase table

步驟 1: 添加一個輔助列以標記每一行的週數:

輸入 WeekNUM 在 D1 單元格中,然後在 D2 單元格中輸入公式 =WEEKNUM(A2,2)。(這裡 A2 指的是日期/時間列中的購買日期。第二個參數 2 告訴 Excel 視週起始日為星期一,這符合大多數商業場景。如果您的週從星期日開始,可以使用 1)。然後拖動填充柄向下填充整個數據範圍的週數。現在您會看到每一行所屬的週數。
=WEEKNUM(A2,2) entered in Cell D2

步驟 2: 此步驟允許按週對數據進行分組,但您可能想區分不同年份的相同週數。在此情況下,添加一個額外的輔助列來表示年份:

輸入 Year 在 E1 單元格中。在 E2 單元格中輸入 =YEAR(A2) (再次強調,A2 是您的購買日期單元格)。拖動填充柄向下,這樣您的數據現在包含週和年的列,以便更準確地進行分組。
=YEAR(A2) entered in cell E2

步驟 3:現在,計算每週平均值。在 F1 單元格中輸入 Average。在 F2 單元格中輸入以下公式: =IF(AND(D2=D1,E2=E1),"",AVERAGEIFS($C$2:$C$39,$D$2:$D$39,D2,$E$2:$E$39,E2))。然後拖動填充柄向下延伸至所需的範圍。
Formula entered in cell F2

該公式計算同年同週的平均值,並且只在每個唯一的(週,年)組合的第一個實例處顯示結果(同一組中的其他行則顯示空白)。
Purchase table with week and year numbers extracted and weekly average calculated

注意:
(1)如果某一年的一週出現多筆條目,則只有第一個相應的行顯示平均值;其他行為了清晰起見保留為空白。
(2)D1 和 D2 參考週數列,E1 和 E2 參考年份列,$C$2:$C$39 是您要平均的金額範圍,$D$2:$D$39 是週數列,$E$2:$E$39 是年份列;根據您的數據集調整這些範圍和參考。
(3)如果您不需要考慮年份,只想按週數平均,在 F2 使用: =AVERAGEIF($D$2:$D$39,D2,$C$2:$C$39) 並向下填充。這將提供簡單的每週平均值而無需區分年份。示例結果如下所示:
The formula entered in cell F2

提示:對於非常大的數據集,複製公式可能會減慢 Excel 的速度。在此情況下,請參閱下方的 VBA 自動化或數據透視表方法以獲得更具可擴展性的解決方案。

如果您收到 #DIV/0! 錯誤,通常意味著數據集中沒有該週的匹配條目。確保您的輔助列和金額列沒有空白或數據類型不匹配的情況。


使用 Kutools for Excel 批量計算所有每週平均值

此方法介紹了 Kutools for Excel 的「高級合併行」工具,使其能夠輕鬆批量計算所有每週平均值而無需手動輸入公式。Kutools 簡化了複雜的分組和平均任務,節省時間並減少手動錯誤——特別是在處理中到大型列表時,重複分組和計算尤為有效。

Kutools for Excel - 包含超過 300 種 Excel 必備工具。永久免費享受 AI 功能!立即下載!

1. 在輔助列中輸入 Week 在 D1 單元格中。然後在 D2 單元格中輸入 =WEEKNUM(A2,2) (A2 = 日期單元格)。向下填充以標記每條記錄的週數。此列讓 Kutools 能夠按週分組記錄。
=WEEKNUM(A2,2) entered in cell D2

2. 選擇包含新 Week 列的表格,然後點擊 Kutools > 文字內容 > 高級合併行。因為 Kutools 對選定範圍進行操作,所以請確保您的選擇覆蓋相關數據,包括要合併的列和每週分組列。
Advanced Combine Rows option on the Kutools tab on the ribbon

3. 在打開的「基於列的合併行」對話框中,設置以下操作:
(1)點擊 Fruit 列並將其設置為 合併 (使用逗號作為分隔符),以便每周保持水果名稱在一起。
(2)對於 Amount 列,設置操作為 計算 > 平均值 以便自動提供每週平均值。
(3)指定 Week 列為 主鍵 以便按週進行分組。
(4)點擊 確定 進行處理。
Combine Rows Based on Column dialog box

Kutools 將快速按週分組所有記錄,列出所有相關水果,並在一個乾淨的新表格中顯示平均值,如下所示:

優勢:大表格的快速處理、最小的手動錯誤和清晰的輸出。注意:確保在開始之前您的輔助列正確無誤,因為那裡的錯誤會影響結果。如果多個年份的條目中有重複的週數,您可能需要添加 Year 輔助列,並按照 Week 和 Year 分組以保證準確性。

Kutools for Excel - 超過 300 種必備工具,讓 Excel 功能更強大。永久免費享受 AI 功能!立即獲取


使用數據透視表分組並平均每週數據

數據透視表方法利用 Excel 的交互式摘要功能,按週分組數據並快速計算每週平均值。此方法最適合喜歡視覺拖放、靈活數據探索以及當源數據改變時輕鬆更新結果的用戶。它適用於大型數據集,避免了手動公式。

步驟1:在表格中添加一個輔助列以表示週數。在空白列的第一個單元格(例如 D2)中輸入:=WEEKNUM(A2,2),引用您的購買日期。向下填充以標記每一行的年度週數。

步驟2:選擇整個表格(包括新的 Week 列),然後轉到「插入」>「數據透視表」。在對話框中確認您的範圍,並選擇新工作表或現有工作表進行放置。

步驟3:在「數據透視表字段列表」窗格中:

  • 將 Week 列拖到「」區域。
  • 可選項:若要按年份分離數據,也將 Year 列(使用 =YEAR(A2) 創建)拖到 Week 上方的「行」中。
  • 將 Amount 列拖到「值」區域。點擊其下拉菜單 >「值字段設置」,並設置為平均值

數據透視表現在將自動顯示每週或(年,週)組的平均金額。您可以在添加新數據後隨時刷新該表,即時更新結果。此方法特別靈活,可以用於探索其他匯總統計數據或根據需要重新按不同的時間段進行分組。

提示:如果您的數據跨越超過一年,務必同時包含 Year 和 Week 字段,以避免將不同年份的相同週數合併。如果您想呈現週的日期範圍,可以添加一個計算輔助列,列出每週的開始日期以增加清晰度。如果您看到空白行或意外結果,請檢查您的輔助列是否沒有間隙,並且您的表格已正確格式化為 Excel 表格(Ctrl+T),以獲得最佳效果。

優勢:無需手動公式、數據變更時動態更新、支持順暢處理大型數據集。限制:需要輔助列來表示週數;原始日期必須是可識別的日期格式。


示範:在 Excel 中計算每週平均值

 
Kutools for Excel:超過 300 種實用工具任您使用!永久免費享受AI功能!立即下載!

相關文章:

最佳 Office 生產力工具

🤖 Kutools AI 助手:以智能執行為基礎,革新數據分析 生成程式碼 創建自訂公式 分析數據並生成圖表 調用 Kutools 增強函數
熱門功能查找、選取項目的背景色或標記重複值刪除空行合併列或單元格且不遺失數據四捨五入(免公式)...
高級 LOOKUP多條件 VLookup多值 VLookup多表查找模糊查找...
高級下拉列表快速創建下拉列表 依賴型下拉列表 多選下拉列表...
列管理器添加指定數量的列移動列切換隱藏列的顯示狀態比較區域及列...
精選功能網格聚焦 設計檢視 增強編輯欄 工作簿及工作表管理器 資源庫(快捷文本) 日期提取器 合併資料 加密/解密儲存格 按列表發送電子郵件 超級篩選 特殊篩選(篩選粗體/傾斜/刪除線...)...
15 大工具集12 項文本工具添加文本刪除特定字符…)50+ 儀表 類型甘特圖等)40+ 實用 公式基於生日計算年齡等)19 項插入工具插入QR码根據路徑插入圖片等)12 項轉換工具金額轉大寫匯率轉換等)7 項合併與分割工具高級合併行分割儲存格等)...及更多
使用 Kutools,語言任你選 — 支援英語、西班牙語、德語、法語、中文及超過40 種語言!

運用 Kutools for Excel,全面提升您的 Excel 技能,體驗前所未有的高效。 Kutools for Excel 提供超過300 項進階功能,讓您提升工作效率、節省時間。 點此尋找您最需要的功能...


Office Tab 為 Office 帶來分頁介面,讓您的工作更加輕鬆簡單

  • 在 Word、Excel、PowerPoint 中啟用分頁編輯與閱讀
  • 在同一視窗的新分頁中打開與創建多份文件,而非開啟新視窗。
  • 提升您的生產力50%,每日可幫您減少數百次鼠標點擊!

所有 Kutools 外掛,一次安裝

Kutools for Office 套裝整合了 Excel、Word、Outlook 和 PowerPoint 的外掛,外加 Office Tab Pro,非常適合需要跨 Office 應用程式協同作業的團隊。

Excel Word Outlook Tabs PowerPoint
  • 全合一套裝 — Excel、Word、Outlook及 PowerPoint 外掛 + Office Tab Pro
  • 一鍵安裝,一份授權 — 幾分鐘完成設置(支援 MSI)
  • 協同運作更順暢 — Office 應用間無縫提升生產力
  • 30 天全功能試用 — 無需註冊、無需信用卡
  • 最超值 — 一次購買,節省單獨外掛費用