Copy công thức trong Excel hàng dọc

Copy công thức trong Excel nhanh chóng trong giây lát là mong muốn của tất cả dân văn phòng. Để có thể thực hiện một phép tính trong Excel, chúng ta sẽ cần đến các hàm công thức khác nhau. Nếu bảng tính có nhiều số liệu với số hàng số cột khác nhau thì việc tính toán khá khó khăn. Vì thế cần các cách sao chép công thức một cách một cách nhanh chóng. Câu trả lời sẽ được bật mí trong bài viết sau!

Thế nào là copy công thức trong Excel?

Sao chép công thức trong Excel là một trong những công việc đơn giản. Thông thường chỉ thực hiện bằng một cái kích chuột. Ví dụ như copy một dãy các công thức mà không làm ảnh hưởng đến các ô tham chiếu. Hoặc nhập những công thức vào những ô không liền kề.

Copy công thức trong Excek bằng cách kéo cột dọc

Các bước thực hiện

• Điền công thức vào ô cần tính trên cùng của ô.
• Chọn ô có công thức được nhập phía trên. Lúc này ô được chọn sẽ được chuyển thành màu đen. Kéo chuột vào cuối góc ô vuông chứa công thức. Sau đó, Fill handle sẽ đổi thành dấu cộng màu đen đậm.
• Giữ và kéo xuống các ô chứa các cột mà bạn muốn sao chép công thức.

Copy công thức trong Excek bằng cách kéo cột dọc

Với công thức tương tự như vậy, bạn có thể copy công thức trong Excel có chứa các ô liền kề bên phải, bên trái hoặc lên trên.

Lưu ý

Nếu công thức có một tham chiếu ô tương đối được ký hiệu $. Nó sẽ tự động thay đổi được các vị trí tương đối theo các hàng các cột. Do đó nên bạn cần lưu ý khi sử dụng tham chiếu này nhé. Nếu cần thiết bạn cần chuyển đổi ô tham chiếu thành ô tuyệt đối chỉ cần dùng phím F4 để thay đổi. Trong ví dụ trên chắc chắn công thức sao chép liệu có chính xác. Bạn hãy chọn một cột C, ví dụ như C4 và xem công thức trên thanh công thức:

Copy công thức trong Excel mà không cần định dạng

Như bạn đã biết khi sao chép một công thức bằng cách kéo các cột dọc. Bạn có thể dùng cách điền các công thức định dạng. Mục đích nhằm công thức có thể giữ được định dạng ô. Công cụ copy-paste là một cách thức linh hoạt và có thể paste được công thức như ý muốn.

• Chọn ô có chứa công thức đã nhập
• Sao chép ô bằng việc nhấn Ctrl + C hoặc click chuột phải vào ô. Chọn tính năng copy. Hay click vào ô Copy trên thanh công cụ Home-> Clipboard.
• Chọn tất cả các ô bạn muốn sao chép công thức mà bạn cần tính.
• Click chuột phải vào ô và chọn Formulas ở mục Paste Options.

Copy công thức trong Excel mà không cần định dạng

Bạn muốn có nhiều lựa chọn hơn bằng cách click vào hình mũi tên trên ô Paste góc trái màn hình trên bảng tính. Hoặc bạn có thể chọn Formulas & Number Formatting để paste duy nhất cho công thức và định dạng số phần trăm, ký hiệu.

Mẹo: Nếu bạn không chắc cách paste nào phù hợp bạn có thể di chuột để đến với những biểu tượng khác nhau mà không click vào. Excel sẽ hiện phần xem thử cho bạn có thể thoải mái lựa chọn.

Copy công thức trong Excel cho toàn bộ cột

Như bạn đã biết, fill handle giúp cho việc sao chép công thức trong Excel đơn giản. Nhưng nếu muốn sao chép công thức cho một trang tính có hơn 1000 dòng thì sẽ như thế nào. Vì vậy việc kéo thả công thức cho hàng trăm cột giúp cho việc tính toán trở nên dễ dàng hơn. Đây cũng là phương án giải quyết nhanh nhất trong trường hợp này.

Các bước thực hiện

Để điền được công thức cho toàn bộ cột bạn cần click chuột đôi vào biểu tượng dấu cộng thay vì kéo thả. Và thực hiện các bước sau đây:

