엑셀 좀 하는 직장인에게 권하는 엑셀 데이터 분석 & 예측 기술

Part 01 데이터 관리 및 추출

Chapter 01 엑셀에서 데이터 관리하기
01 표 구분하기
Q 001 엑셀에서는 데이터를 어떻게 관리해야 하나요?
Q 002 일별로 정리된 표를 월별, 분기별로 다시 정리하려면 어떻게 해야 하나요?

02 표(테이블) 구성하기
Q 003 표를 구성할 때 무엇을 주의해야 하나요?
Q 004 데이터를 좀 더 쉽게 입력할 수는 없나요?

03 엑셀 표로 전환하기
Q 005 엑셀 표가 뭐예요?
Q 006 엑셀 표에 수식을 사용하려면 어떻게 해야 하나요?
Q 007 엑셀 표의 데이터를 표 외부에서 참조하려면 어떻게 해야 하나요?
Q 008 엑셀 표로 변환한 다음 요약 행은 어떻게 표시하나요?
Q 009 엑셀 표를 이용해 데이터를 공유할 수 있다던데 어떻게 해야 하나요?
Q 010 엑셀 표를 다시 범위로 변환하려면 어떻게 해야 하나요?
Q 011 엑셀 2007 이전 버전에서 추가된 데이터 범위를 자동 인식하려면 어떻게 해야 하나요?

04 유효성 검사를 이용해 셀에 입력되는 값 제한하기
Q 012 셀에 영어 또는 한글 등 입력할 언어를 지정해 놓을 수 있나요?
Q 013 셀에 입력될 값을 드롭 다운 목록에서 선택하려면 어떻게 하나요?
Q 014 드롭 다운 목록을 서로 연동할 수 있나요?
Q 015 셀에 입력될 숫자 값을 제한할 수 있나요?
Q 016 셀에 입력될 날짜 값을 특정 기간 내로 제한할 수 있나요?
Q 017 사업자 등록 번호와 같이 셀에 입력될 문자 개수를 정해 놓고 입력할 수 있나요?
Q 018 중복 값이 입력되지 않도록 하려면 어떻게 하나요?
Q 019 중복 조건이 여러 열에 나눠져 있는 경우에는 어떻게 하면 되나요?
Q 020 유효성 검사를 설정한 범위를 확인하고 지우려면 어떻게 하면 되나요?

05 조건부 서식을 이용해 데이터 시각화하기
Q 021 지난 몇 년간의 월별 매출 실적표를 정리했는데 어떤 서식을 지정해야 깔끔할까요?
Q 022 영업 담당자별 실적표를 한눈에 이해할 수 있도록 서식을 설정하려면 어떻게 해야 하나요?
Q 023 조건부 서식을 이용해 주식 시세표와 같은 증감 표시를 할 수 있나요?
Q 024 상위 n개 또는 하위 n개 데이터에만 서식을 설정할 수 있나요?
Q 025 중복된 값 또는 고유 값에만 서식을 적용할 수 있나요?
Q 026 영업 담당자 실적표에서 목표 달성율이 평균 이하인 담당자를 어떻게 표시하나요?
Q 027 여러 개의 조건을 적용해야 하는 경우에는 어떻게 해야 하나요?
Q 028 조건부 서식이 설정된 데이터 범위를 눈으로 확인할 수 있나요?

06 데이터 관리에 도움이 되는 참조 수식 작성
Q 029 참조 연산자가 뭐죠?
Q 030 다른 위치에 있는 셀의 값을 참조할 때 어떻게 해야 하나요?
Q 031 셀 주소에 표시되는 $는 무슨 의미인가요?
Q 032 VLOOKUP 함수를 이용해 값을 참조하려면 어떻게 해야 하나요?
Q 033 VLOOKUP 함수로 참조하지 못하는 값은 어떻게 해야 하나요?
Q 034 조건이 여러 개인 경우에는 값을 어떻게 참조하면 되나요?

