Hãy tạo một công thức gọi là “quy tắc định dạng” để sử dụng trong Định dạng có điều kiện để đánh dấu chuỗi văn bản phụ thuộc sai lỗi trong Google Sheets.
Để giải thích vấn đề này, tôi sẽ xem xét một bảng chứa một danh sách các quốc gia và thủ đô của họ.
Có hai cột là “Quốc gia” và “Thủ đô”, như bên dưới.
- Quốc gia – Thủ đô
- Australia – Canberra
Theo danh sách hai cột này, các giá trị của cột B là các phụ thuộc của các giá trị của cột A.
Ví dụ, “Canberra” là phụ thuộc của “Australia”.
Trong một danh sách hai cột khác, nếu ai đó nhập “Sydney” cho “Australia”, tôi muốn đánh dấu “Sydney” vì nó là phụ thuộc sai lỗi.
Tôi muốn truyền điều này với chủ đề đánh dấu văn bản phụ thuộc sai lỗi trong Google Sheets.
Bạn có thể sử dụng ví dụ trên trong đời thực của bạn bằng cách thay thế bảng quốc gia và thành phố bằng các giá trị liên quan.
Công thức của tôi sẽ giúp bạn xác định các chuỗi văn bản không căn chỉnh bằng cách làm nổi bật chúng.
Quy tắc định dạng để đánh dấu văn bản phụ thuộc sai lỗi trong Google Sheets
Theo kiểm tra của tôi, chúng ta có thể sử dụng VLOOKUP hoặc MATCH để viết quy tắc định dạng có điều kiện.
Để làm nổi bật các chuỗi văn bản phụ thuộc sai lỗi, trước hết, hãy tạo một bảng như trên chứa sự căn chỉnh chính xác.
Tôi đã giữ bảng trên trong Sheet2!A1:B11.
Tôi muốn làm nổi bật các phụ thuộc sai lỗi trong cột B trong Sheet1 trong cùng một tệp.
Hãy viết công thức từ đầu.
Quy tắc định dạng #1 (Vlookup)
Vì chúng ta đang làm việc với định dạng có điều kiện, chúng ta cần viết công thức cho phạm vi ô A2:B2 trong Sheet1. Dựa trên “Áp dụng vào phạm vi” A2:B1000, điều này sẽ áp dụng cho tất cả 1000 hàng.
Trước tiên, tìm kiếm giá trị A2&B2 (Sheet1) trong phạm vi Sheet2!A2:A11&Sheet2!B2:B11. Vlookup sau đây sẽ thực hiện phần đó.
=ArrayFormula(vlookup(A2&B2,Sheet2!A2:A11&Sheet2!B2:B11,1,0))
Việc sử dụng ArrayFormula là cần thiết vì chúng ta đang kết hợp hai mảng trong Sheet2 bằng cách sử dụng dấu &.
Hiện tại, định dạng có điều kiện trong Google Sheets không hỗ trợ tham chiếu chéo như trên. Chúng ta nên sử dụng INDIRECT như bên dưới.
=ArrayFormula(vlookup(A2&B2,indirect("Sheet2!A2:A11")&indirect("Sheet2!B2:B11"),1,0))
Trong bất kỳ ô trống nào trong Sheet1, công thức này sẽ trả về #N/A nếu không có sự khớp, ngược lại trả về giá trị kết hợp.
Để làm nổi bật văn bản phụ thuộc sai lỗi, chúng ta cần các giá trị #N/A.
Chúng ta có thể sử dụng hàm IFNA để chuyển đổi các giá trị #N/A thành TRUE (một giá trị Boolean bằng # 1). Như sau.
=ifna(ArrayFormula(vlookup(A2&B2,indirect("Sheet2!A2:A11")&indirect("Sheet2!B2:B11"),1,0)),TRUE)
Điều này không đủ để làm nổi bật!
Bởi vì chúng ta muốn sử dụng quy tắc định dạng trong toàn bộ phạm vi cột trong Sheet1, không chỉ ở B2.
Có thể có các hàng trống trong B2:B trong Sheet1, cho đó các VLOOKUP cũng sẽ trả về các giá trị lỗi #N/A.
Để khắc phục vấn đề đó, chúng ta có thể sử dụng toán tử AND như bên dưới.
=and(len(B2),ifna(ArrayFormula(vlookup(A2&B2,indirect("Sheet2!A2:A11")&indirect("Sheet2!B2:B11"),1,0)),TRUE)=TRUE)
Chúng ta có thể sử dụng quy tắc định dạng này để làm nổi bật văn bản phụ thuộc sai lỗi trong Google Sheets.
Dưới đây là một công thức khác cho mục đích giáo dục.
Quy tắc định dạng #2 (Match)
Giống như quy tắc định dạng #1, chúng ta cũng có thể viết một quy tắc khác sử dụng MATCH để làm nổi bật các chuỗi văn bản không căn chỉnh trong Google Sheets.
Ở đây, tôi đưa ra công thức ngay lập tức.
=AND(LEN(B2),ifna(ArrayFormula(match(A2&B2,indirect("Sheet2!$A$2:$A$11")&indirect("Sheet2!$B$2:$B$11"),0)),TRUE)=TRUE)
Ở đây, công thức hoạt động tương tự như Vlookup. Hãy xem thế nào?
Phần công thức dưới đây cũng sẽ trả về #N/A cho sự không khớp. Thay vì giá trị kết hợp, công thức này sẽ trả về số vị trí tương ứng cho sự khớp.
=ArrayFormula(match(A2&B2,indirect("Sheet2!$A$2:$A$11")&indirect("Sheet2!$B$2:$B$11"),0))
Các phần còn lại tương tự như Vlookup. Tôi nghĩ, sử dụng IFNA để chuyển đổi #N/A thành TRUE và sử dụng AND để bỏ qua các ô trống.
Chúng ta đã có hai quy tắc định dạng bây giờ. Tôi đang sử dụng quy tắc định dạng đầu tiên, hãy xem nó làm nổi bật chuỗi văn bản phụ thuộc sai lỗi như thế nào.
Làm thế nào để triển khai quy tắc định dạng?
Hãy tuân thủ các bước sau đây.
- Sao chép quy tắc định dạng #1.
- Trong Sheet1, chọn B2:B1000 hoặc số hàng mà bạn muốn trong phạm vi B2:B.
- Nhấp vào Định dạng > Định dạng có điều kiện.
- Trên trình chỉnh sửa định dạng (bảng quy tắc định dạng có điều kiện), hãy kiểm tra trường “Áp dụng vào phạm vi”. Nó sẽ là B2:B1000 hoặc phạm vi đã chọn. Nếu không, sửa lại.
- Trong quy tắc định dạng, chọn “Công thức tùy chỉnh là” và dán công thức đã sao chép vào trường trống.
- Chọn màu để làm nổi bật (màu vàng nhạt theo ví dụ của tôi) và nhấp vào “Xong”.
Như vậy, chúng ta có thể làm nổi bật các chuỗi văn bản phụ thuộc sai lỗi trong Google Sheets.
Đó là tất cả!
Cảm ơn bạn đã đọc. Chúc bạn thú vị!
