Excel에서 가장 흔히 저지르는 9가지 함수 실수와 올바른 수정 방법

Excel은 다양하고 강력한 함수들을 제공하지만, 익숙하게 사용하더라도 대부분의 실수는 사소한 디테일에서 발생합니다. 잘못된 일치 방식(match mode) 선택, 값이 아닌 서식이 적용된 텍스트끼리 비교, 셀 서식이 실제 숫자까지 반올림해 준다고 착각하는 것 등이 대표적입니다.

이 글에서는 많은 사용자들이 잘못 사용하는 9가지 Excel 함수를 살펴보고, 각 문제를 바로잡는 구체적인 방법을 소개합니다.

1. VLOOKUP – 만능 조회 함수로 쓰지 마세요

잘못된 사용법: 많은 사용자가 더 적합한 함수가 있어도 무조건 VLOOKUP에 의존합니다. VLOOKUP은 오른쪽 방향으로만 검색할 수 있고, 근사 일치 시 데이터가 정렬되어 있어야 하며, 원본 데이터에 열이 삽입되면 수식이 깨질 수 있습니다. 특히 기본값이 근사 일치(TRUE)이므로 첫 번째 열이 정렬되어 있지 않으면 엉뚱한 결과를 반환하는 경우가 많습니다.

해결 방법: Microsoft 365 환경이라면 정확한 일치 옵션을 지원하는 XLOOKUP을, 구버전 Excel이라면 INDEX+MATCH 조합을 사용하세요.

더 유연한 조회를 위한 INDEX/MATCH:

• 셀을 선택한 뒤 아래 수식을 입력하면 특정 제품의 가격을 조회할 수 있습니다.

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

INDEX 안에서 반환 열을 자유롭게 지정할 수 있으므로 '왼쪽 조회(left lookup)'도 손쉽게 처리됩니다.

Excel 365 사용자라면 XLOOKUP 활용:

• 셀을 선택하고 아래 수식을 입력하세요.

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

마지막 인수 0은 정확한 일치를 강제하며, 네 번째 인수는 값을 찾지 못했을 때 친절한 안내 메시지를 표시합니다.

• 왼쪽·오른쪽 어느 방향으로든 조회 가능
• 열이 삽입되어도 수식이 깨지지 않음
• 대용량 데이터에서도 더 효율적
• 논리 구조가 명확하고 읽기 쉬움

2. SUMIF/SUMIFS – 조건을 잘못 작성하는 경우

잘못된 사용법: 연산자를 문자열에 그대로 붙여 쓰거나(예: ">=2025-03-01"을 단순 텍스트로 입력), criteria_range(조건 범위)와 sum_range(합계 범위) 인수를 혼동하거나, A:A처럼 열 전체를 참조해 계산 속도를 떨어뜨리는 경우가 많습니다.

해결 방법: 범위 크기를 항상 일치시키고, 연산자 조건은 문자열 연결(&)로 만드세요. 날짜 필터에는 DATE/EOMONTH 함수를 활용하면 안전합니다.

• 실제 데이터가 있는 구체적인 범위를 지정하면 더 빠르고 정확합니다.

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

• 2025년 3월 West 지역 매출 합계 구하기:

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

이 방식은 국가별 설정(locale) 차이나 텍스트형 날짜 문제를 피할 수 있습니다. sum_range와 각 criteria_range는 반드시 같은 크기여야 한다는 점도 유의하세요.

전문가 팁: Excel 테이블과 구조적 참조를 사용하면 범위가 자동으로 확장됩니다.

3. IF 문 – 중첩의 악몽

잘못된 사용법: 불리언(Boolean) 결과를 굳이 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 패턴을 권장합니다.

• East 지역 + 제품명에 'phone' 포함 + 수량 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문을 사용하는 것이 바람직합니다.

• 조회 키가 비어 있으면 공란을 표시하고, 정말로 값이 없을 때만 'No match' 메시지 출력:

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

빈 셀인 경우:

값이 없는 경우:

• 변환할 값이 있을 때만 텍스트를 숫자로 변환:

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

이렇게 하면 정당한 0 값은 보존되고, 관련 없는 오류까지 덮어쓰는 일을 방지할 수 있습니다.

모범 사례 요약

• 올바른 도구 선택: 더 나은 대안이 있는데도 익숙한 함수에만 의존하지 마세요.
• 구체적인 범위 지정: 불가피한 경우가 아니라면 열 전체 참조를 피하세요.
• 유지보수성 고려: 다른 사람(그리고 미래의 자신)도 이해할 수 있는 수식을 작성하세요.
• 적절한 데이터 타입 사용: 날짜는 날짜로, 숫자는 숫자로 다루세요.
• 경계 사례 테스트: 빈 셀, 오류, 예상치 못한 데이터가 들어오는 상황을 미리 점검하세요.
• Excel 테이블 활용: 구조적 참조로 동적 범위를 관리하세요.
• 최신 기능 학습: Excel 365의 새 함수로 복잡한 구식 수식을 대체하는 방법을 익히세요.

마무리

이런 흔한 실수들을 피하면 더 효율적이고 읽기 쉬우며 신뢰할 수 있는 Excel 스프레드시트를 만들 수 있습니다. 핵심은 작업에 맞는 올바른 함수를 선택하고, 조건과 범위를 정확하게 지정하며, 데이터 타입을 명확히 구분하는 것입니다. 즉, 계산은 숫자로 수행하고 서식 관련 함수는 최종 표시 단계에서만 사용하세요. 낡은 강좌에서 배운 오래된 습관을 버리면 수식의 오류 가능성이 줄어들 뿐 아니라, 작업물을 유지보수하고 이해하기도 훨씬 쉬워집니다.