관리비나 이사 정산 입금 내역을 엑셀로 관리하다 보면 모든 데이터를 확인하는 것보다 아직 입금되지 않은 건만 빠르게 찾는 것이 더 중요할 때가 많습니다.
앞선 글에서는 정산 금액과 실제 입금액을 비교하여 입금여부를 O와 X로 구분하는 데이터 구조를 만들었습니다.
이렇게 입금여부를 별도의 검증열로 만들어두면 복잡한 기능을 사용하지 않아도 엑셀의 기본 필터만으로 미입금 건을 쉽게 확인할 수 있습니다.
이번 글에서는 먼저 엑셀에서 필터를 설정하는 방법부터 입금여부가 X인 데이터만 확인하는 방법까지 정리해 보겠습니다.
입금여부를 만들어두면 미입금 건을 찾기 쉬워집니다
앞선 글에서 사용한 데이터에는 다음과 같은 항목이 있습니다.
건물명 / 호 / 전입전출구분 / 기준날짜 / 이사정산금액 / 입금액 / 차액 / 입금날짜 / 입금자명 / 입금여부 / 비고
여기서 입금여부는 실제 입금액과 정산금액을 비교하여 엑셀이 자동으로 판단하도록 만들었습니다.
- 입금액 없음 → X
- 정산 금액보다 부족하게 입금 → X
- 정산 금액 전액 입금 → O
- 정산 금액보다 많이 입금 → O
따라서 미입금 건을 확인할 때 정산 금액과 입금액을 하나씩 비교할 필요 없이 입금여부가 X인 데이터만 찾으면 됩니다.
먼저 엑셀에서 필터 기능을 설정합니다
제목행에 이미 ▼ 모양의 필터 버튼이 표시되어 있다면 이 과정은 건너뛰어도 됩니다.
필터가 설정되어 있지 않다면 데이터 범위 안의 아무 셀이나 하나 선택한 뒤 엑셀 상단 메뉴에서 데이터 → 필터를 클릭합니다.
그러면 각 제목 셀에 ▼ 모양의 필터 버튼이 나타납니다.
단축키를 사용한다면 Ctrl + Shift + L을 눌러 필터 기능을 켜거나 끌 수도 있습니다.
필터를 이용하면 원본 데이터를 삭제하거나 다른 곳으로 옮기지 않고도 원하는 조건에 해당하는 행만 화면에 표시할 수 있습니다.
입금여부에서 X만 필터링합니다
필터가 설정되었다면 입금여부 열의 ▼ 버튼을 클릭합니다.
표시되는 항목에서 전체 선택을 해제한 뒤 X만 선택하고 확인을 누릅니다.
입금여부 ▼ → 전체 선택 해제 → X 선택 → 확인
그러면 입금여부가 X인 행만 화면에 표시됩니다.
이렇게 하면 데이터가 많아도 아직 입금되지 않았거나 일부 금액만 입금된 건을 한꺼번에 확인할 수 있습니다.
미입금과 부분입금이 함께 표시되는 이유
입금여부를 단순히 입금액이 있는가로 판단하지 않고 받아야 할 금액을 모두 받았는가를 기준으로 만들었기 때문입니다.
예를 들어 정산금액이 420,000원인데 300,000원만 입금됐다면 입금내역 자체는 존재합니다.
하지만 아직 120,000원을 더 받아야 하므로 입금여부는 X입니다.
따라서 X만 필터링하면 다음 두 가지를 함께 확인할 수 있습니다.
- 아직 한 번도 입금되지 않은 건
- 일부 금액만 입금된 건
반대로 정산금액을 모두 받았거나 정산금액보다 많은 금액을 받은 건은 O이므로 미입금 확인 화면에서는 제외됩니다.
별도의 미입금 목록을 만들지 않아도 됩니다
처음에는 미입금 건을 별도의 시트나 메모로 관리하는 방법을 생각할 수도 있습니다.
하지만 이렇게 관리하면 원본 데이터와 미입금 목록을 각각 수정해야 하는 문제가 생깁니다.
예를 들어 미입금이었던 세대에서 입금이 확인되면 원본 데이터에 입금내역을 입력하고, 별도로 작성해 둔 미입금 목록에서도 해당 건을 지우거나 상태를 변경해야 합니다.
두 곳을 함께 관리하다 보면 한쪽만 수정하는 일이 생길 수도 있습니다.
저는 그래서 원본 데이터는 한 곳에서 관리하고 필요한 정보만 필터로 조회하는 방식을 사용했습니다.
입금이 확인되면 원본 데이터에 입금액과 입금정보를 입력합니다.
그러면 입금여부가 X에서 O로 변경되고, 다음에 X만 필터링했을 때 해당 건은 자연스럽게 미입금 목록에서 제외됩니다.
원본 데이터 입력 → 입금여부 자동 검증 → 필요한 상태만 필터 조회
이렇게 하면 별도의 미입금 목록을 계속 수정할 필요가 없습니다.
필터를 사용한 뒤에는 해제 여부도 확인해야 합니다
필터는 간단하고 편리하지만 실제 업무에서 한 가지 주의할 점이 있었습니다.
필터가 적용된 상태에서는 조건에 맞지 않는 행이 화면에서 보이지 않습니다.
따라서 미입금 확인을 끝낸 뒤 다른 업무를 계속할 때 필터가 적용되어 있다는 사실을 잊으면 일부 데이터가 없는 것처럼 보일 수 있습니다.
필터를 해제하려면 입금여부의 필터 버튼을 다시 열어 전체 항목을 선택하면 됩니다.
필터 기능 자체를 끄고 싶다면 상단 메뉴에서 데이터 → 필터를 다시 클릭하거나 Ctrl + Shift + L을 사용할 수도 있습니다.
특히 건물명, 입금여부, 계산서 발행여부 등 여러 열에 필터를 함께 사용하기 시작하면 어떤 조건이 적용되어 있는지 확인하는 것도 점점 번거로워집니다.
그래서 저는 필요한 데이터를 확인한 뒤에는 적용했던 필터가 남아 있지 않은지 확인하는 습관을 들이는 것이 좋다고 생각합니다.
데이터가 많아지면서 필터를 매번 설정하는 것도 번거로웠습니다
입금여부 하나만 관리한다면 기본 필터만으로도 충분합니다.
하지만 실제 업무에서는 확인해야 할 항목이 점점 늘어났습니다.
미입금뿐 아니라 사업자번호 오류, 고지 미완료, 계산서 미발행, 정보 오류 등 여러 상태를 확인해야 했습니다.
그때마다 해당 열을 찾아 필터를 열고 원하는 값을 선택한 뒤 다시 필터를 해제하는 작업을 반복해야 했습니다.
한두 번이라면 별 문제가 없지만 매일 반복하다 보니 필터를 설정하는 과정 자체를 줄이고 싶었습니다.
그래서 다음 단계에서는 자주 사용하는 필터 조건을 VBA 버튼에 연결했습니다.
예를 들어 입금미확인 버튼을 누르면 입금여부가 X인 데이터만 바로 표시되도록 하는 방식입니다.
데이터 저장 → 자동 검증 → 필터 조회 → VBA 버튼 조회
처음부터 복잡한 자동화를 만드는 것이 아니라, 먼저 기본 기능으로 필요한 데이터를 조회해보고 반복되는 작업이 생겼을 때 그 부분을 자동화하는 방식입니다.
다음 글에서는 미입금 건을 VBA 버튼으로 조회해 보겠습니다
이번 글에서 사용한 방법은 특별한 VBA나 복잡한 함수가 필요하지 않습니다.
데이터에 입금여부와 같은 검증열을 하나 만들어두고 엑셀의 기본 필터를 이용하는 것만으로도 필요한 데이터를 훨씬 빠르게 확인할 수 있습니다.
다만 같은 필터를 반복해서 사용한다면 매번 필터를 열어 조건을 선택하는 과정도 줄일 수 있습니다.
다음 글에서는 제가 실제 파일에서 사용하고 있는 방식처럼 입금미확인 버튼을 눌러 입금여부가 X인 데이터만 바로 표시하는 VBA 필터 기능을 만들어보겠습니다.
← 이전 글 | 엑셀에서 미입금 건을 쉽게 관리하는 데이터 구조 만들기
다음 글 → | 엑셀에서 미입금 건을 VBA 버튼으로 한 번에 조회하는 방법
감사합니다.