컴활1급 실기 - 1과목 스프레드시트 메모

인강 : 인천시민사이버교육센터 ( http://edu.cyber.incheon.kr/ )

 

[HD]컴퓨터활용능력시험 1급 실기 - 1과목 스프레드시트 실무 (2018개정)

 

 

 

 

1. 외부데이터, 페이지 레이아웃

 

외부데이터 가져오기

 

기타원본 - 마이크로소프트 쿼리

 

(드라이브에 위치한 파일만 탐색가능한듯)

(테이블형태의 데이터만 가져올수있음)

 

표도구 - 디자인 - 범위로 변환 >> 쿼리가 사라진 표형태로 변환가능

 

 

페이지 레이아웃 : 머리

 

한장에 인쇄하기

 

페이지레이아웃 - 페이지설정 

페이지 - 자동맞춤 1 용지너비 1 용지높이

 

페이지 가운데 맞춤

 

페이지 설정 - 여백

 

머리글/바닥글 설정

 

페이지 설정 - 머리글/바닥글

 

&[현재페이지]/&[전체페이지] 식으로 입력키와 &는 세트이므로 슬래시는 뒤쪽에 위치해야함

 

 

각 페이지마다 반복할 행/열 지정

 

페이지 설정 - 시트 - 반복할 행/열 이름(머리글) 드래그

 

시트 탭 :  눈금선, 흑백으로, 간단하게 인쇄(표 모양, 서식 없앤 채로 출력), 행/열 머리글 함께 출력

  메모 표시(시트에 표시된대로/ 시트 끝에 모아서), 

 

 

메모 삽입

 

셀에서 우클릭 - 메모 삽입 >> 셀 우측 상단에 빨간세모 표시

 

 

 

2. 셀 서식 보호

 

 

셀서식 단축키 : Ctrl + 1

 

홈 - 표시형식 : 숫자 / 문자 서식 

 

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

 

# : 유효한 자리수만 표시, 유효하지않은 0은 표시 X

0 : 유효하지 않은 0은 0으로 표시

#,### : 천단위에 , 를 붙여주는 서식 코드. 0 표시 X //0표시를 안한다는 것은 자리수가 없는 숫자 0이 들어왔을 때

#,##0 : 0을 입력하면 0을 표시

@ : 문자데이터의 표시위치 지정  예 ) @"님"

 

 

ddd : Sun Mon Tue...

dddd :  Sunday Monday Tuesday...

aaa : 월화수목금토일

aaaa : 월요일 화요일 ...

 

천단위 구분기호와 소수 첫째자리까지 표시되도록 하시오.

#,##0.0

 

[조건] 

예 ) 30이상의 셀의 숫자 앞에 '00-'를 표시하시오.

[>=30]"00-"##

 

1234.56 를 001234.5600원 으로 표현하려면 000000.0000"원"

 

시간 서식

 

hh:mm:ss AM/PM

 

h"시" mm"분" ss"초" A/P

 

 

 

 

 

 사용자 지정에 여러가지 형식 지정하기

 

양수값;음수값;0일때;문자값 순서대로 지정

 

양수는 소수점 세번째 자리까지 표현, 음수는 소수점 세번째자리까지 나타내고 빨강으로, 0은 0.00으로, 문자는 ㅇㅇ님으로

>> #.##;[빨강](#.###);0.00;@"님"

 

현재 날짜 입력 : Ctrl + ;

 

 

 

 

 

맞춤 : 텍스트 줄바꿈(셀 크기에 맞게 줄바꿈) / 균등분할 / 선택영역의 가운데로 / 셀병합 / 셀에 맞춤

 

보호

 

셀보호

셀서식 - 보호

검토 - 변경내용 - 시트보호

 

통합문서 보호 

검토 - 변경내용 - 통합문서 보호

 

 

 

수식을 감추고, 수식 변경이나 내용 변경 못하도록 보호하기

 

1. 셀서식 - 보호 : 잠금, 숨김

2. 검토 - 변경내용 - 시트보호 

 

잠기지 않은 셀 선택 [ V ]<< 선택만 가능, 수정은 못함

 

통합문서 보호

 

검토 - 변경 내용 - 통합문서 보호

 

구조 , 창 

창에 체크하면 시트 창 크기조절 불가(저장한 형태로만 보기 가능)

구조에 체크하면 시트 추가, 숨김, 삭제 불가

 

 

 

3. 조건부 서식

 

홈 - 스타일 - 조건부 서식

 

조건부 서식 - 규칙관리에서 적용한 규칙 모두 볼 수 있음

 

 

부분이 조건에 해당되면 해당하는 행 전체에 서식 지정하기

 

데이터 전체 전체 선택 후

홈 - 스타일 조건부서식 - 새 규칙 - 수식을 사용하여 서식을 지정할 셀 지정

 

검사할 영역과 조건 입력 예) =$F4="컴퓨터"

 

 

새규칙에 있는 규칙 유형 목록

셀 값을 기준으로 모든 셀의 서식 지정

다음을 포함하는 셀만 서식 지정

평균보다 크거나 작은 값만 서식 지정고유 도는 중복 값만 서식 지정

고유 또는 중복 값만 서식 지정

수식을 사용하여 서식을 지정할 셀 결정

 

 

수식을 사용하여 서식을 지정할 셀 결정시 

 

