엑셀 전문 블로그는 아닙니다만, 엑셀을 활용하다 보면, 할 수 없는 일들이 많았습니다. 그래서 찾았던 건 함수였고, 그 함수로만으로도 안되기 때문에 VBA를 공부하기 시작하죠. 그러면서 코딩으로 빠져드는데, 그러다보면 이런생각이 듭니다.
내가 왜 컴퓨터 프로그래밍을 포기하고 일반인들과 사는걸까?
회의감이 들기 시작하면, 되도록 함수를 쓰지 않고, 뭔가를 구현하려고 애를 쓰기 시작합니다.
또 그러다가, 안되는 것들은 도대체 얼마나 업데이트가 되어야 이런게 구현이 될까? 라는 의문을 품고 손수 수동으로 해결하곤 했습니다.
또 서론이 길었습니다.
[실전편] 엑셀에서 vlookup으로 찾을 수 없는 중복데이터 Index, Match함수 사용하는 꿀팁?
VLOOKUP 함수를 검색해서 들어오신 분들을 위해서 우선 함수의 특성을 알려드리겠습니다.
홍길동의 미팅 날짜와 약속은?
이름 | 날짜 | 약속 |
홍길동 | 11월 25일 | 8:00 |
홍길금 | 11월 27일 | 9:00 |
김닐동 | 11월 24일 | 8:30 |
데이터가 적을 때에는 그냥 눈으로 봐도 찾는 것을 일도 아닙니다.
그럼 이럴때는 어떨까요?
우진의 약속 날짜와 약속은?
이름 | 날짜 | 약속 |
민준 | 11월 25일 | 8:00 |
서준 | 11월 27일 | 9:00 |
도윤 | 11월 24일 | 8:30 |
예준 | 11월 25일 | 9:30 |
시우 | 11월 26일 | 10:30 |
하준 | 11월 27일 | 11:30 |
주원 | 11월 28일 | 12:30 |
지호 | 11월 29일 | 13:30 |
지후 | 11월 30일 | 14:30 |
준우 | 12월 01일 | 15:30 |
준서 | 12월 02일 | 16:30 |
건우 | 12월 03일 | 17:30 |
도현 | 12월 04일 | 18:30 |
현우 | 12월 05일 | 19:30 |
지훈 | 12월 06일 | 20:30 |
우진 | 12월 07일 | 21:30 |
선우 | 12월 08일 | 22:30 |
서진 | 12월 09일 | 23:30 |
찾기 어려우신가요? 이럴 때, Vlookup 함수를 활용하시면 됩니다.
함수의 양식은 이렇게 하시기 바랍니다.
=VLOOKUP("우진",$D$11:$F$28,2,0) “우진” - 찾고 싶은 우진의 셀 행 $D$11:$F$28 – 데이터의 첫행부터 마지막 행까지(무조건 $가 붙어야 합니다.) ,2, - 이름을 기준으로 날짜는 2번째 이기 때문에 숫자 2를 적어 넣습니다. 약속시간이라면? 3을 넣으시면 됩니다. |
위의 칸이 설명이 잘 되었을지 모르겠습니다.
=vlookup(“찾고싶은 사람”,데이터 첫행부터 끝행까지 고정, 찾은 사람의 옆의 값 호출, 0)
으로 설명하면 좀 쉬우실지 모르겠네요.
vlookup은 설명이 되었다면, 중복셀의 경우는 어떨까요?
VLOOKUP의 한계가 여기에 있습니다.
중복값의 경우에는 무조건 첫 번째 찾는 값을 호출하게 됩니다.
무조건 이 함수를 활용해서 뭔가를 하고 싶다고 하시면 절대로 안됩니다.
그래서 공부했던 함수 INDEX, MATCH 함수입니다.
기존의 중복행 관련함수들은 함수만들기를 통해서 했다면, 밑의 방식은 복잡해 보이지만 가져다가 쓰시면 됩니다.
D | E | F | |
10 | 이름 | 날짜 | 약속 |
11 | 민준 | 11월 29일 | 8:00 |
12 | 서준 | 11월 30일 | 9:00 |
13 | 도윤 | 12월 01일 | 8:30 |
14 | 예준 | 12월 02일 | 9:30 |
15 | 시우 | 12월 03일 | 10:30 |
16 | 하준 | 12월 04일 | 11:30 |
17 | 주원 | 12월 05일 | 12:30 |
18 | 지호 | 12월 06일 | 13:30 |
19 | 지후 | 12월 07일 | 14:30 |
20 | 민준 | 12월 08일 | 15:30 |
문제) 민준의 12월08일의 약속시간이 궁금할 때, 어떤 함수를 써야 할까요?
index와 match 함수를 활용하셔야 합니다.
원론적이지만, 함수의 기능을 알려드리겠습니다.
index함수 – 표나 범위 내에서 값이나 참조영역을 구하는 함수입니다.index(범위,행번호,열번호) match함수 – 배열에서 지정된 순서의 내가 정한 지정된 값에 일치하는 항목의 위치값을 찾습니다.match(찾을값,범위(달러를 무조건 표기), option) |
이런 내용이 무슨 상관인가요? 값만 찾으면 되는겁니다.^^
바로 답이 나갑니다.
=INDEX($F$11:$F$20,MATCH(1,($D$11:$D$20="민준")*($E$11:$E$20=DATE(2022,12,8)),0)) |
이걸 쓰라고 적으신건가요???
한번 설명해 드리겠습니다.
=INDEX(찾고자하는 시간 셀,MATCH(1,(찾는 사람의 이름 열="민준")*(찾는 사람의 약속 날짜 열=DATE(2022,12,8)),0)) |
함수서식을 다 치고나서 SHIFT+CTRL+ENTER를 누르시면 됩니다. 이는 배열수식으로 처리하게 하는 기능합니다.
배열이 뭔가요?
흠...그냥 쓰시면 안될까요?
우리가 중학교 때 배운 그 배열이 맞습니다. 강제로 수식을 조정하는 것으로 생각하시면 됩니다.
사실, 저기에 에러처리하는 함수까지 넣어주면 금상첨화이겠지만, 그것까지 함수를 넣어드리기는 힘들 것 같습니다.
이를 활용하여, 응용할 수 있습니다. 중복되는 값을 무한으로 늘려갈 수 있습니다.
조건1, 조건2, 조건3, 조건4를 늘려갈 수 있습니다.
동명이인의 약속은 다르기 때문에, 조건이 늘어나더라도 정확한 값을 찾을 수 있습니다.
사실, 저는 이 함수로 업무의 4~5시간을 절약한 경험이 있습니다.
이 함수의 사용은 정말로 간단하지만, 이 양식을 만드는 데 있어서는 상당한 시간을 이해하는 데에 할애한 적이 있습니다.
이해하실 필요 없습니다. 그냥 코딩에서 클래스를 가져다 쓰듯 그냥 함수를 그대로 가져다 쓰시면 됩니다.
제 글 읽어주셔서 감사합니다. 오늘도 행복한 하루 되시기 바랍니다.
'직장생활 IT 정보 > 무료 프로그램' 카테고리의 다른 글
[실전편] 엑셀은 왜 입력하는 것과 다른 값이 보이는 것일까? (날짜변환, 데이터 변환에 대하여) (0) | 2022.12.26 |
---|---|
컴퓨터 전공자가 추천하는 유튜브 프리미엄을 사용하는 세 가지 이유(feat. 멜론, 지니뮤직, 플로가 밀리는 이유) (0) | 2022.12.25 |
컴퓨터에 보안프로그램 중 V3를 깔아야 할까? 알약을 깔아야 할까? 해킹을 막는 방법? (0) | 2022.12.24 |
컴퓨터 전공자가 추천하는 필수 휴대폰 어플리케이션(앱) BEST 6 (0) | 2022.12.12 |
컴퓨터 전공자가 추천하는 시간을 절약시켜주는 크롬 브라우저 사용법 (0) | 2022.12.08 |
댓글