[E-TEST 엑셀 벼락치기] 1시간안에 정리하는 EXCEL

데이터 입력과 수식 작성하기

★ 리본메뉴가 없는 경우, [Ctrl + F1]을 눌리면 리본 메뉴가 나타남 ★ 엑셀에서 수식을 입력할 때는 반드시 맨 앞에 '='을 입력한 후 함수명을 입력 ★ 수식 입력 후 [Tab]키를 눌리면 괄호가 쳐진다! ★ 셀의 오른쪽 모서리 부분을 드래그 할 경우 연속적으로 값 형성 가능

1. F3셀에 '최고와 차', G3셀에 '비고', G2셀에 '(단위:천명)'을 입력

  - G2셀은 가로 오른쪽 맞춤으로 지정

해당 셀에 타이핑 후, G2셀은 오른쪽 정렬

 

2. A1셀에 제목을 '인천공항 환승여객 현황'으로 입력하시오.

  1) A1~G1셀을 병합하고 가로 가운데 맞춤으로 지정

A1~G1셀  블록 설정 후, 리본메뉴의 '홈-맞춤'에서 병합하고 가운데 맞춤 선택

  2) 글꼴은 궁서체 글꼴 크기는 15, 글꼴 스타일은 굵게 지정

해당 셀을 선택하거나 글자 블록 설정 후, 리본메뉴의 '홈-글꼴'에서 글꼴 설정

 

3. F4~F19셀에 최고와 차를 구하시오

  1) 반드시 MAX, ROUNDDOWN 함수를 모두 이용해 구하시오.

  - 최고의 차 =(각 구분의 2004년부터 2007년까지의 최고값 - 각 구분의 2007년 값)

MAX는 최대값을, ROUNDDOWN은 버림을 의미하는 함수 =ROUNDDOWN(MAX(각 구분의 2004~2007년 데이터 셀)-각 구분의 2007년 데이터 셀)

잘 나오는 간단한 계산 함수

SUM(범위)	합계
AVERAGE(범위)	평균
MAX(범위)	최대값
MIN(범위)	최소값
MOD(대상, 나눌 수)	매상을 나눌 수로 나눈 후의 나머지를 표시
RANK.EQ(대상, 영역[범위], 순서)	지정한 대상이 지정한 범위에서의 순위를 알려줌   * 영역[범위] : F4를 눌려 절대참조로 변경해줌   * 순서 : 내림차순0  / 오름차순1

# 참조[e-test에서는 절대 참조와 상대참조만 알면됨!]

[F4]키를 활용해 참조 설정 가능

절대 참조  : $B$4

상대 참조  : B4

혼합 참조[열고정/행고정]  : $B4 / B$4

잘 나오는 문자열 함수 

LEFT(대상, 몇 개)	왼쪽에서부터 지정한 갯수만큼 알려줌
RIGHT(대상, 몇 개)	오른쪽에서부터 지정한 갯수만큼 알려줌
MID(대상, 몇 번째, 몇 개)	지정한 위치에서 부터 지정한 갯수만큼 알려줌
LEN(대상)	대상의 글자수를 추출
REPLACE(이전 텍스트, 몇번째 자리를 바꿀지, 몇자 바꿀지, 바뀐 단어)	지정한 위치에서부터 지정한 갯수만큼 지정한 단어로 변경

★ 문자열 함수는 " "로 묶어주어야 한다.

★ 공백도 반드시 " "로 묶어주어야 한다.

 

잘 나오는 ROUND 함수

ROUND(대상)	반올림[5부터]
ROUNDUP(대상)	올림[1부터]
ROUNDDOWN(대상)	버림[1~9를 전부 버림]

 

  2) 반드시 주어진 수식으로 구하고, 구한 값을 일의 자리에서 버림하여 십의 자리까지 나타내시오

엑셀에서의 자리 수
소수점 셋째자리	3
소수점 둘째자리	2
소수점 첫째자리	1
일의 자리 정수	0
십의 자리	-1
백의 자리	-2
천의 자리	-3

 

