[Excel] 14. 엑셀 자산 관리용 가계부 만들기 3편 (설정 만들기, 이름관리자)

gbgg (54)in #kr-dev • 8 years ago

안녕하세요! @gbgg입니다. 엑셀 자산관리용 가계부만들기 3편입니다!

이제 거의 전반적인 부분의 틀이 잡힌 것 같네요!

이번 편에서는 설정 창을 따로 만들어 사용자가 항목을 변경할 수 있도록 구현하고,

엑셀 함수를 vba에서 사용하는 법도 소개하겠습니다.

강의자료

1편 강의자료 다운로드 (블로그 링크)
2편 강의자료 다운로드 (블로그 링크)
3편 강의자료 다운로드 (블로그 링크)

기본 셋팅

가장 먼저 만들 메뉴들을 몇 가지 설정해 보겠습니다.

메인화면
달력
계좌관리
지출 현황
수입 현황
설정

DATA시트까지 합쳐 총 7개의 시트로 구성되게 됩니다.

이렇게 시트 디자인을 반복적으로 꾸밀 때 '서식복사'를 사용하면 편하게 작업할 수 있습니다.

복사할 서식을 선택하고 '서식복사'를 누른 후 이 서식을 붙여넣을 셀을 선택하면 됩니다.

이번에는 '계좌관리' 시트로 들어가 항목을 한번 만들어 보겠습니다.

'계좌관리' 보다는 '입출금 내역 추가' 같은 이름이 좋을 것 같긴 한데 우선은 이렇게 진행하겠습니다.

날짜와 금액은 수기로 입력할 것이고 구분, 구분2, 계좌는 설정 시트에서 항목들을 직접 설정할 수 있도록 만들겠습니다.

실시간 날짜 입력하기

날짜 항목에 '현재시간 입력' 이라는 버튼을 추가하겠습니다.

이 버튼을 누르면 저 셀에 현재 시간이 입력되도록 vba로 작성할 것입니다. 굉장히 쉽습니다.

버튼에 위 코드만 붙여넣으면 됩니다.

Sheets("계좌관리").Range("E6").Value = Now()

=now() 혹시 기억나시나요? 엑셀 함수에도 똑같은 함수가 있습니다. 현재시간을 표시해주는 함수이며 함수로 사용할 때에는 새로고침할 때 마다 갱신됩니다.

입출금 내역을 입력할 때 버튼 클릭 할 때만 갱신되야 하기 때문에 vba를 사용하였습니다.

버튼을 누르면 다음과 같이 나오게 되는데, DATA 시트에서는 연,월,일만 사용하므로 데이터 형식을 변경하겠습니다.

다음과 같이 설정하면 됩니다.

드롭박스 설정 항목 만들기

이번에는 구분, 구분2, 계좌의 드롭박스 항목을 설정할 수 있는 '설정'창을 따로 만들겠습니다.

[Excel] 3. 능력있는 직장인이 되는 법. 물품 입고시키기!
위 강의에서 진행했던 부분이니 어려운 부분이 있으면 참조하셔도 좋을 것 같습니다.

'설정' 시트는 위와 같이 간단하게 셋팅 해 보겠습니다.

'구분' 항목의 선택 여부에 따라 두번 째 드롭박스에 들어갈 내용이 결정됩니다.
항목 내용은 간단하게 작성하겠습니다.

드롭박스를 선택한 후 '셀서식'으로 이동합니다.

입력 범위와 셀 연결이 있습니다. 다음과 같이 지정해줍니다.

입력 범위는 위와 같이 맨 아래칸을 제외한 나머지 범위를 선택해줍니다.

셀 연결은 맨 아래부분을 선택해줍니다.

* 이름관리자

INDEX함수 사용에 앞서 엑셀의 기능 중 하나인 '이름관리자' 기능을 짚고 넘어가려 합니다.

보통 엑셀 함수에서 범위지정 하려 할 때 손으로 일일히 드래그해야 합니다.

이름관리자는 자주 쓰이는 범위를 미리 '이름'으로 지정함으로써 함수 사용시 쉽게 범위를 지정할 수 있습니다.

=INDEX(범위,번호)에서 범위 부분을 마우스로 긁어 지정하였습니다.

이 부분을 이름관리자 기능을 통해 쉽게 작성해 보겠습니다.

'이름관리자'는 수식 항목에 존재합니다. 먼저 이름관리자를 클릭해 봅시다.

위와 같은 창이 나오게 됩니다. '새로만들기'를 선택해봅시다.

이름
이름을 설정합니다. 내가 만약 함수 내에서 A1~B2까지의 범위를 '범위1' 이라는 이름으로 쉽게 사용하고 싶다면 이 부분에 '범위1' 이라고 쓰면 됩니다.
참조 대상
위에 설정한 이름으로 사용할 범위를 설정합니다.

'구분'이라는 이름으로 다음과 같이 설정해 보겠습니다.

위와 같이 설정되었다면 '닫기'를 누릅니다.

이제 함수에서 범위를 따로 지정할 필요 없이 '구분' 이라는 문구를 사용하면 간편하게 사용할 수 있습니다.

다음 작업으로 넘어가 보겠습니다.

'선택구분' 부분은 IF문을 사용하여 간단하게 구현하겠습니다.

=IF($C$29=1,D12,E12) C29 셀은 절댓값을 취해주고 모두 적용시킨 뒤 드롭박스와 연결시켜보겠습니다.

입금을 누르면 구분2 항목의 입금에 대한 내용이 뜨게 됩니다.

모든 작업을 완료한 모습입니다.

날짜 값 추출

현재 데이터 시트는 다음과 같은 형식으로 되어있습니다.

달력 기능에서 일별로 데이터를 뽑아내기 위해 따로따로 저장한것인데요,

위 방식대로 데이터를 저장하려면 현재 합쳐져있는 날짜를 분할하여 따로따로 기록해야합니다.

이 작업은 vba로 진행하겠습니다. 우선 날짜 데이터 형식을 바꿔봅시다.

yyyy-mm-dd 형식으로 설정합니다.
이제 이 하나의 날짜 값 안에 들어가 있는 yyyy,mm,dd를 vba에서 분리해보겠습니다.

연도를 구하는 함수 : =YEAR(연도를 구할 셀)
월 값을 구하는 함수 : =MONTH(월 값을 구할 셀)
일 값을 구하는 함수 : =DAY(일 값을 구할 셀)

함수는 보통 이렇게 간단하게 추출할 수 있는데 이 함수들이 엑셀 vba에 '그대로' 적용되어 있습니다.

Sheets("계좌관리").Range("E8").Value = Year(E6) 이렇게 간단하게 쓰면 됩니다.

한번 적용시켜볼까요?

연도값을 추출했으니 '2018'값이 나와야 하는데 1899라는 이상한 값이 출력되었습니다.

엑셀에서 함수를 사용할 때는 E6이라는 값이 특정 셀의 value값을 지칭하지만 vba에서는 인식하지 못합니다.

그렇다면 Year(Sheets("계좌관리").Range("E6").Value) 이런 방식으로 사용해주면 되겠죠?

Sheets("계좌관리").Range("E8").Value = Year(Sheets("계좌관리").Range("E6").Value)
Sheets("계좌관리").Range("F8").Value = Month(Sheets("계좌관리").Range("E6").Value)
Sheets("계좌관리").Range("G8").Value = Day(Sheets("계좌관리").Range("E6").Value)

위와 같은 방식으로 작성하면 됩니다. 한번 확인해 보겠습니다.