Excel 수식은 시간을 절약하고 방대한 데이터 배열을 결합하여 마법 같은 기능을 할 수 있습니다. 그러나 때로는 사소한 수식 오류로 인해 결과의 품질과 유효성이 실망스러울 정도로 제한될 수 있습니다. Excel의 각종 수식 오류를 극복할 수 있는 다양한 방법을 소개합니다.
※ 이 글은 아래 기사 내용을 토대로 작성되었습니다만, 필자의 개인 의견이나 추가 자료들이 다수 포함되어 있습니다.
- 원문: 12 Ways to Fix Your Broken Excel Formula
- URL: https://www.howtogeek.com/ways-to-fix-your-broken-excel-formula/
1. 등호(=)를 사용했는 지 확인
Excel 수식의 첫 번째 부분에는 =기호가 포함되어야 합니다. 이는 스프레드시트 내에서 계산을 생성하고 있음을 Excel에 알려줍니다. =를 사용하지 않으면 Excel에서는 입력 중인 셀에 단순히 데이터를 입력하는 것으로 간주하여 계산을 수행하지 않습니다.
2. 올바른 숫자 형식 사용
Excel을 사용하면 셀에 입력하는 데이터 유형을 정의할 수 있습니다. [홈] 탭 - [표시 형식] 그룹 - [표시 형식]의 드롭다운을 클릭합니다. 수식이 작동하려면 올바른 숫자 형식이 선택되었는지 확인합니다.
- '일반'은 수식에서 다른 셀을 참조하는 경우 다른 셀의 서식을 복사하므로 잘 작동합니다. 예를 들어 통화가 포함된 두 셀을 참조하는 경우 결과가 동일한 통화가 되기를 원할 것입니다. '일반' 숫자 형식은 이 작업을 자동으로 수행합니다.
- '숫자'도 좋은 옵션입니다. 그러나 이 방법은 일반적으로 소수점 이하 두 자리의 결과를 제공하므로 수식을 완성한 후 소수점 이하 자릿수를 변경할 수도 있습니다.
대부분의 다른 숫자 형식은 원하는 결과 유형에 따라 좋은 선택입니다. 그러나 '텍스트'는 피해야 합니다. 이렇게 하면 셀에 텍스트를 입력한다는 의미가 Excel에 전달되므로 입력 내용이 수식으로 표시되지 않습니다.
3. 순환 참조 피하기
수식을 입력한 후 Enter 키를 누르면 Excel에서 수식 내에 순환 참조(circular references)가 포함되었다는 메시지를 표시할 수 있습니다.
이는 수식 내에서 입력 중인 셀을 참조했음을 의미합니다. 순환 참조를 수정하는 가장 좋은 방법은 나타나는 경고를 닫고 Ctrl+Z를 눌러 방금 입력한 내용을 실행 취소한 다음 다른 셀에 수식을 다시 입력하는 것입니다.
4. 자동 계산 켜기
수식이 제대로 작동하지 않는 또 다른 이유는 자동 계산이 꺼져 있기 때문입니다. 이 경우 수식에 참조된 데이터를 변경할 때 수식 결과가 자동으로 업데이트되지 않습니다.
이를 수정하려면, 수식을 입력할 셀을 선택합니다. [수식] 탭 - [계산] 그룹에서 [계산 옵션] 옆에 있는 드롭다운을 클릭하고 [자동] 옵션이 선택되어 있는지 확인하세요.
전체 통합 문서에 대해 자동 계산이 켜져 있는지 확인하려면 다음 과정을 따릅니다.
- [파일] - [옵션]을 선택합니다.
- [Excel 옵션] 대화상자에서 [수식]을 클릭합니다.
- [계산 옵션] - [자동]이 선택되어 있는지 확인합니다.
5. 수식 내에서 서식을 지정하지 말 것
2번 항목에서 설명한 대로 기본적으로 통화나 백분율과 같이 미리 정의된 숫자 형식이 있는 셀을 참조하는 수식을 만드는 경우 수식은 자동으로 숫자 형식을 복사합니다.
위의 예에서 수식을 입력하는 사람은 수식 내에 두 개의 금전적 가치를 함께 추가하려고 합니다. 하지만 특정 기호는 수식 내에서 특정 기능을 가지므로 문제가 발생할 수 있습니다. 수식에 통화 기호를 입력하는 대신 입력하는 셀에 회계 숫자 형식을 사용해야 합니다. 이렇게 하면 결과에 달러 기호가 포함되도록 자동으로 형식이 지정됩니다.
6. 추가 문자에 대한 수식 검토
당연하게 들릴 수도 있지만 수식을 수동으로 검토하는 것만으로도 문제를 즉시 확인할 수 있는 경우가 많습니다. 있어서는 안되는 추가 문자, 셀 참조 내의 공백 또는 수식을 구성할 때 실수로 입력했을 수 있는 숫자를 찾아보세요.
이 작업을 수행한 후 Excel에서 오류를 확인하도록 지시할 수도 있습니다. [수식] 탭 - [수식 분석] 그룹 - [오류 검사]를 클릭합니다. 이렇게 하면 수식과 관련된 대부분의 문제가 강조됩니다.
7. 괄호 쌍 맞추기
여러 개의 함수를 중첩하는 경우 여는 괄호를 여러 개 사용하게 됩니다.
=IFERROR(AVERAGE((TAKE(Ratings!FZ4:INDIRECT("Ratings!GA"&$AI$9
이 수식에는 5개의 여는 괄호가 있습니다. 여는 괄호로 생성된 각 인수가 완성되었는지 확인해야 하므로 공식이 올바르게 구성되었는지 확인하려면 5개의 닫는 괄호가 필요합니다.
8. 참조된 셀 확인
수식 결과가 잘못된 것 같나요? 그렇다면 올바른 셀을 참조했는지 확인하세요. 수식이 포함된 셀을 두 번 클릭하면 Excel에서 수식 내의 참조를 색상으로 구분합니다. 이렇게 하면 셀 참조가 올바르지 않은지 빠르게 확인할 수 있습니다. 이 문제를 해결하려면 색상이 지정된 셀 참조를 올바른 셀로 드래그하면 수식이 자동으로 업데이트됩니다.
9. 엑셀 함수 가이드 활용하기
실망스러운 수식 오류를 극복하는 좋은 방법은 수식을 수동으로 입력하는 대신 Excel의 함수 가이드를 사용하는 것입니다. [수식] 탭에서 [함수 삽입]을 클릭하거나 수식 입력줄 옆에 있는 동일한 기호를 누릅니다.
[함수 마법사] 대화 상자에서 [함수 검색] 상자에 사용하려는 함수 이름을 입력하거나 [함수 선택] 목록에서 선택할 수 있습니다. 올바른 함수를 찾은 후 [확인]을 클릭하면 Excel에서 수식이 올바르게 입력되었는지 확인하는 단계를 안내합니다.
10. 텍스트에는 큰 따옴표만 사용
수식에 큰따옴표를 포함하면 Excel에서는 이를 결과에 포함할 텍스트로 간주합니다.
이 예에서 셀 S9가 2와 같으면 수식은 "Yes"라는 단어를 반환합니다. 2가 아닌 경우 수식은 "No"를 반환합니다. 이는 Excel에 수식이 포함된 셀에 텍스트를 추가하도록 지시하기 위해 이 두 단어 주위에 큰따옴표를 사용했기 때문입니다. 수식이 작동하지 않으면 실수로 큰따옴표를 사용하지 않았는지 확인하세요.
11. 64개 이상의 함수를 중첩하지 말 것
Excel의 인수 내에 함수를 사용하면 수식이 중첩됩니다. 이 예에서는 IF 함수 내에 AVERAGE 및 SUM 함수를 중첩합니다
=IF(AVERAGE(A1:A10)>100,SUM(B1:B113),"FAIL")
Excel에서는 하나의 수식 내에 최대 64개의 중첩 함수를 허용합니다. 더 많이 사용하면 수식이 작동하지 않습니다. 실무에서 이 한도를 넘어가는 경우는 거의 없습니다.
12. 0으로 나누지 말 것
계산기를 사용하여 0으로 나누어 본 적이 있나요? 0으로 나누는 것이 불가능하기 때문에 오류가 발생합니다(#DIV/0!). 이 문제를 해결하려면 IFERROR 함수를 사용하여 오류를 숨기고 대체 값을 삽입하거나 빈 셀을 반환합니다.
'Excel' 카테고리의 다른 글
| Excel과 MS Forms 자동 동기화 하는 방법 (0) | 2024.03.14 |
|---|---|
| Excel에서 저장을 취소하는 방법 (6) | 2024.03.13 |
| Microsoft 365 앱에서 Copilot Pro 사용하는 방법 (5) | 2024.03.11 |
| Excel에서 수집된 데이터를 구조화하는 방법 (5) | 2024.03.11 |
| 멀티플랜에서 현재까지 끊임없는 진화의 성공, 엑셀 (7) | 2024.03.10 |
