통계 데이터 분석툴인 미니탭에 보면 개별 값 그림 이라는 그래프 형식이 있습니다. 한가지 항목에 여러개의 데이터가 있을 때, 세로축으로 점들을 찍어서 직관적으로 산포를 보는 용도입니다. 이 그래프를 엑셀 함수를 이용해 나타내 보겠습니다.
개별값 그림이라는 것이 어떤걸 말하는지는 아래 이미지를 보시면 바로 아 하고 아실겁니다.
개별 값 그림 그래프란?
구글에서 개별 값 그림, individual value plot 이라고 쳐보면 이런 형태의 그래프들이 나옵니다. 가로축에 분류 항목이 있고, 세로축에 그 항목당 가지고 있는 여러 데이터들이 점으로 찍혀서 퍼진 정도를 볼 수 있게 비교하는 차트입니다. 퍼진 정도는 분산, 산포 라고도 하지요.
A와 B가 각각 100개의 데이터를 가지고 있다고 했을 때, 최대/최소나 평균만 비교하는 것이 아니라 100개를 몽땅 찍어보고 어떻게 쏠려있는지 퍼져있는지 두 군간의 트렌드를 비교하는 목적입니다.
오늘은 이 개별 값 그림 그래프를 엑셀에서 구현하는 2가지 방법에 대해 살펴보겠습니다.
분산형 차트로 만들기
아래 예시와 같이 사용할 데이터는 3가지 과목에 대해 성적 수치들을 가지고 있는 형태입니다. 수학 영어 국어 각 10개의 성적이 있고 이 성적들을 점으로 찍어서 분포를 한눈에 비교하기 쉽게 나타내보고자 합니다.
🔺 먼저 처음 두 행의 영역을 선택해줍니다. 항목 부분과 첫번째 데이터까지만 드래그한 상태에서 삽입-차트-세로막대형을 선택해서 그려줍니다. 당연히 첫번째 데이터만 비교하는 듯한 막대 그래프가 하나 생기겠죠?
🔺 그다음 차트를 클릭하면 상단 리본메뉴에 차트 도구 부분이 나타나는데 거기에서 디자인 – 데이터 선택을 눌러줍니다. 차트에 우클릭 하셔서 데이터 선택을 눌러주셔도 됩니다.
🔺 데이터 선택을 누르면 말그대로 차트의 데이터 범위를 수정하는 창이 뜨는데요. 여기에서 현재 첫번째 데이터 한줄만 선택했었기 때문에 계열1만 생성되어 있을 것입니다. 계열1 편집을 눌러주세요.
🔺 계열 편집에서 계열 값을 수동으로 0,0,0 으로 입력해줍니다. 수학 영어 국어 과목의 값을 강제로 0으로 입력하는 것입니다. 데이터 원본 선택 화면으로 돌아가는데 확인을 한번 더 눌러줍니다. 여기서 취소를 하면 계열 편집한 내용이 반영되지 않습니다.
🔺 데이터를 0으로 바꿨으니 차트에 막대부분은 사라졌을 것이고요. (실제로 0 데이터가 하나씩 있는 셈) 이제 데이터 옆칸에 전부 1,2,3인 열을 3개 만들어줍니다. 위 예시와 같이 따라해주세요.
🔺 자 이제 다시 차트를 클릭하고 디자인-데이터 선택에서 이번에는 계열 추가를 눌러주세요.
🔺 계열 이름에는 첫번째 과목인 수학 부분을 클릭하고, 계열값은 1로 입력해줍니다. 초기값이 1로 되어있기 때문에 수정없이 그대로 확인을 눌러 적용하면 됩니다. 차트 보시는 것과 같이 1짜리 막대그래프가 하나 생깁니다.
🔺 이제 새로 만든 1짜리 그래프를 클릭한 뒤 우클릭 – 계열 차트 종류 변경을 눌러줍니다. 이 막대그래프 차트를 분산형으로 바꿀 것입니다.
🔺 차트 종류 변경화면이 나오면 계열에서 수학부분에 차트종류를 누르고 분산형을 골라주세요. 그러면 1짜리 막대그래프가 1짜리 점 한개 찍히는 그래프로 변경됩니다.
🔺 자 이제 다 됐습니다. 다시 차트에 디자인-데이터 선택으로 들어가서 계열중에 수학을 편집해주시고, 계열 편집 화면에서 위와 같이 지정합니다. 분산형 차트가 되었기 때문에 X-Y 쌍의 데이터를 가지게 되는데요. X값은 만들어 두었던 1을 쭉 드래그 해서 지정해주시고, Y값에 실제 수학성적 데이터를 선택해줍니다.
이렇게 하면 X축 1번에 수학성적 Y값이 분산형 데이터로 점이 찍히게 되는 것입니다. X축 값이 1이지만 실제로 이 차트는 최초에 막대 그래프로 생성했기 때문에 막대 그래프 X축 레이블인 과목명으로 나오게 됩니다. 분산형 차트는 X축 레이블이 1,2,3… 숫자로만 지정되고 변경할 수 없어서 이러한 편법을 사용하는 것입니다.
🔺 이제 영어 국어 데이터도 만들어 주어야겠죠? 아까 차트 종류 변경해서 분산형을 선택했었기 때문에, 데이터 선택 – 계열추가를 하면 이제부터는 자동으로 분산형 데이터 추가하는 형태로 나옵니다. 위와 같이 X값은 만들어놓은 1,2,3 을 선택하고 Y값은 실제 데이터인 과목 성적을 지정해줍니다.
🔺 이렇게 하면 완성이 되었습니다.
꺾은선 그래프로 만들기
두번째 소개하는 방법은 꺾은선 그래프로 만드는 방법입니다. 위에서 분산형 차트의 X축 레이블 설정불가 문제를 보완하기 위해 데이터 0인 막대그래프 하나를 숨겨놓는 방법을 사용한 것인데요. 위에 분산형 차트로 만드는 부분까지 잘 따라하셨다면 굳이 이 방법은 몰라도 됩니다.
마찬가지로 꺾은선 그래프를 이용해서 X축 레이블을 표시하고 선을 없애서 분산형처럼 만들수도 있습니다. 만약에 앞에서 예시로 사용한 성적 데이터가 두 반이 있는 경우라면?
🔺 이런 식으로 1반도 수학/영어/국어 성적이 있고 2반도 수학/영어/국어 성적이 있으면 총 6개의 데이터를 점을 찍어서 비교해봐야겠죠?
🔺 일단 데이터의 형태를 위와 같이 바꿔줍니다. 1반 데이터는 놔두고 2반 데이터는 1반 아래쪽으로 붙여주세요. 그리고 빈칸들은 =NA() 함수를 이용하여 #N/A 오류값을 반환하도록 처리합니다. 즉 데이터가 0이 아니라 없는 칸으로 만들어주는 것입니다.
그다음에 전체 영역을 선택하고 꺾은선차트를 그리면 오른쪽과 같이 생성될거에요. 1반 수학 성적이 세로로 쭉 나와야되는데 1반 수학성적이 가로로 꺾은선 그래프가 되어버렸네요.
🔺 차트를 클릭하고 디자인-데이터 선택에서 행/열 전환을 눌러줍니다. 오른쪽과 같이 수학/영어/국어 별로 세로축에 점수가 찍히게 됩니다. 아까 분산형으로 만든것과 똑같이 되었습니다.
🔺 이제 차트에 그래프 하나를 클릭해주세요. 데이터 중에 선택된 부분이 표시가 되었죠? 우클릭 – 데이터 계열 서식으로 들어가서 선 없음을 체크합니다. 꺾은선 그래프의 선이 없어지고 점만 남게됩니다.
그다음에 위아래 방향키를 누르시면 다른게열 데이터가 선택되는데요. 선택, F4 (방금동작 반복하는 단축키), 선택, F4 이런식으로 반복해주면 데이터가 많아도 금새 선을 다 없앨 수 있습니다.
🔺 선을 다 없애면 이렇게 점만 찍힌 차트모양이 됩니다.
🔺 그래프는 다 그렸으니까 차트만 예쁘게 수정해주시면 되겠습니다. 표식 선색과 채우기 색상을 통일해서 보기좋게 해주면 되고요. 이렇게 하면 1반과 2반의 차이가 잘 드러나겠죠.
🔺 1반 과 2반의 수학/영어/국어 전체 성적 데이터를 개별값 그림 그래프로 만든 완성본입니다.
🔺 이런 식으로도 응용할 수 있습니다. 비교하려는 2개이상의 그룹이 있는데 각 그룹별로 서브 항목에 대한 데이터를 가지고 있는 경우 이렇게 나타내 볼 수 있습니다. 예를 들어서 위와 같이 마트별로 전체 점포의 고객만족도 점수가 있고 그게 분기별로 어떻게 달라졌는지를 나타낼 수도 있겠죠.
두 가지 방법을 소개드렸는데 아무래도 만들기 편하고 세로축 항목별로 스타일 변경이 용이한 점을 본다면 첫번째 분산형 차트를 이용한 방법이 더 나아 보이네요. 두번째 예시처럼 두 그룹 데이터가 있는 경우에도 분산형차트 이용한 방법으로도 똑같이 가능하고요.