• Điền công thức vào cột đầu tiên cần tính
• Di con trỏ đến bên góc phải dưới cùng để chọn và sẽ xuất hiện một dấu cộng. Bạn hãy click chuột đôi vào dấu cộng đó.

Copy công thức trong Excel cho toàn bộ cột

Lưu ý khi copy công thức Excel trong toàn bộ cột

Sao chép công thức bằng cách này sẽ sao chép được cho tất cả các cột có trên dữ liệu bên trai. Nghĩa là, khi có cột dữ liệu bên trái thì Excel sẽ tiếp tục copy công thức. Khi có bất kì một hàng trống thì việc điều tự động sẽ được dừng lại. Vì vậy, nếu Excel có bất kỳ hàng trống nào thì bạn sẽ phải lặp lại quy trình trên để tiếp tục copy công thức.

Cách tắt chế độ copy công thức Excel đến các ô khác

Sao chép công thức ở ô dưới

+ Ctrl +D: Sao chép công thức từ một ô trong trang tính đến ô dưới liền kề. Sau đó bạn hãy điều chỉnh khi chứa tham chiếu.

+ Ví dụ bạn có thể sử dụng công thức cho ô A1 và muốn sao chép cho ô dưới là A2 thì hãy nhấn Ctrl + D

Sao chép công thức sang bên phải

+ Ctrl + R: Sao chép công thức sang ô bên phải liền kề và được điều chỉnh khi chứa tham chiếu ô.

+ Ví dụ: Nếu muốn có công thức ở ô A2, bạn cần sao chép sao ô B2 thì chỉ cần nhấn tổ hợp phím Ctrl + R

Cách tắt chế độ copy công thức Excel đến các ô khác

Mẹo: Hai cách trên đều dùng để copy công thức cho nhiều ô khác nhau. Lúc này, bạn chỉ cần chọn cả ô chứa công thức và ô nhận công thức. Sau đó nhấn phím tắt trên Excel. Ví dụ như muốn sao chép một công thức từ A1 đến A9 bạn cần chọn ô A1:A10 và nhấn Ctrl + D.

Sao chép công thức ô dưới chính xác

+ Ctrl + ‘: Sao chép công thức từ ô ở trên xuống ô dưới một cách chính xác để hiển thị chế độ chỉnh sửa.

Đây là một cách cũng khá nhanh và đơn giản mà không thay đổi ô tham chiếu.

+ Ví dụ: Nếu muốn sao chép công thức từ ô A1 xuống ô A2 mà không tham chiếu nào thay đổi chọn A2 và nhấn Ctrl + ‘.

Lưu ý: Bạn cần phân biệt được Ctrl + ‘ là sao chép chính xác công thức. Nhấn Ctrl + ’ là hiển thị công thức trong Excel.

>>> XEM THÊM: 

Trên đây là các cách cơ bản trong việc copy công thức trong Excel. Hy vọng bài viết của TEKY sẽ hữu ích với bạn. Hãy lưu lại ngay thông tin trên để sử dụng khi cần thiết nhé!

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.
• Chọn ô có 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 . Khi 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 các ô bạn muốn sao chép công thức.

Theo cách tương tự, bạn có thể kéo công thức vào các ô liền 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 chính xác, hãy chọn 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 có 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 đến hàng 4, chính xác như nó phải là:

Cách sao chép công thức mà không 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 không 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ầu hết các trường hợp, điều này hoạt động tốt, nhưng đôi khi nó có thể làm xáo trộn các định dạng hiện có trong các ô mà công thức đang được sao chép vào. Một ví dụ phổ biến 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 có, hãy kéo chốt điền như được minh họa ở trên, thả nó ra, bấm vào menu thả xuống Tùy chọn Tự động điền và chọn  Fill Without 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 không phải là một ý tưởng hay. May 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 đúp vào dấu cộng để điền toàn bộ cột

Để áp dụng công thức cho toàn bộ cột, hãy bấm đúp vào dấu cộng thay vì kéo nó. Đối với những người đã bỏ qua phần đầu tiên của hướng dẫn này, các bước chi tiết 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 ô có công thức, đợi cho đến khi nó chuyển thành dấu cộng, rồi bấm đúp vào dấu cộng.

• Lưu ý: Bấm đúp vào dấu cộng sẽ sao chép công thức xuống dưới khi có bất kỳ dữ liệu nào ở bên trái. Nói cách khác, miễn là bạn có 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 khi một hàng trống xuất hiện, tự động điền sẽ dừng lại. Vì vậy, nếu trang tính của bạn có 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 các ô / dải ô không liền kề

