[Part 5-3, 6] 엑셀로 끝내는 실전 데이터 분석 강의 - SUMPRODUCT, MATCH, INDEX, Superstore 대시보드 만들기, 총 정리

---

목차

-

5. 엑셀 전문가로 거듭나기
손익계산서(raw data다루기, 피벗테이블, 양식 만들기, SUMIFS로 합계 구하기[raw data연동], 분기와 누계 데이터 추가) / 월별 손익 조회 장표 / 재직증명서 만들기 / 상품재고현황 파악 / 간트차트 그리기 / 대시보드 만들기 / OFFSET 함수 / OFFSET함수와 동적 범위 / SUMPRODUCT / MATCH, INDEX의 개념과 활용

6. 리더에게 인정받는 자료 작성법
보고자료 만들기 / Superstore 대시보드 만들기 / 엑셀 총정리

---

SUMPRODUCT

SUMPRODUCT ( 배열1, 배열2, ... ) + Ctrl + Shift + Enter
: 조건에 맞는 데이터 더해서 곱하기

예를 들어 이렇게 생긴 데이터에서 1월에 해당하는 데이터 중 물류창고가 이천센터인 값의 총액만 더하고 싶은 경우

raw

new 입력할 새 시트

방법1) SUMIFS

(조건을 찾을 범위, 조건 값, 더할 값들이 있는 범위) 

• SUMIFS ( 총액 K열 raw, 연도 D열 raw, 1월 D$4 new, 물류창고 G열 raw, 이천센터 $B6 new )

방법2) SUMPRODUCT

1. 이름 관리자(Ctrl+F3)에서 동적 범위 만들어주기 - 월범위, 총액범위, 물류센터범위
   • 이전에 했던 것처럼, =OFFSET($G$33,,,COUNTA($G:$G)-1) ← OFFSET(이천센터,물류창고-1) 
2. SUMPRODUCT ( 월범위=D$4 * 물류센터범위=$B6 * 총액범위 ) 
3. 단위 백만원이라 표기해주고, 셀 서식(Ctrl+1)에서 '0,0,,_-' (숫자 중간에 쉼표 붙이고, 맨 뒤 한 칸 띄우는 형식)
4. 열 너비 통일해주고, 가운데 정렬

---

고급 함수 MATCH, INDEX

MATCH ( lookup_value, lookup array, [match type] )
: 찾고자 하는 값이 특정 행/열 범위의 몇 번째에 있는지 알려주는 함수로
단독으로 사용되기 보다는 INDEX등의 함수와 함께 사용됨

• MATCH ( 찾고싶은 값, 찾고싶은 값이 포함된 단일 열/또는 행 범위, 근사 여부 )
  • match_type : 1(default)=찾을 값보다 작은 값들 중 최대값, 0=찾을 값과 정확히 일치하는 값, -1=찾을 값보다 큰 값들 중 최소값

INDEX ( array, row_num, [column_num], [area_num] )
: 선택 범위에서 찾고자 하는 값의 행/열 번호를 활용하여 vlookup처럼 값을 불러옴

• INDEX ( 해당 데이터의 범위, 찾고자 하는 데이터의 행 번호, 찾고자 하는 데이터의 열 번호, 범위 선택[array가2개이상일때] )
  • row_num과 col_num에 직접 숫자를 입력하는 대신, match로 찾은 행/열번호가 입력된 셀을 대신 입력하는 경우가 많다.

VLOOKUP의 한계

• VLOOKUP ( 찾을 데이터, 공통 기준 열을 기준으로 찾아올 데이터 범위, 찾아올 데이터 열 번호, 0 OR 1 ) 
• VLOOKUP의 한계는 기준 열을 중심으로 '오른쪽'의 데이터만 불러올 수 있다는 점

=> 이러한 한계점을 보완하는 게 MATCH + INDEX 함수 조합

