엑셀 실무 데이터 분석(회사에서 바로 통하는)

Part 01 엑셀 데이터 관리 기술 익히기



Chapter 01 엑셀에서 데이터 관리하기

Section 01 표 구분하고 사용 목적 이해하기

[질문 01] 엑셀로 데이터를 관리할 때 좋은 방법이 있나요?

[실무실습] 데이터 관리를 돕는 표 종류 살펴보기

[질문 02] 테이블을 조건별로 구분하여 관리하면 안 되나요?

[실무실습] 분리된 데이터 통합하고 피벗 테이블로 집계하기

[질문 03] 여러 시트로 나뉜 테이블 표를 쉽게 합치는 방법이 있나요?

[실무실습] 매크로 수정하여 여러 시트 테이블 표 합치기

[질문 04] 테이블 표를 구성할 때 주의할 점이 있나요?

[실무실습] 데이터 관리를 위한 테이블 표 구성하기

[질문 05] 데이터를 테이블에 좀 더 쉽게 입력하는 방법이 있나요?

[실무실습] 레코드 관리 명령 추가하고 데이터 입력하기



Section 02 엑셀 표 사용하기

[질문 06] 엑셀 표란 무엇이고 어떻게 사용하나요?

[실무실습] 엑셀 표로 변환하고 특징 이해하기

[질문 07] 엑셀 표에서 수식을 어떻게 사용하나요?

[실무실습] 계산된 열 만들고 구조적 참조 특징 이해하기

[질문 08] 엑셀 표의 구조적 참조를 다른 표에서도 사용할 수 있나요?

[실무실습] 일반 표에서 구조적 참조 사용하기

[질문 09] 표 데이터를 요약하고 집계하는 요약 행은 어떻게 사용하나요?

[실무실습] 요약 행 추가하고 자동 필터와 연계하기

[질문 10] 구조적 참조를 사용하지 않고 동적으로 변하는 범위를 참조할 수 있나요?

[실무실습] 동적 범위를 참조하는 이름 정의하여 집계하기



Section 03 유효성 검사를 이용한 데이터 관리하기

[질문 11] 셀에 잘못된 값이 입력되지 않게 할 수 있나요?

[실무실습] 유효성 검사로 입력 값 제한하고 메시지 표시하기

[질문 12] 입력 날짜를 제한할 수 있나요?

[실무실습] 조건에 맞는 날짜만 입력되도록 하기

[질문 13] 셀에 입력하는 문자 개수를 지정할 수 있나요?

[실무실습] 텍스트 길이 제한하기

[질문 14] 입력 값을 목록에서 선택하게 만들 수 있나요?

[실무실습] 목록 지정하여 입력 값을 목록에서 선택하게 만들기

[질문 15] 유효성 검사로 여러 개의 목록을 연동할 수 없나요?

[실무실습] 선택한 목록에 맞는 하위 목록만 표시하기

[질문 16] 표에 중복된 값을 입력하지 못하게 하려면 어떻게 하나요?

[실무실습] 유효성 검사와 COUNTIF 수식으로 중복 값 제한하기

[질문 17] 중복 조건이 여러 열에 나뉜 경우에는 어떻게 하나요?

[실무실습] 여러 열의 중복 조건 참고하기

[질문 18] 유효성 검사의 조건을 변경하거나 삭제하려면 어떻게 하나요?

[실무실습] 유효성 검사 범위 수정하기



Section 04 조건부 서식으로 데이터 시각화하기

[질문 19] 표가 한눈에 구분되도록 시각적으로 표현할 수 있나요?

[실무실습] 색조로 분포와 변화를 시각적으로 표현하기

[질문 20] 집계된 값이 입력된 셀에 그래프를 표시할 수 있나요?

[실무실습] 데이터 막대로 값과 그래프를 함께 표시하기

[질문 21] 주식시세표와 같이 등락을 아이콘 방식으로 표시할 수 있나요?

[실무실습] 아이콘 집합을 이용해 증감을 아이콘으로 표시하기

[질문 22] 상위 실적 N개에 원하는 서식을 자동으로 표시할 수 있나요?

[실무실습] 상위 실적 N개에 원하는 서식 지정하기

[질문 23] 중복된 값에 원하는 서식을 지정할 수 있나요?

[실무실습] 중복된 값에 서식 지정하기

[질문 24] 평균 이상 또는 이하인 데이터만 따로 표시할 수 있나요?

