취소·미완료를 뺀 집계: 포함·제외·보류로 합계 확인
AI에 “취소 건은 빼고 합계를 내 주세요”라고만 요청하면 취소 상태가 누락된 행, 테스트 주문, 마이너스 조정액까지 제각각 처리될 수 있습니다. 먼저 포함·제외·보류의 기준을 정하고 행별 판정표를 받은 뒤 합계를 계산하면, 숫자가 틀렸을 때 어느 행으로 돌아가야 하는지 보입니다.
이번 예제는 월말 출고 정산 담당자가 AI에 집계 초안을 요청하는 상황입니다. 아래 자료는 가상이며, 금액은 모두 원입니다. 마이너스 금액은 승인된 정산 조정액으로 정했습니다. 실제 정산에서 이런 행을 어떻게 처리할지는 회사 기준을 먼저 확인해야 합니다.
1. 제외 사유와 적용 순서를 먼저 적습니다
업무 규칙을 다음과 같이 정합니다.
- 취소, 출고 미완료, 테스트 주문 중 하나라도 해당하면 제외합니다
- 명확한 제외 사유가 없고 승인·출고·구분 중 값이 비어 있거나 허용 목록 밖이면 보류합니다
- 승인, 출고 완료, 정상 구분이 모두 확인되고 금액이 숫자이면 포함합니다
- 정상적으로 승인된 음수 조정액은 부호를 유지해서 포함합니다
이 예제에서는 명확한 제외 사유를 보류보다 먼저 적용합니다. 따라서 승인 칸이 비어 있어도 테스트 주문이면 제외입니다. 반대로 회사가 모든 결측 행을 먼저 조사하는 방침이라면 그 우선순위를 바꿔야 합니다. “아무래도 제외일 것 같다”는 모델의 판단으로 우선순위를 정하게 두지 않습니다.
| ID | 승인 | 출고 | 구분 | 금액 | 판정 | 근거 |
|---|---|---|---|---|---|---|
| T01 | 승인 | 완료 | 정상 | 120000 | 포함 | 필요한 상태 확인 |
| T02 | 취소 | 완료 | 정상 | 60000 | 제외 | 취소 |
| T03 | 승인 | 미완료 | 정상 | 90000 | 제외 | 출고 미완료 |
| T04 | 미기입 | 완료 | 정상 | 40000 | 보류 | 승인 확인 필요 |
| T05 | 승인 | 완료 | 테스트 | 20000 | 제외 | 테스트 주문 |
| T06 | 승인 | 완료 | 정상 | 30000 | 포함 | 필요한 상태 확인 |
| T07 | 승인 | 완료 | 정상 | -10000 | 포함 | 승인된 음수 조정액 |
표의 ‘미기입’은 독자가 보기 위한 표시입니다. 엑셀에서는 해당 원본 셀을 빈 셀로 두고, 판정 사유에만 “승인 확인 필요”라고 적습니다. 빈 값을 0이나 취소로 바꾸면 원본에 없던 의미가 생깁니다.
2. 복사해서 사용할 AI 요청문
처음에는 합계보다 판정표를 요청합니다. AI에 넣을 자료는 회사 정책상 외부 제공이 가능한 범위로 줄이고, 주문 ID는 필요하면 가명으로 바꿉니다.
목적: 월말 출고 정산에 포함할 행을 구분한다.
원본 ID와 금액을 수정하거나 행을 삭제하지 않는다.
허용값:
승인 = 승인 / 취소 / 빈 값
출고 = 완료 / 미완료 / 빈 값
구분 = 정상 / 테스트 / 빈 값
규칙 우선순위:
1. 취소 또는 출고 미완료 또는 테스트이면 제외
2. 1에 해당하지 않으면서 필수 상태가 비었거나 허용값 밖이면 보류
3. 금액이 숫자가 아니면 보류
4. 나머지 승인·완료·정상 행은 포함
승인된 음수 조정액은 원래 부호를 유지한다.
출력:
ID, 판정, 판정에 사용한 원본 값, 이유
그 뒤 포함/제외/보류 각각의 ID 목록, 행 수, 금액 합계를 적는다.
전체 행 수와 전체 금액이 세 그룹 합계와 맞는지 별도로 적는다.
보류가 있으면 최종 확정이라고 쓰지 않는다.
입력:
T01,승인,완료,정상,120000
T02,취소,완료,정상,60000
T03,승인,미완료,정상,90000
T04,,완료,정상,40000
T05,승인,완료,테스트,20000
T06,승인,완료,정상,30000
T07,승인,완료,정상,-10000판정에 사용한 원본 값을 같이 받으면 “취소라는 단어가 들어 있었으니 제외했다” 같은 오해를 확인하기 쉽습니다. 자유 메모에 “취소 여부 확인 중”이 적힌 것과 승인 상태가 ‘취소’인 것은 다릅니다. 상태 열과 메모 열을 분리하고, 메모의 단어만 보고 상태를 바꾸지 않게 합니다.
3. 기대 결과는 합계와 행 목록을 함께 봅니다
포함 ID는 T01, T06, T07입니다. 포함 금액은 120000+30000-10000으로 140000원입니다. 제외는 T02, T03, T05이며 170000원, 보류는 T04이며 40000원입니다.
| 구분 | 행 수 | 금액 합계 |
|---|---|---|
| 포함 | 3 | 140000 |
| 제외 | 3 | 170000 |
| 보류 | 1 | 40000 |
| 원본 전체 | 7 | 350000 |
검산은 두 방향으로 합니다. 행 수는 3+3+1=7, 금액은 140000+170000+40000=350000입니다. 이 일치는 행을 분할한 결과의 보조 확인입니다. T01을 제외하고 같은 금액의 다른 행을 포함하면 합계만 맞을 수도 있으므로 ID 목록도 대조해야 합니다.
보류 40000원을 포함 합계에 넣지 않았다는 사실도 보고서에 남깁니다. 담당자가 T04의 승인을 확인하면 원본 상태를 근거와 함께 수정하고 전체 규칙을 다시 적용합니다. AI가 기존 합계에 40000원만 더하게 하지 않는 편이 안전합니다. 그 사이 다른 행의 상태가 바뀌었을 수 있기 때문입니다.
4. 엑셀에서 판정 열을 만들고 같은 기준으로 계산합니다
A열부터 ID, 승인, 출고, 구분, 금액을 넣고 F열을 판정으로 둡니다. 데이터가 2행부터 8행에 있다고 할 때 F2에 다음 수식을 넣고 아래로 복사합니다. 원본 빈 상태는 빈 셀로 두어야 합니다.
=IF(OR(B2="취소",C2="미완료",D2="테스트"),"제외",IF(OR(B2<>"승인",C2<>"완료",D2<>"정상",NOT(ISNUMBER(E2))),"보류","포함"))포함 금액과 행 수는 다음처럼 같은 판정 열을 사용합니다.
=SUMIFS(E2:E8,F2:F8,"포함")
=COUNTIFS(F2:F8,"포함")Microsoft의 SUMIFS 설명은 조건에 맞는 금액을 합산하는 방식과 합계 범위·조건 범위의 크기를 맞추는 규칙을 설명합니다. COUNTIFS 설명은 조건을 만족하는 행의 개수를 확인할 때 참고할 수 있습니다. ‘제외가 아닌 행’을 모두 더하는 조건은 보류까지 포함할 수 있으므로 이 예제에서는 ‘포함’과 정확히 일치하는 행만 더합니다.
수식이 0을 반환하면 AI 답변부터 고치기 전에 금액이 숫자로 저장됐는지, 판정에 앞뒤 공백이 있는지, 범위가 마지막 행까지 잡혔는지 살펴봅니다. 금액을 정리할 때 쉼표와 통화 표시를 일괄 삭제하기보다 원본을 보존하고 변환 열을 따로 만드는 편이 문제 추적에 좋습니다.
5. 흔히 틀리는 결과와 복구 요청
“취소만 빼면 되므로 290000원입니다.” T03과 T05가 남아 있습니다. “T03의 출고 상태, T05의 구분을 규칙에 다시 대조하고 전체 판정표를 재작성해 주세요”라고 요청합니다.
“빈 승인은 정상으로 보고 180000원입니다.” T04를 임의로 포함한 경우입니다. 빈 상태를 보류로 돌리고, 담당자에게 필요한 확인 항목을 남깁니다.
“음수는 오류이므로 제외합니다.” 이번 규칙에서는 T07이 정상 조정액입니다. 승인된 음수 처리 규칙을 다시 제시합니다. 반품이나 비용처럼 부호 의미가 다른 파일에서는 이 규칙을 그대로 재사용하지 않습니다.
“합계가 맞으니 검토 완료입니다.” 판정 사유와 ID 목록을 요구합니다. 같은 금액의 누락과 중복은 서로 상쇄될 수 있습니다.
검토 기록에는 원본 버전, 규칙 버전, 포함 ID, 보류 ID와 확인 담당자, 승인한 합계를 남기면 충분합니다. 반복되는 집계라면 AI에게 매번 계산을 맡기기보다 Power Query와 AI의 역할 분리를 기준으로 규칙을 데이터 처리 단계에 옮깁니다. 원본과 AI 제안, 승인 결과를 나눠 관리하는 방법은 엑셀 AI 결과 승인대장에서 이어 볼 수 있습니다.
자료·작성 확인일: 2026-10-04 KST. 이 예제의 기대값은 가상 입력과 명시한 규칙을 기준으로 작성했습니다. 실제 엑셀이나 특정 AI 서비스에서 집계를 실행한 결과를 제시한 것은 아닙니다.
작성자 소개
