[ID UP] 컴퓨터활용능력 컴활 1급 실기 벼락치기 - 엑셀 계산작업 [55/100점]

#엑셀 함수, #엑셀 함수 배우기, #엑셀 함수 속성, #엑셀 배열 수식

엑셀의 꽃이라고 할 수 있는 함수를 집중적으로 다루는 작업입니다. 총 5문제로 구성되며 4개의 함수 사용 문제와 1개의 사용자 정의 함수 문제로 이루어집니다. 각 문제당 배점은 6점입니다. 함수를 사용하는 문제의 경우 문제 형태가 매우 다양하기 때문에 함수를 학습하는 방법에 대해서 최대한 유익하게 작성해보도록 하겠습니다.

또 함수에서 가장 어려운 부분은 배열 수식을 이용하여 함수를 작성하는 부분입니다. 이 부분 또한 집중적으로 다뤄보도록 하겠습니다.

사용자 정의함수는 비주얼베이직을 이용하는데 문제 형태에 큰 차이가 없으므로 풀이방법에대해 공략하겠습니다.

Microsoft Office 2007 기준이지만 2010 버전과 큰 차이는 없습니다.

[ID UP] 컴퓨터활용능력 컴활 1급 실기 벼락치기

개요
엑셀 문제 구성 살펴보기
엑셀 문제 구성

엑셀 기본작업 (25점)
엑셀 계산작업 (30점)
엑셀 분석작업 (20점)
엑셀 기타작업 (25점)

액세스 문제 구성 살펴보기
액세스 문제 구성

액세스 DB구축 (30점)
액세스 입력 및 수정 기능 구현 (25점)
액세스 조회 및 출력 기능 구현 (25점)
액세스 처리 기능 구현 (20점)

컴활 1급 실기에서 출제될 수 있는 함수는 매우 무궁무진합니다. 벼락치기를 하는 과정에서 모든 함수의 기능을 암기 하기란 사실상 불가능에 가깝습니다.

실기 문제에서는 사용할 함수가 주어지며 해당하는 함수만을 이용해 문제를 해결해야합니다. 따라서 속성으로 함수를 익히는 방법을 소개해보도록 하겠습니다.

엑셀 함수 속성 학습 방법

함수를 사용하기 위해서는 각 함수의 정확한 개념을 알고 있어야 하는게 사실입니다. 그런데 정확하게 알지 않고도 함수를 빠르게 익히는 방법이 있습니다. 약간의 응용력을 요구하지만 시험을 응시할 때의 집중력과 두뇌회전이 평소보다 높을 것으로 생각되기 때문에 속성 학습을 적용할 수 있습니다.

무슨 함수를 학습해야 하는가?

기출문제집에서 회차별로 사용하는 함수들이 있습니다. 그 함수들을 우선적으로 학습해야 합니다. 실제로 기출 문제이기 때문에 비슷하게 출제될 확률이 매우 높습니다. 함수가 어떻게 활용되는지 또한 익혀두어야 합니다.

각각의 함수 학습

기본적으로 함수 하나를 학습할 때 알아야 할 것은 고작 세 가지에 불과합니다.

기본 구성 : 함수명(함수 인자1,함수 인자2, ...) =>자체가 반환값이 됩니다.

함수명, 함수의 인자, 함수의 반환값 입니다.

이 세 가지를 이용해서 속성으로 엑셀함수를 학습하는 방법을 알아봅시다.

함수명 : 영어 단어로 구성되어있으므로 어느정도 유추가 가능한 함수들 도 있습니다. 이 정도는 암기해두셔야합니다.

함수의 인자 : 인자 순서와 종류를 무작정 암기하시면 많은양에 질려 슬럼프에 빠질 위험도 있습니다. 이 함수의 인자로 무엇이 필요한지 정도만 알아두시면 됩니다.

함수의 반환값 : 간과하기 쉽습니다. 엑셀 함수는 모두 반환값을 가집니다. 이 점을 학습시에 꼭 명심하셔야 합니다. 반환값이란 함수가 인자들을 받아 결과적으로 내놓게되는 값입니다. AND함수 조차도 TRUE인지 FALSE인지 반환값을 가집니다. 함수가 반환하는것이 숫자인지, 문자열인지, 조건인지 꼭 파악해 두셔야 합니다.

예를 들어보겠습니다. AND함수입니다.

함수명 : AND

함수의 인자 : 조건1,조건2,[조건3],[조건4]..... -> [ ]표시는 필수 사항은 아니라는 뜻입니다.

함수의 반환값 : TRUE or FALSE

이런식으로만 알아 두면 실제 함 수 사용시 설명과 인자의 영문 이름을 확인할 수 있어 함수의 기능을 유추할 수 있습니다.

예를 하나 더 들어보겠습니다. FREQUENCY라는 함수가 있습니다.