[실무실습] 평균 이상 또는 이하인 데이터만 따로 표시하기

[질문 25] 여러 개의 조건부 서식을 함께 적용할 수 있나요?

[실무실습] 여러 개의 조건부 서식 함께 적용하기

[질문 26] 조건부 서식이 설정된 범위와 규칙을 확인할 수 있나요?

[실무실습] 조건부 서식이 설정된 범위와 규칙 확인하기



Chapter 02 외부 데이터 가져오기

Section 01 외부 엑셀 파일에서 데이터 가져오기

[질문 27] 다른 파일의 표를 현재 파일로 가져올 수 있나요?

[실무실습] 외부 파일의 표를 현재 파일로 가져오기

[질문 28] 워크시트에 표가 여러 개 있을 때 원하는 표만 가져올 수 있나요?

[실무실습] 표 범위 이름 지정하여 원하는 표만 가져오기

[질문 29] 표 전체가 아닌 조건에 맞는 데이터만 선별해 가져올 수 있나요?

[실무실습] 쿼리로 조건에 맞는 데이터만 가져오기



Section 02 텍스트 파일 가져오기

[질문 30] 텍스트 파일을 엑셀로 가져오는 쉬운 방법이 있나요?

[실무실습] 구분 기호와 마법사를 이용하여 텍스트 쉽게 가져오기

[질문 31] 구분 기호가 없는 텍스트 파일은 어떻게 가져오나요?

[실무실습] 열 너비를 이용해 텍스트 가져오기

[질문 32] 여러 개의 텍스트 파일에서 필요한 데이터만 취합해 가져올 수 있나요?

[실무실습] ODBC 연결 만들어 표 연결하고 필요한 데이터 가져오기



Section 03 액세스 데이터 가져오기

[질문 33] 액세스 데이터베이스의 데이터를 가져올 수 있나요?

[실무실습] 액세스 데이터베이스에서 필요한 데이터 가져오기

[질문 34] 액세스 데이터베이스의 여러 테이블에서 데이터를 연결해 가져올 수 있나요?

[실무실습] 액세스 데이터베이스 관계와 구성 확인하고 여러 테이블에서 연결해 가져오기



Section 04 웹 데이터 가져오기

[질문 35] 웹 페이지의 데이터를 엑셀로 가져올 수 있나요?

[실무실습] 웹 쿼리로 웹 페이지의 표 데이터 가져오기

[질문 36] 고정된 페이지가 아닌 조건을 지정해 조회한 결과를 가져올 수 있나요?

[실무실습] 매크로 기록기로 주소가 변경되는 웹 페이지 데이터 가져오기

[질문 37] 웹 쿼리 기능을 이용할 때 테이블을 선택할 수 없는 경우에는 어떻게 하나요?

[실무실습] 특정 테이블을 지정해 가져오기



Part 02 데이터 집계&분석 실무 익히기



Chapter 01 피벗 테이블로 보고서 만들기

Section 01 피벗 테이블 보고서 이해하기

[질문 38] 피벗 테이블 보고서는 어떻게 사용하나요?

[실무실습] 피벗 테이블 보고서 사용하기

[질문 39] 데이터베이스에 바로 접속해서 피벗 테이블 보고서를 만들 수 있나요?

[실무실습] 데이터베이스 연결하여 피벗 테이블 보고서 구성하기

[질문 40] 피벗 테이블로 여러 개의 집계표를 합칠 수 있나요?

[실무실습] 피벗 테이블 보고서로 여러 개 집계표 요약하기

[질문 41] 엑셀 2003처럼 피벗 테이블 영역에서 직접 보고서를 구성할 수 있나요?

[실무실습] 보고서를 구성하는 다양한 방법 알아보기

[질문 42] 피벗 테이블 보고서에 삽입된 필드와 항목을 숨기고 표시하려면 어떻게 하나요?

[실무실습] 피벗 테이블 보고서의 필드 및 항목을 숨기고 표시하기

[질문 43] 피벗 테이블로 조건별 상위 데이터를 N개만 뽑아 나열할 수 있나요?

[실무실습] 피벗 테이블 보고서에서 상위 N개만 표시하기

[질문 44] 보고서 필터 영역에 삽입한 필드의 선택 값을 확인할 수 있나요?

[실무실습] 슬라이서를 이용해 피벗 테이블 보고서 구성하기

