쑥떡의 엑셀 공작소

안녕하세요. 직장인의 엑셀 능력을 향상 시켜주는 쑥떡입니다!  오늘의 그래프는 "기준선 분산 그래프"입니다. 분산 그래프는 데이터 집합을 비교, 분석하는 경우에 많이 사용하며, 두가지의 값을 가진 데이터를 x, y축으로 표현된 그래프 위에 마치 흩뿌리듯이 표현한느 그래프입니다. 단순히 데이터를 뿌리는 것에 그치지 않고 특정 기준선을 기준으로 두 그룹으로 나누는 방법에 대해 알아보겠습니다.

국가별 2020년 경제성장률과 2021년 경제성장률을 기준으로 설명하겠습니다. 나라별로 두개의 데이터가 부여되어 있으며, '2020년 대비 2021년 경제 성장률이 10%p 이상 증가"를 기준선으로 국가들을 분류해보겠습니다.

우선 두개년도의 성장률 차이값으로 데이터를 분류해야하므로, 2개연도 성장률의 차이값을 구해줍니다. 해당 값이 그래프에 포함되지는 않으나, 그래프의 기초자료가 되는 표를 만들때 사용될 예정입니다. 그리고 데이터 가장 아래부분에 최대값과 최소값을 추가해줍니다. 최대값, 최소값은 이후 그래프를 추가한 다음 기준선을 그리는데 사용이 되므로, 기준선을 어떻게 구성할지 생각하여 임의의 점으로 구성해줍니다. 예를 들어 위의 경우에는 차이값 10이 기준이 되므로, 차이값 10이 되는 임의의 점 두개를 지정해주되, 데이터 모두를 포함해야 하므로 (10,20)을 최대값, (-15,-5)를 최소값으로 지정해주었습니다.

기존 표 오른쪽에 새로운 표를 하나 추가합니다. 첫 열에는 2020년 데이터를 그대로 옮겨넣고, 2021년 데이터는 두번째 및 세번째 열에 나눠서 기입할 예정입니다. 10%p를 기준으로 데이터를 나눠야 하므로 두번째열(G열)에는 기존에 구해둔 차이가 10%p보다 클 경우 해당 차이값을 기재하고, 아닐경우 데이터 없음으로 처리해야 합니다. 이를 수식으로 표현하면 G3=IF(D3>=10,C3,NA())가 됩니다. 반대로 세번째열에는 10%p보다 작을 경우 데이터가 기재되어야 하므로 H3=IF(D3<10,C3,NA())가 기재되어야 합니다. 해당 수식을 기재한 다음 모든 셀에 붙여넣으면, 차이값이 10을 기준으로 G열과 H열로 나눠서 기재됩니다.

데이터 구성을 완료한 표를 전체 선택한 다음 [삽입]탭에서 [분산형 그래프]를 추가합니다. 10을 기준으로 데이터를 2개로 나누었는데, 그래프에도 해당 분류 기준에 맞춰 점이 찍혀있습니다. 꼭 2개로 나누지 않아도 되지만, 2개로 데이터를 미리 나눠 둘 경우 가장 큰 장점은 데이터 그룹별로 서식을 한번에 지정할 수 있다는 점입니다. 데이터를 나누지 않아도 개별 데이터의 색상을 변경할 수 있으나 데이터가 많은 경우에는 하나하나 색상을 지정하기에는 시간이 오래 소요됩니다.

만들어진 분산그래프를 살펴보면 우측 상단 끝부분과 좌측하단 끝부분에 점이 있는 것을 확인할 수 있습니다. 최대값과 최소값으로 지정한 점들인데, 최대값 점을 클릭한 다음 데이터 요소 서식으로 이동하여 [실선]을 선택하면 최대값과 최소값을 이어주는 선이 추가됩니다. 만약 추가된 선이 너무 길거나 짧으면 표에 기재된 최대/최소값 부분의 데이터를 수정하여 선의 위치, 길이를 수정해주면 됩니다.

선의 위치, 길이를 조정하였다면, 이번에는 가로/세로축 서식으로 이동하여 축 길이를 조정해줍니다. 임의로 설정하여도 좋지만, 기준선이 그래프 끝에 위치하도록 맞춰주는 것이 좋습니다. 축의 최대/최소값을 데이터에 정해둔 최대값, 최소값의 데이터와 동일하게 맞춰주는 것이 가장 보기 좋습니다. 그리고 가로선/세로선은 불필요하면 삭제하되, 점의 정확한 데이터를 한눈에 파악하고 싶다면 그대로 두는 것도 나쁘지 않습니다.

