如何在 Excel 中按年/季度/月/周計算出現次數？

當你需要快速找出某個事件在特定年份或月份中發生的次數時，公式提供了一種靈活且動態的方法。通過將內置日期函數與 SUMPRODUCT 結合使用，你可以直接按年、月或任何組合計算計數，使你的總結準確並且隨著源數據的變化自動更新。這種方法適用於大多數小到中型數據集的常規分析任務。

選擇一個空白單元格以顯示計數結果，然後輸入以下公式：

=SUMPRODUCT((MONTH($A$2:$A$24)=F2)*(YEAR($A$2:$A$24)=$E$2))

輸入公式後，向下拖動單元格的自動填充手柄，根據需要將公式應用於其他行。如下所示：

注意和提示：

• 公式中的 MONTH($A$2:$A$24)=F2 和 YEAR($A$2:$A$24)=$E$2 是匹配 F2 中指定月份和 E2 中指定年份的條件。請根據你的數據佈局更新範圍和引用（例如 A2:A24、E2、F2）。
• 如果只想按月計數，忽略年份，則使用：
  =SUMPRODUCT(1*(MONTH($A$2:$A$24)=F2))
• 確保日期列包含真實的 Excel 日期值，而不是文本格式的日期，以避免錯誤或不匹配。如果公式返回意外結果，請仔細檢查日期格式。
• 如果你的數據集很大，考慮使用樞紐分析表或 VBA 來提高性能並更容易維護。

此方法適用於大多數需要快速日期統計的場景，並且希望在修改數據時自動更新結果。然而，處理多個分組條件可能會使公式變得複雜且難以維護。

如果你已經安裝了 Kutools for Excel，你可以利用其直觀的工具，按年、月、工作日、天或進一步的組合（如年和月或月和天）來分組和計算出現次數，而無需構建複雜的公式。這種方法對於尋求可視化、菜單驅動解決方案的用戶特別有效。

1. 選擇包含日期的列，然後點擊 Kutools > 格式 > 應用日期格式。將會出現以下對話框：

2. 在應用日期格式對話框中，選擇與計數需求相應的格式樣式（如月、年、工作日、日等），然後點擊確定。例如，選擇 "Mar" 以按月計數。

3. 當日期列仍然選中時，點擊 Kutools > 轉為實際值。此步驟將所有日期轉換為顯示值（例如，月份名稱），以便在後續步驟中更容易分組。

4. 接下來，選擇包含已轉換組名和相關數據（如金額或類別列）的範圍。前往 Kutools > 文字 > 高級合併行。你會看到以下界面：

5. 在高級合併行對話框中：
(1) 將你的日期列設置為 主鍵 以進行分組。
(2) 對於你想要計數的列（如金額），將計算設置為 計數.
(3) 你可以選擇其他聚合或組合方法來處理其他列（例如，用逗號合併水果名稱）。
(4) 點擊 確定 進行處理。

你的數據現在將顯示每段選擇期間的記錄計數。見下圖：

與手動公式相比，Kutools 簡化了過程，減少人為錯誤，非常適合經常進行分組計數並希望避免公式複雜性的用戶。它適用於小型和大型數據集。記住，在批量轉換或合併行之前備份你的數據。

樞紐分析表提供了一種強大且互動的方式來分析大型數據集，並按一個或多個時間維度（年、月、季度、小時等）總結出現次數——所有操作都只需簡單的點擊界面即可完成。樞紐分析表還允許快速重新配置和過濾，使其成為探索數據模式或準備管理報告的理想選擇。

1. 選擇你的數據表，然後前往 插入 > 樞紐分析表。創建樞紐分析表對話框將出現。

2. 在對話框中，指定放置樞紐分析表的位置（新工作表或現有位置，例如 E1 單元格），然後點擊 確定.

3. 在樞紐分析表字段窗格中，將日期字段拖到行部分，並將金額（或目標字段）拖到值部分。默認情況下，值可能會被求和。

樞紐分析表如下圖所示：

4. 通過右鍵單擊值列標題（例如，金額總和），然後選擇 值的匯總方式 > 計數.

5. 若要按其他期間（如月、年或季度）進行分組，右鍵單擊行標籤列中的任何單元格，選擇 分組，並從對話框中選擇分組條件（如月、年或季度），然後點擊 確定.

你的表格現在將按所選期間顯示計數：

注意： 按多個期間（如月和年）分組將在行標籤中添加額外層級。你可以在樞紐分析表字段窗格中重新排列分組字段（例如，將 年份 移至 日期下方）以調整你的摘要視圖。

此方法最適合大型和動態數據集，需要定期分組、比較和總結。不太適合快速、臨時的單元格級別計算，或不熟悉樞紐分析表功能的用戶。

當你需要反覆生成按各種時間段分組的出現次數摘要，或希望自動化計數過程以提高效率——特別是在大型數據集中——自定義 VBA 宏可以是一個有效的解決方案。如果你定期處理數據、生成定期摘要表，或需要自定義分組（如財政季度或周）且不易通過公式或樞紐分析表處理，這種方法非常適合。

完整操作步驟：

• 首次運行任何宏之前，請備份你的工作簿。
• 點擊開發工具 > Visual Basic 打開 VBA 編輯器。
• 點擊插入 > 模塊，然後將以下代碼複製並粘貼到模塊窗口中。

