Cách Tìm Các Ô Trống Trong Excel: Hướng Dẫn Chi Tiết Và Thực Hành

Bởi

Có thể bạn quan tâm: Financial Report Format In Excel: Hướng Dẫn Chi Tiết Từ A Đến Z Để Tạo Báo Cáo Tài Chính Chuyên Nghiệp

Mục lục bài viết

Lời mở đầu

Excel là một trong những công cụ phổ biến nhất để xử lý dữ liệu, từ việc quản lý danh sách khách hàng, tính toán tài chính, cho tới phân tích dữ liệu phức tạp. Khi làm việc với một bảng tính lớn, việc xác định các ô trống (empty cells) là một nhiệm vụ quan trọng, giúp bạn:

  • Lọc dữ liệu sạch: Loại bỏ các giá trị thiếu để tránh sai lệch trong phân tích.
  • Điều chỉnh công thức: Tránh lỗi #VALUE! hoặc #DIV/0! do dữ liệu thiếu.
  • Tự động điền dữ liệu: Sử dụng các hàm như IF, IFERROR, VLOOKUP một cách chính xác.
  • Tạo báo cáo: Đảm bảo các biểu đồ, bảng tổng hợp không bị gián đoạn.

Bài viết này sẽ cung cấp cho bạn hướng dẫn chi tiết về cách tìm và xử lý các ô trống trong Excel bằng nhiều phương pháp khác nhau: sử dụng công cụ Find & Replace, Conditional Formatting, hàm Excel, Power Query, và VBA macro. Mỗi phần sẽ kèm theo ví dụ thực tế, bước thực hiện cụ thể, và mẹo tối ưu để bạn có thể áp dụng ngay vào công việc hàng ngày.

Lưu ý: Nội dung được viết dựa trên phiên bản Excel 2026 trở lên, bao gồm cả Office 365/Excel for Microsoft 365. Các bước trong các phiên bản cũ hơn có thể hơi khác, nhưng nguyên tắc chung vẫn giữ nguyên.

Có thể bạn quan tâm: Cách Giữ Cột Cố Định Trong Excel: Hướng Dẫn Chi Tiết Từ Cơ Bản Đến Nâng Cao

1. Tại sao cần tìm các ô trống?

