“숫자 속에서 인사이트를 찾다” 엑셀로 데이터를 멋지게 표시하는 10가지 방법

Computerworld

기발한 데이터 인사이트를 공유하기 위해 스프레드시트를 열자 사람들이 못마땅해 하는 경험을 한 적이 있는가? 이런 경험을 한 적이 있더라도, 당신 잘못이 아니다. 또 청중의 잘못도 아니다. 어쩌면 ‘차트를 너무 많이 사용해 지루함을 유발하는 증후군’ 때문인지 모른다.

사람들이 발표자가 전달하려는 데이터를 쉽게 ‘소화’하도록 도움을 주는 10가지 차트 활용 방법을 소개한다. 청중이 지루해 곁눈질을 하는 행동, 지나치게 많은 생각을 유발하지 않는 차트들이다. 사진처럼 데이터의 의미를 전달하는 차트이다. 다시 말해, 말을 하는 대신 보여준다.

매번 똑같은 원형 차트로 청중을 지루하게 만들어서는 안 된다. 대신 도넛이나 폭포수 차트를 활용해 보기 바란다. 훨씬 더 재미있고, 청중의 관심을 유발한다.



스파크라인 : 데이터 미리보기
셀 내부 차트는 스프레드시트의 시각적 맥락을 즉석에서 알려주는 ‘데이터 미리보기’라고 부를 수 있다. 엑셀 2013에 도입된 스파크라인은 열이나 행 셀의 데이터를 선형 차트, 세로 막대형 차트, 간편한 승패 차트로 표시할 수 있는 기능이다.

아주 쉽다. 차트로 표시하고 싶은 데이터 옆의 셀을 선택한 후 UI 리본의 삽입을 클릭한다. 그리고 스파크라인 그룹에서 원하는 선형 차트를 클릭한다(또는 세로 막대형 차트나 승패 차트). ‘스파크라인 생성’ 대화 상자에서 데이터 범위 텍스트 상자를 클릭한 후 묘사하고 싶은 행과 열을 선택한다. 위치 범위 상자에 스파크라인 그래프를 표시할 셀이 표시된다. 상자의 텍스트를 선택하고 마우스를 사용해 셀의 행이나 열을 선택해 표시 위치를 바꿀 수 있다. 확인을 클릭하면 셀 내부 차트로 데이터가 표시된다. 스파크라인 차트 셀을 선택한 다음 리본에서 디자인항목을 클릭해 서식을 바꿀 수 있다.



셀 내부 차트로 ‘목표 미달 데이터’를 부각해 표시하기
스파크라인은 편리하다. 특히 긍정 값과 부정 값을 색상과 도해로 비교되게 표시할 수 있는 승패 옵션을 즐겨 사용한다. 지역별 영업실적 데이터에서, 전망치(목표치)에 미달한 지역이나 분야를 두드러지게 표시하는 방법을 예로 들자. 그런데 셀 옆이 아닌 셀 내부에 시각적으로 표시를 하기 원한다. 이때 조건부 서식과 셀 내부 차트 활용법을 알면 큰 도움이 된다.

데이터 범위를 선택한 후 리본에서 조건부 서식 항목을 선택한다. 그리고 아이콘 집합을 클릭해 방향 아래에서 간단한 아이콘 하나를 선택한다. 각 데이터 셀에 화살표 아이콘이 표시될 것이다. 그러나 0을 기준으로 정렬되어 있지 않다(또 시각적으로 어지럽다). 범위를 선택한 다음 조건부 서식, 규칙 관리를 클릭한다. 그리고 방금 만든 규칙을 더블 클릭한다.

‘서식 규칙 편집’ 대화 상자 아래의 ‘아이콘’ 항목에서 위쪽 두개의 아이콘의 드롭 다운 메뉴에서 ‘셀 아이콘 없음’으로 지정한다. 첫 번째는 부등호는 >로, 값은 0, 종류는 ‘숫자’로 설정하고, 그 아래 것은 부등호는 >=, 값은 0, 종류는 ‘숫자’로 설정한다. 가장 아래 아이콘 항목의 아이콘은 붉은 색 아래 방향 화살표로 설정한다. ‘확인’과 ‘확인’을 연속해 클릭한다. 이렇게 하면 음수 값을 가진 셀에 아이콘이 표시된다.




