MS 엑셀로 롤리팝, 덤벨, 슬로프 차트 그리기

R로 할 수 있는 시각화 작업은 거의 대부분 마이크로소프트(MS) 엑셀로도 할 수 있습니다. 조금 번거로울 뿐이죠.

바로 직전 포스트 제목은 'R로 롤리팝, 덤벨, 슬로프 차트 그리기(feat. geom_point, geom_segment)'. 그렇다면 엑셀에서도 롤리팝, 덤벨, 슬로프 차트를 모두 그릴 수 있다는 뜻이겠죠?

R로 작업할 때와 마찬가지로 프로배구 2018~2019 V리그 남녀부 각 구단 공격 유형별 시도 횟수 데이터를 가지고 같은 그래프를 그려보겠습니다. 일단 자료는 아래 파일로 붙여 놓겠습니다.

kovo_sets.csv

이 파일은 엑셀 통합 문서(.xlsx) 형식이 아니라 CSV(Comma Separated Value·쉼표로 구분한 값) 형식이지만 엑셀에서 아주 부드럽게 열립니다.

이번 포스트는 엑셀 2016 버전 기준입니다. 버전이 달라도 큰 틀에서는 작업 과정 차이가 없지만 메뉴 위치 등이 일부 다를 수도 있습니다. 이 점은 주의하셔야 합니다.

롤리팝 차트부터 그려보자

제일 먼저 지난 포스트에서 그랬던 것처럼 구단별 퀵오픈 시도 횟수를 롤리팝 차트로 그리는 것부터 시작하겠습니다.

첫 번째 단계는 자료 정리. 우리 데이터에는 남녀부가 같이 들어 있기 때문에 먼저 남녀부를 기준으로 삼고 이어서 퀵오픈이 적은 순서대로 (오름차순) 전체 자료를 정렬합니다.

이어서 남자부 7개 팀 퀵오픈 시도 횟수가 들어 있는 E2~E8을 선택해 가로 막대 그래프를 그립니다. 막대 그래프가 나오면 그래프 위에서 마우스 오른쪽 버튼을 누르고 '데이터 선택', 이어 나오는 화면에서 '가로(항목) 축 레이블' 밑에 있는 편집 아이콘을 클릭하시고 B2~B8을 선택하시면 팀 이름까지 붙일 수 있습니다.

이 그래프를 선택하면 '차트 도구'라는 메뉴가 나타납니다. 여기서 디자인 → 빠른 요소 추가 → 오차 막대 → 표준 오차를 선택합니다. 

그러면 그래프에 오차 막대가 생깁니다. 이 오차 막대를 선택하고 마우스 오른쪽 버튼을 눌러서 오차 막대 서식 탭을 엽니다.

화면 오른쪽에 오차 막대 서식 탭이 나타나면 △방향: 음의 값 △끝 스타일: 끝 모양 없음 △오차량: 백분율을 각각 선택합니다. 백분율에는 100%를 입력합니다.

계속해서 '채우기 및 선'(페인트통 모양 아이콘)을 선택한 다음 화살표 머리 유형을 '타원 화살표'로 선택하시고 화살표 머리 크기도 제일 큰 형태를 선택합니다.

마지막으로 막대 그래프를 선택하시고 데이터 계열 서식에서 '채우기: 채우기 없음'을 선택하시면 롤리팝 그래프 완성입니다. 데이터 레이블을 추가하시면 각 숫자가 얼마인지도 나타낼 수 있습니다.

이제부터는 취향에 따라 차트 디자인을 손보시면 됩니다. 저는 엑셀에 이미 들어 있는 테마를 적용한 다음 살짝 색깔만 손을 봤습니다.

덤벨 차트도 그려보자

롤리팝 차트는 값이 하나여도 그릴 수 있지만 덤벨 차트는 값이 두 개가 필요합니다. 이것만 빼면 롤리팝 차트를 그리는 것과 차이가 날 게 없습니다. 이번에 우리는 공격 유형별 최저·최고 기록을 가지고 덤벨 차트를 그릴 겁니다.

