9가지 일반적인 Excel 기능 실수와 이를 수정하는 방법

Excel은 다양하고 강력한 기능을 제공하지만 이를 정기적으로 사용하는 경우에도 가장 일반적인 실수는 잘못된 일치 모드 사용, 값 대신 서식이 지정된 텍스트 비교, 셀 서식도 기본 숫자를 반올림한다고 가정하는 등 작은 세부 사항에서 발생합니다.

이 튜토리얼에서는 자주 잘못 사용되는 9가지 일반적인 Excel 기능에 대해 설명하고 이를 수정하는 방법을 보여줍니다.

1. VLOOKUP – 모든 용도로 사용 중지

잘못된 방법: 많은 사용자는 다른 기능이 더 적합한 경우에도 모든 작업에 VLOOKUP을 사용합니다. VLOOKUP은 오른쪽으로만 검색하고 대략적인 일치를 위해 정렬된 데이터가 필요하며 원본 데이터에 열이 삽입되면 중단될 수 있습니다. 기본적으로 VLOOKUP은 정렬된 첫 번째 열이 필요한 근사 일치(TRUE)를 사용하며 데이터가 제대로 정렬되지 않은 경우 잘못된 항목을 반환하는 경우가 많습니다.

수정: Microsoft 365에서 정확히 일치하는 XLOOKUP을 사용하거나 이전 버전의 Excel을 사용하는 경우 INDEX+MATCH를 사용하세요.

유연성을 높이기 위해 INDEX/MATCH 사용:

• 특정 제품의 가격을 조회하려면 셀을 선택하고 다음 수식을 삽입하세요.

=INDEX(G2:G101, MATCH(L3, E2:E101, 0))

이는 또한 INDEX 내에서 원하는 반환 열을 선택하여 "왼쪽 조회"를 처리합니다.

Excel 365 사용자의 경우 XLOOKUP을 고려하세요.

• 셀을 선택하고 다음 수식을 삽입하세요.

=XLOOKUP(L3, E2:E101, G2:G101, "Not found", 0)

0은 정확히 일치하도록 강제하고 네 번째 인수는 친숙한 "찾을 수 없음" 메시지를 제공합니다.

• 모든 방향(왼쪽 또는 오른쪽)에서 작동합니다.
• 열을 삽입해도 깨지지 않습니다.
• 대규모 데이터 세트에 더 효율적입니다.
• 논리 흐름이 더욱 명확해졌습니다.

2. SUMIF/SUMIFS – 기준을 잘못 작성

잘못된 방법: 연산자를 연결하는 대신 기준 문자열(예:일반 텍스트로 '>=2025-03-01')에 직접 작성하거나, 기준_범위 및 합계_범위 인수를 혼합하거나, A:A와 같은 전체 열 참조를 사용하면 계산 속도가 느려질 수 있습니다.

수정: 범위를 정렬된 상태로 유지하고 연결을 통해 연산자 기준을 구축합니다. 강력한 날짜 필터를 위해 DATE/EOMONTH를 사용하세요.

• 실제 데이터가 포함된 특정 범위를 사용하면 더 빠르고 정확합니다.

=SUMIF(D2:D101,"East",H2:H101)

• 2025년 3월 서부 지역의 총 판매량:

=SUMIFS(
H2:H101,
D2:D101, "West",
A2:A101, ">=" & DATE(2025,3,1),
A2:A101, "<=" & EOMONTH(DATE(2025,3,1),0)
)

이렇게 하면 로케일 놀라움과 텍스트 날짜 문제를 피할 수 있습니다. 항상 합계_범위와 각 기준_범위의 크기가 동일한지 확인하세요.

전문가의 팁: 자동 범위 확장을 위해 Excel 테이블과 구조적 참조를 사용하세요.

3. IF 문 – 중첩된 악몽

잘못된 방법: 부울 함수를 TRUE와 불필요하게 비교하거나(예:=IF(AND(E4=”Y”,F4=”Y”)=TRUE, …)) 읽고 유지하기 어려운 간단한 범주 맵에 깊게 중첩된 IF 문을 생성합니다.

수정: AND/OR 함수가 부울 값을 직접 반환하도록 합니다. 조건이 여러 개인 경우 IFS, CHOOSE/MATCH 또는 SWITCH를 사용하여 더 명확하고 관리하기 쉬운 수식을 만드세요.

• 깨끗한 부울 테스트:

=IF(AND(D2="East", F2>=3), "Bulk East", "Other")

• IFS를 사용한 다중 조건 점수:

