컴퓨터활용능력1급 - 2과목

2주 합격 도전 - Day2

2과목 스프레드시트

워크시트 구성

• 시트 : ctrl + 방향키로 확인 가능 1,048,576행, 16,384열
• 행 : 번호 (shift + spacebar 행 전체선택)
• 열 : 문자 (ctrl + spacebar 열 전체선택)
• 셀 : ctrl + shift + spacebar / ctrl + a : 전체선택
• 시트탭 : ctrl + PgDn/PgUp 전환 가능
  이름 변경 (더블클릭)
  복사(ctrl)
  삽입(shift+f11)
  여러 개의 시트 선택 = 그룹
  시트이름 : 공백 포함 31자까지 허용 (특수문자, 중복 x)
  시트와 관련된 작업은 실행취소 (ctrl+z) 불가능
• ctrl 비연속 복사 shift 연속적 이동(끼워넣기)
• 그룹으로 묶은 시트에서 복사하거나 잘라낸 모든 데이터는 다른 한 개의 시트에 붙여넣을 수 없음

데이터 입력과 삭제

• 데이터 입력
  숫자(오른쪽 정렬, 사칙 연산이 가능한 자료, 분수 입력 0 1/2)
  문자(왼쪽 정렬, 사칙연산 불가, 숫자에 '를 붙이면 문자로 인식 '5)
  날짜(우정렬, 빼기 또는 나누기 기호로 연월일 구분)
  시간(우정렬, 콜론 : 으로 시분초 구분)
  수식(등호=, 더하기+, 빼기- 기호로 시작, 날짜 및 시간은 "붙여 사용)
• 지금계산 : f9
• ctrl + ~ : 수식 확인 가능

날짜와 시간데이터

• 날짜
  연, 월만 입력 : 1일
  월, 일만 입력 : 현재 년도
  년도 입력 : 00~29 -> 2000년, 30~99 -> 1900년
  하루를 일련번호로 저장 1 = 1900-1-1
  오늘날짜 : ctrl + ;
• 시간
  시, 분만 입력 : 0초
  하루를 비율로 계산하여 소수로 저장 12시 = 0.5
  현재 시간 : ctrl+shift+;

데이터 입력 단축키

• alt+enter : 셀에서 줄바꿈
• alt+↓ : 목록 표시
• ctrl+enter : 일괄 입력
• ctrl+방향키 : 자료 끝 이동
• home : 행의 a열
• ctrl+home : A1셀로 이동
• ctrl+* : 데이터만 선택
• shift+방향키 : 영역 잡기
• shift+enter+(⇥/⇤) : 역방향
• f1 : 내용 편집
• f5 : 이동
• f8 : 선택 영역 확장

데이터 삭제

(영역을 잡은 상태에서)

• backspace : 한 셀 지우기
• del : 영역 전체 지우기 (내용 지우기와 같음)

엑셀 옵션과 기타 기능

• 엑셀 옵션 (파일/엑셀옵션)
  일반 : 글꼴, 기본보기, 시트수(1~255개), 사용자 이름,
  수식 : R1C1 참조 스타일
  저장 : 자동 복구 저장
  고급 : 엔터 방향, 소수점 자동 삽입(입력한 숫자 앞으로 입력한 수만큼), 셀 내용 자동 완성, 사용자 지정 목록 편집(정렬 기능에서 활용 가능)
  리본메뉴 : 개발 도구 메뉴 표시

• 너비 높이 조절
  너비 높이 경계선 이용, 셀-서식-셀크기에서 설정
  너비 높이 자동 맞춤 가능

• 숨기기 특징
  행, 열 : 숨긴 곳 보다 더 넓게 영역 지정 후 숨기기 취소 해야함

• 복사
  클립보드 : 복사할 때 사용되는 임시 저장소(휘발성), 24개까지 저장됨 / 가장 마지막에 복사한 데이터가 위에 저장
  선택하여 붙여넣기 ctrl+alt+v
  잘라내기 - 선택하여 붙여넣기 할 수 없음

저장과 홈 리본메뉴

• 저장 : 지원하는 파일 형식(확장자)
  통합문서(xlsx, xls)
  서식문서(xltx,xlt)
  매크로 통합문서(xlsm)
  매크로 서식문서(xltm)
  바이너리 통합 문서 (xlsb)

• 저장 일반 옵션 (다른이름으로저장-도구-일반옵션)
  백업 파일 항상 만들기(문서를 저장할 때 백업 복사본을 저장)(XLK파일 생성)
  열기 암호(암호를 모르면 파일 열 수 없음)-대소문자구별
  쓰기 암호(암호 몰라도 파일 열 수 있지만 같은 이름으로 파일 저장x)
  읽기 전용 권장 : 파일 열 때 읽기 전용으로 불러올 수 있는 경고창 뜸

홈 리본메뉴

• 윗주 (위에 있는 주석)
  윗주 입력 후 '윗주 필드 표시'를 해야 표시됨
  문자열 데이터에만 표시 가능 (빈셀에 적용 x)
• 맞춤
  가로, 세로, 들여쓰기 등을 설정
  방향을 통해 텍스트 회전 가능
  텍스트 줄바꿈, 셀에 맞춤, 셀 병합 기능 지원
  셀 병합 시 왼쪽 위에 있는 데이터만 남음
• 셀 스타일
  글자 모양 및 서식 등을 정해 놓고 한 번에 적용할 수 있는 기능
  스타일을 변경하면 스타일이 적용된 서식을 한꺼번에 변경 가능
• 찾기 바꾸기 ctrl+F
  반대 방향으로 검색 : shift
  와일드카드(만능문자)를 이용한 검색 의미 : * (모든 문자열 대신한 기호) ?(한글자를 대신한검색신호)
  만능 문자 자체를 검색할 경우 ~ 기호를 만능문자 앞에 사용

표시 형식

셀 서식 ctrl+1 : 기본 표시형식

사용자 지정 - 숫자 표시 형식

• G/표준 : 숫자 그대로를 표현
  입력 : 100
  형식 : G/표준"개"
  출력 : 100개
• '#' : 숫자가 없을 때 표현 안함
  입력 : 12.45
  형식 : ###.###
  출력 : 12.45
• 0 : 숫자가 없을 때 표현함
  입력 : 12.45
  형식 : 000.000
  출력 : 012.450
• ? : 숫자가 없을 때 공백
  입력 : 12.45
  형식 : ???.???
  출력 : 12.45
• % : 백분율
  입력 : 12.45
  형식 : #%
  출력 : 1245%
• , : 쉼표 스타일, 천 단위 배수로 표시
  입력 1234567
  형식 : #,##0,
  출력 : 1,235

사용자 지정 - 문자 표시 형식

• @ : 문자 표시
  입력 : 서울
  형식 : @랜드@
  출력 : 서울랜드서울

사용자 지정 - 날짜, 시간 표시 형식

• Y : 년
  입력 : 2017-11-05
  YY : 17
  YYYY : 2017
• M : 월
  M : 11
  MM : 11
  MMM : Nov
  MMMM : November
• D : 일
  D : 5
  DD : 05
  DDD : Sun
  DDDD : Sunday
• A : 한글 요일
  AAA : 일
  AAAA : 일요일
• H : 시
  입력 : 13:03
  H : 13
  HH : 13
• M : 분
  M : 3
  MM : 03
• S : 초
  S : 0
  SS : 00
• AM/PM : 오전, 오후
  AM/PM : PM
  A/P : P

기타 표시 형식의 규칙

• 대괄호 ( [] )를 이용하여 조건과 글꼴 색 표현 가능
• 조건을 지정하지 않으면 양수;음수;0;문자 순으로 지정됨
• 양수;음수;0;문자 형태에서 음수기호는 사라짐
• 자료를 숨기고자 할 때 = ;;;(세미콜론 3번)
• 곱하기 ( * )를 이용하여 특정 기호를 셀의 너비만큼 반복 가능

데이터 관리 - 표(테이블)의 기본 구조

필드와 레코드(row,행)로 이루어져있음

• 데이터 : 중복된 항목 제거
  필드 및 열을 기준으로 중복되는 레코드를 삭제하는 기능
  중복 레코드의 첫 번째를 제외한 나머지 레코드가 삭제됨

• 외부데이터 가져오기 기능을 이용하여 텍스트 파일을 불러오는 경우 데이터의 구분 기호로 탭, 세미콜론, 쉼표, 공백 등이 기본으로 제공되며 사용자가 원하는 구분 기호를 설정할 수도 있다.
  가져온 데이터는 원본 텍스트 파일이 수정되어도 반영되지 않음
  텍스트 파일에서 특정 열만 선택하여 가져올 수 있음
  기본적으로 사용되는 텍스트 파일 형식은 .txt, prn, csv 파일

• 텍스트 마법사

1. 구분기호로 분리됨 - 구분 기호 선택 - 각 열의 서식 지정
2. 너비가 일정함 - 열 구분선 선택 - 각 열의 서식 설정

채우기 핸들

• 채우기 핸들 기본 특징
  숫자 : 증가값, 감소값 만큼 채워짐
  문자 : 반복
  숫자+문자 : 오른쪽 숫자만 1증가
  날짜 : 증가값, 감소값 만큼 채워짐 (단일셀 : 1증가)

• ctrl 키를 활용한 채우기 핸들
  숫자 : ctrl+단일셀 : 1증가 / 2개셀 : 반복
  숫자+문자 : ctrl : 반복
  날짜 : ctrl : 반복

• 기타 채우기 핸들 특징
  채우기 옵션을 이용해 복사, 연속, 서식 등을 채울 수 있음
  날짜는 일, 평일, 월, 연 단위 채우기 가능
  ctrl+D(down) : 아랫 방향으로 복사
  ctrl+R(Right) : 우측 방향으로 복사
  빠른채우기(ctrl+E) : 패턴을 감지하여 채우기 (2013이상지원)

참조

참조의 기본

• 상대 참조 = A1 : 채우기 핸들 및 복사 = 셀주소 변함
• 절대 참조 = $A$1 : 채우기 핸들 및 복사 = 셀주소 고정 (->f4키로 조정)
• 혼합 참조
  A$1 (행고정) / $A1 (열고정) : 채우기 핸들 및 복사 $기호 뒤 고정

기타 참조 규칙

• 다른 파일, 다른 시트 참조 시 대괄호와 느낌표로 구분 =[파일명]시트명!A1
• 파일, 시트에 공백이나 특수 기호가 있으면 ' 표시 ='[파일명]시트명'!A1
• 시트 이름의 연속성이 있는 경우 3차원 참조 가능
  =SUM(Sheet1:Sheet3!A1) : Sheet1부터 Sheet3의 A1셀 더하기
• 셀 범위와 셀 범위 사이에 공백을 넣으면 교점이 선택됨

필터

데이터 - 고급 필터

• 자료를 조건에 맞게 필터하는 기능
• 현재 위치 또는 다른 장소에 복사 가능
• 동일한 레코드는 하나만 추출할 수 있음
• 숨겨진 셀까지 필터링
• 숫자 필터 : 기호를 먼저 씀
• 와일드카드 필터
  A✽ (A로 시작)
  ✽A (A로 끝)
  ✽A✽ (A 포함)
  ?A (A로 끝나는 2글자)
  A?? (A로 시작하는 3글자)
  ~✽ (✽자체를 검색)
  <>4 (4가 아님)
  <>✽A✽ (A를 포함하지 않음)
• AND (그리고, 이면서, 둘 다 등) : 필드1 필드2 / 조건1 조건2
• OR (또는, 이거나) : 필드1 필드2 / 조건1 / 조건2

수식을 이용한 고급필터

• 함수나 수식을 이용해 조건을 지정하여 필터하는 방식
• 가상의 필드를 이용해 조건이 참인 값 필터
• 함수나 수식을 이용해 조건을 작성할 때 필드는 원본 필드와 반드시 다른 이름을 사용해야 함

데이터 - 자동 필터

• 두 개의 필드에 조건을 지정하면 OR 안됨
• 색 기준 필터 가능
• 텍스트, 숫자, 날짜 등으로 데이터가 혼합된 경우 가장 많은 형식으로 필터 적용

조건부 서식과 기타 기능

홈 - 조건부 서식

• 조건을 만족하면 지정된 서식을 적용하는 기능
• 중복 또는 고유, 상 하위 값, 평균 기준 등으로 조건 가능
• 데이터 막대, 색조, 아이콘 집합 스타일 표현 가능
• 여러 조건부 서식이 충돌하면 우선 순위가 높은 것을 적용

수식을 이용한 조건부 서식

• 표의 구조에 따라 사용해야 할 혼합참조의 형식
  $A1 형식 - 상단에 필드 하단에 레코드 (열)
  A$1 형식 - 좌측에 필드 우측에 레코드 (행)

데이터 유효성 검사

• 입력된 데이터가 유효한지 검사하는 기능
• 값에 제한을 주는 기능
• 설정 : 제한 대상을 지정함
• 설명 메시지 : 자료를 입력할 때 보이는 설명을 설정함
• 오류 메시지 : 유효하지 않은 데이터를 입력 시 메시지를 설정
• IME 모드 : 자료 입력 형태(한글, 영어)를 설정함

메모 - Shift+f2

• 셀에 대한 설명을 입력할 수 있는 기능
• 메모 삭제 및 완전 삭제(모두 지우기)로 제거 가능

삽입 - 하이퍼링크

• 클릭하면 특정 위치 또는 개체로 연결하는 기능
• 단추에는 하이퍼링크를 지정할 수 없음

가상분석 - 분석작업

목표값 찾기

• 결과 값은 알지만 결과가 목표하는 값을 모를 때 사용
• 수식 셀, 찾는 값, 값을 바꿀 셀 순서대로 넣어서 사용
• 하나의 변수 입력 값만 사용

시나리오

• 자료가 변함에 따라 그 결과를 예측하는 기능
• 변경셀은 최대 32개까지 가능
• 결과셀은 반드시 변경셀 값을 참조한 수식으로 되어있어야 함
• 요약을 통해 요약 또는 피벗테이블로 만들 수 있음
• 시나리오 요약 보고서 및 피벗테이블은 자동으로 값이 갱신되지 않음
• 시나리오 피벗 테이블의 결과 셀은 반드시 있어야 함

데이터 표

• 1, 2개의 값을 변화시켜 변화 결과를 표 형태로 볼 수 있는 기능
• 테이블 배열 함수로 데이터 표 완성
• 결과 중 일부분을 변경하거나 삭제할 수 없음

분석 작업

데이터 - 정렬

• 오름차순, 내림차순 (ASC, DESC), 사용자 지정 목록 순으로 정렬 가능
• 값, 글꼴 색, 셀 색, 셀 아이콘(조건부서식 기능) 정렬 가능
• 기준은 64개까지 추가 가능
• 대,소문자 구분하여 정렬 가능
• 왼쪽에서 오른쪽으로 정렬 가능(기본값 위, 아래)
• 병합된 칸, 숨겨진 행과 같은 열은 정렬 안됨
• 오름차순 정렬 기준(작은 값부터)
  숫자 - 기호 - 영문자 - 한글 - 논리 - null/오류 - 빈 칸(항상 마지막 내림차순 정렬 시에도)

데이터 - 부분합

• 자료들을 그룹별로 분류하고 그룹별 계산을 수행하는 기능
• 부분합 전 반드시 정렬이 되어있어야 함
• 새로운 값으로 대치 로 기존 부분합을 새로운 값을 대치
• 그룹 사이에서 피이지 나누기 로 인쇄 시 그룹 별 인쇄 가능
• 데이터 아래에 요약표시 로 및 위에 표시 가능
• 합계, 개수, 평균, 최대/최소값, 곱, 편차, 분산 등이 한 번에 하나씩 계산
• 부분합 후 윤각 기호를 이용하여 하위 목록의 데이터를 표시 또는 감출 수 있음(8수준까지 가능, 1수준 = 완전 감춤)
• 모두 제거 단추로 부분합 제거 가능

데이터 - 통합

• 분산되어 있는 자료를 통합해주는 도구(다른시트, 파일 가능)
• 레이블(제목)을 통해 데이터를 통합할 수 있음
• 일치하지 않는 레이블이 있는 경우 추가로 레이블이 생성되며 계산됨
• 만능문자(와일드카드) 사용 가능
• 같은 시트에 있는 자료는 원본 데이터 연결을 할 수 없음

피벗 테이블

삽입

• 많은 양의 자료를 효율적으로 요약하여 나타내는 기능
• 필터, 열, 행, 값으로 구성
• 값 필드를 합계, 개수, 평균, 최대/최소값, 곱, 편차, 분산 등으로 계산 가능
• 값 영역에 추가된 필드가 2개 이상이면 시그마가 추가됨

옵션과 추가 기능

• [옵션]을 이용하여 오류값, 빈셀, 행/열 총합계 설정 가능
• 그룹을 이용해 자료를 그룹화 할 수 있음
• 사용자가 수식 추가를 통해 값 필드를 만들 수 있음
• 값 영역의 특정 항목을 더블 클릭하면 해당 데이터에 대한 세부적인 데이터가 새로운 시트에 표시됨
• 원본 데이터가 변경되면 새로고침을 눌러야만 갱신됨 (모두 새로고침 기능 이용해서 전부 적용)

피벗 차트

• 피벗 테이블이 존재해야 피벗 차트 생성 가능
• 피벗차트가 만들어진 후 피벗테이블을 지우면 피벗차트는 일반 차트로 변함
• 분산형, 거품형, 주식형 차트로 만들 수 없음
• 피벗 차트 보고서에 필터를 적용하면 피벗 테이블 보고서에 자동 적용됨
• 처음 피벗테이블 보고서를 만들 때 자동으로 피벗차트 보고서를 함께 만들 수 있고 기존 피벗테이블 보고서에서 피벗차트 보고서를 만들 수도 있음
• 피벗차트 보고서를 삭제해도 관련된 피벗테이블 보고서는 삭제되지 않음

차트

삽입 (alt+f1 현재시트에 생성 / f11 새 시트에 생성)

• 차트 영역 : 차트 전체 영역
• 세로, 값 축 / 보조 세로 값축
• 가로, 항목 축
• 그림 영역
• 데이터 계열
• 범례
• 데이터 레이블
• 오차 막대
• 데이터 표 (범례 표지 포함)
• 차트 제목
• 계열 겹치기(양수면 겹쳐지고 음수면 떨어짐)
• 간격 너비 (0이면 간격이 사라짐 숫자가 클 수록 떨어짐)

차트 종류

• 세로막대형 : 항목들을 비교하여 사용 (가로축이 항목축, 세로축이 값축)
• 꺾은선형 : 일정 기간의 추세를 나타낼 때 사용
• 원형 : 비율로 표시, 하나의 계열만 사용 가능
• 가로막대형 : 세로차트와 항목축, 값 축의 방향이 다름
• 영역형 : 꺾은선형과 같은 용도로 사용(영역이 채워짐)
• 도넛형 : 원형을 개선하여 여러 개의 계열 표현 가능
• 분산형 : 과학 데이터의 숫자 그룹을 xy좌표로 표현
• 거품형 : 세 값의 집합을 비교할 때 사용
• 주식형 : 주식의 고가, 저가, 종가 데이터를 표현 (세 값이 없으면 차트 생성 불가)
• 표면형 : 두 데이터 집합에서 최적의 조합을 찾을 때 유용
• 방사형 : 중간 지점에서 뻗어나가는 축이 있음
• 추세선 : 계열의 추세를 나타낼 때 사용 (3차원차트, 원형, 도넛형, 방사형, 표면형에는 표현되지 않음)