Transcript 사무자동화산업기사(엑셀)

사무자동화산업기사
강사 : 장 성 현
HP : 010-2643-7824
E-mail : [email protected]
시험정보
•
•
•
•
•
•
시험 구분 : 국가기술자격
시행기관 : 한국산업인력관리공단
원서접수 및 합격자 발표 : www.q-net.or.kr
응시수수료: 1차(필기) – 18,000원, 2차(실기) – 29,700원
과목별 배점 : 엑셀(35점), 엑세스(35점), 파워포인트(30점)
최종 제출 출력물 : 엑셀(1장), 엑세스(2장), 파워포인트(1장)
출재 기준
• 검정 방법 : 작업형
• 시험 시간 : 약 2시간 40분
• 합격 기준 : 60점(과락 없음)
주요항목
세부항목
상세내용
웅응프로그램 설치 및 삭제
엑셀
엑세스
파워포인트
워크시트 작성
내장함수 응용
데이터 관리
그래프 작성
데이터 입력과 서식
수식 작성과 데이터 처리
데이터 검색과 분석
차트 구성과 설계와 축, 서식, 범례
자료 입력
화면 설계
자료 처리
데이블 폼 설계
데이터 입력 및 쿼리 작성
조건 처리 및 보고서 작성
화면설계
출력물 작성
화면 구성과 설계
도형, 표, 차트 작성
인쇄
표 및 그래프 출력
입력화면 출력
보고서 출력
슬라이드 출력
시간
약 30분
2시간
10분
사무자동화산업기사
엑셀
작업 순서
1
자료 입력
2
계산식(수식) 입력
3
서식 지정
4
차트 작성
5
인쇄
자료 입력
자료 입력 기본(1)
• 셀 포인터의 이동
–
–
–
–
마우스 클릭
방향키(화살표키) 입력
Enter, Tab, Shift+Enter, Shift+Tab
이름상자에 좌표 입력
• 자료의 입력
– 셀 포인터 이동 후 직접 입력
– 셀 포인터 이동 후 수식 입력 줄 클릭 후 입력
• 입력 환료 키
– Enter, Tab, Shift+Enter, Shift+Tab
• 입력 취소키
– ESC
자료 입력 기본(2)
• 하나의 셀에 여러 줄 입력
– 자료 입력 후 줄 바꿈 키(Alt+Enter) 입력
– Alt+Enter키는 입력 완려 키가 아님
• 지정된 범위에 동일 데이터 채우기
1.
2.
범위 지정 후 내용 입력
Ctrl+Enter 입력(지정된 범위에 현재 입력 내용이 복사되어 채워짐)
• 자동완성 기능
– 사용자가 입력 중인 열에 입력 중인 내용으로 시작하는 것이 한 종류
인 경우 나머지 부분을 자동으로 완성시켜 주는 기능
• 범위 지정 후 자료 입력
– 범위가 지정되어 있는 상태에서 자료를 입력할 경우 셀 포인터가 범위
안에서 자동으로 움직이는 기능을 활용한 입력
– 이때 마우스 클릭, 방향키를 이용하면 범위가 해제되므로 주의
자동 채우기(1)
• 채우기 헨들 용도
– 셀 삽입 및 삭제, 자료의 목사, 자동 채우기
• 문자 또는 숫자 데이터의 복사
1.
2.
순수 문자(숫자가 포함되지 않은 문자) 또는 숫자를 입력
입력된 설을 선택한 후 채우기 헨들 드래그
• 순번 채우기
1.
2.
초기 숫자 입력
입력된 셀을 선택한 후 Ctrl+채우기 헨들 드래그
• 일련번호 채우기
– 숫자와 문자를 혼용하여 입력
– 입력된 셀을 선택한 후 채우기 헨들 드래그
– 맨 오른쪽 부분의 숫자가 1씩 증가하며 채워짐
자동 채우기(2)
• 날짜 입력 방법
–
–
–
–
2012-1-2 또는 2012/1/2
2012-1 또는 2012/1
1-2 또는 1/2
현재 날짜 입력 바로 가기 키 : Ctrl+;
• 날짜 채우기
1. 최초 날짜 입력
2. 입력된 셀 선택 후 채우기 헨들 드래그
– 일일씩 증가하며 채워짐
자동 채우기(3)
• 시간 입력
– 1:10:00
– 1:10 pm
– 현재 시간 입력 바로 가기 키 : Ctrl+Shift+;
• 시간 채우기
– 초기 시간 입력
– 입력된 셀 클릭 후 채우기 헨들 드래그
– 한 시간 단위로 증가하며 채워짐
자료의 수정
• 자료가 입력된 셀에 내용을 입력하면 이전 자료는 지워지고
새로운 내용으로 치환됨
• 해당 셀 클릭 후 수식 입력줄을 통해 수정
• 해당 셀 클릭 후 F2키 입력 후 수정
• 해당 셀을 마우스 왼쪽 버튼 더블 클릭하여 수정
한자 입력(1)
• 일반적인 한자 입력
1.
한자 음을 입력한 후 “한자” 키 입력
Tab
2.
해당 한자 선택
한자 입력(2)
• 한자 단어 입력
1.
2.
한자 단어 입력
한자 단어를 드래그하여 선택하거나 단어 앞에 커서 위치
3.
해당 단어 선택 후 “변환(C)” 클릭
특수 문자 입력(1)
• 키보드 표준 입력 방식 이용
– 한글 자음 입력 후 “한자” 키 입력
Tab
– 해당 특수 문자 선택
특수 문자 입력(2)
• 삽입 메뉴의 기호
– “삽입(I)” 메뉴 선택 후 “기호(S)” 선택
– 해당 기호 선택 후 “삽입(I)” 단추 클릭
자료 입력 실습(입력 자료)
차량 운행시 유지비 현황
차량번호
배기량
주유량
연료단가
주행거리
수리비
2011
890
54
1500
200
35000
2223
2500
50
800
100
120000
1122
1000
70
1000
550
178000
3232
2500
60
1400
450
52000
2897
3400
55
100
350
53000
1437
2000
35
2000
150
67000
1002
1800
55
1200
100
75000
3042
2800
40
1350
80
85000
1120
3000
25
1000
90
120000
1032
3200
34
2500
120
135000
3092
1900
28
1400
110
95000
2156
2300
34
1500
220
48000
3552
3200
67
2300
156
34000
1543
3400
20
3200
100
95000
2157
5000
30
1900
200
25000
1137
4500
45
2400
120
35000
2262
3900
36
2000
140
65000
3233
5000
44
1000
110
78000
자료 입력 실습(작업표 형식)
운행시 차량 유지비 계산
차량번호
배기량
구분
연비
보험료
연료비
유지비
평가
-
-
①
②
③
④
⑤
⑥
⑦
⑦
⑦
평균
최우수와 우수인 차량들의 합
⑧
⑨
연비가 3이거나 최우수와 우수인 차량의 합
⑩
⑩
차량번호 끝자리가 2이고 불량인 차량의들의 합
⑪
⑪
배기량이 3000 이상 3500 미만인 합
⑫
⑫
자료 입력 실습(작업표 형식에 자료 입력)
• 작업표 형식을 기초로 입력 자료를 아래 그림과 같이 입력함
• 입력 항목 수 만큼 범위 지정 후 입력
삽입/삭제
• 셀 삽입
1.
2.
삽입 위치 및 범위 지정
바로 가기 메뉴 (마우스 오른쪽 버튼)의 “삽입” 메뉴 선택
• 셀을 오른쪽으로 밀기 : 기존 자료를 오른쪽으로 밀어 선택한 범위만큼 셀
삽입
• 셀을 아래로 밀기 : 기존 자료를 아래로 밀어 선택한 범위만큼 셀 삽입
• 행 전체 : 선택한 범위만큼 행 전체 삽입
• 열 전체 : 선택한 범위만큼 열 전체 삽입
• 셀 삭제
1.
2.
삭제할 자료 범위 지정
바로 가기 메뉴의 “삭제” 메뉴 선택
• 셀을 왼쪽으로 밀기 : 선택한 범위의 자료를 삭제하고 기존 자료를 왼쪽으
로 밀기
• 셀을 위로 밀기 : 선택한 범위의 자료를 삭제하고 기존 자료를 위로 밀기
• 행 전체 : 선택된 범위의 행 전체 삭제
• 열 전체 : 선택된 범위의 열 전체 삭제
계산식(수식) 입력
수식의 구성
값(상수)
=20 + 30 + 40
연산자
=A1 + B1 + C1
수식 시작 기호
=SUM(A1:C1)
함수
A1 : 좌표(참조:~셀에 값)
A1:C1 : 범위 좌표(범위 참조:~에서 ~셀까지의 값들)
좌표(참조) 유형
• 목적
– 패턴이 같은 수식을 복사하여 재사용하기 위함
• 상대좌표(참조)
– 복사하는 위치에 따라 변화는 좌표
– 모양 : A1, B1
• 절대좌표(참조)
– 복사위치와 상관없이 일정한 좌표
– 모양 : $A$1, $B$1
• 혼합좌표(참조)
– 상대좌표와 절대좌표의 특징이 혼합된 좌표
– 모양 : $A1, B$1
좌표(참조)의 입력
• 좌표 입력
– 직접 타이핑
– 마우스 클릭 또는 드래그
• 좌표 변환
– 좌표 입력 후 후 F4키로 좌표 유형 변환
• 수식 복사
– 채우기 핸들을 이용한 수식 복사
– 단축키 또는 메뉴에 의한 복사
좌표(참조)의 쓰임
• 복사하는 위치에 따라 변해야 하는 경우
– 상대좌표 사용
• 복사 위치와 상관 없이 변하지 말아야 하는 경우, 대
부분 기준이 되는 좌표
– 절대좌표 사용
• 일부분만 변해야 하는 경우. 가령 줄 번호는 변하고,
열 이름은 변하지 않아야 하는 경우
– 혼합좌표 사용
함수의 형식
• 함수는 복잡한 계산이나 대량의 데이터를 간편하게 계산해주
는 프로그램임
• 이를 호출하여 보다 빠르게, 효과적으로 계산
함수명( 전달인수 )
반드시 입력되어야 함
날짜 함수
함수의 형식
함수 설명
TODAY()
시스템으로부터 현재 날짜를 구함
(오늘 날짜를 구해라)
NOW()
시스템으로부터 현재 날짜와 시간을 구함
(현재 날짜와 시간을 구해라)
YEAR(날짜)
날짜에서 년도 추출
(이 날짜의 년도를 구해라)
MONTH(날짜)
날짜에서 월 추출
(이 날짜의 월 구해라)
DAY(날짜)
날짜에서 일 추출
(이 날짜의 일을 구해라)
WEEKDAY(날짜, 옵션)
인수의 순위를 구함
(이 날짜의 요일 번호를 구해라)
(옵션 1 : 일요일부터 번호를 매김, 2 : 월요일부터 번호를 매김)
DATE(년도, 월, 일)
년도값, 월값, 일값을 입력하여 엑셀의 날짜 형태로 변환
논리 함수
함수의 형식
함수 설명
IF( 조건, 참값, 거짓값)
처음 인수인 조건에 따라 계산
조건 판정시 참(만족)이면 참값 부분을 계산하고 거짓(만족하지 않
음)이면 거짓 값 부분 계산
AND( 조건, 조건, …. )
여러 조건을 “그리고”로 연결하여 판정함
모든 조건이 참(만족)이면 참값을 되돌려 줌
OR( 조건, 조건, … )
여러 조건을 “또는”으로 연결하여 판정함
조건 중 하나라도 참(만족)이면 참값을 되돌려 줌
IF 함수의 팁(말을 함수식으로 변환)
=IF( 조건 ,
만약
이면
참값 ,
거짓값)
아니면
아니고
• IF(G4>=80,”합격”, “불합격”)
– 만약 G4의 값이 80 이상 이면 합격 아니면 불합격
• 만약 등급(B4)이 A 이면 승전 아니면 공백으로 표시
– =IF(B4=“A”,”승진”,””)
• 만약 등급(B4)이 A 이면 200000 아니고 만약 등급이 B 이면
150000 아니면 100000으로 수당에 표시
– IF(B4=“A”,200000,IF(B4=“B”,150000,100000))
– 괄호가 두 개가 열렸으므로 두개를 닫아 주어야 함
IF 함수 팁(둘 이상의 조건)
만약 등급 이 A 이고 고가가 80 이상 이면 승진대상 아니면 공란으로 표시
조건
조건
이고(그리고)
조건 연결
AND
=IF(AND(B4=“A”,C4>=80),”승진대상”,””)
조건이 둘 이상이 있고 이들 조건이 이고(그리고), 이거나(또는)으로
연결되는 경우 AND 함수, OR함수로 묶어 주어야 함
수학/삼각 함수
함수의 형식
함수 설명
SUM( 인수1, 인수2, … )
인수들의 합을 구합
(야! SUM, 이 인수들의 합을 구해라)
SUMIF( 조건범위, 조건, 합계범위)
조건을 만족하는 자료의 합계를 구함
(부서가 영업2부인 자료의 1월의 합계를 구해라)
ROUND( 인수, 자릿수 )
반올림
ROUNDUP( 인수, 자릿수)
올림
ROUNDDOWN( 인수, 자릿수)
내림
TRUNC(인수, 자릿수)
ROUNDDOWN과 거의 동일함(단 자릿수 0은 생략 가능)
ABS(인수)
인수의 절대값 구함 (이 값의 절대값을 구해라)
(절대값 : 양수 값, 음수 값 모두 양수로 표현)
POWER(인수, 지수)
거듭 제곱을 구함
(이 값의 지수승을 구해라)
INT(인수)
소수 이하 부분을 차감하여 정수를 구함
(이 값을 정수로 만들어라)
MOD(인수, 재수)
인수를 재수로 나눈 나머지를 구함
(이 값을 이 값으로 나눈 나머지를 구해라)
자릿수의 의미
양수 : 소수 이하 몇째 자리까지 표시해라
0 : 소수점 자리까지 표시해라
음수 : 소수 이상의 자리에서 처리해라
(처리 : 반올림, 올림, 내림)
통계 함수
함수의 형식
함수 설명
AVERAGE( 인수1, 인수2, … )
인수들의 평균을 구함
(이 인수들의 평균을 구해라)
MAX( 인수1, 인수2, … )
인수들의 최대값 구함
(이 인수들의 최대값을 구해라)
MIN( 인수1, 인수2, … )
인수들의 최소값 구함
(이 인수들의 최소값을 구해라)
COUNT( 인수1, 인수2, … )
인수들 중 숫자의 개수 구함
(이 인수들 중 숫자의 개수를 구해라)
COUNTIF(조건범위, 조건)
조건을 만족하는 자료의 개수를 구함
(부서가 영업2부인 인원 수를 구해라)
RANK(인수, 범위, 옵션)
인수의 순위를 구함
(옵션 0 또는 생략 : 내림차순 순위, 0이 아닌 값 : 오름차순 순위)
(이 값이 이 범위에서 순위가 어떻게 되는가?)
데이터베이스 함수
함수의 형식
함수 설명
DSUM
조건을 만족하는 자료를 뽑아 지정 항목의 합계를 구함
DAVERAGE
조건을 만족하는 자료를 뽑아 지정 항목의 합계를 구함
DCOUNT
조건을 만족하는 자료를 뽑아 지정 항목의 숫자의 갯수를 구함
DCOUNTA
조건을 만족하는 자료를 뽑아 지정 항목의 갯수를 구함
DMAX
조건을 만족하는 자료를 뽑아 지정 항목의 최대값을 구함
DMIN
조건을 만족하는 자료를 뽑아 지정 항목의 최소값을 구함
데이터베이스 함수명( 데이터베이스 범위, 계산 항목, 조건범위)
표 전체 범위
(단 요약 부분 제외)
계산할 항목 이름, 이름
의 좌표, 열 번호 중 하나
입력
조건을 시트에 기록
한 후 그 범위 입력
데이터베이스 조건 입력
• 기본 형식
항목 이름
조건
• 조건이 이고(그리고)로 연결되는 경우(같은 줄에 조건 기록)
항목 이름
항목이름
조건
조건
• 조건이 이거나(또는)로 연결되는 경우(서로 다른 줄에 조건
기록
항목 이름
항목이름
조건
조건
문자열 함수
함수의 형식
함수 설명
LEFT(문자열, 문자 수 )
문자열의 왼쪽에서부터 지정 문자 수만큼 추출
(이 문자열의 왼쪽에서부터 몇 문자 뽑아라)
MID(문자열, 위치, 문자 수)
문자열의 지정 위치에서부터 지정 문자 수만큼 추출
(이 문자열의 이 위치에서 몇 문자 뽑아라)
RIGHT(문자열, 문자 수 )
문자열의 오른쪽에서부터 지정 문자 수만큼 추출
(이 문자열의 오른쪽에서부터 몇 문자 뽑아라)
찾기/판정 함수
함수의 형식
함수 설명
VLOOKUP(검색 값, 검색 및 추출범
위, 열 번호, 옵션)
검색하고자 하는 값을 검색 범위 및 추출 범위의 왼쪽 열을 대상으
로 검색하고 일치하는 자료가 있으면 그 범위의 지정 열 셀 값을 가
져옴
옵션
일치하는 값 검색 : 0, FALSE
구간 또는 근사 검색 : 1, TRUE, 생략
HLOOKUP(검색 값, 검색 및 추출범
위, 행 번호, 옵션)
검색하고자 하는 값을 검색 범위 및 추출 범위의 첫 행을 대상으로
검색하고 일치하는 자료가 있으면 그 범위의 지정 행 셀 값을 가져
옴
옵션은 VLOOKUP과 동일
ISNUMBER(인수)
인수로 전달된 값이 숫자인지를 판정
숫자이면 TRUE, 그렇지 않으면 FALSE를 되돌려 줌
ISERROR(인수 또는 계산식)
전달된 인수가 오류 값인지 판정
오류이면 TRUE, 그렇지 않으면 FALSE를 되돌려 줌
ISTEXT(인수)
인수로 전달된 값이 텍스트(문자열)인지 판정
텍스트이면 TRUE, 그렇지 않으면 FALSE를 되돌려 줌
ISBLANK(인수)
인수로 전달된 값이 공백인지 판정
공백이면 TRUE, 그렇지 않으면 FALSE를 되돌려 줌
배열 수식
• 배열
– 자료가 연속적으로 입력되어 있는 것
– 표, 셀 범위
• 배열의 표현
– “={1,2,3;4,5,6}” 입력 후 Ctrl+Shift+Enter 입력하여 입력 완료
1
2
3
4
5
6
• 배열 수식 입력 유형
– 하나의 셀에 배열 수식 입력
– 셀 범위를 지정 후 배열 수식 입력
• 이 경우 범위의 일부만 변경하는 것은 불가능
• 배열 수식을 이용하여 조건을 만족하는 자료의 합계 계산
– “=SUM((A4:A20=“영업부”)*D4:D20)”
– 이때 조건은 괄호”()”로 묶어 주어야 함
배열 관련 함수
함수의 형식
함수 설명
SUMPRODUCT(배열, 배열2, …)
배열의 각 요소의 값을 곱한 후 이들의 합계를 구함
FREQUENCY(배열, 구간배열)
구간별로 배열의 자료의 출현 빈도를 구함
SUMPRODUCT 함수의 활용
평가가 최우수와 우수인 차량의 유지비의 합계를 구하시오
=SUMPRODUCT((ISNUMBER(FIND(“우수”,L4:L21))*K4:K21)
부서가 T영업 또는 S영업이면서 황씨인 자료의 실적의 합계를 구하시오
=SUMPRODUCT((LEFT(B5:B24,1)=“황”)*(ISNUMBER(FIND(“T”,A5:A24))+ISNUMBER(FIND(“S”,A5:A24))),G5:
G24)
배열 수식에서 조건의 연결
그리고(AND:*) : 조건 * 조건
또는(OR:+) : 조건 + 조건
계산식 입력 팁(IF 함수)
• 조건에 따른 계산 입력
1.
해당 조건의 계산식을 아래와 같이 풀어 적어 본다.
민약 차량번호 첫 글자 = “1” 이면 “승용차” 아니고 만약 차량번호
첫 글자 = “2” 이면 “승합차” 아니면 “화물차”
2. 상수나 좌표로 기록할 수 없는 것을 찾는다.
3. 그 부분만을 계산식 또는 함수식으로 표현한다.
예) =LEFT(A4,1)
4. 표현된 함수식 또는 계산식을 IF 함수 식의 치환하여 기록하여 함수
식을 완성한다.
예) =IF(LEFT(A4,1)=“1”,”승용차”,IF(LEFT(A4,1)=“2”,”승합차”,”화물차”))
계산식 입력 팁(식이 주어지는 경우)
1. 처음 식이 입력되는 위치를 기준으로 각 항목 값의 좌표를
항목 위에 기록
2. 좌표 또는 상수로 적을 수 없는 경우 그 부분만을 계산식을
기록
• 예)
– 유지비 = 보험료 + 연료비 + 수리비
I4
F4
보험료 = 만약 구분(G4) = “승용차” 이면 40,000 아니고 만약 구분(G4)
= “승합차 이면 60,000 아니면 120,000
=IF(G4=“승용차”,40000,IF(G4=“승합차”,60000,120000))
결과 : =IF(G4=“승용차”,40000,IF(G4=“승합차”,60000,120000))*I4*F4
계산식 입력 팁(식이 주이지지 않는 경우)
1. 직접 계산을 한다는 마음으로 계산 절차 기록
2. 각 절차별 함수식 기록
– 대부분 앞의 함수식이 뒤쪽 함수식에 삽입되는 행태임
• 예) 주민번호 앞 6자리를 이용하여 생년월일을 날짜 형태로
표시하시오
1.
2.
3.
4.
년도 추출 : LEFT(주민번호(I4),2)
월 추출 : MID(주민번호(I4),3,2)
일 추출 : MID(주민번호(I4),5,2)
이를 엑셀의 날짜형태로 변환 : DATE(년도, 월, 일)
• 결과 : DATE(LEFT(I4,2), MID(I4,3,2), MID(I4,5,2))
작성 조건(수식 입력 실습)
가. 제목 서식 16포인트 크기로 하고 가운데 표시(제목 밑줄)
나. 글꼴서체 : 임의 선정
다. 원문자가 표시된 셀은 아래의 방법을 이용함
①
②
③
④
⑤
⑥
⑦
⑧
⑨
⑩
⑪
⑫
구분 : ‘승용차”, “승합차”, “화물차”로 표기한다.
(단, 주어진 자료에서 차량번호 첫째 자리 숫자가 “1”이면 “승용차”, “2”이면 “승
합차”, “3”이면 “화물차”로 표기한다.)
연비 : 주행거리 ÷ 주유량(정수 처리)
보험료 : 차량 종류에 따른 금액
(단, 보험료는 승용차이면 40,000원, 승합차이면 60,000원, 화물차이면 120,000원)
연료비 : 연료단가 × 주유량
유지비 : 보험료 + 연료비 + 수리비
평가 : 연비가 6 이상이면 “최우수”, 6 미만 4 이상이면 “우수”, 그 외는 “불량”
평균 : 각 항목별 평균 산출
최우수와 우수인 차량의 유지비 합 산출
항목 ⑧ 산정시 사용한 함수식 기재
(단, SUMPRODUCT, ISNUMBER, FIND 함수 모두 사용한 함수식 기재)
연비가 3 이거나 최우수와 우수인 차량들의 각 항목별 합 산출
차량번호 끝자리가가 2 이고 불량인 차량들의 항목별 합 산출
배기량이 3000 이상 35000 미만인 차량의 각 항목별 합 산출
행 서식
• 행 놀이 변경
1.
2.
3.
행 번호를 클릭 또는 드래그 하여 행 선택
바로 가기 메뉴의 “행 높이” 메뉴 선택
포인트 단위로 높이 입력 후 “확인” 버튼 클릭
• 마우스를 이용한 행 높이 변경
– 행 번호의 아래 경계선 드래그
– 행 번호를 드래그하여 행 선택 후 기준이 되는 행 번호 아래 경계선 드래
그
• 행 높이 자동 맞춤
1.
2.
행 번호 클릭 또는 드래그하여 행 선택
행 번호의 아래 경계선을 더블 클릭
• 행 숨기기
– 행 번호 클릭 또는 드래그하여 행 선택
– 바로 가기 메뉴의 “숨기기” 메뉴 선택
• 행 숨기기 취소
– 숨겨진 위쪽 행에서부터 숨겨진 아래 행까지 드래그하여 선택
– 바로 가기 메뉴의 “숨기기 취소” 메뉴 선택
계산 식 입력(1)
번호
설명
1
1. 차량 번호의 첫 문자 : LEFT(차량번호(A4),1)
2. 만약 첫문자 = “1” 이면 “승용차” 아니고 만약 첫문자 = “2” 이면 “승합차”
아니면 “화물차
IF( 첫문자=“1”,”승용차”,IF(첫문자=“2”,”승합차”,”화물차))
3. LEFT 함수식 첫문자에 치환
=IF(LEFT(A4,1)=“1”,”승용차”,IF(LEFT(A4,1)=“2”,”승합차”,”화물차”))
2
1. 주행거리 / 주유량
E4
C4
2. 결과를 정수로 처리 : INT(연비)
3. “E4/C4” 계산식 INT 함수의 연비와 치환
=INT(E4/C4)
3
1. 만약 구분(G4) = “승용차“ 이면 40000 아니고 만약 구분(G4)=“승합차
“ 60000 아니면 12000
=IF(G4=“승용차”,40000,IF(G4=“승합차”,60000,120000))
4.
1. 연료단가 * 주유량
C4
D4
=C4*D4
계산 식 입력(2)
번호
설명
5
1. 보험료 + 연료비 + 수리비
I4
J4
F4
=I4+J4+F4
6
1. 만약 연비(H4)>=6 이면 “최우수” 아니고 만약 연비(H4)>=4 이면 “우수”
아니면 “불량”
연비가 6 이상이 아니라면 6 미만이라는 의미이므로 “연비<6” 조건은 기
록할 필요 없음
=IF(H4>=6,”최우수”,IF(H4>=4,”우수”,”불량”))
7
1. 자동합계 활용
항목별 데이터와 계산값이 채워질 셀을 모두 포함하여 범위 지정
2. “표준도구”의 “자동합계” 아이콘 삼각형 클릭
3. “평균” 메뉴 선택
계산 식 입력(3)
번호
설명
8
1.
2.
3.
4.
“최우수”, “우수” 모두 “우수”라는 단어를 포함하고 있음에 주목
“우수”라는 단어의 위치 검색 : FIND( “우수”, 평가배열(L3:L20) )
위치 값이 숫자인지 검사 : ISNUMBER( 위치검색 값 배열 )
숫자인 자료의 유지비의 합 계산 : SUMPRODUCT( 숫자인지 검사한 논리
값 배열 * 유지비배열(K3:K20))
5. 2번 함수식이 “위치검색 값 배열” 부분에 치환하고 치환된 함수식을 4번
함수식의 “숫자인지 검사한 논리값 배열” 부분에 치환하여 계산식 완성
= SUMPRODUCT(ISNUMBER(FIND(“우수”,L3:L20))*K3:K20)
9
1. 8번의 계산식을 수식 입력줄에서 복사한 후 ESC키를 눌러 입력 취소
2. A24 셀에 작은 따음표(‘) 입력 후 붙여 넣기한 후 ENTER를 입력하여 완료
계산 식 입력(4)
번호
10
설명
1. 평가가 최우수와 우수인 조건 : ISNUMBER(FIND(“우수”,$L$3:$L$20))
2. 연비가 3인 조건 : ($H$4:$H$21=3)
조건식은 반드시 괄호로 묶어 처리
3. 조건이 이거나로 연결 되므로 조건은
(ISNUMBER(FIND(“우수”,$L$3:$L$20)) + ($H$4:$H$21=3))
이때 +(OR)은 *에 비해 연산의 우선순위가 떨어지므로 괄호로 묶어서 면
저 계산하도록 함
4. “=SUMPRODUCT( 조건 * 연료비배열(J4:J21))”로 조건 부분에 3의 조건식
으로 치환하여 계산식 완성
5. “=SUMPRODUCT((ISNUMBER(FIND(“우수”,$L$4:$L$21)) +
($H$4:$H$21=3))*J4:J21)” 입력 후 채우기 헨들을 이용하여 복사
계산 식 입력(5)
번호
설명
11
1.
2.
3.
4.
차량번호 끝자리 계산 : RIGHT( $A$4:$A$21,1)
차량번호 끝자리가 2인 조건 : (RIGHT( $A$4:$A$21,1)=“2”)
평가가 불량인 조건 : ($L$4:$L$21=“불량”)
조건이 *(AND:그리고)로 연결
(RIGHT( $A$4:$A$21,1)=“2”)*($L$4:$L$21=“불량”)
5. “=SUMPRODUCT(조건*연료비배열(J4:J12))”로 조건 부분에 4의 조건식으
로 치환
6. “=SUMPRODUCT((RIGHT( $A$4:$A$21,1)=“2”)*($L$4:$L$21=“불
량”)*J4:J12)” 입력 후 채우기 헨들을 이용하여 복사
12
1.
2.
3.
4.
배기량이 3000 이상인 조건 : ($B$4:$B$21>3000)
배기량이 3500 이하인 조건 : ($B$4:$B$21<=3500)
조건은 *(AND:그리고)로 연결 : ($B$4:$B$21>=300)*($B$4:$B$21<=3500)
“=SUMPRODUCT(조건 * 연료비배열(J4:J21))”로 조건 부분 3의 조식으로
치환
5. “=SUMPRODUCT(($B$4:$B$21>=300)*($B$4:$B$21<=3500)*J4:J21)” 입력
후 채우기 헨들을 이용하여 복사
SUMPRODUCT 함수 대체
• 조건에 반드시 SUMPRODUCT 함수를 사용하라는 말이 없을
경우 DSUM 함수로 대체 가능
• 표 서식 옆에 조건 기록(구분이 최우수 또는 우수인 자료)
구분
*우수
• 위 조건을 만족하는 유지비 항목의 합
=DSUM(A3:L21,K3,M3:M4)
• 계산식 입력 작업이 완료 된후 조건이 입력된 열 숨기기
• 그 외 조건 표현
연비
구분
3
논리값인 조건
구분
=RIGHT(A4,1)=“2” 불량
*우수
배기량
배기량
>=3000
<=3500
서식 설정
열 서식
• 열 너비 변경
1.
2.
3.
열 이름을 클릭 또는 드래그 하여 행 선택
바로 가기 메뉴의 “열 너비” 메뉴 선택
글자 단위로 높이 입력 후 “확인” 버튼 클릭
• 마우스를 이용한 열 너비 변경
– 열 이름의 오른쪽 경계선 드래그
– 열 이름을 드래그하여 행 선택 후 기준이 되는 열 이름의 오른쪽 경계선
드래그
• 선택한 열의 가장 긴 내용에 맞게 열 너비 조정
1.
2.
열 이름을 클릭 또는 드래그하여 행 선택
열 이름의 오른쪽 경계선을 더블 클릭
• 열 숨기기
– 열 이름을 클릭 또는 드래그하여 행 선택
– 바로 가기 메뉴의 “숨기기” 메뉴 선택
• 열 숨기기 취소
– 숨겨진 왼쪽 열에서부터 숨겨진 오른쪽 열까지 드래그하여 선택
– 바로 가기 메뉴의 “숨기기 취소” 메뉴 선택
서식 도구를 이용한 셀 서식
맞춤
글꼴 색
글꼴 및 크기
글꼴 스타일
표시 형식
색 채우기(음영)
테두리
셀 서식 대회상자(1)
• 대화상자 표시
– 범위 지정 후 마우스 오른쪽 버튼 클릭
– “셀 서식“ 메뉴 선택
• 표시형식
– 셀에 입력된 자료의 표시 형식(모양) 지정
• 맞춤
– 텍스트의 가료, 세로 맞춤
– 텍스트 조정(텍스트 줄 바꿈, 셀에 맞춤, 셀 병합)
– 텍스트 회전(세로 쓰기, 80도 범위 내 회전)
• 글꼴
– 입력된 자료의 글꼴 지정
• 테두리
– 최대 8종류의 선을 동시에 지정 가능
셀 서식 대회상자(2)
• 무늬
– 셀의 배경색(음영색) 지정
– 배경 무늬 지정
• 보호
– 도구 메뉴의 보호 메뉴의 시트보호와 연계하여 동작
– 잠금 : 시트보호가 실행되면 해당 시트에 데이터 입력 불가능
– 숨김 : 시트 보호가 실행되면 해당 셀의 수식을 숨김
작성 조건(서식 작업 실습)
가. 제목 서식 16포인트 크기로 하고 가운데 표시(제목 밑줄)
나. 글꼴서체 : 임의 선정
라. 작업표의 정렬순서(SORT)는 구분의 오름차순으로 정렬하고,
구분이 같으면 유지비의 오름차순으로 정렬한다.
마. 기타
– 금액에 대한 수치는 원화(\) 표시를 하고 천단위마다 ,(Comma)를 표
시한다.
(단, 금액 이외의 수치는 ,(Comma)를 표시하지 않는다
– 모든 수치(숫자, 통화, 회계, 백분율 등)는 셀 서식의 속성을 설정하는
과정에서 소수 자리수를 “0”으로 지정하여 정수로 한다.
– 음수”-”가 나타나도록 한다.
– 숫자셀은 우측을 수직으로 맞추고, 문자셀은 수평 중앙으로 맞추며 기
타는 작업표 형식에 따른다. 특히, 인쇄 출력시 판독 불가능이 발생하
지 않도록 인쇄 미리보기 등을 통하여 크기를 적당히 조정한다.
정렬
1. 요약을 제외한 표 전체 선택
2. 데이터->정렬 메뉴 선택
3. 첫째 기준 : 구분, 둘째 기준 : 유지비 선택
각 기준별 오름차순 체크
4. 화인버튼 클릭
숫자 서식 지정
1. 해당 부분만을 선택한
2. 셀 서식의 표시형식 범주를 “통화”로, 통화기호는 원화(\)로
지정
3. 확인 버튼 클릭
가로 맞춤 서식 지정
• 숫자 서식은 이미 우측으로 맞추어져 있으므로 맞출 필요 없
음
• 문자가 입력된 셀을 범위지정하여 가로 가운데 맞춤 지정
가로 가운데 맞춤
셀 병합 후 가운데 맞춤
추천
자료 입력 후 병합 대상 셀 그룹을 드래그,
Ctrl+드래그(추가선택)하여 모두 지정한
후 “병합하고 가운데 맞춤” 아이콘 클릭
재목 및 테두리 서식 지정
작성조건 : 제목서식
대부분 크기 16포인트 가운데 표시
표 전체 범위 지정 후
모든 테두리 지정
안쪽 테두리
안쪽 가로 테두리선을 없
엘 부부만 지정
클릭하여 안쪽 가로선을
표시하지 않음
바로가기 메뉴의 셀 서식
선택
숨기기
• 작업표 형식을 기초로 작압표에 표시되지 않는 열만 선택하
여 숨기기 함
– 숨길 열 이름 클릭 또는 드래그
– 바로가기 메뉴의 “숨기기” 메뉴 선택
차트 작성
그래프(Graph)작성 조건
• 작성한 작업표에서 승용차에 대한 차량번호별 연료비와 유지
비를 나타내는 그래프를 작성하시오.
• 작성 조건
1.
2.
3.
4.
5.
6.
7.
8.
9.
그래프 작성 : 혼합형 단일축 그래프
연료비(묶은 세로 막대형), 유지비(데이터 표식이 있는 꺽은선형)
그래프 제목 : 승용차의 차량 유지 비용 ---- (확대 출력, 제목 밑줄)
X축 제목 : 차량 번호
Y축 제목 : 금액
X축 항목 단위 : 해당 문자열
Y축 눈금 단위 : 임위
범례 : 연료비, 유지비
출력물 크기 : A4용지 ½장 범위내
기타 : 작성조건에 없는 형식이나 모양 등은 기본 설정값에 따르며,
그래프 너비는 작업표 너비에 맞춘다.
데이터 범위 선택
1. 차트에 사용할 데이터 범위를 드래그, Ctrl + 드래그하여 선
택(이때 되도록이면 항목 이름을 포함하여 선택)
2. 차트 마법사 이이콘을 클릭
차트 마법사 1단계(차트 종류)
• 문제의 차트 종류에 따라 차트 종류 선택
대부분 단일축 혼합형임
– 묶은 새로 막대형 선택
차트 마법사 2단계(원본데이터/계열)
• 차트에 사용될 데이터의 범위 지정과 세부적 편집이 가능한
단계
• 범위는 지정하고 시작했으므로 수정할 필요 없음
• 계열 부분은 대부분 수정해야 하는 경우가 많음
– 계열 목록에서 차량번호 제거
– X(항목) 출 레이블 란을 클릭하여 차량 번호 범위 지정(항목이름 제외)
차트 마법사 3단계(제목 설정)
• 요구 조건을 보고 각 제목 입력
차트 마법사 4단계 (위치 지정)
• 사무자동화 시험의 차트는 동일 시트에 삽입
• 마침을 클릭하여 차트 삽입
차트의 배치
• 차트영역(차트 테두리 안쪽 흰 부분)을 Alt+드래그하여 표 아
래 A열로 이동
차트의 크기 조정
• 차트의 우측 아래 크기조절 점을 작업표의 오른쪽 열에 맞추
어 Alt+드래그하여 조정(구체적 높이는 추후 조정)
계열 차트 종류 변경
• 막대를 한번 클릭하면 계열(같은색 막대 그룹) 선택됨
• 계열이 선택된 상태에서 막대를 한번 더 클릭하면 데이터 요소(막
대 하나) 선택됨
1. 유지비 계열 선택 후 바로 가기 메뉴의 “차트 종류” 메뉴 선택
2. 차트 종류 “꺽은선형”, 우축에서
“데이터 표식이 있는 꺽은선형” 선택
차트 제목 서식 설정
• 차트 제목을 더블클릭하여 서식창 표시
차트의 모든 부위의 서식은 각 부위를 더블클릭하여 지정
– 제목 확대 출력 : 글자 크기를 크게
인쇄
페이지 설정 (여백 조정1)
• “파일” 메뉴의 “페이지 설정” 메뉴 선택
페이지 설정 (여백 조정2)
•
•
•
•
페이지 설장 창의 “여백” 텝 클릭
위쪽 : 6cm, 머리글 : 1.3cm 정도(완충을 위해)
나머지 여백은 출력물이 누락없이 나올 정도로 자율 조정
“페이지 가운데 맞춤”의 “가로”에 체크(내용이 가운데로 배치
됨)
여백 세부 조정
• 페이지 설정 창에서 “인쇄 미리 보기” 클릭
• “여백” 단추 클릭(가로 가운데로 배치가 되지 않을 때 왼쪽 여
백 선을 드래그하여 내용이 가운데로 배치되도록 설정)
여기까지 완료되
었으면 “닫기”
단추 클릭 후 파
일을 저장합니다.
인쇄
• 반드시 페이지 설정까지 완료된 파일을 인쇄
• 인쇄 전 “인쇄 미리 보기”를 통해 정상적으로 표시되는지 확
인
• “인쇄 미리 보기” 창 위의 “인쇄” 단추 클릭
• 인쇄 범위에서 “인쇄할 페이지(G)”를 지정하고 시작 페이지를
“1”로 설정 후 인쇄(확인단추 클릭)