엑셀 데이터 분석 및 활용(멘토의 한 수)

Part 01 엑셀 데이터 분석의 이해와 엑셀기본기 익히기

Chapter 01 엑셀 데이터 분석의 이해

1. 엑셀 및 엑셀 2016버전의 특징

2. 데이터베이스

3. 엑셀 데이터 분석의 이해



Chapter 02 엑셀의 화면구성과 용어 그리고 환경설정

1. 엑셀의 화면구성

2. 엑셀에서 자주 사용하는 용어

3. 엑셀의 환경설정



Chapter 03 효과적으로 데이터베이스화 하는 방법

1. 데이터에 기본키(고유키)를 반드시 포함해야 합니다.

2. 필드명(머리글)을 입력하고 그 아래로 해당 데이터를 입력합니다.

3. [병합하여 가운데 맞춤]은 데이터베이스화된 파일에는 가급적 사용하지 않습니다.

4. 데이터가 많을수록 함수 대신 피벗 테이블이나 필터 등의 기능을 활용합니다.

5. 데이터를 필요 없이 세분화하지 않습니다.



Chapter 04 엑셀을 편리하고 폼나게 사용하도록 해주는 단축키

1. 컨트롤(Ctrl) 키와 시프트(Shift) 키

2. 꼭 알고 있어야 할 단축키

3. 단축키를 만들어 사용하기



Chapter 05 간단하지만 파워풀한 서식기능

1. 화면에 보이는 셀만 복사 & 붙여넣기

2. 만들어 놓은 표의 방향을 쉽게 바꾸기(표의 행, 열 바꾸기)

3. 날짜처럼 보이는 텍스트를 날짜 형식으로 변환하기

4. 중복된 데이터를 하나만 남기고 지우기



Chapter 06 표 스타일 설정과 데이터 요약이 가능한 표기능

1. [표]로 변환(Ctrl+T), 스타일 지정하기



Chapter 07 원하는 순서로 데이터를 나열할 수 있는 정렬

1. 내림차순 정렬을 이용해 매출 순위 파악하기

2. 원하는 순서로 정렬해 지역별 데이터 분석하기

3. 행을 기준으로 정렬하기



Chapter 08 필요한 데이터만 쉽게 추출할 수 있는 자동필터

1. 자동필터(Ctrl+Shift+L)로 '강남점'의 '육류' 매출액을 쉽게 파악하기



PART 02 쉽고 간단한 기능을 활용한 데이터관리 및 분석

Chapter 01 상위, 하위 데이터를 쉽게 파악해서 서식을 지정하는 조건부 서식

1. [조건부 서식]으로 상위 10%, 하위 10% 데이터 파악하기

2. 수식을 입력해서 이상 값에 서식 지정하기

3. [조건부 서식] 지우기



Chapter 02 원하는 데이터만 다른 표로 만들어주는 고급 필터

1. [고급] 필터에 조건 입력하는 방법 이해하기

2. 달성률이 100% 이상인 데이터 추출하기

3. 조건에 맞는 데이터 중 원하는 항목만 새로운 표로 만들기



Chapter 03 분산되어 있는 데이터를 하나의 시트로 통합하는 데이터통합

1. 분산되어 있는 데이터 통합하기

2. 원본 데이터의 값이 변경되면 통합된 데이터의 값도 함께 변경되도록 하기

- 실습문제



Chapter 04 빠르고 간편하게 데이터를 집계하는 부분합

1. 특정기준으로 매출액 합계 구하기

2. 매출액 합계와 평균값을 함께 보기

- 실습문제



Chapter 05 자동으로 분석해서 표로 만들어 주는 피벗 테이블

1. 피벗 테이블 삽입하기

2. 피벗 테이블의 보고서 형식 변경 및 요약 제거하기

3. 피벗 테이블의 총합계 표시와 비어있는 셀 변경하기

4. 피벗 테이블의 [값]에 표시된 매출액을 구성하는 데이터 확인하기

5. 피벗 테이블의 [값]에 표시된 함수 변경하기

- 실습문제



