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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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