대략의 그래프 윤곽이 잡혔습니다. 이번에는 기준선 양끝에 위치한 점을 제거해 줍니다. 클릭하고 Delete키를 누르거나 삭제를 하면 데이터도 같이 삭제되어 그래프모양이 변경되므로 삭제가 아니라 투명하게 만드는 방법을 사용하여야 합니다. 최대값 점을 클릭(두번 클릭해야 해당점만 클릭됩니다.)한 다음 서식으로 이동하여, [표식]을 클릭하고 [채우기 없음], [테투리 선 없음]을 클릭하여 점이 보이지 않게 만들어 줍니다. 같은작업을 최소값에도 해주면 기준선 양끝에 있던 표식이 사라지고 깔끔한 직선으로 변경됩니다.

점 색상과 선 색상을 원하는 양식으로 적용하고, 주요 포인트에는 레이블을 적용해 줍니다. 분산 그래프에서 점들이 특정 영역에 몰려있는 경우에는 레이블을 모두 추가 하는 것 보다 주요 포인트에만 추가하는 것이 좋습니다. 모두 추가하려면 위치 조정에 시간이 오래걸리고 겹치지 않게 하기 위해서 글씨 크기를 줄여야하기 때문입니다.

추가로 필요한 경우에는 텍스트 박스를 추가하여 x축, y축 제목을 추가하고 그래표 주요 부분에 대한 설명도 간략하게 추가해 줍니다. 기준선을 기준으로 위 아래 영역이 각각 어떤 항목들의 집합인지 간략히 표현해준다면 좋은 그래프가 될 것이라 생각합니다. 엑셀은 쑥떡!

쑥떡(SSOOKDDEOK)

인스타 │@ssookddeok.excel
이메일 │ssookddeok.official@gmail.com
*문의/질문은 언제나 환영합니다!

안녕하세요. 직장인의 엑셀 능력을 향상 시켜주는 쑥떡입니다!  오늘의 그래프는 "추정치 포함 그래프"입니다. 추정치 포함 그래프는 사업계획 발표 또는 회사의 재무분석을 통한 미래가치 추정 등에 사용됩니다. 과거 수치와 같이 표현하되, 과거수치와는 구분되어 주목이 될 수 있게끔 그래프를 만들어보겠습니다.

앞서 말했다시피 보고서에는 과거 실제값과 미래 전망치가 같이 포함되는 경우가 종종 있습니다. 단순히 하나의 그래프로 표현해도 되지만, 실제치와 전망치를 구분하여 표현하다면 가독성이 더 좋아질 것입니다. 매출액과 매출액 증가율 데이터를 기반으로 하여 그래프를 그려보겠습니다.

그래프를 만들때 두가지의 양식을 적용하기 위해서는 데이터를 두개로 나눠주는게 좋습니다. 그래프에서는 두가지 양식을 적용하려면 데이터별로 클릭하여 색상 또는 양식을 적용해야하지만, 데이터를 미리 나눈 다음 그래프를 추가한다면, 각 분류별로 양식을 적용할 수 있어 작업시간을 크게 줄일 수 있습니다. 데이터를 나눌때 1행에 위치한 항목(매출액, 증가율)도 복사해줍니다. 항목이 없으면 그래프가 생각한대로 그려지지 않을 가능성이 있으니 꼭 복사해주시기 바랍니다.

여기에서 한가지 중요한 부분은 앞부분 데이터의 마지막 %값은 옮긴 데이터 부분에도 동일하게 추가하여야 한다는 점입니다. 증가율의 경우에는 꺾은선 그래프로 그려야하는데, 막대그래프와 달리 꺾은선 그래프는 선으로 이어져야 하기 때문입니다. 앞부분 데이터의 마지막 %값을 복사해두지 않는다면 위 표에서의 2022년과 2023년 사이의 선이 없어 꺾은선 그래프가 끊어지게 됩니다.

데이터 전체를 지정한 다음 그래프를 추가합니다. 그래프 종류는 만든 이후에 변경할 예정이므로 임의의 그래프를 우선 추가해줍니다

