[스프레드시트] 컴활 실기 (배열수식, 매크로 사용자 정의 함수, RANK.EQ 등)

===

VLOOKUP함수를 이용해보자.

※ VLOOKUP 함수

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

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

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

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

c) 동일한 행에 위치한

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

 

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

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

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

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

 

단가를 계산하는 부분에 VLOOKUP을 사용할 것이다.

=VLOOKUP(

TRIM($C3),

$K$3:$L$19,

2,

FALSE)

 

그리고 구하고자 하는 값은 다음과 같다.

= 수량 * 단가

= $D3 * VLOOKUP(TRIM($C3), $K$3:$L$19, 2, FALSE)

 

마지막으로 여기서 오류가 발생할 경우, "미수거"로 나타내야 한다.

=IFERROR($D3 * VLOOKUP(TRIM($C3), $K$3:$L$19, 2, FALSE), "미수거")

 

===

배열수식이 나왔다.

 

SUM을 상위에 두고,

WEEKDAY와 CHOOSE를 이용해 각 요일별 구분을 해줘야한다.

 

즉,

날짜=>WEEKDAY를 통해 숫자로 나타내고(단, 월=1~일=7)

숫자=>CHOOSE를 통해 월~금으로 나타내고(1="월", 2="화", ~ 5="금")

월~금=> 아래의 "열"값에 따라 구분짓게끔 한다.

 

=SUM(

($A$3:$A$31=$I23) → 조건 1: 각 행의 분류값이 해당 테이블의 각 행값과 같은가?

*

(

CHOOSE(

WEEKDAY(

$E$3:$E$31, → 각 행의 날짜를 2옵션 (월요일이 1로 값을 잡는 것)으로 값 추출

2

),

"월", "화", "수", "목", "금", "토", "일" → WEEKDAY결과값에 해당하는 문자열(1이라면 "월")을 추출

) = J$22 → 조건 2: CHOOSE까지의 결과값("월"~"일")이 해당 테이블의 각 열값과 같은가

)

*1 → 위 조건1, 2가 모두 충족할 경우의 개수를 세기 위해 1이란 값을 곱해주기

)

 

다음과 같이 결과가 나왔다.

 

===

FREQUENCY(단위단가별 개수), COUNTA(전체 개수)를 이용한 배열 수식

 

=FREQUENCY(

L3:L19, → 전체 범위

J29:J34 → 각 기준 값(각 기준의 최대값)

)

/

COUNTA(

L3:L19 → 전체 범위

)

 

수식은 다음과 같이 입력해주고

적용할 때 주의할 점이 있다.

 

다음과 같이 나타낼 범위를 모두 선택하고,

맨 첫줄에 수식을 입력 후,

Ctrl+Shift+Enter(배열수식 추출)단축키를 사용해야 한다.

 

 

====

VBA매크로 사용하는 것이다.

 

Alt+F11 단축키 사용해서 VBA입력창 열고,

모듈을 하나 삽입해준다. [삽입]->[모듈]

그리고 다음과 같이 스크립트를 적어준다.

(리턴할 때엔 함수값에 원하는 값을 넣어야하는 것 숙지)

Public Function fn스티커(품목명, 규격, 수량)

    If 품목명 = Trim(규격) Then
        fn스티커 = 품목명 & "-" & 수량 & "개"
    Else
        fn스티커 = 규격 & "-" & 수량 & "개"
    End If
    

End Function

 

그리고 다음과 같이 fn스티커라는 사용자 정의 함수를 호출해 출력

 

 

===

순위를 계산하는 것이다.

 

입력할 수식은 다음과 같다.

=IF(

AVERAGE($L$3:$L$19) <= $L3, → 조건 : 평균보다 해당 행의 값이 더 큰가?

"★ Top" & 

RANK.EQ($L3, $L$3:$L$19), → 클 경우엔 문제에서 요구하는 방식으로 입력 시켜준다.

"" → 작을 경우엔 빈칸 처리

)

 

결과는 다음과 같이 나온다.