Trong hướng dẫn này, tôi sẽ thảo luận cách làm nổi bật ô chứa văn bản từ danh sách trong Excel. Tôi sẽ dùng Định dạng có điều kiện (Conditional Formatting) để làm nổi bật ô. Hơn nữa, tôi sẽ dùng nhiều hàm và công thức Excel làm Định dạng có điều kiện để làm nổi bật ô.
Bạn có thể tải xuống sổ làm việc thực hành chúng tôi dùng để chuẩn bị bài viết này.
7 Cách Làm nổi bật ô chứa Văn bản từ Danh sách
1. Dùng hàm COUNTIF làm nổi bật ô có Văn bản từ Danh sách
Bạn có thể dùng hàm COUNTIF làm nổi bật ô có văn bản từ danh sách. Ví dụ, tôi có danh sách Trái cây 1 (Fruit 1) (B4:B14) chứa nhiều tên trái cây. Trong danh sách khác, Trái cây 2 (Fruit 2) (D4:D9), tôi có tên trái cây khác. Bây giờ tôi sẽ làm nổi bật tên trái cây của Trái cây 2 trong danh sách Trái cây 1.
Các bước:
- Trước tiên, chọn bộ dữ liệu (B5:B14).
- Đi Trang chủ (Home) > Định dạng có điều kiện (Conditional Formatting) (nhóm Kiểu (Styles)).
- Tiếp theo, đi Quy tắc mới (New Rule) từ menu thả xuống Định dạng có điều kiện.
- Cửa sổ Quy tắc định dạng mới (New Formatting Rule) sẽ hiện ra. Chọn Loại quy tắc: Sử dụng công thức xác định ô cần định dạng (Use a formula to determine which cells to format). Nhập công thức dưới vào trường: Định dạng giá trị nơi công thức này đúng (Format values where this formula is true). Sau đó, nhấp Định dạng (Format).
=COUNTIF($D$5:$D$9,B5)- Chọn màu làm nổi bật từ tab Điền (Fill). Nhấp OK.
- Lại nhấp OK đóng hộp thoại.
- Cuối cùng, bạn thấy tất cả ô chứa văn bản từ danh sách Trái cây 2 được làm nổi bật.
Đọc thêm: Cách Làm nổi bật Ô trong Excel (5 Phương pháp)
2. Áp dụng hàm MATCH Excel làm nổi bật ô có Văn bản từ Danh sách
Bây giờ dùng hàm MATCH làm nổi bật ô có văn bản từ danh sách.
Các bước:
- Trước tiên, chọn bộ dữ liệu (B5:C14).
- Đi Trang chủ > Định dạng có điều kiện > Quy tắc mới.
- Cửa sổ Quy tắc định dạng mới hiện ra. Chọn Loại quy tắc: Sử dụng công thức xác định ô cần định dạng. Nhập công thức dưới vào trường: Định dạng giá trị nơi công thức này đúng. Sau đó, nhấp nút Định dạng và chọn màu làm nổi bật từ tab Điền (Fill).
=MATCH($B5,$E$5:$E$9,0)- Nhấp OK > OK đóng hộp thoại.
- Kết quả: Tất cả ô chứa văn bản từ danh sách Trái cây 2 được làm nổi bật trong Trái cây 1.
Đọc thêm: Cách Làm nổi bật Văn bản được chọn trong Excel (8 Cách)
3. Kết hợp hàm SUM & COUNTIF làm nổi bật ô
Lần này tôi dùng kết hợp hàm làm nổi bật ô chứa văn bản từ danh sách khác. Ví dụ, kết hợp hàm SUM cùng COUNTIF làm nổi bật ô. Ở đây, tôi có bộ dữ liệu Sách (Books) (B4:B12) chứa tên sách. Trong danh sách khác Văn bản (Text) (D5:D8), tôi có danh sách từ đơn lẻ. Vậy bây giờ tôi làm nổi bật từ của Văn bản trong bộ dữ liệu Sách.
Các bước:
- Trước tiên, chọn bộ dữ liệu (B5:B12).
- Đi tab Trang chủ > chọn menu thả xuống Định dạng có điều kiện.
- Tiếp theo, chọn Quy tắc mới từ menu thả xuống Định dạng có điều kiện.
- Cửa sổ Quy tắc định dạng mới hiện ra. Chọn Loại quy tắc: Sử dụng công thức xác định ô cần định dạng. Nhập công thức dưới vào trường: Định dạng giá trị nơi công thức này đúng. Sau đó, nhấp nút Định dạng và chọn màu làm nổi bật từ tab Điền.
=SUM(COUNTIF(B5,"*"&$D$5:$D$8&"*"))- Nhấp OK > OK đóng hộp thoại.
Cuối cùng, ô chứa văn bản từ danh sách Văn bản được làm nổi bật trong Sách.
Ở đây, hàm COUNTIF trả về số ô trong dải thỏa mãn điều kiện. Và hàm SUM cộng các số trong dải.
Đọc thêm: Cách Làm nổi bật Ô trong Excel dựa trên Giá trị (9 Phương pháp)
4. Dùng hàm COUNT & SEARCH làm nổi bật ô chứa Văn bản từ Danh sách
Tương tự Phương pháp 3, tôi dùng kết hợp hàm làm nổi bật ô. Ví dụ, dùng kết hợp COUNT và SEARCH làm nổi bật ô có văn bản từ danh sách khác. Lần này, tôi lại dùng hai danh sách tên trái cây làm bộ dữ liệu.
Các bước:
- Ban đầu, chọn bộ dữ liệu (B5:B14).
- Đi Trang chủ > Định dạng có điều kiện > Quy tắc mới.
- Hộp thoại Quy tắc định dạng mới hiện ra. Chọn Loại quy tắc: Sử dụng công thức xác định ô cần định dạng. Nhập công thức dưới vào trường: Định dạng giá trị nơi công thức này đúng.
=COUNT(SEARCH(D$5:D$9,B5))- Sau đó, nhấp nút Định dạng chọn màu làm nổi bật từ tab Điền. Nhấp OK > OK đóng hộp thoại.
Kết quả: Ô chứa tên trái cây từ danh sách Trái cây 2 được làm nổi bật trong Trái cây 1.
Ở đây, hàm SEARCH đếm số ký tự tại vị trí ký tự hoặc chuỗi văn bản cụ thể đầu tiên được tìm thấy, đọc trái sang phải. Mặt khác, hàm COUNT đếm số ô trong dải chứa số.
Đọc thêm: Cách Làm nổi bật Ô dựa trên Văn bản trong Excel [2 Phương pháp]
Đọc tương tự:
- Cách Làm nổi bật Cột trong Excel (3 Phương pháp)
- Điền màu ô dựa trên Phần trăm trong Excel (6 Phương pháp)
- Cách Làm nổi bật Mỗi 5 Hàng trong Excel (4 Phương pháp)
- Làm nổi bật Ô dùng Câu lệnh If trong Excel (7 Cách)
- Cách So sánh 2 Trang tính Excel và Làm nổi bật Khác biệt (7 Cách)
5. Áp dụng Công thức đơn giản và Danh sách thả xuống (Drop Down List) làm nổi bật ô từ Danh sách
Lần này, tôi dùng công thức đơn giản và danh sách thả xuống làm nổi bật ô. Mục đích này, tôi tạo danh sách thả xuống. Sau đó trong quy tắc Định dạng có điều kiện, tôi đặt công thức. Lần này, tôi lại dùng hai danh sách tên trái cây làm bộ dữ liệu.
Các bước:
- Trước tiên, tạo danh sách thả xuống từ tên trái cây ở Ô D5.
- Chọn bộ dữ liệu (B5:B14).
- Đi Trang chủ > Định dạng có điều kiện.
- Tiếp theo, đi menu thả xuống Định dạng có điều kiện > Quy tắc mới.
- Hộp thoại Quy tắc định dạng mới hiện ra. Chọn Loại quy tắc: Sử dụng công thức xác định ô cần định dạng. Nhập công thức dưới vào trường: Định dạng giá trị nơi công thức này đúng. Nhấp nút Định dạng chọn màu làm nổi bật từ tab Điền.
=$B5=$D$5- Nhấp OK > OK đóng hộp thoại.
Kết quả: Ô chứa trái cây từ danh sách thả xuống được làm nổi bật.
- Phương pháp này hoạt động động nghĩa là nếu bạn đổi tên trái cây từ danh sách thả xuống, ô làm nổi bật thay đổi tương ứng.
Đọc thêm: Cách Làm nổi bật từ Trên xuống Dưới trong Excel (5 Phương pháp)
6. Hàm ISNUMBER và FIND Excel làm nổi bật ô từ Danh sách
Bây giờ, tôi dùng kết hợp hàm và danh sách thả xuống làm nổi bật ô. Ví dụ, dùng kết hợp ISNUMBER và FIND làm nổi bật ô có văn bản từ danh sách khác. Lần này, tôi lại dùng hai danh sách tên trái cây làm bộ dữ liệu.
Các bước:
- Trước tiên, tạo danh sách thả xuống từ tên trái cây ở Ô D5.
- Chọn bộ dữ liệu (B5:B14).
- Đi Trang chủ > Định dạng có điều kiện > Quy tắc mới.
- Cửa sổ Quy tắc định dạng mới hiện ra. Chọn Loại quy tắc: Sử dụng công thức xác định ô cần định dạng. Nhập công thức dưới vào trường: Định dạng giá trị nơi công thức này đúng. Sau đó, nhấp nút Định dạng chọn màu làm nổi bật từ tab Điền.
=ISNUMBER(FIND($D$5,B5))- Nhấp OK sau khi chọn màu định dạng. Lại nhấp OK đóng hộp thoại.
Cuối cùng, ô chứa văn bản từ danh sách Văn bản được làm nổi bật trong Sách.
Ở đây, hàm FIND trả về vị trí bắt đầu của chuỗi văn bản trong chuỗi khác (Phân biệt chữ hoa thường). Sau đó, hàm ISNUMBER kiểm tra giá trị có phải số, trả về TRUE hoặc FALSE.
Đọc thêm: Cách Làm nổi bật Ô được chọn trong Excel (5 Cách dễ dàng)
7. Kết hợp SUMPRODUCT, ISNUMBER, và SEARCH làm nổi bật ô chứa Văn bản từ Danh sách
Phương pháp này dùng hàm SUMPRODUCT cùng ISNUMBER và SEARCH. Tôi làm nổi bật tên sách trong Sách (Books) (B4:B12) với từ từ Văn bản (Text) (B5:B8)
Các bước:
- Chọn bộ dữ liệu (B5:B12) trước.
- Đi Trang chủ > menu thả xuống Định dạng có điều kiện.
- Tiếp theo, chọn Quy tắc mới từ menu thả xuống Định dạng có điều kiện.
- Hộp thoại Quy tắc định dạng mới hiện ra. Chọn Loại quy tắc: Sử dụng công thức xác định ô cần định dạng. Nhập công thức dưới vào trường: Định dạng giá trị nơi công thức này đúng. Sau đó, nhấp nút Định dạng chọn màu làm nổi bật từ tab Điền.
=SUMPRODUCT(--ISNUMBER(SEARCH($D$5:$D$8,B5)))>0- Nhấp OK > OK đóng hộp thoại.
Cuối cùng, ô chứa tên sách với văn bản từ danh sách Văn bản được làm nổi bật.
🔎 Cách công thức hoạt động:
➤ SEARCH($D$5:$D$8,B5)
Phần này trả về: {#VALUE!;#VALUE!;1;5}. Hàm SEARCH trả về vị trí giá trị. Nhưng trả lỗi (#VALUE!) nếu không tìm thấy.
➤ ISNUMBER(SEARCH($D$5:$D$8,B5))
Hàm ISNUMBER chuyển tất cả kết quả lỗi thành FALSE và kết quả khác thành TRUE.
Kết quả: {FALSE;FALSE;TRUE;TRUE}
➤ –ISNUMBER(SEARCH($D$5:$D$8,B5))
Phần này trả về: {0;0;1;1}. Dấu trừ kép (—) chuyển tất cả FALSE và TRUE thành 0 và 1.
➤ SUMPRODUCT(–ISNUMBER(SEARCH($D$5:$D$8,B5)))>0
Phần này trả về: {TRUE;FALSE;TRUE;FALSE;FALSE;TRUE;FALSE;TRUE}. Cuối cùng, hàm SUMPRODUCT kiểm tra kết quả trước so với không (0) và dẫn dắt quy tắc Định dạng có điều kiện làm nổi bật ô.
Nội dung liên quan: Công thức Excel dựa trên Màu ô (5 Ví dụ)
Kết luận
Trong bài viết trên, tôi đã cố gắng thảo luận nhiều phương pháp làm nổi bật ô chứa văn bản từ danh sách chi tiết. Hy vọng các phương pháp và giải thích đủ giải quyết vấn đề của bạn. Hãy cho tôi biết nếu có thắc mắc.
Bài viết liên quan
- Màu ô Excel: Thêm, Sửa, Dùng & Xóa
- Cách Điền màu ô Excel dùng Công thức (5 Cách dễ dàng)
- Cách Làm nổi bật Hàng trong Excel (5 Phương pháp nhanh)
- Excel VBA làm nổi bật Ô dựa trên Giá trị (5 Ví dụ)
- VBA đổi Màu ô dựa trên Giá trị trong Excel (3 Ví dụ dễ dàng)