góc học tập

CÁC HAM SỐ TRONG EXCEL – HÀM TEXT VÀ DATA

CÁC HÀM SỐ TRONG EXCEL

 Data & Time          Math        Logical
 
 Text & Data        Information  Lookup
 
Statistical                    Error
 
 

HÀM TEXT VÀ DATA

Bao gồm các hàm xử lý chuỗi văn bản như trích lọc, tìm kiếm, thay thế, chuyển đổi chuỗi văn bản trong Excel.

Hàm Công dụng Hàm Công dụng
CHAR Chuyển số thành ký tự CLEAN Xóa ký tự không phù hợp
CODE Trả về mã số của ký tự đầu tiên CONCATENATE Nối nhiều chuỗi thành một chuỗi
DOLLAR Chuyển định dạng số thành tiền tệ EXACT So sánh hai chuỗi văn bản
FIND Tìm kiếm một chuỗi trong chuỗi khác FIXED Chuyển một số sang định dạng văn bản
LEFT Trích bên trái một chuỗi LEN Tính độ dài một chuỗi
LOWER Chuyển thành chữ thường PROPER Chuyển ký tự đầu mỗi từ thành chữ hoa
MID Trích chuỗi con từ một chuỗi REPLACE Thay thế một phần của chuỗi.
RIGHT Trích bên phải một chuỗi REPT Lặp lại một chuỗi
SUBSTITUTE Thay thế một chuỗi xác định SEARCH Tìm kiếm một chuỗi
TEXT Chuyển một số sang text. T Kiểm tra dữ liệu kiểu text
TRIM Xóa những ký tự trắng bên trong chuỗi. UPPER Chuyển ký tự thường thành hoa.
VALUE Chuyển một chuỗi thành số.


CHAR

Công dụng

Chuyển đổi một mã số trong bộ mã ANSI có miền giá trị từ 1 – 255 sang ký tự tương ứng

Công thức

=CHAR(number) number là một mã số trong bảng mã ANSI.

Ví dụ

=CHAR(65) –> Trả về A
=CHAR(A3) –> Trả về a

Tham khảo  Hàm T

==============

CODE

Công dụng

Trả về mã số của ký tự đầu tiên trong chuỗi ký tự

Công thức

=CODE(text)

text là chuỗi ký tự.

Ví dụ
Để dễ hiểu hơn, bạn áp dụng các công thức sau vào bất kỳ ô nào trong bảng tính.
Công thức Giải thích
=CODE(“ABC”) Trả về 65, Mã số của ký tự A.
=CODE(“VIETNAM”) Trả về 86, Mã số của ký tự V.

DOLLAR

Công dụng

Chuyển đổi số thành dạng tiền tệ, với số thập phân được chỉ định để làm tròn số đó.
Ký tự tiền tệ phụ thuộc vào thiết lập trong Regional Options. Dưới đây là minh họa công dụng của hàm theo ngôn ngữ là tiếng Anh, ký tự tiền tệ là $.

Công thức

=DOLLAR(number,decimals) number là số cần chuyển sang định dạng tiền tệ.
decimals là số số thập phân. Nếu decimals < 0 thì hàm sẽ làm tròn về bên trái số. Mặc định là 2.

Lưu ý!

Điểm khác biệt chính giữa một ô tiền tệ được định dạng bằng lệnh Format – Cells – Numbers từ menu và định dạng một số với hàm DOLLAR là hàm DOLLAR chuyển đổi kết quả của nó sang dạng văn bản (text) trong khi định dạng với lệnh Cells vẫn là số. Bạn có thể tiếp tục dùng số được định dạng với hàm DOLLAR trong công thức, bởi vì Microsoft Excel đổi số được nhập ở dạng giá trị text sang dạng số khi nó được tính.

Ví dụ

Để thử công thức, bạn có thể copy các giá trị bên trong bảng vào bảng tính và nhập công thức vào.
A B
1
1050.5
1050.1
1050.4
2
3
Công thức Giải thích
=DOLLAR(A1) Trả về $1050.50 – làm tròn với 2 số thập phân
=DOLLAR(A2,0) Trả về $1050 – làm tròn thành số nguyên.
=DOLLAR(A3,-2) Trả về $1100 – làm tròn về bên trái 2 số.

FIND
Công dụng
Tìm chuỗi find_text bên trong chuỗi within_text, và trả về vị trí bắt đầu của within_text trong find_text.
Công thức

