Cover_Image

Cover_Image

일러두기

 

이 전자책은 독자가 사용하는 단말기(PC, 테블릿 PC, 스마트폰, 전자책 전용 단말기 등)와 전자책을 구매한 서점의 뷰어 프로그램에 따라 다르게 보일 수 있습니다. 전자책 실행 상의 기술적인 문제는 전자책을 구매한 서점에 문의하세요.


엑셀

The cakewalk Series - EXCEL TIPBOOK

 

초판 발행 • 2018년 5월 30일

ePub 전자책 발행 • 2018년 7월 2일

 

지은이 • 김철

발행인 • 이종원

발행처 • (주) 도서출판 길벗

출판사 등록일 • 1990년 12월 24일

주소 • 서울시 마포구 월드컵로 10길 56(서교동)

대표 전화 • 02) 332-0931 | 팩스 • 02) 323-0586

홈페이지www.gilbut.co.kr | 이메일gilbut@gilbut.co.kr

 

기획 및 책임 편집 • 박슬기 (sul3560@gilbut.co.kr) | 표지 디자인 • 장기춘 | 전자책 기획 및 책임 편집 • 김지원 | 전자책 제작 • 파인북스

 

 

ISBN 979-11-6050-516-0 05000


작가의 말

 

 

“엑셀은 가장 똑똑한 데이터 관리&분석 프로그램!”

아직도 엑셀을 제대로 사용할 줄 모른다면 이 책을 꼭 읽어야 합니다.

 

필자는 엑셀 강의를 시작할 때마다 “엑셀은 데이터 분석 프로그램입니다!”라는 말을 꼭 합니다. 따라서 필자는 엑셀을 워드처럼 편집 프로그램으로 쓰는 것이 아닌 데이터를 관리하고 분석하여 결과를 도출하는 방법과 활용에 대해 강의를 하고 있는 것이죠. 그런데 대부분의 수강생들은 셀 서식 변경이나 표시 형식, 표 적용과 같은 사소한(엑셀 데이터 분석에 큰 영향을 주지 않는) 것들을 질문합니다. 이러한 질문을 통해 필자는 많은 사람들이 분석을 제대로 해보기도 전에, 데이터를 정리하느라 작업 시간을 허비하고 있다는 사실을 깨닫게 되었습니다.

따라서 필자는 독자들이 데이터 분석에 시간을 투자할 수 있도록 데이터를 쉽고 빠르게 정리할 수 있는 『엑셀 팁북』을 집필하게 되었습니다. 또한 엑셀을 쓰고는 있지만, 데이터 분석의 최강자라고 할 수 있는 엑셀을 제대로 경험해 보지 못한 사람들에게 엑셀을 사용하면서 범할 수 있는 다양한 실수(변수)의 확률을 줄이고, 업무 효율까지 높여줄 수 있는 팁 중의 팁을 엄선하여 이 책 한 권에 담아 두었습니다.

점차 사용하는 데이터는 늘어나 빅데이터로 커지는 상황에서 필자가 알려준 엑셀 팁만 제대로 익혀둔다면, 이 책을 접한 독자들은 좀 더 많은 데이터 분석 시간을 보장받을 수 있습니다. 필자에게는 미처 담지 못한 고급 팁과 노하우가 여전히 가득합니다. 이 책을 통해 엑셀에 대한 자신감이 붙을 여러분들을 위해 엑셀 프로의 고급 기술만 모은 책을 준비하여 다시 만날 수 있는 기회를 갖고 싶습니다. 여러분들의 칼퇴에 건투를 빕니다!

 

감사의 뜻을 전하며…….

얼마 전 우연히 보게 된 필자 책의 후기 중에서 눈에 띄는 것이 있었습니다. ‘김철 저자의 책이기에 고민 없이 선택했다.’라는 한줄 서평이었습니다. 짧은 글이었지만, 부담과 함께 한편으로 사명감도 생겼습니다. 그래서 그분을 포함한 많은 독자들을 실망시키지 않는 책으로 보답하겠다고 생각하며 집필하였습니다.

부디 『엑셀 팁북』이 책장 구석에서 잠들어 있지 않고 필요할 때마다 항상 꺼내 볼 수 있는 유용한 책이 되길 바랍니다. 또한 작성된 팁을 반드시 채득해서 데이터 분석에 투자하며 생산성을 높이는 계기가 되었으면 합니다.

