Sao chép công thức trong Excel mà không làm thay đổi dữ liệu tham chiếu

Trường hợp bạn muốn Excel sao chép công thức trong Excel khác mà không làm thay đổi tham chiếu ô tương ứng thì phải làm sao? Học Excel Online sẽ đưa ra một số giải pháp trên tưng trường hợp. 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 copy dữ liệu trong excel dưới đây nhé.

Hầu hết trong những trường hợp, bạn hoàn toàn có thể điền công thức vào một ô và sao chép nó vào những ô khác trong cùng cột hoặc hàng để hoàn toàn có thể thực thi những thống kê giám sát tựa như so với một nhóm tài 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 USD ), Excel sẽ tự động hóa kiểm soát và điều chỉnh để mỗi công thức chỉ giải quyết và xử lý tài 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 .

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 công thức trong Excel hoặc chuyển dời chỉ một công thức, việc sao chép y hệt rất đơn thuầ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 hoàn toà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 ô

Để vận động và di chuyển hay sao chép vùng tài liệu công thức để mà không có tham chiếu ô nào bị biến hóa, bạn hoàn toàn có thể sử dụng phương pháp 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 đúng mự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 ( USD A $ 1 ) để cố định và thắt chặt tham chiếu này so với ô, như vậy nó sẽ giữ nguyên dù bạn có vận động và di chuyển công thức đi đâu đi chăng nữa. Bên cnahj dó, bạn hoàn toàn có thể sử dụng tham chiếu ô hỗn hợp ( USD 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ài hòa và 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 giám sát giá hoa quả ở theo đồng Euro dựa trên giá USD ở cột B và tỉ giá ở ô C2 :

Như bạn hoàn toàn có thể thấy ở ảnh trên, công thức gồm có một tham chiếu ô tuyệt đối ( USD C USD 2 ) nhằm mục đích cố định và thắt chặt tỷ giá ở ô C2, và một tham chiếu ô tương đối ở ô B5 vì bạn muốn tham chiếu này kiểm soát và điều chỉnh ở mỗi hàng. Cách tiếp cận này sẽ hiệu suất cao 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ì bằng công cụ copy / paste, công thức ở ô C5 ( = B5 * USD C USD 2 ) sẽ đổi thành = D5 * USD C USD 2 khi dán vào ô F5, khiến việc thống kê giám sát bị sai .
Để tránh thực 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 USD trước mỗi ký tự của cột, bạn đã cố định và thắt chặt tham chiếu ở cột B dù cho công thức vận động và di chuyển như thế nào .
Nếu bạn sao chép hay chuyển dời công thức từ cột D sang cột F, hay bất kể cột nào, tham chiếu cột sẽ không biến hóa vì bạn đã ‘ khóa ’ tham chiếu bằng ký hiệu USD ( $ B5 ) .

Tham chiếu ô trong Excel hoàn toàn có thể khó để tưởng tượng khi mới mở màn, nhưng nó sẽ xứng danh thời hạn và công sức của con người của bạn vì chúng tiết kiệm ngân sách và chi phí nhiều thời hạn cho bạn một cách vĩnh viễn. Ví dụ, bạn trọn vẹn hoàn toàn có thể giám sát hàng loạt 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 những công thức của Excel mà không làm đổi khác tham chiếu ô, bạn hoàn toà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 biến hóa tham chiếu ô trong suốt quy 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 hành lang 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 hoàn toàn có thể chọn bất kể ô nào, chọn Ctrl + C để copy, chọn ô trên cùng trong trang tính mà bạn đang thao tác nơi bạn muốn dán công thức, và nhấn Ctrl + V. Vì Excel không biến hóa 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 biến hóa 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.

– – – – – –
Đó là một vài cách để sao chép công thức trong Excel mà không làm đổi khác tài liệu tham chiếu hay nói cách khác là cách copy dữ liệu trong excel. Để hoàn toàn có thể ứng dụng tốt Excel vào trong việc làm, tất cả chúng ta không chỉ nắm vững được những hàm mà còn phải sử dụng tốt cả những công cụ của Excel. Những hàm nâng cao giúp vận dụng tốt vào việc làm như SUMIFS, COUNTIFS, SUMPRODUCT, INDEX + MATCH … Những công cụ thường sử dụng là Data validation, Conditional formatting, Pivot table …
Toàn bộ những kiến thức và kỹ năng này những bạn đều hoàn toàn có thể học được trong khóa học Excel từ cơ bản đến chuyên viên dành cho người đi làm

Source: http://wp.ftn61.com
Category: Thủ Thuật