안녕하세요. 직장인의 엑셀 능력을 향상 시켜주는 쑥떡입니다! 오늘의 그래프는 "변화 브릿지 그래프"입니다. 브릿지 그래프는 현업에서도 해당 단어가 통용될 정도로 그 이름이 알려진 그래프이며, 특정 두개 값의 차이를 항목별로 분석하여 그 효과를 보여줄 때 사용합니다. 값의 변화를 단계별로 나누어 분석하는 그래프를 만들어보도록 하겠습니다.
연초와 연말의 가입자수를 월별로 나누어 설명해야하는 상황이라고 가정해보겠습니다. 단순히 세로 막대 그래프를 기입하는 방법도 있고, 증감을 별도 그래프로 추가하여 설명하는 법도 있지만, 월별 가입자수보다 월마다 어떻게 변화했는지가 중요할 경우에는 브릿지 그래프가 유용하게 사용됩니다.
그래프를 그리기 위해서는 항상 데이터 세팅이 가장 중요합니다. 그래프를 이쁘게, 깔끔하게 만드는 것도 중요하지만 데이터 가공이 되지 않는다면 그래프를 그릴수가 없기 때문에 데이터를 준비하는 것에 좀 더 신경을 쓰는 것이 좋습니다. 브릿지 그래프를 그리기 위해서는 증가/감소 구간의 분리가 필요하고, 이를 통해 세로 막대길이를 역으로 계산해주어야 합니다. 따라서, 기존 데이터 옆에 표를 하나더 만들어서 왼쪽부터 차례대로 월, 가입자수, 증가, 감소, 증감 열을 추가해줍니다. 월은 기존데이터에서 불러오고, 증감 역시 기존 데이터를 통해 계산해줍니다. 여기에서 한가지 중요한 점은 브릿지 그래프의 양쪽 끝은 전체 막대가 그려져야 하므로 12개월 데이터라고 하면 총 14개의 데이터가 있어야 합니다(12월이 12월 말이지만, 그래프에는 12개월 변화분으로 그려질 예정이므로 연말 데이터가 하나더 있어야 합니다.).
증감데이터가 확보되었으면 이제 증가와 감소를 각각 나눠줘야 합니다. 나누지 않으면 감소되는 (-)값이 그래프의 0 이하에 별도로 그려지게 되어 브릿지가 되지 않기 때문입니다. 증가 부분에는 위와 같이 G3 = IF(H3>0,H3,0)을 기입하여, 증감이 0보다 클때에만 기입되고, 0보다 작을 때에는 0이 기입되게 해줍니다.
반대로 감소 열에는 G3 = IF(H3<0,-H3,0)을 기입하여, 증감이 0보다 작을때에만 기입되고, 0보다 클 때에는 0이 기입되게 해줍니다. 단 감소값 역시 얼마나 감소했는지 그 크기가 필요하므로, H3앞에 -를 추가하여 마이너스 부호가 없어지게 처리해줍니다.
이번에는 만들어진 증가, 감소폭을 이용해 가입자수를 역산해줍니다. 단, 여기에서 가입자수는 기존 데이터의 가입자수를 똑같이 구하는 것이 아니라, 브릿지 아래에 투명하게 처리해줄 막대의 크기를 계산하는 것이라고 이해하면 될 것 같습니다. E3 = E2 + F2 -G3의 수식이 입력되며, "전기 수치 +전기 증가 - 당기 감소"에 대응되는 셀들을 지정하면 됩니다. 앞서 말했듯이 기존 가입자수에 대응되는 데이터가 아니므로 다른 값이 도출되더라도 그냥 진행하면 됩니다.
그래프를 위해 만든 표 중에서 가장 오른쪽에 있는 증감 열은 제외한 모든 데이터(D~G열)를 지정한 다음, [삽입]탭의 세로 그래프를 클릭한 다음, [누적 세로 막대형 그래프]를 클릭해줍니다. 클릭하면 위와 같은 기본 그래프가 입력되며, 해당 그래프를 기초로 수정/변경을 통해 브릿지를 만듭니다.
누적 그래프에서 가장 아래에 있는 막대를 선택해준 다음, 오른쪽 클릭을하고, [데이터 계열 서식]을 선택합니다. 오른쪽 화면에 나타나는 데이터 계열 서식에서 [계열 옵션](막대 그래프 모양)을 클릭하고, [간격 너비] 옆의 수치를 0%로 조절해 줍니다. 중간에 위치한 게이지를 왼쪽 끝으로 옮겨줘도 똑같이 0%가 됩니다.
그리고 그래프 가장 아래부분을 다시 클릭한 다음, 색상을 투명하게 변경해줍니다. 단, 양쪽 끝의 막대그래프는 전체 수치를 보여주어야 하므로 투명하게 만들지 않습니다. 전체를 투명하게 변경한 다음 양쪽 그래프의 색상을 원하는 색으로 지정하여도 되고, 막대가 많지 않다면 가운데 막대들을 하나하나 선택하여 투명하게 변경하여도 됩니다.
다음은 세로축 조정입니다. 값이 모두 큰 상황에서 그래프를 0부터 표현하면 브릿지가 매우 얇게 표현되므로, 브릿지의 크기를 크게 해주기 위해서 브릿지가 위치한 부분이 확대될 수 있도록 세로축의 최소값/최대값을 조절해 줍니다. 첫 데이터보다 조금 낮은 값을 최소값으로, 마지막 데이터보다 조금 높은 값을 최대값으로 해주면 적절히 반영됩니다. 축서식은 세로축 부분을 우클릭 한다음 [축 서식]을 클릭해주면 됩니다.
그리고 막대부분을 우클릭한 다음 레이블을 추가해 줍니다. 모든 값이 양수로 기입되어 있어 레이블이 처음에는 (+) 값으로 입력될 텐데, (-)값들은 글자색상을 변경하거나, ( )를 추가해주는 등의 양식 변경을 해주면 됩니다. 레이블 글자 양식과 관련해서 방법이 궁금하신 경우에는 댓글 달아주시면 별도로 설명드리겠습니다.
마지막으로 언제나 그렇듯 깔끔한 그래프를 위해 세로축, 가로선 등 불필요한 선을 제거해주고, 원하는 색상 또는 회사에서 요구하는 색상을 추가하여 그래프를 깔끔하게 만들어줍니다. 그리고, 설명이 필요한 부분에는 대비되는 색상의 박스를 추가하고 간단한 설명을 추가해줍니다. 엑셀은 쑥떡!
쑥떡(SSOOKDDEOK)
인스타 │@ssookddeok.excel
이메일 │ssookddeok.official@gmail.com
*문의/질문은 언제나 환영합니다!
'EXCEL GRAPH' 카테고리의 다른 글
| 엑셀 그래프 시리즈 005. 기준선 분산 그래프 (0) | 2023.03.20 |
|---|---|
| 엑셀 그래프 시리즈 004. 추정치 포함 그래프 (0) | 2023.03.19 |
| 엑셀 그래프 시리즈 002. 차이값 강조 그래프 (0) | 2023.03.16 |
| 엑셀 그래프 시리즈 001. 양방향 가로 그래프 (6) | 2023.03.15 |