KutoolsforOffice— 하나의 솔루션， 다섯 가지 강력한 도구。적은 노력으로 더 큰 성과。

Excel 에서 인접하지 않은/연속되지 않은 셀의 평균을 0 을 제외하고 계산하려면 어떻게 하나요？

작성자Siluvia수정 날짜

Excel 데이터 작업 중 숫자 집합의 평균을 계산해야 할 때가 많습니다. 그러나 관련 셀이 서로 인접하지 않고, 또한 0 값(누락된 데이터나 0 으로 처리된 항목을 나타낼 수 있음)을 제외하고 싶을 수도 있습니다. 예를 들어, 판매 또는 재고 시트에서 특정 품목의 재고가 없거나 특정 날짜에 판매가 없을 수 있습니다. 이런 경우 단순히 평균을 계산하면 0 이 결과를 왜곡하여 실제 데이터 경향을 정확히 파악하기 어렵습니다. 0 을 제외하면 활성 데이터 포인트만 분석할 수 있습니다。

다음 스크린샷과 같은 과일 테이블이 있다고 가정해 보겠습니다. 수량 열에 있는 모든 셀의 평균을 계산하되 0 은 제외하고자 합니다. 다음 방법들은 Excel 에서 이를 달성하기 위한 여러 실용적인 접근법을 안내합니다。 데이터가 인접하지 않은 셀에 흩어져 있든， 여러 행/열에 걸쳐 있든， 또는 Excel 기본 제공 기능이나 간단한 공식을 활용하든 유연하게 적용할 수 있습니다。

0을 제외한 비인접 셀의 평균을 계산하는 데 사용된 Excel 테이블의 스크린샷

공식을 사용하여 인접하지 않은 셀의 평균에서 0 제외

이 방법은 배열 공식을 사용하여 인접하지 않은 셀(예: 행이나 열에서 다른 셀마다 하나씩)의 평균을 신속하게 계산하면서 선택된 셀 중 0 을 제외합니다。 대상 셀이 고정된 간격 패턴을 따를 때 적용 가능합니다。 특히 매일이 아닌 격일 값과 같이 주기적인 데이터나 규칙적인 간격으로 떨어져 있지만 바로 옆에 있지 않은 데이터를 처리할 때 매우 효율적입니다。

제한 사항： 이 접근법은 인접하지 않은 셀이 일정한 간격으로 배열되어 있을 때 가장 적합합니다。 임의의 수동 선택의 경우 아래의 공식 기반 방법을 참조하세요。

1. 결과를 표시할 빈 셀을 선택하고 다음 배열 공식을 입력하세요：

=AVERAGE(IF(MOD(COLUMN(C2:G2)-COLUMN(C2),2)=0,IF(C2:G2,C2:G2)))

그런 다음Ctrl+Shift+Enter키를 동시에 누르세요(이전 Excel 버전의 경우). Excel 365 또는 2019 에서는 동적 배열을 지원하므로Enter만 누르면 됩니다。

Excel에서 비인접 셀의 평균을 구하는 수식을 보여주는 스크린샷

참고: 공식에서 C2 와 G2 는 대상 범위의 첫 번째 및 마지막 셀을 나타냅니다。 숫자 “2"은 열 간격을 설정합니다。 인접하지 않은 셀이 다른 열 간격으로 분리되어 있다면 이 값을 조정하세요。 데이터 레이아웃에 따라 범위(C2:G2)와 간격(2)을 수정하세요。

팁:

  • 선택한 범위가 평균을 계산하려는 모든 인접하지 않은 셀을 포함하는지 확인하세요。
  • 데이터 선택이 일정한 간격을 기반으로 하지 않는 경우， 임의의 셀에 대해 아래의 공식 기반 솔루션을 사용하세요。
  • 이 공식은 빈 셀과 0 값을 자동으로 무시하여 의미 있는 데이터 포인트의 정확한 평균을 제공합니다。
  • 오류를 방지하려면 셀 참조가 올바른지 확인하고, 이전 버전의 Excel 에서는 배열 공식 입력 시 필요한 키 조합을 눌렀는지 확인하세요。

VBA 매크로를 사용하여 인접하지 않은 셀의 평균에서 0 제외

