Tham chiếu tương đối và tuyệt đối trong Excel: A1, $A$1, $A1, A$1 và phím F4

Dấu $ trong công thức Excel làm đúng một việc: khoá tham chiếu để khi bạn copy công thức đi chỗ khác, nó không bị lệch. Nghe đơn giản, nhưng đây là kỹ năng phân biệt người kéo được một công thức cho cả bảng với người phải gõ lại từng ô. Nó cũng là nguyên tắc số 6 trong 12 nguyên tắc dựng file.

Vì sao tham chiếu lại "chạy" khi copy #

Mặc định, tham chiếu trong Excel là tương đối — nó nhớ vị trí tương đối, không nhớ địa chỉ tuyệt đối. Nếu ô B2 chứa =A2 (lấy ô ngay bên trái), thì copy xuống B3 sẽ tự thành =A3, copy sang C2 thành =B2. Excel dịch chuyển tham chiếu theo đúng hướng bạn copy.

Đây là tính năng, không phải lỗi — nhờ nó bạn viết một công thức rồi kéo cho cả cột. Nhưng đôi khi bạn không muốn nó chạy, và đó là lúc cần $.

Bốn kiểu tham chiếu #

$ đặt trước chữ cái thì khoá cột, đặt trước số thì khoá hàng:

Phím F4: đổi vòng bốn kiểu #

Khi đang soạn công thức, đặt con trỏ vào một tham chiếu rồi bấm F4 để đổi vòng: A1 → $A$1 → A$1 → $A1 → A1. Không cần gõ dấu $ bằng tay.

Ví dụ kinh điển: bảng nhân bằng đúng một công thức #

Đây là bài tập kinh điển làm rõ sức mạnh của khoá một chiều. Bạn có cột đơn giá (nằm dọc ở cột F) và dòng số lượng (nằm ngang ở hàng 21), muốn điền cả bảng thành tiền:

=$F22*G$21

Chỉ một công thức này, kéo cho cả bảng:

• $F22 khoá cột F (luôn lấy đơn giá ở cột F) nhưng để hàng chạy — mỗi dòng lấy đúng đơn giá của dòng đó.

• G$21 khoá hàng 21 (luôn lấy số lượng ở hàng tiêu đề) nhưng để cột chạy — mỗi cột lấy đúng số lượng của cột đó.

Kéo công thức đi đâu trong bảng, nó cũng tự ghép đúng đơn giá của hàng với số lượng của cột. Nếu dùng $F$21 hay F21 là bảng sai ngay.

Vì sao nên tách giả định ra một ô riêng #

Kết hợp với nguyên tắc "không gõ số vào giữa công thức": để thuế VAT ở một ô riêng $H$2 rồi tham chiếu tuyệt đối tới nó:

=Đơn_giá*$H$2

Khi thuế đổi, bạn sửa đúng một ô H2 là cả bảng tự tính lại. Nếu gõ *0,08 rải khắp nơi, đổi thuế là phải săn từng công thức. Đặt tên cho ô đó (Thue_VAT) thì công thức còn tự đọc được: =Đơn_giá*Thue_VAT.

Sai lầm hay gặp #

• Quên khoá giả định: kéo công thức tính thuế xuống, tham chiếu tới ô VAT bị trôi theo thành ô trống → kết quả nhân với 0. Luôn F4 để khoá ô giả định.

• Khoá quá tay: khoá cứng $A$1 cả những tham chiếu lẽ ra phải chạy theo dòng → mọi dòng ra cùng một kết quả.

• Nhầm chiều khoá: trong bảng hai chiều, cần khoá cột thì lại khoá hàng. Nhớ: $ đứng trước cái gì thì khoá cái đó — trước chữ là cột, trước số là hàng.