[필기 풀이] 컴활1급 기출 2017년2회-33번 엑셀 INDEX MATCH 함수식

2017년도 2회 컴퓨터활용능력 1급 필기 기출문제 33번을 풀이한 것이다.

엑셀 INDEX 와 MATCH 함수를 활용하는 함수식 문제이다. 문제가 요구하는 수식을 찾아보자.

33. 아래 워크시트에서 일자[A2:A7], 제품명[B2:B7], 수량[C2:C7], [A9:C13] 영역을 이용하여 금액[D2:D7]을 배열수식으로 계산하고자 한다. 다음 중 [D2] 셀에 입력된 수식으로 옳은 것은?(단, 금액은 단가*수량으로 계산하며, 단가는 [A9:C13] 영역을 참조하여 구함)

① {=INDEX( $C$10:$C$13, MATCH( MONTH(A2)&B2, $B$10:$B$13&$A$10:$A$13, 0 ) )*C2}

② {=INDEX( $C$10:$C$13, MATCH( MONTH(A2)&B2, $A$10:$A$13,$A$10:$A$13, 0 ) )*C2}

③ {=INDEX( $C$10:$C$13, MATCH( MONTH(A2),B2, $B$10:$B$13&$A$10:$A$13, 0 ) )*C2}

④ {=INDEX( $C$10:$C$13, MATCH( MONTH(A2),B2, $A$10:$A$13&$B$10:$B$13, 0 ) )*C2}

풀이 :

길이만을 보면 바로 포기할 수도 있는 긴 엑셀 함수식이다.

하지만, 전체 수식을 크게 그려보고, 부분적으로 자세히 분석하면, 어려운 수식도 아니다. 함께 풀어 보자.

또, "배열수식" 을 왜? 배열수식이라 부르는지도 잠깐 살펴보자.

배열은 여러개의 값을 차례로 배열하여 그 전체를 하나의 값으로 보는 것이다.

문제에서는 B10:B13 & A10:A13 의 값을 차례로 하나의 배열에 담고, 2행~7행의 제품명 & 일자(월)와 각각 비교하는 것이다.

그래서, 배열 수식을 사용한다.

엑셀 INDEX MATCH 함수 중첩 문제 풀이

[금액]을 구하는 수식을 작성하는 것이 문제이다. 

먼저, 문제의 요구를 크게 보면,

[금액] = [단가] * [수량] 이다. 너무도 간단하다. [수량]은 C열에 전달되어 있다. 문제는 [단가]를 구하는 것.

따라서, [일자]와 [제품명]을 A9:C13 영역에서 찾아 [단가]를 알아내고, 여기에 [수량]을 곱하여 [금액]을 얻는다.

[그림1] INDEX MATCH 함수 배열 수식 계산하기

단가를 얻는 과정에서, INDEX 함수와 MATCH 함수가 사용된다. 

INDEX 함수는 행과 열로 해당 셀값을 얻는 함수이다. MATCH 함수는 배열이나 범위에서 특정값을 찾고, 그 순서를 숫자로 반환한다.

관련 함수 :

[엑셀 모바일] INDEX 함수 행 열 주소로 셀 참조하기 - 배열 형식

[컴활 2급] 값을 찾아 위치를 숫자로, 엑셀 MATCH 함수

이렇게 전체 수식의 흐름과 기초를 알았으니 세부적으로 분석해 보자.

문제의 수식은 3가지 함수가 중첩되어 있다. 안쪽에서 밖으로 나오면서 D2 셀 수식을 분석해 본다.

1. 가장 안쪽의 MONTH 함수로 날짜 형식인 [일자]에서 월을 얻는다.

[그림2] 엑셀 MONTH 함수로 월 얻기

( =MONTH(A2) = 10 )

2. 월만으로는 [단가]를 얻을 수 없다. [제품명]도 필요하다. 그래서, 두 값을 & 로 연결하여, 하나의 텍스트 문자열로 만든다.

[그림3] 앰퍼샌드( & )로 텍스트 문자열 연결

( =MONTH(A2) & B2 = 10 & "허브차" = "10허브차" )

3. 이 병합 텍스트 문자열을 A9:C13 에서 찾아 단가를 얻는다. MATCH 함수가 사용된다. 열머리글(A9:C9)은 범위에서 제외.

[그림4] MATCH 함수로 텍스트 찾아 순번 반환

=MATCH( "10허브차", $B$10:$B$13&$A$10:$A$13, 0 ) = 1

=MATCH( 찾는값, 검색범위, 찾는방법 )

[찾는값] 텍스트 병합순서에 맞추어, 

[월]과 [제품명]의 순으로 셀값을 합친 검색범위(배열), $B$10:$B$13&$A$10:$A$13 에서 동일한 텍스트 문제열을 찾고, 

그 순서를 숫자로 반환한다. 이 반환값이 곧, 범위내 해당 단가의 행 위치가 된다.

참고1 : 배열은 {} 중괄호로 묶이고, 열은 콤마( , )로, 행은 세미콜론( ; )으로 구분된다.

4. 이제 이렇게 찾아진 단가의 행위치를 사용하여 INDEX 함수로 셀 값을 얻어낸다. 

[그림5] INDEX 함수로 원하는 위치의 값 얻기

=INDEX( $C$10:$C$13, 1 ) = 1500

=INDEX( 찾을범위, 행순번, 열순번 )

[단가] 범위인 $C$10:$C$13 에서 3단계로 얻어진 행순번을 사용하여 해당 [단가] 의 셀 값을 얻는다. 

이 단계에서 다시 확인할 것은 [찾을범위]의 행범위와 3단계 [검색범위]의 행범위가 일치하여야 한다는 것이다. 

5. 이렇게 구해진 단가에 2번행의 수량인 C2 셀을 곱하면 2행의 금액을 구할 수 있다. 

마지막으로, 이 모든 함수식의 완성은 CTRL+SHIFT+ENTER 의 배열수식이다. 

이렇게 배열수식으로 작성한 이유는 2가지이다. MATCH 함수식에서 2개의 병합된 열로 검색범위를 만들었기 때문이다. 

D2 셀의 결과는 이상의 단계로 얻은 단가 1500 과 2행의 수량값 35 의 곱이다. 1500*35 = 52500 이 결과이다. 

이에 해당하는 수식은 1번) 보기이다.

3번)과 4번) 보기는 [일자]를 [제품명]에서 찾고 있는 MATCH 함수가 잘못되었다. 

2번) 보기는 MATCH 함수의 찾는값이 [일자]&[제품명] 의 순인데, 검색범위 참조가 [제품명]&[월]의 순이라 제대로된 검색이 진행될 수 없다.

관련 함수 :

[엑셀 활용] INDEX + MATCH 함수로 VLOOKUP 대체하기

[엑셀 활용] INDEX + MATCH 함수의 장점 배우기

INDEX 와 MATCH 함수는 자주 사용되는 검색 조합이다. 

두 함수를 조합하면, VLOOKUP 이나 HLOOKUP 보다 유연한 찾기 처리가 가능해진다. 여기에 배열참조를 추가하면 최고의 조합이다. 

활용하자!

정답 : 1번

이전 문제 :

[필기 풀이] 컴활1급 기출 2017년2회-32번 엑셀 다양한 조건부 합계

마늘빵.