Skip to main content

Kutools for Office — 하나의 패키지. 다섯 가지 도구. 더 많은 일을 처리하세요.

Excel에서 5행 또는 5열마다 평균을 구하는 방법은?

Author Xiaoyang Last modified

Excel에서 대규모 데이터 세트를 다룰 때, 5행 또는 5열마다 그룹별로 평균 계산이 필요한 경우가 많습니다. 수동으로 =AVERAGE(A1:A5), =AVERAGE(A6:A10), =AVERAGE(A11:A15)와 같은 수식을 삽입할 수 있지만, 셀이 수백 또는 수천 개일 경우 이는 비효율적이며 시간이 많이 걸립니다. 이러한 작업을 수동으로 반복하는 것은 시간 소모적이고 오류 발생 가능성이 높습니다. 다행히도 Excel에서는 이러한 작업을 자동화할 수 있는 여러 가지 방법을 제공하여 데이터 분석을 훨씬 더 효율적이고 덜 지루하게 만듭니다. 본 기사는 수식 기반 접근법, Excel 추가 기능, VBA 자동화 및 피벗 테이블 기술을 포함해 5행 또는 5열마다 평균을 계산하는 몇 가지 실용적인 방법을 소개합니다.

수식을 사용하여 5행 또는 5열마다 평균 구하기

Kutools for Excel을 사용하여 5행마다 평균 구하기

VBA 코드를 사용하여 5행 또는 5열마다 평균 구하기

피벗 테이블을 사용하여 5행마다 평균 구하기

수식을 사용하여 5행 또는 5열마다 평균 구하기

표준 Excel 수식을 선호한다면, 추가 기능이나 스크립팅 없이도 5행 또는 5열마다의 계산을 자동화할 수 있습니다. 이 접근법은 단순히 평균 세트를 생성하여 분석을 지원해야 하는 정적 데이터 세트에 특히 적합합니다. 그러나 데이터를 올바르게 참조하고 빈 칸이나 불규칙한 간격을 처리하는 데 주의해야 합니다.

다음 예제는 열의 5행마다 평균을 계산하는 방법을 보여줍니다:

1. 결과를 원하는 첫 번째 셀(C2)에 다음 수식을 입력하세요:

=AVERAGE(OFFSET($A$2,(ROW()-ROW($C$2))*5,,5,))

여기서 A2는 데이터 열의 시작 셀, C2는 수식 출력 셀, 5는 간격(평균을 내야 할 행의 수)입니다. 실제 데이터 세트에 맞게 이 참조들을 조정해야 합니다.

수식을 입력한 후 Enter 키를 누릅니다. 그러면 첫 번째 평균 결과가 표시됩니다. 스크린샷 보기:

a screenshot of using formula to calculate the average of every5 rows

2. 수식 셀을 선택한 다음, 남은 데이터에 5개 미만의 값이 있어 #DIV/0!과 같은 오류 값이 나올 때까지 채우기 핸들을 아래로 드래그합니다. 이렇게 하면 각 5행 그룹의 평균이 자동으로 검색됩니다. 스크린샷 보기:

a screenshot showing all results

팁 및 참고사항: 데이터가 완벽한 크기의 그룹으로 구성되지 않을 경우 IFERROR()와 같은 오류 처리 함수를 사용하여 오류 값을 숨길 수 있습니다. 예:

=IFERROR(AVERAGE(OFFSET($A$2,(ROW()-ROW($C$2))*5,,5,)),"")

행 전체에서 5열마다 평균을 계산하려면 다음 수식을 적용하세요(A3에 배치하고 드래그).

=AVERAGE(OFFSET($A$1,,(COLUMNS($A$3:A3)-1)*5,,5))

여기서 A1은 시작 셀, A3은 수식 출력 셀, 5는 각 그룹의 열 수입니다. 데이터 레이아웃에 따라 셀 참조를 조정하세요.
수식을 입력하고 Enter 키를 누른 후오류 값이 나올 때까지 채우기 핸들을 오른쪽으로 드래그합니다. 스크린샷 보기:

a screenshot of using formula to calculate the average of every5 columns

이 수식 기반 방법은 빠른 일회성 계산에 적합하거나 추가 도구를 사용하지 않고 싶을 때 유용합니다. 그러나 데이터의 크기나 모양이 변경됨에 따라 수식을 조정하거나 셀 범위를 수동으로 업데이트해야 할 수 있으며, 불완전한 그룹을 처리하는 데 추가 주의가 필요할 수 있습니다.

Kutools for Excel을 사용하여 5행마다 평균 구하기

