티스토리 뷰
엑셀 VLOOKUP 함수, HLOOKUP 함수 사용 예
커피 한 잔의 여유 2013. 3. 20. 16:01
[링크]메모리스트 엑셀 함수 정리 목록 및 엑셀 팁 정리 목록
엑셀에는 많은 함수들이 있다. 함수는 데이터를 이용한 여러가지 처리작업에 활용된다.
이 중 VLOOKUP 함수와 HLOOKUP 함수는 자주 사용되는 함수인데, 함수 사용법을 다른 함수에 비해 많이 어려워하는 함수 중 하나이다.
VLOOKUP 함수는 열배열 함수이며, HLOOKUP 함수는 행배열 함수이다. VLOOKUP 함수와 HLOOKUP 함수는 특정 데이터의 값에 따른 관련 데이터를 가져오는데 사용되는 함수이다.
VLOOKUP 함수는 수직의 의미를 가지고 있는 Vertical 과 찾아보다의 의미를 가지고 있는 Look up 단어를 합해 놓은 함수이고, HLOOKUP 함수는 수평의 의미를 가지고 있는 Horizontal 과 찾아보다의 의미를 가지고 있는 Look up 단어를 합해 놓은 함수이다.
VLOOKUP 함수 사용 예
아래의 그림은 A열에 제품코드를 입력하면 [제품명], [단위], [단가]가 자동으로 계산되어 입력되는 예이다.
참조되는 데이터는 사전에 별도의 영역(H1:K6)에 입력해 둔 자료를 활용한다. 일반적으로 참조데이터는 다른 시트에 작성해서 사용하게 된다.
본 포스팅에서는 지면 관계상 데이터 영역의 옆에 표시해 두었다.
B2 셀에 제품명을 가져와서 출력이 되도록 VLOOKUP 함수를 적용해 본다.
함수 마법사(단축키 SHIFT + F3)를 실행해서 =VLOOKUP(A2,$H$2:$K$6,2,0) 라고 입력한다.
여기서 첫 번째 인수인 Lookup_value 는 표의 첫 열에서 찾으려는 값으로 기준이 되는 값을 선택하면 된다. 여기서는 A2 셀에 입력된 제품코드 [100]을 기준으로 하여 [컴퓨터]라는 제품명을 찾아 올 것이므로 A2 셀을 선택한다.
두 번째 인수인 Table_array는 데이터를 검색하고 추출하려는 표 영역으로, 참조 데이터가 있는 H2:K6 영역을 지정하면 된다. 이 때 참조되는 영역은 영역이 상대적으로 이동되는 영역이 아닌 절대영역이므로 범위 지정 후 F4키를 눌러서 절대참조로 변경해 주면 된다.
Table_array 항목으로 이동해서 영역 설정 후 곧바로 절대참조로 변경할 경우는 F4키를 눌러주면 되며, 다른 항목으로 이동했다가 다시 Table_array 항목을 클릭해서 절대참조로 변경할 경우에는 H2:K6 범위를 전체 지정 후 F4키를 눌러서 변경해야 한다. 그렇지 않으면 H2:$K$6처럼 설정이 되어 일부만 절대참조로 바뀌게 되고, 원하는 결과를 얻지 못하게 된다.
세 번째 인수는 추출하고자 하는 열을 입 번호로, 추출항목이 참조 데이터에서 몇 번째 열에 존재하는가를 입력하면 된다. 여기서는 제품명을 가져올 것이므로 2를 입력하면 된다. 단위를 가져올 경우는 세 번째 항목이므로 3을 입력하면 되며, 단가는 4를 입력하면 된다.
네 번째 인수는 추출 조건으로 정확하게 일치하는 항목을 가져올 경우는 FALSE를 입력하면 되며, 비슷(?)하게 일치하는 항목을 가져올 경우 TRUE를 입력하면 된다. 입력의 편의를 위해서 FALSE는 1 이 아닌 값을 입력하면 되는데 1 이 아닌 값인 0 을 주로 입력하며, TRUE는 1을 입력하면 된다.
TRUE에 해당하는 비슷한 항목을 입력한다는 의미는 숫자 데이터에서 주로 사용되며, 아래의 HLOOKUP 예제에서 설명한다.
엑셀 데이터 작업 중 단위 입력은 아래의 링크에서 설명하는 특수문자 입력기를 활용하면 쉽게 입력할 수 있다.
[클릭] [엑셀-팁]특수문자 입력기로 특수기호, 단위, 원문자를 쉽게 입력하기!!!
HLOOKUP 함수 사용 예
HLOOKUP 함수의 사용법은 VLOOKUP 함수의 사용법과 유사하다. 다만 데이터의 나열이 수직(위에서 아래로)이냐 수평(좌에서 우로)이냐만 다른 뿐이다.
일반적으로 VLOOKUP 함수를 많이 사용한다. 데이터가 늘어날 경우 아래에 추가 데이터를 나열하는 방식의 데이터에서는 VLOOKUP 함수를 사용하고, 추가 데이터를 오른쪽에 나열하는 방식에서는 HLOOKUP 함수를 사용하는데 거의 대부분의 데이터 형식이 추가 데이터가 있으면 아래에 추가하는 방식이기 때문이다.
아래의 예제는 HLOOKUP 함수의 활용 예이다.
아래의 그림 중 맨 아래의 평가표(수, 우, 미, 양, 가)가 VLOOKUP 함수와는 달리 항목들이 왼쪽에서 오른쪽으로 나열되어 있다.
인수의 사용법은 VLOOKUP 함수와 동일하며, 항목 중 평균 데이터를 기준으로 판정 항목을 가져오게 된다.
평균은 수치 데이터로 데이터에 따라서는 소숫점을 포함하게 된다. 이 때 나올 수 있는 모든 경우의 수를 나열한다는 것은 비효율적이다. 그래서 해당 항목을 구분짓는 수치들만 적어놓고 비슷한(?) 항목을 찾는 것이다.
아래의 판정표는 평균이 90점 이상이면 [수], 80점 이상이면 [우], 70점 이상이면 [미], 60점 이상이면 [양], 그 외는 [가]로 판정하는 예이다. 이처럼 비슷한(?) 항목을 찾도록 할 때 사용하는 항목이 네 번째 인수이다.
위의 VLOOKUP 함수 예에서는 네 번째 인수로 FALSE에 해당하는 0 을 입력했다. 여기에서는 비슷한 항목을 찾는 경우이므로 네 번째 인수로 TRUE에 해당하는 1 을 입력하거나, 생략하면 된다. 아래의 그림은 생략한 예이다.
아래의 예에서 네 번째 인수로 FALSE에 해당하는 0 을 입력하게 되면 0점, 60점, 70점, 80점, 90점이 경우만 찾을 수 있게 된다.
적용한 함수식은 =HLOOKUP(E2,$A$14:$E$15,2) 이다.
저는 위 상품을 소개하면서 포인트를 받았습니다.