Bài 4: Bản Chất Địa Chỉ Ô (Tuyệt Đối & Tương Đối)
Giai đoạn 1 – Cơ bản · Mục tiêu: Hiểu rõ cách Excel dịch chuyển công thức khi sao chép, phân biệt và sử dụng thành thạo 3 loại địa chỉ.
1. Vấn đề: Tại sao copy công thức lại bị sai?
Khi bạn viết công thức =A1*B1 ở ô C1 rồi kéo xuống dòng 2, Excel sẽ tự động thay đổi thành =A2*B2. Đây gọi là dịch chuyển tương đối – công thức di chuyển theo cùng hướng với ô đích.
Nhưng nếu bạn muốn nhân tất cả các dòng với cùng một ô cố định (ví dụ: tỷ giá ở ô E1), thì cần sử dụng địa chỉ tuyệt đối.
2. Ba loại địa chỉ ô
2.1. Địa chỉ Tương đối (Relative Reference)
=A1
- Không có dấu
$nào. - Khi sao chép, cả cột lẫn dòng đều thay đổi theo hướng kéo.
| Ô gốc | Công thức gốc | Kéo xuống 1 dòng | Kéo sang phải 1 cột |
|---|---|---|---|
| C1 | =A1 | =A2 | =B1 |
2.2. Địa chỉ Tuyệt đối (Absolute Reference)
=$A$1
- Có dấu
$trước cả cột và dòng. - Khi sao chép, cả cột lẫn dòng đều KHÔNG thay đổi – luôn trỏ về cùng một ô.
| Ô gốc | Công thức gốc | Kéo xuống 1 dòng | Kéo sang phải 1 cột |
|---|---|---|---|
| C1 | =$A$1 | =$A$1 | =$A$1 |
2.3. Địa chỉ Hỗn hợp (Mixed Reference)
Khóa cột, thả dòng:
=$A1
- Cột A cố định, dòng thay đổi khi kéo lên/xuống.
Khóa dòng, thả cột:
=A$1
- Dòng 1 cố định, cột thay đổi khi kéo sang trái/phải.
3. Bảng tổng hợp so sánh
| Loại | Cú pháp | Kéo xuống | Kéo sang phải | Dùng khi nào |
|---|---|---|---|---|
| Tương đối | A1 | A2 | B1 | Công thức lặp lại cho mỗi dòng/cột |
| Tuyệt đối | $A$1 | $A$1 | $A$1 | Tham chiếu ô cố định (tỷ giá, thuế suất) |
| Hỗn hợp (khóa cột) | $A1 | $A2 | $A1 | Bảng tra cứu, giá trị cố định theo cột |
| Hỗn hợp (khóa dòng) | A$1 | A$1 | B$1 | Bảng tra cứu, giá trị cố định theo dòng |
4. Phím tắt F4 – Chuyển đổi nhanh
Khi đang gõ hoặc chỉnh sửa công thức, đặt con trỏ vào địa chỉ ô rồi nhấn F4 liên tục:
A1 → $A$1 → A$1 → $A1 → A1 (quay vòng)
:::tip Mẹo thực chiến Thay vì nhớ cú pháp, hãy gõ địa chỉ bình thường rồi nhấn F4 cho đến khi thấy đúng loại bạn cần! :::
5. Ví dụ thực tế
Bảng tính lương nhân viên
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Tỷ lệ thưởng: | 10% | ||
| 2 | Họ Tên | Lương CB | Thưởng | Tổng |
| 3 | Nguyễn A | 10,000,000 | =B3*$B$1 | =B3+C3 |
| 4 | Trần B | 12,000,000 | =B4*$B$1 | =B4+C4 |
| 5 | Lê C | 8,000,000 | =B5*$B$1 | =B5+C5 |
- Cột C (Thưởng):
B3dịch chuyển theo dòng (tương đối),$B$1cố định (tuyệt đối). - Cột D (Tổng): Cả
B3vàC3đều tương đối → dịch chuyển đúng khi kéo xuống.
Bài tập thực hành
- Tạo bảng tính lương cho 10 nhân viên với cột: Họ tên, Lương cơ bản, Phụ cấp, Thuế, Thực lĩnh.
- Đặt Tỷ lệ thuế = 10% tại ô riêng biệt (ví dụ:
F1). - Cột Thuế:
= Lương cơ bản × Tỷ lệ thuế. Sử dụng địa chỉ tuyệt đối cho ô tỷ lệ thuế. - Kéo công thức cho tất cả nhân viên và kiểm tra kết quả.
Bài trước: Bài 3 – Phép Tính & Hàm Cơ Bản · Bài tiếp: Bài 5 – Hàm Logic