Tham chiếu tương đối trong định dạng có điều kiện không quá lo lắng khi sử dụng Google Sheets nếu dải ô cần được làm nổi bật và các điều kiện nằm trong cùng một sheet. Điều này chỉ trở thành vấn đề khi hai sheet khác nhau nằm trong cùng một file!
Khác với các công thức bảng tính thông thường, các công thức trong định dạng có điều kiện, gọi là quy tắc công thức, yêu cầu sử dụng hàm INDIRECT để tham chiếu đến sheet khác.
Như bạn có thể biết, hàm INDIRECT sử dụng tham chiếu ô dưới dạng chuỗi. Vì vậy, khi chúng ta sử dụng INDIRECT để tham chiếu đến một ô trong Google Sheets, theo cách ‘bình thường’, tham chiếu ô tương đối sẽ không có hiệu lực.
Cú pháp: INDIRECT(tham_chiếu_ô_dưới_dạng_chuỗi, [là_chuẩn_A1])
Nhưng chúng ta có thể vượt qua vấn đề này bằng cách sử dụng các hàm ROW và COLUMN trong hàm ADDRESS như đối số tham_chiếu_ô_dưới_dạng_chuỗi trong hàm INDIRECT.
Trong bài viết này, chúng ta sẽ tìm hiểu cách áp dụng tham chiếu tương đối trong định dạng có điều kiện khi hai sheet trong cùng một file liên quan đến Google Sheets.
Nhưng trước tiên, bạn phải biết cách áp dụng tham chiếu ô tương đối trong định dạng có điều kiện trong cùng một sheet của Google Sheets.
Tham chiếu Ô tương đối trong Định dạng có điều kiện trên cùng một Sheet
Tham chiếu ô tương đối giúp chúng ta sử dụng một công thức định dạng có điều kiện duy nhất cho một dải ô hoặc dải hàng/cột. Vì vậy, bạn không cần tạo các quy tắc công thức cho từng ô hoặc hàng/cột riêng lẻ.
Hãy bắt đầu với dải cột vì nó là định dạng có điều kiện phổ biến nhất.
Công thức cho một dải cột
Dưới đây là một ví dụ. Tôi muốn làm nổi bật các giá trị trong cột A2:A nếu B2:B là “Pending”. Cả hai giá trị đều nằm trong cùng một sheet. Làm thế nào để thực hiện điều đó?
Dưới đây là công thức cần thiết mà bạn phải nhập vào menu Format > Định dạng có điều kiện > Quy tắc định dạng. Hãy chọn “Công thức tùy chỉnh là” và nhập công thức sau đây.
Công thức tùy chỉnh #1:
=B2="Pending"
Điều duy nhất mà bạn cần chú ý là trường “Áp dụng vào dải” bên trong bảng “Quy tắc định dạng có điều kiện”.
Bạn phải nhập A2:A để công thức trên cho ô đầu tiên trong dải được áp dụng tự động cho dải đã chọn. Bởi vì chúng ta đã sử dụng tham chiếu ô tương đối trong công thức định dạng có điều kiện trên (không có ký tự đô la với tham chiếu ô B2).
Giả sử rằng tôi muốn làm nổi bật cả cột A và B. Thay đổi “Áp dụng vào dải” thành A2:B sẽ không đạt được kết quả mong muốn.
Trong trường hợp như vậy, hãy làm cột (ký tự) cố định và hàng (số) tương đối trong công thức trên.
Công thức tùy chỉnh #2:
=$B2="Pending"
Còn nếu các giá trị nằm trong hai hàng thay vì hai cột thì sao?
Công thức cho một dải hàng
Ở đây, không có thay đổi nào khi sử dụng tham chiếu tương đối trong định dạng có điều kiện trên Google Sheets.
Nếu số hóa đơn nằm ở hàng số 1 và trạng thái nằm ở hàng số 2, thì quy tắc công thức số 1 vẫn áp dụng để làm nổi bật hàng số 1.
Chỉ cần thay đổi “Áp dụng vào dải” thành B1:K1 để có kết quả như hình ảnh dưới đây.
Để làm nổi bật cả hai hàng, việc sử dụng công thức tùy chỉnh số 2 sẽ không hoạt động vì chúng ta đã làm cột cố định.
Ở đây, thay vì cột (ký tự), chúng ta phải làm hàng (số) cố định. Hãy thay đổi dải để làm nổi bật thành A1:K2 thay vì A1:K1.
Công thức tùy chỉnh #3:
=B$2="Pending"
Tham chiếu Ô tương đối trong Định dạng có điều kiện trong Hai Tab Sheet
Các công thức tùy chỉnh trên sẽ không hoạt động nếu số hóa đơn nằm ở một sheet và trạng thái nằm ở sheet khác. Bất kể là theo cột hay hàng.
Ví dụ, số hóa đơn nằm trong A1:A của “Sheet1”. Nhưng cột trạng thái B nằm trong B1:B của “Sheet2”.
Điều đó có nghĩa là dải ô cần được làm nổi bật nằm trong một sheet và các điều kiện (tiêu chí) nằm trong sheet khác. Trong tình huống này, một số bạn có thể nghĩ rằng quy tắc dưới đây sẽ hoạt động.
=Sheet2!B2="Pending"
Nó sẽ không hoạt động vì “Quy tắc định dạng có điều kiện” trong Google Sheets chỉ áp dụng cho cùng một sheet. Vì vậy, cách làm gián đoạn là tham chiếu INDIRECT.
Điều đó có nghĩa là khi có hai hoặc nhiều hơn hai sheet trong cùng một file liên quan đến định dạng có điều kiện, chúng ta phải sử dụng hàm INDIRECT.
Hãy đọc thêm: Vai trò của hàm Indirect trong Định dạng có điều kiện trong Google Sheets.
Vì chủ đề của chúng ta là tham chiếu tương đối trong định dạng có điều kiện, hãy xem cách sử dụng Indirect trong tình huống như vậy.
Công thức INDIRECT cho một dải cột
Trong hình ảnh dưới đây, bạn có thể thấy các giá trị (số hóa đơn) mà chúng ta cần làm nổi bật trong “Sheet1” và các tiêu chí trong “Sheet2”.
Làm thế nào để tham chiếu đến “Sheet2” và làm nổi bật trong “Sheet1”?
Dưới đây là công thức để sử dụng.
Công thức tùy chỉnh #4:
`=indirect(“‘Sheet2’!”&address(row(B2