그러면 먼저 각 공격 유형별 최소·최다 기록이 각각 얼마인지 알아봐야겠죠? 엑셀에서는 이럴 때 MIN(), MAX() 함수를 쓰면 됩니다. 먼저 J2셀에 =MIN(C2:C8), J3열에 =MAX(C2:C8)이라고 입력한 뒤 O열까지 복사하겠습니다. 그러면 2행에는 오픈 공격부터 후위 공격까지 최저 기록, 3행에는 최고 기록이 나옵니다. 이어서 J1셀부터 O1셀까지 이름을 붙여줍니다.

그리고 나서 J1부터 O3까지 선택한 다음 J6셀에 커서를 옮기고 마우스 오른쪽 버튼 → 선택하여 붙여 넣기 → 값 → 행/열 바꿈을 차례로 클릭합니다.

붙여 넣기를 마무리 하셨으면 J5에 종류, K5에 최저, L5에 최고, M5에 차이라고 제목을 달아줍니다. 차이를 나타내는 M6~M11셀에는 최고에서 최저를 빼는 수식을 넣어주시면 됩니다. M6셀에 =L6-K6을 넣으시고 아래로 복사하시면 그만입니다. 마지막으로 J5부터 M11까지 그러니까 지금 만든 작은 표를 선택하시고 최고 기준으로 오름차순 정렬까지 해주시면 됩니다.

이제부터는 롤리팝 차트와 비슷합니다. 일단 최저 기록이 들어 있는 K6~K11셀을 선택하신 다음 차트 삽입 화면으로 이동합니다. 이번에 우리가 선택할 차트 종류는 '누적 가로 막대형'입니다.

막대 그래프가 처음 나오면 그래프 위에서 마우스 오른쪽 버튼을 누르고 '데이터 선택'을 누릅니다. 새로 창이 뜨면 '범례 항목(계열)' 아래 있는 추가를 눌러서 차이 그러니까 M6~M11셀을 선택합니다. 계속해 오른쪽에 있는 '가로(항목) 축 레이블'을 클릭하고 공격 종류 이름이 나와 있는 J6~J11셀을 선택해 넣어줍니다. 계열 이름도 바꿔주면 좋습니다. 마지막으로 확인.

다음 단계는 오차 막대 삽입입니다. 차이를 나타낸 부분(주황색)을 클릭한 뒤 롤리팝 차트 때처럼 차트 도구 - 디자인 - 차트 요소 추가 - 오차 막대 - 표준 오차 순서로 진행합니다.

이 다음 단계도 롤리팝 차트와 마찬가지입니다. 오차 막대를 선택하고 마우스 오른쪽 버튼을 클릭해 오차 막대 서식을 연 다음 △방향: 음의 값 △끝 스타일: 끝 모양 없음 △오차량: 백분율을 각각 선택합니다. 백분율에는 100%를 입력합니다(2).

그다음에는 뭘할까요? 맞습니다. 이 서식에서 페인트 통 모양 아이콘(채우기 및 선)을 선택하고 화살표 머리 유형과 머리 크기를 바꿔줍니다. 아, 이번에는 꼬리 유형과 크기도 같이 바꿔주셔야 합니다.

이어서 할 일은? 예, 그렇습니다. 막대 그래프 두 종류를 각각 선택해 마우스 오른쪽 버튼을 눌러 데이터 계열 서식 탭을 열고 채우기 없음, 선 없을을 선택합니다. 그럼 이런 그림이 기다리고 있을 겁니다. (곧바로 덤벨 차트 쪽으로 스크롤을 내려 과정이 잘 이해가가 가지 않으시는 분은 위에 있는 롤리팝 차트 꼭지를 참고하시면 좋습니다.)

이렇게 채우기 없음, 선 없음으로 처리했다고 그래프가 아예 사라진 건 아닙니다. 그래프가 있을 것 같은 부분을 선택하시고 마우스 오른쪽 버튼을 누르시면 메뉴가 뜹니다. 데이터 레이블 추가를 누르시면 숫자를 확인할 수 있게 됩니다.