=IFS(F2:F101>=5, "High", F2:F101>=2, "Medium", TRUE, "Low")

• CHOOSE/MATCH를 사용한 깔끔한 카테고리 지도(예:J2의 문자 성적을 GPA 점수로 변환):

=CHOOSE(MATCH(J2, {"A","B","C","D","F"}, 0), 4,3,2,1,0)

이는 깊은 IF 피라미드보다 간단하고 오류가 발생할 가능성이 적습니다.

4. CONCATENATE – 오래된 접근 방식

잘못된 방법: 여전히 오래된 CONCATENATE 함수를 사용하거나 복잡한 텍스트 결합을 위해 여러 앰퍼샌드를 연결하는 방식은 번거롭고 오류가 발생하기 쉽습니다.

수정: 최신 버전의 Excel에서는 TEXTJOIN을 사용하세요. 지정된 구분 기호를 사용하여 여러 값을 효율적으로 결합할 수 있습니다.

• 구분 기호가 있는 여러 값에는 TEXTJOIN을 사용하세요.

=IF(L2="","", TEXTJOIN(", ", TRUE, FILTER(E$2:E$101, C$2:C$101=L2)))

간단한 연결에는 &연산자를 사용하지만 다음과 같은 경우에는 TEXTJOIN이 더 좋습니다.

• 빈 셀을 자동으로 건너뜁니다.
• 전체적으로 동일한 구분 기호를 사용하세요.
• 셀 범위를 결합합니다.

5. COUNTIF – 여러 기준 효율성 무시

잘못된 방법: 리터럴 연산자를 연결하는 대신 COUNTIF를 사용하거나, 대소문자를 구분하는 결과를 잘못 예상하거나, 와일드카드를 따옴표로 묶어야 한다는 사실을 잊어버린 경우. 또 다른 일반적인 실수는 여러 기준에 대해 더 효율적인 COUNTIFS를 사용하는 대신 여러 COUNTIF 함수를 합산하는 것입니다.

수정: 연산자를 올바르게 연결하십시오. 대소문자 구분이나 더 복잡한 '포함' 논리가 필요한 경우 SUMPRODUCT 또는 FILTER로 전환하세요.

• US-E로 시작하는 코드 계산:

=COUNTIF(J2:J101, "US-E*")

• 'phone'이라는 단어가 포함된 제품의 주문 수 계산(대소문자 구분):

=SUMPRODUCT(--ISNUMBER(SEARCH("phone", E2:E101)))

대소문자를 구분하는 '포함'이 필요한 경우 SEARCH를 FIND로 바꾸세요.

6. ROUND – 잘못된 시간에 반올림

잘못된 방법: 셀을 소수점 이하 두 자리까지 서식을 지정하면 계산 시 기본 값도 반올림된다고 가정합니다. 서식을 지정하면 저장된 값이 아닌 모양만 변경되므로 총계에 불일치가 발생할 수 있습니다.

수정: 비즈니스 규칙에서 요구하는 단계를 라운드합니다. 증분하려면 ROUND, ROUNDUP, ROUNDDOWN 또는 MROUND를 사용하세요.

• 반올림된 줄 금액:

=ROUND(F2:F101*G2:G101, 2)

• 가장 가까운 0.05로 반올림(현금 가격과 공통):

핵심 원칙: 결과를 반올림하여 표시하지만 특별히 요구되지 않는 한 중간 계산에서는 정밀도를 유지합니다.

7. 계산에 사용되는 TEXT 및 날짜 형식

잘못된 방법: 표시 목적으로 값을 텍스트로 변환한 다음 해당 텍스트 기반 결과를 수학적 계산에 사용하거나 형식이 지정된 날짜 문자열을 실제 날짜 값과 비교합니다. 여기에는 날짜를 텍스트로 처리하고 전용 날짜 기능 대신 복잡한 텍스트 조작을 사용하는 경우가 많습니다.

수정: 원시 숫자 값에 대한 계산을 수행합니다. 차트 제목이나 보고서 라벨 등 최종 프리젠테이션 단계에서만 TEXT 기능을 사용하세요.

• 실제 날짜 값과 함께 적절한 날짜 함수를 사용하세요.

=YEAR(A1)
=MONTH(A1)
=DAY(A1)

• 숫자를 구분하지 않고 월과 합계를 표시하는 대시보드 제목:

="March " & YEAR(DATE(2025,3,1)) & " Sales: " &
TEXT(SUMIFS(H$2:H$101, A$2:A$101, ">="&DATE(2025,3,1), A$2:A$101, "<="&EOMONTH(DATE(2025,3,1),0)),"$#,##0")