Chapter 06 간단하게 데이터의 추이를 분석하는 스파크라인

1. 스파크라인으로 데이터 분석하기

2. 스파크라인 편집하기

- 실습문제



Chapter 07 원하는 데이터만 추출해서 보는 슬라이서

1. 데이터를 [표]로 변환 및 슬라이서 삽입하기

2. 슬라이서로 원하는 데이터 추출하기

- 실습문제



Chapter 08 데이터 관리가 쉬워지는 데이터 유효성 검사_INDIRECT 함수와 이름 정의

1. '항목' 시트에 있는 대분류 항목을 목록단추로 표시하기

2. 대분류 목록에서 선택한 값에 대한 중분류 값만 표시하기

- 실습문제



PART 03 꼭 알아야 하는 핵심함수

Chapter 01 엑셀 참조방식과 기초 통계함수(SUM, AVERAGE, MEDIAN, COUNT, LARGE, SMALL 함수)

1. 참조방식의 이해

2. 기초함수 이해하기 매출 합계 구하기

3. 매출 평균구하기

4. 평균의 함정에 빠지지 않기 위한 중간값 구하기

5. 기초 통계함수로 매출건수 구하기

6. 기초 통계함수로 최대매출액과 최소매출액 구하기

7. 기초 통계함수로 두 번째로 큰 값, 두 번째로 작은 값 구하기

- 실습문제



Chapter 02 원하는 텍스트만 추출해 주는 LEFT, RIGHT, MID 함수

1. 왼쪽에서부터 원하는 문자수만큼 추출하는 LEFT 함수

2. 오른쪽에서부터 원하는 텍스트의 수만큼 추출하는 RIGHT 함수

3. 중간에서 원하는 텍스트의 수만큼 추출하는 MID 함수

- 실습문제



Chapter 03 순위를 알려주는 RANK.EQ 함수(RANK.AVG 포함)

1. 지점별 매출순위 구하기

- 실습문제



Chapter 04 필터 기능에 유용한 SUBTOTAL 함수

1. SUBTOTAL 함수로 서울지역만 판매건수, 총 판매수량, 판매금액 구하기

- 실습문제



Chapter 05 결과값이 오류일 때 원하는 값을 표시할 수 있는 IFERROR 함수

1. IFERROR 함수로 오류 대신 다른 값 넣기

- 실습문제



Chapter 06 숫자의 소숫점과 자릿수를 결정하는 ROUND, ROUNDUP, ROUNDDOWN 함수1. 숫자를 반올림할 수 있는 ROUND 함수

2. 숫자를 올림할 수 있는 ROUNDUP 함수

3. 숫자를 내림할 수 있는 ROUNDDOWN 함수

- 실습문제



Chapter 07 기준값으로 세부데이터를 추출하는 VLOOKUP 함수

1. 거래처별 담당자 현황파일 완성하기

- 실습문제



Chapter 08 데이터의 위치를 알려주는 MATCH 함수

1. 데이터의 위치를 알려주는 MATCH 함수

- 실습문제



Chapter 09 행과 열이 교차하는 위치에 있는 데이터를 추출하는 INDEX 함수

1. 근무기간과 매출수준에 따라 인센티브 적용하기

- 실습문제



Chapter 10 날짜와 날짜 사이의 간격을 알 수 있는 DATEDIF 함수

1. 고객의 회원가입일수 구하기

2. "00년00월00일"로 총 기간 표시하기

- 실습문제



Chapter 11 조건의 결과에 따라 값을 표시하는 IF 함수

1. 상반기 매출액 vs 하반기 매출액의 현황 분석

- 실습문제



Chapter 12 여러 개의 조건에 따라 다른 결과를 표시해주는 IF 함수 중첩

1. 코드를 기준으로 여러 단계의 거래처로 구분하기

- 실습문제



Chapter 13 여러 조건의 결과에 따라 다른 결과값을 표시해주는 IFS 함수(office365의 2016버전)

1. 여러 조건에 맞는 결과값 구하기

- 실습문제



Chapter 14 여러 조건의 결과가 모두 참일때만 참인 AND 함수와 IF 함수활용

