HLOOKUP, VLOOKUP 함수

이번 시간에는 컴퓨터활용능력 2급 필기시험에 출제되는

HLOOKUP, VLOOKUP 함수에 대해서 알아보려고 합니다.

 

1. HLOOKUP과 VLOOKUP

위의 그림에서 포인트 점수에 따른 할인율을 구하려고 할 때 [표1]에 있는 할인율이 가로 방향으로 나열되어 있으면 HLOOKUP을, 세로 방향으로 나열되어 있으면 VLOOKUP함수를 사용한다. 위의 그림에서는 할인율(0%, 5%, 10%, 20%)이 가로 방향으로 나열되어 있으므로 HLOOKUP함수를 사용해야 한다.

=HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])으로 

박미리의 포인트 점수에 따른 할인율을 구하려고 하는데 이때

 

lookup_value : 박미리의 포인트 점수 [C10]

 

table_array : 포인트 점수에 따른 할인율 표로 이 때 이현주, 한가람의 할인율을 구할 때도 범위는 바뀌면 안 되기 때문에 F4를 눌러서 절대주소로 만들어 준다. [$C$5:$F$6] 또는 [$C$5:$F$7](할인율만 구한다면 포인트 점수에서 할인율까지만 선택해도 되고, 포인트점수에서 상품권까지 선택해도 무방하다)

 

row_index_num : [표1]을 가로방향으로 봤을 때(HLOOKUP 함수이므로) 찾는 값이 몇 번째 줄에 있느냐에 관한 내용으로 첫 번째 줄에 포인트점수, 두 번째 줄에 할인율이 있고, 우리는 할인율을 구하려고 하니 2라고 입력

 

[range_lookup] : 150포인트에 대한 할인율을 구하려고 하는데 [표1]에는 150포인트가 없는데 이럴 때 TRUE 또는 1을 사용을 하고, 만약 구하려고 하는 모든 포인트점수에 대한 값이 [표1]에 다 있다면 FALSE 또는 0을 사용한다. TRUE 또는 1을 사용할 때에는 [표1]의 포인트점수는 반드시 오름차순으로 정렬이 먼저 되어 있어야 하고, lookup_value의 값이 문자면 range_lookup은 무조건 FALSE 또는 0이다.

 

정리하면 =HLOOKUP(C10, $C$5:$F$6, 2, 1)이라고 입력하면 된다.

 

포인트점수에 따른 상품권을 구한다면 =HLOOKUP(C10, $C$5:$F$7, 3, 1)이라고 입력하면 된다.

 

할인율에 따른 상품권을 구한다면 =HLOOKUP(D10, $C$6:$F$7, 2, 0)이라고 입력해야 하는데 HLOOKUP함수는 table_array에서 선택하는 첫행의 내용과 lookup_value값을 비교하는 함수이기 때문에 table_array의 첫행의 내용은 반드시 할인율이어야한다. 그리고 할인율은 [표1]에 모두 있는 값이므로 [range_lookup]값을 0으로 해야한다.

 

 

 

1. 다음 중 찾기/참조 함수에 대한 설명으로 옳지 않은 것은? 

    가) VLOOKUP 함수의 네 번째 인수를 'FALSE'로 사용하는 경우 참조 표의 첫 열의 값은 반드시 오름차순 정렬되어 있어야 한다.

    나) HLOOKUP 함수는 참조 표의 첫 행에서 값을 찾을 때 대/소문자를 구분하지 않는다.

    다) INDEX 함수는 표나 범위에서 값 또는 값에 대한 참조를 반환한다.

    라) CHOOSE 함수의 첫 번째 인수는 1에서 254 사이의 숫자를 나타내는 숫자나 수식, 셀 참조 등을 사용한다.

 

VLOOKUP함수나 HLOOKUP함수에서 네 번째 인수를 'TRUE'로 사용하는 경우 참조 표의 첫 줄은 반드시 오름차순으로 정렬되어 있어야 한다.

답은 가)

 

2. 다음 중 아래의 워크시트에서 ‘박지성’의 결석 값을 찾기 위한 함수식은? 

    가) =VLOOKUP("박지성", $A$3:$D$5, 4, 1)

    나) =VLOOKUP("박지성", $A$3:$D$5, 4, 0)

    다) =HLOOKUP("박지성", $A$3:$D$5, 4, 0)

    라) =HLOOKUP("박지성", $A$3:$D$5, 4, 1)

 

