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. 10/2021

Cách sao chép công thức xuống một cột

Microsoft Excel cung cấp một cách thực sự nhanh chóng để sao chép công thức xuống một cột. Bạn chỉ cần làm như sau:

• Nhập công thức vào ô trên cùng.
• Tìm ô mang công thức và di con trỏ chuột qua một hình vuông nhỏ ở góc dưới bên phải của ô, được gọi là ô điều khiển Điền . Lúc bạn làm điều này, con trỏ sẽ thay đổi thành một dấu thập dày màu đen.
• Giữ và kéo chốt điền xuống cột qua những ô bạn muốn sao chép công thức.

Theo cách tương tự, bạn mang thể kéo công thức vào những ô ngay lập tức kề ở bên phải, bên trái hoặc lên trên.

Trong ví dụ trên, để đảm bảo công thức đã được sao chép chuẩn xác, hãy tậu một số ô trong cột C, chẳng hạn như C4, và xem tham chiếu ô trong thanh công thức. Như bạn mang thể thấy trong ảnh chụp màn hình bên dưới, công thức hoàn toàn đúng – liên quan tới hàng 4, chuẩn xác như nó phải là:

Cách sao chép công thức mà ko cần sao chép định dạng

Sao chép công thức bằng cách kéo chốt điền ko chỉ sao chép công thức mà còn cả định dạng ô nguồn như phông chữ hoặc màu nền, ký hiệu tiền tệ, số lượng vị trí thập phân được hiển thị, v.v. Trong hồ hết những trường hợp, điều này hoạt động tốt, nhưng thỉnh thoảng nó mang thể làm xáo trộn những định dạng hiện mang trong những ô mà công thức đang được sao chép vào. Một ví dụ phổ quát là ghi đè màu vào hàng thay thế như trong ảnh sau.

Để ngăn việc ghi đè lên định dạng ô hiện mang, hãy kéo chốt điền như được minh họa ở trên, thả nó ra, bấm vào thực đơn thả xuống Tùy tậu Tự động điền và tậu Fill With out Formatting.

Sao chép công thức vào toàn bộ cột

Như bạn vừa thấy, chốt điền giúp sao chép công thức trong Excel thực sự dễ dàng. Nhưng nếu bạn cần sao chép một công thức xuống một trang tính hàng trăm dòng thì sao? Kéo công thức qua hàng trăm hàng ko phải là một ý tưởng hay. Might mắn thay, Microsoft Excel cũng cung cấp một số giải pháp nhanh chóng cho trường hợp này.

Bấm lưu ban vào dấu cùng để điền toàn bộ cột

Để vận dụng công thức cho toàn bộ cột, hãy bấm lưu ban vào dấu cùng thay vì kéo nó. Đối với những người đã bỏ qua phần trước nhất của hướng dẫn này, những bước khía cạnh sẽ làm theo bên dưới.

Để sao chép một công thức Excel vào toàn bộ cột, hãy làm như sau:

• Nhập công thức của bạn vào ô trên cùng.
• Định vị con trỏ ở góc dưới bên phải của ô mang công thức, đợi cho tới lúc nó chuyển thành dấu cùng, rồi bấm lưu ban vào dấu cùng.

• Lưu ý: Bấm lưu ban vào dấu cùng sẽ sao chép công thức xuống dưới lúc mang bất kỳ dữ liệu nào ở bên trái. Nói cách khác, miễn sao bạn mang tiêu đề hàng hoặc bất kỳ dữ liệu nào khác trong bất kỳ cột nào bên trái, Excel sẽ tiếp tục sao chép công thức. Ngay sau lúc một hàng trống xuất hiện, tự động điền sẽ ngừng lại. Vì vậy, nếu trang tính của bạn mang bất kỳ khoảng trống nào, bạn sẽ phải lặp lại quy trình trên để sao chép công thức bên dưới một hàng trống:

Sao chép công thức vào những ô / dải ô ko ngay lập tức kề

Ko cần phải nói rằng chốt điền là cách nhanh nhất để sao chép công thức trong Excel. Nhưng điều gì sẽ xảy ra nếu bạn muốn sao chép công thức Excel của mình trong những ô ko ngay lập tức kề hoặc nằm ngoài phần cuối của dữ liệu nguồn? Chỉ cần sử dụng cách sao chép và dán cũ:

• Bấm vào ô mang công thức để tậu nó.
• Nhấn Ctrl + C để sao chép công thức.
• Tìm một ô hoặc một dải ô mà bạn muốn dán công thức (để tậu những dải ô ko ngay lập tức kề, hãy nhấn và giữ Ctrl).
• Nhấn Ctrl + V để dán công thức.
• Nhấn Enter để hoàn thành những công thức đã dán.

Và đây là kết quả