=FIND(find_text,within_text,start_num) find_text là chuỗi cần tìm.
within_text là chuỗi chứa chuỗi cần tìm. start_num: vị trí bắt đầu trong chuỗi within_text để tìm kiếm. Nếu để trống, start_num là 1.

Lưu ý!

Nếu không tìm thấy text_find trong within_text hàm trả về lỗi #VALUE!Nếu start_num (vị trí bắt đầu tìm kiếm) nhỏ hơn 0 hàm trả về lỗi #VALUE!
Nếu start_num lớn hơn chiều dài chuỗi cần tìm find_text hàm trả về lỗi #VALUE!

Ví dụ

Để dễ hiểu hơn, bạn hãy copy dữ liệu bên trong bảng dưới đây vào trang bảng tính mới và nhập công thức vào.
A B
1
Việt Nam
Hà Nội
2
3
Công thức Giải thích
=FIND(“N”,A1) Trả về 6 – vị trí xuất hiện N trong Việt Nam
=FIND(A1,A2) Trả về lỗi #VALUE! do không tìm thấy Việt Nam trong Hà Nội

LEFT
Công dụng

Trích bên trái một chuỗi một hoặc nhiều ký tự dựa vào số ký tự mà bạn chỉ định.

Công thức

=LEFT(text,num_chars) text là chuỗi cần trích ký tự
num_chars là ký tự mà bạn cần trích bên trái chuỗi text..

Lưu ý!

num_chars không phải là số âm

num_chars nếu lớn hơn độ dài của chuỗi thì sẽ trả về toàn bộ chuỗi text.
num_chars nếu bỏ qua thì mặc định là 1.

Ví dụ

Để dễ hiểu hơn, bạn có thể copy dữ liệu bên trong bảng bên dưới vào một trang bảng tính mới và nhập công thức vào.
A B
1
SBD Họ và tên
QSA0001 Nguyễn Hoài An
QSA0002 Nguyễn Tấn Anh
2
3
Công thức Giải thích
=LEFT(A2,3) Trả về QSA
QSA là mã trường dự thi của thí sinh

LOWER

Công dụng

Đổi tất cả các ký tự trong chuỗi sang chữ thường.

Công thức

=LOWER(text) text là chuỗi, hoặc tham chiếu đến chuỗi cần chuyển định dạng.

Ví dụ

Để thử công thức, bạn có thể copy dữ liệu bên trong bảng bên dưới vào bảng tính và nhập công thức vào.
A B
1
SBD Họ và tên
QSA0001 Nguyễn Hoài An
QSA0002 Nguyễn Tấn Anh
2
3
Công thức Giải thích
=LOWER(A2) Trả về qsa0001
=LOWER(B2) Trả về nguyễn hoài an
Tham khảo  Hàm UPPER, Hàm PROPER

MID

Công dụng

Trích một chuỗi con từ một chuỗi text, bắt đầu từ vị trí start_num với số ký tự được chỉ định num_chars

Công thức

=MID(text,start_num,num_chars) text là chuỗi hoặc tham chiếu đến chuỗi.
start_num: vị trí bắt đầu trích lọc chuỗi con trong textnum_chars: số ký tự của chuỗi mới cần trích từ chuỗi text

Lưu ý!

start_num: lớn hơn chiều dài chuỗi text thì hàm trả về chuỗi rỗng “” start_num: nhỏ hơn 1 hàm trả về lỗi #VALUE!
num_chars: âm MID trả về lỗi #VALUE!

Ví dụ

Để dễ hiểu hơn, bạn có thể copy dữ liệu bên trong bảng bên dưới vào một trang bảng tính mới và nhập công thức vào.
A
B
C
1
Mã hàng Tên Hàng Đơn vị tính
MC010A Bàn mica loại A cái
PL012B Bàn plastic loại B cái
2
3
Công thức Giải thích
=MID(A2,3,3) Trả về 010
================

RIGHT

Công dụng

Trích bên phải một chuỗi văn bản một hoặc nhiều ký tự dựa vào số ký tự mà bạn chỉ định.

Công thức

=RIGHT(text, num_chars) text là chuỗi cần trích ký tự
num_chars là ký tự mà bạn cần trích bên phải chuỗi text.

Lưu ý!

num_chars không phải là số âm num_chars nếu lớn hơn độ dài của chuỗi thì sẽ trả về toàn bộ chuỗi text.
num_chars nếu bỏ qua thì mặc định là 1.

