1. 엑셀(Excel) 피벗 테이블 이란?
안녕하십니까?
투잡부엉입니다.
오늘은
신입사원
엑셀 편에서
아주아주 중요한
사용빈도 및 활용도가
무한한
피벗 테이블 사용하기
피벗테이블에
대해서
소개하고자
1시간 반 시간의
사전 자료 작성을 끝내고
따끈따끈한
자료를 가지고
찾아왔습니다.
피벗테이블의
중요성은 회사생활에서
아무리 강조해도 지나치지 않습니다.
보통 회사의 경우는
실시간으로
방대한 공정 및 품질, 물류 등의
실시간 Data들이
Sytem으로 들어가고,
그것들은
품질이나 공정의
문제 해결을 위해서
분석하는 자료로 활용하게 됩니다.
요즘은
System 상에서도
실시간 모니터링 가능하고,
이상점에 대한 Warning을
띄울 수 있지만,
회사 업무에서
수많은 Data 인자를 다운로드하여서
그 Data를 가공하면서
분석하는 것은
예나 지금이나,
현 업무에서 이루어지고 있기 때문에
이러한
일련의 활동들을
엑셀의 피벗 테이블을 통해서
분석이 가능합니다.
따라서,
엑셀의 피벗 테이블의
기능은 매우 중요하고 그 활용도가
높다고
자신 있게
말할 수 있습니다.
자 그럼
언제나 그랬든
친절한 부엉씨는
신입사원이
배운다는 생각으로
상세하고,
천천히
예를 들어 설명드리도록
하겠습니다.
출발해 보시죠.
2. 엑셀(Excel) 피벗 테이블 사용방법
자 그럼
본격적으로 진행합니다.
엑셀의 피벗테이블은
삽입 탭에서 찾으실 수 있습니다.
이해를 돕기 위해서
제가 Raw Data를 만들었습니다.
저는 부엉이 도시락 생산업체
CEO 매의 눈 부엉입니다.
감정이입 좀 하고 들어가겠습니다.
제가 만든 Raw Data는
우리 도시락 업체 생산&매출 관리
System에서 다운로드하였습니다.
짜잔!
날짜, 설비, 가동시간, 작업자,
잔업(O.T), 불량 건수, 판매수량, 매출
다양한 정보 Data를 확보했습니다.
그런데
그냥 쳐다보고 있으면
특별한 정보는 보이지 않습니다.
우리는 회사의
녹을 받고 생활하기 때문에
그냥 흘러가는 대로 회사를
둘 수 없기 때문에,
1년 치 Data를 보면서
분석을 하고
강점, 단점 등을
Data 속에서 찾아서
개선해 나가는 것이
우리의 임무이기 때문에
분석에 들어갑니다.
이때 피벗 테이블이 등장합니다.
자 그럼
피벗 테이블을 통해
System에서 다운로드한
자료를
분석하도록 하겠습니다.
엑셀 Data 전체를
드래그하여 선택을 한 후,
피벗 테이블을 선택합니다.
메뉴 → 삽입 → 피벗 테이블
클릭을 하면,
표 또는 범위의 피벗 테이블 창이
활성화되며,
자동으로 새 워크시트가
체크되어서 활성화됩니다.
그냥 확인을 클릭!
짠!
피벗 테이블이 생성이 되었습니다.
좌측은 테이블 형식으로
나오게 될 자리며,
우측은
우리의 목적과 상황에 맞게
그 피벗 테이블을
세팅하는 창이라고 생각하시면
이해가 편하실 겁니다.
매의 눈 CEO 부엉은
먼저,
설비별 가동시간과
불량 건수 들에 대해
궁금합니다.
날짜를 선택하면,
좌측 피벗 테이블에
날짜가 나타납니다.
이어서,
설비, 가동시간을
우측 피벗 테이블 필드에서
선택을 해 줍니다.
좌측 피벗 테이블을
조금 보기 편하게 세팅을
바꿔 주겠습니다.
상부 메뉴탭의 디자인에서
피벗 테이블의 레이아웃을
세팅해 보겠습니다.
먼저
디자인 → 보고서 레이아웃 → 테이블 형식으로 표시
두 번째로
불필요한 합계를 삭제해 보겠습니다.
디자인 → 총합계 → 행 및 열의 총합계 해제
세 번째로
디자인 → 부분합 → 부분합 표시 안 함
짠
피벗 테이블이
조금 보기 편하게 바뀌었습니다.
자 그다음
피벗 테이블 필드에서
불량 건수 항목을
체크하면,
좌측 피벗 테이블에
자동으로 반영이 됩니다.
참 쉽죠?
그런데 날짜가
있으니 눈에 잘 띄지 않습니다.
피벗 테이블 필드에서
행 부분에서 날짜를 클릭하시면
메뉴가 나옵니다.
여기서 필드 제거를
선택해 줍니다.
날짜를 제거하니
좀 단순한 Data로 표현되었습니다.
매의 눈 CEO 부엉은
가동 시간은 유의차가 없는데
B와 C의 설비가
불량 건수가 높다는 것을
추정해 볼 수 있습니다.
이건 1년 치의 Data의
합계이므로,
다르게 표현해서 살펴보겠습니다.
우측 피벗테이블 필드에서
값 부분에서
기존 항목을 클릭해서
값 필드 설정으로 들어가 봅니다.
값 부분에는
우리가 분석하고자
하는 목적에 맞게
여러 가지 산술 평균 등을
표현해 줄 수 있습니다.
저는 가동시간과 불량 건수
모두 평균으로 바꿔보겠습니다.
짠
평균 가동 시간과
평균 불량 건수로 바뀌었습니다.
소수점 보기 힘드니
수정해 줍니다.
메뉴 → 홈 → 표시 형식 ( , ) 클릭
짠
소수점 둘째 자리로
Data를 변경해 줍니다.
메뉴 → 홈 → 표시형식 → 소수점 자릿수 늘림
흠..
1년 Data로 보면
설비 가동시간은 유의차 없고,
불량 유발 설비로 보면
(원인은 정확하게 찾아봐야겠지만)
C > B > A 설비 순으로
불량 건수가 많고
C → B → A 설비 순으로
점검을 해보는 것도 좋은 방법이라
생각이 됩니다.
작업자의 의한 불량인지
설비에 의한 불량인지
명확하게
좀 더 확대해서
접근해서 원인 파악을 해야겠군요.
자 다음
매의 눈 CEO 부엉은
작업자 별 잔업시간에
대해서 확인해 보기로 했습니다.
법정 근로시간을 (52HR/주)
잘 준수하는지
매의 눈으로 확인합니다.
기존
피벗 테이블 필드에서
설비, 가동시간
체크를 해제하고,
날짜, 작업자, 잔업 OT를
선택해 줍니다.
좌측에 피벗 테이블을
보시면
작업자 별, 월별 잔업 합계 시간이
표현되었군요
피벗 테이블
편하지 않나요?
이번에는
보기 변경을 또 해보겠습니다.
전체가 아닌
작업자 별 통계치가
좀 필요하거든요.
디자인 → 부분합 → 그룹 하단에 모든 부분합 표시
짠
작업자 별
부분 합계가 표현되었습니다.
추가로
피벗 테이블 필드에서
잔업 OT 부분을 클릭 드래그해서
값 부분으로 하나를 더 옮겨 줍니다.
좌측 피 벗 테이블에
동일한 값의 열이 하나 더 생성되었습니다.
값에서
잔업OT(hr)2를 클릭하여
값 필드 설정을 기존 합계에서
평균으로 바꿔줍니다.
짜잔
그럼 피벗 테이블에
1년 동안의 작업자 별 전체 OT 시간과
평균 OT 시간을 확인할 수 있습니다.
흠
매의 눈 CEO
투잡부엉이 분석합니다.
평균 잔업 시간은 큰 차이 없으나,
여행 가이드 부엉은
잔업을 거의 하지 않았군요.
투잡 부엉은
평균 잔업시간은 평균 수준이나,
1년 전체 잔업으로 보면
다른 작업자에 비해
많은 OT를 한 것을 확인하였습니다.
여기까지
조금씩
피벗 테이블에 대해서
감이 좀 오시나요?
익숙해지고
반복하다 보면
전문가가 되실 수 있습니다.
자 그럼 계속
GO GO!
매의 눈 CEO 부엉은
사실 매출과 TREND에 대해서
궁금했는데
마음을 숨기고 있었습니다.
본격적으로 GO!
피벗 테이블 필드에서
기존 체크를 모두 해제하고
날짜, 판매수량, 매출을
클릭해 줍니다.
그럼 좌측에
월별 판매 수량과 매출을
나타내는 피벗 테이블이
나타났습니다.
소소한 변경
숫자가 보기 힘드니
표시 형식
변경해줍니다.
이번에는
년 매출도 확인이 필요하니
합계 부분도 또 수정해 줍니다.
디자인 → 총합계 → 행 및 열의 총합계 설정
흠.
1년 동안 1,681개의 도시락을
생산해서 판매하고,
843만원 정도의 매출을 올렸습니다.
매의 눈 CEO 부엉은
초조해서
이대로 볼 수 없습니다.
메뉴 → 삽입 → 그래프 → 2차원 꺾은선형 → 표식이 있는 꺾은선형
2022년 5월까지
상승세를 타던 매출 TREND가
2022년 6월부터 지속적인
하향 TREND를 보이고,
2022년 10월에
매출 최하를 찍고
2022년 12월까지
바닥에서 유지하고 있군요.
화가 납니다.
회사의 경영을 잘 못한
저의 잘 못이 가장 큽니다.
여러 가지로 지금부터
분석해서
개선을 해야겠습니다.
이렇게 분석 시에는 표뿐만 아니라
여러 가지 Data에 맞는 그래프들도 그려보면서
분석해 보시면 쉽게 현상에 대해서 확인이 가능합니다.
평균 매출도
넣어 보았습니다.
자 그럼
매출이 심각한 수준임을
확인한 매의 눈 CEO 부엉은
추가적인
분석을 해보기로 하였습니다.
피벗 테이블 필드에서
모두 체크를 해제하고,
작업자, 불량 건수, 판매수량에
체크를 하였습니다.
작업자 별로
불량률을 확인해 보기로 하였습니다.
원래 불량률은
불량 건수 / 생산 수량
입니다만,
판매수량 = 생산 수량
이라 가정하고
진행해 봅시다.
여행 가이드 부엉이
눈에 딱 띕니다.
좀 전에
잔업시간이 없었던 것도
여행 다니느라 바빴나 봅니다.
생산/판매도 많지 않네요
불량 건수로만 보면
가장 잘했다고 착각할 수?
있지만
생산/판매 수량이
다른 작업자에 비해
확연하게 차이가 나기 때문에
불량률로 표현하여
봐야 합니다.
자!
C4/B4
불량 건수/생산량(판매수량)
으로 계산해 줍니다.
친절한 부엉씨는
친절해서
드래그하는 화면도
캡처해서 보여 드립니다.
계산된 게임부엉의
불량률을
아래로 드래그해 줍니다.
와하!
매의 눈 CEO 부엉은
고민이 많습니다.
몇 가지 분석을 해보겠습니다.
여행 가이드 부엉은
현재 직장 생활하면서 무슨
개인 문제가 있는지 조금
면담을 해봐야 할 것 같습니다.
그리고 또 눈에 띄는
투잡부엉이가 있습니다.
여행 가이드 부영을 제외하면,
나머지 작업자의 불량률은
거의 유사 수준으로 볼 수 있습니다.
다만,
판매수량에서는
투잡부엉이 TOP이기
때문에
투잡부엉이
올해의 우수사원상을
수상할 가능성이 높겠군요.
3. 엑셀(Excel) 피벗 테이블 활용
여기까지
오늘 준비한 내용은
모두 마쳤습니다.
단순 설명을 위한
Raw Data를 만들어서
약간 부자연스러운 부분이
있었다면, 이해를 부탁드립니다.
앞에서 보신 바와 같이,
어떠한 방대한 Data를
분석할 때
엑셀(Excel)의 피벗 테이블은
항상 사용되게 되는데요
오늘 소개한 부분도
일부분이라,
사용하시면서
여러 가지 활용을 해 보시면
더 추가적인 기능으로
문제 해결을 위한
현상 파악 등에
사용이 될 수 있습니다.
저도 뭐 잘 하진 못하지만
여러 번 하다 보니
스스로의 노하우가 생기더라고요
아무튼
오늘 배운
피벗테이블
회사에서
혼자 시간 되실 때
흩어져 있는 여러 Data를 가지고 와서
여러 방법으로
분석을 진행해 보시면
도움 되실 거라
믿습니다.
오늘도 좋은 하루 보내시고
행복하세요
신입사원 엑셀(Excel) 기초 수식(함수) - 행렬, 사칙연산, 소수점처리, 여러셀의 합계구하기
1. 신입사원 엑셀(Excel) 안녕하세요. 투잡부엉입니다. 오늘은 신입사원 엑셀(Excel) 기초에 대해서 소개해 드리려고 합니다. 제가 최근에 여러분들을 위해서 체험판을 설치하였습니다. 그래서 틈틈
infovalley.tistory.com
신입사원 엑셀(Excel) 기초 - 조건부서식 활용하기
1. 조건부 서식 활용 안녕하세요. 투잡부엉입니다. 오늘은 긴 여행 가이드 부엉에서 신입사원 부엉으로 다시 돌아왔습니다 오랜만에 엑셀 기능 활용 조건부 서식에 대해서 안내해 드리고자 합니
infovalley.tistory.com
'신입사원부엉' 카테고리의 다른 글
| 신입사원 PPT(파워포인트) 스킬 - 슬라이드 마스터, 개체삽입하기 (0) | 2022.11.20 |
|---|---|
| 신입사원 아웃룩(Outlook) 자동회신 및 보기설정 (0) | 2022.11.14 |
| 신입사원 엑셀(Excel) 기초 - 조건부서식 활용하기 (0) | 2022.11.05 |
| 신입사원 아웃룩(Outlook) 폴더 정리 방법 및 규칙 사용하기 (0) | 2022.10.14 |
| 신입사원 엑셀(Excel) 기초 수식(함수) - 행렬, 사칙연산, 소수점처리, 여러셀의 합계구하기 (1) | 2022.10.11 |