Kutools for Excel은 복잡한 수식을 관리하지 않고도 자주 행 그룹의 평균을 내야 하는 경우에 편리한 그래픽 솔루션을 제공합니다. '다른 행마다 페이지 나누기 삽입' 및 '데이터 페이지 통계' 기능을 통해 몇 번의 클릭만으로 데이터를 빠르게 분할하고 일괄 평균을 계산할 수 있습니다. 이 방법은 반복 가능한 간격에 평균을 적용하고 워크시트에서 직접 그룹화를 시각화하려는 경우 특히 유용합니다.

Kutools for Excel을 다운로드하고 설치한 후 Kutools for Excel 다음 단계를 따르세요:

1. Kutools Plus > 인쇄 > 다른 행마다 페이지 나누기 삽입을 클릭하세요. 스크린샷 보기:

a screenshot of enabling the Insert Page Break Every Row feature

2. '다른 행마다 페이지 나누기 삽입' 대화 상자에서 간격(예: 5)을 지정하여 5행마다 페이지 나누기를 삽입합니다. 이를 통해 Kutools가 데이터를 자동으로 분할합니다. 스크린샷 보기:

a screenshot of specifying the interval of rows

3. 다음으로 Kutools Plus > 인쇄 > 데이터 페이지 통계를 클릭하세요. 스크린샷 보기:

a screenshot of enabling the Paging Subtotals feature of Kutools

4. 데이터 페이지 통계 대화 상자에서 평균을 내고 싶은 열을 선택하고, 계산 방법으로 평균을 선택하세요. 스크린샷 보기:

a screenshot of choosing Average as the paging subtotals

5. 확인 버튼을 클릭하면, Kutools가 즉시 각 5행 간격마다 평균 값을 가진 소계 행을 삽입합니다. 스크린샷 보기:

a screenshot showing the average of every5 rows

지금 바로 Kutools for Excel을 다운로드하고 무료로 사용해 보세요!

Kutools는 수식을 조정하거나 스크립트를 작성하지 않고도 반복적인 데이터 그룹화 및 분석을 쉽게 완료할 수 있게 해줍니다. 그러나 삽입된 페이지 나누기가 인쇄 레이아웃 및 보기에 영향을 미칠 수 있으므로 보고서에서 필요하지 않은 경우 사용 후에는 제거하는 것이 좋습니다.

VBA 코드를 사용하여 5행 또는 5열마다 평균 구하기

대규모 또는 끊임없이 변화하는 데이터 세트에서 고정된 수의 행 또는 열마다 평균값을 반복적으로 계산해야 하는 경우, VBA를 사용하여 이 프로세스를 자동화하면 많은 수작업을 절약할 수 있습니다. VBA를 사용하면 데이터를 순환하며 필요한 대로 그룹화하고 각 배치의 평균 결과를 출력할 수 있습니다. 이 방법은 고급 사용자나 동적 데이터 블록을 다루는 사람들에게 특히 적합하며, 수식으로 인해 워크시트가 어지럽혀지는 것을 방지합니다. 아래는 쉽게 수정할 수 있는 범용 VBA 매크로입니다.

5행마다 평균 자동화하기:

1. 개발 도구 > Visual Basic을 클릭하여 Microsoft Visual Basic for Applications 창을 엽니다. 그런 다음, 삽입 > 모듈을 클릭하고 아래 코드를 모듈에 붙여넣으세요:

Sub AverageEvery5Rows()
    Dim DataRange As Range
    Dim OutputCell As Range
    Dim GroupSize As Integer, i As Integer, j As Integer
    Dim LastRow As Long, StartRow As Long
    Dim SumValue As Double, CountValue As Integer
    
    On Error Resume Next
    xTitleId = "KutoolsforExcel"
    
    Set DataRange = Application.InputBox("Select the data range to average (single column)", xTitleId, Selection.Address, Type:=8)
    Set OutputCell = Application.InputBox("Select the first cell for output", xTitleId, , Type:=8)
    GroupSize = Application.InputBox("Enter group size (e.g. 5)", xTitleId, 5, Type:=1)
    
    On Error GoTo 0
    
    If DataRange Is Nothing Or OutputCell Is Nothing Then Exit Sub
    
    LastRow = DataRange.Rows.Count
    StartRow = 1
    i = 0
    
    Do While StartRow <= LastRow
        SumValue = 0
        CountValue = 0
        
        For j = 0 To GroupSize - 1
            If (StartRow + j) <= LastRow Then
                SumValue = SumValue + DataRange.Cells(StartRow + j, 1).Value
                CountValue = CountValue + 1
            End If
        Next j
        
        If CountValue > 0 Then
            OutputCell.Offset(i, 0).Value = SumValue / CountValue
        Else
            OutputCell.Offset(i, 0).Value = ""
        End If
        
        StartRow = StartRow + GroupSize
        i = i + 1
    Loop
