Hướng dẫn sao chép công thức trong Excel mà không làm thay đổi dữ liệu tham chiếu

Hầu hết trong các trường hợp, bạn có thể điền công thức vào một ô và sao chép nó vào các ô khác trong cùng cột hoặc hàng để có thể thực hiện các tính toán tương tự đối với một nhóm dữ liệu. Nếu công thức của bạn có chứa ô tham chiếu tương đối (không có ký hiệu $), Excel sẽ tự động điều chỉnh để mỗi công thức chỉ xử lý dữ liệu trong cùng ô và cùng cột. Ví dụ, nếu bạn có công thức =A1*2 ở ô B1, và bạn copy công thức tới ô B3, công thức sẽ đổi thành =A3*2.

Nhưng nếu bạn muốn Excel copy công thức chính khác mà không làm thay đổi tham chiếu ô tương ứng thì sao? Tùy thuộc vào yêu cầu công việc của bạn, hãy chọn một trong những cách thức dưới đây nhé.

Sao chép hoặc di chuyển một công thức đơn lẻ mà không làm thay đổi tham chiếu ô

Nếu bạn cần sao chép hoặc di chuyển chỉ một công thức, việc sao chép y hệt rất đơn giản.

• Chọn ô với công thức mà bạn muốn sao chép
• Chọn công thức trong thanh công thức bằng chuột, nhấn Ctrl + C để sao chép. Nếu bạn muốn di chuyển công thức, nhấn Ctrl + X.

• Nhấn phím Esc để thoát khỏi thanh công thức.
• Chọn ô bạn muốn dán công thức và nhấn Ctrl + V.

Bên cạnh đó, bạn có thể sử dụng cách 2:

• Chọn ô có công thức bạn muốn sao chép
• Nhấn F2 hoặc click chuột đôi để có thể sửa đổi ô đó
• Chọn công thức trong ô bằng cách dùng chuột và Ctrl + C để sao chép.
• Chọn ô để dán công thức và nhấn Ctrl + V. Bạn sẽ có thể dán công thức chính xác mà không thay đổi tham chiếu của ô vì công thức được copy dưới dạng chữ.

Mẹo. Để nhanh chóng copy công thức từ những ô phía trên mà không thay đổi tham chiếu, chọn ô bạn muốn dán công thức và nhấn Ctrl + ‘

Sao chép một vùng dữ liệu công thức mà không thay đổi tham chiếu ô

Để di chuyển hay sao chép vùng dữ liệu công thức để mà không có tham chiếu ô nào bị thay đổi, bạn có thể sử dụng cách thức sau.

Cách 1. Sử dụng tham chiếu tuyệt đối hay tham chiếu hỗn hợp

Nếu bạn cần phải sao chép chính xác công thức với tham chiếu ô tương đối (như A1), cách tốt nhất là đổi nó thành tham chiếu tuyệt đối ($A$1) để cố định tham chiếu này đối với ô, như vậy nó sẽ giữ nguyên dù bạn có di chuyển công thức đi đâu đi chăng nữa. Bên cnahj dó, bạn có thể sử dụng tham chiếu ô hỗn hợp ($A1 hoặc A$1) để khóa cột hoặc hàng. Bạn nghĩ rằng cách này không hợp lý chút nào? Vậy hãy cùng xem xét ví dụ sau nhé.

Giả sử bạn có một bảng tính toán giá hoa quả ở theo đồng Euro dựa trên giá USD ở cột B và tỉ giá ở ô C2:

Như bạn có thể thấy ở ảnh trên, công thức bao gồm một tham chiếu ô tuyệt đối ($C$2) nhằm cố định tỷ giá ở ô C2, và một tham chiếu ô tương đối ở ô B5 vì bạn muốn tham chiếu này điều chỉnh ở mỗi hàng. Cách tiếp cận này sẽ hiệu quả miễn là khi nào công thức vẫn ở cột C.

Nhưng hãy xem thử điều gì xảy ra nếu bạn cần chuyển giá theo Euro từ cột C sang cột F. Nếu bạn copy công thức theo cách thông thường bằng công cụ copy/paste, công thức ở ô C5 (=B5*$C$2) sẽ đổi thành =D5*$C$2 khi dán vào ô F5, khiến việc tính toán bị sai.

Để tránh tình trạng này, hãy chuyển tham chiếu tương đối (B5) thành tham chiếu hỗn hợp $B5 (cột tuyệt đối và dòng tương đối). Bằng việc thêm ký hiệu $ trước mỗi ký tự của cột, bạn đã cố định tham chiếu ở cột B dù cho công thức di chuyển như thế nào.