1. 매출데이터를 활용해 우수고객 파악하기

- 실습문제



Chapter 15 여러 조건 중 하나라도 참이면 참인 OR 함수와 IF 함수의 활용

1. 직급과 관련교육경험에 따라 수강가능 여부 파악하기

- 실습문제



Chapter 16 값이 전체 평균 이상이면 평균 이상을 표시하도록 AVERAGE 함수와 IF 함수 중첩

1. 지점별 매출평균 vs 전체 매출평균 현황 분석



Chapter 17 조건에 맞는 데이터의 개수를 구하는 COUNTIF와 COUNTIFS 함수

1. COUNTIF 함수로 경영지원본부의 교육 참여 인원 구하기

2. COUNTIFS 함수로 경영지원본부의 교육 참여자 중 교육을 수료한 인원을 구하기

- 실습문제



Chapter 18 조건에 맞는 데이터의 합계를 구하는 SUMIF와 SUMIFS 함수

1. SUMIF 함수로 특정지역의 총 판매금액 구하기

2. SUMIFS 함수로 특정지역의 특정 제품에 대한 총 판매금액 구하기

- 실습문제



Chapter 19 조건에 맞는 데이터의 평균을 구하는 AVERAGEIF와 AVERAGEIFS 함수

1. 각 지점별 0을 제외한 매출 평균 구하기

2. AVERAGEIFS 함수로 리더십교육 수료자들의 평균 이수시간 파악하기

- 실습문제



Chapter 20 조건에 맞는 데이터 값을 구하는 데이터베이스 함수

1. 데이터베이스 함수를 사용하기 위한 조건 만들기

2. 만들어놓은 조건을 활용하여 데이터베이스 함수 사용하기

- 실습문제



Chapter 21 다중조건에 맞는 최대값과 최소값을 구하는 MAXIFS, MINIFS 함수(office365의 2016버전 이상)

1. 여러 조건에 맞는 최대값을 구하는 MAXIFS 함수

2. 여러 조건에 맞는 최소값을 구하는 MINIFS 함수

- 실습문제



PART 04 함수를 활용한 데이터관리 및 분석

Chapter 01 데이터 분석도구를 활용한 데이터 분석

1. 기술통계법을 활용하여 한 번에 데이터 분석하기



Chapter 02 제품코드를 활용하여 매출세부내역서 작성

1. 제품코드를 기준으로 데이터 추출 및 상세내역 완성하기



Chapter 03 매출현황 작성과 상품별 할인 전?후 매출분석

1. 매출현황파일을 생성하기 위한 내용 구성하기

2. '상품코드'를 기준으로 '상품명', '형태', '단가' 데이터 추출하기

3. 할인율을 적용하여 '할인단가', '할인금액', '금액'완성하기

4. 각 상품의 구매구분에 따른 매출액분석 하기

5. '구매구분'에 대한 할인율 적용 전과 후의 매출액 표시하기



Chapter 04 단가조정 여부에 따라 인상된 금액과 기존 금액 적용

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



Chapter 05 크로스탭으로 쉽게 만드는 보고서(SUMIFS와 피벗 테이블)

1. SUMIFS 함수를 활용한 매출 보고서작성(크로스탭 분석)을 위한 기본 작업

2. 함수를 활용한 지역별, 월 매출 보고서작성(크로스탭 분석)

3. 매출분석 보고서를 조건부 서식으로 가시화하여 쉽게 분석하기

4. 매출분석 보고서를 스파크라인으로 가시화하여 쉽게 분석하기

5. 피벗 테이블을 활용한 지역별, 월 매출 보고서작성(크로스탭 분석)



Chapter 06 [통합] 기능과 함수로 효율적인 데이터 취합

1. 거래처별로 나눠 관리하던 파일을 하나로 통합하기

2. VLOOKUP 함수로 제품명과 제품분류를 추출하여 데이터 완성하기



Chapter 07 데이터의 분포상황 분석(FREQUENCY & 데이터 분석 도구의 히스토그램)