End Sub

2. 코드를 실행하려면 Run button 버튼을 클릭하거나 F5 키를 누릅니다.데이터 범위(한 열)를 선택한 후 출력 시작 셀과 그룹 크기(예: 5)를 지정합니다. 매크로는 지정된 출력 열에서 각 5행 세트의 평균을 한 줄씩 출력합니다.

비슷한 매크로를 사용하여 행 전체에서 5열마다 평균을 구할 수 있습니다.

5열마다 평균 자동화하기:

Sub AverageEveryNColumns()
    Dim DataRange As Range
    Dim OutputCell As Range
    Dim GroupSize As Long
    Dim totalCols As Long, totalRows As Long
    Dim startCol As Long, endCol As Long, outCol As Long
    Dim v As Variant
    Dim r As Long, c As Long
    Dim sumVal As Double, cntVal As Long
    Dim xTitleId As String
    xTitleId = "KutoolsforExcel"
    On Error Resume Next
    Set DataRange = Application.InputBox("Select the data range (single rows)", _
                                         xTitleId, Selection.Address, Type:=8)
    Set OutputCell = Application.InputBox("Select the first cell for output (results will spill to the right)", _
                                          xTitleId, , Type:=8)
    GroupSize = Application.InputBox("Enter group size (e.g. 5)", xTitleId, 5, Type:=1)
    On Error GoTo 0
    
    If DataRange Is Nothing Or OutputCell Is Nothing Then Exit Sub
    If GroupSize < 1 Then
        MsgBox "Group size must be >= 1.", vbExclamation
        Exit Sub
    End If
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    Dim prevCalc As XlCalculation
    prevCalc = Application.Calculation
    Application.Calculation = xlCalculationManual
    
    totalCols = DataRange.Columns.Count
    totalRows = DataRange.Rows.Count
    v = DataRange.Value
    outCol = 0
    For startCol = 1 To totalCols Step GroupSize
        endCol = startCol + GroupSize - 1
        If endCol > totalCols Then endCol = totalCols
        sumVal = 0
        cntVal = 0
        For r = 1 To totalRows
            For c = startCol To endCol
                If Not IsEmpty(v(r, c)) Then
                    If IsNumeric(v(r, c)) Then
                        sumVal = sumVal + CDbl(v(r, c))
                        cntVal = cntVal + 1
                    End If
                End If
            Next c
        Next r
        If cntVal > 0 Then
            OutputCell.Offset(0, outCol).Value = sumVal / cntVal
        Else
            OutputCell.Offset(0, outCol).Value = ""
        End If
        outCol = outCol + 1
    Next startCol
CleanExit:
    Application.Calculation = prevCalc
    Application.EnableEvents = True
    Application.ScreenUpdating = True
End Sub

피벗 테이블을 사용하여 5행마다 평균 구하기

5행마다 그룹 평균을 계산하는 또 다른 실용적인 방법은 피벗 테이블을 사용하여 시퀀스 번호 또는 인덱스 열을 결합하고 데이터를 그룹화하는 것입니다. 이 방법은 구조화된 표 형식 데이터로 작업하면서 공식 작성이나 추가 기능 사용 없이 빠르고 대화형 요약이 필요한 사용자에게 특히 유용합니다. 피벗 테이블은 데이터 변경을 동적으로 처리하고 유연한 그룹화를 지원합니다. 대규모 데이터 세트나 정기적인 보고 작업에 매우 적합합니다.

보조 열과 피벗 테이블을 사용하여 이 작업을 수행하는 방법은 다음과 같습니다:

1. 데이터 옆에 "인덱스" 또는 "그룹" 열을 추가하여 5행마다 그룹을 표시합니다. 첫 번째 데이터 행(B2)에 다음을 입력하세요:

=INT((ROW()-ROW($A$2))/5)+1

이 수식은 각 행을 순차적으로 라벨링하고, 5행마다 동일한 그룹 번호를 할당합니다. 데이터 세트를 따라 이 수식을 모두 채우세요.

2. 데이터와 새로운 인덱스 열을 선택한 다음, 삽입 > 피벗테이블을 클릭합니다. 피벗테이블 생성 대화 상자에서 데이터 범위를 확인하고 피벗 테이블을 배치할 위치를 선택합니다.

