AVERAGE, AVERAGEIF, AVERAGEIFS 함수 파헤치기

AVERAGE, AVERAGEIF, AVERAGEIFS 함수 파헤치기

 

AVERAGE, AVERAGEIF, AVERAGEIFS 함수는 사용법이 SUM, SUMIF, SUMIFS 함수와 유사하며, SUM 계열 함수는 조건에 맞는 데이터의 합을 구했다면 AVERAGE 계열 함수는 평균을 구하는 차이만 있다고 이해하면 됩니다.

 

따라서 AVERAGE 계열 함수는 구조나 개념보다는 바로 예시를 통해 함수의 사용법을 알아보도록 하겠습니다.

 

 

1. AVERAGE 함수 예제

 

예제

주어진 표에 제시된 5명의 평균 키를 구하는 문제로 수식은 간단하게 AVERAGE(키 범위)로 작성하여 구할 수 있습니다.

예제 풀이

SUM 함수와 사용법이 동일하며, 수식 자체도 복잡하지 않아 쉽게 결과를 확인할 수 있습니다.

 

 

2. AVERAGEIFS 함수 예제(1)

 

AVERAGEIF 함수는 생략하고 AVERAGEIFS 함수 예제를 풀어보겠습니다.

예제1

예제의 내용은 품목별 금액이 제시된 표에서 품목이 지정되지 않은 부분의 평균 금액을 구하는 문제입니다.

 

이를 풀기 위해 조건을 먼저 확인하면, 조건은 '품목이 지정되지 않은'으로 볼 수 있습니다.

따라서 수식은 AVERAGEIFS(금액 범위,품목 범위,품목이 지정되지 않은 부분)으로 정리할 수 있으며, 이를 풀어보면 아래와 같이 나오는 것을 확인할 수 있습니다.

예제1 풀이

적용된 수식을 옮겨보면 AVERAGE(B3:B11,A3:A11,"")입니다.

 

B열과 A열의 범위는 각각 금액과 품목의 범위로 지정하였으며, 품목이 지정되지 않고 빈 셀로 되어있는 부분을 조건의 인수로 넣기 위해 큰 따옴표(") 두 개를 붙여 '빈칸'의 의미를 만들었습니다.

 

빈 셀의 금액이 각각 20,000원, 350,000원, 500,000원으로 평균을 구하면 290,000원이 되며 수식을 통해 구한 결과와 동일하게 나오는 것을 확인할 수 있습니다.

 

※ ""와 같이 넣으면 빈칸을 의미하며 "<>"는 아니다(같지 않다)를 의미합니다. 조건식에서 이와 같은 기호를 많이 사용하므로 알아두면 유용하게 사용할 수 있습니다.

 

 

3. AVERAGEIFS 함수 예제(2)

 

예제2

이번 예제는 모델 2 노트북의 평균 금액을 구하는 것입니다.

조건은 '노트북'과 '모델 2' 두 가지 이므로 AVERAGEIFS 함수를 통해 조건 범위와 조건을 설정하여 답을 구할 수 있습니다.

수식은 AVERAGEIFS(금액 범위,품목 범위,"노트북",모델 범위,"모델2")로 작성할 수 있으며, 아래와 같이 풀어볼 수 있습니다.

예제2 풀이

수식은 AVERAGEIFS(D3:D8,A3:A8,"노트북",C3:C8,"모델2")이며, 두 결과는 1,550,000원으로 조건에 만족하는 D4, D5셀의 금액인 1,100,000원과 2,000,000원의 평균과 동일함을 확인할 수 있습니다.

 

※ AVERAGEIFS 함수 사용 시 주의할 사항으로 SUMFIS나 COUNTIFS도 동일하지만 함수 안에 들어가는 조건은 모두 AND로 묶인다는 것입니다.

 

앞서 풀었던 예시에서 설명하면, 만약 노트북과 책상의 평균 금액을 구하고 싶다면? 아마도 먼저 떠올리기 쉬운 수식 작성 방법은 AVERAGE(금액 범위, 품목 범위,"노트북",품목 범위,"책상")일 것입니다.

하지만 위와 같이 수식을 작성하면 오류 값이 반환됩니다.

이유는 '조건은 모두 AND로 묶인다'는 설명을 되새기면 이해하기 쉬운데 바로 AVERAGEIFS 함수 안에 조건이 품목이 '노트북' 또는 '책상'으로 인식되는 것이 아니라 '노트북' 그리고(이면서) '책상'으로 인식하기 때문입니다.

품목은 '노트북'이거나, '휴대폰'이거나, '책상'일 수밖에 없으며, '노트북'이면서 동시에 '책상'인 품목은 없기 때문입니다.

따라서 노트북과 책상의 평균 금액을 구하고 싶다면 복잡하지만 아래와 같이 구해야 됩니다.

 

(SUMIFS(금액 범위,품목 범위,"노트북")+SUMIFS(금액 범위,품목 범위,"책상"))/(COUNTIFS(품목 범위,"노트북")+COUNTIFS(품목 범위,"책상"))

 

풀어서 설명하면 원하는 '조건 품목의 금액 합계/조건 품목의 개수'로 구하는 방법입니다.

 

다소 복잡하긴 하지만 동일한 범위에 적용되는 조건이 여러 가지로 나뉠 경우 해당 조건은 모두 AND로 인식하기 때문에 발생하는 문제이므로 유의하여 사용하시기 바랍니다.