Thủ thuật IT

Hướng dẫn sử dụng hàm text trong excel mới nhất 2020

Bạn đang xem: hàm text trong excel Tại Nega.vn

Hàm text trong excel là một trong những hàm quan trọng mà bất cứ người làm excel nào cũng cần sử dụng. Trong bài viết này, Phanmemmienphi.vn sẽ  viết bài Hướng dẫn sử dụng hàm text trong excel mới nhất 2020

Hướng dẫn sử dụng hàm text trong excel mới nhất 2020

Trong bài viết này, Blog Học Excel Trực tuyến sẽ cho biết các đặc trưng của hàm Text trong Excel và chỉ ra những phương pháp tăng cao để dùng bí quyết hàm Text trong Excel.

Xem thêm: Hướng dẫn thủ thuật xuống dòng trong excel 2007

HÀM TEXT TRONG EXCEL – 

phương pháp

 VÀ 

cách

 

sử dụng

 CƠ BẢN

Hàm Text được sử dụng để chuyển đổi trị giá số sang chuỗi văn bản ở một định hình cụ thể.

Công thức: TEXT(giá trị, định dạng_văn bản)

Cụ thể:

  • Giá trị: 

    trị giá

     số cần chuyển sang văn bản. 

    đủ sức

     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ề 

    hiệu quả

     là số hay ngày.

  • định hình

     văn bản: 

    định dạng

     cần 

    dùng

    phân phối

     thể thức của mã 

    định hình

     được đặt trong dấu ngoặc kép, 

    giống như

     “mm/dd/yy”.

Hàm Text phù hợp với all các phiên bản Excel 2016, Excel 2013, 2010, 2007, 2003 và thấp hơn.

Thông thường, hàm Text trong Excel được dùng trong các trường hợp sau đây:

  • Hiển thị số theo 

    hướng dẫn

     

    easy

     đọc hơn hoặc theo một 

    định hướng

     

    không khó khăn

     hơn cho 

    người dùng

    .

  • Hiển thị ngày tháng theo một 

    định dạng

     nhất định

  • hòa 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 hình truyền thống giống như “Jan 1, 2016”, 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. do vậy, hiệu quả hàm Text đưa ra k thể sử dụng trong các phép tính khác. Nếu muốn, đủ nội lực giữ nguyên giá trị nguồn và sử dụng cách thức không giống.

Mà

định hình

 HÀM TEXT TRONG EXCEL

giống như vừa mới thấy, phương pháp hàm Text rất dễ dàng. Mỗi phần được cấp một mã định hướng thích hợp và hiệu quả trả về số theo hướng dẫn mong muốn. Về cơ bản, hàm Text đồng ý hầu hết các định hướng mã được dùng trong định hướng Excel. Bảng dưới đây liệt kê những mã định hướng cơ bản nhất.

giới thiệu

định hình

 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 

k

 biểu diễn thêm số 0
#.## – tính đến số thập phân thứ 2Nế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 

k

 biểu diễn chúng. Thường được 

sử 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 

hướng dẫn

 hàng 

ngàn

###,###.## biểu hiện dấu phân tách hàng 

ngàn

 và 2 số thập phân

Cùng đó, có thể dùng cùng với bất cứ kí tự nào dưới đây trong mã định hình, kết quả thể hiện đượ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 

to

 hơn

=
Dấu bằng

/
Dấu gạch chéo

Bảng dưới đây chỉ hướng dẫn sử dụng mẹo hàm Text trong Excel để vận dụng các định hướng khác nhau ra trị giá tương đương.

A
B
C

1

giá trị

 

gốc

giá trị

 

định hình

phương 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 sử dụng hàm TEXT trong Excel với ngày và giờ, đủ sức sử dụng mã định hướng sau:

Mã 

định hướng

giới thiệu

gợi ý

d (day)
Ngày của tháng hoặc ngày của năm
d – một hoặc 2 số 

k

 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 

dùng

 

giống như

 một phần của ngày)
m – một hoặc 2 số 

k

 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ố 

k

 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ách thức hàm Text cho ngày:

A
B
C

1
Ngày 

nguồn

Ngày được định đạng

cách 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”)

phương pháp

 

sử dụng

 HÀM TEXT TRONG EXCEL – 

phương thức

 MẪU

đủ nội lực thấy, vận 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 refresh bằng cách 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.

liên kết

 VẲN BẢN VÀ SỐ (HOẶC NGÀY) THEO 

