6. AI로 표·집계·조건 분석 자동화하기

엑셀 보고서에서 가장 시간이 오래 걸리는 일은 차트를 예쁘게 꾸미는 일이 아니라, 숫자의 뼈대를 만드는 일입니다. 부서별 몇 건인지, 상태별로 얼마나 쌓였는지, 평균 처리일이 며칠인지, 어느 값이 유난히 큰지부터 빨리 잡혀야 보고서가 앞으로 나갑니다.

이번 장에서는 AI의 도움을 받아 피벗테이블, 요약표, 조건별 집계, 이상치 점검, 핵심지표 계산을 빠르게 만드는 방법을 익힙니다. 한마디로 말하면, 원본 데이터를 보고서 숫자로 바꾸는 속도를 올리는 장입니다.

현실 업무 상황: 월간 보고서 마감 2시간 전

공공기관 실무에서는 이런 일이 자주 생깁니다.

사업 운영 실적 파일이 도착했습니다. 행은 3,000개가 넘고, 열에는 접수번호, 접수일, 부서, 지역, 사업유형, 처리상태, 처리일수, 예산집행액, 만족도가 들어 있습니다. 팀장은 말합니다.

"부서별 처리 건수, 지연 건수, 평균 처리일수, 예산집행액 합계, 특이한 값까지 한 번에 정리해 주세요."

이때 사람이 처음부터 끝까지 손으로 필터를 걸고, 복사하고, COUNTIFS와 SUMIFS를 직접 쓰기 시작하면 금방 지칩니다. 반대로 AI에게 무엇을 어떤 기준으로 집계할지 정확히 시키면, 초안은 몇 분 안에 나옵니다.

왜 필요한가: 보고서는 숫자 요약이 먼저다

보고서의 첫 줄에는 늘 요약 숫자가 들어갑니다. "총 1,248건 처리", "완료율 92%", "지연 37건", "예산 집행률 84%" 같은 문장입니다.

이 숫자가 빨리 나오면 나머지 작업도 빨라집니다. 피벗테이블로 큰 흐름을 보고, 조건별 집계로 필요한 숫자를 뽑고, KPI로 핵심 수치를 묶고, 이상치를 따로 점검하면 보고서가 훨씬 안정적으로 완성됩니다.

특히 AI는 이런 작업에서 강합니다. 데이터가 표 형식으로 정리되어 있고, 열 이름이 분명하며, 빈 행 없이 이어져 있을 때 더 정확하게 요약을 도와줍니다.¹ 또 Excel의 Analyze Data나 Copilot 계열 기능은 자연어 질문만으로 요약표나 피벗테이블 초안을 만들 수 있습니다.²

핵심 개념: 딱 이것만 알고 시작하자

피벗테이블은 많은 행을 항목별로 묶어 자동 집계하는 기능입니다. "부서별 건수", "월별 예산집행액", "상태별 평균 처리일수"처럼 큰 흐름을 볼 때 가장 빠릅니다.

조건별 집계는 특정 조건을 만족하는 값만 세거나 더하거나 평균내는 방식입니다. 예를 들어 "복지부서의 완료 건수", "서울 지역 예산 합계", "지연 상태의 평균 처리일수"를 구할 때 씁니다.

KPI는 보고서에서 맨 앞에 보여줄 핵심 숫자입니다. 총 건수, 완료율, 평균 처리일수, 1건당 평균 집행액 같은 값이 여기에 들어갑니다.

이상치 점검은 평소와 다르게 튀는 값을 찾는 일입니다. 처리일수가 지나치게 긴 건, 예산집행액이 유난히 큰 건, 만족도가 매우 낮은 건은 따로 표시해 두면 보고서 신뢰도가 높아집니다. 최근 AI 도구는 이런 이상치 후보를 자동으로 짚어주는 데에도 강점을 보입니다.⁴

그리고 한 가지 더 기억해 두세요. 조건별 합계에 많이 쓰는 SUMIFS는 더할 범위가 먼저 오고, 그 뒤에 조건 범위와 조건이 붙습니다.³ 순서를 헷갈리면 결과가 틀어지기 쉽습니다.

이제 바로 실습해 보겠습니다.

실습용 데이터 설정

아래와 같은 열 이름이 있다고 가정하고 진행하겠습니다.

가능하면 이 데이터는 먼저 Ctrl + T로 표로 바꿔두세요. 표 이름은 tbl_work로 두면 이후 실습이 편합니다.

실습 1. AI로 피벗테이블 초안 만들기

목표

부서별 처리 건수와 상태별 분포를 한 번에 보는 피벗테이블을 만든다.

STEP BY STEP 실행

1. 원본 데이터 안 아무 셀이나 클릭합니다.

