전입전출 통합 관리 엑셀 파일을 만들고 계속 개선해 가는 과정에서 데이터의 가독성을 높이고, 업무 판단에 필요한 고민 시간을 줄이며, 처리 누락과 같은 오류를 줄이기 위해 여러 가지 방법을 적용해 보았습니다.
그 과정에서 조건부서식과 검증열을 모두 사용해 보았고, 현재는 검증열 중심으로 관리하는 방식을 선호하게 되었습니다.
이번 글에서는 실제 업무를 하면서 조건부서식을 줄이고 검증열을 사용하게 된 이유에 대해 이야기해 보려고 합니다.
## 데이터의 가독성 높이기(조건부서식)
전입전출 데이터는 다양한 상황에 대응하기 위해 최대한 많은 원본 정보를 저장해야 합니다. 그리고 저장된 데이터를 바탕으로 단계별 업무 진행 상태를 확인할 수 있어야 합니다.
초기에는 데이터 양이 많지 않아 한눈에 확인할 수 있었습니다. 하지만 관리 건물이 늘어나고 데이터가 누적되면서 스크롤을 많이 해야 할 정도로 데이터 양이 증가하게 되었습니다.
이 문제를 해결하기 위해 가장 먼저 적용한 방법이 조건부서식이었습니다. 조건부서식은 특정 조건에 맞는 데이터를 자동으로 색상이나 글자 서식으로 표시해 주는 기능입니다.
제가 주로 이용했던 조건부서식은 아래와 같습니다.
- 줄 단위 데이터의 가독성을 높이기 위한 교차 색상 적용
- 업무 처리 상태에 따른 배경색 변경
- 오류 데이터에 대한 글자색 및 배경색 변경
이 방법은 데이터가 얼마 되지 않을 때 쉽게 현재 상태를 판단할 수 있어 해야 하는 업무를 결정하는데 도움이 되었습니다. 하지만 데이터가 많아질수록 조건부서식도 함께 늘어나게 되었습니다.
그 결과:
- 파일 속도가 느려지고
- 조건부서식 관리가 어려워지고
- 색상 종류가 많아지면서
- 오히려 화면이 복잡해지는
문제가 발생하였습니다.
처음에는 가독성을 높이기 위해 사용했던 방법이 오히려 데이터가 많아지자 직관성을 떨어뜨리는 경우도 있었습니다. 그래서 단순히 색상으로 표시하는 방식보다 현재 상태와 필요한 업무를 직접 표시하는 검증열 방식을 점점 더 많이 사용하게 되었습니다.
## 업무 판단에 필요한 고민 시간 줄이기(제목행 필터 설정)
전입전출 업무를 완료하기 위해서는 여러 업무를 처리해야 합니다.
- 기본 정보 입력(필수 정보 미입력 확인)
- 중간정산금액 입금 완료 여부
- XPBIZ 시스템 가수금 처리 여부
- XPBIZ 시스템 전출 및 전입 처리 여부
- 고지완료 여부
- 계산서, 현금영수증, 일반매출 처리 여부 등등
위와 같은 업무 처리 여부를 관리하기 위해 검증열 방식을 이용하면 좀더 쉽게 정리가 가능합니다. 검증열은 각 업무의 처리 상태를 저장하고 표시하기 위한 열입니다.
예를 들어 입금확인, 가수금처리, 계산서발행, 고지완료 등의 상태를 각각 표시하도록 구성하였습니다.검증열이 만들어지면 제목행 필터를 이용하여 원하는 상태의 데이터만 쉽게 확인할 수 있습니다.
예를 들어:
- 입금확인필요
- 계산서발행필요
- 정보오류
와 같은 데이터만 따로 확인할 수 있습니다.
이렇게 되면 전체 데이터를 모두 확인하지 않아도 필요한 업무만 빠르게 확인할 수 있습니다.
이제 원하는 데이터만 볼 수 있는 환경까지 구현하였으나, 여전히 불편함을 느끼게 된 점은 많은 데이터와 함께 필터 데이터도 많다는 점입니다.
하지만 데이터가 많아지면서 또 다른 문제가 발생했습니다.
필터를 여러 개 사용하기 시작하자 현재 어떤 필터가 적용되어 있는지 한눈에 확인하기 어려웠습니다.
원하는 데이터를 찾기 위해 여러 필터를 반복해서 설정해야 했고, 클릭 횟수도 점점 늘어나게 되었습니다.
그래서 필요한 데이터만 한 번에 확인할 수 있도록 VBA 버튼 기능을 도입하게 되었습니다.
다음 단계에서는 AI와 VBA를 활용하여 필요한 업무만 자동으로 표시하는 구조를 만들게 되었습니다.
## 처리 누락 줄이기(검증열 중심 관리)
엑셀을 자주 사용하는 실무자라면 여기까지 언급된 조건부서식이나 필터 기능은 인터넷 검색을 조금만 해도 충분히 적용이 가능합니다.
그러나 버튼이나 특정 위치에 클릭한 셀의 정보를 보여주는 등 방법은 VBA라는 프로그래밍 기술이 필요합니다.
VBA는 프로그래밍에 대한 경험이 없는 경우 쉽게 접근하기 어려운 기능입니다.
그래서 많은 실무자들이 필터까지만 활용하고 더 이상의 자동화는 시도하지 않는 경우도 많습니다.
다행스럽게도 최근 AI가 급격히 발전하면서 엑셀 파일을 분석하여 수식이나 VBA 코드를 제안해 주기도 하고 또 원하는 기능을 설명하면 자세하게 방법과 함께 코드를 얻을 수 있습니다.
이렇게 해서 현재 적용한 형태는 검증열 + AI + VBA 입니다.
이미 검증 수식으로 생성된 업무별 검증열을 활용하여 엑셀의 속도를 개선했으며, AI가 추천 제안한 VBA 코드를 적용하여 각 업무별로 버튼을 만들어 버튼만 클릭하면 필요한 업무 목록을 바로 확인할 수 있도록 하였습니다.
- 전체 필터 초기화 버튼 : 모든 시트 필터를 한꺼번에 초기화 합니다.
- 시트 필터 초기화 버튼 : 현재 시트 필터를 초기화 합니다.
- 사업자 번호 오류 버튼 : 사업자 확인이 필요한 전입 건들 표시 버튼
- 입금 미확인 버튼 : 전출자의 중간 정산 미입금 건들 표시 버튼
- 가수금 미처리 버튼 : 전출자 중간 정산 입금 건에 대한 가수금 미처리 표시 버튼
- 정보 오류 버튼 : 필수 항목 미 입력 건들 표시 버튼
- 고지 미완료 버튼 : 고지미완료 상태 건들 표시 버튼
- 정산 오류 버튼 : 마감된 건이나 정산 오류로 다시 확인이 필요한 건들 표시 버튼
- 계산서 미발행 버튼 : 계산서 미발행 건들 표시 버튼
- 공실 버튼 : 현재 공실 건들 표시 버튼
이 외에도 필요한 상황에 맞춰 검증열, 필터, 버튼(VBA) 를 추가하여 사용할 수 있습니다.
매번 이 정도면 충분하다 생각하지만, 실제 업무를 하다 보면 불편한 점을 또 느끼게 됩니다. 시중에 있는 시스템들을 조합 이용하여 업무를 하다 이제는 나만의 업무 스타일에 맞춰 커스텀 마이징이 가능한 엑셀을 좀 더 적극적으로 활용하게 되었습니다. 앞으로 업무 처리에 들어간 기술이나 업무 처리 노하우 등이 있다면 정리해서 글을 올리도록 하겠습니다.
감사합니다.