-조건이 두개이상이라면

 

=AND($C4>=90,$E4>=70)

 

-날짜정보를 포함한 셀을 조건으로 삼을시

 

=MONTH($D4)=5

 

- MOD함수 이용하여 배수 조건 입력하기//MOD는 modulo(나머지연산)의 약자

 

=(mod($H3,3)=0)  //3의 배수

 

- and를 사용하지 않고 여러가지 조건 충족한 수식 만들기

 

수식이 1이 되면 참이므로 (참인 조건) *(곱하기) (참인 조건)을 이용하여 작성한다. 

=(mod($H3,3)=0) * ($L3>=70)

 

- 사용할 함수 조건을 정확히 보기

 

(조건부서식2 파일의 sheet2)

[E3:R30]영역에 대해서 해당 열번호가 홀수이면서 [E3:R3]영역의 월이 홀수인 열전체에 대해서 채우기 색을 '표준색-노랑'으로 적용하시오.

단. 규칙유형은 '수식을 사용하여 서식을 지정할 셀 결정'으로 지정하고, 한 개의 규칙만을 이용하여 작성하시오.

AND, COLUMN, ISODD, MONTH함수를 이용하시오.

 

=(isodd(column($E$4:$R$30)))*(isodd(month($E$3:$R$3)))

 

=and(isodd(column()),isodd(month(E$3)))

 

이미 선택영역이 있으므로 column()은 따로 영역 지정할 필요 X

 

AND 함수를 사용하지 못할 경우에는 논리곱 이용

 

=(isodd(column()))*(isodd(month(E$3)))

 

 

 

 

 

 

4. 자동필터, 고급필터 : 조건에 맞는 레코드 추출하여 보여줌

 

자동필터

 

데이터 탭 - 정렬 및 필터 그룹 - 필터 : 도메인 형식으로 선택해서 보기 가능

 

셀 우클릭 - 필터로도 자동 필터 사용 가능

 

자동 필터의 텍스트 필터에서 시작문자 , 같음, 같지않음, 끝문자, 포함, 포함하지 않음, 사용자 지정 필터 가능  

 

숫자 필터 - 해당범위 : 그리고, 또는 으로 여러가지 조건 지정 가능

 

 

고급필터

 

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

 

목록 범위 / 조건 범위 / 복사 위치

 

조건범위

 

직접 입력 또는 기존 데이터테이블에서 원하는 조건 부분 복사해오기 

and 조건은 같은 행에, or 조건은 다른 행에 입력 

단순 연산식이 아닌 수식(함수)을 입력할 때는 필드명이 같으면 안되고, 

필드명이 주어지지 않기때문에 조건 범위를 명확하게 표시해주어야한다.

 

조건

=$D3>=average($D$3:$D$10)

: 왜인지는 모르겠으나 1항에 D3으로만 써도 된다.

 

문자를 포함하는 조건쓰기

 

이* (*은 여러개의 문자를 표현해줌. 서식이 아닌 조건이므로 조건이 되는 문자를 따옴표로 감싸지 않아도 된다.)

 

전체 필드가 아닌 나타내고싶은 필드가 있을 경우에는

나태낼 필드들만 복사해오고 그 영역을 복사 위치로 설정한다.

 

 

숫자범위 조건이 2개 이상일 경우 그냥 같은 행에 같은 필드 명, 조건을 각각 써준다.

 

관리비용	관리비용
>=600000	<=700000

 

 

 

조건범위 입력할때 웬만하면 직접입력하지 말고 필드 복사해오기

수식을 써야해서 필드명을 바꿔야할 경우에도 일단은 복사해온 다음에 수정하기

<<이렇게 하지 않았더니 이유없이 오류가 났음 // 다시 해보니 오류가 안남.... 이유는 알 수 없음

고급필터 사용 전에 빈 셀 클릭해도 되고, 데이터가 있는 셀 클릭해도 다 정상적으로 추출됨

 

=month($B4)=10

=month(B4)=10

둘 다 됨

 

영역지정은 절대참조로 할 것

 

2번째로 크거나 작은 수 구하기

 

조건

=H3=large(영역,2)

=H3=small(영역,2)

 

5. 연습문제

 

6. 논리/날짜/시간 함수

 

조건 검사할 범위 / 합계를 낼(계산할) 범위 

 

 

 

 

주민등록번호 셀에서 생년월일 날짜정보 추출하기

 

=DATE(LEFT(D4,2)+1900,MID(D4,3,2),MID(D4,5,2))

 

left, mid, right 함수는 문자열에 대한 함수이므로 숫자도 문자 데이터로 인식한다.(쌍따옴포 꼭 써줘야함)

 

 

 

 

기준 년도를 정해두고 나이 구하기

 

year(기준날짜) - year(생년월일)

기준 셀을 잡을 땐 핸들러를 사용해도 값이 변하지 않도록 고정해준다. $I$3

 

기준을 오늘날짜로 잡고싶으면

year(today()) - year(생년월일)

 

 

주민번호 뒷자리 숨김처리

 

=replace(텍스트, 시작번호, 문자개수, 대체할 문자)

 

 

수식 뒤에 문자를 입력할때에는 & "문자"

 

 

여러 조건과 그에 따른 다른 결과 입력하기 위해서는

 