Sub CountOccurrencesByPeriod()
    Dim lastRow As Long
    Dim ws As Worksheet, summaryWs As Worksheet
    Dim periodType As String
    Dim dict As Object, key As Variant
    Dim dateRange As Range, cell As Range
    Dim outputRow As Long
    
    On Error Resume Next
    xTitleId = "KutoolsforExcel"
    
    Set ws = Application.ActiveSheet
    Set dateRange = Application.InputBox("Select date range:", xTitleId, Selection.Address, Type:=8)
    
    periodType = Application.InputBox("Count by (Year/Quarter/Month/Week):", xTitleId, "Month", Type:=2)
    
    If dateRange Is Nothing Or periodType = "" Then Exit Sub
    
    Set dict = CreateObject("Scripting.Dictionary")
    
    For Each cell In dateRange
        If IsDate(cell.Value) Then
            Select Case LCase(periodType)
                Case "year"
                    key = Year(cell.Value)
                Case "quarter"
                    key = "Q" & WorksheetFunction.RoundUp(Month(cell.Value) / 3, 0) & " " & Year(cell.Value)
                Case "month"
                    key = Format(cell.Value, "yyyy-mm")
                Case "week"
                    key = "W" & WorksheetFunction.WeekNum(cell.Value) & " " & Year(cell.Value)
                Case Else
                    key = Format(cell.Value, "yyyy-mm")
            End Select
            
            If dict.Exists(key) Then
                dict(key) = dict(key) + 1
            Else
                dict.Add key, 1
            End If
        End If
    Next cell
    
    Set summaryWs = Worksheets.Add(After:=ws)
    summaryWs.Name = "Occurrence_Summary"
    
    summaryWs.Range("A1").Value = "Period"
    summaryWs.Range("B1").Value = "Occurrences"
    
    outputRow = 2
    For Each key In dict.Keys
        summaryWs.Cells(outputRow, 1).Value = key
        summaryWs.Cells(outputRow, 2).Value = dict(key)
        outputRow = outputRow + 1
    Next key
    
    MsgBox "Summary completed in sheet 'Occurrence_Summary'.", vbInformation
End Sub

輸入代碼後：

• 返回 Excel 並按 Alt+F8，選擇 CountOccurrencesByPeriod，然後點擊運行。
• 系統將提示你選擇要分析的日期範圍。選擇包含日期的相關列或範圍。
• 第二個提示詢問要按哪個期間分組：輸入 "Year"、"Quarter"、"Month" 或 "Week"（不區分大小寫）。
• 宏將生成一個名為 Occurrence_Summary 的新工作表，列出每個期間及其內部的出現次數。

故障排除和提示：

• 如果遇到宏安全警告，請在 文件 > 選項 > 信任中心 > 宏設置 中調整宏設置。
• 確保日期列包含有效的 Excel 日期值；文本字符串或混合格式可能會導致不準確的計數或錯誤。
• 宏具有靈活性——輸入 "Quarter" 可快速按年和季度分組計數，或輸入 "Week" 以按周總結。
• 如果你想自定義輸出（例如，添加更多詳細信息），可以修改宏以處理其他列或計算規則。

該解決方案對於批量報告或定期分析非常穩健，但假定你對 VBA 有基本了解並能正確管理工作簿。如果你希望結合可視化總結，考慮同時使用樞紐分析表和 VBA。

按周計算條目或事件的頻率是銷售追蹤、項目管理和資源分配中的常見需求。Excel 提供了 WEEKNUM 函數，該函數返回給定日期在一年中的周數，使用公式可以輕鬆按周分組數據。

適用場景：你有一系列日期（例如銷售或出勤數據），並想計算每年有多少條目落入每個周。當你的數據經常變化時，這種方法適用於持續分析，因為計數會自動更新。

1. 在空列（例如 B2）中，輸入以下公式以計算 A 列中每個日期的周數：

=WEEKNUM(A2,1)

第二個參數（"1"）表示周從星期日開始（如果想讓周從星期一開始，則更改為 "2"）。將此公式複製到所有日期數據的行。

2. 列出你想要總結的周數（例如，1,2,3,…）。在另一個空白單元格（比如 D2）中，使用以下公式來計算特定周數的出現次數（假設 B2:B24 列出了周數，D2 包含要查找的周數）：

=COUNTIF($B$2:$B$24, D2)

按下 Enter 後，將此公式向下拖動到你的周數列表中。每個結果顯示該周的出現次數。

提示和注意事項：

• 如果你想按年和周進行計數，以區分不同年份的條目，請使用：

  =SUMPRODUCT((YEAR($A$2:$A$24)=$F$2)*(WEEKNUM($A$2:$A$24,1)=G2))

  其中 F2 是目標年份，G2 是目標周數。根據需要調整列範圍和引用。
• WEEKNUM 函數的周編號可能會根據設置（系統、US/ISO、你選擇的起始日）有所不同。
• 如果使用 ISO 周數（歐洲標準，周從星期一開始，第一周為包含第一個星期四的那一周），請使用 =ISOWEEKNUM(A2)（適用於 Excel2013 及更高版本）。
• 務必確保所有日期值都是有效的 Excel 日期格式，以獲得準確的結果。

該方法對於動態數據表非常靈活，可以用於儀表板、定期摘要以及當你想跨表計數而不使用樞紐分析表或額外插件的情況。

在 Excel 中計算兩個日期之間的周末/工作日數量

在 Excel 中按日期/月/年和日期範圍進行 Countif 計算

在 Excel 中根據條件計算篩選數據/列表的 Countif