AVERAGEIF로 조건에 따라 평균 내기

SUMIF로 합계를 구하고 COUNTIF로 개수를 셌습니다. 이 계열의 세 번째 함수인 AVERAGEIF는 조건을 만족하는 값만 골라 평균을 계산합니다.

모든 판매액의 평균을 내는 대신 동부 지역의 판매액만 평균 내거나, 50보다 높은 점수만 평균 낼 수 있습니다. 필터링과 평균 계산을 한 번에 깔끔하게 처리합니다.

AVERAGEIF는 SUMIF와 같은 세 부분 구조를 사용합니다.

• 범위 검사할 셀
• 조건 기준
• 평균 범위 평균을 낼 셀

범위를 살펴보고 일치하는 항목을 찾은 다음, 평균 범위에서 이에 대응하는 셀의 평균을 계산합니다.

지역이 A2:A10에 있고 금액이 B2:B10에 있다면, 동부 지역의 평균 판매액을 구할 때 A열에서 동부를 검사하고 B열에서 일치하는 값을 평균 냅니다.

내부적으로는 SUMIF를 COUNTIF로 나누는 작업을 하지만, AVERAGEIF는 이를 읽기 쉬운 하나의 수식으로 처리합니다.

검사하는 열과 평균을 내고 싶은 열이 같다면 세 번째 인수를 생략할 수 있습니다. B2:B10에서 70보다 높은 점수만 평균 내는 경우가 이에 해당합니다.

여기서 B열은 검사 대상이면서 평균을 내는 대상이기도 합니다. average_range를 생략하면 스프레드시트는 범위를 두 용도로 모두 사용합니다.

다른 IF 함수와 마찬가지로 유연하게 사용하려면 조건을 셀에 저장합니다. D1에 지역 이름이 있다면 조건에서 D1을 참조합니다.

이제 하나의 셀이 평균을 결정합니다. D1을 동부에서 서부로 바꾸면 즉시 다시 계산되므로, 요약표나 대시보드 선택기에 적합합니다.

AVERAGEIF도 다른 함수들과 마찬가지로 따옴표 안에 연산자를 사용할 수 있습니다. 100 이상인 판매액만 평균 내려면 다음과 같이 합니다.

• ">=100" 100 이상
• "<50" 50 미만
• "<>0" 0 제외

마지막 조건은 0인 항목을 무시하면서 평균을 계산하므로 특히 유용합니다. 0인 항목이 평균을 낮추는 것을 막을 수 있습니다.

셀의 기준값을 사용하려면 앰퍼샌드로 연산자와 셀을 연결합니다. D1에 기준값이 있다면 그보다 큰 모든 값을 평균 냅니다.

&는 ">"와 D1의 값을 사용해 조건 텍스트를 만듭니다. SUMIF와 COUNTIF에서 사용한 것과 같은 연결 방법입니다.

AVERAGEIF에는 다른 함수에는 없는 함정이 하나 있습니다. 일치하는 행이 없으면 나눌 대상이 없어 #DIV/0! 오류가 발생합니다.

예를 들어 데이터에 존재하지 않는 지역의 평균을 구하면 이 오류가 반환됩니다. 수식이 잘못되었다는 뜻이 아니라, 필터와 일치하는 셀이 0개라는 뜻입니다.

일치하는 항목이 없을 때 #DIV/0! 대신 알기 쉬운 메시지를 표시하려면 수식을 IFERROR로 감쌉니다.

이제 일치하는 지역이 없으면 불안감을 주는 오류 대신 데이터 없음이라는 텍스트가 표시됩니다. 일부 범주가 없어도 보고서를 깔끔하게 유지할 수 있습니다.

AVERAGEIF는 일치하는 셀 중 숫자가 들어 있는 셀만 평균 냅니다. 평균 범위에서 실제로 비어 있는 셀은 무시되므로 0으로 계산되지 않습니다.

이는 중요합니다. 값이 누락되어도 숫자 0이 직접 입력된 경우처럼 평균이 0에 가까워지지 않습니다. 0도 제외하려면 값 열에 "<>0" 조건을 추가하세요.

깔끔한 요약표를 만들어 보겠습니다. D2, D3, D4에 각 범주를 입력한 다음, 레이블 셀을 참조하는 AVERAGEIF 하나를 작성하고 아래로 채웁니다.

이제 각 행에 해당 범주의 평균값이 표시됩니다. 이전 레슨에서 구한 SUMIF 합계 및 COUNTIF 개수와 나란히 표시할 수 있습니다. 이 셋을 함께 사용하면 수식만으로 완성되는 간결한 보고서가 됩니다.

AVERAGEIF의 작동 방식에 대한 이해도를 확인해 보세요.

이제 AVERAGEIF를 사용해 조건에 따라 평균을 계산할 수 있습니다. 핵심 내용은 다음과 같습니다.

• 순서는 범위, 조건, 평균 범위입니다. 마지막 인수를 생략하면 검사 범위 자체의 평균을 계산합니다.
• 텍스트, 숫자, ">=100"과 같은 연산자를 지원합니다.
• 일치하는 항목이 없으면 #DIV/0! 오류가 발생하므로 IFERROR로 처리하세요.
• 빈 셀은 건너뛰며 0으로 취급하지 않습니다.

다음에는 와일드카드를 사용해 텍스트의 일부와 일치시키는 방법을 알아봅니다.