Tìm hiểu cách copy 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,… Show
Quảng cáo  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ì gõ lặp đi lặ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. 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.
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.
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 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. Quảng cáo Sau đó, chúng tôi đi đến ô 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àngTrong 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),… 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ạngKhi bạn sao chép một 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,…(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 trình đơn 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 như định dạng phần trăm, dấu thập phân,… 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ữ,… Sao chép công thức vào các ô không liền kề hoặc phạm vi không liền kềNếu bạn muốn sao chép một công thức vào các ô không liền kề hoặc các phạm vi không liền kề, bạn có thể thực hiện điều đó với sự trợ giúp của phím Ctrl 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ữ phím Ctrl. Sau đó, nhấn Ctrl + Vđể dán công thức và nhấn Enterđể hoàn thành. Sao chép công thức mà không thay đổi tham chiếu ô trong ExcelKhi 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 chữ cái cột và số hàng của một ô (Ví dụ: $ A $ 1), nó sẽ biến ô thành một ô 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 hoặc á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 Escphím để rời khỏi thanh công thức. Ngoài ra, hãy chọn ô có công thức và nhấn F2phím 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ợpNế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ụ: việc 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 ký hiệ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 NotepadBạ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 + `phím tắt, chế độ này sẽ 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 ESCphí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ổ tay. Tiếp theo, chọn công thức và sao chép ( Ctrl + C) nó từ notepad, và dán nó ( Ctrl + V) vào ô 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 ExcelNế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 tuỳ 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 một ký hiệu hoặc ký tự chưa phải là một phần trong công thức của bạn, như #. 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à đã 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:
Quảng cáo |