만들어진 그래프를 오른쪽 클릭한다음 [차트 종류 변경]을 선택합니다. 위와 같은 팝업창이 나타나면, 왼쪽 그래프 목록에서 [콤보]를 선택합니다. 각 계열별로 선택할 그래프를 지정해야하는데, 금액 데이터는 [묶은 세로 막대형]을 선택하고, 비율 그래프는 [꺾은선 형]을 클릭합니다.

증가율 추정치 부분 그래프(노란색)를 선택하고 우클릭한 다음, [데이터 계열 서식]을 클릭해줍니다. 우측 화면에서 대시 종류를 점선으로 변경해줍니다. 미래 추정값은 아직 발생하지 않은 데이터이기 때문에 일반적으로 점선으로 표현합니다.

막대와 꺽은선 그래프의 색상을 각각 원하는 색상으로 수정합니다. 데이터를 표에서 나눠뒀기 때문에 각각 서식을 적용하기에 아주 유용합니다. 강조하고 싶은 데이터는 진한 색상, 상대적으로 강조하지 않아도 되는 데이터는 연한색상을 적용합니다. 동일한 색상을 적용한 다음에 투명도를 조절하는 방식으로 색상을 변경하면 ,같은 계열의 색상을 적용할 수 있습니다.

오른쪽에 위치해있던 보조축의 글씨 크기를 최소한으로 줄이고 글자 색상을 흰색(바탕색이 따로 있을경우 해당 바탕색과 동일한 색상)으로 변경합니다. 해당 작없을 수행하면 세로축이 있던 공간이 남게되는데, 그만큼 정해진 공간 내에 그래프를 가로로 크게 그릴수 있어 도움이 됩니다.

그래프를 자세히 보면 과거치 마지막 부분(2022년)과 추정치 첫 부분(2023년) 사이의 간격이 다른 부분에 비해 조금 넓습니다. 이는 데이터가 다른 데이터로 변경되는 부분이기 때문에 발생하는데, 이는 계열 서식에서 조정가능합니다. 막대그래프를 우클릭하고 데이터 계열 서식으로 간 다음 막대 그래프의 계열 겹치기를 100%로 조정해줍니다. 이는 두 종류의 막대그래프에 각각 적용해주어야 합니다. 그 다음에 원하는 레이블을 추가하고, 나머지 서식을 반영해주면 끝납니다. 엑셀은 쑥떡!

쑥떡(SSOOKDDEOK)

인스타 │@ssookddeok.excel
이메일 │ssookddeok.official@gmail.com
*문의/질문은 언제나 환영합니다!

안녕하세요. 직장인의 엑셀 능력을 향상 시켜주는 쑥떡입니다!  오늘의 그래프는 "변화 브릿지 그래프"입니다. 브릿지 그래프는 현업에서도 해당 단어가 통용될 정도로 그 이름이 알려진 그래프이며, 특정 두개 값의 차이를 항목별로 분석하여 그 효과를 보여줄 때 사용합니다. 값의 변화를 단계별로 나누어 분석하는 그래프를 만들어보도록 하겠습니다.

연초와 연말의 가입자수를 월별로 나누어 설명해야하는 상황이라고 가정해보겠습니다. 단순히 세로 막대 그래프를 기입하는 방법도 있고, 증감을 별도 그래프로 추가하여 설명하는 법도 있지만, 월별 가입자수보다 월마다 어떻게 변화했는지가 중요할 경우에는 브릿지 그래프가 유용하게 사용됩니다. 

그래프를 그리기 위해서는 항상 데이터 세팅이 가장 중요합니다. 그래프를 이쁘게, 깔끔하게 만드는 것도 중요하지만 데이터 가공이 되지 않는다면 그래프를 그릴수가 없기 때문에 데이터를 준비하는 것에 좀 더 신경을 쓰는 것이 좋습니다. 브릿지 그래프를 그리기 위해서는 증가/감소 구간의 분리가 필요하고, 이를 통해 세로 막대길이를 역으로 계산해주어야 합니다. 따라서, 기존 데이터 옆에 표를 하나더 만들어서 왼쪽부터 차례대로 월, 가입자수, 증가, 감소, 증감 열을 추가해줍니다. 월은 기존데이터에서 불러오고, 증감 역시 기존 데이터를 통해 계산해줍니다. 여기에서 한가지 중요한 점은 브릿지 그래프의 양쪽 끝은 전체 막대가 그려져야 하므로 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
*문의/질문은 언제나 환영합니다!

