Excel · Power Query · VBA · 피벗 대시보드 · PNG 자동 생성
고객의 보험 보장분석 파일을 한 번 교체하면 가입 보험 요약, 주요 보장 현황, 핵심보장 비교표를 자동으로 다시 만들고 상담 관리와 이미지 저장까지 이어지도록 구축한 프로젝트입니다.
고객마다 수십 개 보험을 다시 정리하고 계신가요?
보험 상담에서는 고객이 가입한 보험을 확인하는 것보다, 그 내용을 상담에 사용할 수 있는 형태로 다시 정리하는 데 더 많은 시간이 들기도 합니다.
원본 보장분석 파일에는 보험명, 보험사, 계약일, 계약 상태, 갱신 여부, 피보험자, 납입 기간, 보장 만기, 월 보험료와 같은 정보가 가로로 길게 펼쳐집니다. 고객이 가입한 보험 수가 달라질 때마다 표의 폭도 달라지고, 주요 보장 정보는 다시 카테고리별로 분류해야 합니다.
그다음에는 상담용 표를 만들고, 핵심 보장금액을 따로 옮기고, 고객에게 보낼 이미지를 다시 저장해야 합니다.
각 작업은 어렵지 않습니다.
하지만 고객 한 명을 상담할 때마다 같은 정리 과정을 처음부터 반복해야 한다는 것이 문제였습니다.
자동화 전에는
담당자는 보장분석 한 건을 처리할 때 아래 업무를 반복해야 했습니다.
•
원본 파일에서 비어 있는 행과 열을 정리
•
가입 보험별 계약 정보와 보험료를 다시 배치
•
실비, 진단, 수술, 입원, 사망 등 보장 항목을 따로 분류
•
보험별 보장금액과 전체 보장총액을 다시 확인
•
납입 중인 보험과 납입이 끝난 보험을 구분
•
현재 납입 중인 월 보험료를 다시 계산
•
상담용 리포트의 제목, 표 너비, 글자 크기와 색상을 수동 보정
•
고객에게 보낼 표를 이미지로 나누어 저장
•
상담 일정과 진행 상태를 별도 표에서 다시 관리
가입 보험이 많아질수록 가로 열이 계속 늘어나기 때문에 표를 복사하는 범위도 매번 달라집니다.
원본의 중간에 빈 행이나 반복된 헤더가 있으면 보장 항목이 끊기고, 숫자 안에 쉼표나 -가 섞여 있으면 합계 계산에도 영향을 줄 수 있었습니다.
우리가 해결하고 싶었던 문제
이번 프로젝트의 목표는 단순히 엑셀 표를 예쁘게 만드는 것이 아니었습니다.
원본 보장분석 파일을 상담에 사용할 수 있는 자료로 바꾸는 전 과정을 한 번의 새로고침으로 연결하는 것이 목표였습니다.
담당자는 수식이 어느 열까지 들어가야 하는지, 어떤 행을 복사해야 하는지, 저장할 이미지 범위가 어디까지인지 기억하지 않아도 되어야 했습니다.
즉,
사람이 보험 개수에 맞춰 표를 다시 만드는 것이 아니라, 시스템이 고객의 보험 개수를 읽고 결과 화면을 스스로 구성하도록 설계했습니다.
자동화 구조
고객 보장분석.xlsx
↓
Power Query
빈 행·빈 열 제거 + 원본 행 번호 생성
↓
상단 계약 요약 / 주요 카테고리 보장정보 분리
↓
Excel 수식
보험별 계약정보와 보장금액을 동적으로 재배치
↓
VBA 새로고침
상담용 리포트 + 핵심보장 비교표 생성
↓
PNG 2종 자동 저장
보장분석 리포트 / 주요 카테고리별 보장정보
Plain Text
복사
같은 통합문서 안에는 상담 업무를 이어서 관리할 수 있도록 다음 기능도 함께 구성했습니다.
•
고객 DB 진행 현황
•
상담 단계별 통계
•
계약 건수와 금액 대시보드
•
월별 상담 스케줄 달력
•
상담 질문지
•
상황별 안내 멘트 모음
Power Query로 원본 파일부터 정리했습니다
원본 보장분석 파일은 지정된 위치에서 불러오도록 연결했습니다.
먼저 전체 시트를 읽은 뒤 값이 하나도 없는 행과 열을 제거합니다. 이후 원본 순서를 잃지 않도록 행 번호를 추가했습니다.
원본에는 가입 보험 요약과 주요 카테고리 보장정보가 한 시트에 함께 들어 있었습니다. 그래서 주요 카테고리 별 보장 정보라는 구분 행을 기준으로 데이터를 두 갈래로 나눴습니다.
가입 보험 요약
구분 행보다 위쪽 데이터만 남기고, 상담용 표에 필요하지 않은 열과 작업용 행 번호를 제거했습니다.
주요 카테고리 보장정보
구분 행보다 아래쪽 데이터만 남긴 뒤 첫 행을 헤더로 올렸습니다. 중간에 반복된 헤더는 제거하고, 비어 있는 대분류는 위 값을 이어받도록 처리했습니다.
보장금액 영역은 쉼표를 제거하고 숫자로 변환했습니다. 빈칸이나 -는 값이 없는 것으로 처리해 합계와 비교에 사용할 수 있도록 만들었습니다.
이 구조 덕분에 원본에 빈 행이 생기거나 보험 열이 늘어나도 정리 규칙을 다시 손볼 필요가 줄었습니다.
보험 개수가 달라도 표가 자동으로 늘어납니다
고객마다 가입한 보험 수는 다릅니다.
고정된 열만 복사하면 보험이 적은 고객에게는 빈칸이 길게 남고, 보험이 많은 고객에게는 일부 보험이 잘릴 수 있습니다.
그래서 상담용 작업 시트에서는 INDEX, MATCH, AGGREGATE 수식을 조합해 각 항목에서 값이 들어 있는 보험 열만 순서대로 가져오도록 구성했습니다.
•
보험명
•
보험사명
•
계약일
•
계약 상태
•
갱신 여부
•
피보험자
•
증권번호
•
납입 여부
•
납입주기와 납입기간
•
보장 만기일과 만기연령
•
납입 종료일과 종료연령
•
월납보험료
•
기납보험료
보험이 5개면 5개까지만, 30개면 30개까지 같은 규칙으로 표가 확장됩니다.
주요 보장정보 역시 소분류명 + 보험명을 기준으로 금액을 찾아오기 때문에 어떤 보험에서 어떤 보장이 구성되어 있는지 한 화면에서 확인할 수 있습니다.
납입 중인 보험료만 다시 계산했습니다
원본에 월납보험료가 있다고 해서 모두 현재 지출되는 보험료는 아닙니다.
납입이 끝난 계약까지 단순 합산하면 고객이 실제로 매달 부담하는 금액과 달라질 수 있습니다.
그래서 VBA는 보험별 납입 상태를 확인하고, 납입종료가 아닌 계약의 월납보험료만 합산합니다.
쉼표, 원, 만원, 공백이 섞여 있어도 숫자로 정리한 뒤 합계에 반영하도록 처리했습니다.
결과 리포트에는 현재 납입 기준의 총 보험료가 별도 강조 영역으로 표시됩니다.
새로고침 버튼 하나로 상담용 리포트를 완성합니다
새로고침 버튼을 누르면 VBA가 아래 작업을 순서대로 실행합니다.
1.
Power Query와 연결 데이터를 모두 새로고침
2.
비동기 쿼리 계산이 끝날 때까지 대기
3.
실제 데이터가 들어 있는 마지막 보험 열과 보장 행을 확인
4.
고객명과 리포트 제목 영역 자동 병합
5.
원본 작업 시트의 서식과 열 너비를 상담용 시트에 복사
6.
결과값만 옮겨 수식이 노출되지 않는 출력용 시트 생성
7.
글꼴, 색상, 테두리, 행 높이와 숫자 형식 통일
8.
납입 중인 월 보험료 합계 계산
9.
내부 쿼리 시트 숨김 처리
10.
핵심보장 비교표 갱신
출력 시트는 값과 서식만 남기기 때문에 상담 중 실수로 수식이 바뀌는 위험을 줄였습니다.
보험 개수와 보장 항목 수에 따라 마지막 열과 마지막 행을 다시 찾기 때문에 고객이 바뀌어도 같은 버튼을 사용할 수 있습니다.
핵심보장은 상담 순서에 맞춰 따로 정리했습니다
전체 보장정보는 정확하지만 항목이 많아 상담 중 바로 설명하기에는 복잡할 수 있습니다.
그래서 별도의 핵심보장 시트에서는 상담 우선순위에 따라 필요한 항목만 다시 모았습니다.
•
실비
•
진단
•
수술
•
입원
•
사망
•
후유장해
•
골절·화상
•
생활배상
•
운전자 보장
각 항목의 현재 보장총액은 카테고리별 집계표에서 자동으로 찾아옵니다.
담당자가 비교 금액을 입력하면 현재 보장총액이 더 작은 경우와 더 큰 경우를 서로 다른 글자색으로 표시하도록 조건부 서식도 연결했습니다.
단순히 금액을 보여주는 표가 아니라, 어떤 보장을 먼저 설명하고 어디를 비교해야 하는지 상담 흐름까지 반영한 화면입니다.
고객에게 보낼 이미지를 두 장으로 자동 저장합니다
상담용 리포트는 가로와 세로가 길기 때문에 한 장의 이미지로 저장하면 글자가 너무 작아질 수 있습니다.
그래서 VBA는 결과 화면을 두 구간으로 나눠 PNG로 저장합니다.
•
보장분석 리포트: 보험별 계약 정보와 보험료
•
주요 카테고리별 보장정보: 보험별 핵심 보장금액
저장할 폴더만 선택하면 고객명과 작업 날짜를 기준으로 파일명이 자동 생성됩니다.
고객명_작업일_01_보장분석리포트.png
고객명_작업일_02_주요카테고리별보장정보.png
Plain Text
복사
파일명에 사용할 수 없는 문자는 자동으로 바꾸고, 실제 데이터가 있는 마지막 열과 행까지만 이미지 범위로 잡습니다.
필요한 경우 특정 표 안의 셀 하나만 선택해도 주변 표 전체를 찾아 별도 PNG로 저장할 수 있도록 보조 기능도 만들었습니다.
상담 DB와 일정 관리까지 같은 파일에 연결했습니다
보장분석 자료를 만든 뒤 상담 진행 상황을 또 다른 파일에 기록하면 고객 정보와 일정이 쉽게 분리됩니다.
그래서 같은 통합문서 안에 고객 DB를 두고 아래 내용을 관리하도록 구성했습니다.
•
담당자와 고객 기본정보
•
카카오톡 응답과 TA 결과
•
1차부터 5차까지의 상담 단계
•
상담일 1~5
•
계약 건수와 금액
•
참고사항과 최종 상담 상태
최종 상담 상태는 카카오톡부터 5차 상담까지 입력된 값 중 가장 마지막 상태를 자동으로 가져옵니다. 고객 나이도 원본 문자열에서 보험나이를 찾아 숫자로 변환하도록 만들었습니다.
피벗테이블과 슬라이서를 이용해 계약 건수, 계약 금액, 평균 고객 나이, 성별, 응답 상태, 상담 대비 계약 비율을 대시보드에서 확인할 수 있습니다.
상담 일정은 월간 달력으로 자동 변환됩니다
고객 DB에는 한 고객당 최대 5개의 상담일이 기록됩니다.
달력 생성 버튼을 누르면 VBA가 모든 상담일을 확인하고 월별 달력을 새로 만듭니다.
날짜 칸에는 다음 정보가 함께 표시됩니다.
•
상담 시간
•
고객명
•
몇 번째 상담인지
•
해당 상담 단계의 상태
같은 날짜에 일정이 여러 개면 시간순으로 정렬합니다. 오늘 날짜는 별도 색상과 굵은 테두리로 표시하고, 상담이 있는 날짜도 배경색으로 강조했습니다.
담당자는 DB에서 날짜만 관리하면 되고, 월간 일정표를 따로 다시 작성할 필요가 없습니다.
질문과 안내 멘트도 운영 흐름 안에 넣었습니다
보험 상담에서는 분석표만큼 어떤 질문을 어떤 순서로 하는지도 중요합니다.
질문지 시트에는 가입 경로, 적정 보험료, 기존 점검 경험, 보완하고 싶은 부분, 주요 보장에 대한 이해도를 확인할 수 있는 질문을 단계별로 정리했습니다.
멘트정리 시트에는 사전 안내, 부재 안내, 상담 후 정리, 가입심사 결과, 모바일 가입, 기존 보험 해지 안내 등 상황별 메시지를 모았습니다.
고객명, 회사명, 날짜처럼 매번 달라지는 부분은 공통 자리표시자로 정리해 상담사가 필요한 문구를 빠르게 찾아 사용할 수 있도록 했습니다.
자동화 후 달라진 점
상담 담당자
•
원본 보장분석 파일을 다시 복사해 정리하지 않음
•
가입 보험 수가 달라도 같은 버튼으로 리포트 생성
•
현재 납입 중인 월 보험료를 자동 확인
•
핵심보장을 상담 우선순위대로 설명
•
고객에게 보낼 PNG 2종을 한 번에 저장
•
상담 DB와 월간 일정을 같은 파일에서 관리
관리 담당자
•
계약 건수와 금액을 대시보드에서 확인
•
상담 단계별 진행 현황을 한 화면에서 관리
•
고객별 최종 상담 상태 자동 확인
•
질문지와 안내 멘트를 공통 기준으로 운영
시스템
•
Power Query로 빈 행·빈 열·반복 헤더 자동 정리
•
원본을 계약 요약과 카테고리 보장정보로 자동 분리
•
보험 개수에 따라 결과 열 자동 확장
•
쿼리 완료 후 VBA가 출력 화면 생성
•
값 전용 결과 시트로 수식 훼손 방지
•
고객명과 날짜 기준 파일명 자동 생성
이번 프로젝트의 찐 포인트
이 프로젝트의 핵심은 보장금액을 계산하는 수식 하나가 아닙니다.
원본 파일을 불러오는 단계부터 상담용 표를 만드는 과정, 핵심보장을 설명하는 화면, 고객에게 보낼 이미지, 이후 상담 일정과 진행 상태까지 하나의 업무 흐름으로 연결한 것이 핵심입니다.
Power Query는 원본을 정리하고, Excel 수식은 고객마다 달라지는 보험 수에 맞춰 표를 구성하며, VBA는 새로고침과 출력, 이미지 저장을 담당합니다.
각 기능이 잘하는 역할을 나눠 맡도록 만들었기 때문에 담당자는 내부 구조를 몰라도 원본 파일을 바꾸고 버튼을 누르는 방식으로 사용할 수 있습니다.
좋은 자동화는 표를 대신 만드는 데서 끝나지 않습니다. 그 표를 가지고 실제 상담하고, 전달하고, 다음 일정을 관리하는 흐름까지 이어져야 한다고 생각합니다.
이런 업무에 적합합니다
•
고객마다 가입 보험 수가 크게 달라지는 경우
•
보장분석 원본을 상담용 자료로 다시 정리하는 경우
•
실비, 진단, 수술 등 카테고리별 보장을 비교해야 하는 경우
•
현재 납입 중인 보험료만 따로 계산하고 싶은 경우
•
Excel과 Power Query를 이용해 반복 리포트를 만드는 경우
•
고객에게 전달할 표를 PNG로 자주 저장하는 경우
•
상담 DB, 통계, 일정과 보장분석을 한 파일에서 관리하고 싶은 경우
•
기존 엑셀 업무 방식을 유지하면서 반복 작업만 자동화하고 싶은 경우