if문에 겹 if문을 쓰면 된다.(if 함수의 인자로 if함수를 주면 주고 함수를 인자로 줄땐 =를 포함하지 않는다.)

 

조건만 여러가지이고 결과는 두가지인 경우

 

if함수의 조건 인자에 and 함수를 사용한다.

 

숫자 비교, 연산자 함수를 사용할땐 단위까지 똑같이 입력해줘야 비교가 가능하다.

 

 

 

7. 데이터베이스/찾기/참조함수

 

 

 

 

 

 

찾기함수 : VLOOKUP(찾을 값, 테이블 범위, 열번호, 옵션: 정확한 값을 찾을댄 0이나 false 입력)

 

열 번호는 테이블의 조건을 포함한 테이블에서 나타내고자하는 결과 열 정보

 

sql : select (attribute1, attribute2) from table1 where attribute3=찾을값; 

에서 테이블 범위가 table1, 열번호는 attribute1,2 찾을 값은 where(조건)절에 들어갈 조건을 값으로 나타낸셈

 

VLOOKUP은 레코드가 가로형일때, HLOOKUP은 레코드가 세로형일때(애트리뷰트가 세로로 정렬돼있을때) 사용한다.

애트리뷰트가 열로 결정되는지(VLOOKUP)) 행으로 결정되는지(HLOOKUP)로 쓰임이 갈림

 

옵션 설정할때, 데이블 범위에 있는 찾을값에 해당하는 자료가 모든 경우의 수를 포함하고 있지 않은 경우에는

비슷한 값으로 찾을 수 있도록 1이나 true를 입력해준다.

 

구하려는 값에 단위가 남아있으면 셀서식에서 고쳐준다.

 

 

 

데이터베이스 함수

 

 

 

DB 영역 잡을때 반드시 필드를 포함해야함

필드는 계산을 수행하고자하는 필드번호, 조건 범위는 필드 제목과 조건으로 구성 

 

 

DCOUNT 함수는 숫자필드만 연산가능, DCOUNTA는 숫자, 문자 모두 가능

 

(sheet3 문제 다시 풀어볼것)

 

에러나는 부분은 iferror(사용할 수식, 에러날경우 표시할 숫자나 문자) 사용하여 처리

 

 

몇 등까지 다른 결과를 산출해야할때, choose함수에 rank함수를 인자로 사용

 

iserror를 사용해도 되면 편하게 구할 수 있지만 사용하지 못하면 choose함수에 결과값 일일히 주어줘야함

 

인자로 함수를 입력했는데 자꾸만 문자열로 인식하면 함수식이 틀리지 않았는지 확인해본다.

 

 

 

lookup함수는 다른 테이블에서 정보를 가져와 비교해야하는 경우 사용

 

match함수는 하나의 테이블에서 특정 값의 행 순서를 반환할 때 사용, 

match type인자는 정렬이 돼있을경우에만 1로 하고 일반적으로 0을 쓴다.

 

index함수를 사용하여 특정 값이 큰 레코드의 구하려는 값을 찾기 위해서는

 

match함수로 행(레코드 순서 번호)을 알아야내야한다.

 

(sheet7 문제 다시 풀어보기)

 

 

8. 재무/텍스트함수

 

 

eday() 기준일로부터 정확히 몇달 전후 날짜를 반환

(1990년 1월 1일을 1로 잡아 숫자로 반환하므로 형식을 날짜로 바꿔주면 날짜로 표기된다.)

emonth() 기준일로부터 몇달 전후 달의 마지막 날짜를 반환

 

workday() 인수로 시작일, 업무처리기간, 공휴일(날짜입력, 생략하면 주말만 제외) 을 주고

주말을 제외한 영업일로 계산하여 날짜 반환

 

yearfrac //fraction :분수

1년중 특정 기간이 차지하는 비율을 반환

 

weekday함수는 요일을 숫자로 반환하므로 한글로 입력하고싶다면 choose함수 인자에 weekday 함수를 사용

 

#VALUE! 에러는 계산에 사용된 값이 유효하지 않을 때 발생  

예) 2021-09-31(존재하지 않는 날짜) 셀을 포함한 수식에서 나는 에러

 

 

find함수는 텍스트를 찾아 시작위치를 반환, 찾을 텍스트, 범위가되는 텍스트, start number

 

findb 함수는 문자를 세지 않고 바이트를 센다.

 

search 함수는 find 함수와 같은 기능을 하지만 대소문자를 구분하지 않는다는 차이점이 있다.

 

len항수는 문자열 기이 알려주는 함수

 

replace 함수는 문자열의 일부를 다른 문자로 보여주는 기능 (변환할 텍스트의 위치를 번호로 입력)

(기존 텍스트,스타트 넘버, 변환할 문자개수, 대체할 문자)

 

sunstitute 함수도 같은 기능을 하지만 문자를 찾아 고쳐준다는 점에서 다름 (변환할 텍스트를 직접 입력)

(텍스트, 바꾸고싶은 문자, 대체할 문자, (선택)바꿀 문자의 몇번째부터 바꿀것인지 입력)

 

text 함수 : value와 format입력하여 셀의 포맷을 정할 수 있게해주는 함수

 

concatenate 함수는 여러 셀의 문자열을 하나로 합쳐서 반환해준다.    //나열하다

 

fixed 함수는 소수 몇번째 자리까지 표시할 것인지 입력하고(decimal : 십진법 말고 소수라는 뜻도 있음) 반올림해줌

 

