이 글의 목적 — 주문 로그가 수백 행을 넘어가면 같은 고객이 여러 번 등장합니다. 보고서는 고객 단위 요약이 필요한데, 매번 피벗을 열고 닫는 과정은 반복 작업을 늘리고 오류를 부릅니다. 이 글은 UNIQUE를 중심으로 고유 고객 테이블을 자동 생성하고, COUNTIFS와 SUMIFS, MINIFS, MAXIFS를 결합해 주문 수와 첫 주문일, 마지막 주문일, 누적 금액, 평균 금액, 최신성 지표까지 단숨에 산출하는 방법을 다룹니다. 바로 따라 하실 수 있도록 샘플 CSV와 결과 CSV를 제공합니다.
자료 내려받기
- 샘플 원본 CSV: A5_unique_orders_sample.csv
- 결과 CSV(고객 단위 요약본): A5_unique_customers_result.csv
샘플 데이터 설명
원본은 2025년 6월부터 7월까지의 주문 로그입니다. 고객 식별자와 주문 금액, 채널이 포함되어 있고 같은 고객이 여러 번 등장합니다. 결과 파일은 고객별 집계와 최신성 계산을 포함합니다.
원본 파일 A5_unique_orders_sample.csv
| 컬럼 | 의미 | 예시 | 비고 |
|---|---|---|---|
date |
주문일 | 2025-07-15 | YYYY-MM-DD |
order_id |
주문 번호 | O-00057 | 문자열 |
customer_id |
고객 식별자 | C024 | 고유 키 |
amount |
주문 금액 | 154000 | 정수 원 단위 |
channel |
구매 채널 | Online | Online 또는 Store 또는 Phone |
customer_name |
고객 이름 | Cust 024 | 표시용 |
segment |
고객 구분 | Retail | Retail B2B VIP 중 하나 |
결과 파일 A5_unique_customers_result.csv
| 컬럼 | 의미 | 예시 | 계산식 개념 |
|---|---|---|---|
customer_id |
고객 식별자 | C024 | UNIQUE로 생성된 목록 |
customer_name |
고객 이름 | Cust 024 | 첫 등장값 |
segment |
고객 구분 | Retail | 첫 등장값 또는 다수 규칙 |
first_date |
첫 주문일 | 2025-06-02 | MINIFS |
last_date |
마지막 주문일 | 2025-07-30 | MAXIFS |
orders_count |
주문 건수 | 8 | COUNTIFS |
total_amount |
누적 금액 | 1164593 | SUMIFS |
recency_days |
마지막 주문 이후 경과일 | 1 | 기준일−last_date |
avg_amount |
평균 주문 금액 | 145574.12 | total_amount÷orders_count |
핵심 아이디어 요약
고객 단위 표를 만드는 과정은 세 단계로 생각하면 깔끔합니다. 하나. UNIQUE로 고객 키를 만든다. 둘. 그 키를 기준으로 COUNTIFS SUMIFS MINIFS MAXIFS로 보조 지표를 붙인다. 셋. 정렬과 한글 표시 형식을 마무리한다. 이렇게 별도 피벗 없이도 안정적인 고객 요약 표가 완성됩니다.
Excel 365 수식 레시피
- 고객 목록 만들기
=UNIQUE(Table1[customer_id])
동적 배열이므로 고객 수가 늘어나면 목록이 자동으로 확장됩니다. - 주문 건수 붙이기
고객 목록이 셀H2#에 있다고 가정합니다.=COUNTIFS(Table1[customer_id], H2#)
배열 입력으로 각 고객의 건수가 한 번에 계산됩니다. - 누적 금액
=SUMIFS(Table1[amount], Table1[customer_id], H2#) - 첫 주문일과 마지막 주문일
=MINIFS(Table1[date], Table1[customer_id], H2#)=MAXIFS(Table1[date], Table1[customer_id], H2#) - 평균 금액
=LET(cnt, COUNTIFS(Table1[customer_id], H2#), amt, SUMIFS(Table1[amount], Table1[customer_id], H2#), IFERROR(amt/cnt, "")) - 기준일 대비 최신성
기준일 셀K1에2025-07-31을 둡니다.=K1 - MAXIFS(Table1[date], Table1[customer_id], H2#) - 보기 좋게 정렬
=SORT(HSTACK(H2#, 이름열, 구분열, 첫주문열, 마지막주문열, 건수열, 누적열, 최신성열, 평균열), 6, -1, 7, -1)
해석. 주문 건수 내림차순, 누적 금액 내림차순으로 정렬합니다.
Google Sheets 수식 레시피
- 고객 목록
=UNIQUE(A2:A)또는 테이블 구조라면=UNIQUE(Table1!C2:C) - 집계 결합
=LET(c, UNIQUE(C2:C), cnt, BYROW(c, LAMBDA(x, COUNTIF(C2:C, x))), amt, BYROW(c, LAMBDA(x, SUMIF(C2:C, x, D2:D))), f, BYROW(c, LAMBDA(x, MIN(FILTER(A2:A, C2:C=x)))), l, BYROW(c, LAMBDA(x, MAX(FILTER(A2:A, C2:C=x)))), avg, IFERROR(amt/cnt, ), HSTACK(c, cnt, f, l, amt, avg)) - 최신성
기준일이 셀K1이라면=K1 - l을 HSTACK에 추가하면 됩니다.
실무 지향 가이드
- 키 안정성 — 이름은 바뀔 수 있으니 반드시
customer_id를 키로 삼으십시오. 이름을 키로 삼으면 동명이인과 표기 흔들림 때문에 중복이 풀리지 않습니다. - 표 구조 사용 — Excel에서는 표 기능을 활성화하여 구조적 참조를 쓰면 열 이동이나 데이터 추가에도 수식이 깨지지 않습니다.
- 결측 처리 — 금액이 비어 있거나 텍스트가 섞이면 합계가 어긋납니다. 입력 단계에서 숫자 변환과 공백 제거를 먼저 적용하고, 집계에서는
IFERROR로 방어하십시오. - 기준일 문서화 —
recency_days는 기준일에 민감합니다. 기준일 셀을 노출해 협업자가 언제든 변경 가능하도록 하십시오.
검증 루틴
-- 고객 수 검증
=ROWS(UNIQUE(Table1[customer_id])) = COUNTA(고객요약[customer_id])
-- 건수 무결성
=SUM(고객요약[orders_count]) = ROWS(Table1[order_id])
-- 금액 무결성
=SUM(고객요약[total_amount]) = SUM(Table1[amount])
-- 최신성 음수 여부 검사
=COUNTIF(고객요약[recency_days], "<0") = 0문장 자동 생성 예시
요약 표만으로도 보고 문장을 자동 생성할 수 있습니다. 다음 예시는 상위 고객 한 명을 집어 문장화하는 수식입니다.
=LET(
top, TAKE(SORT(고객요약, 6, -1, 7, -1), 1),
id, INDEX(top,,1),
name, INDEX(top,,2),
cnt, INDEX(top,,6),
sumv, INDEX(top,,7),
rec, INDEX(top,,8),
name & " 고객은 최근 " & rec & "일 내 주문을 포함해 총 " & cnt & "건, "
& TEXT(sumv, "#,##0") & "원을 기록했습니다."
)자주 하는 실수와 해결책
- 날짜가 텍스트 —
MINIFSMAXIFS가 기대대로 나오지 않으면 날짜가 텍스트일 수 있습니다. 변환 수식으로=DATEVALUE를 먼저 적용하십시오. - 동적 범위를 고정 셀로 참조 —
UNIQUE결과를H2:H100같은 고정 범위로 잡으면 신규 고객이 누락됩니다.H2#처럼 스필 범위를 사용하십시오. - 정렬 방향 혼동 —
SORT에서 내림차순은-1입니다. 건수와 금액을 동시에 기준으로 걸 때 순서를 명확히 지정해야 합니다.
확장 아이디어
- 활동 고객 정의 —
recency_days가 30 이하인 고객만 필터링한 표를 따로 두면, 캠페인 타깃을 실시간으로 확인할 수 있습니다. - 등급 규칙 — 주문 수와 누적 금액을 기준으로 등급을 산출해 텍스트 라벨을 붙이면 간단한 CRM 등급표가 됩니다. 예를 들어 주문 수 5 이상이면서 누적 금액 상위 20퍼센트는 VIP로 지정합니다.
- 다중 소스 병합 — 채널이 다른 로그를 세트로 합칠 때는 먼저 공통 스키마로 통일한 다음
UNIQUE를 적용하십시오. 헤더 불일치는 오류의 주 원인입니다.
정리하며
고객 단위 요약은 복잡한 대시보드가 없어도 충분히 자동화할 수 있습니다. UNIQUE로 목록을 만들고, COUNTIFS와 SUMIFS, MINIFS, MAXIFS로 핵심 지표를 붙이면 집계가 투명해지고 유지보수가 쉬워집니다. 위의 샘플 CSV를 내려받아 본문의 수식을 그대로 적용해 보시기 바랍니다. 동일한 구조라면 여러분의 작업문서에도 즉시 적용할 수 있습니다.