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

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.
• 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ự như, bạn hoàn toà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 hàng loạt 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 tài 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à hiệu 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 đúng chuẩn công thức mà không đổi khác tham chiếu ô trong quy trình triển khai ? Tùy thuộc vào trách nhiệm đơn cử 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 chuyển dời một công thức, việc tạo một bản sao đúng chuẩn rất thuận tiện .

• 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 ô

Để vận động và 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ị đổi khác, hãy sử dụng một trong các giải 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 gọn tạo một bản sao đúng mực của những công thức đó nhưng bạn không cảm thấy mình hoàn toàn có thể tải đúng tham chiếu, hãy triển khai một trong các giải pháp sau hoàn toàn 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 biến hóa tham chiếu ô của chúng, bạn hoàn toà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 biến hóa tham chiếu ô trong quy 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ổng thể 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 1 số ít chiêu thức khác để nhanh gọn vận động và di chuyển hoặc sao chép công thức trong trang tính Excel, vui vẻ san sẻ thêm. Tôi cảm ơn bạn đã đọc và kỳ vọng sẽ gặp bạn trên blog của chúng tôi vào tuần tới !

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