Bài 3 · Chương 4: Công thức và địa chỉ ô
Địa chỉ tuyệt đối ($) và phím F4
Cố định một ô trong công thức bằng dấu $, dùng phím F4 để đổi A1 thành $A$1, và sao chép công thức nhân với ô thuế suất, tỷ giá cho cả cột.
Mục lục bài
Ôn lại trước khi học
Thử nhớ lại trước, rồi mới xem đáp án.
Excel làm phép tính nào trước trong công thức
=5+2*3? Muốn cộng trước thì làm thế nào?Xem đáp án
Nhân trước, cộng sau, nên kết quả là 11. Muốn cộng trước, đặt phép cộng trong ngoặc:=(5+2)*3cho 21.Kéo nút điền từ một ô chứa số 1 xuống dưới thì các ô mới chứa gì? Làm sao để được dãy 1, 2, 3?
Xem đáp án
Các ô mới chép lại số 1. Muốn dãy 1, 2, 3, gõ 1 và 2 vào hai ô đầu, chọn cả hai rồi kéo nút điền.Công thức ở G2 là
=F2*H2. Sao chép xuống G3 thì công thức ở G3 là gì, và vì sao?Xem đáp án
Là=F3*H3. Địa chỉ tương đối đi theo hướng sao chép: xuống một hàng thì mỗi địa chỉ xuống một hàng.
Mục tiêu
Sau bài này bạn có thể:
- Giải thích khi nào cần cố định một ô bằng dấu $.
- Dùng F4 để đổi A1 thành $A$1.
- Viết công thức nhân với một ô cố định (thuế suất VAT, tỷ giá) rồi sao chép đúng cho cả cột.
Ví dụ mẫu: tính hoa hồng theo một tỷ lệ chung
Mọi dòng hóa đơn dùng chung một tỷ lệ hoa hồng, nằm ở ô J1 (5%). Cột K (Hoa hồng) còn trống.
Tính Hoa hồng ở K2, rồi cố định ô J1 trước khi sao chép
- Chỗ hay sai
Làm gì: Chọn ô
K2, gõ=E2*F2*J1rồi bấm Enter.K2hiện59,850.Vì sao: Hoa hồng bằng Đơn giá nhân Số lượng nhân tỷ lệ ở J1. Kiểm tra: 133,000 × 9 = 1,197,000, nhân 5% ra 59,850. Hàng đầu tính đúng, nhưng J1 chưa được cố định.
Làm gì: Bấm vào
K2, rồi bấm vào thanh công thức, chọn địa chỉJ1rồi bấmF4cho đến khi địa chỉ thành$J$1. Công thức là=E2*F2*$J$1.Vì sao: Mỗi lần bấm F4, địa chỉ chuyển sang một kiểu khác; bạn dừng khi thấy $J$1. Dấu $ khóa cột J và hàng 1. Địa chỉ có $ gọi là địa chỉ tuyệt đối: khi sao chép, nó giữ nguyên.
Làm gì: Bấm Enter, rồi kéo nút điền từ
K2xuốngK8.Vì sao: Đơn giá và Số lượng không có $, nên đi theo từng hàng. Tỷ lệ $J$1 đứng yên ở mọi hàng.
Làm gì: Bấm vào
K3: công thức là=E3*F3*$J$1và kết quả là11,200.Vì sao: Nếu bạn quên bước 2, K3 sẽ thành
=E3*F3*J2: J2 là một ô khác, không còn là tỷ lệ 5%, nên kết quả sai. Kiểm tra: 14,000 × 16 = 224,000, nhân 5% ra 11,200.
Tự hỏi mình
- Vì sao E2 và F2 không cần dấu $, còn J1 thì cần?
- Nếu chỉ cần sao chép công thức xuống dưới, viết J$1 thay cho $J$1 có dùng được không? Vì sao?
Bảng dưới đây là bốn kiểu địa chỉ khi bạn sao chép công thức xuống hai ô và sang phải hai ô.
| Địa chỉ ban đầu | Tên gọi | Sau khi sao chép |
|---|---|---|
$A$1 | Cột và hàng đều cố định | $A$1 |
A$1 | Hàng cố định, cột đi theo | C$1 |
$A1 | Cột cố định, hàng đi theo | $A3 |
A1 | Cả cột và hàng đều đi theo | C3 |
Điền vào chỗ trống
Ba công thức dưới đây cùng một dạng: nhân với một ô cố định. Chỗ trống nhiều dần.
Bảng giá nhập khẩu: Giá USD ở B2, Tỷ giá ở E1. Viết công thức Giá VND cho dòng 2, rồi sao chép xuống.
Bảng lương: Lương cơ bản ở C2, Tỷ lệ phụ cấp ở F1. Viết công thức Phụ cấp cho dòng 2, rồi sao chép xuống.
Bảng giá: Đơn giá ở D2, Thuế suất ở B1. Viết công thức Tiền thuế cho dòng 2, rồi sao chép xuống.
Tìm chỗ sai
Công thức hoa hồng dưới đây cố định quá tay: có dấu $ ở một ô không được khóa. Từ hàng 3 trở đi kết quả khác 0 nhưng sai: mọi hàng đều lấy Đơn giá của hàng 2, nên đổi Đơn giá ở hàng dưới thì Hoa hồng không đổi.
Tìm chỗ sai
Công thức này có một chỗ sai. Bấm vào phần bạn nghĩ là sai.
Đọc xong, giờ thử
Luyện tập ngay (3 bài tập)
Làm trực tiếp trên lưới trong trình duyệt. Mỗi lần bấm chấm bài, DeskLab kiểm tra kết quả và gợi ý nếu bạn chưa làm đúng.
Bắt đầu luyện tậpChấm tự động ngay trên máy bạn. Không cần đăng nhập. Tiến độ lưu trên trình duyệt này.
Kiểm tra nhanh
Khi đang sửa công thức, con trỏ nằm ở địa chỉ E2. Bấm phím nào để đổi E2 thành $E$2?
Công thức ở K2 là =E2*F2*$J$1. Sao chép xuống K3 thì công thức ở K3 là gì?
Cột Hoa hồng ra đúng ở hàng 2 nhưng sai từ hàng 3 trở đi. Nguyên nhân hay gặp nhất là gì?
Bạn bấm F4 mà địa chỉ không đổi. Cách nào luôn làm được?
Lỗi hay gặp
Hàng đầu đúng, nhưng từ hàng 3 trở đi kết quả ra 0 hoặc sai.
- Nguyên nhân
- Quên $ nên ô tham chiếu (ô tỷ lệ, tỷ giá) đi xuống theo hàng và trỏ sang một ô khác.
- Cách sửa
- Mở công thức ở hàng đầu, chọn địa chỉ cần cố định và bấm F4 để có dạng
$J$1, rồi sao chép lại cả cột.
Từ hàng 3 trở đi kết quả khác 0 nhưng sai: đổi Đơn giá ở hàng dưới thì Hoa hồng không đổi.
- Nguyên nhân
- Đặt $ cho cả ô cần đổi theo hàng, như $E$2: mọi hàng khi sao chép vẫn lấy Đơn giá của hàng 2.
- Cách sửa
- Chỉ đặt $ cho ô dùng chung. Với
=$E$2*F2*$J$1, bỏ $ ở E2 để có=E2*F2*$J$1.
Bấm F4 mà địa chỉ không đổi.
- Nguyên nhân
- Microsoft ghi, trong hướng dẫn Làm lại và Lặp lại, rằng nếu F4 không phản ứng thì có thể cần bấm phím F-Lock hoặc Fn rồi bấm F4. Bàn phím của bạn có thể cần như vậy.
- Cách sửa
- Luôn có thể gõ dấu
$thẳng vào công thức, ví dụ$J$1.
Trên Excel thật
Trên Excel thật, F4 chỉ đổi kiểu địa chỉ khi con trỏ đang ở một địa chỉ trong công thức. Khi không ở trong công thức, F4 lặp lại thao tác bạn vừa làm. Mỗi lần bấm F4, địa chỉ chuyển sang một kiểu khác trong các kiểu A1, $A$1, A$1 và $A1.
Lưới luyện tập của DeskLab chạy trên bộ máy Univer. Gói này có khai báo phím tắt F4 để đổi kiểu địa chỉ khi bạn đang sửa công thức, nhưng DeskLab chưa thử phím này trên máy thật. Nếu F4 không phản ứng, bạn gõ dấu $ bằng tay: bài chấm công thức theo chữ bạn gõ, không theo việc bạn dùng F4 hay không.