엑셀(스프레드시트) 이론 총 정리/단축키 정리 에서 엑셀 실기의 전체적인 이론과 숙지하면 좋을 단축키를 정리하였는데요, 이제 본격적으로 실제 기출 문제 풀이에 있어 알아야 할 실전 전략들을 정리해 봅니다.
실전 전략
앞서 실기 빠르게 합격하기! / 직장 병행 후기 [실기 합격 후기] 에서 정리했었죠? 실전 준비에 앞서 다시 한번 정리해 봅니다. 실기 시험에서의 배점과 목표 점수는 아래와 같습니다.
스프레드시트
출제 문제
배점 [점]
목표 점수
기본작업
기본작업1
고급 필터
5
10점 이상
기본작업2
조건부 서식
5
기본작업3
페이지 레이아웃 / 시트 보호
5
계산작업
함수 2Q / 배열 수식 2Q / 사용자 정의 함수 1Q
30
18점 이상
분석작업
분석작업1
피벗 테이블
10
20점
분석작업2
데이터 도구
10
기타작업
기타작업1
차트 수정 5Q
10
30점
기타작업2
매크로 2Q
10
기타작업3
프로시저 작성 3Q
15
합계 // 합격 점수 : 스프레드시트, 데이터베이스 평균 70점
100점
78점
그럼, 실전에서의 전략을 어떻게 짜야 할까요?
1) 일반적인 경우, 계산작업에 취약하다면 위의 표는 보통 어려워들 하는 계산작업에서 많은 오답을 허용하는 경우입니다. 기본작업 최대한 만점 / 계산작업에서 절반 이상 / 분석작업 만점 / 기타작업 최대한 만점 를 해야 합격 안정권입니다. 이 때 기본작업에서 비교적 어렵다고 생각하는 조건부 서식이나, 기타 자잘한 실수를 허용할 수 있겠습니다. 또 기타작업에서 비교적 어려운 프로시저를 작성까지는 하되, 추가적인 꼬리문제를 풀지 못하는 경우입니다.
2) 어려운 문제를 포기한다면 만약 어려운 계산작업의 사용자 정의 함수(6점)와, 프로시저 작성(15점)을 아예 포기한다면 어떨까요? 전체 100점에서 21점을 포기하고 들어가므로, 합격 안정권을 위해 최소 75점을 목표로 할 때 기본/계산/분석/기타작업에서 감점 4점만을 허용하게 됩니다. 이 경우라면, 다른 영역을 전부 만점을 목표로 하고 자잘한 실수만을 허용한다는 의미입니다. 즉, 기본작업 만점 / 계산작업 함수, 배열수식 전부 맞추기 / 분석작업 만점 / 기타작업 차트수정, 매크로 전부 맞추기 를 해야 합니다.
추가적으로 컴활 실기 시험장에서 확인할 세팅 옵션을 정리하겠습니다.
매크로 실행 확인 : [파일] > [옵션] > [Excel 옵션] > [보안센터]-[보안센터 설정]-[매크로 설정]-'모든 매크로 포함' 체크
함수 입력 시 도움이 되는 스크린 팁 : [파일] > [옵션] > [고급] > [표시]-'함수 화면 설명 표시' 체크
색상, 차트 등 마우스를 올렸을 때 이름과 설명 보이는 팁 : [파일] > [옵션] > [Excel 옵션] > [일반]-'실시간 미리보기 사용' 체크, 화면 설명 스타일-'화면 설명에 기능 설명 표시' 체크.
개발 도구 메뉴 생성 : [파일] > [옵션] > [Excel 옵션] > [리본 사용자 지정]-'개발도구' 체크
팁) FX함수 마법사 활용하기
모르는 함수가 나왔을 때 : FX함수 마법사 클릭 > 모르는 함수 선택 후 왼쪽 하단 '도움말' 클릭 > 함수 설명 및 예제 확인 가능
최근 사용 함수 조회 : FX함수 마법사 클릭 > 최근 사용한 함수 확인 (최근 응시자가 사용한 함수들이므로 나올 가능성이 큼)
자신만의 전략을 마음에 두었다면, 이제 기출문제 풀이와 실전에서 꼭 숙지해야 할 꿀팁들을 정리해 보겠습니다.
영역 별 실기 분석
[기본작업]
기본작업1 : 고급필터
고급필터는 [데이터]>[정렬 및 필터]>[고급]에 있다.
고급필터를 적용할 때는 필드명 (첫줄) 을 포함하여 목록범위를 지정한다.
조건범위에 조건을 입력할 때, And에 해당하는 조건은 동일한 행에, Or에 해당하는 조건은 다음 행에 작성한다.
이 경우 조건에서 텍스트와 숫자에 ""은 들어가지 않는다. 그냥 그대로 쓴다.
조건범위가 아래 위의 단 두 셀만 허용된다면, 위에는 필드명을 조건으로 지정하고 아래에 =~ 하고 조건을 입력한다.
이 경우 첫 셀에 대해서만 조건을 입력하며 텍스트에 ""을 넣는다.
복사위치에 넣어야 할 필드명만을 복사하여 붙여두고 지정해두면, 알아서 해당 필드명 아래에 데이터가 필터되어 붙는다.
기본작업2 : 조건부서식
조건부서식은 [홈]>[스타일]>[조건부서식]에 있다.
[수식을 사용하여 서식을 지정할 셀 결정]에서 입력하는 조건에서는, 텍스트에 ""가 들어간다.
조건을 입력할 때, 데이터의 첫번째 행에 해당하는 셀만 입력하고 범위를 데이터 전체로 지정하면 알아서 조건이 적용된다.
조건에서는 첫번째 셀만 지정, 적용대상에 전체 데이터 지정
- weekday 함수 : 기본적인 weekday=1 은 일요일이다. - 시간 관련 함수 : 아무 함수 없이 셀 자체를 입력할 때 1이 24시이다. 즉, 0.5가 12시이다. hour 함수 : 0-23을 사용할 수 있다. text 함수 : Text(셀,“hh”) = “시간“ - 세 번째 행마다 적용하는 조건부 서식 = MOD(ROW(),3)=0 - ‘A’가 상위 10위 이내이고, ’B’가 10 미만이거나 20 이상인 행에 적용하는 조건부 서식 = And(RANK(셀,전체 영역)<=10, Or(‘B’<10,’B’>=20) - ‘A’가 하위 5위 이내에 포함되는 행에 적용하는 조건부 서식 = ‘A’<=Small(전체 영역,5) - 최댓값과 최솟값 구하기 = And(셀=Large(전체 영역,1),셀=Small(전체 영역,1) = And(셀=Max(전체 영역),셀=Min(전체 영역)) = And(RANK.EQ(셀,전체영역,0)=1,RANK.EQ(셀,전체영역,1)=1)
왼쪽에서 오른쪽으로 정렬 : [정렬]>[옵션]>'왼쪽에서 오른쪽으로 정렬' 선택
기본작업3 : 페이지 레이아웃 / 시트 보호
인쇄영역 설정은 [페이지레이아웃]-[페이지설정]-[인쇄영역]-[인쇄영역설정]에 있다.
여백, 머리글/바닥글, 인쇄 제목, 반복할 행/열은 [페이지레이아웃]-[페이지설정]에서 할 수 있다.
머리글/바닥글 에 홀수쪽, 짝수쪽을 다르게 지정하기
'짝수와 홀수 페이지를 다르게 지정하기' 체크
나누기는 [페이지레이아웃]-[페이지설정]-[나누기]-[페이지 나누기 삽입]에서 할 수 있다.
한 페이지에 모든 열이 인쇄되도록 용지 너비를 조절하려면 [페이지레이아웃]-[페이지설정]-[페이지] 에서 배율에 '자동맞춤'을 선택하고 용지 너비에만 '1'을 입력한다.
페이지 나누기 미리보기는 [보기]-[통합 문서 보기] 그룹에서 선택한다. 여기서 페이지 나누기 구분선을 드래그해 조절할 수 있다.
셀 보호는 우클릭>[셀 서식] 에서, 시트 보호는 [검토]>[보호] 에서 지정한다.
셀 잠금과 수식 숨기기 : [셀 서식] '잠김', '숨김' 클릭
잠긴 셀 선택과 잠기지 않은 셀 선택 : [검토]>[보호] '잠긴 셀 선택', '잠기지 않은 셀 선택' 체크
[계산작업]
계산작업1 : 함수 2Q
기출을 많이 접하는 게 최고의 방법이다. 1급 엑셀(스프레드시트) 실기_총 정리에서 전반적인 함수를 훑어 보고, 함수의 앞글자들 위주로 암기하면 자동완성되는 함수를 사용하면 된다. 중요한 것은, 해당 함수에 들어가는 인자들을 어떻게 활용하느냐를 암기하는 것이다. 함수를 입력할 때 기본적인 도움말은 나오지만, 영단어만을 믿지 말고 나만의 방법으로 외우는 것이 좋다. 아래에 틀리기 쉬운 함수들을 몇 가지 정리해 본다. (추가 업데이트 중)
- AVERAGEIF(조건영역,조건,평균영역) - INDEX(범위,행번호,열번호[,참조영역번호]) - Int(수치) : 수치 이하의 가장 큰 정수 - DSUM(데이터베이스 범위,필드 번호, 조건 범위)
계산작업2 : 배열 수식 2Q
행과 열에 맞는 이름 별 값을 찾을 때, (행 이름=범위)*(열 이름=범위)*(원하는 값 범위) 로 배열 수식을 입력한다.
- INDEX, MATCH, MAX, IF 를 활용하는 배열 수식 : INDEX(찾을 값 영역,MATCH(MAX(IF(조건,계산할 영역),(조건)*(계산할 영역,0)) - 배열에서 조건을 나열할 때 And는 *, Or은 +을 사용한다. 예) Month 가 6~7월인 경우의 영역 평균 : =Average(IF(MONTH(일자필드=6)+MONTH(일자필드=7),계산영역))
계산작업3 : 사용자 정의 함수 1Q
Visual Basic 빠른 실행 단축키는 Alt+F11이다. 닫을 때는 Alt+F4을 사용한다.
[삽입]-[모듈]을 클릭한다.
대부분의 사용자 정의 함수는 Public Function 으로 시작한다.
사용자 정의 함수를 적용하려고 할 때 인수를 입력하는 화면이 보이지 않으면 [개발도구]-[코드]-[매크로 보안]에서 모든 매크로를 포함하는 선택메뉴를 지정해야 한다. 위험성 있는 코드가 실행될 수 있으므로 권장하지 않는다고 하지만 컴활을 위해서는 지정하자.
IF ~ END IF
And 와 Or을 혼합한 If 문을 작성할 때는 If (조건1 And 조건2) Or (조건3 And 조건4) 로 괄호를 묶어준다.
IF ~ ELSEIF ~ ENDIF
If 조건1 Then 결과1 ElseIf 조건2 Then 결과2 Else / 조건1도 2도 아닌 결과3 End If
SELECT CASE
변수를 인수로 받아 계산하여 fn함수 를 되돌려 줌.
Selcet Case 변수 Case Is 조건1 변수값1 Case Is 조건2 변수값2 Case Else 변수값3 End Selct
fn함수 = 변수를 활용한 계산식
IF ~ AND ~ END IF
인수를 활용한 새로운 변수를 이용해 fn함수 를 되돌려 줌.
Public Function fn함수(인수1,인수2) 변수 = 인수1, 인수2를 활용한 계산식 if 조건1 AND 변수1에 대한 조건2 Then fn함수 = 결과1 Else fn함수 = 결과2 End If End Function
[분석작업]
분석작업1 : 피벗테이블
외부에서 엑세스 파일을 가져올 때는 [데이터]-[외부데이터 가져오기]-[기타원본에서]-[Microsoft Query]-'Ms Access Database *'을 선택한다.
외부에서 csv 파일을 가져올 때는 [삽입]-[표]-[피벗 테이블]-[피벗 테이블 만들기], '데이터 모델에 이 데이터 추가' 를 체크하고, '외부 데이터 원본 사용' 에서 '연결 선택'을 클릭한다. [연결 도는 표 선택]에서 파일을 선택한다.
피벗 테이블 보고서에서, 그룹화는 행 레이블의 필드에서 우클릭-[그룹]에서 지정할 수 있다.
피벗테이블의 생김새는 [피벗테이블 도구]-[디자인]-[레이아웃] 그룹에서 [보고서 레이아웃]의 옵션들로 바꿀 수 있다. 보통 '개요 형식으로 표시'를 많이 선택하게 된다.
계산필드는 피벗 테이블 안쪽에 커서를 두고 [피벗테이블 도구]-[분석]-[계산] 그룹에서 [필드 항목 및 집합]-[계산 필드]를 선택한다. [계산 필드 삽입]에서 근본이 될 데이터 필드를 선택하여야 한다. 이후 생성된 계산필드에서, 우클릭 하여 [값 필드 설정]에 들어간 후 값 표시 형식을 변경한다.
만일 이미 피벗 테이블을 만든 후 필드 수정이 필요하면, 테이블 내 커서를 두고 [피벗 테이블 분석]-[데이터 원본 변경]-[연결 속성]-[정의] 에서 쿼리 편집을 선택한다.
자동서식 보고서1 의 형식을 지정할 때 : [파일]-[옵션]-'빠른 실행 도구 모음'-'모든 명령'-'자동 서식 추가' > 피벗테이블 안쪽 커서를 두고 [자동 서식] 도구 선택 > '보고서1' 형식 선택
셀 서식
1,000 단위 쉼표가 들어간 서식 : #,###,
레이블이 있는 셀 병합 및 가운데 맞춤, 빈 셀 표시, 오류 값 표시 : [피벗 테이블 도구]-[분석]-[피벗 테이블]
사용자 지정 그룹 필드
[피벗 테이블 분석]-[그룹]-[선택 항목 그룹화]
그룹 셀을 선택 후 텍스트 편집을 하면 그룹 필드명 수정이 가능하다.
분석작업2 : 데이터도구
데이터 유효성 검사는 영역을 범위 지정한 후 [데이터]-[데이터 도구] 그룹에서 선택할 수 있다. 오류 메시지, 설명 메시지도 여기서 편집한다.
사용자 지정 수식
A3:A20 영역의 값이 중복되지 않게 하기 CountIf($A$3:$A$20,A3)=1
필드에 목록단추를 만드는 단축키는 Ctrl+Shift+L 이다.
먼저 목록단추를 만들고 사용자 지정 필터를 적용한다.
부분합 이중 적용의 경우, 두 번째 부분합 적용 시 '새로운 값으로 대치' 체크를 해제하면 된다.
[기타작업]
기타작업1 : 차트 수정 5Q
섞여있는 데이터 중 원하는 계열만 선택 후 우클릭하여 [계열 차트 종류 변경]을 할 수 있다.
차트에 각 값을 표시하기 위해서 우클릭하여 [데이터 레이블 추가]를 한다.
차트 요소의 경우 차트 우상단에 있는 + 모양 단추 (차트요소) 를 누르면 추가할 수 있다.
범례의 경우 차트요소에서도 위치 지정을 할 수 있다.
대부분의 서식은 계열 선택한 후 오른쪽에 나오는 서식 탭에서 변경할 수 있다.
보조 축 선택 : 원하는 계열 우클릭 > '데이터 계열 서식' > '계열 옵션' > '보조 축'
차트 제목 연결 : 차트 제목 클릭 > 수식 입력줄에 = 입력 후 연결할 셀 클릭 (주소가 입력됨) > Enter
주어진 데이터 추가하기 : 차트 위에 빠진 데이터 영역을 복사한 후, 차트를 선택한 뒤 붙여넣기 한다.
또는, [데이터 선택] 메뉴에서 직접 편집한다.
기타작업2 : 매크로 2Q
매크로를 만들기 위해 빈 셀을 클릭하고, [개발도구]-[코드] 그룹에서 [매크로 기록]을 누른 순간부터 [기록 중지]를 누를 때까지 저장할 내용을 이행한다.
셀 서식의 단축키는 Ctrl+1 이다.
사용자 지정 서식의 경우, 셀 값이 ★일 경우 빨강색 @(문자)서식으로 표시 → [빨강][=★]@;
만약 셀 값에 대해 조건이 지정된다면 → [색상1][조건1]서식1; [색상2][조건2]서식2;서식3
보통 따로 조건이 없다면 형식은 양수;음수;0;문자 이다.
입력 가능한 최대 조건은 총 2개이다.
주어진 조건에 해당되지 않는 경우 ######으로 나타날 수 있으며, 이 경우 조건이 없는 서식에 대해서도 0; 으로 추가적으로 서식을 지정해 줘야 한다.
천 단위 생략 표시 : 0,
입력된 숫자 그대로 표시 : G/표준
소숫점 없는 숫자 : 0 (필수) 또는 # (선택)
매크로 저장 후 단추의 경우 Alt 을 누르고 드래그하면 줄에 딱 맞게 그릴 수 있다.
- 앞의 두 자리와 뒤의 세 자리 사이를 +으로 채우기 = ##*+### - 아이콘 집합 - 5가지 색(흑백), 숫자 90 이상이면 꽉 찬 원, 70 이상이면 원 1/4, 나머지는 기본 설정으로 두시오. 꽉 찬 원과 원 1/4 만 숫자로 종류를 바꾸고, 나머지는 기본 값 그대로 둔다.
기타작업3 : 프로시저 작성 3Q
기출을 많이 접하는 게 최고의 방법이다. 아래에 빈출 코드를 정리해 본다. (추가 업데이트 중)
*[개발도구]-[컨트롤]-[디자인모드] 를 선택하면 버튼을 편집상태로 만들 수 있다. *프로시저 입력 팁 : cdate, val, Value 사용을 하는 경우 Cells(입력행,2) = cdate(txt날짜) —> 날짜 형태로 변경됨 Cells(입력행,2) = val(txt나이) —> 숫자 형태로 변경됨 Cells(입력행,2) = txt나이.value —> 입력한 그대로 표시가 됨 (날짜 형식으로 입력하면 날짜로, 숫자 형식으로 입력하면 숫자로, 텍스트 형식으로 입력하면 텍스트로) Cells(입력행,2) = txt나이 —> 텍스트 형태로 입력됨 ** 데이터 원본을 쓰고 어떤 걸 쓸지 판단한다.
폼 보이기
- 버튼을 클릭하면 폼이 나타나게 하기 Private Sub 버튼_Click() 폼.Show End Sub
폼 초기화
- 폼이 초기화되면 특정 영역(Cmb영역) $H$4:$H$6 에 값이 설정되게 하기 / Text등록일에 오늘 날짜의 연도를 표시하기 Private Sub UserForm_Initialize() Cmb영역.RowSource = "$H$4:$H$6"
쌍따옴표 사용
만약 RowSource가 두 열 이상의 여러 열이라면, n개 열일 때
Cmb영역.ColumnCount=n
Cmb영역.RowSource=" " 으로 ColumnCount을 추가해 준다.
Text등록일 = Year(Date) End Sub
- 폼이 실행되면 Cmb콤보상자에 'A', 'B', 'C'가 추가되는 프로시저 작성하기 Private Sub UserForm_Initialize() With Cmb콤보상자 .AddItem "A" .AddItem "B" .AddItem "C" End With End Sub
- 포커스가 Txt컨트롤 로 옮겨가는 프로시저 작성하기 Txt컨트롤.SetFocus
폼 종료 후 메시지 박스 띄우기
Private Sub Cmd종료_Click() Unload Me MsgBox "종료 메시지" End Sub
셀의 데이터가 변경되면 해당 셀의 글꼴이 '굴림체', '굵게' 변경되도록 한다. Private Sub Worksheet_change(ByVal Target As Range) With Target .Font.Name="바탕체" .Font.Bold=True End With End Sub
With ~ End With
Target 을 여러 번 쓰지 않아도 된다.
Activate 프로시저
Private Sub Worksheet_Activate() [셀주소]="텍스트" End Sub
계산 이벤트 (정수)
Private Sub Cmd계산_Click() lbl계산 = Int(txt인자_Value / txt인자2_Value) End Sub
Int : 정수로 표현해주기
txt _Value : txt 에 입력된 값. 생략도 가능하다.
입력 프로시저
- 폼의 입력 버튼(cmd입력)을 누르면 A,B,C,D 인자를 받아 특정 계산이 표에 입력되게 하기. 이 때 조건에 따라 C의 값이 바뀌게 하기.
Private Sub cmd입력_Click()
i=Range("B3").CurrentRegion.Rows.Count+2
i로 변수를 선언하고 필드의 첫 셀(값 A)에 해당하는 주소 B3을 Range().CurrentRegion 에 입력한다. 새롭게 데이터를 입력할 마지막 행의 위치를 구하기 위해 +2를 더한다. 이 때, 새로운 데이터는 4행에 입력되지만, 2행과 3행에는 [표1]과 필드명이 입력되어 있으므로 연결된 행으로 인식되어 실제로 비어있는 행은 1행 뿐이다. 따라서, 1행과 새로 데이터가 입력될 그 다음 행까지 총 2를 더해 준다.
그런데 만약, [표1]이 아래 그림과 같이 필드명과 떨어져 있다면 연결된 행으로 인식되지 않으므로 총 3을 더해야 한다.
iRow=cmb영역.ListIndex+4
iRow는 참조표 (cmb영역) 에서 찾아올 행 위치를 기억한다. 참고표 에서 첫번째 데이터는 4행에 있으므로, +4을 한다.
이 때, iRow는 임의로 만든 변수이므로 다른 변수 이름으로 작성해도 된다.
Cells(i,2)=A Cells(i,3)=B If 조건 Then Cells(i,4)="오류" Else If Cells(i,4)=계산식 End If Cells(i,5)=D
2~5는 각 열번호를 매칭해준다.
만약 셀에 들어갈 데이터가 Text날짜 라면 Text날짜.Value 을 붙여준다.
값의 형식을 지정해주고 싶다면,
Format(Cells(i, n), "0.0"
등과 함께 마지막에 작성해 준다.
End Sub
조회 프로시저
- ListIndex, 참조표가 따로 없고, 주어진 표(내용표)의 내용을 cmd조회 에서 조회하면 내용에 따라 메세지 박스가 나오게 하기.
Private Sub cmd조회_Click() iRow=cmd내용표.ListIndex+2
ListIndex는 첫 번째 값을 선택하면 0, 두 번째 값을 선택하면 1이다. 1행의 빈 행과, ListIndex가 0으로 시작하므로 +1을 하여 총 +2를 더해준다.
Cells(iRow,2)=B Cells(iRow,3)=C If 조건 Then MsgBox (B & "입니다") End If End Sub
- 마지막행의 데이터 조회하기 Private Sub cmd조회_Click() i = Range("A2").CurrentRegion.Rows.Count+1
i는 마지막에 입력된 행의 위치를 구해 기억하는 변수이다. 조회 프로시저에서는 새로 입력이 없기 때문에, 새롭게 입력할 1행을 더하지 않아도 된다. 따라서 1행의 빈 공간만 +1 로 더해준다.
txtA=Cells(i,1) txtB=Cells(i,2) txtC=Cells(i,3) End Sub
댓글