엑셀 조회및 참조 함수

엑셀에는 수백가지의 함수들이 분포되어 있습니다. 통계 함수만 하더라도 수십개의 함수들이 있고, 도움말의 내용을 들어 가서 확인해보면 전공자가 아니라면 용어 자체도 첨 들어보는 후덜덜한 내용들의 함수들이 많이 들어 있습니다.

여기에서 다룰 내용은 이런 어려운 함수들이 아니라 알게 된다면 많이 사용할 수 있을 것 같은, 그러나 이런 함수들의 사용 법을 배우지 않았다면 함수를 모르면 수작업을 하느라고 고생할 것 같은 조회및 참조 함수에 대해 다루어 볼까 합니다. 참고로 조회및 참조 함수는 컴활시험이나, ITQ 시험에서 필기, 실기 가릴 것 없이 항상 나오는 함수들 이기도 합니다.

 

CHOOSE 함수

이 함수는 인덱스 넘버를 이용해서 인수 목록에서 값을 반환해주는 함수입니다. 사용법

CHOOSE(index_num, value1, value2, value3,...... )

index_num: 1부터 무려 254까지의 숫자를 반환해 줄 수 있습니다. 이 함수의 특이한 점은 value 가 자동적으로 1부터 순차적으로 증가한다는 점입니다. 첫째값은 무엇을 선택하고, 두번째 값은 무엇을 선택하거나 하는 지정 사항이 전혀 없습 니다. 그래서 인덱스 넘버는 숫자만 사용할 수 있고 소수점 이하의 숫자가 있다면 소수점 이하는 무시됩니다. 그렇기 때문 에 이 함수를 어떤 곳에서 사용할 것인지 대강 감이 올 수가 있습니다. 순차적으로 증가하는 값이 있다면 Choose 를 사용 할 수 있고, 시트는 행과 열로 이루어져 있으므로 행번호나 열번호가 순차적으로 증가하므로, 행과 열번호를 참고로 해서 계산을 해야 하는 수식이 필요하다면 Choose 함수를 이용할 수 있습니다. 또한 날짜 데이터를 이용해서 각 날짜의 요일을 계산해 주는데에도 Choose 함수는 제격입니다.

value : 첫번째만 필수이고, 그 이후 부터는 선택 사항입니다. value 는 인덱스넘버와 마찬가지로 1개부터 254개 까지 지 정할 수 있습니다. 실제로 254개 까지 지정해서 사용할 일은 없겠지만 여하튼 사용할려면 가능은 하겠습니다.

 

그러면 Choose 함수를 이용해서 요일을 반환하는 방법에 대해 알아보도록 하겠습니다.

 

Choose 함수를 이용해서 요일을 반환해 주려면 먼저 Weekday 함수를 알아야 합니다. Weekday 함수는 날짜에 해당하 는 요일을 일련번호로 반환해주는 함수입니다. 예를 들어 위와 같이 "=Weekday(A1,2)"를 입력하면 2019년1월1일의 일련번호인 2가 반환됩니다.

Weekday가 반환해주는 숫자는 각 나라마다 다를 수 있으므로 시작 요일을 일요일부터 1로 계산해 주느냐, 월요일부터 1 로 계산해 주느냐를 선택해 줄 수 있습니다.

이것을 알면 이제 쉽게 Weekday 함수가 나타나는 결과를 이용해서 Choose 함수에 요일을 표시해 줄 수 있게 됩니다.

 

C열에는 Weekday 함수가 들어가 있습니다. 요일에 해당하는 값이 일련번호로 반환되어 있고 1월1일이 화요일이므로 Choose 함수에서 두번째 인수에 들어가 있어야 하므로, Choose 함수의 value는 월, 화, 수, 목, 금, 토, 일 의 순서로 지정 해주면 각 날짜에 해당하는 요일이 반환이 되게 됩니다. 사실 좀 쉽게 설명드리기 위해서 Weekday 함수와 Choose 함수 를 따로 분리해서 작성해 놓았는데, 두개의 함수를 중첩해서 사용해서 한번에 작성할 수도 있습니다.

 

위와 같이 Choose 함수 안에 Weekday 함수를 중첩해서 사용하면 함수를 여러번 따로 만들 필요없이 한번의 함수식 작 성으로 작업을 완료할 수 있습니다. 결과 값은 아래와 같습니다.

 

