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 BasicVBA 편집기를 엽니다。
• 클릭하여삽입>모듈， 그런 다음 아래 코드를 모듈 창에 복사하여 붙여넣으세요。

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함수의 주차 번호은 설정(시스템， 미국/ISO， 사용자가 선택한 시작 요일)에 따라 달라질 수 있습니다。
• ISO 주차 번호(유럽 표준， 월요일 시작 주， 첫 번째 목요일이 포함된 주를 첫 주로 간주)를 사용하는 경우=ISOWEEKNUM(A2)를 사용하세요(Excel 2013 이상용)。
• 정확한 결과를 얻으려면 모든 날짜 값이 유효한 Excel 날짜 형식 형식인지 항상 확인하세요。

이 방법은 동적 데이터 테이블에 유연하게 적용 가능하며， 대시보드 구성， 주기적 요약 작성， 피벗테이블이나 추가 애드인 없이 주 단위로 교차 집계를 수행하고자 할 때 활용할 수 있습니다。

Excel 에서 두 날짜 사이의 주말/평일 수 집계하기

Excel 에서 날짜/월/연도 및 날짜 범위 기준으로 COUNTIF 사용하기

Excel 에서 필터링된 데이터/목록을 기준으로 COUNTIF 사용하기