Excel giúp bạn dễ dàng sao chép công thức trên toàn bộ hàng hoặc cột, nhưng không phải lúc nào bạn cũng nhận được kết quả mong muốn. Nếu bạn nhận được kết quả không mong muốn hoặc những lỗi #REF và / DIV0 khủng khiếp đó, điều đó có thể khiến bạn vô cùng bực bội. Nhưng đừng lo lắng-bạn sẽ không cần phải chỉnh sửa từng ô bảng tính 5.000 dòng của mình. WikiHow này hướng dẫn bạn những cách dễ dàng để sao chép công thức sang các ô khác.
Các bước
Phương pháp 1/4: Sử dụng Tìm và Thay thế để Sao chép Chính xác Công thức
Bước 1. Mở sổ làm việc của bạn trong Excel
Đôi khi, bạn có một bảng tính lớn chứa đầy các công thức và bạn muốn sao chép chúng một cách chính xác. Thay đổi mọi thứ thành tham chiếu ô tuyệt đối sẽ rất tẻ nhạt, đặc biệt nếu bạn chỉ muốn thay đổi lại chúng sau đó. Sử dụng phương pháp này để nhanh chóng di chuyển công thức có tham chiếu ô tương đối sang nơi khác mà không thay đổi tham chiếu. Trong bảng tính ví dụ của chúng tôi, chúng tôi muốn sao chép các công thức từ cột C sang cột D mà không thay đổi bất kỳ điều gì.
| Cột A | Cột B | Cột C | Cột D | |
|---|---|---|---|---|
| hàng 1 | 944 | Ếch | = A1 / 2 | |
| hàng 2 | 636 | Con cóc | = A2 / 2 | |
| hàng 3 | 712 | Sa giông | = A3 / 2 | |
| hàng 4 | 690 | Rắn | = A4 / 2 |
Nếu bạn chỉ đang cố gắng sao chép công thức trong một ô, hãy chuyển sang bước cuối cùng ("Thử các phương pháp thay thế") trong phần này
Bước 2. Nhấn Ctrl + H để mở cửa sổ Tìm
Phím tắt giống nhau trên Windows và macOS.
Bước 3. Tìm và thay thế "=" bằng một ký tự khác
Nhập "=" vào trường "Tìm nội dung", sau đó nhập một ký tự khác vào hộp "Thay thế bằng". Nhấp chuột Thay thế tất cả để biến tất cả các công thức (luôn bắt đầu bằng dấu bằng) thành chuỗi văn bản bắt đầu bằng một số ký tự khác. Luôn sử dụng một ký tự mà bạn chưa sử dụng trong bảng tính của mình.
Ví dụ: thay thế nó bằng # hoặc &, hoặc một 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 | |
|---|---|---|---|---|
| hàng 1 | 944 | Ếch | ## & A1 / 2 | |
| hàng 2 | 636 | Con cóc | ## & A2 / 2 | |
| hàng 3 | 712 | Sa giông | ## & A3 / 2 | |
| hàng 4 | 690 | Rắn | ## & A4 / 2 |
Không sử dụng các ký tự * hoặc?, Vì những ký tự này sẽ làm cho các bước sau này khó khăn hơn
Bước 4. Sao chép và dán các ô
Đánh dấu các ô bạn muốn sao chép, sau đó nhấn Ctrl + C (PC) hoặc Cmd + C (Mac) để sao chép chúng. Sau đó, chọn các ô bạn muốn dán vào và nhấn Ctrl + V (PC) hoặc Cmd + V (Mac) để dán. Vì chúng không còn được hiểu là công thức, chúng sẽ được sao chép chính xác.
| Cột A | Cột B | Cột C | Cột D | |
|---|---|---|---|---|
| hàng 1 | 944 | Ếch | ## & A1 / 2 | ## & A1 / 2 |
| hàng 2 | 636 | Con cóc | ## & A2 / 2 | ## & A2 / 2 |
| hàng 3 | 712 | Sa giông | ## & A3 / 2 | ## & A3 / 2 |
| hàng 4 | 690 | Rắn | ## & A4 / 2 | ## & A4 / 2 |
Bước 5. Sử dụng Find & Replace một lần nữa để đảo ngược thay đổi
Bây giờ bạn đã có các công thức mà bạn muốn, hãy sử dụng lại "Replace All" để đảo ngược thay đổi của bạn. Trong ví dụ của chúng tôi, chúng tôi sẽ tìm kiếm chuỗi ký tự "## &" và thay thế nó bằng "=" một lần nữa, để các ô đó trở thành công thức một lần nữa. Bây giờ bạn có thể tiếp tục chỉnh sửa bảng tính của mình như bình thường:
| Cột A | Cột B | Cột C | Cột D | |
|---|---|---|---|---|
| hàng 1 | 944 | Ếch | = A1 / 2 | = A1 / 2 |
| hàng 2 | 636 | Con cóc | = A2 / 2 | = A2 / 2 |
| hàng 3 | 712 | Sa giông | = A3 / 2 | = A3 / 2 |
| hàng 4 | 690 | Rắn | = A4 / 2 | = A4 / 2 |
Bước 6. Hãy thử các phương pháp thay thế
Nếu phương pháp được mô tả ở trên không hoạt động vì lý do nào đó hoặc nếu bạn lo lắng về việc vô tình thay đổi nội dung ô khác bằng tùy chọn "Thay thế tất cả", bạn có thể thử một số cách khác:
- Để sao chép công thức của một ô mà không thay đổi tham chiếu, hãy chọn ô, sau đó sao chép công thức được hiển thị trong thanh công thức gần đầu cửa sổ (không phải trong chính ô). nhấn Thoát ra để đóng thanh công thức, sau đó dán công thức vào bất cứ nơi nào bạn cần.
- nhấn Điều khiển và ` (thường trên cùng một phím với ~) để đặt bảng tính ở chế độ xem công thức. Sao chép các công thức và dán chúng vào một trình soạn thảo văn bản như Notepad hoặc TextEdit. Sao chép chúng một lần nữa, sau đó dán chúng trở lại bảng tính ở vị trí mong muốn. Sau đó nhấn Điều khiển và ` một lần nữa để chuyển về chế độ xem thông thường.
Phương pháp 2/4: Điền cột hoặc hàng bằng công thức
Bước 1. Nhập công thức vào ô trống
Excel giúp dễ dàng truyền công thức xuống một cột hoặc trên một hàng bằng cách "điền" vào các ô. Như với bất kỳ công thức nào, hãy bắt đầu với một = ký, sau đó sử dụng bất kỳ hàm hoặc số học nào bạn muốn. Chúng tôi sẽ sử dụng một bảng tính mẫu đơn giản và thêm cột A và cột B lại với nhau. nhấn Vào hoặc Trở lại để tính công thức.
| Cột A | Cột B | Cột C | |
|---|---|---|---|
| hàng 1 | 10 | 9 | 19 |
| hàng 2 | 20 | 8 | |
| hàng 3 | 30 | 7 | |
| hàng 4 | 40 | 6 |
Bước 2. Nhấp vào góc dưới bên phải của ô có công thức bạn muốn sao chép
Con trỏ sẽ trở thành một chữ đậm + ký tên.
Bước 3. Nhấp và kéo con trỏ qua cột hoặc hàng mà bạn đang sao chép
Công thức bạn đã nhập sẽ tự động được nhập vào các ô bạn đã đánh dấu. Tham chiếu ô tương đối sẽ tự động cập nhật để tham chiếu đến ô ở cùng một vị trí tương đối thay vì giữ nguyên chính xác. Đây là bảng tính mẫu của chúng tôi, hiển thị các công thức được sử dụng và kết quả được hiển thị:
| Cột A | Cột B | Cột C | |
|---|---|---|---|
| hàng 1 | 10 | 9 | = A1 + B1 |
| hàng 2 | 20 | 8 | = A2 + B2 |
| hàng 3 | 30 | 7 | = A3 + B3 |
| hàng 4 | 40 | 6 | = A4 + B4 |
| Cột A | Cột B | Cột C | |
|---|---|---|---|
| hàng 1 | 10 | 9 | 19 |
| hàng 2 | 20 | 8 | 28 |
| hàng 3 | 30 | 7 | 37 |
| hàng 4 | 40 | 6 | 46 |
- Bạn cũng có thể nhấp đúp vào dấu cộng để lấp đầy toàn bộ cột thay vì kéo. Excel sẽ ngừng điền vào cột nếu nó thấy một ô 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 vào cột bên dưới khoảng trống.
- Một cách khác để điền vào toàn bộ cột với cùng một công thức là chọn các ô ngay bên dưới ô chứa công thức và sau đó nhấn Ctrl + D.
Phương pháp 3/4: Sao chép một công thức thành nhiều ô bằng cách dán
Bước 1. Nhập công thức vào một ô
Như với bất kỳ công thức nào, hãy bắt đầu với một = ký, sau đó sử dụng bất kỳ hàm hoặc số học nào bạn muốn. Chúng tôi sẽ sử dụng một bảng tính mẫu đơn giản và thêm cột A và cột B lại với nhau. Khi bạn nhấn Vào hoặc Trở lại, công thức sẽ tính toán.
| Cột A | Cột B | Cột C | |
|---|---|---|---|
| hàng 1 | 10 | 9 | 19 |
| hàng 2 | 20 | 8 | |
| hàng 3 | 30 | 7 | |
| hàng 4 | 40 | 6 |
Bước 2. Chọn ô và nhấn Ctrl + C (PC) hoặc ⌘ Command + C (Mac).
Thao tác này sao chép công thức vào khay nhớ tạm của bạn.
Bước 3. Chọn các ô bạn muốn sao chép công thức
Nhấp vào một và kéo lên hoặc xuống bằng chuột hoặc các phím mũi tên. Không giống như với phương pháp điền cột hoặc hàng, các ô bạn đang sao chép công thức không cần phải liền kề với ô bạn đang sao chép từ đó. Bạn có thể giữ Điều khiển trong khi chọn để sao chép các ô và dải ô không liền kề.
Bước 4. Nhấn Ctrl + V (PC) hoặc ⌘ Command + V (Mac) để dán.
Các công thức hiện xuất hiện trong các ô đã chọn.
Phương pháp 4/4: Sử dụng tham chiếu ô tương đối và tuyệt đối
Bước 1. Sử dụng tham chiếu ô tương đối trong một công thức
Trong công thức Excel, "tham chiếu ô" là địa chỉ của ô. Bạn có thể nhập những thứ này theo cách thủ công hoặc nhấp vào ô bạn muốn sử dụng trong khi nhập công thức. Ví dụ: bảng tính sau có công thức tham chiếu đến ô A2:
| Cột A | Cột B | Cột C | |
|---|---|---|---|
| hàng 2 | 50 | 7 | = A2 * 2 |
| hàng 3 | 100 | ||
| hàng 4 | 200 | ||
| hàng 5 | 400 |
Bước 2. Hiểu tại sao chúng được gọi là 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ụ của chúng tôi, C2 có công thức “= A2”, là một tham chiếu tương đối đến giá trị ở hai ô bên trái. Nếu bạn sao chép công thức vào C4, thì công thức sẽ vẫn tham chiếu đến hai ô bên trái, hiện hiển thị “= A4”.
| Cột A | Cột B | Cột C | |
|---|---|---|---|
| hàng 2 | 50 | 7 | = A2 * 2 |
| hàng 3 | 100 | ||
| hàng 4 | 200 | = A4 * 2 | |
| hàng 5 | 400 |
Điều này cũng hoạt động cho các ô bên ngoài cùng một hàng và cột. Nếu bạn đã sao chép cùng một công thức từ ô C1 vào ô D6 (không được hiển thị), Excel sẽ thay đổi tham chiếu "A2" thành một ô ở bên phải một cột (C → D) và 5 hàng bên dưới (2 → 7) hoặc " B7”
Bước 3. Sử dụng một tham chiếu tuyệt đối để thay thế
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ể làm cho nó tuyệt đối bằng cách thêm biểu tượng $ vào trước cột hoặc hàng mà bạn muốn giữ nguyên, bất kể bạn sao chép công thức ở đâu. Dưới đây là một số bảng tính ví dụ, hiển thị công thức gốc ở dạng văn bản in đậm, lớn hơn và kết quả khi bạn sao chép-dán nó vào các ô khác:
-
Cột Tương đối, Hàng Tuyệt đối (B $ 3):
Công thức có tham chiếu tuyệt đối đến hàng 3, vì vậy nó luôn tham chiếu đến hàng 3:
Cột A Cột B Cột C hàng 1 50 7 = B $ 3 hàng 2 100 = A $ 3 = B $ 3 hàng 3 200 = A $ 3 = B $ 3 hàng 4 400 = A $ 3 = B $ 3 -
Cột tuyệt đối, hàng tương đối ($ B1):
Công thức có tham chiếu tuyệt đối đến cột B, vì vậy nó luôn tham chiếu đến cột B.
Cột A Cột B Cột C hàng 1 50 7 = $ B1 hàng 2 100 = $ B2 = $ B2 hàng 3 200 = $ B3 = $ B3 hàng 4 400 = $ B4 = $ B4 -
Cột & Hàng tuyệt đối ($ B $ 1):
Công thức có tham chiếu tuyệt đối đến cột B của hàng 1, vì vậy nó luôn tham chiếu đến cột B của hàng 1.
Cột A Cột B Cột C hàng 1 50 7 = $ B $ 1 hàng 2 100 = $ B $ 1 = $ B $ 1 hàng 3 200 = $ B $ 1 = $ B $ 1 hàng 4 400 = $ B $ 1 = $ B $ 1
Bước 4. Sử dụng phím F4 để chuyển đổi giữa tuyệt đối và tương đối
Đánh dấu tham chiếu ô trong một công thức bằng cách nhấp vào nó và nhấn F4 để tự động thêm hoặc bớt các biểu tượng $. Tiếp tục nhấn F4 cho đến khi các tham chiếu tuyệt đối hoặc tương đối bạn muốn được chọn, sau đó nhấn Vào hoặc Trở lại.
Video - Bằng cách sử dụng dịch vụ này, một số thông tin có thể được chia sẻ với YouTube
Lời khuyên
- Nếu bạn sao chép một công thức vào một ô mới và thấy một hình tam giác màu xanh lá cây, thì Excel đã phát hiện ra một lỗi có thể xảy ra. Kiểm tra công thức một cách cẩn thận để xem có gì sai không.
- Nếu bạn vô tình thay thế ký tự = bằng? hoặc * trong phương thức "sao chép chính xác công thức", tìm kiếm "?" hoặc "*" sẽ không cho bạn kết quả như mong đợi. Sửa lỗi này bằng cách tìm kiếm "~?" hoặc cho "~ *" thay thế.
- Chọn một ô và nhấn Ctrl '(dấu nháy đơn) để điền vào ô đó với công thức ngay phía trên ô đó.