멋지게 테이블 활용하기
엑셀에서 가장 좋아하는 기능 중 하나가 표 기능이다. 아주 큰 데이터 집합도 마우스 클릭 한 번으로 필터링해 정렬할 수 있기 때문이다. 그런데 차트에 적용할 수 있는 ‘마법 같은 기능’이 숨겨져 있다. 예를 들면, 표 끝에 새 데이터 열을 추가하면 연결된 차트가 자동으로 확장되어 새 데이터 계열이 추가된다. 아주 편리한 기능이다.

특정 데이터 범위를 표로 표시하려면 먼저 관련 셀(머리글 열과 행 포함)을 선택한다. 그리고 삽입을 누른 후 리본 메뉴에서 표를 선택한다. ‘머리글 포함’ 확인상자에 표시가 되어 있어야 한다. 표시를 확인한 후 확인을 클릭한다. 그러면 간편히 서식을 지정하고, 머리글 열을 기준으로 드롭다운 방식의 필터링 기능을 이용할 수 있다.

데이터 옆에 차트가 표시되도록 연결해 놓은 상태에서, 새로운 머리글 열 항목과 아래 셀의 데이터를 입력한다. 그러면 머리글 열 셀에서 엔터를 클릭하는 즉시 차트 크기가 변경되면서 새로운 항목이 표시된다. 각 셀에 데이터를 추가하면, 차트 또한 업데이트되어 새 데이터가 표시된다.

더 나아가, 표를 이용하면 차트에서 유용한 필터링 기능을 사용할 수 있다. 100개 현장 사무소의 월 예산에 대한 표를 만들었다고 가정하자. 주(state) 행의 드롭다운 메뉴에서 필터를 클릭하고, 모두 선택의 표시를 없앤 후 아래로 스크롤해 오하이오만 표시하는 방법으로 오하이오 사무소의 예산만 표시할 수 있다. 확인을 누르면 차트가 업데이트된다.



원형 차트보다 나은 선버스트 차트

엑셀 2016에 도입된 새 선버스트 차트는 데이터 관계를 이해하고, 데이터를 계층화하고, 이를 통해 각 요소가 결합해 더 큰 데이터 집단이 되는 과정을 보여줄 수 있어 유용하다. 여러 계층으로 구성된 원형 차트라고 생각하면 된다.

선버스트 차트는 데이터 집합의 구조를 읽어 이를 처리한다. 3가지 전략적 제품군(위젯, 컨트롤러, 커넥터)를 생산하는 회사가 있다고 가정하자. 이 회사는 산업, 특수, 소비자 시장 등 모든 시장을 대상으로 이들 제품군의 영업 실적을 추적해 보여주는 스프레드시트를 만들어 유지하고 있다. 그런데 산업시장에서 자동차, 의료 등 분야 별로 세분화를 하기 원한다. 이 경우, 데이터를 적절히 배열 및 정렬한 후 관련 셀을 선택한다. 그리고 삽입>추천 차트>모든 차트 탭을 클릭한 후 리스트에서 선버스트를 선택하면 된다.



깔끔하게 데이터를 보여주는 폭포 차트
오피스 2016에서 폭포 차트라는 새 차트가 도입되었다. 음의 값과 양의 값을 보여주는 차트이다. 시간 경과에 따른 재무 포트폴리오 추적, 소득과 비용 시각화에 유용하다. 추세를 보여주는 선형 차트와 다르게, 각각의 이익과 손실을 강조해 보여주는 차트가 폭포 차트이다.

폭포 차트를 만들려면 열을 2개로 배열한다. 왼쪽은 ‘월’에 대한 열, 오른쪽은 ‘금액(달러, 음수와 양수)’에 대한 열이다. 해당 행의 셀을 선택한 후 삽입과 추천 차트 아이콘을 클릭한다. 그리고 차트 삽입 대화 상자에서 모든 차트를 선택한다. 이후 폭포를 선택한 후 확인을 클릭하면 된다. 그러면 서식이 깔끔하지 않은 상태에서 차트가 표시된다.

이제 아주 큰 Start의 금액이 다른 데이터를 방해하지 않도록 Y를 조정한다. Y축 레이블을 오른쪽 클릭한 후 축 서식을 클릭한다. 축 옵션 창에 15,400을 입력한 다음 확인을 클릭한다. 숫자 항목을 클릭하고 범주에서 통화를 선택한다. 그러면 표시되는 소수점 위치 상자에 0을 입력한 다음 엔터를 누른다. 마지막으로 차트의 첫 데이터 포인트를 Total 항목으로 지정한다. 차트의 첫 열을 클릭한다. 또 한 번 더 클릭하면 해당 열만 표시된다(다른 부분은 음영으로 표시). 데이터 포인트 서식 창에서 계열 옵션 아이콘을 클릭한 후 전체 세트 확인란에 표시한다. 선택한 열이 회색으로 변한다.
 