định hình

 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ỉ mong 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 user ý 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à 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 hướng số bên trong một chuỗi văn bản

Giả sử, cần tính tổng tỉ lệ dựa trên giá của đối tượng trong ô A2, số lượng đối tượng trong ô B2, và giảm số lượng trong ô C2 sử dụng phép tính: =A2*B2*(1-C2). so với người sử dụng biết chuẩn xác ý nghĩa của kết 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 đó, user muốn hiển thị biểu tượng tiền tệ, dấu phân phương pháp hàng nghìn và hai chữ số thập phân.

Xem thêm: Hướng dẫn xuống dòng trong excel mac mới nhất 2020

Để hoàn thiện, bổ sung phương pháp tính vào đối số thứ nhất của hàm Text, bao gồm mã định hình tương ứng trong đối số thứ 2, và nối hàm Text với chuỗi bằng mẹo 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:

ADVERTISEMENT

gợi ý 2: phối hợp văn bản và ngày tháng theo định dạng mong muốn

Khi quay lại ngày cho đến nay bằng hướng dẫn sử dụng hàm Today() hay Now, đủ nội lực user muốn biểu diễn ngày bằng mẹo hòa 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 mong muốn nối văn bản với ngày tháng theo phương pháp thông thường:

=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 số sim. Bởi vậy, chiếc sim này xuất hiện trong chuỗi văn bản nối. Để giải quyết trạng thái nay, sử dụng hàm Text để biểu diễn ngày tháng theo ý muốn của user.

ví dụ, gắn hàm Today trong phương thức hàmText có mã định hình “dddd d mmm,yyyy”, kết quả trả về sẽ là: “Today is Monday 12 Dec, 2016”

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 trên đỉnh

 MỖI SỐ VỚI ĐỘ DÀI NGẮN 

không giống

 NHAU

Microsoft Excel auto xóa những số 0 đứng trên đỉnh mỗi con số trong một ô, điều này không có tác động gì trong hầu hết các trường hợp. không những thế, 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 số lượng trong một cột, ngay cả khi những số lượng đó không có trị giá tương đương. đơn giản bằng phương pháp sử dụng mã định hướng chưa những số 0 vào đầu như “0000000”, trong đó, những số 0 phải tương ứng với tổ chức số được hiển thị.

gợi ý, để biểu diễn số có 7 chữ số với số 0 đứng đầu, sử dụng phương thức sau: (A2 là ô chứa trị giá gốc)

=TEXT(A2,”0000000″)

Trong bảng dưới đây, mẹo hàm Text thêm đã thêm số 0 đứng đầu theo yêu cầu để tạo chuỗi 7 kí tự. (Lưu ý: kết 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 

trị giá

 THÀNH 

phone

 THEO MỘT 

định hướng

 NHẤT ĐỊNH

Chuyển giá trị số trong ô thành những sđt 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 hướng.

Bởi vậy, để biểu diễn số trong ô A2 theo phone 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ố), gồm có 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ách thức thực hiện giống như sau:

HÀM TEXT TRONG EXCEL 

không

 THỰC HIỆN ĐƯỢC – 

nguyên nhân

 VÀ 

phương pháp

 

giải quyết

so với các hàm không giống trong Excel, hàm Text không khó khăn hơn rất nhiề, tưởng như k có chút khó khăn nào cản trở. Nếu trong trường hợp hàm Text k thực hiện được, lí do của hầu hết trường hợp là do sai mã định hướng. Dưới đây là 2 trường hợp điển hình:

  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

gợi ý bí quyết =TEXT(A2, mm/dd/yy) bị lỗi, nên sửa thành: =TEXT(A2, “mm/dd/yy”)

  1. 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ể.

gợi ý, phương pháp =TEXT(A2, “mm/dd/yy”) hoạt động tốt với user sử dụng tiếng Anh, nhưng những vùng khác thì k. Điển hình, ở Đức, user 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ách 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 các bạn thành công!

không những thế để ứng dụng Excel vào công việc một phương pháp hiệu quả thì bạn còn phải sử dụng tốt các hàm, các tool 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 hình

     theo điều kiện với Conditional formatting

  • xây dựng

     điều kiện nhập dữ liệu với Data Validation

  • mẹo

     đặt Name và 

    sử dụng

     Name trong 

    phương thức

  • Lập báo cáo với Pivot Table…

Related Articles

Back to top button