Cách xử lý ô tham chiếu tuyệt đối, tương đối trong định dạng có điều kiện của Excel

Trước khi vào những nội dung chính của bài viết này, chúng ta hãy cùng tìm hiểu rõ các quy tắc hoạt động của ô tham chiếu tương đối, tuyệt đối dưới định dạng có điều kiện. Nếu như chúng ta nắm rõ các quy tắc này thì sẽ dễ dàng hơn cho chúng ta tạo nên một công thức riêng cho bảng tính.

Đầu tiên, chúng ta sẽ đi qua các loại tham chiếu ô thường thấy trong các công thức Excel:

- Ô tham chiếu dạng tương đối: Nhận biết ô tham chiếu này thì khá đơn giản vì nó không chứa ký tự "$" và sẽ thay đổi dựa trên vị trí tương đối của hàng và cột khi đang được sao chép qua nhiều ô khác nhau

- Ô tham chiếu dạng tuyệt đối: Ngược lại với tham chiếu tương đối, ô tham chiếu tuyệt đối sẽ dựa có vị trí cố định kể cả khi chúng được sao chép đi nhiều ô khác nhau

- Ô tham chiếu dạng hỗn hợp: Với kiểu dạng hỗ hợp thế này thì chúng ta hiểu rằng nó sẽ chứa hàng tương đối và cột tuyệt đối (chẳng hạn như ($ A1) hoặc một số trường hợp là hàng tuyệt đối và cột tương đối (ví dụ: A 1 $). Có thể nói rằng ô tham chiếu dạng hỗn hợp là quy tắc định dạng có điều kiện có số lượng sử dụng nhiều nhất của Excel bởi nó cho phép người dùng để chữ cái cột và hàng phù hợp khi áp dụng lên tất cả các loại ô trong vùng dữ liệu.

Khi sử dụng tham chiếu, ô bắt đầu tham chiếu từ vị trí ô phía trên cùng nằm bên phía tay trái của vùng mà ta đang áp dụng tham chiếu. Đây là điều chúng ta cần lưu ý khi cần tạo một quy tắc mới để Excel có thể làm theo công thức của ta để đi tới tất cả các ô khác nằm trong vùng tham chiếu ta đã quy định.

Xem thêm: Tác dụng ký hiệu $ (Dollar) trong công thức Excel - Tham chiếu ô tương đối và tuyệt đối là gì?

Dưới đây là một số trường hợp dùng công thức giống nhau nhưng lại cho ra kết quả khác nhau mà phụ thuộc vào ô tham chiếu mà ta sử dụng:

Trường hợp 1: Hàng tuyệt đối và cột tương đối

Với trường hợp mà hàng tuyệt đối và cột tương đối thì số hàng sẽ được coi là hằng số trong khi phần thay đổi là phần cột. Mục đích sử dụng của tham chiếu này khi người dùng Excel muốn kiểm tra giá trị đang thuộc một hàng nhất định hoặc với các giá trị trong hàng khác

Trường hợp dưới đây ta so sánh hàng 1 với hàng 2. Những số trên hàng 1 được đánh dấu là những số lớn hơn các giá trị ở hàng 2. Chúng ta có công thức như sau:

= A$1> A$2

Để cố định số hàng, ta phải sử dụng tham chiếu hàng tuyệt đối ta sẽ sử dụng dấu $. Tiếp đến ta so sánh các giá trị trong mỗi cột riêng lẻ, ta cần tạo nên một công thức cho cột ngoài cùng bên trái (như trong ảnh là cột A) và khi sử dụng cột tham chiếu tương đối thì sẽ không có sự xuất hiện của dấu $.

Trường hợp 2: Hàng tuyệt đối và cột tuyệt đối

Với kiểu định dạng này ta sẽ sử dụng khi mà ta cần so sánh tất cả giá trị trong phạm vi quy định với các giá trị khác. Ví dụ như ta có trường hợp đánh dấu tất cả các giá trị nào của cột A so với giá trị của ô B1.

Trong hình ảnh minh hoạ dưới đây yêu cầu chúng ta tô màu vào những ô trong cột A có giá trị lớn hơn giá trị của ô B1

Để thực hiện tác vụ này, ta dùng công thức sau: = $A1> $B$1

Chú ý:

- $B$ 1 - Vì ô B1 đang chứa giá trị mà ta muốn so sánh với tất cả các giá trị khác cũng như để chúng ta sử dung như một hằng số tham chiếu ô => Sử dụng cột tuyệt đối & dòng tuyệt đối

- $A1 - Vì ta đang muốn so sánh tất cả các ô thuộc cột A với giá trị trong ô B1 => Sử dụng cột tuyệt đối và dòng tương đối.

Xem thêm: Cách tham chiếu ngược bằng những hàm vô cùng đơn giản trên Excel

Trường hợp 3: Hàng tương đối và cột tương đối

Với định dạng tham chiếu này thì ta sẽ sử dụng trong việc kiểm tra tất cả các ô trong phạm vi đã chọn để so sánh với một giá trị đã chọn. Ví dụ, nhiệm vụ của chúng ta là thêm màu vào những ô thuộc cột A và B lớn hơn giá trị trong cột B1. Vậy, công thức để làm tác vụ này sẽ là:

= A1> $B$1

Trường hợp 4: Hàng tuyệt đối và hàng tương đối

Đây là định dạng có điều kiện điển hình và dễ sử dụng nhất trong Excel. Trong ví dụ này chúng ta sẽ tạo ra một quy tắc nhằm so sánh các giá trị trong cột A với B. Bất kỳ gía trị nào trong cột A lớn hơn giá trị trong cột B sẽ được tô màu trong bảng tính

Công thức ở đây sẽ là:

= $A1> $B1

Ta cần chú ý rằng dấu $ sẽ được đặt trước các ký tự cột trong công thức trên bởi ta đang so sánh các giá trị cột A và B. Vậy nên ta cần phải thay đổi làm sao để các cột này sử dụng tham chiếu cột tuyệt đối. Bên cạnh đó, vì ta đang so sánh các gía trị trong mỗi hàng riêng lẻ, vậy nên ta không cần dấu $ vì ta đang sử dụng tham chiếu tương đối.

Meo: Để chuyển đổi giữa tham chiếu tuyệt đối và tham chiếu tương đối, ta hãy lựa chọn ô tham chiếu rồi nhấn F4 để tham chiếu có thể xoay quanh bốn kiểu tương đối và tuyệt đối như sau: A1> $ A $ 1> A $ 1> $ A1, rồi sau cũng là quay về tham chiếu tương đối A1.