안녕하세요! gbgg입니다. 엑셀 자산관리용 가계부 만들기 3편입니다!
지난 2편 포스팅에서 "데이터" 시트에 있는 자료를 조건에 맞게 달력에 표시해보았습니다.
이번 포스팅에서는 조건부 서식을 사용해 달력을 깔끔하게 완성시켜보겠습니다.
아래 자료는 아래에서 진행할 자산관리용 가계부 만들기 2~3편의 예제입니다.
조건부 서식
지난 포스팅에서 "DATA" 시트의 값을 가져와 조건에 맞게 표시해보았는데요.
두 가지 문제점이 발생하였습니다.
-
1) 입금, 출금 데이터가 없는 날짜에도 0이 표시되어 지저분하다.
-
2) 다음 월로 넘어간 부분(2월 1일)이 1월 1일로 인식되어 표시된다.
위와 같은 문제는 함수를 사용하지 않더라도 엑셀의 "조건부 서식"이라는 기능으로 손쉽게 수정 가능합니다.
우선 "조건부 서식"에 대해 간단하게 알아보겠습니다.
엑셀의 각 셀들은 "셀 서식"을 통해 표시형식, 테두리, 채우기 등을 지정할 수 있습니다.
내가 강조하고 싶은 셀은 빨간색으로 표시하거나 테두리를 변경하는 등 마음대로 지정 가능합니다.
그렇다면 이러한 셀 서식이 조건에 맞게 자동으로 변경되고 표시되게 하려면 어떻게 해야할까요?
바로 조건부 서식을 사용하면 됩니다.
조건부 서식은 해당 조건에 맞게 셀 서식을 변경해주는 기능입니다.
위와 같이 여러 조건을 지정해줄 수 있는데 간단하게 한 가지만 예제로 설명드리겠습니다.
위와 같은 데이터에서 금액이 10,000원 보다 큰 셀의 배경색이 빨간색으로 표시되도록 지정해보겠습니다.
금액 부분에서 숫자 데이터가 있는 부분을 선택한 뒤 조건부 서식의 "보다 큼"을 선택합니다.
조건 식에 10,000을 입력하고 적용할 서식은 기본 서식인 빨강 채우기를 선택합니다.
위와 같이 10,000원이 넘어가는 셀의 배경색이 빨간색으로 변경되었습니다.
이렇게 셀에 조건부 서식을 지정해두면 값이 변경되었을 때에도 자동으로 지정해둔 조건부 서식이 적용되며
한 셀에 다수의 조건부 서식을 지정할 수도 있어 여러 상황에 맞게 서식이 변경되도록 설정할 수 있습니다.
그럼 이제 조건부 서식을 사용하여 달력의 문제점을 해결해볼까요?
1번 문제점부터 해결해 보겠습니다.
1) 입금, 출금 데이터가 없는 날짜에도 0이 표시되어 지저분하다.
위 그림과 같이 범위를 설정해 준 뒤 '다음을 포함하는 셀만 서식 지정'을 선택하여
셀 값이 0인 것만 연한 회색으로 글씨 색이 변경되도록 규칙을 변경해 보겠습니다.
위와 같이 조건부 서식을 사용하여 입/출금 데이터가 없는 곳은 회색 글씨로 표시되도록 수정하였습니다.
2) 다음 월로 넘어간 부분(2월 1일)이 1월 1일로 인식되어 표시된다.
2번 문제는 IF문을 사용하여 해결해 보겠습니다.
조건식을 한글로 표현하자면 아래와 같이 표현되겠죠?
IF(해당 일의 월 값 = 선택한 월 값, SUMIFS 수행, 0)
| =IF(MONTH(C6)=D2, SUMIFS수행, 0) |
C6 : 해당 일의 월 값
D2 : 현재 선택되어있는 월 값
MONTH 함수를 사용하여 월 값을 비교한 뒤 일치할 경우에만 SUMIFS 함수를 통해 계산 값을 출력하도록 해보겠습니다.
자 그럼 한번 이전 포스팅에서 작성한 SUMIFS문을 수정해볼까요?
| =IF(MONTH(C6)=$D$2, SUMIFS(DATA!$G$2:$G$1000, DATA!$E$2:$E$1000, "입금" ,DATA!$B$2:$B$1000, $B$2,DATA!$C$2:$C$1000, $D$2,DATA!$D$2:$D$1000, TEXT(C6,"d")), 0) |
위와 같은 방법으로 출금 부분도 수정한 뒤 모두 적용시키겠습니다.
자 위와 같이 선택한 월의 값만 표시되고, 수입과 지출이 없는 부분은 회색으로 표시하였으며
현재 선택한 월의 데이터가 달력에 표시되도록 수정하였습니다.
데이터 시트에 입/출금 값을 한번 넣어보겠습니다.
이제 결과를 확인해 보겠습니다.
디자인
이제 기본적인 달력이 완성되었으니 다른 메뉴를 만들기 전에 디자인을 조금 변경해보겠습니다.
달력 주변 셀을 깔끔하게 회색으로 처리해주고 메뉴는 진한 회색과 선택한 메뉴만 파란색으로 설정해주었습니다.
이제 조금 더 눈에 잘 들어오도록 '보기' 메뉴에서 몇 가지를 변경해보겠습니다.
눈에 보기 편하도록 수식 표시줄과 눈금선, 머리글의 체크박스를 해제해봅시다.
수식 표시줄, 눈금선, 머리글의 체크를 해제한 상태입니다. 이렇게 해 두면 사용할 때 더 깔끔하고 편하겠죠?
디자인을 변경하면서 행과 열이 추가되면서 셀의 번호가 변경되었으니 '코드 보기'에 들어가
변경된 부분을 수정해줍시다.
결과
위와 같이 데이터 시트에 있는 입/출금 값이 달력에 표시되는 것을 확인할 수 있습니다.
엑셀 자동화
엑셀의 함수와 vba, 그리고 조건부 서식을 사용하여 기본적인 가계부의 틀이 완성되었습니다.
가계부 앱이나 프로그램을 사용해도 되지만 이처럼 엑셀로 내가 원하는 형태의 가계부를 만들어낼 수 있습니다.
다음 포스팅에서는 은행 정보, 계좌정보, 입/출금 정보 등의 설정 메뉴를 만들고
한 달의 입/ 출금 내용을 한눈에 정리하여 볼 수 있는 상세내역도 만들어 보고자 합니다.
궁금한 점이나 추가 요청사항은 댓글로 남겨주시면 답변 달아드리겠습니다!
능력 있는 직장인이 되기 위해 엑셀로 전산프로그램을 만들 때까지 한번 열심히 달려봅시다!
다음 포스팅에서 뵙겠습니다!
최근댓글