• Ghi chú: Những phím tắt sao chép / dán sao chép công thức và định dạng. Để sao chép công thức mà ko cần định dạng, hãy tậu tùy tậu Dán thích hợp hơn nhé.

Sao chép công thức trong Excel mà ko thay đổi tham chiếu

Những công thức Excel hiếm lúc xuất hiện trong bảng tính một cách đơn độc. Trong hồ hết những trường hợp, bạn nhập công thức vào một ô, sau đó sao chép công thức đó sang những ô khác trong cùng một cột hoặc hàng, để thực hiện cùng một phép tính trên một nhóm dữ liệu. Và nếu công thức của bạn chứa những tham chiếu ô tương đối (ko mang $), Excel sẽ tự động điều chỉnh chúng để mỗi công thức hoạt động trên dữ liệu trên hàng hoặc cột của riêng nó. Hồ hết thời kì, đây chuẩn xác là những gì bạn muốn. Ví dụ: nếu bạn mang công thức =A1*2 trong ô B1 và ​​bạn sao chép công thức này vào ô B3, công thức sẽ thay đổi thành =A3*2.

Nhưng điều gì sẽ xảy ra nếu bạn muốn Excel sao chép chuẩn xác công thức mà ko thay đổi tham chiếu ô trong quá trình thực hiện? Tùy thuộc vào nhiệm vụ cụ thể của bạn, hãy tậu một trong những giải pháp sau.

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

Nếu bạn chỉ cần sao chép hoặc di chuyển một công thức, việc tạo một bản sao chuẩn xác rất dễ dàng.

• Tìm ô mang công thức bạn muốn sao chép.
• Tìm công thức trong thanh công thức bằng chuột và nhấn Ctrl + C để sao chép nó.
• Nhấn ESC để thoát khỏi thanh công thức
• Ctrl+V tới ô đích bạn cần di chuyển

Nếu bạn muốn di chuyển công thức bằng cách cắt luôn nó, hãy nhấn Ctrl + X để cắt nó.

• Giống như những bước trên nhưng lần này mình nhấn Ctrl+X
• Ctrl+V tới ô đích bạn cần di chuyển

Sao chép một loạt công thức mà ko thay đổi tham chiếu ô

Để di chuyển hoặc sao chép một loạt công thức Excel để ko mang tham chiếu ô nào bị thay đổi, hãy sử dụng một trong những phương pháp sau.

Phương pháp 1. Sử dụng tham chiếu ô tuyệt đối hoặc hỗn hợp

Nếu bạn cần tạo một bản sao chuẩn xác của những công thức mang tham chiếu ô tương đối (như A1), cách tốt nhất là thay đổi chúng thành tham chiếu tuyệt đối ($A$1) để sửa tham chiếu vào một ô nhất định, để nó vẫn tĩnh bất kể nơi công thức di chuyển. Trong những trường hợp khác, bạn mang thể cần sử dụng tham chiếu ô hỗn hợp ($A1 hoặc A$1) để khóa một cột hoặc hàng. Được rồi, hãy xem xét ví dụ sau.

Giả sử, bạn mang một bảng tính giá trái cây bằng VND dựa trên giá USD trong cột B và tỷ giá hối đoái trong ô C2:

Như bạn mang thể thấy trong ảnh chụp màn hình ở trên, công thức bao gồm một tham chiếu ô tuyệt đối ($C$2) để một mực tỷ giá hối đoái cho ô C2 và một tham chiếu ô tương đối cho ô B5 vì bạn muốn tham chiếu này điều chỉnh cho mỗi hàng. Và cách tiếp cận này hoạt động tốt miễn sao những công thức vẫn còn trong cột C.

Nhưng hãy xem điều gì sẽ xảy ra nếu bạn cần, chẳng hạn, để di chuyển giá VND từ cột C sang cột F. Nếu bạn sao chép công thức theo cách thông thường bằng cách sao chép / dán những ô, công thức từ ô C5 (= B5 * $C$2) sẽ thay đổi thành = D5 * $C$2 lúc được dán vào ô F5, làm cho những phép tính của bạn đều sai!

Để khắc phục điều này, chỉ cần thay đổi một tham chiếu tương đối (B5) thành một tham chiếu hỗn hợp $B5 (cột tuyệt đối và hàng tương đối). Bằng cách đặt ký hiệu đô la ($) trước ký tự cột, bạn tham chiếu vào cột B, bất kể công thức di chuyển tới đâu.

Và hiện tại, nếu bạn sao chép hoặc di chuyển công thức từ cột C sang cột F hoặc bất kỳ cột nào khác, thì tham chiếu cột sẽ ko thay đổi vì bạn đã khóa nó bằng ký hiệu đô la ($B5).