value 함수는 숫자로 된 문자열을 숫자 값으로 반환해준다.

 

exact 함수는 두 셀의 텍스트가 정확히 일치하는지 논리값으로 반환한다. (띄어쓰기만 달라도 false 반환)

 

rept 함수는 문자열을 원하는 만큼 반복해서 보여준다.

예) 각자의 점수를 10으로 나눈 수 만큼 별 문자를 부여할 때

 

sumproduct 각각의 요소를 각각에 주어진 조건으로 계산 한 뒤 더해준다. // product : 산출

 

곱하는 요소끼리의 순서가 같아야한다. array1과 array2를 인자로 주고 각 요소를 곱해서 더한 값을 반환한다.

 

 

재무 함수

 

 

FV함수는 일정 금액을 정기적으로 불입(지불하여 넣은 자본금)하고 일정한 이자율을 적용한 투자의 미래 가치를 계산

 

월을 기준으로 하므로 연이율 4%, 납입기간 5년 이면 

rate는 4% / 12달

nper은 총 납입기간 5 * 12달

pmt는 정기적으로 적립하는 금액인데 음수로 입력해야 산출결과가 양수로 출력된다.

pv는 현재가치(선택)

type은 1은 투자주기 초, 0은 투자 주기 말

roundup 함수는 소수점 몇째자리까지 표시하는 가를 인수로 받기때문에

100의 자리까지 나타내기위해서는 

 

 

9. 사용자정의함수/배열수식

 

사용자 정의 함수

 

함수 작성

 

개발도구 탭 - 코드 그룹 - Visual Basic

 

삽입 - 모듈

 

(개발도구 탭이 안보일 경우 빠른실행도구모음 - 기타 명령 - 리본사용자 지정 - 우측 하단 개발도구 체크)

 

우측 파일 목록에 모듈1 폴더 밑에 Module1 파일 생성

 

Public Function 함수명( 파라미터 )

 

if 조건

 

then 실행문1

else 실행문 2

 

end if

 

End Function

 

문제) 

보너스 지급율을 계산하는 사용자 정의함수 'm보너스지급율'을 작성하여 계산을 수행하시오.

 

▶m보너스지급율은 월평균임금과 전년월평균임금을 인수로 받아 보너스지급율을 계산하여 돌려준다.

▶보너스지급율은 (월평균임금-전년월평균임금)/월평균임금이 0.05이상이면 0.05로 그 이외에는 0.1이 표시되도록 계산한다.

▶m보너스지급율 함수를 이용하여 H3:H9영역에 보너스지급율을 표시하시오.

 

Public Function m보너스지급율(월평균임금,전년월평균임금)

 

End Function

 

 

Public Function m보너스지급률(월평균임금, 전년월평균임금)

 

    If (월평균임금 - 전년월평균임금) / 월평균임금 >= 0.05 Then

    m보너스지급률 = 0.05

    Else 

m보너스지급률 = 0.1 

    End If

    

End Function

 

 

[Error] 컴파일 오류입니다. 블록 if가 없는 End if

 

★★★실행문이 하나뿐인 간단한 if문은 then 뒤에 줄바꿈 없이 실행문을 쓰고 End if를 사용하지 않는다.

하지만 Else를 사용하여 실행문이 2개 이상인 if문은 then, 실행문1, Else, 실행문2 ... 뒤 줄바꿈을 반드시 해주어야하며

End if로 if문을 닫아주어야한다.

 

작성후 매크로사용통합문서 형식으로 저장

 

매개변수 이름 틀리지 않았는지 제대로 체크하기,,, 

비주얼베이직에서는 컴파일러가 오류를 자세히 찾아주지 않는다

 

사용자정의 함수식을 수정하면 함수를 다시 적용하지 않아도 자동으로 적용된다. 

 

 

문제)

페이지단가를 계산하는 사용자 정의함수 'm단가'을 작성하여 계산을 수행하시오.

 

▶m단가은 도서코드와 정가를 인수로 받아 페이지수를 계산한 후 페이지당 단가를 계산하는 함수이다.

▶페이지수는 도서코드의 길이에 따라 다르며(Hint), '-'와 '-'사이의 숫자이다.

m단가는 정가/페이지수로 계산하고 소수 첫째자리에서 반올림하여 표시한다.

▶m단가 함수를 이용하여 H3:H9영역에 페이지단가을 표시하시오.

 

Public Function m단가(도서코드,정가)

 

End Function

 

 

Public Function m단가(도서코드, 정가)

 

If Len(도서코드) = 7 Then

페이지수 = Mid(도서코드, 3, 3)

Else

페이지수 = Mid(도서코드, 3, 2)

End If

 

m단가 = 정가 / 페이지수

 

End Function

 

※인강 답안과는 약간 다름

인강에서는 페이지수라는 변수를 따로 만들지 않고 if의 실행문에 m단가의 값을 줌

 

 

사용자 정의 함수 사용

 

함수 마법사 - 범주 선택 - 사용자정의

함수 선택 목록에서 지정된 함수 선택하고 확인

 

수식 칸 f(x)의 함수 선택 목록에서 사용자정의함수 선택

 

 

 

배열수식

 

 

배열수식은 반드시 수식 입력 후 Ctrl + Shift + Enter 를 입력해줘야한다.

 

