컴활1급 필기 메모 : 2과목 스프레드시트 메모

필요한 내용은 Ctrl + F (페이지 내에서 찾기) 로 키워드 검색하여 찾아주세요

저작권에 위배될만한 이미지는 모두 삭제해서 중간중간 빠진 내용이 많습니다.

 

컴활1급 필기 메모 : 2과목 스프레드시트

 

 

엑셀 기초

 

엑셀 열의 끝은 2^14

행의 끝은 2^20

 

A1으로 돌아오려면 Ctrl + Home

 

Crtl+방향키 : 엑셀 셀의 끝단으로 이동

Ctrl + End : 데이터범위의 맨오른쪽 아래 셀로 이동

Enter / Enter+Shift : 상하이동 //Shift가 되돌리기 기능

Tab / Tab +Shift : 좌우이동

Alt + PageUp / PageDown : PageUpDown만 쓰면 보이는 화면 상하이동, alt와 함께쓰면 좌우이동

Crll+PageUp/PageDown : 시트이동

F5 :이동하고자하는 셀주소 직접입력

 

셀 안에서 줄바꿈 : Alt+Enter

여러 셀에 동일한 내용 입력 : 범위지정 후  데이터 입력 후 Ctrl + Enter

 

한 행이나 열 전체 선택 : 머리글 클릭하거나 / 행 전체 선택 :  Shift+spacebar / 열 전체 선택 : Ctrl+spacebar

 

워크시트 전체 지정 : Ctrl + Shift + SpaceBar

 

 

숫자데이터 문자데이터 형식으로 입력하기 : 숫자데이터 앞에 작은따옴표 입력

###나 지수형으로 표시되면 열의 너비 늘려주면 해결된다

 

분수 입력 : 0을 입력하고 한칸 띄운다음 1/2 입력

 

날짜, 시간 데이터 : / or - r 입력, 시간은 콜론으로 구분

Ctrl + ;   :  오늘 날짜 자동 표기

Ctrl + Shift + ;   :  현재 시각 자동 표기

 

기호 입력 : 자음 - 한자 - Tab  // 삽입 - 텍스트 - 기호 명령

 

 

셀채우기

 

홈 : 지우기 / 

 

채우기

 

셀 하나 그냥 드래그 : 복붙

셀 하나 드래그 + Ctrl : 1씩 증가

셀 두개 드래그 : 규칙 증가

셀 두개 드래그 + Ctrl : 복붙

 

기간설정은 끝자리(날짜)만 바뀌는데 시간은 분말고 시간이 바뀜

 

 

파일 - 옵션 - 고급 - 사용자 지정 목록 편집 에서 채우기 핸들러에 채우기 목록 등록 가능

 

 

계열 채우기 : 홈 - 편집 - 채우기 - 계열 (채우기 방향, 단계 등 설정하여 채우기 가능)

 

검토 - 새메모

메모는 시트에 표시된대로 인쇄하거나 시트끝에 모아서 인쇄 가능

페이지 레이아웃 - 페이지 설정 - 그룹의 옵션 - 시트 - 메모에서 설정

 

매크로 설정은 파일 - 옵션 - 보안센터 - 매크로설정

 

 

 

워크 시트

 

Ctrl은 복사기능에 주로 쓰임

 

데이터 이동 : 영역 지정하여 경계부분 클릭하여 드래그

 

홈 - 클립보드 - 붙여넣기 - 선택하여 붙여넣기 (설정한 부분만 붙여넣기, 테두리 제외 가능)

 

시트 이름바꾸기는 셀 그룹 서식에서 할 수 있음 // 시트 더블클릭

 

 

Shift+F11 : 워크시트 삽입 단축키

 

 

기본 확장자 : xlsx

 

 

 

 

 

 

데이터 찾아 바꾸기

 

1. 찾기 : 범위지정 가능, 만능문자 ?와 * (와일드카드 문자))사용가능, 만능문자 자체를 찾을땐 앞에 ~ 사용 