• 하지만 함수를 중첩하여 써야하기 때문에 raw data 열 위치를 변경할 수 있거나, 우측에 있는 데이터를 불러오는 일에서는 최대한 vlookup을 사용하기

 

---

6. 리더에게 인정받는 자료 작성법

6-1. 보고자료 만들기

 

피벗 테이블 분석 - "슬라이서 삽입"

• 사용 사례 : 센터별로 슬라이서를 누르면 해당 센터의 월별금액을 나타내는 그래프만 볼 수 있도록 하고 싶은 경우
  1. 슬라이서 삽입 : 센터가 10개인 경우, 슬라이서를 삽입한 뒤, 열의 개수를 10개로 지정하여 옆으로 슬라이서 하나하나 누를 수 있도록 만들어주기
  2. IF 함수 사용 : =IF(센터명 =  피벗 테이블 속 센터명, 1, 0)
  3. IF 함수 한번 더 사용 : IF(IF(센터명 = 피벗 테이블 속 센터명, 1, 0)=1,월별금액,0))
  4. 슬라이서 설정 :  '머릿글 표시'를 해제해줘서 버튼처럼 사용

선택시 IF함수가 적용된 모습

슬라이서에서 선택된 센터만 하이라이트 되는 모습

 

피벗 테이블 슬라이서

 

---

조건부 서식 활용

1. 위에서 사용했던 함수 그대로 사용 => IF 함수 사용 : =IF(센터명 =  피벗 테이블 속 센터명, 1, 0)
2. 조건부 서식에서 IF 함수를 적용한 셀이 1일경우, 특정 셀 서식이 적용되도록 설정해주기 (EX. =$P$24=1) 

---

6-2. Superstore 대시보드 만들기

1. Superstore raw data 살펴보기
   • '중복된 항목 제거' 기능으로 데이터의 고유값을 확인하여 구조 파악하기
   • '피벗 테이블'을 생성하여 데이터의 구조 파악하기
2. 쓰지 않을 데이터 (EX. Ship date, Customer ID, Country [모두 같은 국가], Postal Code, Product ID, Product Name, Quantity, Discount ..) 삭제하기
3. raw data 가공하기
   1. 여러 정보를 담고 있는 셀 Order Date를 Year, Month, Day 함수를 사용하여 년,월,일로 나누어 써주기
4. summary 시트 만들기
   1. SUMIFS로 지역별 매출액 입력하기 
      • 2017년 11월의 도시별 이익 입력하기
      • SUMIFS ( 이익 raw, 연도 raw, 2017년 new, 월 raw, 1월 new, 도시 raw, 도시명 new ) 
   2. 연도별 데이터 확인할 수 있도록 목록 만들기 - 데이터 유효성 검사 - 목록, 2014년, 2015년, 2016년, 2017년

---

대시보드를 만들기 위한 back date 만들기 ~ 그래프 그리기

1. 연 피벗테이블 설정
2. 월 피벗테이블 설정
   • 각각 '연도', '월'만 들어간 피벗테이블을 만들기
   • 1,2=> 각각 슬라이서 삽입
   • '월'은 가로로 슬라이서를 나열하기 위하여 열 개수를 12개로 지정
3. 지역 Region 피벗테이블 설정
4. 주 State 피벗테이블 설정
5. 도시 City 피벗테이블 설정
   • => 각각 슬라이서 삽입
   • '피벗테이블 옵션'에서 이름을 '연도구분', '월구분', '지역구분', '주구분', '도시구분'으로 지정해주기
   • Region 슬라이서에 나머지 슬라이서를 연결해주기 (하위항목만) - '보고서 연결'
     • Region은 지역구분, 주구분, 도시구분 체크해주기
     • State는 도시구분 체크해주기  
   • 슬라이서 설정 - 머릿글 표시 체크 해제, 데이터가 없는 항목 숨기기 체크해주기
