엑셀 가계부 만들기 3탄: 이중(혼합형) 그래프로 수입/지출내역 한 번에

올해 초, 새해를 맞아 재테크를 다짐하신 분들 많을 것 같습니다. 일년의 4분의 1이 지난 지금 그 목표는 잘 이뤄지고 있나요? 처음 목표와는 달리 조금은 지지부진을 면하지 못하고 있는 것은 아닌지 모르겠어요. 그럴 땐 거창한 목표를 세우기 보다는 하루하루의 수입과 지출을 꼼꼼하게 관리해주는 것이 더 좋겠죠? 지난 번에는 데이터 유효성 검사 및 SUMIF 함수를 통해 가계부의 항목을 정하고 항목별 합계를 구하는 방법을 소개해드렸는데요, 오늘은 이중 그래프를 통해 일별 수입 및 지출 추이를 살피는 법을 소개해드릴게요.

▶ 엑셀 가계부 만들기 기본편: 데이터 유효성 검사 이용하기

▶ 엑셀 가계부 만들기 응용편: SUMIF 함수로 항목별 합계 구하기

* 본문의 모든 이미지는 클릭하면 크게 보실 수 있습니다.

* 오피스 2010 버전을 기준으로 작성되었습니다.

SUMIF 함수 복습, 일별 사용 합계 구하기

지난 번에 SUMIF 함수를 사용하여 항목별 지출/수입 금액의 합을 구하는 법을 알려드렸었는데요, 이번에는 SUMIF 함수로 일별 사용 합계를 구한 뒤, 그래프로 일별 수입/지출 추이를 살펴보고자 해요. 복습하는 의미로 한 번 더 소개해드릴게요.

먼저, ‘일별 내역’이라는 새로운 탭을 만들어 날짜 별로 지출과 수입을 기록할 수 있는 표를 만들었어요.

그 다음 SUMIF 함수를 사용해 일별 합계를 구해주려고 합니다. SUMIF 함수는 특정 범위에서 조건에 맞는 셀의 합계를 구하는 것으로 ‘=SUMIF(range, criteria, [sum_range])’ 순으로 나타내는데요, 다시 말해 ‘=SUMIF(항목 범위, 조건, 합계를 구할 금액 범위)’라고 할 수 있습니다.

여기서는 일자별 지출의 합을 구할 것이므로 ‘range’, 즉 항목의 범위가 되는 부분은 ‘가계부’ 탭의 ‘날짜’ 부분이 되며, criteria는 ‘일별 내역’ 탭의 날짜, [sum_range]는 ‘가계부’ 탭의 금액 부분이 됩니다.

그럼 다시 ‘일별 내역’ 탭으로 돌아와, 2월 1일의 지출의 합을 구해볼까요? range는 ‘가계부’ 탭의 ‘B4~B22’로, criteria는 ‘일별 내역’ 탭의 ‘2월 1일’이 있는 ‘B3’으로, [sum_range]는 ‘가계부’ 탭의 ‘G4~G22’로 설정하여 SUMIF 함수를 적용해주었습니다. 아래와 같이 말이에요.

=SUMIF(가계부!$B$4:$B$22,'일별 내역'!B3,가계부!$G$4:$G$22)

위와 같이 함수를 적용하였다면, 수식을 복사하여 ‘지출’의 나머지 칸들도 채워주세요. 채우기를 하면 복사한 셀들의 범위가 바뀔 수 있는데요, 이 때 셀 주소에 ‘$’를 붙여주면 범위를 고정시킬 수 있습니다.

셀 범위를 고정시키는 절대참조값 ‘$’ 엑셀 함수에서 ‘$’는 셀 범위를 고정시킬 경우에 사용되는데요, 수식을 복사하더라도 셀의 범위는 그대로 유지하고자 할 경우, ‘$A$1’과 같이 표시할 수 있습니다. 이를 절대참조라고 합니다. 열을 고정시키면서 행을 고정시키거나, 행을 고정시키면서 열을 고정할 때에는 혼합참조를 써서 ‘$A1’ 혹은 ‘A$1’과 같이 나타낼 수도 있습니다.

수입 역시 마찬가지로 채우되, [sum_range] 부분을 수입 금액이 있는 ‘I4~I22’로 바꿔주면 되겠죠?

수식을 복사한 후, 나머지 칸들도 채워주세요.

일별 그래프 만들기

일별 지출/수입 합계를 구했다면, 이번에는 그래프를 만들어볼게요. 앞서 만든 표를 블록설정한 후, ‘삽입>세로막대형>2차원 세로 막대형’ 그래프를 선택하여 기본 그래프를 만들어줍니다.

이 중 지출은 하루하루의 금액을 비교하기 쉽게 막대그래프를 사용하고, 수입은 그날의 변화를 보기 쉽도록 꺾은선그래프로 표시하려 합니다. 이럴 때 이중 그래프를 사용하면 한 눈에 보기 편해요. 이중 그래프를 만들 때에는 하나는 주축으로, 하나는 보조축으로 설정하는데, 여기서는 지출을 주축으로, 수입을 보조축으로 정해봅니다.

보조축이 될 ‘수입’이 있는 막대에 마우스 우클릭 후, ‘데이터 계열 서식’을 클릭해주세요.

계열 옵션의 ‘데이터 계열 지정’에서 ‘보조 축’을 선택해주세요.

보조축을 설정하고 나니, 지출과 수입이 막대그래프 하나로 합쳐졌네요. 수입 부분을 꺾은선그래프로 나타내기 위해 수입 부분이 있는 빨간 그래프를 클릭해 '파일>차트 종류 변경'을 클릭합니다.

‘꺾은선형’ 탭에서 변경할 그래프 모양을 선택한 후 확인.

위와 같이 수입 항목이 꺾은선형으로 변경된 것을 확인할 수 있습니다. 차트 제목을 입력하거나, 데이터 레이블을 추가하여 조금만 더 꾸며주면 더 보기 좋은 그래프가 완성되겠죠?

위와 같이 원그래프와 막대그래프를 결합할 수도 있습니다.

하지만 이중그래프의 경우, 결합할 수 있는 형태가 제한적이었습니다. 2차원과 3차원은 결합되지 않으며, 막대그래프와 꺾은선 그래프, 막대그래프와 원그래프, 혹은 분산형 그래프 정도만 표현할 수 있으니 참고해주세요. 하지만 더 방대한 자료의 경우 이중그래프로 표현하면 더욱 더 한 눈에 보기 쉽겠죠?

새해에는 재테크를 결심했지만 그 다짐이 흐지부지 되고 있다면, 지금부터라도 가계부를 꼬박꼬박 쓰는 습관을 들여보는 것은 어떨까요? 한 달간의 결과를 그래프로 정리해서 본다면, 조금 더 계획적인 경제 계획을 세울 수 있을 테니까요. ^^