Skip to main content

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

엑셀에서 0 또는 오류를 무시한 중앙값을 어떻게 계산합니까?

Author Sun Last modified

엑셀에서의 많은 데이터 분석 작업에서, 정확한 중앙값 계산은 데이터 집합의 중심 경향성을 이해하는 데 필수적입니다. 그러나 때로는 데이터 집합에 0 또는 오류 값(예: #DIV/0!, #N/A등)이 포함되어 있어 간단한 중앙값 계산에 방해가 될 수 있습니다. 예를 들어, 표준 공식을 사용하면 =MEDIAN(range) 계산에 0이 포함되고 범위 내에 유효하지 않은 셀이 있으면 오류가 반환되어 잘못된 결과 또는 계산 실패로 이어질 수 있습니다. 아래 그림과 같이 말이죠.
A screenshot showing when calculating the median with zeros and errors included in the data range is needed

이 문제를 해결하기 위해 0 또는 오류를 제외하고 중앙값을 계산할 수 있는 여러 가지 솔루션이 있습니다. 이를 통해 분석의 정확성과 견고성을 보장할 수 있습니다. 이러한 솔루션은 설문지 데이터, 재무 보고서, 과학적 측정 등에서 의미 있는 결과를 얻기 위해 0 또는 오류를 제거해야 하는 다양한 시나리오에 적합합니다. 아래에서는 엑셀에서 사용 가능한 각 방법에 대한 실용적인 단계별 가이드를 제공하며, 직접 입력하는 공식부터 고급 자동화 기술까지 다양합니다.

0을 무시한 중앙값

오류를 무시한 중앙값

VBA: 0과 오류를 무시한 중앙값 (UDF)

Power Query: 0 또는 오류 필터링 후 중앙값

arrow blue right bubble 0을 무시한 중앙값

중앙값 계산에서 0을 고려하고 싶지 않은 경우 — 예를 들어, 누락된 값을 0으로 표현하는 경우 — 배열 공식을 사용하여 0을 제외할 수 있습니다. 이는 특히 0이 실제 측정값이 아닌 사용할 수 없는 데이터의 자리 표시자인 데이터 세트에서 유용합니다.

중앙값을 표시하려는 셀을 선택(예: C2)하고 다음 공식을 입력하세요:

=MEDIAN(IF(A2:A17<>0,A2:A17))

공식을 입력한 후 일반적인 Enter 대신 Ctrl + Shift + Enter 를 눌러 배열 공식으로 만드세요(수식 표시줄에서 공식 주변에 중괄호가 나타납니다). 이렇게 하면 A2:A17 범위에서 0이 아닌 값만 중앙값 계산에 포함됩니다. 스크린샷 참조:
A screenshot showing how to apply the median formula in Excel while ignoring zeros

팁:

  • 엑셀 365 또는 엑셀 2021 이상 버전을 사용 중이라면 동적 배열 지원 덕분에 Enter만 누르면 됩니다.
  • 범위에 최소 하나의 0이 아닌 숫자 값이 있는지 확인하세요. 그렇지 않으면 공식이 #NUM! 오류를 반환합니다.
  • 이 솔루션은 조사 응답, 비용 보고서 또는 판매 데이터에서 0을 분석에서 제외해야 하는 경우에 이상적입니다.

arrow blue right bubble 오류를 무시한 중앙값

#N/A, #DIV/0!, 또는 #VALUE!와 같은 오류 값은 표준 중앙값 함수가 오류를 반환하게 하여 데이터 분석을 중단시킬 수 있습니다. 이러한 오류를 제외하고 안전하게 중앙값을 계산하려면 다음 배열 공식을 사용할 수 있습니다.

결과를 표시하려는 셀을 선택하고 아래 공식을 입력하세요:

=MEDIAN(IF(ISNUMBER(F2:F17),F2:F17))

공식을 입력한 후 Ctrl + Shift + Enter 를 눌러주세요(엑셀 365/엑셀 2021 이상 버전을 사용 중이 아니라면, 이 버전은 동적 배열을 허용합니다). 이 공식은 F2:F17 범위에서 진짜 숫자만 포함하며 오류 셀은 완전히 무시합니다.
A screenshot showing how to apply the median formula in Excel while ignoring errors

팁 및 주의사항:

  • 모든 셀이 오류 값이라면 결과는 #NUM! 오류를 반환합니다 — 데이터에 최소 하나의 유효한 숫자가 포함되어 있는지 확인하세요.
  • 중첩 조건을 통해 배제 기준을 결합할 수 있습니다(예: 0과 오류를 모두 제외).
  • 이 공식은 가져온 데이터, 설문 결과 또는 부분적으로 또는 실패한 계산이 포함될 수 있는 재무제표를 다룰 때 특히 유용합니다.

arrow blue right bubble VBA: 0과 오류를 무시한 중앙값 (UDF)

빈번히 0과 오류를 무시하면서 중앙값을 계산해야 하거나, 수동으로 배열 공식을 입력하는 것을 피하고 싶다면 사용자 정의 VBA 함수(UDF)를 사용할 수 있습니다. 이 접근법은 모든 배제 기준을 캡슐화하고 기본 제공 함수처럼 사용할 수 있기 때문에 큰 데이터셋이나 자주 업데이트되는 데이터셋에 적합합니다.

UDF 설정 방법:

  1. 엑셀에서 개발 도구 탭을 클릭하세요. 해당 옵션이 보이지 않는다면 파일 > 옵션 > 리본 사용자 정의에서 활성화하세요.
  2. Visual Basic을 클릭하여 VBA 편집기를 엽니다.
  3. VBA 편집기에서 삽입 > 모듈을 클릭하여 새 모듈을 생성하세요.
  4. 다음 코드를 모듈에 복사하여 붙여넣으세요:
Function MedianIgnoreZeroError(rng As Range) As Variant
    Dim cell As Range
    Dim tempList() As Double
    Dim count As Integer
    
    count = 0
    On Error Resume Next
    xTitleId = "KutoolsforExcel"
    
    For Each cell In rng
        If IsNumeric(cell.Value) Then
            If cell.Value <> 0 And Not IsError(cell.Value) Then
                count = count + 1
                ReDim Preserve tempList(1 To count)
                tempList(count) = cell.Value
            End If
        End If
    Next cell
    
    On Error GoTo 0
    
    If count = 0 Then
        MedianIgnoreZeroError = CVErr(xlErrNum)
    Else
        MedianIgnoreZeroError = Application.WorksheetFunction.Median(tempList)
    End If
End Function

UDF 사용 방법:
엑셀로 돌아간 후, 단순히 공식을 입력하세요 =MedianIgnoreZeroError(A2:A17) (원하는 범위로 A2:A17 를 대체하세요). 배열 공식과 달리 Enter만 누르면 되며 Ctrl + Shift + Enter.

  • 가 필요 없습니다. 이 방법은 매우 큰 데이터셋에 효과적이며 배열 공식의 복잡성을 피하고 코드를 추가로 수정하여 다른 원치 않는 값을 무시하도록 적용할 수 있습니다.
  • 범위에 0 또는 오류만 포함되어 있다면 결과는 #NUM!
  • #NAME? 오류가 발생하면 VBA 매크로가 올바르게 설치되었는지, 그리고 엑셀 설정에서 매크로가 활성화되었는지 확인하세요.

arrow blue right bubble Power Query: 0 또는 오류 필터링 후 중앙값

Power Query는 엑셀에서 데이터를 가져오고 변환하며 분석하는 강력한 도구입니다. 특히 중앙값과 같은 계산을 수행하기 전에 큰 데이터셋을 정리하고 사전 처리하는 것이 목표일 때 유용합니다. Power Query를 사용하면 쉽게 0과 오류를 필터링하여 계산에 유효한 숫자만 남길 수 있습니다. 이 접근법은 소스 데이터가 정기적으로 업데이트되거나 외부 시스템에서 가져오는 경우 특히 유익합니다.

0 및 오류를 무시한 중앙값 계산을 위한 Power Query 사용 단계:

  1. 데이터 범위 내의 아무 셀이나 선택한 후, 데이터 탭으로 이동하여 표/범위에서 가져오기를 클릭하세요. 데이터가 표 형식이 아닌 경우, 엑셀에서 표를 만들 것을 요청할 것입니다 — 확인을 클릭하세요.
  2. Power Query 편집기 창이 열립니다. 관련 열의 드롭다운 화살표를 클릭하고 0의 체크를 해제하여 0 값을 필터링하세요. (오류 필터링의 경우, 열 머리글을 마우스 오른쪽 버튼으로 클릭하고 오류 제거를 선택하세요.)
  3. 필터링이 완료되면 홈 > 닫기 및 로드를 클릭하여 정리된 데이터를 다시 워크시트로 전송하세요.
  4. 이제 정리된 값만 포함된 열에 표준 =MEDIAN() 공식을 적용하세요. 이제 데이터에는 모든 불필요한 항목이 제외되었습니다.

이 방법은 원본 데이터를 변경하지 않고도 새로운 또는 업데이트된 데이터와 함께 강력한 반복성을 제공하며, 특히 주기적인 보고 작업이나 큰 데이터 또는 외부 데이터로 작업할 때 효과적입니다. Power Query 워크플로우는 소스 데이터가 변경될 때마다 한 번의 클릭으로 새로 고칠 수 있으며, 수작업 개입과 오류 위험을 최소화합니다.

  • Power Query는 엑셀 2016 이상 버전에서 사용 가능합니다(또는 엑셀 2010 및 2013의 경우 플러그인으로 사용 가능).
  • 변환 후 정리된 데이터에 대해 계산을 수행하여 더 신뢰할 수 있는 후속 분석을 제공합니다.

예상치 못한 결과가 발생하면 Power Query의 필터링 단계를 다시 확인하고 정리된 데이터에 유효한 숫자 값이 남아 있는지 확인하세요.

요약하면, 배열 공식을 직접 사용하거나 사용자 정의 VBA 솔루션을 만들어 자동화하거나, 더 큰 워크플로우 자동화를 위해 Power Query를 활용하더라도 엑셀에서는 0 또는 오류를 무시하면서 중앙값을 계산하는 여러 실용적인 옵션을 제공합니다. 데이터 크기, 업데이트 빈도 및 워크플로우 선호도에 맞는 방법을 선택하여 신뢰할 수 있고 정확한 결과를 얻으세요.

최고의 오피스 생산성 도구

🤖 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일 모든 기능 사용 가능 — 회원가입/카드 불필요
  • 최고의 가성비 — 개별 추가 기능 구매 대비 절약