• 자료실

[컴퓨터활용능력] 필기 요약자료 - 2과목 스프레드시트

등록자 : 이애본 등록일 : 2015-09-30 | 조회 2176
  • 글꼴
  • 확대
  • 축소

2과목 스프레드시트

 

1. 입력 및 편집 18.75%
▶워크시트 기본 지식
  - 엑셀2007 사용법, 종료(Alt+F4), 워크시트는 기본은 3개다(추가가능)
▶데이터 입력
  - 여러줄 입력은 Alt+엔터, 셀선택 후 동시 입력은 Ctrl+엔터
  - 데이터의 종류(문자, 숫자, 날짜, 시간, 수식(Ctrl + ~ 수식보기),
                  한자, 특수문자, 메모입력, 윗주)
  - 카메라기능, 그림복사(영역지정하고 Shift 누르고 그림복사 Ver.2003)
▶데이터 편집
  - 데이터수정(F2),데이터찾기/바꾸기(Ctrl+F/Ctrl+H)
▶Office단추, Excel옵션
  - 메뉴에 개발도구 추가하기, 사용자지정 빠른실행 메뉴 추가/삭제 가능
▶셀 편집
  - 셀을 추가할때 열과 행 모두 각각 선택한 셀 앞쪽에...(열은 왼쪽, 행은 위쪽)
  - 복사하고 붙여넣기할때... 선택하여 붙여넣기
  - 유효성 검사 : 데이터를 정확하게 입력할 수 있도록 도와주는 것 
▶통합 문서
  - 읽어올 수 있는 파일 확장자 : Excel파일(*.xl*(2003이하), *.xlsx, *.xlsm)
                                웹페이지, XML파일, 엑세스, 데이터베이스,
                                쿼리파일, 등....
  - 암호는 최대 255자까지 가능(문자, 숫자, 기호, 대소문자)
  - 서식파일(*.xltx)
  - 서식파일로 저장할 수 있는 요소
    [홈]탭의 명령으로 설정할 수 있는 셀과 시트 서식, 시트별 페이지 서식과 인쇄 영역 설정, 셀 스타일, 통합문서의 시트 개수와 종류, 통합 문서에서 보호되거나 숨긴 영역, 반복되는 문자열, 데이터, 그래픽, 수식, 새로운 통합 문서나 워크시트마다 반복하여 나타낼 문자열(페이지 머리글이나 행과 열 레이블), 새 통합 문서나 워크시트에 각각 나타낼 데이터, 그래픽, 수식, 차트, 기타 정보, 데이터 유효성 설정 등
  - 통합 문서 공유 (공유하는 방법, 특징, 지정 방식)
▶셀서식 - 표시형식 / 사용자지정
  - 셀서식 Ctrl+1
  - 사용자 지정 표시 형식 : #,##0;[빨강](#,##0);0.00;@"님"
                            양수   색지정 음수  0값  텍스트
  - 날짜표시형식 mm,mmm,mmmm,dd,ddd,dddd
▶조건부 서식
  - 조건부 서식은 규칙(조건)을 만족하는 셀에만 셀 서식을 적용하는 기능
  - 설정 방법, 특징 기억하기
  - 조건을 만족하는 데이터가 있는 행 전체에 서식을 지정할 때는
    열 이름 앞에 '$'를 붙인다는 것

 

2. 수식 활용 20.98%
▶수식작성
  셀에 데이터, 주소값, 함수 -> 수식
  수식표현 Ctrl + ~, 상수입력은 수식을 입력한 후 F9
