Hãy cùng tìm hiểu cách sử dụng hàm định dạng số trong Excel ở bài viết bên dưới nhé!
Nội dung bài viết
HÀM TEXT TRONG EXCEL – CÔNG THỨC VÀ CÁCH SỬ DỤNG CƠ BẢN
Hàm Text được dùng để chuyển đổi giá trị số thành chuỗi văn bản ở định dạng cụ thể.
Công thức như sau: TEXT(giá trị, định dạng_văn bản)
Cụ thể:
- Giá trị: là giá trị số cần được chuyển sang văn bản. Đó có thể là số, ngày tháng, hoặc là tham chiếu của một ô có chứa giá trị số hoặc là một hàm khác trả về kết quả là số hoặc ngày.
- Định dạng văn bản: là định dạng cần dùng, cung cấp thể thức của mã định dạng được đặt nằm trong dấu ngoặc kép, ví dụ như “mm/dd/yy”.
Hàm Text thích hợp với toàn bộ những phiên bản Excel 2016, Excel 2013, 2010, 2007, 2003 và thấp hơn.
Hàm Text trong Excel được dùng trong những trường hợp sau đây:
- Hiển thị số theo một cách dễ đọc hơn hoặc là theo một định dạng dễ dàng hơn cho người dùng.
- Hiển thị ngày tháng theo định dạng nhất định
- Kết hợp số hoặc ngày tháng cùng với văn bản hoặc là các kí tự nhất định.
Ví dụ như để kéo ngày từ ô A2 sang ô khác theo định dạng truyền thống, giống như “Jan 1, 2016”, bạn có thể dùng công thức sau:
Kết quả trả về lúc này sẽ là:
Một lưu ý quan trọng đó là: hàm Text trong Excel được sử dụng để chuyển đổi giá trị số sang thành chuỗi văn bản. Vì vậy, kết quả của hàm Text đưa ra không thể dùng trong những phép tính khác. Nếu muốn, bạn có thể giữ nguyên giá trị gốc, rồi dùng công thức khác.
MÃ ĐỊNH DẠNG HÀM TEXT TRONG EXCEL
Như bạn đã biết, công thức hàm Text khá đơn giản. Mỗi phần sẽ được cấp một mã định dạng phù hợp, sau đó kết quả trả về số theo một cách mong muốn. Về cơ bản thì hàm Text chấp nhận đa số những định dạng mã được dùng trong định dạng Excel. Bảng dưới đây liệt kê các mã định dạng cơ bản nhất.
| Mã | Mô tả | Định dạng mã mẫu |
| 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 |
| . | Điểm thập phân | |
| , | 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 |
Cùng với đó, bạn cũng có thể dùng cùng với bất kì kí tự nào dưới đây ở mã định dạng, kết quả thể hiện được sẽ vẫn giữ nguyên kí tự
| + và – | Dấu cộng và trừ |
| () | Dấu ngoặc đơn |
| : | Dấu : |
| ‘ | Dấu nháy |
| {} | Dấu ngoặc nhọn |
| < > | Nhỏ hơn hoặc lớn hơn |
| = | Dấu bằng |
| / | Dấu gạch chéo |
Bảng dưới đây chỉ ra cách dùng công thức hàm Text trong Excel để ứng dụng vào những định dạng khác nhau cho ra giá trị tương đương.
| A | B | C | |
| 1 | Giá trị gốc | Giá trị định dạng | Công thức |
| 2 | 5.5 | 5.50 | =TEXT(A2, “0.00”) |
| 3 | 550% | =TEXT(A2,”#%”) | |
| 4 | $5.50 | =TEXT(A2, “$#,##0.00” | |
| 5 | +$5.50 | =TEXT(A2,”+$#,##0.00;-$#,##0.00;$0.00″) | |
| 6 | -$5.50 | =TEXT(A2, “- $#,##0.00;- $#,##0.00;$0.00”) | |
| 7 | 5 ½ | =TEXT(A2,”# ?/?”) | |
| 8 | 5.50E+00 | =TEXT(A2,”0.00E+00″) | |
| 9 | ~6! | =TEXT(A2,”~# !”) |
Khi dùng hàm TEXT trong Excel với ngày và giờ, bạn có thể dùng mã định dạng sau:
| Mã định dạng | Mô tả | Ví dụ |
| 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à 2016)
yyyy – 4 chữ số (2006,2016) |
| 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) |
Bảng tính bên dưới thể hiện một vài công thức hàm Text cho ngày:
| A | B | C | |
| 1 | Ngày gốc | Ngày được định đạng | Công thức |
| 2 | 12/9/2016 | Dec 09 2016 | =TEXT(A2, “mmm dd yyyy”) |
| 3 | Friday 09 December, 2016 | =TEXT(A2, “dddd dd mmmm, yyyy”) | |
| 4 | 9-Dec-16 | =TEXT(A2, “d-mmm-yy”) | |
| 5 | Friday | =TEXT(A2,”dddd”) |
CÁCH SỬ DỤNG HÀM TEXT TRONG EXCEL – CÔNG THỨC MẪU
Ứng dụng của hàm Text ở Excel thường còn khá hạn chế (vì giá trị hiển thị có thể dễ dàng được thay đổi bằng việc sử dụng hộp Định dạng ô có sẵn thông qua các chương trình lập trình ô hoặc là 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 tiến hành tạo tóm tắt hoặc là báo cáo, người dùng thường không chỉ muốn thực hiện tính tổng trong Excel, phần trăm và các phần tương tự, mà còn giải thích cho người sử dụng ý nghĩa của chúng. Để thực hiện được việc này, bạn sẽ sử dụng hàm Concatenate để tiến hành nhập văn bản và số, và dùng hàm Text để thể hiện số (hay ngày) tùy theo ý muốn của người dùng.
Ví dụ 1. Thực hiện định dạng số bên trong một chuỗi văn bản
Ví dụ như, bạn cần thực hiện tính tổng số lượng dựa trên giá của đối tượng trong ô A2, cũng như số lượng đối tượng trong ô B2, và giảm tỉ lệ trong ô C2 dùng phép tính: =A2*B2*(1-C2). Đối với người dùng biết chính xác ý nghĩa của kết quả trả về, và người dùng muốn chèn thêm câu giải thích ví dụ như: “Your price is” – “giá của bạn là”. Cùng với đó, người dùng cũng muốn hiển thị biểu tượng tiền tệ, dấu phân cách hàng nghìn cũng như hai chữ số thập phân.
Để hoàn thành được yêu cầu này, bạn hãy bổ sung công thức tính vào trong đối số thứ nhất của hàm Text, gồm mã định dạng tương ứng trong đối số thứ 2, và thực hiện nối hàm Text với chuỗi bằng việc dùng hệ thống kí hiệu hoặc là hàm Concatenate:
=“Your price is “&TEXT(A2*B2*(1-C2), “$###,###.00”)
Hoặc là
= CONCATENATE(“Your price is “,TEXT(A2*B2*(1-C2), “$###,###.00”))
Kết quả trả sẽ như bảng sau:
Ví dụ 2: Thực hiện kết hợp văn bản và ngày tháng theo như định dạng mong muốn
Khi quay lại ngày hiện tại bằng việc dùng hàm Today() hay Now, có thể bạn sẽ muốn biểu diễn ngày bằng việc kết hợp với một vài câu, hay đoạn để có thể tránh gây nghi ngờ hay là nhầm lẫn.
Tuy vậy, nếu như người dùng muốn nối văn bản với ngày tháng theo như cách thông thường, hãy sử dụng công thức sau:
=CONCATENATE(“Today is “, TODAY())
Excel lúc này sẽ trả về kết quả rất lạ, chẳng hạn như “Today is 42198”.
Nguyên nhân ở đây là do hệ thông lập trình của Excel, ngày tháng được lưu bằng những con số. Chính vì vậy, những con số này xuất hiện trong chuỗi văn bản nối. Để có thể khắc phục tình trạng nay, bạn hãy dùng hàm Text để biểu diễn ngày tháng theo ý muốn của mình.
Ví dụ, thực hiện gắn hàm Today vào trong công thức hàmText có chứa mã định dạng “dddd d mmm,yyyy”, kết quả trả về lúc này sẽ là: “Today is Monday 12 Dec, 2016”
Công thức tổng quát sẽ như sau:
=CONCATENATE(“Today is “, TEXT(TODAY(), “dddd d mmm, yyyy”))
hoặc là
=”Today is ” & TEXT(TODAY(), “dddd d mmm, yyyy”)
Xem thêm: Hướng dẫn cách dùng hàm SUMIF trong Excel
THÊM SỐ 0 ĐỨNG ĐẦU MỖI SỐ VỚI ĐỘ DÀI NGẮN KHÁC NHAU
Microsoft Excel sẽ tự động xóa các số 0 đứng đầu mỗi con số trong một ô, việc này sẽ không có ảnh hưởng gì trong đa số các trường hợp. Tuy vậy, nếu người sử dụng muốn giữ nguyên các số 0 đứng trước này thì sẽ làm thế nào?
Hàm Text trong Excel có thể thêm các số 0 đứng trước các con số trong một cột khá dễ dàng, ngay cả khi các con số đó không có giá trị tương đương. Cách làm rất đơn giản, bạn dùng mã định dạng chứa các số 0 vào đầu như “0000000”, trong đó, các số 0 phải tương ứng với đơn vị số đã được hiển thị.
Ví dụ như để thực hiện biểu diễn số có 7 chữ số, trong đó số 0 đứng đầu, bạn sử dụng công thức sau: (A2 là ô chứa giá trị gốc)
=TEXT(A2,”0000000″)
Ở bảng bên dưới, công thức hàm Text đã thêm số 0 vào đứng đầu theo yêu cầu để có thể tạo chuỗi 7 kí tự. (Lưu ý rằng kết quả trả về của hàm Text luôn là chuỗi văn bản, ngay cả lúc kết quả trả về tưởng chừng như là số).
CHUYỂN GIÁ TRỊ THÀNH SỐ ĐIỆN THOẠI THEO MỘT ĐỊNH DẠNG NHẤT ĐỊNH
Thực hiện chuyển giá trị số trong ô thành các số điện thoại có vẻ được xem là một nhiệm vụ khó nhằn. Tuy vậy, bạn hãy nhớ rằng hàm Text cho phép dùng dấu gạch ngang (-) và dấu ngoặc đơn () nằm trong mã định dạng.
Chính vì vậy, để có thể biểu diễn số trong ô A2 theo dạng số điện thoại có 7 chữ số truyền thống theo định dạng 123-456, bạn hãy sử dụng công thức:
=TEXT(A2, “###-####”)
Nếu như một vài giá trị gốc có chứa mã khi vực (VD: có thể đó là 7 chữ số, hoặc là 10 chữ số), gồm mã định dạng có điều kiện bên dưới đây để hiển thị số có 10 chữ số ở định dạng (123) 246-789:
=TEXT(A2,”[<=9999999]###-####;(###) ###-####”)
Công thức thực hiện sẽ là:
HÀM TEXT TRONG EXCEL KHÔNG THỰC HIỆN ĐƯỢC – NGUYÊN NHÂN VÀ CÁCH KHẮC PHỤC
So với những hàm khác trong Excel, thì hàm Text đơn giản hơn rất nhiều. Nếu như trong trường hợp hàm Text không thực hiện được, thì lí do của hầu hết các trường hợp đó là do sai mã định dạng. Bên dưới đây là 2 trường hợp điển hình:
- Hàm Text trả về lỗi #NAME? nếu như bạn không sử dụng dấu ngoặc kép “” bên ngoài mã định dạng
Ví dụ như công thức =TEXT(A2, mm/dd/yy) bị lỗi, thì bạn nên sửa thành: =TEXT(A2, “mm/dd/yy”)
- Hàm Text trong Excel có ngôn ngữ đặc trưng, sẽ yêu cầu có mã định dạng ngày và giờ cụ thể.
Ví dụ như công thức =TEXT(A2, “mm/dd/yy”) hoạt động tốt với những ai dùng tiếng Anh, nhưng các vùng khác thì không. Điển hình là ở Đức, người dùng sẽ cần sử dụng định dạng sau đây: “t” thay cho “d” và “j” thay cho “y”. Chính vì vậy, trong phần mềm Excel của Đức, thì công thức bên trên sẽ được thay bằng =TEXT(A2; “mm/tt/jj”).
Hi vọng những thông tin về cách sử dụng hàm định dạng số trong Excel trên đây sẽ giúp ích được nhiều cho bạn, chúc bạn thực hiện thành công!
Xem thêm: Hướng dẫn cách sử dụng hàm SUBTOTAL trong Excel
