Excel에서 다른 시트의 평균 셀을 계산하는 방법은 무엇입니까?

예를 들어 Sheet1부터 Sheet5까지의 시트에서 A1:A10 범위의 평균 판매량을 계산해야 하는 경우와 같이 여러 워크시트에서 동일한 범위의 평균을 계산해야 하는 경우, Excel은 직접적인 수식 기반 솔루션을 제공합니다. 이 접근 방식은 모든 시트가 동일한 구조와 일관된 이름을 가지고 있을 때 가장 효과적입니다.

단계:

결과를 원하는 빈 셀(예: C3 셀)을 선택하고 다음 수식을 입력하세요:

=AVERAGE(Sheet1:Sheet5!A1:A10)

Enter 키를 누른 후, Excel은 Sheet1부터 Sheet5까지의 모든 시트에서 지정된 범위의 평균 값을 반환합니다.

다양한 시트에서 다른 범위의 값을 평균해야 하는 경우, 다음과 같이 수동으로 나열할 수 있습니다:

=AVERAGE(A1:A5, Sheet2!A3:A6, Sheet3!A7:A9, Sheet4!A2:A10, Sheet5!A4:A7)

이 버전은 시트 간 범위가 다를 때 유용합니다. 결과 셀에 입력하고 Enter 키를 누릅니다.

Kutools for Excel은 워크시트 참조 자동 증가 기능을 통해 여러 워크시트에서 동일한 셀 또는 범위의 값을 추출하고 통합하는 능력을 강화합니다. 이는 일관된 레이아웃을 가진 많은 수의 시트를 작업할 때 특히 유용합니다.

사용 단계:

1. 새 워크시트(예: 요약 시트)를 열고 평균을 계산하려는 셀을 선택하세요. 예를 들어 D7 셀을 선택합니다.

2. 다음으로 이동하세요: Kutools > More (내부의 수식 그룹) > 워크시트 참조 자동 증가.

3. 대화 상자에서:
- 다음에서 채우기 순서를 선택하세요 채우기 순서 드롭다운 (예: 열로 채우고 행으로 채우기).
- 다음에서 워크시트 목록, 평균을 계산하려는 셀이 포함된 시트를 체크하세요.
- 클릭 범위 채우기그리고 대화 상자를 닫습니다.

4. 선택된 셀 값은 범위에 나열됩니다(예: D7:D11). 그런 다음 다른 빈 셀에 다음 수식을 입력하여 평균을 계산하세요:

=AVERAGE(D7:D11)

Enter 키를 눌러 결과를 얻습니다. 이렇게 하면 통합이 간소화되지만 새로 추가된 시트에는 자동으로 확장되지 않습니다. 시트 목록이 변경되면 기능을 다시 실행해야 합니다.

경우에 따라 여러 워크시트에서 동시에 해당 셀들(A1, B1 및 C1 등)의 평균을 계산해야 할 수도 있습니다. 표준 수식을 사용하면 복잡해질 수 있지만, Kutools for Excel의 데이터 병합(워크시트 및 통합 문서) 유틸리티는 이 과정을 크게 단순화합니다.

이 기능을 사용하는 방법:

1. 클릭하세요 Kutools Plus > 데이터 병합 워크시트 병합 마법사를 엽니다.

2. 마법사에서 (3단계 중 1단계):
체크 여러 워크북에서 데이터를 요약하여 하나의 워크시트로 병합, 그리고 클릭하세요 다음 계속 진행합니다.

3. 3단계 중 2단계에서:
- 다음에서 포함할 워크시트를 선택하세요 워크시트 목록.
- 사용하세요 찾아보기 버튼 평균을 계산할 범위를 정의합니다.
- 클릭하세요 동일한 범위 모든 시트에서 범위가 동일한 경우.
- 클릭하세요 다음 계속 진행합니다.

4. 3단계 중 3단계에서:
선택하세요 평균 다음에서 함수 드롭다운. 필요에 따라 행/열 레이블을 구성한 후 클릭하세요 완료.