고정된 패턴을 따르지 않는 인접하지 않은 셀 그룹의 평균을 신속하게 계산하면서 0 을 생략해야 하는 경우(예: 서로 다른 행과 열에 걸쳐 특정 셀을 수동으로 선택하고 싶은 경우), VBA 매크로가 유연하고 강력한 해결책을 제공합니다. 이 코드는 선택한 셀을 순회하며 0 이 포함된 셀을 제외하고 평균을 계산합니다。

이 방법은 다음 경우에 탁월합니다：

  • 크거나 불규칙한 데이터 선택을 신속하게 처리합니다。
  • 유사한 데이터셋에 반복적으로 사용할 수 있도록 맞춤 논리를 저장합니다。
  • 복잡한 선택 영역에 대해 전통적인 워크시트 공식의 한계를 우회합니다。

1. 개발자>Visual Basic를 클릭하세요。 열리는 창에서삽입>모듈을 클릭하고 다음 코드를 모듈에 붙여넣으세요：

Sub AverageNonAdjacentExcludeZero()
    Dim rng As Range
    Dim cell As Range
    Dim SumVal As Double
    Dim CountVal As Long
    Dim SelectedRng As Range
    
    On Error Resume Next
    xTitleId = "KutoolsforExcel"
    
    Set SelectedRng = Application.Selection
    Set SelectedRng = Application.InputBox("Select non-adjacent cells to average (exclude zeros)", xTitleId, Type:=8)
    
    On Error Resume Next
    SumVal = 0
    CountVal = 0
    
    For Each cell In SelectedRng
        If IsNumeric(cell.Value) And cell.Value <> 0 Then
            SumVal = SumVal + cell.Value
            CountVal = CountVal + 1
        End If
    Next
    
    If CountVal > 0 Then
        MsgBox "Average (excluding zeros): " & SumVal / CountVal, vbInformation, xTitleId
    Else
        MsgBox "No non-zero numeric cells selected.", vbExclamation, xTitleId
    End If
End Sub

2. VBA 편집기에서실행 버튼실행 버튼을 클릭하거나F5를 누르세요。 셀 선택을 요청하는 대화 상자가 나타납니다。 마우스와Ctrl키를 사용해 평균을 구하려는 인접하지 않은 셀을 클릭하여 선택하세요。확인을 클릭하면 매크로가 0 을 제외한 결과를 표시합니다。

Excel 기본 제공 함수를 사용하여 인접하지 않은 셀의 평균에서 0 제외

Excel 은 공식 작업 없이도 이 작업을 처리할 수 있는 몇 가지 기본 제공 방법을 제공합니다。 이는 신속한 일회성 계산이나 시각적 검사에 적합합니다。

상태 표시줄 사용하기：

  • Ctrl 키를 누른 상태에서마우스를 사용해 평균을 구하려는 각각의 인접하지 않은 셀을 수동으로 선택하세요。 단， 0 값이 포함된 셀은 건너뛰세요。
  • 선택 후 Excel 창 오른쪽 하단에 있는 상태 표시줄을 확인하세요。평균값이 두 개 이상의 셀이 선택된 경우 자동으로 표시됩니다。
  • 이 방법은 시각적 확인 및 소규모 데이터셋에는 빠르지만， 워크시트 셀에 결과를 출력하지는 않습니다。

적합한 방법을 선택할 때 데이터 크기， 선택한 셀의 분산 정도， 반복 가능한 공식 출력이 필요한지 또는 단순한 시각적 확인만 필요한지를 고려하세요。 오류()#DIV/0!)가 발생하면 선택 항목에 최소한 하나의 0 이 아닌 값이 있는지 확인하세요. VBA 를 사용할 때는 새로운 매크로 실행 전 항상 워크북을 저장하여 실수로 인한 데이터 손실을 방지하세요。

Excel 에서 제한된 범위의 간격(인접하지 않은) 행 또는 열을 신속하게 선택하기：

Kutools for Excel 의행/열 간격 선택기능을 사용하면 아래 스크린샷과 같이 Excel 에서 제한된 범위의 간격 행 또는 열을 쉽게 선택할 수 있습니다。 공식이나 매크로를 사용하기 전 원하는 셀을 정확히 선택하는 데 유용합니다。feature can help you easily select 간격 행 or columns in 제한된 범위 in Excel as the below screenshot shown。 This can be handy before using formulas or macros by helping you select the exact cells you want to process。