Tuy nhiên, nếu bạn đã mang rất nhiều công thức mang tham chiếu ô tương đối trong trang tính Excel của mình và bạn cần nhanh chóng tạo một bản sao chuẩn xác của những công thức đó nhưng bạn ko cảm thấy mình mang thể tải đúng tham chiếu, hãy thực hiện một trong những phương pháp sau mang thể là một giải pháp.

Phương pháp 2. Sao chép công thức Excel mà ko thay đổi tham chiếu qua Notepad

1. Vào chế độ xem công thức bằng cách nhấn phím tắt Ctrl + `

2. Tìm tất cả những ô mang công thức bạn muốn sao chép hoặc di chuyển.

3. Nhấn Ctrl + C để sao chép những công thức, hoặc Ctrl + X để cắt chúng.

4. Mở Notepad hoặc bất kỳ trình soạn thảo văn bản nào khác và nhấn Ctrl + V để dán những công thức vào đó. Sau đó nhấn Ctrl + A để tậu tất cả những công thức và Ctrl + C để sao chép chúng dưới dạng văn bản.

5. Trong trang tính Excel của bạn, hãy tậu ô phía trên bên trái nơi bạn muốn dán những công thức và nhấn Ctrl + V.

Ghi chú:

• Bạn chỉ mang thể dán những công thức trong cùng một trang tính nơi chứa những công thức ban sơ, trừ lúc những tham chiếu bao gồm tên trang tính, nếu ko, những công thức sẽ bị hỏng.
• Trang tính phải ở chế độ xem công thức . Để xác minh điều này, đi tới công thức Formulation tab > Formulation Auditing và kiểm tra xem Present Formulation được bật.
• Sau lúc dán những công thức, nhấn Ctrl + ` để tắt chế độ xem công thức.

Phương pháp 3. Sao chép chuẩn xác những công thức bằng cách sử dụng Tìm và Thay thế, (Discover & Exchange) của Excel

Để sao chép một loạt công thức Excel mà ko thay đổi tham chiếu ô của chúng, bạn mang thể sử dụng tính năng Tìm và Thay thế trong Excel theo cách sau.

1. Tìm những ô mang công thức bạn muốn sao chép.
2. Trên tab Trang đầu , hãy chuyển tới nhóm Discover & Choose > Exchange…. Hoặc, chỉ cần nhấn Ctrl + H, là phím tắt để khởi chạy hộp thoại Tìm & Thay thế (Discover & Exchange) trong Excel.
3. Trong cửa sổ hộp thoại Discover & Exchange nhập dấu bằng (=) vào hộp Discover what. Trong hộp Exchange with nhập một số ký hiệu hoặc một chuỗi ký tự ko được sử dụng trong bất kỳ công thức nào của bạn, như ‘ , # hoặc .

Mục tiêu của bước này là biến công thức thành chuỗi văn bản, điều này sẽ ngăn Excel thay đổi tham chiếu ô trong quá trình sao chép.

Ghi chú. Ko sử dụng dấu hoa thị (*) hoặc dấu chấm hỏi (?) Để thay thế, vì đây là những ký tự đại diện trong Excel và việc sử dụng chúng sẽ làm cho những bước sau khó khăn hơn.

• Nhấp vào nút Exchange All và đóng hộp thoại Discover & Exchange . Tất cả những công thức trong phạm vi đã tậu sẽ chuyển thành chuỗi văn bản:

Hiện nay, bạn mang thể tậu bất kỳ ô nào, nhấn Ctrl + C để sao chép chúng, hãy tậu ô trên cùng trong trang tính ngày nay nơi bạn muốn dán những công thức và nhấn Ctrl + V. Vì Excel ko diễn giải những công thức ko mang dấu bằng dưới dạng công thức, chúng sẽ được sao chép chuẩn xác mà ko thay đổi tham chiếu.

Sử dụng Discover & Exchange một lần nữa để đảo ngược thay đổi. Tìm cả hai vùng, với những công thức gốc và những công thức đã sao chép (để tậu những vùng ko ngay lập tức kề, hãy nhấn và giữ Ctrl). Nhấn Ctrl + H để mở hộp thoại Discover & Exchange. Lần này, hãy nhập dấu gạch chéo ngược () (hoặc bất kỳ ký tự nào khác mà bạn đã sử dụng cho lần thay thế trước nhất) vào hộp Discover what và dấu bằng (=) trong hộp Exchange with và nhấp vào nút Exchange All.

Đây là tất cả những gì tôi phải nói về việc sao chép công thức trong Excel. Nếu bạn biết một số phương pháp khác để nhanh chóng di chuyển hoặc sao chép công thức trong trang tính Excel, vui lòng san sẻ thêm. Tôi cảm ơn bạn đã đọc và hy vọng sẽ gặp bạn trên weblog của chúng tôi vào tuần tới!