마지막으로 이 책을 집필하며 고생한 배호종, 박슬기 님께 지면을 통해 감사의 말씀을 전하며, 제 옆에서 항상 마르지 않는 샘물처럼 힘이 되어주는 아내와 하연이, 서연이 두 딸에게 고맙다는 말을 전합니다.

 

마이크로소프트 엑셀 MVP 김철


미리보기

 

 

“단, 한 권으로 해결한다!”

 

기본&실무 완벽 대응

국내 최고의 엑셀 전문가, 마이크로소프트 엑셀 MVP가 실무에 꼭 필요한 팁만 엄선하여 제대로 알려줍니다. 현업에서 써보면 무릎을 탁 치게 되는 엑셀 실무 활용 100%의 핵심 팁을 익혀보세요!

 

 

엑셀 핵심 팁

팁 중의 팁, 독자들이 가장 많이 묻고, 가장 많이 사용하며 업무 효율이 가장 높은 팁만 엄선하여 제공

 

모든 버전 사용 가능

엑셀 2007~2016 버전 뿐 아니라, Office 365 환경에서도 쓸 수도록 엑셀 모든 버전 대응

 

예제 및 완성 파일

예제를 따라할 수 있도록 준비된 예제 파일과 결과물을 비교할 수 있는 완성 파일 제공

 

 

“엑셀에 대한 부담을 버려라!”

 

현장 밀착&검색 강화

작고 가볍지만, 실무에는 강한 엑셀 팁북! 데이터 분석 및 관리부터 업무 자동화까지 단숨에 익힐 수 있습니다. 또한 원하는 내용을 단숨에 찾을 수 있는 검색 기능으로, 엑셀의 각종 문제에 대한 해결법을 빠르게 제시하여 독자의 시간을 아껴줍니다!

 

 

빠른 검색

분야별 탭을 통해 원하는 엑셀 기능을 빠르고 쉽게 검색

 

PLUS

실습을 따라하면서 알아두면 좋은 유용한 내용 소개

 

NOTE

중·고급 사용자로 발돋움할 수 있는 실무 노트 제공


실무 목차

 

빠른 검색, 시간 절약! 원하는 내용을 단숨에 찾을 수 있는 검색 장치를 제공합니다. 분야별 실무 키워드를 통해 현업에서 써보면 무릎을 탁 치는 놀라운 팁을 순식간에 익힐 수 있으며, 필요할 때마다 바로 바로 찾아 쓸 수 있습니다.

 

 

⋮옵션 설정⋮

실무 키워드

링크

숫자로 바뀐 열 번호를 영어로 수정

Part1_001

리본 메뉴에 [개발 도구] 탭 나타내기

Part1_002

직급 순서에 따른 데이터 정렬

Part1_003

선택한 셀의 통계량 간편 확인

Part1_004

데이터에서 빈 셀이 포함된 행 삭제

Part1_005

그룹별 자세히 버튼으로 옵션 설정

Part1_006

 

⋮문자 입력⋮

실무 키워드

링크

자주 사용하는 문자 등록

Part1_007

열 너비 조정해서 한 화면에 나타내기

Part1_008

행/열 삽입 못하게 보고서 양식 보호

Part1_009

드롭다운 목록으로 데이터 쉽게 추가

Part1_010

하나의 셀에 대각선 긋기

Part1_011

한영 변환 및 전각 반각 문자 변환

Part1_012

숫자로 작성된 금액을 한글로 변환

Part1_013

보이지 않는 차트 다시 나타내기

Part1_014

 

⋮빠른 설정⋮

실무 키워드

링크

입력한 데이터에 자동 순번 매기기

Part1_015

매월 말일 날짜만 입력

Part1_016

원하는 시트로 빠르게 이동

Part1_017

자주 쓰는 파일을 고정

Part1_018

빠른 실행 도구로 손쉽게 메뉴 관리

Part1_019

이름 정의로 빠르게 범위 선택

Part1_020

 

⋮셀 서식⋮

실무 키워드

링크

백만 단위 숫자로 빠르게 수정

Part1_021