Kutools for Excel 간격별 행 및 열 선택 유틸리티의 스크린샷

Kutools for Excel– Excel 을 300 개 이상의 필수 도구로 강력하게 개선하여 작업을 더 빠르고 쉽게 처리하고， 스마트한 데이터 처리와 생산성을 위한 AI 기능을 활용하세요。지금 받기

최고의 Office 생산성 도구

🤖KUTOOLS AI 도우미： 다음을 기반으로 데이터 분석 혁신하기：지능형 실행   |  코드 생성|  사용자 지정 수식 생성  |  데이터 분석 및 차트 생성|  향상된 함수 호출…
인기 기능:찾기， 강조 표시 또는 중복 표시   |  빈 행 삭제   |  데이터 손실 없이 열 결합 또는 셀 제거   |  공식을 사용하지 않는 반올림...
슈퍼 LOOKUP:다중 조건 VLookup  |  다중 값 VLookup  |   여러 시트에서 VLookup   |   퍼지 매치....
고급 드롭다운 목록:드롭다운 목록 빠르게 생성   |  종속형 드롭다운 목록   |  다중 선택 드롭다운 목록....
열 관리자:특정 수의 열 추가|열 이동|숨겨진 열의 표시 상태 전환|범위 및 열 비교...
주요 기능:그리드 포커스   |  디자인 보기   |향상된 수식 표시줄   | 워크북 및 시트 관리자   |  자원 라이브러리(자동 텍스트)|  날짜 선택기   |  워크시트 병합  |  암호화/셀 해독   | 목록으로 이메일 보내기   |  슈퍼 필터   |   특수 필터(굵은 글꼴이 있는 셀 필터링/기울임꼴/취소선。。。) 。。。
상위 15 도구 모음:12 텍스트도구(텍스트 추가,특정 문자 삭제, ...)|   50+차트유형(간트 차트, ...)|   40+ 실용적인수식(생일을 기준으로 나이 계산, ...)|   19 삽입도구(QR 코드 삽입,경로에서 그림 삽입, ...)|   12 변환도구(단어로 변환하기,환율 변환, ...)|   7 병합 및 분할도구(고급 행 병합,셀 분할, ...)|그 외 더 많은 기능
Kutools 를 선호하는 언어로 사용하세요 – 영어， 스페인어， 독일어， 프랑스어， 중국어 및 40+개 이상의 언어를 지원합니다！

Kutools for Excel 로 Excel 역량을 한 단계 업그레이드하고 전례 없는 효율성을 경험하세요。Kutools for Excel 는 생산성과 저장 시간을 향상시키는 300 개 이상의 고급 기능을 제공합니다。가장 필요한 기능을 지금 바로 확인하세요。。。

Office Tab 가 Office 에 탭 인터페이스를 제공하여 작업을 훨씬 쉽게 만들어 줍니다

  • Word, Excel, PowerPoint 에서 탭 기반 편집 및 읽기 기능을 활성화합니다, Publisher, Access, Visio 및 Project 에서도 사용 가능합니다。
  • 새 창이 아닌 동일한 창의 새 탭에서 여러 문서를 열고 생성할 수 있습니다。
  • 50% 만큼 생산성을 높이고 매일 수백 번의 마우스 클릭을 줄여줍니다！

모든 Kutools 애드인。 하나의 설치 프로그램

Kutools for Office스위트 번들은 Excel, Word, Outlook 및 PowerPoint 용 애드인과 Office Tab Pro 를 포함하며， 다양한 Office 앱을 사용하는 팀에 이상적입니다。

ExcelWordOutlookTabsPowerPoint
  • 올인원 스위트— Excel， Word， Outlook 및 PowerPoint 애드인 + Office Tab Pro
  • 하나의 설치 프로그램， 하나의 라이선스— 몇 분 안에 설정 완료(MSI 지원)
  • 함께 사용할수록 더 효과적입니다— Office 앱 전반에서 생산성 향상
  • 30 일간 모든 기능 무료 체험— 등록이나 신용카드 필요 없음
  • 최고의 가성비— 개별 애드인 구매 대비 절약