1. 교육과정별 평균 만족도 점수에 대한 분포 분석

2. 분포표를 히스토그램으로 표현하기

3. [데이터 분석] 도구를 활용하여 분포표와 히스토그램 삽입하기



PART 05 데이터 분석 & 미래예측

Chapter 01 수식을 활용한 제품별 손익분기점 분석 및 목표값 찾기를 활용한 손익분기점 분석

1. 매출액에서 비용을 뺀 이익금 계산하기

2. 손익분기점 매출액과 손익분기점 수량 구하기

3. 그룹기능으로 원하는 값만 화면에 표시되도록 하기

4. [목표값 찾기] 기능을 활용해 손익분기점 수량과 손익분기점 매출액구하기



Chapter 02 매출기여제품을 알 수 있는 ABC분석

1. 피벗테이블을 활용하여 매출액 및 매출누계비율 생성

2. 생성된 데이터를 활용해 콤보차트(이중축 혼합형차트) 만들기



Chapter 03 매출 추이 분석을 위한 Z차트

1. 피벗테이블을 활용하여 년도별 월 매출액 및 매출액누계, 이동년계 생성



Chapter 04 고객을 분류하여 적절한 마케팅을 할 수 있는 RFM분석

1. RFM분석에 필요한 데이터 생성하기

2. 피벗 테이블로 R, F, M 각 요소별 점수표시하기

3. 구매자별로 Recency, Frequency, Monetary 점수를 반영하기



Chapter 05 선형추세에 따라 예측하는 FORECAST.LINEAR 함수와 데이터 분석 도구의 회귀분석

1. FORECAST.LINEAR 함수(FORECAST 함수)로 직원복지비용에 따른 매출예측

2. 데이터 분석 도구를 이용하여 매출액 예측하기

3. 기울기()와 절편을 함수로 구해 2020년 매출액을 예측하기



Chapter 06 다중회귀분석으로 여러 요인에 영향을 받는 매출액 분석 & 상관분석

1. 상관분석을 통해 총매출액과의 연관성 분석하기

2. CORREL 함수로 상관 분석하기

3. 데이터 분석 도구를 이용하여 매출액 예측하기

4. 예측해 본 매출액을 차트로 만들기



Chapter 07 목표 판매금액이 되기 위해 조정해야 할 단가의 비율을 자동으로 찾아주는 목표값 찾기

1. 'DB' 시트에서 '제품명', '단가', '제품별', '총판매수량'을 구해 기본표를 완성합니다.

2. 목 표 매출액이 되기 위한 '조정비율' 구하기



PART 06 엑셀로 효과적인 보고서 작성

Chapter 01 확인란을 활용한 직원의 역량평가 분석 보고서

1. [결과] 시트에 보고서를 만들기 위해 필요한 테이블 구성하기

2. '직원명'에 맞는 '소속', '직책', '직급' 데이터 추출하기

3. 직원에 따라 각 항목의 점수와 직급, 직책, 소속, 전체 평균점수 생성하기

4. 양식컨트롤의 [확인란]을 삽입하고 [확인란]과 연결하기 위한 테이블 작성하기

5. 데이터 가시화하기



Chapter 02 슬라이드바를 활용한 기간별 매출 분석 보고서

1. [DB] 시트의 데이터 가공하기

2. 기준일, 시작일, 종료일 설정하기

3. 스핀단추로 날짜를 변경하도록 하기

4. 일 매출현황의 기준일에 맞는 분류별 매출액 및 총매출액, 누적매출현황 구하기

5. 일 매출현황을 차트로 표현하기

6. 주간매출현황의 분야별 매출액 구하기

7. 주간 매출현황을 차트로 표시하기

8. 월매출현황의 분야별 매출액 구하기

9. 월매출현황을 차트로 표현하기

10. 데이터 범위 서식 설정하기

장효선 (스마트워크플러스연구소 소장)

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



주요 강의 분야

- 비즈니스 엑셀실무

- 함수로 하는 데이터 관리

- 엑셀 데이터 관리 및 분석

- 엑셀 데이터 가시화



집필 도서

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