4. G4~G19셀 부분의 비고를 구하시오.

  1) 반드시 AVERAGE, IF, RANK.EQ, RIGHT 함수를 모두 이용해서 구하시오.

  2) 반드시 아래 주어진 조건에 따른 참과 거짓의 값으로 나타내시오.

  - 조건 : 각 구분의 오른쪽 두 자리 문자가 '전체'인 경우

  - 참 : 2007년을 기준으로 각 구분의 내림차순 순위

  - 거짓 : 각 구분의 2004년부터 2007년까지의 평균

IF 함수를 활용해서 구하는 문제 =IF(RIGHT(해당 구분의 셀 선택,2)="전체",RANK.EQ(해당 구분의 2007년 셀, 각 구분의 2007년 셀 전부 선택,0),AVERAGE(해당 구분의 2004~2007년 데이터 셀)

자주 나오는 함수

IF(조건, 참, 거짓)	조건에 만족하면 참 값을 만족하지 않으면 거짓 값을 출력   * 조건 : 대상, 비교연산자, 비교값으로 구성             AND(조건1,조건2...)는 모든 조건을 만족해야 가능             OR(조건1, 조건2...)는 하나의 조건만 만족하면 가능
CHOOSE(대상, 1일 때 나타낼 값, 2일 때 나타낼 값....)	대상의 값이 1일 때, 2일 때.... 나오는 값들을 지정 가능
FIND("찾을 텍스트", 대상)	지정한 텍스트가 대상에 있는지 알려줌
ISERR(대상)	대상이 오류인지 아닌지 판단 후 논리값 변환   * #N/A를 제외한 오류값이면 TRUE를 출력

비교연산자

>	크다, 초과
>=	크거나 같다, 이상, 이후
<	작다, 미만
<=	작거나 같다, 이하, 이전
=	같다.

 

 

 

서식 지정하기

1. 표(A3~G19) 안의 글꼴은 굴림체, 글꼴 크기는 10으로 지정하시오.

해당 셀을 선택하거나 글자 블록 설정 후, 리본메뉴의 '홈-글꼴'에서 글꼴 설정

2. A3~A19셀은 가로 균등분할(들여쓰기) 맞춤으로 지정하고, B3~G3셀, F4~G19셀은 가로 가운데 맞춤으로 지정하시오.

A3~A19셀 블록 설정 후 마우스 오른쪽을 클릭해 '셀서식-맞춤-가로'에서 균등분할(들여쓰기) 선택 B3~G3셀, F4~G19셀은 가운데 정렬

3. A열의 열 너비는 8, B~E열의 열 너비는 9로 지정하시오.

A열을 마우스 오른쪽 클릭해서 '열 너비' 선택하면 열 너비 설정 가능 B~E열 블록 설정 후 마우스 오른쪽 클릭해서 '열 너비' 선택하면 열 너비 설정 가능

4. A3~G3셀의 글꼴 스타일은 굵게로 지정하시오.

A3~G3셀 블록 설정 후 [Ctrl + B]를 눌림

5. B4~C19셀의 수치는 숫자 표시형식을 이용해 세 자리마다 콤마가 나타나고, 음수인 경우 빨강색으로 1,234로 나타나도록 지정하고, D4~E19셀의 수치는 사용자 지정 표시형식을 이용해 세 자리마다 콤마가 나타나고, 999미만인 경우 수치 앞에 빈 열 폭 만큼 '*'이 나타나도록 지정하시오.

해당 셀 선택 후, 마우스 오른쪽을 클릭해 '셀서식-표시형식'에서 지정 숫자 표시 방식은 '숫자'에 들어가서 설정   - 1000단위 구분기호(,) 사용에 체크하면 세자리마다 콤마가 나타남   - 음수의 경우 원하는 표시 형식 선택 가능 [<999]**#,##0;#,##0]

사용자 지정 표시형식

[조건] _* 참일때 형식 ; 거짓일때 형식	[조건]	대상, 비교연산자, 비교값    * 이미 블록을 설정했으므로 대상을 기입할 필요없음
	_*	_에 해당하는 문자 반복 ex1> ** : *반복 ex2> @* : @반복
	참일때 형식 거짓일 때 형식	세자리마다 콤마 : #,##0
		소수점 첫째자리 : 0.0
		세자리마다 콤마 + 소수점 첫째자리 : #,##0.0

6. E4~E19셀의 수치는 조건부 서식을 이용해 393이하인 경우 글꼴 스타일이 굵게 나타나도록 지정하시오 [단, 수식을 이용할 경우 감점]

해당 셀 블록 설정 후 리본메뉴의 '홈-스타일-조건부서식-새규칙'을 눌러 '다음을 포함하는 셀만 서식 지정 눌린 후 설정 서식을 눌러 해당 서식 지정

7. 표(A3~G19셀) 윤곽선은 가장 굵은 선, 표 안쪽 세로선은 실선, A3~G3셀의 아래 선은 가장 굵은 선이 나타나도록 지정하시오.

해당 셀 블록 설정 후, 리본메뉴의 '홈-글꼴-테두리-다른 테두리'에서 설정

 

 

 

차트작성과 데이터베이스 기능 사용하기

1.자동 필터를 이용해 '구분'의 값이 '전체'를 포함하는 경우인 자료를 추출하고, 추출한 상태를 복사하여 A21셀부터 붙여넣으시오.(단, 추출 후 반드시 자동 필터 상태를 유지하시오.)

블록 설정한 후, 리본메뉴의 '데이터-필터'를 눌리면 자동 필터 설정 ▼를 눌린후 '텍스트 필터'나 '숫자 필터' 선택 후 해당 조건 선택 필터가 적용된 상태에서 표를 복사하여 A21셀에 붙여넣기

 

2. 차트를 작성하시오.[차트는 반드시 지정상태확인이 가능해야하며 차트를 두 개 이상 작성하거나 그림, 외부개체로 입력되면 감점]

  1) 붙여 넣은 자료 중 '최고의 차'와 '비고'를 제외한 자료를 이용해 차트를 작성

  2) 차트 종류는 '3차원 누적 세로 막대형', 차트 스타일은 '스타일 2'로 지정

  3) 작성한 차트 이동 위치는 '새 시트(S)'에 삽입

  4) 작성한 차트가 있는 시트명은 '000(응시자 본인의 이름)'으로 입력