sum만 쓸 땐 합계를 구할 범위를 곱하고

if와 함께쓸땐 합계를 구할 범위를 if의 인자로 준다.

 

조건끼리는 곱하고 if함수일 경우에는 합계구할 범위를 true 위치에, 일반연산자인 경우엔 조건과 함께 곱해준다.

 

 

 

10. 연습문제  (시험전날 해보기)

 

 

11. 부분합과 정렬

 

정렬

 

데이터 탭 - 정렬 및 필터 그룹 - 정렬

 

기준은 여러개 설정 가능

 

공백은 오름,내림차순 관계없이 가장 마지막에 표시된다.

 

오름차순 정렬 순서 : 숫자 특수문자 영문(소, 대) 한글 논리값 ///// 오류값 공백  //도서관 청구기호 생각하면 쉬움

 

오름(작은거부터)차순 / 내림(큰거부터)차순

 

정렬 순서 직접 지정하기 

 

정렬 목록에서 사용자 지정 목록 선택

콤마로 구분하여 목록 추가

 

 

 

부분합 (부분합을 하기전엔 기준이 되는 필드는 반드시 정렬이 돼있어야함)

: 워크시트에 입력된 자료들을 그룹별로 분류하고 해당 그룹별로 특정한 계산을 수행하는 기능

 

사용할 수 있는 함수 : 합계, 개수, 평균, 최대값, 최소값, 곱, 숫자 개수, 표준편자, 표본 표준편차, 표본분산, 분산 등

 

데이터 탭 - 윤곽선 그룹 - 부분합

 

그룹화할 항목은 정렬했던 기준으로

함수, 계산할 항목 정하고 확인

 

그룹화하여 계산하는 함수가 여러가지이면

하단의 [새로운 값으로 대치]를 체크 해제해야 모두 표시된다.

 

그룹화 기준을 잘못잡았으면 [새로운 값으로 대치]를 체크해놓고 다시 설정하면 된다.

 

그룹화하면 특정 그룹만 세부데이터가 보이도록 나머지 그룹 데이터는 접어놓을 수 있음

 

윤곽지우기는 데이터 - 윤곽선그룹 - 그룹해제 - 윤곽지우기

 

합계를 구하면 "ㅇㅇ 요약" 이라고 표시되는데 이 텍스트를 수정하기 위해서는 

홈 - 찾기 및 선택 - 바꾸기

 

 

부분합 지우려면 부분합 창에서 [모두 제거]

 

12. 피벗테이블보고서

 

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

 

피벗테이블을 생성할 때 데이터의 범위는 합계를 포함하지 않게 한다. (피벗테이블에서 낼 수 있으므로)

 

 

 

필드 창이 뜨는데 문제에서 요구하는대로 보고서필터 / 열레이블 / 행레이블 / 값 목록에 필드를 드래그해준다.

 

값부분에 두 개 이상의 필드를 끌어다놓으면 열레이블에 값 행이 하나 추가된다.

 

피벗테이블 우클릭

 

레이아웃 및 서식

 

레이블에 있는 셀 병합 및 가운데 맞춤

계약기간이라는 열레이블 셀 ( 아래에 값 목록들(계약금액 합계, 이자액 평균)이 위치)을 병합하고 가운데 맞춤해준다.

 

빈 셀 표시 : 병합된 셀인지 빈 셀인지 구분할 수 있도록 빈셀에 표시

 

요약 및 필터

 

총합계(행 총합계 표시 , 열 총합계 표시)

 

값 필드 설정

 

값요약 기준 : 합계 / 평균 / 최대값 / 최소값 등 값 요약 기준 설정 가능

 

값필드 표시형식 : 특정 값의 셀 서식만 변경 가능

 

피벗테이블의 값 영역에 옮긴 필드 클릭 - 값 필드 설정 - 값 표시 형식

 

보고서 필드 영역

 

이 영역에 있는 필드가 필터가된다.

예시에서는 필터로 선택(여러 항목 선택가능)한 이름에 해당하는 데이터만 볼 수 있다.

 

 

필드 그룹화

 

필드 - 우클릭 - 그룹화 : 시작, 끝, 단위 입력하여 그룹화

 

 

13. 피벗테이블보고서2

 

문제 풀때 피벗테이블 생성 위치도 신경쓰기

 

왼쪽에 아래로 나열 된 필드가 행레이블

행레이블 보다 한 행 위로 행방향으로 나열된 필드가 열레이블

 

피벗테이블 행 그룹 순서를 드래그 수동 이동 시

그룹 이름 셀을 선택하고 경계선 박스를 끌면 이동된다.

 

 

문제1) 

 

피벗테이블 보고서의 레이아웃과 위치는 그림을 참조하여 설정하고

보고서 레이아웃을 개요 형식으로 표시하시오. ( 테이블 형식 / 개요 형식 )

 

가공일은 월단위로 그룹을 지정하시오

 

제조팀은 제조 2팀, 제조 4팀, 제조6팀만 나타나도록 한 후 단위당원가와 목표 매출액을 열합계비율을 기준으로 

다음과같이 나타나도록 작성하시오.

 

완성된 피벗테이블보고서에는 피벗스타일보통5를 지정하시오

 

값 영역의 표시형식은 값 필드 설정의 셀서식을 이용하여 소수1자리까지 표시하시오

 

