엑셀 숫자가 합계에서 빠질 때, 값 붙여넣기로도 해결되지 않는 이유

비용 내역을 보고서에 옮길 때는 직접 입력한 금액과 웹에서 복사한 금액이 같은 열에 섞이기 쉽다. 어느 행은 더해지고 어느 행은 빠지면 합계가 0인 경우보다 오히려 알아채기 어렵다. 그럴듯한 총액이 나왔기 때문이다. 수식을 다시 넣기 전에, 빠진 금액 하나를 골라 Excel이 무엇으로 읽고 있는지 살펴보면 수정할 곳을 좁힐 수 있다.

여기서 살펴볼 것은 금액은 보이지만 셀 안에서는 텍스트로 저장된 경우다. 범위를 잘못 잡았거나 계산이 갱신되지 않는 문제와는 고치는 위치가 다르다. 원본 금액을 남긴 채 새 열에서 변환하면 잘못된 변환도 발견하기 쉬워진다.

합계에서 빠진 셀 하나부터 확인한다

먼저 합계 셀을 선택해 수식의 범위에 문제의 행이 들어 있는지 본다. B2부터 B4까지 더하려는데 수식이 =SUM(B2:B3)이면 B4의 저장 형식을 바꿔도 해결되지 않는다. 아래 예시는 범위가 =SUM(B2:B4)로 맞고, 세 금액 가운데 하나만 텍스트로 저장됐다고 가정한다.

세 금액의 합은 4500이지만 이 범위의 SUM은 2300이다. SUM은 참조한 셀 범위 안의 텍스트를 건너뛰기 때문이다. 숫자처럼 보이는 문자가 모든 계산식에서 똑같이 처리된다고 생각하면 진단이 꼬인다. 지금 확인할 대상은 SUM이 읽는 범위의 각 셀이다. Microsoft SUM 설명

B3을 확인하려면 빈 셀에 =ISNUMBER(B3)을 넣는다. TRUE면 숫자이고 FALSE면 숫자가 아니다. 다만 FALSE만으로 텍스트라고 확정할 수는 없다. 빈 셀이나 오류도 있을 수 있으므로 =ISTEXT(B3)도 확인한다. 위 가정에서는 첫 식이 FALSE, 둘째 식이 TRUE여야 한다. 왼쪽·오른쪽 정렬은 사용자가 바꿀 수 있어 이런 판정을 대신하지 못한다. Microsoft IS 함수 설명

몇 행이나 더해졌는지 보려면 =COUNT(B2:B4)도 사용할 수 있다. 이 예시에서는 숫자가 들어 있는 셀이 2개이므로 결과가 2다. 금액 3건을 합산하려던 작업에서 숫자가 2개뿐이라는 사실이 누락을 드러낸다. 실제 금액 0도 숫자로 세므로, 합계 변화만 볼 때 놓칠 수 있는 0원 행도 확인 대상에 넣을 수 있다. Microsoft COUNT 설명

금액 옆에 숫자로 변환할 열을 만든다

텍스트로 저장된 숫자라는 경고가 셀에 표시된다면, 해당 금액 셀을 선택하고 경고 메뉴의 숫자로 변환을 사용할 수 있다. 몇 개의 금액을 바로 고칠 때 편하다. 경고 표시가 안 보인다고 해서 모두 정상 숫자인 것은 아니다. 원본과 결과를 비교해야 하거나 경고 메뉴를 찾기 어렵다면 보조 열을 쓰는 편이 작업 과정을 확인하기 쉽다. Microsoft 숫자 변환 안내

B열을 ‘원본 금액’, C열을 ‘변환 금액’으로 둔다. C2에 =VALUE(B2)를 입력하고 금액이 있는 마지막 행까지 채운다. 중간에 미입력 행이 있다면 =IF(B2="","",VALUE(B2))로 빈값을 남긴다. D열에 =ISNUMBER(C2)를 넣어 같이 내려주면 결과를 숫자로 읽는지도 행마다 볼 수 있다. 실제 파일에서 C·D열이 이미 쓰이고 있다면 먼저 빈 열을 마련한다. 원본인 B열을 바로 덮어쓸 필요는 없다.

VALUE는 Excel이 숫자로 해석할 수 있는 텍스트를 변환한다. 형식을 해석하지 못하면 #VALUE!가 나온다. 이때 오류는 삭제할 잡음이 아니라 ‘이 행을 아직 금액으로 읽지 못했다’는 표시다. C열에 숫자가 나온 행부터 원본과 대조하고, 실패한 행은 다음 절처럼 구분자와 공백을 살핀다. Microsoft VALUE 설명