6. 막대그래프 raw 만들기
   • 월별 매출 총액 데이터 만들기
   • 구분|1월,2월... / 총액 / 총액_차트(슬라이서에 해당하는 데이터만 하이라이트 하기위함)
     • 총액은 sumifs로 불러오기 ( 매출액 raw, 총액 new, 연도 raw, 2015년 new, 월 raw, 1월 new )
     • 총액_차트는 if함수로 '구분'의 월이 피벗테이블 속 월과 같으면, 1, 아니면 0 출력하도록 설정
7. 막대그래프 그리기
8. 원형차트 raw 만들기
   • 지역별 매출을 비교하기 위함
   • 구분|Central, East, South, West, 총액 , 위에서와 동일하게 sumifs로 총액 불러오기
9. 원형차트 그리기
10. 주별 raw 피벗 만들기
    • 행에 연,월 넣고
    • 열에 지역 Region과 주 State가 들어가고
    • 값에 Sales가 들어감
11. 주별 영역 그래프 그리기
    • 피벗 차트 분석 탭 - 필드 단추 - 모두 숨기기
12. 주별 raw 피벗 만들기      
13. 주별 꺾은선 그래프 그리기
14. 카테고리별 피벗 만들기
15. 카테고리별 원형차트 그리기
    • 생성한 차트 모두 선택, 우클릭 - '크기 및 속성' - 속성 - 변하지 않음에 체크
    • 윤곽선 없애고, 색상 전체적으로 맞춰주기

---

6-3. 엑셀 총정리

• Ctrl + 9 : 숨기기
• 숨긴 셀을 제외하고 셀 서식과 같은 일괄적인 처리를 하기위해 '화면에 보이는 셀 선택' 기능 활용
• IF
  • 목적 : 데이터 분류
  • IF ( 참/거짓을 나눌 조건 값 , 참일 경우 표시할 값 , 거짓일 경우 표시할 값 )
• VLOOKUP
  • 목적 : 특정 열을 기준으로 N번째에 있는 데이터를 찾아 오는 함수
  • VLOOKUP ( 찾을 기준 , 공통 기준 열을 기준으로 찾아올 데이터의 '범위' , 찾아올 데이터의 '열 번호' , 0 OR 1 )
  • 행, 열 고정에 주의 (절대참조)
• COUNTIFS
  • 목적 : 조건에 맞는 데이터 개수 구하기
  • COUNTIF ( 개수를 세어야할 값들이 있는 셀의 '전체 범위', range에서 세어야하는 '조건 값')
  • COUNTIFS ( 전체 범위1, 조건 값1, 전체 범위2, 조건값 2...)
• SUMIFS
  • 목적 : 조건에 맞는 데이터 합계 구하기
  • SUMIF ( 조건을 찾을 범위, 조건 값, 더할 값들이 있는 범위 )
• IFERROR
  • IFERROR ( value , value_if_error )
• 조건부 서식
  • 규칙 유형 : 셀 값을 기준으로 모든 셀의 서식 지정 / 포함하는 셀만 지정 / 상위,하위 값만 지정 / 평균 이상,이하 값만 지정 / 고유,중복 값만 지정 / 수식을 사용하여 서식을 지정할 셀 결정
  • 서식 스타일 : 데이터 막대, 아이콘 집합

---

엑셀 사용 시
사용자의 입장에서, 목적에 부합하도록 자료를 구성하자.

• 사용자의 입장에서 : 사용자가 누구인지 (직급, 연령, 상황), 무엇을 선호하는지, 쉽게 파악할 수 있는지
• 목적에 부합하도록 : 왜 이 자료가 필요한지, 어떤 관점이 있는지, Output이 무엇인지
• 자료를 구성하는 것 : MECE, Visualization, Simple & Easy
• 마지막 팁
  • 마우스 커서는 항상 A1(문서의 가장 위)에
  • 구분 자를 중심으로 틀 고정 
  • Sheet이름 꼭 써주기
  • Sheet가 많은 경우 빈 Sheet로 구분해주기
  • 사용자가 보기 좋은 비율 설정 (75%~100%)

---