스프레드시트 컴활 실기 문제 풀어보자 (사용자 정의 함수, MATCH INDEX 배열수식)

아래문제 출처 ( https://m.cafe.daum.net/hermingway/ZYwR/38)

 

​​​엑셀 컴활 문제를 계속해서 풀어보자.

 

 

 

 

===

 

사용자 정의 함수 만들기

 

 

Alt+F11로 VB창 연다.

그리고 모듈을 만든다.

 

 

코드는

if 조건1 then

elseif 조건2 then

end if

형식을 이용해서 다음과 같이 만든다.

Public Function fn연소득(실적, 연봉)

    If 실적 >= 0.4 Then
        fn연소득 = 연봉 + 1000
    ElseIf 0.4 > 실적 And 실적 >= 0.2 Then
        fn연소득 = 연봉 + 500
    ElseIf 0.2 > 실적 Then
        fn연소득 = 연봉
    End If

End Function

 

 

그리고 시트에서

fn연소득을 이용해서 나타낸다.

 

 

===

 

배열수식 만들기

 

 

배열수식의 중요한 것

1) 조건 * 값으로 표시

2) 수식입력 후 Ctrl+Shift+Enter 눌러서 나타낼 것

 

이 문제에서 주의해야할 것은

가장 높은 실적 따로,

가장 높은 연봉 따로 배열수식으로 구해줘야 한다는 것

 

 

먼저 가장 간단하게 MAX만으로 배열수식 표현

 

=MAX( → 제시된 배열에서 가장 큰 값 찾기

($B$3:$B$24 = $C28) → 조건

*

($C$3:$C$24) → 값

)

 

사실, 이렇게 써주면 문제에서 요구하는 답과 같다.

하지만 MAX이외 함수

INDEX, MATCH를 써줘야 한다.

 

MATCH가 이해하기 가장 어려웠는데,

MATCH는 아까 나온 값의 위치값(행 수)을 나타내주면 된다.

 

 

 

 

= MATCH(

MAX(

($B$3:$B$24 = $C28) * ($C$3:$C$24)

)

, 

($B$3:$B$24 = $C28) * ($C$3:$C$24)

,

0

)

 

즉, MAX에 썼던 배열수식을

그대로

MATCH의 두번째 인자(찾아볼 범위)에 써줘야 정확하게 해당값의 위치를 찾을 수 있다.

 

 

마지막으로 생각해줘야할 것은

INDEX와 그 다음 열의 연봉표시이다.

=INDEX(

C$3:C$24,

MATCH(MAX( ($B$3:$B$24 = $C28) * (C$3:C$24) ), ($B$3:$B$24 = $C28) * (C$3:C$24), 0)

)

 

INDEX의 1번째 인자에

열을 1개만 구성하는 array를 넣었기 때문에

3번째 인자값(열 숫자)를 생략했다.

 

또, 다음 열에 표시해야 했기에

=INDEX(

C$3:C$24,

MATCH(MAX( ($B$3:$B$24 = $C28) * (C$3:C$24) ), ($B$3:$B$24 = $C28) * (C$3:C$24), 0)

)

위의 실적을 나타내는 범위의 열표시를 상대참조하게끔 바꿔놨다.

 

 

 

 

그래야 아래처럼 연봉도 빨리 표시할 수 있기 때문