엑셀 데이터 분석 및 활용 (회사에서 칭찬받는 엑셀 전문가 양성을 위한)

장효선

(스마트워크플러스연구소소장)
컴퓨터공학과컴퓨터교육학을전공하였고,프로그래밍언어,자격증,오피스등컴퓨터전문강사로다년간활동후웅진식품해외사업팀으로입사하였습니다.현업에서는영업기획,영업관리,무역,SAPPI등의방대한양의업무를하며엑셀을업무에맞게효과적으로활용하고자동화할수있는방법을고민하고개발하게되었습니다.웅진그룹내엑셀사내강사로활동하며여러계열사의업무형태에따라엑셀을현업에서쉽게활용하고,학습한내용의적용률을높일수있도록강의하였습니다.현재는현대해상,티몬,삼양,KT,CJ,대한상공회의소,이스트소프트,한국문화예술교육진흥원,노사발전재단등의여러기업및공공기관,그리고대학등에서강의하며,엑셀을활용하여업무의효율성을높일수있도록프로그램을개발하고있습니다.

주요강의분야
-비즈니스엑셀실무
-함수로하는데이터관리
-엑셀데이터관리및분석
-엑셀데이터가시화

집필도서
-엔트리로시작하는블록코딩(성안당)

Part01엑셀데이터분석의이해와엑셀기본기익히기
Chapter01엑셀데이터분석의이해
1.엑셀및엑셀2016버전의특징
2.데이터베이스
3.엑셀데이터분석의이해

Chapter02엑셀의화면구성과용어그리고환경설정
1.엑셀의화면구성
2.엑셀에서자주사용하는용어
3.엑셀의환경설정

Chapter03효과적으로데이터베이스화하는방법
1.데이터에기본키(고유키)를반드시포함해야합니다.
2.필드명(머리글)을입력하고그아래로해당데이터를입력합니다.
3.[병합하여가운데맞춤]은데이터베이스화된파일에는가급적사용하지않습니다.
4.데이터가많을수록함수대신피벗테이블이나필터등의기능을활용합니다.
5.데이터를필요없이세분화하지않습니다.

Chapter04엑셀을편리하고폼나게사용하도록해주는단축키
1.컨트롤(Ctrl)키와시프트(Shift)키
2.꼭알고있어야할단축키
3.단축키를만들어사용하기

Chapter05간단하지만파워풀한서식기능
1.화면에보이는셀만복사&붙여넣기
2.만들어놓은표의방향을쉽게바꾸기(표의행,열바꾸기)
3.날짜처럼보이는텍스트를날짜형식으로변환하기
4.중복된데이터를하나만남기고지우기

Chapter06표스타일설정과데이터요약이가능한표기능
1.[표]로변환(Ctrl+T),스타일지정하기

Chapter07원하는순서로데이터를나열할수있는정렬
1.내림차순정렬을이용해매출순위파악하기
2.원하는순서로정렬해지역별데이터분석하기
3.행을기준으로정렬하기

Chapter08필요한데이터만쉽게추출할수있는자동필터
1.자동필터(Ctrl+Shift+L)로‘강남점’의‘육류’매출액을쉽게파악하기

PART02쉽고간단한기능을활용한데이터관리및분석
Chapter01상위,하위데이터를쉽게파악해서서식을지정하는조건부서식
1.[조건부서식]으로상위10%,하위10%데이터파악하기
2.수식을입력해서이상값에서식지정하기
3.[조건부서식]지우기

Chapter02원하는데이터만다른표로만들어주는고급필터
1.[고급]필터에조건입력하는방법이해하기
2.달성률이100%이상인데이터추출하기
3.조건에맞는데이터중원하는항목만새로운표로만들기

Chapter03분산되어있는데이터를하나의시트로통합하는데이터통합
1.분산되어있는데이터통합하기
2.원본데이터의값이변경되면통합된데이터의값도함께변경되도록하기
-실습문제

