들어가기 전에
엑셀의 가장 위험한 오류는 ‘침묵의 수식 오류’로, 수식은 정상처럼 보이지만 잘못된 값을 계산할 수 있습니다. 수식 안에 숫자를 직접 입력하는 하드코딩, 잘못된 셀 참조, 수동 계산 모드 사용 등이 대표적인 원인입니다. 오류 검사 도구를 활용하고, 자동 계산 모드 유지하며, 데이터 정확성을 정기적으로 점검할 것을 권장합니다.
이 글은 아래 기사 내용을 토대로 작성되었습니다만, 필자의 개인 의견이나 추가 자료들이 다수 포함되어 있습니다.
- 원문: Your Excel formulas are silently breaking your data—here's how to fix them
- URL: https://www.howtogeek.com/microsoft-excel-silent-formula-errors-breaking-data-how-to-fix/
계산을 왜곡할 수 있는 상대 참조
수식을 작성하고 채우기 핸들을 사용하여 열 아래로 복사하면 Excel은 셀 참조의 행 번호를 자동으로 증가시킵니다. 이는 품목별 합계를 계산할 때 유용하지만, 세율, 할인율 또는 배송비와 같이 고정된 단일 변수를 참조해야 하는 수식의 경우에는 계산이 제대로 되지 않습니다. 예를 들어, 다음 수식을 복사합니다.
=B4*B1
한 줄 아래로 내려가면 다음과 같이 바뀝니다.
=B5*B2
B2 셀이 비어 있으면 Excel은 이를 0으로 처리하므로 명시적인 오류를 발생시키는 대신 수식에서 잘못된 계산 결과가 반환됩니다.
이 문제를 해결하려면 셀 참조 방식을 변경해야 합니다.
- 수식 입력줄 에서 고정해야 하는 특정 셀 참조를 선택합니다.
- 좌표 주위에 달러 기호를 삽입하려면 F4 키를 한 번 누릅니다.
- Ctrl+ Enter 키 를 눌러 수식을 적용하고 해당 셀을 선택한 상태로 유지합니다.
- 채우기 핸들을 아래로 드래그하여 잠긴 수식을 열의 나머지 부분에 적용합니다.
업데이트가 완료되면 수식은 해당 열 전체에서 올바른 값을 계속 참조하게 됩니다.
검색 및 논리적 일치를 방해하는 숨겨진 공백
앞뒤 공백은 합계나 평균과 같은 일반적인 수학 함수에는 영향을 미치지 않습니다. Excel은 일반적으로 숫자 계산 시 공백을 무시하기 때문입니다. 하지만 텍스트 기반 연산 , 논리 테스트 및 조회는 데이터를 문자 그대로 평가합니다.
엄밀히 말하면 수식 오류는 아니지만, 잘못된 입력값을 사용했을 때 수식이 올바르게 평가되는 데이터 문제입니다. 스프레드시트로 데이터를 가져올 때 흔히 발생하는 것처럼 단어 앞뒤에 보이지 않는 공백이 있으면 Excel은 이를 완전히 다른 구문으로 인식합니다. 만약 다음과 같은 논리 테스트를 작성한다면 어떨까요?
=IF(B2="Completed", "Manager check", "No action required")
드래그하여 한 열 아래로 이동시켰는데, 대상 셀에 "Completed"(앞에 공백 포함)라는 내용이 있는 경우, Excel은 경고나 오류 표시 없이 잘못된 결과를 반환합니다.
텍스트 문자열에서 숨겨진 앞뒤 공백을 제거하려면 TRIM 함수를 사용하세요. TRIM 함수를 사용하면 불필요한 공백들이 제거되므로 논리가 예상대로 작동합니다.
TRIM 함수는 일반적인 앞뒤 공백을 제거하지만, 줄 바꿈 방지 공백이나 외부 소스에서 가져온 기타 숨겨진 문자는 제거하지 않습니다.
레이아웃이 변경되면 제대로 작동하지 않는 하드코딩된 컬럼 인덱스
기존 조회 함수는 Excel에 데이터를 가져올 열을 알려주기 위해 고정된 인덱스 번호를 하드코딩해야 하므로 구조적 변경에 매우 취약합니다. 예를 들어, 다음과 같은 수식이 있습니다.
=VLOOKUP(E2, A2:B8, 2, FALSE)
Excel은 A열을 검색하고 해당 범위의 두 번째 열인 B열에서 해당 값을 반환합니다. 하지만 나중에 새 내부 열을 삽입하면 실제로 필요한 데이터는 세 번째 열로 이동합니다. 문제는 수식이 이러한 물리적 레이아웃 변경에 맞춰 조정되지 않고 계속 두 번째 열의 값을 가져와 잘못된 정보를 표시한다는 것입니다.
이 문제는 스프레드시트를 XLOOKUP 함수로 업그레이드 하면 해결할 수 있습니다(INDEX + MATCH 함수 조합으로도 가능합니다). XLOOKUP 함수는 열 개수를 세는 대신 특정 원본 및 반환 범위를 지정합니다.
=XLOOKUP(F2, A2:A8, C2:C8)
고정된 열 인덱스 대신 별도의 조회 범위와 반환 범위를 사용하기 때문에 VLOOKUP의 구조적 취약성을 피하고 시트가 변경되더라도 올바른 필드를 계속 반환합니다.
구조적 계산 오류를 숨기는 포괄적인 오류 처리
IFERROR 문 으로 수식을 감싸는 것은 워크시트에 오류 코드가 쌓이기 시작할 때 빠르게 정리하는 방법입니다. 하지만 문제는 IFERROR 문이 모든 오류를 동일하게 처리한다는 것입니다. 즉, 예상되는 조회 실패와 스프레드시트 구조적 오류를 구분하지 못합니다.
이는 IFERROR가 실제로 확인해야 할 문제를 숨길 때 위험해집니다. 예를 들어 다음과 같은 수식이 있습니다.
=IFERROR(SUM(INDEX(Data!B2:C4, ,MATCH(Dashboard!C3, Data!B1:C1, 0))), 0)
겉보기에는 무해해 보이지만, 데이터 워크시트가 삭제되면 해당 수식은 #REF! 오류를 표시하는 대신 아무런 경고 없이 0을 반환합니다.
모든 수식을 IFERROR 함수로 감싸는 대신, 모든 오류가 실제로 동일한 결과를 생성해야 하는 경우에만 사용하세요. 그렇지 않으면 오류 메시지를 표시하여 보고서에 영향을 미치기 전에 실제 계산 오류를 식별하고 수정할 수 있도록 하세요.
누락된 조회 결과만 처리해야 하는 경우, #N/A 오류를 포착하기 위해 IFNA와 같은 보다 구체적인 접근 방식을 사용하거나, 관련 없는 계산 오류를 숨기지 않고 대체 동작을 정의할 수 있는 "찾을 수 없는 경우" 인수가 내장된 XLOOKUP 과 같은 최신 함수를 사용하는 것이 좋습니다.
보이는 내용을 무시하는 요약 함수
수동으로 숨기거나 필터링한 행을 사용하는 경우, 합계 및 평균과 같은 표준 요약 함수는 해당 행이 표시되지 않더라도 전체 범위를 계산합니다. 이로 인해 화면에 표시되는 값과 Excel에서 실제로 계산하는 합계 값 사이에 불일치가 발생할 수 있습니다. 예를 들어, 다음과 같은 수식이 있습니다.
=SUM(B2:B8)
7행과 8행을 숨기거나 필터링하더라도 수식은 숨겨진 항목을 합계에 포함합니다.
합계에 표시된 데이터만 반영되도록 하려면 SUBTOTAL 함수를 사용합니다.
=SUBTOTAL(109, B2:B8)
아래 표에 따라 "109"를 필요한 연산으로 바꾸세요. 합계에 109를 사용하면 필터링된 행과 수동으로 숨겨진 행이 모두 제외되어 현재 표시되는 내용과 일치하는 결과가 나옵니다.
함수 번호는 Excel에서 수동으로 숨긴 행을 처리하는 방식을 제어합니다.
| 함수 | 코드(수동으로 숨긴 행 포함) | 코드(수동으로 숨긴 행 제외) |
| AVERAGE | 1 | 101 |
| COUNT | 2 | 102 |
| COUNTA | 3 | 103 |
| MAX | 4 | 104 |
| MIN | 5 | 105 |
| PRODUCT | 6 | 106 |
| STDEV | 7 | 107 |
| STDEVP | 8 | 108 |
| SUM | 9 | 109 |
| VAR | 10 | 110 |
| VARP | 11 | 111 |
SUBTOTAL은 필터링된 행을 항상 무시합니다. 함수 번호는 수동으로 숨겨진 행을 포함할지 여부만 제어합니다.
마치며
이러한 숨겨진 오류를 수정하면 부정확한 계산으로 인해 요약 결과가 왜곡되는 것을 방지할 수 있지만, 개별 수식을 관리하는 것은 문제 해결의 절반에 불과합니다. 아무리 잘 만들어진 수식이라도 구조가 제대로 되어 있지 않은 스프레드시트에서는 오류가 발생할 수 있습니다. 깔끔한 스프레드시트 구조는 이러한 문제가 발생하기 전에 예방하는 데 큰 도움이 됩니다.
'Excel' 카테고리의 다른 글
| 구글 시트를 수년간 사용해 왔지만 여전히 놀라운 기능들 (0) | 2026.09.29 |
|---|---|
| 시도해 볼 만한 실용적인 엑셀 프로젝트 3가지 (0) | 2026.09.28 |
| 추석 맞이 20% 할인 | 파워툴스 마스터클래스 · 엑셀 대시보드 실전 워크북 (9/30까지) (0) | 2026.09.20 |
| 상사가 내 엑셀 대시보드를 계속 무시하는 이유는? (0) | 2026.09.15 |
| AI가 엑셀을 절대 대체할 수 없는 이유 (0) | 2026.09.11 |