[질문 45] 피벗 테이블 보고서에 원본 표 수정 사항을 바로 반영할 수 없나요?

[실무실습] 엑셀 표와 이벤트 연계해 피벗 테이블 단점 해결하기



Section 02 그룹 필드 이용해 보고서 구성하기

[질문 46] 연, 분기, 월별로 피벗 테이블 보고서를 집계하는 쉬운 방법이 있나요?

[실무실습] 날짜 값을 갖는 필드를 그룹 필드로 묶어 관리하기

[질문 47] 원본 표에 없는 필드가 피벗 테이블 보고서에 있는 경우는 무엇인가요?

[실무실습] 항목을 직접 선택해 만드는 그룹 필드 사용하기

[질문 48] 단가별 분석 보고서를 만들기 위해 가격대별로 판매실적을 집계할 수 있나요?

[실무실습] 숫자 값을 갖는 필드의 그룹 필드 만들기

[질문 49] 비활성화된 [그룹 필드] 명령을 활성화할 수 있나요?

[실무실습] [그룹 필드] 명령을 사용하지 못할 경우 해결하기



Section 03 계산 필드와 계산 항목 만들어 보고서 구성하기

[질문 50] 거래처별 판매실적을 피벗으로 정리할 경우 부가세를 계산해 넣을 수 있나요?

[실무실습] 계산 필드 만들고 사용하기

[질문 51] 구분 열의 입출고 항목으로 재고를 계산할 수 있나요?

[실무실습] 계산 항목을 사용해 재고 계산하기

[질문 52] 피벗 테이블 보고서의 총합계 열을 맨 앞에 표시할 수 있나요?

[실무실습] 계산 항목으로 총합계 열 위치 변경하기

[질문 53] 피벗 테이블의 계산 필드나 항목의 계산식을 빠르게 확인할 수 있나요?

[실무실습] 수식 보고서와 계산 필드 사용하기



Section 04 값 표시 형식을 이용해 보고서 구성하기

[질문 54] 피벗 테이블 보고서의 집계 값의 비율을 표시할 수 있나요?

[실무실습] 값 표시 형식으로 비율 표시하기

[질문 55] 피벗 테이블 보고서에서 증감률을 표시할 수 있나요?

[실무실습] 피벗 테이블 보고서에 증감률 표시하기

[질문 56] 피벗 테이블 보고서에서 누계를 집계할 수 있나요?

[실무실습] 피벗 테이블 보고서에 누계와 누계 비율 표시하기

[질문 57] 피벗 테이블 보고서로 순위를 집계할 수 있나요?

[실무실습] 피벗 테이블 보고서에 순위 표시하기



Section 05 기타 유용한 피벗 테이블 보고서 기법 익히기

[질문 58] 고객 업체와의 거래 기간, 거래 횟수 등을 정리하는 보고서를 만들 수 있나요?

[실무실습] 다양한 형식으로 요약하여 집계하기

[질문 59] 피벗 테이블에서 중복되지 않은 항목을 셀 수 있나요?

[실무실습] 피벗 테이블에서 중복되지 않은 건수 세기

[질문 60] 목표 대비 달성률을 피벗 테이블 보고서로 집계할 수 있나요?

[실무실습] 여러 표 데이터를 하나의 피벗 테이블 보고서에서 분석하기

[질문 61] 값 영역에 등급 같은 텍스트를 표시할 수 있는 방법이 있나요?

[실무실습] 피벗 테이블 보고서의 값 영역에 텍스트 표시하기

[질문 62] 피벗 차트를 사용하는 좋은 방법이 있나요?

[실무실습] 피벗 차트 사용하기



Chapter 02 차트를 이용해 보고서 시각화하기

Section 01 차트 바르게 사용하기

[질문 63] 실적을 비교할 경우 어떤 차트를 사용하면 좋나요?

[실무실습] 세로 막대형 차트 구성하기

[질문 64] 실적을 분야별로 비교하려면 어떤 차트를 사용하나요?

[실무실습] 가로 막대형 차트 구성하기

[질문 65] 긴 기간의 데이터 추이를 표시할 때 어떤 차트가 좋나요?

[실무실습] 꺾은선형 차트 구성하기

[질문 66] 영업사원별 실적을 한눈에 보기 좋게 표시하려면 어떤 차트를 사용하나요?

[실무실습] 원형 차트와 도넛형 차트 구성하기

[질문 67] 2가지 항목의 연관성을 설명하고자 할 때 차트를 활용할 수 있나요?