2. Ctrl + T를 눌러 표로 바꾸고, 표 이름을 tbl_work로 지정합니다.

3. Excel에 Copilot 또는 데이터 분석(Analyze Data) 기능이 있다면 실행합니다.

4. 아래 프롬프트를 입력합니다.

5. 결과로 나온 요약표나 피벗테이블 제안을 확인합니다.

6. 원하는 형태가 나오면 새 시트에 삽입합니다.

7. 부족하면 후속 질문으로 수정합니다. 예를 들어 "열에 처리상태를 추가해줘", "합계 대신 개수로 바꿔줘"처럼 말하면 됩니다.

복붙 가능한 프롬프트

다음 열을 가진 표를 요약해줘: 접수번호, 접수일, 부서, 지역, 사업유형, 처리상태, 처리일수, 예산집행액, 만족도.
부서별 접수번호 개수를 보여주는 피벗테이블을 만들어줘.
열에는 처리상태를 배치하고, 값은 접수번호 개수로 해줘.
결과는 새 시트에 넣기 좋은 형태로 보여줘.

결과 예시

이 표만 있어도 바로 이런 해석이 가능합니다.

민원 부서의 전체 처리 건수가 가장 많고, 시설 부서는 지연 건수가 상대적으로 많습니다. 즉, 다음 보고서 문장은 AI 없이도 거의 자동으로 나옵니다. "민원 부서의 처리량이 가장 많았으며, 시설 부서에서 지연 건수가 상대적으로 높게 나타났다."

한 번 더 시켜보기

같은 데이터로 아래처럼 질문하면 다른 각도의 피벗도 금방 만들 수 있습니다.

월별 예산집행액 합계와 평균 처리일수를 부서별로 비교하는 피벗테이블을 만들어줘.
행은 접수일의 월, 열은 부서, 값은 예산집행액 합계와 처리일수 평균으로 해줘.

피벗테이블의 좋은 점은 초안은 AI가 만들고, 마지막 다듬기는 내가 한다는 점입니다. 즉, AI가 80%를 만들고, 실무자는 보고서 목적에 맞게 20%만 고치면 됩니다.

실습 2. 조건별 집계를 AI로 자동 생성하기

목표

COUNTIFS, SUMIFS, AVERAGEIFS를 AI에게 맡겨서 요약표를 빠르게 만든다.

이번 실습은 피벗테이블보다 더 실무적입니다. 이유는 보고서에서 자주 이런 표가 필요하기 때문입니다.

"부서별 완료 건수는?" "서울 지역만 따로 보면?" "지연 상태 평균 처리일수는?"

이런 질문은 대부분 조건별 집계로 해결합니다.

STEP BY STEP 실행

1. 새 시트 이름을 요약으로 만듭니다.

2. A1:D1에 부서, 완료, 진행중, 지연을 입력합니다.

3. A2:A5에 부서명을 적습니다.

4. AI에게 아래 프롬프트를 넣어 수식을 받습니다.

5. 받은 수식을 B2에 붙여넣고 오른쪽, 아래로 복사합니다.

6. 같은 방식으로 예산집행액 합계표와 평균 처리일수 표도 만듭니다.

복붙 가능한 프롬프트 1: 건수 집계

엑셀 표 이름은 tbl_work야.
열 이름은 접수번호, 접수일, 부서, 지역, 사업유형, 처리상태, 처리일수, 예산집행액, 만족도야.
요약 시트에서 A열에는 부서명, 1행에는 처리상태(완료, 진행중, 지연)가 들어 있어.
B2 셀에 넣을 수식을 만들어줘.
조건은 A2의 부서와 B1의 처리상태를 기준으로 접수번호 개수를 세고,
오른쪽과 아래로 복사해도 참조가 맞게 해줘.

결과 예시 1

=COUNTIFS(tbl_work[부서],$A2,tbl_work[처리상태],B$1)

이 수식은 정말 자주 씁니다. 부서와 상태라는 두 조건을 동시에 만족하는 행의 개수를 세는 방식입니다.

복붙 가능한 프롬프트 2: 금액 집계

같은 구조에서 부서와 처리상태별 예산집행액 합계를 구하는 수식으로 바꿔줘.

결과 예시 2

=SUMIFS(tbl_work[예산집행액],tbl_work[부서],$A2,tbl_work[처리상태],B$1)

여기서 꼭 기억할 점이 있습니다. SUMIFS는 첫 번째 인수가 합계 범위입니다. 즉, 무엇을 더할지 먼저 써야 합니다.³

복붙 가능한 프롬프트 3: 평균 처리일수 집계

같은 구조에서 부서와 처리상태별 평균 처리일수를 구하는 수식으로 만들어줘.

결과 예시 3

=AVERAGEIFS(tbl_work[처리일수],tbl_work[부서],$A2,tbl_work[처리상태],B$1)

