|
10. VLOOKUP과
HLOOKUP 함수 
VLOOKUP과 HLOOKUP 함수는 표의
가장 왼쪽 열이나 첫 행에서 특정 값을 찾아, 지정한 열이나 행에서
같은 행이나 열에 있는 값을 표시할 때 사용합니다. 대표적인 찾기
/ 참조 함수이며 다른 함수와 중첩해서 많이 활용됩니다. 여기서는 VLOOKUP에
관해 자세하게 알아본 뒤 HLOOKUP의 사용법도 같이 설명하도록 하겠습니다. VLOOKUP 함수의 구문은 다음과 같습니다.
VLOOKUP과 HLOOKUP 함수의 사용방법은
동일합니다. 우선 그 구문을 살펴보면요,
VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)
▶ Lookup_value : 배열의 첫째 열에서 찾을 값입니다. Lookup_value는 숫자,
참조 영역 또는 문자열을 지정할 수 있습니다.
▶ Table_array : 데이터를 찾을
정보 표입니다. 데이터베이스나 목록과 같은 범위의 참조 영역 또는
범위
이름을 사용합니다. range_lookup이 TRUE이면 table_array의 첫
열에 있는 값은 오름차순(..., -2, -1, 0, 1, 2, ... , A-Z, FALSE,
TRUE)으로 정렬되어 있어야 하며 그렇지 않으면 VLOOKUP은 정확한 값을
찾지 못할 수
있습니다.
▶
Col_index_num : 비교값과 같은 행에 있는 값을
표시할 table_array의 열 번호입니다.
▶ Range_lookup : VLOOKUP이
정확하게 일치하는 값을 찾을 것인지, 근사값을 찾을 것인지를 결정하는
논리값입니다. TRUE이거나 생략되면 정확한 값이 없는 경우 근사값 즉
lookup_value보다 작은 값 중에서 최대값을 찾습니다. FALSE이면 정확하게
일치하는 값을 찾으며 만일 일치하는 값이 없으면 #N/A 오류값을 표시합니다.
다른 함수에 비해서 인수가 복잡해 보이지만, 실제 예를 통해서
보면 별로 어렵지 않습니다. 이 함수는 전체 데이터에서 일부 데이터를
추출해서 다른 작업과 연관시킬 때 많이 사용됩니다.
1> 비교값이 왼쪽 열에 있으면 VLOOKUP을 사용한다.
다음 성적표 데이터에서 학번이 9인 채승헌의 데이터를
검출해보겠습니다. 이렇게 비교값(학번)이 왼쪽 열에 있으면 VLOOKUP을
사용하고, 첫 행에 위치해 있으면 HLOOKUP을 사용하는 것입니다.
① A16:D16에 데이터 검출에 필요한 항목을 입력합니다.
② A17에 검출하려는 데이터의 비교값(채승헌의 학번)인 9를 입력합니다.
그 후 B17을 선택하고, 함수 마법사를 실행합니다.
③ 함수마법사가 나타나면
VLOOKUP 함수를 선택하고, 다음과 같이 인수를 입력합니다.
④ 찾으려는 데이터인 "채승헌"이 정확하게 추출되었습니다.
현재 값을 찾는 기준은 학번을 기준으로 하고 있습니다. 따라서
Lookup_value에는 학번을 적어주는 것입니다. 채승헌의 학번인 9를 입력해도
되지만, 학번이 입력되어 있는 A17 셀을 입력했습니다. Table_array는
데이터가 있는 범위를 선택해 주는 겁니다. 따라서 성적표의 전체 범위인
A3:I13을 선택했습니다. 다음으로 Col_index_num는 비교값인 학번을
기준으로 해서 몇 번째 행에 있는 정보를 빼내 올 것인지를 정해주는
부분입니다. 지금 찾아야 하는
값은 이름이므로 학번을 기준으로 2번째 행에 있죠? 따라서 2를 입력한
것입니다. 마지막으로 정확한 값을 추출해내기
위해서 Range_lookup은 FALSE를 입력했습니다. 그 다음 확인 버튼을
누르면 정확하게 데이터에서 원하는 값인 "채승헌"을 추출해
냈습니다.
⑤ 계속해서 평균과 순위를 찾아야 합니다. 자동
채우기를 하면 어떻게 될까요? 역시 참조하는 범위가 상대 참조이기
때문에 범위가 변경되겠죠? 따라서 수식에서 셀 주소는 모두 절대 참조로
변경시킵니다. ( F4키 이용) 그 후 자동 채우기를 해보세요. 모두 "채승헌"이라는
같은 데이터가 채워졌습니다.
⑥ 평균을 구할 C17을 선택합니다.
수식입력줄에서 3번째 인수인 "2"를 7로 변경해줍니다.
평균에 관한 정보는 학번을 기준으로 7번째 셀에 위치하고 있기 때문입니다.
⑦ 같은 방법으로 순위를 구할 D17을 선택한 후 Col_index_num을
9로 바꿔줍니다. 다음 그림처럼 원하는 값을 모두 추출해냈습니다.
이제 VLOOKUP도 감이 잡히시죠? VLOOKUP과 HLOOKUP은 데이터베이스
작업에도 연관되어 많이
사용되므로 어렵더라도 사용법을 확실하게 익혀두시기
바랍니다.
2> 비교값이 첫 행에
있을 때는 HLOOKUP을 사용한다.
HLOOKUP은 VLOOKUP과 같은 기능을 하지만 다음 데이터 구조처럼 데이터의
기준이 첫째 행에 있을 때 사용하게 됩니다. 다음 성적표에서 학번 3인
학생의 이름과 수학성적, 그리고 평균, 순위를 추출해 보도록 하겠습니다.
수식 입력상자에서 다음과 같이 작성한 후 확인 버튼을 눌러서
값을 찾아낼 수 있습니다.
다음과 같이 HLOOKUP 함수로 데이터를 모두 추출했습니다.
HLOOKUP과 VLOOKUP 함수는 매우 중요하게
취급되는 함수이며, 주로 다른 함수와 중첩해서 그 효과를 발휘하게
됩니다. 잘 알아두세요~
이전(RANK 함수로
순위 구하기) | 다음(PERCENTRANK로 백분율 순위 구하기)
|