제조 4팀의 1월 목표매출액 데이터만 자동생성한 후 시트이름을 1월 가공현황으로 지정하시오.

 

 

 

피벗테이블을 생성하면 피벗테이블도구 탭 : [옵션], [디자인]이 생성된다.

 

디자인 탭 - 레이아웃 그룹 - 보고서 레이아웃 : 압축형식 / 개요형식 / 테이블형식 / 모든 항목 레이블 반복 / 항목 레이블 반복 안 함  중 개요형식 선택

 

값 필드를 열합계비율을 기준으로 나타내려면

값 필드 설정 - 값표시 형식 : 열합계비율을 기준으로 ( 행 합계비율, 총 합계비율 )

 

생성된 피벗테이블도구 탭 - 디자인 - 피벗테이블스타일 탭 : 목록에 들어가서 피벗스타일보통5 로 지정

 

제조 4팀의 1월 목표매출액에 해당하는 데이터 셀을 더블클릭하면 해당 데이터만 새로운 시트에 자동생성된다.

 

 

문제2)

 

▶ 피벗테이블 보고서의 레이아웃과 위치는 그림을 참조하여 설정하고 보고서레이아웃을 테이블형식으로 표시하시오.

▶ 값 영역의 표시형식은 '값필드설정'의 셀서식을 이용하여 사용자 지정에서 지정하시오.

 

▶학번을 기준으로 그림과 같이 그룹을 설정하고 하위수준의 표시여부를 설정하시오.(단 학번이 03으로 시작하면 1학년, 04로 시작하면 2학년임)

 

▶∑값의 위치를 행 레이블로 이동시키고 수강과목은 엑셀만 나타나도록 하시오.

 

 

 

행 레이블을 그룹화할 때 학번같이 자동으로 그룹화가 어려울 경우에는 직접 드래그하여 그룹화하고 이름을 붙여준다. 

 

값 목록을 세로로 나열하려면

열레이블에 자동 생성된 값 (가로로 위치) 을 행레이블 영역으로 옮겨준다.

 

 

 

           

열 레이블의 값을 행레이블로 옮김 ▶  

 

 

 

그룹화했던 기존의 행에서 그룹화한 행만 남기고 싶다면 

기존의 묶이지 않은 행 머리 - 우클릭 - 확장/축소 - 전체 필드 축소

 

 

 

문제3)

 

▶ 피벗테이블 보고서의 레이아웃과 위치는 그림을 참조하여 설정하고 보고서레이아웃을 개요형식으로 표시하시오

 

▶ 대출금액×0.05/12를 계산하는 월이자 계산필드를 추가하시오.

 

▶ 대출지점이 그림과같이 표시되도록 정렬하시오.

 

▶ 피벗스타일 밝게21을 설정하고 줄무늬행 옵션을 설정하시오.

 

▶ 각 그룹의 하단에 요약이 표시되도록 설정하고 대출금액과 월이자의 표시형식은 값필드설정의 셀서식을 이용하여 숫자범주에서지정하시오.

 

 

월이자 계산 필드 추가

 

피벗테이블 도구의 옵션 탭 - 계산 - 필드 항목 및 집합 - 계산 필드 

: 이름은 월이자, 수식은  = 대출금액*(0.05/12)

입력한 후 [추가]하면 월이자가 필드에 추가됨

 

 

14. 목표값찾기, 시나리오

 

목표값 찾기 : 목표값을 정해놓고 목표값 달성을 위해 특정한 셀값이 얼마가 되어야하는가 알고 싶은 경우에 사용

 

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

 

바꾸고자하는 셀을 선택한 상태에서 가상분석-목표값찾기

 

수식셀 :  선택된 셀 (현재 결과값)

찾는 값 : 원하는 결과값

값을 바꿀 셀 : 목표에 다다르기 위해서 바꿀 셀

 

 

 

 

시나리오 : 가상의 상황을 만들어 그 결과를 분석하고 예측하는 가상분석 도구

 

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

 

셀 이름은 수식창 좌측의 열머리부분에서 수정하면 된다. // 공백은 포함될 수 없다.

 

바꿀 셀, 결과 셀의 셀 이름을 주어진 대로 변경 // 수식 - 이름관리자에서 변경한 열머리 이름 전체적으로 관리가능

 

변경할 셀을 선택하고 (동시에 여러 셀 선택가능. 여러 항목에 대하여 동시에 변화를 줄 경우 여러 셀 선택)

 

가상분석 - 시나리오

 

동일한 값에 대한 시나리오를 등록하고 싶은 만큼 등록하고 [요약]

 

//변경하고자하는 값이 퍼센트일경우 퍼센트 단위를 써주거나 100으로 나눈 수로 입력한다.

 

새로운 시트지에 보고서가 생성된다.

 

(목표값찾기는 결과를 정해두고, 시나리오는 변경값을 정해둠)

 

 

15. 데이터표, 통합

 

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

(시나리오 다음 차례로 설명하는 이유가 있다)

 

데이터 탭 - 데이터도구 그룹 - 가상분석 - 데이터표

 

 

1. 데이터표 비고란에 기존 데이터의 결과값(비교할 기존값) 셀을 참조시킨다. (=결과값 셀 클릭, 엔터)

//문제에서 구하고자하는 결과값이 무엇인지를 제대로 봐야한다.

 

