이전 강좌에서 MAX와 MIN으로 가장 큰 값과 가장 작은 값을 찾아보았습니다. 이번엔 2번째, 3번째, ... 큰 값과 작은 값을 찾아보겠습니다. 사용할 함수명은 LARGE와 SMALL입니다. 이름에서 알 수 있듯이 LARGE는 큰 값을, SMALL은 작은 값을 찾는 기능을 합니다.
함수 정리 전에 함수의 인수를 예측하는 방법에 대해 조금 설명하겠습니다.
엑셀에서 함수는 굉장히 많습니다. 엑셀뿐 아니라 컴퓨터 언어를 조금만 공부해보면 함수가 굉장히 많다는 것을 알게 됩니다. 그 함수를 모두 알고 있다는 것은 사실 불가능한 일이죠. 더욱이 함수에 딸린 인수까지 모두 기억하는 것은 더욱 힘든 일입니다. 하지만 함수의 기능 정도는 기억할 수 있습니다. "합치기, 평균 내기, 최대값 찾기 ..." 등이 되겠죠.
이렇게 함수의 기능을 파악했다면, 대부분 해당 함수의 인수는 논리적으로 생각하여 예측하거나, 이해할 수 있습니다.
예로, MAX 함수의 주요 기능은 범위 중 가장 큰 값을 찾는 것입니다. 특정 범위에서 최대값이라는 고정된 값을 찾는 것이죠. 그러니 범위만 알고 있으면 고정된 최대값을 반환할 수 있습니다. 필요한 것은 특정 범위란 것이고 그 필요한 특정 범위가 인수가 된 것입니다. LARGE 함수도 당연히 범위 중 어떤 값을 찾는 함수이니 당연히 범위라는 인수가 필요하겠죠. 그런데 LARGE 함수는 최대값처럼 고정된 값을 찾은 것이 아닙니다. 1번째, 2번째, 3번째, ... 큰 값을 찾는 것입니다. 즉 몇 번째 큰 값을 찾을 것인지 알아야 한다는 얘기가 됩니다. 그래서 특정 범위와 함께 찾고자 하는 순서가 또 필요해지고, 이렇게 필요로 하는 2가지(범위, 찾고자 하는 값의 순서)가 바로 인수가 되는 것이죠.
LARGE데이터 집합(범위 또는 배열)에서 k번째로 큰 값을 반환합니다. 상대 순위 값을 반환할 수 있습니다.
LARGE(array,k)| array | k번째로 큰 값을 확인할 데이터 배열 또는 범위입니다. |
| k | 데이터의 배열이나 셀 범위에서 가장 큰 값을 기준으로 한 상대 순위입니다. |
SMALL데이터 집합(범위 또는 배열)에서 k번째로 작은 값을 반환합니다. 상대 순위 값을 반환할 수 있습니다.
SMALL(array,k)| array | k번째로 작은 값을 확인할 데이터 배열 또는 범위입니다. |
| k | 데이터의 배열이나 셀 범위에서 가장 작은 값을 기준으로 한 상대 순위입니다. |
함수의 정리는 위와 같습니다. 두 함수 모두 array와 k라는 인수를 갖는데 array는 위에서 언급한 특정 범위를, k는 찾고자 하는 값의 순서를 의미합니다.
즉 함수의 기능을 알아보고 논리적으로 필요한 것을 따져보면, 인수들을 대략 예측할 수 있습니다. 설사 예측하지 못했더라도 최소한 이해는 할 수 있죠.
예제 파일을 열고 2번째 높은 점수를 찾아보겠습니다.
- 2번째 높은 점수를 작성할 셀(I7)을 클릭하여 선택합니다.
- 수식 입력줄에서 수식을 시작(=)하고 함수명을 작성합니다. => =LARGE(
- 함수의 첫 번째 인수 힌트(array)를 확인하고 만족도 범위(F5:F16)를 클릭 드래그하여 참조로 작성합니다. => =LARGE(F5:F16
- 쉼표로 구분, 두 번째 인수 힌트(k)를 확인하고 2번째 이므로 2를 입력합니다. => =LARGE(F5:F16,2
- 최종, 함수가 하나이므로 그냥 엔터(닫힌 괄호 자동 생성 후 셀 완성) => =LARGE(F5:F16,2)
결과 90이 반환됩니다. 간단하죠.
MAX 함수와 굉장히 유사한데 단지 몇 번째라는 것만 추가 인수로 작성해주면 되는 것이네요. 만약 2번째가 아니라 5번째라면 k 인수에는 5를 입력하면 되는 것이겠죠.
SMALL 함수도 완벽히 동일합니다. 단지 작은 값의 순서로 반환한다는 기능만이 다를 뿐이죠.
SMALL 함수도 작성해 보겠습니다. 그런데 k 인수를 반드시 값으로 입력할 필요는 없습니다. 참조를 해도 되겠죠. 즉 작은 값의 순서를 1로 작성해도 되지만 H11셀을 참조해도 값은 1이니 동일합니다. 이렇게 참조하면 자동 채우기로 나머지 항목을 쉽게 만들 수 있습니다.
- 낮은 점수를 작성할 셀(I11)을 클릭하여 선택합니다.
- 수식 입력줄에서 수식을 시작(=)하고 함수명을 작성합니다. => =SMALL(
- 함수의 첫 번째 인수 힌트(array)를 확인하고 만족도 범위(F5:F16)를 클릭 드래그하여 참조로 작성합니다. => =SMALL(F5:F16
- 쉼표로 구분, 두 번째 인수 힌트(k)를 확인하고 H11(셀 값:1)셀을 클릭하여 참조하고 엔터(닫힌 괄호 자동 생성 후 셀 완성) => =SMALL(F5:F16,H11)
- 다시 I11셀을 클릭하고 우측 하단에서 I15셀까지 클릭 드래그하여 자동 채우기. => 자동 채우기된 셀의 서식까지 바뀝니다.
- 자동 채우기 옵션 버튼을 누르고 서식 없이 채우기를 클릭합니다. => 기존 서식으로 돌아갑니다.
결과, 값은 반환되었지만 뭔가 이상합니다.
자동 채우기된 셀 좌측 상단으로 에러 표시가 나오고 값도 좀 이상합니다. 1번째, 2번째는 맞는 값이지만 3번째부터 잘못된 값이 반환 되었습니다. 범위 중 3번째 낮은 값은 65이고, 4번째는 70, 5번째는 75여야 합니다. 뭐가 잘못됐을까요?
이유는 상대 참조 때문입니다. 참조의 자동 채우기 원리를 다시 생각해 보겠습니다. 참조 후 자동 채우기를 하면 행으로는 숫자가 열로는 알파뱃이 증가한다는 것이었죠.
위의 경우 인수 k는 참조 후 행으로 자동 채우기를 했으니 H11 → H12 → H13 → H14 → H15 로 바뀌었을 겁니다. k인수는 잘 참조되었습니다. 하지만 참조한 것이 k 인수만은 아니죠. 인수 array도 범위 F6:F17를 참조 했습니다. 그렇다면 이것도 숫자가 증가하겠죠. F6:F17 → F7:F18 → F8:F19 → F9:F20 → F10:F21이 된다는 것입니다.
참조를 적용한 셀을 더블 클릭하거나, 셀 클릭 후 수식 입력줄을 클릭하면 참조 셀을 확인할 수 있습니다.
수식 입력줄에서 작성한 함수의 인수의 색을 확인하면 array는 파란색으로, k는 빨간색으로 표시되는 것을 알 수 있습니다.
즉, 그림처럼 잘못된 범위에서 값을 찾고 있는 것입니다. 제대로 찾은 것은 처음 작성한 I11셀밖에 없죠. 나머지는 array의 범위를 모두 바꾼 것입니다.
우측 그림을 예로 보면 F5, F6셀이 제외되어 90과 65라는 값은 선택된 범위에는 없는 것이죠. 선택된 범위에 3번째 작은 값은 70이 나올 수밖에 없는 이유입니다.
이유를 알았으니 해결 방법을 아셨을 것으로 압니다. array 인수로 범위를 참조할 때는 고정해야 하는 것입니다. 이 범위를 고정하려면 숫자 앞에 "$"를 붙여 혼합 참조하거나 아니면 아예 절대 참조로 고정하면 되겠죠.
- 함수를 작성한 첫 셀(I11)셀을 다시 클릭하여 선택합니다.
- 수식 입력줄에서 arrary 인수인 F5:F16를 클릭 드래그하여 블럭 선택 후 키보드 f4키를 눌러 절대 참조로 변경하고 엔터 => =SMALL($F$5:$F$16,H11)
- 다시 I11셀을 클릭하고 우측 하단에서 I15셀까지 클릭 드래그하여 자동 채우기. => 자동 채우기된 셀의 서식까지 바뀝니다.
- 자동 채우기 옵션 버튼을 누르고 서식 없이 채우기를 클릭합니다. => 기존 서식으로 돌아갑니다.
결과, 에러가 사라지고 올바른 값이 반환되었습니다.
4번째, 5번째는 동일하게 70으로 나옵니다. 동일 값이 있다는 얘기입니다. 그렇다면 75는 낮은 순으로 6순위라는 것이네요. 만족도 데이터의 다른 셀 값을 보면, 75도 2개가 있으니 7순위도 75가 되겠습니다.
LARGE 함수와 SMALL 함수는 간단하게 정리되었습니다. 하지만 지금처럼 범위를 참조할 때는 상대 참조로 해야 할지, 절대 참조로 해야 할지, 혼합 참조로 해야 할지를 잘 판단해야 합니다. 대게의 경우 범위를 참조할 때는 절대 참조하면 대부분 해결됩니다. 물론 상황에 따라 다를 수 있습니다. 잘 생각하시고 적용하시길 바랍니다.
의견글 입니다.
()