//사용법은 sql과 동일

 

홈 - 편집 - 찾기 및 선택 - 선택 / 바꾸기

 

워크시트 보호

검토 - 변경내용 - 시트보호 / 시트 보호 해제 (암호 입력)

 

 

 

 

셀 서식

 

셀서식 : Ctrl+ 1 

 

셀 서식 - 표시형식 - 사용자지정

 

# : 유효한 자리수만 표시하고 0은 표시하지 않는다 // 0점은 입력되지 않는다

0 : 유효하지 않은 자리수는  0으로 표시

 

정수부분은 짤리지 않고, 소수부분은 마지막# 자리에 반올림

 

점수는  ->  #,##0 점

 

@ : 문자데이터 표시위치 나타냄 

 

@님

 

123000000

>> #,##0,  = 123,000

>> #,##0,,  = 123    //콤마가 세자리수 의미

 

 

양수, 음수, 0, 텍스트 순서로 표시형식 한꺼번에 지정할 수 있다.

 

 

 

 

 

연 원 일 요일 표기

 

mmm은 영어표기, ddd는 영어 요일 세글자 표기, dddd는 영어 요일 풀네임 표기, 

 

aaa는 요일을 [월~일]로, [aaaa는 월요일 ~ 일요일]로 표기

 

 

초, 천원, -, : 등은 문자이므로 " " 안에 입력

 

 

 

셀서식 - 맞춤 ☆

 

조건부 서식

 

홈 - 스타일 - 조건부 서식 

1. 영역지정

2. 명령어

3. 수식 사용

4. 식 입력

 

F4 : 수식 입력시 $ 키 위치 자동으로 바꿔줌

 

A25 : 상대주소

$A25 : 혼합주소

$A$25 : 절대주소

 

 

연습문제

 

xlsm : 엑셀 매크로 사용 통합 문서

xltm : 매크로 서식파일 (엑셀 지원파일 아님)

xlsb : 엑셀 바이너리 통합문서

xls : 2003까지 통합문서

 

셀에서 delete 누르면 내용만 삭제됨 (메모나 서식은 지워지지 않음)

 

숫자는 셀하나 채우기 핸들로 끌었을때 반복

Ctrl눌러야 증가

 

A

B

문자 연속데이터를 드래그하면 AB반복됨 (컨트롤 눌러도 똑같다) // 등록돼있는 문자만 나타남

숫자데이터만 연속데이터를 채우기핸들러로 끌었을 시 규칙증가

 

 

행과 열 바꿔 설명하는 문제도 나온다

 

영역지정하고 backspace 누르면 셀 첫 칸만 지워짐

 

 

 

 

 

시트 이름에 공백 사용 가능

 

 

 

 

 

 

 

 

 

 

함수 1

 

 

 

 

 

=RANK(순위를 구하려는 수, 참조영역, 옵션(생략가능) )

순위를 구하려는 수는 그대로 쓰고 범위는 고정돼야함

=COUNT : 셀개수

=COUNTBLANK : 비어있는 셀 개수

 

=VAR(영역) : 영역 내 분산

=STDEV(영역) : 표준편차

=MEDIAN(영역) : 영역 중간값

=MODE(영역) : 영역 내 최빈수

 

 

 

 

 

MOD : 나머지 값 

 

음수로 나눌땐 딱 떨어지는 큰 값으로 가정하고 거기서 얼마나 - 하면되는지 계산

 

EX) =MOD(3,-2) : 3은 -2로 나누어 딱 떨어지는 4보다 1작으므로 0-1=-1

 

SQRT : 제곱근

POWER (수, 지수)

TRUNC : 소수점 이하 잘라버림

INT : 소수점 내림 , INT(-8.9) : -9

 

 

 

함수 2

 

FIND : 찾고자하는 텍스트를 지정한 문자열 안에서 찾아 몇번째에 위치해 있는지 정수형으로 리턴