Chapter04빠르고간편하게데이터를집계하는부분합
1.특정기준으로매출액합계구하기
2.매출액합계와평균값을함께보기
-실습문제

Chapter05자동으로분석해서표로만들어주는피벗테이블
1.피벗테이블삽입하기
2.피벗테이블의보고서형식변경및요약제거하기
3.피벗테이블의총합계표시와비어있는셀변경하기
4.피벗테이블의[값]에표시된매출액을구성하는데이터확인하기
5.피벗테이블의[값]에표시된함수변경하기
-실습문제

Chapter06간단하게데이터의추이를분석하는스파크라인
1.스파크라인으로데이터분석하기
2.스파크라인편집하기
-실습문제

Chapter07원하는데이터만추출해서보는슬라이서
1.데이터를[표]로변환및슬라이서삽입하기
2.슬라이서로원하는데이터추출하기
-실습문제

Chapter08데이터관리가쉬워지는데이터유효성검사_INDIRECT함수와이름정의
1.‘항목’시트에있는대분류항목을목록단추로표시하기
2.대분류목록에서선택한값에대한중분류값만표시하기
-실습문제

PART03꼭알아야하는핵심함수
Chapter01엑셀참조방식과기초통계함수(SUM,AVERAGE,MEDIAN,COUNT,LARGE,SMALL함수)
1.참조방식의이해
2.기초함수이해하기매출합계구하기
3.매출평균구하기
4.평균의함정에빠지지않기위한중간값구하기
5.기초통계함수로매출건수구하기
6.기초통계함수로최대매출액과최소매출액구하기
7.기초통계함수로두번째로큰값,두번째로작은값구하기
-실습문제

Chapter02원하는텍스트만추출해주는LEFT,RIGHT,MID함수
1.왼쪽에서부터원하는문자수만큼추출하는LEFT함수
2.오른쪽에서부터원하는텍스트의수만큼추출하는RIGHT함수
3.중간에서원하는텍스트의수만큼추출하는MID함수
-실습문제

Chapter03순위를알려주는RANK.EQ함수(RANK.AVG포함)
1.지점별매출순위구하기
-실습문제

Chapter04필터기능에유용한SUBTOTAL함수
1.SUBTOTAL함수로서울지역만판매건수,총판매수량,판매금액구하기
-실습문제

Chapter05결과값이오류일때원하는값을표시할수있는IFERROR함수
1.IFERROR함수로오류대신다른값넣기
-실습문제

Chapter06숫자의소숫점과자릿수를결정하는ROUND,ROUNDUP,ROUNDDOWN함수1.숫자를반올림할수있는ROUND함수
2.숫자를올림할수있는ROUNDUP함수
3.숫자를내림할수있는ROUNDDOWN함수
-실습문제

Chapter07기준값으로세부데이터를추출하는VLOOKUP함수
1.거래처별담당자현황파일완성하기
-실습문제

Chapter08데이터의위치를알려주는MATCH함수
1.데이터의위치를알려주는MATCH함수
-실습문제

Chapter09행과열이교차하는위치에있는데이터를추출하는INDEX함수
1.근무기간과매출수준에따라인센티브적용하기
-실습문제

Chapter10날짜와날짜사이의간격을알수있는DATEDIF함수
1.고객의회원가입일수구하기
2.“00년00월00일”로총기간표시하기
-실습문제

Chapter11조건의결과에따라값을표시하는IF함수
1.상반기매출액vs하반기매출액의현황분석
-실습문제

Chapter12여러개의조건에따라다른결과를표시해주는IF함수중첩
1.코드를기준으로여러단계의거래처로구분하기
-실습문제

Chapter13여러조건의결과에따라다른결과값을표시해주는IFS함수(office365의2016버전)
1.여러조건에맞는결과값구하기
-실습문제

Chapter14여러조건의결과가모두참일때만참인AND함수와IF함수활용
1.매출데이터를활용해우수고객파악하기
-실습문제