Không 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 các ô không liền 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 ô có công thức để chọn nó.
• Nhấn Ctrl + C để sao chép công thức.
• Chọn một ô hoặc một dải ô mà bạn muốn dán công thức (để chọn các dải ô không liền 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 các công thức đã dán.

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

• Ghi chú: Các 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à không cần định dạng, hãy chọn tùy chọn Dán thích hợp hơn nhé. 

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

Các công thức Excel hiếm khi xuất hiện trong bảng tính một cách đơn độc. Trong hầu hết các 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 các ô 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 các tham chiếu ô tương đối (không có $), 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ầu hết thời gian, đây chính xác là những gì bạn muốn. Ví dụ: nếu bạn có 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 chính xác công thức mà không 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 chọn một trong các giải pháp sau.

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 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 chính xác rất dễ dàng.

• Chọn ô có công thức 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ó. 
• Nhấn ESC để thoát khỏi thanh công thức
• Ctrl+V đến ô đí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ư các bước trên nhưng lần này mình nhấn Ctrl+X
• Ctrl+V đến ô đích bạn cần di chuyển

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

Để di chuyển hoặc sao chép một loạt công thức Excel để không có tham chiếu ô nào bị thay đổi, hãy sử dụng một trong các 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 chính xác của các công thức có 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 các trường hợp khác, bạn có 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 có 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 có 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) để cố định 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 là các 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 các ô, công thức từ ô C5 (= B5 * $C$2) sẽ thay đổi thành = D5 * $C$2 khi được dán vào ô F5, làm cho các 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 đến đâu.

Và bây giờ, 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ẽ không thay đổi vì bạn đã khóa nó bằng ký hiệu đô la ($B5).

Tuy nhiên, nếu bạn đã có rất nhiều công thức có 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 chính xác của những công thức đó nhưng bạn không cảm thấy mình có thể tải đúng tham chiếu, hãy thực hiện một trong các phương pháp sau có thể là một giải pháp.

Phương pháp 2. Sao chép công thức Excel mà không 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. Chọn tất cả các ô có công thức bạn muốn sao chép hoặc di chuyển.    

3. Nhấn Ctrl + C để sao chép các 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 các công thức vào đó. Sau đó nhấn Ctrl + A để chọn tất cả các 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 chọn ô phía trên bên trái nơi bạn muốn dán các công thức và nhấn Ctrl + V.

Ghi chú:

• Bạn chỉ có thể dán các công thức trong cùng một trang tính nơi chứa các công thức ban đầu, trừ khi các tham chiếu bao gồm tên trang tính, nếu không, các 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 đến công thức Formulas tab > Formulas Auditing và kiểm tra xem Show Formulas được bật.
• Sau khi dán các công thức, nhấn Ctrl + ` để tắt chế độ xem công thức.

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

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

1. Chọn các ô có công thức bạn muốn sao chép.
2. Trên tab Trang đầu , hãy chuyển đến nhóm Find & Select > Replace.... 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ế (Find & Replace) trong Excel.
3. Trong cửa sổ hộp thoại Find & Replace nhập dấu bằng (=) vào hộp Find what. Trong hộp Replace with nhập một số ký hiệu hoặc một chuỗi ký tự không được sử dụng trong bất kỳ công thức nào của bạn, như ' , # hoặc \.

Mục đích 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ú. Không sử dụng dấu hoa thị (*) hoặc dấu chấm hỏi (?) Để thay thế, vì đây là các ký tự đại diện trong Excel và việc sử dụng chúng sẽ khiến các bước sau khó khăn hơn.

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

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

Sử dụng Find & Replace một lần nữa để đảo ngược thay đổi. Chọn cả hai vùng, với các công thức gốc và các công thức đã sao chép (để chọn các vùng không liền kề, hãy nhấn và giữ Ctrl). Nhấn Ctrl + H để mở hộp thoại Find & Replace. 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ế đầu tiên) vào hộp Find what và dấu bằng (=) trong hộp Replace with và nhấp vào nút Replace 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 chia sẻ thêm. Tôi cảm ơn bạn đã đọc và hy vọng sẽ gặp bạn trên blog của chúng tôi vào tuần tới!