엑셀 스프레드시트의 90%에 오류가 있습니다: 당신의 파일을 고치는 방법

엑셀은 현대 생활의 중심축으로, 개인 주택 대출 계산부터 복잡한 기업 예산에 이르기까지 모든 것을 지원합니다. 그러나 워크북의 신뢰성은 그것을 만든 사람에게 달려 있습니다—경험 많은 전문가조차도 큰 차이를 초래하는 단순한 실수를 저지르곤 합니다. 수식이 문제를 일으키기 전에 지금 바로 수정할 때입니다.

스프레드시트 연구자 레이몬드 판코(Raymond Panko)의 연구를 포함한 여러 연구에 따르면, 모든 스프레드시트의 최대 90%가 최소한 하나 이상의 중대한 오류를 포함하고 있으며, 대기업에서 사용되는 모델의 약 절반에는 중요한 결함이 있는 것으로 추정됩니다. 숨겨진 하드코딩 숫자이든, 업데이트되지 않은 수식이든, 이러한 보이지 않는 오류는 유용한 도구를 부채로 바꿀 수 있습니다.

수식에 값을 하드코딩하지 마세요

엑셀 스프레드시트를 망치는 가장 흔한 방법은 =A1*0.05와 같이 수식에 고정된 숫자를 직접 입력하는 것입니다. 만약 그 5% 값이 변경되면, 모든 항목에 대해 수동으로 바꾸거나 Find and Replace와 같은 오류가 발생하기 쉬운 도구에 의존해야 합니다. 이로 인해 여전히 이전 데이터를 사용하고 있는 '고아' 수식이 생기며, 총계에 보이지 않는 오류를 만들어냅니다.

대신, 변수들을 위한 전용 섹션이나 워크시트를 만들어서, 수식에서 그 셀을 참조하세요. 이렇게 하면 비율을 조정할 필요가 있을 때, 한 곳에서 명확하게 라벨이 붙은 셀만 수정하면 전체 시트가 즉시 업데이트됩니다.

읽기 쉬운 논리를 위해 이름 있는 범위를 사용하세요

위에서 언급한 첫 번째 팁을 사용하여 만든 전용 입력 셀을 바탕으로, 이러한 Excel 참조를 더욱 안전하게 만드는 가장 좋은 방법은 명명된 범위를 사용하는 것입니다. =A1*₩B₩2와 같은 수식이 =A1*0.05보다 낫긴 하지만, 한눈에 추적하고 검사하기에는 여전히 어렵습니다. 셀 ₩B₩2를 'Tax_Rate'로 명명하면, 수식은 =A1*Tax_Rate가 됩니다.

이 사람이 읽을 수 있는 논리는 오류를 한눈에 드러나게 합니다—만약 가격과 연도를 곱하는 수식을 본다면, 바로 뭔가 잘못되었음을 알 수 있습니다. 게다가 입력 셀이 별도의 워크시트에 있더라도 ='Sheet2'!$B$2 같은 복잡한 참조를 신경 쓸 필요가 없으며, 이름이 워크북 전체에서 전역적으로 작동하기 때문입니다.

입력 셀의 이름을 지정하려면 해당 셀을 선택한 다음, 수식 입력줄 왼쪽에 있는 이름 상자에 Tax_Rate와 같이 한 단어로 이름을 입력하고 Enter 키를 누르세요. 이제 셀 좌표 대신 해당 이름을 모든 수식에서 사용할 수 있습니다.

나중에 명명된 범위가 잘못된 위치를 가리키고 있음을 알게 되거나 이름을 삭제하고 싶으면, 수식 탭에서 '이름 관리자'를 클릭하세요.

엑셀 표를 사용하여 '격차'를 방지하세요

엑셀에서 흔히 발생하는 오류의 원인 중 하나는 '틈(gap)'입니다. 이는 목록 하단에 새 데이터를 추가했지만, 수식과 피벗 테이블이 이를 포함하도록 업데이트되지 않는 상황을 말합니다.

데이터를 Excel 표로 변환하면 바로 해결됩니다. 표는 동적이어서 새로운 행과 열이 추가되면 자동으로 확장되므로, 표를 참조하는 모든 수식은 항상 전체 데이터를 포함하게 됩니다. 또한 표는 구조화된 참조를 제공하여 =SUM(Sales[Total])과 같은 수식을 사용할 수 있게 해 주며, 이는 고정된 셀 범위보다 훨씬 신뢰할 수 있습니다. 가장 중요한 것은, 표의 첫 번째 셀에 수식을 작성하면 Excel이 자동으로 같은 열의 다른 모든 셀에 수식을 복사해 주므로, 수식이 불일치하거나 빈 영역이 생길 가능성을 줄일 수 있다는 점입니다.

