실무에서 많이 활용하는 엑셀 함수 중 하나인 AVERAGEIF 함수
엑셀도 그렇고 개발도 그렇고 IF 와 관련된 함수는 기본 중에 기본인 것 같다.
AVERAGEIF 함수는 내가 설정한 조건에 맞는 값들만 추려서 그 값들의 평균을 구하는 함수이고,
조건을 여러 개인 다중 조건을 설정하고 싶다면 AVERAGEIFS 함수를 활용할 수도 있다.
오늘은 AVERAGEIF 함수 사용 방법과 예제를 공유해보려고 한다!
[목차여기]
엑셀 AVERAGEIF 함수
엑셀 AVERAGEIF 함수는 내가 설정한 범위 안에서 내가 지정한 조건을 만족하는 값의 평균을 계산해주는 함수이다.
AVERAGE 함수가 범위만 지정해주면 그 범위의 값들을 평균 내주었다면,
IF가 붙음으로써 조건을 추가로 설정할 수 있다는 것으로 이해하면 쉽다.
=AVERAGEIF(조건범위, 조건, 평균 적용할 범위)
=AVERAGEIF(range, criteria, [average_range])
- 조건범위 : 조건을 적용할 셀의 범위
- 조건 : 숫자, 식 또는 텍스트 형식의 조건으로 평균을 구할 셀을 정의
- 평균 적용할 범위 : 평균을 구하는데 실제 사용할 셀, 입력하지 않으면 조건범위의 셀이 사용됨
조건범위와 평균 적용할 범위를 헷갈려할 수도 있다.
조건범위와 평균 적용할 범위가 같으면 문제 없겠지만 다른 경우가 있을 수 있기 때문!
이 부분은 예제를 보면서 쉽게 이해해보자
AVERAGEIF 함수 예제
기본 예제
아래 예제를 보면 날짜별로 내가 마신 커피 메뉴, 어느 지역에서 마셨는지와 가격이 나와있다.
실무에서는 훨씬 많은 데이터를 다루고 그 안에서 조건을 통해 결과를 반환하겠지만,
쉽게 이해를 돕기 위해서 간단하게 구성해보았다.
첫 번째 내가 구하고 싶은 건 데이터들 중에 아이스아메리카노의 평균 가격이다.
메뉴에 아이스아메리카노 외에도 다른 커피 메뉴들이 많기 때문에 아이스아메리카노만 골라서 가격의 평균을 내야한다.
"메뉴가 아이스아메리카노인 것" 이 조건이 되기 때문에 조건 범위는 메뉴에 해당하는 컬럼이다.
=AVERAGEIF(B3:B14
이후에는 조건이 들어가야 한다.
선택한 조건범위 중 값이 "아이스아메리카노" 인 것만 골라내야하기 때문에 조건은 "아이스아메리카노" 라고 선택하면 된다.
아이스아메리카노가 적힌 셀을 참조해도 무방하다.
=AVERAGEIF(B3:B14, "아이스아메리카노"
마지막으로는 실제로 평균을 낼 범위를 지정해주어야 한다.
나의 조건이 아이스아메리카노만 고르는 것이었기 때문에 조건범위는 메뉴가 되었으나,
결국 내가 최종적으로 원하는건 조건에 맞는 항목들의 가격의 평균이기 때문에
평균을 낼 범위는 가격 컬럼이 된다.
=AVERAGEIF(B3:B14, "아이스아메리카노", D3:D14)
응용 예제
다음은 지역을 조건으로 하여 서울 지역과 대구 지역의 커피 가격 평균을 비교해보자.
하지만 지역 컬럼의 데이터들은 서울, 대구만 있는게 아니라 서울신사, 서울양재, 대구동성로 등 세부지역이 포함되어 있다.
이럴 때는 간단하게 * 하나로 해결할 수 있다.
앞에 서울이라는 단어만 있으면 뒤에 어떤 것이 와도 선택하겠다! 라는 것을
서울 단어 뒤에 *를 넣음으로써 만들어줄 수 있다.
"서울*" 이렇게 표기하면 서울 뒤에 어떤 글자가 있던 앞에 서울만 있으면 선택하겠다는 의미이다.
만약 "*서울*" 이렇게 앞 뒤로 *를 붙이면 앞 뒤에 어떤 글자가 있던 중간에 서울이라는 단어가 있으면 선택하겠다는 의미가 된다.
조건범위는 지역 컬럼인 C3:C14 가 될 것이고,
조건은 "서울*" 이 될 것이다.
평균을 적용할 범위는 역시 가격 컬럼이므로 D3:D14가 된다.
=AVERAGEIF(C3:C14, "서울*", D3:D14)
오류 유형
만약 조건을 "서울*" 이렇게 하지 않고 그냥 "서울" 이라고만 해서 참조할 값이 없다면 어떻게 될까?
참조할 값이 없으면 0을 평균을 낸다는 의미와 동일하기 때문에 오류값도 #DIV/0! 이 반환된다.
조건범위에 조건에 해당하는 것이 하나도 없는 경우에 이 오류가 발생한다.
엑셀 AVERAGEIFS 함수
만약 조건을 여러 개 걸고 싶다면 AVERAGEIFS 함수를 사용하면 된다.
조금 다른 점은 평균을 적용할 범위가 가장 앞에 온다는 것이고 선택값이 아니라 필수값이 된다.
AVERAGEIF 함수는 조건범위가 곧 평균을 적용할 범위가 될 수 있었기 때문이지만,
다중 조건인 AVERAGEIFS 함수는 조건범위가 2개 이상이기 때문에 명확하게 평균을 적용할 범위를 지정해주어야 한다.
=AVERAGEIFS(평균 적용할 범위, 조건범위1, 조건1, 조건범위2, 조건2, ...)
다중 조건은 기본적으로 AND 조건(모두 충족하는 것)이고,
OR 조건을 활용하고 싶을 경우에는 SUMIF나 COUNTIF 함수를 활용하여 계산할 수 있다.
AVERAGEIFS 함수 역시 이어서 간단한 예제로 확인해보겠다.
기본 예제
동일한 데이터에서 조건만 2가지로 늘어났다.
날짜가 2023년 10월 이후에 먹은 커피 중에서 서울에서 마신 커피의 평균 가격이다.
논리함수로 보면 AND 함수인데 AVERAGEIFS 함수의 다중조건은 기본적으로 AND 조건이기 때문에 굳이 AND 함수를 쓸 필요는 없다.
평균을 적용할 범위인 가격, D3:D14가 가장 먼저 나오고,
그 뒤에 첫 번째 조건인 2023년 10월 이후를 만들기 위해 조건범위는 날짜 컬럼, 조건은 2023년 10월 이후가 된다.
=AVERAGEIFS(D3:D14, A3:A14, ">=2023-10-01"
2023년 10월 이후라는 조건은 곧 2023년 10월 1일보다 크거나 같을 때라는 것과 동일하기 때문에
부등호를 활용하여 날짜를 계산해주면 된다.
이때 쌍따옴표("")를 꼭 함께 표기해주어야 하고,
조건범위인 A3:A14가 날짜가 아니라 문자로 되어 있으면 #DIV/0! 오류가 반환되니 잘 확인하도록 하자!
두 번째 조건은 서울에서 마신 커피이기 때문에 처음 예제와 동일하게 *를 활용하여 작성해주면 된다.
=AVERAGEIFS(D3:D14, A3:A14, ">=2023-10-01", C3:C14, "서울*")
그럼 첫 번째 조건인 2023년 10월 이후인 데이터와 두 번째 조건인 지역이 서울인 데이터,
두 조건을 모두 만족시키는 데이터에 대한 가격 평균을 결과값으로 반환해준다.
오늘은 조건에 맞는 평균을 구하는 AVERAGEIF 함수와,
조건이 여러개일 경우 활용할 수 있는 AVERAGEIFS 함수 사용 방법과 예제를 알아보았다.
모두 실무에서 잘 응용해서 활용하시길!!
'Excel 함수' 카테고리의 다른 글
| 두 범위값들의 상관계수를 엑셀 CORREL 함수로 구하는 방법 (1) | 2024.01.08 |
|---|---|
| 엑셀 LEFT, RIGHT, MID 함수로 원하는 텍스트 추출하거나 제거하는 방법 (32) | 2024.01.06 |
| 엑셀에서도 선형회귀를 활용하여 예측을 할 수 있을까? FORECAST.LINEAR 함수 알아보기 (24) | 2024.01.04 |
| 엑셀 데이터베이스 함수 DSUM 함수, 조건에 맞는 값들의 합계 구하기 (17) | 2024.01.02 |
| 엑셀 곱한값끼리 더해주는 다중조건 수식 SUMPRODUCT 함수 활용 방법! (1) | 2024.01.02 |
