Chuyển Text thành Formulas là một trong những thủ thuật hay mà mình muốn chia sẻ đến bạn trong bài viết này.
Tuy hiếm gặp với các công việc liên quan đến tính toán, tuy nhiên với thiết kế hay dự toán cốp pha nhôm, mình thường xuyên dùng tới.
Ở đây, ví dụ mình có bài toán như bảng dưới:
| Text | Kết quả |
|---|---|
| 1000*5 | 5000 |
| 1000*5+1 | 5001 |
| 1000*5-1 | 4999 |
| 1000*5/5 | 1000 |
| (1000+10)/5 | 202 |
Trong cột Kết quả chúng ta cần dùng đúng công thức thể hiện ở cột Text 1000*5=5000.
Với cách thông thường, bạn sẽ:
- Dùng tính năng Flash Fill, tách chuỗi 1000 và 5 ra hai cột riêng biệt.
- Sau đó dùng hàm tính nhân “=1000*5” để trả về kết quả.
Xem thêm: Sức mạnh của Flash Fill trong Excel
Nhưng ở đây mình sẽ giới thiệu đến bạn những cách nhanh hơn nhiều.
Nội dung chính:
Chuyển Text thành công thức tính Formulas trong Excel
Có 2 cách giúp bạn giải quyết được vấn đề: Bằng VBA và bằng Define Formula.
Vào luôn…
Cách 1: Chuyển công thức bằng VBA Excel
Chỉ cần 1 đoạn VBA ngắn gọn, bạn có thể chuyển chuỗi text thành công thức hàm. Theo các bước:
Bước 1:
- Mở trình soạn thảo VBA của Excel bằng cách ấn đồng thời tổ hợp phím Atl + F11.
- Hoặc trên thanh menu Excel bạn tìm đến tab Developer / Visual Basic.
Lưu ý: Nếu Excel của bạn không tìm thấy mục Developer hoặc không thể mở VBA, hãy vào Option/Customize Ribbon, tại mục Customize the Ribbon, chọn vào Developer.
Bước 2:
- Sau khi mở VBA, click menu Insert chọn Module.
Bước 3:
Copy text bên dưới và paste vào khung soạn thảo (code):
Function Eval(Ref As String)
Application.Volatile
Eval = Application.Evaluate(“=” & Ref)
End Function
Bước 4:
- Lưu lại và trở về màn hình làm việc của Excel.
- Tại cột kết quả bạn thực hiện hàm: =Eval(ô cần tính).
- Giờ thì bạn có thể tận hưởng thành quả.
Lưu ý:
Nếu MS Excel của bạn chưa Enable Macro thì sẽ gặp lỗi #NAME? hoặc không hiển thị kết quả hàm. Do đó bạn cần bật Macro lên, bằng cách:
- Với Excel 2007, nhấn vào biểu tượng Office Button, chọn Excel Option, chọn Trust Center, nhấn Trust Center Settings.
- Với Excel 2010, nhấn vào menu File, chọn Options, chọn Trust Center, nhấn Trust Center Settings
- Với Excel 2013, nhấn vào biểu tượng Open Other Workbooks, chọn Options, chọn Trust Center, nhấn Trust Center Settings.
- Trong cửa sổ hộp thoại Trust Center Settings, chọn Macro Settings ở thanh trượt bên trái, chọn Enable All Macros, nhấn OK.
- Khởi động lại excel hoặc chọn mở lại tập tin tin excel để phần vừa thiết lập có tác dụng.
Cách 2: Chuyển công thức bằng Formulas Define
- Chọn tab Formulas trên thanh menu.
- Kích chọn mục Define Name.
- Hộp thoại “New Name” hiện lên.
- (1) Dòng Name, nhập tên hàm, ví dụ “congthuc“.
- (2) Dòng “Refers to”, nhập = EVALUATE(
- (3) Chọn tab refer (bảng có mũi tên màu đỏ) để chọn vùng chứa công thức.
- Bấm ok để thoát hộp thoại.
- Tại cột kết quả, gõ hàm =congthuc hoặc hàm cần thực hiện với công thức ở cột công thức.
Chúc bạn thành công!
Xem thêm: VBA ghép nhiều files, nhiều sheets Excel thành một
(Bài viết được cập nhật vào 06/08/2024)
xin chào mr Việt Hưng.
Mình sử dụng Eval với chuỗi công thức này đều báo lỗi #VALUE!. Bạn có thể chỉ dẫn gỡ rối. Tks!
IF(IF(OR(I3=”nhận khách”,I3=”Trả khách”),1+RIGHT(D3,3)/1000,IF(AND(D3>0)*(D2=0)*(D4>0),1+RIGHT(D3,3)/1000,IF(AND(D3>0)*(D2=0)*(D4=0),1+RIGHT(D3,3)/1000,IF(D3>0,(D3-D2)/1000,IF(AND(D3=0)*(LEFT(I3,4)=”Dừng”),”Xe chạy lại”,IF(D3=0,I3))))))=0,”Xe dừng”,IF(OR(I3=”nhận khách”,I3=”Trả khách”),1+RIGHT(D3,3)/1000,IF(AND(D3>0)*(D2=0)*(D4>0),1+RIGHT(D3,3)/1000,IF(AND(D3>0)*(D2=0)*(D4=0),1+RIGHT(D3,3)/1000,IF(D3>0,(D3-D2)/1000,IF(AND(D3=0)*(LEFT(I3,4)=”Dừng”),”Xe chạy lại”,IF(D3=0,I3)))))))
mình mới bổ sung cách 2, bạn thử giải quyết được không nhé
cho em hỏi, em dùng công thức trong đó có 1 ô có sử dụng hàm eval nhưng khi kết quả ra thì có lúc đúng kết quả, có lúc lại bị âm, công thức bỏ ô có sử dụng hàm eval thì lại đúng, xin chỉ giúp ạ !
em chuyển dữ liệu ô eval về dạng number nhé, hoặc dùng hàm =value(eval)….
Đối với trường hợp này sao mình làm không AD hướng dẫn giúp mình được không ạ
((0,8 x 4 x 0,08 x 2) + (0,5 x 4 x 1 x 2)) / 100 0
Vẫn thực hiện được bạn nhé, bạn kiểm tra xem có khoảng trắng (dấu cách) nào không, dấu nhân là “*” chứ không phải x.
xóa khoảng trắng bạn dùng hàm TRIM
thay x thành * bạn dùng hàm SUBSTITUTE
Chào bạn. Tôi sử dụng các phép tính thì rất ok.
Nhưng khi tôi sử dụng Max & A1 & : & A5 (tìm số lớn nhất trong các ô từ A1 đến A5) thì không được bạn ạ.
không biết a dùng max trong hàm eval hay max cho kết quả equal
nếu max trong eval thì ko được a nha, vì eval của text chứ ko phải number, mà max, min là number
a có thể sử dụng 1 bước gián tiếp khác từ kết quả của eval
Mình cũng giống như anh kia. Sử dụng hàm min(if(… Sau khi sử dụng Eval cũng báo lỗi #VALUE!. Trong Hàm Min(if… khi dùng hàm phải bấm Ctrl + Shift + Enter. Bạn có cách gián tiếp nào khác để sử dụng Eval không. Cám ơn.
bạn thử đoạn thay đoạn trên bằng đoạn này xem nhé:
Function Eval(Ref As String)
Application.Volatile
Eval = Application.Evaluate(“=” & Ref)
End Function
Ad ơi. Vui lòng cho hỏi không thực hiện được với các số lẻ
ko biết số lẻ e định dạng đúng ko, nếu định dạng số thập phân là dấu chấm “.” thì vẫn dùng đc em nha.
nếu định dạng của em đang là dấu phẩy “,” thì e dùng hàm SUBSTITUTE để thay thế nhé
Anh ơi, hỗ trợ giúp em chỗ này với ạ. Em có 1 công thức như sau: IF(LEFT(INDIRECT(“AM”&ROW()),3)=”292”,IF(INDIRECT(“J”&ROW())<=2,15000,15000+ROUNDUP(INDIRECT("J"&ROW())-2,0)*5000),IF(INDIRECT("J"&ROW())<=2,20000,20000+ROUNDUP(INDIRECT("J"&ROW())-2,0)*7000)). Công thức này được dò dựa trên mã NV từ 1 file khác sang. Em muốn sau khi dò được công thức, thì công thức tự ra kết quả luôn mà không cần thêm dấu "=" hoặc dấu "+". Em đã làm theo cách anh hướng dẫn ở trên nhưng không thành công, chỉ thành công với dạng chuỗi như ví dụ của anh, chẳng hạn 30/2; 25*5…..
em gửi anh file được ko
Dạ, em xin email của anh ạ.
blogcopphanhom@gmail.com
nếu dãy số có chữ thì em tách chữ ra thôi, dùng Substitute
được nhé bạn
EM MUỐN TÍNH TỔNG DÃY SỐ ĐẤY CHÈN THÊM CHỮ THÌ THẾ NÀO AH ?
Có hiệu quả ko nếu mình nhập chuỗi string là Eval(“vlookup(xxx)”) thì hàm vlookup có được tính toán ko bạn?
mình Enable Macro trong xcel 2007 tới bước Trong cửa sổ hộp thoại Trust Center Settings, chọn Macro Settings nhưng ko thấy biểu tượng Enable All Macros, nhấn OK.
Mình làm ok rùi đã cài Macro sau tắt file đi rùi mở lại vẫn báo lối ?NAME# là sao bạn nhỉ
để mình kiểm tra xem, chứ ms 2013 mình ko bị lỗi gì cả
CÁC BẠN CHO MÌNH HỎI MÌNH LÀM THEO OK RÙI. HUM SAU MỞ RA THÌ TOÀN BỊ BÁO LỖI #NAME? LÀ SAO NHỈ
có thể do anh chưa Enable Macro nhé
mình cũng cài Enable Macro rùi làm bình thường sau tắt file mở lại vẫn bị báo lỗi ?NAME# là sao bạn nhỉ
bạn mở macro lên xem code còn lưu sau khi tắt mở lại không? mình đón là ko lưu
thế nó cứ k lưu thì làm thế nào để lưu code đó được hả bạn?
bạn save as, chọn định dạng Excel Macro-Enabled (.xlsm) nhé
Ủa cho mình hỏi, sao ô mình cần chọn dùng dấu phẩy, thì Eval không hiểu được, phỉa chuyển sang dấu chấm thì Eval mới tính được hả bạn
dấu a nói là định dạng format cell hay dấu a tự gõ?
Excel số thập phân dùng dấu “.” còn dấu “,” ko hiểu đâu a
mình làm theo hướng dẫn của bạn, xong tắt file excel mở lên lại cái hàm eval ko còn chạy nữa, phải làm lại từ đầu?
anh kiểm tra lại đã lưu chưa nhé, mình vẫn chạy bình thường
a ơi, sao kết quả ra cho ra phía trước có hiện dấu “=” ạ, làm sao để bỏ dấu “=” đi, chỉ hiện kết quả số thôi ạ
em xem định dạng format cells là text hay general, number…. a áp dụng kết quả ko có dấu bằng em ơi. F9 lại xem sao em nhé
Chỉnh format là number, mất dấu bằng được rồi a ạ, e cám ơn a nhé
welcome!
Sao mình cài không được nhỉ.nó cứ báo lỗi ấy.mà tắt file đi là nó mất cài đặt luôn.
bạn dùng excel bao nhiêu, mở chế độ macros chưa
Em để số 12,3*9 thì báo lỗi value còn để 12.3*9 thì ra kết quả anh ah. vậy là lỗi gì ạ
Excel số thập phân dùng dấu “.” chứ sao phẩy được em
Em làm giống theo hướng dẫn n bị lỗi value là sao ạ, file cũng đã lưu dạng macro r
kiểm tra xem chuỗi tính của em có khoảng trắng ko nhé
Thủ thuật rất hay, xin cảm ơn bạn !
thanks
Chuỗi phép tính dài quá ko tính được vậy phải làm sao thế tiền bối?
cho ví dụ thử bạn
Rất hay bạn Hưng ạ. Thanks nhé.
Thanks bạn