[Part 5-1] 엑셀로 끝내는 실전 데이터 분석 강의 - 손익 계산서, 월별 손익 조회 장표, 재직증명서, 상품재고현황 파악

---

목차

-

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

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

---

" 자동 손익계산서 만들기"

손익계산서 - raw data 다루기

Raw data 예시

=> raw data를 보고 '영업a팀의 1월 매출액은 어떠한가?" 와 같은 질문을 던질 수 있음

 

1. 텍스트나누기로 거래일시를 연/월/일 따로 나누기

• 앞서 텍스트나누기 후, 엔드(%)로 글자 년/월/일을 연결할 때, 원래 행을 지우면 수식이 참조된 셀이라서 에러가 난다.
  • Alt+3 눌러서 '값 붙여넣기'를 해주어 수식을 값으로 바꿔준다. 이제 원래 행을 지워도 에러가 나지 않음
  • 그리고 앞서 맨 아래에 1을 입력해서 Ctrl + Alt + ↓ 를 눌렀을 때 데이터 맨 끝 값으로 가도록 했는데, 그것보다 이미 채운 이전 행에서 Ctrl + ↓ 으로 내려간 후, 아래서부터 Ctrl + Shift + ↑ 위로 올라가 Alt+2로 채워넣는 방법이 더 빠름

2. 본부 행을 복사하여 데이터 탭에서 '중복된 항목 제거' 로 총 몇 개의 팀이 있는지 한 눈에 파악한다.

3. 서식 작성 - 수식을 미리 표에 작성해놓는다. 

• '매출이익률'과 같은 경우, 매출이익/매출액인데, 현재 값이 없어 분자가 0이라 에러가 난다. 이런 경우, IFERRE(E11/E6,) 를 써서 에러가 날 경우 빈칸으로 두도록 설정

---

손익계산서 - 피벗 테이블

1. 아무 셀 선택 => 삽입 탭에서 '피벗 테이블' 선택, 범위 잡아주기, '새 워크시트'에 만들어주기 (일반적)
2. 피벗 테이블 안의 셀을 선택해야 '피벗 테이블 필드'가 생긴다. 
   1. 영역으로는 '필터', '열', '행', '값'이 있는데, 피벗 테이블을 어떻게 구성할지 조합해보며 구성하기
      • 열은 한 개의 열에 모든 데이터가, 행은 한 개의 행에 모든 데이터가 들어오는 방식
      • 필터는 선택해서 특정 월에 해당하는 데이터를 볼 수 있음 => 한 눈에 보고 싶을 때 적절하지 X
      • 영업a팀의 5월 데이터를 찾고 싶은 경우 => 행을 '월'로, 열을 '팀'으로 설정
      • 월마다 어떤 분야에 돈을 많이 썼는지 알기 위해서 '중분류'를 행에 추가
      • 더하고 빼보며 어느 필드가 적절한지 판단
   2. 다른 시트에 값 붙여넣기(Alt+2), '행 레이블'을 '구분'으로 바꾸고, 구분자 행과 열의 셀 서식을 표처럼 수정해주기
      • 셀 서식 붙여넣기(Alt+4)로 구분하는 테두리 둘러주기 

---

손익계산서 - 양식 만들기, SUMIFS로 합계 구하기 (raw data 연동)

a팀의 1월 매출을 구하여 실적을 알아보자

▼

raw data에서

=> '월=1월', '중분류=매출액', '팀=영업a팀',  총 3가지 조건에 해당하는 값('금액=...')만 구해서 더해야 한다.

=> 조건이 여러개일 경우, 더할 때 쓰는 함수 "SUMIFS"사용

SUMIFS ( 더할 값들이 있는 범위, 1번째 조건 범위, 조건 값1, 2번째 조건 범위, 조건 값2 )

▼

손익계산서 양식에 

=> SUMIFS

