#6-1. 컴활1급실기 계산작업1 (hlookup, vlookup, match함수)

 

[ 문제 ]

1. 표-1의 등록일의 연도와 성별을 표-2의 할인 비율과 비교하여

할인액 (F3:F14)에 예시와 같이 표시. [ Vlookup, Match, Year 함수 사용]

(예시 : 2000원) [할인액 = 할인 비율 * 등록비 ]

 

2. 표-1의 등록번호의 첫 글자와 마지막 글자를 표-3의 할인 비율과 비교하여

총 비용 (G3:G14)에 표시 [Hlookup, Match, Left, Right 함수 사용 ]

[ 총 비용 = 등록비 - (등록비 * 할인 비율) ]

 

3. 작성된 총 비용(G3:G14)을 활용하여,

총 비용의 최댓값을 지닌 사람의 이름을 I14에 표시

[ Hlookup, Vlookup, Index, Match, Max, Min, Maxa, Large 중 옳은 함수 사용 ]

 

4. 표-4의 등록번호에 해당하는 이름을 표-5의 등록번호의 마지막 글자를 활용하여

L14:L17에 표시 [ Lookup, Right, Value 함수 사용 ]

---

1. 표-1의 등록일의 연도와 성별을 표-2의 할인 비율과 비교하여

할인액 (F3:F14)에 예시와 같이 표시. [ Vlookup, Match, Year 함수 사용]

(예시 : 2000원) [할인액 = 할인 비율 * 등록비 ]

 

문제를 풀기 앞서, match함수와 vlookup함수를 알아야 합니다.

정말 자주 나오는 함수 종류이기 때문에

컴활을 준비하는 수험생의 입장에서 꼭. 확인하고 가셔야 합니다.

 

특히 match함수의 경우,

문제와 같이 vlookup함수와 떨어질 수 없는 함수이며,

hlookup 등 다양한 함수와 결합되어 사용되기 때문에 정말 중요한 함수입니다.

 

match함수는 지정하는 범위 안에 지정한 값, 식이 어디에 위치했는지 찾아주는 함수입니다.

위치를 찾아주는 함수이기 때문에 숫자로 표시됩니다.

 

hlookup함수의 경우,

찾을 값, 식이 있는 행에서

입력한 열에 있는 값을 표시하는 함수입니다.

 

풀이를 통해 더 자세히 살펴볼게요.

 

먼저 match함수를 통해서

표-1의 등록연도와 표-2의 연도를 비교하여 열의 번호를 찾습니다.

 

왜? why?

열의 번호를 찾는 것인가???

vlookup함수를 사용해야 하기 때문입니다. (문제에 따라 사용해야 합니다.)

 

때문에 lookup_Value는 표-1의 등록일의 연도를 작성하고, (year함수를 쓰면 쉽지)

lookup_Value와 같은 값이 있는 표-2의 연도를 lookup_array로 지정합니다.

 

그러면 array에 있는 value가 몇 번째 위치했는지 숫자로 알려줍니다.

첫 셀의 경우 2020년은 array의 첫 번째 위치했으니까 숫자 1로 표시됩니다.

(숫자 1이라는 것은 value가 array에 첫 번째 순서에 위치했다는 것을 알려주는 위치값!)

 

그렇게 쭉쭉 쉽게 쉽게 작성하면 끝인 줄 알았는데,