Ví dụ

Để dễ hiễu hơn, bạn hãy copy các giá trị bên trong bảng bên dưới vào một trang bảng tính mới và nhập công thức vào.
=RIGHT(“VIET NAM”,3) –> Trả về NAM

SUBSTITUTE
Công dụng

Thay thế một chuỗi cụ thể bên trong chuỗi bằng chuỗi khác. Dùng SUBSTITUTE khi muốn thay thế một chuỗi cụ thể.

Công thức

=SUBSTITUTE(text,old_text,new_text,instance_num) text: chuỗi văn bản cần thay thế nội dung.
old_text: nội dung bên trong chuỗi text cần thay thế.. new_text: chuỗi  văn bản mới để thay chuỗi cũ

instance_num: chỉ định thay thế ở lần mà tìm thấy chuỗi old_text trong chuỗi text. Nếu bỏ qua thì sẽ thay thế tất cả các old_text được tìm thấy trong chuỗi text.

Ví dụ

Để dễ hiểu hơn, bạn có thể copy dữ liệu bên trong bảng bên dưới vào một trang bảng tính mới và nhập công thức vào.
A
B
C
1
Mã hàng Số lượng
CD001 10
DVD002 20
2
3

=SUBSTITUTE(A2,”CD”,”CDRW”,1) –> Trả về CDRW001. Thay thế CD bằng CDRW
=SUBSTITUTE(A3,”0″,”A”) –> Trả về DVDAA2


TEXT
Công dụng

Chuyển một giá trị số sang văn bản với kiểu định dạng số được chỉ định.

Công thức

=TEXT(value,format_text)

value giá trị số, hoặc tham chiếu đến giá trị số cần chuyển đổi.
format_text kiểu định dạng bạn muốn chuyển đổi. Có thể tham khảo các kiểu định dạng trong Format – Cells, thẻ Number, trong danh sách Category.
Lưu ý!

format_text không đựơc có dấu *kết quả của TEXT không được tính toán ở kiểu số nữa.

Ví dụ

Để dễ hiểu hơn, bạn có thể copy dữ liệu bên trong bảng bên dưới vào một trang bảng tính mới và nhập công thức vào.
A B C
1
Doanh thu tháng 12
Tên hàng Số tiền Ghi chú
Monitor 15000000
CPU 20000000
CD-ROM 4000000
Tổng cộng 39000000
2
3
4
5
6

=TEXT(B6,”0 $”) –> Trả về 39000000 $


TRIM

Công dụng

Xóa tất cả các ký tự trắng của chuỗi trừ những khoảng trắng đơn dùng để làm khoảng cách bên trong chuỗi.

Công thức

=TRIM(text)
text chuỗi cần xóa các ký tự trắng.

Ví dụ

=TRIM(”  Microsoft Excel”). Trả về Microsoft Excel sau khi loại bỏ các khoảng trắng đầu chuỗi này.


VALUE
Công dụng
Đổi chuỗi text đại diện cho một số thành dữ liệu kiểu số
Công thức
=VALUE(text)

text là chuỗi văn bản đại diện cho một số.

Lưu ý!

text là định dạng số, ngày tháng, hoặc thời gian bất kỳ được Microsoft Excel công nhận. Nếu không phải định dạng trên sẽ trả về lỗi #VALUE!

Ví dụ

Để thử công thức, bạn có thể copy các giá trị bên trong bảng vào bảng tính và nhập công thức vào.
A B
1
SBD Họ và tên
QSA0100 Nguyễn Tấn Minh
QSA0101 Nguyễn Văn Minh
2
3
=VALUE(RIGHT(A2,4)). Trả về 100. Kết quả của hàm RIGHT chỉ trả về một chuỗi muốn chuyển nó thành số phải dùng hàm VALUE.

CLEAN

Công dụng

Xóa những ký tự không hiển thị và in được trong Worksheet được đưa từ các ứng dụng khác.

Công thức

=CLEAN(text) text là vùng dữ liệu cần xóa những ký tự không cần thiết.


CONCATENATE

Công dụng

Dùng để kết nối các chuỗi văn bản thành một chuỗi

Công thức

=CONCATENATE(text1,text2,…)
text1, text2 là các chuỗi văn bản con cần kết nối thành một chuỗi duy nhất.

Lưu ý!

Có thể dùng toán tử & để kết nối các chuỗi thay cho hàm CONCATENATE.

