검색

분석으로 판매 패턴 알아내기

조건부 서식, 차트, 피벗 테이블

  • 페이스북
  • 트위터
  • 복사

피벗 테이블만 사용할 수 있다면 누구나 몇 번의 클릭으로 쉽게 원하는 관점에 따라 보고서를 작성할 수 있게 된다. 우리는 이것만으로도 엑셀의 놀라운 분석 능력을 확인할 수 있을 것이다.

요즘 이슈가 되고 있는 데이터 분석에는 컴퓨터 언어인 R 또는 파이썬(Phython) 등 여러 도구들이 거론되곤 한다. 그러나 이런 통계, 수리 데이터 분석에 사용되는 도구는 대부분 프로그래밍을 기본으로 사용하고 있으므로 일반 기업의 사용자들이 접근하긴 쉽지 않아 보인다.

 

그렇다면 대안은 무엇인가? 역시 엑셀이다. 엑셀은 계산 툴이자 분석 툴이며, 업무의 핵심 무기이다. 그렇다면 엑셀은 어떤 분석 툴이며 엑셀에서 표현할 수 있는 데이터 표현 기술은 무엇이 있는지 이제부터 하나씩 살펴보자.

 

 

원초적인 데이터로 추세나 패턴을 읽어내자! -조건부 서식

 

엑셀에는 데이터의 조건에 따라 값을 강조하는 조건부 서식이 있다.

 

조건부 서식은 일반적으로 조건을 지정하여 셀에 서식으로 강조하는 기능이다 예를 들어, 평균 이상인 값에 글꼴 색을 빨강으로 지정하거나, 서울지역만 셀 색을 지정하거나 하는 식의 서식 지정 방법이다. 여기에 2007버전부터 새롭게 추가된 3가지의 조건부 서식 (데이터 막대, 색조, 아이콘 집합)은 데이터의 기초 데이터 분석을 가능하게 함으로써 우리가 어떤 방향으로 데이터 분석의 관점을 가져야 할지 그 방향을 가늠케 해 준다.

 

먼저 색조 조건부 서식으로 각 지역 사업부별 매출 데이터를 살펴보면 ‘실적합계’에 지정된 색조 즉 ‘영업1팀’의 매출이 다른 사업부에 비해 높은 것을 알 수 있다. 또한 매출이 전년에 비해 어떻게 바뀌었는지 한눈에 구분할 수 있다,

 

또 하나의 분석 결과를 보면 매출과 이익률의 상관 관계를 알 수 있는 분석 결과이다.

 

이런 색조나 데이터 막대를 기간별 매출에 적용한다면 값의 추이를 판단할 수 있게 되며, 지난 매출을 근거로 새 상품 기획에 반영할 수 있는 좋은 분석이 된다.

 

※ 데이터 막대와 색조를 이용한 매출 분석

 

 크기변환_e3_1.png

 

※ 아이콘 집합을 이용한 매출과 이익률 분석

 

크기변환_e3_2.png

 

 

차트와 스파크라인으로 데이터를 시각적으로 표현해 보자!

 

엑셀에서 데이터를 요약하게 되면 반드시 차트로 함께 표현하게 된다. 값을 가장 빠르게 비교해 볼 수 있기 때문인데 버전이 올라갈수록 차트의 범위는 단순한 값의 표현에서 분석적 기능으로 진화하고 있다. 엑셀 2010에서 추가된 스파크라인이나 2016에 추가된 분석 차트가 그것이다.

 

또한 스파크라인의 경우는 차트로 표현하지 못하는 많은 항목의 값 표현을 하나의 셀에 차트로 삽입하는 것으로 값들의 추세를 한눈에 파악하기에 아주 적절한 표현 방법이다.

 

아래는 스파크라인으로 표현된 매출 추이이다. 꺾은 선형으로 삽입된 ‘판매추이’와 제품별 소계의 매출이 월별로 비교되어 있다.

 

※ 스파크라인으로 알아본 상반기 매출 추세

 

크기변환_e3_3.png

 

※ 대표적인 엑셀 2016의 분석 차트

 

여기에 엑셀 2016에서 새롭게 추가된 분석 차트를 다룰 수만 있다면 좀 더 강력한 시각적 분석을 다룰 수 있게 된다.

 