도넛 차트

가장 좋지 않은 차트는 아마 원형 차트일 것이다. 층으로 구성된 세로 막대형 차트처럼 시각적으로 정확하지 않다. 또 혼잡하다. 크기가 작은 데이터 포인트는 알아볼 수 없는 조각으로 줄어든다. 또 지나치게 장식이 많은 3D 원형 파트가 자주 사용되는 데, 사용자에게 혼란과 혼동을 초래한다.

엑셀에서 원형 차트를 활용할 때 도넛 차트를 고려해야 하는 이유가 여기에 있다. 원형 차트처럼 전체에서 차지하는 비중을 시각화 할 때 유용하다. 그러나 ‘여백의 미’가 존재하는 차트이다. 자주 사용되지 않아 보는 사람에게 신선한 느낌을 준다.

도넛 차트를 만들려면 데이터를 선택한 후 삽입을 클릭한다. 그리고 파이나 도넛 차트 아이콘을 클릭한 후 도넛 차트를 선택한다. 차트를 조정하려면 차트 부분을 오른쪽 클릭한 후 ‘데이터 계열 서식’을 클릭한다. 표시되는 창에서 ‘도넛 구멍 크기 값을 60% 정도로 설정한다 (개인적으로 기본값인 75%는 폭이 너무 좁다고 생각).



프로젝트 추적에 유용한 간트(Gannt) 차트 활용하기
층으로 구성된 막대형 차트를 프로젝트에 유용한 간트 차트로 바꿀 수 있다. 맨위의 행에 ‘Start’, ‘Stage’, “Date’, ‘On Task’라는 4개의 열로 구성된 표를 만든다. 셀 B2부터 B7까지 ‘Plan(계획)’, ‘Build(구축)’, ‘Approve(승인)’ 같은 프로젝트 단계를 입력한다. Start 아래 셀 A2에는 첫 단계의 시작일을 입력한다. 그리고 셀 D2부터 D7(On Task 아래)에는 각 단계를 완료하는데 소요된 기간(일)을 입력한다. 셀 C2에 “=A2”를 입력하고, 셀 서식은 일반으로 지정한다. 43205 같은 숫자를 확인할 수 있을 것이다. 엑셀에서 차트 레이아웃에 필요한 시간/날짜 코드이다. 마지막으로 C3에 “=C2+D2”를 입력한다. 그리고 이 식을 셀 C4부터 C7까지 복사해 붙여 넣는다.

B부터 D열까지의 셀을 선택한 후 삽입을 클릭한다. 가로 막대형 차트 아이콘을 클릭한 다음 누적(층으로 구성된) 가로 막대형 차트를 선택한다. 이제 차트를 수정하자. X축 레이블을 오른쪽 클릭하고 축 서식을 클릭한다. ‘표시 형식’ 항목에서 범주를 클릭한 다음 드롭다운 메뉴에서 날짜를 선택한다. 형식에서 길이가 짧은 날짜 서식을 선택한다. 이후 상단 축 옵션 아래 ‘경계’에서 최소값에 Task 리스트 첫 항목 C열의 값을 입력한다. 43205같은 형태로 표시되어야 한다. Stage가 역순으로 정렬될 것이다. Y축을 오른쪽 클릭하고 축 서식을 클릭한다. 축 옵션 창에서 ‘항목을 거꾸로’에 체크한다.



온도계 차트로 목표 표시하기
목표 달성치를 시각화해 표시해야 하는 경우가 있다. 2만 달러의 자본을 유치한다는 목표를 예로 들 수 있다. 이때 유용한 차트가 온도계(Thermometer) 차트이다. 4개 셀로 구성된 예제로 연습을 해보자. 왼쪽은 Goal과 Donations, 오른쪽은 $20,000 및 $16,425로 구성한다. 셀을 선택한 다음 리본에서 삽입을 클릭한다. 그리고 세로 막대 차트 중 누적 세로 막대를 선택한다. 리본에서 ‘행/열 교체(Switch Row/Column)’ 아이콘을 클릭한다. 차트의 X축 레이블에 1이 표시되도록 만들기 위해서이다.