1.1. Ảnh hưởng tới công thức và tính toán

  • Lỗi chia cho 0 (#DIV/0!) khi công thức chia cho một ô trống.
  • Lỗi giá trị (#VALUE!) khi hàm yêu cầu số nhưng nhận được ô trống.
  • Sai lệch kết quả khi tính trung bình, tổng, hoặc các hàm thống kê khác.

1.2. Ảnh hưởng tới báo cáo và biểu đồ

  • Biểu đồ có thể bị gián đoạn nếu dữ liệu không liên tục.
  • Báo cáo tổng hợp (PivotTable) sẽ bỏ qua các ô trống, dẫn đến sai lệch.

1.3. Tối ưu hoá quy trình làm việc

  • Tiết kiệm thời gian khi bạn không phải kiểm tra từng ô một.
  • Tự động hoá quy trình làm sạch dữ liệu trước khi phân tích.

Có thể bạn quan tâm: Cách Sử Dụng Filter By Color Trong Excel: Hướng Dẫn Chi Tiết Từ A Đến Z

2. Phương pháp tìm ô trống bằng tính năng “Find & Replace”

2.1. Bước thực hiện cơ bản

  1. Mở bảng tính và chọn vùng dữ liệu muốn kiểm tra (hoặc nhấn Ctrl + A để chọn toàn bộ sheet).
  2. Vào Home → Find & Select → Find… (hoặc nhấn Ctrl + F).
  3. Trong hộp thoại Find, để không nhập gì trong ô “Find what”.
  4. Nhấn Options để mở rộng các tùy chọn:
  5. Within: Sheet (hoặc Workbook nếu muốn tìm trên toàn bộ file).
  6. Search: By Rows hoặc By Columns tùy nhu cầu.
  7. Look in: Values (để tìm giá trị thực tế, không phải công thức).
  8. Nhấn Find All. Excel sẽ liệt kê tất cả các ô trống trong danh sách phía dưới.
  9. Chọn tất cả (Ctrl + A trong danh sách) và đánh dấu (ví dụ: thay đổi màu nền) hoặc xóa tùy mục đích.

2.2. Thay thế ô trống bằng giá trị mặc định

Nếu bạn muốn điền các ô trống bằng một giá trị cố định (ví dụ: “N/A” hoặc 0):

  1. Mở Find & Replace (Ctrl + H).
  2. Để Find what trống.
  3. Nhập Replace with là giá trị bạn muốn (ví dụ: 0).
  4. Nhấn Replace All. Tất cả ô trống sẽ được thay thế ngay lập tức.

Lưu ý: Khi sử dụng “Replace All”, Excel sẽ thay thế cả các ô trống và các ô chứa công thức trả về chuỗi rỗng (""). Nếu bạn muốn giữ lại công thức, nên dùng phương pháp Conditional Formatting hoặc hàm IFERROR thay vì thay thế trực tiếp.

3. Sử dụng Conditional Formatting để đánh dấu ô trống

Conditional Formatting (Định dạng có điều kiện) cho phép tự động tô màu các ô trống, giúp bạn nhìn nhanh các vị trí cần xử lý.

3.1. Các bước thực hiện

  1. Chọn vùng dữ liệu cần kiểm tra (hoặc toàn bộ sheet bằng Ctrl + A).
  2. Vào Home → Conditional Formatting → New Rule.
  3. Chọn “Use a formula to determine which cells to format”.
  4. Nhập công thức:
    excel =ISBLANK(A1)
  5. Lưu ý: A1 là ô đầu tiên trong vùng đã chọn; công thức sẽ tự động áp dụng cho toàn bộ vùng.
  6. Nhấn Format…, chọn Fill (điền màu) – ví dụ màu vàng nhạt.
  7. Nhấn OK hai lần để hoàn tất.

3.2. Ứng dụng thực tế

  • Kiểm tra danh sách khách hàng: Đánh dấu các ô trống trong cột “Email” để nhanh chóng gửi yêu cầu cập nhật.
  • Kiểm tra dữ liệu bán hàng: Đánh dấu ô trống trong cột “Số lượng” để tránh tính sai khi tổng hợp.

3.3. Loại bỏ Conditional Formatting

Nếu muốn xóa quy tắc định dạng:

  1. Home → Conditional Formatting → Clear Rules → Clear Rules from Selected Cells.

4. Sử dụng các hàm Excel để xác định ô trống

Excel cung cấp một loạt hàm giúp xác định và xử lý ô trống trong công thức.

4.1. Hàm ISBLANK

  • Cú pháp: =ISBLANK(value)
  • Giá trị trả về: TRUE nếu ô trống, FALSE nếu có dữ liệu (kể cả giá trị 0 hoặc chuỗi rỗng "" nếu là công thức trả về).

Ví dụ:

=IF(ISBLANK(B2), "Chưa nhập", B2)

Công thức trên sẽ trả về “Chưa nhập” nếu ô B2 trống, ngược lại trả về giá trị của B2.

4.2. Hàm COUNTA và COUNTBLANK

  • COUNTA(range): Đếm số ô không trống trong một vùng.
  • COUNTBLANK(range): Đếm số ô trống trong một vùng.

Ví dụ:

=COUNTBLANK(A2:A100)

Sẽ trả về số ô trống trong cột A từ hàng 2 đến 100.

4.3. Hàm IFERROR + VLOOKUP để tránh lỗi do ô trống

Khi sử dụng VLOOKUP, nếu giá trị tìm kiếm không tồn tại hoặc ô chứa giá trị rỗng, hàm sẽ trả về lỗi #N/A. Bạn có thể bao bọc bằng IFERROR:

=IFERROR(VLOOKUP(A2, $D$2:$E$100, 2, FALSE), "Không tìm thấy")

4.4. Hàm FILTER (Excel 365) để lọc ra các ô không trống

=FILTER(A2:A100, A2:A100 <> "")

Công thức trên sẽ trả về một mảng chỉ chứa các giá trị không trống trong cột A.

4.5. Hàm AGGREGATE để bỏ qua ô trống trong tính toán

Khi muốn tính trung bình mà không tính các ô trống:

=AGGREGATE(1, 6, A2:A100) ' 1 = AVERAGE, 6 = ignore errors & hidden rows

5. Tìm ô trống bằng Power Query (Get & Transform)

Find Empty Cells In Excel
Find Empty Cells In Excel

Có thể bạn quan tâm: Cách Tìm Và Xử Lý Các Giá Trị Trùng Lặp Trong Excel: Hướng Dẫn Chi Tiết Từ Cơ Bản Đến Nâng Cao

Power Query là công cụ mạnh mẽ để làm sạch dữ liệu trước khi tải vào Excel. Dưới đây là cách lọc và đánh dấu các ô trống trong Power Query.

5.1. Nhập dữ liệu vào Power Query

  1. Chọn Data → Get Data → From Table/Range (hoặc Ctrl + T để tạo Table rồi import).
  2. Power Query Editor sẽ mở ra.

5.2. Lọc các ô trống trong một cột

Giả sử bạn có cột “Email”:

  1. Click vào mũi tên lọc ở tiêu đề cột “Email”.
  2. Bỏ chọn (Blanks) để loại bỏ các hàng có ô trống.
  3. Nhấn OK.

Nếu muốn đánh dấu thay vì loại bỏ:

  1. Thêm cột mới: Add Column → Custom Column.
  2. Đặt tên “EmailStatus”, nhập công thức:
    powerquery if Email = null then "Thiếu Email" else "Đầy đủ"
  3. Nhấn OK. Bạn sẽ có một cột mới cho biết ô trống hay không.

5.3. Thay thế ô trống bằng giá trị mặc định

  1. Chọn cột cần thay thế.
  2. Vào Transform → Replace Values.
  3. Để Value To Find trống, nhập Replace With là giá trị mong muốn (ví dụ: N/A).
  4. Nhấn OK.

5.4. Tải dữ liệu trở lại Excel

Sau khi hoàn thiện, nhấn Home → Close & Load để đưa dữ liệu đã được làm sạch trở lại sheet.

Ưu điểm: Power Query giữ lại lịch sử các bước làm sạch, cho phép bạn tự động cập nhật khi dữ liệu gốc thay đổi.

6. Sử dụng VBA Macro để tự động tìm và xử lý ô trống

Khi làm việc với các file lớn hoặc cần tự động hoá quy trình, VBA (Visual Basic for Applications) là lựa chọn mạnh mẽ.

6.1. Mở cửa sổ VBA

  1. Nhấn Alt + F11 để mở Visual Basic for Applications.
  2. Vào Insert → Module để tạo một module mới.

6.2. Mã VBA cơ bản: Đánh dấu ô trống màu vàng

Sub HighlightBlankCells() Dim rng As Range Dim cell As Range ' Chọn vùng dữ liệu (thay "A1:D100" bằng vùng thực tế) Set rng = ThisWorkbook.Worksheets("Sheet1").Range("A1:D100") Application.ScreenUpdating = False For Each cell In rng If IsEmpty(cell) Then cell.Interior.Color = vbYellow End If Next cell Application.ScreenUpdating = True MsgBox "Hoàn thành! Các ô trống đã được đánh dấu màu vàng.", vbInformation
End Sub
  • IsEmpty(cell): Kiểm tra xem ô có trống không.
  • cell.Interior.Color = vbYellow: Đánh dấu màu vàng.

6.3. Mã VBA: Thay thế ô trống bằng giá trị mặc định

Sub ReplaceBlankWithZero() Dim rng As Range Dim cell As Range Set rng = ThisWorkbook.Worksheets("Sheet1").UsedRange Application.ScreenUpdating = False For Each cell In rng If IsEmpty(cell) Then cell.Value = 0 ' Thay 0 bằng giá trị bạn muốn End If Next cell Application.ScreenUpdating = True MsgBox "Thay thế ô trống bằng 0 hoàn tất.", vbInformation
End Sub

6.4. Mã VBA: Xóa hàng có bất kỳ ô trống nào

Sub DeleteRowsWithBlank() Dim ws As Worksheet Dim lastRow As Long Dim i As Long Set ws = ThisWorkbook.Worksheets("Sheet1") lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row Application.ScreenUpdating = False For i = lastRow To 1 Step -1 If Application.WorksheetFunction.CountBlank(ws.Rows(i)) > 0 Then ws.Rows(i).Delete End If Next i Application.ScreenUpdating = True MsgBox "Đã xóa các hàng có ô trống.", vbInformation
End Sub
  • CountBlank: Đếm số ô trống trong một hàng.
  • Vòng lặp từ dưới lên để tránh lỗi khi xóa hàng.

6.5. Cách chạy macro

  1. Trở lại Excel (Alt + Q).
  2. Vào Developer → Macros (hoặc Alt + F8), chọn macro và nhấn Run.
  3. Đối với người dùng chưa bật tab Developer, vào File → Options → Customize Ribbon và tick Developer.

Lưu ý bảo mật: Khi lưu file có macro, chọn định dạng .xlsm và luôn bật Macro Settings phù hợp để tránh rủi ro bảo mật.

7. Kịch bản thực tế: Kiểm tra dữ liệu bán hàng

Giả sử bạn có bảng dữ liệu bán hàng với các cột: Ngày, Mã SP, Số lượng, Giá, Khách hàng. Bạn muốn:

  1. Đánh dấu các ô trống trong cột Số lượng và Giá.
  2. Thay thế ô trống trong cột Khách hàng bằng “Khách vãng lai”.
  3. Xóa các hàng có ô trống trong cột Mã SP.

7.1. Sử dụng Conditional Formatting

  • Chọn cột Số lượng → Conditional Formatting → New Rule → Formula: =ISBLANK(C2) → Đặt màu nền đỏ.
  • Lặp lại cho cột Giá.

7.2. Thay thế bằng Find & Replace

  • Chọn cột Khách hàng.
  • Ctrl + H → Find what: (để trống) → Replace with: Khách vãng lai → Replace All.

7.3. Xóa hàng có ô trống trong cột Mã SP bằng VBA

Sub DeleteRowsMissingProductCode() Dim ws As Worksheet, lastRow As Long, i As Long Set ws = ThisWorkbook.Worksheets("SalesData") lastRow = ws.Cells(ws.Rows.Count, "B").End(xlUp).Row Application.ScreenUpdating = False For i = lastRow To 2 Step -1 ' Bắt đầu từ hàng 2 (giả sử hàng 1 là tiêu đề) If IsEmpty(ws.Cells(i, "B")) Then ws.Rows(i).Delete Next i Application.ScreenUpdating = True MsgBox "Xóa các hàng không có Mã SP hoàn tất.", vbInformation
End Sub

Kết hợp các bước trên, bạn sẽ có một bảng dữ liệu sạch, đầy đủ, sẵn sàng cho việc phân tích và lập báo cáo.

8. Mẹo và lưu ý khi làm việc với ô trống

MẹoMô tảKhi nào dùng
Sử dụng Ctrl + G → Special → BlanksChọn nhanh tất cả ô trống trong vùng đã chọn.Khi muốn xóa hoặc điền nhanh.
Kiểm tra công thức trả về chuỗi rỗng ("")ISBLANK không nhận diện công thức trả về "" là trống. Dùng LEN(TRIM(cell))=0 để kiểm tra.Khi có công thức như =IF(A1>0, A1, "").
Sử dụng Data ValidationNgăn người dùng nhập ô trống bằng cách đặt Required.Khi thiết kế form nhập liệu.
Tạo Table (Ctrl + T)Khi dữ liệu được chuyển thành Table, Excel tự động mở rộng công thức và dễ dàng lọc ô trống.Khi làm việc với dữ liệu động, thêm/sửa thường xuyên.
Đánh dấu bằng iconConditional Formatting → Icon Sets → chọn “3 Symbols” và thiết lập rule “Blanks”.Khi muốn hiển thị trực quan hơn trong dashboard.

8.1. Khi ISBLANK không hoạt động như mong đợi

  • Lý do: Ô chứa công thức trả về chuỗi rỗng ("") sẽ không được ISBLANK coi là trống.
  • Giải pháp: Dùng công thức:
    excel =IF(LEN(TRIM(A1))=0, "Trống", "Có dữ liệu")
    Hoặc trong VBA:
    vba If Len(Trim(cell.Value)) = 0 Then ' Xử lý ô trống End If

8.2. Xử lý ô trống trong các hàm mảng (Array Formulas)

Khi dùng hàm mảng như SUMPRODUCT, COUNTIFS, bạn có thể cần loại bỏ ô trống:

=SUMPRODUCT((A2:A100<>"")(B2:B100))

Hàm trên sẽ bỏ qua các ô trống trong cột A trước khi tính tổng B.

9. Tổng kết

Việc tìm và xử lý các ô trống trong Excel không chỉ giúp dữ liệu sạch hơn mà còn ngăn ngừa lỗi trong công thức, cải thiện độ tin cậy của báo cáo và tăng hiệu suất làm việc. Bài viết đã trình bày nhiều phương pháp từ cơ bản (Find & Replace, Conditional Formatting) tới nâng cao (Power Query, VBA), kèm theo ví dụ thực tế và mẹo tối ưu.

Bạn có thể lựa chọn phương pháp phù hợp với quy mô và tính chất công việc:

  • Dữ liệu nhỏ: Find & Replace hoặc Conditional Formatting.
  • Bảng tính trung bình: Hàm ISBLANK, COUNTBLANK, Conditional Formatting.
  • Dữ liệu lớn, đa nguồn: Power Query.
  • Tự động hoá, lặp lại: VBA macro.

Hãy áp dụng những kiến thức này ngay hôm nay để nâng cao chất lượng dữ liệu và tối ưu hoá quy trình trong công việc của bạn. Chúc bạn thành công!

⚠️
LƯU Ý QUAN TRỌNG: Miễn trừ trách nhiệm: Mặc dù chúng tôi luôn nỗ lực để đảm bảo tính chính xác và cập nhật của thông tin, nhưng các quy định về giáo dục và tuyển sinh có thể thay đổi theo thời gian. BigSchool không chịu trách nhiệm về bất kỳ sai sót nào hoặc kết quả phát sinh từ việc sử dụng thông tin trên trang này. Độc giả nên liên hệ trực tiếp với nhà trường hoặc cơ quan chức năng để có thông tin mới nhất. Ngoài ra, BigSchool tuyệt đối không bán sản phẩm, khóa học hay cung cấp dịch vụ được nhắc đến trong bài.

Viết một bình luận