에러가 나옵니다. (#N/A)

 

왜 그럴까... 보면

표-2의 arrya는 2018년까지 표시되어 있지만,

표-1의 value를 포함한 등록일에는 2017년이 있기 때문입니다.

 

지져스.

처음에 이거 정말 머리 아픕니다 ㅋㅋㅋ

이럴 때는 당황 조금 한 뒤에

lookup_type을 수정하면 됩니다.

(1 = 보다 작음, 0 = 일치, -1 = 보다 큼)

 

이 경우 array의 값이 value의 값보다 크죠?

 

그렇기 때문에

"나는 value '보다 큰' array를 원해!"

보다 큰을 의미하는 -1을 type에 써줍니다.

(시험 당일 생각이 안 나면 당황하지 말고 1, 0, -1 모두 써서 비교하시면 됩니다.)

 

그러면 우측과 같이 2017일 때에도 값을 구할 수 있습니다.

저희는 이제 match함수를 통해 열 번호를 구한 겁니다.

 

이제 구한 열 번호를 사용해야 합니다.

작성한 식은 "="을 제외하고 잘라내기 하신 뒤에

vlookup함수 마법사를 열어 줍니다.

 

"이거 정말 짜증나"

 

그래도 많이 하다 보면 한 번에 성공 못해도

10~20초 안에 완성할 수 있으니,

원리만 이해하시면 합격하는데 큰 문제는 없습니다.

 

anyway,

match를 통해 열 번호를 구했으니 (표-2의 연도)

vlookup을 통해 행 번호를 구하고 (표-2의 남, 여)

일치하는 값을 찾아야 합니다. (위 2개와 일치하는 표-2의 비율)

 

따라서 lookup_value는 찾을 값인 "남" (표-1의 성별 첫 셀)

talble_array는 value의 "남"과 "여"를 가지고 있는 표-2를 범위로 설정( 그림의 파란색 사각형)

열 번호는 당연히 아까 구한 match함수를 복붙! 하고 + 1 을 해줍니다.

그리고 range_lookup은 false로 합니다.

(false는 일치, true는 비슷)

 

??????????????????

"더하기 1을 왜 하는 거야?"

 

이거 정말 중요합니다.

도대체 더하기 1을 왜 하는 걸까?

 

바로 match의 범위와 vlookup의 범위가 일치하지 않기 때문입니다.

여기서 일치하지 않는다는 것은

match의 열보다 vlookup의 열이 더 많죠?

match는 3개의 열을 가지고 있지만,

vlookup은 4개의 열을 가지고 있습니다.

 

이것을 일치시키기 위해 더하기 1을 match에 해주는 것!

 

그림으로 표현하면 이런 느낌?

 

"야 그럼 그냥 match의 범위를 늘리면 되는 거 아니냐?"

 

응 맞아요.

4개의 결과에 따른 4개 식의 범위를 표로 표현해 봤습니다.

 

결과1과 결과2는 같은 값을 표시합니다.

 

"야 그럼 더하기 1 왜 한 거야?"

 

그게...

저도 배운 지 오래되어서 잘 기억이 나질 않지만...

그 무슨 이유가 있었어요...

 

match의 범위를 늘리면 제대로 된 식이 성립되지 않는 경우가 있던 것 같아서

저는 항상 match의 범위를 늘리는 것 대신 더하기 1을 사용했거든요

 

뭔가 있어요.

혹시 아시는 분 댓글로 ㅎㅎ..

 

다시 문제로 돌아와서

 

 

작성된 식을 문제에서 요구하는 예시처럼 만들어 줍니다.

(예시 : 2000원)

(할인액 = 등록비 * 할인 비율)

 

*는 곱하기를 의미해욤.

&와 ""를 활용하는 것은 저번 시간에 했어욤 (해당 링크 3번)

---

2. 표-1의 등록번호의 첫 글자와 마지막 글자를 표-3의 할인 비율과 비교하여

총 비용 (G3:G14)에 표시 [Hlookup, Match, Left, Right 함수 사용 ]

[ 총 비용 = 등록비 - (등록비 * 할인 비율) ]

 

2번 문제는 1번 문제와 아주 유사해요

차이가 있다면 vlookup이 아닌, hlookup을 사용했다는 점,

그리고 left와 right함수를 추가했다는 점입니다.

 

그렇기 때문에 match함수를 통해 행 번호를 구해줍니다.

(이게 vlookup과 hlookup이 자주 나오는 이유입니다.

v는 열 번호를 필요로 하고, h는 행 번호를 필요로 하기 때문에 시험자의 입장에서 혼란스럽죠.

하지만 어림없어요. 그냥 함수 마법사 들어가서 행인지 열인지 확인하면 됩니다.

굳이 외울 필요는 없지만, 그래도 외우면 편하긴 합니다.)

 

이러이러해서 다시,

match함수를 통해 행 번호를 구해줍니다.

lookup_Value는 표-1의 등록번호의 마지막 글자인

"1,2,3,4"가 올 수 있도록 right(등록번호의 첫 셀, 1)을 해주고

array는 표-3의 1,2,3,4

그리고 2번의 match는 value와 array가 정확히 일치하니까 0으로 해줍니다.

 

?????????

근데 틀렸어.

 

"왜 그러냐?"

 

표-1의 등록번호는 foot1과 같이

문자+숫자 입니다.

 

엑셀은 멍청하게 right함수를 통해 마지막 "1"을 추출해도

기존의 단어가 문자+숫자이기 때문에

"1"만 남아도 이를 문자로 인식합니다.

 

그래서 강제로 곱하기 1( *1 )을 해주어 숫자로 변형해 줍니다.

value함수를 사용하여 숫자로 변형해도 됩니다.

(간혹 문제 중에, "함수를 사용하지 않고 숫자로 변형" 이런 의미의 조건을 붙이는 경우가 있어요.

이럴 때는 *1로 해결해 주면 끝.)

 

표-3의 f, b, p와 일치하는 hlookup의 value값을 구하기 위해서 left를 사용하는 것이 뽀인트.

 

전송중...

사진 설명을 입력하세요.

마지막으로 문제의 요구대로

식을 완성하면 끝!

---

너무 길어져서 2분에서 계속!