2과목: 스프레드시트
●하이퍼링크
텍스트나 그래픽개체에 웹페이지, 현재문서, 새 통합문서, 전자메일주소에 연결시키는 기능
바로가기키: Ctrl + K도형 및 개체는 연결되나 단추 개체에는 하이퍼링크가 안된다
핵심84 엑셀의 화면 구성
●빠른실행도구모음
명령을 추가하면 일련번호로 된 바로가기가 지정됨
[파일]-[옵션]-[빠른실행도구모음]
●리본 메뉴
리본 메뉴는 탭, 그룹, 명령으로 구성
탭 종류: 홍, 삽입, 페이지 레이아웃, 수식, 데이터, 검토 보기, 개발 도구 등
Alt or F10을 누르면 리본네뮤에 바로가기 키가 표시
| ●리본메뉴를 감추거나 표시하는 방법 -엑셀 창 오른쪽 위에 있는 '리본 메뉴 최소화 단추( 이미지를 클릭하면 확대 가능합니다.(한번더 클릭하면 원본크기로 변경 됩니다). )'를 클릭한다 -리본 메뉴의 활성 탭 이름을 더블 클릭한다. -리본 메뉴를 최소화하거나 원래 상태로 되돌리려면 단축키 리본메뉴를 감추거나 표시하는 방법을 누른다. |
●이름 상자
현재 작업 중인 셀 이름이나 주소를 표시하는 부분
차트 항목이나 그리기 개체를 선택하면 개체의 이름이 표시됨
| ●엑셀이름상자 중복,셀이름형태,숫자시작,공백,특수문자 안됩니다 밑줄(_)시작은 가능 |
●수식 입려줄
현재 작업하는 셀의 수식을 그대로 표시하는 부분
수식입력 상자를 이용하여 셀 포인터 위치에 내용을 입력하거나 수정할 수 있음
●상태 표시줄
현재의 작업 상태나 선택한 명령에 대한 기본적인 정보가 표시되는 곳
-상태 표시줄 사용자 지정: 평균, 개수, 숫자 셀 수, 최소값, 최대값, 합계를 선택하여 자동계산 가능
-매크로 기록: 매크로를 기록할 수 있는 아이콘,
기본적으로 는 매크로 기록 아이콘으로 표시되지만, 매크로 기록 중에는 기록중지 아이콘으로 변경됨
-보기 바로 가기: 워크시트의 보기 상태를 변경할 수 있는 아이콘
-확대/ 축소 슬라이더: 10%~400%로 확대/축소할 수 있는 건트롤
핵심85 워크시트
●워크시트의 편집
| <워크시트 선택> | |
| 연속적인 + 여러 개의 시트 선택 | SHIFT누른채 마지막시트 선택 |
| 비연속적인+ 여러 개의 시트 선택 | CTRL누른채 원하는 시트를 차례대로 클릭 |
여러 개의 시트를 선택하면 제목 표시줄에 [그룹]이라고 표시됨
여러 개의 시트를 선택하고 데이터를 입력하면 선택한 모든 시트에 동일한 데이터가 입력됨
그룹상태에서는 도형, 차트 등의 그래픽 개체를 삽입하거나 정렬, 필터 등의 데이터 관리 작업을 수행할 수 없음
| <워크시트 이름변경> | |
| 바꿀 시트 이름을 더블클릭한 후 원하는 이름을 입력하고 Enter를 누름 | |
| 시트의 이름은 공백을 포함하여 최대 31자까지 지정 *?:/[] 등의 문자는 사용할 수 없음 |
| <워크시트 삽입> | |
| 하나의 통합문서에는 기본적으로 1개의 워크시트가 포함, 메모리 한도까지 워크시트 추가 가능 | |
| 삽입된 시트는 활성 시트의 왼쪽에 삽입됨 | |
| 여러 개의 시트를 연속적으로 선택한 후 시트를 삽입하면 선택한 수만큼의 시트가 삽입되나 서로 떨어져 있는 시트를 선택한 경우에는 삽입될 수 없다시트 삽입 바로가기 키: Shift+F11 |
|
| 시트 삽입 바로가기 키: Shift+F11 |
| <워크시트 이름변경> | |
| 바꿀 시트 이름을 더블클릭한 후 원하는 이름을 입력하고 Enter를 누름 | |
| 시트의 이름은 공백을 포함하여 최대 31자까지 지정 *?:/[] 등의 문자는 사용할 수 없음 |
| <워크시트 이동 및 삭제> | |
| 시트 마지막으로 이동: CTRL+방향키 | |
| 여러 개의 시트를 선택하여 한꺼번에 삭제할 수 있음 | |
| 삭제된 시트는 되살릴수 없다. |
핵심86 데이터 입력
| 한 셀에 여러 줄로 데이터를 입력하려면 줄을 바꾸기 | Alt+ Enter |
| 지정한 범 위 안에서만 셀 포인터가 이동 | 특정 부분을 범위로 지정하고 Enter |
| 같은 열에 입력된 문 자열 목록이 표시 | 셀을 선택하고 Alt+ 아래방향키 |
●셀 내용 자동 완성
셀에 입력한 처음 몇 자가 같은 열에 있는 기본 항목과 동일하면 자동으로 나머지 문자가 채워진다
문자데이터만 적용되고, 숫자, 날짜, 시간 형식의 데이터에는 적용되지 않는다
자동완성기능을 사용하려면 파일-옵션-고급-편집 옵셥에서 셀 내용을 자동완성을 선택한다
●실행 취소 및 다시 실행
실행취소 방법: Ctrl+Z
다시 실행 방법: Ctrl+Y
●실행 취소가 불가능한 것
시트와 관련된 작업(시트 이름 변경,삽입,삭제,복사,이동)
틀고정,창 숨기기
핵심87 데이터 형식
● 데이터 형식
*문자데이터
기본적으로 셀의 왼쪽 정렬
숫자 데이터 앞에 문자 접두어(')를 입력하면 문자 데이터로 인식함
입력 데이터가 셀의 너비보다 긴 경우 ####
*숫자 데이터
기본적으로 셀의 오른쪽 정렬
분수는 0 입력 후 한 칸 띄고 입력(0 1/2)
음수 표현: 숫자 앞에 - 기호를 붙이거나, 괄호( )
셀의 너비보다 긴 경우 지수 형식으로 표시
*날짜/시간 데이터
|
기본적으로 셀의 오른쪽 정렬
슬래시(/), 하이픈(-)으로 연, 월, 일을 구분하여 입력합니다.
수식에서 날짜 데이터를 직접 입력할 때에는 큰따옴표("")로 묶어서 입력합니다.
오늘 날짜 입력: ＜Ctrl＞+;
현재 시간 입력: ＜Ctrl＞+＜Shift＞+＜;＞
12간제 표시: 시간 입력 후 AM이나 PM 입력 연도가 두 자리일 때 30이상은 1900년대, 29이하면 2000년대 |
*수식 데이터
입력된 셀에는 수식의 결과 값이 수식 입력줄에는 입력한 수식이 표시됨
셀에 수식을 입력한 후 결과값이 수식이 아닌 상수로 입력되기 하려면 수식을 입력한 후 F9를 누름
입력된 수식 보기: Ctrl + ~
*메모
셀에 입력된 데이터를 지워도 메모는 삭제되지 않음
메모를 삽입할 셀을 선태갛고, Shift + F2를 누르거나 검토-메모=새 메모를 선택
메모가 삽입된 셀에는 오른쪽 상단에 빨간색 삼각형, 점이 표시
메모를 인쇄하거나 메모만 따로 모아서 시트 끝에 인쇄가능
*윗주
위주가 삽입된 셀의 데이터를 삭제하면 윗주도 함께 삭제됨
데이터 위쪽에 표시되며, 문자데이터만 삽입할 수 있음
셀에 수치 데이터, 날짜/시간 데이터가 입력되어 있거나, 공백이면 윗주를 삽입할 수 없음
윗주 서식은 윗주 전체에 대해서만 적용하거나 변경할 수 있음
핵심88 채우기 핸들을 이용한 연속 데이터 입력
●채우기 핸들을 이용,연속 데이터 입력
|
숫자 데이터
|
한 셀 :CTRL+드래그할 경우 1씩 증가한다.
*숫자를 ＜Ctrl＞+드래그 하면 1씩증가
두 셀: 두셀의 숫자 차이만큼 증가/감소 한다
(예)10,30 입력된 두셀을 블록 후 드래그하면 20씩 증가된 값으로 채워진다)
|
||||
|
문자 데이터
|
동일한 데이터가 복사
|
||||
|
날짜,시간
데이터 |
날짜를 드래그 하면 1일씩 증가
날짜를 ＜Ctrl＞+드래그 하면 복사
시간을 드래그 하면 1시간씩 증가
시간을 ＜Ctrl＞+드래그 하면 복사
|
||||
|
혼합데이터
(문자+숫자 데이터) |
＜Ctrl＞+드래그 하면 복사
한 셀:가장 오른쪽에 있는 숫자는 1씩 증가하고 문자는 그대로 복사되어 채워진다.
두 셀:숫자 데이터는 차이만큼 증가/감소하고 문자는 그대로 복사
|
||||
|
사용자정의
목록 |
사용자 정의 목록에 등록된 문자 데이터 중 하나를 입력하고 채우기 핸들을 드래그하면 사용자 정의 목록에 등록된 문자 순서대로 반복되어 입력
[파일]-[옵션]-[고급]-[사용자지정 목록편집 탭] 등록 |
||||
●자동 채우기 옵션 단추
셀 복사
연속 데이터 채우기
서식만 채우기
일 단위 채우기
평일 단위 채우기
월 단위 채우기
연 단위 채우기
빠른 채우기
핵심89 찾기
● 찾기
문자, 숫자, 특수 문자, 수식, 메모 서식 등을 찾음
워크시트를 전체/범위를 대상으로 탐색을 지정해서도 찾을 있다
거꾸로(역순)으로 검색하려면 Shift를 누른 상태에서 [다음찾기]
Ctrl + F
Shift + F5
● 찾기 및 바꾸기 대화상자
대/소문자 구분
전체 셀 내용 완전히 일치하는 셀만 찾음
*, ? 등의 만능 문자(와일드카드)를 사용할 수 있으며,
만능 문자 자체를 검새하려면 ~* 또는 ~?와 같이 기호 앞에 ~를 붙임
찾을 내용을 입력하고 [다음찾기]를 한번이라도 수행한 후에는 '찾기' 대화상자를 닫아도 F4를 눌러 찾을 수 있다
*S*: S를 포함하는
S???: S로 시작하면서 뒤에 3글자
핵심90 셀 포인터 이동 / 범위 지정
● 셀포인트 이동작업/단축키
SHIFT + 좌우 = 좌우로 이동
SHIFT + 엔터 = 윗행으로 이동(상하)
자동채우기 핸들 = 수식이 자동으로 복사되는 개념
단, 자동채우기 시 컨트롤을 누르고 (+가 뜬 상태에서 자동채우기 하면 1씩 커짐)
<Ctrl> + <Page Down> 다음 시트로 이동하는 단축키
<Alt> + <Page Down>키는 한 화면 오른쪽으로 이동
<ALT> + 엔터 = 동일 셀에서 줄 바뀜
<Ctrl> + 엔터 = 동일한 데이터 입력
<Ctrl> + 쉬프트 + 엔터 =동일 데이터 입력
<Ctrl> + <Home> 키를 눌러 [A1] 셀로 이동한다.
<Home> 키를 눌러 해당 행의 A 열로 이동한다.
<Alt>+<F1> : 현재 범위의 데이터에 대한 차트 생성
<F5>: 이동하고자 하는 셀 주소를 직접 입력하여 이동
● 범위지정
연속된 셀
Shift 를 누른 상태에서 범위로 지정할 마지막 셀을 클릭
Shift를 누른 상태에서 방향키를 눌러 범위를 지정
F8을 누른 후 방향키를 눌러 범위를 지정
행 전체: shift+스페이스파
떨어진 셀
Ctrl+선택할 셀 클릭
열 전체: Ctrl+스페이스파
워크시트 전체: Ctrl+A, Ctrl+Shift+스페이스바
데이터목록 전체: Ctrl+*, Ctrl+Shift+;
핵심91 [파일]→[옵션]
*일반 탭
●사용자 인터페이스 옵션
선택영역에 미니도구모음표시: 범위 잡으면 미니메뉴 표시
실시간 미리보기 사용
●새 통합 문서 만들기
새 통합문서 만들기: 글꼴과 크기, 포함할 시트수 (255개까지 설정가능)
*수식 탭
●계산옵션
통합 문서 계산
●수식작업
R1C1참조 스타일
수식 자동완성 사용
쉭에 표 이름 사용
*고급 탭
●편집 옵션
Enter키 누른 후 다음 셀 이동, 형 및 열머리글 표시
소수점 자동삽입, 채우기핸들, 자동%, 눈금선 표시
셀 내용 자동완성/ 직접편집, 계산결과대신 수식을 셀에 표시
IntelliMouse로 화면 확대/축소
●이 통합 문서의 계산 대상
다른 문서에 대한 링크 업데이트
외부 연결값 저장
●이 워크시트의 표시 옵션
행 및 열 머리글 표
페이지 나누기 표시
0 값이 있는 셀에 0 표시
눈금선 표시
●일반
자동 연결 업데이트 확인
정렬 및 채우기 순서에서 사용할 목록 만들기
핵심92 선택하여 붙여넣기
선택하여 붙여넣기는 잘라내기한 경우에는 사용할 수 없고, 복사한 경우에만 사용할 수 있다
Ctrl + Alt + V
핵심93 데이터 유효성 검사
●데이터 유효성 검사
데이터탭-데이터 도구- 데이터 유효성 검사
데이터를 정확하게 입력할 수 있게 하는 기능
핵심94 통합 문서
● 통합 문서
| 새 파일 작성 | CTRL+N |
| 파일 열기 | CTRL+O |
| 파일 닫기 | CTRL+F4 또는 CTRL+W |
*통합문서는 기본적으로 확장자가 .xlsx로 저장
● 저장 가능한 파일 형식
- xlsx : 통합 문서 파일
- xlsm : VBA 매크로 코드나 매크로가 포함된 통합 문서 파일
- xltx : 서식 파일
- xltm : 매크로가 포함된 서식 파일
- xlsb : 바이너리 통합 문서 파일
- xlk : 백업 파일
- htm, html : 웹 페이지 형식 파일
- xls : Excel 97~2003 통합 문서 파일
- prn : 공백으로 분리된 아스키 텍스트 파일
- csv : 쉼표로 분리된 아스키 텍스트 파일
- txt : 탭으로 분리된 아스키 텍스트 파일
● 일반옵션
| 백업 파일 항상 만들기 | 통합 문서를 저장할 때마다 백업 복사본을 저장함 |
| 열기 암호 | •암호를 모르면 통합 문서를 열 수 없음 •암호의 최대 길이는 255자까지 가능 |
| 쓰기 암호 | •암호를 모르더라도 읽기 전용으로 열어 수정할 수 있으나, 원래 문서에는 저장할 수 없음 •암호의 최대 길이는 15자까지 가능 |
| 읽기 전용 권장 | 문서를 열 때마다 통합 문서를 읽기 전용으로 열 도록 대화상자를 나타냄 |
핵심95 서식 파일
서식 파일은 일정한 형식이나 스타일을 적용하여 만들 어 놓은 문서이다.
일반적인 서식 파일의 확장자는 ‘xltx’이고, 매크로가 포함된 서식 파일의 확장자는 ‘xltm’이다.
행, 열, 시트 등을 숨기거나 통합 문서를 보호하는 기능 을 부여한 서식 파일을 작성할 수 있다.
사용자가 작성한 서식 파일은 기본적으로 ‘`Templates`’ 폴더에 저장된다.
기본 서식 파일을 새로 만들려면 워크시트는 ‘sheet. xltx’,
통합 문서는 ‘book.xltx’로 파일 이름을 지정하여 XLStart 폴더에 저장한다.
●서식 파일로 저장할 수 있는 요소
- 셀 서식
- 시트별 페이지 서식 - 인쇄 영역
- 셀 스타일
- 통합 문서의 시트 개수와 종류 - 통합 문서에서 보호되거나 숨긴 영역
- 페이지 머리글, 행/열 레이블 등 워크시트마다 반복 하여 나타낼 문자열
- 워크시트마다 표시할 데이터, 그래픽, 수식, 차트, 데이터 유효성 설정 등
핵심96 통합 문서 공유
● 통합 문서 공유
•네트워크로 연결된 환경에서 하나의 통합 문서를 여러 사람이 공동으로 작업할 수 있게 하는 기능이다.
•통합 문서를 공유하면 데이터의 입력과 편집은 가능하나
조건부 서식, 차트, 시나리오, 부분합, 데이터 표, 피벗 테이블 보고서 등에 대한 작업은 추가 및 변경이 불가능하다.
•공유된 통합 문서는 제목 표시줄에 [공유]라고 표시된다.
•공유 통합 문서의 변경 내용을 추적하여 변경 내용만을 모아 새로운 시트에 작성할 수 있다.
•공유된 통합 문서는 여러 사용자가 동시에 변경 및 병 합할 수 있다
•공유 통합 문서를 사용하는 특정 사용자의 연결을 강제 로 종료시킬 수 있다.
•공유 통합 문서를 보호하기 위해 암호를 설정할 수 있다.
•암호로 보호된 공유 통합 문서의 보호를 해제하려면 먼저 통합 문서의 공유를 해제해야 한다.
•다른 사용자가 문서의 내용을 변경하였을 경우 자동으 로 변경된 셀에 메모가 표시된다.
•통합 문서 공유가 설정된 파일을 다른 위치에 복사해도 공유 설정 값은 유지된다.
•공유 통합 문서가 저장된 네트워크 위치를 액세스하는 모든 사용자는 공유 통합 문서를 액세스할 수 있다.
•공유 통합 문서의 변경 내용을 일정 기간 동안 보관할 수 있으며, 그 여부를 지정할 수도 있다.
•변경 내용을 저장하려면 공유 통합 문서의 복사본이 만 들어져 변경한 내용들을 병합할 수도 있다.
•엑셀의 상위 버전에서 작성된 공유 통합 문서는 하위 버전에서 사용할 수 없다.
•공유 통합 문서를 사용하는 여러 사용자들의 변경 내용 이 충돌할 경우 저장할 내용을 선택하거나, 자신이 변경한 내용이 무조건 저장되도록 지정할 수 있다.
• 실행 [검토] → [변경 내용] → [통합 문서 공유]를 클릭 한 후 ‘편집’ 탭에서 ‘여러 사용자가 동시에 변경할 수 있으며 통합 문서 병합도 가능’ 항목 선택
핵심97 시트 보호 / 통합 문서 보호
● 시트 보호
•워크시트에 입력된 데이터나 차트 등을 변경할 수 없도 록 보호하는 것으로, 보호된 시트에서는 기본적으로 셀 선택하는 것만 가능하다.
•통합 문서 중 특정 시트만을 보호하는 것으로, 나머지 시트는 변경이 가능하다.
•모든 요소를 모든 사용자가 액세스하지 못하도록 보호 할 수 있으며, 지정한 범위에 대해 개별적으로 사용자의 수정을 허용할 수도 있다.
•시트보호 제외 항목: 셀/행/열의 서식, 하이퍼링크 삽입, 개체 편집, 시나리 오 편집, 자동 필터, 피벗 테이블 보고서
[검토]-[변경내용]-[시트보호]
● 통합문서 보호
•시트 삽입·삭제·이동·숨기기·이름 바꾸기 등을 할 수 없도록 보호한다.
•통합 문서에 ‘시트 보호’가 설정되지 않은 경우 워크시 트에 도형, 차트, 데이터 등을 입력, 수정, 삭제하거나 피벗 테이블 보고서, 부분합과 같은 데이터 분석 작업 을 할 수 있다.
•암호를 지정할 수 있다.
새 파일 작성: Ctrl + N
파일 열기: Ctrl + O
파일 닫기: Ctrl + F4, Ctrl + W
● 통합문서 공유
[검토]-[변경내용]-[통합문서공유]
데이터 입력, 편집은 가능
조건부서식, 차트, 시나리오, 데이터표, 피벗테이블, 부분합 등의 작업 변경은 불가능하다
엑셀상위버전에서 작성한 문서는 하위버전에서 사용할 수 없다
핵심98 사용자 지정 서식
● 사용자지정 표시형식
조건이 없을 때는 양수,음수,0,텍스트 순으로 표시
조건이 있을 때는 조건이 지정된 순으로 표시 형식
조건이나 글꼴색의 지정은 대괄호([])안에 입력
|
#.###;[빨강](#.###);0.00;@"님"
양수 음수 0값 텍스트
|
; = 양수, 음수, 0값을 세미콜론으로 구분함
•셀에 입력된 자료를 숨기려면 표시형식은 지정하지 않고 ‘;;;’와 같이 각 구역을 구분하는 세미콜론(;) 만 지정함
● 사용자 지정 서식 코드
: <Ctrl>+<1> => 셀서식 =>사용자 지정
- 숫자 서식
# = 유효한 자릿수만 표시,유효하지 않은 0은 표시하지 않음.
0 = 유효하지 않은 자릿수를 0으로 표시
? = 유효하지 않은 자릿수를 0대신 공백으로 표시
, = 천 단위 구분 기호 표시,표시 형식 맨끝에 표시하면 할 때마다 3자리씩 생략
※천 단위 생략-천 단위 미만의 값을 반올림 표시(예:44600을 #. 이면 45가 표시)
% = 숫자에 100을 곱한 다음 %를 붙임
[DBNUM1]= 숫자를 한자 및 한글, 한자/한글로 표시함
- 문자 서식
•@ : 문자 데이터의 표시 위치 지정
•* : * *기호 다음에 있는 특정 문자를 셀의 너비만큼 반복하
_ : 셀에 입력 데이터의 오른쪽 끝에 하나의 공백이 생김
[] = 글꼴색, 조건
- 날짜 서식
월 mmm: Jan~Dec
요일 ddd:Sun~Sat,dddd:Sunday~Saturday
시간 hh:00~23 [h] 경과된 시간 표시
핵심99 조건부 서식
● 조건부 서식
•규칙에 만족하는 셀에만 셀 서식을 적용한다.
•조건부 서식의 규칙을 수식으로 입력할 경우 수식 앞에 반드시 등호(=)를 입력해야 하고, 수식의 결과는 참 (TRUE) 또는 거짓(FALSE)이 나오도록 작성해야 한다.
•워크시트의 특정 셀을 이용하여 규칙을 작성할 수 있 고, 규칙 작성 시 셀을 클릭하면 절대 참조로 지정된다.
•셀의 값이 변경되어 규칙을 만족하지 않으면 적용된 서 식이 해제된다.
•셀에 입력된 값에 따라 데이터 막대, 색조, 아이콘 등을 표시할 수 있다.
•규칙별로 다른 서식을 적용할 수 있다.
•둘 이상의 조건부 서식의 조건이 참일 경우 규칙에 지 정된 서식이 모두 적용되나, 서식이 충돌할 경우에는 우선 순위가 높은 규칙의 서식이 적용된다. 예를 들어 글꼴 색과 채우기 색을 지정하는 두 규칙이 모두 참일 때는 두 서식이 모두 적용되나, 글꼴 색을 빨강과 파랑 으로 지정하는 두 규칙이 모두 참일 때는 우선 순위가 높은 규칙에 대해서만 글꼴 색이 지정된다.
•규칙이 참일 때 지정될 서식과 셀에 이미 지정된 서식이 충돌할 경우 규칙에 지정된 서식이 적용된다.
•규칙에 맞는 데이터가 있는 행 전체에 서식을 지정할 때는 규칙에 수식 입력 시 비교할 데이터가 있는 열 이 름 앞에 $를 붙인다.
•다른 통합 문서를 참조하여 조건을 지정할 수 없다.
• 실행 [홈] → [스타일] → [조건부 서식] → [새 규칙] 선택
-규칙을 서식으로 입력할 경우 수식 앞에 등호(=)를 반드시 입력해야 함.
조건은 3개까지 지정할 수 있다
행 전체에 조건부 서식: $B4 =열 고정, 행 변화
열 전체에 조건부 서식: B$4 =행 변화, 열 고정
•조건부 서식 규칙 관리자
- 지정된 모든 조건부 서식을 확인하거나 수정, 삭제, 추가, 우선 순위 등을 변경할 수 있다.
- 실행 [홈] → [스타일] → [조건부 서식] → [규칙 관 리] 선택
핵심100 오류 메시지
● 액셀 오류 메시지
|
#####
|
셀에 셀 너비보다 큰 숫자,날짜 또는 시간이 있을때
계산 결과가 음수인 날짜와 시간이 있을 때
|
||||
|
#DIV/0!
|
나누는 수가 빈 셀이나 0이 있는 셀을 참조한 때
|
||||
|
#N/A
|
함수나 수식에 사용할 수 없는 값을 지정했을 때
|
||||
|
#NAME?
|
인식할 수 없는 텍스트를 수식에 사용했을 때
|
||||
|
#NULL!
|
교차하지 않는 두 영역의 교점을 지정하였을 때
|
||||
|
#NUM!
|
표현할 수 있는 숫자의 범위를 벗어났을 때
|
||||
|
#REF!
|
셀 참조가 유효하지 않을 때
|
||||
|
#VALUE!
|
잘못된 인수,피연산자를 사용하거나 수식 자동 고침 기능으로 수식을 고칠 수 없을때
|
||||
● 순환참조 경고
수식에서 직접 또는 간접적으로 수식이 입력된 그 셀을 그 수식에서 참조하는 경우 순환 참조라고 하고,
순환 참조인 경우 아래와 같은 메시지가 표시됩니다
핵심101 셀 참조
● 셀 참조
전체수식보기: Ctrl + ~
| 상대 참조 A1 | 수식을 입력한 셀의 위치가 변동되면 참조가 상대 적으로 변경됨 |
| 절대 참조 $A$1 | 수식을 입력한 셀의 위치와 관계없이 고정된 주소 로, 참조가 변경되지 않음 |
| 혼합 참조 | •열 고정 혼합 참조 : 열만 절대 참조가 적용됨($A1) •행 고정 혼합 참조 : 행만 절대 참조가 적용됨(A$1) |
| 다른 워크시트의 셀 참조 =Sheet!A5 |
•다른 워크시트에 있는 셀의 데이터를 참조할 경우 시트 이름과 셀 주소 사이를 느낌표(!)로 구분함 •시트 이름에 한글, 영어 외의 문자가 있을 경우 작은따옴표(‘ ’)로 묶어줌 |
| 3차원 참조 =SUM(Sheet1:Sheet3!B2) |
•여러 시트의 동일한 셀이나 셀 범위에 대한 참조를 3차원 참조라고 함 •참조하는 시트가 연속적으로 나열되어 있고, 셀 주소가 모두 동일할 때는 첫 번째 시트와 마지막 시트의 이름을 콜론(:)으로 연결하고 셀 주소를 한 번만 지정하면 됨 •SUM, AVERAGE, AVERAGEA, COUNT, COUNTA, MAX, MAXA, MIN, MINA, PRODUCT, STDEV, VAR 함수를 사용할 수 있음 •배열 수식에는 3차원 참조를 사용할 수 없음 |
| 다른 통합 문서의 셀 참조 =‘C:\[매출현황]Sheet4’ |
•다른 통합 문서에 있는 셀의 데이터를 참조할 경우 통합 문서의 이름을 대괄호([ ])로 묶어줌 •경로명은 작은따옴표(‘ ’)로 묶어줌 |
핵심102 이름 정의
● 이름정의
•자주 사용하는 셀이나 셀 범위에 이름을 지정하는 것으 로, 수식이나 함수에서 주소 대신 이름을 참조하여 사 용한다.
•정의된 이름을 사용하면 수식이나 함수에서 참조 범위 를 쉽게 지정할 수 있으며,
함수나 수식의 의미를 좀 더 명확히 할 수 있다.
•정의된 이름은 참조 시 절대 참조 방식으로 사용된다.
•이름 상자의 화살표 단추를 누르고 정의된 이름 중 하 나를 클릭하면 해당 셀 또는 셀 범위가 선택된다.
•이름 작성 규칙
- 첫 문자는 반드시 문자(영문, 한글)나 밑줄( _ ) 또는 역슬래시(\)로 시작해야 한다.
- 이름에 공백을 포함할 수 없다.
- 대·소문자는 구분하지 않으며 최대 255자까지 지정 할 수 있다.
- 같은 통합 문서 내에서 동일한 이름을 중복하여 사용 할 수 없다.
핵심103 통계 함수
● 통계 함수
| AVERAGE(인수1, 인수2, …) | 인수의 평균 값 |
| AVERAGEA(인수1, 인수2, … | 수치가 아닌 셀을 포함하는 인수의 평균값 |
| MAX(인수1, 인수2, …) | 인수 중 가장 큰 값 |
| MAXA(인수1, 인수2, …) | 숫자, 빈 셀, 논리값(TRUE/FALSE), 숫자로 표시된 텍스트 등을 모두 포 함한 인수 중 가장 큰 값 |
| MIN(인수1, 인수2, …) | 인수 중 가장 작은 값 |
| MINA(인수1, 인수2, …) | 숫자, 빈 셀, 논리값(TRUE/FALSE), 숫자로 표시된 텍스트 등을 모두 포 함한 인수 중 가장 작은 값 |
| COUNT(인수1, 인수2, …) | 인수 중 숫자가 들어 있는 셀의 개수 |
| COUNTA(인수1, 인수2, …) | 인수 중 비어 있지 않은 셀의 개수 |
| LARGE(범위, n번째) | 범위 중 n번째로 큰 값 |
| SMALL(범위, n번째) | 범위 중 n번째로 작은 값 |
| RANK.EQ(인수, 범위, 논리 값) | •지정된 범위 안에서 인수의 순위 를 구하되, 동일한 값들은 동일하 지 않을 경우 나올 수 있는 순위 들 중 가장 높은 순위를 동일하게 표시함 •논리 값이 0이거나 생략되면 내림 차순, 0 이외의 값은 오름차순으 로 표시 |
| RANK.AVG(인수, 범위, 논리 값) | 지정된 범위 안에서 인수의 순위를 구하되, 동일한 값들은 동일하지 않 을 경우 나올 수 있는 순위들의 평 균을 계산하여 동일하게 표시함 |
| VAR(인수1, 인수2, …) | 인수의 표본 분산 |
| STDEV(인수1, 인수2, …) | 인수의 표본 표준 편차 |
| MEDIAN(인수1, 인수2, …) | 인수들의 중간 값 |
| MODE(인수1, 인수2, …) | 인수 중 가장 많이 발생한 값을 구함 |
| FREQUENCY(배열1, 배열2) | 배열2의 범위에 대한 배열1 요소들 의 빈도 수 계산 |
| GEOMEAN(인수1, 인수2, …) | 인수의 기하 평균 |
| HARMEAN(인수1, 인수2, …) | 인수의 조화 평균 |
| PERCENTILE(범위, 인수) | 범위에서 인수 번째 백분위수 값 |
핵심104 수학/삼각 함수
● 수학 함수
|
SUM(수1,수2...)
|
인수의 합계
|
||||
|
SUMIF(조건이 적용될 범위..)
|
조건에 맞는 셀들의 합계
|
||||
|
SUMIFS(조건1이 적용될 범위..)
|
여러 조건에 맞는 셀들의 합계
|
||||
|
SIGN(수)
|
인수의 부호값(양수이면 1 0이면 0, 음수이면 -1)
|
||||
|
ROUND(수1,수2)
|
수1을 반올림하여 자릿수(수2)만큼 반환함.
|
||||
|
SQRT(수
|
수의 양의 제곱근을 구함.
|
||||
|
ABS(수)
|
절대값
|
||||
|
INT(수)
|
정수
|
||||
|
RAND( )
|
0과 1 사이의 난수 발생
|
||||
| RANDBETWEEN (인수1, 인수2) |
지정한 두 수 사이의 난수를 구함
|
||||
| MOD(인수1, 인수2) |
인수1을 인수2로 나눈 나머지
|
||||
|
FACT(인수
|
인수의 계승 값(팩토리얼)
|
||||
| PRODUCT(인수1, 인수2, …) |
인수를 모두 곱한 값을 구함
|
||||
| SUMPRODUCT (배열1, 배열2, …) | 배열에서 대응하는 요소를 모두 곱 하고 그 곱의 합을 구함 | ||||
| POWER(인수, 제곱 값) |
인수의 거듭 제곱 값
|
||||
|
TRUNC(인수, 자릿수)
|
지정한 자릿수 미만을 버림
|
||||
| QUOTIENT(인수1, 인수2) | 인수1을 인수2로 나눈 몫을 구함 | ||||
핵심105 텍스트 함수 / 날짜/시간 함수
● 텍스트 함수
| LEFT(텍스트, 개수) | 텍스트의 왼쪽부터 지정한 개수만 큼 표시 |
| MID(텍스트, 시작 위치, 개수) | 텍스트의 시작 위치부터 지정한 개수만큼 표시 |
| RIGHT(텍스트, 개수) | 텍스트의 오른쪽부터 지정한 개수 만큼 표시 |
| LOWER(텍스트) | 텍스트를 모두 소문자로 표시 |
| UPPER(텍스트) | 텍스트를 모두 대문자로 표시 |
| PROPER(텍스트) | 텍스트의 첫 글자만 대문자로 표시 |
| TRIM(텍스트) | 텍스트의 양쪽 공백 제거 |
| REPLACE(텍스트1, 시작 위치, 개수, 텍스트2) | 텍스트1의 시작 위치에서 개수로 지정된 문자를 텍스트2로 변 |
| SUBSTITUTE(텍스트, 인수1, 인수2) | 텍스트에서 인수1을 인수2로 변경 |
| LEN(텍스트) | 문자의 개수를 구함 |
| TEXT(인수, 형식) | 인수를 지정된 형식의 텍스트로 바꿈 |
| FIXED(인수, 자릿수, 논리값) | •인수를 반올림하여 지정된자릿 수까지 텍스트로 표시 •논리값이 TRUE이면 쉼표를 포 함하지 않고, FALSE이거나 생략 되면 텍스트에 쉼표가 포함됨 •자릿수가 지정되지 않으면 2로 인식됨 |
| •FIND(텍스트1, 텍스트2, 시작 위치) •FINDB(텍스트1, 텍스트2, 시작 위치) => 찾는다 |
•텍스트2의 시작 위치부터 텍스 트1을 찾아 위치를 표시함 •FIND는 각각의 문자를 항상 한 글자로 계산하고, FINDB는 숫자 와 영어는 한 글자, 한글과 특수 문자 등은 두 글자로 계산함 •대/소문자를 구분하며, 와일드카 드(*,?) 문자를 사용할 수 없음 |
| •SEARCH(텍스트1, 텍스트2, 시작 위치) •SEARCHB(텍스트1, 텍스트2, 시작 위치) => 샅샅히 검색한다 |
•텍스트2에서 시작 위치부터 텍스 트1을 찾아 위치를 표시함 •SEARCH는 각각의 문자를 항상 한 글자로 계산하고, SEARCHB 는 숫자와 영어는 한 글자, 한글 과 특수문자 등은 두 글자로 계 산함 •대/소문자를 구분할 수 없고, 와일드카드(*,?) 문자를 사용할 수 있음 |
| EXACT(텍스트1, 텍스트2) | 두 텍스트를 비교하여 동일하면 TRUE, 다르면 FALSE를 표시함 |
| REPT(텍스트, 개수) | 텍스트를 개수만큼 반복하여 표시함 |
| CONCATENATE(텍스트1, 텍스트2, …) | 여러 텍스트 항목을 한 텍스트로 합침 |
| VALUE(텍스트) | 텍스트를 숫자로 변환함 |
● 날짜 시간 함수
| WEEKDAY(날짜) | •날짜에 해당하는 요일 번호를 표시 •요일은 1(일요일)~7(토요일)까지의 정 수로 표시 |
| DAYS(마지막 날짜, 시작 날짜) | 두 날짜 사이의 일수 계산 |
| DATE(연, 월, 일) | 연, 월, 일에 대한 일련번호를 구함(기준 1900년 1월 1일) |
| TIME(시, 분, 초) | 지정한 시간에 대한 일련번호를 구함 (0.0~0.999) |
| TODAY( ) | 현재 날짜 표시 |
| NOW( ) | 현재 날짜와 시간 표시 |
| DATEVALUE(날짜) | 텍스트로 표시된 날짜의 일련번호를 구함 |
| EDATE(날짜, 월수) | •지정한 날짜를 기준으로 몇 개월 이전 또는 이후 날짜의 일련번호를 구함 •월수가 양수이면 이후 날짜를, 음수이 면 이전 날짜를 대상으로 구함 |
| EOMONTH(날짜, 월수) | •지정한 날짜를 기준으로 몇 개월 이전 또는 이후 달의 마지막 날짜의 일련번 호를 구함 •월수가 양수이면 이후 날짜를, 음수이 면 이전 날짜를 대상으로 구함 |
| NETWORKDAYS(날짜1, 날짜2, 휴일날짜) | 주말과 지정한 휴일날짜를 제외한 두 날 짜 사이의 작업 일수를 구함 |
| WORKDAY(날짜, 날짜 수, 휴일날짜) | 날짜에서 토요일, 일요일, 지정한 휴일날 짜를 제외하고 지정한 날짜수 만큼 지난 날짜의 일련번호를 구함 |
| WEEKNUM(날짜,옵션) | •지정된 날짜가 일년 중 몇 번째 주에 속하는지를 구함 •옵션이 1이면 일요일부터 주가 시작되 고, 2이면 월요일부터 주가 시작함 |
핵심106 논리 함수 / 정보 함수
● 논리함수
| IF(조건, 인수1, 인수2) | 조건을 비교하여 참이면 인수1, 거 짓이면 인수2를 실행 |
| IFERROR(인수1, 인수2) | 인수1이 오류면 인수2를 표시하고, 그렇지 않으면 인수1을 그대로 표시함 |
| NOT(인수) | 인수의 반대 논리값을 표시 |
| AND(인수1, 인수2, …) | 인수가 모두 참이면 참 |
| OR(인수1, 인수2, …) | 인수 중 하나라도 참이면 참 |
| FALSE( ) | 논리값 FALSE를 표시 |
| TRUE( ) | 논리값 TRUE를 표시 |
● 정보함수
| ISBLANK(인수) | 인수로 주어진 셀이 빈 셀이면 TRUE를 출력 |
| ISERROR(인수) | 인수로 주어진 셀이 오류값(#N/A, #VALUE!, #REF!, #DIV/O! 등)을 가 지고 있으면 TRUE를 출력 |
| ISERR(인수) | 인수로 주어진 셀이 #N/A를 제외 한 오류 값을 가지고 있으면 TRUE를 출력 |
| ISEVEN(인수) | 인수가 짝수이면 TRUE를 출력 |
| ISODD(인수) | 인수가 홀수이면 TRUE를 출력 |
| ISNUMBER(인수) | 인수가 숫자이면 TRUE를 출력 |
| ISTEXT(인수) | 인수가 텍스트이면 TRUE를 출력 |
| ISNONTEXT(인수) | 인수가 텍스트가 아니면 TRUE를 출력 |
| ISLOGICAL(인수) | 인수가 논리값이면 TRUE를 출력 |
| N(인수) | 인수를 숫자로 변환하여 표시. 문자 형태의 숫자는 숫자로, 날짜는 일련번호로, ‘TRUE’는 1로, 그 외 의 값은 0으로 표시 |
| TYPE(인수) | •인수의 데이터 형식을 숫자로 표시 •숫자 : 1, 텍스트 : 2, 논리값 : 4, 오류값 : 16 |
| CELL(정보유형, 셀주소) | 셀의 서식 지정이나 위치, 내용 등에 대한 정보 표시 CELL 함수의 정보유형 • address : 절대 주소 표시 • col : 셀의 열 번호를 숫자로 표시 • color : 셀의 값이 ‘-’ 기호 대신 빨강색 등으로 음수 여부를 표시 할 경우에는 1, 그 외는 0을 표시 • contents : 셀의 값 표시 • filename : 현재 작업 대상 셀이 들어 있는 파일의 이름 표시 • format : 숫자 서식에 적용된 서식을 텍스트로 표시 • parentheses : 셀의 숫자값에 괄호 서식이 적용된 경우에는 1, 그 렇지 않은 경우에는 0 표시 • prefix : 셀이 왼쪽 맞춤이면 작은따옴표(‘), 오른쪽 맞춤이면 큰따 옴표(“), 가운데 맞춤이면 캐럿(^) 표시 • protect : 셀이 잠겨 있으면 1, 그렇지 않으면 0 표시 • row : 셀의 행 번호 표시 • type : 셀이 비어 있으면 ‘b’, 텍스트 상수를 포함하면 ‘l’, 그 밖의 경우는 ‘v’ 표시 • width : 열의 너비를 정수로 반올림하여 표시 |
핵심107 찾기/참조 함수
● 찾기, 참조 함수
| VLOOKUP(기준값, 범위, 열 번호, 옵션) | 범위의 첫 번째 열에서 기준값과 같은 데이터를 찾은 후, 기준값이 있 는 행에서 지정된 열 번호 위치에 있는 데이터 표시 |
| HLOOKUP(기준값, 범위, 행 번호, 옵션) | 범위의 첫 번째 행에서 기준값과 같은 데이터를 찾은 후, 기준값이 있 는 열에서 지정된 행 번호 위치에 있는 데이터 표시 |
| CHOOSE(인수, 첫 번째, 두 번째, …) | 인수가 1이면 첫 번 째를, 인수가 2이면 두 번째를 입력 |
| INDEX(범위, 행 번호, 열 번호) | 지정된 범위에서 행 번호 와 열 번호에 있는 데이터를 입력 |
| INDEX(범위, 행 번호, 열 번호, 범위 번호) | 지정된 범위 에서 지정된 행과 열이 교차되는 위치의 셀 참조를 반 환함 - 범위 : 한 개 이상의 셀 범위를 지정함 - 범위 번호 : 범위를 두 개 이상 지정한 경우 1이면 첫 번째, 2이면 두 번째 범위가 사용 |
| LOOKUP(기준값, 범위) | 범위의 첫째 행 또는 열에서 지 정한 값을 찾아, 범위의 마지막 행이나 열의 같은 위치 에 있는 값을 입력 |
| LOOKUP(기준값, 찾을 범위, 결과 범위) | 찾을 범위에서 기준값을 찾아 같은 위치에 있는 결과 범위의 값을 입력 |
| MATCH(기준값, 범위, 옵션) | 옵션으로 지정된 방법으로 지정된 범위에서 기준값과 같은 데이터를 찾아 상대 위 치를 표시 MATCH 함수의 옵션 • 1 : 기준값보다 작거나 같은 값 중에서 근접한 값을 찾음. 데이터 범위가 오름차순으로 정렬되어 있어야 함 • 0 : 기준값과 정확히 일치하는 값을 찾음 • -1 : 기준값보다 크거나 같은 값 중에서 근접한 값을 찾음. 데이터 범위가 내림차순으로 정렬되어 있어야 함 |
| OFFSET(범위, 행, 열, 높이, 너비) | 선택한 범위에서 지정 한 행과 열만큼 떨어진 위치에 있는 데이터 영역의 데 이터를 표시 |
| COLUMN(셀) | 주어진 셀의 열 번호를 구함 |
| COLUMNS(셀 범위 | 주어진 셀 범위의 열 개수를 구함 |
| ROW(셀 | 주어진 셀의 행 번호를 구함 |
| ROWS(셀 범위) | 주어진 셀 범위의 행 개수를 구함 |
| TRANSPOSE(범위) | 범위에 입력된 값을 행/열을 바꾸 어 현재 셀 범위에 표시 |
| ADDRESS(행 번호, 열 번호, 참조유형) | - 행 번호와 열 번호에 해당하는 셀 주소 표시 - 참조유형 : 1이면 절대참조, 2이면 행만 절대참조, 3 이면 열만 절대참조, 4이면 상대참조 |
| INDIRECT(텍스트) | 주소 형식을 갖춘 텍스트를 셀 주소 로 변환하여 해당 주소에 있는 값을 표시 |
| AREAS(범위) | 범위 안에서의 영역 수를 표시 |
| HYPERLINK(위치) | 하드 드라이브, 네트워크 서버, 인터 넷 등 지정된 위치에 있는 문서를 열 수 있는 바로 가기 나 이동 텍스트를 만듦 |
핵심108 데이터베이스 함수/재무 함수
●데이터베이스 함수(제목포함전체범위, 계산할 제목, 제목포함 조건)
DSUM: 조건에 맞는 합계
DAVERAGE: 조건에 맞는 평균
DCOUNT: 조건에 맞는 숫자개수
DCOUNTA: 조건에 맞는 숫자 또는 문자개수 구하기
DMAX: 조건에 맞는 최대값
DMIN: 조건에 맞는 최소값
DPRODUCK: 조건에 맞는
DSTDEV: 조건에 맞는
DVAR: 조건에 맞는
DGET: 조건에 맞는
● 재무함수
FV(월이자율, 기간(월),-금액) => 저축
(이자,기간,금액,현재가치,납입시점): 미래가치를 구함(만기일에 받을 원금과 이자 계산)
PV(월이자율, 기간(월), -금액)=> 현재가치
:현재 가치를 구함, 1년 뒤에 받을 1000원의 가치를 구함
PMT(월이자울, 기간(월), -금액) => 대출
PMT(월이자울, 기간(월), ,-금액) => *저출 4번째 칸에다가 -금액 적음
핵심109 배열 수식
● 배열수식
•배열(Array)은 동일한 특성을 갖는 데이터들을 일정한 형식에 맞게 나열한 데이터의 집합이다.
•배열 수식은 배열 인수에 대해 여러 가지 계산을 수행 하고 하나 또는 여러 개의 결과를 반환한다.
•배열 수식에 사용되는 배열 인수 각각은 동일한 개수의 행과 열을 가져야 한다.
•배열 수식은 수식을 입력할 때Ctrl+Shift+Enter를 누르 는 것 외에는 다른 수식을 만들 때와 같다.
•Ctrl+Shift+Enter를 누르면 수식의 앞뒤에 중괄호(`{ }`)가 자동으로 입력된다.
•수식 입력줄이 활성화되면 배열 수식의 { }는 나타나지 않는다.
•여러 셀을 범위로 지정하고, 배열 수식을 입력하면 지 정된 범위에 모두 동일한 수식이 입력된다.
•동시에 입력된 배열 수식은 전체를 이동하거나 삭제할 수는 있어도 일부 수식을 수정, 삭제, 이동할 수는 없다.
핵심110 배열 상수
● 배열상수
•배열 수식에 사용되는 배열 인수를 배열 상수라고 한다.
•배열 상수의 종류 : 숫자, 텍스트, TRUE나 FALSE 등의 논리 값, #N/A 등의 오류 값 등
•배열 상수에 정수, 실수, 5E+3 같은 지수형 숫자를 사 용할 수 있다.
•다른 종류의 값을 같은 배열의 상수로 사용할 수 있다.
•배열 상수로 사용할 수 없는 값 : $, 괄호, %, 길이가 다른 행이나 열, 셀 참조 등
•배열 상수 값은 수식이 아닌 상수이어야 한다.
상수를 입력시 열의 구분은 쉼표(,) / 행의 구분은 세미클론(;)
•배열 상수로 사용할 범위에서 빈 칸은 0으로 취급된다.
•배열 상수를 입력할 때는 중괄호 { }를 직접 입력하여 상수를 묶어야 한다.
핵심111 배열 수식의 활용
● 배열수식의 활용
(1) 조건이 한 개일 때 배열 수식을 이용하여 개수 구하는 방법
•방법1 : {=SUM( (조건) * 1 )}
•방법2 : {=SUM( IF(조건, 1) )}
•방법3 : {=COUNT( IF(조건, 1) )}
***조건의 개수에 따라 조건을 지정하는 부분만 아래와 같이 늘어납니다.
•방법1 : {=SUM( (조건1) * (조건2) * 1 )}
•방법2 : {=SUM( IF(조건1, IF(조건2, 1 ) ) )}
•방법3 : {=COUNT( IF(조건1, IF(조건2, 1) ) )}
(2) 조건이 한 개일 때 배열 수식을 이용하여 합계 구하는 방법
•방법1 : {=SUM( (조건) * (합계를_구할_범위) )}
•방법2 : {=SUM( IF(조건, 합계를_구할_범위) )}
(3) 조건이 한 개일 때 배열 수식을 이용하여 평균 구하는 방법
•방법 : {=AVERAGE( IF(조건, 평균을_구할_범위) )}
핵심112 차트의 특징
●차트의 특징
•차트를 이용하면 데이터의 추세나 유형 등을 쉽고 직관 적으로 이해할 수 있을 뿐만 아니라, 많은 양의 데이터 를 간결하게 요약할 수도 있다.
•차트를 작성하기 위해서는 반드시 원본 데이터가 있어 야 한다.
•원본 데이터가 바뀌면 차트의 모양도 바뀐다.
•데이터가 입력된 셀 중 하나를 선택한 상태에서 차트를 만들면 해당 셀을 둘러싼 모든 셀의 데이터가 차트에 표시된다.
•차트는 2차원과 3차원 차트로 구분된다.
•차트만 별도로 표시할 수 있는 차트(Chart) 시트를 만 들 수 있다.
•기본 차트는 묶은 세로 막대형 차트지만 사용자가 다른 차트로 변경할 수 있다.
•차트에 사용될 데이터를 범위로 지정한 후 !을 누르면 -> 별도의 차트 시트에 기본 차트가 작성되고,
Alt+F1을 누르면 -> 데이터가 있는 워크시트에 기본 차트가 작성 된다.
●차트의 구성요소
범례랑 범례표지 구분하기
핵심113 차트 편집
●차트의 편집
| 차트의 종류 | [디자인]-[종류]-[차트종류변경] 바로가기메뉴 |
| 원본데이터 변경 | [디자인]-[종류]-[데이터선택] 바로가기메뉴 |
| 위치 | [디자인]-[종류]-[차트이동] 바로가기메뉴 |
| 크기조절 | ALT키 누른채로 드래그 => 눈금선에 맞게 크기조절 |
| 차트제목 범례 축 제목 데이터레이블 데이터표 눈금선 |
레이아웃-차트제목 |
| 데이터 계열 순서변경 | 차트영역 바로가기- 데이터선택 -범례항목계열 |
| 간격너비 계열 겹치기 | 바로가기메뉴-데이터계열-간격너비(0%~500%) 바로가기메뉴-데이터계열-겹치기(-100%~100%) |
핵심114 추세선 / 오차 막대
●추세선
•특정한 데이터 계열에 대한 변화 추세를 파악(예측, 분 석)하기 위해 표시하는 선이다.
추세선을 추가할 수 없는 차트- 3차원,방사형, 원형, 표면형, 도넛형
•추세선이 추가된 계열의 차트를 3차원으로 변경하면 추 세선이 삭제된다.
•추세선의 종류에는 선형, 로그, 다항식, 거듭제곱, 지 수, 이동 평균 이렇게 6가지가 있다.
•하나의 데이터 계열에 두 개 이상의 추세선을 동시에 표시할 수 있다
●오차막대(분산형, 거품형차트)
•데이터 계열의 오차량을 그림으로 나타낸 것이다.
•고정값, 백분율, 표준 편차, 표준 오차 등으로 표시할 수 있다.
•3차원 차트에는 오차 막대를 표시할 수 없다.
•세로 오차 막대 적용 가능 차트 : 영역형, 세로 막대형, 꺾은선형, 분산형, 거품형 차트 등
•세로 오차 막대, 가로 오차 막대 적용 가능 차트 : 분산 형, 거품형 차트
핵심115 용도별 차트의 종류
●차트의 종류
| 세로/가로 막대형 차트 |
•각 항목 간의 값을 막대의 길이로 비교·분석 •세로 막대형 차트에서는 항목 축을 수평, 값 축을 수직 으로 나타내고, 가로 막대형 차트에서는 항목 축을 수 직, 값 축을 수평으로 나타냄 |
| 꺾은선형 | 일정 기간 동안의 데이터 변화 추세를 확인함 |
| 원형 | •전체 항목의 합에 대한 각 항목의 비율을 표시 •항상 한 개의 데이터 계열만 가지고 있으므로 축이 없음 •다른 차트와 달리 데이터 레이블로 계열 이름, 항목 이 름, 값 외에 백분율을 표시할 수 있음 •차트의 각 조각을 분리할 수 있고 첫 번째 조각의 각 을 0~360도로 회전할 수 있음 |
| 분산형 | •X·Y 좌표로 이루어진 한 계열로 두 개의 숫자 그룹을 나타냄 •데이터의 불규칙한 간격이나 묶음을 보여 주며, 주로 과학·공학용 데이터 분석에 사용됨 •데이터 요소 수가 많아 데이터 요소 간의 차이점보다는 큰 데이터 집합 간의 유사점을 표시하기 위해 사용됨 |
| 영역형 | •시간에 따른 각 값의 변화량을 비교할 때 사용 •전체 영역과 특정 값의 영역을 비교해 전체와 부분 간 의 관계를 나타낼 수 있음 |
| 도넛형 | •전체에 대한 각 부분의 관계를 비율로 나타내어 각 부 분을 비교할 때 사용 •원형 차트와는 달리 여러 개의 데이터 계열을 갖음 •도넛 구멍의 크기를 10~90% 사이의 값으로 조정할 수 있고, 첫 번째 조각의 각을 0~360도로 회전할 수 있음 |
| 표면형 | 두 개의 데이터 집합에서 최적의 조합을 찾을 때 사용함 |
| 거품형 | •분산형 차트의 한 종류로, 데이터 계열값이 세 개인 경우에 사용함 •Z축에 해당하는 값(세 번째 변수값)을 작성하지 않고, 거품의 크기로 표시함 |
| 주식형 | •주식의 거래량과 같은 주가의 흐름을 파악하고자 할 때 사용함 •거래량, 시가, 고가, 저가, 종가 등을 나타내기 위해 5 개의 계열이 필요함 |
| 이중 축 차트 | •2개 이상의 데이터 계열을 가진 차트에서 또 하나의 값 축을 추가하여 이중으로 값을 표시하는 차트 •특정 데이터 계열의 값이 다른 데이터 계열의 값과 현저하게 차이가 나거나, 종류가 다른 2개 이상의 데이터 계열을 가진 차트에 효율적으로 사용됨 •왼쪽에 표시되는 세로(값) 축의 맞은편(오른쪽)에 보조 축이 표시됨 |
| 혼합형 (콤보) 차트 | •두 개 이상의 데이터 계열을 가진 차트에서 특정 데이 터 계열을 강조하기 위해 해당 데이터 계열을 다른 차 트로 표시하는 차트 •3차원, 주식형, 거품형, 표면형 차트는 혼합형 차트로 만들 수 없음 |
핵심116 확대/축소
•작업 화면의 크기를 10~400%까지 확대하거나 축소하 는 기능이다.
•확대/축소 배율은 지정한 시트에만 적용된다.
•‘확대/축소’ 대화상자의 사용자 지정 입력 상자에 직접 배율을 입력할 수 있다.
•Ctrl을 누른 채 마우스의 휠 버튼을 위로 굴리면 화면이 확대되고, 아래로 굴리면 화면이 축소된다.
•화면의 확대/축소는 인쇄 시 적용되지 않는다.
핵심117 틀 고정 / 창 나누기
● 틀 고정
•데이터의 양이 많은 경우, 열이나 행을 고정시켜 셀 포인터의 이동과 상관없이 특정 영역을 항상 표시하 기 위해 사용함
•화면에 표시되는 틀 고정 형태는 인쇄 시 적용되지 않음
•틀 고정을 수행하면 셀 포인터의 왼쪽과 위쪽으로 고 정선이 표시됨
•틀 고정선의 위치를 마우스로 조정할 수 없음
데이터의 양이 많은 경우, 특정한 범위를 셀 포인터와 상관없이 화면에 항상 표시
틀 고정 형태는 인쇄시 적용되지 않는다
셀 포인터의 왼쪽과 위쪽으로 고정선이 표시된다
● 창 나누기
•데이터의 양이 많아 필요한 데이터를 한 화면으로 보 기 어려운 경우, 창 나누기를 이용하면 서로 떨어져 있는 데이터를 한 화면에 표시할 수 있음
•창 나누기를 수행하면 셀 포인터의 왼쪽과 위쪽으로 창 구분선이 표시됨
•하나의 시트를 2개 혹은 4개의 영역으로 나눔
•창 나누기 구분선의 위치를 마우스로 이동시킬 수 있음
•마우스로 더블클릭하면 창 나누기 구분선이 제거됨
창 나누기 형태는 인쇄시 적용되지 않는다
핵심118 페이지 설정
● 페이지 설정
셀에 설정된 메모는 '시트에 표시된 대로' 인쇄할 수 있음.
시트 끝으로 인쇄하면 맨 마지막에 몰아서 인쇄할 수도 있는 것이고, 각 시트에 표시된대로 인쇄할 수도 있음.
| 페이지 | -자동맞춤: 용지 너비,용지 높이 =1로 설정하면 여러 페이지가 한 페이지에 인쇄됨. -배율은 워크시트 표준 크기의 10%~400%까지 가능. |
| 여백 | 페이지의 가로,세로, 정 가운데 출력 가능 |
| 머리글/바닥글 | 머리글/바닥글은 [머리글/바닥글]탭에서 설장함. 도구 모음을 이용하지 않고 머리글/바닥글을 삽입하려면 대괄로[ ]로 묶고 왼쪽 &를 붙이기 &[페이지번호] |
| 시트 | 인쇄영역, 인쇄 제목, 눈금선, 메모, 행/열 머리글 등의 인쇄여부와 페이지 순서등을 설정 간단하게 인쇄= 차트, 도형, 그림 등의 그래픽 요소를 제외하고 텍스트만 빠르게 인쇄 메모= 시트 끝에 뫄서 인쇄하거나 화면에 표시된 상태 그대로 인쇄 가능 페이지 순서 |
●여러 페이지를 한 페이지로 출력하는 방법
페이지 설정 대화상자의 페이지 탭에서 자동 맞춤의 용지 너비와 용지 높이를 1로 저장
●차트의 페이지 설정
- 차트를 선택한 상태에서 페이지 설정을 선택하면 ‘페 이지 설정’ 대화상자에 ‘시트’ 탭 대신 ‘차트’ 탭이 표시된다.
- 일반 시트의 인쇄 방법과 동일하게 머리글 및 바닥 글을 지정할 수 있다.
- 차트를 선택한 상태에서는 인쇄 영역을 지정할 수 없으므로 차트의 일부분만 인쇄할 수 없다.
- ‘차트’ 탭에서는 인쇄 품질을 지정할 수 있다.
- 차트를 제외하고 인쇄하려면 차트 영역을 더블클릭 한 후 ‘차트 영역 서식’ 창의
[도형 옵션] → [ (크기 및 속성)] → [속성]에서 ‘개체 인쇄’의 체크 표시를 해제해야 한다.
핵심119 페이지 나누기 / 페이지 나누기 미리보기 / 페이지 레이아웃 보기
● 페이지 나누기
| 자동 페이지 나누기 | 인쇄할 데이터가 많아 한 페이지 넘어가면 자동으로 페이지 구분선이 삽입됨 페이지 구분선은 용지 크기, 여백 설정, 설정한 배율옵션을 기준으로 설정됨 행 높이와 열 너비를 변경하면 자동 페이지 나누기의 위치도 변경됨 |
| 수동 페이지 나누기 | 셀 포인터의 위치를 기준으로 왼쪽과 위쪽 페이지를 구분선이 삽입됨 |
● 페이지 나누기 미리보기
작성한 문서가 출력될 떄의 페이지 경계선을 한눈에 볼 수 있는 기능
페이지 구분선, 인쇄 영역, 페이지 번호등이 표시
페이지 구분선을 마우스로 드래그하여 구분선의 위치를 변경하거나 삭제할 수 있다
페이지 나누기 미리보기 상태에서도 데이터 입력 및 편집할 수 있다
자동으로 표시된 페이지 구분선 = 점선, 수동으로 표시된 페이지 구분선= 실선
● 페이지 레이아웃 보기출룍
페이지 레이아웃 보기 상태에서도 데이터 입력 및 편집할 수 있다 + 셀 서식, 레이아수 등을 변경할 수 있다
워크시트에 머리글과 바닥글 영역이 함계 표시되어 머리글/바닥글을 바로 입력하거나 수정할 수 있다
행 높이, 열 너비, 페이지 여백, 머리글/바닥글 여백 등은 마우스를 드래그하여 조절할 수 있지만
페이지 구분선은 조절할 수 없다
출력물의 크기를 가늠할 수 있다
핵심120 인쇄 미리보기/ 인쇄 영역
● 인쇄 미리보기
인쇄영역이 설정된 경우 인쇄 미리보기 화면에는 인쇄 영역으로 설정된 부분만 표시된다
마우스로 드래그하여 여백의 크기나 열 너비를 조정할 수 있다
인쇄 미리 보기 상태에서 페이지 설정을 클릭하면 여백, 머리글/바닥글 등은 설정할 수 있지만
'시트' 탭의 인쇄 영역, 반복할 행, 반복할 열은 설정할 수 없다
● 인쇄 미리보기
워크시트의 내용 중 특정 부분만을 인쇄 영역으로 설정하여 인쇄할 수 있다
설정된 인쇄 영역은 통합 문서를 저장할 때 함께 저장된다
기존 인쇄 영역에 다른 인쇄 영역을 추가할 수 있다
여러 개의 인쇄 영역을 설정한 후 인쇄하면 설정한 순서대로 각기 다른 페이지에 인쇄된다
워크시트에 포함된 도형을 제외하고 인쇄하려면 도형의 바로가기메뉴에서 [크기 및 속성]-[속성 탭]-[개체 인쇄 옵션] 선택 해제한다
핵심121 정렬
● 데이터 정렬
•정렬 기준은 최대 64개까지 지정할 수 있으며, 기본적 으로 행 단위로 정렬된다
•원칙적으로 숨겨진 행이나 열에 있는 데이터는 정렬에 포함되지 않는다.
•정렬 기준에는 셀에 입력된 값이나 셀에 지정된 셀 색, 글꼴 색, 셀 아이콘이 있다.
•정렬방식에는 오름차순, 내림차순, 사용자지정목록이 있으며 사용자 지정 목록은 모든 정렬 기준에서 지정 가능
•영문자 대/소문자를 구분하여 정렬할 수 있는 기능을 제공하며, 오름차순 시 소문자가 우선순위를 갖는다.
•오름차순은 숫자 > 문자 > 논리값 > 오류값 > 빈 셀 순, 내 림차순은 오류값 > 논리값 > 문자 > 숫자 > 빈 셀 순이다.
- 문자는 오름차순 시 특수 문자, 영문자(소문자, 대문자) 한글 순으로 정렬된다.
- 논리 값은 오름차순 시 거짓값(False), 참값(True) 순으로 정렬된다.
오름차순시 소문자가 우선순위를 갖는다
오름차순은 숫자> 문자 > 논리값 > 오류값> 빈 셀 순
내림차순은 오류값 > 논리값 > 문자 > 숫자 > 빈 셀 순
*빈 셀은 둘다 맨 마지막
문자는 오름차순시 특수문자> 한글> 영문자(소문자, 대문자) 순
[정렬옵션]:
- 대/소문자를 구분해 정렬할 것인지를 지정할 수 있다.
- 정렬할 방향을 지정할 수 있다.
▶ 위쪽에서 아래쪽 : 열 기준 ▶ 왼쪽에서 오른쪽 : 행 기준
[정렬기준]: 값, 셀 색, 글꼴 색, 셀 아이콘
※ 사용자 지정 정렬
- 사용자가 ‘사용자 지정 목록’에 등록한 목록을 기준 으로 정렬하는 기능이다.
- 일정한 연관성을 가진 문자열을 정해진 순서대로 만들어 놓은 것으로, 자동 채우기나 정렬에서 사용
- 목록(정렬 순서)을 추가하거나 삭제할 수 있으나 엑셀에서 기본적으로 제공하는 목록은 수정하거나 제 거할 수 없다.
•‘정렬’ 대화상자의 ‘내 데이터에 머리글 표시’ :
선택한 데이터 목록의 첫 번째 행이 필드명일 경우 ‘내 데이터에 머리글 표시’를 선택하여 정렬 대상에서 제외시키고,
첫 번째 행이 필드명이 아닌 경우에는 ‘내 데이터에 머리글 표시’를 해제하여 첫 행을 정렬 대상에 포함시킴
‘정렬 경고’ 대화상자
•데이터 목록 중 한 행이나 한 열만 정렬 범위로 지정한 경우 ‘정렬 경고’ 대화상자가 표시됩니다.
•선택 영역 확장 : 현재 셀 포인터와 인접한 영역에 있는 데이터 목록 전체가 정렬 범위로 확장되어 정렬이 수행됨
•현재 선택 영역으로 정렬 : 현재 선택된 영역만을 기준으로 정렬이 수행됨
핵심122 자동 필터
● 자동필터
•단순한 비교 조건을 사용하여 간단한 데이터 추출 작업 에 사용되는 필터이다.
•자동 필터를 사용하려면 데이터 목록에 반드시 필드명 (열 이름표)이 있어야 한다.
자동필터를 사용하여 추출한 결과는 원본 목록(데이터 목록)의 위치에 표시된다 => 다른 곳에서 추출 불가능
두 개 이상의 필드(열)에 조건이 설정된 경우, AND조건으로 정해짐
동일한 필드에는, OR조건이 가능하다•자동 필터를 적용하면 지정한 조건에 맞지 않는 행은 숨겨진다.
•필터링된 데이터 그대로 복사나 찾기, 편집, 인쇄 등을 수행할 수 있다.
•자동 필터를 사용하면 목록 값, 서식, 조건을 이용하여 세 가지 유형의 필터를 만들 수 있지만,
한 번에 한가지 필터만 적용할 수 있다.
•필드(열)에 입력된 데이터에 따라 [숫자 필터], [텍스트 필터], [날짜 필터]가 표시되고,
하나의 필드에 날짜, 숫자, 텍스트가 섞여 있으면 가장 많이 있는 형식의 필터 가 표시된다.
•필터를 이용하여 추출한 데이터는 레코드(행) 단위로 표시된다.
•상위 10 자동 필터 : 항목이나 백분율을 기준으로 상위나 하위로 데이터의 범위를 지정하여 해당 범위에 포함된 레코드만 추출하는 기능으로, 숫자 필드에서만 사용할 수 있음
•사용자 지정 자동 필터 : 하나의 필드에 1개나 2개의 조건 을 지정하여 추출하는 기능
핵심123 고급 필터
자동필터에 비해 복잡한 조건을 사용하거나 여러 필드를 결합하여 조건을 지정할 경우
자동필터를 사용하여 추출한 결과는 원본 목록(데이터 목록)의 위치가 아닌 다른 곳에서 추출 가능
조건에 맞는 특정한 필드(열)만을 추출할 수도 있다
조건을 갖는 행에 입력하면 AND조건으로 정해짐, 다른 행에 입력하면, OR조건이 가능하다
| 기본조건 지정 방법 |
•조건을 지정할 범위의 첫 행에는 원본 데이터 목록의 필드명을 입력하고, 그 아래 행에 조건을 입력함 •조건을 모두 같은 행에 입력하면 AND, 다 른 행에 입력하면 OR 조건임 •조건을 지정할 때 ‘ ?, * ’ 등의 만능 문자 (와일드 카드)도 사용할 수 있음 •고급 필터의 조건으로 일반적인 수식이 아닌 값에 대한 비교 연산자로 등호(=)를 사용할 때는 =“=항목” 형식으로 입력함 |
| 고급 조건 지정 방 | •함수나 식의 계산 값을 고급 필터의 찾을 조건으로 지정하는 방식 •조건 지정 범위의 첫 행에 입력될 조건 필 드명은 원본 데이터의 필드명과 다른 필드 명을 입력하거나 생략하며, 그 아래 행에 조건을 입력함 •함수나 식을 사용하여 조건을 입력하면 셀 에는 비교되는 현재 대상의 값에 따라 TRUE나 FALSE가 표시됨 •함수와 식을 혼합하여 조건을 지정할 수 있음 •함수나 식을 사용해도 AND나 OR 조건을 입력하는 방법은 동일함 |
핵심124 텍스트 마법사 / 텍스트 나누기
● 텍스트 마법사
일정한 너비나 기호로 구분된 텍스트 파일을 워크시트로 가져올 때 사용
탭, 세미클론, 쉼표, 공백 등의 구분 기호가 제공되며, 사용자가 구분 기호를 정의할 수 있다
실행: [오피스단추]-[열기] 메뉴,
[데이터]-[외부데이터 가져오기]-[텍스트] 중 하나 이용
•*.txt, *.prn, *.csv 등의 텍스트 파일 형식을 불러옵니다.
•특정 열(Column)만 선택하여 가져올 수 있습니다.
•불러온 데이터가 들어갈 위치(기존 워크시트 또는 새 워크시트)를 사용자가 지정할 수 있습니다.
•원본 텍스트 파일이 수정된 경우 [데이터] → [연결] → [ (모두 새 로 고침)]을 클릭하면 수정된 내용이 반영
•텍스트 마법사 실행 순서
- 1단계 : 텍스트를 열로 나눌 방법 선택(구분 기호로 분 리됨, 너비가 일정함)
- 2단계(구분 기호로 구분된 데이터)
▶ 탭, 세미콜론, 쉼표, 공백 등의 구분 기호가 제공되 며, 사용자가 구분 기호를 정의할 수 있음
- 2단계(너비가 일정한 데이터)
▶ 열 구분선 삽입 : 원하는 위치를 마우스로 클릭
▶ 열 구분선 삭제 : 구분선을 마우스로 두 번 클릭
▶ 열 구분선 이동 : 열 구분선을 원하는 위치로 드래그
- 3단계 : 데이터 서식 지정 및 불러올 때 제외할 열 지정
핵심125 외부 데이터 가져오기 개념
● 외부 데이터 가져오기
•SQL, dBASE, Access 등에서 사용하는 데이터베이스 파일과 텍스트 파일 등을 워크시트로 가져오거나
데이 터베이스 파일을 쿼리 형태로 변경하여 워크시트에서 사용할 수 있도록 하는 기능이다.
•Microsoft Query, VBA, 웹 쿼리 등을 이용하여 외부 데 이터를 가져오거나 쿼리를 작성할 수 있다.
•엑셀에서 가져올 수 있는 외부 데이터 종류
데이터베이 스 파일(SQL, Access, dBase), 웹(*.htm), XML, 텍스트 파일(txt, prn),
엑셀 파일(xlsx, xlsm), 쿼리(*. dqy), OLAP 큐브 파일(*.oqy) 등
•Excel이나 데이터베이스 파일에서 데이터를 가져올 경 우 여러 테이블로 구성된 데이터를 가져올 수 있다.
•외부 데이터 가져오기를 사용하여 가져온 데이터는 원 본 데이터가 변경될 경우 가져온 데이터에도 반영되도 록 설정할 수 있다.
데이터베이스나 텍스트파일을 워크시트로 가져오는 기능
테이블로 구성된 데이터를 가져올 수 있다
원본이 변경될 경우, 가져온 데이터도 변경될 수 있다
외부데이터형식
-텍스트 파일, Excel 파일, 웹, XML, 데이터베이스 파일(DQL, Access, dBASE, 쿼리(.dqy), OLAP 큐브 파일
핵심126 Microsoft Query / 웹 쿼리
●Microsoft Query
•외부 데이터베이스에서 가져올 데이터의 추출 조건을 쿼리로 만들어 가져오거나 만든 쿼리를 다음에 사용할 수 있도록 저장한다.
•데이터베이스 파일(SQL, Access, dBASE), 쿼리 (*.dqy), OLAP 큐브 파일(*.oqy)을 가져오거나 쿼리 파일로 만들 수 있다.
•새 쿼리는 하나의 통합 문서에 대해서만 만들 수 있다.
• 실행 [데이터] → [외부 데이터 가져오기] → [기타 원 본에서 데이터 가져오기] → [Microsoft Query] 선택
●웹 쿼리
•웹 페이지에서 테이블이나 텍스트와 같은 데이터를 검 색하여 워크시트에서 사용할 수 있도록 가져오거나 웹 쿼리를 작성할 때 사용한다.
•웹 페이지에서 텍스트, 서식이 설정된 텍스트 영역, 테이블의 텍스트 등은 가져올 수 있지만
그림과 스크립트 의 내용은 가져올 수 없다.
•웹 페이지의 데이터를 검색하여 가져오려면 인터넷에 연결되어 있어야 하고, 웹 브라우저가 설치되어 있어야 한다
핵심127 부분합
● 부분합
•많은 양의 데이터 목록을 그룹별로 분류하고, 각 그룹 별로 계산을 수행하는 데이터 분석 도구이다.
•부분합을 작성하려면 첫 행에는 열 이름표가 있어야 하며,
반드시 기준이 되는 필드를 기준으로 오름차순이나 내림차순으로 정렬되어 있어야 한다.
•SUBTOTAL을 사용하여 합계나 평균 등의 요약 함수 를 계산한다.
•같은 열에 있는 자료에 대하여 여러 개의 함수를 중복 사용하여 다중 함수 부분합을 작성할 수 있다.
•사용할 수 있는 함수 : 합계, 개수, 평균, 최대값, 최소값, 곱, 숫자 개수, 표준 편차, 표본 표준 편차, 표본 분산, 분산
•부분합을 작성하면 워크시트 왼쪽에 부분합을 계산한 하위 그룹 단위로 윤곽이 설정되고, 윤곽 기호가 나타난다.
하나의 부분합만 나타낼때는 새로운 값 대치 체크가 되어있는 상태에서 작업하면 되지만
두 개 이상으로 중첩하여 부분합을 작업을 할 때는 반드시 새로운 값으로 대치 체크된거 눌러서 해제를 해야합니다.
즉, 새로운 값으로 대치 체크된 것을 매번 해제를 해야합니다.
중첩 부분합을 작성하려면 중첩할 부분합 그룹의 기준 필드들이 정렬(2차 정렬 기준)이 되어야 하고,
'부분합' 대화상자에서 반드시 '새로운 값으로 대치'를 해제해야 한다.
●윤곽기호
윤곽이 설정된 부분합 워크시트의 모양을 바꿀 때 사용하는 기호
윤곽기호를 이용하여 워크시트에서 하위 수준(그룹)을 숨기거나 나타낼 수 있다
•부분합을 제거하면 부분합과 함께 표에 삽입된 윤곽 및 페이지 나누기도 모두 제거된다.
•부분합의 결과로 차트를 작성하면 화면에 보이는 데이터에 대해서만 차트가 작성된다.
• 실행 [데이터] → [윤곽선] → [부분합] 클릭
•‘부분합’ 대화상자의 주요 항목
- 그룹화할 항목 : 값을 구하는 기준이 되는 항목을 선택 (정렬된 항목)
- 사용할 함수 : 사용할 함수를 선택
- 부분합 계산 항목 : 함수를 적용할 필드를 선택
- 새로운 값으로 대치 : 이미 작성된 부분합을 지우고, 새 부분합으로 변경할 경우 선택함
- 그룹 사이에서 페이지 나누기 : 부분합을 구한 뒤 각 그 룹 다음에 페이지 나누기를 자동으로 삽입함
- 데이터 아래에 요약 표시 : 부분합의 결과를 각 그룹의 아래쪽에 표시할지, 위에 표시할지를 결정함. 아래쪽 에 표시할 때 선택함
- 모두 제거: 부분합을 해제하고, 원래 데이터 목록을 표시함
핵심128 피벗 테이블
● 피벗테이블(요약분석보고서)
•많은 양의 데이터를 한눈에 쉽게 파악할 수 있도록 요 약·분석하여 보여주는 도구이다.
•엑셀 목록, 데이터베이스, 외부 데이터, 다른 피벗 테이 블 등의 데이터를 사용할 수 있다.
•각 필드에 다양한 조건을 지정할 수 있으며, 일정한 그 룹별로 데이터 집계가 가능하다.
•원본 데이터가 변경되면 [피벗 테이블 도구] → [분석] → [데이터] → [새로 고침( )]을 이용하여 피벗 테이블 의 데이터도 변경할 수 있다.
•피벗 테이블 작성 시 피벗 테이블의 작성 위치를 지정 하지 않으면 새 워크시트에 작성된다.
•작성한 피벗 테이블의 필드 단추를 다른 열이나 행으로 드래그하여 변경할 수 있으며,
필드 단추의 위치를 변경하면 데이터 표시 형식이 변경된다.
•사용자가 원본 데이터를 기초로 피벗 테이블에 새로운 필드를 추가할 수 있다.
• 실행 [삽입] → [표] → [피벗 테이블] 클릭
- 행 영역에 지정한 필드는 하나의 열에 모두 표시되고, 열 영역에 지정한 필드는 하나의 행에 모두 표시된다.
- 행 영역이나 열 영역에 표시된 데이터는 수정할 수 있으나 값 영역에 표시된 데이터는 수정할 수 없다.
- 값 영역에 두 개 이상의 필드를 지정하면 열 영역 또 는 행 영역에 값(Σ) 필드가 생성된다.
- 값 필드를 열 영역과 행 영역 중 어디에 삽입하느냐에 따라 값 영역에 추가된 필드의 표시 방향이 결정된다.
●피벗 테이블의 그룹화
- 특정 필드를 일정한 단위로 묶어 표현할 때 사용하는 것으로, 문자, 숫자, 날짜, 시간 등 모든 필드에서 사용할 수 있다.
- 숫자나 날짜 필드일 경우에는 ‘그룹화’ 대화상자에서 시작, 끝, 단위를 지정한다.
- 문자 필드에 그룹 만들기를 실행하면 자동으로 그룹 1, 그룹2, …와 같은 그룹 이름이 자동으로 부여된다
- 그룹을 지정하려면 그룹으로 묶고자 하는 데이터를 기준으로 먼저 정렬해야 하는데, 정렬 방식은 오름차 순이든, 내림차순이든 상관 없다.
- 그룹화 : 그룹을 지정할 필드의 바로 가기 메뉴에서 [그룹] 선택
- 그룹 해제 : 그룹으로 설정된 필드의 바로 가기 메뉴 에서 [그룹 해제] 선택
●피벗 차트 보고서
- 피벗 테이블의 데이터를 이용하여 작성한 차트로,
피벗 테이블에서 항목이나 필드에 변화를 주면 피벗 차트도 변경되고
반대로 피벗 차트에서 변화를 주면 피벗 테이블도 변경된다.
- 피벗 차트 작성 시 자동으로 피벗 테이블도 함께 만들어짐.
- 피벗 차트는 피벗 테이블을 작성할 때 함께 작성하거나, 이미 작성된 피벗 테이블을 이용하여 작성한다.
- 피벗 차트는 피벗 테이블을 사용하므로 피벗 테이블을 만들지 않고 피벗 차트를 작성할 수 없다.
- 피벗 차트를 추가하면 피벗 테이블이 있는 워크시트 에 삽입된다.
원본 데이터가 변경되면 데이터 모두 새로 고침 아이콘을 이용하여 피벗테이블의 데이터도 변경할 수 있다
=>차트는 원본이 변경되면 자동으로 바뀌지만
피벗테이블은 자동으로 바뀌지 않는다 따라서 새로고침을 눌러야함
- 피벗 테이블을 삭제하면 피벗 차트가 일반 차트로 변경되지만, 피벗 테이블=> 피벗 차트 영향 O
피벗 차트를 삭제해도 피벗 테이블에는 아 무 변화가 없다. 피벗 차트 => 피벗 테이블 영향X
- 표준 차트의 항목, 계열, 데이터가 피벗 차트에서는 축 필드(항목), 범례 필드(계열), 값 필드에 해당한다.
- 분산형, 거품형, 주식형 차트는 피벗 차트로 만들 수 없다.
- 데이터 새로 고침 : 피벗 테이블은 원본 데이터와 연결되어 있지만, 원본 데이터가 변경될 때 자동으로 피벗 테이블 내용을 변경하지 못함.
핵심129 시나리오
●시나리오
결과 셀은 반드시 변경 셀을 참조하는 수식으로 입력되어야 한다
하나의 시나리오는 최대 32개의 변경 셀을 지정할 수 있다
작업시트의 입력된 값을 변경하면 원래의 값은 되살릴 수 없다
시나리오 병합을 통하여 다른통합문서나 워크시트에 저장된 시나리오를 가져올 수 있다
•다양한 상황과 변수에 따른 여러 가지 결과값의 변화를 가상의 상황을 통해 예측하여 분석하는 도구이다.
•변경 셀에는 데이터를 변경할 셀의 범위를 지정하고, 결과 셀은 반드시 변경 셀을 참조하는 수식으로 입력되 어야 한다.
•시나리오의 결과는 요약 보고서나 피벗 테이블 보고서 로 작성할 수 있다.
•이자율, 손익 분기점, 주가 분석 등에 많이 사용된다.
•시나리오를 작성하면 현재 작업하는 시트 앞에 새 워크시 트를 삽입하고 그 시트에 시나리오 보고서를 표시한다.
- 시나리오 이름과 변경 셀을 입력할 수 있는 대화 상자를 표시한다.
- 변경 셀에는 데이터를 변경할 셀의 범위를 지정한다.
- 그룹을 지정하려면 그룹으로 묶고자 하는 데이터를 기준으로 먼저 정렬해야 하는데, 정렬 방식은 오름차 순이든, 내림차순이든 상관 없다.
➊시나리오 관리자
•‘시나리오 관리자’ 대화상자에서 시나리오를 삭제해도 이미 작성된 시나리오 요약 보고서는 삭제되지 않고,
반대로 시나리오 요약 보고서를 삭제해도 시나리오는 삭제되지 않는다.
•시나리오가 작성된 원본 데이터를 변경해도 이미 작성 된 시나리오 보고서에는 반영되지 않는다.
•‘변경 셀’과 ‘결과 셀’에 이름을 지정한 후 시나리오 요약 보고서를 작성하면 셀 주소 대신 지정한 이름이 표시 된다.
➊시나리오 요약 보고서 작성 순서
➊ [데이터] → [예측] → [가상 분석] → [시나리오 관리 자]를 선택하여 시나리오 관리자 실행
➋ <추가>를 클릭하여 시나리오 이름을 정의하고 변경 셀을 선택
➌ 변경할 값 입력
➍ ‘`시나리오 관리자`’ 대화상자에서 <요약> 클릭
➎ 보고서 종류에서 시나리오 요약을 선택하고 결과 셀을 지정한 후 <확인> 클릭
➊시나리오 피벗 테이블 보고서
•여러 시나리오를 서로 비교하기 위해 시나리오를 피벗 테이블로 요약할 수 있다.
-시나리오 요약 보고서를 생성하기 전에 변경 셀과 결과셀에 이름을 정의하면 셀 참조 주소 대신 정의된 이름이 보고서에 표시된다.
-시나리오 요약 보고서는 자동으로 다시 갱신되지 않으므로 변경된 값을 요약 보고서에 표시하려면 새 요약 보고서를 만들어야 한다.
시나리오 '요약 보고서'를 만들 때에는 결과 셀을 반드시 지정할 필요가 없지만,
시나리오 '피벗 테이블 보고서'를 만들 때에는 결과 셀을 지정하지 않으면
[시나리오 피벗 테이블 보고서를 만들려면 결과셀을 지정해야 합니다.]라는 오류메세지가 뜨며 ,결과셀을 반드시 지정해야 합니다.
※ 시나리오는 별도의 파일로 저장되는 것이 아니라 워크시트에 저장되며, 변경할 값을 자동으로 입력한 것이 아니라 수동으로 입력한 것이다.
핵심130 목표값 찾기
● 목표값찾기
•수식에서 원하는 결과(목표) 값은 알고 있지만 그 결과 값을 계산하기 위해 필요한 입력값을 모를 경우에 사용
•목표값 찾기는 변하는 데이터를 하나만 지정할 수 있다.
•목표값 찾기에서 목표값(찾는 값)은 사용자가 원하는 데이터를 직접 입력해야 한다.
•결과값은 입력값을 참조하는 수식으로 작성되어야 한다.
주어진 결과값에 대해 하나의 입력값만 변경할 수 있다
목표값 찾기 대화상자의 '찾는 값' 항목은 키보드를 통합 입력만 가능하며, 특정 셀을 선택하여 지정할 수 없다
- 수식의 결과값은 알고 있으나, 결과값을 얻기 위한 입력값을 모를 때 목표값 찾기 기능 이용
실행: 데이터 - 데이터도구 - 가상분석 - 목표값 찾기
- 수식 셀, 찾는 값, 값을 바꿀 셀을 지정
- 찾는 값 : 수식 셀의 결과로, 원하는 특정한 값을 숫자 상수로 입력함.
[데이터] → [예측] → [가상 분석] → [목표값 찾 기] 선택
핵심131 데이터 표
● 데이터 표
•특정 값의 변화에 따른 결과값의 변화 과정을 표의 형 태로 표시해주는 도구이다.
•결과값은 반드시 변화하는 특정 값을 포함한 수식으로 작성되어야 한다.
•수식이 입력될 부분을 범위로 설정한 후 데이터 표를 실행해야 한다.
•데이터 표 기능을 이용하여 계산된 결과는 참조하고 있는 셀의 데이터가 수정되면 자동으로 갱신된다.
•데이터 표의 결과는 일부분만을 수정하거나 삭제할 수 없다. (데이터표 범위의 전체를 수정해야함)
•데이터 표는 지정한 특정 값의 수에 따라 단일 표와 이중표로 구분한다.
•단일 데이터 표에서는 [행 입력 셀]이나 [열 입력 셀] 중 하 나만 사용하고 이중 데이터 표에서는 2개 모두 사용한다.
• 실행 [데이터] → [예측] → [가상 분석] → [데이터 표] 선택
모서리에 계산식을 넣어줘야 하며 ( 행 입력셀, 열 입력셀에 입력)
- 행 입력 셀 : 행에 있는 변화되는 값을 사용할 주소를 지정함
- 열 입력 셀 : 열에 있는 변화되는 값을 사용할 주소를 지정함
핵심132 데이터 통합
● 데이터 통합
•데이터 통합은 비슷한 형식의 여러 데이터를 하나의 표 로 통합·요약하여 표시해주는 도구이다.
•사용할 데이터의 형태가 다르더라도 같은 이름표를 사용하면 항목을 기준으로 통합할 수 있다.
•사용할 수 있는 함수에는 합계, 개수, 평균, 최대값, 최 소값, 곱, 숫자 개수, 표본 표준 편차, 표준 편차, 표본 분산, 분산이 있다
•다른 워크시트나 통합 문서의 데이터를 사용할 수 있다.
•지정한 항목이나 위치를 기준으로 통합한다.
원본데이터에 연결:
통합할 데이터의 워크시트와 결과가 작성될 워크시트가 서로 다른 경우에만 적용됨(시트가 동일하면 안 됨)
첫 행과 왼쪽 행 체크(상황에 따라서)
• 실행 [데이터] → [데이터 도구] → [통합] 클릭
핵심133 매크로
● 매크로
•매크로는 엑셀에서 다양한 명령들을 일련의 순서대로 기록해 두었다가 필요할 때마다 해당 키나 도구를 이용 하여 호출하면 기록해 둔 처리 과정이 수행되도록 하는 기능이다.
•매크로 기록에 사용된 명령과 함수는 Visual Basic 모듈에 저장되므로 Visual Basic Editor를 사용하여 내 용을 추가, 삭제, 변경할 수 있다.
- Visual Basic Editor 실행 방법
[개발 도구] → [코드] → [Visual Basic] 클릭
방법 2 Alt+F11 누름
- Visual Basic Editor에서 매크로 전체를 복사하려면 Sub부터 End Sub까지를 복사하면 된다.
•매크로 기록 중에 선택된 셀의 주소는 기본적으로 절대 참조로 기록되지만
[개발 도구] → [코드] → [상대 참조 로 기록]을 이용하여 상대 참조로 기록할 수 있다.
•매크로를 절대 참조로 기록하면 매크로를 실행할 때 선택한 셀의 위치를 무시하고 매크로가 셀을 선택한다.
●‘매크로 기록’ 대화상자
•매크로 이름, 바로 가기 키, 매크로 저장 위치, 설명을 지정할 수 있다.
•매크로 이름
- ‘매크로1, 매크로2, …’ 등과 같이 자동으로 부여되 는 이름을 지우고 사용자가 임의로 지정할 수 있다.
- 첫 글자는 반드시 문자로 지정해야 하고, 두 번째부터 문자, 숫자, 밑줄 문자( _ ) 등의 사용이 가능하다.
- / ? ‘ ’ . - ※ 등과 같은 문자와 공백은 매크로 이름으로 사용할 수 없다.
- 하나의 통합 문서에는 이름이 동일한 매크로가 존재 할 수 없다.
- 매크로 이름을 ‘Auto_Open’으로 지정하면 해당 통합 문서를 열 때마다 기록된 매크로가 자동으로 실행 된다.
•바로 가기 키
- 바로 가기 키는 영문자만 가능하고, 입력하지 않아도 매크로를 기록할 수 있다.
바로가기 키는 Ctrl과 조합하여 사용하나
대문자를 지정할 때에는 Shift가 자동으로 추가 지정된다- 엑셀에서 이미 사용하고 있는 바로 가기 키를 매크로 의 바로 가기 키로 지정할 수 있지만,
바로 가기 키가 동일한 경우 엑셀의 바로 가기 키가 동작하지 않 는다.
- 바로 가기 키 지정 후 수정이 가능하다.
•매크로가 저장되는 위치
- 개인용 매크로 통합 문서 : XLSTART 폴더 안의 PERSONAL.XLSB에 저장되므로 엑셀을 실행시킬 때마다 사용 가능함
※ XLSTART 폴더에 있는 모든 파일은 엑셀이 실행될 때 자동으로 열림
- 새 통합 문서 : 새로운 통합 문서에 매크로를 만들어 사용함
- 현재 통합 문서 : 기본 저장 위치로 현재 작업하고 있 는 통합 문서에만 적용시킬 때 사용함
•설명 : 해당 매크로에 대한 간략한 설명으로 사용자가 임의로 지정할 수 있음
핵심134 매크로 실행
● 매크로 실행방법
•다음과 같이 수행한 후 ‘매크로’ 대화상자에서 매크로 이름을 선택하여 실행한다.
[보기] → [매크로] → [매크로( )] 클릭
[개발 도구] → [코드] → [매크로] 클릭
Alt+F8 누름
•매크로를 기록할 때 지정한 바로 가기 키를 누른다.
•워크시트에 삽입된 도형, 그림, 차트나 양식 컨트롤을 이용해 만든 단추 등에 매크로를 지정하여 실행한다.
•Visual Basic Editor에서 매크로 실행 방법
F5 : 일반적인 실행 방법
F8 : 한 단계씩 코드 실행
Ctrl+ F8 : 모듈 창의 커서 위치까지 실행
•‘매크로’ 대화상자
- 실행 : 선택한 매크로를 실행함
- 한 단계씩 코드 실행 : 선택한 매크로를 한 줄씩 실행 함(디버깅 용도)
- 편집 : 선택한 매크로를 Visual Basic Editor를 이 용해 매크로 이름이나 명령 내용을 편집함
- 만들기 : Visual Basic Editor를 이용해 매크로를 작 성함
- 옵션 : 선택한 매크로의 바로 가기 키나 설명을 변경함
*엑셀에서 이미 사용하고 있는 바로 가기 키를 매크로의 바로 가기 키로 지정할 수 있음.
바로 가기 키가 동일 한 경우 매크로 실행의 바로 가기 키가 엑셀 고유의 바로가기 키보다 우선
핵심135 프로그래밍의 기본
●프로그래밍
모듈: 프로그램을 구성하는 기본단위
-폼 모둘: 사용자정의 폼을 디자인, 프로시저 작성
-보고서모듈: 보고서를 디자인, 프로시저 작성
프로시저: 특정기능을 수행하는 명령어의 집합
Sub - End Sub: 가장 일반적인 형태, 결과값을 반환하지 않음
Function - End Function: 코드를 실행하고 결과값을 반환 (사용자정의함수로 사용)
Property - End Property: 개체의 속성을 새로 정의할 때
- 개체: 작업한 내용이 실제 적용되는 대상
- 속성: 개체의 특성이나 상태
- 매서드: 개체가 실행할 수 있는 동작 또는 활동 (개체명, 매서드)
- 이벤트: 프로그램 사용 중에 일어나는 사건 (클릭, 더블클릭)이 일어났을 때 실행되는 프로시저
한줄에 두개 이상의 명령문 입력 시 콜론(:)을 사용.
Range("A1").FORMULA = 3*4 라는 것은
[A1]셀에 3*4의 결과 12가 입력됨. 수식이 그대로 셀에 나타나려면 Range("A1").FOMULA = "=3*4"로 하고 해당 셀이 텍스트 표시 형식이어야 함.
CLearformats 메서드 : 개체의 서식을 지움
range.clearformats 매서드 : 개체 서식을 지움(같음)
Range("a5".select = [a5]셀로 셀 포인터를 이동한다
●변수: 변하는 값을 저장하는 곳
- DIM: 위치에 따라 다르며 프로시저 처음에 선언하면 프로시저만에서만, 모듈의 처음에 선언하면 모듈내에서만 사용
- STATIC: 모둘 처음에 선언, 종료되어도 값을 유지
- PRIVATE: 모듈 처음에 선언, 모든 프로시저내에서 사용가능
- PUBLIC: 모듈 처음에 선언, 모든 모듈내에서 사용가능
●변수규칙
부호나 공백 사용 불가능
255자, 문자숫자밑줄(언더바) 포함
VBA 키워드(예약어)를 지정할 수 없다
핵심137 VBA 기본 문법 – If-~ Then
핵심138 VBA 기본 문법 – For~Next / For Each ~ Next
핵심139 VBA 기본 문법 – Do~Loop/ Do Until~Loop
핵심140 Workbook(Workbooks) 개체
핵심141 Worksheet(Worksheets) 개체
핵심142 Range 개체
