[Part 5-2] 엑셀로 끝내는 실전 데이터 분석 강의 - 간트 차트, 대시보드 만들기, OFFSET(&이름 관리자), 동적 범위

---

목차

-

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

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

---

간트차트 그리기

• 일정대로 업무가 진행되고 있는지, 프로젝트에 차질은 없는지 확인하기 위해 자주 활용된다.

raw data 예시

1. start date - days - end date 입력하기

• End date = Start date + Days - 1
  • 다음 Start date = 이전 End date + 1 후, 붙여넣기
• Start date와 End date만 주어지고, Days가 주어지지 않은 경우 (간트차트 그릴 때에는 Days정보 필수)
  • 반대로 End date에서 Start date를 빼고, + 1

2. 누적 가로 막대형 그래프를 수정하여 간트 차트 그리기

• 데이터 선택 후, 삽입 - "누적 가로 막대형 그래프" 
  • 축 우클릭 - 축 서식 - "항목을 거꾸로"에 체크해주기

** 날짜 형식을 일반으로 설정했을 때 뜨는 수는 1900-01-01로부터 경과한 일 수이다. 이것을 이용해서 차트 내의 최솟값/최댓값을 설정해주기 (제일 이른 Start date와 제일 마지막의 End date를 일반 형식으로 바꾼 숫자 입력)

• 간트 차트처럼 만들어주기 (채우기 없음)

 

(파) start date - (주) days - (회) end date

days만 남기고 나머지 채우기 없음 해주기

• 채우기 없앤 계열(start date, end date) 선택 - 데이터 레이블 추가
  • 우측의 end date는 데이터 레이블 서식 - 레이블 위치를 '축에 가깝게'로 설정
  • 좌측의 start date는 데이터 레이블 서식 - 레이블 위치를 '안쪽 끝에'로 설정 
• 마지막으로 시간이 지날수록 연해지도록 그라데이션 해주기

---

대시보드 만들기

• 대시보드는 '현황'을 한 눈에 파악할 수 있게 도와준다.

1. 열 너비를 3으로 설정하여 여백을 주고 시작

2. 삽입 - 도형에서 빈 시트에 어떤 정보를 담은 어떤 그래프를 그려넣을지 각각 입력해보며 대시보드 구상하기

---

월별 주요 지표 현황 표 만들기

3. 월에 따라 자동으로 데이터가 바뀌도록 목록 만들어주기

두 셀에 각각 입력해주고

빈 셀에 1~12 써주기

데이터 탭 - 데이터 유효성 검사

조건을 '목록'으로 설정하여, 범위로 이전에 입력한 1~12 선택해주기

4. 수식 입력해서 데이터 불러오기 (vlookup)

영업a팀의 매출액을 vlookup으로 불러올 때, 1월 데이터는 A로부터 F열은 6번째에 있으므로 6을 입력했었다. 하지만 목록에 따라서 바뀌게 하기 위해서 바뀌는 숫자(1~12)셀+5로 적어주기 => 5 + $1$20

5. 매출이익률과 영업이익률은 조건부 서식 적용하여 %정도 시각적으로 표시해주기

---

조직별 주요 지표 현황 표 만들기

** 셀서식(Ctr+1) - 맞춤 - 균등 분할, 서식은 처음에만 직접 설정해주고 그 후에는 서식 붙여넣기 기능 활용

** 표끼리 너무 붙어있는 경우, 우클릭해서 행 높이 설정 (15)

• 월 목록을 만들었던 것처럼, 조직선택 목록 만들어주기(전사, 영업a팀 ...) 
• 여기서는 목록이 숫자가 아니기 때문에, 직접 숫자를 빈 셀에 입력하여 자동으로 바뀌며 데이터를 불러오도록 (옆으로 이동하니까 행(32)에 절대참조 걸기 C$32) => 그 후 가려주기 (글자 색 변경)

• 매출이익률, 영업이익률 조건부 서식에서 '평균보다 크거나 작은 값만 서식 지정'을 규칙으로 지정하여, 조직별로 평균을 넘었는지 한 눈에 확인할 수 있도록 하기 (조건부 서식도 항상 '서식 붙여넣기' 기능 활용해서 시간 절약하기)

 

수치를 시각적 요소로 한 눈에 파악하면서 쉽게 인사이트를 얻을 수 있다. 
예를 들면 여기에서는 일반적으로 매출이익률이 낮으면 영업이익률이 낮을 수밖에 없으나, 
예외적으로 매출이익률이 올라갔는데에도 영업이익률은 떨어진 케이스가 있어,
 왜 그런지 문제점 분석의 시작점을 찾을 수 있음

 

---

<판매 관리비 월별 현황> 콤보차트 만들기

• 100% 누적 세로 막대형 그래프 삽입
• '평균' 항목은 우클릭 - 계열 차트 종류 변경 - 꺾은선형으로 바꾸어준다. (+보조축도)
  • 꺾은선 그래프 배경에 다른 그래프가 있어 눈에 띄지않는 경우, 데이터 계열 서식에서 표식 옵션을 설정하여 테두리를 주고 안을 비워주기 (하얀색으로 채우기)  

---

<매출액/매출이익/영업이익 추이> 꺾은선 그래프 만들기

