Đánh dấu toàn bộ hàng trong điều kiện định dạng trong Google Sheets

Trong Google Sheets, định dạng có điều kiện cho phép bạn làm nổi bật toàn bộ hàng dựa trên các tiêu chí cụ thể.

Áp dụng quy tắc trở nên dễ dàng khi bạn biết cột để tìm kiếm. Ví dụ, bạn có thể có các mục ngày trong cột A và muốn làm nổi bật các hàng mà cột A chứa ngày hôm nay.

Tuy nhiên, việc tìm kiếm trong một phạm vi sẽ trở nên phức tạp hơn. Hãy tưởng tượng bạn có một giá trị cụ thể trong các cột A, B, C, D hoặc bất kỳ cột nào khác trong phạm vi, và bạn muốn làm nổi bật các hàng chứa giá trị đó.

Trong bài hướng dẫn này, chúng ta sẽ khám phá cả hai tình huống, và tôi cũng sẽ chia sẻ một mẹo thú vị liên quan đến VLOOKUP. VLOOKUP cho phép bạn tìm kiếm một tiêu chí trong một cột, trả về một giá trị từ cột khác, sau đó làm nổi bật toàn bộ hàng bằng cách đánh giá giá trị trả về với các toán tử so sánh.

Đánh dấu toàn bộ hàng bằng cách tìm kiếm một khóa trong một cột cụ thể trong Google Sheets

Trong ví dụ sau, mục tiêu là làm nổi bật các hàng trong phạm vi A5:E100 mà A5:A100 khớp với ngày hôm nay. Làm thế nào để đạt được điều này?

1. Di chuyển đến ô A1 trong bảng mà bạn muốn áp dụng đánh dấu.
2. Nhấp vào Format > Điều kiện định dạng.
3. Trong trường bên dưới ‘Áp dụng cho phạm vi’, nhập A5:E100.
4. Chọn ‘Công thức tùy chỉnh là’ trong danh sách thả xuống bên dưới ‘Quy tắc định dạng’.
5. Nhập quy tắc công thức tùy chỉnh =INT($A5)=TODAY() trong trường bên dưới ‘Công thức tùy chỉnh là’.
6. Chọn màu văn bản và màu nền ô theo sở thích của bạn trong ‘Kiểu định dạng’.
7. Nhấp vào ‘Hoàn tất’.

Công thức này sẽ làm nổi bật bất kỳ hàng nào chứa ngày hôm nay hoặc một DateTime chứa ngày hôm nay. Nếu có nhiều ô trong cột A chứa ngày hôm nay hoặc dấu thời gian, tất cả các hàng đó sẽ được làm nổi bật.

Để chỉ định một ngày, văn bản hoặc số cụ thể là tiêu chí để làm nổi bật toàn bộ hàng, hãy làm theo các ví dụ công thức tùy chỉnh dưới đây:

• Ngày cụ thể: =INT($A1)=DATE(nam, tháng, ngày)
• Văn bản: =$A1=”Apple”
• Số: =$A1=100
• Số với so sánh: =$A1>=100
• Dấu thời gian: =$A1=DATE(nam, tháng, ngày)+TIME(giờ, phút, giây)

Trong tất cả các ví dụ công thức này, thay thế $A1 bằng địa chỉ ô đầu tiên trong cột tìm kiếm. Ví dụ, nếu bạn muốn áp dụng quy tắc này cho phạm vi C2:G10, và cột tìm kiếm là cột G, bạn nên sử dụng $G2.

Mẹo: Bao gồm Nhiều Khóa Tìm kiếm cho Định dạng có Điều kiện

Để bao gồm nhiều khóa tìm kiếm, ví dụ: làm nổi bật các hàng trong phạm vi A5:E100 mà các giá trị trong B5:B100 khớp với “Apple” hoặc “Orange”, hãy sử dụng toán tử OR như sau:

=OR($B5=”Apple”, $B5=”Orange”)

Tìm kiếm Tiêu chí Qua Các Cột và Đánh dấu toàn bộ hàng trong Google Sheets

Trong các ví dụ trên, chúng ta đã thảo luận về việc biết cột để tìm kiếm các khóa cụ thể. Nhưng nếu bạn cần tìm kiếm qua các cột thì sao?