Chapter15여러조건중하나라도참이면참인OR함수와IF함수의활용
1.직급과관련교육경험에따라수강가능여부파악하기
-실습문제

Chapter16값이전체평균이상이면평균이상을표시하도록AVERAGE함수와IF함수중첩
1.지점별매출평균vs전체매출평균현황분석

Chapter17조건에맞는데이터의개수를구하는COUNTIF와COUNTIFS함수
1.COUNTIF함수로경영지원본부의교육참여인원구하기
2.COUNTIFS함수로경영지원본부의교육참여자중교육을수료한인원을구하기
-실습문제

Chapter18조건에맞는데이터의합계를구하는SUMIF와SUMIFS함수
1.SUMIF함수로특정지역의총판매금액구하기
2.SUMIFS함수로특정지역의특정제품에대한총판매금액구하기
-실습문제

Chapter19조건에맞는데이터의평균을구하는AVERAGEIF와AVERAGEIFS함수
1.각지점별0을제외한매출평균구하기
2.AVERAGEIFS함수로리더십교육수료자들의평균이수시간파악하기
-실습문제

Chapter20조건에맞는데이터값을구하는데이터베이스함수
1.데이터베이스함수를사용하기위한조건만들기
2.만들어놓은조건을활용하여데이터베이스함수사용하기
-실습문제

Chapter21다중조건에맞는최대값과최소값을구하는MAXIFS,MINIFS함수(office365의2016버전이상)
1.여러조건에맞는최대값을구하는MAXIFS함수
2.여러조건에맞는최소값을구하는MINIFS함수
-실습문제

PART04함수를활용한데이터관리및분석
Chapter01데이터분석도구를활용한데이터분석
1.기술통계법을활용하여한번에데이터분석하기

Chapter02제품코드를활용하여매출세부내역서작성
1.제품코드를기준으로데이터추출및상세내역완성하기

Chapter03매출현황작성과상품별할인전?후매출분석
1.매출현황파일을생성하기위한내용구성하기
2.‘상품코드’를기준으로‘상품명’,‘형태’,‘단가’데이터추출하기
3.할인율을적용하여‘할인단가’,‘할인금액’,‘금액’완성하기
4.각상품의구매구분에따른매출액분석하기
5.‘구매구분’에대한할인율적용전과후의매출액표시하기

Chapter04단가조정여부에따라인상된금액과기존금액적용
1.단가조정여부에따라인상된단가와기존단가적용하기

Chapter05크로스탭으로쉽게만드는보고서(SUMIFS와피벗테이블)
1.SUMIFS함수를활용한매출보고서작성(크로스탭분석)을위한기본작업
2.함수를활용한지역별,월매출보고서작성(크로스탭분석)
3.매출분석보고서를조건부서식으로가시화하여쉽게분석하기
4.매출분석보고서를스파크라인으로가시화하여쉽게분석하기
5.피벗테이블을활용한지역별,월매출보고서작성(크로스탭분석)

Chapter06[통합]기능과함수로효율적인데이터취합
1.거래처별로나눠관리하던파일을하나로통합하기
2.VLOOKUP함수로제품명과제품분류를추출하여데이터완성하기

Chapter07데이터의분포상황분석(FREQUENCY&데이터분석도구의히스토그램)
1.교육과정별평균만족도점수에대한분포분석
2.분포표를히스토그램으로표현하기
3.[데이터분석]도구를활용하여분포표와히스토그램삽입하기

PART05데이터분석&미래예측
Chapter01수식을활용한제품별손익분기점분석및목표값찾기를활용한손익분기점분석
1.매출액에서비용을뺀이익금계산하기
2.손익분기점매출액과손익분기점수량구하기
3.그룹기능으로원하는값만화면에표시되도록하기
4.[목표값찾기]기능을활용해손익분기점수량과손익분기점매출액구하기

Chapter02매출기여제품을알수있