숫자만 따로 뽑아 놓은 자료에서도 같은 일이 생긴다. 예컨대 수량과 설명이 붙어 있는 셀에서 LEFT로 앞부분을 가져오면, 화면에는 숫자만 남아도 함수는 문자를 추출한 것이다. 그 결과를 ‘값’으로 붙여넣으면 수식은 없어지지만 결과가 텍스트라는 점까지 바뀌지는 않는다. 이미 분리한 수량 셀을 VALUE로 변환해야 계산에 사용할 숫자를 얻는다. Microsoft LEFT 설명

이때 숫자가 몇 자리인지도 확인해야 한다. 앞의 두 글자만 추출한 식에 세 자리 수량이 들어오면, VALUE가 정상적으로 작동해도 잘못 잘린 수량을 변환하게 된다. 숫자로 바뀌었더라도 원래 수량을 온전히 가져왔는지는 따로 확인해야 한다. 금액을 추출한 표라면 소수나 마이너스 금액이 섞인 행도 비교해 본다.

변환할 열을 정할 때는 숫자 모양보다 용도를 본다. 비용이나 수량은 계산하지만 상품 코드 0142는 특정 상품을 찾기 위한 이름이다. 이를 142로 바꾸는 것은 합계 오류를 고치는 일이 아니다. 거래처 코드·주문 번호를 포함해 시트 전체를 선택한 뒤 숫자로 변환하지 말고, 이번 보고서에서 합산할 금액 열만 다룬다.

쉼표가 소수점인 자료도 있다

다른 나라에서 받은 표라면 숫자 사이의 점과 쉼표부터 읽어야 한다. 1.234,50이 1234.50을 뜻하는 자료에서 쉼표를 없애면 소수 부분의 의미가 달라진다. 반대로 1,234.50을 쓰는 자료에 같은 규칙을 적용할 수도 없다. 셀이 텍스트라는 진단이 맞더라도 어떤 숫자로 바꿀지는 원본의 표기 관례에 달려 있다.

소수점이 쉼표, 천 단위 구분자가 점이라는 것을 확인했다면 C2에 =NUMBERVALUE(B2,",",".")를 쓴다. B2 뒤의 첫 구분자는 소수점, 마지막 구분자는 천 단위 표시를 지정한다. 이 규칙의 텍스트 1.234,50은 숫자 1234.5로 해석된다. 변환 뒤 소수 두 자리로 보이게 할지는 셀 표시 형식에서 정할 일이다. Microsoft NUMBERVALUE 설명

표본 하나만 보고 전체 열에 적용하면 위험한 경우도 있다. 두 거래처의 내역을 이어 붙였고 한쪽은 쉼표를 소수점으로, 다른 쪽은 천 단위로 썼다면 출처별로 나눠 처리해야 한다. 특히 1,234처럼 숫자 하나만으로 뜻을 확정하기 어려운 값은 원래 문서의 다른 금액이나 표기 안내와 대조한다. 변환이 오류 없이 끝나도 해석이 틀렸을 수 있다.

금액이 비어 있는 행도 구분한다. NUMBERVALUE는 빈 문자열을 0으로 바꾸므로, 미입력과 실제 0원을 구별하는 표에서는 빈값을 먼저 걸러야 한다. 예를 들어 =IF(B2="","",NUMBERVALUE(B2,",","."))는 B2가 비었거나 빈 문자열일 때 결과도 빈 문자열로 남긴다. 공백 문자만 들어 있는 셀까지 같은 조건으로 걸러지는 것은 아니므로, 빈칸처럼 보이는데 0이 된 행은 원본을 다시 본다.

식을 입력하는 단계에서부터 받아들여지지 않는다면 인수 사이의 구분자도 확인한다. Excel 설정에 따라 =NUMBERVALUE(B2;",";".")처럼 세미콜론으로 인수를 나눠야 할 수 있다. 이때 따옴표 안의 쉼표와 점은 원본 금액의 표기 규칙이므로 그대로 둔다. Microsoft 수식 구분자 안내

자료마다 통화 단위가 다르면 구분자 변환에 앞서 합산할 대상을 정해야 한다. 유로 금액과 원화 금액에서 기호를 떼어낸 뒤 더하면 숫자 합계는 나와도 보고서의 총액으로 쓸 수 없다. 부호나 백분율 표시도 값의 의미를 갖는다. 변환을 통과시키려고 숫자 이외의 문자를 전부 지우는 방식은 피하는 것이 좋다.