결과 예시 표

이 방식의 장점은 피벗테이블과 다릅니다. 피벗테이블은 빠르게 전체를 보는 데 좋고, 조건별 집계 수식은 보고서 양식 안에 숫자를 딱 맞게 꽂아 넣기 좋습니다.

실습 3. 핵심지표(KPI) 요약표 만들기

목표

보고서 첫 장에 넣을 핵심 숫자를 AI로 빠르게 계산한다.

공공기관 실무에서는 길고 복잡한 표보다, 맨 위에 있는 숫자 네다섯 개가 더 중요할 때가 많습니다. 그래서 KPI 요약표를 따로 만들어 두면 좋습니다.

이번에는 다음 다섯 가지를 만들어 보겠습니다.

1. 총 접수 건수

2. 완료율

3. 평균 처리일수

4. 지연 건수

5. 1건당 평균 예산집행액

STEP BY STEP 실행

1. KPI 시트를 만듭니다.

2. A1:B6 영역에 항목명과 값을 배치할 자리를 만듭니다.

3. AI에게 아래 프롬프트를 넣어 각 셀 수식을 받습니다.

4. 수식을 붙여넣습니다.

5. 마지막으로 AI에게 보고서용 한 줄 해석 문장까지 받습니다.

복붙 가능한 프롬프트

엑셀 표 이름은 tbl_work야.
열 이름은 접수번호, 접수일, 부서, 지역, 사업유형, 처리상태, 처리일수, 예산집행액, 만족도야.
아래 KPI를 계산하는 엑셀 수식을 각각 만들어줘.
1) 총 접수 건수
2) 완료율 = 완료 건수 / 전체 건수
3) 평균 처리일수
4) 지연 건수
5) 1건당 평균 예산집행액
구조화 참조를 사용하고, 바로 복사해서 붙여넣을 수 있게 수식만 깔끔하게 제시해줘.

결과 예시

결과 예시 표

복붙 가능한 프롬프트: 보고서 문장까지 만들기

다음 KPI 결과를 바탕으로 공공기관 월간 보고서용 요약 문장 3개를 만들어줘.
문장은 딱딱하지 않게, 하지만 보고서에 바로 붙여넣을 수 있게 써줘.
총 접수 건수 808건, 완료율 80.7%, 평균 처리일수 6.8일, 지연 건수 56건, 1건당 평균 예산집행액 1,240,000원.

결과 예시

이번 달 총 접수 건수는 808건으로 집계되었으며, 이 중 80.7%가 완료 처리되었습니다. 평균 처리일수는 6.8일로 확인되었고, 지연 건수는 56건으로 나타났습니다. 건당 평균 예산집행액은 124만 원 수준으로 파악되었습니다.

숫자만 뽑는 데서 끝나지 않고, 보고서 문장까지 연결하는 것이 AI 활용의 핵심입니다.

실습 4. 이상치와 점검 대상 자동 표시하기

목표

튀는 값을 빠르게 찾아서 점검 리스트를 만든다.

실무에서 이상치는 꼭 복잡한 통계로만 찾는 것이 아닙니다. 오히려 먼저 하는 방식은 아주 단순합니다.

"처리일수 30일 초과" "예산집행액 1,000만 원 초과" "만족도 2점 이하"

이런 기준만 잡아도 점검 대상이 바로 드러납니다.

STEP BY STEP 실행

1. 원본 표 오른쪽에 새 열 점검여부를 추가합니다.

2. AI에게 기준에 맞는 수식을 요청합니다.

3. 수식을 첫 행에 넣어 자동 채우기합니다.

4. 점검으로 표시된 행만 필터링합니다.

5. 별도 시트 점검목록으로 복사합니다.

복붙 가능한 프롬프트

엑셀 표 이름은 tbl_work야.
열 이름은 접수번호, 접수일, 부서, 지역, 사업유형, 처리상태, 처리일수, 예산집행액, 만족도야.
다음 기준 중 하나라도 만족하면 "점검", 아니면 "정상"을 표시하는 구조화 참조 수식을 만들어줘.
- 처리일수 30일 초과
- 예산집행액 10000000 초과
- 만족도 2 이하

결과 예시

=IF(OR([@처리일수]>30,[@예산집행액]>10000000,[@만족도]<=2),"점검","정상")

결과 예시 표

이렇게 해 두면 보고서 작성 중간에 "이 값 왜 이렇게 크지?" 하고 다시 원본을 찾으러 돌아갈 일이 줄어듭니다.

한 단계 더: AI에게 자동 점검 의견 받기

다음은 점검 대상으로 분류된 행이야.
각 행에 대해 왜 점검이 필요한지 한 줄씩 설명해줘.
설명은 실무 메모처럼 짧게 써줘.

