오른쪽부터 3개의 평균 구하기

1. 문제

 

아래와 같이 회차별 점수가 있을 때

오른쪽부터 3개의 평균만을 구하려고 합니다.

 

2. 관련된 함수

해법 1은 Index와 Large 함수가 필요하고,

해법 2는 Take, Filter 함수가 필요합니다.

 

각각의 함수의 구문에 대해 알아보겠습니다.

 

가. Index 함수

참조형과 배열형이 있는데,

배열형을 사용하면 되며,

 

배열형의 구문은

INDEX(array, row_num, [column_num])로서,

 

배열 array에서 행 수와 열 수에 해당하는 값을 가져오는 것입니다.

 

나. Large 함수

구문은 LARGE(array,k)로서

array는 배열, k는 순번입니다.

 

다. Take 함수

구문은

=TAKE(array, rows,[columns])로서

 

첫 번째 인수는 array 배열이고, rows는 가져올 행 수, columns는 가져올 열 수입니다.

 

라. Filter 함수

구문은

=FILTER(array,include,[if_empty])로서

 

array는 필터를 적용할 범위,

include는 필터 조건식,

if_empty는 값이 없을 경우의 값, 다시 말해 if_errror일 때의 값과 같습니다.

 

3. 해법 1

Index와 Large함수를 이용해서 일정한 범위 내에서 큰 열의 값 순으로 3개를 가져오면 됩니다.

따라서, Large함수의 k값을 배열로 사용해야 하며,

배열로 사용할 때는 중괄호를 이용해서 {1,2,3}식으로 입력합니다.

 

또한 1은 column(a2)이므로

column(a2:c2)라고 입력할 수 있습니다.

 

Large함수를 이용해서는 제일 큰 열부터 작은 열순으로 3개 값을 가져오는데,

0보다 큰 값만 가져와야 하므로

N2셀의 수식은

=large((c2:g2>0)*column(c2:g2),column(a2:c2))

가 됩니다.

 

그러면 7,5,3이 반환되는데, 7번째 열, 5번째 열, 세 번째 열이라는 의미입니다.

 

이제 Index 함수를 이용해 A2셀에서 G2셀 중 7,5,3번째 값을 가져오면 됩니다.

=index(a2:g2,large((c2:g2>0)*column(c2:g2),column(a2:c2)))

열 순서이므로 a2:g2,,large((c2:g2>0)*column(c2:g2),column(a2:c2))라고

행 수는 건너뛰어야 하지만, 행 수란에 열 수를 넣어도 됩니다.

오른쪽부터 10, 20, 50을 가져왔습니다.

 

이제 Aveage함수를 추가하고, 에러일 경우를 대비해서 ""으로 처리하는 구문을 추가하면 됩니다.

=iferror(average(index(a2:g2,large((c2:g2>0)*column(c2:g2),column(a2:c2)))),"")

 

그런데 3개 이상일 때는 문제가 없는데, 한 개이거나 점수가 없을 때는 이상한 숫자가 나옵니다.

 

수식 탭에서 수식 계산을 해보니

Large 함수에서 첫 번째부터 세 번째까지의 열 번호가 7,0,0이다 보니 (20+4+4)/3=9.33333

 

따라서, 0보다 큰 숫자가 3개 이상이면 위 수식대로 하고,

아니면 average(c5:g5)로 해야 합니다.

 

수정한 수식은

=IFERROR(IF(COUNT(A5:G5)>=3,AVERAGE(INDEX(A5:G5,LARGE((C5:G5>0)*COLUMN(C5:G5),COLUMN(A5:C5)))),AVERAGE(C5:G5)),"")입니다.

그리고, H5셀의 수식을 H2셀부터 H7셀까지 복사하면 모두 문제가 없이 잘 계산됩니다.

 

 

4. 해법 2

위와 같이 복잡한 수식을 Take와 Filter 함수를 이용해 오른쪽 열부터 3개를 가져오도록 하면 쉽게 해결됩니다.

수식은

=AVERAGE(TAKE(FILTER(C2:G2,C2:G2>0),,-3))

입니다.

Take함수의 인수중 행 수는 입력하지 않고, 열 수를 -3으로 입력해서 오른쪽부터 3개를 가져오도록 한 후 평균을 구하는 것입니다.

 

그런데 값이 하나도 없을 경우 #CALC! 에러가 발생하므로

=다음에 IfError(를 추가하고 에러일 때 값을 ""로 하면 됩니다.

 

수정된 수식은

=IFERROR(AVERAGE(TAKE(FILTER(C2:G2,C2:G2>0),,-3)),"")

입니다.

역시 새로운 함수를 이용한 수식이 훨씬 깔끔합니다.

 

오른쪽부터 세개 평균 구하기 (완성).xlsx

0.01MB