• 날짜를 사용하는 강력한 월 필터(TEXT 필요 없음):

=SUMIFS(H$2:H$101, D$2:D$101, "East",
A$2:A$101, ">="&DATE(2025,3,1),
A$2:A$101, "<="&EOMONTH(DATE(2025,3,1),0))

8. SUMPRODUCT – 강제를 잊어버리거나 FILTER가 더 명확할 때

잘못된 방법: 부울(TRUE/FALSE) 배열을 숫자(1/0)로 강제 변환하는 것을 잊었거나 크기가 일치하지 않는 배열을 만드는 것입니다. 반대로 최신 Excel에서 간단한 SUM(FILTER(…)) 조합이 더 읽기 쉬운 경우 복잡한 SUMPRODUCT 수식을 사용하는 것이 좋습니다.

수정: 이중 단항을 사용하거나 1을 곱하여 TRUE/FALSE를 1/0으로 만듭니다. Microsoft 365에서는 다기준 합계에 대해 투명한 SUM 및 FILTER 패턴을 선호합니다.

• 동부 지역의 총 판매량, '휴대전화'가 포함된 제품, 레거시 친화적인 수량 3개 이상:

=SUMPRODUCT((D$2:D$101="East") * ISNUMBER(SEARCH("phone", E$2:E$101)) * (F$2:F$101>=3) * H$2:H$101)

• 동적 배열과 동일한 논리(365/2021):

=SUM(FILTER(H$2:H$101, (D$2:D$101="East")*(ISNUMBER(SEARCH("phone", E$2:E$101)))*(F$2:F$101>=3)) )

FILTER를 사용하면 기준이 1/0 게이트로 곱해지며 결과를 계속 읽을 수 있습니다.

9. IFERROR 담요 반창고로 사용

잘못된 방법: 모든 오류를 억제하기 위해 크고 복잡한 수식을 IFERROR(…,””)로 래핑합니다. 이는 철자가 틀린 범위 이름, 진짜 #DIV/0과 같은 실제 문제를 숨길 수 있기 때문에 위험합니다! 오류 또는 귀하가 알아야 할 기타 논리적 결함.

수정: 예상되는 특정 오류만 포착하세요. 값을 찾을 수 없을 때 조회 함수에 IFNA를 사용하고, 계산을 수행하기 전에 IF 문을 사용하여 빈 셀을 처리합니다.

• 조회 키가 비어 있으면 공백으로 표시합니다. 그렇지 않으면 실제로 누락된 경우에만 친숙한 '찾을 수 없음' 메시지를 표시합니다.

=IF(L2="","", IFNA(XLOOKUP(L2, E$2:E$101, G$2:G$101),"No match"))

공백:

일치하지 않음:

• 변환할 항목이 있는 경우에만 텍스트를 숫자로 변환:

=IF(A2="", "", VALUE(A2))

이렇게 하면 합법적인 0이 유지되고 관련 없는 오류가 가려지는 것을 방지할 수 있습니다.

모범 사례 요약

• 올바른 도구 선택: 더 나은 대안이 있을 때 익숙한 기능을 기본으로 사용하지 마세요.
• 범위를 구체적으로 지정하세요. 필요한 경우가 아니면 전체 열 참조를 피하세요.
• 유지관리 가능성 고려: 다른 사람들(미래의 당신 포함)이 이해할 수 있는 공식을 작성하세요.
• 적절한 데이터 유형 사용: 날짜를 날짜로, 숫자를 숫자로 취급하세요.
• 특수 사례 테스트: 빈 셀, 오류 또는 예상치 못한 데이터가 있으면 어떻게 되는지 생각해 보세요.
• Excel 표 활용: 동적 범위에 구조화된 참조를 사용하세요.
• 최신 상태 유지: 복잡한 레거시 수식을 대체할 수 있는 Excel 365의 새로운 기능을 알아보세요.

결론

이러한 일반적인 실수를 피함으로써 보다 효율적이고 읽기 쉽고 안정적인 Excel 스프레드시트를 만들 수 있습니다. 핵심 사항은 작업에 적합한 기능을 선택하고, 기준과 범위를 정확하게 설정하고, 데이터 유형을 별도로 유지하는 것입니다. 즉, 숫자에 대한 계산을 수행하고 최종 프리젠테이션에만 서식 기능을 사용하십시오. 오래된 튜토리얼에서 배운 오래된 습관을 깨면 수식 오류 가능성이 줄어들 뿐만 아니라 작업을 더 쉽게 유지하고 이해할 수 있습니다.