Ví dụ

Để thử công thức, bạn có thể copy các giá trị bên trong bảng vào bảng tính và nhập công thức vào.

A B
1
Hàm CONCATENATE
Việt
Nam
2
3

=CONCATENATE(A2,A3) –> Trả về Việt Nam


EXACT

Công dụng

So sánh hai chuỗi văn bản. Trả về TRUE nếu cả hai chuỗi giống nhau hoàn toàn, FALSE nếu ngược lại. EXACT phân biệt chữ thường và chữ hoa.

Công thức

=EXACT(text1,text2) text1 là chuỗi văn bản thứ nhất.
text2 là chuỗi văn bản thứ hai.

Ví dụ

Để thử công thức, bạn có thể copy các giá trị bên trong bảng vào bảng tính và nhập công thức vào.
A B
1
Word Word
Word word
2
3
Công thức Giải thích
=EXACT(A1,A2) Trả về TRUE
=EXACT(A2,B2) Trả về FALSE

FIXED

Công dụng

Chuyển đổi một số sang dạng văn bản (text) đồng thời làm tròn nó với số số thập phân được chỉ định.

Công thức

=FIXED(number, decimals,no_commas) number là dữ liệu kiểu cần chuyển đổi

decimals là số số thập phân chỉ định để làm tròn số. Nếu decimals âm thì sẽ làm tròn về bên trái number.
no_commas là cờ hiệu có giá trị (TRUE hoặc FALSE). Nếu TRUE thì kết quả trả về không có dấu phân cách hàng nghìn.

Lưu ý!

Số trong Excel không lớn hơn 15 ký số, nhưng phần thập phân có thể tới 127 ký số.

decimals nếu bỏ qua thì có giá trị mặc định là 2.
co_commas nếu bỏ qua thì có giá trị mặc định là FALSE.

Ví dụ

Để dễ hiểu hơn, bạn có thể copy bên trong bảng dưới đây vào bảng tính mới và nhập công thức vào.
A B
1
1024.55
5005.56
707812.5
2
3
Công thức Giải thích
=FIXED(A1,0,TRUE) Trả về 1025
=FIXED(A2,,) Trả về 5,005.56
=FIXED(A3,-2) Trả về 707,800

LEN

Công dụng

Tính độ dài (số ký tự) của mỗi chuỗi.

Công thức

=LEN(text) text là nội dung mà bạn cần xác định độ dài.

Ví dụ

Để dễ hiểu, bạn có thể copy dữ liệu bên trong bảng bên dưới vào một trang bảng tính mớ và nhập công thức vào.

A B
1
SBD Họ và tên
QSA0001 Nguyễn Hoài An
QSA0002 Nguyễn Tấn Anh
2
3
Công thức Giải thích
=LEN(A2) Trả về 7
=LEN(C3) Trả về 0

PROPER

Công dụng

Chuyển ký tự đầu tiên của mỗi từ thành chữ hoa, và các ký tự còn lại thành chữ thường.

Công thức

=PROPER(text) text là chuỗi văn bản cần chuyển định dạng

Ví dụ

Để dễ hiểu hơn, bạn có thể copy dữ liệu bên trong bảng bên dưới vào trang bảng tính mới và nhập công thức vào.
A B
1
nguyễn an nhiên
TRẦN NHẬT NAM
2
3
Công thức Giải thích
=PROPER(A2) Trả về Nguyễn An Nhiên
=PROPER(A3) Trả về Trần Nhật Nam
Tham khảo  Hàm LOWER, Hàm UPPER

REPLACE

Công dụng

Thay thế một phần của chuỗi text bằng một chuỗi khác dựa vào số ký tự bạn chỉ định.

Công thức

=REPLACE(old_text,start_num,num_chars,new_text)

old_text: chuỗi cũ cần thay thế

start_num: vị trí bắt đầu thay thế

num_chars: số ký tự của chuỗi cũ bắt đầu từ vị trí start_num sẽ bi6 thay bằng chuỗi mới.
new_text: chuỗi mới dùng để thay thế một phần chuỗi cũ.

Ví dụ

Để dễ hiễu hơn,bạn có thể copy dữ liệu bên trong bảng dưới vào một trang bảng tính mới và nhập công thức vào.

A B
1
Địa chỉ web tìm kiếm
www.google.com.vn
www.vinaseek.com
2
3

=REPLACE(A2,12,6,”*”) –> Trả về www.google.*


 

