[스프레드시트] 컴활실기 풀어보자 (DAVERAGE, 배열수식, 매크로)

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

 

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

 

 

 

 

 

===

VLOOKUP : 검색문자열과 동일한 값을 갖는 행의 몇번째 열의 값을 리턴

HLOOKUP : 검색문자열과 동일한 열을 갖는 열의 몇번째 행의 값을 리턴

 

위 함수의 성질을 파악하여 수식을 만든다.

 

=CONCATENATE( → 아래 3개 나열된 문자열을 다 합치기

HLOOKUP( → 검색문자열과 동일한 열을 갖는 열의 몇번째 행의 값을 리턴

LEFT($B3, 1), → 비품코드의 가장 왼쪽 글자 1개

$B$33:$F$34, → [표2] 의 범위 (값을 리턴시킬 대상 테이블)

2, → 열의 2번째 행값

FALSE),

"-",

$D3 → 비품명

)

 

 

 

===

배열수식

 

배열수식의 성질을 잘 이해하면 된다.

{ → 중괄호는 수식에 쓰는게 아니라 수식을 다 쓰고 나서 (Ctrl+Shift+Enter)를 눌러야 배열수식으로써 나타나게된다.

=SUM( → 배열수식에서 조건에 합당할 때에만 값을 합쳐라

(

RIGHT($B$3:$B$30,1)=$G34

) → 조건

*

(

$G$3:$G$30

) → 합칠 값의 범위

)

}

 

 

===

 

배열수식 (상당히 어려움)

INDEX, MATCH, MAX의 이해가 필요

 

INDEX : 대상 배열(테이블)을 제시했을 때 열이 1개 밖에 없으면 열의 값(3번째 인자)을 생략해도 됨

MATCH : 일치 조건(3번째 인자)를 0으로 하면 정확한 값을 찾아줌. 리턴 값은 대상 배열에 위치하는 (검색된값의) 위치

MAX : 배열수식에서 직접적으로 쓰일 수 있다.

{=MAX( (조건) * (값) )} = 조건에 충족하는 값 중 최대값

 

위의 성질을 이용해서 배열 수식을 만들면 다음과 같다.

 

{=INDEX(

$D$3:$D$30, → 열이 1개라 3번째 인자값을 적지않고 2번째 인자값(행)만 적어도 값이 나옴

MATCH( → 제시한 ARRAY중에 MAX값에 일치하는 위치값 리턴

MAX( → 조건에 만족하는 값 중 최대값 리턴

($F$3:$F$30=$A38)

*

($H$3:$H$30)

)

,

($F$3:$F$30=$A38) → 정확한 ARRAY를 뽑아내야 MATCH값이 정확해진다.

*

($H$3:$H$30)

,

0

)

)}

 

 

===

최빈수 - 빈도수가 가장 많은 수

- 주어진 값 중에서 가장 많이 나온 값

- MODE함수 사용

 

DAVERAGE는 고급검색 처럼

조건을 일부 셀에 넣어서 필터링한 결과값의 평균을 내는 것

 

먼저 조건을 다음과 같이 넣었다.

2줄로 나눈 이유는

비품명이 "컴퓨터" 혹은 "복합기"라는 때문이다.

 

그리고 내용연수는 각 행의 내용연수값이 최빈수 이하라는 조건을 입력해야 한다.

 

그리고 DAVERAGE의 2번째 인자값은 열의 맨 윗줄 값을 넣어주면 된다.

그리고 백의 자리에서 올림해서 천의 자리까지 나타내야하므로

=ROUNDUP(

DAVERAGE(A2:H30,H2,D40:E42),

-3

)

 

 

===

 

비주얼베이직 이용하기

 

 

Alt+F11 로 VB창 연다.

그리고 [삽입]->[모듈] 실행

 

 

그리고 스크립트를 다음과 같이 입력

Public Function fn비고(구분코드)

    If Right(구분코드, 1) = "K" Then
        fn비고 = "가구"
    Else
        fn비고 = "전자제품"
    End If

End Function

 

 

<문제에서 요구하는 함수 select 넣어서 풀기>

 

 

그리고 I열에 다음과 같이 입력해주면 완료