피벗 합계가 0으로 나올 때: ‘숫자처럼 보이는 텍스트’ 안전하게 고치는 법

거래처에서 받은 매출 파일을 열었는데 금액은 분명히 보입니다. 그런데 피벗테이블에 넣으면 합계가 아니라 개수로 잡히거나, SUMIFS 결과가 0으로 떨어지는 경우가 있습니다. 더 난감한 건 화면상으로는 125,000처럼 멀쩡한 숫자라서 어디가 문제인지 바로 보이지 않는다는 점입니다.

실무에서는 이 문제가 꽤 자주 터집니다. 특히 ERP, 쇼핑몰 관리자, 카드 매출 시스템, 물류 프로그램에서 내려받은 CSV 파일은 숫자처럼 보이지만 실제로는 텍스트인 경우가 많습니다. 오늘은 이 상황에서 억지로 수식을 덧씌우기보다, 원인을 확인하고 안전하게 고치는 흐름으로 정리해 보겠습니다.

실수 사례: 금액 열이 왼쪽 정렬인데 그냥 피벗을 만든 경우

아래처럼 매출 원장을 받았다고 해보겠습니다. 금액 열에는 쉼표도 있고, 일부 행에는 공백도 섞여 있습니다. 눈으로 보면 전부 숫자처럼 보이지만 실제 저장 형식은 다를 수 있습니다.

이 상태에서 피벗테이블을 만들면 금액 필드가 자동으로 ‘합계’가 아니라 ‘개수’로 들어가는 일이 있습니다. 또는 금액 필드를 값 영역에 넣었는데 합계가 이상하게 작게 나오기도 합니다. 대부분은 엑셀이 그 열을 숫자가 아닌 텍스트로 보고 있기 때문입니다.

왜 문제가 되는지: 보이는 값과 계산되는 값은 다릅니다

엑셀에서 셀에 125,000이 보인다고 해서 반드시 숫자 125000인 것은 아닙니다. 셀 안에 보이지 않는 공백이 있거나, 쉼표가 텍스트의 일부로 저장되어 있거나, 마이너스 기호가 일반 하이픈이 아닌 다른 문자로 들어가 있으면 계산 대상에서 빠질 수 있습니다.

특히 아래 항목은 현장에서 자주 만나는 원인입니다.

• CSV에서 내려온 금액이 처음부터 텍스트로 저장된 경우
• 숫자 앞뒤에 일반 공백 또는 줄바꿈 공백이 섞인 경우
• 웹에서 복사한 데이터에 보이지 않는 공백, 즉 CHAR(160)이 들어간 경우
• 날짜가 2026.06.01, 06/01/2026처럼 지역 설정과 맞지 않는 형식인 경우
• 금액에 원, VAT포함, - 같은 문자가 섞여 있는 경우

흔한 실수는 초록색 오류 표시가 보일 때 무조건 ‘숫자로 변환’을 누르는 것입니다. 작은 파일에서는 괜찮을 때도 있지만, 원본 열에 취소 금액, 빈 행, 메모성 문자가 섞여 있으면 일부 값만 바뀌고 일부는 그대로 남습니다. 그 상태에서 피벗을 만들면 더 찾기 어려운 오류가 됩니다.

안전한 처리법: 원본을 보존하고 변환 열을 따로 만듭니다

가장 안전한 방식은 원본 열을 그대로 두고, 옆에 계산용 열을 새로 만드는 것입니다. 원본을 바로 덮어쓰면 나중에 값이 왜 바뀌었는지 추적하기 어렵습니다. 특히 회계, 정산, 재고 파일에서는 원본 보존이 생각보다 중요합니다.

먼저 금액 열이 실제 숫자인지 간단히 확인합니다. 예를 들어 금액이 D열이라면 옆 열에 아래 수식을 넣어 봅니다.

=ISNUMBER(D2)

TRUE가 나오면 숫자, FALSE가 나오면 숫자처럼 보이는 다른 값입니다. 범위 전체에 텍스트가 얼마나 섞였는지 보려면 아래처럼 확인할 수 있습니다.

=SUMPRODUCT(--ISTEXT(D2:D5000))

금액 안에 쉼표와 불필요한 공백이 섞여 있다면 계산용 금액 열에 다음 수식을 사용할 수 있습니다.

=IFERROR(VALUE(SUBSTITUTE(SUBSTITUTE(TRIM(D2),CHAR(160),""),",","")),0)

여기서 핵심은 TRIM만 믿지 않는 것입니다. TRIM은 일반 공백에는 강하지만 웹 데이터에 자주 붙는 CHAR(160) 공백은 그대로 남는 경우가 있습니다. 그래서 SUBSTITUTE로 CHAR(160)을 한 번 더 제거해 주는 편이 안전합니다.

점검 포인트: 변환 후 합계가 맞는지 반드시 대조합니다

변환 수식을 넣었다면 바로 피벗으로 넘어가지 말고, 원본 금액 열과 변환 금액 열을 비교해야 합니다. 실무에서 많이 놓치는 부분이 바로 이 검산입니다.