공백을 지웠는데도 변환되지 않는다면

웹에서 가져온 값은 숫자 앞뒤에 빈칸이 붙기도 한다. 일반적인 공백을 정리하는 TRIM을 써도 남는 경우가 있는데, 화면에서는 비슷하게 보여도 다른 문자일 수 있기 때문이다. Microsoft는 TRIM이 대상으로 삼는 일반 공백과, 웹에서 쓰이는 줄 바꿈 없는 공백인 NBSP를 구분한다. NBSP의 문자 코드는 160이며 TRIM만으로는 제거되지 않는다. Microsoft TRIM 설명

숫자 앞뒤에 NBSP가 붙은 경우라면 다음 식으로 그 문자를 일반 공백으로 바꾼 뒤 정리할 수 있다. C2에 =VALUE(TRIM(SUBSTITUTE(B2,UNICHAR(160)," ")))를 넣는다. 이 예시는 정리한 뒤의 금액 표기를 현재 Excel이 VALUE로 해석할 수 있다는 조건이다. 소수점 규칙까지 다른 자료라면 앞 절의 NUMBERVALUE 방식과 함께 판단해야 한다.

식 안의 UNICHAR(160)은 특정 문자를 지정하고, SUBSTITUTE는 그 문자만 바꾼다. 모든 숨은 문자나 통화 단위를 한꺼번에 정리하는 식이 아니다. UNICHAR와 SUBSTITUTE의 공식 설명도 각각 문자 생성과 지정 문자열 교체를 다룬다.

이 식을 넣어도 실패하면 원본에서 문제가 있는 셀 하나를 골라 살핀다. 숫자 뒤에 ‘원’ 같은 글자가 붙었는지, 중간에 공백이 있는지, 소수점이 둘 이상 들어갔는지 확인한다. 숫자 사이의 공백은 천 단위 표시일 수도 있지만 서로 다른 항목이 붙은 흔적일 수도 있다. 원본에서 한 금액을 뜻한다는 확인 없이 공백을 전부 없애면, 두 값을 하나의 큰 숫자로 만들 수 있다.

누락된 행까지 더해졌는지 확인한다

처음의 3행 예시로 돌아가면 변환 뒤 =COUNT(C2:C4)는 3, =SUM(C2:C4)는 4500이어야 한다. 원래 SUM이 2300이었던 이유가 텍스트 2200 한 건이었다면, 증가한 금액도 2200으로 설명된다. 이것처럼 어떤 행이 새로 포함됐는지를 확인해야 숫자가 커진 것을 곧바로 성공으로 받아들이지 않게 된다.

실제 표에서는 숫자 개수가 원래 금액이 있어야 할 행 수와 맞는지 보고, 원본과 변환 열에서 금액의 부호·소수점·단위를 비교한다. 코드나 설명 행까지 포함한 전체 행 수를 기준으로 삼으면 안 된다. 비어 있어야 하는 행과 실제 0원 행도 구별해 두면, 다음번 자료를 받았을 때 미입력을 0원으로 집계하는 실수를 찾기 쉬워진다.

변환 열의 오류를 IFERROR(...,0)으로 덮어 합계를 만들면 이번 문제는 겉으로만 사라질 수 있다. 못 읽은 금액을 0원으로 바꿔 버리기 때문이다. 보고서 마감 전까지 확인되지 않은 행은 별도로 남겨 원본 제공자에게 물을 수 있어야 한다. 합계 셀에서 오류가 보이지 않는 것보다, 아직 포함하지 못한 금액이 무엇인지 아는 편이 정산에 도움이 된다.

검증이 끝났다면 보고서는 C열의 변환 금액을 참조하게 하거나, 그 결과를 별도 확정 열에 ‘값’으로 붙여넣어 보관한다. 이때의 값 붙여넣기는 이미 숫자로 변환된 결과를 고정하는 작업이다. 처음에 텍스트 결과를 그대로 붙여넣었던 때와는 출발점이 다르다. 보조 수식을 지울 계획이라면 결과를 값으로 보관한 뒤 원본과 다시 대조한다.

다음 달에도 같은 내역을 붙여넣는다면 마지막 행까지 변환식과 합계 범위가 이어졌는지 확인한다. 지난달의 금액을 고친 것과 새로 들어온 금액까지 처리하는 것은 별개다. 원본 금액·변환 금액·확인 결과를 나란히 남겨 두면, 합계가 다시 어긋났을 때 어느 행부터 살펴봐야 할지 알 수 있다.