Khi copy ô chứa công thức, tham chiếu ô trong công thức cũng di chuyển tương ứng số hàng/cột. Loại tham chiếu này gọi là tham chiếu tương đối (relative reference).
Khi copy (Ctrl + C) công thức hình trên, dán (Ctrl + V) vào ô khác, tham chiếu đổi từ cột B sang cột D, tổng khác đi.
Muốn ngăn Excel đổi tham chiếu khi copy, dùng tham chiếu tuyệt đối (absolute reference). Tạo tham chiếu tuyệt đối: chèn dấu đô la ($) trước cả 2 phần tham chiếu ô trong công thức muốn cố định, như hình.
Có 3 loại tham chiếu: tương đối, tuyệt đối, hỗn hợp. Ví dụ:
- Tham chiếu tương đối: A1 – Excel đổi tham chiếu theo cột/hàng tương ứng.
- Tham chiếu hỗn hợp: $A1 – Excel luôn tham chiếu cột A.
- Tham chiếu hỗn hợp: B$1 – Excel luôn tham chiếu hàng 1.
- Tham chiếu tuyệt đối: $B$1 – Excel luôn tham chiếu ô B1.
Có cách tắt gõ dấu $ khi chọn/nhập tham chiếu trong công thức. Khi gõ công thức xong một tham chiếu ô, nhấn F4 chuyển đổi 4 loại tham chiếu. Ví dụ gõ =100*B1.
- Nhấn F4 → =100*$B$1 (luôn tham chiếu ô B1)
- Nhấn F4 lại → =100*B$1 (luôn tham chiếu hàng 1)
- Nhấn F4 lại → =100*$B1 (luôn tham chiếu cột B)
- Nhấn F4 lại → quay về =100*B1 (tham chiếu tương đối)
Tạm dừng nhập từng tham chiếu để nhấn F4 cho đến khi đúng loại tham chiếu cho tham chiếu hiện tại.
Để copy công thức dùng tham chiếu tuyệt đối giữ nguyên tham chiếu, chọn ô chứa công thức > copy (Ctrl + C) > click ô đích dán.
Đảm bảo tab Trang chủ (Home) đang hoạt động. Click mũi tên nút Dán (Paste) khu vực Dán tab Trang chủ. Chọn Công thức (Formulas) từ menu thả xuống.
Nhận thấy tổng hiển thị ở ô đích từ ô gốc, và công thức Thanh công thức (Formula Bar) ô đích chứa cùng tham chiếu tuyệt đối như công thức gốc.
LƯU Ý: Copy dán công thức vào ô đích không nghĩa là công thức ô đích cập nhật khi công thức gốc cập nhật.
Có cách copy dán ô chứa công thức vào ô đích sao cho kết quả công thức gốc luôn hiển thị ở ô đích khi thay đổi. Liên kết (link) đến ô gốc.
Làm: chọn copy ô gốc có công thức > click ô đích dán link. Click nút Dán (Paste) khu vực Dán tab Trang chủ hiện menu thả xuống. Chọn Dán liên kết (Paste Link).
Lại thấy tổng ô gốc hiển thị ô đích. Lần này Thanh công thức hiển thị tham chiếu tuyệt đối đến ô gốc chứa công thức. Mỗi lần kết quả thay đổi ô gốc, tổng ô đích cũng cập nhật.
LƯU Ý: Chỉ muốn dán kết quả công thức vào ô đích, chọn Dán giá trị (Paste Values) từ menu Dán khi dán vào ô đích.
Dán liên kết tiện lợi nếu muốn hiển thị tổng từ phần worksheet ở vị trí nổi bật với định dạng đặc biệt, có thể cho bài trình bày, và muốn ô chứa liên kết luôn cập nhật. Thưởng thức!