如何在 Excel 中根據星期幾來計算平均值？

作者Xiaoyang修改日期2025-10-11

在 Excel 中，您經常會遇到需要根據每筆資料對應的星期來計算平均值的情境。例如，您可能想分析銷售資料，找出週一、工作日或週末的平均訂單數量。這類需求常見於銷售報表、績效追蹤，以及任何涉及時間分析的場合。以下提供多種實用解決方案，透過公式、VBA 程式碼與資料透視表，協助您依星期計算平均值，深入洞察資料，並根據曆法模式更透徹地掌握數據分佈。

根據星期幾計算平均值

💡 快速導覽

➤ 使用公式根據星期幾計算平均值
↳ 特定星期幾（例如週一）
↳ 工作日（週一至週五）
↳ 週末（週六至週日）
➤ 使用 VBA 巨集根據星期幾自動計算平均值
➤ 資料透視表：無需公式即可依星期幾分組並計算平均值

使用公式根據星期幾計算平均值

根據特定星期幾計算平均值

若要計算與特定星期（例如所有週一）相關數值的平均值，可運用 Excel 的陣列公式或 SUMPRODUCT 函數。此方法特別適合彙整每日趨勢並分析事件模式。例如，若需計算資料集中所有週一的平均訂單數量，請採用下列方法：

在空白儲存格中輸入下列公式：

=AVERAGE(IF(WEEKDAY(D2:D15)=2,E2:E15))

接著同時按下 Ctrl + Shift + Enter，即可將該公式設為陣列公式，讓 Excel 能針對每一列個別運算，確保得出正確結果！

使用公式根據特定星期幾計算平均值

注意事項與說明：

D2:D15 是您的日期清單，請確保這些儲存格包含有效的 Excel 日期值。
2 代表星期一。星期的數字對應如下：星期日＝1、星期一＝2、星期二＝3、星期三＝4、星期四＝5、星期五＝6、星期六＝7.
E2:E15 是您要計算平均值的數字範圍，例如訂單數量、銷售額等關鍵指標。

提示：

若您的 Excel 版本支援動態陣列公式（Office 365 或更新版本），即可直接輸入公式，無需使用 Ctrl + Shift + Enter。
請檢查是否有空白或非日期格式的儲存格，以免造成公式錯誤。

作為更靈活的替代方案，您可運用 SUMPRODUCT 函數達成相同效果。此方法無需以陣列公式輸入，特別適合處理大型資料集：

=SUMPRODUCT((WEEKDAY(D2:D15,2)=1)*E2:E15)/SUMPRODUCT((WEEKDAY(D2:D15,2)=1)*1)

在儲存格中輸入此公式後，按下 Enter。D2:D15 為您的日期範圍，E2:E15 為您的數據區域，而 1 代表星期一（當 WEEKDAY 函數的第二個引數設為 2 時，星期一＝1、星期二＝2、……、星期日＝7）。

根據工作日計算平均值

若要計算資料中工作日（週一至週五）的平均值，可使用下列陣列公式：

=AVERAGE(IF(WEEKDAY(D2:D15,2)={1,2,3,4,5},E2:E15))

在空白儲存格中輸入此公式，然後按下 Ctrl + Shift + Enter 確認。

使用公式根據工作日計算平均值

注意事項與說明：

此公式僅針對日期落在工作日（週一至週五）的資料列計算平均值。
請確保 D2:D15 中的日期值有效，否則 WEEKDAY 函數可能無法傳回預期結果！

提示：

若您希望避免使用陣列輸入，可採用下方的 SUMPRODUCT 替代方案。
請確認日期欄位包含的是真正的 Excel 日期，而非文字格式。

另一種達成方式是使用 SUMPRODUCT 公式：

=SUMPRODUCT((WEEKDAY(D2:D15,2)<6)*E2:E15)/SUMPRODUCT((WEEKDAY(D2:D15,2)<6)*1)

只需輸入此公式並按下 Enter，即可自動計算週一至週五（週幾數字小於 6）資料的平均值！

根據週末計算平均值

若僅需計算週末（星期六與星期日）數值的平均值，請使用下列陣列公式：

=AVERAGE(IF(WEEKDAY(D2:D15,2)={6,7},E2:E15))

在空白儲存格中輸入，並以 Ctrl + Shift + Enter 確認。

使用公式根據週末計算平均值

注意事項與說明：

此公式適用於 WEEKDAY 函數在第二個引數設為 2 時，傳回 6 或 7（代表星期六或星期日）的日期。

