이 글의 목적은 엑셀에서 COUNTBLANK 함수가 빈 셀 개수를 실제와 다르게 계산하는 원인을 유형별로 구분하고, 실무에서 즉시 적용 가능한 진단·정리·공식·자동화 방법을 제공하여 정확한 빈 셀 집계를 가능하게 하는 것이다.
1. 문제 정의: “빈 셀”의 세 가지 해석
COUNTBLANK는 “비어 있는 셀”과 “빈 문자열("")을 반환하는 수식”을 빈 것으로 간주한다. 반면 사람 눈에는 비어 보이지만 실제로는 공백 문자, 제로폭 문자, 단일 따옴표('), 비분리 공백(Non-breaking space, 코드 160) 등 데이터가 존재할 수 있다. 이 차이 때문에 오집계가 발생한다.
| 보이는 상태 | 실제 내용 | COUNTBLANK 결과 | 비고 |
|---|---|---|---|
| 완전 빈 칸 | 값 없음 | 카운트됨 | 정상이다 |
| 빈 문자열 표시 | =IF(A1="","",B1) | 카운트됨 | 수식이 있어도 ""는 빈으로 침 |
| 띄어쓰기만 있음 | 스페이스 하나 이상 | 카운트 안 됨 | 문자 존재 |
| 안 보이는 공백 | CHAR(160) 등 | 카운트 안 됨 | 웹·PDF 복붙 시 흔함 |
| 텍스트 강제 | ' 뒤 빈 문자열 | 카운트 안 됨 | 선행 따옴표 |
| 0 표시 숨김 | 값 0 + 표시 형식 | 카운트 안 됨 | 값 존재 |
| 필터로 숨김 | 숨겨진 행 | 모두 포함 | COUNTBLANK는 숨김 무시 안 함 |
| 병합 셀 | 병합 영역의 비표시 셀 | 예상치와 다름 | 선두 셀 값 기준 |
주의 : “눈으로 빈 칸처럼 보인다”와 “엑셀이 빈 칸으로 인식한다”는 별개이다. 반드시 함수로 판별해야 한다.
2. 빠른 진단 절차
- 대상 범위를 선택한다.
LEN검사로 길이를 확인한다.
길이 0이면 실제 빈 또는 빈 문자열이다.=LET(r,A2:A1000, SUM(--(LEN(r)=0)))- 트림·클린 후 길이를 재확인한다.
이 값이 늘면 숨은 공백·제어문자가 원인이다.=LET(r,A2:A1000, SUM(--(LEN(TRIM(CLEAN(SUBSTITUTE(r,CHAR(160),""))))=0))) COUNTBLANK와 비교한다.
차이가 크면 공백 문자 또는 따옴표 적용 사례가 많다는 의미이다.=COUNTBLANK(A2:A1000)
3. 원인별 해결 레시피
3.1 일반 공백·비분리 공백(CHAR(160)) 제거
웹·PDF에서 복사한 값은 CHAR(160)이 섞여 있는 경우가 많다. 다음 변환으로 정규화한다.
=LET( r, A2:A1000, cleanText, TRIM(CLEAN(SUBSTITUTE(r,CHAR(160)," "))), SUM(--(LEN(cleanText)=0)) )실제 값을 정리하려면 도움열을 만든 뒤 값 붙여넣기 한다.
=TRIM(CLEAN(SUBSTITUTE(A2,CHAR(160)," ")))주의 :
CLEAN은 인쇄 불가 문자 제거에 강하지만 CHAR(160)는 제거하지 못한다. 반드시 SUBSTITUTE로 160을 공백으로 치환한 뒤 TRIM을 적용한다.3.2 단일 따옴표로 강제 텍스트
입력 시작에 '가 존재하면 셀은 빈 칸이 아니다. 수식으로 제거하거나 “텍스트 나누기”를 활용한다.
=IF(LEFT(A2,1)="'", MID(A2,2, LEN(A2)-1), A2)대량 치환은 찾기/바꾸기(Ctrl+H)에서 찾을 내용에 ^'가 아닌 문자 그대로 '를 넣고 바꾸기를 빈 칸으로 수행한다.
3.3 제로폭 문자(Zero-width) 제거
문자열에 ZWSP(U+200B) 등이 포함되면 TRIM으로는 제거되지 않는다. 다음 패턴을 사용한다.
=LET( r, A2, z, SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(r,UNICHAR(8203),""),UNICHAR(8237),""),UNICHAR(8236),""), TRIM(CLEAN(SUBSTITUTE(z,CHAR(160)," "))) )3.4 0을 표시 형식으로 숨김
값 0인데 표시 형식으로 빈 칸처럼 보일 수 있다. 이 경우 COUNTBLANK는 카운트하지 않는다. 진단은 ISTEXT, ISNUMBER, LEN을 병행한다.
=SUM(--(A2:A1000=0)) 주의 : 보고서에서 “빈 칸 수”를 요구하면, 명확히 “값 없음”과 “0값”을 구분하여 정의해야 한다.
3.5 병합 셀 영향
병합된 영역은 선두 셀만 값으로 취급한다. 집계 전 병합을 해제하고 값 채움 또는 “채우기 아래”를 실행한다.
3.6 숨겨진 행/열과 필터
COUNTBLANK는 숨김 여부를 고려하지 않는다. 필터링된 표시 행만 대상으로 하려면 SUBTOTAL 또는 AGGREGATE와 조합한다.
=SUMPRODUCT(SUBTOTAL(103,OFFSET(A2,ROW(A2:A1000)-ROW(A2),0,1)), --(LEN(A2:A1000)=0))SUBTOTAL(103,...)은 표시된 셀만 카운트하는 논리 마스크를 만든다.
4. “정확한 빈 셀”을 엄밀히 세는 대표 공식
4.1 기본형: 눈에 빈 칸처럼 보이는 모든 경우 포함
=LET( r, A2:A1000, t, TRIM(CLEAN(SUBSTITUTE(r,CHAR(160)," "))), SUM(--(t="")) )이 패턴은 빈 값과 빈 문자열, 공백만 있는 문자열을 모두 빈으로 취급한다.
4.2 고급형: 제로폭·방향제어 문자까지 제거
=LET( r, A2:A1000, norm, LAMBDA(x, TRIM(CLEAN(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(x,CHAR(160)," "), UNICHAR(8203),""),UNICHAR(8237),""),UNICHAR(8236),"")))), SUM(--(norm(r)="")) )4.3 동적 배열과 표(Structured Reference) 버전
=LET( r, Table1[고객명], norm, MAP(r, LAMBDA(x, TRIM(CLEAN(SUBSTITUTE(x,CHAR(160)," "))))), SUM(--(norm="")) )4.4 표시된 행만 대상(필터 적용 보고서)
=LET( r, A2:A1000, vis, SUBTOTAL(103, OFFSET(A2, ROW(r)-ROW(A2), 0)), t, TRIM(CLEAN(SUBSTITUTE(r,CHAR(160)," "))), SUMPRODUCT(--(vis=1), --(t="")) )4.5 데이터 검증 규칙: 입력 시 공백만 금지
공백만 입력되는 것을 차단하는 사용자 지정 유효성 검사 수식이다.
=LEN(TRIM(SUBSTITUTE(A2,CHAR(160)," ")))>0주의 : 유효성 검사는 기존 값에는 소급 적용되지 않는다. “잘못된 데이터 원본 순서”에서 “모두 지우기” 후 재적용한다.
5. COUNTBLANK를 대체하는 집계 전략
5.1 COUNTA 기반 역연산
총 행 수에서 비빈이 아닌 셀을 빼는 방식이다. 공백 정규화 후 사용한다.
=ROWS(A2:A1000) - SUM(--(TRIM(CLEAN(SUBSTITUTE(A2:A1000,CHAR(160)," ")))<>""))5.2 SUMPRODUCT/LEN 패턴
=SUMPRODUCT(--(LEN(TRIM(CLEAN(SUBSTITUTE(A2:A1000,CHAR(160)," "))))=0))5.3 조건부 집계와 결합
특정 열이 빈 행만 세기:
=SUMPRODUCT(--(LEN(TRIM(SUBSTITUTE(B2:B1000,CHAR(160)," ")))=0), --(C2:C1000="미완료"))6. 실무 파이프라인: 탐지→정리→검증
6.1 탐지
- 조건부 서식으로 공백만 있는 셀 시각화
=LEN(TRIM(SUBSTITUTE(A2,CHAR(160)," ")))=0 - 보조열로 문제 유형 태깅
=LET(x,A2, IF(x="","빈문자열", IF(LEN(x)=0,"진짜빈", IF(LEN(TRIM(SUBSTITUTE(x,CHAR(160)," ")))=0,"공백문자", IF(LEFT(x,1)="'","선행따옴표","값존재")))))
6.2 정리
파워쿼리 또는 수식 도움열로 정규화한다.
- 수식 정규화:
=TRIM(CLEAN(SUBSTITUTE([@열],CHAR(160)," "))) - 파워쿼리: “값 바꾸기”로
Character.FromNumber(160)치환 후Text.Trim적용
6.3 검증
=COUNTBLANK(정리열) = SUMPRODUCT(--(LEN(정리열)=0))두 값이 일치해야 한다.
7. 파워쿼리(M) 정리 스니펫
// 텍스트 열의 비분리 공백 제거 후 트림 = Table.TransformColumns( 이전단계, {{"열이름", each Text.Trim( Text.Replace(_, Character.FromNumber(160), " ") ), type text}} )null을 빈 문자열로 바꾸면 COUNTBLANK와의 인식 차이가 생길 수 있다. Excel로 로드하기 전 null 유지 또는 빈 문자열 통일을 결정한다.
8. LAMBDA로 “진짜 빈 칸” 판별 사용자 함수
이름 관리자에 다음을 추가하여 재사용한다.
이름: ISBLANKX 참조 대상: =LAMBDA(x, LET( s, TRIM(CLEAN(SUBSTITUTE(x,CHAR(160)," "))), IF(s="", TRUE, FALSE) ))사용 예:
=SUM(--(ISBLANKX(A2:A1000)))9. 보고서 환경에서의 주의점
- 피벗 테이블 값 필드는
빈 문자열과진짜 빈을 동일하게 처리하지 않을 수 있다. 원본을 정규화한 뒤 피벗을 새로고침한다. - 동적 배열 범위가 “흩뿌려진 오류(#SPILL!)”로 막히면 COUNTBLANK 결과도 달라진다. 스필 블록을 확보한다.
- CSV/TSV 불러오기 시 인코딩과 구분자에 따라 공백 문자가 달라진다. 파일 원본을 확인한다.
10. 체크리스트
| 점검 항목 | 권장 조치 | 검증 수식 |
|---|---|---|
| 비분리 공백 | SUBSTITUTE(,CHAR(160)," ") 후 TRIM | LEN(TRIM(SUBSTITUTE(x,CHAR(160)," ")))=0 |
| 제로폭 문자 | UNICHAR 제거 | SUBSTITUTE(x,UNICHAR(8203),"") |
| 선행 따옴표 | LEFT=“'”이면 MID로 제거 | LEFT(x,1)="'" |
| 0값 표시 숨김 | 표시 형식 확인 | ISNUMBER(x) * (x=0) |
| 병합 셀 | 해제 후 값 채움 | 병합 해제 후 COUNTBLANK 재측정 |
| 필터된 데이터 | SUBTOTAL 마스크 적용 | SUBTOTAL(103,OFFSET(...)) |
FAQ
COUNTBLANK와 COUNTA 차이는 무엇인가?
COUNTBLANK는 빈 칸과 빈 문자열을 빈으로 센다. COUNTA는 값이 있는 모든 셀을 센다. 공백만 있는 문자열은 COUNTA에선 값으로 취급한다.
빈 문자열을 빈 칸으로 간주하지 않으려면?
LEN 기반 공식으로 집계한다. 예: =SUM(--(LEN(A2:A1000)=0)) 대신 =SUM(--(A2:A1000=""))는 빈 문자열만 센다.
표시된 행만 대상으로 빈 셀을 세려면?
SUBTOTAL 마스크와 곱한다. 예: =SUMPRODUCT(--(LEN(A2:A1000)=0), SUBTOTAL(103,OFFSET(A2,ROW(A2:A1000)-ROW(A2),0)))를 사용한다.
파워쿼리에서 null과 빈 문자열 차이는?
null은 “값 없음”, 빈 문자열은 길이 0의 값이다. Excel 시트로 출력 시 COUNTBLANK는 빈 문자열을 빈으로 취급하지만, 일부 변환 단계에서 혼재되면 결과가 달라진다.
피벗테이블에서 빈 값이 이상하게 집계된다.
원본을 정규화한 뒤 피벗 새로고침을 수행한다. 데이터 모델을 사용한다면 쿼리 단계에서 공백 정리 후 로드한다.
VBA로 일괄 정리할 수 있나?
가능하다. 아래 매크로는 선택 영역에서 CHAR(160)과 제로폭 문자를 제거한다.
Sub NormalizeSpaces() Dim c As Range, s As String For Each c In Selection If Not IsError(c.Value) Then s = CStr(c.Value) s = Replace(s, Chr(160), " ") s = Replace(s, ChrW(8203), "") s = Replace(s, ChrW(8237), "") s = Replace(s, ChrW(8236), "") s = WorksheetFunction.Trim(WorksheetFunction.Clean(s)) c.Value = s End If Next c End Sub정리: 선택 가이드
- 보고용으로 “보이는 빈 칸”을 세고 싶다 → TRIM·CLEAN·SUBSTITUTE 정규화 후 LEN=0 집계가 적합하다.
- 데이터 품질 점검으로 “실제 값 없음”만 세고 싶다 → 원본에서 빈 문자열 생성 수식 제거 또는 도움열로 빈 문자열을 null에 해당하는 진짜 빈으로 치환한다.
- 필터를 쓴 보고서만 대상 → SUBTOTAL 마스크 결합을 사용한다.