컴퓨터활용능력 1급 기출 엑셀 문제풀이 - 6 (HLOOKUP 함수)

두번째 문제인 "계산작업" 문제는 총 5개의 문제로 구성되어있습니다. 5개가 각 6점씩 총 30점의 배점에 해당하는데요. 실기의 합격기준인 70점을 넘기 위해서는 적어도 "계산작업" 파트에서 3개 이상의 문제를 맞춰야 안정적으로 합격을 바라 볼 수 있습니다.

 

오늘은 "계산작업"의 두번째 문제인 HLOOKUP (VLOOKUP) 함수를 함께 배워보도록 하겠습니다.

 

HLOOKUP 또는 VLOOKUP 함수는 앞서 살펴본 "사용자 정의 함수"와 함께 단골로 출제되는 함수 중에 하나입니다. "사용자 정의 함수"가 명령문의 구조만 외우면 쉽게 풀 수 있는것에 반해 HLOOKUP 함수는 함수의 원리와 구조를 알고 있어도 기타 다른 함수(LEFT, RIGHT, TEXT, VALUE, 배열함수 등)과 같이 사용되며 그 응용법을 알아야 되는 경우가 많아 시험당일에 머리를 아프게 할 수 있습니다.

 

지금부터 HLOOKUP함수를 함께 풀어보며 원리와 구조에 대해 이해해 보도록 하겠습니다.

 

먼저 문제를 한번 보도록 하겠습니다. 문제를 보시면 "전공"과 "과목"을 추출하여 "교양(영어)"의 형식으로 표를 채워야 하는데요.

 

"계산작업"문제는 항상 문제 마지막 부분에 사용해야하는 함수를 알려줍니다. 이를 토대로 다시 문제를 해석해보도록 하겠습니다.

 

먼저 "전공여부"는 "과목ID"의 첫번째 글자를 이용합니다. 이는 LEFT함수를 이용하여 첫번째 함수를 추출하면 되는데요.

그림의 빨간 박스 속 과목ID에서 첫번째 글자인 알파벳(C,S,P)을 추출해야 합니다.

 

그럼 함수식은 LEFT(A4,1) 의 형태로 설정하면 알파벳을 추출할 수 있습니다.

 

이제 추출한 알파벳을 [표2]에서 HLOOKUP을 이용해 찾아야 합니다.

 

그럼 먼저 HLOOKUP의 구성을 함수마법사를 이용해 설명해 드리겠습니다.

 

HLOOKUP 함수의 함수마법사를 실행하면 위와 같은 창이 나옵니다. 각 항의 알맞은 인수들을 넣으면 되는데요.

제일 첫번째 항은 찾고자 하는 값입니다. 직접 값을 입력해도 되고 셀의 주소를 입력해도 되는데 요번 문제에서 찾고자 하는 값은 "과목ID"에서 추출한 첫번째 글자인 알파벳(C,S,P)이므로 첫번째 항에는 LEFT함수를 입력하면 되겠습니다.

 

두번째 항은 찾고자하는 데이터를 추출할 표의 범위 입니다. 문제에서는 [표2]가 이에 해당하므로 [표2], 그중에서 C, S, P가 있는 셀까지만([$A$27:$D$28]) 범위로 지정해주시고 반드시 절대참조($)를 넣어 채우기핸들 적용시에 범위가 변경되는 일이 없게 해야 됩니다.

 

세번째 항은 지정한 데이터 범위내에서 몇번째 행에 있는 값을 추출할지 입력하는 곳입니다.

 

[표2]를 보시면 문제에서 추출해야하는 값은 "교양", "전공선택", "전공필수"이므로 두번째 행을 뜻하는 숫자 2를 입력합니다.

 

네번째 항은 논리값으로 비슷한 값을 찾고자 할때는 TRUE, 정확한 값을 찾고자 할때는 FALSE를 입력합니다. 문제의 경우 정확한 값을 찾아야하므로 FALSE를 입력하면 되겠습니다.

 

위의 내용을 정리하면 입력하면 아래와 같은 화면이 나옵니다.

이제 확인을 누르면 아래와 같이 결과값이 나올것입니다.

 

이렇게 "교양"만 달랑 나오게 되는데요. 문제에서는 "교양(영어)"의 형태로 만들라 하였으므로 이제 여기에 "&" 연산자를 이용해 괄호 "(" , ")"와 (영어)를 만들어 보겠습니다.

 

먼저 "영어"부분은 "교양"과 마찬가지로 HLOOKUP를 사용합니다.

 

과목ID의 맨마지막 숫자를 사용한다는 점이 차이점인데요. 여기서 RIGHT함수를 사용하면 맨 마지막 글자를 추출 할 수 있습니다.

 

RIGHT(A4,1)

 

이런 형태로 입력해주시면 되는데요. 여기서 주의하실게 있습니다. 추출되는 값은 숫자의 모습이지만 시스템상에서는 "인수"이기에  HLOOKUP 적용시 [표2]의 데이터 속 숫자와 매칭이 안됩니다.

 

따라서

 

VALUE 함수를 사용해 인수를 숫자로 변경해줘야 합니다.

 

VALUE함수까지 활용하면

 

VALUE(RIGHT(A4,1))의 형태가 되겠습니다.

 

이제 HLOOKUP함수의 함수마법사를 이용해서 식을 완성해보겠습니다.

 

이런 형태로 식을 완성해주시면 되겠습니다. 여기서 논리값 부분은 정확한 숫자가 아닌 "1이상", "3이상" 등의 범위를 나타내므로 TRUE를 사용합니다.

 

그럼 이제 문제에서 바라는 "교양(영어)"의 형태로 완성해야 하는데요.

 

먼저 저 문자의 구성을 살펴보겠습니다.

 

교양 + ( + 영어 + ) 

 

의 형태인데요. 제가 "+"로 표현한 부분을 엑셀에서는 "&" 연산자를 활용해주시면 간단하게 끝납니다.

 

교양 : HLOOKUP(LEFT(A4,1),$A$27:$D$28,2,FALSE)

영어 : HLOOKUP(VALUE(RIGHT(A4,1)),$E$27:$H$28,2,TRUE)

 

그리고 괄호는 문자이기에 반드시 큰따옴표로 묶어줘야 합니다.

 

이걸 모두 "&" 연산자로 이으면

 

=HLOOKUP(LEFT(A4,1),$A$27:$D$28,2,FALSE) & "(" & HLOOKUP(VALUE(RIGHT(A4,1)),$E$27:$H$28,2,TRUE) & ")"

 

의 형태가 되겠습니다.

 

 

그 뒤 채우기핸들을 사용해 셀을 채워주면 위와 같은 형태로 셀이 채워집니다.

 

이상으로 HLOOKUP함수를 사용해 문제를 해결해보았는데요. HLOOKUP 또는 VLOOKUP 함수는 원리와 구조를 알고 있다하더라도 여러 데이터와 접목시키고 다른 함수와 함께 응용해서 사용해야되기 때문에 많은 연습을 통해 손에 익히는 것이 굉장히 중요한 함수입니다.