▶셀 참조 / 이름정의
  (영역 지정 이름 부여 - 수식 계산에서 참조 영역으로 활용)
  재계산을 수동으로 하는 방법 : Office -> Excel옵션 -> 수식 탭에서 수동에 체크
  [F4]상대/절대참조 : 1번 $A$1(절대), 2번 A$1(열), 3번 $A1(행), 4번 A1(상대)
  다른 워크시트를 참조하는 방법 =컴퓨터활용!A1 (시트이름에 공백이 없는 경우)
                               ='컴퓨터 학원'!B3(시트이름에 공백이 있는 경우)
                               =Sheet1!A1 (시트에 이름이 없는 경우)
  오류메시지 : #### 셀의 너비 오류
               #DIV/0! 빈 셀이나 0으로 나누기 한 경우
               #N/A 함수나 수식에 사용할 수 없는 값을 지정했을 때
               #NAME? 인식할 수 없는 텍스트를 수식에 사용했을 때
               #NULL! 교차하지 않는 두 영역의 교점을 지정했을 때
               #NUM! 표현할 수 있는 숫자의 범위를 벗어 났을 때
               #REF! 셀 참조가 유효하지 않을 때
               #VALUE! 잘못된 인수나 피 연산자를 사용할 때,
                       수식 자동 고침 기능으로 수식을 고칠 수 없을 때

-----함수 문제는 기출문제 풀이할 때 직접 실습해서 결과 확인 할 것! -----

▶통계함수, 수학/삼각함수
▶텍스트 함수, 날짜/시간함수, 논리함수
▶찾기/참조참수, 데이터베이스함수

▶재무함수,정보함수 (재무,정보함수 문제는 간헐적으로 1문제 정도 출제 됨)

-----함수 문제는 기출문제 풀이할 때 직접 실습해서 결과 확인 할 것! -----

▶배열함수(배열 수식의 개념, 배열 수식에 대한 계산 결과 묻는 문제 출제)
  배열 수식은 피연산자나 함수의 인수로 배열을 사용하여 여러 가지 계산을 하고
  한개의 결과나 여러 개의 결과를 동시에 반환하는 수식을 말한다.
 
  배열 수식의 개념(★)
  - 배열(Array)이란
    동일한 특성을 갖는 데이터들을 일정한 형식에 맞게 나열한 데이터의 집합
  - 배열 수식은 하나 이상의 값 집합에 대해
    여러가지 계산을 수행하고 하나 또는 여러개의 결과를 반환하는 수식이다.
  - 배열 수식은 배열 인수라는 두 개 이상의 값에 의해 이루어진다.
  - 배열 수식에 사용되는 배열 인수 각각은 동일한 개수의 행과 열을 가져야 한다.
  - 배열 수식은 수식을 입력할 때 [Ctrl + Shift + Enter]를 누르는 것 외에는
    다른 수식을 만들 때와 같다.
  - Ctrl+Shift+Enter를 누르면 수식의 앞뒤에 중괄호({ })가 자동으로 입력된다.

3. 차트 작성 8.63%
▶차트는 워크시트의 데이터를 막대나 선, 도형, 그림 등을 사용하여 시각적으로 표현하는 것(각 구성 요소 구분)
  - 차트는 2차원과 3차원으로 구분, 차트만 별도 표시 가능(차트시트)
  - 기본차트는 묶은 세로 막대형 차트, 원본 데이터가 바뀌면 차트의 모양도 변경
  - 차트에 사용될 데이터를 범위로 지정한 후
    F11을 누르면 별도의 차트 시트에 기본차트가 작성되고,
    Alt+F1을 누르면 데이터가 있는 워크시트에 기본 차트가 작성된다.
▶차트의 종류 (거품형, 원형, 꺾은선형의 차트 특징 기억할 것)
  - 세로 막대형(항목간 길이 비교, 항목은 수평, 값은 수직),
    가로 막대형(항목간 길이 비교, 항목은 수직, 값은 수평),
    꺾은선형(일정 기간 동안의 데이터 변화 추세 확인),
    원형(항상 계열이 하나, 축이 없다.차트의 각 조각 분리, 첫 조각은 각 조절),
    분산형(XY좌표로 이루어진 한 계열을 두개의 숫자 그룹으로 표현),
    영역형(시간에 딸느 각 값을 변화량 비교),
    도넛형(각 부분의 관계를 비율로 나타내어 비교할 때,
           원형 차트와 달리 여러개의 데이터 계열을 갖는다),
    방사형(많은 데이터 계열의 집합적인 값을 나타낼 때),
    표면형(두 개의 데이터 집합에서 최적의 조합을 찾을 때),
    거품형(계열 간의 항 목 비교,
           분산형 차트의 한 종류로 데이터 계열 값이 3개인 경우에 사용),
    주식형(주식의 거래량과 같은 주가의 흐름 파악,
           거래량, 시가, 고가, 저가, 종가 등 5개의 계열이 필요하다.),
    원통형, 원뿔형, 피라미드형(3차원 세로/가로 막대형 차트에 시각적 효과),
    특수차트(이중 축(하나의 값 축을 추가하여 이중으로 값 표시), 혼합형)