FINE는 각각의 문자를 1글자로 계산, FINDB는 숫자와 영어는 1, 한글과 특수문자는 2글자로 계산

SEARCH와 SEARCHB도 똑같지만

대소문자 구분하지 않고 와일드카드 문자 사용 가능하다는 점에서 FIND와 다름

 

 

ROUND는 반올림

DOWN은 무조건 자르기

UP은 올리기

 

 

TODAY() DHK NOW() 함수는 인수가 없다

DAY360(날짜 1, 날짜2): 1년을 360일로 하여 두 날짜 사이의 일수 계산

 

EDATE(시작날짜, 개월수) : 시작날짜 기준 개월 후의 날짜 표시

EMONTH(시작날짜, 개월수) : EDATE와 비슷하나 개월 전후의 마지막날 날짜를 표시

 

WORKDAY(시작날짜, 작업일수, 휴일목록)

YEARFRAC(시작날짜, 끝날짜, 옵션) : 일년중 차지하는 비율 표시

 

VLOOKUP (찾을 값, 테이블 범위, 열 번호,  옵션)

HLOOKUP : V는 세로방향, H는 수평방향

 

CHOOSE(값, 인수1, 인수2...) : 값이 3이면 인수 3 값 반환

INDEX(영역, 행, 열) : 영역 안에서 ㅇ행ㅇ열 값

 

proper은 단어 첫글자 대문자

trim은 양끝 공백만 제거 (중간은 X)

 

데이터베이스 함수

 

 

 

 

 

 

함수 연습문제

 

 

 

 

 

옳지 않은것은? <<<<

 

 

DCOUNT는 숫자필드, DCOUNTA는 어느곳을 지정하나 상관X x

 

 

할인율은 2번째 행이니 2번틀림

 

 

 

 

 

 HLOOKUP 이나 VLOOKUP은 결과의 필드를 인수로 갖는 반면 

LOOKUP 함수는 결과의 범위를 인수로 갖는다,

 

 

SUMPRODUCT(범위1, 범위2) 각 자리 수끼리 곱하고 곱한수끼리 더한 값 반환 A1*B1 +A2*B2

 

 

COUNT 함수는 숫자셀의 개수만 셀 수 있다. 문자면 COUNTA 써야함

 

 

VLOOKUP(비교할 값, 범위, 결과값필드, 옵션)

 

 

&는 수식 뒤에 셀 연결해주는 기호

 

 

 

 

필터

 

데이터 - 데이터도구 - 데이터유효성검사

 

IME모드(INPUT METHOD EDITOR) : 기본 설정(한글이나 영어로 기본입력모드 설정 가능)

 

 

자동필터 :

 

2개 이상의 필드에 조건이 설정된 경우 AND 조건으로 결합된다.

 

고급 필터 : 자동필터에 비해서 다양한 조건을 사용하여 데이터 추출가능

 

상단 표가 목록(원본) 데이터

아래 표가 조건 데이터 : 각각 다른 줄에 기입하면 OR조건이 됨

복사위치 지정 : 조건 만족하는 전체 셀을 보고싶다면 전필드를 복사위치에 가져와서 영역지정하면된다.

 

 

텍스트 나누기

 

 

 

 

텝, 세미콜론, 쉼표, 공백, 기타 기호로 구분 기준을 설정하여 텍스트 나눌 수 잇음

 

 

 

데이터분석

 

인쇄  

페이지 레이아웃 - 페이지 설정 - 페이지 : 자동맞춤, dpi

   여백 : 페이지 가운데 맞춤

   시트 : 똑같은 제목으로 여러장 인쇄할때 반복할 행, 열 설정 가능

 눈금선, 흑백으로, 메모, 행열머리글 인쇄, 인쇄 미리보기

 

 

 

 

정렬

 

공백은 오름내림차순 관계없이 마지막 표시

 