5. 현재 설정을 시나리오로 저장하여 향후 사용할지 묻는 대화 상자가 나타납니다. 필요에 따라 선택하세요 예 또는 아니오 필요에 따라 선택하세요.

이제 정의된 출력 범위의 각 셀은 선택된 모든 워크시트의 해당 셀의 평균을 반영합니다. 이 방법은 반복 작업이나 대량의 구조화된 데이터를 빠르게 통합할 때 특히 유용합니다.

특히 시트 이름이 비인접하거나 자주 변경되거나, 런타임에 범위를 지정하려는 경우, VBA 매크로는 여러 워크시트 간 셀 평균을 자동화하는 효율적인 솔루션이 될 수 있습니다. 이 방법은 고급 사용자나 자주 추가되거나 이름이 변경되는 시트가 있는 통합 문서에 적합합니다.

다음 VBA 코드는 동적으로 시트 이름과 셀 범위를 입력받아, 열거된 모든 시트에서 지정된 범위의 평균을 계산할 수 있게 해줍니다. 이는 복잡하거나 자주 업데이트되는 통합 문서에서 값을 통합하는 데 이상적입니다.

이 VBA 솔루션을 설정하고 사용하는 방법:

1. Excel에서 개발 도구 탭으로 이동하세요. 보이지 않는 경우 파일 > 옵션 > 리본 사용자 정의를 통해 활성화합니다. Visual Basic을 클릭하여 편집기를 엽니다. 그런 다음 삽입 > 모듈로 이동하여 다음 코드를 붙여넣으세요:

Sub AverageAcrossSheets()
    Dim xSheetNames As String
    Dim xCellRange As String
    Dim xArr As Variant
    Dim xSheet As Worksheet
    Dim xTotal As Double
    Dim xCount As Long
    Dim i As Integer
    
    On Error Resume Next
    xTitleId = "KutoolsforExcel"
    
    xSheetNames = Application.InputBox("Enter sheet names separated by commas (e.g., Sheet1,Sheet3,Summary):", xTitleId, Type:=2)
    If xSheetNames = "" Then Exit Sub
    
    xCellRange = Application.InputBox("Enter cell or range to average (e.g., A1 or A1:B10):", xTitleId, Type:=2)
    If xCellRange = "" Then Exit Sub
    
    xArr = Split(xSheetNames, ",")
    xTotal = 0
    xCount = 0
    
    For i = LBound(xArr) To UBound(xArr)
        Set xSheet = Nothing
        Set xSheet = ThisWorkbook.Sheets(Trim(xArr(i)))
        
        If Not xSheet Is Nothing Then
            If Not IsError(Application.WorksheetFunction.Average(xSheet.Range(xCellRange))) Then
                xTotal = xTotal + Application.WorksheetFunction.Sum(xSheet.Range(xCellRange))
                xCount = xCount + xSheet.Range(xCellRange).Count
            End If
        End If
    Next i
    
    If xCount = 0 Then
        MsgBox "No valid data found!", vbExclamation, xTitleId
    Else
        MsgBox "The average across selected sheets and range is: " & xTotal / xCount, vbInformation, xTitleId
    End If
End Sub

2. 매크로를 실행하려면 편집기에서 F5 키를 누르거나, 편집기를 닫고 개발 도구 > 매크로로 이동하여 AverageAcrossSheets를 선택하고 실행을 클릭하세요.

3. 메시지가 표시되면 쉼표로 구분된 워크시트 이름 목록(예: Sheet1,Sheet3,Summary)을 입력한 다음 범위(예: A1:A10)를 지정하세요.

4. 매크로는 각 유효한 시트에서 합계와 개수를 계산하고, 메시지 상자에 평균을 표시합니다.

매개변수 참고:

• 시트 이름은 대소문자를 구분하지 않지만 정확히 일치해야 합니다.
• 범위는 단일 셀, 전체 열(예: B:B), 또는 직사각형 범위(예: D2:E12)가 될 수 있습니다.
• 잘못된 시트나 없는 시트는 자동으로 건너뜁니다.