엑셀 AverageIfs 함수 사용법

엑셀 함수 AverageIFs를 간략히 설명하면, 주어진 여러 조건이 전부 일치하는 값의 평균을 구하는 함수입니다. 사실 AveragaIF 대비 얼마나 활용도가 높은 지는 모르겠지만, 그래도 가끔씩 사용할 일이 생기는 엑셀 함수인만큼 아래 내용을 통해 조금 더 자세히 알아보도록 하겠습니다.

 

AverageIFs 함수 사용법

 

엑셀 AverageIFs 함수 사용법

 

1. 엑셀 함수 AverageIFs 구조

AverageIFs (평균 범위, 조건 범위#1, 조건#1, 조건 범위#2, 조건 #2...)

 

• 평균 범위 : 실제 평균값을 구할 대상이 있는 범위
• 조건 범위#1 ~... : 조건을 찾을 범위로 복수 사용이 가능
• 조건 #1 ~... : 조건 범위에서 찾을 조건으로 복수 사용이 가능..

주의할 사항은 조건 범위와 조건은 조건 개수가 일치해야 된다는 점입니다.

 

2. 엑셀 함수 AVERAGEIFs 예제

 

수식 : =AVERAGEIFS($E$3:$E$18,  $B$3:$B$18,  "#2제품",  $C$3:$C$18,  "창고#2")

결과 : 651.5

 

• 첫 번째 조건 범위 $B$3:$B$18에서 "#2제품"과 일치하는 셀 전체를 찾고, 두 번째 조건 범위 $C$3:$C$18에서도 "창고#2"와 일치하는 모든 조건을 찾습니다.
• 이렇게 두 조건이 일치하는 행 값은 9행, 10행이기 때문에, 평균 범위 $E$3:$E$18의 9행,10행인 E9, E10셀의 평균을 구하게 됩니다.

 

 

수식 : =AVERAGEIFS($E$3:$E$18,  $C$3:$C$18,  "창고#2",  $D$3:$D$18,  ">2019-07-01")

결과 : 1052.666667

 

• 조건 범위 $C$3:$C$18에서 조건 "창고#1"과 일치하는 값을 찾은 후 두 번째 조건 범위 $D$3:$D$18에서 조건 ">2019-07-04"와 일치하는 값 모두를 찾습니다.
• 두 조건이 일치하는 행(줄)은 9행, 10행, 13행입니다
• 평균을 구할 범위 $E$3:$E$18에서 9행(760), 10행(543), 13행(1855)의 평균을 구합니다.
• 참고로 부등호를 입력할 때 " "를 빼먹지 않도록 주의가 필요합니다.

 

수식 : =AVERAGEIFS($E$3:$E$18,  $E$3:$E$18, ">1300", $E$3:$E$18, "<1700")

결과 : 1561.5

 

• 이번엔 AND 조건과 비슷한 조건 형식입니다.
• 두 개의 동일한 조건 범위 $E$3:$E$18에서, 조건 ">1300"과 "<1700"이 일치하는 값을 전부 찾습니다. 이 조건으로 찾는 값은 1301 ~ 1699의 숫자입니다.
• 이렇게 찾아진 행은 5행, 7행이며, 평균 범위 $E$3:$E$18에서 5행(1642)과 7행(1481)의 평균을 구합니다.

 

수식 : =AVERAGEIFS($E$3:$E$18, $C$3:$C$18,  "창고#2",  $D$3:$D$18, ">2019-06-30",  $E$3:$E$18,  ">1100")

결과 : 1688

 

• 첫 번째 조건 범위 $C$3:$C$18에서 "창고#2"와 일치 값을 찾습니다.
• 두 번째 조건 범위 $D$3:$D$18에서 조건 ">2019-06-30"와 일치하는 값을 찾습니다.
• 마지막 조건 범위 $E$3:$E$18에서 조건 ">1100"와 일치하는 값을 찾습니다.
• 이렇게 3가지 조건이 일치하는 행은 7행, 13행이고, 평균 범위의 E7(1481)과 E13(1855) 셀의 평균을 구합니다.

 

 

 

3. 엑셀 함수 AverageIfs 와일드카드 사용하기

AverageIFs에서 사용 가능한 와일드카드는 " * ", " ? " 로 2가지를 사용할 수 있습니다

• *: * 위치부터 뒷 내용은 조건이 만족하는 것으로 판단합니다
• ?: ? 는 1개의 문자에 대해 조건 만족으로 판단합니다
• ~* : * 문자로 사용하는 방법입니다
• ~? : ? 문자로 사용하는 방법입니다.

 

수식 : =AVERAGEIFS($E$3:$E$16, $B$3:$B$16, "#1*", $C$3:$C$16, "*#1")

결과 : 1240.666667

 

• 조건 범위 $B$3:$B$16와 $C$3:$C$16에서 각각 "#1*", "*#1"에 해당되는 조건을 찾습니다
• "#1*"는 #1123ADB 이든 "#1 제품"이든 일치하는 것으로 판단됩니다.
• "*#1"은 123AAAV#1 이든 "창고#1"이든 일치하는 것으로 판단됩니다.
• 따라서 "#1제품"과 "창고#1"이 만족하는 3행(914), 4행(1166), 5행(1642) 값의 평균을 구합니다.

 

수식 : =AVERAGEIFS($E$3:$E$16, $B$3:$B$16, "#2??", $C$3:$C$16, "????")

결과 : 969.333333

 

• 두 가지 조건 범위 $B$3:$B$16와 $C$3:$C$16에서 각각 "#2??"와 "????"에 대한 일치하는 조건을 찾습니다
• "#2??"는 #2AA, #211 등 문자 수가 같으면 일치하는 것으로 판단하며, 위 예제 기준 #2제품이 해당됩니다.
• ???? 도 문자 수가 같으면 조건 일치하는 것으로 판단하며, 위 예제 기준 $B$3:$B$16 범위의 모든 내용이 해당됩니다.
• 두 조건이 일치하는 행은 7행, 8행, 9행이기 때문에, 평균 대상 범위 $E$3:$E$16의 E7(1481), E8(543), E9(884)의 평균을 구하게 됩니다.