• 1번째 조건 = 1월 / 2번째 조건 = 중분류'매출액' / 3번째 조건 = 영업a팀
• ( raw'금액( 절대참조 )', raw'1월 전체', form'1월'( 혼합참조, 2,3월...로 이동할 수 있도록 행만 고정 ex. F$51 ), raw '중분류=매출액 전체', form'매출액'( 혼합참조, 여기서는 반대로 매출원가,재료비..등으로 아래로 이동해야해서 열만 고정 ex. $D52 ), raw'영업a팀 전체', form'팀'( 팀 구분=영업a팀 (중복항목 제거된 데이터(사진참고▼),절대참조 )

중복된 항목 제거로 만들어놓았던 팀구분 행

위 한 가지 수식응로 모두 채워넣을 수 있다

• 영업 a팀에 이어서 b팀을 구하기 위해 위 사진에서의 영업a팀($N$3)이 영업b팀($N$4)로 바뀌도록 해준다.
  • 합계를 제외한 1월~12월 데이터를 모두 선택한후, Ctrl+H로 $N$3을 $N$4로 바꿔준다.
  • 표 속의 데이터가 모두 수식으로 입력되었기 때문에, 수식 속의 숫자 $N$3이 4로 바뀌어 원하는 결과를 얻을 수 있다.
  • 나머지 표도 마찬가지로 채워주기

---

손익계산서 - 분기와 누계 추가하기

• 분기의 경우, 1-3, 4-6, 7-9, 10-12 SUM을 그냥 구해주면 되고,
• 누계의 경우, 절대참조 활용하기 
  • 1월과 2월의 매출액이 각각 F52, G52일 때, 누적 합계를 구해야하니, 그 전 매출액만 고정되도록 절대참조를 걸어준다. → $F$52 : G52

---

월별 손익 조회 장표

• 실적을 이전 데이터와 한 눈에 비교할 수 있는 장표 만들기
• 1) 전년도와의 비교, 2) 전년도의 계획과의 비교
• 손익 데이터를 볼 때에는, 1) '월별' 데이터와 2) '누계'데이터를 같이 봐야 한다. 
  • 그 이유는 월별 데이터에 가려진 정보가 있기 때문
  • EX. 1~4월에 매출액이 낮았다가, 5월에 200%를 달성한 경우, 5월 데이터만 보면 실적이 좋은 것처럼 보이나, 합계로 판단하면 실적이 좋은 게 아님

(복습) VLOOKUP으로 전 년도 데이터 불러오기

• VLOOKUP ( 찾을 기준 데이터, 공통 열을 기준으로 찾아올 데이터들의 범위 설정, 찾아올 데이터의 열 번호, 0 OR 1 )
  • VLOOKUP ( 매출액 $D6, 전년도 시트에서 전체 범위 잡기,  3, 0 )
    • $D6 : 매출액, 매출원가, 인건비 ...로만 이동해야하므로(행 방향), D열 앞은 잠궈주기
    • 3 : 1월 데이터는 3번째 (구분→합계→1월)
    • 0 : 정확한 값만 찾기 
• 단, 3번째를 4,5...번째로 일일이 바꾸는 대신 자동으로 바뀌도록 설정해야 함
• 앞서 3을 사용하는 대신에, 3을 빈 셀에 적은뒤, 해당 셀을 절대참조를 걸어 입력해주기 ( EX. I3열에 적고, 3대신 $I$3입력 ) 
  • 이 방법과 유사하게 하되, 1부터 시작하게 하여 1월,2월.. 숫자만 바꾸면 특정 월 데이터를 볼 수 있도록 하려면
  • 1을 입력하고, 절대 참조를 건 1에 2를 더해주면 된다. (2+$I$3) 