07 데이터 관리에 필요한 편집 수식 작성하기
Q 035 특정 문자열에서 원하는 값만 잘라내려면 어떻게 해야 합니까?
Q 036 주민등록번호에서 나이와 성별을 표시하려면 어떻게 해야 하나요?
Q 037 전화번호의 지역번호처럼 잘라낼 문자수가 불규칙할 때는 어떻게 해야 하나요?
Q 038 전체 주소에서 우편번호용 주소와 세부 주소를 나누려는데 어떻게 해야 하나요?

Chapter 02 데이터 가져오기
01 외부 엑셀 파일에서 데이터 가져오기
Q 039 다른 통합 문서의 테이블을 전부 가져올 수 있나요?
Q 040 A1셀부터 기록되어 있지 않은 테이블을 가져오려면 어떻게 해야 하나요?
Q 041 다른 파일의 테이블에서 원하는 조건에 맞는 데이터만 가져올 수 있나요?

02 텍스트 파일 가져오기
Q 042 쉼표로 구분된 텍스트 파일을 엑셀로 가져오려면 어떻게 하면 되나요?
Q 043 구분자가 없는 텍스트 파일은 어떻게 가져올 수 있나요?
Q 044 여러 개의 텍스트 파일을 연결해 필요한 데이터만 가져올 수 있나요?

03 액세스 데이터 가져오기
Q 045 액세스에서 필요한 데이터 가져오는 방법은?
Q 046 전체 데이터는 너무 많아 최근 데이터만 가져오고 싶은데 어떻게 해야 하나요?

04 웹 데이터 가져오기
Q 047 웹 페이지에 존재하는 환율 데이터를 엑셀로 가져올 수 있나요?
Q 048 환율처럼 가져올 웹 주소가 고정되지 않고 날마다 달라지는 경우에는 어떻게 해야 하나요?

Chapter 03 필요한 데이터 추출하기
01 정렬을 이용한 데이터 추출하기
Q 049 표를 정렬하는 방법에는 어떤 것들이 있나요?
Q 050 표를 정렬한 다음 원래 순서대로 복원할 수 있나요?
Q 051 셀에 적용된 색을 기준으로 표를 정렬할 수 있나요?
Q 052 내림차순, 오름차순 말고 내가 원하는 순서대로 정렬할 수 있나요?
Q 053 가로(=열) 방향으로 표를 정렬할 수 있나요?
Q 054 표의 열 위치를 내 맘대로 정렬할 수 있나요?

02 자동 필터를 이용한 데이터 추출하기
Q 055 자동 필터를 어떻게 사용하나요?
Q 056 빈 셀이 포함된 행을 모두 삭제하고 싶은데 어떻게 해야 하나요?
Q 057 전체 거래내역 데이터에서 특정 기간이나 특정 시간대에 해당하는 데이터만 추출할 수 있나요?
Q 058 평균 이하인 데이터만 추출할 수 있나요?
Q 059 특정 기간 내의 판매량 상위 10개 제품 목록을 뽑을 수 있나요?
Q 060 원하는 색상을 가진 데이터만 추출할 수 있나요?
Q 061 고객 데이터를 가나다 순으로 추출할 수 있나요?

03 고급 필터를 이용한 데이터 추출하기
Q 062 고급 필터는 어떻게 사용하나요?
Q 063 고급 필터를 이용해 중복 데이터에서 고유 항목만 뽑아낼 수 있나요?
Q 064 특정 기간 동안의 데이터만 추출할 수 있나요?
Q 065 고급 필터 기능을 이용해 다른 워크시트로 데이터를 추출할 수 있나요?
Q 066 조건을 제대로 구성한 것 같은데 추출된 데이터가 잘못 나옵니다. 왜 그런가요?
Q 067 두 범위를 비교해 어느 한쪽에만 있는 데이터를 추출할 수 있나요?


Part 2 피벗 테이블을 이용한 데이터 요약, 집계, 분석

