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 함수는 매우 중요하게 취급되는 함수이며, 주로 다른 함수와 중첩해서 그 효과를 발휘하게 됩니다. 잘 알아두세요~ |