Cách Chọn Và Tô Sáng Các ô Trống Trong Excel - KetoanMVB

Khi bạn nhận được tệp Excel từ ai đó hoặc nhập tệp từ cơ sở dữ liệu bên ngoài, bạn nên kiểm tra dữ liệu để đảm bảo không có lỗ hổng hoặc thiếu điểm dữ liệu. Trong một tập dữ liệu nhỏ, bạn có thể dễ dàng phát hiện ra tất cả các khoảng trống bằng chính mắt mình. Nhưng nếu bạn có một tệp khổng lồ chứa hàng trăm hoặc thậm chí hàng nghìn hàng, việc xác định chính xác các ô trống là điều không thể.

Hướng dẫn này sẽ dạy cho bạn 4 cách nhanh chóng và dễ dàng để làm nổi bật các ô trống trong Excel để bạn có thể nhận dạng chúng một cách trực quan. Phương pháp nào là tốt nhất? Chà, điều đó phụ thuộc vào cấu trúc dữ liệu, mục tiêu và định nghĩa của bạn về “khoảng trống”.

Chọn và tô sáng các ô trống bằng Chuyển đến Đặc biệt

Phương pháp đơn giản này chọn tất cả các ô trống trong một phạm vi nhất định, sau đó bạn có thể điền bất kỳ màu nào bạn chọn.

Để chọn các ô trống trong Excel, đây là điều bạn cần làm:

1. Chọn phạm vi mà bạn muốn đánh dấu trống. Để chọn tất cả các ô có dữ liệu, bấm vào ô trên bên trái và nhấn Ctrl + Shift + Kết thúc để mở rộng vùng chọn đến ô được sử dụng cuối cùng.
2. Trên Trang Chủ trong tab Chỉnh sửa nhóm, nhấp Tìm và chọn > Đi đặc biệt. Hoặc nhấn F5 và bấm vào Đặc biệt… .
3. bên trong Chuyển đến đặc biệt hộp thoại, chọn Khoảng trống và bấm vào đồng ý. Điều này sẽ chọn tất cả các ô trống trong phạm vi.
4. Với các ô trống đã chọn, nhấp vào Tô màu biểu tượng trên Trang Chủ trong tab Nét chữ nhóm, và chọn màu mong muốn. Làm xong!

Mẹo và ghi chú:

• Các Chuyển đến đặc biệt tính năng chỉ chọn các ô trống thực sự, tức là các tế bào hoàn toàn không chứa gì. Các ô chứa chuỗi rỗng, dấu cách, trả về vận chuyển, ký tự không in, v.v. không được coi là trống và không được chọn. Để làm nổi bật các ô có công thức trả về một chuỗi rỗng (“”) làm kết quả, hãy sử dụng một trong hai Định dạng có điều kiện hoặc là VBA vĩ mô.
• Phương pháp này là tĩnh và tốt nhất là được sử dụng như một giải pháp một lần. Những thay đổi mà bạn thực hiện sau này sẽ không được phản ánh tự động: các khoảng trống mới sẽ không được tô sáng và các khoảng trống cũ mà bạn điền vào các giá trị sẽ được tô màu. Nếu bạn đang tìm kiếm một giải pháp năng động, tốt hơn bạn nên sử dụng Phương pháp định dạng có điều kiện.

Lọc và tô sáng khoảng trống trong một cột cụ thể

Nếu bạn không quan tâm đến các ô trống ở bất kỳ đâu trong bảng mà chỉ muốn tìm và tô sáng các ô hoặc toàn bộ các hàng có khoảng trống trong một cột nhất định, Bộ lọc Excel có thể là giải pháp đúng đắn

Để hoàn thành nó, hãy thực hiện các bước sau:

1. Chọn bất kỳ ô nào trong tập dữ liệu của bạn và nhấp Sắp xếp & Lọc > Bộ lọc trên Trang Chủ chuyển hướng. Hoặc nhấn CTRL + Shift + L phím tắt để bật bộ lọc tự động.
2. Nhấp vào mũi tên thả xuống cho cột mục tiêu và lọc các giá trị trống. Đối với điều này, xóa Chọn tất cả hộp, sau đó chọn (Khoảng trống).
3. Chọn các ô được lọc trong cột khóa hoặc toàn bộ hàng và chọn Lấp đầy màu mà bạn muốn áp dụng.

Trong bảng mẫu của chúng tôi, đây là cách chúng tôi có thể lọc và sau đó tô sáng các hàng nơi các ô SKU trống:

Ghi chú:

• Không giống như phương pháp trước, phương pháp này liên quan đến các công thức trả về chuỗi rỗng (“”) dưới dạng các ô trống.
• Giải pháp này không phù hợp với dữ liệu thường xuyên thay đổi vì bạn sẽ phải dọn dẹp và tô sáng lại với mỗi thay đổi.

Xem thêm:

1. Sửa lỗi Excel ‘Dữ liệu có thể bị mất’
2. 3 thiết lập Excel cơ bản nhưng quan trọng cho người mới bắt đầu
3. Tạo chuỗi ngày tự động điền trong Excel với hàm Sequence
4. Công thức và đồ thị Spearman tương quan xếp hạng

Cách làm nổi bật các ô trống trong Excel bằng Định dạng có điều kiện

Cả hai kỹ thuật được thảo luận trước đây đều đơn giản và ngắn gọn, nhưng chúng có một nhược điểm đáng kể – không phương pháp nào phản ứng với những thay đổi được thực hiện đối với tập dữ liệu. Không giống như họ, Định dạng có điều kiện là một giải pháp động, có nghĩa là bạn cần thiết lập quy tắc chỉ một lần. Ngay khi một ô trống được điền với bất kỳ giá trị nào, màu sẽ lập tức biến mất. Và ngược lại, một khi một ô trống mới xuất hiện, nó sẽ tự động được tô sáng.

