구체적으로도와주는책잘된다! 데이터를자유롭게다루는 우리사무실고수들의업무기술대공개! 된다! 엑셀능력자 데이터시대의엑셀기본서 8 년연속마이크로소프트엑셀 MVP 김철지음
이제구체적으로도움이되는책! 된다! 시리즈를만나보세요. 원하는것을되게만들어드립니다. 된다! 엑셀능력자 Gotcha! Excel Hero 초판 1 쇄인쇄 2016 년 2 월 12 일 초판 1 쇄발행 2016 년 2 월 23 일 지은이 김철펴낸이 이지연펴낸곳 이지스퍼블리싱 ( 주 ) 출판사등록번호 제313-2010-123호주소 서울특별시영등포구당산로 41길 11, SK V1센터 323호대표전화 02-325-1722 / 팩스 02-326-1723 홈페이지 www.easyspub.co.kr / 이메일 service@easyspub.co.kr 기획및책임편집 배호종 (riger303@hanmail.net) 편집진행 김영준 디자인및전산편집 이승현 인쇄소 보광문화사 제본소 정성문화사 영업및강의자료 PPT 문의 이주동 (nlrose@easyspub.co.kr) 잘못된책은구입한서점에서바꿔드립니다. 이책에실린모든내용, 디자인, 이미지, 편집구성의저작권은이지스퍼블리싱 ( 주 ) 과 지은이에게있습니다. 허락없이복제할수없습니다. ISBN 978-89-97390-89-2(13000) 가격 22,000 원
나무심기에가장좋은때는 20 년전이었다. 그다음으로좋은때는바로지금이다. The best time to plant a tree was 20 years ago. The next best time is now. 아프리카속담 African Proverb
머리말 데이터테크놀로지의시대, 엑셀활용의트렌드가달라졌다 엑셀은데이터분석을대표하는도구로서오피스환경에서절대적인위치를차지하고있습니다. 업 무효율과생산성을높이기위해데이터를분석하고활용하는일련의활동이이전보다점점더중요해지 고있기때문입니다. 지난해내한한알리바바그룹의마윈회장역시 세상은 IT 시대에서 DT(Data Technology) 시대 로가고있다 라고말할정도입니다. 그외세계적석학이나수많은경영자들도이구동성으로데이터의 활용과분석의중요성을이야기하고있습니다. 필자는 2008년부터마이크로소프트오피스엑셀 MVP(Most Valuable Professional) 로활동하며여러기업체, 공공기관, 교육기관에서강의를진행했습니다. 그중가장반응이뜨거웠던강의는 비즈니스엑셀, 데이터의활용과분석 이었습니다. 기업대부분은 ERP와같은사내시스템을구축했기때문에직원들은축적된데이터를손쉽게엑셀로다운로드할수있습니다. 즉, 이제는 데이터를어떻게활용하느냐 가직원들의핵심역량으로평가받는시대가된것입니다. 강의를할때마다 어떻게하면사용자가빅데이터로쌓이고있는자료들을더효율적으로활용할수있을까 에대해고민했습니다. 그리고지금까지강의를하며현장에서느낀경험을여러분과나누고싶었습니다. 이책은그렇게탄생했습니다. 데이터를효과적으로분석하고활용하는팁을모아책에담았고점점데이터분석과활용능력이강조되고있는트렌드에부합하도록내용을구성했습니다. 그간의경험과생각을정리하느라책이세상에나오기까지시일은조금걸렸지만책을통해익히는 독자들은필자와는반대로쉽고빠르게데이터활용기술을익힐수있기를바랍니다. 또옆에두고필요 할때마다찾는친한친구같은책이길바랍니다. 책을집필하는동안함께고생하신이지연대표님, 배호종실장님께이지면을빌려고맙단말씀을 전합니다. 아울러늘제게힘을주는집사람과우리두딸김하연, 김서연에게도고맙단말을꼭하고싶 습니다. 늦은밤에머리말을적고있노라니고이자고있는아이들이마에뽀뽀라도해주고싶군요. 김철 bomism@gmail.com
엑셀능력진단차트 출발 ~! 엑셀공부, 나는어디에서출발할까? 자신의수준에맞는학습방법을알면효과적으로엑셀을배울수있습니다. 아래에있는엑셀능력진단차트를통해수준별학습안내를받아보세요. 나의엑셀능력은? 된다! 엑셀능력자 이렇게공부하세요! 셀주소 [A1] 이어디인지안다. NO 내가아는단축키는 Ctrl+C, V 가전부다. YES 당신은다른책을먼저보세요. 아직이책을보기는어렵겠네요. 아쉽지만다른입문서를먼저찾아보세요. YES NO [A1], [$A1], [A$1], [$A$1] 각각의차이를알고있다. NO 당신은엑셀초보자! 기본기가필요하군요. 준비마당은모르는것이없는지훑어보더라도첫째마당은확실히이해할때까지꼼꼼히실습하세요. YES 엑셀을좀써본당신! 조건부서식으로데이터를강조할수있다. NO 피벗테이블을만들수있다. NO 데이터를편집하는방법을배워야겠군요. 시간 이걸리더라도둘째마당부터순서대로배우길 권합니다. 기초가부족하다고느끼면첫째마당 을참고하세요. YES YES VLOOKUP 함수를사용할수있다. NO 엑셀이이미익숙한당신! 더놓은수준의데이터활용기술을배울차례입 니다. 둘째마당부터살피면서자신의업무와연관성이높은기능을우선적으로실습해보세요. YES 당신은이미능력자! 당신은프로직장인이될가능성이충분합니다. 여섯째 ~ 일곱째마당을통해데이터활용방법을중점적으로학습해보세요.
이책의특징 하나! 현장느낌그대로! 실제기업에서쓰는 DB 를이용해실습할수있다 회사에들어가야볼수있는 DB, 회사에서실제사용하는보고서양식을담았습니다. 현장감을 100% 느낄수있 는실습예제파일로학습하세요. 회사에들어가야 볼수있다 그래프도응용이 가능하다 1 만 7 천행이넘는방대한 DB 계획대비매출현황보고서 마우스만사용해 보고서를만든다 조건을만족하는 값을찾는다 스파크라인목표관리보고서 예산에맞는제품찾기 둘! 어떤버전이든지배울수있게배려한책 최신 2016 버전까지! 이책은엑셀 2007 버전부터 2016 버전까지모든사용자가볼수있지만버전에따라기능이조금씩다르거나사 용에제약이있을수는있습니다. 각절이시작할때예제실습이가능한버전을표기하고있으니이를확인하고각자의환경에맞춰학습을준비해보세요. 실습을진행할수있는버전별표기 3 해당버전에서사용할수있음 s 해당버전에서일부기능의제약이있음 7 해당버전에서사용할수없음
셋! 독학부터대학교재까지, 비즈니스에강하다! 기업체, 공공기관, 교육기관에서강의를하며쌓은저자의실전노하우부터실력을한단계더높여줄특별강의 (Special Page) 까지학습자입장에서고민하며탄생한 4 가지학습요소를활용하세요. ❶ 김철쌤의한마디 : MVP 김철저자의노하우공개! ❷ 수식알고넘어가기 : 저절로이해되는상세한수식풀이! ❸ 하위버전에서는 : 하위버전이깔린 PC 를만나도문제없어요! ❹ Special Page : 다른곳에는없는김철샘의특별강의수록! 실습예제다운로드안내이지스퍼블리싱홈페이지에오시면다양한학습지원뿐아니라실습예제파일도한꺼번에내려받을수있습니다. 궁금한점이있다면저자가직접운영하는엑셀전문가클럽을이용해보세요. 전체실습파일한꺼번에내려받기 이지스퍼블리싱홈페이지 www.easyspub.co.kr 자료실 저자운영카페에질문올리기 엑셀전문가클럽 cafe.daum.net/funnyexcel
이책의핵심기능훑어보기 준비마당 첫째마당 둘째마당 엑셀기초, 한시간에끝낸다 화면구성 데이터입력 표와차트 인쇄설정 단축키 프로직장인에게이정도는기본! 연산자와셀참조 표만들기과셀서식 데이터베이스 피벗테이블 ( 기본 ) 슬라이서 ( 기본 ) 우리사무실고수들의보고서작성법 조건부서식 피벗테이블 ( 응용 ) GETPIVOTDATA 함수 데이터유효성검사 스파크라인 셋째마당 넷째마당 보고서의품격을높여주는함수활용법 크로스탭보고서 이름정의 중복된항목제거 VLOOKUP 함수 통계 찾기 날짜함수 마당별핵심기능 원하는정보만골라보는데이터편집의기술 자동 고급필터 COUNTIFS 함수 데이터통합 목표값찾기 조견표작성및활용 다섯째마당 여섯째마당 일곱째마당 질문이필요없는직관적인차트보고서 차트 ( 기본 ): 이중축차트 원형대원형차트 3 차원원형차트 방사형차트 차트 ( 응용 ): 반원차트 / 간트 차트 / 중첩막대형차트 우리팀장님의데이터활용법 슬라이서 ( 응용 ) 피벗테이블보고서필터 손익분기점차트 7 가지엑셀실용팁 엑셀로익히는기획과경영의기술, 데이터분석 Z 차트 ABC 분석 마아코프분석 단회귀분석 다중회귀분석
목차 준비마당 엑셀기초, 한시간에끝낸다 I 알수록득이되는엑셀기초 I-1 나도프로직장인이될수있을까? 17 I-2 엑셀화면적응하기 20 I-3 데이터입력하고수정하기 입력 22 I-4 표와차트로나타내기 편집 29 I-5 인쇄페이지설정하기 출력 37 II 모르면손해보는엑셀단축키 II-1 Ctrl 키를이용한단축키 45 II-2 Shift 키를이용한단축키 51 II-3 Ctrl 키와 Shift 키를조합한단축키 53 첫째마당 프로직장인에게이정도는기본! 01 수식입력의기본 연산자와셀참조 01-1 기본연산자활용법익히기 57 01-2 셀참조로데이터를반복해서사용하기 63 02 데이터입력의기본 표와셀서식 02-1 함수와서식으로데이터구분하기 IF 함수, 셀서식 69 02-2 표에서데이터다루기 표 78 Special 데이터베이스정리, 처음부터이렇게해야편하다! 1. 데이터는항목별로구분해서셀을만든다음 가로로배치하세요 81 2. 데이터베이스를만들때는표형태로입력하세요 81 3. 데이터를입력할때에셀병합은사용하지마세요 82 4. 년, 월, 일은하나의필드에입력해야활용도가높아요 83 5. 데이터를누적해서입력한다면하나의시트에보관하세요 83 6. 데이터입력시에는반드시유효성검사를하세요 84 7. 데이터의동적범위를활용하면누적된수치도참조됩니다 84 8. 데이터와조건이많으면피벗테이블을쓰세요 85 9. 셀서식으로데이터를구분하지마세요 86
목차 03 데이터분석과시각화의기본 피벗테이블과피벗차트 03-1 데이터를요약하고분석해서차트로나타내기 88 Special 엑셀 2013, 2016 엔터프라이즈버전의 특별한기능, 파워뷰알아보기 파워뷰기능으로데이터시각화하기 101 둘째마당 우리사무실고수들의보고서작성법 04 마우스로작성하는속성보고서 04-1 셀에서데이터를시각화하기 조건부서식 111 04-2 원본과연동되는보고서만들기 피벗테이블 139 05 매출집계및소비성향분석보고서 05-1 특정기간동안의매출집계뽑기 피벗테이블 & 데이터유효성검사 147 05-2 소비성향및구매자분포파악하기 피벗테이블 & 조건부서식 154 06 매출추이와목표관리보고서 06-1 기간별로매출을집계하고매출추이나타내기 스파크라인 166 06-2 손쉬운목표관리보고서만들기 스파크라인응용 175
셋째마당 보고서의품격을높여주는함수활용법 07 크로스탭보고서와 SUMIFS 함수 07-1 조건에맞는합계구하기 SUMIFS 함수 183 08 통계함수와 VLOOKUP 함수 08-1 함수의기본이되는통계함수익히기 평균, 반올림 197 08-2 자동으로일일매출일지작성하기 VLOOKUP 함수 203 09 찾기함수와날짜함수 09-1 다양한찾기함수활용하기 MATCH, INDEX, OFFSET 216 09-2 근속기간을쉽게계산하는날짜함수활용하기 DATEDIF 236 넷째마당 원하는정보만골라보는데이터편집의기술 10 자동필터와고급필터 10-1 자동필터활용법 249 10-2 고급필터기초활용법 253 10-3 고급필터응용활용법 266 11 여러시트에작성된데이터를집계하고통합하는방법 11-1 다중시트를참조해월별집계보고서만들기1 피벗테이블 283 11-2 다중시트를참조해월별집계보고서만들기2 통합 288 12 목표값찾기와조견표작성방법 12-1 목표값찾기로제품가격제안하기 295 12-2 조견표를만들어활용하기 299
목차 다섯째마당 질문이필요없는직관적인차트보고서 13 매출분석및스케줄관리를위한차트 13-1 기준이서로다른데이터를한차트에나타내기 이중축차트 309 13-2 차트로프로젝트일정관리하기 간트차트 321 14 점유율을효과적으로비교하는원형차트 14-1 차트안의차트로세부항목을설명하는방법 원형대원형차트 335 14-2 점유율을입체감있게나타내는방법 3차원원형차트 346 14-3 원형차트를반원형태로응용해서사용하는방법 반원차트 353 15 복합적인요소를비교, 분석할때유용한차트 15-1 SWOT 분석의기본이되는보고서 방사형차트 363 15-2 목표대비실적보고서 중첩막대형차트 378 여섯째마당 우리팀장님의데이터활용법 16 목표관리, 실적관리에강하다! 슬라이서 16-1 피벗테이블과연동하여데이터를빠르게분석하기 397 17 보고서를자동으로생성하는방법 피벗테이블보고서필터 17-1 거래처별매출보고서를각각다른시트에 자동으로생성하기 411 18 손해보지않으려면얼마에팔아야할까? 손익분기점차트 18-1 손익분기점차트에서단가산출하기 419
Special 꼭알아야하는프로직장인의기술다른열의데이터에영향을주지않고행을삽입하거나삭제하기 432 VLOOKUP 함수사용시조견표범위를참조범위없이작성하기 433 동일한양식의두신청내역에서변경된내용빨리찾아내기 435 각각의행에한행씩삽입하는방법 436 입력한수식을수정하지못하게하기 440 문자로입력된날짜를정상적인날짜로변환하기 444 수식에주석넣기 446 일곱째마당 엑셀로익히는기획과경영의기술, 데이터분석 19 현재를분석하고미래를예측하라 19-1 기저효과를배제한매출추이분석하기 Z 차트 451 19-2 항목별매출기여도분석하기 ABC 분석 462 19-3 전이행렬을이용해서휴대폰시장점유율예측하기 마아코프분석 470 19-4 광고비규모에따른매출액예측하기 단회귀분석 476 19-5 하나이상의조건에서빙과류매출액예측하기 다중회귀분석 482 19-6 신차판매가능성예측하기 다중회귀분석의활용및응용 484
3 2007 3 2010 3 2013 3 2016 02-2 표에서데이터다루기 - 표 새로운데이터를추가로입력하면셀범위는늘어나게됩니다. 데이터가자주추가되거나바뀌면처음작성했던셀범위를벗어날수밖에없습니다. 그래서새로운데이터를추가하거나삭제하더라도동적으로변할수있는셀범위가필요합니다. 2장 _02_ 표 _ 예제.xlsx 파일을불러와동적범위를반영하는표기능에대해살펴보겠습니다. 표모양바꾸기동적범위로만들어활용하기쉬운데이터베이스로작성하려면먼저입력했던데이터를표형식으로바꿔야합니다. 데이터를표만들기로변경하는것만으로동적범위가적용된데이터베이스가됩니다. 1. 데이터중에서임의의셀을선택하고 [ 삽입 ] 탭 [ 표 ] 그룹 [ 표 ] 를클릭해서실행합니다. 그런다음 [ 확인 ] 을누르면파란색의기본표스타일 ( 표스타일보통 ) 이적용됩니다. 4 [ 표만들기 ] 대화상자를불러오는단축키는 Ctrl + T 입니다. 78
2. 표스타일바꾸고싶을때는 [ 디자인 ] 탭 [ 표스타일 ] 그룹 [ 자세히 ] 버튼을클릭해서 원하는스타일을클릭하면됩니다. 여기에서는 표스타일보통 19 를선택합니다. [ 디자인 ] 탭은표내부에커서가위치했을때만나타납니다. 즉, 표에서만적용할수있는메뉴입니다. 4 마우스커서를올려보면표스타일의이름을알수있습니다. Shift 키로행추가하기 1. 표를이미완성했는데한행을추가할일이생겼다면 Shift 키를이용해행을추가하는것이가장효율적입니다. 여기서 8행의하단에데이터를추가해보겠습니다. [A8:I8] 셀을선택합니다. 2. [I8] 셀우측하단으로마우스커서를가져가면채우기핸들 (+) 로커서의모양이변경됩니 다. 이때 Shift 키를누르면커서의모양이다시상하방향을가리키는화살표 ( ) 로변경되는것을확인할수있습니다. 클릭해서아래로 1행만큼만드래그하면행하나를삽입할수있습니다. 데이터입력의기본 - 표와셀서식 79
4 Shift 키를이용한행삽입은다른열의데이터에영향을주지않고선택된셀의범위만큼만행을삽입할수 있습니다. 그리고 Shift 키를이용하면행삽입이나행삭제, 열삽입은가능하지만 열삭제 는지원하지않습 니다. 3. 삽입된행에임의의데이터를입력한뒤전년대비증감액을나타내는 [I4] 셀의값을확인하면 대비증감 의금액이자동으로변경되는것을확인할수있습니다. 이것은가장하단의비어있는 [28] 행에다른데이터를추가로입력했을때 [I4] 셀 ( 대비증감액 ) 의참조범위를변경하지않아도자동으로반영되는것을확인할수있습니다. 80
Special Page 데이터베이스정리, 처음부터이렇게해야편하다! 엑셀에서데이터처리기능을쉽게활용하고싶다면데이터베이스부터잘만들어놓아야합니다. 여기서알 려주는데이터입력방법으로데이터를관리하여, 엑셀의기능과함수들을이용할때쉽고편하게활용해보 세요. 1. 데이터는항목별로구분해서셀을만든다음가로로배치하세요데이터를활용해서결과물을만들고싶을때는목표에맞게항목을구분하여각각의셀로만든후같은행의가로로배치하세요. 예를들어지점별판매량데이터를입력하려면구분되어야할지점이름, 날짜등의필드를하나의행에가로로배치합니다. 2. 데이터베이스를만들때는표형태로입력하세요처음부터 크로스탭형태 의표에데이터를작성하면데이터의누적입력이나변경등이쉽지않습니다. 물론자동화가안되는것은아니지만엑셀의기능을원활히사용하는데제약이됩니다. 그러니데이터베이스파일을만들때에는표형태로입력하는것이좋습니다. Special Page _ 데이터베이스정리, 처음부터이렇게해야편하다! 81
03 데이터분석과시각화의기본피벗테이블과피벗차트 데이터베이스를이용하면마우스만으로도아주손쉽고빠르게데이터를처리할수있을뿐만아니라해당정보를시각화하는데도효과적입니다. 여기에서는마우스만으로각종비즈니스데이터를손쉽게원하는대로만들어보겠습니다. 우선보고서, 차트보고서등을작성하는피벗테이블을익혀서정보를시각화하여빠르고편하게데이터를분석해보겠습니다. 03-1 데이터를요약하고분석해서차트로나타내기 김사원이야기 피벗테이블을쓰니제품별, 기간별분석이바로되네? 분기별임원회의를앞두고김철민사원은신팀장에게중요한과제를전달받았다. 파일을하나보낼테니내일까지매출이성장한거래처와제품경향을분석해오라는거다. 그것도분기별로끊어서. 파일은 4년치의주문내역이기록된데이터베이스였다. 연도별, 제품별등그어느것으로도분석된시트는없었다. 이걸어쩌지. 내일까지할수있을까? 직장인은두종류다. 피벗을아는사람과모르는사람.
7 2007 s 2010 3 2013 3 2016 03-1 데이터를요약하고분석해서차트로나타내기 엑셀데이터베이스를이용해서어느문구회사의기간별매출분석, 보고서차트를작성하는방법에대해살펴보겠습니다. 실습을위해 3장 _01_ 피벗테이블 _ 예제.xlsx라는엑셀파일을불러옵니다. 이파일은 1만 7 천건이넘는주문이기록되어있습니다. 파일을연후, 먼저이데이터베이스를관찰해보십시오. 거래중인문구점과상품명, 그리고상품의종류를분류해놓은것이보일겁니다. 입력된데이터를이용해서피벗테이블만들기 피벗 (Pivot) 은중심축이란뜻이죠. 피벗테이블은원본데이터베이스를특정한중심축으로재 배열하여보고서를작성할수있는엑셀의강력한기능입니다. 1. 피벗테이블을만들려면반드시참조할영역을표로만들어야합니다. 표로작성할범위안에서아무셀이나선택한후 Ctrl + T 키를눌러 [ 표만들기 ] 대화상자를불러옵니다. 점선형태의범위내에머리글이포함된것을확인하고 [ 머리글포함 ] 에체크된상태로 [ 확인 ] 버튼을클릭해서표로만듭니다. 4 데이터를표로지정할때선택범위에머리글이포함되어있는지그렇지않은지를판단해야합니다. 만약머리글이들어있지않아 [ 머리글포함 ] 을체크하지않고 [ 확인 ] 버튼을누르면데이터의첫행에머리글을자동으로만들어줍니다. 그러므로머리글이있는경우 는반드시 [ 머리글포함 ] 을체크해야불필요한머리글의추가생성을막을수있습니다. 88
2. 표로작성된영역에서임의의셀을선택한후 [ 디자인 ] 탭 [ 도구 ] 그룹 [ 피벗테이블로요약 ] 을클릭해서 [ 피벗테이블만들기 ] 대화상자를실행하세요. 대화상자가나타나면 [ 새워크시트 ] 를선택한후 [ 확인 ] 버튼을클릭합니다. 데이터베이스 (DB) 시트가아닌새시트가열릴것입니다. [ 표또는범위선택 ] 의 [ 표 / 범위 ] 부분에선택된표의이름이나타납니다. 표이름은표를만들면 표 1, 표 2 와같은순서로자동지정되며, 표이름은사용자가임의로수정할수있습니다. 3. 오른쪽에생성된 [ 피벗테이블필드 ] 목록에서 매출일 필드는마우스를이용해서아래에 있는 [ 행 ] 영역으로끌어다놓고, 구분 필드는 [ 열 ] 영역으로끌어다놓습니다. 구분 필드가열영역에표시됩니다. 보고서나분석자료를작성할때세로로나열된필드부분은 [ 행 ] 영역에, 가로로나열된필드부분은 [ 열 ] 영역에끌어다놓으면됩니다. 매출일 필드가행영역에표시됩니다. 데이터분석과시각화의기본 - 피벗테이블과피벗차트 89
4. 집계할필드인 금액 필드는 [ 값 ] 영역으로끌어다놓습니다. 순식간에해당날짜의구 분별매출이합산되었습니다. [ 값 ] 영역으로이동할필드가숫자로구성되어있으면마우스로끌어다놓았을때 [ 합계 ] 가기본설정으로나타내고, 문자로구성된필드라면 [ 개수 ] 가기본설정으로적용됩니다. 그런데합계금액이쉼표스타일로나타나지않아한눈에금액을확인하기가쉽지않습니다. 이런경우집계된합계의서식을통화형태로변경하면됩니다. 5. [ 값 ] 영역내부에서 [ 합계 : 금액 ] 부분의드롭다운메뉴를클릭합니다. [ 값필드설정 ] 항목을클릭해서실행하면 [ 값필드설정 ] 대화상자가나타납니다. 좌측하단의 [ 표시형식 ] 버튼을클릭합니다. 90
6. [ 셀서식 ] 대화상자의 [ 범주 ] 에서 통화 를선택하고 [ 확인 ] 버튼을클릭합니다. [ 값필드설 정 ] 대화상자로돌아와서 [ 확인 ] 버튼을클릭하면금액에통화기호가생성된것을확인할 수있습니다. 7. 이번에는매출을특정연도와월의구분별매출합계로알아보기위해 [ 매출일 ] 필드의내용을그룹메뉴로그룹화하겠 습니다. 날짜가입력된임의의셀을선택한다음마우스오른쪽버튼을클릭하면빠른메뉴가나타납니다. 메뉴에서 [ 그룹 ] 을클릭합니다. 데이터분석과시각화의기본 - 피벗테이블과피벗차트 91
8. [ 그룹화 ] 대화상자가나타나면사용자가그룹화할단위를선택하게됩니다. 이예제에서는특정연도, 월별매출합계를확인하고자하므로 [ 월 ], [ 연 ] 을선택하고 [ 확인 ] 버튼을클릭합니다. 그러면해당월별합계와연도별합계를구분되기때문에쉽게확인할수있습니다. [ 시작 ] 날짜와 [ 끝 ] 날짜는 [ 매출일 ] 의가장빠른날짜와마지막날짜를자동으로채워줍니다. 9. 다른기준으로그룹화하려면어떻게해야할까요? 만약연간합계만보고싶다면먼저마우스오른쪽버튼을눌러, 나오는메뉴에서 [ 그룹해제 ] 를선택하세요. 그런다음다시 [ 그룹화 ] 대화상자를실행해 [ 연 ] 만선택한후 [ 확인 ] 을누르면됩니다. 무척간단하죠? 이제다음실습을위해대화상자에서 [ 연 ], [ 월 ] 을선택한상태로되돌아오세요. 슬라이서기능으로선택기준에따른결과빠르게확인하기 1. 특정 거래처 와 구분 을기준으로빠른결과를나타내고싶다면슬라이서기능을이용해 보세요. 먼저 [ 삽입 ] 탭 [ 필터 ] 그룹 [ 슬라이서 ] 를클릭합니다. 2. [ 슬라이서삽입 ] 대화상자에서 [ 거래처 ] 와 [ 구분 ] 을선택하고 [ 확 인 ] 버튼을클릭합니다. 92
김철쌤의한마디! 피벗테이블의원본데이터가바뀐다면? 더불어피벗테이블의데이터인 [DB] 시트에새로운내용이추가되면곧바로피벗테이블에적용되지않기때문에 [ 분석 ] 탭 [ 데이터 ] 그룹 [ 새로고침 ] 을클릭해야반영되는것을확인할수있습니다. 이때표가아닌일반범위로피벗테이블을작성한다면 [ 분석 ] 탭 [ 데이터 ] 그룹 [ 데이터원본변경 ] 을실행해서다시범위를설정해야하므로표를활용하는것이훨씬효율적입니다. 하위버전에서는엑셀 2007 버전에서는슬라이서기능이없습니다. 슬라이서기능은엑셀 2010부터새로생긴기능이기때문에엑셀 2007 버전은사용할수없습니다. 원하는특정필드를피벗테이블에반영하고요약된피벗테이블의 [ 행레이블 ] 과 [ 열레이블 ] 의 [ 필터 ] 버튼을클릭해서결과를확인해야합니다. 3. [ 거래처 ], [ 구분 ] 슬라이서가화면에나타나면사용자가원하는거래처와구분을선택합니 다. 만약가좌문구에서그동안잉크토너가얼마나팔렸는지알고싶다면슬라이서에서 가좌문구 와 잉크토너 를클릭하면됩니다. 피벗테이블에선택된내용의결과가빠르게나타나는것을확인할수있습니다. 데이터분석과시각화의기본 - 피벗테이블과피벗차트 93
이부분을누르면원래의피벗테이블로돌아갑니다. 4 셀에서연속되지않은범위를선택할때 Ctrl 키를이용하는데슬라이서에서도 Ctrl 키를이용합니다. [ 거 래처 ] 슬라이서에서 [ 가좌문구 ] 와 [ 강북문구 ] 를선택하려면 [ 가좌문구 ] 를선택한후 Ctrl 키를누른채 [ 강북문 구 ] 를선택하면됩니다. 4. 시간표시막대기능을활용하면빠르고쉽게시간의경과에따른변화를볼수있습니다. 먼저 [ 삽입 ] 탭 [ 필터 ] 그룹 [ 시간표시막대 ] 를클릭합니다. 이때표안의셀을선택한후 [ 시간표시막대 ] 를선택해야합니다. 표밖의셀을선택한채 [ 시간표시막대 ] 를누르면다른대화상자가나오니주의하세요. 4 [ 시간표시막대 ] 는엑셀 2013 에서새로생긴기능입니다. 엑셀 2007 과엑셀 2010 은사용할수없고 [ 그룹 화 ] 대화상자에서 [ 단위 ] 를선택해서이용해야합니다. 94
하면된다! Q1. 전년대비매출이 1,000 만원이상증가한사람을포상금지급대상자로선정하세요. 문제 1-1_ 포상금지급자명단 _ 문제.xlsx Hint! 1. [F5] 셀에전년대비증감액을계산합니다. 2. [G5] 셀에는전년대비 1,000만원이상의매출이증가했을때 포상금 이입력되도록 =IF 함수를사용합니다. 3. [F5:G5] 셀의내용을나머지셀에적용합니다. 4. 전년대비매출이증가한사람은 [ 셀서식 ] 을이용하여 기호가증감매출액앞에나타나도록합니다. 정답 1-1_ 포상금지급자명단 _ 정답.xlsx 하면된다! Q2. 가좌문구에서판매한 [ 결재판 ], [ 노트 ], [ 독서카드 ] 물품을피벗차트로만들어보세요. 문제 1-2_ 거래처별품명 _ 피벗테이블 _ 문제.xlsx Hint! 1. 데이터를우선표로설정한후피벗테이블로요약합니다. 2. 피벗테이블필드에서품명, 거래처, 금액필드를각각행, 열, 값영역으로드래그합니다. 3. 필요한데이터만피벗테이블로정리했다면슬라이서를삽입합니다. 4. 품명슬라이서를삽입한후 [ 가좌문구 ] 에서판매한 [ 결재함 ], [ 노트 ], [ 독서카드 ] 의총합계금액을필터링합 니다. 5. 필터링한내용은피벗테이블도구의 [ 분석 ] 탭 [ 도구 ] 그룹에서 원형피벗차트 로만들어삽입합니다. 정답 1-2_ 거래처별품명 _ 피벗테이블 _ 정답.xlsx 108