[실무실습] 분산형 차트 구성하기

[질문 68] 적은 공간에 차트를 효율적으로 표시할 수 있는 방법이 있나요?

[실무실습] 스파크라인 구성하기

[질문 69] 스파크라인의 승패는 언제 사용하나요?

[실무실습] 승패 스파크라인 구성하기



Section 02 실무에 적합한 차트 구성하기

[질문 70] 차트에 그림을 이용하여 꾸밀 수 있나요?

[실무실습] 차트에 그림 넣기

[질문 71] 긴 막대그래프를 잘라 표시할 수 없나요?

[실무실습] 로그 차트 구성하기

[질문 72] 단위 차가 큰 계열을 동시에 표시할 수 있나요?

[실무실습] 이중 축 혼합형 차트 구성하기

[질문 73] 차트 서식을 저장하여 사용할 수 있나요?

[실무실습] 차트 서식 파일 사용하기



Chapter 03 집계, 통계 작업을 위한 수식과 기술 통계법 이해하기

Section 01 실무에서 자주 사용하는 계산식 이해하기

[질문 74] 비율은 수식을 이용해 어떻게 계산하나요?

[실무실습] 비율을 계산하는 수식 만들기

[질문 75] 할인율이나 증감률 등은 어떻게 계산하나요?

[실무실습] 할인율이나 증감률 계산하기

[질문 76] 비율을 계산할 때 음수가 포함된 경우에는 어떻게 하나요?

[실무실습] 음수가 포함된 비율 계산하기

[질문 77] 평균 비율은 어떻게 계산하나요?

[실무실습] 평균 성장률 계산하기



Section 02 기술 통계법을 이용한 데이터 이해하기

[질문 78] 모집단과 표본은 무엇인가요?

[실무실습] 집계와 통계의 차이 구분하기

[질문 79] 평균 이외에 데이터를 대표할 수 있는 것으로는 무엇이 있나요?

[실무실습] 대표값을 이용해 데이터 이해하기

[질문 80] 입사 가능 회사의 동일 직급 연봉을 안다면, 이것으로 어떤 정보를 알 수 있나요? 337

[실무실습] 산포도 구하고 데이터 이해하기

[질문 81] 분산, 표준 편차와 같은 산포도를 좀 더 시각적으로 표시할 수 있나요?

[실무실습] 산포도 차트 구성하기

[질문 82] 데이터를 보다 잘 이해하기 위해 구간별로 데이터 건수를 셀 수 있나요?

[실무실습] 도수 분포표 구하기

[질문 83] 도수 분포표를 이용해 히스토그램 차트를 만들 수 있나요?

[실무실습] 히스토그램 차트 구성하기

[질문 84] 한번에 기술 통계 값을 얻을 수 있나요?

[실무실습] 분석 도구 추가 기능 설치하고 사용하기



Part 03 데이터 분석 & 예측 실무 익히기



Chapter 01 고객 및 제품 분류 기법 사용하기

Section 01 ABC 분석 기법 사용하기

[질문 85] ABC 분석을 위해 필요한 데이터는 어떤 것들이고 무엇을 집계해야 하나요?

[실무실습] ABC 분석을 하기 위해 데이터 집계하기

[질문 86] A, B, C 등급은 어떻게 부여하나요?

[실무실습] A, B, C 등급 부여하기

[질문 87] ABC 분석으로 등급을 지정한 표를 차트로 표시할 수 있나요?

[실무실습] 파레토 차트 구성하기

[질문 88] ABC 분석 결과를 어떻게 이해해야 하나요?

Section 02 RFM 분석을 이용해 고객 분류하기

[질문 89] RFM 분석을 위해서는 어떤 데이터가 있어야 하고 집계는 어떻게 해야 하나요?

[실무실습] RFM 분석을 위해 집계표 구성하기

[질문 90] 집계한 데이터를 어떻게 분류하고 등급을 나눌 수 있나요?

[실무실습] 분석 항목별로 점수를 부여할 구간 정리하기

[질문 91] R, F, M별 점수를 통해 고객을 어떻게 분류해야 하나요?

[실무실습] 고객 등급 부여하기



Chapter 02 회귀 분석으로 데이터 예측 기법 사용하기

Section 01 회귀 분석 입문을 위한 추세선 이용하기

[질문 92] 과거 실적을 집계한 표에 다음 분기의 판매량을 예측할 수 있나요?

