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는 프로세스를 간소화하고, 사람의 실수를 줄이며, 자주 그룹화된 계산을 수행하며 공식 복잡성을 피하려는 사용자에게 매우 적합합니다. 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에서 날짜/월/연도 및 날짜 범위별로 Countif 하는 방법

Excel에서 필터링된 데이터/목록에 조건을 적용하여 Countif 하는 방법