Ứng dụng hữu ích của Hàm Text trong Excel

Siêu Marketing sẽ trình bày về tính năng của hàm Text trong Excel và các cách hiệu quả để áp dụng công cụ này trong bảng tính.

TÌM HIỂU VỀ HÀM TEXT VÀ CÁCH DÙNG CƠ BẢN TRONG EXCEL

Công dụng của hàm Text là biến đổi giá trị số thành dạng chuỗi ký tự theo định dạng cố định nào đó.

Dạng công thức: TEXT(giá_trị, định_dạng_text)

Chi tiết về các tham số:

  • Giá trị: là giá trị số cần chuyển đổi qua chuỗi. Có thể là một con số, ngày tháng năm, tham chiếu tới một ô chứa giá trị số hoặc là kết quả từ một hàm khác mang giá trị số hoặc ngày tháng.
  • Định dạng văn bản: là định dạng mong muốn, được biểu diễn bằng mã định dạng nằm trong dấu nháy kép, ví dụ “mm/dd/yy”.

Excel phiên bản 2016, 2013, 2010, 2007, 2003 và trước đó đều tương thích với hàm Text.

Hàm Text thường được sử dụng trong các tình huống như:

  • Hiển thị các số theo kiểu dễ cảm nhận hoặc theo định dạng tiện lợi hơn cho người dùng.
  • Định dạng ngày tháng theo một chuẩn cụ thể.
  • Kết hợp số hoặc ngày tháng với văn bản hay ký tự đặc biệt nào đó.

Chẳng hạn, muốn chiết xuất ngày từ ô A2 sang ô khác theo định dạng quen thuộc như “Jan 1, 2016”, bạn có thể dùng công thức dưới đây:

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

Hiển thị kết quả như sau:

Chú ý: Hàm Text trong Excel chỉ chuyển đổi giá trị số thành văn bản. Do đó, bạn không thể dùng kết quả từ hàm Text để tính toán. Nếu cần thiết, bạn cần giữ lại giá trị số gốc và sử dụng một công thức khác.

MÃ ĐỊNH DẠNG SỬ DỤNG TRONG HÀM TEXT CỦA EXCEL

Hàm Text sử dụng đơn giản với các mã định dạng tương ứng. Hầu như tất cả mã định dạng mà Excel hỗ trợ đều có thể áp dụng được trong hàm Text. Dưới đây là bảng liệt kê những mã định dạng cơ bản:

Diễn giải Thí dụ mã định dạng
0 Chữ số hiển thị số không quan trọng #.00 làm tròn tới hai chữ số sau dấu phẩy.

Khi giá trị nhập là 2.5, hiển thị sẽ là 2.50

# Dấu phẩy để loại bỏ số không cần thiết #.## làm tròn tới hai chữ số sau dấu phẩy

Nếu giá trị nhập vào là 2.5 thì hiển thị vẫn là 2.5

Nếu giá trị là 2.5555 thì hiển thị sẽ là 2.56

? Dấu cách thay cho số 0 không quan trọng nhằm duy trì độ rộng không gian. Thường được sử dụng để căn các con số trong một cột tại điểm thập phân chung #.?? dùng để biểu diễn không quá 2 phần thập phân và căn chỉnh số tại điểm thập phân cho một cột
. Chấm biểu thị điểm thập phân
, Dấu phân cách phần nghìn ###,###.## cho thấy dấu phân cách hàng nghìn và hai chữ số sau dấu phẩy

Bạn cũng có thể sử dụng các ký tự dưới đây trong mã định dạng, nó sẽ không thay đổi khi được hiển thị

+ và – Biểu thị dấu cộng và trừ
() Sử dụng dấu ngoặc đơn
: Dấu hai chấm
Dấu nháy đơn
{} Dấu ngoặc nhọn
< > Nhỏ hơn và lớn hơn
= Dấu bằng
/ Sử dụng dấu gạch chéo

Bảng sau đây hướng dẫn cách dùng công thức hàm Text trong Excel để hiện thị giá trị theo các định dạng đặc biệt.

A B C
1 Giá trị ban đầu Giá trị đã được định dạng Cú pháp
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 áp dụng hàm TEXT trong Excel để xử lý dữ liệu thời gian và ngày tháng, bạn có thể dùng các mã định dạng sau:

Mã định dạng Giải thích Thí dụ
d (ngày) Ngày trong tháng hoặc ngày trong năm d – từ 1 đến 2 chữ số không bắt đầu bằng 0 (1 đến 31)

dd – 2 chữ số bắt đầu bằng 0 (01 đến 31)

ddd – 3 chữ cái viết tắt (Mon – Sun)

m (tháng) Tháng (trong định dạng ngày) m – từ 1 đến 2 chữ số không bắt đầu bằng 0 (1 đến 12)

mm – 2 chữ số bắt đầu bằng 0 (01 – 12)

mmm – tên viết tắt của tháng (Jan – Dec)

mmmm – tên đầy đủ của tháng

y (năm) Năm yy- 2 chữ số (ví dụ: 06 là năm 2006, hay 16 là năm 2016)

