엑셀 배우기, INDEX/ MATCH 함수 활용해서 시간표 짜기!

VLOOKUP 함수와 더불어 가장 대표적인 참조 함수가 INDEX함수와 MATACH 함수입니다. 둘은 항상 짝꿍처럼 쓰이는 함수랍니다. 그러니 한꺼번에 함수의 기능과  구성을 익혀 주시기 바립니다. 더불어 컴퓨터활용능력 시험에서도 단골로 등장하는 함수이기 때문에 완벽한 이해가 필요합니다.

일단 아래를 보시면, 시간표의 종류가 두가지입니다. 하나는 강사 중심의 전체 시간표이고, 두번째는 학생 중심의 개인 시간표입니다. 강사 중심의 전체 시간표가 데이터베이스 역할을 하는 것이고, 학생 중심의 개인시간표는 이 데이터베이스에서 특정 데이터를 참조해서 유효한 정보를 만들어 내는 것이라 이해하시면 되겠습니다.

물론 학생이 소수라면, 전체 시간표 보면서, 학생 시간표의 데이터들은 손수 입력해 주면 되겠지만, 학생이 많다면 절대 이렇게 할 수는 없겠지요? 우리에게는 엑셀이 있고, 또 INDEX 함수와 MATCH 함수가 있으니까요.

 먼저 해당 함수들에 대한 상세한 설명을 드리기 전에, 시간표의 구조 자체가 아주 일반적인 시간표와는 다르다는 점을 미리 알려드립니다. 이는 개별 강사가 한과목 이상을 담당한다는 조건과 동시에 여러 교재를 가르친다는 조건하에서 엑셀에서 INDEX 함수와 MATCH 함수를 활용해서, 전체 시간표에서 학생 개별 시간표를 추출하기 위해 아래와 같이 일반적인 세로형 시간표가 아닌 가로형 시간표를 만든 것입니다.

언젠가 말씀드린 적이 있지만, 엑셀과 함수는 창의성이라 말씀드렸습니다. 엑셀로 표를 구성하는 것 역시 사용할 함수가 무엇인지를 고려해야 만들어야 한다는 점에서 창의성이 필요합니다. 결론적으로, INDEX 함수로 MATCH 함수를 가장 효율적으로 활용하기 위해 일반적인 세로형 시간표가 아닌 가로형 시간표를 만들었다고 생각하시면 되겠습니다.

각설하고, 이제 INDEX 함수와 MATCH 함수를 알아보도록 하겠습니다. 간단히 말해서 INDEX 함수는 인수로 지정된 행과 열이 교차하는 셀 값을 찾는 함수 이고, MATCH 함수는 특정한 값의 지정 범위 내에서의 행 번호를 찾는 함수 입니다. MATCH 함수 구성은 '=MATCH(찾을 값, 참조할 열 범위, 0)'으로, 옵션 값, 0은 정확한 값을 찾아오라는 의미입니다. VOOKUP 함수의 FALSE와 유사한 역할입니다. 

그렇다면 위의 E19번 셀값의 함수 수식 중, 'MATCH(A18,$E$1:$E$16,0) ' 부분를 먼저 풀이해 보겠습니다. E19 셀은 1교시의 AAA 학생의 담당 강사를 참조합니다. 따라서 학생 이름을 키 값으로 설정해서, 찾을 값인 'AAA'가 E열, 모든 강사들의 1교시 범위인, E1:E16사이에서 몇번째 행에 위치해 있는지를 찾으으면 됩니다. 답은 당연히 2행입니다.

INDEX 함수 구성은 '=INDEX(참조할 범위, 행 번호, 열 번호)입니다. 우리는 이미 MATCH 함수를 통해 모든 강사의 1교시의 범위, E1:E16에서 AAA 데이터가 위치한 행 번호, 2번을 찾았습니다. 그러니 남는 것은 INDEX 함수에 열 번호 정보를 추가해서 함수를 완성하는 일입니다. 다시 말하지만, E19 셀은 AAA라는 학생의 담당 강사 정보를 참조해야 한다는 것을 알 수 있습니다.

그리고 전체 시간표의 범위, 즉, A1:L16에서 1번 열에서 강사 정보를 찾을 수 있으니, 당연히 열번호는 1번으로 고정하면 됩니다. 즉, 전체 시간표의 범위, A1:L16에서, 필요한 행번호는 MATCH 함수를 통해 2번으로 찾고, 열번호는 1번으로 고정하면, INDEX/MATCH 함수를 통해서, E19셀에 '가'라는 'AAA' 학생의 담당 '강사' 정보를 출력하는 것입니다.

그렇다면 여기서 의문이 발생합니다. 왜 행 번호를 그냥 2번으로 입력하지 않고 애써 MATCH 함수를 통해 찾느냐라는 겁니다. 많은 학생의 데이터를 참조해야 하기 때문입니다. MATCH 함수를 INDEX 함수에 삽입함으로써, 다른 학생들의 전체 시간표 내의 각 교시의 행 번호도 자동으로 찾을 수 있고, INDEX 함수로 차례로 담당 강사, 강의실, 과목, 교재 정보를 불러올 수 있기 때문입니다.

즉, A18 셀의 키 값, 찾을 값인 학생의 이름만 계속 AAA, BBB, CCC, DDD로 바꾸어 주면 MATCH 함수를 통해 해당 학생 이름의 행 번호를 찾고, 미리 입력된 열 번호를 가지고,  INDEX 함수를 통해, 순차적으로 강사, 강의실 과목 교재 정보를 확인할 수 있답니다. 제가 만들었지만, 엑셀로 만든 시간표에서는 최고로 자동화된 시간표라 말할 수 있습니다! 감사히 익혀주시기 바랍니다.