큰 금액은 단위를 넣어 간단하게 표시

Part1_022

24시간을 초과한 시간 포함해서 계산

Part1_023

값이 0인 셀은 표현하지 않기

Part1_024

근무일지의 요일 자동 변경

Part1_025

조건에 맞는 데이터만 시각화

Part1_026

실적 증감을 구분하기 쉽게 시각화

Part1_027

 

⋮수식 입력⋮

실무 키워드

링크

수식 셀 값을 바꿔도 결과가 같을 때

Part1_028

입력한 수식이 셀에 그대로 나타날 때

Part1_029

수식 입력 시 빠르게 범위 지정

Part1_030

빈 셀에 수식 빠르게 채우는 방법

Part1_031

이름 정의를 수식에 간편하게 적용

Part1_032

셀 서식 변화 없이 수식 일괄 적용

Part1_033

 

⋮키 조작⋮

실무 키워드

링크

행 방향으로 같은 수식 채우기

Part2_035

서식 변경 없이 데이터 채우기

Part2_036

빅데이터에서 연속된 데이터 채우기

Part2_037

셀 크기에 맞게 사진 크기 조정

Part2_038

 

⋮표 기능⋮

실무 키워드

링크

거래처가 자동 추가되는 데이터베이스

Part2_039

표 데이터를 선택적으로 입력

Part2_040

매출이 자동 변경되는 보고서 작성

Part2_041

수정이 편리한 결재란 작성

Part2_042

선택한 범위의 데이터를 일괄 연산

Part2_043

합산 가능한 정상 숫자로 변환

Part2_044

 

⋮이동 및 찾기⋮

실무 키워드

링크

변경된 데이터만 찾아서 표시

Part2_045

변경된 기록 전체를 찾아 표시

Part2_046

파일 용량 줄이기 위한 개체 삭제

Part2_047

함수로 입력하지 않은 셀 찾기

Part2_048

신청 수량이 변경된 데이터만 찾기

Part2_049

같은 서식이 적용된 데이터만 찾기

Part2_050

특정 값만 일괄 변경

Part2_051

여러 시트에서 특정 값만 일괄 변경

Part2_052

 

⋮시각화⋮

실무 키워드

링크

값의 크기를 데이터 막대로 표시

Part2_053

값의 크기에 따른 평점 아이콘 표시

Part2_054

빈 셀에 간단한 추이 곡선 표시

Part2_055

투명한 배경 이미지 제작

Part2_056

 

⋮페이지 설정⋮

실무 키워드

링크

행/열 머리글을 포함시켜 인쇄

Part3_063

오류가 있는 셀은 출력하지 않기

Part3_064

인쇄 방향 설정

Part3_065

파일 경로와 파일명 넣어 인쇄

Part3_066

인쇄 페이지 직접 설정

Part3_067

워터마크로 인쇄되는 공문서 제작

Part3_068

 

⋮고급 필터⋮

실무 키워드

링크

특정 값이 포함된 데이터 필터링

Part3_069

특정 기간에 속하는 데이터만 필터링

Part3_070

필터링한 데이터 다른 시트로 옮김

Part3_071

다중 조건에 맞는 데이터 필터링

Part3_072

논리합 조건까지 필터링

Part3_073

조건과 일치하는 데이터만 필터링

Part3_074

 

⋮피벗 테이블⋮

실무 키워드

링크

거래처별 시트로 거래 명세서 분리

Part3_075

클릭만으로 매출 금액 집계

Part3_076

슬라이서로 데이터 분석 툴 제작

Part3_077

통계 자료의 원본을 빠르게 필터링

Part3_078

지정한 기간만 매출 보고서로 작성

Part3_079

피벗 테이블의 셀 참조로 보고서 작성

Part3_080

반응형 매출 보고서 작성

Part3_081

 

⋮차트 보고서⋮

실무 키워드

링크

데이터가 빈 셀도 선으로 연결

Part3_082

비교 분석용 반원 차트

Part3_083

차트에 계열 추가해서 매출 분석

Part3_084

 

⋮실무 키워드⋮

실무 키워드

링크

도움말 빨리 찾기

Part3_085

함수 빨리 입력하기

Part3_086

함수 삽입 버튼 활용