안녕하세요. 직장인의 엑셀 능력을 향상 시켜주는 쑥떡입니다!  오늘의 그래프는 "차이값 강조 그래프"입니다. 차이값 강조 그래프는 두개의 데이터를 단순히 비교만하는 것이 아니라 차이값을 그래프에 추가함으로써 두 데이터 사이의 간극을 강조해주는 그레프입니다. 꺾은선 그래프 두개 사이에 막대 그래프를 추가한 양식 배워보겠습니다.
 

그래프를 그리기 위해 우선 필요한 데이터를 구성해야합니다. 그래프에 포함될 두 개의 항목 중 작은 값을 한번 더 입력하고, 두 항목의 차이 값을 다음열에 수식으로 입력해줍니다. 두 데이터 사이에 막대그래프를 추가해야하므로, 두 개의 데이터가 서로 크로스(cross) 되지않는 데이터에 적용하여야 합니다.
 

데이터 세팅이 완료되었다면, 데이터 전체를 선택하고, 임의의 차트를 삽입해 줍니다. 차트 종류를 변경할 예정이기때문에 아무 차트나 삽입하여도 상관없습니다. 차트 종류 변경을 위해 추가된 기본차트를 우클릭하고, [차트 종류 변경]을 선택합니다.
 

차트 종류변경화면이 나타나면 왼쪽 목록에서 [콤보]를 선택해줍니다. 오른쪽와면에 데이터별로 차트 종류를 선택하는 화면이 나타는데, 기존 데이터는 [표식이 있는 꺾은선형]을, 처음에 추가한 2개열은 [누적 세로 막대형]을 각각 선택해주고 확인을 클릭합니다.
 

차트 제목은 원하는 이름으로 정하고, 범례는 꺾은선 그래프에 해당하는 것만 남기고 나머지는 삭제해 줍니다. 추가로, 막대그래프의 아래부분은  색상을 투명하게 변경해줍니다. 색상 변경은 막대 부분을 우클릭한 다음 [데이터 계열 서식]을 클릭하면 변경할 수 있습니다.
 

다음으로는 그래프의 세로축을 조정해주는 단계가 필요합니다. 필수는 아니지만 그래프 위아래에 약간의 여백만 남을정도로 세로 축을 조정하면, 두 꺾은선 그래프의 간극이 강조되어 데이터를 보기에도 수월하고, 차이의 변화에 대해 설명하기도 쉬워집니다. 그렇다고 해서 위아래 여백없이 세로축을 조절하면 그래프가 너무 가득차서 보기엔 오히려 안좋을 수 도 있으니, 그래프를 보는 사람의 스타일 또는 회사 양식 등에 맞춰 적절한 수준으로 조절하시면 됩니다.
 

 
그래프를 만드는 것도 중요하지만, 만들어진 그래프를 깔끔하게 정리해주는 것도 좋은 보고서가 되는 지름길 중 하나입니다. 불필요한 가로선, 세로축 등은 과감하게 삭제하고 그래프가 상하좌우에 여백이 많이 남지않도록 크기를 조절해줍니다. 데이터 레이블을 추가하는 경우에는 값이 얼마인지 바로 확인이 가능하므로 세로축은 없어도 무방합니다. 단, 회사에서 요구하는 양식이 있는 경우에는 해당 양식을 최우선적으로 적용해주어야 합니다.
 

마지막으로 각 그래프(가운데 막대 그래프 포함)를 우클릭한 다음 [데이터 레이블 추가]를 선택합니다. 각 그래프에 해당 데이터의 수치가 표시되지만 그래프와 겹쳐있어 보기 어렵습니다. 이를 조정하기위해 레이블을 우클릭한 다음 [레이블 서식]을 선택하고, 오른쪽 화면에서 레이블위 위치를 원하시는 방향으로 조정하면 됩니다. 그리고 그 외 추가적으로 반영하고 싶거나 간략화 하고픈 부분들을 조정해주면 보기 좋은 그래프가 완성됩니다.
 

추가적으로 데이터의 변화에 대해 설명을 하고 싶다면 눈에 잘 보이게 대비되는 색상으로 박스를 추가하고, 설명을 더해줍니다. 그래프의 설명은 핵심만 아주 간단하게 기재해주는 것이 보기에 좋습니다. 실무 엑셀은 쑥떡!

쑥떡(SSOOKDDEOK)

인스타 │@ssookddeok.excel
이메일 │ssookddeok.official@gmail.com
*문의/질문은 언제나 환영합니다!