'offset'에 해당되는 글 1건

[Excel] 그룹별 평균, 표준편차, 최대값, 최소값 한번에 구하기

이번에는 평균, 표준편차, 최댓값, 최솟값을 한 번에 구하는 법을 소개하려고 합니다. 이것은 일반적으로 "AVERAGE", "STDEV.P", "MAX", "MIN"을 쓰면 된다고 생각하실 수 있는데, 해당 함수를 막상 쓰기에 어려울 때가 있습니다. 그리고 한 번에 구하기가 어려울 때가 있습니다. 그룹별로 포함된 수가 다르고 많은 경우 평균, 표준편차, 최댓값, 최솟값 등을 한 번에 구한다는 것은 쉽지 않습니다. 예를 들어 아래와 같은 상황입니다.

어떻게 해야 할지 고민하다 보면 평균을 구할 때 "AVERAGEIF" 함수가 생각나게 됩니다. 이 함수를 이용해서 그룹별 평균을 구하면 되겠다는 생각을 하게 됩니다. 그럼 표준편차, 최댓값, 최솟값, 중앙값 등은 어떻게 해야 할지 몰라 막히기 시작합니다. 그리고 고민하다가 그냥 그룹별로 하나씩 드래그해가며 구하시는 분들이 많을 것 같습니다. 이렇게 구하면 계산은 되지만 시간이 무척 오래 걸립니다. 거기에다 만약에 그룹이나 값의 개수가 10,000개를 넘어가게 되면 할 수가 없습니다.

위와 같은 상황에서 편하게 계산하기 위해 한번에 함수를 쓰고 드래그해서 복사하면 편하게 계산될 수 있는 법을 소개하려고 합니다.

바로 "OFFSET" 함수를 이용한 방법입니다.

그럼 우선 OFFSET 함수를 알아보겠습니다. OFFSET 함수는 기준 셀에서 지정한 행, 열 수만큼 이동한 후 해당 셀의 값을 읽을 때 사용합니다.

 1) OFFSET(기준 셀, 이동할 행수, 이동할 열수): 기준 셀에서 지정한 행, 열 수만큼 이동한 셀을 읽어 옵니다.

 2) OFFSET(기준 셀, 이동할 행수, 이동할 열수, 행수, 열수): 기준 셀에서 지정한 행, 열수만큼 이동한 셀을 기준으로 지정한 행수, 열수에 해당하는 범위를 읽어 옵니다.

예를 들어, "OFFSET(A1,2,5)"는 "A1" 셀에서 행으로 2칸, 열로 5칸 이동한 "F3" 셀을 읽어옵니다.

​"OFFSET(A1,2,5,3,4)"는 위와 같이 이동한 "F3" 셀에서부터 3행 4열 범위를 지정하므로 [F3:I5]가 됩니다.

이를 기반으로 우리가 원하는 범위에 AVERAGE, STDEV.P, MAX, MIN, MEDIAN 등의 함수를 붙여서 사용이 가능합니다.

(예) "AVERAGE(OFFSET(A1,2,5,3,4))", "MAX(OFFSET(A1,2,5,3,4))" 등)

이를 이용하여 위의 문제를 해결해 보려고 합니다.

고민 1. 어떻게 그룹별로 범위를 읽어 올 수 있을까?

OFFSET 함수를 이용하면 그룹별로 원하는 범위를 읽을 수 있을 것이라 생각했습니다. A그룹의 수학 점수는 D2:D4, B 그룹의 수학 점수는 D5:D7, C그룹의 수학 점수는 D8:D11 이므로, 각 그룹의 시작점과 각 그룹의 속한 개수만 알면 범위를 읽어 올 수 있겠다고 생각했습니다.

그럼 제가 할 일은 각 그룹의 각 과목이 시작하는 셀을 읽어오는 방법과, 각 그룹의 개수만 알면 될 것 같습니다.

일단 각 그룹의 개수는 "COUNTIF" 함수를 사용했습니다. 그리고 각 그룹의 각 과목이 시작하는 셀은 아래 표와 같습니다.

그래서 열은 1칸씩, 행은 각 그룹의 개수씩 추가되게끔 해서 OFFSET 함수를 이용하면 되겠다는 결과를 내렸습니다. 그냥 간단한 수열이라고 생각하면 더 편할 것 같습니다.

고민 2. 행, 열 방향으로 드래그를 했을 때 다른 그룹 또는 다른 과목에서도 동일하게 원하는 범위를 읽어 오는가?

저는 매번 함수를 사용하는 것보다 하나를 써서 드래그를 해서 복사해서 사용하고, 원래 data가 바뀌더라도 사용해 놓은 함수를 변형하지 않아도 결과가 나오는 것을 좋아합니다. Data가 바뀔 때마다, 각 조건을 따져가며 함수를 계속해서 바꿔야 한다면 너무 불편하다고 생각해서 그렇습니다.

우선 어떻게 결과를 정리할까 결과물을 생각해봤습니다.

제가 생각하는 데이터와 결과표입니다. 우선은 데이터 수가 적게 해서 만들어 보고자 합니다. 나중에 1,000개, 10,000개 이상의 데이터에서도 사용 가능합니다.

일단 그룹 내 속한 개수를 가져오고자 합니다. 우선 N에 해당하는 K2셀에는 "COUNTIF($C:$C,$J2)" 를 적용하였습니다. 그리고 같은 열에 복사하면 문제없이 나올 것입니다.

결과를 보니 잘 나왔습니다. 그다음 A1셀을 기준으로 각 그룹의 각 과목이 시작하는 셀로 이동시켜보려고 합니다.

행: A그룹을 위해서는 1, B 그룹을 위해서는 1+ A그룹 수, C그룹을 위해서는 1+ A그룹 수 + B그룹 수

열: A1에서 3칸 이동 이후에 한 칸씩 이동

이러한 고민 끝에 나온 것이 L2셀에 "OFFSET($A$1,ROW(E$1)+SUM($K$1:$K1),COLUMN(E$1)-1)" 입니다. 이것을 L2:O4까지 드래그해서 복사해보겠습니다.

테스트 해 본 결과 제가 원하는 셀을 읽어온 것을 볼 수 있습니다. 생각하는 대로 값이 나오는 것을 보니 매우 만족스럽습니다. 그럼 계산할 범위를 구해보면 행수만 적고, 여기에 해당하는 열은 1개 이기 때문에 1로 적겠습니다. 행수는 N 수를 쓰면 될 것 같습니다.

L2셀에 "(OFFSET($A$1,ROW(D$1)+SUM($K$1:$K1),COLUMN(D$1)-1,$K2,1))" 을 입력했습니다.

그럼 원하는 범위가 됩니다. 그럼 여기에 원하는 AVERAGE, STDEV.P, MAX, MIN을 더해보겠습니다. 함수를 넣고 L2:O4까지 드래그해서 복사해보겠습니다.

원하는 값이 계산된 것을 볼 수 있습니다. 이제 1,000개 이상의 행으로 늘어나도 쓰는데 문제없고 열이 늘어나도 똑같이 결과표에 열만 늘려가면 됩니다. 위와 같은 데이터 형식이라면 위의 식을 이용해서 편하게 원하는 값을 편하게 구할 수 있을 것 같습니다.

보시는 분들께 도움이 되었으면 좋겠습니다.