※ 트리맵 차트<e3_4>                                    ※ 파레토 차트<e3_5>

 

크기변환_크기변환_e3_4.png 크기변환_크기변환_e3_5.png

 

※ 폭포 차트<e3_5>                                    ※ 선버스트 차트<e3_6>

 

크기변환_폭포차트.bmp 크기변환_크기변환_e3_6.png 

 

이런 차트로 데이터를 분석하려면 기존의 데이터 값으로는 차트를 그릴 수 없게 되는 경우가 많으므로 반드시 차트에 대한 데이터 가공이 필수라 할 수 있다.

 

예를 들어 파레토 차트의 경우 데이터는 내림차순으로 정렬된 열과 총 누적 백분율을 나타내는 선이 모두 포함되어야 하므로 다음과 같이 정렬된 데이터를 가지고 있어야 한다.

 

파레토 차트를 작성하면 데이터 집합 중 가장 큰 요소가 강조 표시되어 몇 가지 중요한 항목만 집중해서 결과를 확인할 수 있다. 즉, 비용 지출이 가장 큰 항목이 무엇인지 파레토 차트로 알아낼 수 있는 것이다.

 

크기변환_e3_7.png

 

 

피벗 테이블로 원하는 쉽게 요약하자!

 

그렇다면 이런 분석 결과를 얻기 위해 우리는 데이터를 쉽게 요약할 수 있어야 한다. 우선은 함수를 사용해 집계를 하는 것이 보통이지만 피벗 테이블만 사용할 수 있다면 누구나 몇 번의 클릭으로 쉽게 원하는 관점에 따라 보고서를 작성할 수 있게 된다. 우리는 이것만으로도 엑셀의 놀라운 분석 능력을 확인할 수 있을 것이다.

 

똑같은 데이터일지라도 원하는 관점에 따라 얼마든지 분석을 다르게 시뮬레이션 할 수 있는 방법이 바로 피벗 테이블이다. 우선 아래의 데이터를 살펴보면 년도, 판매처, 사업부, 제품에 대한 매출과 영업이익등이 입력되어 있다. 

 

크기변환_e3_8.png

 

이런 데이터를 원하는 관점에 따라 다음과 같은 대시보드를 작성할 수 있다. 먼저 왼쪽의 보고서는 국가, 사업부, 년도 별 매출을 요약한 것이고 오른쪽 보고서는 년도와 판매처 별 매출과 영업이익에 대한 요약이다. 같은 데이터를 요약의 기준을 얼마든지 바꿔 봄으로써 우리가 알고자 하는 매출이나 영업이익의 결과를 분석하고 이 보고서에 위에서 언급한 조건부 서식이나 차트로 다시 표현하게 된다면 더욱 쉽게 결과를 판단할 수 있게 된다.

 

크기변환_e3_9.png

 

물론 이렇게 피벗 테이블이란 기능을 사용하기까지는 데이터베이스의 요건과 오류 없는 데이터 원본이 기본인 것은 두말할 나위 없다. 이미 이 칼럼의 첫 세션에서 언급한대로 모든 계산과 분석은 엑셀에 맞게 가공된 데이터 집합만이 대상이 될 수 있기 때문이다.

 

여기에 추가된 데이터나 변경된 데이터가 주기적으로 반영되고 이 분석을 공유하게 된다면 굳이 매번 데이터를 계산하고 보고서를 작성하지 않고도 누구나 결과를 쉽게 확인하고 빠른 판단을 할 수 있을 것이다.

지금까지 3차례에 걸쳐서 진행된 연재에서 엑셀의 전반적인 기술과 우리가 엑셀로 무엇을 할 수 있는지 알아보았다. 현업에서 칼럼에서 다뤘던 내용대로 데이터의 이해, 계산, 분석만 할 수 있다면 그 누구도 부럽지 않은 엑셀의 능력자가 될 것을 의심하지 않는다.

 

이제 시작해 보자! 엑셀로 실현되는 오피스의 모든 것을……
 

 

 

img_book_bot.jpg

