[MS 엑셀강좌] MATCH와 INDEX 함수를 활용한 등급 구간별 분류

오늘은 MATCH함수와 INDEX함수를 사용하여 등급 구간에 따른 결과값을 도출해 내는 방법을 알아보겠습니다.

뭘 하려는 건지 잘 이해가 안되실 텐데요..^^ 다음의 예로 한번 보겠습니다.

 

예를 들면, 어떤 사람의 점수에 따라 다음의 기준으로 등급을 메기고자 합니다.

 

점수 구간	등  급
0~29점	5등급
30~49점	4등급
50~69점	3등급
70~99점	2등급
100점 이상	1등급

 

즉, 내 점수가 40점이면 4등급, 120점이면 1등급 등으로 자동으로 분류해 주는 것이지요...

 

이 경우, 아주 간단히는 IF 문을 여러개 중첩해서 처리할 수도 있는데, 그러면 식이 매우 지저분(?)해 지겠죠...

그래서, MATCH 함수와, INDEX함수를 사용하여 간단히 처리할 수 있습니다.

 

 

먼저, MATCH 함수에 대해 알아보겠습니다.

MATCH함수는 셀 범위에서 지정된 항목을 검색한 다음 범위 내에서 해당 항목의 상대 위치를 반환하는 함수입니다.

 

=MATCH(lookup_value, lookup_array, [match_type])

 

- lookup_value: (필수) lookup_array에서 찾으려는 값입니다.

                          특정 값을 직접 입력할 수도 있고 cell을 지정할 수도 있습니다.

- lookup_array: (필수) 숫자, 텍스트, 논리값 등이거나, cell을 지정할 수 있습니다.

- match_type: (선택, 기본값 1)

1: lookup_value보다 작거나 같은 값 중에서 최대값을 찾습니다.      이때, lookup_array의 인수값은 오름차순으로 지정해야 합니다.   0: lookup_value와 같은 첫째값을 찾습니다.      이때, lookup_array는 임의의 순서로 지정해도 됩니다.  -1: lookup_value보다 크거나 같은 값 중에서 최소값을 찾습니다.       이때, lookup_array의 인수값은 내림차순으로 지정해야 합니다.

 

그럼 사용 예를 한번 보겠습니다.

 

 

위의 식에서 값인 45는 [A1:E1] 중 작거나 같은 값 중 최대값인 '30' 이 있는 상대위치(즉, 지정된 array에서 상대적 위치)를 나타내는 '2'의 값을 반환합니다. 즉, array에서 두번째 값이라는 것을 표시해 줍니다.

이것은 다시 말해, 값 45는 30보다는 크고 50보다는 작은 구간에 해당하는 값이라는 것을 나타내기도 하는 것이죠...

어렵지 않죠?^^

이것은 뒤에 설명할 INDEX함수에서 위치를 지정하기 위한 값을 구할 때 사용됩니다.

 

 

다음으로 INDEX 함수에 대해 알아보겠습니다.

INDEX함수는 표나 범위에서 값을 반환합니다. INDEX함수는 '배열형'과 '참조형'의 두가지 방법으로 사용이 가능한데, 이번 내용에는 '배열형'으로 사용할 것입니다.

간단히 정리하면, 지정됨 범위(array)에서 가로로 몇번째, 세로로 몇번째 있는 cell의 값을 반환해라 하는 것입니다.

 

= INDEX(array, row_num, [column_num])

 

- array: (필수) cell의 범위입니다.

- row_num: (필수) 값을 반환할 배열의 행이 위치한 숫자입니다.

- column_num: (선택) 값을 반환할 배열의 열이 위치한 숫자입니다.

 

그럼 어떻게 사용되는지 한번 보겠습니다.

 

 

위의 식에서는 왼쪽 3x3 배열에서 첫번째 행과 세번째 열이 만나는 cell의 값인 '300'이 결과 값으로 반환됩니다.

 

 

이제 본론으로 MATCH함수와 INDEX함수를 함께 사용하는 방법을 알아보겠습니다.

적용하는 원리는 INDEX함수의 column 위치를 MATCH함수를 사용하여 찾아낸 값을 적용하는 것입니다.^^

아래 예를 한번 보겠습니다.

 

 

수식을 보시면, INDEX함수의 column_num을 지정하는 곳에 MATCH 함수를 써서 찾은 '점수'가 해당하는 상대위치를 사용하도록 만들었습니다.

 

=INDEX(B1:F2, 2, MATCH(A5, B1:F1, 1))

 

즉, MATCH함수를 이용하여 값 45가 해당하는 구간의 상대위치인 '2'를 찾고, INDEX함수를 사용하여 행과 열이 각각 2인 위치의 값인 '4등급'의 결과를 찾도록 하는 방법입니다.

 

원리를 알면 어렵지 않은 방법이므로 여러가지 분석에 많이 사용될 수 있을 것입니다.

특히, 위의 예와 같이 점수대별 등급을 정하거나, 학점을 메기는 경우 등에 사용하면 유용할 것입니다.

Excel을 사용하는 방법은 무궁무진한 것 같습니다.

 

잘 사용하시고 업무에 많은 효율 가져오세요...^^

오늘은 여기까지...^^