Chapter 04 피벗 테이블을 이용한 보고서 작성하기
01 피벗 테이블 보고서 이해하기
Q 068 피벗 테이블 보고서는 어떻게 만드나요?
Q 069 외부 데이터베이스에 연결해 피벗 테이블 보고서를 만들 수 있나요?
Q 070 여러 군데 흩어져 있는 데이터를 피벗 테이블 보고서로 통합할 수 있나요?
Q 071 피벗 테이블 보고서의 구성을 변경하려면 어떻게 해야 하나요?
Q 072 원하지 않는 필드나 항목을 표시하지 않으려면 어떻게 해야 하나요?
Q 073 보고서에 표시되는 필드의 레이블 명을 수정할 수 있나요?
Q 074 피벗 테이블 보고서의 값을 정렬하려면 어떻게 하나요?
Q 075 각 영업 담당자들이 가장 많이 판매한 상위 2개 제품 목록을 뽑는 것이 가능한가요?

02 그룹 필드를 이용해 보고서 구성하기
Q 076 원본 데이터에 날짜 값이 존재할 경우 년, 분기, 월 등 다양한 기준에 따라 집계할 수 있나요?
Q 077 원본 데이터에 부서 정보가 없는데 피벗 테이블로 부서를 나눠 매출을 집계할 수 있나요?
Q 078 제품 단가를 구분해 집계 보고서를 만드는 것이 가능한가요?
Q 079 그룹 필드가 더 이상 필요하지 않아 삭제하고 싶은데 어떻게 해야 하나요?

03 계산 필드, 계산 항목을 만들어 보고서 구성하기
Q 080 피벗 테이블을 이용해 고객 업체별 거래내역 보고서를 만들었는데 세액을 함께 표시할 수 없나요?
Q 081 제품별 입출고 내역에서 재고를 어떻게 계산하는 것이 가장 좋나요?
Q 082 총합계 열을 맨 앞으로 표시할 수 있나요?
Q 083 계산 필드와 계산 항목을 수정하거나 삭제하려면 어떻게 해야 하나요?

04 값 표시 형식을 이용해 보고서 구성하기
Q 084 실적이 가장 우수한 사원 대비 타 사원을 비교하는 보고서를 피벗 테이블로 작성할 수 있나요?
Q 085 피벗 테이블로 월별 증감율과 목표 대비 달성율을 표시하는 것이 가능한가요?
Q 086 일별 누계 등의 작업을 피벗 테이블로 처리할 수 있나요?
Q 087 영업 사원의 실적을 집계할 때 각 직원의 실적 비율을 표시할 수 있나요?

05 기타 유용한 피벗 테이블 보고서 만들기
Q 088 원본 데이터 범위가 수정되면 수정된 범위를 자동으로 인식하게 할 수 있나요?
Q 089 원본 데이터의 수정 사항을 바로 반영하는 피벗 테이블 보고서를 만들 수 있나요?
Q 090 값 영역에 텍스트 값을 표시하는 보고서를 만들 수 있나요?
Q 091 고객별로 가장 최근 거래일을 표시하는 피벗 테이블 보고서를 만들 수 있나요?

06 피벗 차트 만들기
Q 092 피벗 차트는 어떻게 만드나요?
Q 093 하나의 피벗 테이블과 연결된 피벗 차트를 여러 개 만들 수 있나요?
Q 094 피벗 테이블을 변경해도 피벗 차트가 변경되지 않도록 하려면 어떻게 해야 하나요?

Chapter 05 차트를 이용해 요약 데이터 설명하기
01 차트 선택하기
Q 095 데이터를 비교해야 할 때는 어떤 차트 종류를 선택하는 것이 좋은가요?
Q 096 두 집단의 여러 항목을 비교할 때는 어떤 차트를 사용하는 것이 좋나요?
Q 097 시간 추이에 따른 데이터 변화를 차트로 표시할 때 좋은 차트는 무엇인가요?
Q 098 전체 판매 비율을 차트에 표시하려 할 때는 어떤 차트가 좋을까요?
Q 099 매출과 이벤트의 관련성을 차트로 표시하려면 어떤 차트를 사용해야 하나요?

