엑셀의 지능형 디버깅 엔진을 사용하여 복잡한 수식의 오류를 찾는 방법

수많은 수식으로 가득 찬 스프레드시트를 다루는 것은 정말 편리하지만, 문제가 발생하고 원인을 알 수 없는 오류가 생기면 이야기가 달라집니다. 엑셀에서 원인을 알 수 없는 오류가 발생했습니다. 그리고 인식하세요 Excel 수식에서 가장 흔한 오류수식이 길어지고 복잡해짐에 따라 육안으로 문제의 원인을 찾는 것이 거의 불가능해지고, 셀 하나하나를 확인하는 데 많은 시간이 낭비됩니다.

새로운 지능형 디버깅 엔진과 오류 검사 도구를 통해 Excel은 사용자에게 더욱 풍부한 도움을 제공합니다. 복잡한 공식에서 오류를 찾아내고, 이해하고, 수정하기하지만 이러한 기능을 최대한 활용하려면 수식 작성에 대한 몇 가지 기본 규칙을 숙지하고, 다양한 오류 메시지의 의미를 이해하고, 특히 다음과 같은 경우에 일괄 수정을 자동화하는 방법을 아는 것이 필수적입니다. 다른 프로그램에서 가져온 데이터 그리고 그것들은 다양하고 특이한 형태로 제공됩니다.

공식이 처음부터 오류를 일으키지 않도록 하기 위한 기본 개념

지능형 엔진과 고급 디버거를 본격적으로 사용하기 전에 대부분의 오류를 방지하는 몇 가지 기본 규칙을 이해하는 것이 중요합니다. Excel은 구문에 매우 엄격하며, 사소한 오류라도 문제를 일으킬 수 있습니다. 수식이 텍스트로 해석되거나 완전히 실패합니다..

첫 번째이자 가장 중요한 것은 모든 수식은 등호(=)로 시작해야 합니다.등호(=)를 생략하면 Excel은 계산을 하려는 것이 아니라 텍스트나 날짜를 입력하는 것으로 인식합니다. 예를 들어, 등호 없이 SUM(A1:A10)을 입력하면 셀에는 SUM(A1:A10)이 그대로 표시되고 계산은 수행되지 않습니다. 날짜의 경우에도 비슷한 현상이 발생합니다. 셀 서식이 일반으로 설정된 상태에서 11/2을 입력하면 Excel은 11을 2로 나누는 대신 2-nov(11월 2일)를 표시할 수 있습니다. Excel은 사용자가 계산이 아닌 날짜를 입력하는 것으로 인식하기 때문입니다. 이는 다음과 관련이 있습니다. 엑셀의 이상한 날짜 오류.

또 다른 중요한 점은 괄호의 올바른 사용입니다. 괄호를 사용하는 모든 함수는 반드시 다음 조건을 충족해야 합니다. 적절한 위치에 있는 시작 괄호와 종료 괄호~와 함께 일할 때 중첩 함수괄호를 하나 더 넣거나 빼먹는 실수는 흔히 발생하며, 이로 인해 구문 오류가 생깁니다. 예를 들어 `=IF(B5<0,”Not valid”,B5*1,05)`와 같은 수식을 생각해 보세요. 만약 실수로 닫는 괄호를 하나 더 추가하여 `=IF(B5<0,”Not valid”,B5*1,05))`라고 입력하면 괄호의 개수가 일치하지 않아 수식이 제대로 작동하지 않습니다. 따라서 여는 괄호와 닫는 괄호의 개수가 같은지, 그리고 올바른 위치에 있는지 항상 확인하는 것이 찾기 어려운 오류를 방지하는 데 매우 중요합니다.