문제는 레이블이 둘 다 그래프 가운데에 자리잡는다는 것. 레이블을 아무 거나 선택하신 다음에 오른쪽에 데이터 레이블 서식 탭이 뜨면 레이블 위치를 '안쪽 끝에'로 바꿔주시면 레이블 표시에 도움을 받을 수 있습니다. 그래도 오른쪽 레이블 위치가 마음에 들지 않으실 텐데 하나씩 선택해서 적당히 옮겨주시면 됩니다.

이때 주의하셔야 할 건 오른쪽 레이블은 최고가 아니라 차이를 나타내고 있다는 것. 이 문제는 데이터 레이블 서식 탭에서 범위 선택을 클릭하시고 최곳값이 들어 있는 L6~L11셀을 선택하시는 걸로 해결하실 수 있습니다. 그리고 값 속성 체크 표시를 클릭해서 빼면 레이블에 최고만 남게 됩니다.

이러면 덤벨 그래프 완성입니다. 역시 취향에 따라서 디자인을 바꾸시면 됩니다. 이번에도 저는 원래 있는 테마를 바탕으로 살짝만 손 봐서 아래 같은 결과를 얻었습니다.

슬로프 차트까지 그려보자

이제 마지막 슬로프 차트만 남았습니다. 이번 포스트에서 우리가 다루고 있는 차트 종류 세 가지 가운데서는 슬로프 차트가 엑셀에서 그리기가 제일 쉽습니다. 엑셀에서 이미 제공하는 그래프 형식이기 때문입니다. 이번에는 남녀부에 따라 공격 유형이 어떻게 다르게 나타나는지 그려보겠습니다.

이런 그래프를 그리려면 공격 유형별 남녀부 평균이 필요합니다. K2셀에 =AVERAGE(C2:C8), K3셀에 =AVERAGE(C9:C14)를 넣어서 각각 남녀부 오픈 공격 시도 평균 횟수를 구합니다. 이어서 P열까지 이 식을 복사해서 값을 채워넣고 각 행열에 어울리는 이름도 붙여 줍니다. 이어서 J1부터 P3까지 통째로 선택한 다음 차트 삽입 화면으로 넘어갑니다. 여기서 우리는 꺾은선형 가운데 두 번째 차트를 선택할 겁니다.

사실 이 유형을 선택하는 순간 이미 슬로프 차트는 다 그린 거나 마찬가지입니다. 차트가 나오면 계열 하나씩 데이터 계열 서식 탭에서 화살표 머리와 꼬리 유형을 원 형태로 바꿔주면 이미 그럴 듯한 슬로프 차트가 나옵니다.

나머지는 그냥 디테일입니다. 각 계열을 선택해 데이터 레이블을 표시하도록 한 다음, 표시 형식을 숫자 + 소수 자릿수 0으로 바꿔줍니다. 이어서 남자 쪽은 레이블 위치를 왼쪽, 여자부는 오른쪽으로 설정합니다.

굳이 밑에 범례가 있을 필요가 없으니까 지우는 대신 왼쪽 레이블에 계열 이름까지 함께 표시하게 만듭니다. 레이블 사이 간격을 조절하고 차트 세로 높이를 좀 키워주면 이런 그래프를 얻을 수 있습니다. 

개인적으로 이 정도면 충분히 훌륭하다고 생각하기 때문에 슬로프 차트는 따로 완성본을 제출하지 않겠습니다.

아래 있는 엑셀 파일에는 제가 이 포스트를 쓰면서 직접 작업한 내용에 들어 있습니다. 혹시 필요한 분이 계시면 내용을 확인해 보시기 바랍니다.

kovo_sets.xlsx

이 포스트를 읽으신 모든 분께서 원하시던 그래프를 그리시는 데 성공하시길 기원합니다. 그럼 Happy Charging!