Cách sao chép công thức với tham chiếu trong Excel

Trong công việc hàng ngày, khi bạn gặp các công thức có tham chiếu, việc muốn copy chúng để sử dụng lại có thể gặp một số khó khăn. Dưới đây là hướng dẫn chi tiết cách copy công thức chứa tham chiếu trong Excel.

1. Sao chép công thức tham chiếu tương đối hoặc cả tham chiếu tuyệt đối và tương đối

Với công thức chứa tham chiếu tương đối, có 2 phương pháp sao chép:

1.1. Sao chép công thức tham chiếu và đổi theo địa chỉ mới

- Trước hết, hãy hiểu rõ về tham chiếu tương đối. Đây là tham chiếu có địa chỉ linh hoạt, không cố định cột và dòng, không chứa dấu $. Khi sao chép, địa chỉ sẽ thay đổi theo vị trí mới.

- Công thức thường kết hợp tham chiếu tuyệt đối và tương đối, với ít nhất một tham chiếu cố định không thay đổi khi sao chép.

Ví dụ: Sao chép công thức trong cột thành tiền.

Sau khi đặt công thức cho ô F6, muốn sao chép cho các ô khác trong cột F6, bạn có thể thực hiện những cách sau đây:

Cách 1: Chọn ô F6 -> nhấn tổ hợp phím Ctrl + C -> Bôi đen từ ô E7 tới E12 -> Nhấn tổ hợp phím Ctrl + V => Đã sao chép công thức cho các ô còn lại. Bạn cũng có thể sử dụng chuột phải và lựa chọn Copy và Paste.

Cách 2: Bôi đen toàn bộ từ ô nhập công thức (F6) tới ô F12, nhấn tổ hợp phím Ctrl + D => Đã sao chép công thức cho tất cả các ô còn lại.

Cách 3: Chọn ô nhập công thức đầu tiên, ở đây là ô F6 -> di chuyển xuống góc dưới bên phải của ô khi xuất hiện dấu cộng, kéo thả tới các ô còn lại hoặc kích đúp chuột => Như vậy đã sao chép công thức cho các ô còn lại.

Tất cả 3 phương pháp đều đem lại kết quả giống nhau:

Đối với công thức chứa cả tham chiếu tuyệt đối và tương đối, thực hiện tương tự.

1.2. Sao chép và giữ nguyên công thức tham chiếu khi di chuyển đến vị trí mới

Nếu bạn muốn sao chép công thức tham chiếu tương đối và vẫn giữ nguyên công thức, bạn có thể thực hiện như sau:

- Cách 1: Sử dụng khi bạn cần sao chép cho 1 ô dữ liệu.

Chọn vào ô chứa công thức tham chiếu tương đối -> Di chuyển thanh công thức -> Bôi đen để sao chép công thức -> Nhấn Ctrl + C (Lưu ý không sao chép ô chứa công thức tham chiếu) -> Di chuyển đến ô cần dán công thức và nhấn Ctrl + V.

Kết quả: Công thức được giữ nguyên khi sao chép xuống ô phía dưới:

Lưu ý: Trong cách này, ngoài việc sử dụng tổ hợp phím Ctrl + C, Ctrl + V, bạn cũng có thể sử dụng chuột phải và chọn Copy và Paste.

Cách 2: Sao chép công thức mà không làm thay đổi cho nhiều ô dữ liệu.

Bước 1: Chọn khối công thức bạn muốn sao chép.

Bước 2: Nhấn tổ hợp phím Ctrl + H -> Hộp thoại xuất hiện, chọn tab Replace trong mục Find What nhập dấu '=', mục Replace nhập bất kỳ kí tự nào không có trong công thức, ví dụ như dấu & -> Nhấn chọn Replace All.

Bước 3: Sau khi chọn Replace All, toàn bộ dấu = trong công thức sẽ được thay thế bằng dấu &:

Bước 4: Ấn tổ hợp phím Ctrl + C (hoặc chuột phải chọn Copy) -> Di chuyển chuột tới vị trí cần dán công thức -> Ấn tổ hợp phím Ctrl + V (hoặc chuột phải chọn Paste). Kết quả:

Bước 5: Ấn tổ hợp phím Ctrl + H -> trong tab Replace thay dấu & bằng dấu =. Mục Find What nhập dấu &, mục Replace nhập dấu = rồi ấn Replace All.

Kết quả:

Như vậy, bạn đã sao chép công thức tham chiếu tương đối mà không làm thay đổi công thức.

Tương tự với công thức có cả tham chiếu tuyệt đối và tham chiếu tương đối, bạn thực hiện như sau.

2. Sao chép công thức chứa tham chiếu tuyệt đối

Khi làm việc với công thức chứa tham chiếu tuyệt đối, lưu ý rằng vì là tham chiếu tuyệt đối nên địa chỉ hàng và cột không thay đổi. Khi thực hiện Copy cho tham chiếu khác giá trị không đúng. Đặt giá trị tại ô F6 và chuyển địa chỉ tương đối sang địa chỉ tuyệt đối bằng cách nhấn F4 => Kết quả:

Trong trường hợp bạn sử dụng thao tác Copy công thức tham chiếu tương đối, kết quả như sau:

Vì địa chỉ là tuyệt đối, khi sao chép công thức, nó không thay đổi.

Để Copy công thức chứa tham chiếu tuyệt đối, bạn thực hiện như sau:

Cách 1: Chọn ô F6 -> nhấn tổ hợp phím Ctrl + C -> Bôi đen từ ô E7 tới E12 -> Nhấn tổ hợp phím Ctrl + V => Đã copy công thức cho các ô còn lại. Trong trường hợp này, bạn cũng có thể sử dụng chuột phải và lựa chọn Copy và Paste.

Cách 2: Bôi đen toàn bộ từ ô nhập công thức (F6) tới ô F12, nhấn tổ hợp phím Ctrl + D => Đã copy công thức cho tất cả các ô còn lại.

Cách 3: Chọn ô nhập công thức đầu tiên, ở đây ô F6 -> di chuyển xuống góc dưới bên phải của ô khi xuất hiện dấu cộng, kéo thả tới các ô còn lại hoặc kích đúp chuột=> Như vậy đã Copy công thức cho các ô còn lại.

Tất cả 3 phương pháp đều mang lại kết quả như nhau:

Ở đây là cách Copy công thức tham chiếu. Tùy thuộc vào loại tham chiếu bạn sử dụng mà bạn có thể lựa chọn cách Copy phù hợp nhất.

Chúc mừng bạn đã thành công!