Trong bài viết này, tôi sẽ chỉ cho bạn cách bạn có thể xóa công thức từ một trang tính trong Excel giữ nguyên các giá trị và định dạng bằng VBA . Trước tiên, bạn sẽ học cách xóa công thức khỏi một dải ô đã chọn, sau đó khỏi toàn bộ trang tính.
VBA để loại bỏ công thức trong Excel Giữ giá trị và định dạng (Xem nhanh)
Sub Remove_Formulas_from_Selected_Range()
Dim Rng As Range
Set Rng = Selection
Rng.Copy
Rng.PasteSpecial Paste:=xlPasteValuesAndNumberFormats
Application.CutCopyMode = False
End Sub
⧭ Giải thích về Quy tắc:
- Mã này tạo Macro được gọi là Remove_Formulas_from_Selected_Range .
- Đầu tiên, nó sao chép các giá trị từ tất cả các ô của một dải ô do người dùng chọn.
- Sau đó, nó sẽ dán các giá trị vào các ô tương ứng để giữ nguyên các định dạng.
- Do đó, nó sẽ xóa tất cả các công thức khỏi một dải ô đã chọn, giữ nguyên giá trị và định dạng.
2 Cách Loại bỏ Công thức trong Excel Giữ Giá trị và Định dạng bằng VBA
Ở đây, chúng tôi đã có một tập dữ liệu với các tên , mức lương khởi điểm và mức lương hiện tại của một số nhân viên của một công ty có tên là Jupyter Group.
Ngoài ra, chúng tôi có mức lương trung bình , tên của nhân viên có mức lương cao nhất, và điều đó với mức lương thấp nhất trong các ô riêng biệt của trang tính.
Ở đây, mức lương hiện tại của mỗi người lao động là 20% mức lương khởi điểm.
Đó là, ô D4 có công thức:
=C4+(C4*20)/100 D5 có công thức:
=C5+(C5*20)/100
Và như vậy.
Ô G7 có mức lương trung bình với công thức:
=AVERAGE(D4:D13)
Ô H7 có nhân viên có mức lương cao nhất với công thức:
=INDEX(B4:D13,MATCH(MAX(D4:D13),D4:D13,0),1) Và ô I7 có nhân viên có mức lương thấp nhất với công thức:
=INDEX(B4:D13,MATCH(MIN(D4:D13),D4:D13,0),1)
Hôm nay chúng tôi sẽ phát triển Macro sử dụng Ứng dụng Visual Basic ( VBA ) sẽ xóa các công thức khỏi trang tính này, giữ nguyên các giá trị và định dạng.
1. VBA để loại bỏ công thức trong Excel Giữ giá trị và định dạng từ một phạm vi đã chọn
Trước hết, hãy phát triển Macro sẽ xóa công thức khỏi một phạm vi ô cụ thể, không phải khỏi toàn bộ trang tính.
Ví dụ:hãy cố gắng xóa công thức chỉ khỏi hồ sơ nhân viên ( B4:D13 ).
⧭ Mã VBA:
Sub Remove_Formulas_from_Selected_Range()
Dim Rng As Range
Set Rng = Selection
Rng.Copy
Rng.PasteSpecial Paste:=xlPasteValuesAndNumberFormats
Application.CutCopyMode = False
End Sub⧪ Lưu ý: Mã này tạo Macro được gọi là Remove_Formulas_from_Selected_Range .
⧭ Đầu ra:
Trước tiên, hãy lưu tệp dưới dạng Sổ làm việc được bật Macro trên Excel trước tiên . Sau đó, chọn phạm vi ô mà bạn muốn xóa công thức.
Ở đây tôi chỉ muốn xóa công thức khỏi bản ghi nhân viên ( B4:D13 ). Vì vậy, tôi đã chọn phạm vi B4:D13 .
Sau đó, chạy Macro có tên Remove_Formulas_from_Selected_Range.
( Đọc bài viết này để xem chi tiết cách chạy Macro ).
Và bạn sẽ xóa tất cả các công thức khỏi phạm vi ô đã chọn, để lại các giá trị và định dạng.
Đọc thêm: Chuyển đổi công thức thành giá trị mà không cần dán đặc biệt trong Excel (5 phương pháp dễ dàng)
Bài đọc tương tự
- Loại bỏ Công thức Khi được Lọc trong Excel (3 Cách)
- Cách Loại bỏ Công thức Tự động trong Excel (5 Phương pháp)
- Dừng công thức để tự động chuyển đổi thành giá trị trong Excel
- Cách chuyển đổi công thức thành giá trị trong Excel (8 phương pháp nhanh)
2. VBA để loại bỏ công thức trong Excel Giữ giá trị và định dạng từ toàn bộ trang tính
Trong phương pháp trước, chúng tôi đã phát triển Macro để xóa công thức khỏi một dải ô đã chọn.
Bây giờ, chúng tôi sẽ cố gắng xóa công thức khỏi toàn bộ trang tính.
Sử dụng mã VBA sau cho mục đích này.
⧭ Mã VBA:
Sub Remove_Formulas_from_the_Whole_Worksheet()
Sheet_Name = InputBox("Enter the Name of the Worksheet to Remove Formulas: ")
Dim Rng As Range
Set Rng = Sheets(Sheet_Name).Cells
Rng.Copy
Rng.PasteSpecial Paste:=xlPasteValuesAndNumberFormats
Application.CutCopyMode = False
End Sub⧪ Lưu ý: Mã này tạo Macro được gọi là Remove_Formulas_from_the_Whole_Worksheet.
⧭ Đầu ra:
Quay lại trang tính của bạn và chạy Macro được gọi là Remove_Formulas_from_the_Whole_Worksheet .
Bạn sẽ nhận được Hộp nhập liệu yêu cầu bạn nhập tên của trang tính từ nơi bạn muốn xóa công thức.
Tại đây, tôi muốn xóa công thức khỏi Sheet2 , vì vậy tôi đã nhập Sheet2 .
Nhấp vào OK .
Và bạn sẽ xóa các công thức khỏi toàn bộ trang tính, chỉ giữ lại các giá trị và định dạng.
Nó xóa các công thức khỏi mức lương hiện tại của các nhân viên.
Ngoài ra, nó sẽ xóa các công thức khỏi mức lương trung bình , nhân viên có mức lương cao nhất và nhân viên có mức lương thấp nhất .
Đọc thêm: Cách hiển thị giá trị thay vì công thức trong Excel (7 phương pháp)
Những điều cần nhớ
Ở đây, chúng tôi đã sử dụng xlPasteValuesAndNumberFormats tùy chọn để dán bằng VBA , giữ nguyên các giá trị và định dạng khi dán một số giá trị trong Excel.
Cùng với nó, có 11 nhiều tùy chọn hơn để dán các giá trị trong VBA , mỗi người trong số họ thực hiện một kiểu dán cụ thể.
Truy cập liên kết này để biết chi tiết về chúng .
Kết luận
Sử dụng các phương pháp này, bạn có thể xóa công thức khỏi trang tính trong Excel, giữ nguyên giá trị và định dạng bằng VBA . Bạn có câu hỏi nào không? Hãy hỏi chúng tôi.
Các bài viết liên quan
- Cách chuyển đổi kết quả công thức thành chuỗi văn bản trong Excel (7 cách dễ dàng)
- Đặt kết quả của một công thức vào một ô khác trong Excel (4 trường hợp phổ biến)
- Cách trả lại giá trị của ô không phải công thức trong Excel (3 phương pháp dễ dàng)
- Tự động chuyển đổi công thức thành giá trị trong Excel (6 cách hiệu quả)
- Cách Loại bỏ Công thức Ẩn trong Excel (5 Phương pháp Nhanh)