Bạn muốn tiết kiệm thời gian khi làm việc với Excel? Bài viết này trên Thủ Thuật sẽ hướng dẫn bạn cách tạo công thức hiệu quả cho bảng tính, đặc biệt là cách sử dụng tham chiếu ô trong định dạng có điều kiện.
Việc nắm vững cách thức hoạt động của ô tham chiếu tương đối và tuyệt đối trong quy tắc định dạng có điều kiện là chìa khóa để tối ưu hóa công việc trên Excel. Trong mọi công thức Excel, bao gồm cả định dạng có điều kiện, ô tham chiếu có thể là:
- Tuyệt đối: ($A$1) Luôn giữ nguyên vị trí, bất kể sao chép ở đâu.
- Tương đối: (A1) Thay đổi dựa trên vị trí tương đối của hàng và cột khi sao chép.
- Hỗn hợp: ($A1 hoặc A$1) Một phần cố định (hàng hoặc cột) và một phần thay đổi. Loại này thường được sử dụng trong định dạng có điều kiện để giữ nguyên chữ cái hàng hoặc cột khi áp dụng cho toàn bộ vùng dữ liệu.
Lưu ý, trong định dạng có điều kiện, ô được tham chiếu từ ô trên cùng bên trái của vùng áp dụng. Bạn chỉ cần viết công thức cho ô này, Excel sẽ tự động “sao chép” công thức cho các ô còn lại trong vùng đã chọn.
Dưới đây là một vài ví dụ minh họa cách sử dụng cùng một công thức nhưng cho kết quả khác nhau tùy thuộc vào kiểu tham chiếu ô:
Ví dụ 1: Cột Tuyệt Đối & Hàng Tương Đối
Đây là kiểu tham chiếu phổ biến nhất trong định dạng có điều kiện. Giả sử bạn muốn so sánh giá trị cột A với cột B và tô màu ô trong cột A nếu giá trị đó lớn hơn giá trị tương ứng cùng hàng ở cột B.
Công thức sẽ là: =$A1>$B1
Vì bạn luôn so sánh giá trị ở cột A và B, nên bạn “cố định” cột bằng cách sử dụng tham chiếu tuyệt đối (dấu $ trước chữ cái cột). Còn hàng sử dụng tham chiếu tương đối (không có dấu $) vì bạn so sánh giá trị trên từng hàng.
Absolute column and relative row cell references in conditional formatting rules
Ví dụ 2: Cột Tương Đối & Hàng Tuyệt Đối
Ngược lại với ví dụ trên, số hàng được cố định trong khi cột thay đổi. Kiểu này dùng khi bạn muốn so sánh giá trị trên một hàng cụ thể với một giá trị cố định hoặc với các giá trị trên một hàng khác.
Ví dụ: =A$1>A$2 so sánh giá trị hàng 1 với hàng 2 và tô màu ô ở hàng 1 nếu nó lớn hơn giá trị tương ứng cùng cột ở hàng 2.
Relative column and absolute row cell references in Excel conditional formatting
Dấu $ trước số hàng biểu thị tham chiếu hàng tuyệt đối. Cột sử dụng tham chiếu tương đối để so sánh giá trị trên từng cột.
Ví dụ 3: Cột Tuyệt Đối & Hàng Tuyệt Đối
Sử dụng khi bạn muốn so sánh tất cả giá trị trong vùng chọn với một ô cố định.
Ví dụ: =$A1>$B$1 tô màu tất cả ô trong cột A có giá trị lớn hơn giá trị trong ô B1.
$A1: Cột tuyệt đối, hàng tương đối để so sánh tất cả các ô trong cột A.$B$1: Cột tuyệt đối, hàng tuyệt đối để giữ ô B1 là giá trị so sánh cố định.
Absolute column and absolute row cell references in Excel conditional formatting
Ví dụ 4: Cột Tương Đối & Hàng Tương Đối
Ít được sử dụng trong định dạng có điều kiện. Áp dụng khi bạn muốn so sánh tất cả ô trong vùng chọn với một giá trị nhất định.
Ví dụ: =A1>$B$1 tô màu các ô trong cột A và B lớn hơn giá trị trong ô B1.
Relative column and relative row in Excel conditional formatting rules
Mẹo: Nhấn phím F4 khi chọn ô tham chiếu trong thanh công thức để chuyển đổi nhanh giữa các kiểu tham chiếu (A1 > $A$1 > A$1 > $A1 > A1).
Tóm lại, việc hiểu rõ cách sử dụng tham chiếu ô trong định dạng có điều kiện sẽ giúp bạn tạo ra các công thức linh hoạt và hiệu quả, từ đó tiết kiệm thời gian và nâng cao năng suất làm việc với Excel. Hãy thử áp dụng ngay những kiến thức này vào công việc của bạn!
Discussion about this post