이번 게시물은 지난 게시물에 이어 찾기와 참조 함수 중 하나인 엑셀 HLOOKUP 함수입니다. HLOOKUP 함수는 VLOOKUP 함수와 사용법은 동일합니다. 다만 찾는 방향이 VLOOKUP 함수가 첫번째 열에서 수직으로 찾는다면 엑셀 HLOOKUP 함수는 첫번째 행에서 수평으로 찾습니다. VLOOKUP 함수를 뉘어 놓은 함수라고 생각하면 될 것입니다. 벌써 이해 끝나신 분들이 있을 수도 있겠네요.
아무튼 좀 더 자세히 살펴 봅시다.
* 엑셀 HLOOKUP 함수
기능: 검색값을 범위의 첫번째 행에서 찾아 지정한 행의 값을 반환하는 함수입니다.
용법: =HLOOKUP( 검색값, 범위, 행번호, [검색 유형] )
- 검색 유형은 생략하시면 기본값인 TRUE로 유사값을 검색합니다. FALSE 는 정확히 일치하는 값을 찾습니다.
용례1: =HLOOKUP( 1, A1:F4, 3 )
용례1의 결과:
검색값 1 을 A1:F4 의 범위에서 찾아 그 열에 해당하는 3번째 행의 값을 결과로 반환합니다. 만일 정확하게 일치하는 값이 없다면 찾는 값보다 작은 가장 큰 값을 반환합니다. 찾는 값보다 작은 값도 없을 경우는 에러가 생깁니다.
이 모든 결과가 정확하게 이루어지기 위해 유사값을 찾을 때는 찾는 값들이 정렬되어 있어야 합니다. 이를 잊으시면 안됩니다. (수평 방향의 정렬이 어려운신 분들은 정렬 범위를 선택하시고 "사용자 지정 정렬" 로 들어가셔셔 콤보를 표준으로 잡으시고 아래의 방향을 '왼쪽에서 오른쪽' 으로 설정해 주세요)
용례2: =HLOOKUP("KOR", A2:G6, 2, FALSE)
용례2의 결과:
엑셀 HLOOKUP 함수로 검색값 "KOR'' 을 범위영역 A2:G6에서 찾습니다. 첫번째 열에서 값을 찾으면 찾은 값의 열에 해당하는 2번째 열의 값을 찾습니다. 검색유형이 FALSE 로 설정되어 있습니다. 정확하게 일치하는 값만을 찾는다는 것이지요. 만일 범위 내에서 "KOR" 을 찾을 수 없다면 #N/A 라는 에러를 발생시킵니다. 정확하게 일치하는 값을 찾을 때는 굳이 정렬이 필요없습니다.
기출 예제: 진료코드[C3:C9]와 진료코드표[A12:E14]를 이용하여 진료과목을 구하시오. (컴활 2급 13회 A형)
- VLOOKUP, HLOOKUP 중 알맞는 함수를 선택하여 사용
기출 풀이:
E3 셀에 셀포인터를 놓고 다음의 수식을 입력합니다.
=HLOOKUP(C3, $B$12:$E$14, 3, FALSE)
입력후 채우기 핸들을 끌어 E9 셀까지 수식 복사 합니다.
수식을 설명드리면 문제는 진료코드를 보고 진료과목을 얻어내는 문제입니다.
해서 C3 셀의 진료코드를 진료코드표 B12:E14 범위의 맨 위줄에서 찾습니다. 그리고 검색범위의 3번째 행에서 진료과목을 얻어 내는 것이지요.
검색 범위[B12:E14]는 절대 참조로 지정하셔야 합니다. 그래야 아래로 수식복사시 검색 범위가 따라서 움직이지 않습니다.
또 한 가지 이 문제에서 주의하셔야 할 것은 마지막 FALSE 옵션을 반드시 넣어주어야 한다는 것입니다. 마지막의 FALSE 를 생략하면 기본값인 TRUE로 인식합니다. TRUE는 비슷한 값을 찾겠다는 것이지요. 해서 오름 차순 정렬되지 않고서는 정확한 결과를 얻어 내기가 곤란합니다.
대부분의 문자열를 찾는 문제에서는 FALSE 옵션을 기본으로 입력해 주세요. 물론 문제에 따라 다를 수는 있다는 것 잊지마시구요.
기출 예제2: 제품코드[A3:A6]와 제품별 단가표[A9:E10]를 이용하여 제품별 판매금액[D3:D6]을 구하시오.
- 판매금액 = 판매수량 * 판매단가
- 판매단가는 제품코드의 왼쪽 첫 번째 글자와 제품별 단가표의 제품기호[B9:E9)를 참조
- HLOOKUP 와 LEFT 함수 사용.
기출 풀이2:
D3 셀에 셀포인터를 놓고 다음의 수식을 입력합니다.
=C3 * HLOOKUP(LEFT(A3, 1), $B$9:$E$10, 2, 0)
입력 후 채우기 핸들을 사용하여 D6까지 수식 복사합니다.
수식을 살펴보면 판매금액을 구하는데 판매수량에 판매단가를 곱해서 구하네요. 판매수량은 주어져 있기에 간단히 셀을 참조하면 되지만 단가를 구하는 것이 문제인것 같습니다.
천천히 살펴 봅니다. 엑셀 HLOOKUP 함수와 LEFT 함수를 사용하라고 알려주네요. 제품코드의 첫자리를 LEFT 함수로 얻어 얻어진 첫자리를 단가표에서 HLOOKUP 함수를 이용하여 찾습니다.
일치하는 값이 나오면 그 열의 2번째 행의 값을 그 제품의 단가로 반환합니다. 정확한 값을 찾아야 하기에 0을 마지막에 넣어줍니다. 생략하면 유사값을 찾고 생략하기 위해서는 오름 차순 정렬되어 있어야 합니다. 너무 반복해서 말씀드리나요. 그만큼 중요하다는 말이겠지요. 또 범위는 절대참조 되어있습니다. 이유는 수식 복사시 범위가 움직이지 못하게 하기 위해서 입니다.
함수가 중첩된 수식을 만드는 것을 어려워하시는데 문제를 읽으시고 문제에서 요구하는 작업 중 가장 나중에 해야 하는 작업을 바깥쪽에 중첩합니다. 먼저 필요한 함수부터 안쪽으로 작성하는 것이지요.
@ 이상이 HLOOKUP 함수 입니다. 이전에 함수를 게시하고 이렇게 함수를 다시 시작한 이유는 앞에서 말씀드렸지만 이전에 게시한 내용이 빈약한 것 같아서 입니다. 뒤로 올수로 이전 게시물도 앞보다는 많이 보완이 되어 있네요. 하여 이전 게시물과 중복되는 내용이 많이 있을 수도 있습니다. 또 이전 게시물에는 없는 내용이 진행중인 게시물들에 있을 수도 있지만 그 반대의 경우가 있을 수도 있습니다. 버전업은 물론 계속 있을 것입니다.
아직 많이 부족합니다. 많이 지켜봐 주시고 오류가 있으면 지적해 주세요. 감사합니다. ^^
* 컴활 2급과 컴활 3급 함수목록 ver 0.2
아무튼 좀 더 자세히 살펴 봅시다.
* 엑셀 HLOOKUP 함수
기능: 검색값을 범위의 첫번째 행에서 찾아 지정한 행의 값을 반환하는 함수입니다.
용법: =HLOOKUP( 검색값, 범위, 행번호, [검색 유형] )
- 검색 유형은 생략하시면 기본값인 TRUE로 유사값을 검색합니다. FALSE 는 정확히 일치하는 값을 찾습니다.
용례1: =HLOOKUP( 1, A1:F4, 3 )
용례1의 결과:
검색값 1 을 A1:F4 의 범위에서 찾아 그 열에 해당하는 3번째 행의 값을 결과로 반환합니다. 만일 정확하게 일치하는 값이 없다면 찾는 값보다 작은 가장 큰 값을 반환합니다. 찾는 값보다 작은 값도 없을 경우는 에러가 생깁니다.
이 모든 결과가 정확하게 이루어지기 위해 유사값을 찾을 때는 찾는 값들이 정렬되어 있어야 합니다. 이를 잊으시면 안됩니다. (수평 방향의 정렬이 어려운신 분들은 정렬 범위를 선택하시고 "사용자 지정 정렬" 로 들어가셔셔 콤보를 표준으로 잡으시고 아래의 방향을 '왼쪽에서 오른쪽' 으로 설정해 주세요)
용례2: =HLOOKUP("KOR", A2:G6, 2, FALSE)
용례2의 결과:
엑셀 HLOOKUP 함수로 검색값 "KOR'' 을 범위영역 A2:G6에서 찾습니다. 첫번째 열에서 값을 찾으면 찾은 값의 열에 해당하는 2번째 열의 값을 찾습니다. 검색유형이 FALSE 로 설정되어 있습니다. 정확하게 일치하는 값만을 찾는다는 것이지요. 만일 범위 내에서 "KOR" 을 찾을 수 없다면 #N/A 라는 에러를 발생시킵니다. 정확하게 일치하는 값을 찾을 때는 굳이 정렬이 필요없습니다.
기출 예제: 진료코드[C3:C9]와 진료코드표[A12:E14]를 이용하여 진료과목을 구하시오. (컴활 2급 13회 A형)
- VLOOKUP, HLOOKUP 중 알맞는 함수를 선택하여 사용
기출 풀이:
E3 셀에 셀포인터를 놓고 다음의 수식을 입력합니다.
=HLOOKUP(C3, $B$12:$E$14, 3, FALSE)
입력후 채우기 핸들을 끌어 E9 셀까지 수식 복사 합니다.
수식을 설명드리면 문제는 진료코드를 보고 진료과목을 얻어내는 문제입니다.
해서 C3 셀의 진료코드를 진료코드표 B12:E14 범위의 맨 위줄에서 찾습니다. 그리고 검색범위의 3번째 행에서 진료과목을 얻어 내는 것이지요.
검색 범위[B12:E14]는 절대 참조로 지정하셔야 합니다. 그래야 아래로 수식복사시 검색 범위가 따라서 움직이지 않습니다.
또 한 가지 이 문제에서 주의하셔야 할 것은 마지막 FALSE 옵션을 반드시 넣어주어야 한다는 것입니다. 마지막의 FALSE 를 생략하면 기본값인 TRUE로 인식합니다. TRUE는 비슷한 값을 찾겠다는 것이지요. 해서 오름 차순 정렬되지 않고서는 정확한 결과를 얻어 내기가 곤란합니다.
대부분의 문자열를 찾는 문제에서는 FALSE 옵션을 기본으로 입력해 주세요. 물론 문제에 따라 다를 수는 있다는 것 잊지마시구요.
기출 예제2: 제품코드[A3:A6]와 제품별 단가표[A9:E10]를 이용하여 제품별 판매금액[D3:D6]을 구하시오.
- 판매금액 = 판매수량 * 판매단가
- 판매단가는 제품코드의 왼쪽 첫 번째 글자와 제품별 단가표의 제품기호[B9:E9)를 참조
- HLOOKUP 와 LEFT 함수 사용.
기출 풀이2:
D3 셀에 셀포인터를 놓고 다음의 수식을 입력합니다.
=C3 * HLOOKUP(LEFT(A3, 1), $B$9:$E$10, 2, 0)
입력 후 채우기 핸들을 사용하여 D6까지 수식 복사합니다.
수식을 살펴보면 판매금액을 구하는데 판매수량에 판매단가를 곱해서 구하네요. 판매수량은 주어져 있기에 간단히 셀을 참조하면 되지만 단가를 구하는 것이 문제인것 같습니다.
천천히 살펴 봅니다. 엑셀 HLOOKUP 함수와 LEFT 함수를 사용하라고 알려주네요. 제품코드의 첫자리를 LEFT 함수로 얻어 얻어진 첫자리를 단가표에서 HLOOKUP 함수를 이용하여 찾습니다.
일치하는 값이 나오면 그 열의 2번째 행의 값을 그 제품의 단가로 반환합니다. 정확한 값을 찾아야 하기에 0을 마지막에 넣어줍니다. 생략하면 유사값을 찾고 생략하기 위해서는 오름 차순 정렬되어 있어야 합니다. 너무 반복해서 말씀드리나요. 그만큼 중요하다는 말이겠지요. 또 범위는 절대참조 되어있습니다. 이유는 수식 복사시 범위가 움직이지 못하게 하기 위해서 입니다.
함수가 중첩된 수식을 만드는 것을 어려워하시는데 문제를 읽으시고 문제에서 요구하는 작업 중 가장 나중에 해야 하는 작업을 바깥쪽에 중첩합니다. 먼저 필요한 함수부터 안쪽으로 작성하는 것이지요.
@ 이상이 HLOOKUP 함수 입니다. 이전에 함수를 게시하고 이렇게 함수를 다시 시작한 이유는 앞에서 말씀드렸지만 이전에 게시한 내용이 빈약한 것 같아서 입니다. 뒤로 올수로 이전 게시물도 앞보다는 많이 보완이 되어 있네요. 하여 이전 게시물과 중복되는 내용이 많이 있을 수도 있습니다. 또 이전 게시물에는 없는 내용이 진행중인 게시물들에 있을 수도 있지만 그 반대의 경우가 있을 수도 있습니다. 버전업은 물론 계속 있을 것입니다.
아직 많이 부족합니다. 많이 지켜봐 주시고 오류가 있으면 지적해 주세요. 감사합니다. ^^
* 컴활 2급과 컴활 3급 함수목록 ver 0.2
'엑셀 배우기' 카테고리의 다른 글
| [엑셀 함수] 찾기와 참조 함수 INDEX 배우기 (0) | 2007.11.04 |
|---|---|
| [엑셀 함수] 찾기와 참조 함수 CHOOSE 함수 강좌 (4) | 2007.11.04 |
| [엑셀 함수] 찾기와 참조 함수 VLOOKUP 함수 강좌입니다. (1) | 2007.11.03 |
| [답변] 향기로운님의 vlookup 관련 질문 답변입니다. (6) | 2007.11.03 |
| [엑셀] 산술 참조 비교 연결 연산자 배우기 (0) | 2007.11.02 |