Ví dụ, hãy xem xét một phạm vi từ A1 đến Z1000. Nếu bạn muốn làm nổi bật các hàng mà bất kỳ ô nào trong phạm vi này chứa chuỗi “Apple”, bạn có thể sử dụng công thức quy tắc sau cho ‘Áp dụng cho phạm vi’ A1:Z1000:

=XMATCH(“Apple”,$A1:Z1)

Hàm XMATCH tìm kiếm “Apple” trên hàng #1 và trả về số cột nếu tìm thấy hoặc #N/A nếu không tìm thấy. Nếu kết quả không phải là #N/A, các hàng sẽ được làm nổi bật. Điều này tự động áp dụng cho mỗi hàng trong ‘Áp dụng cho phạm vi’ trong định dạng có điều kiện.

Đánh dấu toàn bộ hàng bằng VLOOKUP trong Google Sheets

Trong ví dụ sau, bảng của tôi nằm trong phạm vi A3:E11, nơi tôi muốn làm nổi bật toàn bộ hàng chứa lần xuất hiện đầu tiên của trái cây “Blueberry” trong A3:A11.

Trong trường hợp đó, bạn có thể sử dụng công thức VLOOKUP sau cho ‘Áp dụng cho phạm vi’ A3:E11:

=ROW()=ROW(INDIRECT(CELL(“ADDRESS”, VLOOKUP(“Blueberry”, $A$3:$E11, 2, 0))))

Phân tích công thức:

• VLOOKUP(“Blueberry”, $A$3:$E11, 2, 0): Công thức VLOOKUP này tìm kiếm khóa “Blueberry” trong phạm vi ô $A$3:$E11 và trả về giá trị (kết quả) từ cột thứ hai trong phạm vi. Cột kết quả không quan trọng; phạm vi chứa 5 cột, vì vậy bạn có thể chỉ định bất kỳ cột nào từ 2 đến 5.
• Hàm CELL trả về ID ô của giá trị kết quả.
• Bao bọc ID ô bằng ROW và INDIRECT trả về số hàng của kết quả VLOOKUP.
• Quy tắc kiểm tra số hàng hiện tại với số hàng trên và làm nổi bật hàng khớp.

Lưu ý: Nếu bạn muốn làm nổi bật hàng của kết quả VLOOKUP chỉ khi kết quả là một giá trị cụ thể, bạn có thể sử dụng công thức sau:

=AND(VLOOKUP(“Blueberry”, $A$3:$E11, 2, 0) > 3, ROW() = ROW(INDIRECT(CELL(“ADDRESS”, VLOOKUP(“Blueberry”, $A$3:$E11, 2, 0)))))

Quy tắc định dạng có điều kiện này chỉ được áp dụng nếu kết quả lớn hơn 3.

Làm thế nào để làm nổi bật một hàng khớp từ dưới cùng của một bảng

Đối với dữ liệu mẫu tương tự, bạn có thể sử dụng công thức XLOOKUP sau để làm nổi bật một hàng khớp từ dưới cùng của bảng:

=ROW()=ROW(INDIRECT(CELL(“ADDRESS”, XLOOKUP(“Blueberry”, $A$3:$A11, $A$3:$A11, , 0, -1))))

Ở đây, XLOOKUP đã được thay thế bằng XLOOKUP vì nó có thể tìm kiếm từ giá trị cuối cùng đến giá trị đầu tiên.

Hàm XLOOKUP tìm kiếm “Blueberry” trong $A$3:$A11 và trả về giá trị từ cùng phạm vi.

Tất cả các thành phần công thức khác vẫn giữ nguyên, và cùng với đó là logic.

Kết luận

Sau khi xem xét các công thức, một quan sát quan trọng trở nên rõ ràng. Việc sử dụng dấu $ với các tham chiếu ô là quan trọng để xác định xem chúng có phải là tương đối hay tuyệt đối.

Nguyên tắc quan trọng trong việc làm nổi bật toàn bộ hàng trong Google Sheets là sử dụng cột tuyệt đối và hàng tương đối. Khi làm việc với một ô duy nhất, cột trở thành tuyệt đối, trong khi hàng vẫn là tương đối.

Ví dụ: $B5

Khi chỉ định một phạm vi ô, tham chiếu ô xuất phát trở thành tuyệt đối trong khi tham chiếu ô kết thúc trở thành cột tuyệt đối và hàng tương đối.

Ví dụ: $A$3:$E11

Tương tự: How to Highlight Vlookup Result Value in Google Sheets.