3. 새로 생성된 피벗 테이블 필드 목록에서 "그룹" 필드를 행 영역으로, 값 필드(예: "매출")를 값 영역으로 드래그합니다.

4. 값 영역의 드롭다운을 클릭하고, 값 필드 설정을 선택한 후 평균을 선택합니다.

이제, 피벗 테이블은 보조 열에 의해 편리하게 그룹화된 원본 데이터의 5행마다 평균을 표시합니다.

피벗 테이블 방법의 주요 장점은 소스 데이터가 변경될 때 유연성과 간편한 업데이트입니다. 하지만 보조 열을 추가해야 하며, 데이터가 정확히 포맷되거나 변경되지 않아야 하는 상황에는 적합하지 않을 수 있습니다.

관련 기사:

새로운 숫자가 들어올 때 열의 마지막 5개 값의 평균을 구하는 방법은?

Excel에서 상위 또는 하위 3개 값의 평균을 구하는 방법은?

최고의 오피스 생산성 도구

🤖 Kutools AI 도우미: 데이터 분석에 혁신을 가져옵니다. 방법: 지능형 실행   |  코드 생성  |  사용자 정의 수식 생성  |  데이터 분석 및 차트 생성  |  Kutools Functions 호출…
인기 기능: 중복 찾기, 강조 또는 중복 표시   |  빈 행 삭제   |  데이터 손실 없이 열 또는 셀 병합   |   반올림(수식 없이) ...
슈퍼 LOOKUP: 다중 조건 VLOOKUP  |   다중 값 VLOOKUP  |   다중 시트 조회   |   퍼지 매치 ....
고급 드롭다운 목록: 드롭다운 목록 빠르게 생성   |  종속 드롭다운 목록   |  다중 선택 드롭다운 목록 ....
열 관리자: 지정한 수의 열 추가  |  열 이동  |  숨겨진 열의 표시 상태 전환  |  범위 및 열 비교 ...
추천 기능: 그리드 포커스   |  디자인 보기   |   향상된 수식 표시줄   |  통합 문서 & 시트 관리자   |  자동 텍스트 라이브러리   |  날짜 선택기   |  데이터 병합   |  셀 암호화/해독   |  목록으로 이메일 보내기   |  슈퍼 필터   |   특수 필터(굵게/이탤릭/취소선 필터 등) ...
15대 주요 도구 세트: 12 가지 텍스트 도구(텍스트 추가, 특정 문자 삭제, ...)  |  50+ 종류의 차트(간트 차트, ...)  |  40+ 실용적 수식(생일을 기반으로 나이 계산, ...)  |  19 가지 삽입 도구(QR 코드 삽입, 경로에서 그림 삽입, ...)  |  12 가지 변환 도구(단어로 변환하기, 통화 변환, ...)  |  7 가지 병합 & 분할 도구(고급 행 병합, 셀 분할, ...)  |  ... 등 다양
Kutools는 사용자가 선호하는 언어로 이용할 수 있습니다 – 영어, 스페인어, 독일어, 프랑스어, 중국어 및40개 이상의 언어 지원!

Kutools for Excel과 함께 엑셀 능력을 한 단계 끌어 올리고, 이전에 없던 효율성을 경험하세요. Kutools for Excel은300개 이상의 고급 기능으로 생산성을 높이고 저장 시간을 단축합니다.  가장 필요한 기능을 바로 확인하려면 여기를 클릭하세요...

Office Tab은 Office에 탭 인터페이스를 제공하여 작업을 더욱 간편하게 만듭니다

  • Word, Excel, PowerPoint에서 탭 편집 및 읽기를 활성화합니다.
  • 새 창 대신 같은 창의 새로운 탭에서 여러 파일을 열고 생성할 수 있습니다.
  • 생산성이50% 증가하며, 매일 수백 번의 마우스 클릭을 줄여줍니다!

모든 Kutools 추가 기능. 한 번에 설치

Kutools for Office 제품군은 Excel, Word, Outlook, PowerPoint용 추가 기능과 Office Tab Pro를 한 번에 제공하여 Office 앱을 활용하는 팀에 최적입니다.

Excel Word Outlook Tabs PowerPoint
  • 올인원 제품군 — Excel, Word, Outlook, PowerPoint 추가 기능 + Office Tab Pro
  • 설치 한 번, 라이선스 한 번 — 몇 분 만에 손쉽게 설정(MSI 지원)
  • 함께 사용할 때 더욱 효율적 — Office 앱 간 생산성 향상
  • 30일 모든 기능 사용 가능 — 회원가입/카드 불필요
  • 최고의 가성비 — 개별 추가 기능 구매 대비 절약