[Kách Hay] Hướng dẫn cách sao chép công thức trong Excel mới nhất 2023

Hướng dẫn cách sao chép công thức trong Excel – Kách Hay .Com đã tổng hợp thông tin từ nhiều nguồn, giúp bạn có góc nhìn đa chiều hơn. Nào chúng ta bắt đầu thôi

New Page

Xem nhanh

Cách sao chép công thức trong Excel: Xuống một cột mà không thay đổi tham chiếu, v.v.

Sao chép công thức trong Excel là một trong những tác vụ đơn giản nhất thường được thực hiện bằng một cú nhấp chuột. Tôi nói “thường” bởi vì có thể có những trường hợp rất đặc biệt yêu cầu các thủ thuật đặc biệt, chẳng hạn như sao chép một bộ công thức mà không thay đổi tham chiếu ô hoặc nhập cùng một công thức vào nhiều ô không liền kề nhau. May mắn thay, Microsoft Excel cung cấp nhiều cách để thực hiện cùng một nhiệm vụ và việc sao chép công thức cũng vậy. Trong hướng dẫn này, chúng ta sẽ thảo luận về các cách khác nhau để sao chép công thức trong Excel để bạn có thể chọn một cách tốt nhất cho công việc của mình.

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

Thể loại:
Excel đơn giản

Phải làm gì nếu bạn muốn Excel sao chép công thức sang Excel khác mà không thay đổi tham chiếu ô tương ứng? Trong mọi trường hợp , Học Excel Online sẽ đưa ra một số giải pháp. Tùy theo nhu cầu nghiệp vụ mà bạn chọn một trong các cách sao chép dữ liệu sang Excel sau.

Trong hầu hết 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ó sang các ô khác trong cùng một cột hoặc hàng, cho phép thực hiện các phép tính tương tự trên một nhóm dữ liệu. Nếu công thức của bạn chứa các tham chiếu ô tương đối (không có dấu $), thì Excel sẽ tự động điều chỉnh để mỗi công thức chỉ xử lý dữ liệu trong cùng một ô và cột. Ví dụ: nếu bạn có công thức =A1*2 trong ô B1 và sao chép công thức vào ô B3, công thức sẽ thay đổi thành =A3*2.

xem lướt qua

Sao chép hoặc di chuyển một công thức mà không thay đổi tham chiếu ô
Sao chép một dải dữ liệu công thức mà không thay đổi tham chiếu ô
- - Phương pháp 1. Sử dụng tham chiếu tuyệt đối hoặc hỗn hợp
  - Cách 2. Sao chép công thức trong Excel không thay đổi tham chiếu bằng Notepad
  - Phương pháp 3. Sao chép công thức chính xác bằng Find and Replace của Excel

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

Nếu bạn cần sao chép một công thức trong Excel hoặc chỉ cần di chuyển một công thức, thì việc sao chép chính xác điều tương tự thật dễ dàng.

Chọn ô có 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 và nhấn Ctrl + C để sao chép nó. Nếu bạn muốn di chuyển công thức, hãy nhấn Ctrl + X.

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

Ngoài ra, bạn có thể sử dụng Cách 2:

Chọn ô có công thức mà bạn muốn sao chép
Nhấn F2 hoặc nhấp đúp để có thể sửa đổi ô này
Wählen Sie die Formel in der Zelle mit der Maus und Strg + C aus, um sie zu kopieren.
Wählen Sie die Zelle aus, um die Formel einzufügen, und drücken Sie Strg + V. Sie sollten die Formel genau einfügen können, ohne den Zellbezug zu ändern, da die Formel als Text kopiert wird.

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 trong 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.

– – – – – – Có một số cách sao chép công thức trong Excel mà không làm thay đổi dữ liệu tham chiếu hay nói cách khác là cách sao chép dữ liệu trong Excel. Để có thể sử dụng tốt Excel trong công việc, chúng ta không những phải thành thạo các hàm mà còn phải sử dụng thành thạo các công cụ của Excel. Các hàm nâng cao giúp áp dụng tốt vào công việc như SUM, COUNTIFS, SUMPRODUCT, INDEX+MATCH… Các công cụ thường dùng là xác thực dữ liệu, định dạng điều kiện, pivot table… Bạn có thể nắm bắt tất cả các kiến thức này tại khóa học Excel Cơ bản đến Chuyên gia cho các chuyên gia