[실무실습] 추세선 선택과 미래 값 예측하기

[질문 93] 추세선 선택을 위한 가이드라인이 있나요?

[실무실습] 결정계수를 참고해 올바른 추세선 선택하기

[질문 94] 추세선의 결정계수 값을 높일 수 있나요?

[실무실습] 추세선의 결정계수 값 높이기

[질문 95] 추세선으로 예측된 구간의 값은 어떻게 계산하나요?

[실무실습] 회귀 방정식으로 추세선의 미래 구간 계산하기



Section 02 산점도로 두 변수 사이의 상관관계 설명하기

[질문 96] 광고 효과를 분석할 때 매출과 연관성이 높은 항목을 어떻게 선별하나요?

[실무실습] 산점도 차트로 광고 효과 분석하기

[질문 97] 연관성 있는 항목을 좀 더 자세하게 분석할 수 있나요?

[실무실습] RSQ 함수로 결정계수 값 계산하기

[질문 98] 하나의 산점도 차트에서 계열을 나눠 표시할 수 있나요?

[실무실습] 산점도 차트에서 계열 구분해 표시하기



Section 03 보간법을 이용해 점 사이 값 계산하기

[질문 99] 광고횟수와 매출이 연관성이 있을 때 이벤트 횟수로 매출을 계산할 수 있나요?

[실무실습] 선형 보간법으로 X0 값 계산하기

[질문 100] 보간법을 이용할 때 두 점을 선형이 아닌 곡선으로 연결할 수 있나요?

[실무실습] 2차 다항식 보간법 이용하기



Chapter 03 고급 회귀 분석 및 시나리오 기법 사용하기

Section 01 일정 주기를 반복하는 데이터 예측하기

[질문 101] 일정 주기를 반복하는 데이터는 어떻게 예측하나요?

[실무실습] 일정 주기를 반복하는 데이터 예측하기

[질문 102] 월별 지수를 어떻게 계산하고 반영하나요?

[실무실습] 월별 지수(계절 지수) 계산하기

[질문 103] 선형과 지수 추세를 확인한 후 예측은 어떻게 하나요?

[실무실습] 추세로 미래 값 예측하기

[질문 104] 더미변수를 사용하는 방법이 있다고 합니다. 더미변수가 무엇인가요?

[실무실습] 더미변수 사용하기

[질문 105] 더미변수로 구한 추세 값에서 추세를 선택하는 방법은 이전과 동일한가요?

[실무실습] 더미변수로 미래 값 예측하기



Section 02 목표 달성을 위한 다중 회귀 분석으로 데이터 예측하기

[질문 106] 매출에 영향을 끼치는 변수를 어떻게 수집하나요?

[실무실습] 매출에 영향을 끼치는 내부 변수 집계하기

[질문 107] 피벗 테이블 보고서와 외부에 집계한 데이터로 매출을 예측할 수 없나요?

[실무실습] 상관관계 분석으로 예측에 필요한 변수 선별하기

[질문 108] 정리된 변수로 매출을 예측하면 안 되나요?

[실무실습] 변수의 모형 구성하고 조정 결정계수 확인하기

[질문 109] 선택된 모형으로 어떻게 매출을 예측할 수 있나요?

[실무실습] 다중 회귀 분석을 이용해 매출 예측하기



Section 03 목표 달성을 위한 시나리오 설계하기

[질문 110] 예측된 결과와 목표가 다릅니다. 목표를 어떻게 수정하나요?

[실무실습] 목표값 찾기로 계획 수정하기

[질문 111] 매출 목표를 달성하기 위해 계획을 수정할 필요가 있는 경우에는 어떻게 하나요?

[실무실습] 해 찾기로 계획 수정하기

[질문 112] 목표를 달성하기 위한 시나리오를 필요에 따라 선택하는 방법이 있나요?

[실무실습] 시나리오 선택에 따른 계획 확인하기

[질문 113] 시나리오를 자동으로 등록할 수는 없나요?

[실무실습] 해 찾기로 시나리오 등록하고 사용하기

[질문 114] 등록된 시나리오를 보기 좋게 정리하려면 어떻게 해야 하나요?

[실무실습] 등록된 시나리오로 요약 보고서 만들기



부록

부록 1 OFFSET 함수를 사용한 동적 범위 참조하기

부록 2 해 찾기 제대로 활용하기



찾아보기

최준선