-
[팁] 엑셀 VLOOKUP 무작정 따라하기: 다차원 VLOOKUP 및 INDEX 함수건강 2015. 7. 24. 13:01
[팁] 엑셀 VLOOKUP 무작정 따라하기: 다차원 VLOOKUP 및 INDEX 함수
엑셀에서 데이터 가공작업을 할때 기본적인 연산함수 이외에 제일 많이 사용하는 함수가 VLOOKUP 과 HLOOKUP 일것입니다. 그 중에서도 개념적으로 행의 나열에 익숙한 사용자들은 VLOOKUP을 제일 많이 사용합니다. 이시간에는 VLOOKUP 의 기본 및 고급 활용에 대해서 알아 보겠습니다. 그리고 VLOOKUP 과 비슷한 기능을 가진 INDEX 기능도 함께 알아 보겠습니다.
* 데이터 제공처
- 정부 공공데이터 포털( www.data.go.kr) : 논산시 강우량 정보 (링크)
- 논산시 강우량 조회 : http://safe.nonsan.go.kr/prog/rainfallSearch/list.do
* 목적
- VLOOKUP 함수의 기본 및 고급 활용
- INDEX 함수의 기본 기능
1. 기초 데이터 수신
먼저 데이터 제공처에서 아래 내역을 다운 받습니다. (다운로드)
2. VLOOKUP 활용
먼저 기초 데이터를 확인하면 논산시의 지역별 2014년 월별 강우량을 확인하실 수 있습니다. 그중에서 '시청'에서 측정한 1월, 8월, 11월 강우량을 찾는 방법을 VLOOKUP 를 활용해 찾아 보겠습니다.
▲ VLOOKUP 을 이용한 특정지역의 특정월 강우량 찾기
* VLOOKUP 함수
- lookup_value : 찾고자 하는 대상(예) 1월,8월,11월)
- table_array : 찾고자 하는 대상의 목록과 해당하는 값들이 있는 전체 영역
- col_index_num : 찾고자 하는 대상의 목록에서 해당하는 값들이 있는 셀사이의 거리
( 1을 입력할 경우 찾고자 하는 대상의 목록이 결과값으로 나옵니다.)
- range_lookup : 정확한 값(false 입력) 또는 비슷한 값(true 입력) 여부
* 전체 예제 및 함수 결과가 포함된 파일(화면 캡처 내역 포함)
VLOOKUP_엑셀_연습.xls
.3. VLOOKUP 활용해 에제 풀기
3.1 시청의 1월,8월,11월 강우량 찾기
-> 1월 : =VLOOKUP(S20,B20:C31,2,FALSE)
- lookup_value : S20
- table_array : B20:C31 (대상 영역으로 시청의 경우 C31 이고, 강경의 경우 D31 선택 함. 전체 F31을 선택하고 모든 도시에 동일하게 적용해도 관계 없음)
- col_index_num : 2 (시청의 경우 '월'을 나타내는 영역에서 2번째 있으므로 2 임. 강경은 3, 연무는 4 입력)
- range_lookup : 정확한 값(false 입력) 또는 비슷한 값(true 입력) 여부 ( 대부분은 경우 False 입력)
3.2 동일 서식 적용하기
- 같은 도시의 경우 셀의 오른쪽 아래에 마우스를 놓고 클릭& 드래그를 하면 동일 서식이 적용됩니다.
이때 table_array 를 절대 경로 설정(함수 내 영역 선택후 F4 입력)
▲ VLOOKUP 을 이용한 특정지역의 특정월 강우량 찾기의 함수 결과
4. VLOOKUP 대신 Index 를 활용해 에제 풀기
- Index 의 활용 : VLOOKUP 을 이용해서 처리하는 경우 도시마다 col_index_num 를 임으로 변경해 주면서 '동일 서식 적용' 해야 하기 때문에 번거롭기도 하고 수식이 틀리는 경우가 종종 있습니다. 그래서 신규 함수인 Index 를 활용해 봅시다.
* Index 함수 와 Match 의 활용
- array : 찾고자 하는 결과값의 영역을 선택함.(여기서는 지역별 강우량 영역. 머리말 및 월목록 제외)
- row_num : 찾고자 하는 결과값의 row number (여기서는 원하는 월)
- column_num : 찾고자 하는 결과값의 column number (여기서는 원하는 지역:필수는 아님)
* Index 함수 와 Match 의 활용 예
- '=INDEX($C$36:$P$47,MATCH($R36,$B$36:$B$47,0),MATCH(S$35,$C$35:$P$35,0))
-> 여기서 row_num 와 column_num 을 가져오기 위해 Match 함수를 활용합니다.
( Match 함수 : 첫번째에 찾고자 하는 값, 두번째에 찾고자 하는 영역을 선택하면 해당하는 순서값을 표시해줌)
▲ VLOOKUP 대신 Index 를 활용한 목적값 찾기(함수 적용 셀값 및 첨부파일 참고)
.5. 다중 VLOOKUP 적용
- 아래에 표과 같이 행과 열이 아닌 여러개의 열의 조합에서 목적값(강수량)을 찾는 방법입니다.
▲ 다차원 VLOOKUP 이 필요한 예제
- 이러경우 B 칼럼과 C 칼럼의 조합을 "B2&C2" 을 D칼럼에 넣고 찾고자 하는 값도 I 칼럼에 동일하게 적용한 이후에 기존에 Vlookup 을 활용하면 됩니다.
▲ 다차원 VLOOKUP 을 해결한 예제
6. VLOOKUP 결과가 생각처럼 안나오는 경우
- 실제로 데이터를 외부에서 받거나 조합하여 목적값을 찾는 경우 vlookup 결과가 예상처럼 안나오는 경우가 있습니다. 혹 아래 케이스가 아닌지 확인해 보십시요.
6.1 공백 포함여부 확인 : 표나 목적값 옆에 보이지는 않지만 공객이 포함된 경우가 있습니다. 이럴경우 보이기에는 값이 일치하지만, vlookup 은 공백을 포함해서 대사하기 때문에 정확한 결과값을 찾을 수 없습니다. 일괄로 바꾸기를 통해 공백을 제거해 주십시요.
6.2 셀서식 오류 : 일부 숫자의 경우 문자로 셀서식이 적용되거나 하는 경우 값을 못 찾는 경우가 있습니다. 셀서식을 여러가지로 변경해 가며 적용해 보십시요.
* 전체 예제 및 함수 결과가 포함된 파일(화면 캡처 내역 포함)
VLOOKUP_엑셀_연습.xls맺으며
연습만큼 좋은 스승은 없습니다. 엑셀이 초보자에게는 어렵게 느껴지지만, 실제로 업무에서 사용되는 함수들은 몇개에 불과하기 때문에, 몇개 기능만 확실히 알아 둔다면 누구나 엑셀(excel)의 고수가 될 수 있습니다.
도움이 되셨다면 아래 "공유하기(Share)" 공유 버튼을 눌러주세요!!