提示：

對於大型資料集，以下 SUMPRODUCT 替代方案執行速度更快，且無需陣列輸入。
請確認已妥善處理空白列或非日期值，以避免產生非預期的平均值。

使用 SUMPRODUCT 的快速選項，無需陣列輸入即可運作：

=SUMPRODUCT((WEEKDAY(D2:D15,2)>5)*E2:E15)/SUMPRODUCT((WEEKDAY(D2:D15,2)>5)*1)

務必驗證日期值並檢查是否為空白，以確保結果準確無誤。

VBA 程式碼－透過巨集自動根據星期幾計算平均值

若您追求完全自動化處理，且擁有大型資料集或需頻繁更新，可運用 VBA 逐一掃描資料、依星期幾分組，並計算每日平均值。此方法極適合希望省去手動調整公式、快速產出星期摘要的使用者。

適用情境：適用於處理大量清單、自動化重複性分析，或為所有星期建立摘要表格的情況。

優點：省去手動操作步驟，自動產出完整摘要，還能進一步自訂處理！

缺點：需啟用巨集並具備基本 VBA 知識；可能不適用於高度動態或雲端型試算表。

操作步驟：

1. 按一下開發人員工具 > Visual Basic，開啟 VBA 編輯器。在視窗中選擇插入 > 模組，然後將下列程式碼貼到新模組中：

Sub AverageOrdersByWeekday()
    Dim dict As Object
    Set dict = CreateObject("Scripting.Dictionary")
    
    Dim cell As Range
    Dim ws As Worksheet
    Dim datesRange As Range, valuesRange As Range
    Dim i As Long, dayKey As String
    Dim sumArr(1 To 7) As Double
    Dim countArr(1 To 7) As Long
    
    On Error Resume Next
    xTitleId = "KutoolsforExcel"
    
    Set ws = Application.ActiveSheet
    Set datesRange = Application.InputBox("Select the date range", xTitleId, Selection.Address, Type:=8)
    Set valuesRange = Application.InputBox("Select the corresponding values range", xTitleId, "", Type:=8)
    
    For i = 1 To datesRange.Count
        If IsDate(datesRange.Cells(i).Value) Then
            Dim wd As Integer
            wd = Weekday(datesRange.Cells(i).Value, 2)
            sumArr(wd) = sumArr(wd) + valuesRange.Cells(i).Value
            countArr(wd) = countArr(wd) + 1
        End If
    Next i
    
    Dim resWs As Worksheet
    Set resWs = Worksheets.Add
    resWs.Name = "Weekday Averages"
    resWs.Cells(1, 1).Value = "Weekday"
    resWs.Cells(1, 2).Value = "Average"
    
    Dim dayNames As Variant
    dayNames = Array("Monday", "Tuesday", "Wednesday", "Thursday", "Friday", "Saturday", "Sunday")
    
    For i = 1 To 7
        resWs.Cells(i + 1, 1).Value = dayNames(i - 1)
        If countArr(i) > 0 Then
            resWs.Cells(i + 1, 2).Value = sumArr(i) / countArr(i)
        Else
            resWs.Cells(i + 1, 2).Value = "No data"
        End If
    Next i
End Sub

2. 若要執行巨集，請按一下按鈕，或按下 F5. 系統將提示您選取日期範圍（例如 D2:D15）及對應的數值範圍（例如 E2:E15）。
巨集會自動建立新工作表，彙整每週各日的平均值：「平均值」將計算週一至週日的數據；若某個週幾無對應資料，則顯示「無資料」。

注意事項與提示：

請確保日期與數值兩個範圍的大小一致，並逐列對齊。
巨集執行時，需將活頁簿儲存為啟用巨集的檔案（*.xlsm）。
若遇到錯誤，請檢查您的範圍是否包含空白或無效的項目。
您可以修改程式碼，加入特定星期幾的篩選條件，或擴充摘要內容。

資料透視表－使用資料透視表依週幾分組日期並計算平均值，無需公式

另一種依星期分析並計算平均值的便捷方法，就是使用資料透視表。此方法操作簡單，無需手動輸入公式或撰寫程式碼，還能讓您動態分組資料、即時計算平均值，並在資料更新時自動反映最新結果。

適用情境：適合偏好點選式介面，並希望透過拖放操作靈活建立摘要的使用者。

優點：設定快速、適用於大型資料集、新增資料時自動更新，並支援進一步分析（例如篩選與排序）。

