Bài viết dưới đây, chúng ta sẽ cùng nhau tìm hiểu cách tạo ra công thức đại diện cho bảng tính bằng việc sử dụng hàm trị tương đối và hàm trị tuyệt đối trong Excel nhé!
Ô tham chiếu tương đối và chiếu tuyệt đối trong Excel hoạt động như thế nào ở những quy tắc định dạng có điều kiện.
Trong toàn bộ những công thức Excel, bao gồm những quy tắc định dạng có điều kiện,thì ô tham chiếu có thể là:
- Ô tham chiếu tuyệt đối (là ô có dấu $, ví dụ: $ A $ 1) có đặc điểm luôn luôn giữ nguyên không đổi, mặc cho chúng được sao chép ở đâu đi nữa.
- Ô tham chiếu tương đối (là ô không có dấu $, ví dụ A1) có thể thay đổi dựa trên vị trí tương đối của hàng, cột khi nó được sao chép qua nhiều ô.
- Ô tham chiếu hỗn hợp (là ô có cột tuyệt đối và hàng tương đối (ví dụ $ A1), hoặc là cột tương đối và hàng tuyệt đối (ví dụ A $ 1). Trong những quy tắc định dạng có điều kiện thì những ô tham chiếu hỗn hợp này sẽ được dùng nhiều nhất, mục đích để chữ cái hàng hoặc là cột vẫn phù hợp khi được áp dụng cho tất cả các ô nằm trong vùng dữ liệu.
Trong những quy tắc định dạng có điều kiện, thì ô sẽ được tham chiếu từ ô phía trên cùng nằm bên trái tại vùng áp dụng. Vì vậy, khi bạn cần một quy tắc mới, hãy viết một công thức cho ô nằm trên cùng bên trái, và Excel sẽ thực hiện “sao chép” công thức này tới tất cả những ô khác trong phạm vi đã được chọn.
Bây giờ, bạn sẽ thấy một vài ví dụ về việc sử dụng công thức giống hệt nhau nhưng lại tạo ra kết quả khác nhau, chứng tỏ sẽ tùy thuộc vào ô tham chiếu đang được sử dụng.
Ví dụ 1. Hàng tuyệt đối và hàng tương đối
Đây là loại điển hình nhất cho những quy tắc định dạng có điều kiện trong Excel.
Bạn hãy tạo ra một quy tắc đơn giản để giúp so sánh những giá trị trong các cột A với B, và tiến hành đánh dấu màu giá trị trong cột A nếu như nó lớn hơn giá trị trong cột B và chúng cùng một hàng.
= $A1> $B1
Vì bạn sẽ thường so sánh những giá trị tại các cột A và B, bạn sẽ sửa những cột này bằng việc dùng tham chiếu cột tuyệt đối, và sẽ dùng dấu $ trước kí tự muốn tuyệt đối. Vì bạn đang so sách những giá trị tại mỗi hàng riêng lẽ, nên bạn sẽ dùng tham chiếu hàng tương đối và không có dấu $.
Xem thêm: Hướng dẫn cách đổi trục biểu đồ trong Excel
Ví dụ 2. Cột tương đối và hàng tuyệt đối
Đây là loại tham chiếu có đặc điểm đối lập với kiểu 1. Ở đây, số hàng sẽ luôn luôn là một hằng số trong khi đó cột có thể thay đổi được. Bạn dùng loại tham chiếu này khi muốn tiến hành kiểm tra một giá trị nào đó trong 1 hàng so với một giá trị nhất định nào đó hoặc các giá trị khác trong một hàng khác.
Ví dụ: Hãy xem công thức dưới đây, so sánh những giá trị trong hàng 1 với hàng 2 rồi đánh dấu màu giá trị trong hàng 1 nếu như nó lớn hơn giá trị trong hàng 2 tại cùng một cột:
= A$1> A$2
Bởi vì bạn đang muốn cố định số hàng, bạn nên dùng tham chiếu dòng tuyệt đối, với dấu $ phí trước kí tự muốn cố định. Và vì bạn muốn tiến hành so sánh các giá trị trong mỗi cột riêng lẻ, nên bạn hãy tạo công thức cho cột ngoài cùng bên trái (A), rồi dùng tham chiếu cột tương đối, và không có dấu $.
Ví dụ 3. Cột tuyệt đối và hàng tuyệt đối
Bạn dùng tham chiếu hàng và cột tuyệt đối nếu như bạn đang muốn tiến hành so sánh tất cả những giá trị trong phạm vi đã chọn so với các giá trị khác.
Ví dụ: Bạn hãy tạo ra quy tắc đánh dấu màu vào tất cả giá trị trong cột A lớn hơn giá trị trong ô B1. Công thức sẽ như sau:
= $A1> $B$1
Bạn cần chú ý đến việc dùng các tham chiếu sau:
- $A1 – sử dụng cột tuyệt đối và dòng tương đối khi muốn so sánh tất cả những ô của cột A so với giá trị trong ô B1.
- $B$ 1 – sử dụng cột tuyệt đối & dòng tuyệt đối khi ô B1 chứa giá trị mà bạn muốn so sánh với tất cả những giá trị khác với, và bạn muốn tham chiếu ô này sẽ là hằng số.
Ví dụ 4. Cột tương đối và hàng tương đối
Đây là loại tham chiếu được sử dụng trong những quy tắc định dạng có điều kiện của Excel ít nhất. Bạn dùng tham chiếu các cột tương đối và hàng tương đối khi muốn tiến hành kiểm tra toàn bộ các ô trong phạm vi đã chọn với một giá trị nhất định.
Giả sử như bạn muốn đánh dấu màu vào các ô trong những cột A và B lớn hơn so với một giá trị trong ô B1. Lúc này, bạn chỉ cần thực hiện sao chép công thức từ ví dụ trước, rồi thay thế $A1 bằng A1 vì không muốn sửa hàng hoặc là cột:
= A1> $B$1
Chú ý rằng, bạn nên viết công thức cho ô phía trên bên trái của vùng dữ liệu, trong trường hợp này là ô A1. Khi bạn thực hiện tạo một quy tắc với công thức nêu trên và áp dụng cho một số phạm vi, ví dụ là A1: B10, kết quả cho ra sẽ giống như sau:
Mẹo. Để có thể nhanh chóng thực hiện chuyển đổi giữa tham chiếu tuyệt đối và tham chiếu tương đối, bạn hãy chọn ô tham chiếu tại thanh công thức, rồi nhấn phím F4. Lúc này, tham chiếu sẽ tự xoay giữa bốn kiểu tương đối và tuyệt đối, hiển thị như sau: A1> $ A $ 1> A $ 1> $ A1, rồi sau đó trở lại tham chiếu tương đối A1.
Xem thêm: Hướng dẫn cách sửa lỗi #N/A trong hàm VLOOKUP