범위를 올바르게 처리하는 것도 중요합니다. 여러 개의 연속된 셀을 참조하려면 다음을 사용해야 합니다. 첫 번째 셀과 마지막 셀 사이에 콜론(:)이 있습니다.범위는 예를 들어 A1:A5와 같이 작성됩니다. 콜론을 생략하고 =SUM(A1 A5)와 같이 작성하면 Excel은 이를 관련 없는 셀 간의 잘못된 참조로 해석하여 오류를 반환합니다. #NULL!이는 존재하지 않는 교차점이 있거나 일관성이 없는 참조가 있을 때 나타나는 오류입니다.

마지막으로, 각 함수의 인자 개수와 순서를 반드시 지켜야 합니다. 일부 함수는 특정 인자를 요구합니다. 필수적인 주장과 특정 위치에서일부 함수는 여러 개의 인수를 필요로 하는 반면, 다른 함수는 선택적 매개변수를 허용합니다. 인수가 부족하거나 필요 이상으로 추가하면 Excel은 함수와 관련된 오류 메시지를 표시합니다. 각 함수의 정확한 구문(인수 개수, 예상 데이터 형식 등)을 알고 있으면 문제가 발생하기 전에 최소화할 수 있습니다.

인수 유형: 숫자, 텍스트 및 중첩 제한

엑셀 함수는 특정 개수의 인수를 필요로 할 뿐만 아니라, 특정 조건을 만족하는 인수를 전달받기를 기대합니다. 각 인수의 데이터 형식을 올바르게 지정하십시오.숫자를 더하는 함수와 텍스트를 조작하는 함수는 서로 다릅니다. 이 두 함수를 서로 바꿔 사용하면 예상치 못한 결과가 나오거나 오류가 발생할 수 있습니다.

예를 들어, SUM, AVERAGE, PRODUCT와 같은 함수는 숫자 인수를 필요로 합니다. 이러한 함수에 숫자 대신 텍스트 값을 전달하면 Excel은 상황에 따라 0을 반환하거나, 해당 셀을 무시하거나, 오류를 표시할 수 있습니다. 반대로 REPLACE, CONCAT, RIGHT와 같은 함수는 텍스트 문자열을 처리하도록 설계되었습니다. 이러한 함수에서는 인수 중 하나 이상이 텍스트 값이거나 텍스트가 포함된 셀에 대한 참조여야 합니다. 적절한 처리 없이 숫자를 텍스트로 처리하도록 강제하면 함수가 예상대로 작동하지 않을 수 있습니다.

또한 Excel은 함수들을 서로 결합하는 데 상당한 제약을 두고 있습니다. 다음과 같은 경우에는 사용할 수 없습니다. 하나의 수식에 64단계 이상의 함수를 중첩하세요즉, IF 문 안에 또 다른 IF 문이 있고 그 안에 또 다른 IF 문이 있는 식으로 수식이 여러 단계로 구성되면 Excel에서 더 이상 단계 수식을 허용하지 않는 한계가 있다는 뜻입니다. 실제로 이러한 극단적인 상황에 도달하는 경우는 드물지만, 매우 복잡하거나 제대로 설계되지 않은 모델에서는 이 한계를 넘어서 해석하기 어려운 오류가 발생할 수 있습니다. 특정 수준 이상의 복잡성을 가진 경우에는 모든 논리를 하나의 매우 긴 수식에 집중하기보다는 여러 보조 셀로 논리를 나누는 것이 일반적으로 더 현명합니다. 이를 위해서는 Excel의 다음 사항을 검토하는 것이 좋습니다. 엑셀 성능 문제의 원인 및 해결 방법.

인자 유형 및 개수로 인해 발생하는 오류의 대표적인 예는 ABS 함수입니다. 이 함수는 특정 유형의 인자만 허용합니다. 단일 숫자 인수 이 함수는 절댓값을 반환합니다. 만약 `=ABS(-2;134)`와 같이 입력하면, 세미콜론으로 구분된 두 개의 인수를 함수가 처리할 수 없기 때문에 Excel에서 오류가 발생합니다. 올바른 방법은 `=ABS(-2134)` 또는 A1 셀에 음수를 양수로 변환하려는 경우 `=ABS(A1)`과 같이 입력하는 것입니다.