4. 출력 9.52%
▶틀고정 : 데이터의 양이 많은 경우 특정한 범위의 열 또는 행을 고정시켜 셀 포인터의 이동과 상관없이 화면에 항상 표시할 경우 (왼쪽과 위쪽)
▶창나누기 : 데이터의 양이 많아 필요한 데이터를 한 화면으로 보기 어려운 경우, 찬 나누기를 이용하면 서로 떨어져 있는 데이터를 한 화면에 표시할 수 있다.
▶페이지 설정
  - 페이지 : 용지방향, 배율, 용지 크기, 인쇄 품질, 시작 페이지 번호
  - 여백
  - 머리글/바닥글 : 문서 제목, 페이지 번호, 사용자 이름, 작성 날짜 등 고정출력
  - 시트 : 인쇄 영역, 인쇄 제목, 눈금선, 흑백으로, 간단하게 인쇄, 행/열 머리글
           메모, 셀 오류 표시, 페이지 순서
▶페이지 나누기 : 자동 페이지 나누기, 수동 페이지 나누기, 나누어 미리 보기
▶인쇄 미리보기 : Office단추 -> 인쇄 -> 인쇄 미리 보기

5. 데이터 관리 9.97%
▶레코드 관리 : 데이터를 추가, 삭제, 수정, 검색하는 등의 데이터베이스 관리 기능
▶레코드 찾기 조건 : <> NOT / * 모든문자 / ? 한문자
  "김"씨가 아닌 레코드를 찾는 조건 입력으로 올바른 것?  <>김*
▶정렬의 특징
  - 정렬 기준은 최대 64개까지 지정할 수 있으며, 기본적으로 위에서 아래로 행단위 정렬
  - 정렬 방식은 오름차순과 내림차순, 사용자 지정 목록이 있으며, 셀 값에 따라 정렬
  - 숨겨진 행이나 열에 있는 데이터도 정렬에 포함된다.
  - 영문자 대/소문자도 구분하여 정렬 가능, 오름차순으로 정렬하면 소문자가 우선순위
  - 오름차순은 숫자->문자->논리값->오류값->빈셀
    내림차순은 오류값->논리값->문자->숫자->빈셀
  - 오름차순 정렬 순서(대/소문자 구분 설정) [데이터]->[정렬 및 필터]->[정렬] 클릭 설정
▶필터(Filter)는 데이터 목록에서 설정된 조건에 맞는 데이터만을 추출하여 화면에 나타내는 기능이다. (자동필터, 고급필터)
  - 고급필터:기본 조건 지정(AND:같은 줄, OR:다른 줄, AND와 OR 같이 사용하는 조건)
▶가져오기 기능 : 외부 데이터베이스 이용
  - 엑셀의 외부 데이터 가져오기 기능에서 외부로부터 가져올 수 있는 형태가 아닌것?
  (가져오기가 가능한 파일은 데이터베이스(쿼리)파일과 텍스트 파일이다.) Java Script

6. 데이터 분석 10.27%
   (부분합, 피벗 테이블, 시나리오, 목표값 찾기, 해 찾기, 데이터 표, 데이터 통합)
▶부분합의 특징
  - 부분합을 작성하려면 첫 행에는 열 이름표가 있어야 하며, 기준이 되는 필드를 기준으로     반드시 오름차순이나 내림차순으로 정렬해야 한다.
  - SUBTOTAL 함수를 사용하여 합계나 평균 등의 요약 함수를 계산한다.
    (목록이나 데이터베이스에서 부분합을 구하는 함수)
  - 같은 열에 있는 자료에 대하여 여러 개의 함수를 중복 사용하여
    다중 함수 부분합을 작성할 수 있다.
  - 부분합을 제거하며 부분합과 함께 표에 삽입된 윤곽 및 페이지 나누기도 모두 제거된다.
  - 부분합에서 사용할 수 있는 함수는 합계, 개수, 평균, 최대값, 최소값, 곱, 숫자 개수,
    표준 편차, 표본 표준 편차, 표본 분산, 분산이 있다.
  - 제일 큰 값을 계산한 윤곽선의 번호는 1이다.