yyyy – 4 chữ số chính xác của năm (2006, 2016)

h (giờ) Giờ h – từ 1 đến 2 chữ số không bắt đầu bằng 0 (1-12)

hh – 2 chữ số bắt đầu bằng 0 (01-12)

m (phút) Phút m – từ 1 đến 2 chữ số không bắt đầu bằng 0 (1-60)

mm – 2 chữ số bắt đầu bằng 0 (01-60)

s (giây) Giây s – từ 1 đến 2 chữ số không bắt đầu bằng 0 (1-60)

ss – 2 chữ số bắt đầu bằng 0 (01-60)

Bảng tính dưới đây minh họa một số cú pháp của hàm TEXT áp dụng cho ngày tháng:

A B C
1 Ngày nhập vào Ngày được biểu diễn theo định dạng Cú pháp
2 12/09/2016 12 Tháng 9 2016 =TEXT(A2, “mmm dd yyyy”)
3 Thứ Sáu ngày 09 Tháng 12 năm 2016 =TEXT(A2, “dddd dd mmmm, yyyy”)
4 9-Tháng 12-16 =TEXT(A2, “d-mmm-yy”)
5 Thứ Sáu =TEXT(A2,”dddd”)

HƯỚNG DẪN CÁCH SỬ DỤNG HÀM TEXT TRONG EXCEL – MẪU CÔNG THỨC

Bạn có thể quan sát, việc áp dụng hàm Text trong Excel có phần hạn chế do đã có sẵn chức năng định dạng ô thông qua giao diện lập trình ô hoặc sử dụng phím tắt Ctrl+1 để thay đổi giá trị hiển thị.

MỘT SỐ CÁCH GỘP VĂN BẢN VÀ SỐ LIỆU (HOẶC NGÀY THÁNG) THEO ĐỊNH DẠNG RIÊNG

Khi tạo biên bản hoặc báo cáo, ngoài việc tính tổng hoặc phần trăm trong Excel, điều người dùng cần là giải thích chúng cho người xem. Vậy nên, hãy sử dụng hàm Concatenate để ghép chữ và số, và hàm Text để biểu diễn số liệu hay ngày tháng theo định dạng mà người dùng mong muốn.

Ví dụ 1. Định dạng số trong một chuỗi văn bản

Cho rằng bạn muốn tính tổng số tiền theo giá sản phẩm tại ô A2, số lượng sản phẩm tại ô B2 và mức chiết khấu trong ô C2 với công thức tính: =A2*B2*(1-C2). Để người dùng hiểu rõ ý nghĩa của số tiền được tính, bạn muốn thêm vào một câu giải thích như sau:

“Giá của bạn là” – câu này được diễn đạt bằng tiếng Việt là “giá của bạn là”. Cùng với đó, người dùng mong muốn thấy được ký hiệu tiền tệ, dấu chia ngàn và hai phần thập phân.

Để thực hiện, hãy tích hợp công thức tính nhẩm vào tham số đầu tiên của hàm Text, chứa mã định dạng đáng lẽ ở tham số thứ hai, và liên kết hàm Text với chuỗi thông qua hệ thống các dấu hoặc dùng hàm Concatenate:

