안녕하세요 채랑이 아빠 프리아입니다.
오늘도 시간을 잠시 내서 글 쓰러 왔습니다 ^^. 계속해서 엑셀 관련 글을 쓰게 되네요 ^^. 간단하게 글쓰기엔 엑셀이 참 좋아요.
오늘도 데이터 검색에 대한 팁을 하나 더 드리려고 합니다.
Vlookup 명령어를 쓰다보면, 가끔 여러가지 조건을 넣은 검색을 해야 하는 경우가 있습니다.
여러가지 조건을 넣은 합계나 평균의 경우 DSUM이나, DAVERAGE 명령으를 쓰면 되지만, 데이터를 찾아서 가져오는 것은 별도의 얘기죠.
그래서 다중 조건 검색하는 방법을 설명 드리겠습니다.
오늘도 샘플 데이터를 가지고 설명 드릴께요.
데이터는 랜덤으로 만들어져 있습니다.
가격차가 심하다구요? 네 랜덤으로 그냥 3만원 미만의 단가를 집어 넣었습니다. ^^
아 랜덤 데이터 생성하는 것도 나중에 한번 다뤄봐야겠네요 ^^
자 이번에는 문제를 내야 되겠죠? 가장 많은 부품명이 음…. HHHH 인것 같네요. 그럼 HHHH 부품 중 2022년 11월 11일에 들어온 자재의 단가를 확인해 보겠습니다. 2가지 조건이 들어간 문제로 테스트를 해보겠습니다.
자 Vlookup을 사용하여 데이터를 찾으려고 한다면, 보이는 테이블에서 바로 수행은 어렵습니다.
네 하나의 열을 추가하여 검색을 하셔야 합니다.
Vlookup 명령어의 기본은 찾는 데이터가 있는 열이 맨 앞에 있어야 합니다.
즉, 단가의 데이터를 찾으시려면 A열의 앞과 뒤에 열을 추가해야 합니다.
열을 추가하셨으면, 질의 에 대한 서치 데이터를 만드셔야 겠죠?
저는 A열 뒤에 열을 추가하고, B열에 다음의 수식을 반영하였습니다.
=A2 & F2
그리고 복사해서 다 채웠어요
그리고 아래와 같이 Vlookup 명령어로 데이터를 찾았습니다.
=VLOOKUP(L4&L5,B2:C23,2,FALSE)
결과 화면은 다음과 같습니다.
결과는 보시다 시피 잘 나왔습니다.
다만, B열에 신규 열이 추가되었습니다. 간단하게 데이터만 사용하는 경우에는 이렇게 작업해도 아무런 문제가 없겠지만, 여러사람이 봐야하는 데이터나 열을 추가할 수 없는 상황에서라면 이렇게 작업할 수 없게 됩니다.
이럴때 사용하는 수식이 Match와 Index을 복합적으로 사용하는 것입니다.
Index 수식은 배열에서 원하는 좌표를 반영하여 데이터를 가져오는 수식입니다.
수식의 사용방법은 다음과 같습니다.
=INDEX(array, row_num, [column_num])
여러번 설명 드렸지만, array는 원하는 범위입니다. 그리고 row_num은 행의 번호이며, column_num은 열의 번호입니다.
즉, 원하는 범위 내에서 행 번호, 열 번호에 해당되는 데이터를 출력하는 것입니다.
위에 표에서 예를 들어 드릴께요.
=INDEX(A2:G23,3,1)
범위 A2:23에서 3번째 행의 첫번째 열은 JJJJ값이 됩니다. 수학적인 표를 보는 것과 동일한 것입니다.
이번에는 Match 수식에 대해 설명드리겠습니다. Match는 요청하는 데이터가 몇번째에 있는지 찾아내는 수식입니다.
수식의 사용방법은 다음과 같습니다.
=MATCH(lookup_value, lookup_array, [match type])
첫번재는 lookup_value입니다. 찾는 값이죠. 두번째는 lookup_array입니다. 찾는 데이터를 검색할 범위 입니다. 단, 이 범위는 행이면 1개의 행에서 진행되어야 하며, 열로 찾을 것이면 1개의 열에서 진행되어야 합니다. 여러개의 열과 행을 동시에 적용할 수 없습니다. 마지막으로 match type인데요, 이것은 숫자를 검색할 때 많이 사용하는 기능입니다. match type는 0, -1, 1이며, 0은 일치하는 값, -1은 작은 값, 1은 큰 값을 찾는 역활을 합니다.
문자를 찾을 경우에는 반드시 0으로 검색해야 합니다.
이것도 예를 들어보겠습니다.
=MATCH(“BBBB”,A2:A23,0)
자 A2에서 A23 범위 안에서 “BBBB”라는 부품이 몇번째 있냐는 질문입니다. 위에서부터 4번재에 있죠. 이것도 Vlookup과 마찬가지로 위에서부터 검색하므로, 여러개가 있을 경우 맨 위의 위치 값을 리턴합니다.
여기까지 듣고 나서 Match와 Index를 어떻게 써야할지 감이 잡히시는 분들도 계실 겁니다. 일단은 다중 조건 검색 이전에 Vlookup 명령어와 동일하게 사용되는 화면을 먼저 보여드리겠습니다.
간단하게 질문 내용은 CCCC 부품의 단가로 찾아 보도록 하겠습니다.
자 결론부터 말씀드리면, 동일한 데이터가 출력된 것이 보이시죠? 맞습니다. Vlookup 명령어는 이렇게 Match와 인덱스 명령어를 섞어서 만들어진 수식입니다.
그래서 동일한 결과가 나오게 되는 것입니다.
Vlookup 명령어는 다시 설명 드리지 않겠습니다.
Match와 Index를 사용한 수식을 설명드리겠습니다.
먼저 Index 명령어를 분석하면 밑에처럼 A1:G23 범위 내에서 행번호 열번호를 입력하도록 하는 것입니다.
INDEX(A1:G23,행번호,열번호)
행번호는 MATCH(K4,A1:A23,0)입니다. 즉 찾는 데이터는 K4 즉 “CCCC”라는 부품명이죠, 범위는 A열이구요. match type는 당연히 일치(0)입니다. 그러면 CCCC가 있는 7이라는 값이 리턴되겠습니다.
마지막 열 번호는 단가가 있는 2열을 직접 입력하셔도 되고, MATCH(“단가”,A1:G1,0)으로 찾으셔도 됩니다. 1번행에서 “단가”와 일치하는 열 위치를 찾으니, 당연히 2라는 값이 리턴되겠죠.
그래서 인덱스에서 11,2의 데이터인 24,200의 값이 출력되게 됩니다.
자 이제 Match, Index 명령어에 대해서 감을 잡으셨지요?
그럼 이번 글의 주요 핵심인 다중조건 검색을 진행해 보도록 하겠습니다.
수식은 다음과 같습니다.
=INDEX(array, MATCH(lookup_value1&lookup_value2,lookup_array1&lookup_array2,0),column_num)
=INDEX(데이터 범위, MATCH(조건1&조건2, 조건1의범위&조건2의범위,0),열번호)
원래는 엑셀에서 주는 조건을 입력하는데 이해하기 힘드실까봐 한글로도 내용을 써 드렸습니다.
우선 Index 명령어 사용은 위의 1가지 조건으로 검색하던 것과 동일합니다.
대산 Match명령어는 조금 변경되었습니다. 우선 찾는 데이터가 조건1 & 조건2로 바뀌었습니다. 조건들 사이에 &가 들어가는 겁니다.
또한 찾는 데이터를 검색할 범위 또한 범위1 & 범위2로 바뀌었습니다. 당연히 순서는 바뀌면 안되겠습니다. 그외에 match type은 동일하게 0(일치)으로 적용하면 됩니다.
여기서 아셔야 할 점은 2가지 조건이 아니라 조건이 추가되는 경우에도 위와 동일하게 적용할 수 있습니다. 즉, 조건1&조건2&조건3&……으로 하고, 범위1&범위2&범위3&….으로 하셔도 결과가 나온다는 겁니다.
이렇게 사용하면 열을 추가하지 않고도 다중 조건의 데이터를 검색할 수 있습니다. 다만, 이 경우는 최신 엑셀에서 적용되는 수식이라는 점입니다. 저의 경우 Office 2022를 사용하고 있어 데이터가 잘 나오는 것입니다.
그렇다고 이전 버젼에서는 실행되지 않느냐 하면 그렇지는 않습니다. 다만, 식을 입력하고 ctrl+shift+enter를 눌러야만 적용된다는 점만 다릅니다.
ctrl+shift+enter을 누르면 수식의 양 옆에 “{ }”가 추가된 것을 볼 수 있습니다.
예전에는 저렇게 하지않으면 데이터가 출력되지 않았으나, 최신버젼에서는 일반 수식처럼 입력해도 동작을 하도록 바뀌었습니다.
참고로 말씀드리면, ctrl+shift+enter을 사용할 경우 파일의 용량이 커지며, 여러개를 사용할 경우 속도가 느려질 수 있다는 점 유의하여 주시기 바랍니다.
아 물론 최신 버젼의 경우 다중조건 수식이 포함되는 것만으로도 용량이 커집니다. ㅎㅎㅎ.
자 이렇게 오늘은 다중조건의 데이터 검색을 알아보았습니다.
좋은 팁이 되었기를 바랍니다.
오늘도 좋은 하루 되세요 ^^