02 차트 응용하기
Q 100 여러 개의 차트를 하나의 차트로 나타낼 수 있나요?
Q 101 판매수량과 매출과 같이 단위 차가 큰 데이터를 하나의 차트에서 모두 표시할 수 있나요?
Q 102 총합계를 차트에 표시하니 다른 것들이 잘 표시되지 않습니다. 어떻게 하면 되나요?
Q 103 매번 차트를 만들지 않고 내가 만들어 놓은 차트를 등록해 쓸 수도 있나요?


Part 03 데이터 분석 실무

Chapter 06 제품 관리 방안 수립을 위한 제품 분류 방법
01 ABC 분석을 이용해 제품 분류하기
Q 104 ABC 분석을 위해 필요한 데이터는 어떤 것들이고 뭘 집계해야 하나요?
Q 105 A, B, C 등급은 어떤 기준에 따라 부여해야 하나요?
Q 106 ABC 분석으로 구한 결과를 차트로 표시할 수 있나요?
Q 107 ABC 분석 결과로 얻은 내용을 어떻게 이해해야 하나요?

02 교차비율 분석을 이용해 제품 상세 관리하기
Q 108 상품 회전율은 어떻게 구하나요?
Q 109 매출 이익률은 어떻게 구하나요?
Q 110 교차비율은 어떻게 구하나요?
Q 111 교차비율을 구한 제품을 어떻게 분류하나요?
Q 112 교차비율을 구한 제품의 각 포지션별 방안 수립은 어떻게 해야 하나요?

03 특정 제품의 판매실적 변화 예측하기
Q 113 제품별 판매실적을 집계한 후 내년 1사분기 판매량을 예측하고 싶은데 어떻게 해야 하나요?
Q 114 추세선은 종류도 많은데 어떤 추세선을 선택해야 하나요?
Q 115 추세선으로 표시된 예측 값을 워크시트에 표시하려면 어떻게 해야 하나요?
Q 116 특정 제품이 일정한 주기를 가지고 판매 사이클이 반복되는 경우에는 어떻게 해야 하나요?
Q 117 이진변수를 이용한 예측 방법은 어떻게 하는 건가요?

Chapter 07 고객 관리 방안 수립을 위한 고객 분류 방법
01 RFM 분석을 이용한 고객 분류 방법
Q 118 RFM 분석을 위해서는 어떤 데이터가 있어야 하고 집계는 어떻게 해야 하나요?
Q 119 집계된 데이터를 어떤 기준으로 분류해야 하나요?
Q 120 R, F, M별 점수를 통해 고객을 어떻게 분류해야 하나요?

Chapter 08 매출 목표 수립을 위한 예측 및 보완 기법
01 목표 달성을 위한 계획 수립 및 매출 예측
Q 121 매출에 영향을 미치는 요소를 어떻게 수집해야 하나요?
Q 122 수집된 데이터를 이용해 매출을 예측하나요?
Q 123 이제 추려진 데이터로 매출을 예측할 수 있나요?
Q 124 선택된 모형으로 매출을 어떻게 예측할 수 있나요?

02 목표 달성을 위한 계획 수정하기
Q 125 예측된 결과와 목표가 달라 계획을 일부 수정하고 싶은데 어떻게 해야 하나요?
Q 126 매출 목표를 달성하기 위해 계획을 전반적으로 수정할 필요가 있는 경우에는 어떻게 해야 하나요?

03 시나리오를 통한 의사 결정 보고서 만들기
Q 127 시나리오는 어떤 기능인가요?
Q 128 시나리오를 등록할 때 값을 일일이 등록해 주어야 하는데 그 방법 밖에 없나요?
Q 129 등록된 시나리오로 요약 보고서를 만들려면 어떻게 해야 하나요?

Appendix 1 사용자 지정 숫자 서식
Appendix 2 엑셀 2007에서 매크로 사용
Appendix 3 동적 범위 참조 방식
Appendix 4 해 찾기 명령