결석 값이 세로 방향으로 나열되어 있으므로 VLOOKUP 함수를 사용해야 하고, lookup_value값(“박지성”)이 문자이므로 네 번째 인수는 정확히 일치(0 또는 FALSE)이어야한다.

답은 나)

 

3. 다음 중 아래 시트에서 [C2:G3] 영역을 참조하여 [C5] 셀의 점수 값에 해당하는 학점을 [C6] 셀에 구하기 위한 함수식으로 옳은 것은?

    가) =VLOOKUP(C5,C2:G3,2,TRUE)

    나) =VLOOKUP(C5,C2:G3,2,FALSE)

    다) =HLOOKUP(C5,C2:G3,2,TRUE)

    라) =HLOOKUP(C5,C2:G3,2,FALSE)

 

학점 값이 가로 방향으로 나열되어 있으므로 HLOOKUP 함수를 사용해야 하고, 76점([C5] 셀의점([C5] 값)이 점수 테이블([C2:G2])영역에 없으므로 유사일치(1 또는 TRUE)를 사용해야 한다.

답은 다)

 

4. 다음 중 아래의 워크시트를 참조하여 작성한 수식 ‘=VLOOKUP (LARGE(A2:A9,4),A2:F9,5,0)’의 결과로 옳은 것은?   

    가) 90

    나) 95

    다) 88

    라) 74

 

=LARGE(array, k)는 선택한 범위에서 k번째로 큰 값을 구하는 함수로 =LARGE(A2:A9, 4)는 4번째로 큰 값인 ‘5’가 된다.
5에 해당하는 5번째 열(수학)의 값은 ‘88’이다.

답은 다)

 

5. 아래 워크시트는 수량과 상품코드별 단가를 이용하여 금액을 산출한 것이다. 다음 중 [D2] 셀에 사용된 수식으로 옳은 것은? (단, 금액 = 수량 × 단가)   

    가) = C2 * VLOOKUP(B2, $B$8:$C$10, 2)

    나) = C2 * VLOOKUP($B$8:$C$10, 2, B2, FALSE)

    다) = C2 * VLOOKUP(B2, $B$8:$C$10, 2, FALSE)

    라) = C2 * VLOOKUP($B$8:$C$10, 2, B2)

 

=VLOOKUP(lookup_value, table_array, column_index_num, [range_lookup])에서 lookup_value는 'AA-10'([B2]셀)이고 lookup_value의 값이 문자이므로 네 번째 인수는 FALSE를 반드시 적어줘야 한다..
참고로 TURE는 생략가능지만 FALSE는 생략하면 안 된다.

답은 다)

 

6. 다음 워크시트에서 아래의 [수당기준표]를 이용하여 각 직급별 근속 수당을 구하는 수식을 [C2] 셀에 작성한 후 채우기 핸들로 나머지 근속수당을 계산하기 위한 수식으로 올바른 것은?​

가) =VLOOKUP(B2,$B$7:$D$9,2,FALSE)

나) =VLOOKUP(B2,$B$7:$D$9,3,FALSE)

다) =HLOOKUP(B2,$B$7:$D$9,2,FALSE)

라) =HLOOKUP(B2,$B$7:$D$9,3,FALSE)

 

[수당기준표]에서 근속수당이 가로방향이므로 HLOOKUP함수를 써야하고 수당기준표에서 근속수당은 3번째 줄에 나오니까 row_index_num을 3이라고 해야한다.

답은 다)

 

7. 다음 중 아래의 수식을 [A7] 셀에 입력한 경우 표시되는 결과 값으로 옳은 것은?

=IFERROR(VLOOKUP(A6,$A$1:$B$4,2),“입력오류”)

가) 미흡

나) 분발

다) 입력오류

라) #N/A

 

0이상 10미만은 미흡, 10이상 20미만은 분발, 20이상 30미만은 적정, 30이상은 우수로 표시하고 오류가 나면 "입력오류"라고 표시하는 문제인데 -5는 어떤 범위에도 들어가지 않기 때문에 오류가 나서  "입력오류"가 결과로 표시된다.

답은 다)

 

8. 다음 시트에서 함수식의 결과가 잘못된 것은?​

가) =VLOOKUP(28,A1:D5,3) → 2.35

나) =VLOOKUP(22,A1:D5,3) → 2.22

다) =HLOOKUP(17,A1:D5,4) → 1.27

라) =INDEX(A1:D5,3,4) → 2.22

 

나)에서 22는 20이상 25미만인 자료(4행)이고 그 중에 3번째 열(C열)에 있는 값을 구하는 문제이므로 C4셀에 있는 1.27이 답으로 나와야한다.

답은 나)