엑셀 합계가 화면의 숫자를 더한 값과 다를 때
소수점 두 자리로 표시한 숫자들을 더했는데 SUM 합계와 0.01 차이 난다면, 표시 형식과 계산식 중 무엇을 바꿔야 할까?
보고서에 보이는 숫자를 계산기로 더했는데 Excel 합계와 다르다면, 각 숫자의 보이지 않는 소수 자릿수가 계산에 들어갔을 수 있다. 보고서에 표시한 각 항목을 확정 금액으로 쓸 때는 행마다 반올림한 뒤 그 값들을 더한다. 원본의 총액이 필요한 보고서라면 원본을 모두 합친 뒤 반올림한다. 두 계산은 결과가 다를 수 있으며, 합계 셀의 표시 자릿수만 바꿔서는 반올림하는 순서가 바뀌지 않는다.
이 문제는 금액 표에만 생기지 않는다. Microsoft Q&A에는 열의 합계가 1페니 다르다는 질문도 있고, 화면에 보이는 점수들을 더하면 242인데 합계 셀에는 241이 나온다는 질문도 있다. 다만 숫자가 조금 다르다는 증상만으로 원인을 확정할 수는 없다. 실제 파일에서 표시 자릿수와 계산식을 함께 확인해야 한다. 금액 합계 질문, 점수 반올림 질문
표시하지 않은 소수 자릿수 확인하기
숫자 셀에 1.125를 넣고 소수점 이하 두 자리만 보이도록 하면 화면에는 1.13이 나온다. 기본 설정에서는 그 셀의 계산값이 1.13으로 바뀐 것이 아니다. 다른 수식이 이 셀을 참조하면 여전히 1.125를 사용한다. Excel의 일반적인 계산 기준은 표시값이 아닌 저장값이다. Excel 계산 정밀도 설명
수정 전 파일을 복사한 다음, 차이가 나는 합계와 그 합계가 참조하는 숫자 셀들을 선택한다. 홈 탭의 소수 자릿수 늘리기로 두 자리 뒤에 숫자가 더 있는지 본다. 셀 서식의 숫자 범주에서 소수 자릿수를 늘려도 된다. 숫자 대신 ####가 보이면 열 폭을 넓혀 결과를 읽는다. 한 셀만 보지 말고 개별 값과 합계를 같은 자릿수로 놓고 비교한다. 반올림되어 보이는 숫자 확인
직접 입력한 숫자는 셀을 선택한 뒤 수식 입력줄에서도 확인할 수 있다. 반면 단가와 수량을 곱하는 수식이 들어 있다면 수식 입력줄에는 계산 결과 대신 식이 나온다. 이때는 셀의 표시 자릿수를 늘려 결과를 읽고, 수식 안에 ROUND가 이미 있는지도 살핀다. 이미 두 자리로 반올림한 결과에 같은 ROUND를 다시 적용해도, 행별로 반올림할지 합계에서 반올림할지를 바꾸지는 못한다.
검사 중에는 합계가 포함하는 첫 행과 마지막 행도 확인한다. 같은 범위를 더하고 있는지 먼저 맞춰야 한다. 일부 행이 빠진 합계를 반올림해도 빠진 숫자가 돌아오지는 않는다. 표시 자릿수만 살피다 보면 합산에서 빠진 행을 놓칠 수 있다.
같은 세 숫자로 6.38과 6.39가 나오는 이유
차이를 분리해서 보기 위해 빈 시트의 A2, A3, A4에 각각 1.125, 2.125, 3.125를 입력하는 예제를 생각해 보자. 세 셀을 소수점 두 자리로 표시하면 1.13, 2.13, 3.13이다. 이 세 표시값을 계산기로 더하면 6.39다. 하지만 원래 숫자의 합은 6.375이고, 이를 두 자리로 반올림하면 6.38이 된다.
| 계산 대상 | 계산 과정 | 결과 |
|---|---|---|
| 원본 숫자 | 1.125 + 2.125 + 3.125 | 6.375 |
| 각 행을 두 자리로 반올림한 숫자 | 1.13 + 2.13 + 3.13 | 6.39 |
| 원본 합계만 두 자리로 반올림 | 6.375를 두 자리로 반올림 | 6.38 |
=ROUND(SUM(A2:A4),2)는 안쪽 SUM이 먼저 원본을 더하고, 바깥쪽 ROUND가 합계에 적용되는 식이다. 개별 행을 먼저 반올림하는 식이 아니다. 반대로 각 행에서 =ROUND(A2,2)처럼 계산한 값을 모아 더하면 두 번째 줄의 결과가 나온다. ROUND의 두 번째 인수 2는 소수점 이하 두 자리까지 반올림한다는 뜻이다. ROUND 함수의 인수와 동작
이 예제의 0.01은 세 항목을 각각 반올림한 뒤 더할 때와, 합계에서 한 번만 반올림할 때 생기는 차이다. 한쪽 결과를 다른 쪽에 맞추려고 마지막 행에 0.01을 빼 넣으면 원래 항목의 값이 달라진다. 먼저 표가 어느 계산을 보여줘야 하는지 결정해야 한다.
두 방식의 차이가 늘 0.01인 것도 아니다. 행 수와 각 숫자의 소수 부분에 따라 차이가 없어질 수도, 커질 수도 있다. 따라서 모든 합계 수식 뒤에 0.01을 더하는 보정은 다음 달 데이터에 그대로 쓸 수 없다. 비교해야 할 것은 고정된 차액이 아니라 반올림을 적용하는 위치다.
원본을 남기고 행마다 반올림한 값 더하기
제출할 표에서 각 행의 두 자리 숫자가 확정된 값이고, 합계도 그 숫자들을 더한 값이어야 한다면 보조 열을 만든다. A열에 원본을 남기고 B열을 ‘행별 반올림 값’으로 두면 어느 단계에서 숫자가 바뀌었는지 바로 볼 수 있다. 아래 과정은 앞의 세 행 예제를 그대로 사용한다.
- A1에는 ‘원본 값’, B1에는 ‘행별 반올림 값’을 적는다. A2:A4는 1.125, 2.125, 3.125로 유지한다.
- B2에
=ROUND(A2,2)를 입력하고 B4까지 채운다. B3과 B4가 각각 A3과 A4를 참조하는지 확인한다. - B5에
=SUM(B2:B4)를 넣는다. B2:B5를 소수점 두 자리 숫자로 표시하면 개별 값은 1.13·2.13·3.13, 합계는 6.39다. - 비교용 셀 D2에
=ROUND(SUM(A2:A4),2)를 넣고 ‘원본 합계 반올림’이라고 표시한다. 결과는 6.38이다. - D3에
=ROUND(B5-D2,2)를 넣고 ‘반올림 순서에 따른 차이’라고 적는다. 이 예제에서는 0.01이다.
D3의 두 자리 반올림은 이 예제의 두 자리 결과를 비교하기 위한 것이다. 모든 데이터의 차이를 두 자리에서 없애라는 뜻이 아니다. 네 자리 정밀도가 필요한 측정 자료에서 같은 비교식을 무조건 쓰면 확인해야 할 차이까지 0으로 보일 수 있다.
실제 표에 적용할 때는 보조 열을 마지막 데이터 행까지 채우고, 그 열의 합계를 제출용 합계 셀에 연결한다. 원본 A열 합계가 그대로 연결되어 있으면 보조 열을 만들었어도 보고서 값은 달라지지 않는다. ‘행별 반올림 값의 합’이라는 열 제목이나 주석을 남기면 다음 사람이 원본 합계와 대조했을 때 차이를 추적할 수 있다.
원본으로 평균이나 비율을 다시 계산할 일이 있으면 A열을 계속 사용한다. 제출용으로 확정한 행별 값의 통계를 구해야 할 때만 B열을 참조한다. 둘을 같은 이름으로 두거나 A열에 B열 결과를 덮어쓰면 후속 계산에서 어느 값을 사용했는지 알아보기 어렵다. 원본·반올림 결과·보고서 합계를 나누는 이유가 여기에 있다.
보고서 단위와 반올림 시점을 정한다
원 단위 정수를 요구하는 보고서에서는 ROUND의 자릿수 인수를 0으로, 소수점 두 자리가 필요한 외화 표에서는 2로 쓴다. 같은 원본이라도 제출 양식이 원 단위인지 천 원 단위인지에 따라 먼저 단위를 환산해야 할 수 있다. 각 항목을 확정한 뒤 더할지, 총액만 환산해 반올림할지도 양식의 산정 기준을 따른다.
가상의 보고서에 12,400원 두 건이 있다고 하자. ‘천 원’ 단위로 각 행을 정수 표시하면 둘 다 12로 보이므로 독자가 더한 값은 24다. 원본 총액 24,800원을 천 원으로 바꾼 뒤 정수로 반올림하면 25다. 이 차이는 앞의 0.01 사례와 같은 구조지만, 보고서 단위에서는 1천 원 차이로 보인다.
| 보고서가 요구하는 값 | 원 단위 A열에서의 계산 | 예제 결과 |
|---|---|---|
| 천 원 단위로 확정한 각 항목의 합 | 각 행에 ROUND(A2/1000,0), 그 결과 열을 합산 | 12 + 12 = 24 |
| 원본 총액을 천 원 단위로 반올림 | ROUND(SUM(A2:A3)/1000,0) | 25 |
보고서가 원본 총액을 요구한다면 25가 맞고, 개별 표시 항목의 합을 요구한다면 24가 맞다. 이 조건을 확인하지 않고 ‘눈에 보이는 대로 맞추기’만 선택하면 원본 총액과 대조할 때 다른 문제가 생긴다. 총액 기준 보고서에는 반올림 때문에 항목 합과 총액이 다를 수 있다는 주석을 두거나, 양식이 허용하면 항목의 표시 자릿수를 늘려 차이를 설명할 수 있다.
단가와 수량을 곱하는 표도 반올림 위치를 확인한다. 원래 단가에 수량을 곱한 뒤 행 금액을 확정하는지, 단가부터 반올림한 뒤 수량을 곱하는지에 따라 결과가 달라진다. 예를 들어 1.125에 수량 8을 곱하면 9지만, 먼저 단가를 1.13으로 만들고 8을 곱하면 9.04다. 화면에 보이는 단가를 기준으로 식을 다시 만드는 것이 항상 원래 계산을 보존하는 것은 아니다.
표시된 정밀도 설정은 무엇을 바꾸나
Windows 데스크톱 Excel의 파일 → 옵션 → 고급 → 이 통합 문서의 계산 대상에는 표시된 정밀도로 계산(Set precision as displayed)이 있다. 이는 합계 셀에만 ROUND를 넣는 기능과 범위가 다르다. 통합 문서의 계산 정밀도를 표시 형식에 맞추는 설정이다. Microsoft는 이 옵션이 누적 계산의 정확성을 떨어뜨릴 수 있다고 안내한다. 표시 정밀도 옵션과 주의점
이 설정으로 저장값에서 사라진 자릿수는 옵션을 다시 꺼도 복원되지 않는다. 다른 시트에 소수 자릿수를 적게 표시한 단가나 비율이 있다면 그 원본도 바뀔 수 있다. 원본이 필요하다면 이 옵션은 꺼 둔다. A열을 보존한 채 B열에서 ROUND로 보고서 금액을 만들면, 이후 단가나 평균을 다시 계산할 때 원래 숫자를 계속 쓸 수 있다.
이미 이 옵션을 켠 파일이라면 복사본부터 보존하고, 변경 전 파일이나 원래 받은 데이터와 비교한다. 표시 자릿수를 늘렸는데 끝에 0만 붙는다고 해서 원래 숫자가 정수였다고 단정할 수는 없다. 원본이 변경된 뒤라면 표시 형식만 바꿔 잃은 자릿수를 찾아낼 수 없다. 복구할 근거는 변경 전 데이터에 있다.
반올림해도 합계가 맞지 않을 때
소수점 두 자리 문자열을 만들려고 =TEXT(A2,"0.00")를 쓴 경우에는 값의 종류부터 다르다. TEXT 결과는 숫자가 아닌 텍스트다. 계산용 보조 열에는 ROUND를 쓰고, TEXT는 문장이나 표시용 문구를 만들 때 사용한다. TEXT 함수가 반환하는 값
SUM은 참조 범위 안의 텍스트를 합산에서 제외한다. B2:B4가 모두 TEXT 결과라면 숫자처럼 보이더라도 그 범위를 더한 값은 기대와 달라진다. 반올림된 숫자가 들어 있는 열을 합계가 참조하는지 다시 확인한다. 새 행을 기존 합계 범위 바깥에 추가했다면 마지막 행까지 포함되었는지도 확인해야 한다. SUM의 텍스트 처리와 범위
아주 작은 소수점 잔차는 별도의 문제다. Excel은 이진 부동소수점 방식으로 수를 저장·계산하므로 일부 십진 소수를 정확히 표현하지 못한다. 하지만 앞서 만든 6.38과 6.39의 차이는 반올림 순서만으로 설명된다. 모든 합계 차이를 부동소수점 오차로 묶으면 어떤 수식을 선택해야 할지 놓친다. 부동소수점 계산의 한계
제출 전에는 원본 합계, 행별 반올림 합계, 실제 보고서에 연결된 합계를 나란히 놓는다. 원본 숫자를 바꾸지 않았는지, 모든 행에 같은 기준이 적용됐는지, 보고서의 단위와 자릿수가 맞는지 확인한다. 행별 확정 금액을 제출하는 표라면 보고서 합계는 보조 열의 합과 일치해야 한다. 원본 총액을 제출하는 표라면 원본 합계에 정해진 단위 변환과 반올림을 적용한 값과 일치해야 한다.