이 글의 목적은 엑셀에서 MIN, MAX 함수가 잘못된 결과를 반환하거나 빈값처럼 보이는 상황을 숫자·텍스트 혼합 문제 관점에서 진단하고, 데이터 정규화·수식·Power Query·서식·VBA 등 실무 전 과정을 통해 재발 방지까지 완결하는 방법을 제시하는 것이다.
1. 증상 정의와 원인 개요
MIN, MAX 함수는 숫자형만 계산 대상에 포함하는 것이 원칙이다. 범위 내에 숫자로 보이지만 실제로는 텍스트로 저장된 값이 섞여 있으면 다음과 같은 현상이 발생한다.
- 최솟값(MIN)이 비정상적으로 큰 값으로 계산되거나, 최대값(MAX)이 비정상적으로 작은 값으로 계산된다.
- 정렬 시 숫자처럼 보이는 값이 텍스트 블록으로 따로 묶여 정렬된다.
- SUM은 정상인데 MIN·MAX만 비정상으로 보이는 등 함수별 결과 불일치가 발생한다.
주요 원인은 다음과 같다.
- 숫자처럼 보이는 텍스트(선행·후행 공백, 비가시문자, 단위 포함, 특수 기호 포함)이다.
- 지역 설정 차이로 천 단위 구분기호(,)와 소수 구분기호(.)가 뒤바뀐 텍스트이다.
- 마이너스 기호가 표준 하이픈(-, U+002D)이 아닌 유니코드 특수 문자(예: U+2212)이다.
- 붙여넣기(웹, ERP, PDF) 과정에서 비표준 공백(Non-breaking space, U+00A0)이 섞였다.
- 숫자와 텍스트 혼합 열에서 자동 서식이 숫자를 텍스트로 고정했다.
주의 : 셀 서식은 표시 형식일 뿐 값의 유형을 바꾸지 않는다. 셀 서식을 숫자로 바꿔도 값이 텍스트면 MIN·MAX는 여전히 제외한다.
2. 빠른 진단 체크리스트
| 점검 항목 | 방법 | 판정 기준 |
|---|---|---|
| 셀 정렬 | 홈 탭에서 맞춤 확인 | 기본 설정이면 숫자는 우측 정렬, 텍스트는 좌측 정렬이다. |
| 오류 표시 삼각형 | 셀 좌측 상단 초록 삼각형 확인 | “숫자가 텍스트로 저장됨” 알림이면 텍스트이다. |
| 수식 평가 | =ISTEXT(A2), =ISNUMBER(A2) | TRUE/FALSE로 데이터 유형을 즉시 확인한다. |
| 길이 확인 | =LEN(A2) | 숫자에 불필요한 공백·비가시문자 존재 여부를 추정한다. |
| 문자 코드 | =CODE(MID(A2,ROW($1:$5),1)) 배열 | 비표준 문자 코드 검출에 사용한다. |
3. 수식 기반 일시 해결
보고서를 급히 마감해야 하거나 원본 데이터를 바꾸기 어렵다면 수식으로 숫자만 강제 추출하여 MIN·MAX를 계산한다.
3.1 간단 강제 변환(이중 음수 연산자)
=MIN(IFERROR(--A2:A1000,"")) =MAX(IFERROR(--A2:A1000,"")) 설명: 이중 음수 연산자(--)는 숫자로 변환 가능한 텍스트를 강제 숫자로 바꾼다. 변환 실패 시 빈 문자열로 대체하여 계산에서 제외한다. 동적 배열을 지원하지 않는 버전은 Ctrl+Shift+Enter로 배열 수식 입력을 한다.
주의 : 단위(예: "kg", "m")나 문자 혼입이 있는 셀은
--로 변환되지 않는다. 아래 정규화 절차를 추가해야 한다.3.2 지역 구분기호 이슈 대응
소수점이 콤마인 지역 데이터(예: "1,25")를 점 소수로 바꾸어 계산한다.
=LET( rng, A2:A1000, arr, SUBSTITUTE(SUBSTITUTE(rng,CHAR(160)," "),",","."), MIN(IFERROR(--TRIM(arr),"")) ) 설명: NBSP(160)를 공백으로 치환하고, 쉼표를 점으로, TRIM으로 여백 제거 후 숫자 강제 변환한다.
3.3 단위·특수 문자 제거 후 계산
=LET( rng, A2:A1000, s1, SUBSTITUTE(rng,"kg",""), s2, SUBSTITUTE(s1,"m",""), s3, SUBSTITUTE(SUBSTITUTE(s2,UNICHAR(8722),"-"),CHAR(160)," "), clean, TRIM(CLEAN(s3)), MIN(IFERROR(--clean,"")) ) 설명: CLEAN으로 제어문자 제거, UNICHAR(8722)로 유니코드 마이너스(−)를 표준 하이픈으로 치환한다.
3.4 NUMBERVALUE로 로캘 무시 숫자 파싱
=LET( rng, A2:A1000, parsed, NUMBERVALUE(rng, ".", ","), /* 소수구분, 천단위 구분 */ MIN(IFERROR(parsed,"")) ) 설명: NUMBERVALUE는 문자열 숫자를 지정한 구분기호 체계로 해석한다. 국제 혼합 데이터 정리 시 안전하다.
4. 원본 데이터 영구 정규화
근본 해결은 원본을 숫자형으로 통일하는 것이다. 다음 단계별로 실행한다.
4.1 숨은 문자 제거
- 보조열에 다음 수식을 입력한다.
=TRIM(CLEAN(SUBSTITUTE(SUBSTITUTE(A2,CHAR(160)," "),UNICHAR(8722),"-"))) - 필요 시 단위를 제거한다.
=LET(t,TRIM(CLEAN(SUBSTITUTE(A2,CHAR(160)," "))), t1,SUBSTITUTE(SUBSTITUTE(t,"kg",""),"m",""), TRIM(t1)) - 값을 복사하여 원래 열에 “값으로 붙여넣기” 한다.
주의 : 값 붙여넣기 뒤에도 셀 앞에 작은 초록 삼각형이 남으면 변환이 덜 된 것이다. 다음 단계를 이어서 적용한다.
4.2 텍스트를 숫자로 일괄 변환
- 방법 A: 1을 곱하기. 빈 셀에 1 입력 → 복사 → 대상 범위 선택 → 선택하여 붙여넣기 → 연산에서 “곱하기”.
- 방법 B: 0을 더하기. 동일 절차로 “더하기”.
- 방법 C: 데이터 탭 → 텍스트 나누기 → 바로 마침. 내부 파서가 숫자형으로 강제 변환한다.
4.3 지역 구분기호 통일
쉼표 소수 데이터를 점 소수로 바꿔야 하는 경우 다음을 적용한다.
- Ctrl+H 찾기/바꾸기에서 , → .로 전체 치환한다.
- 파일 → 옵션 → 고급 → “소수 구분 기호 사용” 해제 후 원하는 구분 기호 지정으로 임시 변환한다.
주의 : 천 단위 구분기호가 포함된 문자열(예: "1,234.56")을 일괄 치환하면 값이 망가질 수 있다. NUMBERVALUE를 먼저 사용해 안전하게 변환한 뒤 값 붙여넣기를 권장한다.
4.4 유니코드 마이너스 통합
다음 수식으로 하이픈과 마이너스를 표준화한다.
=SUBSTITUTE(SUBSTITUTE(A2,UNICHAR(8722),"-"),UNICHAR(8211),"-") 이후 곱하기 1 또는 VALUE로 숫자 변환한다.
5. 재계산용 견고한 MIN·MAX 패턴
원본 정리가 끝났더라도 향후 유입 데이터가 불규칙하면 다시 문제가 생긴다. 다음 패턴으로 범위를 감싸 예외를 방지한다.
5.1 예외 무시 최소·최대
=LET( rng, A2:A100000, txt, TRIM(CLEAN(SUBSTITUTE(SUBSTITUTE(rng,CHAR(160)," "),UNICHAR(8722),"-"))), val, IFERROR(NUMBERVALUE(txt,".",","),IFERROR(--txt,"")), MIN(IF(val<>"",val)) ) =LET( rng, A2:A100000, txt, TRIM(CLEAN(SUBSTITUTE(SUBSTITUTE(rng,CHAR(160)," "),UNICHAR(8722),"-"))), val, IFERROR(NUMBERVALUE(txt,".",","),IFERROR(--txt,"")), MAX(IF(val<>"",val)) ) 설명: NUMBERVALUE 우선, 실패 시 -- 강제, 그래도 실패하면 제외한다.
5.2 조건부 최소·최대
음수 제외, 0 초과 등 조건이 있으면 FILTER를 결합한다.
=LET( rng, A2:A100000, v, IFERROR(--rng,""), MIN(FILTER(v, v<>"" * v>0)) ) 6. 대용량·정형 데이터는 Power Query로 정리
수십만 행 이상, 주기적 갱신, 여러 파일 병합에는 Power Query가 적합하다.
- 데이터 탭 → 데이터 가져오기 → 파일 또는 폴더 선택 → Power Query 편집기에서 열 선택.
- 변환 → 형식 → 텍스트 → 값을 바꾸기에서 U+00A0를 일반 공백으로 치환, U+2212를 하이픈으로 치환한다.
- 변환 → 값 바꾸기에서 단위 문자열 제거 후 “데이터 형식”을 고정소수점 숫자로 설정한다.
- 홈 → 닫기 및 로드. 로드 후 표 범위에서 MIN·MAX 계산을 수행한다.
필요 시 사용자 지정 열에서 정규식 유사 치환을 단계로 기록한다.
// Power Query M 예시 = Table.TransformColumns( Source, { {"측정값", each Number.From( Text.Replace( Text.Replace( Text.Replace(Text.Clean(_), Character.FromNumber(160), " "), "kg","" ), "−","-" ) ), type number } } ) 주의 : Power Query에서 “형식 감지”가 문자열 내부의 단위를 해석하지 못하면 오류가 발생한다. 반드시 치환 단계를 먼저 적용한 뒤 형식을 숫자로 지정한다.
7. 시각적 검증과 품질관리(QA)
- 정렬 테스트: 같은 열을 오름차순 정렬했을 때 텍스트 블록이 따로 분리되지 않아야 한다.
- 데이터 유효성 검사: 숫자만 허용으로 제한하여 재발을 방지한다.
데이터 > 데이터 도구 > 데이터 유효성 검사 허용: 정수 또는 소수 데이터: 크거나 같음 최소: 0 - 조건부 서식으로 텍스트 숫자 탐지.
수식 사용: =ISTEXT(A2) 서식: 채우기 색 연한 노랑 8. 자동 정규화 매크로(선택)
반복 작업이 많다면 VBA로 텍스트 숫자 정규화 후 숫자로 변환한다.
Sub NormalizeNumbers() Dim rng As Range, c As Range On Error Resume Next Set rng = Application.Selection On Error GoTo 0 If rng Is Nothing Then Exit Sub For Each c In rng.Cells Dim s As String s = CStr(c.Value) s = WorksheetFunction.Clean(s) s = Replace(s, Chr(160), " ") s = Replace(s, "−", "-") ' U+2212 s = Replace(s, "kg", "") s = Replace(s, "m", "") s = Trim(s) If IsNumeric(s) Then c.Value = CDbl(s) Else ' 로캘 혼합 처리 s = Replace(s, ".", "#") s = Replace(s, ",", ".") s = Replace(s, "#", "") If IsNumeric(s) Then c.Value = CDbl(s) End If Next c End Sub 주의 : 대량 치환은 데이터 의미를 바꿀 수 있다. 운영 데이터베이스에 반영하기 전 샘플 검증과 백업을 수행한다.
9. 대표 사례별 처방 요약
| 사례 | 원인 | 해결 절차 |
|---|---|---|
| 웹에서 복사한 수치가 MIN에 미반영 | NBSP, 유니코드 마이너스 | SUBSTITUTE(CHAR(160)," "), UNICHAR(8722)→"-", 곱하기 1이다. |
| 유럽형 소수 “1,25” 혼입 | 로캘 불일치 | NUMBERVALUE(텍스트,".",",")로 파싱 후 값 붙여넣기이다. |
| 단위 포함 “15 kg” | 숫자+문자 혼합 | SUBSTITUTE로 단위 제거, TRIM·CLEAN 후 -- 변환이다. |
| 정렬 시 값이 두 묶음으로 분리 | 텍스트 숫자 존재 | 텍스트 나누기 즉시 마침 또는 곱하기 1로 일괄 변환이다. |
| MAX가 너무 작게 계산 | 큰 값이 텍스트로 제외 | 예외 무시 패턴으로 재계산 후 원본을 숫자로 통일이다. |
10. 재발 방지 설계
- 입력 단계에서 데이터 유효성으로 숫자만 허용한다.
- Power Query 파이프라인에서 문자 정리 단계와 형식 강제를 고정한다.
- 보고서 수식은 정규화 → 계산 2단계 LET 패턴으로 유지한다.
- 외부 데이터 소스별(ERP, CSV, 웹) 정규화 스크립트를 표준화한다.
FAQ
SUM은 정상인데 MIN·MAX만 이상한 이유는 무엇인가?
SUM은 텍스트를 숫자로 간주하지 않지만, 일부 상황에서 범위 참조와 자동 변환의 영향으로 기대와 다른 결과가 나올 수 있다. 반면 MIN·MAX는 텍스트를 값으로 포함하지 않기 때문에 숫자로 보이는 텍스트가 섞이면 극단값이 제외되어 비정상으로 보인다. 텍스트 숫자를 숫자형으로 변환하면 해결된다.
셀 서식을 숫자로 바꿨는데 여전히 안 된다.
서식은 표시만 바꾼다. 값 유형은 ISTEXT, ISNUMBER로 확인해야 한다. 곱하기 1, 텍스트 나누기, NUMBERVALUE 등으로 실제 값을 숫자로 변환해야 한다.
CSV를 열면 다시 텍스트가 된다.
CSV는 형식을 저장하지 않는다. 가져오기 마법사나 Power Query에서 형식을 지정하거나, 열기 직후 텍스트 나누기로 강제 변환 절차를 포함한다.
유니코드 마이너스 문제를 빠르게 찾는 방법은?
길이(LEN)로 음수 값 길이를 비교하거나, CODE/MID 배열로 각 문자의 코드값을 확인한다. 하이픈(-)은 45, 유니코드 마이너스는 8722이다.
로캘 혼합 데이터에서 안전한 단일 수식은?
NUMBERVALUE를 우선 사용하고 실패 시 강제 변환으로 폴백하는 LET 패턴이 안전하다. 본문 5.1 수식을 사용한다.