2. 데이터표 드래그 >> 가상분석 - 데이터표 : 행입력, 열입력 셀에는 기존 데이터 셀을 지정해줌

//데이터표의 영역지정은 머리글을 포함하지 않게 한다.

변화시킬 요소가 하나뿐이면 데이터표 생성시 해당하는 행 열 중 하나만 입력해주면 된다.

 

데이터 통합 : 여러 시트에 입력돼있는 데이터를 통합해주고 다른 통합문서에 입력되어 잇는 데이터도 하나로 통합할 수 있다.

 

1. 기존 틀이 없으면 바로 데이터 - 데이터 도구 - 통합

   기존 표 틀이 있다면 표 빈 통합데이터 표를 드래그 한후 데이터 - 데이터 도구 - 통합

 

2. 사용할 함수 선택

 

3. 참조란에 통합하고자하는 표 입력하고 추가

 

4. 사용할 레이블의 첫 행, 왼쪽 열 체크 - 확인

 

데이터 통합 표에 통합할 데이터의 형식을 지정해주어 해당되는 형식에 해당되는 데이터를 나타낼 수도 있다.

예) 김, 이, 박 성씨별로 목표액과 실적의 평균을 구할 때 사원명이 입력되는 셀에 김* 이* 박* 입력

 

요소별로 다른 함수를 사용할 때에는 ,,,,,, 인강에 안나옴

 

 

16. 매크로 

 

개박도구 탭 : 파일 - 옵션 - 리본사용자지정 - 개발도구에 체크

도형, 양식단추를 이용하여 매크로 지정할 수 있다.

 

비주얼 베이직 에디터로 통합문서에 연결된 매크로를 작성하고 편집할 수 있다.

 

매크로 저장 : 다른이름으로 저장 - 매크로사용통합문서 형식으로 지정 (사용자 정의 함수도 이 형식)

 

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

 

매크로 지정

 

1. 도형 그린 후 - 우클릭 - 매크로 지정 OR 개발도구 - 컨트롤 그룹 - 삽입

 

2. 매크로 기록 : 바로가기 키 설정 // 선택사항

이 과정 다음부터 녹화시작, 엑셀 좌측 하단에 녹화 아이콘 생김

작업을 마친 후엔 반드시 영역을 해제해준다.(왜인진 모름. 해제안해도 정상작동 하던데)

 

기존에 만들어둔 매크로 지우기/ 삭제하기

도형의 바로가기키가 아닌 개발도구 탭의 매크로 버튼으로 접근하면 매크로 창에 삭제버튼이 생긴다.

 

3. 개발도구 탭 - 코드 그룹 - 기록중지 //개발도구 탭 왼쪽 상단에 위치

 

실수로 도형을 삭제해도 다시 만들고 매크로 지정- 만들어둔 매크로 설정 - 확인

 

[ERROR] 매크로 작성 시 '수식이 너무 복잡해서 개체에 할당할수 없습니다.' 오류 해결방법  

 

엑셀에서 매크로 작업 시 아래와 같이  '수식이 너무 복잡해서 개체에 할당할수 없습니다'라는 메세지 등장과 함께 아무리 다시 매크로 작업을 해도 해결이 안되는 경우가 있습니다.

 

오류의 원인은 여러가지가 있지만 대부분 해당 실습(엑셀)파일의 파일명이나 폴더명 등에 "[", 또는 "]" 등의 부호가 포함되어 있는 경우에 발생합니다.

 

이럴땐 [다른 이름으로 저장]하셔서 대괄호는 지우시고 저장하셨다가 다시 열어서 특정 개체에 매크로를 연결해 보시기 바랍니다.

(아니면 작업하신 파일은 휴지통으로 버리시고 다시 실습파일을 다운로드 받으세요.)

 

*F2 누르고 파일명이나 폴더명을 확인했을 때 대괄호가 없다고 하시는 분들!

그대로 다른이름으로 저장하시거나 새로운 실습파일을 다운로드 받으셔서 매크로 작업을 실행하시길 바랍니다.

 

출처 : 컴활 전문 사이트 기사퍼스트(www.gisafirst.com) 

 

 

+ 매크로가 안먹힐 경우

 

파일 - 옵션 - 보안 센터 - 보안센터 설정 - 매크로 설정 - 디지털 서명된 매크로만 포함 체크

 

 

 

17. 차트

 

1. 영역지정 

2. 삽입 - 차트 OR Alt + F1

 

삭제하려면 경계선 누르고 delete

 

 F11은 새시트에 차트생성

F11로 빈 차트 시트를 만들고 디자인 탭에서 데이터를 선택해도 된다.

 

차트 생성하면 차트 도구 탭 생성됨

디자인 / 레이아웃 / 서식

 

디자인 탭 : 차트이동(우측) / 차트종류 변경 / 행열전환 / 데이터 선택 / 차트레이아웃(세트)

 

 

 

차트 종류 변경 : 차트도구 - 디자인 - 차트 종류 변경

 

차트종류 변경은 같은 그래프 내에서도 계열별로 다르게 설정할 수 있다. 

바꾸고자 하는 계열 형태를 누르고 차트종류 변경

 

예) 판매액은 꺾은선으로, 실적은 막대그래프로 표시할 수 있다.

 

그래프가 작거나 수치가 작아서 차트 선택이 어려운 경우

 

