Buzz
Excel provides features that make it easy to copy formulas across rows or columns, but the results may not always meet your expectations. When you encounter unexpected outcomes or errors like #REF and /DIV0, it can be frustrating. However, there's no need to worry—you don’t have to manually edit each cell in a 5,000-row spreadsheet. This article will share simple methods to help you copy formulas to other cells efficiently.
Steps
Use Find and Replace to copy formulas accurately
Mở bảng tính Excel. Chắc hẳn sẽ có lúc bạn phải xử lý bảng tính lớn với nhiều công thức, và muốn sao chép đầy đủ thông tin. Việc thay đổi mọi thông tin thành tham chiếu ô tuyệt đối sẽ có phần khó khăn, đặc biệt khi bạn muốn xem lại thông tin gốc ngay sau đó. Hãy áp dụng phương pháp này để nhanh chóng di chuyển công thức với tham chiếu ô tương đối đến nơi khác mà không cần thay đổi tham chiếu. Với bảng tính mẫu sau đây, chúng ta sẽ sao chép công thức từ cột C sang cột D mà không thay đổi yếu tố nào.
| Cột A | Cột B | Cột C | Cột D | |
|---|---|---|---|---|
| dòng 1 | 944 | Ếch | =A1/2 | |
| dòng 2 | 636 | Cóc | =A2/2 | |
| dòng 3 | 712 | Thằn lằn | =A3/2 | |
| dòng 4 | 690 | Rắn | =A4/2 |
- Nếu bạn muốn sao chép công thức trong một ô, hãy chuyển sang bước cuối cùng ("Thử các cách khác") của phần này.
Press Ctrl+H to open the Find window. This shortcut works on both Windows and macOS computers.
Tìm và thay dấu "=" thành ký tự khác. Nhập "=" vào trường "Find what" (Tìm gì), rồi nhập ký tự khác vào ô "Replace with" (Thay thế bằng). Nhấp vào Replace All (Thay thế toàn bộ) để chuyển toàn bộ công thức (thường bắt đầu bằng dấu bằng) vào phần chữ bắt đầu bằng ký tự khác. Luôn dùng ký tự mà bạn chưa sử dụng trong bảng tính. Ví dụ, thay bằng dấu # hay &, hoặc chuỗi ký tự dài hơn, chẳng hạn như ##&.
| Cột A | Cột B | Cột C | Cột D | |
|---|---|---|---|---|
| dòng 1 | 944 | Ếch | ##&A1/2 | |
| dòng 2 | 636 | Cóc | ##&A2/2 | |
| dòng 3 | 712 | Thằn lằn | ##&A3/2 | |
| dòng 4 | 690 | Rắn | ##&A4/2 |
- Không sử dụng ký tự * hay ? để tránh gặp khó khăn ở các bước sau.
Sao chép và dán ô. Tô sáng ô mà bạn muốn sao chép, và ấn Ctrl + C (trên Windows) hoặc Cmd + C (trên Mac) để sao chép. Tiếp theo, chọn ô mà bạn muốn dán thông tin, và ấn Ctrl + V (trên Windows) hoặc Cmd + V (trên Mac) để dán. Vì chúng không còn được xem là công thức, nên sẽ được sao chép chính xác.
| Cột A | Cột B | Cột C | Cột D | |
|---|---|---|---|---|
| dòng 1 | 944 | Ếch | ##&A1/2 | ##&A1/2 |
| dòng 2 | 636 | Cóc | ##&A2/2 | ##&A2/2 |
| dòng 3 | 712 | Thằn lằn | ##&A3/2 | ##&A3/2 |
| dòng 4 | 690 | Rắn | ##&A4/2 | ##&A4/2 |
Sử dụng Find & Replace (Tìm và Thay thế) một lần nữa để đảo ngược thay đổi. Bây giờ bạn đã có các công thức theo ý muốn, hãy dùng "Replace All" (Thay thế toàn bộ) một lần nữa để đảo ngược thay đổi. Trong bảng tính mẫu sau đây, chúng ta sẽ tìm chuỗi ký tự "##&" và thay bằng dấu "=" một lần nữa, để những ô đó trở thành công thức ban đầu. Bây giờ bạn có thể tiếp tục chỉnh sửa bảng tính như thường lệ:
| Cột A | Cột B | Cột C | Cột D | |
|---|---|---|---|---|
| dòng 1 | 944 | Ếch | =A1/2 | =A1/2 |
| dòng 2 | 636 | Cóc | =A2/2 | =A2/2 |
| dòng 3 | 712 | Thằn lằn | =A3/2 | =A3/2 |
| dòng 4 | 690 | Rắn | =A4/2 | =A4/2 |
Try another method. If the above approach doesn’t work, or if you’re concerned about accidentally altering other cell contents using the "Replace all" feature, you can explore these alternatives:
- To copy a formula from a cell without altering its references, select the cell, then copy the formula displayed in the formula bar near the top of the window (not directly from the cell). Press Esc to close the formula bar, and paste the formula wherever needed.
- Press Ctrl and ` (also the ~ key) to switch the spreadsheet to formula view. the formulas and paste them into a text editor like Notepad or TextEdit. them again, then paste them into the desired location in the spreadsheet. Finally, press Ctrl and ` once more to return to the normal view.
Fill formulas across columns or rows
Nhập công thức vào một ô trống. Chương trình Excel giúp người dùng dễ dàng nhân bản công thức theo cột hoặc dòng bằng cách "điền" thông tin vào các ô. Tương tự như với mọi công thức khác, bạn sẽ nhập dấu = trước, rồi nhập hàm hoặc phép tính mà bạn muốn. Chúng ta sẽ dùng một bảng tính mẫu đơn giản, và cộng số ở cột A với cột B. Ấn Enter hoặc Return để xem kết quả.
| Cột A | Cột B | Cột C | |
|---|---|---|---|
| dòng 1 | 10 | 9 | 19 |
| dòng 2 | 20 | 8 | |
| dòng 3 | 30 | 7 | |
| dòng 4 | 40 | 6 |
Click below the bottom-right corner of the cell containing the formula you want to copy. The cursor will turn into a bold + sign.
Nhấp và kéo con trỏ trên cột hoặc dòng mà bạn muốn nhân bản công thức. Công thức đã nhập sẽ tự động được nhập vào các ô mà bạn đã tô sáng. Tham chiếu ô tương đối sẽ tự động cập nhật theo ô trong cùng vị trí tương đối thay vì giữ nguyên. Sau đây là bảng tính mẫu hiển thị công thức đã dùng và kết quả:
| Cột A | Cột B | Cột C | |
|---|---|---|---|
| dòng 1 | 10 | 9 | =A1+B1 |
| dòng 2 | 20 | 8 | =A2+B2 |
| dòng 3 | 30 | 7 | =A3+B3 |
| dòng 4 | 40 | 6 | =A4+B4 |
| Cột A | Cột B | Cột C | |
|---|---|---|---|
| dòng 1 | 10 | 9 | 19 |
| dòng 2 | 20 | 8 | 28 |
| dòng 3 | 30 | 7 | 37 |
| dòng 4 | 40 | 6 | 46 |
- Bạn cũng có thể nhấp đúp vào dấu cộng để điền thông tin vào cả cột thay vì kéo. Chương trình Excel sẽ dừng điền thông tin vào cột khi thấy ô trống. Nếu dữ liệu tham chiếu có khoảng trống, bạn sẽ phải lặp lại bước này để điền cột bên dưới khoảng trống đó.
- Một cách khác để nhân bản công thức cho cả cột là chọn ô ngay bên dưới ô chứa công thức và ấn Ctrl + D.
formulas into multiple cells using paste operations
Nhập công thức vào một ô. Tương tự như với mọi công thức khác, bạn sẽ nhập dấu = trước, rồi nhập hàm hoặc phép tính cần dùng. Trong bảng tính mẫu đơn giản dưới đây, chúng ta sẽ cộng số trong cột A với cột B. Khi bạn ấn Enter hoặc Return, kết quả liền hiển thị.
| Cột A | Cột B | Cột C | |
|---|---|---|---|
| dòng 1 | 10 | 9 | 19 |
| dòng 2 | 20 | 8 | |
| dòng 3 | 30 | 7 | |
| dòng 4 | 40 | 6 |
Select the cell and press Ctrl+C (on Windows) or ⌘ Command+C (on Mac). This action copies the formula to the clipboard.
Select the cell where you want to paste the copied formula. Click on a cell and drag the mouse up or down, or use the arrow keys. Unlike the method of replicating formulas across columns or rows, the cells where the formula will be pasted do not need to be adjacent to the cell containing the copied formula. You can hold the Control key while selecting to copy non-adjacent cells or ranges.
Press Ctrl+V (on Windows) or ⌘ Command+V (on Mac) to paste the information. The formula will now appear in the selected cells.
Use relative and absolute cell references
Sử dụng tham chiếu ô tương đối trong công thức. Trong công thức Excel, "tham chiếu ô" là địa chỉ của ô. Bạn có thể nhập tham chiếu ô này theo cách thủ công, hoặc nhấp vào ô mà bạn muốn dùng trong khi nhập công thức. Ví dụ, bảng tính sau đây có công thức tham chiếu đến ô A2:
| Cột A | Cột B | Cột C | |
|---|---|---|---|
| dòng 2 | 50 | 7 | =A2*2 |
| dòng 3 | 100 | ||
| dòng 4 | 200 | ||
| dòng 5 | 400 |
Tìm hiểu ý nghĩa của tham chiếu tương đối. Trong công thức Excel, tham chiếu tương đối sử dụng vị trí tương đối của địa chỉ ô. Trong ví dụ mẫu, ô C2 có công thức “=A2” là tham chiếu tương đối đến ô giá trị thứ hai bên trái. Nếu bạn sao chép công thức này vào ô C4, giá trị vẫn tham chiếu đến ô thứ hai bên trái, nhưng bây giờ là “=A4”.
| Cột A | Cột B | Cột C | |
|---|---|---|---|
| dòng 2 | 50 | 7 | =A2*2 |
| dòng 3 | 100 | ||
| dòng 4 | 200 | =A4*2 | |
| dòng 5 | 400 |
- Cách này cũng áp dụng được với ô bên ngoài bảng tính nhưng trong cùng dòng và cột. Nếu bạn đã sao chép công thức từ ô C1 vào ô D6 (không hiển thị), Excel sẽ thay đổi tham chiếu "A2" thành ô trong cột liền kề bên phải (C→D) và xuống 5 dòng (2→7), hoặc "B7".
Sử dụng tham chiếu tuyệt đối. Giả sử bạn không muốn Excel tự động thay đổi công thức của mình. Thay vì sử dụng tham chiếu ô tương đối, bạn có thể biến nó thành tuyệt đối bằng cách thêm biểu tượng $ trước cột hoặc dòng cần giữ nguyên, bất kể bạn sao chép công thức từ đâu. Sau đây là các bảng tính mẫu hiển thị công thức gốc với cỡ chữ to và in đậm, cùng kết quả khi bạn thực hiện thao tác sao chép và dán nó vào ô khác:
-
Cột tương đối, dòng tuyệt đối (B$3): Công thức có tham chiếu tuyệt đối đến dòng 3, nên nó luôn tham chiếu đến dòng 3:
Cột A Cột B Cột C dòng 1 50 7 =B$3 dòng 2 100 =A$3 =B$3 dòng 3 200 =A$3 =B$3 dòng 4 400 =A$3 =B$3
-
Cột tuyệt đối, dòng tương đối ($B1): Công thức có tham chiếu tuyệt đối đến cột B, nên nó luôn tham tham chiếu đến cột B.
Cột A Cột B Cột C dòng 1 50 7 =$B1 dòng 2 100 =$B2 =$B2 dòng 3 200 =$B3 =$B3 dòng 4 400 =$B4 =$B4
-
Cột và dòng tuyệt đối ($B$1): Công thức có tham chiếu tuyệt đối đến cột B của dòng 1, nên nó luôn tham chiếu đến cột B của dòng 1.
Cột A Cột B Cột C dòng 1 50 7 =$B$1 dòng 2 100 =$B$1 =$B$1 dòng 3 200 =$B$1 =$B$1 dòng 4 400 =$B$1 =$B$1
Use the F4 key to toggle between absolute and relative references. Highlight the cell reference in the formula by clicking on it and press F4 to automatically add or remove the $ symbol. Continue pressing F4 until the desired absolute or relative reference is selected, then press Enter or Return.
Tips
- If you copy a formula to a new cell and notice a green triangle, Excel has flagged a potential error. Carefully review the formula to identify the issue.
- If you accidentally replace the = sign with ? or * in the "exact formula copy" method, searching for "?" or "*" won’t yield the expected results. You can resolve this by searching for "~?" or "~*".
- Select a cell and press Ctrl' (single quote) to copy the formula from the cell above into it.
Warnings
- Other versions of Excel may have interfaces that differ from the screenshots in this article.