엑셀&파워포인트&워드 2016 + 한글 2014무작정 따라하기박미정,박은진 공저 | 길벗
회사 업무에서 자주 사용하는 필수 기능만 모아 엑셀, 파워포인트, 워드를 단 한 권으로 알차게 배울 수 있도록 구성되었다. 30인의 베타테스터들이 원고를 직접 따라해 보면서 이해되지 않는 내용을 수정하고, 잘못된 부분을 고쳐가면서 꼭 맞는 학습 방법을 제시한다.

 

 

[추천 기사]

- 조선시대에도 과연 이혼을 했을까? (1)
- [공부법] 공부해도 국어점수 안 오르는 학생들의 특징
- [문학개념] '이미지(심상)' 정의 이해하기 (feat. 뇌과학)
- 복잡한 계산 쉽게 처리하는 노하우!

- 엑셀 데이터의 비밀

 

 

‘대한민국 No.1 문화웹진’ 예스24 채널예스

 

 

이 기사가 마음에 드셨다면 아래 SNS 버튼을 눌러 추천해주세요.

독자 리뷰

(0개)

  • 독자 의견 이벤트

채널예스 독자 리뷰 혜택 안내

닫기

부분 인원 혜택 (YES포인트)
댓글왕 1 30,000원
우수 댓글상 11 10,000원
노력상 12 5,000원
 등록
더보기

글 | 박미정(오피스튜터 강사)

오피스튜터 강사, 삼성멀티캠퍼스 튜터로 활동 중이다. 행정자치부, 한국토지공사, 삼성전자, 이화여대 등 다수의 공공기관 및 기업에서 강의했다. 주요 저서로는 《엑셀 실무 바이블》, 《엑셀&파워포인트2016+한글2014 무작정 따라하기》 등이 있다.

엑셀&파워포인트&워드 2016 + 한글 2014무작정 따라하기

<박미정>,<박은진> 공저16,200원(10% + 5%)

『엑셀&파워포인트&워드 2016 무작정 따라하기』는 회사 업무에서 자주 사용하는 필수 기능만 모아 엑셀, 파워포인트, 워드를 단 한 권으로 알차게 배울 수 있도록 구성되었다. 30인의 베타테스터들이 원고를 직접 따라해 보면서 이해되지 않는 내용을 수정하고, 잘못된 부분을 고쳐가면서 꼭 맞는 학습 방법을 제시한다...

  • 카트
  • 리스트
  • 바로구매

오늘의 책

스티븐 킹이 그린 현대인의 일그러진 자화상

스티븐 킹이 ‘리처드 바크만’의 이름으로 발표한 소설 중 하나. 이야기는 고속도로 공사 계획 때문에 자신의 터전과 가정을 잃게 된 한 남자가 분노로 파멸에 이르는 과정을 담아내며, 거대 자본의 이익 논리 앞에서 무력해지고 마는 개인의 모습, 현대인의 심리를 밀도 있게 그린다.

세계 최고 혁신 전문가가 발견한 변곡점의 시그널

마이크로소프트, 아마존, 넷플릭스, 애플… 세계적인 혁신 기업들의 공통점은 무엇일까? 바로 변곡점을 빨리 발견하고 그에 맞춰 비즈니스 모델에 변화를 일으켜 시장을 장악했다는 점이다. 세계 최고의 혁신 전문가이자 컬럼비아 비즈니스 스쿨 교수가 쓴, 변곡점의 모든 것을 다룬 책.

인생은 뇌에 달렸다

청중의 눈높이에 맞는 재미난 강연으로 뇌과학을 알려온 김대수 카이스트 교수가 쓴 책. 뇌를 이해하면 인생을 주체적으로 운영할 수 있다. 세계를 정확하게 인식하고, 욕망을 조절하여, 창의적으로 세상을 즐기는 데 뇌과학이 힘을 준다. 구체적인 해법이 책 속에 담겼다.

뮤지션 유희열과 함께 걷는 밤 산책

뮤지션 유희열이 서울의 동네 구석구석을 걸으며 마음 속에 들어온 풍경을 글로 담아냈다. 천천히 밤을 걸으며 우연히 마주친 순간은 지난 추억을 떠오르게 하고, 친한 친구를 생각나게 한다. 시원한 밤 공기가 더욱 그리운, 지금 이 계절에 어울리는 유희열의 심야 산책 에세이.

.

주목! 투데이 포커스


문화지원프로젝트
PYCHYESWEB03