[엑셀] 상/하위 몇% 의 평균 구하기

2017/07/13 - [엑셀] - [엑셀] 배열수식에 여러 개의 조건 사용하기 와

2017/07/14 - [엑셀] - [엑셀] 상/하위 몇 퍼센트에 해당하는 값 알아보기 percentile()

위 두 가지를 이용하면 상/하위 몇%의 값뿐 아니라, 그 구간에 해당하는 값의 평균도 구해볼 수 있다.

(AVERAGEIFS 등의 함수를 이용할 수도 있으나 여기서는 배열수식을 이용하기로 한다.)

다음과 같은 자료가 있다고 하자.

일단 하위 10%에 해당하는 값을 찾으려면 Percentile.INC 를 이용해야 할 것이다.

(2017/07/31 - [엑셀] - [엑셀] PERCENTILE INC와 EXC의 차이 참고)

345.3이라고 한다.

그러니 하위 10%에 해당하는 값은 317, 336, 337, 345 네 개가 될 것이다.

이 값들을 배열로 만들기 위해 다음과 같이 적는다.

A열의 10%에 해당하는 값보다 작은 행들의, A열 값들로 만든 배열

IF(A:A<PERCENTILE.INC(A:A,10%), A:A)

하지만 이대로 하면 A:A에 있는 빈 셀들이 0이라는 값으로 배열에 포함되어 제대로 평균이 나오지 않는다.

Average()나 Percentile()모두 알아서 빈셀을 제외하고 결과를 도출해 주나,

배열을 만들 때는 빈 셀이 0의 값으로 배열에 포함될 수 있으므로 주의한다.

번거롭지만, "0이 아닌"에 해당하는 조건을 추가해주자. 

조건이 2개 이상이므로 *(asterisk)를 사용한다.

(A열의 10%에 해당하는 값보다 작고)*(0은 아닌 행들의), A열 값들로 만든 배열

IF((A:A<PERCENTILE.INC(A:A,10%))*(A:A<>0), A:A)

배열만으로는 값이 나오지 않으므로 Average로 둘러싸 주자.

=AVERAGE(IF((A:A<PERCENTILE.INC(A:A,10%))*(A:A<>0),A:A))

배열수식이므로 Ctrl + Shift + Enter를 잊지 않는다.

하위 10%의 평균은 333.8이다.

이번에는 20%에 해당하는 백분위수를 구해보자.

394가 나왔다. 

하위 20%의 평균을 구하고 싶다면 아까와 같은 방법을 사용하면 되겠고,

여기서는 10% ~ 20% 구간에 해당하는 값들의 평균을 구해 보자.

10% 가 345.3, 20%가 394이므로 

10% ~ 20% 구간에 해당하는 값은 346, 363, 382 세 개가 된다.

배열수식을 이용하여 이 범위에 해당하는 배열을 만들자.

(A열의 10%에 해당하는 값보다는 크고)*(20%에 해당하는 값보다는 작은행들의), A열 값들로 만든 배열

IF((A:A>=PERCENTILE.INC(A:A,10%))*(A:A<PERCENTILE.INC(A:A,20%)), A:A)

이 경우 345.3이나 394에 해당하는 값이 데이터에 없으므로 이상과 초과, 이하와 미만이 동일하지만

공교롭게 데이터에 있는 값이 나올 수도 있으므로 원칙을 나름 정해 두면 좋다. 

여기서는 낮은 값 이상, 높은 값 미만으로 정해주었다.

배열만으로는 값이 나오지 않으므로 Average()로 둘러싸 주자.

=AVERAGE(IF((A:A>=PERCENTILE.INC(A:A,10%))*(A:A<PERCENTILE.INC(A:A,20%)),A:A))

마지막으로 Ctrl + Shift + Enter를 잊지 않는다.

346, 363, 382 세 값의 평균이 나온다.

10% 구간별로 같은 작업을 반복하려면 일일이 써주기보다 다음과 같이 하는 편이 낫다.

애초 수식에서 10%와 20%라는 값에 해당하는 자리가, C열의 셀들을 참조하도록 바뀌었다.

이제 아래로 복사해 주면

의도한대로 평균이 잘 나오는 것을 볼 수 있다.

유일한 예외는 10% 미만 구간에 해당하는 값인데, 앞서 하위 10%의 평균을 구할 때 나온 것처럼 수식을 적거나

=AVERAGE(IF((A:A<PERCENTILE.INC(A:A,10%))*(A:A<>0),A:A))

혹은 위에 0%를 하나 더 만들어 주어 0% ~ 10%로 인식하도록 해도 되겠다.

여기서 Percentile.INC(A:A,C1)은 A열의 0%를 의미하는데, 

Percentile이 판단하기에 A열의 0%는 0(빈셀)이 아니라 최소값인 317에 해당하므로 자연히 0을 제외하고 평균을 구하게 된다.

참고로 AVERAGEIFS로 10% ~ 20% 구간의 평균을 구할 때는 다음과 같은 수식을 사용한다.

=AVERAGEIFS(A:A, A:A,">="&PERCENTILE.INC(A:A,10%), A:A,"<"&PERCENTILE.INC(A:A,20%))

배열수식을 사용하는 것과 크게 다르지 않으나, 일단 원하는 값들로 배열을 만드는 것에 익숙해지면

AVERAGEIFS 같은 함수의 문법을 별도로 찾아보지 않아도 된다는 장점이 있다.