값, 셀 색, 글꼴 색 등을 기준으로 정렬할 수 있다.

 

오름차순 정렬 순서 : 숫자 특수문자 영문(소>대) 한글 논리값 오류값 공백 셀 순

 

데이터 -정렬및필터 - 정렬

 

정렬 기준 추가 가능

 

부분 합

 

입력된 자료들을 그룹별로 분류하고 해당 그룹별로 특정한 계산 수행. 

기준이되는 필드는 반드시 정렬돼있어야한다.

 

 

 

 

>> 제품종류(기준: 정렬돼있어야함)로 그룹화

>> 사용할 함수 설정

>> 부분합 계산항목 

 

 

피벗테이블 : 행과 열을 이용하여 데이터를 요약한 표

 

요약데이터 제공 목적

 

삽입 - 그룹 - 피벗테이블 - 피벗테이블

 

 

 

 

피벗 차트

 

삽입 - 그룹 - 피벗테이블 - 피벗차트

 

피벗차트 작성하면 피벗테이블도 작성된다.

테이블 삭제하면 일반파트로 변경된다.

 

 

 

목표값 찾기

 

데이터 - 데이터 도구 - 가상분석 - 목표값 찾기

 

 

 

 

수식셀(계산하는 곳)을

찾는값 78 으로 바꾸려면

값을 바꿀 셀 C5 이 무슨 값으로 변해야하는지 알려줌

 

 

시나리오

 

가상의 상황에서 데이터 결과 분석 예측

 

결과셀은 반드시 변경셀을 참조하는 수식으로 되어 있어야 함

 

하나의 시나리오는 최대 32개까지 변경 셀을 지정할 수 있다,

 

시나리오 요약보고서를 삭제해도 시나리오는 삭제되지 않음

 

 

 

요약 버튼은 요약보고서 만들어줌

 

 

데이터 표 : 특정 값의 변화에 따른 결가값의 변화과정을 표 형태로 표시해주는 가장분석도구

 

데이터 - 데이터도구 - 가상분석 - 데이터표

 

 

 

>> 상황기간을 행(변동되는 값)으로 / 연이율을 열로 한 데이터 표

 

 

데이터 통합

 

두개 표에 있는 데이터를 하나로 통합

 

 

문제 풀면서 정리

 

정렬 조건은 최대 64개까지 지정가능, 정렬방향(L /R) 지정가능,  색상 순서는 직접 지정해야함

사용자 지정 정렬순서는 첫번째 기준이 아니어도 지정 가능  이사 > 사장 > 부장 이어도 사장 쓰고 채우기 핸들러 사용 가능

 

피벗테이블의 보고서에서는 데이터 수정 삭제가 불가능

보고서를 작성한 후에 사용자가 새로운 수식을 추가하여 표시할 수 있다.

원본 데이터가 변경되고 새로고침을 하면 보고서의 데이터도 변경된다.

 

부분합을 할때 그룹화할 항목을 선택하는데, 그룹화를 하기 전에 그룹화 기준 필드는 정렬을 해놔야한다.

 

시나리오는 결과를 예측, 목표값 찾기는 결과값을 정하고 어떤값이 어떻게 변해야하는지 알려주는 도구

시나리오 결과는 요약 보고서나 피벗테이블 보고서로 작성할 수 있다.

 

데이터 통합 : [원본데이터에 연결] 기능은 

통합할 데이터가 있는 워크시트와 통합결과가 작성될 워크시트가 다른 통합문서에 있어도 적용할 수 있다.

 

 

 

 

 

 

시나리오 요약 보고서에서 결과 셀에는  변결셀을 참조하는 수식이 입력돼있다. 

보고서를 삭제해도 시나리오는 삭제되지 않는다.

 

 

 

차트와 매크로

 

기본형은 묶은 세로 막대형 차트

 

원본데이터를 선택한 후 [ F11 ] 누르면 새로운 차트시트에 자동으로 기본 차트 생성

