Nội dung
Vấn đề
Giả sử chúng ta có một bảng đơn giản như thế này, trong đó số tiền được tính cho mỗi tháng ở hai thành phố, và sau đó tổng số được chuyển đổi thành euro theo tỷ giá từ ô màu vàng J2.
Vấn đề là nếu bạn sao chép phạm vi D2: D8 với các công thức ở một nơi khác trên trang tính, thì Microsoft Excel sẽ tự động sửa các liên kết trong các công thức này, di chuyển chúng đến một vị trí mới và ngừng đếm:
Nhiệm vụ: sao chép phạm vi có công thức để các công thức không thay đổi và giữ nguyên, giữ nguyên kết quả tính toán.
Phương pháp 1. Liên kết tuyệt đối
Phương pháp 2: Tạm thời vô hiệu hóa công thức
Để ngăn các công thức thay đổi khi sao chép, bạn cần (tạm thời) đảm bảo rằng Excel ngừng coi chúng là công thức. Điều này có thể được thực hiện bằng cách thay thế dấu bằng (=) bằng bất kỳ ký tự nào khác thường không được tìm thấy trong công thức, chẳng hạn như dấu thăng (#) hoặc một cặp dấu và (&&) cho thời gian sao chép. Đối với điều này:
- Chọn phạm vi có công thức (trong ví dụ D2: D8 của chúng tôi)
- Nhấp chuột Ctrl + H trên bàn phím hoặc trên một tab Trang chủ - Tìm và Chọn - Thay thế (Trang chủ - Tìm & Chọn - Thay thế)
- Trong hộp thoại xuất hiện, hãy nhập những gì chúng tôi đang tìm kiếm và những gì chúng tôi thay thế bằng Thông số (Tùy chọn) đừng quên làm rõ Phạm vi tìm kiếm - Công thức. Chúng tôi nhấn Thay thế tất cả (Thay thế tất cả).
- Sao chép phạm vi kết quả với các công thức đã hủy kích hoạt vào đúng vị trí:
- Thay thế # on = quay lại bằng cách sử dụng cùng một cửa sổ, trả lại chức năng cho các công thức.
Phương pháp 3: Sao chép qua Notepad
Phương pháp này nhanh hơn và dễ dàng hơn nhiều.
Nhấn phím tắt Ctrl + Ё hoặc nút Hiển thị công thức chuyển hướng công thức (Công thức - Hiển thị công thức), để bật chế độ kiểm tra công thức - thay vì kết quả, các ô sẽ hiển thị các công thức mà chúng được tính toán:
Sao chép phạm vi D2: D8 của chúng tôi và dán vào tiêu chuẩn Sổ tay:
Bây giờ chọn mọi thứ đã dán (Ctrl + A), sao chép lại vào khay nhớ tạm (Ctrl + C) và dán nó vào trang tính ở nơi bạn cần:
Nó vẫn chỉ để nhấn nút Hiển thị công thức (Hiển thị công thức)để đưa Excel về chế độ bình thường.
Lưu ý: phương pháp này đôi khi không thành công trên các bảng phức tạp với các ô đã hợp nhất, nhưng trong phần lớn các trường hợp, nó hoạt động tốt.
Phương pháp 4. Macro
Nếu bạn thường xuyên phải thực hiện việc sao chép các công thức mà không cần thay đổi các tham chiếu, thì bạn nên sử dụng macro cho việc này. Nhấn phím tắt Alt + F11 hoặc nút Visual Basic chuyển hướng nhà phát triển (Nhà phát triển), chèn một mô-đun mới thông qua menu Chèn - Mô-đun và sao chép văn bản của macro này vào đó:
Sub Copy_Formulas() Dim copyRange As Range, pasteRange As Range On Error Resume Next Đặt copyRange = Application.InputBox("Chọn các ô có công thức để sao chép.", _ "Sao chép chính xác công thức", Mặc định:=Selection.Address, Type := 8) Nếu copyRange không có gì thì thoát Sub Set pasteRange = Application.InputBox("Bây giờ hãy chọn dải ô dán." & vbCrLf & vbCrLf & _ "Dải ô phải có kích thước bằng với dải ô " & vbCrLf & _ " ban đầu để sao chép." , "Sao chép công thức chính xác", _ Mặc định:=Selection.Address, Type:=8) If pasteRange.Cells.Count <> copyRange.Cells.Count Then MsgBox "Sao chép và dán các phạm vi có kích thước khác nhau!", vbExclamation, "Copy error" Thoát Sub End If If pasteRange Is nothing Then Exit Sub Khác pasteRange.Formula = copyRange.Formula End If End SubBạn có thể sử dụng nút để chạy macro. Macros chuyển hướng nhà phát triển (Nhà phát triển - Macro) hoặc phím tắt Alt + F8. Sau khi chạy macro, nó sẽ yêu cầu bạn chọn phạm vi có các công thức ban đầu và phạm vi chèn và sẽ tự động sao chép các công thức:
- Thuận tiện xem các công thức và kết quả cùng một lúc
- Tại sao cần có kiểu tham chiếu R1C1 trong công thức Excel
- Cách tìm nhanh tất cả các ô có công thức
- Công cụ sao chép công thức chính xác từ tiện ích bổ sung PLEX