AVERAGEIF · AVERAGEIFS로 조건부 평균 끝! (이상치 제외·가중 평균·다중 조건 1분 컷)
가격대별 평균, 지역·상품 조합 평균, 0/빈값 제외 평균… AVERAGEIF/AVERAGEIFS로 바로 계산하세요. 가중 평균·이상치 제거까지 실무 팁을 담았습니다.
기본 문법 & 차이
| 함수 | 형식 | 포인트 |
|---|---|---|
| AVERAGEIF | =AVERAGEIF(range, criteria, [average_range]) | 조건 1개, average_range 생략 시 range 평균 |
| AVERAGEIFS | =AVERAGEIFS(average_range, criteria_range1, criteria1, ...) | 합계가 아닌 평균 버전, 조건 페어 나열 |
TIP: 범위 크기를 동일하게, 표시 포맷은 숫자/날짜 형식을 명확히.
단일 조건 — AVERAGEIF
예제 1) 카테고리=”Keyboard”의 평균 단가
=AVERAGEIF(A2:A100, "Keyboard", C2:C100)예제 2) 0 제외 평균
=AVERAGEIF(C2:C100, "<>0")예제 3) 빈셀과 0 모두 제외
=AVERAGEIFS(C2:C100, C2:C100, "<>", C2:C100, "<>0")다중 조건 — AVERAGEIFS
예제 4) 지역=Seoul AND 상품=Keyboard
=AVERAGEIFS(C2:C100, A2:A100,"Keyboard", B2:B100,"Seoul")예제 5) OR 조건(Keyboard 또는 Mouse)
=AVERAGE( FILTER(C2:C100, (A2:A100="Keyboard") + (A2:A100="Mouse") ) ) // 365구버전은 두 AVERAGEIFS의 결과를 AVERAGE로 결합하거나 SUM/COUNT로 직접 계산하세요.
날짜 범위 평균
예제 6) 2025-08-01 ~ 2025-08-31 평균 매출
=AVERAGEIFS(C2:C100, B2:B100, ">="&DATE(2025,8,1), B2:B100, "<="&DATE(2025,8,31))예제 7) 기준월(F2)의 평균
=AVERAGEIFS(C:C, B:B, ">="&EOMONTH(F2,-1)+1, B:B, "<="&EOMONTH(F2,0))텍스트 포함·제외·와일드카드
예제 8) "Pro" 포함 제품의 평균
=AVERAGEIFS(C2:C100, A2:A100, "*Pro*")예제 9) "Refurbished" 제외
=AVERAGEIFS(C2:C100, D2:D100, "<>*Refurbished*")가중 평균(SUMPRODUCT)
수량을 가중치로 쓰는 가중 평균은 AVERAGEIF/S 대신 SUMPRODUCT가 정확합니다.
예제 10) 지역=Seoul의 단가 가중 평균(가중치=수량)
=SUMPRODUCT( 단가*수량*(지역="Seoul") ) / SUMPRODUCT( 수량*(지역="Seoul") )예제 11) 다중 조건 가중 평균
=SUMPRODUCT(단가*수량*(지역="Seoul")*(상품="Keyboard"))
/SUMPRODUCT(수량*(지역="Seoul")*(상품="Keyboard"))이상치 제거(TRIMMEAN)
극단값이 평균을 왜곡하면 TRIMMEAN으로 상·하위 p%를 제거합니다.
예제 12) 상·하위 10% 제거 평균
=TRIMMEAN(C2:C100, 0.10)조건부 + 이상치 제거는 먼저 FILTER로 조건을 걸고, 그 결과에 TRIMMEAN을 적용합니다(365).
=TRIMMEAN( FILTER(C2:C100, A2:A100="Keyboard"), 0.10 )실수·성능·정확도 체크
- 텍스트 숫자 → 왼쪽 정렬이면 텍스트일 수 있음.
VALUE변환 또는 원천 형식 통일. - 분모 0 → 조건을 만족하는 값이 없으면
#DIV/0!.IFERROR(수식,"없음")처리. - 0과 빈셀 → 평균에서 다르게 취급됩니다. 필요 시
AVERAGEIFS(범위,범위,"<>",범위,"<>0"). - 범위 크기 불일치 → 모든 범위 행·열 수 동일.
- 성능 → 테이블 참조,
LET으로 중복 계산 캐싱.
요약 정리
| 목표 | 대표 공식 |
|---|---|
| 단일 조건 평균 | AVERAGEIF(범위,조건,[평균범위]) |
| 다중 조건 평균 | AVERAGEIFS(평균범위, 범위1,조건1, ...) |
| 가중 평균 | SUMPRODUCT(값*가중치*조건)/SUMPRODUCT(가중치*조건) |
| 이상치 제거 | TRIMMEAN(범위, 비율) 또는 TRIMMEAN(FILTER(...), 비율) |
FAQ
OR 조건만으로 평균이 필요하면?
365는 AVERAGE(FILTER(값,(조건1)+(조건2)))가 간단합니다. 구버전은 SUM/COUNT 분리 또는 두 AVERAGEIFS의 평균을 취하세요.
빈셀을 0으로 간주하고 평균을 낼 수 있나요?
기본 평균은 빈셀을 제외합니다. 빈셀을 0으로 보고 싶다면 IF로 0으로 바꾼 보조열을 만든 뒤 평균을 구하세요.