AVERAGEIFS로 여러 조건에 따라 평균 내기

AVERAGEIFS는 여러 조건을 동시에 충족하는 값의 평균을 계산합니다. 모든 판매액의 평균을 계산하는 대신, 1월 동부 지역 판매액의 평균은 얼마였을까요?와 같은 질문을 할 수 있습니다.

이 함수로 세 함수가 완성됩니다. SUMIFS는 합계를 계산하고, COUNTIFS는 개수를 세며, AVERAGEIFS는 평균을 계산합니다. 모두 동일한 여러 조건 스타일을 사용합니다. 하나를 알고 있다면 거의 모두 아는 셈입니다.

AVERAGEIFS는 SUMIFS와 정확히 같은 구조입니다. 평균을 낼 값이 먼저 오고, 그다음에 조건 쌍이 옵니다.

• average_range — 평균을 낼 숫자
• criteria_range1, criteria1
• criteria_range2, criteria2

내부적으로는 조건에 맞는 값을 모두 더한 다음, 일치한 항목 수로 나눕니다. 즉, 하나의 깔끔한 함수로 SUMIFS를 COUNTIFS로 나눈 것과 같은 결과를 냅니다.

A열이 지역, B열이 월, C열이 금액이라면 다음은 1월 동부 지역 판매액의 평균을 구하는 예입니다.

C:C의 금액을 평균 냅니다. 평균을 계산하기 전에 두 조건 쌍이 행을 동부와 1월에 해당하는 행으로 필터링합니다.

평균을 낼 값을 해당 값의 크기로 필터링할 수도 있습니다. 예를 들어 100을 초과하는 큰 동부 지역 판매액만 평균 내고 싶다고 가정해 보겠습니다.

이 경우 C열은 평균 범위와 조건 범위로 모두 사용됩니다. AVERAGEIFS는 같은 열을 두 역할에 재사용해도 문제없이 처리합니다.

사용자가 직접 조작할 수 있는 보고서를 만들려면 값을 직접 입력하는 대신 셀을 참조합니다. F1에 지역을 입력하고 F2에 최소 금액을 입력합니다.

비교할 때는 연산자를 셀에 연결합니다. ">"&F2는 F2에 들어 있는 값보다 큰 값을 의미합니다. 지역처럼 일반 텍스트를 일치시키는 경우에는 연산자 없이 셀 참조만 사용하면 됩니다.

AVERAGEIFS에서 가장 주의해야 할 점은 모든 조건에 맞는 행이 하나도 없을 때입니다. 평균을 낼 값이 없으므로 #DIV/0! 오류를 반환합니다.

이는 SUMIFS(0을 반환)와 COUNTIFS(0을 반환)의 동작과 다릅니다. 항목이 0개일 때의 평균은 정의되지 않으므로 Excel은 임의의 값을 추측하는 대신 오류를 발생시킵니다.

아무것도 일치하지 않을 때 알기 쉬운 값을 표시하려면 수식을 IFERROR로 감싸십시오. 보기 좋지 않은 #DIV/0! 대신 사용자에게 대시나 메시지를 표시할 수 있습니다.

이렇게 하면 필터 조합에 데이터가 없을 때도 대시보드를 깔끔하게 유지할 수 있습니다. 필터링한 데이터를 평균 낼 때는 항상 빈 결과를 고려하십시오.

중요한 세부 사항이 있습니다. AVERAGEIFS는 평균 범위의 빈 셀을 무시합니다. 빈 셀은 합계나 나눗셈에 사용되는 항목 수에 포함되지 않습니다. 반면 0이 들어 있는 셀은 실제 숫자이므로 평균에 포함됩니다.

0이 누락된 데이터를 대신 표시하는 값이라면 평균을 낮추게 됩니다. 적절한 경우 C:C, "<>0"과 같은 조건을 추가하여 0을 제외하는 방법을 고려하십시오.

필요한 만큼 조건을 추가할 수 있습니다. D열이 판매 담당자라면 동부 지역에서 1월에 Maria가 판매한 항목의 평균 판매액을 구할 수 있습니다.

쌍을 하나씩 추가할 때마다 필터가 더 좁혀집니다. AVERAGEIFS는 AND 논리를 사용하므로 세 조건을 모두 통과한 행만 평균 계산에 포함됩니다.

두 함수의 인수 순서를 정확히 구분하십시오.

• AVERAGEIF: range, criteria, [average_range] — 먼저 검사 범위가 오고, 평균 범위는 선택 사항이며 마지막에 옵니다.
• AVERAGEIFS: average_range, range1, crit1, ... — 평균 범위가 항상 먼저 옵니다.

다른 함수와 마찬가지로 기본적으로 복수 조건을 지원하는 ...IFS 버전을 사용하면 SUM, COUNT, AVERAGE에서 일관된 패턴을 유지할 수 있습니다.

AVERAGEIFS는 나란히 비교하는 요약 보고서에 다양하게 활용됩니다. F열에 지역 이름을 아래로 나열한 다음, 조건 열을 고정하여 하나의 수식을 복사해 각 지역의 평균 판매액을 계산할 수 있습니다.

$C:$C를 평균 범위로, $A:$A를 조건 범위로, $F2를 상대적인 지역 셀로 사용하면 수식을 아래로 복사할 때 지역별 평균이 즉시 계산됩니다.

AVERAGEIFS가 자매 함수와 다르게 동작하는 이유를 떠올려 보십시오.

이제 여러 조건에 따라 값의 평균을 계산할 수 있습니다.

• AVERAGEIFS(average_range, range1, crit1, ...) — 평균 범위가 먼저 옵니다.
• 일치하는 항목이 없으면 #DIV/0!이 반환되므로 IFERROR로 대비하십시오.
• 빈 셀은 건너뛰지만 0은 포함됩니다. 필요하면 "<>0"으로 0을 제외하십시오.
• 동적인 숫자 한도에는 ">"&F1을 사용하십시오.

다음으로 이러한 조건 함수에서 날짜 범위를 처리하는 방법을 알아보겠습니다.