Ví dụ 1. Đánh dấu tất cả các ô trống trong một phạm vi

Để làm nổi bật tất cả các ô trống trong một phạm vi nhất định, hãy định cấu hình quy tắc định dạng có điều kiện Excel theo cách này:

1. Chọn phạm vi mà bạn muốn làm nổi bật các ô trống (A2: E6 trong trường hợp của chúng tôi).
2. Trên Trang Chủ trong tab Kiểu dáng nhóm, nhấp Quy tắc mới > Sử dụng công thức để xác định các ô cần định dạng.
3. bên trong Định dạng giá trị trong đó công thức này là đúng hộp, nhập một trong các công thức dưới đây, trong đó A2 là ô phía trên bên trái của phạm vi đã chọn:Để làm nổi bật ô trống hoàn toàn không chứa gì:=ISBLANK(A2)Để cũng nổi bật các ô dường như trống có chứa các chuỗi có độ dài bằng không (“”) được trả về bởi các công thức của bạn:=LEN(A2)=0

   hoặc là

   =A2=""

4. Nhấn vào định dạng nút, chuyển sang Lấp đầy tab, chọn màu nền bạn muốn và nhấn OK.
5. Nhấp chuột đồng ý để lưu quy tắc và đóng cửa sổ hộp thoại chính.

Để biết các bước chi tiết, vui lòng xem Tạo quy tắc định dạng có điều kiện dựa trên công thức trong Excel.

Ví dụ 2. Đánh dấu các hàng có khoảng trống trong một cột cụ thể

Trong trường hợp khi bạn muốn làm nổi bật toàn bộ các hàng có các ô trống trong một cột cụ thể, chỉ cần thực hiện một thay đổi nhỏ trong các công thức được thảo luận ở trên để chúng tham chiếu đến ô trong cột cụ thể đó và đảm bảo khóa tọa độ cột với ký hiệu $.

Ví dụ: để làm nổi bật các hàng có khoảng trống trong cột B, hãy chọn toàn bộ bảng không có tiêu đề cột (A2: E6 trong ví dụ này) và tạo quy tắc với một trong các công thức sau:

Để làm nổi bật ô trống hoàn toàn:

=ISBLANK($B2)

Để làm nổi bật khoảng trống và các tế bào chứa chuỗi rỗng:

=LEN($B2)=0

hoặc là

=$B2=""

Kết quả là, chỉ các hàng có ô SKU trống được tô sáng:

Đánh dấu nếu trống với VBA

Nếu bạn thích tự động hóa mọi thứ, bạn có thể thấy các mã VBA sau hữu ích để tô màu các ô trống trong Excel.

Macro 1: Các ô trống màu

Macro này có thể giúp bạn làm nổi bật các ô trống thực sự hoàn toàn không chứa gì

Để tô màu tất cả các ô trống trong một phạm vi đã chọn, bạn chỉ cần một dòng mã:

Sub Highlight_Blank_Cells() Selection.SpecialCells(xlCellTypeBlanks).Interior.Color = RGB(255, 181, 106) End Sub

Để làm nổi bật các khoảng trống trong bảng tính và phạm vi được xác định trước (phạm vi A2: E6 trên Bảng 1 trong ví dụ bên dưới), đây là mã để sử dụng:

Sub Highlight_Blank_Cells() Dim rng As Range Set rng = Sheet1.Range("A2:E6") rng.SpecialCells(xlCellTypeBlanks).Interior.Color = RGB(255, 181, 106) End Sub

Thay vì màu RGB, bạn có thể áp dụng một trong 8 màu cơ bản chính bằng cách nhập “vb” trước tên màu, ví dụ:

Selection.SpecialCells(xlCellTypeBlanks).Interior.Color = vbBlue

Hoặc bạn có thể chỉ định chỉ số màu, chẳng hạn như:

Selection.SpecialCells(xlCellTypeBlanks).Interior.ColorIndex = 6

Macro 2: Khoảng trống màu và chuỗi trống

Để nhận ra các ô trống trực quan có chứa các công thức trả về các chuỗi trống dưới dạng khoảng trắng, hãy kiểm tra xem Bản văn thuộc tính của từng ô trong phạm vi đã chọn = “” và nếu TRUE, sau đó áp dụng màu.

Đây là mã để làm nổi bật tất cả các khoảng trống và chuỗi trống trong một phạm vi đã chọn:

Sub Highlight_Blanks_Empty_Strings() Dim rng As Range Set rng = Selection   For Each cell In rng If cell.Text = "" Then cell.Interior.Color = RGB(255, 181, 106) Else cell.Interior.ColorIndex = xlNone End If Next End Sub

Cách chèn và chạy macro

Đến thêm một macro vào sổ làm việc của bạn, thực hiện các bước sau:

1. nhấn Alt + F11 để mở Trình soạn thảo Visual Basic.
2. Trong Project Explorer ở bên trái, bấm chuột phải vào sổ làm việc đích, rồi bấm Chèn > Mô-đun.
3. Trong cửa sổ Mã ở bên phải, dán mã VBA.

Đến chạy macro, đây là những gì bạn cần làm:

1. Chọn phạm vi trong bảng tính của bạn.
2. nhấn Alt + F8 để mở Vĩ mô hộp thoại.
3. Chọn macro và nhấp Chạy.

Để biết hướng dẫn chi tiết từng bước, vui lòng xem:

Đó là cách tìm, chọn và tô sáng các ô trống trong Excel. Tôi cảm ơn bạn đã đọc và hy vọng sẽ gặp bạn trên blog của dịch vụ kế toán tại Hà Nội vào tuần tới!