Tham chiếu tuyệt đối (absolute reference) giữ cố định một ô khi bạn sao chép công thức. Trong =B2*$E$1, các dấu đô la khóa E1: kéo công thức xuống thì hàng nào cũng vẫn nhân với E1, còn B2 lần lượt chuyển thành B3, B4 và cứ thế tiếp tục. Để thêm dấu đô la, bấm vào tham chiếu trong công thức rồi nhấn F4 (trên Mac là Cmd+T).
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Rep | Sales | Commission | Rate | 5% |
| 2 | Ana | $12,000 | $600 | ||
| 3 | Ben | $8,500 | $425 | ||
| 4 | Chen | $15,200 | $760 | ||
| 5 | Dina | $9,800 | $490 | ||
| 6 | Eli | $11,000 | $550 |
C2 chỉ được gõ một lần rồi điền xuống. Bấm vào C4: công thức của nó là =B4*$E$1. Ô doanh số đã chuyển xuống hàng 4, còn tỷ lệ vẫn nằm ở E1. Đổi tỷ lệ trong E1 thành 8% và mọi khoản hoa hồng đều cập nhật theo.
Tham chiếu tương đối và tuyệt đối
| Tham chiếu | Tên gọi | Sao chép xuống một hàng và sang phải một cột |
|---|---|---|
A1 | tương đối | B2 |
$A$1 | tuyệt đối | $A$1 |
A$1 | hỗn hợp: khóa hàng | B$1 |
$A1 | hỗn hợp: khóa cột | $A2 |
Một tham chiếu thông thường như B2 là tương đối: Excel lưu nó dưới dạng "ô ở vị trí này so với tôi", nên bản sao nằm thấp hơn một hàng sẽ trỏ tới ô thấp hơn một hàng. Đó chính là điều bạn muốn với dữ liệu theo từng hàng, và đây là cách hoạt động mặc định. Dấu $ đặt trước chữ cái của cột hoặc số của hàng sẽ khóa phần đó lại.
Lỗi kinh điển: kéo xuống mà không có $
Đây vẫn là trang tính hoa hồng, nhưng C2 chứa =B2*E1 và không có dấu đô la. Hàng đầu tiên đúng. Các hàng còn lại đều bằng 0.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Rep | Sales | Commission | Rate | 5% |
| 2 | Ana | $12,000 | $600 | ||
| 3 | Ben | $8,500 | $0 | ||
| 4 | Chen | $15,200 | $0 | ||
| 5 | Dina | $9,800 | $0 | ||
| 6 | Eli | $11,000 | $0 |
C3 chứa =B3*E2: tham chiếu tới tỷ lệ đã trượt xuống E2, một ô trống, và ô trống được tính là 0. Hãy sửa ngay tại đây: bấm vào C2, đổi công thức thành =B2*$E$1 rồi nhấn Enter. Cả cột thay đổi theo, vì C3:C6 là bản sao của C2. Khi ô cố định là số chia, như =B2/B7 để tính tỷ trọng trên tổng, cùng lỗi này sẽ hiện #DIV/0! thay vì 0 (phần trăm trên tổng là trường hợp hay gặp nhất).
Nhấn F4 để thêm dấu đô la
Khi đang gõ hoặc sửa công thức, đặt con trỏ vào trong một tham chiếu (hoặc ngay sau nó) rồi nhấn F4. Mỗi lần nhấn sẽ chuyển sang dạng kế tiếp:
E1 -> $E$1 -> E$1 -> $E1 -> E1
Trên nhiều laptop, F4 dùng để chỉnh màn hình hoặc âm thanh, nên bạn cần nhấn Fn+F4. Trên Mac, dùng Cmd+T hoặc Fn+F4. Bạn cũng có thể tự gõ dấu $.
Tham chiếu hỗn hợp: chỉ khóa hàng hoặc cột
Tham chiếu hỗn hợp có một dấu đô la. $A2 luôn đọc cột A nhưng để hàng dịch chuyển; B$1 luôn đọc hàng 1 nhưng để cột dịch chuyển. Khi có cả hai trong một công thức, chỉ cần điền một công thức ra cả lưới ô là có ngay bảng cửu chương:
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | x | 1 | 2 | 3 | 4 | 5 |
| 2 | 1 | 1 | 2 | 3 | 4 | 5 |
| 3 | 2 | 2 | 4 | 6 | 8 | 10 |
| 4 | 3 | 3 | 6 | 9 | 12 | 15 |
| 5 | 4 | 4 | 8 | 12 | 16 | 20 |
| 6 | 5 | 5 | 10 | 15 | 20 | 25 |
B2 chứa =$A2*B$1. Bấm vào F6: ô này chứa =$A6*F$1, tức số hàng lấy từ cột A nhân với số cột lấy từ hàng 1, nên kết quả là 25. Bỏ một dấu đô la trong B2 là bảng hỏng ngay, vì các bản sao bắt đầu nhân với ô bên cạnh thay vì ô tiêu đề.
Cũng theo mẫu này, bạn có thể tính giá một danh sách sản phẩm theo nhiều mức giảm giá: =$A2*(1-B$1) với giá nằm dọc cột A và mức giảm nằm ngang hàng 1.
Tổng lũy kế với vùng khóa một đầu
Một vùng có thể chỉ bị khóa ở một đầu. =SUM($B$2:B2) luôn bắt đầu từ B2, còn điểm cuối dịch xuống khi công thức được điền, nên mỗi hàng cộng tất cả các giá trị tính đến chính nó:
| A | B | C | |
|---|---|---|---|
| 1 | Month | Sales | Total so far |
| 2 | Jan | 420 | 420 |
| 3 | Feb | 380 | 800 |
| 4 | Mar | 510 | 1310 |
| 5 | Apr | 450 | 1760 |
| 6 | May | 470 | 2230 |
C6 chứa =SUM($B$2:B6) và hiện 2230, tổng của cả năm tháng. Cũng với vùng khóa một đầu như vậy, =COUNTIF($A$2:A2,A2) đếm xem một giá trị đã xuất hiện bao nhiêu lần tính đến hàng hiện tại, và đó là cách đánh dấu các giá trị trùng lặp sau lần xuất hiện đầu tiên.
Luyện tập: một công thức cho cả bảng
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Price | 10% | 20% | 30% |
| 2 | $40.00 | |||
| 3 | $25.00 | |||
| 4 | $60.00 | |||
| 5 | $18.00 |
Đến lượt bạn: Trong B2, viết giá của sản phẩm đầu tiên sau khi áp dụng mức giảm ở B1. Dùng $ sao cho cùng một công thức đó, khi điền sang phải và xuống dưới đến D5, cho ra mọi mức giá trong bảng.
Trang tính sao chép công thức của bạn vào mọi ô của B2:D5, giống như cách núm điền (fill handle) làm, rồi nút Kiểm tra đọc cả mười hai kết quả. Nếu đặt dấu đô la sai, các bản sao ở hàng 3 hoặc cột C sẽ đọc nhầm giá hoặc nhầm mức giảm.
Tham chiếu tuyệt đối tới trang tính khác hoặc bảng tra cứu
Dấu đô la hoạt động y hệt khi đi kèm tên trang tính: =B2*Settings!$B$1. Chúng quan trọng nhất trong các hàm tra cứu, khi bảng phải đứng yên còn giá trị cần tìm thì dịch chuyển: =VLOOKUP(A2,$E$2:$F$10,2,FALSE) điền xuống vẫn luôn tìm trong E2:F10, còn =VLOOKUP(A2,E2:F10,2,FALSE) làm bảng trượt xuống một hàng sau mỗi bản sao và bắt đầu bỏ sót những hàng đầu (VLOOKUP). Nếu một ô cố định được dùng trong nhiều công thức, bạn cũng có thể đặt tên cho nó bằng Formulas > Define Name rồi viết =B2*Rate; tên được đặt theo cách này luôn trỏ tới cùng một ô từ mọi công thức, giống như $E$1.
Câu hỏi thường gặp
Dấu $ trong công thức Excel có nghĩa là gì?
Dấu E1chỉ khóa hàng, còn$E1` chỉ khóa cột.
Phím tắt tạo tham chiếu tuyệt đối trong Excel là gì?
Khi đang sửa công thức, bấm vào bên trong tham chiếu rồi nhấn F4 (Fn+F4 trên nhiều laptop). Mỗi lần nhấn sẽ lần lượt đổi qua $A$1, A$1, $A1 và A1. Trên Mac, nhấn Cmd+T hoặc Fn+F4.
Tham chiếu tương đối và tham chiếu tuyệt đối khác nhau thế nào?
Tham chiếu tương đối như B2 dịch chuyển khi bạn sao chép công thức: xuống một hàng, nó thành B3. Tham chiếu tuyệt đối như $B$2 vẫn là $B$2. Dùng tham chiếu tuyệt đối cho một ô mà hàng nào cũng cần, ví dụ một tỷ lệ hay một tổng.
Tham chiếu hỗn hợp trong Excel là gì?
Là tham chiếu có một dấu đô la: $A2 giữ cột và để hàng dịch chuyển, B$1 giữ hàng và để cột dịch chuyển. Điền =$A2*B$1 sang phải và xuống dưới trên một lưới ô sẽ tạo ra bảng cửu chương.
Vì sao công thức hiện 0 hoặc #DIV/0! sau khi tôi kéo xuống?
Một tham chiếu lẽ ra phải cố định đã dịch chuyển theo bản sao. Nếu hàng 2 có =B2/B7 thì hàng 3 nhận =B3/B8, mà B8 lại trống. Hãy khóa ô tổng bằng =B2/$B$7 rồi điền lại.