缺點：需先將資料整理成 Excel 表格或結構化範圍；與 VBA 解決方案相比，自訂彈性較為有限。

操作步驟：

1. 新增週幾名稱輔助欄：
在空白欄位（例如）F）的 F2 儲存格中輸入：

=TEXT(D2,"dddd")

將公式向下複製，使其與您的資料列數相符。（假設日期位於）D2:D15。）

2. 選取包含輔助欄的來源區域（例如）D2:F15）。為達最佳效果，請先將其轉換為 Excel 表格（Ctrl+T），並保持選取狀態。

3. 前往插入> 樞紐分析表。在「建立樞紐分析表」對話方塊中，選擇放置位置（建議使用新工作表），然後按一下確定。

4. 在「樞紐分析表欄位」窗格中：
— 將星期幾輔助欄位（F 欄）拖曳至列區域。
— 將您的數值欄位（例如）訂單數，來自 E 欄）拖曳至值區域。

5. 將彙總方式改為平均值：
按一下值區域中的下拉式清單 > 值欄位設定> 選擇平均值> 確定。

6. （選擇性）將星期幾排序為週一→週日：
在任一星期幾標籤上按一下右鍵 > 排序> 其他排序選項，或新增一個小型自訂排序輔助欄（1–7）並依此排序。您也可以透過值欄位設定> 數字格式 來調整數字顯示格式。

7. 資料變更時重新整理：
在更新來源資料表後，於樞紐分析表內任意位置按一下，然後選擇重新整理（或）資料> 全部重新整理）。

提示與疑難排解：

請確保日期欄位包含有效的 Excel 日期（而非文字），否則週幾公式可能無法正確運作。
如果平均值看起來不正確，請確認數值欄位已設為平均值，而非總和。
在變更來源表格或新增資料列後，請使用重新整理，讓樞紐分析表立即重新計算！
若地區設定使用分號，請改為輸入 =TEXT(D2;"dddd")。

運用資料透視表進行星期幾分析，不僅能簡化流程，更能輕鬆打造互動式報表，特別適合用於簡報或與他人分享關鍵洞察。

透過 KUTOOLS AI 解鎖 Excel 的神奇功能

智慧執行：透過簡易指令，輕鬆執行儲存格操作、分析資料，並建立圖表！
自訂公式：打造專屬公式，讓您的工作流程更順暢！
VBA 編碼：輕鬆撰寫並套用 VBA 程式碼，立即提升工作效率！
公式解析：輕鬆掌握複雜公式！
文字翻譯：輕鬆打破試算表中的語言隔閡！

運用 AI 驅動的工具強化您的 Excel 能力。立即下載體驗前所未有的高效！

相關文章：

如何在 Excel 中計算兩個日期之間的平均值？

如何在 Excel 中根據多個條件計算儲存格的平均值？

如何在 Excel 中計算前 3 名或後 3 名數值的平均值？

最佳 Office 生產力工具

🤖	KUTOOLS AI 助手：基於以下內容徹底革新數據分析：智慧執行｜產生程式碼｜建立自訂公式｜分析資料並產生圖表｜呼叫增強函數……
	熱門功能：尋找、醒目提示或標記重複值｜刪除空白行｜合併列或儲存格而不遺失資料｜不使用公式的四捨五入……
	高級 LOOKUP：多重條件 VLookup ｜多重數值 VLookup ｜跨多個工作表 VLookup ｜模糊查找……
	高級下拉列表：快速建立下拉式清單｜相依式下拉式清單｜多選下拉式清單……
	欄位管理員：新增指定數量的欄位｜移動欄位｜切換隱藏欄位的可見狀態｜比較範圍與欄位……
	精選功能：網格聚焦｜設計視圖｜增強編輯欄｜工作簿與工作表管理員｜資源庫（自動文字）｜日期提取｜合併工作表｜加密／解密儲存格｜依清單傳送電子郵件｜超級篩選｜特殊篩選（篩選粗體儲存格／斜體／刪除線……） ......
	頂尖 15 工具組：12 文字工具（添加文本，刪除特定字符，……）｜ 50+ 圖表類型（甘特圖，……）｜ 40+ 實用公式（基於生日計算年齡，……）｜ 19 插入工具（插入二維碼，從路徑插入圖片，……）｜ 12 轉換工具（金額轉大寫，匯率轉換，……）｜ 7 合併和拆分工具（高級合併行，分割儲存格，……）｜……以及更多