Donations 막대를 오른쪽 클릭한 다음 데이터 계열 서식을 클릭한다. 계열 옵션 창에서 보조 축(Secondary Axis)을 클릭한다. 막대가 Goal 막대 뒤로 사라지고, 두 번째 Y축 레이블이 표시된다. 왼쪽 축을 오른쪽 클릭한 다음 축 서식을 클릭한다. 그리고 축 옵션 창의 '최대값’ 상자에 20,000을 입력한다. 오른쪽(보조)축도 같은 방법을 적용한다.

Goal 막대를 오른쪽 클릭하고, ‘채우기 및 선(Fill & Line)’ 아이콘을 클릭한다. 그리고 ‘채우기 없음’을 클릭하고 윤곽선을 검은색 선으로 지정한다. Donation 막대에 원하는 색을 적용하고, 윤곽선은 ‘선 없음’으로 설정한다. 오른쪽 축과 X축 레이블을 삭제해 차트를 정리한다. 또 축 서식 창에서 왼쪽 축의 크기를 줄인다. 축 옵션의 소수점(Number Decimal)을 0으로 설정한다. 원할 경우 틱(tick) 마크를 추가한다.



데이터를 입력하면 자동으로 업데이트되는 차트
입력한 내용을 기준으로 특정 데이터를 표시하는 차트 기능을 소개한다. MATCH 및 OFFSEST 함수를 이용하는 아주 유용한 기능이다. 배열에서 데이터를 가져와 차트가 연결된 셀에 표시하는 기능이다. 연도와 관련된 7개의 열, 영업 담당자와 관련된 12개의 행으로 구성된 데이터 배열을 예로 들자. 각 셀에는 연간 매출액이 입력되어 있다.

오른쪽 마지막 열의 각 셀에 다음 수식을 입력한다.

=OFFSET($A$1,ROW()-1,MATCH($J$1,$B$1:$H$1,0))

엑셀이 배열의 머리글 열에서 셀 J1의 연도와 일치하는 값을 찾는 함수이다. 엑셀은 OFFSET 함수를 이용해 해당 연도의 데이터를 가져와 I열의 셀에 표시를 한다. 즉 셀 J1에 2014를 입력하면, 열 I의 수식이 2014 아래 셀의 데이터를 표시한다(이 경우 열 E). X축으로 열 A, 데이터에 열 I를 이용하는 차트를 만들기만 하면 된다. 그러면 열이 자동으로 업데이트되어, 셀 J1에 입력한 연도에 해당되는 데이터를 표시한다. 아주 유용한 기능이다.



또 하나의 동적인 차트 활용법

이번에는 스크롤이 가능한 리스트를 기준으로 선형 차트를 업데이트하는 방법을 알아보자. 앞서 사용한 데이터 배열 아래, OFFSET 및 MATCH 함수를 사용해 행 15에 셀들을 추가하자. 셀 A18에 입력된 담당자를 기준으로 영업 관련 데이터를 끄집어내는 수식을 적용한다. 셀 A15부터 H15까지 다음 수식을 적용한다.

=OFFSET($A$1,MATCH($A$18,$A$1:$A$13,0)-1,COLUMN()-1)

셀 A1에서 H1(X축 값)과 셀 A15에서 H15(영업 담당자 이름과 영업 실적 데이터)를 선택한다. 그리고 삽입>추천 차트를 클릭한 다음 리스트 가장 위의 선형 차트를 선택한다. 이제 셀 A2에서 A13까지의 이름이 표시될 드롭다운 리스트를 만들자. A18을 선택한 다음 리본의 데이터 탭을 선택한다. 그리고 데이터 유효성 검사를 두 번 연속해 클릭한다. ‘드롭 다운 컨트롤 허용’의 리스트를 클릭하고, 소스 상자를 클릭한다. 그리고 마우스를 사용해 A2에서 A13까지 선택한다. 확인을 클릭한다. 이제 A18을 누르면 드롭다운 컨트롤이 표시된다. 여기를 클릭하면 담당자 이름이 스크롤 할 수 있는 리스트로 표시된다. 하나를 선택하면 A18의 이름은 물론 행 15의 담당자 관련 데이터, 여기에 연결된 차트가 모두 업데이트된다. editor@itworld.co.kr