Trong hướng dẫn này, tôi sẽ thảo luận về cách làm nổi bật các ô có chứa văn bản từ một danh sách trong excel. Tôi sẽ sử dụng Định dạng có điều kiện để đánh dấu các ô. Hơn nữa, tôi sẽ sử dụng một số hàm và công thức excel làm Định dạng có Điều kiện để đánh dấu các ô.
Bạn có thể tải xuống sách bài tập thực hành mà chúng tôi đã sử dụng để chuẩn bị bài viết này.
7 cách để đánh dấu các ô chứa văn bản từ danh sách
1. Sử dụng hàm COUNTIF để đánh dấu các ô có văn bản từ danh sách
Bạn có thể sử dụng hàm COUNTIF để đánh dấu các ô có văn bản từ danh sách. Ví dụ:tôi có danh sách Trái cây 1 ( B4:B14 ) có chứa một số tên trái cây. Trong một danh sách khác, Trái cây 2 ( D4:D9 ), Tôi có một số 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 Fruit 2 trong danh sách Trái cây 1 .
Các bước:
- Đầu tiên, chọn tập dữ liệu ( B5:B14 ).
- Đi tới Trang chủ > Định dạng có điều kiện ( Kiểu nhóm).
- Tiếp theo, chuyển đến Quy tắc mới từ Định dạng có điều kiện thả xuống.
- Do đó, Quy tắc định dạng mới cửa sổ sẽ hiển thị. Sau đó, 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 bên dưới vào trường: Định dạng giá trị trong đó công thức này đúng . Sau đó, nhấp vào Định dạng .
=COUNTIF($D$5:$D$9,B5)
- Chọn màu tô sáng từ Tô màu Nhấp vào OK .
- Nhấp lại vào OK để đóng hộp thoại.
- Cuối cùng, bạn sẽ thấy tất cả các ô chứa văn bản từ danh sách Trái cây 2 được đánh dấu.
Đọc thêm:Cách đánh dấu một ô trong Excel (5 phương pháp)
2. Áp dụng hàm MATCH trong Excel để đánh dấu các ô có văn bản từ danh sách
Bây giờ chúng ta sẽ sử dụng hàm MATCH để đánh dấu các ô có văn bản từ danh sách.
Các bước:
- Đầu tiên, chọn tập dữ liệu ( B5:C14 ).
- Đi tới Trang chủ > Định dạng có điều kiện > Quy tắc mới .
- Quy tắc định dạng mới cửa sổ sẽ xuất hiện. Sau đó, 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 bên dưới vào trường: Định dạng giá trị trong đó công thức này đúng . Sau đó, nhấp vào Định dạng và chọn màu tô sáng từ nút Tô tab.
=MATCH($B5,$E$5:$E$9,0) - Nhấp vào OK > Được để đóng các hộp thoại.
- Do đó, tất cả các ô chứa văn bản từ danh sách Trái cây 2 được đánh dấu trong Trái cây 1 .
Đọc thêm: Cách đánh dấu văn bản đã chọn trong Excel (8 cách)
3. Sự kết hợp của các hàm SUM &COUNTIF để làm nổi bật các ô
Lần này tôi sẽ sử dụng kết hợp các hàm để đánh dấu các ô chứa văn bản từ một danh sách khác. Ví dụ:tôi sẽ kết hợp hàm SUM cùng với COUNTIF có chức năng làm nổi bật các ô. Ở đây, tôi có tập dữ liệu Sách ( B4:B12 ) chứa một số tên sách. Trong một danh sách khác Văn bản ( D5:D8 ), Tôi có một danh sách các từ đơn. Vì vậy, bây giờ tôi sẽ đánh dấu các từ của Văn bản trong tập dữ liệu Sách .
Các bước:
- Đầu tiên, chọn tập dữ liệu ( B5:B12 ).
- Sau đó, chuyển đến Trang chủ > chọn Định dạng có điều kiện thả xuống.
- Tiếp theo, chọn Quy tắc mới từ Định dạng có điều kiện thả xuống.
- Do đó, Quy tắc định dạng mới cửa sổ sẽ hiển thị. 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 bên dưới vào trường: Định dạng giá trị trong đó công thức này đúng . Sau đó, nhấp vào Định dạng và chọn màu đánh dấu từ nút Tô tab.
=SUM(COUNTIF(B5,"*"&$D$5:$D$8&"*")) - Nhấp vào OK > Được để đóng các hộp thoại.
- Cuối cùng, các ô chứa văn bản từ danh sách Văn bản được đánh dấu trong Sách .
Đây, COUNTIF hàm trả về số ô trong phạm vi đáp ứng điều kiện đã cho. Và SUM hàm thêm số trong một phạm vi.
Đọc thêm: Cách đánh dấu ô trong Excel dựa trên giá trị (9 phương pháp)
4. Sử dụng các hàm COUNT &SEARCH để đánh dấu các ô chứa văn bản từ danh sách
Tương tự như vậy trong Phương pháp 3 , Tôi sẽ sử dụng kết hợp các hàm để làm nổi bật các ô. Ví dụ:tôi sẽ sử dụng kết hợp COUNT và TÌM KIẾM chức năng đánh dấu các ô có văn bản từ danh sách khác. Lần này, tôi sẽ lại sử dụng hai danh sách tên trái cây làm tập dữ liệu của mình.
Các bước:
- Lúc đầu, hãy chọn tập dữ liệu ( B5:B14 ).
- Đi tới Trang chủ > Định dạng có điều kiện > Quy tắc mới .
- Quy tắc định dạng mới hộp thoại sẽ bật lên. Sau đó, 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 bên dưới vào trường: Định dạng giá trị trong đó công thức này đúng .
=COUNT(SEARCH(D$5:D$9,B5)) - Sau đó, nhấp vào Định dạng và chọn màu tô sáng từ nút Tô chuyển hướng. Nhấp vào OK > Được để đóng các hộp thoại.
- Do đó, các ô chứa tên trái cây từ danh sách Trái cây 2 được đánh dấu trong Trái cây 1 .
Đây, TÌM KIẾM hàm đếm số ký tự mà tại đó một ký tự hoặc chuỗi văn bản cụ thể được tìm thấy lần đầu tiên, đọc từ trái sang phải. Mặt khác, COUNT hàm đếm số ô trong một phạm vi có chứa số.
Đọc thêm: Cách đánh dấu ô dựa trên văn bản trong Excel [2 phương pháp]
Bài đọc tương tự:
- Cách đánh dấu một cột trong Excel (3 phương pháp)
- Tô màu ô dựa trên tỷ lệ phần trăm trong Excel (6 phương pháp)
- Cách Đánh dấu Mỗi 5 Hàng trong Excel (4 Phương pháp)
- Đánh dấu ô bằng cách sử dụng câu lệnh If trong Excel (7 cách)
- Cách So sánh Hai Trang tính Excel và Đánh dấu Sự 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 để đánh dấu ô từ danh sách
Lần này, tôi sẽ sử dụng một công thức đơn giản và danh sách thả xuống để đánh dấu các ô. Với mục đích này, tôi sẽ tạo một danh sách thả xuống. Sau đó, trong Định dạng có điều kiện quy tắc, tôi sẽ đặt công thức. Lần này, tôi sẽ lại sử dụng hai danh sách tên trái cây làm tập dữ liệu của mình.
Các bước:
- Đầu tiên, tạo danh sách thả từ một số tên trái cây trong Ô D5 .
- Chọn tập dữ liệu ( B5:B14 ).
- Đi tới Trang chủ > Định dạng có điều kiện .
- Tiếp theo, chuyển đến Định dạng có điều kiện trình đơn thả xuống> Quy tắc mới .
- Do đó, Quy tắc định dạng mới hộp thoại sẽ xuất hiện. Sau đó, 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 bên dưới vào trường: Định dạng giá trị trong đó công thức này đúng . Nhấp vào Định dạng và chọn màu đánh dấu từ nút Tô tab.
=$B5=$D$5 - Nhấp vào OK > Được để đóng các hộp thoại.
- Do đó, các ô chứa trái cây từ danh sách thả xuống được đánh dấu.
- Phương pháp này hoạt động động, có nghĩa là nếu bạn thay đổi tên trái cây từ danh sách thả xuống, các ô được đánh dấu sẽ thay đổi tương ứng.
Đọc thêm: Cách đánh dấu từ trên xuống dưới trong Excel (5 phương pháp)
6. Các hàm ISNUMBER và FIND của Excel để đánh dấu các ô từ một danh sách
Bây giờ, tôi sẽ sử dụng kết hợp các hàm và danh sách thả xuống để đánh dấu các ô. Ví dụ:tôi sẽ sử dụng kết hợp của ISNUMBER và TÌM HIỂU chức năng đánh dấu các ô có văn bản từ danh sách khác. Lần này, tôi sẽ lại sử dụng hai danh sách tên trái cây làm tập dữ liệu của mình.
Các bước:
- Đầu tiên, tạo danh sách thả xuống từ một số tên trái cây trong Ô D5 .
- Chọn tập dữ liệu ( B5:B14 ).
- Đi tới Trang chủ > Định dạng có điều kiện > Quy tắc mới .
- Quy tắc định dạng mới cửa sổ sẽ hiển thị. 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 bên dưới vào trường: Định dạng giá trị trong đó công thức này đúng . Sau đó, nhấp vào Định dạng và chọn màu tô sáng từ nút Tô tab.
=ISNUMBER(FIND($D$5,B5)) - Nhấp vào OK sau khi bạn chọn màu định dạng. Một lần nữa nhấp vào OK để đóng các hộp thoại.
- Cuối cùng, các ô chứa văn bản từ danh sách Văn bản được đánh dấu trong Sách .
Đây, TÌM HIỂU hàm trả về vị trí bắt đầu của một chuỗi văn bản trong một chuỗi văn bản khác (Phân biệt chữ hoa chữ thường). Sau đó, ISNUMBER hàm kiểm tra xem giá trị có phải là số hay không và trả về TRUE hoặc FALSE .
Đọc thêm: Cách đánh dấu các ô đã chọn trong Excel (5 cách dễ dàng)
7. Kết hợp các hàm SUMPRODUCT, ISNUMBER và SEARCH để đánh dấu các ô chứa văn bản từ danh sách
Trong phương pháp này, tôi sẽ sử dụng hàm SUMPRODUCT cùng với hàm ISNUMBER và TÌM KIẾM hàm số. Tôi sẽ đánh dấu tên sách trong Sách ( B4:B12 ) với các từ trong Văn bản ( B5:B8 )
Các bước:
- Chọn tập dữ liệu ( B5:B12 ) lúc đầu.
- Đi tới Trang chủ > Định dạng có điều kiện thả xuống.
- Tiếp theo, chọn Quy tắc mới từ Định dạng có điều kiện thả xuống.
- Do đó, Quy tắc định dạng mới hộp thoại sẽ xuất hiện. 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 bên dưới vào trường: Định dạng giá trị trong đó công thức này đúng . Sau đó, nhấp vào Định dạng và chọn màu tô sáng từ nút Tô tab.
=SUMPRODUCT(--ISNUMBER(SEARCH($D$5:$D$8,B5)))>0 - Nhấp vào OK > Được để đóng các hộp thoại.
- Cuối cùng, các ô chứa tên sách có văn bản từ danh sách Văn bản được đánh dấu.
🔎 Công thức hoạt động như thế nào?
➤ TÌM KIẾM ($ D $ 5:$ D $ 8, B5)
Phần này của công thức trả về:
{ #VALUE!; # VALUE!; 1; 5 }
Đây, TÌM KIẾM hàm trả về vị trí của giá trị. Nhưng nó trả về một lỗi ( #VALUE! ) nếu giá trị không được tìm thấy.
➤ ISNUMBER (TÌM KIẾM ($ D $ 5:$ D $ 8, B5))
ISNUMBER hàm chuyển đổi tất cả các kết quả lỗi thành FALSE và các kết quả khác thành TRUE .
Phần này trả lời:
{ FALSE; FALSE; TRUE; TRUE }
➤ –ISNUMBER (TÌM KIẾM ($ D $ 5:$ D $ 8, B5))
Phần này trả về:
{ 0; 0; 1; 1}
Dấu trừ kép ( - Dấu) chuyển đổi tất cả FALSE và TRUE giá trị thành 0 và 1 .
➤ SUMPRODUCT (–ISNUMBER (TÌM KIẾM ($ D $ 5:$ D $ 8, B5)))> 0
Phần này trả lời:
{ TRUE; FALSE; TRUE; FALSE; FALSE; TRUE; FALSE; TRUE }
Cuối cùng, SUMPRODUCT hàm kiểm tra kết quả trước đó so với 0 ( 0 ) và do đó hướng dẫn Định dạng có điều kiện quy tắc để đánh dấu các ô.
Nội dung có 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 một số phương pháp để đánh dấu các ô có chứa văn bản từ một danh sách một cách công phu. Hy vọng rằng, những phương pháp và giải thích này sẽ đủ để giải quyết vấn đề của bạn. Vui lòng cho tôi biết nếu bạn có bất kỳ thắc mắc nào.
Các bài viết có liên quan
- Màu ô Excel:Thêm, Chỉnh sửa, Sử dụng &Loại bỏ
- Cách tô màu trong ô Excel bằng công thức (5 cách dễ dàng)
- Cách đánh dấu một hàng trong Excel (5 phương pháp nhanh)
- Excel VBA để Đánh dấu ô Dựa trên Giá trị (5 Ví dụ)
- VBA để thay đổi màu ô dựa trên giá trị trong Excel (3 ví dụ dễ hiểu)