[ Alt + F1 ] 는 현재 워크시트에 차트 삽입

 

삽입 - 차트

 

 

 

 

 

 

 

 

 

 

 

차트 도구 

 

디자인 : 차트 종류, 서식파일로 저장, 데이터 선택, 차트이동(차트를 다른 시트에 첨부할때 사용)

레이아웃 : 영역 서식, 레이블 그룹(차트제목, 축 범례, 데이터 레이블 표시와 위치 , 데이터 표), 축, 추세선 등

서식 : 스타일, 워드아트, 정렬, 크기 설정

 

 

 

매크로 : 반복적인 작업 자동화

 

리본메뉴에 개발도구 탭 ( 파일- 옵션-리본사용자지정 - 개발도구에 체크해야 보임 )

매크로 저장 : 파일 - 다른이름으로 저장 - 엑셀 매크로 사용 통합문서 로 저장해야한다

매크로 실행 : 개발도구 - 코드 - 매크로 또는 바로가기키 또는 개체

다른 통합문서에서도 사용할 수 있도록 복사 이동 가능

Visual Basic Editor 이용항 통합문서에 연결된 매크로를 작성하고 편집할 수 있다.

 

 

매크로 지정

도형 그린 후 바로가기 메뉴 - 매크로 지정

 

매크로 이름 첫글자는 반드시 문자, 나머지는 문자 숫자 밑줄 등 함께 사용 가능, 기호와 공백은 X

 

 

매크로 기록

매크로의 바로가기 키는 Ctrl _ 소문자, Ctrl + Shift +대문자의 결합으로 구성된다.

엑셀의 바로가기키와 같은경우 매크로에서 지정하 바로가기 키가 적용됨

 

개발도구 - 코드 - 기록중지  후  개발도구 - 코드 - 매크로 실행가능

 

 

파일 - 옵션 - 보안센터 - 매크로설정 허용해놔야 실행됨

 

 

여기서부터 연습문제 <<< 18일 아침에 하기

 

 

 

함수-통계 / 수학 / 텍스트

 

MAXA나 MINA의 인수는 참조의 논리값, 숫자를 포함하는 이름, 배열, 텍스트로 나타난 숫자일 수도 있다.

 

FIXED(인수, 소수점이하 자리값, 논리값) 인수를 자리값 자리수까지 표시(생략하면 2), TRUE면 쉼표 포함하지 않고, FALSE거나 생략되면 쉼표 포함됨.(자리값에 포함하는건 아님)

 

WEEKNUM(날짜, 옵션) : 지정된 날짜가 일년 중 몇번째 주에 속하는지 구한다. 옵션 1이면 일요일부터, 2이면 월요일부터

 

 

DAYS360(날짜, 날짜) : 날짜 입력시 쌍따옴표 안에 입력해야한다.

 

 

 

 

 

 

 

 

 

 

 

 

 

 

함수 - 재무/배열수식 (보류)

 

1. 재무 함수

 

 

 

2. 

 

 

외부데이터 / vba

 

텍스트 나누기 

범위에 포함되는 행수에는 제한을 두지 않지만 열은 반드시 하나만 포함돼야한다.

오른쪽에 빈 열이 한개이상 있어야하며, 비어있지 않을경우 기존의 데이터를 덮어버림

 

텍스트 파일 가져오기

 

일정한 너비나 기호로 구분된 텍스트 형식의 파일을 워크시트로 가져올 때 사용하는 도구

사용자 구분기호 정의 가능

데이터 - 외부데이터 가져오기 - 텍스트 명령 이용

 

외부데이터 가져오기

 

db와 텍스트 파일을 워크시트로 가져오거나 DB 파일을 쿼리형태로 변경하여 워크시트에서 사용할 수 있도록 하는 기능

 

데이터 - 외부데이터 가져오기 그룹에서 불러올 파일 형식 클릭

 

