구글시트 QUERY 집계 검증: 숫자·날짜·빈칸 때문에 사라진 행 찾기

AI Editter

QUERY 결과가 나오고 합계도 계산됐는데 원본과 금액이 다르다면, 조건식보다 열의 데이터 형식을 먼저 확인해 볼 필요가 있습니다. 구글시트 QUERY는 한 열에 여러 형식이 섞이면 다수 형식을 기준으로 판단하고 소수 형식은 null로 취급할 수 있습니다. Google QUERY 문서에 명시된 동작입니다.

따라서 “오류 메시지가 없다”는 것만으로 모든 행이 정상 집계됐다고 볼 수 없습니다. 원본을 보존한 채 형식과 제외 사유를 확인하는 과정이 필요합니다.

6,000원이 4,000원으로 집계되는 예제

아래 가상 자료를 A1:C6에 넣습니다. R-02의 금액은 앞에 작은따옴표를 붙여 '2000으로 입력해 텍스트로 만듭니다. 셀에는 2000처럼 보이지만 숫자와 다릅니다.

기록번호	부서	금액
R-01	영업	1000
R-02	영업	'2000
R-03	영업	3000
R-04	영업	0
R-05	영업	보류

업무상 확인된 금액은 1,000+2,000+3,000+0=6,000원이고 R-05는 미정입니다. 금액 열에 숫자 세 개와 문자 두 개가 있으므로 다음 QUERY에서는 문자로 들어간 R-02가 합계에서 빠지는 사례를 확인할 수 있습니다.

=QUERY(A1:C6,"select B,sum(C) group by B label sum(C) '합계'",1)

여기서 보류를 0으로 바꾸면 6,000원이라는 숫자는 만들 수 있어도 미확정 기록이 있다는 사실을 잃습니다. “확정 금액 합계”와 “미확정 건수”를 함께 표시해야 합니다.

보조열로 원본 형식을 드러낸다

D열을 원본형식으로 만들고 D2에 아래 수식을 넣어 실제 데이터 범위까지 복사합니다.

=IF(C2="","빈칸",IF(ISNUMBER(C2),"숫자","문자"))

E열에는 정제금액을 둡니다. 다음 식은 빈칸과 변환 실패를 문자 상태로 남기는 예시입니다.

=IF(C2="","미입력",IF(ISNUMBER(C2),C2,IFERROR(VALUE(C2),"변환불가")))

이때 VALUE가 해석하는 구분자와 날짜 형식은 지역 설정의 영향을 받을 수 있습니다. 모든 문자를 무조건 숫자로 변환하는 정책으로 쓰지 않습니다. 통화 기호, 괄호 음수, 천 단위와 소수점이 섞인 원본은 원본 시스템의 표기 규칙을 확인한 뒤 변환합니다.

F열에는 집계상태를 둡니다.

=IF(ISNUMBER(E2),"집계 가능","확인 필요")

미입력과 변환불가를 합계에서 조용히 제외하지 말고 별도 목록으로 보여 줍니다. 0원은 유효한 숫자이므로 집계 가능에 포함됩니다.

정제된 숫자만 집계하고 예외 건수를 함께 표시

아래 예시는 A2:A6에 기록번호가 모두 있다는 전제입니다. 부서와 정제금액 두 열을 배열로 만든 뒤 숫자만 집계합니다.

=QUERY(
 FILTER({B2:B6,E2:E6},ISNUMBER(E2:E6)),
 "select Col1,sum(Col2) group by Col1 label sum(Col2) '확정금액'",
 0
)

예제의 기대 결과는 영업 6,000원입니다. 확인 필요 건수는 =COUNTIF(F2:F6,"확인 필요")로 1건이어야 합니다. 데이터가 전부 예외인 경우 FILTER는 일치하는 값이 없어 오류를 낼 수 있으므로, 운영 보고서에서는 집계 가능 건수를 먼저 확인하고 0건 상태를 명시합니다. 연결 오류나 수식 오류를 실제 매출 0으로 대체하지 않습니다.

헤더 인수를 생략하면 내용으로 추정되므로, 실제 머리글 행 수를 명시하는 편이 재현하기 쉽습니다. 범위에 머리글이 포함되면 1, 데이터만 넘겼다면 0을 사용합니다.

날짜도 표시 형식만으로 판단하지 않는다

2026-10-01이라는 모양의 문자와 실제 날짜값은 다릅니다. ISNUMBER로 기초 확인한 뒤, 원본이 어떤 날짜 형식인지 검증합니다. 월/일 순서가 모호한 10/11/2026은 지역 설정만 믿고 변환하지 않습니다.

날짜형으로 정제한 열을 QUERY에서 비교할 때는 쿼리 언어의 날짜 리터럴 형식을 사용합니다. 아래 날짜 예제는 앞의 금액 검증 표와 별개인 Dates 시트를 사용하며, D열에 날짜값이 있다고 가정합니다.

=QUERY(Dates!A1:D100,
"select A,B,D where D >= date '2026-10-01' and D < date '2026-11-01'",
1)

이 예시는 D열이 날짜형이라는 전제입니다. 날짜시간을 다룬다면 열의 실제 유형과 시간대까지 확인하고 그에 맞는 비교를 사용합니다. 문법의 기준은 Google Visualization Query Language입니다.

원본과 보고서를 맞추는 네 가지 통제값

첫째는 기록 수입니다. 원본 기록 수가 집계 가능 건수와 확인 필요 건수의 합으로 돌아와야 합니다. 둘째는 식별자입니다. 기록번호가 중복되거나 빈 행은 별도 오류로 봅니다.

셋째는 금액입니다. 원본 시스템이 제공하는 통제 합계와 정제 후 확정 금액을 비교합니다. 정제 금액을 두 곳에서 더한 값이 같다는 것은 원본과 일치한다는 증거가 아닙니다.

넷째는 기간입니다. 시작 경계, 종료 경계, 빈 날짜, 다음 달 기록을 하나씩 넣어 포함·제외 결과를 시험합니다. 새 행을 추가했을 때 범위가 확장되는지도 확인합니다.

원본 값과 정제 값을 함께 남겨 두면 담당자가 R-02의 2,000원이 왜 처음에는 빠졌고 나중에는 포함됐는지 설명할 수 있습니다. QUERY 수식을 더 복잡하게 만드는 것보다, 집계에 들어오기 전 자료의 상태를 드러내는 편이 유지보수하기 쉽습니다.