• 2차원 꺾은선 그래프 삽입
• 그래프를 삽입하고 행을 추가해주면, 그래프의 크기를 늘릴 수 있음
• 똑같이 데이터 레이블 추가, 서식에서 위치 조정해주기

---

<경쟁사 시장 점유율 현황> 파이 차트 만들기

• 도형 서식에서 '쪼개진 요소'로 자사 파이 강조해주기

---

OFFSET 함수 ( & 이름 관리자 )

OFFSET ( reference, rows, cols, [heights], [width] )
특정 셀을 기준으로 다른 위치에 있는 셀을 불러오거나, 연산에 필요한 범위를 설정할 수 있는 함수
= 보통 "동적범위"를 설정할 때 사용

• reference는 시작 위치, 그 뒤 rows와 cols는 시작 위치로부터 얼마나 몇 개의 열/행이 떨어진 곳의 데이터를 가져올 것인지 입력 (얼마만큼 이동할 것인지)
  • 하지만 수식에 직접 숫자를 입력하는 것은 지양해야하기 때문에, 구간 행수와 구간 일수를 빈 셀에 적어준 뒤 해당 셀을 입력해주기 (절대참조)
  • [ ] 대괄호가 입력된 부분은 생략 가능하다는 의미
• offset은 연산을 하는 함수가 아니라, 단순히 범위만 잡아주는 함수이기 때문에, 연산을 하고자 할 경우에는 offset 함수 바깥에 sum 써주기
• height가 2이고, width가 1이면, 위아래로 2가지 셀에 있는 값을 불러온다는 의미

---

"동적 범위" 이해하기

• 처음에 정한 범위에서 데이터가 추가로 입력되면 자동으로 늘어나도록 범위를 설정해주는 것
• COUNTA 활용하기 (비어있지 않는 셀의 수를 세주는 함수)
  
  • OFFSET에서 괄호 안에 들어가는 height에 COUNTA(F:F-1) 써주기, -1한 건 구분 자 행을 빼준 것
  • EX. =SUM(OFFSET($G$7,,,COUNTA(F:F)-1)
• 수식 - 이름 관리자(Ctrl+F3) - 새로 만들기에 '구분동적범위1'이라는 이름으로  위의 동적 범위 수식 추가해주기

---

VLOOKUP에서의 이름 관리자(OFFSET) 활용

• 이름 관리자에서 VLOOKUP을 위한 OFFSET 동적 범위 생성, =OFFSET(OFFSET동적범위!$F$9,,,COUNTA ( OFFSET동적범위!$F:$F)-1,8 )
  • OFFSET동적범위!$F$9 : 범위가 바뀌더라도 F9위치에 있는 데이터를 그 자리에서 불러오기 위한 함수
  • 8 : OFFSET에서 마지막 width위치에 있으므로 reference로부터 8개의 행(즉, H열)이 떨어져있는 수
  • 그 후 VLOOKUP 사용시 범위를 이름 관리자에서 생성한 범위 입력해주기

---

SUMIFS에서의 이름 관리자(OFFSET) 활용

 

 

SUMIFS를 사용해서 구분자별로 재고를 파악해보자.

1. 데이터 탭의 '중복된 항목 제거'로 구분자에 어떤 항목이 있는지 확인하기

2. SUMIFS를 쓰려면, 구분과 기초 재고량, 기말 재고량 세 가지 범위가 필요하다.

3. Ctrl+F3 이름 관리자에서 앞과 같이 동적 범위 설정해주기

• 구분1 : =OFFSET(OFFSET동적범위!$G$8,,,COUNTA(OFFSET동적범위!$G:$G)-1)
• 기초재고량1, 기말재고량1 : 기초재고량과 기말 재고량도 똑같이 기준 잡아주고, OFFSET을 써서 동적 범위 만들어주기

SUMIF (range, criteria, [sum_range])

아래로만 이동해야하니 criteria O17에서는 열O 잠궈주기

---

구분에 해당하는 상품코드만 선택할 수 있도록 목록 만들기

• 구분이 BSS인 상품코드 10가지를 이름 관리자에서 BSS로 범위 추가해주기
• 상품코드 탭을 '데이터 유효성 검사'를 걸어서 목록으로 입력 제한하기 <= INDIRECT 함수 활용

 INDIRECT ( ref_text, [a1] )
참조해서 나타낼 셀의 주소, 셀 주소의 형식 설정

• 셀 주소를 나타내는 방법 두 가지
  • A1 : a열과 1행이 만나는 셀의 주소 (주로 사용)
  • R1C1 : R1, R2, R3 .. (1행, 2행, 3행 ..), C1, C2, C3(1열, 2열, 3열) 등으로 셀 주소 표시
• EX. C10에 '가나다' 입력, D10에 'C10' 입력 → =INDIRECT(D10) 의 결괏값 = D10에 입력된 셀 주소의 값인 '가나다' 
• 이 함수를 활용해서, 데이터 유효성 검사 - 제한 대상 : 목록 - 원본 : =INDIRECT($O$13) 
  • O13은 'BSS' 이므로, 해당 함수를 상품 코드에 데이터 유효성 검사로 입력해놓아서 O13에 입력된 BSS에 해당되는 상품코드만 목록으로 뜨게 된다.

---