매주 반복하는 엑셀 분석, 계산과 해석을 분리하면 빨라집니다
매주 월요일마다 지난주 매출 파일을 열고, 담당자별 실적을 합산하고, 목표 달성률을 계산한 뒤, 특이사항을 찾아 보고 문장으로 정리하는 업무가 있습니다.
데이터가 많지 않아도 과정은 번거롭습니다. 필터를 바꾸고, 수식을 복사하고, 지난주 보고서와 비교하며 문장을 다시 작성해야 하기 때문입니다. 특히 숫자 계산과 보고서 작성이 한 작업 안에 섞이면 실수가 발생하기 쉽습니다.
이 업무는 엑셀 함수와 AI를 다음과 같이 역할 분담하면 효율적으로 처리할 수 있습니다.
- 엑셀: 합계, 달성률, 증감률, 상태 분류처럼 기준이 명확한 계산
- AI: 계산된 결과를 읽고 보고용 문장으로 정리하는 작업
계산 기준은 엑셀에 고정하고, AI에는 검증된 결과만 전달하는 방식입니다. 이렇게 하면 AI가 숫자를 임의로 계산하면서 발생할 수 있는 오류를 줄이고, 반복 보고서 작성 시간도 단축할 수 있습니다.
실제 장면: 담당자별 매출 현황을 자동으로 분석하기
다음과 같은 주간 영업 실적표가 있다고 가정하겠습니다.
| 담당자 | 목표 매출 | 실제 매출 | 전주 매출 |
|---|---|---|---|
| 영업1팀 | 50,000,000 | 54,000,000 | 48,000,000 |
| 영업2팀 | 45,000,000 | 39,600,000 | 42,000,000 |
| 영업3팀 | 60,000,000 | 61,800,000 | 59,000,000 |
| 영업4팀 | 40,000,000 | 30,000,000 | 36,000,000 |
여기에 다음 세 가지 분석 항목을 추가하겠습니다.
- 목표 달성률
- 전주 대비 증감률
- 자동 상태 판정
1단계: 목표 달성률 계산하기
E2 셀에 아래 수식을 입력합니다.
=IFERROR(C2/B2,0)
C2의 실제 매출을 B2의 목표 매출로 나누는 수식입니다. IFERROR를 함께 사용하면 목표 매출이 비어 있거나 0일 때 발생하는 오류를 0으로 처리할 수 있습니다.
E열의 표시 형식을 백분율로 바꾸면 다음 결과가 나옵니다.
| 담당자 | 목표 달성률 |
|---|---|
| 영업1팀 | 108.0% |
| 영업2팀 | 88.0% |
| 영업3팀 | 103.0% |
| 영업4팀 | 75.0% |
2단계: 전주 대비 증감률 계산하기
F2 셀에는 아래 수식을 입력합니다.
=IFERROR((C2-D2)/D2,0)
현재 매출에서 전주 매출을 뺀 뒤 전주 매출로 나누는 방식입니다. F열도 백분율 형식으로 변경합니다.
| 담당자 | 전주 대비 증감률 |
|---|---|
| 영업1팀 | 12.5% |
| 영업2팀 | -5.7% |
| 영업3팀 | 4.7% |
| 영업4팀 | -16.7% |
여기까지는 일반적인 엑셀 분석입니다. 다음 단계부터는 사람이 매번 판단하던 기준까지 수식으로 고정합니다.
3단계: 실적 상태를 자동으로 분류하기
목표 달성률과 전주 대비 증감률을 함께 보고 상태를 분류하겠습니다. 예를 들어 다음 기준을 사용할 수 있습니다.
- 목표 달성률 100% 이상: 목표 달성
- 목표 달성률 90% 이상 100% 미만: 점검 필요
- 목표 달성률 90% 미만이면서 전주 대비 10% 이상 감소: 집중 확인
- 그 외: 개선 필요
G2 셀에 다음 수식을 입력합니다.
=IFS(E2>=1,"목표 달성",AND(E2<0.9,F2<=-0.1),"집중 확인",E2>=0.9,"점검 필요",TRUE,"개선 필요")
수식을 아래 행까지 복사하면 결과는 다음과 같습니다.
| 담당자 | 달성률 | 전주 대비 | 자동 판정 |
|---|---|---|---|
| 영업1팀 | 108.0% | 12.5% | 목표 달성 |
| 영업2팀 | 88.0% | -5.7% | 개선 필요 |
| 영업3팀 | 103.0% | 4.7% | 목표 달성 |
| 영업4팀 | 75.0% | -16.7% | 집중 확인 |
이제 담당자가 매주 눈으로 달성률과 증감률을 비교해 상태를 판단할 필요가 없습니다. 새로운 매출 데이터를 붙여 넣으면 동일한 기준으로 결과가 자동 갱신됩니다.
엑셀 결과를 AI용 분석 문장으로 바꾸기
AI에 원본 엑셀 파일 전체를 바로 올리기보다, 필요한 결과만 정리해서 전달하는 편이 안전하고 정확합니다. 이를 위해 엑셀에서 AI 입력용 문장을 자동 생성할 수 있습니다.
4단계: TEXTJOIN 함수로 AI 입력 자료 만들기
H2 셀에 다음 수식을 입력합니다.
=A2&" | 목표 "&TEXT(B2,"#,##0")&"원 | 실적 "&TEXT(C2,"#,##0")&"원 | 달성률 "&TEXT(E2,"0.0%")&" | 전주 대비 "&TEXT(F2,"0.0%")&" | 상태 "&G2
영업1팀의 결과는 다음과 같이 표시됩니다.
영업1팀 | 목표 50,000,000원 | 실적 54,000,000원 | 달성률 108.0% | 전주 대비 12.5% | 상태 목표 달성
각 행의 분석 문장을 한 셀에 모으려면 별도의 셀에 다음 수식을 입력합니다.
=TEXTJOIN(CHAR(10),TRUE,H2:H5)
CHAR(10)은 각 팀의 결과를 줄바꿈으로 구분합니다. 셀 서식에서 ‘자동 줄 바꿈’을 적용하면 네 팀의 결과가 읽기 좋은 형태로 정리됩니다.
ChatGPT에 입력해 주간 보고문 만들기 ( ( ( (자세히 보기)자세히 보기)자세히 보기)자세히 보기)
앞에서 만든 결과를 복사한 뒤 ChatGPT에 다음과 같이 입력합니다.
실제 AI 입력 예시
아래 자료는 엑셀 함수로 계산한 주간 영업 실적입니다.
숫자를 다시 계산하거나 변경하지 말고, 제공된 수치를 기준으로만 분석하세요.
1. 전체 실적을 3문장으로 요약하세요.
2. 목표를 달성한 팀과 집중 확인이 필요한 팀을 구분하세요.
3. 확인이 필요한 원인을 단정하지 말고, 추가로 점검할 항목을 제안하세요.
4. 부서장에게 보고하는 차분한 문체로 작성하세요.영업1팀 | 목표 50,000,000원 | 실적 54,000,000원 | 달성률 108.0% | 전주 대비 12.5% | 상태 목표 달성
영업2팀 | 목표 45,000,000원 | 실적 39,600,000원 | 달성률 88.0% | 전주 대비 -5.7% | 상태 개선 필요
영업3팀 | 목표 60,000,000원 | 실적 61,800,000원 | 달성률 103.0% | 전주 대비 4.7% | 상태 목표 달성
영업4팀 | 목표 40,000,000원 | 실적 30,000,000원 | 달성률 75.0% | 전주 대비 -16.7% | 상태 집중 확인
AI가 작성할 수 있는 결과 예시
이번 주에는 영업1팀과 영업3팀이 각각 목표 대비 108.0%, 103.0%를 기록해 목표를 달성했습니다. 영업1팀은 전주 대비 12.5% 증가해 가장 뚜렷한 상승세를 보였습니다. 반면 영업4팀은 목표 달성률이 75.0%이고 전주 대비 16.7% 감소해 우선 확인이 필요합니다.
영업2팀은 목표 달성률 88.0%로 목표에는 미달했으나, 전주 대비 감소 폭은 5.7%로 영업4팀보다 제한적입니다. 영업4팀은 주요 거래처 주문 변동, 납기 지연, 영업 활동량 변화, 일회성 매출 종료 여부를 추가로 확인하는 것이 적절합니다.
이 과정에서 AI는 계산을 담당하지 않습니다. 엑셀이 확정한 수치를 바탕으로 요약하고, 확인할 항목을 제안하는 역할만 맡습니다.
이 구분이 중요한 이유는 AI가 긴 표 안의 숫자를 잘못 읽거나, 사용자가 요청하지 않은 방식으로 다시 계산할 가능성이 있기 때문입니다. 보고서의 기준 숫자는 엑셀에 남겨 두고, AI 결과는 설명 초안으로 활용하는 편이 안전합니다.
새 데이터가 들어올 때 자동으로 범위를 확장하는 방법
매주 행이 추가되는 파일이라면 일반 셀 범위보다 ‘표’ 기능을 사용하는 것이 편리합니다.
- 원본 데이터 영역 안의 셀을 클릭합니다.
- Ctrl+T를 누릅니다.
- ‘머리글 포함’을 확인한 뒤 표로 변환합니다.
- 달성률, 증감률, 상태 판정 수식을 표 열에 입력합니다.
표로 변환하면 다음 주 데이터를 마지막 행 아래에 붙여 넣을 때 수식이 자동으로 확장됩니다. H2:H5처럼 범위를 수동 수정하는 대신 구조화된 참조를 사용할 수도 있습니다.
=TEXTJOIN(CHAR(10),TRUE,실적표[AI입력문장])
표 이름이 ‘실적표’이고 문장을 만든 열 이름이 ‘AI입력문장’일 때 사용하는 방식입니다. 데이터가 4행에서 40행으로 늘어나도 범위를 다시 지정할 필요가 없습니다.
대부분 놓치는 단계: 예외값을 먼저 분리하기
자동화 수식이 정상 작동해도 원본 데이터에 문제가 있으면 보고서 역시 잘못됩니다. AI로 넘기기 전에 다음 예외를 엑셀에서 확인해야 합니다.
- 목표 매출이나 전주 매출이 0인 행
- 숫자가 문자 형식으로 저장된 행
- 담당자명이 중복된 행
- 취소·반품 매출이 반영되지 않은 행
- 집계 기간이 다른 데이터가 섞인 행
I2 셀에 아래 검증 수식을 추가하면 기본적인 오류를 표시할 수 있습니다.
=IF(A2="","담당자 누락",IF(B2<=0,"목표값 확인",IF(D2<=0,"전주값 확인",IF(COUNTIF($A:$A,A2)>1,"중복 확인","정상"))))
검증 결과가 ‘정상’이 아닌 행은 AI 입력 자료에서 제외하거나 담당자가 먼저 확인합니다. 보기 좋은 보고서를 빠르게 만드는 것보다 잘못된 숫자가 보고되는 것을 막는 일이 우선입니다.
반복 업무에 적용하는 순서
처음부터 복잡한 자동화를 만들 필요는 없습니다. 현재 사용 중인 엑셀 파일에 다음 순서로 적용해 볼 수 있습니다.
- 반복 판단 찾기: 매번 사람이 비교하는 달성률, 증감률, 지연 여부를 찾습니다.
- 판단 기준 수식화: IF, IFS, SUMIFS, COUNTIFS 등의 함수로 기준을 고정합니다.
- 예외값 검증: 빈칸, 0, 중복, 문자 형식 데이터를 별도 표시합니다.
- AI 입력문 생성: TEXT, TEXTJOIN, & 연산자로 필요한 결과만 문장으로 만듭니다.
- AI 역할 제한: 숫자를 다시 계산하지 않고 요약과 확인 항목만 작성하도록 지시합니다.
- 최종 검토: AI 보고문 속 수치가 엑셀 결과와 일치하는지 확인한 뒤 사용합니다.
매출뿐 아니라 생산 불량률, 재고 부족 목록, 고객 문의 유형, 프로젝트 일정 지연, 부서별 비용 분석에도 같은 구조를 적용할 수 있습니다. 계산 항목과 판정 기준만 업무에 맞게 바꾸면 됩니다.
개인정보와 사내 자료를 안전하게 다루는 방법
업무용 데이터를 외부 AI 서비스에 입력하기 전에는 회사의 정보보안 규정과 AI 사용 지침을 먼저 확인해야 합니다. 회사가 외부 AI 도구 사용을 금지하거나 승인된 계정만 허용한다면 해당 기준을 따라야 합니다.
외부 AI에 전달할 때는 다음 정보를 제거하거나 대체하는 것이 안전합니다.
- 직원 이름, 사번, 전화번호, 이메일 주소
- 고객명, 거래처명, 계약번호, 계좌번호
- 아직 공개되지 않은 매출, 원가, 생산계획
- 제품 설계 정보, 제조 조건, 내부 품질 기준
- 인사평가, 급여, 민감한 고객 상담 내용
예를 들어 ‘홍길동’이나 실제 거래처명을 입력하는 대신 ‘담당자 A’, ‘거래처 1’처럼 바꿀 수 있습니다. 분석 목적에 필요하지 않은 열은 처음부터 복사하지 않는 것이 좋습니다.
민감한 자료를 외부로 전달할 수 없는 환경이라면 AI에는 실제 데이터 대신 표 구조와 가상의 예시값만 제공해 수식이나 보고서 양식을 만든 뒤, 실제 계산은 사내 엑셀에서 수행하는 방식이 적절합니다.
FAQ
Q1. ChatGPT에 엑셀 파일 전체를 올려도 되나요?
파일에 개인정보, 고객정보, 매출, 원가, 계약 내용 등 민감한 정보가 포함돼 있다면 바로 올리지 않는 것이 안전합니다. 회사의 보안 규정과 승인된 AI 사용 범위를 먼저 확인해야 합니다. 가능하면 필요한 열만 남기고 이름과 식별정보를 익명화한 뒤 사용하세요. 외부 전송이 금지된 자료라면 실제 파일을 올리지 말고 가상의 데이터 구조만 활용해야 합니다.
Q2. AI가 계산까지 하도록 맡기면 더 편하지 않나요?
간단한 계산은 가능하지만, 업무 보고의 기준 숫자는 엑셀 함수로 계산하는 편이 안정적입니다. 엑셀은 같은 수식과 기준을 반복 적용하고 계산 과정을 셀에서 확인할 수 있습니다. AI는 검증된 숫자를 요약하고 의미를 설명하는 역할로 제한하면 계산 오류를 발견하기 쉽습니다.
Q3. 매주 데이터를 바꿀 때 수식을 다시 복사해야 하나요?
데이터 영역을 엑셀의 ‘표’로 변환하면 새 행을 추가할 때 수식과 서식이 자동으로 확장됩니다. AI 입력용 문장도 TEXTJOIN과 구조화된 참조를 사용하면 데이터 행 수에 맞춰 자동 갱신됩니다. 다만 새로 추가된 데이터의 빈칸, 중복, 숫자 형식 오류는 별도 검증 열에서 확인해야 합니다.