• 나머지 값들도 (매출원가, 매출 이익, 영업이익...) 수식과 (EX. 매출이익률=IFERROR(매출이익/매출액) 전 년도 데이터(Alt+2)로 붙여넣기
• 비교 표 작성에서 수식을 편하게 사용하기 위해, 표 작성시, 시트만 바꾸면 같은 위치에 같은 데이터가 위치하게끔 만드는 것이 중요 (동일한 틀 사용)
• 앞서 전년도 시트에서 데이터를 불러왔으니, 같은 수식을 사용하여 Ctrl+H로 'PL2016' → 'PL2017p' 로 전 년도 계획했던 값을 바로 불러오기.

▼

• 데이터 탭 - 데이터 유효성 검사 
  • 선택한 셀에 지정한 특정 조건의 데이터만 입력이 되도록 설정하는 기능.
  • 데이터 유효성 검사로 1~12월과 같은 특정 값만 선택할 수 있는 목록을 만들 수도 있다.
• 조건부 서식도 서식 붙여넣기 기능 활용이 가능하다.
• 차트 복사시, 붙여넣기 한 차트 우클릭 - 데이터 선택 - 계열 편집에서 범위를 새로운 차트의 값으로 다시 잡아주면 됨
• 단순 기능보다도 중요한 것은 숫자로 말하고자 하는 바가 무엇인지 

---

" VLOOKUP을 활용한 다양한 자료 작성 "

재직증명서

• 사번만 입력하면 나머지 정보가 불러와지는 양식 만들기
• 직원 리스트에서 데이터 불러오기
  • VLOOKUP ( 사번, 기준 열 사번부터 범위 전체 잡기, 이름 열에 해당하는 2(번째) 입력, 0)
    1. 2를 직접 입력하면 안됨 => 재직 증명서에 해당하는 이름(2), 본부(3), 직급(5) 열 번호를 빈 셀에 입력하기. 절대 참조 안걸어야 이동하면서 다른 데이터를 불러올 수 있다. (▼그림 참고)
    2. today() 함수로 재직증명서 입력 날짜 하단에 입력되도록 해주고,
    3. 셀 서식에서 날짜 형식 지정해주기 + 셀 서식 - 맞춤에서 '선택 영역의 가운데로'로 가운데 정렬
• 인쇄할 때 팁
  • 양 옆 빈 셀 우클릭 - 열 너비를 3정도로 지정하여 양 옆에 여백 주기
  • 보기 탭 - '페이지 나누기 미리보기' 선택 후 인쇄할 범위 정확히 잡아주기

열 번호 빈 셀에 입력해서 vlookup함수의 열 번호가 자동으로 바뀌도록 해주기

---

상품재고 현황을 알 수 있는 장표 만들기

정보를 찾아보기 힘든 형태의 raw data

▼

'재고'를 파악하기 쉽도록 다음과 같은 형식으로 다듬기

• 여기에서는 상품코드, 색상, 사이즈 3가지 조건에 맞는 재고를 파악하고자 한다.
  • 가져오고자 하는 데이터의 조건이 3개인 경우, VLOOKUP을 바로 적용할 수 없다.
  • 이런 경우, 세 가지 정보를 하나의 셀로 만들고 적용할 수 있다. 두 가지 방법이 있음
    • 텍스트 나누기/합치기 기능을 활용하기
    • concatenate 함수 활용하기, 합치고자하는 텍스트를 순서대로 선택해주면 된다.

** 데이터가 제대로 입력되지 않는 경우, 대부분 서식이 잘못되어 있어 그렇다. 셀 서식에서 표시 형식을 "일반"으로 바꿔주기

concatenate로 합친 모습(▼)

• 상품코드, 색상, 사이즈를 합쳤지만, 전체 상품&재고 현황에서 "상품 코드&색상"과 "사이즈"가 따로 나와있기 때문에  vlookup에 입력되는 첫 번째 값인 lookup_value에 상품코드&색상과 사이즈를 and(&)로 이어서 한번에 입력해주기
  • 상품코드&색상은 아래로 이동하니까 행($C)을 잠궈주고, 사이즈는 옆으로 이동하니까 열($4)을 잠궈주기
• 해당 상품코드&색상에 해당 사이즈가 없을 경우, 에러가 난다. 따라서 IFERROR로 에러 숨겨주기
  • 함수를 두 개 사용할 경우, 안쪽에 들어가는 함수를 먼저 입력해주고, 해당 함수를 인수로 활용하는 또 다른 함수를 밖에 써주어야 한다.
• 이전에 배운 조건부 셀 규칙을 적용하여 재고가 50개 이상인경우, 안정적이라는 의미로 초록색을, 10개 이상 50개 이하는 노란색을, 1개 이하인 경우 빨간색을 적용하여 재고 상태가 한 눈에 띌 수 있도록 해주기
  • 항상 마지막에 조건의 범위가 작은 조건이 위로 오도록 정렬해주어야 한다.

---