REPT

Công dụng

Lặp lại một chuỗi với số lần do bạn đưa ra.

Công thức

=REPT(text,number_times) text: chuỗi cần lặp lại
number_times: số lần lặp lại

Lưu ý!

number_times: bằng 0 REPT trả về chuỗi rỗng “” text: không phải là số nguyên sẽ được làm tròn
Kết quả REPT không được quá 32.767 ký tự, nếu lớn hơn trả về lỗi #VALUE!

Ví dụ

Để dễ hiểu hơn, bạn hãy copy dữ liệu bên trong bảng dưới vào bảng tính mới và nhập công thức vào.
A B
1
$
9
*
2
3
Công thức Giải thích
=REPT(A1,5) Trả về $$$$$
=REPT(A2,4) Trả về 9999
=REPT(A3,50000) Trả về #VALUE!

SEARCH
Công dụng

Trả về vị trí đầu tiên của ký tự cần tìm bên trong chuỗi.

Công thức

=SEARCH(find_text,within_text,star_num) find_text: chuỗi cần tìm. Có thể dùng ký tự ? để đại diện một ký tự đơn, dùng * để đại diện một nhóm ký tự. Nếu muốn tìm dấu ? hoặc dấu * thì gõ dấu ~ trước ký tự đó.
within_text: chuỗi chứa chuỗi mà bạn muốn tìm.
star_num: vị trí bắt đầu tìm kiếm.

Lưu ý!

SEARCH không phân biệt chữ thường, chữ hoa

SEARCH tương tự như FIND nhưng FIND phân biệt chữ thường và chữ hoa khi tìm kiếm
SERACH tìm không có kết quả sẽ trả về lỗi #VALUE!

Ví dụ

Để dễ hiểu hơn, bạn có thể copy dữ liệu bên trong bảng bên dưới vào một trang bảng tính mới và nhập công thức vào.
A
B
C
1
SBD Họ và tên
QSA0010 Nguyễn Minh
QSA0210 Nguyễn Văn Chương
2
3

=SEARCH(“QSA”,A2) –> Trả về 1
=REPLACE(A3,SEARCH(“QSA”,A3),3,”QSK”) –> Trả về QSK0210 là kết quả của 2 hàm thay thế (REPLACE) và tìm kiếm SEARCH.


T
Công dụng

Trả về chuỗi nếu giá trị được tham chiếu là một chuỗi.

Công thức

=T(value)

value tham chiếu tới giá trị cần kiểm tra.

Lưu ý!

value tham chiếu đến chuỗi text thì T trả về chuỗi, ngược lại trả về chuỗi rỗng “”.
Không dùng hàm T trong công thức của bạn vì Microsoft Excel có khả năng tự chuyển đổi các kiểu dữ liệu phù hợp khi cần thiết. Hàm T được Excel hỗ trợ để tương thích với các ứng dụng bảng tính khác.
Ví dụ

Để dễ hiểu hơn, bạn có thể copy dữ liệu bên trong bảng bên dưới vào một trang bảng tính mới và nhập công thức vào.

A
B
C
C
1
Quốc gia HCV HCB HCĐ
Việt Nam 120 50 60
Thái Lan 98 60 56
2
3

=T(A2) –> Trả về Việt Nam
=T(B2) –> Trả về “” giá trị của B2 là kiểu số.


UPPER

Công dụng

Chuyển tất cả các ký tự trong chuỗi thành ký tự hoa.

Công thức

=UPPER(text) text là chuỗi văn bản cần chuyển định dạng

Ví dụ

Để dễ hiểu hơn, bạn có thể copy dữ liệu bên trong bảng bên dưới vào trang bảng tính mới và nhập công thức vào.
=UPPER(“Việt Nam”) Trả về “VIỆT NAM”
Tham khảo  Hàm LOWER, Hàm PROPER

CÁC HAM SỐ TRONG EXCEL – HÀM TEXT VÀ DATA
Đánh giá bài viết này

Về tôi

Ngọc Nam

Tôi là Nam. là nhân viên kinh doanh công ty CP BĐS Tín Phúc. Blog này lập ra nhằm mục đích chia sẽ những cái gì mình biết và Rất vui khi bạn đã ghé thăm Blog của mình.Hi vọng sẽ giúp ích được gì đó cho bạn
Liên hệ hotline/zalo: Mr Nam 093.369.2131

Để lại bình luận