Chuyển công thức thành TEXT trong Excel
Hàm TEXT trong Excel là một hàm giúp bạn chuyển đổi giá trị số sang chuỗi văn bản ở một định dạng cụ thể, với mục đích phổ biến là giúp các dữ liệu số hiển thị dưới định dạng dễ nhìn hơn hoặc dễ sử dụng hơn. Trong một số trường hợp, hàm này cũng được dùng khi muốn kết hợp các số hoặc ngày tháng với văn bản/một số ký tự nhất định. Công thức hàm TEXT: Lưu ý:
Xem thêm: Hàm SUMIF
Dưới đây là một vài ví dụ về cách sử dụng phổ biến của hàm TEXT để bạn tham khảo: Ví dụ 1: Chèn định dạng số bên trong một chuỗi văn bản Ví dụ, mục đích của bạn là tính được tổng số tiền khách hàng cần trả (Thành tiền) với số liệu như trên. Công thức để ra được kết quả là: Thành tiền = Đơn giá x Số lượng x % còn lại sau khi được giảm giá (ở đây sẽ lấy 1 - % giảm) Như vậy, nếu tính toán đơn thuần bạn sẽ chỉ cần nhập công thức là: =A2*B2*(1-C2). Kết quả trả về cho bạn sẽ là con số 156.8. Tuy nhiên, bạn chưa hài lòng với việc kết quả trả về chỉ là một con số, không có đơn vị, không có định dạng đẹp mắt, lại không có dòng chú thích khiến những con số lẫn lộn với nhau gây rối mắt, khó hiểu. Cụ thể, để dữ liệu trông dễ xem hơn, bạn muốn chèn thêm câu giải thích là "Thành tiền: " trước giá tiền; đồng thời bạn muốn giá tiền có thêm biểu tượng tiền tệ, dấu phân cách hàng nghìn và hai chữ số thập phân. Lúc này hàm TEXT sẽ giúp bạn. Bạn có thể thực hiện 1 trong 2 cách sau: Cách 1: Nối chuỗi ký tự với hàm TEXT bằng hệ thống ký hiệu. Bạn nhập: ="Thành tiền: "&TEXT(A2*B2*(1-C2),"$###,###.00") Cách 2: Nối chuỗi ký tự với hàm TEXT bằng hàm CONCATENATE. Cú pháp của CONCATENATE như sau:
=CONCATENATE (text1, text2,…). Trong đó text là một chuỗi văn bản, tham chiếu ô hoặc giá trị trong công thức.Như vậy, đối với ví dụ trên, bạn nhập: =CONCATENATE("Thành tiền: ",TEXT(A2*B2*(1-C2),"$###,###.00")) Ví dụ 2: Kết hợp văn bản và ngày tháng Giả sử, bạn có một bảng dữ liệu và bạn muốn thêm một dòng chú thích "Ngày khởi tạo dữ liệu: ", đồng thời muốn ngày tháng hiển thị theo thứ tự: Ngày, tháng, năm (Ví dụ: 08/08/2021) Bạn có thể lồng hàm TODAY trong công thức của hàm TEXT, và khai báo mã định dạng "dd/mm/yyyy" để kết quả trả về theo ý muốn của mình. Cách 1: Bạn dùng hệ thống ký hiệu để nối chuỗi ký tự với hàm TEXT: ="Ngày khởi tạo dữ liệu: " & TEXT(TODAY(), "dd/mm/yyyy") Cách 2: Bạn dùng hàm TEXT kết hợp CONCATENATE: =CONCATENATE("Ngày khởi tạo dữ liệu: ", TEXT(TODAY(), "dd/mm/yyyy")) Ví dụ 1: Thêm những số 0 đứng trước mỗi con số trong một cột Bạn có thể dùng hàm TEXT để thêm số 0 đứng trước các con số trong một cột. Những con số 0 này cần phải tương ứng với đơn vị số được hiển thị. Lưu ý: Dữ liệu trả về của hàm TEXT là chuỗi văn bản, bạn không thể dùng dữ liệu này để làm các phép tính trong Excel nữa. Ví dụ 2: Thêm số 0 vào trước dữ liệu số điện thoại đã bị Excel xóa mất số 0 đầu tiên Cách này thường áp dụng khi bạn thu về một bảng dữ liệu số điện thoại nhưng bị mất số 0 đầu tiên (Excel hiểu số điện thoại là một dãy ký tự số bình thường, do đó sẽ loại đi số 0 dư thừa) gây khó khăn trong quá trình xem và thu thập dữ liệu. Lưu ý: Cách làm này chỉ áp dụng được khi số lượng chữ số trong số điện thoại của tất cả ô dữ liệu đều bằng nhau (ví dụ: Data dữ liệu của bạn chỉ chứa số điện thoại có 10 chữ số). Hàm TEXT trong Excel cho phép bạn sử dụng dấu gạch ngang (-) và dấu ngoặc đơn () trong mã định dạng để tạo thành những chuỗi văn bản có định dạng phù hợp với mục đích sử dụng của mình. Với số điện thoại có chứa mã vùng, bạn có thể sử dụng mã định dạng như hình dưới để thiết lập định dạng số điện thoại dạng: (123) 456-7890. Bên cạnh đó, bạn có thể chuyển số điện thoại 335263321 (tại ô C2) thành 0335 263 321 cho phù hợp với cách xem của người Việt nhờ nối số "0" với hàm TEXT: ="0"&TEXT(C2, "### ### ###") Ở đây, bạn có thể sử dụng dấu cách để phân cách các nhóm số. Trong quá trình thao tác với hàm TEXT, bạn có thể gặp một số lỗi sau: - Nguyên nhân: Người dùng không dùng dấu ngoặc kép ("") ở mã định dạng. Ví dụ công thức =TEXT(D2, mm/dd/yy) là công thức sai, hàm trả về lỗi #NAME? Cách khắc phục: Thêm dấu ngoặc kép vào mã định dạng, thành: =TEXT(D2, "mm/dd/yy") - Nguyên nhân 1: Bạn nhập sai cú pháp, hoặc khi bạn copy đoạn công thức từ nguồn khác về, dấu ngoặc kép (") bị sai định dạng, khiến Excel không thể thực hiện lệnh. Hàm sẽ báo Lỗi công thức (There's a problem with this formula). Cách khắc phục: Xóa dấu ngoặc kép cũ có trong công thức và tiến hành nhập tay lại. - Nguyên nhân 2: Do sự khác nhau trong thiết lập phần mềm Excel giữ các nước Một số nước có thiết lập phần mềm Excel để tối ưu việc sử dụng cho công việc theo ngôn ngữ của nước đó, điển hình là nước Đức. Ví dụ trong trường hợp dùng Excel tại Đức, thay vì nhập công thức =TEXT(A1, “mm/dd/yy”), người dùng bản Excel phát hành ở Đức sẽ cần nhập “t” thay cho “d” và “j” thay cho “y”. Như vậy, công thức vừa đề cập sẽ được thay bằng =TEXT(A1; “mm/tt/jj”). Cách khắc phục: Bạn có thể tìm hiểu để khai báo cú pháp hàm theo thiết lập ở phiên bản Excel mình đang sử dụng, hoặc đổi sang dùng một phiên bản Excel khác gần gũi hơn với ngôn ngữ mình sử dụng. Tham khảo một số mẫu laptop kinh doanh tại Điện máy XANH: Xem thêm: Trên đây là hướng dẫn cách dùng hàm TEXT trong Excel chuyển đổi dữ liệu số thành chuỗi văn bản và định dạng theo ý muốn. Hy vọng bài viết sẽ giúp ích cho bạn trong việc thao tác với dữ liệu Excel để tăng hiệu quả làm việc nhé! 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. Trường hợp 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 coppha nhôm, mình thường xuyên dùng tới. Ở đây chúng ta có bài toán như hình dưới: Trong cột Kết quả chúng ta cần dùng đúng công thức thể hiện ở cột Text. Ví dụ kết quả của 1000*5=5000 sau khi thực hiện lệnh. Với cách thông thường, bạn sẽ dùng tính năng Flash Fill trong Excel 2013, 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 một cách nhanh hơn nhiều. Chuyển Text thành công thức tính Formulas trong ExcelChỉ 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. Bằng cách: Bước 1Mở 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 2Sau khi mở VBA, click menu Insert chọn Module Bước 3Copy text bên dưới và paste vào khung soạn thảo (code): Function Eval(Ref As String) Bước 4Lư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:
Chúc bạn thành công! Xem thêm: Small App ghép nhiều file Excel thành một file, nối nhiều sheet thành một sheet |