Bài 2 · Chương 4: Công thức và địa chỉ ô
Sao chép công thức và địa chỉ tương đối
Viết công thức một lần rồi sao chép cho cả cột. Hiểu vì sao địa chỉ ô tự đổi khi sao chép, kiểm tra hàng cuối và nhận ra lỗi #REF!.
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.
Muốn cả hàng tiêu đề in đậm và căn giữa, bạn làm thế nào cho nhanh, và sao chép định dạng sang bảng khác bằng cách nào?
Xem đáp án
Chọn cả hàng tiêu đề rồi định dạng một lần, thay vì làm từng ô. Muốn sao chép định dạng sang bảng khác, dùng Format Painter (Cọ định dạng).Gõ 1 và 2 vào hai ô liền nhau rồi kéo nút điền xuống thì ra dãy nào? Nếu chỉ gõ 2 vào một ô rồi kéo thì sao?
Xem đáp án
Hai ô 1 và 2 cho dãy 1, 2, 3, 4, 5. Một ô chứa 2 thì kéo xuống chép lại số 2 ở mọi ô.Vì sao trong công thức nên bấm chọn ô thay vì gõ thẳng một con số?
Xem đáp án
Công thức đọc giá trị từ ô, nên khi số trong ô đổi thì kết quả tự đổi theo. Số gõ cứng trong công thức chỉ đổi khi bạn sửa công thức.
Mục tiêu
Sau bài này bạn có thể:
- Giải thích vì sao địa chỉ trong công thức thay đổi khi sao chép xuống dưới hoặc sang phải.
- Sao chép một công thức cho cả cột bằng nút điền hoặc nháy đúp.
- Kiểm tra công thức ở hàng cuối để xác nhận tham chiếu đúng.
- Nhận biết lỗi #REF! khi xóa hàng/cột mà công thức đang tham chiếu.
Ví dụ mẫu: tính Thực lĩnh cho cả bảng lương
Bảng lương dưới đây có Ngày công ở cột F và Lương ngày ở cột H. Cột G (Thực lĩnh) còn trống.
Viết công thức một lần ở G2 rồi sao chép xuống G7
- Chỗ hay sai
Làm gì: Chọn ô
G2, gõ=, bấm ôF2, gõ*, bấm ôH2rồi bấm Enter. Công thức là=F2*H2vàG2hiện8,234,000.Vì sao: Bạn chỉ viết công thức một lần, ở hàng đầu. F2 và H2 đều nằm ở hàng 2, hãy kiểm tra kỹ: nếu công thức đầu trỏ sai hàng thì lỗi lan ra cả cột.
Làm gì: Chọn lại
G2. Kéo nút điền (chấm vuông nhỏ ở góc dưới bên phải ô) xuống tới ôG7. Trên Excel thật, bạn cũng có thể bấm đúp vào nút điền thay cho kéo.Vì sao: Excel chép công thức xuống từng hàng và tự dịch địa chỉ theo hàng, nên bạn không phải gõ lại công thức cho 6 nhân viên.
Làm gì: Bấm vào
G3: công thức là=F3*H3. Bấm vàoG7: công thức là=F7*H7và kết quả là8,398,000.Vì sao: Địa chỉ trong công thức là địa chỉ tương đối: nó nhớ vị trí so với ô chứa công thức. Ở G2, F2 là ô cùng hàng, cách một cột bên trái. Chép xuống G7 thì ô cùng hàng, cách một cột bên trái là F7.
Làm gì: Luôn kiểm tra hàng cuối cùng của bảng. Nếu công thức ở
G7đúng thì thường cả cột đúng.Vì sao: Công thức hàng cuối cho thấy việc sao chép có lệch hàng hay không, mà bạn không phải đọc từng ô.
Tự hỏi mình
- Vì sao công thức ở G7 là
=F7*H7mà bạn không phải gõ lại nó? - Nếu sao chép công thức ở G2 sang một ô trống bên phải, ví dụ J2, địa chỉ F2 sẽ đổi thành ô nào?
Khi bạn sao chép một công thức xuống hai ô và sang phải hai ô, địa chỉ tương đối A1 thành C3. Địa chỉ đi theo hướng sao chép, đúng số ô bạn đi.
Điền vào chỗ trống
Ba bài dưới đây hỏi cùng một việc: công thức ở ô mới sau khi sao chép. Chỗ trống nhiều dần.
Bảng tồn kho: công thức ở E2 là =C2*D2, rồi được sao chép xuống. Công thức ở E5 là gì?
Bảng tiền điện: công thức ở D2 là =B2*C2, rồi được sao chép xuống. Công thức ở D9 là gì?
Bảng điểm: công thức ở E3 là =C3-D3, rồi được sao chép sang phải một ô. Công thức ở F3 là gì?
Tìm chỗ sai
Ô G2 có công thức dưới đây, rồi được sao chép xuống cho cả cột. Cột Thực lĩnh ra sai từ hàng đầu tiên.
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 (2 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
Công thức ở G2 là =F2*H2. Bạn sao chép nó xuống G3. Công thức ở G3 là gì?
Bạn kéo nút điền xuống quá hàng cuối của bảng. Điều gì xảy ra?
Sau khi xóa cột Phụ cấp, ô Thực lĩnh báo #REF!. Nguyên nhân là gì?
Cách nào đúng để có công thức cho cả cột Thực lĩnh?
Lỗi hay gặp
Tưởng sao chép công thức thì giữ nguyên địa chỉ, rồi ngạc nhiên vì cả cột ra sai.
- Nguyên nhân
- Địa chỉ tương đối đổi theo vị trí khi sao chép. Nếu bạn dán giá trị thay vì công thức, cả cột sẽ mang cùng một số.
- Cách sửa
- Sao chép công thức (kéo nút điền), rồi bấm vào ô ở hàng cuối để xem địa chỉ đã đi theo hàng chưa.
Kéo nút điền quá hàng cuối, thấy công thức nằm ở cả những hàng trống.
- Nguyên nhân
- Nút điền chép vào mọi ô bạn kéo qua.
- Cách sửa
- Bấm Ctrl+Z ngay sau khi kéo, hoặc chọn phần thừa rồi xóa nội dung.
Công thức viết ở hàng 2 nhưng trỏ tới hàng 3, như
=F2*H3.- Nguyên nhân
- Chọn nhầm ô khi viết công thức đầu tiên. Khi sao chép, độ lệch một hàng đi theo mọi hàng.
- Cách sửa
- Mở ô đầu, kiểm tra các địa chỉ cùng hàng, sửa rồi sao chép lại cả cột.
Ô báo
#REF!sau khi xóa hàng hoặc cột.- Nguyên nhân
- Công thức đang tham chiếu ô đã bị xóa.
- Cách sửa
- Bấm Ctrl+Z để khôi phục nếu vừa xóa nhầm. Nếu không, mở công thức và chọn lại ô đúng, đừng gõ đè số vào ô lỗi.
Trên Excel thật
Trên Excel thật, Ctrl+D điền công thức xuống cả vùng đã chọn, và Ctrl+R điền sang phải. Nếu không thấy nút điền, nó có thể đang bị tắt: vào File (Tệp) › Options (Tùy chọn) › Advanced (Nâng cao), mục Editing options, rồi bật Enable fill handle and cell drag-and-drop.
Lưới luyện tập của DeskLab đọc công thức trong từng ô, kể cả công thức tạo bằng nút điền. Vì vậy dù bạn kéo nút điền hay gõ từng ô, bài chỉ cần công thức đúng ở mọi ô.