컴활 1급 실기 (VLOOKUP, 배열수식, 사용자정의함수)

컴활1급실기_2023년 상시1_문제.xlsm

1.10MB

 

해당 내용은 위 첨부파일의 내용입니다. (출처 : https://klesa108.tistory.com/81 )

 

 

실기문제를 계속해서 풀어보자.

 

 

※ VLOOKUP 함수

-> 단순하게 "값을 찾아주는 함수"

-> 아래 4가지 구분에 유의해서 기억하자.

a) 검색할 대상 표의 맨 좌측에서

b) 찾으려는 값을 검색한 뒤,

c) 동일한 행에 위치한

d) 다른 값을 출력 해준다.

 

사진 삭제

사진 설명을 입력하세요.

1번째 인자 : lookup_value => 찾으려는 값

2번째 인자 : table_array => 검색할 대상 표

3번째 인자 : col_index_num => 출력해줄 "동일한 행에 위치한 몇번째 열" 다른 값

4번째 인자 : range_lookup (기본값 : TRUE 유사한 일치 , FALSE 완벽한 일치)

 

* 기본수수료를 살펴보면

대출기간에 따라 달라진다. 이 "대출기간"을 조건으로 쓰자면

IF(대출기간 < 20, 50, => 20미만

IF(대출기간 < 60, 100, => 60미만 20이상

150 => 그외

)

)

 

* 고객등급 및 매출액별 수수료 표를 먼저 살펴보자

고객등급은 해당 행을 먼저 찾게끔 해주는 VLOOKUP으로 해결해주고

특정 열을 반환 할 때엔, MATCH를 쓰되

 

범위가 모두 나온 위에 행을 써주는 게 좋을 것 같다.

그리고 해당 행은 "오름차순"이므로

MATCH의 세번째 인자(1, 0, -1)에선 1을 써줄 것이다.

 

그런데 문제가 발생한다.

바로 작은값에 매치되는 셀의 열값을 반환해주면

VLOOKUP의 3번째 인자에 넣어줄

3번째 인자 : col_index_num => 출력해줄 "동일한 행에 위치한 몇번째 열" 다른 값

의 위치가 1만큼 줄어든 수치가 나타난다.

(1은 "찾으려는 값"을 나타내고 2부터 우측으로 1칸씩 움직여서

n+1을 입력해야 우측으로 n칸의 셀을 보여준다.)

 

그러므로 VLOOKUP과 MATCH를 제대로 적용하기 위해선 다음과 같이 나타내줘야 한다.

VLOOKUP(

"고객등급(일반~최우수)",

"고객등급 및 매출액별 수수료 표",

MATCH(

"매출액(0~)"

"매출 범위가 모두 나온 행"

1

),

FALSE

)

 

결국 첫번째 행에 입력된 수식을 만들면 다음과 같을 것 같다.

=IF($F3 < 20, 50, IF($F3 < 60, 100, 150)) +VLOOKUP($B3, $A$37:$E$39, MATCH($E3, $B$35:$E$35, 1) + 1, FALSE)

 

 

-------------------------------

 

※ PMT 함수

대출금을 매월 일정한 금리로 n개월간 상환해야 할 경우,

매월 상환해야 할 원리금(원금+이자)를 계산

 

1번째 인자 : rate => 이자율(고정금리)

2번째 인자 : nper => 상환회수

3번째 인자 : pv => 현재가치, 여기선 대출금액

4번째 인자 : fv => 미래가치, 상환이 모두 끝나고 남게될 금액 (기본값 : 0)

5번째 인자 : type => 납입시점 (0:기간말, 1:기간초) (기본값 : 0)

 

(첫번째 행 기준) 수식은 다음과 같이 나타냈다.

=PMT(

IF($B3="일반",

4,

3.5)%/12, =>연이율이므로 나누기 12를 넣어준다.

$F3, => 대출기간이 상환회수

-$E3, => 대출액을 넣어주되, -로 표시가 되지 않게 앞에 -를 붙여준다.

0, => 남겨질 금액을 0

0 => 기간말

)

 

 

------------------------------------------------------

대표사진 삭제

사진 설명을 입력하세요.

* 수식, 함수에 배열이 들어가야해서 배열수식(실행시 Ctrl+Shift+Enter)를 사용

{=COUNT(

IF(

(LEFT($A$3:$A$32, 1)=B$43) * => 고객번호의 1번째 문자와 43행의 각 열의 문자와 동일한지 체크

(YEAR($C$3:$C$32)=$A44) =>대출년도가 각 행의 A열에 있는 문자와 동일한지 체크

,

1) => 3번째 인자값을 생략할 경우 해당 값은 FALSE로 인식됩니다.

)}

 

----------------------------------------------------------------------------------------------------------------

* 역시 수식에 배열이 들어간 배열수식을 사용

{=LARGE(

(

(RIGHT( => 우선 조건을 적는다.

$D$3:$D$32, => 대출형태가 적힌 셀 범위

2 => 끝 2 문자열이

)=$G36 => G열에 적힌 문자열과 일치하는지 체크

)

*

$E$3:$E$32 => 위 조건이 충족되면 E열(대출액) 범위에 적힌 숫자 불러오기

),

H$35 => 위 조건에 충족된 E열 범위 중 LARGE순으로 35행의 각 열에 적힌 숫자에 해당하는 값 불러오기

)}

 

 

-----------------------------------------------------------------------------------------------------

 

* 사용자 정의 함수 만들기

[개발도구]->[Visual Basic] 선택하거나 [Alt+F11] 단축키 실행

 

Visual Basic창에서 [삽입]->[모듈] 실행

 

Public Function관련 스크립트를 작성한다.

 

논리식 처럼 스크립트를 작성한다.

단 최종적으로 리턴하려는 값은 "함수이름 = "으로 표현해준다.

 

최종적으로 스크립트를 완성했다.

Public Function fn비고(대출액, 대출기간)

    If 대출액 >= 10000000 Then
        If 대출기간 < 20 Then
            fn비고 = "●"
        Else
            fn비고 = "◎"
        End If
    End If

End Function

 

 

마지막으로 I열에

=fn비고(대출액, 대출기간)을 적용하고

모든 행에 적용시키면 된다.