제외할 영역을 제외하고 블록 설정한 후, F11클릭 리본 메뉴의 '차트도구 디자인-종류-차트종류변경'에서 해당 차트 종류 선택 리본 메뉴의 '차트도구 디자인-차트스타일'에서 해당 차트 스타일 선택 F11로 차트를 작성할 경우 새 시트에 생성되므로 시트명을 더블클릭해서 이름만 변경해주면 됨

  5) 차트 제목은 [차트 도구]-[디자인]메뉴 [차트 레이아웃] 그룹의 '레이아웃 1'을 '환긍여객 현황'으로 입력하고, 테두리 색은 '실선', 그림자는 미리 설정의 '바깥쪽, 오프셋 대각선 오른쪽 아래'를 지정

리본 메뉴의 '차트도구 디자인-차트 레이아웃'에서 레이아웃 지정 제목 도형을 더블 클릭하면 나오는 '차트 제목 서식'의 '테두리 색'에서 테두리 색 설정 '그림자'에서 그림자 설정

  6) 기본 세로 축 옵션의 '값을 거꾸로'로 지정하고, 기본 가로 눈금선은 '없음'으로 지정

세로 축 선택 후 마우스 오른쪽을 클릭해 '축서식-축옵션'에서 값을 거꾸로

  7) 데이터 계열 서식의 계열 옵션 - 간격 너비를 60%로 지정

데이터 계열(막대) 선택 후 마우스 오른쪽 버튼을 선택해 '데이터 계열 서식-계열옵션'에서 간격 너비 설정

  8) 3차원 회전의 회전은 X10˚,Y10˚로 지정

차트 선택 후, 마우스 오른쪽 버튼을 클릭해 '3차원 회전'으로 들어가 각도 설정

  9) 'KAL_전체' 계열의 데이터 레이블 값이 나타나도록 지정

해당 데이터 계열 선택 후, 마우스 오른쪽을 클릭해 데이터 레이블 추가

  10) 차트 영역의 상단 오른쪽에 [차트형태]와 같이 텍스트 상자를 이용하여 '(단위:천명)'을 입력