한편, 구분 기호 사용은 지역 설정에 따라 달라질 수 있으므로 이를 고려해야 합니다. 많은 스페인어 버전의 Excel에서는 다음과 같은 구분 기호가 사용됩니다. 인자 구분자로 세미콜론(;) 사용 소수 부분에는 쉼표(,)를 사용합니다. 수식에서 쉼표와 세미콜론을 부적절한 위치에 혼용하면 구문 오류가 발생하여 수식의 가독성과 디버깅이 어려워질 수 있습니다.

요약하자면, 각 함수에 전달할 올바른 데이터 유형을 선택하고, 함수 구문에 정의된 인자 개수를 준수하며, 중첩 제한을 초과하지 않는 것은 복잡한 수식에서 오류 발생을 줄이는 세 가지 기본 원칙입니다.

다른 페이지 및 도서에 대한 참조: 링크 오류를 방지하는 방법

수식이 같은 통합 문서 내의 다른 시트 또는 외부 통합 문서와 연결되기 시작하면, 잘못된 참조 오류 메시지를 발생시키는 작은 입력 실수를 하기 쉽습니다. Excel은 사용자가 이러한 실수를 방지하도록 지원합니다. 도면, 책, 노선의 이름은 매우 정확하게 표기되어 있습니다. 데이터를 정확하게 찾기 위해서입니다.

수식에서 시트 이름에 공백이나 문자 이외의 문자(숫자, 하이픈, 기호 등)가 포함된 경우, 시트 이름을 항상 포함해야 합니다. 작은따옴표 안에예를 들어, '분기별 데이터'라는 시트가 있다면 해당 시트의 D3 셀을 올바르게 참조하려면 '='분기별 데이터'!D3'와 같이 작성해야 합니다. 마찬가지로 시트 이름이 '123'이라면 Excel이 시트 이름을 제대로 인식하고 임의의 숫자로 오인하지 않도록 '='123'!A1'과 같이 작성해야 합니다.

