Tìm hiểu cách sao chép công thức sang nhiều ô, xuống một cột, sang các ô không liền kề, sao chép công thức với tham chiếu ô tuyệt đối hoặc hỗn hợp, v.v.
Sao chép công thức là một trong những tác vụ phổ biến và dễ dàng nhất mà bạn thực hiện trong một bảng tính điển hình chủ yếu dựa vào công thức. Thay vì nhập đi nhập lại cùng một công thức trong Excel, bạn có thể dễ dàng sao chép và dán một công thức từ một ô sang nhiều ô.
Sau khi viết công thức trong Excel, bạn có thể sử dụng lệnh sao chép và dán vào nhiều ô, nhiều ô không liền kề hoặc toàn bộ cột. Nếu bạn không làm đúng, bạn sẽ gặp phải lỗi # REF và / DIV0 khủng khiếp đó. Trong bài viết này, chúng tôi sẽ chỉ cho bạn các phương pháp khác nhau mà bạn có thể sử dụng để sao chép công thức trong Excel.
Cách sao chép và dán công thức trong Excel
Microsoft Excel cung cấp nhiều cách khác nhau để sao chép công thức với tham chiếu ô tương đối, tham chiếu ô tuyệt đối hoặc tham chiếu hỗn hợp.
- Sao chép công thức từ ô này sang ô khác
- Sao chép công thức một ô sang nhiều ô
- Sao chép công thức vào toàn bộ cột
- Sao chép công thức mà không cần định dạng
- Sao chép công thức vào các ô không liền kề
- Sao chép công thức mà không thay đổi tham chiếu ô
Cách sao chép công thức từ ô này sang ô khác trong Excel
Đôi khi bạn có thể muốn sao chép công thức từ ô này sang ô khác trong excel để tránh nhập lại toàn bộ công thức và tiết kiệm thời gian khi thực hiện việc đó.
Giả sử chúng ta có bảng này:
Có một số phương pháp để sao chép công thức từ ô này sang ô khác.
Đầu tiên, chọn ô có công thức và nhấp chuột phải, và trong menu ngữ cảnh, chọn ‘Sao chép’ để sao chép công thức. Hoặc bạn có thể sử dụng tùy chọn ‘Sao chép’ trong phần ‘Bảng tạm’ của tab ‘Trang chủ’.
Nhưng bạn cũng có thể sao chép công thức bằng cách chỉ cần nhấn phím tắt Ctrl + C. Đây là một phương pháp hiệu quả và tiết kiệm thời gian hơn.
Sau đó, chúng tôi đi đến ô mà chúng tôi muốn dán nó, nhấn phím tắt Ctrl + V để dán công thức. Hoặc nhấp chuột phải vào ô bạn muốn dán và nhấp vào các tùy chọn trong ‘Tùy chọn dán’: tùy chọn ‘Dán (P)’ đơn giản hoặc dán dưới dạng tùy chọn ‘Công thức (F)’.
Ngoài ra, bạn cũng có thể nhấp vào ‘Dán Đặc biệt’ bên dưới sáu biểu tượng dán để mở hộp thoại ‘Dán Đặc biệt’. Tại đây, bạn có một số tùy chọn bao gồm sáu tùy chọn dán từ menu ngữ cảnh. Chọn ‘Tất cả’ hoặc ‘Công thức’ trong phần Dán và nhấp vào ‘OK’.
Bây giờ ô có công thức được dán sẽ có cùng công thức (như công thức trong ô đã sao chép) nhưng với các tham chiếu ô khác nhau. Địa chỉ ô được excel tự điều chỉnh để khớp với số hàng của ô đã dán.
Sao chép công thức từ một ô sang nhiều ô
Thao tác dán tương tự hoạt động giống nhau nếu chúng ta chọn nhiều ô hoặc một dải ô.
Chọn ô có công thức và nhấn Ctrl + C để sao chép công thức. Sau đó, chọn tất cả các ô mà bạn muốn dán công thức và nhấn Ctrl + V để dán công thức hoặc sử dụng một trong các phương pháp dán ở trên để dán công thức (giống như chúng tôi đã làm đối với ô đơn).
Sao chép Công thức vào Toàn bộ Cột hoặc Hàng
Trong Excel, bạn có thể nhanh chóng sao chép một công thức vào toàn bộ một cột hoặc một hàng.
Để sao chép công thức vào một cột hoặc một hàng, trước tiên, hãy nhập công thức vào một ô. Sau đó, chọn ô công thức (D1) và di con trỏ qua một hình vuông nhỏ màu xanh lục ở góc dưới bên phải của ô. Khi bạn di chuột, con trỏ sẽ thay đổi thành dấu cộng màu đen (+), được gọi là Fill Handle. Nhấp và giữ chốt điền đó, và kéo nó theo bất kỳ hướng nào bạn muốn (cột hoặc hàng) qua các ô để sao chép công thức.
Khi bạn sao chép công thức vào một phạm vi ô, các tham chiếu ô của công thức sẽ tự động điều chỉnh dựa trên vị trí tương đối của các hàng và cột và công thức sẽ thực hiện các phép tính dựa trên các giá trị trong các tham chiếu ô đó (Xem bên dưới).
Trong ví dụ trên, khi công thức trong D1 (= A1 * B1) / 2) được sao chép vào ô D2, tham chiếu tương đối sẽ thay đổi căn cứ vào vị trí của nó (= A2 * B2) / 2), v.v.
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 trái, bên phải hoặc lên trên.
Một cách khác để sao chép công thức vào toàn bộ cột là bấm đúp vào chốt điền thay vì kéo nó. Khi bạn bấm đúp vào ô điều khiển điền, nó ngay lập tức áp dụng công thức cho đến khi có bất kỳ dữ liệu nào cho ô liền kề.
Sao chép công thức vào một phạm vi mà không cần sao chép định dạng
Khi bạn sao chép công thức vào một dải ô có chốt điền, nó cũng sẽ sao chép định dạng của ô nguồn, chẳng hạn như màu phông chữ hoặc màu nền, đơn vị tiền tệ, tỷ lệ phần trăm, thời gian, v.v. (như được hiển thị bên dưới).
Để ngăn việc sao chép định dạng ô, hãy kéo chốt điền và nhấp vào 'Tùy chọn tự động điền' ở góc dưới bên phải của ô cuối cùng. Sau đó, trong menu thả xuống, hãy chọn ‘Điền mà không cần định dạng’.
Kết quả:
Sao chép một công thức Excel chỉ với định dạng số
Nếu bạn muốn sao chép công thức chỉ với công thức và định dạng chẳng hạn như định dạng phần trăm, dấu thập phân, v.v.
Sao chép công thức và chọn tất cả các ô mà bạn muốn sao chép công thức. Trên tab 'Trang chủ', nhấp vào mũi tên bên dưới nút 'Dán' trên ruy-băng. Sau đó, nhấp vào biểu tượng ‘Công thức & Định dạng Số’ (biểu tượng có% fx) từ menu thả xuống để chỉ dán công thức và định dạng số.
Tùy chọn này chỉ sao chép công thức và định dạng số nhưng bỏ qua tất cả các định dạng ô khác như màu nền, màu phông chữ, v.v.
Sao chép công thức vào các ô không liền kề / không liền kề
Nếu bạn muốn sao chép công thức vào các ô không liền kề hoặc các dải ô không liền kề, bạn có thể thực hiện điều đó với sự trợ giúp của Điều khiển Chìa khóa.
Chọn ô có công thức và nhấn Ctrl + C để sao chép nó. Sau đó, chọn các ô / dải ô không liền kề trong khi nhấn và giữ Điều khiển Chìa khóa. Sau đó nhấn Ctrl + V để dán công thức và nhấn đi vào hoàn thành.
Sao chép công thức mà không thay đổi tham chiếu ô trong Excel
Khi một công thức được sao chép sang một ô khác, Excel sẽ tự động thay đổi các tham chiếu ô để khớp với vị trí mới của nó. Các tham chiếu ô này sử dụng vị trí tương đối của địa chỉ ô, do đó chúng được gọi là tham chiếu ô tương đối (không có $). Ví dụ: nếu bạn có công thức ‘= A1 * B1’ trong ô C1 và bạn sao chép công thức này vào ô C2, công thức sẽ thay đổi thành ‘= A2 * B2’. Tất cả các phương pháp chúng tôi đã thảo luận ở trên sử dụng tài liệu tham khảo tương đối.
Khi bạn sao chép một công thức với các tham chiếu ô tương đối, nó sẽ tự động thay đổi các tham chiếu để công thức tham chiếu đến các hàng và cột tương ứng. Nếu bạn sử dụng tham chiếu tuyệt đối trong một công thức, thì cùng một công thức sẽ được sao chép mà không thay đổi tham chiếu ô.
Khi bạn đặt một ký hiệu đô la ($) trước ký tự cột và số hàng của ô (Ví dụ: $ A $ 1), nó sẽ biến ô thành ô tuyệt đối. Bây giờ, bất kể bạn sao chép công thức có chứa tham chiếu ô tuyệt đối ở đâu, công thức sẽ không bao giờ xảy ra. Nhưng nếu bạn có tham chiếu ô tương đối hoặc hỗn hợp trong một công thức, hãy sử dụng bất kỳ phương pháp nào sau đây để sao chép mà không thay đổi tham chiếu ô.
Sao chép công thức với tham chiếu ô tuyệt đối bằng phương pháp sao chép-dán
Đôi khi, bạn có thể cần sao chép / áp dụng công thức chính xác xuống cột mà không thay đổi tham chiếu ô. Nếu bạn muốn sao chép hoặc di chuyển một công thức chính xác với tham chiếu tuyệt đối, hãy làm như sau:
Đầu tiên, chọn ô có công thức bạn muốn sao chép. Sau đó, nhấp vào thanh công thức, chọn 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 để cắt nó. Tiếp theo, nhấn vào Thoát ra để rời khỏi thanh công thức.
Ngoài ra, hãy chọn ô có công thức và nhấn F2 hoặc bấm đúp vào ô. Thao tác này sẽ đưa ô đã chọn vào chế độ chỉnh sửa. Sau đó, chọn công thức trong ô và nhấn Ctrl + C để sao chép công thức trong ô dưới dạng văn bản.
Sau đó, chọn ô đích và nhấn Ctrl + V để dán công thức.
Bây giờ công thức chính xác được sao chép vào ô đích mà không có bất kỳ thay đổi tham chiếu ô nào.
Sao chép công thức với tham chiếu ô tuyệt đối hoặc hỗn hợp
Nếu bạn muốn di chuyển hoặc sao chép công thức chính xác mà không thay đổi tham chiếu ô, bạn nên thay đổi tham chiếu tương đối của ô thành tham chiếu tuyệt đối. Ví dụ: thêm dấu ($) vào tham chiếu ô tương đối (B1) làm cho nó trở thành một tham chiếu tuyệt đối ($ B $ 1), vì vậy nó vẫn ở trạng thái tĩnh cho dù công thức được sao chép hoặc di chuyển ở đâu.
Nhưng đôi khi, bạn có thể cần sử dụng tham chiếu ô hỗn hợp ($ B1 hoặc B $ 1) bằng cách thêm dấu đô la ($) vào trước ký tự cột hoặc số hàng để khóa một hàng hoặc một cột tại chỗ.
Hãy để chúng tôi giải thích bằng một ví dụ. Giả sử bạn có bảng này tính số tiền tiết kiệm hàng tháng bằng cách trừ tiền thuê nhà (B9) vào thu nhập (trong cột B) hàng tháng.
Trong ví dụ bên dưới, công thức sử dụng tham chiếu ô tuyệt đối ($ B $ 9) để khóa số tiền thuê cho ô B9 và tham chiếu ô tương đối cho ô B2 vì nó cần được điều chỉnh cho từng hàng để phù hợp với mỗi tháng. B9 được làm tham chiếu ô tuyệt đối ($ B $ 9) vì bạn muốn trừ cùng một khoản tiền thuê vào thu nhập của mỗi tháng.
Giả sử bạn muốn di chuyển số dư từ cột C sang cột E. Nếu bạn sao chép công thức (bằng phương pháp sao chép / dán thông thường) từ ô C2 (= B2- $ B $ 9) sẽ thay đổi thành = D2- $ B $ 9 khi được dán trong ô E2, làm cho tất cả các phép tính của bạn sai!
Trong trường hợp đó, hãy thay đổi tham chiếu ô tương đối (B2) thành tham chiếu ô hỗn hợp ($ B2) bằng cách thêm dấu ‘$’ vào trước ký tự cột của công thức được nhập vào ô C2.
Và bây giờ, nếu bạn sao chép hoặc di chuyển công thức từ ô C2 sang E2 hoặc bất kỳ ô nào khác và áp dụng công thức xuống cột, thì tham chiếu cột sẽ vẫn giữ nguyên trong khi số hàng sẽ được điều chỉnh cho mỗi ô.
Sao chép Dán công thức Excel mà không thay đổi tham chiếu bằng Notepad
Bạn có thể xem mọi công thức trong bảng tính Excel của mình bằng cách sử dụng tùy chọn Hiển thị công thức. Để làm điều đó, hãy chuyển đến tab 'Công thức' và chọn 'Hiển thị Công thức'.
Ngoài ra, bạn có thể vào chế độ xem công thức bằng cách nhấn Ctrl + ` lối tắt, hiển thị mọi công thức trong trang tính của bạn. Bạn có thể tìm thấy phím trọng âm (`) ở góc trên cùng bên trái của bàn phím trên hàng có các phím số (bên dưới thoát phím và trước phím số 1).
Chọn tất cả các ô có công thức bạn muốn sao chép và nhấn Ctrl + C để sao chép chúng, hoặc Ctrl + X để cắt chúng. Sau đó mở Notepad và nhấn Ctrl + V để dán các công thức vào sổ ghi chú.
Tiếp theo, chọn công thức và sao chép (Ctrl + C) nó từ notepad, và dán nó (Ctrl + V) trong ô mà bạn muốn sao chép công thức chính xác. Bạn có thể sao chép và dán từng cái một hoặc tất cả cùng một lúc.
Sau khi dán công thức, hãy tắt chế độ xem công thức bằng cách nhấn Ctrl + ` hoặc lại vào ‘Công thức’ -> ‘Hiển thị công thức’.
Sao chép công thức chính xác bằng tính năng tìm và thay thế của Excel
Nếu bạn muốn sao chép một loạt các công thức Chính xác, bạn cũng có thể sử dụng công cụ Tìm và Thay thế của Excel để làm như vậy.
Chọn tất cả các ô có công thức bạn muốn sao chép. Sau đó, chuyển đến tab 'Trang chủ', nhấp vào 'Tìm & Chọn' trên nhóm Chỉnh sửa và chọn tùy chọn 'Thay thế', Hoặc chỉ cần nhấn Ctrl + H để mở hộp thoại Tìm & Thay thế.
Trong hộp thoại Tìm và Thay thế, nhập dấu bằng (=) vào trường 'Tìm gì'. Trong trường 'Thay thế bằng', hãy nhập ký hiệu hoặc ký tự chưa phải là một phần của công thức của bạn, như # hoặc, v.v. Sau đó, nhấp vào nút 'Thay thế tất cả'.
Bạn sẽ nhận được một hộp thông báo nhắc rằng "Chúng tôi đã thực hiện 6 ô thay thế" (vì chúng tôi đã chọn 6 ô có công thức). Sau đó nhấp vào ‘OK’ và ‘Close’ để đóng cả hai hộp thoại. Việc làm này sẽ thay thế tất cả các dấu bằng (=) bằng dấu thăng (#) và biến các công thức thành chuỗi văn bản. Giờ đây, các tham chiếu ô của công thức sẽ không bị thay đổi khi được sao chép.
Bây giờ, bạn có thể chọn các ô này, nhấn Ctrl + C để sao chép chúng và dán chúng vào các ô đích với Ctrl + V.
Cuối cùng, bạn cần thay đổi dấu (#) thành dấu (=). Để làm điều đó, hãy chọn cả hai phạm vi (phạm vi gốc và phạm vi đã sao chép) và nhấn Ctrl + H để mở hộp thoại Tìm & Thay thế. Lần này, hãy nhập dấu thăng (#) vào trường "Tìm gì" và bằng dấu (=) trong trường "Thay thế bằng" và nhấp vào nút "Thay thế tất cả". Nhấp vào 'Đóng' để đóng hộp thoại.
Bây giờ, các chuỗi văn bản được chuyển đổi trở lại các công thức và bạn sẽ nhận được kết quả sau:
Làm xong!