결과 예시는 이렇게 나올 수 있습니다.

A-1022는 처리일수가 기준치 30일을 넘어 장기 지연 가능성이 있습니다. A-1088은 예산집행액이 다른 건보다 커서 집행 근거 확인이 필요합니다. A-1150은 만족도가 낮아 민원 재발 가능성을 점검할 필요가 있습니다.

실습 5. 피벗 없이 바로 요약표 만들기

목표

AI에게 "피벗처럼 보이는 요약표"를 바로 설계받아 보고서 표를 빠르게 만든다.

가끔은 피벗테이블보다 그냥 표 하나가 더 편합니다. 예를 들어 이런 형태입니다.

이럴 때는 AI에게 표 구조 자체를 설계하게 하면 됩니다.

STEP BY STEP 실행

1. 새 시트에 지역 요약표 영역을 만듭니다.

2. AI에게 필요한 열 구성을 설명합니다.

3. 각 열에 들어갈 수식을 받습니다.

4. 표를 완성한 뒤 필요하면 정렬합니다.

복붙 가능한 프롬프트

엑셀 표 이름은 tbl_work야.
지역별로 다음 항목을 보여주는 요약표를 만들고 싶어.
- 접수번호 건수
- 예산집행액 합계
- 처리일수 평균
A열에는 지역명이 들어 있고, B열부터 D열까지 들어갈 수식을 만들어줘.
아래로 복사 가능한 형태로 작성해줘.

결과 예시

• B2: =COUNTIFS(tbl_work[지역],$A2)

• C2: =SUMIFS(tbl_work[예산집행액],tbl_work[지역],$A2)

• D2: =AVERAGEIFS(tbl_work[처리일수],tbl_work[지역],$A2)

이 방법은 기관별 보고서 양식이 정해져 있을 때 특히 편합니다. 피벗테이블로 만든 뒤 다시 복사해서 옮길 필요가 줄어들기 때문입니다.

실무에서 자주 나는 오류와 해결법

숫자가 안 맞을 때는 대부분 아래 세 가지를 먼저 의심하면 됩니다.

1. 열 이름이 프롬프트와 정확히 일치하지 않는 경우

   AI가 처리상태를 찾는데 실제 열 이름이 처리 상태면 결과가 어긋날 수 있습니다.

2. 숫자처럼 보이지만 실제로는 텍스트인 경우

   예산집행액이 텍스트면 합계가 이상하게 나옵니다. 쉼표나 공백이 섞여 있는지 확인하세요.

3. 상태값 표기가 제각각인 경우

   완료, 처리완료, 완 료가 섞여 있으면 집계가 갈라집니다. 먼저 표준화해야 합니다.

이럴 때 AI에게 이렇게 다시 요청하면 좋습니다.

이 표에서 집계가 틀어질 수 있는 값 표기 불일치를 찾아줘.
특히 처리상태, 지역, 부서 열의 중복 표기나 띄어쓰기 차이를 점검해줘.

바로 써먹는 프롬프트 모음

아래 프롬프트는 실무에서 거의 그대로 쓸 수 있습니다.

1. 부서별 완료 건수와 지연 건수를 한 표로 요약해줘.

2. 지역별 예산집행액 합계를 내림차순으로 정리해줘.

3. 처리일수가 긴 상위 10건을 찾아줘.

4. 만족도 2점 이하인 건만 따로 요약해줘.

5. 월별 건수와 예산집행액 추이를 비교할 수 있는 피벗테이블 구조를 제안해줘.

6. 이 데이터를 기준으로 보고서 첫 장에 넣을 KPI 5개를 추천해줘.

7. 이상치로 보이는 행을 찾고, 왜 점검이 필요한지도 짧게 설명해줘.

마무리

이번 장의 핵심은 어렵지 않습니다. 원본 데이터를 요약 숫자로 바꾸는 일을 AI에게 맡기는 것입니다.

피벗테이블로 큰 그림을 잡고, 조건별 집계로 보고서 숫자를 채우고, KPI로 핵심만 보여주고, 이상치를 따로 점검하면 엑셀 업무 시간이 크게 줄어듭니다. 특히 공공기관 실무처럼 반복 보고가 많은 환경에서는 이 차이가 더 크게 느껴집니다.

다음 장에서는 이렇게 정리된 숫자를 바탕으로 차트와 대시보드를 만드는 방법을 이어서 살펴보겠습니다.

참고

¹How to Create a Pivot Table in Excel Using AI

²Unlocking Insights: AI-Supported Pivot tables In Excel (2026 Guide)

³Mastering Excel Conditional Aggregations: COUNTIFS, SUMIFS, and More

⁴HOW TO USE 5 AI-POWERED INSIGHTS TO SUMMARIZE EXCEL TABLES