Part3_087

회원번호로 구매 내역 빠르게 조회

Part3_088

근속 기간을 산출하는 DATEDIF 함수

Part3_089

INDEX-MATCH 수식으로 값 찾기

Part3_090

자동 순번 만들기

Part3_091

조견표 참조로 단가와 합계액 산출

Part3_092

성적의 절대평가, 상대평가

Part3_093

조견표를 지우고 결과 나타내기

Part3_094

 

⋮기타⋮

실무 키워드

링크

오류도 날아간 파일 복구

Part1_034

사용자가 지정한 입력 값만 받기

Part2_057

수식이 입력된 셀 보호

Part2_058

암호 설정으로 파일 보호

Part2_059

이름 정의로 쉬운 수식 완성

Part2_060

크로스탭 보고서 양식 빠르게 제작

Part2_061

크로스탭 보고서의 행/열 변경

Part2_062

데이터를 색으로 다시 정렬

Part3_095

한 행씩 빠르게 일괄 삽입

Part3_096

병합된 데이터 해제

Part3_097

여러 시트에 분산된 데이터 통합

Part3_098

빠른 채우기로 주민번호 변환

Part3_099

가상 분석으로 예산 역추산

Part3_100


팁 중의 팁, 엑셀에 꼭 필요한 "핵심 TIP"만 모았다!

 

 

마이크로소프트 MVP인 저자가 다년간 다양한 사람들, 다양한 공간(온/오프라인)에서 강의한 데이터를 집계하여 ① 가장 많이 묻는 질문, ② 가장 많이 사용하는 기능, ③ 가장 호응이 높았던 기능만 모아 핵심 팁으로 구성하였습니다.

팁 중의 팁, 핵심 팀만 알아도 엑셀을 사용하는 환경이라면, 언제, 어디서든 유용하게 써 먹을 수 있습니다!

 

 

‘핵심 팁’만 알아도 파워포인트 마스터

빠른 찾기

01

옵션 설정

리본 메뉴에 [개발 도구] 탭 나타내기

Part1_002

02

옵션 설정

데이터 중에서 빈 셀이 포함된 행 삭제하기

Part1_005

03

문자 입력

행/열 삽입을 못하도록 보고서 양식 간단히 보호하기

Part1_009

04

빠른 설정

매월 말일 날짜만 입력하기

Part1_016

05

셀 서식

24시간을 초과한 시간까지 포함해서 계산하기

Part1_023

06

셀 서식

전년대비 실적의 증감을 쉽게 구분하도록 시각화하기

Part1_027

07

수식 입력

이름 정의를 활용해서 수식에 간편하게 적용하기

Part1_032

08

파일 복구

작업 중 갑작스런 오류로 엑셀 프로그램이 닫혔을 때 작업했던 파일 복구하기

Part1_034

09

표 기능

거래처가 자동으로 추가되는 데이터베이스 만들기

Part2_039

10

표 기능

유효성 검사로 표 데이터를 선택적으로 입력하기

Part2_040

11

이동 및 찾기

순서가 뒤죽박죽인 데이터에서 변경된 내용만 찾아 표시하기

Part2_045

12

이동 및 찾기

데이터에 입력된 특정 값을 일괄적으로 변경하기

Part2_051

13

시각화

값의 크기에 따라 달라지는 평점 아이콘 표시하기

Part2_054

14

시각화

투명한 배경 이미지 만들기

Part2_056

15

데이터 유효성 검사

사용자가 지정한 최대 입력 값만 받도록 설정하기

Part2_057

16

보호

수식이 입력된 셀을 수정이나 삭제할 수 없게 보호하기

Part2_058

17

중복된 항목 제거

DB를 활용해서 크로스탭 보고서 양식 빠르게 만들기

Part2_061

18

페이지 설정

회사 CI가 워터마크로 인쇄되는 공문서 만들기

Part3_068

19

고급 필터

2개 이상의 다중 조건에 맞는 데이터 필터링하기

Part3_072

20

피벗 테이블

거래 명세서를 거래처별 시트로 분리시켜 작성하기

Part3_075

21

피벗 테이블

슬라이서를 삽입해서 기업용 데이터 분석 툴로 사용하기

Part3_077

22