=“Giá của bạn là “&TEXT(A2*B2*(1-C2), “$###,###.00”)

Hoặc

= CONCATENATE(“Giá của bạn là “, TEXT(A2*B2*(1-C2), “$###,###.00”))

Kết quả thu được sẽ tương đương với bảng sau:

Ví dụ 2: Kết hợp thông tin về ngày tháng và văn bản theo định dạng yêu cầu

Nếu sử dụng hàm Today() hay Now để lấy ngày hiện tại, người dùng có thể muốn kết hợp ngày đó với một số câu từ nhằm tránh hiểu nhầm.

Tuy vậy, khi kết nối ngày tháng với văn bản qua cách thức thông thường:

=CONCATENATE(“Hôm nay là “, TODAY())

Excel sẽ cho ra một kết quả không mong muốn, ví dụ như “Hôm nay là 42198”.

Điều này xảy ra do cách lưu ngày tháng dưới dạng số của hệ thống Excel. Vì thế, các số này xuất hiện trong chuỗi văn bản được ghép lại. Để giải quyết vấn đề này, hãy dùng hàm Text để thể hiện ngày tháng như mong đợi của người dùng.

Ví dụ, sử dụng hàm Today cùng với công thức hàm Text và mã định dạng “dddd d mmm,yyyy”, bạn sẽ nhận được kết quả là: “Hôm nay là Thứ Hai ngày 12 tháng 12, 2016”

Dưới đây là công thức chung:

=CONCATENATE(“Hôm nay là “, TEXT(TODAY(), “dddd d mmm, yyyy”))
hoặc
=”Hôm nay là ” & TEXT(TODAY(), “dddd d mmm, yyyy”)

THÊM CÁC SỐ 0 TRƯỚC NHỮNG SỐ CÓ ĐỘ DÀI KHÁC NHAU

Excel thường xóa bỏ các số 0 đặt trước trong ô, điều này không ảnh hưởng trong hầu hết trường hợp. Nếu cần giữ lại những số 0 này, thì sao?

Hàm Text trong Excel cho phép bạn dễ dàng thêm các số 0 vào đầu của một dãy số trong cột, kể cả khi các con số này không có tính chất tương đương. Chỉ việc áp dụng mã định dạng với các số 0 phía trước như “0000000” – số lượng số 0 ứng với số lượng chữ số mong muốn hiển thị.

Ví dụ, để hiển thị một số bảy chữ số có số 0 đứng đầu, sử dụng công thức sau: (với A2 là ô chứa giá trị ban đầu)

=TEXT(A2,”0000000″)

Dưới đây là bảng minh họa công thức hàm Text đã thực hiện việc thêm số 0 như yêu cầu, tạo thành chuỗi 7 kí tự. (Chú ý: kết quả từ hàm Text trong Excel luôn là chuỗi văn bản, kể cả khi kết quả tưởng chừng như là một con số).

BIẾN ĐỔI GIÁ TRỊ THÀNH ĐỊNH DẠNG SỐ ĐIỆN THOẠI

Chuyển một giá trị số trong ô Excel thành định dạng số điện thoại có vẻ phức tạp, nhưng nhớ là hàm Text cho phép bao gồm dấu gạch ngang (-) và dấu ngoặc đơn () trong mã định dạng.

Vậy để biểu diễn một số thuộc ô A2 dưới dạng số điện thoại truyền thống 7 chữ số dưới định dạng 123-4567, ta dùng công thức:

=TEXT(A2, “###-####”)

Nếu một số giá trị ban đầu có mã khu vực (ví dụ 7 hoặc 10 chữ số), hãy dùng định dạng có điều kiện sau để hiển thị số 10 chữ số theo định dạng (123) 456-7890:

=TEXT(A2,”[<=9999999]###-####;(###) ###-####")

Xuất hiện công thức như sau:

GIẢI QUYẾT VẤN ĐỀ KHI HÀM TEXT TRONG EXCEL KHÔNG HOẠT ĐỘNG – NGUYÊN NHÂN VÀ CÁCH KHẮC PHỤC

Trong Excel, hàm Text được biết đến với tính đơn giản của nó, gần như không bao giờ gặp phải trục trặc. Tuy nhiên, khi hàm Text gặp lỗi, thì thường do người dùng nhập sai cú pháp định dạng. Dưới đây là hai tình huống thường gặp:

  1. Nếu mã định dạng không được bao quanh bằng dấu ngoặc kép, hàm Text sẽ báo lỗi #NAME?

Chẳng hạn, công thức =TEXT(A2, mm/dd/yy) không đúng và cần được sửa lại thành =TEXT(A2, “mm/dd/yy”)

  1. Hàm Text phụ thuộc vào ngôn ngữ địa phương và yêu cầu định dạng ngày tháng phù hợp.

Nếu sử dụng tiếng Anh, công thức =TEXT(A2, “mm/dd/yy”) sẽ chạy tốt. Nhưng ở các khu vực khác như Đức, người dùng cần phải thay “d” bằng “t” và “y” bằng “j”, từ đó công thức sẽ là =TEXT(A2; “mm/tt/jj”) trong phiên bản Excel tiếng Đức.

Bài hướng dẫn sử dụng hàm Text trong Excel đã được trình bày, hy vọng các bạn sẽ áp dụng thành công!

Bên cạnh đó, để ứng dụng Excel hiệu quả trong công việc, bạn cần thành thạo thêm các hàm và công cụ khác như:

Một số hàm thường dùng gồm:

  • Hàm SUMIF, SUMIFS để tính tổng khi thỏa mãn điều kiện đơn hoặc điều kiện kép
  • Hàm COUNTIF, COUNTIFS để đếm số lượng tùy theo điều kiện đơn hoặc nhiều điều kiện
  • Các hàm liên quan đến xử lý dữ liệu chẳng hạn như chuỗi, ngày tháng, số…
  • Tham chiếu và dò tìm sử dụng Index+Match, hàm SUMPRODUCT…

Và các công cụ bạn nên biết:

  • Conditional formatting để định dạng dữ liệu theo điều kiện
  • Data Validation để thiết lập các ràng buộc nhập dữ liệu
  • Cách đặt tên (Name) trong công thức và cách dùng chúng
  • Việc tạo báo cáo bằng Pivot Table…

Kho kiến thức Excel vô cùng phong phú, đúng không? Bạn có thể học hết những kiến thức này thông qua khóa học SM90 – Excel từ cơ bản đến chuyên gia của Siêu Marketing. Khóa học này sẽ trang bị đầy đủ và chi tiết kiến thức cần thiết, không giới hạn thời gian học nên bạn có thể tiếp tục học bất kỳ lúc nào và dễ dàng tra cứu lại thông tin. Hiện tại, hệ thống đang có ưu đãi đặc biệt cho những ai đăng ký tham gia. Thông tin chi tiết xin bạn xem tại: sieumarketing.com

Trương Thành Tài

    [submission_id id-lien-he]

    0
      0
      Đơn hàng
      Đơn hàng trốngQuay lại Shop