함수명 : FREQUENCY

함수의 인자 : 원본 배열, 데이터 배열

함수의 반환값 : 데이터 배열의 분포 정도를 가진 배열

배열을 반환하기 때문에 배열 수식을 이용해줘야하는 함수입니다. 그렇지 않으면 배열의 첫 값만을 반환합니다.

영역을 선택후 수식 입력후 [Ctrl] + [Shift] + [Enter]를 눌러 배열 수식을 적용해줘야 합니다.

배열 수식

컴활에서 사용하는 배열 수식 활용 방식에 대해 설명 드리겠습니다.

두 가지로 나눌 수 있습니다.

1. 위 처럼 범위를 선택해서 수식 입력 후 배열 수식을 설정하는 방식과

2. 한 셀에 배열 수식을 설정하는 방식입니다. -> 여러 셀에 지정 가능

Tip. 배열 수식을 설정하는 방법 : 수식 입력후 [Enter] 입력 대신 [Ctrl] + [Shift] + [Enter] 을 입력하면 수식이 { }로 감싸지면서 배열 수식화 됩니다.

Frequency 함수처럼 도수 분포를 구하는 경우를 제외 하면 2번 방식이 더 많이 사용됩니다.

주로 사용되는 배열 수식의 조합과 예제입니다.

SUM

{=SUM(MOD(Q15:Q45,2)*(Q15:Q45))}

한 셀에 이렇게 입력한다면 Q15부터 Q45까지 수식이 반복되면서 각 결과값이 SUM 합계에 집계됩니다. 직접 수정해가면서 해보시기 바합니다.

위의 배열 수식 실행결과는 Q15:Q45에서 홀수인 값들의 합 입니다.

IF/AVERAGE

{=AVERAGE(IF($A$2:$A$10=F20,IF($G$2:$G$10>=5,$I$2:$I$10)))}

위처럼 수식을 입력했을 경우 역시 A2,G2,I2 부터 수식이 반복됩니다.

단, AVERAGE의 경우 평균이므로 합계/개수 이기 때문에 해당하지 않는 셀은 개수로 취급하면 안됩니다. FALSE를 반환받으면 개수로 집계되지 않습니다.

위의 배열 수식 실행 결과는 A2:A10중 F20이고 G열이 5 이상인 I열 값의 평균입니다.

IF/SUM

{=SUM(IF($A$2:$A$10=F20,IF($G$2:$G$10>=5,$I$2:$I$10)))}

위의 배열수식에서 AVERAGE -> SUM 으로 변경하였습니다. SUM에서도 FALSE는 합계로 집계되지 않습니다.

위의 배열 수식 실행 결과는 A2:A10중 F20이고 G열이 5 이상인 I열 값의 합입니다.

INDEX/MATCH/(MAX,SUM 등)

{=INDEX($C$12:$C$38,MATCH(MAX(($H$12:$H$38)*($D$12:$D$38=L31)),($H$12:$H$38)*($D$12:$D$38=L31),0))}

조건을 $D$12:$D$38=L31 처럼 수식안에 넣게되면 TRUE 이면 1 FALSE 이면 0을 반환합니다. 따라서 조건이 FALSE라면 MAX에서 집계되지 않습니다.

위의 배열 수식 실행 결과는 D열이 L31인 행중에서 H열이 최대값을 가진 행의 C열 값을 반환합니다.

처음 접하시는 경우 굉장히 생소하게 느껴질 수 있는 부분입니다. 저도 테스트하면서 조금 힘들었네요.^^

이외의 함수들은 기출문제에서 자신이 부족하다 느껴지는 함수를 사용하는 계산작업을 몇개 수행해 보시면 됩니다.

사용자 정의함수

지시 사항에 따라 아래의 스크린샷을 따라하시면 됩니다. 형식은 거의 같습니다. 코드에 관해서 스크린샷에서 설명드리겠습니다.

[개발 도구] - [코드] - [Visual Basic] 클릭

전역 함수 사용을 위해 프로젝트 창에서 오른쪽 단추 클릭후 모듈 삽입

조건에 맞게 코드를 작성합니다. 함수의 반환값은 셀에 입력될 것이며 함수 이름을 변수로 갖고 그것을 반환합니다. '수강료'처럼 인수를 입력 받을 수 있습니다.

저장후 [Alt] + Q 를 눌러 엑셀로 돌아와서 함수를 사용할 셀을 선택 후 [수식] - [함수 라이브러리] - [함수 삽입] 클릭

함수 마법사에서 사용자 정의 에서 저장한 함수 삽입

인수 지정 후 확인

여기 까지 완벽하게 익히셨다면 엑셀 100점중 55점을 획득 하실 수 있습니다. 질문은 댓글을 달아주세요!

[ Score 55/100 ]

저작자표시 비영리 변경금지

ID UP 아이디업 취미활동