Hàm Text Và Các Ứng Dụng Tuyệt Vời Của Hàm Text Trong Excel
Trong bài viết này, Blog Học Excel Online sẽ giải thích các đặc trưng của hàm Text trong Excel và chỉ ra những cách tối ưu để sử dụng công thức hàm Text trong Excel.
HÀM TEXT TRONG EXCEL - CÔNG THỨC VÀ CÁCH SỬ DỤNG CƠ BẢN
Hàm Text được sử dụng để chuyển đổi giá trị số sang chuỗi văn bản ở một định dạng cụ thể.
Công thức: TEXT(giá trị, định dạng_văn bản)
Cụ thể:
- Giá trị: giá trị số cần chuyển sang văn bản. Có thể là số, ngày tháng, là tham chiếu của một ô chứa giá trị số hay một hàm khác trả về kết quả là số hay ngày.
- Định dạng văn bản: định dạng cần sử dụng, cung cấp thể thức của mã định dạng được đặt trong dấu ngoặc kép, như "mm/dd/yy".
Hàm Text thích hợp với tất cả các phiên bản Excel 2024, Excel 2013, 2010, 2007, 2003 và thấp hơn.
Thông thường, hàm Text trong Excel được sử dụng trong các trường hợp sau đây:
- Hiển thị số theo cách dễ đọc hơn hoặc theo một định dạng dễ dàng hơn cho người sử dụng.
- Hiển thị ngày tháng theo một định dạng nhất định
- Kết hợp các số hoặc ngày tháng với văn bản hoặc các kí tự nhất định.
Ví dụ, để kéo ngày từ ô A2 sang ô khác theo định dạng truyền thống như "Jan 1, 2024", sử dụng công thức sau:
=TEXT(A2, "mmmm d, yyyy")
Kết quả trả về:
MÃ ĐỊNH DẠNG HÀM TEXT TRONG EXCEL
0
Chữ số thể hiện những số 0 vô nghĩa
#.00 tính đến số thập phân thứ 2.
Nếu nhập 2.5. trong ô tham chiếu, số hiển thị là 2.50
#
Dấu ngăn không biểu diễn thêm số 0
#.## - tính đến số thập phân thứ 2
Nếu nhập 2.5, số hiển thị là 2.5
Nếu nhập 2.5555, số hiển thị là 2.56
?
Dấu để khoảng trống cho số 0 vô nghĩa nhưng không biểu diễn chúng. Thường được dùng để sắp xếp các số trong cột tại một điểm thập phân
#.?? Biểu diễn tối đa 2 phần thập phân và căn nhập cho các điểm thập phân trong một cột
,
Ngăn cách hàng nghìn
###,###.## biểu hiện dấu phân tách hàng nghìn và 2 số thập phân
Khi sử dụng hàm TEXT trong Excel với ngày và giờ, có thể sử dụng mã định dạng sau:
d (day)
Ngày của tháng hoặc ngày của năm
d - một hoặc 2 số không chứa số 0 ở đầu (từ 1 đến 31)
dd - 2 số chứa số 0 ở đầu (01 đến 31)
ddd - 3 kí tự viết tắt (Mon - Sun (thứ 2 đến chủ nhật)
m (month)
Tháng (được sử dụng như một phần của ngày)
m - một hoặc 2 số không chứa số 0 ở đầu (1 đến 12)
mm - 2 số chứa số 0 ở đầu (01 - 12)
mmm - viết tắt của tháng (Jan - Dec)
mmmm - chữ đầy đủ của tháng
y (year)
Năm
yy- một hoặc 2 số (ví dụ 06 là 2006, hay 16 là 2024)
yyyy - 4 chữ số (2006,2024)
h (hour)
Giờ
h - một hoặc hai chữ số không chứa số 0 ở đầu (1-60)
hh -2 chữ số chứa số 0 ở đầu (01-60)
m (minute)
Phút
m - một hoặc hai chữ số không chứa số 0 ở đầu (1-60)
mm -2 chữ số chứa số 0 ở đầu (01-60)
S (second)
giây
s - một hoặc hai chữ số không chứa số 0 ở đầu (1-60)
ss -2 chữ số chứa số 0 ở đầu (01-60)
Trang tính dưới thể hiện một số công thức hàm Text cho ngày:
CÁCH SỬ DỤNG HÀM TEXT TRONG EXCEL - CÔNG THỨC MẪU
Có thể thấy, ứng dụng của hàm Text trong Excel còn khá hạn chế (bởi giá trị hiển thị có thể dễ dàng thay đổi bằng cách sử dụng hộp Định dạng ô có sẵn thông qua chương trình lập trình ô hoặc phím tắt Ctrl+1.
KẾT NỐI VĂN BẢN VÀ SỐ (HOẶC NGÀY) THEO ĐỊNH DẠNG TÙY CHỈNH
Khi tạo tóm tắt hoặc báo cáo, người dùng thường không chỉ muốn tính tổng, phần trăm và những phần tương tự, mà còn giải thích cho người dùng ý nghĩa của chúng. Để thực hiện, sử dụng hàm Concatenate để nhập văn bản và số, và sử dụng hàm Text để thể hiện số (hay ngày) theo ý muốn của người dùng.
Ví dụ 1. Định dạng số bên trong một chuỗi văn bản
Giả sử, cần tính tổng số lượng dựa trên giá của đối tượng trong ô A2, số lượng đối tượng trong ô B2, và giảm tỉ lệ trong ô C2 sử dụng phép tính: =A2*B2*(1-C2). Đối với người sử dụng biết chính xác ý nghĩa của kết quả trả về, người sử dụng muốn chèn thêm câu giải thích như: "Your price is" - "giá của bạn là". Cùng đó, người dùng muốn hiển thị 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.
Để hoàn thành, bổ sung công thức tính vào đối số thứ nhất của hàm Text, bao gồm mã định dạng tương ứng trong đối số thứ 2, và nối hàm Text với chuỗi bằng cách sử dụng hệ thống kí hiệu hoặc hàm Concatenate:
="Your price is "&TEXT(A2*B2*(1-C2), "$###,###.00")
Hoặc
= CONCATENATE("Your price is ",TEXT(A2*B2*(1-C2), "$###,###.00"))
Kết quả trả về tương tự bảng sau:
Khi quay lại ngày hiện tại bằng cách sử dụng hàm Today() hay Now, có thể người dùng muốn biểu diễn ngày bằng cách kết hợp với một vài câu, hay đoạn để tránh gây nghi ngờ hay nhầm lẫn.
Tuy nhiên, nếu người dùng muốn nối văn bản với ngày tháng theo cách thông thường:
=CONCATENATE("Today is ", TODAY())Excel sẽ trả về kết quả rất lạ, như "Today is 42198".
Đó là do hệ thông lập trình của Excel, ngày tháng được lưu bằng các con số. Bởi vậy, các con số này xuất hiện trong chuỗi văn bản nối. Để khắc phục tình trạng nay, sử dụng hàm Text để biểu diễn ngày tháng theo ý muốn của người dùng.
Ví dụ, gắn hàm Today trong công thức hàmText có mã định dạng "dddd d mmm,yyyy", kết quả trả về sẽ là: "Today is Monday 12 Dec, 2024"
Công thức tổng quát như sau:
=CONCATENATE("Today is ", TEXT(TODAY(), "dddd d mmm, yyyy"))
or
="Today is " & TEXT(TODAY(), "dddd d mmm, yyyy")
Microsoft Excel tự động xóa những số 0 đứng đầu mỗi con số trong một ô, điều này không có ảnh hưởng gì trong hầu hết các trường hợp. Tuy nhiên, nếu người dùng muốn giữ nguyên những số 0 đứng trước này thì sao?
Hàm Text trong Excel có thể dễ dàng thêm những số 0 đứng trước những con số trong một cột, ngay cả khi những con số đó không có giá trị tương đương. Đơn giản bằng cách dùng mã định dạng chưa những số 0 vào đầu như "0000000", trong đó, những số 0 phải tương ứng với đơn vị số được hiển thị.
Ví dụ, để biểu diễn số có 7 chữ số với số 0 đứng đầu, dùng công thức sau: (A2 là ô chứa giá trị gốc)
Chuyển giá trị số trong ô thành những số điện thoại có vẻ là một nhiệm vụ khó nhằn, tuy nhiên, hãy nhớ rằng hàm Text trong Excel cho phép sử dụng dấu gạch ngang (-) và dấu ngoặc đơn () trong mã định dạng.
Bởi vậy, để biểu diễn số trong ô A2 theo số điện thoại 7 chữ số truyền thống theo định dạng 123-456, sử dụng công thức:
=TEXT(A2, "###-####")
Nếu một vài giá trị gốc chứa mã khi vực (VD: có thể là 7 chữ số, hay 10 chữ số), bao gồm mã định dạng có điều kiện sau đây để hiển thị số có 10 chữ số theo định dạng (123) 246-789:
=TEXT(A2,"[<=9999999]###-####;(###) ###-####")
Công thức thực hiện như sau:
- Hàm Text trả về lỗi #NAME? nếu người dùng không dùng dấu ngoặc kép "" ngoài mã định dạng
Ví dụ công thức =TEXT(A2, mm/dd/yy) bị lỗi, nên sửa thành: =TEXT(A2, "mm/dd/yy")
- Hàm Text trong Excel có ngôn ngữ đặc trưng, yêu cầu có mã định dạng ngày và giờ cụ thể.
Ví dụ, công thức =TEXT(A2, "mm/dd/yy") hoạt động tốt với người dùng sử dụng tiếng Anh, nhưng những vùng khác thì không. Điển hình, ở Đức, người dùng cần sử dụng định dạng sau: "t" thay cho "d" và "j" thay cho "y". Bởi vậy, trong phần mềm Excel của Đức, công thức bên trên sẽ thay bằng =TEXT(A2; "mm/tt/jj").
Ngoài ra để ứng dụng Excel vào công việc một cách hiệu quả thì bạn còn phải sử dụng tốt các hàm, các công cụ khác của Excel:
Một số hàm cơ bản thường gặp như:
- SUMIF, SUMIFS để tính tổng theo 1 điều kiện, nhiều điều kiện
- COUNTIF, COUNTIFS để thống kê, đếm theo một điều kiện, nhiều điều kiện
- Các hàm xử lý dữ liệu dạng chuỗi, dạng ngày tháng, dạng số...
- Các hàm dò tìm tham chiếu Index+Match, hàm SUMPRODUCT...
Một số công cụ hay sử dụng như:
- Định dạng theo điều kiện với Conditional formatting
- Thiết lập điều kiện nhập dữ liệu với Data Validation
- Cách đặt Name và sử dụng Name trong công thức
- Lập báo cáo với Pivot Table...