원본데이터 변경 시 가져온 데이터에 반영되도록 설정하려면 

데이터 연결 - 모두 새로고침 에서 해당 메뉴를 선택하면 된다.

모두 새로고침 - 통합문서에서 참조한 모든 외부데이터 범위 새로고침

새로고침 - 워크시트에서 참조한 범위만

새로고침 상태 - 쿼리상태를 확인하여 새로고친다 <<?

새로고침 취소 - 새로고친 내용 취소

 

외부데이터가 들어갈 위치를 기존 데이터가 있는 셀로 지정할 경우 원래 있던 시트 데이터는 오른쪽으로 밀려남

 

마이크로소프트 쿼리

 

데이터 - 외부데이터 가져오기 - 기타원본 - 마이크로 쿼리

 

웹 쿼리

 

웹페이지에서 테이블이나 텍스트와 같은 데이터를 검색해서 워크시트에서 사용할 수 있도록 가져오거나 

웹 쿼리 작성할 때 사용

웹페이지의 텍스트, 서식 설정된 텍스트 영역, 테이블의 텍스트 등은 가져올 수 있지만

그림과 스크립트의 내용은 가져올 수 없다.

 

데이터 - 외부데이터가져오기 - 웹

 

웹쿼리는 .iqy 형태로 저장

 

 

 

 

 

vba

 

개념 : VBA는 MS사에서 사용되는 매크로 작업용 언어로 비주얼베이직 언어와 유사

 

(애플리케이션을 위한 비주얼베이직이라는 의미)

 

VBE (비주얼 베이직 애디터)

 

 

 

sub 프로시저 : 인쇄같이 반환값 없는 간단한 기능

function 프로시저 : 반환값 ㅇ, 사용자 정의 함수 작성

프로퍼티 프로시저 : 개체에 속성을 할당 및 반환할  사용 

 

 

 

 

 

 

 

VBA

 

Dim abc(6) As Integer 

 

배열은 60차원까지 작성가능

 

배열 첨자는 0부터 시작이지만 프로시저 시작 전에 모듈 처음에 Option Base 1 선언하면 배열위치는 1부터 시작

 

Dim abc() As Integer  : 동적배열

 

 

 

제어문(If)

 

 

If  조건 then

실행문

 

End i

 

If  조건 then

참 Else 거짓

 

End if

 

제어문(Select)

 

Select case 값(수식)

 

Case 값1

명령 1

 

Case 값2

명령2

...

 

Case else 

명령3 (위 값 1 2 ... 중 없을때, default)

 

End select

 

 

제어문(for)

 

For 반복변수 = 시작값 to 최종값 스탭증가값

실행문

Next 반복변수

 

For i=1 to 5 step 1 

살행문 

Next 

 

 

제어문(do ~ loop)

 

do while 조건식

 

실행문

 

Loop

 

 

 

Do  //실행 한번 하고 조건이 맞으면 계속 이어서 반복

실행문

 

Loop while 조건식

 

 

 

Do until 조건식 //조건 만족하지 않을때 실행

실행문

Loop

 

 

제어문 (while - wend)

 

While 조건

 

실행문

 

wend

 

반복, do while ~ loop문과 동일

 

 

제어문 (For Each ~ Next)

 

컬렉션에 포함된 개체 수만큼 명령 코드 반복 실행

 

For Each 개체변수 In 컬렉션 개체

 

실행문

Next 개체변수

 

 

 

 

 

 

MsgBox ("메시지", 버튼 번호나 이름 + 마크이미지, "Winform")

 

 

Application개체

 

주요속성

 

Active + 셀, 시트, 가장 위에 있는 창(Window)

ActiveWorkbook : 가장 위에 있는 통합 문서

ThisWorkbook : 매크로 코드가 실행중인 문서

Workbooks : 통합문서

WorksheetFunction 

Worksheets

Selection : 선택된 개체