예를 들어 원본 금액이 텍스트라도 단순히 쉼표를 제거하면 대부분 숫자로 바뀝니다. 하지만 ‘취소’, ‘반품’, ‘-’, 빈칸 같은 예외값은 0으로 처리될 수 있습니다. IFERROR로 0을 반환하게 해두면 보고서가 깔끔해 보이지만, 오류값이 조용히 사라지는 문제가 있습니다.

그래서 아래처럼 오류 여부를 따로 표시하는 열을 하나 더 두면 좋습니다.

=IF(AND(D2<>"",E2=0),"확인 필요","")

위 예시는 D열이 원본 금액, E열이 변환 금액일 때 사용할 수 있습니다. 원본에 뭔가 들어 있는데 변환 결과가 0이면 사람이 한 번 보자는 의미입니다. 모든 오류를 수식이 알아서 해결하게 만들기보다, 확인해야 할 행을 줄여주는 쪽으로 설계하는 것이 실무에서는 더 안전합니다.

Power Query로 처리하면 매달 반복 작업이 줄어듭니다

매달 같은 형식의 파일을 받는다면 수식보다 Power Query가 더 편합니다. 한 번 정리 규칙을 만들어 두면 다음 달 파일은 새로고침만으로 처리할 수 있습니다. 특히 금액 열의 쉼표 제거, 공백 제거, 데이터 형식 변경을 순서대로 기록할 수 있어서 재현성이 좋습니다.

Power Query 편집기에서는 금액 열을 선택한 뒤, 변환 탭에서 형식을 바로 숫자로 바꾸기 전에 먼저 텍스트 정리를 해주는 것이 좋습니다. 공백 제거, 정리, 값 바꾸기 순서로 진행한 뒤 마지막에 정수 또는 십진수로 변환합니다.

let
    원본 = Excel.CurrentWorkbook(){[Name="매출원장"]}[Content],
    금액텍스트정리 = Table.TransformColumns(
        원본,
        {{"금액", each Text.Replace(Text.Trim(Text.Clean(Text.From(_))), ",", ""), type text}}
    ),
    금액숫자변환 = Table.TransformColumnTypes(
        금액텍스트정리,
        {{"금액", Int64.Type}}
    )
in
    금액숫자변환

다만 이 코드도 만능은 아닙니다. 금액에 ‘원’이라는 문자가 붙어 있거나, 괄호로 음수를 표시한 데이터라면 변환 전에 규칙을 추가해야 합니다. 예를 들어 (125,000)을 -125000으로 봐야 하는 정산 파일이라면 괄호 처리 규칙을 먼저 정해야 합니다.

날짜 열도 마찬가지입니다. 06/07/2026이 6월 7일인지, 7월 6일인지는 파일을 만든 시스템의 지역 설정에 따라 달라질 수 있습니다. Power Query에서는 데이터 형식을 바꿀 때 ‘로캘 사용’을 활용하면 이런 오류를 줄일 수 있습니다.

자동화 팁: 새로고침 버튼 하나로 검산까지 이어지게 만들기

정리된 쿼리 결과를 표로 불러온 뒤, 피벗테이블의 원본을 그 표로 연결해 두면 흐름이 단순해집니다. 파일 교체 또는 원본 표 붙여넣기 후 새로고침만 하면 정리된 데이터와 피벗 보고서가 같이 갱신됩니다.

조금 더 자동화하고 싶다면 통합 문서의 모든 쿼리와 피벗을 한 번에 새로고침하는 매크로를 버튼에 연결할 수 있습니다.

Sub 전체_새로고침()
    ThisWorkbook.RefreshAll
    MsgBox "데이터와 보고서 새로고침이 완료되었습니다."
End Sub

이때 주의할 점은 새로고침 직후 피벗테이블이 바로 계산되지 않는 파일도 있다는 것입니다. 데이터 양이 많거나 외부 연결이 포함된 경우에는 쿼리 새로고침이 끝난 뒤 피벗 새로고침이 따라오도록 설정을 확인해야 합니다. 피벗테이블 옵션에서 파일 열 때 데이터 새로고침, 원본 범위가 표 이름으로 잡혀 있는지도 함께 봐야 합니다.

다음 파일 받기 전에 확인할 것

금액이 왼쪽 정렬되어 있거나, 피벗 값 필드가 합계가 아닌 개수로 들어가거나, SUMIFS 결과가 0이면 서식을 바꾸기 전에 실제 데이터 형식부터 확인해 보세요. 원본 보존, 변환 열 분리, 오류 행 표시, Power Query 새로고침 흐름까지 잡아두면 매달 같은 문제를 훨씬 덜 보게 됩니다.

실무 체크는 짧게 잡으면 됩니다. 원본 열은 그대로 두었는지, 변환 후 합계가 원장 합계와 맞는지, 예외값이 조용히 0으로 묻히지 않았는지. 이 세 가지만 확인해도 피벗 보고서의 숫자 신뢰도가 꽤 올라갑니다.