▶피벗 테이블 / 피벗 차트 보고서의 특징
  - 완성된 피벗 테이블에 제목이 있다? - 피벗 테이블은 제목을 따로 설정하지 않는다.
    데이터 범위는 순수하게 피벗 테이블로 작성될 데이터 영역만을 지정한다.
  - 피벗 테이블의 값 영역에 있는 데이터 수정을 불가하다.
  - 피벗 테이블에서 피벗 차트를 만들고 피벗 테이블 삭제하면??? 일반 차트로 변경된다.
▶시나리오의 개념(시나리오는 다양한 상황과 변수에 따른 여러 가지 결과값의 변화를 가상의 상황을 통해 예측하여 분석하는 도구로 이자율, 손익 분기점, 주가 분석 등에 많이 사용된다.)
  - 시나리오 요약 보고서를 만들 때는 결과 셀을 지정하지 않아도 되지만,
    시나리오 피벗 테이블 보고서를 만들 때에는 결과 셀을 반드시 지정해야 한다.
    이때 결과 셀은 반드시 변경 셀을 참조하는 수식으로 입력되어야 한다.
  - 시나리오의 결과는 요약 보고서나 피벗 테이블 보고서로 작성할 수 있다.
  - 시나리오 보고서는 현재 작업하는 시트 앞에 새 워크시트를 삽입하여 그곳에 표시한다.
  - 시나리오 병합을 통하여
    다른 통합 문서나 다른 워크시트에 저장된 시나리오를 가져올 수 있다. 
  - 여러 시나리오를 서로 비교하기 위해 시나리오를 피벗 테이블로 요약 할 수 있다.
  - 실행 : [데이터]->[데이터 도구]->[가상 분석]->[시나리오 관리자]를 이용한다.
▶목표값 찾기(수식에서 원하는 결과(목표)값은 알고 있지만 그 결과값을 계산하기 위해 필요한 입력값을 모를 경우에 사용하는 도구)
  - 목표값 찾기는 주어진 결과값에 대해 하나의 입력값만 변경할 수 있다.
  - 결과값은 입력값을 참조하는 수식으로 작성되어야 한다.
  - 목표값은 사용자가 원하는 데이터를 직접 입력해야 한다.
▶데이터 표(특정 값의 변화에 따른 결과값의 변화 과정을 표의 형태로 표시해 주는 도구)
  - 데이터 표는 지정한 특정 값의 수에 따라 단일 표와 이중 표로 구분된다.
  - 데이터 표의 결과값은 반드시 변화하하는 특정 값을 포함한 수식으로 작성되어야 한다.
  - 데이터 표의 결과값 중 일부분만 수정할 수 없다.
▶데이터 통합(비슷한 형식의 여러 데이터를 하나의 표로 통합/요약하여 표시해 주는 도구)
  - 사용할 데이터의 형태가 다르더라도 같은 이름표를 사용하면 항목을 기준으로 통합 가능
  - 사용할 수 있는 함수는 합계, 개수, 평균, 최대값, 최소값, 곱, 숫자 개수,
    표본 표준 편차, 표준 편차, 표본 분산, 분산이 있다.
  - 통합할 여러 데이터의 순서와 위치가 동일할 경우 위치를 기준으로 통합할 수 있다.
  - 다른 워크시트나 통합 문서의 데이터를 사용할 수 있다.