INDEX 함수

셀 범위에서 행번호와 열번호를 이용해서 값을 찾아주는 함수입니다. 사용법

INDEX(array, row_num, [colum_num])

array: 배열 상수나 셀 범위를 지정합니다. 배열의 사용법은 여기서 설명드리자면 너무 길어질 것 같아 따로 나중에 설명드 리도록 하겠습니다.

row_num: 행번호 입니다. 둘 중 하나는 반드시 지정해야 합니다. 행번호를 생략하면 colum_num을 지정해야 합니다. column_num: 열번호 입니다. 생략하면 row_num은 반드시 지정해야 합니다.

 

위의 수식을 보면 쉽게 이해할 수 있을 겁니다. 범위 지정을 A2:B4로 지정했으므로 A2 셀은 1,1, A3 셀은 2,1 B2 셀은 1,2 등과 같이 Index 함수가 셀을 참조하게 됩니다. 그래서 E1 셀의 값은 사과가 되고, E2셀의 값은 1500이 됩니다.

 

MATCH 함수

셀 범위에서 지정한 항목을 검색하는 것은 Index 함수와 같은데, 인덱스 함수와는 달리 해당 셀의 위치를 반환해 주는 함 수입니다. 예를 들어 A1=10, A2=20, A3=30의 값이 있을 경우 Macth(20, A1:A3,0) 과 같이 함수를 작성했을 경우 2 를 반환해 줍니다.

사용법

MATCH(lookup_value, lookup_array, [match_type]) lookup_value: 찾으려는 값입니다.

lookup_array: 검색할 셀 범위 입니다.

match_type: 1: 범위의 최대값, 0: 정확히 일치하는 값, -1: 범위의 최소값, 1, -1 로 지정할 경우는 각각 오름차순, 내림 차순으로 데이터가 정렬되어 있어야 합니다.

 

위의 수식을 보면 쉽게 이해할 수 있을 겁니다. Match 함수는 범위에서 값을 찾아서 그 위치를 반환해 주기 때문에 반환 값은 상대 위치를 나타내는 숫자값으로 표시되게 됩니다.

 

INDEX(), MATCH() 의 조합

Index 함수와 Macth 함수를 조합하면 Vlookup 함수와 비슷한 작동을 하는 기능을 만들 수 있습니다. Vlookup 함수를 사용하기 어려운 환경이나 속도 문제로 인해서(vlookup함수는 많은 자료가 있을 경우 속도가 현격하게 느려짐) Index와 Match를 사용해야 한다면 아주 좋은 해결책을 가지고 있는 것입니다.

 

위와 같은 데이터에서 직위별 상여금을 입력해야 하는 데 각 직위별 상여금을 Index와 match 함수를 이용하면 쉽게 계산 을 할 수 있습니다. Vlookup 함수를 이용해서도 똑같은 작업을 할 수 있지만 여기에서는 Index와 Match 함수를 이용하 는 방법을 알아보도록 하겠습니다.

C2에 들어갈 인덱스 값은 어떤 값이 되어야 할까요? "=Index($E$2:$F$3, 1,2)" 가 되어야 합니다.

C3는

"=Index($E$2:$F$3, 2,2)" 가 되어야 하고 C4는

"=Index($E$2:$F$3, 3,2)" 가 되어야 합니다.

잘 들여다 보니 열 값은 고정적이고, 행 값만 순서대로 1씩 증가된다는 것을 알 수 있습니다. 이렇게 행 값이 1씩 증가되는 것은 어떻게 함수를 사용해서 매칭을 시킬 수 있을까요?

바로 Match 함수를 이용하면 쉽게 해결할 수 있다는 것을 알 수 있습니다. Match("부장", $E$2:$E$6,0) = 1,

Match("차장", $E$2:$E$6,0) = 2,

Match("과장", $E$2:$E$6,0) = 3

과 같이 되므로 인덱스의 행번호 지정을 Match 함수를 이용해서 지정해 주면 위의 수식은 완성이 되게 됩니다. Match("부장", $E$2:$E$6,0) 에서 "부장" 은 상대참조로 지정되도록 A2셀을 찍어주면 마지막으로 해결해야 할 수식 부 분까지 완성하게 됩니다.