데이터셋을 엑셀 테이블로 변환하려면 먼저 맨 위 행에 열 머리글이 있는지 확인하세요. 그런 다음 범위 내의 아무 셀이나 선택하고 Ctrl+T를 누릅니다. '내 테이블에 머리글이 있습니다'가 선택되어 있는지 확인하고 '확인'을 클릭하세요.

데이터 유효성 검사를 활용하여 잘못된 입력을 차단하세요

엑셀 수식이 숫자를 예상하는데 다른 것이 입력되면 전체 계산 체인이 깨집니다. 데이터 유효성 검사를 사용하면 셀에 입력할 수 있는 값에 대한 엄격한 규칙을 설정할 수 있어 오류가 발생하기 전에 막을 수 있습니다.

데이터 유효성 검사를 사용하면 입력값을 정수, 특정 날짜 범위, 그리고 다양한 다른 데이터 유형으로 제한할 수 있습니다. 더욱 유용하게는 사람들이 선택할 수 있도록 미리 정의된 드롭다운 목록을 만들 수도 있습니다.

데이터 유효성 검사 규칙을 적용하려면 해당 셀 또는 셀들을 선택한 후, 데이터 탭에서 '데이터 유효성 검사'를 클릭하세요.

그런 다음, 설정 탭에서 기준을 선택하고 허용할 규칙을 정의하세요.

공식 표시와 추적 도구로 논리를 시각화하세요

완성된 Excel 스프레드시트를 보면 결과만 보이고 논리는 보이지 않기 때문에, 계산이 잘못된 부분을 놓치기 쉽습니다. 수식 표시 모드는 결과를 없애고 작업의 골격을 보여주어 열의 일관성을 확인할 수 있게 해줍니다.

수식 탭에서 '수식 표시'를 클릭하거나 키보드 단축키 Ctrl+` (보통 숫자 1 옆에 있는 그레이브 악센트)를 사용하세요.

수식 자체가 올바르게 보이지만 결과가 여전히 잘못되면, '선행셀 추적'과 '종속셀 추적'을 사용하세요. 이 도구들은 화살표를 그려 수식에 영향을 주는 셀과 수식의 영향을 받는 셀을 표시하므로, 잘못된 셀에서 데이터를 가져오는 수식을 쉽게 찾아낼 수 있습니다.

조사하려는 셀을 선택한 다음, 수식 탭에서 '선행 셀 추적'을 클릭하세요. 데이터가 어디로 이동하는지 보려면 '후행 셀 추적'을 클릭하세요.

범인을 찾으면 '화살표 제거'를 클릭하세요.

오류 확인 및 수식 평가 도구를 사용하세요

엑셀 워크시트에 #VALUE! 또는 #REF! 같은 오류가 있지만 원인을 알 수 없다면, 엑셀에는 이를 도와주는 내장 진단 도구가 있습니다.

오류 검사 도구는 첫 번째 방어선으로, 텍스트로 저장된 숫자나 인접한 셀을 제외한 수식과 같은 문제를 식별하고, 몇 초 만에 통합 문서 전체에서 이러한 문제를 해결할 수 있는 안내 인터페이스를 제공합니다. 반면, 수식 평가 도구는 여러 중첩 함수를 포함한 긴 수식에 유용합니다. 문자열의 어느 부분이 오류를 일으키는지 추측하는 대신, 수식의 각 부분을 밑줄로 표시하고 해당 부분의 결과를 보여줍니다.

이 두 도구는 리본의 수식 탭에서 찾을 수 있습니다.

스프레드시트 오류는 단순한 사소한 불편함이 아닙니다—현실 세계의 재정적 문제로 이어질 수 있습니다. 실제로 역사에는 간단한 감사 습관만으로도 피할 수 있었던 비용이 많이 드는 마이크로소프트 엑셀 오류가 가득합니다. 수동 입력에서 벗어나 엑셀의 내장 진단 도구를 활용하면 앞으로의 문제 해결에 소요될 시간을 절약할 수 있습니다.