1. 엑셀 이름 관리자 및 동적영역을 활용한 가계부 소개
지난 포스팅에서는 엑셀에서 피벗테이블을 활용한 가계부 TOOL을 소개드렸는데요. 지난 포스팅에 이어서 만들어진 가계부 시트에서 값만 추가하면 자동으로 피벗에 값이 반영되고 그래프까지 자동으로 갱신되는 방법을 알려드리겠습니다.
지난 포스팅에서 이어지는 부분이 있으므로 꼭 하기 링크의 지난 포스팅을 참조부탁드립니다.
*지난 포스팅 참조 링크 : 엑셀 피벗테이블을 활용한 가계부 쓰기 자동화(그래프 자동화) (tistory.com)
이번 포스팅에서 알아야될 것의 핵심은 INDEX(COUNTA)함수를 이용한 특정 범위의 자동추가(동적영역), 이 함수를 이름 관리자에 할당하는 것으로 피벗을 자동화할 수 있다입니다.;
2. 피벗테이블의 자동화 가계부의 작동원리
우선 지난 포스팅에서는 피벗 테이블을 원Data로부터 만들고 해당 피벗 테이블을 통해 연동된 그래프를 만들어서 자동으로 피벗을 바꿔주면 그래프가 변하는 가시화 TOOL을 만들었습니다. 다만 지난 포스팅의 자료에서는 원Data인 가계부 시트에 값이 추가되거나 삭제되면 그래프가 변하지는 않았는데요. 이번 포스팅에서는 피벗의 원Data의 참조영역을 자동화된 이름으로 바꿔줌으로 가계부 시트에 값이 추가,삭제되면 자동으로 그래프, 피벗에 반영되도록 만들어보겠습니다.
● 동적영역 및 이름 작성방법
(1) 먼저 하기와 같이 특정 영역을 함수로 표현할때 맨 좌측 위의 셀과 우측 하단의 셀의 좌표가 ":"기호로 연결된 구조를 가지게됩니다. 여기서 우측 하단부의 셀의 좌표를 Data가 있는 개수만큼 자동으로 움직이게 만들어줍니다. 그 때 사용되는 함수가 Index(Counta)함수입니다.
(2) INDEX(참조영역, 행 숫자, 열숫자)가 됩니다만, 참조영역 =자동화하고자 하는 영역의 맨 우측열로 지정합니다.
(하기 예시에서는 G열 즉 결재방법이 있는 열입니다.) 그리고 행 숫자부분에 COUNTA(참조영역)을 넣어줍니다.
COUNTA함수는 특정영역에 있는 DATA의 개수를 세려주는 함수입니다. 이를 통해서 하기와 같이 B3:우측 하단 셀이 자동으로 DATA가 있는 개수만큼 자동으로 이동하는 함수가 만들어집니다.
(3) 상기 함수를 다른 시트에서 걸어줍니다. 이유는 상기 함수를 추후 이름관리자에 특정 이름으로 등록이 필요한데, 함수에서 이 가계부시트의 셀이다라는 것을 지정해주기 위해서는 다른 시트에서 함수를 동일하게 만들면 하기와 같이 시트이름!셀주소가 출력됩니다. 이렇게 해주는 것으로 가계부시트의 특정영역이다라는 것이 완벽하게 식별되게 됩니다. 그리고
F4으로 함수내 모든 셀을 절대참조로 바꿔주세요.
(4) 상기에서 작성한 함수를 복사한 후 Ctrl+F3번을 눌러 이름 관리자 창을 띄웁니다. 그리고 새로 만들기 버튼을 눌러 원하시는 이름으로 작성된 함수를 붙여넣습니다. 성공적으로 원하는 이름으로 함수를 참조대상에 지정하였다면,
하기 이미지 처럼 해당이름을 함수창에 "=해당이름"으로 기입하면 자동으로 작성된 함수에 맞는 동적영역이 형성된 것을 확인할 수 있습니다.
(5) 마지막으로 지난 포스팅에서 만들었던 것과 같이 가계부 시트의 Rawdata로 만든 피벗을 클릭한 상태에서 피벗 테이블 분석 탭의 데이터 원본 변경을 클릭하여 상기에서 만든 이름으로 표/범위 영역을 바꿔줍니다. 그러면 모든 자동화가 완료되었습니다.
(6) 이제 피벗을 우클릭하여 새로고침을 누르거나 최상단의 이미지 처럼 피벗과 연결된 슬라이서 혹은 시간 표시 막대에서 우클릭하여 새로고침을 눌러주면 가계부 시트에 추가,삭제한 값이 자동으로 그래프 및 피벗에 반영되는 것을 확인할 수 있습니다.
3. 엑셀 가계부 자동화 TOOL공유;
완성된 엑셀 자동화 TOOL을 공유합니다. 원 Data에 항목이 추가되어도 가계부 현황까지 자동으로 새로고침만 누르면 갱신되도록 완전 자동화하였습니다. 감사합니다.