피벗 테이블

반응형 매출 보고서 작성하기

Part3_081

23

차트 보고서

비교 분석이 쉬운 반원 차트 그리기

Part3_083

24

함수

매출 내역에서 회원번호로 구매 내역 빠르게 조회하기

Part3_088

25

함수

근속 기간을 산출하는 DATEDIF 함수 제대로 사용하기

Part3_089

26

함수

필터가 적용된 데이터의 자동 순번 만들기

Part3_091

27

함수

조견표를 참조해서 매출 단가와 합계액 나타내기

Part3_092

28

정렬

많은 데이터에 빠르게 한 행씩 일괄 삽입하기

Part3_096

29

데이터 통합

여러 시트에 분산 입력한 데이터를 하나로 통합하기

Part3_098

30

목표값 찾기

예산에 맞춘 금액을 가상 분석으로 역추산하기

Part3_100


PART 01
기본에 충실, 제대로 배우는기초 탄탄 입문!

 

 

엑셀의 고급 사용자가 되려면 반드시 짚고 넘어가야 할, 아니 알고 있어야 할 자료들로만 준비했습니다. 환경 설정부터 키 조작, 시트 조작까지 업무 효율을 높이고 생산성을 높일 수 있도록 기초를 탄탄히 다지는 시간이 될 것입니다.

잊지 마세요! 기초를 잘다지면 엑셀만큼 발전 속도가 빠른 프로그램도 드물다는 것을…….


2007201020132016Office 365

 

옵션 설정

TIP 001 숫자로 변경된 열 번호를 알파벳으로 수정하기

 

 

알파벳으로 되어 있던 열 번호가 갑자기 숫자로 변경되는 경우가 있습니다. 이때 숫자로 변경된 열 번호를 다시 알파벳으로 변경하는 환경 설정 방법을 알아보겠습니다.

 

예제 파일 | 빈 화면에서 시작하세요!

 

01 다음 그림을 보면 A, B, C, D로 되어 있어야 할 열 번호가 숫자로 변경되어 있습니다. 이렇게 되면 셀 주소도 [이름 상자]와 같이 R1C1으로 변경됩니다. 이때 숫자 열 번호를 다시 알파벳 열 번호로 변경하려면 [파일] 탭 - [옵션]을 클릭합니다.

 

02 [Excel 옵션] 창 왼쪽의 [수식] 탭을 선택합니다. 그런 다음 [수식 작업]에서 첫 번째 항목인 [R1C1 참조 스타일]에 체크되어 있으면 알파벳이 숫자로 변경됩니다. 그러므로 해당 옵션의 체크를 지우고 [확인]을 클릭합니다.

 

03 셀 편집 화면으로 돌아오면 열 번호가 알파벳으로 변경된 것을 확인할 수 있습니다.


2007201020132016Office 365

 

핵심옵션 설정

TIP 002 리본 메뉴에 [개발 도구] 탭 나타내기

 

 

리본 메뉴의 마지막에 나타나는 [개발 도구]는 VBA 매크로 기록기나 컨트롤을 활용할 때 사용하는 메뉴입니다. 하지만 엑셀을 처음 실행하면 메뉴로 나타나지 않습니다. [개발 도구] 탭을 나타내는 옵션 설정 방법을 알아보겠습니다.

 

예제 파일 | 빈 화면에서 시작하세요!

 

01 엑셀을 실행한 후 [파일] 탭 - [옵션]을 선택합니다.

 

02 [Excel 옵션] 창의 [리본 사용자 지정]을 선택합니다. 오른쪽에 있는 [리본 메뉴 사용자 지정(B)]의 아래쪽 항목 중에서 [개발 도구] 항목을 체크한 뒤 [확인] 버튼을 클릭합니다.

 

03 리본 메뉴의 오른쪽 마지막에 [개발 도구] 탭이 나타난 것을 확인할 수 있습니다.

 

NOTE

2007 버전에서 [개발 도구] 탭 표시하기

2007 버전은 [오피스 단추] - [Excel 옵션]을 실행해야 [Excel 옵션] 창이 나타납니다. 여기에서 [기본 설정] 탭의 오른쪽에 [리본 메뉴에 개발 도구 탭 표시]를 체크하면 [개발 도구] 탭이 나타납니다.

 