리본메뉴의 '삽입-일러스트레이션-도형-텍스트상자' 선택 후, 빈 곳을 선택하고 타이핑

  11) [차트형태]와 같이 범례가 나타나도록 지정

범례가 나타나 있으므로 별도 지정할 필요없음!

 

3. 표(A1~E17셀)를 이용해 피벗 테이블을 작성하시오.

  1) 아래 조건으로 피벗 테이블을 작성하시오.

  - 피벗 테이블 보고서 작성 위치 : 새 워크시트

  - 피벗 테이블 레이아웃

    행 레이블 : 지역, ∑값

    ∑ 값 : 2005년, 2006년, 2007년 (함수:평균)

  - 시트명은 '피벗'으로 입력

리본메뉴의 '삽입-표-피벗테이블'에서 생성 후 행레이블 값에 지역을 드래그 ∑ 값에 2005년, 2006년, 2007년 드래그 후 함수를 평균으로 변경 열 레이블에 생성된 ∑ 값을 행 레이블로 드래그 시트명 더블 클릭 후 시트명 변경

 

4. 표를 이용해 부분합을 작성하시오[부분합 결과는 열 너비를 조절하지 않아도 됨]

  1) 아래 조건으로 부분합을 구하시오.

  - 정렬 : 정렬 기준은 '구분', '오름차순'으로 지정

  - 그룹화할 항목 : 구분

  - 사용할 함수 : 평균

  - 부분합 계산 항목 : 2005년, 2006년, 2007년

블록설정 후 리본메뉴의 '데이터-정렬 및 필터-정렬'을 눌러 정렬 설정 리본메뉴의 '데이터-윤곽선-부분합' 클릭해서 부분합 설정

 

5. 표(J1~J17셀)를 이용해 텍스트 나누기를 작성하시오.

  1) 아래 조건으로 텍스트 나누기를 작성하시오.

  - 원본 데이터 형식 : 너비가 일정함

  - 열 구분선 : 4개를 지정하여 5열로 나눔[구분선 지정위치 : 6, 10, 14, 21]

  - 열 데이터 서식 : 세번째 열은 열 가져오지 않음[건너뜀] 지정

J1~J17셀 블록 설정 후, 리본메뉴의 '데이터-데이터 도구-텍스트 나누기'클릭 너비가 일정함 선택 후 다음으로 넘기면 해당 위치를 클릭하면 선이 생성됨 ★ 선을 더블 클릭하면 없어짐 ★ 선을 드래그하면 이동 가능 세번째 열 선택한 후 열 가져오지 않음(건너뜀) 선택

 

 

 

데이터 분석기능 사용하기

1. 목표값 찾기를 이용해 표(A1~E10셀)의 편수 합계의 평균이 11,500이 되도록 여객처리의 2007년 값을 구하시오

  1) 아래 조건으로 목표값 찾기를 구하시오

  - 수식 셀 : E10(편수 합계 평균)

  - 찾는 값 : 11,500

  - 값을 바꿀 셀 : D7(여객처리 2007년)

목표값과 시나리오 시트에서 해당 영역 블록 설정 후, 리본메뉴의 '데이터-데이터도구-가상분석-목표값 찾기'선택 후 설정

2. 표(H1~L10셀)을 이용해 편수 합계의 2007년(K10)을 위한 시나리오를 작성하시오.

  1) 시나리오 이름 : 유형1, 유형2

  2) 변경 셀 : K5, K6, K7 셀

변경 셀	유형1 변경 값	유형2 변경 값
K5	2.00	3.00
K6	0.10	0.20
K7	0.05	0.10

  3) 보고서 종류 : 시나리오 요약

목표값과 시나리오 시트에서 해당 영역 블록 설정 후, 리본메뉴의 '데이터-데이터도구-가상분석-시나리오 관리자'선택 후 설정 시나리오 추가 선택 후, 시나리오 이름 작성 후 확인을 눌려 변경 셀에 해당하는 값 입력 후 확인 클릭 '표시'가 아닌 '요약'을 눌린 후 결과 셀에 문제에 주어진 셀[K10] 기입

 

3. 시트의 순서는 반드시 아래와 같이 하시오.[반드시 지정된 시트만 있어야 함]

주어진 순서대로 시트 설정