차트도구 - 레이아웃 - 현재 선택영역 : 계열 "ㅇㅇㅇ" - 아래 선택 영역 서식 - 계열 옵션 - 보조축 체크

 

계열 차트 누르면 전체 데이터가 선택되는데, 이 때 한 번 더 누르면 데이터 하나(항목)만 선택된다.

 

레이블(계열 위에 나타나는 이름 및 수치)

 

데이터 레이블 - 기타 - 레이블 옵션 - 레이블 내용

 

차트 영역, 그림 영역 구분할 것

 

차트에서 원하는 부분 데이터만 차트화하기

 

1. F11로 새 차트시트 생성

2. 차트도구 - 디자인 - 데이터그룹 - 데이터 선택 

3. 이름 설정하고 값부분에는 그 값이 되는 데이터의 셀들 중 원하는 데이터 셀만 선택해준다

4. 범례 항목(계열)에 차트로 나타내고자하는 데이터를 모두 추가한 후

5. 가로(항목) 축 레이블 [편집]을 항목 레이블을 채운다

 

원형 차트는 한가지 계열만 나타낼 수 있다.

차트 우클릭 - 차트 영역 서식에서 차트 모양을 디테일하게 수정할 수 있다.

 

각 영역은 더블클릭하면 서식을 지정할 수 있다.

축 서식을 지정할 때 축 옵션에서 간격을 조정할 수 있고 

표시형식에서 회계 - 없음 으로 지정해주면 0점을 -으로 표기할 수 있다.

 

 

18. 차트2

 

완만한 꺾은선 그래프는 그래프 종류가 아니라 데이터 계열 서식의 선 스타일 하단 완만한 선을 체크한다

 

데이터 계열 겹치기는 데이터 계열 서식의 계열 옵션에서 설정가능

 

데이터 계열 위치를 열로 변경하시오 = 행/열 전환

 

계열 제거는 계열을 선택할 뒤 delete 해주면된다. 해당 범례는 자동삭제됨.

 

보조축을 설정하면 (일반적으로)오른쪽에 보조축이 생성된다.

 

 

 

시험전에 실습 해보기

 

 

19. 프로시저 : 명령문 집합

 

엑셀에서는 VBA //비주얼 베이직

 

왜인진 모르겠는데 여기서부터는 함수 작성할 때 접근제한자를 퍼블릭으로 안하고 프라이빗으로 함 

 

개발도구 탭 - 컨트롤 그룹 - 디자인 모드 

 

프로시저 수정

 

 

 

 

 

 

 

프로시저.xlsm  (자료현황 프로시저 코드)

 
Private Sub cmd등록_Click()
If txt제품명 = "" Then
    MsgBox "제품명을 입력하시오."
ElseIf txt수량 = "" Then
    MsgBox "수량을 입력하시오."
ElseIf txt단가 = "" Then
    MsgBox "단가를 입력하시오."
ElseIf cmb결재형태 = "" Then
    MsgBox "결재형태를 입력하시오."
Else
기준행위치 = [b3].Row
기준범위행수 = [b3].CurrentRegion.Rows.Count
입력행 = 기준행위치 + 기준범위행수
Cells(입력행, 2) = CDate(txt판매일자) //입력행의 2열에 
Cells(입력행, 3) = txt제품명 //3열에
Cells(입력행, 4) = txt수량
Cells(입력행, 5) = txt단가
Cells(입력행, 6) = Format(Val(txt수량) * Val(txt단가), "currency") //숫자 + 통화방식으로 포맷 설정
Cells(입력행, 7) = cmb결재형태
 
//모두 입력되었을 시 자료가 비어있는 행의 시작 행에 입력한 자료를 채워넣고
//변수를 초기화
 
txt제품명 = ""
txt수량 = ""
txt단가 = ""
cmb결재형태 = ""
End If
 
 
 
 
End Sub
 
Private Sub cmd조회_Click()
기준행위치 = [b3].Row
기준범위행수 = [b3].CurrentRegion.Rows.Count - 1 //자료가 입력돼있는 연속된 영역을 의미
입력행 = 기준행위치 + 기준범위행수
txt판매일자 = Cells(입력행, 2)
txt제품명 = Cells(입력행, 3)
txt수량 = Cells(입력행, 4)
txt단가 = Cells(입력행, 5)
End Sub
 
Private Sub cmd종료_Click()
Unload Me // 현재 작성중인 폼을 닫는다. Me는 This와 같이 현재 객체 쯤으로 보면 될듯
End Sub
 
Private Sub lst제품목록_Click()
    txt제품명 = lst제품목록 //제품목록은 리스트인데 자동으로 클릭한 이름의 오브젝트를 대입해주나보다
End Sub
 
Private Sub txt판매일자_Change()
 
End Sub
 
Private Sub UserForm_Click()
 
End Sub
 
Private Sub UserForm_Initialize() //창을 열자마자 실행되는 서브함수 init
txt판매일자 = Date
lst제품목록.RowSource = "i4:i13"
cmb결재형태.AddItem "현금"
cmb결재형태.AddItem "카드"
cmb결재형태.AddItem "어음" //도메인에 현금, 카드, 어음 추가
End Sub
 

 

 

20. 분석/기타작업

 

 

 

 

 

 

모의고사 몰랐던부분 정리