2007201020132016Office 365

 

옵션 설정

TIP 003 직급 순서에 따라 데이터 정렬하기

 

 

엑셀 데이터를 정렬할 때 오름차순이나 내림차순이 아닌 사용자가 지정한 순서대로 정렬하는 방법을 알아보겠습니다.

 

예제 파일 | 사용자_지정_정렬_예제.xlsx   완성 파일 | 사용자_지정_정렬_완성.xlsx

 

01 예제 파일을 열고 [파일] 탭 - [옵션]을 선택합니다.

 

02 [고급] 탭을 선택하고 스크롤바를 마지막까지 내리면 [사용자 지정 목록 편집] 버튼이 나타납니다. 해당 버튼을 클릭합니다.

 

03 [사용자 지정 목록] 창을 불러오면 왼쪽에 [사용자 지정 목록]이 나타납니다. [사용자 지정 목록]에는 미리 만들어둔 기본적인 목록들이 들어 있습니다. [새 목록]이 선택된 상태에서 오른쪽의 [목록 항목] 란에 정렬하고 싶은 순서대로 항목을 입력합니다. 직급별로 항목을 작성하고 Enter를 누르면서 필요한 항목을 모두 입력합니다. 사용자가 정렬하고 싶은 모든 직급을 입력했으면 오른쪽의 [추가]를 클릭한 후 [확인]을 클릭합니다.

 

항목을 작성할 때 Enter를 눌러 아래쪽으로 입력해도 되지만 ‘사장, 부사장, 전무’ 등과 같이 직급별로 콤마(,)를 넣어서 입력해도 구분할 수 있습니다.

 

04 시트에 작성된 데이터 중에서 임의의 셀을 선택하고 [데이터] 탭 - [정렬 및 필터] 그룹 - [정렬]을 실행합니다.

 

05 [정렬] 창에서 열 항목의 정렬 기준은 [직급]을 선택하고 정렬 항목의 정렬 기준은 [사용자 지정 목록]을 선택합니다.

 

[정렬] 창에서 [내 데이터에 머리글 표시]가 체크되어 있기 때문에 열 항목의 정렬 기준을 클릭했을 때 시트의 머리글이 항목으로 나타나는 것입니다.

 

06 [사용자 지정 목록] 창이 나타나면 [사용자 지정 목록]에 이미 입력해 둔 정렬 방법을 선택하고 [확인]을 클릭합니다.

 

07 [정렬] 창의 [확인]을 눌러서 정렬을 실행합니다. 시트에서 사용자가 지정한 정렬 방법대로 직급별로 정렬됩니다.


2007201020132016Office 365

 

옵션 설정

TIP 004 선택한 셀의 통계량 간편하게 확인하기

 

 

집계된 자료의 데이터를 임의로 선택해서 선택한 데이터의 기초 통계량을 빠르게 뽑아 보려고 할 때가 많습니다. 이때 선택한 셀의 기초 통계량을 간단하게 확인하는 설정 방법을 알아보겠습니다.

 

예제 파일 | 선택셀의_통계량_나타내기_예제.xlsx 완성 파일 | 선택셀의_통계량_나타내기_완성.xlsx

 

01 예제를 열고 [B6:G10] 셀까지 데이터 범위를 선택합니다. 아래쪽의 상태 표시줄을 보면 선택한 범위의 평균, 개수, 합계 등 기초 통계량이 나타나는 것을 확인할 수 있습니다.

 

02 이때 최대값, 최소값도 추가로 나타내려면 일단 상태 표시줄을 마우스 오른쪽 버튼으로 클릭합니다. [상태 표시줄 사용자 지정] 옵션이 나타나면 [최대값], [최소값]을 각각 클릭해서 선택합니다.

 

03 상태 표시줄을 확인해 보면 선택한 데이터 범위의 통계량에 최대값과 최소값이 추가된 것을 확인할 수 있습니다.


2007201020132016Office 365

 

핵심옵션 설정

TIP 005 데이터 중에서 빈 셀이 포함된 행 삭제하기

 

 

데이터 중에서 빈 셀이 포함된 행 전체를 삭제하거나 빈 셀을 한 쪽 방향으로 삭제해서...