Nếu bạn sao chép hay di chuyển công thức từ cột D sang cột F, hay bất cứ cột nào, tham chiếu cột sẽ không thay đổi vì bạn đã ‘khóa’ tham chiếu bằng ký hiệu $ ($B5).

Tham chiếu ô trong Excel có thể khó để hình dung khi mới bắt đầu, nhưng nó sẽ xứng đáng thời gian và công sức của bạn vì chúng tiết kiệm nhiều thời gian cho bạn một cách lâu dài. Ví dụ, bạn hoàn toàn có thể tính toán toàn bộ bảng chỉ với một công thức duy nhất sử dụng tham chiếu ô hỗn hợp.

Tuy nhiên, nếu bạn có rất nhiều công thức với tham chiếu ô tương đối trong trang tính và bạn cần sao chép chính xác những công thức đó nhưng bạn không chắc bạn có thể tham chiếu đúng, một trong vài cách sau có thể là giải pháp cho bạn.

Cách 2. Sao chép công thức Excel mà không thay đổi tham chiếu bằng Notepad

1. Điền công thức ở chế độ hiển thị bằng cách nhấn Ctrl + `, hoặc bằng bất cứ phương thức nào trong bài viết Cách hiển thị công thức trong Excel.
2. Chọn tất cả các ô với công thức bạn muốn sao chép hay di chuyển
3. Nhấn Ctrl + C để sao chép công thức, hoặc Ctrl + X để di chuyển công thức.

4. Mở Notepad hay bất cứ công cụ chỉnh sửa văn bản nào và nhấn Ctrl + C để dán công thức vào công cụ đó, sau đó chọn Ctrl + A để chọn toàn bộ công thức và nhấn Ctrl + C để sao chép chúng.
5. Trong Excel, bạn chọn ô muốn dán công thức và nhấn Ctrl + V.

Chú ý:
1. Bạn chỉ có thể dán công thức tại cùng 1 trang tính nơi chứa công thức gốc, trừ khi tham chiếu có bao gồm tên của trang tính, nếu không công thức sẽ bị hỏng.
2. Trang chiếu nên ở trong chế độ hiển thị công thức. Để kiểm tra, hãy vào Formulas > Formula Auditing và kiểm tra phần Show Formulas có được chọn hay không. Sau khi đi dán công thức, nhấn Ctrl + ` để đóng chế độ hiển thị công thức.

Cách 3. Sao chép công thức chính xác bằng cách dùng Find and Replace của Excel

Để sao chép một dãy các công thức của Excel mà không làm thay đổi tham chiếu ô, bạn có thể dùng công cụ Find and Replace của Excel.

1. Chọn ô có công thức bạn muốn sao chép.
2. Ở thanh Home, bạn vào Editing, chọn Find & Select > Replace… Hoặc đơn giản hơn, nhấn phím Ctrl + H là đường tắt để mởi hộp thoại Find & Replace trên Excel.
3. Trong cửa sổ hộp thoại Find & Replace, đánh dấu = vào ô Find what. Trong ô Replace with, điền một vào ký hiệu hay dãy ký hiệu không được sử dụng trong bất cứ công thức nào như ~, # hay .

Mục đích của bước này là biến công thức của bạn thành dãy văn bản, ngăn không cho Excel thay đổi tham chiếu ô trong suốt quá trình sao chép.

Lưu ý. Không sử dụng dấu hoa thị (*) hay dấu hỏi (?) để thay thế vì chúng sẽ khiến những bước sau trở nên khó khăn hơn.

4. Click vào ô Replace All trong hội thoại và đóng cửa sổ Find and Replace. Tất cả công thức trong vùng được chọn sẽ biến thành dãy văn bản:

5. Bây giờ, bạn có thể chọn bất cứ ô nào, chọn Ctrl + C để copy, chọn ô trên cùng trong trang tính mà bạn đang làm việc nơi bạn muốn dán công thức, và nhấn Ctrl + V. Vì Excel không thay đổi công thức không có dấu = phía trước, chúng sẽ được sao chép y hệt mà không thay đổi tham chiếu.

6. Dùng Find & Replace để đảo ngược sự thay đổi này. Chọn cả vùng dữ liệu copy và vùng dữ liệu dán công thức. Nhấn Ctrl + H để mở cửa sổ Find & Replace. Ở ô Find what, hãy điền hoặc bất cứ ký hiệu nào mà bạn đã dùng ở bước 3. Ở ô Replace with, điền vào đó dấu = và click nút Replace All để hoàn thành.