엑셀 Match 함수와 배열 수식

교통량 자료를 분석하기위해 엑셀을 자주 사용합니다. 캄보디아 일하면서 가장 많이 사용했던 엑셀 함수는 Match 함수인 것 같군요. 

Match(찾고자 하는 값, 찾을 범위, 찾는 방법)

Match함수는 특정 범위에서 원하는 값이 있는 위치를 찾아주는 함수입니다. 찾는 방법은 "0"을 입력하면 됩니다. "0"은 찾고자 하는 값과 정확히 일치하는 첫번째 값의 위치를 찾습니다. 

CSV 포맷으로 된 자료 파일을 엑셀로 불러오면 아래 그림과 같습니다. 검지기 ID, 연, 월, 일, 시, 분, 방향, 검지속도, 차종별 교통량 자료가 나열되어 있습니다. A열은 작업의 편의를 위해 Date()함수를 써서 연월일을 숫자로 변환한 값입니다.

다른 쉬트에서 연월일을 입력하고 이를 Date 숫자로 변경했습니다. 연월일은 숫자나 셀주소로 입력하면 됩니다. 

Date(연,월,일)

N4 셀에 입력한 Match 함수의 내용은 아래와 같습니다. 

MATCH($C$2&$F$4:$F$8763&$E$4:$E$8763,Original!$J$4:$J$17523&Original!$A$4:$A$17523&Original!$H$4:$H$17523,0)

첫번째 $C$2 & $F$4:$F$8763 & $E$4:$E$8763

- & 연산자를 써서 세가지 조건을 걸었습니다. 방향구분이 "1"($C$2)이고, 날자숫자가 F열의 값($F$4:$F$8763)과 같고, 검지 시간이 E열과 같은 값($E$4:$E$8763)을 찾겠다는 내용입니다. 

두번째 Original!$J$4:$J$17523 & Original!$A$4:$A$17523 & Original!$H$4:$H$17523

- "Original"이라는 쉬트에서 찾을 범위를 지정한 내용입니다. 첫번째 항목과 동일한 순서로 &연산자로 정의했습니다. 방향 구분, 날자숫자, 시간 순입니다. 

세번째 0

- 위의 세가지 조건을 동시에 만족하는 정확한 값을 찾으라는 명령입니다.

그런데 위의 수식을 입력하면 #N/A 라는 에러를 출력합니다. 이는 애초에 배열수식을 이용하기위해 만든 수식이기 때문에 그렇습니다. 

배열 수식 복사 방법

1. Match 함수를 이용한 수식을 복사하고자하는 범위의 첫번째 셀에 입력합니다. 당연히 #N/A 라고 나옵니다. 

2. 배열수식을 적용하고자하는 범위를 선택합니다. 

3. "F2"를 누릅니다. 이렇게하면 첫번째 셀의 편집모드로 들어갑니다. 

4. 3번의 편집모드 상태에서 Ctrl+Shift+Enter를 누릅니다.(컨트롤(Ctrl)키와 쉬프트(Shift)키를 누른 상태에서 엔터(Enter)키를 누릅니다. )

그러면 해당 범위에 배열수식이 복사되면서 제대로 된 함수값을 나타냅니다. 위의 그림에서 일부 값이 #N/A로 나타나는 건 조건에 해당하는 검지자료가 없어서 나타나는 것으로 정상적인 출력입니다.