- 최초 작성일: 2002-06-28
- 최종 수정일: 2026-09-29
- 조회수: 24 회
- 작성자: 권현욱 (엑셀러)
- 강의 제목: 최고 최저값을 제외한 평균구하기2
들어가기 전에
오래 전 엑셀 파일로 진행하던 강의를 웹 글로 복원한 것이라, "버튼을 눌러보세요"처럼 엑셀에서 직접 조작하라는 표현이 남아 있을 수 있습니다. 결과는 본문의 그림이나 표로 확인하실 수 있으며, 현재와 맞지 않는 내용은 보완하되 전반적인 스타일은 당시의 갬성(?)을 유지했습니다.
지지난번 강좌 시간에 영역 내에서 최고 최저값을 제외한 평균을 구하는 몇 가지 방법에 대해 살펴보았습니다(X0271 강좌 참고). 이번 시간에는 거기에서 한 걸음 더 나아가, 계산 대상 범위와 제외할 순위를 자유롭게 바꿔가며 확인할 수 있는 방법을 살펴보겠습니다.
- 최신 글을 불러오는 중...
최고 최저값을 제외한 평균구하기2
핵심 요약: OFFSET으로 범위와 제외 순위를 가변적으로 조정하기
X0271 강좌에서는 무조건 특정한 영역 내에서 최대값과 최소값 하나씩만 제외한 평균을 구했습니다. 이번에는 OFFSET 함수로 계산 범위의 크기 자체를 바꾸고, LARGE·SMALL 함수로 몇 번째까지 제외할지도 선택할 수 있게 만들어 봅니다.
- 가변 범위: OFFSET(Start,0,0,행수,열수)로 계산 대상 범위 크기를 스크롤 바로 조정합니다.
- 가변 순위 제외: LARGE·SMALL 함수의 n번째 인수를 콤보 박스로 선택합니다.
- 배열 수식: 최종 합계·평균 수식은 배열 수식(Ctrl+Shift+Enter)으로 입력합니다.
아래를 보세요. 스크롤 바와 콤보 박스를 클릭하면 [표 1] 자료의 범위가 어떻게 변하는지, 각종 통계 수치들은 또 어떻게 바뀌는지 확인하실 수 있습니다.
배열 크기 선택과 계산 기준 선택 영역입니다.
| 구분 | 값 | 계산 기준 |
|---|---|---|
| 행 방향 | 7 | 1 |
| 열 방향 | 4 |
| 지정 영역 내의 최대값 | =MAX(OFFSET(Start,0,0,C20,C21)) |
| 지정 영역 내의 최소값 | =MIN(OFFSET(Start,0,0,C20,C21)) |
| n번째까지 제외한 합계 | {=(SUM(OFFSET(Start,0,0,C20,C21))-LARGE(OFFSET(Start,0,0,C20,C21),D20)-SMALL(OFFSET(Start,0,0,C20,C21),D20))} |
| n번째까지 제외한 평균 | {=(SUM(OFFSET(Start,0,0,C20,C21))-LARGE(OFFSET(Start,0,0,C20,C21),D20)-SMALL(OFFSET(Start,0,0,C20,C21),D20))/(COUNT(OFFSET(Start,0,0,C20,C21))-2)} |
[표 1] 계산 대상이 되는 원본 데이터입니다. 콤보 박스에서 선택한 순위(첫 번째~다섯 번째로 크거나 작은 값)에 따라 제외 대상이 달라집니다.
=ROUND(RAND()*100,-1) | =ROUND(RAND()*100,-1) | =ROUND(RAND()*100,-1) | =ROUND(RAND()*100,-1) | 제일 큰 것/제일 작은 것 제외 |
=ROUND(RAND()*100,-1) | =ROUND(RAND()*100,-1) | =ROUND(RAND()*100,-1) | =ROUND(RAND()*100,-1) | 두번째 큰 것/작은 것 제외 |
=ROUND(RAND()*100,-1) | =ROUND(RAND()*100,-1) | =ROUND(RAND()*100,-1) | =ROUND(RAND()*100,-1) | 세번째 큰 것/작은 것 제외 |
=ROUND(RAND()*100,-1) | =ROUND(RAND()*100,-1) | =ROUND(RAND()*100,-1) | =ROUND(RAND()*100,-1) | 네번째 큰 것/작은 것 제외 |
=ROUND(RAND()*100,-1) | =ROUND(RAND()*100,-1) | =ROUND(RAND()*100,-1) | =ROUND(RAND()*100,-1) | 다섯번째 큰 것/작은 것 제외 |
=ROUND(RAND()*100,-1) | =ROUND(RAND()*100,-1) | =ROUND(RAND()*100,-1) | =ROUND(RAND()*100,-1) | |
=ROUND(RAND()*100,-1) | =ROUND(RAND()*100,-1) | =ROUND(RAND()*100,-1) | =ROUND(RAND()*100,-1) | |
=ROUND(RAND()*100,-1) | =ROUND(RAND()*100,-1) | =ROUND(RAND()*100,-1) | =ROUND(RAND()*100,-1) |
우선 '배열 크기 선택' 항목 아래에 있는 두 개의 스크롤 바를 누르면 [표 1] 영역 중에서 계산을 할 대상 영역이 선택됩니다. 이 때 보다 시각적인 효과를 높이기 위해 VBA 코드를 사용하여 선택된 영역의 크기만큼 노란색으로 채워 나갑니다.
그런 다음 '계산 기준 선택' 콤보 박스에서 아래 위로 몇 번째 값을 제외한 합계와 평균을 구할 것인지를 결정합니다. 합계와 평균을 구하기 위해 사용된 수식은 아래와 같습니다. 배열 수식이 사용되었군요.
합계 {=(SUM(OFFSET(Start,0,0,C20,C21))
-LARGE(OFFSET(Start,0,0,C20,C21),D20)
-SMALL(OFFSET(Start,0,0,C20,C21),D20))}
평균 {=(SUM(OFFSET(Start,0,0,C20,C21))-LARGE(OFFSET(Start,0,0,C20,C21),D20)
-SMALL(OFFSET(Start,0,0,C20,C21),D20))/(COUNT(OFFSET(Start,0,0,C20,C21))-2)}모르는 함수는 하나도 없을 것입니다. 혹시 OFFSET 함수가 뭐하는 함수였더라 하는 생각이 드는 분은 X0024 강좌를 다시 살펴보고 오시기 바랍니다.
이 강좌에는 몇 가지 중요한 사항들이 숨겨져 있으며, 어떤 상황에 이르면 에러가 발생하기도 합니다. 이것은 직접 수정해 보시기 바랍니다. 잘 연구해 보시고 이해가 안 가시는 부분이 있으면 질문하시기 바랍니다.
정리 — OFFSET·LARGE·SMALL로 가변 범위 평균 구하기
| 구성 요소 | 역할 |
|---|---|
| OFFSET(Start,0,0,행수,열수) | 스크롤 바 값에 따라 계산 대상 범위의 크기를 동적으로 반환 |
| LARGE(영역,n) / SMALL(영역,n) | 콤보 박스로 선택한 n번째 큰 값/작은 값을 반환 |
| 배열 수식 {=...} | SUM에서 LARGE·SMALL 결과를 뺀 뒤 COUNT-2로 나누어 평균을 계산 |
자주 묻는 질문 (FAQ)
Q1. X0271 강좌와 이번 강좌의 차이는 무엇인가요?
X0271 강좌는 항상 최대값 1개, 최소값 1개만 고정적으로 제외했지만, 이번 강좌는 OFFSET 함수로 계산 범위 자체를 가변적으로 지정하고, 몇 번째로 크거나 작은 값까지 제외할지도 콤보 박스로 선택할 수 있게 만들어 훨씬 유연합니다.
Q2. OFFSET(Start,0,0,C20,C21)은 무엇을 의미하나요?
이름 정의된 Start 셀을 기준으로 행/열 이동 없이(0,0), 세로로 C20 셀 값만큼, 가로로 C21 셀 값만큼의 크기를 가진 영역을 동적으로 반환합니다. 즉 C20, C21 값을 바꾸면 계산 대상 범위의 크기가 자동으로 바뀝니다.
Q3. 몇 번째로 큰 값과 작은 값을 동시에 제외하려면 어떻게 하나요?
LARGE(영역,n)과 SMALL(영역,n) 함수를 함께 사용합니다. 전체 합계에서 LARGE(영역,n)과 SMALL(영역,n)을 빼면 n번째로 큰 값과 n번째로 작은 값이 제외된 합계를 구할 수 있고, 이를 COUNT(영역)-2로 나누면 평균이 됩니다.
마치며
OFFSET 함수는 이처럼 계산 범위 자체를 가변적으로 만들어야 할 때 매우 유용합니다. 스크롤 바, 콤보 박스와 같은 폼 컨트롤과 결합하면 사용자가 직접 조건을 바꿔가며 결과를 확인할 수 있는 대시보드 형태의 시트를 만들 수 있으니 다양하게 응용해 보시기 바랍니다.
