Excel에서 누락된 값을 찾고 채우는 방법(추측하지 않음)
빈 셀을 빠르게 찾고, 채울지, 플래그를 지정할지, 그대로 둘지 결정하고, 수식이나 AI를 사용하여 Excel에서 누락된 데이터를 완성하세요. 변경 사항에 대한 감사 추적이 가능합니다.
누락된 값은 스프레드시트가 제공하는 가장 조용한 방법입니다. 비용 열의 빈 셀은 단지 하나의 숫자를 잃는 것이 아닙니다. AVERAGE마다 자동으로 축소되고 모든 피벗이 왜곡되며 이익 계산이 오류로 변합니다.
다음은 체계적인 작업 흐름입니다. 모든 빈칸을 찾고, 각각 무엇을 *은(는) 의미합니다.*로 결정하고, 채워야 할 항목만 채우고, 변경된 내용을 기록합니다.
빠른 답변
먼저 셀이 실제로 비어 있는지, 빈 수식 결과인지, 공백인지, 0인지 또는 "해당 사항 없음"인지 식별합니다. 그런 다음 신뢰할 수 있는 규칙이나 소스에서 복구할 수 있는 값만 입력하세요. 해결되지 않은 케이스를 상태 열에 배치하고 원본 데이터를 보존하며 채우기 후 합계를 검증합니다. 기본적으로 모든 공백을 0 또는 평균으로 바꾸지 마십시오.
1단계: 모든 결측값 찾기
세 가지 빠른 방법(가장 빠른 것부터):
스페셜로 이동하세요. 데이터 범위를 선택하고 F5 → 특수… → 공백 → 확인를 누릅니다. 이제 범위의 모든 빈 셀이 선택됩니다. 표시되도록 채우기 색상을 지정합니다.
열별로 계산합니다.
=COUNTBLANK(B2:B1000)
필터링하세요. 필터(Ctrl+Shift+L)를 추가하고 열의 드롭다운을 연 다음 **(공백)**를 확인하여 정확히 어떤 행이 영향을 받는지 확인하세요.
또한 가짜 공백(공백이 포함된 셀 또는 수식에서 반환된 빈 문자열 "")을 살펴보세요. COUNTBLANK는 ""를 계산하지만 특집으로 가기 → 공백는 이를 선택하지 않습니다. 이러한 불일치는 혼란의 전형적인 원인입니다.
=SUMPRODUCT(--(TRIM(B2:B1000)=""))
은 실제 공백과 공백만 있는 셀을 모두 계산합니다.
2단계: 각 공백의 의미 결정
대부분의 사람들이 건너뛰는 단계입니다. 공백은 다음과 같습니다.
| 의미 | 올바른 행동 |
|---|---|
| 데이터가 존재하지만 입력되지 않았습니다 | 소스에서 채우기 |
| 진짜 제로 | 0를 명시적으로 입력 |
| 해당 없음 | 의도적으로 N/A를 텍스트로 표시 |
| 알 수 없음/후속 조치 필요 | 숫자를 만들어내지 말고 신고하세요 |
"알 수 없음"을 가상의 숫자로 채우는 것은 공백으로 두는 것보다 나쁩니다. 눈에 보이는 불확실성을 눈에 보이지 않는 오류로 전환한 것입니다.
3단계: 채워야 할 것을 채워라
위에서 아래로 채우기(범주가 그룹당 한 번씩 나타나는 보고서 내보내기에 일반적임): 범위 F5 → 특수 → 공백를 선택하고 =를 입력한 다음 위쪽 화살표를 누르고 Ctrl+Enter로 확인합니다. 이제 모든 공백은 그 위의 값을 복사합니다. 나중에 선택하여 붙여넣기를 사용하여 값으로 변환합니다.
다른 열에서 계산합니다. 비용이 누락되었지만 수익과 이익이 있는 경우:
=IF(B2="", C2-D2, B2)
다른 시트에서 찾아보세요.
=IF(B2="", XLOOKUP(A2, Ref!A:A, Ref!B:B, "no match"), B2)
4단계: 감사 추적 유지
그러나 공백을 채우고 변경된 셀(강조 색상, "채워진" 상태 열 또는 변경 로그)을 기록합니다. 앞으로는 원본 데이터와 재구성된 데이터를 구별해야 할 것입니다.
유용한 감사 테이블에는 다음이 포함됩니다.
| 필드 | 예 |
|---|---|
| 행 또는 레코드 키 | Order-1042 |
| 열이 변경됨 | Cost |
| 원래 값 | 공백 |
| 새로운 가치 | 42.50 |
| 소스 또는 규칙 | Prices!B:B via SKU |
| 검토 상태 | Verified |
대규모 데이터 세트의 경우 열별로 전후의 누락된 값을 계산합니다. 빈칸 수를 줄이는 것만으로는 충분하지 않습니다. 해결되지 않은 값과 채워진 값의 수가 원래 합계와 일치해야 합니다.
사용 방법 및 시기
- **위에서부터 채우기:**는 빈 셀이 설계상 그룹 레이블을 상속하는 경우에만 해당됩니다.
- **참조 테이블에서 조회:**는 안정적인 키와 신뢰할 수 있는 소스가 존재할 때 가장 좋습니다.
- 다른 필드에서 계산: 관계가 회계 또는 비즈니스 ID인 경우 안전합니다.
- **통계적 대치:**는 분석 모델에 적합하지만 일반적으로 방법이 문서화되지 않은 운영 기록에는 적합하지 않습니다.
- 공백으로 두고 플래그 지정: 값이 실제로 알려지지 않은 경우 정확합니다.
채워진 값이 어디서 왔는지 설명할 수 없는 경우 이를 사실로 다시 기록하지 마세요.
단일 명령 버전
이 전체 워크플로는 통합 문서 내에서 작업하는 도우미에 대한 단일 요청입니다. 사이드바에 Excel용 AI가 열려 있는 상태에서:
"이 표에서 누락된 값을 모두 찾으세요. 가능한 경우 참조 시트에서 비용을 채우고, 실제 0을 0으로 설정하고, 새 상태 열에서 나머지 항목에 플래그를 지정하고, 변경한 내용을 알려주십시오."
추가 기능은 범위를 읽고, 각 채우기를 적용하고, 행별로 상태를 기록하고, 결과를 요약합니다. 이는 누락된 비용이 완료되고 이익 열이 다시 기록되는 홈페이지의 데모와 같습니다. 통합 문서를 작성하기 전에 스냅샷을 찍고 작성한 내용을 확인하기 때문에 "AI가 내 데이터를 채웠다"는 것이 "내 데이터를 추적할 수 없었습니다"를 의미할 필요는 없습니다.
관련 엑셀 안내
누락된 값은 일반적으로 광범위한 정리 작업의 일부입니다. Excel 데이터 정리 체크리스트 완료를 계속 진행하고 Microsoft의 Excel에서 데이터를 정리하는 가장 좋은 방법를 검토하세요.
FAQ
Excel에서 빈 셀을 모두 강조 표시하려면 어떻게 해야 합니까?
범위를 선택하고 F5를 누른 다음 특수 → 공백을 선택한 다음 선택되어 있는 동안 채우기 색상을 적용합니다. =ISBLANK(A2) 수식을 사용한 조건부 서식은 이후 공백을 자동으로 강조 표시합니다.
누락된 값은 0이어야 합니까, 아니면 공백이어야 합니까?
값이 실제로 0인 경우에만 0을 입력하십시오. 공백은 "데이터 없음"을 의미하며 이를 0으로 처리하면 평균과 비율이 변경됩니다. 값을 알 수 없으면 숫자를 만들어내는 대신 알 수 없음으로 플래그를 지정하세요.
AI가 Excel에서 누락된 데이터를 자동으로 채울 수 있나요?
예 — 하지만 세 가지 안전 장치를 고집해야 합니다. 도구는 채워진 각 값의 출처를 어디에로 표시하고, 채워진 셀을 원본과 구별할 수 있도록 표시하고, 쓰기 전에 시트를 백업해야 합니다. Excel용 AI는 세 가지를 모두 수행하며 필요한 경우 전체 변경 사항을 롤백할 수 있습니다.