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, 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 để quy đổi giá trị số sang chuỗi văn bản ở một định dạng đơn cử .

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 toàn bộ những phiên bản Excel năm nay, Excel 2013, 2010, 2007, 2003 và thấp hơn .
Thông thường, hàm Text trong Excel được sử dụng trong những 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 cuội nguồn như “ Jan 1, năm nay ”, sử dụng công thức sau :

=TEXT(A2, “mmmm d, yyyy”)

Kết quả trả về :

Lưu ý quan trọng : hàm Text trong Excel được dùng để chuyển giá trị số sang chuỗi văn bản. Vì vậy, hiệu quả hàm Text đưa ra không hề sử dụng trong những phép tính khác. Nếu muốn, hoàn toàn có thể giữ nguyên giá trị gốc và dùng công thức khác .

MÃ ĐỊNH DẠNG HÀM TEXT TRONG EXCEL

Như đã thấy, công thức hàm Text rất đơn thuần. Mỗi phần được cấp một mã định dạng tương thích và tác dụng trả về số theo cách mong ước. Về cơ bản, hàm Text đồng ý hầu hết những định dạng mã được sử dụng trong định dạng Excel. Bảng dưới đây liệt kê những 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 đó, hoàn toàn có thể sử dụng cùng với bất kỳ kí tự nào dưới đây trong mã định dạng, tác dụng biểu lộ được 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ỉ cách sử dụng công thức hàm Text trong Excel để ứng dụng những định dạng khác nhau ra giá trị tương tự .

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 sử dụng hàm TEXT trong Excel với ngày và giờ, hoàn toàn có thể sử 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ữ vừa đủ 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 )
Trang tính dưới bộc lộ 1 số ít 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

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ị hoàn toàn có thể thuận tiện biến hóa bằng cách sử dụng hộp Định dạng ô có sẵn trải 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 trong Excel, 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 người dùng trong ô A2, số lượng đối tượng người dù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 đúng mực ý nghĩa của hiệu quả trả về, người sử dụng muốn chèn thêm câu lý giải như : “ Your price is ” – “ giá của bạn là ”. Cùng đó, người dùng muốn hiển thị hình tượng tiền tệ, dấu ngăn cách hàng nghìn và hai chữ số thập phân .
Để triển khai xong, bổ trợ công thức tính vào đối số thứ nhất của hàm Text, gồm có 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 mạng lưới 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ự như bảng sau :

Ví dụ 2 : Kết hợp văn bản và ngày tháng theo định dạng mong ước
Khi quay lại ngày hiện tại bằng cách sử dụng hàm Today ( ) hay Now, hoàn toàn có thể người dùng muốn trình diễn ngày bằng cách tích hợp với một vài câu, hay đoạn để tránh gây hoài nghi 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ì :

=CONCATENATE(“Today is “, TODAY())

Excel sẽ trả về hiệu 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 những số lượng. Bởi vậy, những số lượng này Open trong chuỗi văn bản nối. Để khắc phục thực trạng nay, sử dụng hàm Text để màn 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 ”, tác dụng trả về sẽ là : “ Today is Monday 12 Dec, năm nay ”
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”)

THÊM SỐ 0 ĐỨNG ĐẦU MỖI SỐ VỚI ĐỘ DÀI NGẮN KHÁC NHAU

Microsoft Excel tự động hóa xóa những số 0 đứng đầu mỗi số lượng trong một ô, điều này không có tác động ảnh hưởng gì trong hầu hết những 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 hoàn toàn có thể thuận tiện thêm những số 0 đứng trước những số lượng trong một cột, ngay cả khi những số lượng đó không có giá trị tương tự. Đơ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ị chức năng số được hiển thị .
Ví dụ, để màn 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 )

=TEXT(A2,”0000000″)

Trong bảng dưới đây, công thức hàm Text thêm đã thêm số 0 đứng đầu theo nhu yếu để tạo chuỗi 7 kí tự. ( Lưu ý : hiệu quả trả về của hàm Text trong Excel luôn là chuỗi văn bản, ngay cả khi hiệu quả trả về tưởng chừng là số ) .

CHUYỂN GIÁ TRỊ THÀNH SỐ ĐIỆN THOẠI THEO MỘT ĐỊNH DẠNG NHẤT ĐỊNH

Chuyển giá trị số trong ô thành những số điện thoại thông minh có vẻ như là một trách nhiệm khó nhằn, tuy nhiên, hãy nhớ rằng hàm Text trong Excel được 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, để màn biểu diễn số trong ô A2 theo số điện thoại cảm ứng 7 chữ số truyền thống lịch sử 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 : hoàn toàn có thể là 7 chữ số, hay 10 chữ số ), gồm có mã định dạng có điều kiện kèm theo sau đây để hiển thị số có 10 chữ số theo định dạng ( 123 ) 246 – 789 :
= TEXT ( A2, ” [ < = 9999999 ] # # # - # # # # ; ( # # # ) # # # - # # # # ” ) Công thức triển khai như sau :

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, hàm Text đơn thuần hơn rất nhiề, tưởng như không có chút khó khăn vất vả nào cản trở. Nếu trong trường hợp hàm Text không thực thi được, lí do của hầu hết trường hợp là do sai mã định dạng. Dưới đây là 2 trường hợp nổi bật :

1. 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 ” )

2. 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 giải trí 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 ứng dụng Excel của Đức, công thức bên trên sẽ thay bằng = TEXT ( A2 ; “ mm / tt / jj ” ) .
Trên đây là bài giảng về hàm Text trong Excel, chúc những bạn thành công xuất sắc !

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…

Rất nhiều kiến thức và kỹ năng phải không nào ? Toàn bộ những kỹ năng và kiến thức này những bạn đều hoàn toàn có thể học được trong khóa học EX101 – Excel từ cơ bản tới chuyên viên của Học Excel Online. Đây là khóa học giúp bạn mạng lưới hệ thống kiến thức và kỹ năng một cách vừa đủ, chi tiết cụ thể. Hơn nữa không hề có số lượng giới hạn về thời hạn học tập nên bạn hoàn toàn có thể tự do học bất kỳ khi nào, thuận tiện tra cứu lại kỹ năng và kiến thức khi cần. Hiện nay mạng lưới hệ thống đang có khuyến mại rất lớn cho bạn khi ĐK tham gia khóa học. Chi tiết xem tại : HocExcel. Online

Source: http://wp.ftn61.com
Category: Hỏi Đáp