또한, 수식이 다른 시트를 참조할 때는 항상 이름 바로 뒤에 하이픈(-)을 붙여야 합니다. 느낌표 (!)느낌표(```)는 시트 식별자와 특정 셀 참조 사이의 연결을 나타냅니다. 예를 들면 다음과 같습니다. ='분기별 데이터'!D3. 느낌표를 생략하거나 잘못된 위치에 입력하면 수식이 유효하지 않게 되고 참조 오류가 발생합니다.

정보가 다른 통합 문서에 있는 경우 Excel은 더 많은 컨텍스트가 필요합니다. 이러한 경우 외부 참조에는 다음 내용이 포함되어야 합니다. 파일 이름은 대괄호 안에, 시트 이름은 시트 범위 안에 표시됩니다.예를 들어, Operations T2.xlsx 통합 문서의 Sales 시트에서 A1:A8 범위에 있는 행 수를 계산하려면 `=ROWS('Sales'!A1:A8)`과 같은 수식을 사용할 수 있습니다. 파일이 열려 있지 않은 경우 수식에 전체 파일 경로를 지정해야 합니다(예: `C:\My Documents\Sales'!A1:A8`).

이러한 상황에서 일반적인 수식은 `=ROWS('C:\My Documents\Sales'!A1:A8)`과 같습니다. 이 명령은 다른 통합 문서의 A1:A8 범위에 있는 행 수를 반환하며, 이 경우 8이 됩니다. 경로, 통합 문서 이름, 시트 이름 또는 대괄호를 잘못 입력하면 오류가 발생합니다. 참조 오류 또는 값이 업데이트되지 않음 Excel에서 외부 데이터 소스를 찾을 수 없기 때문입니다.

연결된 데이터가 포함된 파일을 이동, 이름 변경 또는 삭제할 때 발생하는 상황을 모니터링하는 것도 좋습니다. 오류 검사 엔진이 일부 문제를 감지할 수 있지만, 외부 파일이 예상 위치에 더 이상 존재하지 않는 경우 수식 기능을 복원하려면 링크를 수동으로 업데이트하거나 경로를 다시 정의해야 합니다.

수식에서 숫자 서식 지정: 기호 및 구분 기호로 인한 오류

특히 다른 애플리케이션에서 가져온 데이터가 많은 스프레드시트에서 가장 흔한 문제 중 하나는 잘못된 데이터 사용입니다. 수식 내 숫자 형식이 잘못되었습니다.Excel은 셀에 저장된 실제 값과 해당 값을 표시하는 데 사용된 서식을 명확하게 구분합니다. 이 두 가지를 혼동하면 미묘한 오류가 발생하기 쉽습니다.

수식에서는 숫자에 서식을 지정해서는 안 됩니다. 즉, 값이 1000유로인 경우 수식에서는 유로 기호(€), 천 단위 구분 기호 또는 쉼표 없이 1000으로 입력해야 합니다. 1.000 또는 1,000(지역 설정에 따라 다름)과 같이 입력하면 Excel에서 이를 인수 구분 기호 또는 의도한 값과 다른 값으로 인식할 수 있습니다. 숫자는 통화, 천 단위 구분 기호 또는 소수점 구분 기호를 사용하여 서식을 지정해야 합니다. 후수식 내에서가 아니라 셀 서식 옵션을 사용하여 서식을 적용합니다. 서식을 올바르게 적용하는 방법에 대한 자세한 내용은 다음을 참조하세요. Excel 365의 데이터 형식.

셀 A3의 내용에 3100을 더하고 싶다고 가정해 보겠습니다. 만약 3.100을 더하는 것이라고 생각하고 무심코 `=SUM(3100,A3)`라고 입력하면, Excel은 이를 3과 100을 먼저 더한 다음 A3의 값을 결과에 더하는 것으로 해석합니다. 즉, (3 + 100) + A3을 계산하게 되는데, 이는 A3 + 3100과는 다릅니다. 올바른 수식은 천 단위 구분 기호를 고려하지 않고 `=SUM(3100,A3)`입니다.

ABS 함수처럼 인수의 개수가 특정 개수로 제한되는 함수에서도 비슷한 문제가 발생합니다. ABS 함수는 숫자 값만 인수로 받는데, 숫자 안에 쉼표나 세미콜론을 넣어 수천 단위를 나타내려고 하면 Excel은 이를 여러 개의 개별 인수로 인식하여 함수가 제대로 작동하지 않습니다. 구문 오류가 발생합니다따라서 =ABS(-2;134)와 같은 표현식은 작동하지 않지만 =ABS(-2134)는 유효합니다.

특히 회계 시스템, ERP 또는 송장 발행 프로그램에서 데이터를 가져올 때 이 점을 명심해야 합니다. 이러한 시스템 중 상당수는 금액을 서식, 통화 기호, 공백 또는 심지어 끝에 "EUR"라는 단어가 포함된 상태로 내보냅니다. 이 모든 것이 숫자 값이어야 할 것을 Excel에서 직접 계산에 사용할 수 없는 텍스트 문자열로 변환하여 입력 오류, 빈 결과 또는 합계 불일치를 초래할 수 있습니다.

가장 좋은 방법은 저장된 데이터를 다음과 같이 유지하는 것입니다. 특수 서식이 적용되지 않은 셀의 원시 숫자 그런 다음 서식 메뉴에서 통화, 백분율 또는 천 단위 구분 기호에 대한 시각적 서식을 적용합니다. 이렇게 하면 기호가 계산에 방해되는 것을 방지하고 오류 엔진이 불일치를 더 잘 감지할 수 있습니다.

엑셀에서 지능형 오류 검사 및 디버깅

Excel은 구문 오류 수정 외에도 다음과 같은 시스템을 포함하고 있습니다. 수식을 분석하고 수정 사항을 제안하는 오류 검사 기능 점점 더 지능화되는 이 엔진은 비정상적인 상황을 감지하면 복잡한 수식에서 누락된 참조, 불일치 및 유형 문제를 찾아내어 오류의 원인을 찾는 데 걸리는 시간을 줄여줍니다.

Excel 데스크톱 버전에서 오류 검사는 [수식] 탭의 [수식 편집] 그룹에서 [오류 검사] 옵션을 통해 접근할 수 있습니다. 이 마법사는 경고가 있는 셀을 검사하고 문제의 가능한 원인을 알려주는 메시지를 표시하며, 한 번의 클릭으로 적용할 수 있는 구체적인 변경 사항을 제안합니다. 이 기능은 일관성이 없는 범위, 드래그해도 업데이트되지 않은 수식, 빈 셀 참조 또는 합계의 불일치를 감지하는 데 유용합니다.

하지만 Excel Online(웹 버전)에서는 현재 이러한 고급 오류 검사 규칙을 동일한 방식으로 구성하거나 사용할 수 없습니다. 클라우드 서비스는 기본 기능만 제공합니다. 여기에는 공식 검토 규칙 전체가 포함되어 있지 않습니다. 데스크톱에서 사용할 수 있습니다. 따라서 매우 복잡한 통합 문서를 다룰 때는 디버깅 엔진을 최대한 활용하기 위해 데스크톱 애플리케이션으로 여는 것이 가장 실용적입니다.

데스크톱 버전을 사용하는 경우 Excel Online에서 "Excel로 열기" 버튼을 사용하여 통합 문서를 정식 Excel 애플리케이션에서 열 수 있습니다. 그러면 수식 오류를 검사하고, 특정 규칙을 활성화하고, 시스템에서 제공하는 경고를 검토하는 등 필요한 모든 작업을 수행할 수 있습니다. 온라인 작업의 편리함과 데스크톱 애플리케이션의 강력한 기능을 효과적으로 결합할 수 있는 방법입니다.

새로운 검증 엔진 기능 및 스마트 디버깅 도구의 개선 사항에 대한 최신 정보를 얻으려면 문서를 주기적으로 확인하는 것이 좋습니다. Microsoft Excel 공식 블로그이곳에는 데스크톱 및 온라인 버전의 뉴스, 업데이트 및 변경 사항이 게시되어 새로운 버그 수정 기능이 언제 제공되는지 알 수 있습니다.

Word, Excel, PowerPoint 등 모든 Office 응용 프로그램과 관련 서비스에 대한 보다 포괄적인 액세스가 필요한 경우 Office.com을 통해 전체 제품군을 체험하거나 구매하는 옵션이 있습니다. 이를 통해 모든 스프레드시트에 대한 최첨단 오류 검사 도구를 이용할 수 있습니다.

숫자가 텍스트로 저장되어 발생하는 오류 및 일괄 수정 방법

특히 회계 또는 재무 환경에서 흔히 발생하는 시나리오는 관리 소프트웨어에서 상세 총계정원장(상세 GL)을 내보낼 때입니다. 많은 경우 이러한 유형의 애플리케이션은 그들은 수치 금액을 텍스트로 내보냅니다.그 결과, 해당 파일을 엑셀에서 열었을 때, 해당 필드를 더하거나 빼거나 분석하려는 수식이 값을 숫자로 인식하지 못하여 연산이 실패하거나 잘못된 결과가 나옵니다.

엑셀의 내장 오류 검사 기능은 이 문제를 감지하여 셀 모서리에 녹색 삼각형을 표시하고 "숫자가 텍스트로 저장되었습니다"라고 알려줍니다. 경고 아이콘을 클릭하면 엑셀에서 텍스트를 숫자로 변환하는 옵션을 선택할 수 있습니다. 하지만 기본적으로 [수식] 탭 > [수식 편집] > [오류 검사]에서 오류 검사 도구를 사용하면 대부분 자동으로 수정이 적용됩니다. 세포 하나하나수천 개의 행이 있는 GL에서는 이것이 정말 골치 아픈 일이 될 수 있습니다.

또한, 문서 전체를 선택하면 오류 검사가 숫자 열에만 집중되지 않고 날짜, 텍스트, 설명 등이 포함된 다른 열에서도 오류가 발생하여 일괄 수정 작업이 더욱 복잡해집니다. 따라서 전체 시트를 선택하고 텍스트를 실제 숫자로 변환하여 이러한 오류를 한 번에 모두 수정할 수 있는 방법이 있는지 궁금해집니다.

이러한 상황을 해결하기 위해 데스크톱 Excel에서 직접 적용할 수 있는 몇 가지 전략이 있습니다. 가장 직접적인 방법 중 하나는 다음을 사용하는 것입니다. 수학적 연산이 포함된 특수 접착제예를 들어, 빈 셀에 숫자 1을 입력하고 해당 셀을 복사한 다음, 텍스트로 저장된 숫자가 포함된 셀 범위(예: 금액이 있는 전체 열)를 선택하고 [붙여넣기 옵션] > [곱하기]를 사용할 수 있습니다. 이 작업을 수행하면 Excel은 각 "텍스트" 항목을 숫자로 인식하고 1을 곱한 다음 결과를 실수 값으로 저장하여 원래 금액을 그대로 유지합니다. 이는 대량의 데이터를 일괄적으로 변환하는 매우 효율적인 방법입니다.

또 다른 방법은 보조 열에서 VALUE 함수를 사용하는 것입니다. 예를 들어, 금액 텍스트가 B열에 있다면 C열에 `=VALUE(B2)`와 같은 수식을 입력하고 아래로 복사한 다음, 그 결과를 원래 열에 값으로 복사하여 붙여넣어 텍스트를 대체할 수 있습니다. 이 방법을 사용하면 원본 데이터를 삭제하기 전에 모든 내용이 일치하는지 시각적으로 확인할 수 있습니다.

경우에 따라 "숫자가 텍스트로 저장됨" 경고 메시지가 표시되면 작은 녹색 삼각형 상황별 메뉴를 통해 더 넓은 범위를 선택하고 여러 셀에 한 번에 변환을 적용할 수 있습니다. 그러나 이러한 기능의 효과는 데이터 생성 방식과 Excel이 모든 경우를 동일한 유형의 오류로 인식하는지 여부에 따라 달라집니다.

대량의 회계 데이터를 다룰 때는 Excel의 지역 설정과 원본 프로그램의 내보내기 형식을 검토하는 것이 좋습니다. 소수점 구분 기호, 통화 기호 및 날짜 형식의 호환성을 확보하기 위해 이러한 매개변수를 조정하면 텍스트 형식으로 숫자가 표시되는 경우와 광범위한 오류가 발생하는 빈도를 크게 줄일 수 있으며, 수식이 처음부터 올바르게 작동하도록 할 수 있습니다.

요약하자면, 표준 오류 검사는 일반적으로 행 단위로 작동하지만, 경고 메시지와 특수 붙여넣기 및 변환 함수와 같은 기술을 결합하여 오류를 수정할 수도 있습니다. 대량의 잘못된 형식 데이터를 일괄적으로 수정하기 그리고 셀 단위로 하나씩 살펴보지 않고도 해당 셀들을 숫자로 복원할 수 있습니다.

등호, 괄호, 범위, 인수 유형, 숫자 서식, 외부 참조 및 오류 검사 도구에 대한 이러한 모든 권장 사항은 Excel의 새로운 지능형 디버깅 엔진이 최고 효율로 작동하도록 지원한다는 공통 목표를 가지고 있습니다. 수식이 잘 구성되고 데이터가 올바른 유형과 형식을 사용하는 경우, 경고 및 제안 시스템은 복잡한 수식과 여러 소스에서 가져온 데이터가 포함된 워크시트에서도 불일치, 잘못된 참조 또는 문제가 있는 셀을 신속하게 찾는 데 강력한 도구가 됩니다.