Xử lý ô trống không có nghĩa là điền cho đầy bảng, mà là quyết định với mỗi loại ô trống: để nguyên, điền bằng giá trị suy ra chắc chắn, hay đánh dấu là thiếu để hỏi lại. Một ô trống trong cột "số lượng" có thể là bằng 0, có thể là quên nhập, có thể là đơn đã huỷ. Ba nghĩa đó cần ba cách xử lý khác nhau, và điền nhầm một số 0 vào chỗ quên nhập sẽ làm tổng sai mà không ai nhận ra.
Bài này hướng dẫn cách đếm và phân loại ô trống, xử lý các ô trống sinh ra do gộp ô, điền những chỗ suy ra được từ cột khác, thống nhất cách ghi "thiếu", và đặt ranh giới rõ cho AI: nó giúp tìm và giải thích, không được bịa giá trị. Ví dụ trong bài là ví dụ giả định.
Ô trống mang những nghĩa nào
Trước khi xử lý, hỏi một câu cho từng cột: ô trống ở đây có nghĩa là gì. Bảng dưới gợi ý các nghĩa thường gặp và cách xử lý tương ứng.
| Nghĩa của ô trống | Ví dụ giả định | Cách xử lý |
|---|---|---|
| Không áp dụng | Cột "mã số thuế công ty" của khách cá nhân | Để trống, hoặc ghi "không áp dụng" thống nhất |
| Bằng 0 | Cột "số lượng trả lại" của đơn không trả hàng | Điền 0 nếu chắc chắn, vì tổng và trung bình cần số 0 thật |
| Chưa biết, cần hỏi | Cột "ngày sinh" của khách chưa cung cấp | Để trống và thêm cột cờ "thiếu"; không điền đoán |
| Giống dòng trên | Cột "nhóm hàng" chỉ ghi ở dòng đầu mỗi nhóm, các dòng dưới bỏ trống | Điền xuống bằng giá trị dòng trên |
| Suy ra được từ cột khác | Cột "thành tiền" trống nhưng có đơn giá và số lượng | Tính lại bằng công thức, ghi nguồn |
| Lỗi khi xuất hoặc gộp tệp | Cả một khoảng dòng trống một cột sau khi ghép hai tệp | Quay về tệp nguồn, không vá trong bảng đích |
Một cột có thể chứa nhiều nghĩa cùng lúc. Khi đó, phân nhóm theo cột khác, như trạng thái đơn hay nguồn dữ liệu, rồi xử lý từng nhóm.
Đếm ô trống và bắt ô trống giả
Đếm tỷ lệ thiếu từng cột. Ở dòng tổng phía dưới, =COUNTBLANK(C2:C500) đếm số ô trống của cột C; chia cho tổng số dòng ra tỷ lệ thiếu. Làm cho mọi cột, bạn sẽ có bức tranh nhanh: cột nào gần đủ, cột nào thiếu nhiều đến mức nên hỏi lại cách thu thập.
Ô trông trống nhưng không trống. Ô chứa một dấu cách, hoặc chứa công thức trả về chuỗi rỗng, nhìn y như ô trống. ISBLANK trả về FALSE với cả hai loại, còn COUNTBLANK lại đếm chuỗi rỗng là trống nhưng không đếm ô có dấu cách. Để bắt chung mọi loại, dùng =LEN(TRIM(C2))=0 ở cột phụ.
Ký hiệu thay cho trống. Nhiều bảng dùng "-", "N/A", "không có", "chưa rõ", "x", hay số 0 để đánh dấu thiếu, mỗi người một kiểu. Lọc cột và xem danh sách giá trị khác nhau trong ô lọc để thấy hết các ký hiệu đang dùng. Đây là loại thiếu khó thấy nhất vì COUNTBLANK không đếm chúng.
Ngày giả và số giả. Cột ngày có ô hiện 00/01/1900 hoặc một ngày rất cũ thường là ô số 0 được định dạng ngày, tức là thiếu chứ không phải ngày thật. Cột tuổi có giá trị 0 hoặc 999 cũng vậy. Kiểm bằng MIN và MAX để lộ ra.
Ô trống do gộp ô và tiêu đề nhóm: điền xuống
Bảng xuất từ báo cáo hay bảng soạn để in thường gộp ô hoặc chỉ ghi tên nhóm ở dòng đầu. Để lọc và tính được, cần điền giá trị cho mọi dòng:
- Lưu bản sao. Chọn vùng dữ liệu, bỏ gộp ô bằng Merge and Center rồi chọn Unmerge Cells. Sau khi bỏ gộp, giá trị chỉ còn ở ô trên cùng, các ô dưới trống.
- Chọn cột cần điền, mở Go To Special (phím F5 rồi Special, hoặc trong Find and Select), chọn Blanks. Excel chọn tất cả ô trống trong cột.
- Không bấm chuột vào đâu khác. Gõ dấu bằng rồi bấm phím mũi tên lên, để tạo công thức trỏ tới ô ngay trên ô đang chọn.
- Bấm Ctrl+Enter để điền công thức đó cho mọi ô trống đã chọn cùng lúc. Mỗi ô trống sẽ lấy giá trị của ô trên nó, lan dần xuống hết nhóm.
- Chọn cả cột, sao chép, dán dạng giá trị để công thức không phụ thuộc thứ tự dòng nữa. Bỏ qua bước này thì sắp xếp lại bảng sẽ làm giá trị nhảy lung tung.
Chỉ dùng cách này cho cột mà ô trống thật sự có nghĩa "giống dòng trên". Áp nhầm cho cột số lượng hay ngày sinh là nhân bản dữ liệu sai cho hàng loạt dòng. Trong Power Query có lệnh Fill Down làm việc tương tự và lặp lại được cho tệp mới.
Điền những gì suy ra được, đánh dấu phần còn lại
Giá trị suy ra được là giá trị có quy tắc chắc chắn từ dữ liệu khác trong bảng hoặc từ danh mục chính thức của bạn. Thành tiền bằng đơn giá nhân số lượng; nhóm hàng tra từ mã hàng qua danh mục hàng; tỉnh mới tra từ tỉnh cũ qua bảng tra. Những ô này điền bằng công thức, và nên có một cột "nguồn" ghi "tính lại" hoặc "tra danh mục" để người sau biết đó không phải dữ liệu nhập gốc.
Những ô không có quy tắc chắc chắn thì không điền. Ngày sinh thiếu không đoán từ năm vào học; giới tính thiếu không đoán từ tên; số điện thoại thiếu không lấy của người cùng họ cùng địa chỉ. Với các ô này, để trống và thêm cột cờ, ví dụ "thiếu ngày sinh", để lọc và xử lý dần: hỏi lại khách, đối chiếu hồ sơ giấy, hoặc chấp nhận là không có.
Việc điền giá trị trung bình, giá trị phổ biến nhất hay ước lượng thống kê vào chỗ thiếu là quyết định của bước phân tích, phụ thuộc vào câu hỏi đang trả lời, không thuộc khâu làm sạch. Ở khâu này, giữ cho chỗ thiếu được đánh dấu rõ ràng là đủ; trang AI Phân Tích có bài riêng về cách xử lý dữ liệu thiếu khi phân tích.
Sau khi xử lý, thống nhất một quy ước duy nhất cho chỗ thiếu trong cả bảng, ví dụ để trống thật cộng cột cờ, và thay mọi ký hiệu cũ như "-", "N/A", "chưa rõ" về quy ước đó bằng Find and Replace với tuỳ chọn khớp toàn bộ ô.
Ô trống sinh ra khi ghép hai bảng
Một nguồn ô trống hay bị quên là bước ghép bảng. Khi bạn tra thông tin từ bảng khác sang bằng XLOOKUP hay VLOOKUP, kết quả có thể trống vì hai lý do khác nhau: không tìm thấy dòng tương ứng, hoặc tìm thấy nhưng ô nguồn trống. Hai trường hợp cần xử lý khác nhau, nên phải tách được.
Với XLOOKUP, dùng đối số thứ tư để ghi rõ trường hợp không tìm thấy, ví dụ =XLOOKUP(A2,DanhMuc!A:A,DanhMuc!C:C,"KHÔNG THẤY"). Ô nào ghi "KHÔNG THẤY" là lỗi khớp khoá, thường do mã chưa làm sạch; ô trống còn lại là ô nguồn trống thật.
VLOOKUP có một bẫy riêng: khi tìm thấy dòng nhưng ô nguồn trống, nó trả về số 0 chứ không trả về ô trống. Cột ngày sẽ hiện ngày đầu năm 1900, cột số lượng hiện 0, trông như dữ liệu thật. Nếu dùng VLOOKUP, bọc thêm điều kiện kiểm ô nguồn có trống không, hoặc chuyển sang XLOOKUP nếu Excel của bạn có hàm này.
Sau khi ghép, đếm riêng số ô "KHÔNG THẤY" và số ô trống thật. Số "KHÔNG THẤY" lớn là dấu hiệu khoá hai bên chưa khớp, cần quay lại làm sạch khoá trước khi xử lý ô trống, nếu không bạn sẽ gắn cờ thiếu cho những dữ liệu thật ra vẫn có ở bảng nguồn.
Dùng AI đúng chỗ khi bảng có nhiều ô trống
AI hữu ích trong khâu này, nhưng có một ranh giới không được vượt: AI không điền giá trị cho dữ liệu thật. Những việc nên nhờ:
- Lập kế hoạch xử lý: mô tả tên cột, ý nghĩa và tỷ lệ thiếu từng cột, nhờ AI đề xuất cột nào nên điền theo quy tắc nào, cột nào nên để trống và gắn cờ.
- Viết công thức: nhờ viết công thức đánh dấu ô trống giả, công thức tính lại cột suy ra được, hoặc bước Fill Down trong Power Query.
- Rà ký hiệu thiếu: đưa danh sách các giá trị khác nhau của một cột, không kèm dữ liệu khác, nhờ AI chỉ ra giá trị nào có vẻ là ký hiệu thiếu.
- Soạn câu hỏi bổ sung: nhờ viết biểu mẫu hoặc tin nhắn lịch sự xin khách bổ sung thông tin còn thiếu, nói rõ mục đích sử dụng.
- Không nhờ: "điền nốt các ô trống cho hợp lý". Mô hình ngôn ngữ sẽ tạo ra giá trị trông hợp lý, và đó chính là dữ liệu bịa nằm lẫn trong dữ liệu thật, không ai tách ra được nữa.
Bảng nhiều ô trống thường chứa thông tin cá nhân. Khi nhờ AI lập kế hoạch, chỉ gửi tên cột và con số thống kê, không gửi dòng dữ liệu.
Chặn ô trống từ khâu nhập và kiểm lại
Ô trống ít nhất ở những bảng có khâu nhập chặt. Với cột bắt buộc, dùng Data Validation để không cho bỏ qua, hoặc dùng định dạng có điều kiện tô đỏ ô trống ở cột bắt buộc để người nhập thấy ngay. Biểu mẫu trực tuyến đặt câu hỏi bắt buộc cho các trường cần thiết, và có lựa chọn "không có" rõ ràng cho các trường không áp dụng, để phân biệt được "không có" với "quên trả lời".
Sau khi xử lý, kiểm lại bằng chính bảng tỷ lệ thiếu từng cột đã lập lúc đầu. Cột đã điền xuống phải về 0 ô trống; cột để trống có gắn cờ phải có số cờ bằng số ô trống; cột tính lại phải khớp với công thức trên mọi dòng. So tổng các cột số trước và sau để chắc không có số 0 nào bị điền nhầm làm thay đổi tổng ngoài dự kiến.
Ghi kết quả vào nhật ký: tỷ lệ thiếu trước và sau, quy tắc đã dùng cho từng cột, số dòng còn chờ bổ sung. Khi bảng được chuyển sang người phân tích, nhật ký này cho họ biết con số nào là dữ liệu gốc, con số nào là tính lại, chỗ nào còn thiếu thật.
Câu hỏi thường gặp
Ô trống trong cột số lượng có nên điền số 0 không?
Chỉ điền 0 khi chắc chắn ô trống nghĩa là không có, ví dụ đơn không trả hàng. Nếu ô trống có thể là quên nhập, để trống và gắn cờ, vì số 0 điền nhầm làm tổng và trung bình sai mà không ai phát hiện.
Vì sao ISBLANK báo ô không trống dù nhìn thấy trống?
Vì ô đó chứa một dấu cách, một ký tự ẩn, hoặc công thức trả về chuỗi rỗng. Dùng điều kiện độ dài sau khi TRIM bằng 0 để bắt chung mọi loại ô trông như trống.
Làm sao điền nhanh các ô trống bằng giá trị dòng trên?
Bỏ gộp ô nếu có, chọn cột, dùng Go To Special chọn Blanks, gõ dấu bằng và mũi tên lên, rồi bấm Ctrl+Enter. Sau đó sao chép và dán dạng giá trị. Chỉ dùng cho cột mà ô trống nghĩa là giống dòng trên.
Có nên nhờ AI điền các ô còn thiếu cho đủ bảng không?
Không. AI sẽ tạo ra giá trị trông hợp lý nhưng là dữ liệu bịa, lẫn vào dữ liệu thật và không tách ra được. Chỉ nhờ AI lập kế hoạch xử lý, viết công thức và soạn câu hỏi xin bổ sung thông tin.
Nên dùng ký hiệu gì cho dữ liệu thiếu?
Chọn một quy ước duy nhất cho cả bảng. Cách gọn là để ô trống thật và thêm cột cờ ghi lý do thiếu. Tránh dùng số 0 hay ngày giả để đánh dấu thiếu vì chúng lẫn với giá trị thật trong phép tính.
Tỷ lệ thiếu bao nhiêu thì nên bỏ cột đó?
Không có ngưỡng chung. Nếu cột cần cho công việc, tỷ lệ thiếu cao là dấu hiệu nên sửa cách thu thập. Quyết định bỏ hay giữ cột khi phân tích tuỳ câu hỏi cần trả lời, thuộc bước phân tích sau khi bảng đã sạch.