7. 매크로 작성과 VBA 프로그래밍 21.88%
▶매크로 생성
  - 매크로 이름 지정 : 임의 지정 가능, 이름 지정 시 첫 글자는 반드시 문자,
                      숫자, 밑줄문자(_) 사용 가능.
    (특수문자, /, ?, ', -, ※ 등과 같은 문자와 공백은 매크로 이름으로 사용 불가)
  - 바로 가기 키 지정하기 : Ctrl + 영문자의 소문자, Ctrl + Shift + 영문자의 대문자
  - 매크로 저장 위치
    개인용 매크로 통합 문서는 PERSONAL.XLSB 라고 하는 특수한 엑셀 통합 문서로 저장
    이 문서 안에 저장된 매크로는 워크시트를 열지 않은 상태에서도 실행 가능
    XLSTART 폴더에 있는 모든 문서를 한꺼번에 열어 사용하기에 이 파일도 여기에 저장된다
  - 매크로 중지는 ESC를 누른다.
▶매크로 실행(ALT+F8)
  - 매크로 이름을 클릭하거나, 단축키를 사용하거나, 버튼을 사용
▶VBA 기본 개념
  - VBE(Visual Basic Editor) : Alt + F11 [F5-매크로 실행]
  - 프로그램밍의 기본 : 모듈, 프로시저, 개체, 속성, 메서드, 이벤트, 컬렉션
    모듈 : 프로시저의 집합이며, 프로젝트를 구성하는 단위
    프로시저 : 특정 기능을 실행할 수 있도록 나열된 명령문의 집합
▶VBA 문법 - 상수, 변수, 배열
  - 상수 : 프로그램 처리 과정에서 변함이 없는 항상 같은 값
  - 변수 : 컴퓨터가 명령을 처리하는 도중 발생하는 값을 저장하기 위한 공간
           변수는 사용범위 내에서 유일하게 구별될 수 있는 이름으로 지정
           변수 이름의 첫글자는 반드시 영문자나 한글,영문자,숫자,밑줄 혼합 사용 가능
           변수이름의 길이는 255자까지. 예약어(키워드) 사용 불가
           VBA에서는 변수를 선언하지 않아도 사용 가능
           (선언하고 사용하려면 모듈의 맨 위에 Option Explicit 선언)
  - 배열 : 동일한 데이터 유형을 여러 개 사용하는 경우,
           모든 데이터를 하나의 변수 이름으로 정의해 사용하는 것을 말한다. 
▶VBA 문법 - 제어문
  - if ~ Then
  - Select Case ~ End Select
  - For ~ Next
  - Do ~ Loop
  - Do Until ~ Loop
  - While ~ Wend
  - For Each ~ Next
  - Call
  - 에러 처리
  - With ~ End With
  - 주석
▶VBA 문법 - 입/출력문
  - MsgBox  메시지 박스 / 단순 메시지 전달
  - InputBox  사용자가 내용을 입력하고 단추를 누르면
              입력란의 내용을 포함하는 문자열을 반환
▶엑셀 개체의 이용
  - Application 개체 : 엑셀의 모든 개체 중 최 상위 개체, 바로 엑셀 자체
  - Workbook(Workbooks) 개체 : 통합 문서
  - Worksheet(Worksheets) 개체 : 워크시트자체
  - Range 개체 : 셀, 행, 열, 연속 셀 블록이 하나 이상 들어 있는 셀 선택 영역
  - Chart(Charts) 개체 : 차트나 차트 시트
  - Window(Windows) 개체 : 엑셀의 Window 개체 모두
  - UserForm 개체 : 응용프로그램에서 사용자인터페이스의 일부분을 구성하는 창,대화상자
▶사용자 정의 폼 / 컨트롤
  - 컨트롤의 주요 속성(디자인 모드)
    그림/글꼴/기타 속성, 동작 속성, 모양/위치 속성, 테이터/스크롤 속성
  - 컨트롤의 주소 속성(실행모드)
  - 컨트롤의 주요 메서드
  - 컨트롤의 주요 이벤트
▶개체 활용(★)
  함수에 대해서 이해를 한다면, 쉽게 풀이할 수 있는 범위
  - Rank() 함수는 등수를 구한다.
  - 선택 영역의 바탕색 변경
  - 수식 입력 후 자동 채우기로 수식 복사하기
  - 선택 영역의 글꼴 스타일과 바탕색 변경하기
  - 고급 필터 적용하기
  - 데이터 정렬하기
  - SUM() 함수 입력 후 수식 복사하기
  - 자동 서식 지정하기
  - 메모 삽입하기
  - 부분합 수행하기
  - 데이터 통합 수행하기
  - 목표값 찾기