如何在 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，然後點擊執行。
• 系統將提示你選擇要分析的日期範圍。選擇包含日期的相關列或範圍。
• 第二個提示詢問你要按哪個周期分組：輸入「年」、「季度」、「月」或「周」（不區分大小寫）。
• 宏將生成一個名為 Occurrence_Summary 的新工作表，列出每個周期及其內的出現次數。

故障排除與提示：

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

該解決方案對於批量報告或周期性分析非常穩健，但假設你對 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 函數的周編號可能因設置（系統、美國/ISO、你選擇的起始日）而有所不同。
• 如果使用 ISO 周數（歐洲標準，一周從星期一開始，第一周包含第一個星期四），請使用 =ISOWEEKNUM(A2)（適用於 Excel 2013 及更高版本）。
• 為確保準確的結果，請確保所有日期值均為有效的 Excel 日期格式。

該方法對於動態數據表非常靈活，可以適應儀表板、周期性總結，並且當你希望按周交叉表計數而不使用數據透視表或額外插件時非常有用。

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

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

在 Excel 中按條件計數篩選數據/列表