Giáo trình Microsoft Excel 2007 - TextFunction

ng cho ngôn ngữ sử dụng bảng mã 1 byte (SBCS), RIGHTB dùng cho ngôn ngữ sử dụng bảng mã 2 byte (BDCS)
RIGHT luôn đếm mỗi ký tự 1-byte hoặc 2-byte là 1, không kể đến ngôn ngữ mặc định của Office (Office, Excel Options,Popular, Language setting)
Đồ thị
========
===========
============
==========
=========
===============
================
=====================
===============
==============
=============
================
Thấp hơn dự toán Vượt dự toán
n
nnnn
nnnnnnnnnnnnnnnn
nnnnnnnnn
nnnnn
nnnnn
nnn
nn
nnnnnnnnnnnnnnn
nnnnnnnnnnnnnnnnn
nnnn
Trần Thanh Phong 10
Microsoft Excel 2007 Công thức và Hàm
896
897
898
899
900
901
902
903
904
905
906
907
908
909
910
911
912
913
914
915
916
917
918
919
920
921
922
923
924
925
926
927
928
929
974
975
976
C D E F G H I J K L M N
RIGHTB đếm mỗi ký tự 2-byte là 2 khi thiết lập ngôn ngữ hỗ trợ DBCS và thiết lập ngôn ngữ mặc định. Ngược lại, FINDB đếm mỗi ký tự là 1.
Các ngôn ngữ hỗ trợ mã DBCS: Japanese, Chinese (Simplified), Chinese (Traditional), và Korean.
Cú pháp
RIGHT(text,num_chars)
RIGHTB(text,num_bytes)
Text là chuỗi văn bản chứa các ký tự cần trích
Num_chars số ký tự mà hàm RIGHT cần trích ra.
Num_bytes số byte mà hàm RIGHTB cần trích ra.
Ghi chú
Num_chars phải là số lớn hơn hay bằng 0 (zero). 
Nếu num_chars lớn hơn chiều dài của chuỗi văn bản, hàm RIGHT trả về cả chuỗi. 
Nếu num_chars không khai báo, Excel xem như là số 1. 
Ví dụ
Các chuỗi Hàm Right Hàm Rightb
Xin chào chào chào F910=RIGHT(D910,5)
Nguyễn Hoàng Long g g G910=RIGHTB(D910,5)
Xem thêm ví dụ nâng cao tại hàm LEFT (ví dụ 2)
Ví dụ 2: Cho chuỗi Hàm
Xin chào Microsoft Excel 2007 chào Microsoft Excel 2007 G916=RIGHT(D916,LEN(D916)-FIND(" ",D916,1))
HelloTheWorld HelloTheWorld G917=IFERROR(RIGHT(D917,LEN(D917)-FIND(" ",D917,1)),D917)
HelloTheWorld HelloTheWorld G918=IF(ISERR(FIND(" ",D918)),D918,RIGHT(D918,LEN(D918)-FIND(" ",D918,1)))
Trich tất cả chuỗi trừ từ đầu tiên, nếu dùng theo cách 1 thì sẽ báo lỗi khi chuỗi không có khoảng trắng, do vậy thêm hàm kiểm tra lỗi như cách 2 và 3 
(cách 2 chỉ dùng được trên Excel 2007 với hàm mới IFError)
Ví dụ 3 Tạo số thứ tự trong tiếng Anh
Các con số Hàm
1 1st E925=D925&IF(OR(VALUE(RIGHT(D925,2))={11,12,13}),"th",IF(OR(VALUE(RIGHT(D925))={1,2,3}),CHOOSE(RIGHT(D925),"st","nd","rd"),"th"))
3 3rd
45 45th
122 122nd
SEARCH, SEARCH
Tìm vị trí của một chuỗi trong một chuỗi thứ hai và trả về vị trí của ký tự đầu tiên của chuỗi thứ nhất tìm thấy trong chuỗi thứ 2
SEARCH dùng cho ngôn ngữ sử dụng bảng mã 1 byte (SBCS), SEARCHB dùng cho ngôn ngữ sử dụng bảng mã 2 byte (BDCS)
Trần Thanh Phong 11
Microsoft Excel 2007 Công thức và Hàm
977
978
979
980
981
982
983
984
985
986
987
988
989
990
991
992
993
994
995
996
997
998
999
1000
1038
1039
1040
1041
1042
1043
1044
1045
1046
1047
1048
1049
1050
C D E F G H I J K L M N
SEARCH luôn đếm mỗi ký tự 1-byte hoặc 2-byte là 1, không kể đến ngôn ngữ mặc định của Office (Office, Excel Options,Popular, Language setting)
SEARCHB đếm mỗi ký tự 2-byte là 2 khi thiết lập ngôn ngữ hỗ trợ DBCS và thiết lập ngôn ngữ mặc định. Ngược lại, FINDB đếm mỗi ký tự là 1.
Các ngôn ngữ hỗ trợ mã DBCS: Japanese, Chinese (Simplified), Chinese (Traditional), và Korean.
Cú pháp
SEARCH(find_text,within_text,start_num)
SEARCHB(find_text,within_text,start_num)
Find_text là chuỗi văn bản mà ta muốn tìm 
Within_text là chuỗi văn bản mà ta cần tìm chuỗi find_text trong nó
Start_num là vị trí trong chuỗi within_text tại đó hàm sẽ bằt đầu tìm.
Ghi chú
Dùng SEARCH và SEARCHB để xác định vị trí một ký tự hoặc chuỗi trong một chuỗi khác để sau đó dùng các hàm MID, MIDB, REPLACE, REPLACEB.
SEARCH và SEARCHB là không phân biệt chữa HOA hay thường, nếu muốn tìm có phân biệt chữ HOA và thường thì dùng FIND và FINDB. 
Ta có thể sử dụng các ký tự thay thế như ?, * trong điều kiện. Dấu ? thay cho một ký tự nào đó và dấu * thay cho một chuỗi nào đó.
Khi điều kiện lại là dấu ? hay * thì bạn đặt thêm dấu ~ phía trước nó.
Nếu find_text không tìm thấy, hàm báo lỗi #VALUE!. 
Nếu start_num không khai báo Excel xem như là số 1. 
Nếu start_num nhỏ hơn hay bằng 0 (zero) hoặc lớn hơn chiều dài của within_text, hàm báo lỗi #VALUE!. 
Sử dụng start_num để bỏ qua một số các ký tự bắt đầu chuỗi mà ta không quan tâm.
Ví dụ
Chuỗi Hàm
Statements 7 E997=SEARCH("e",D997,6) Tìm ký tự "e"và bắt đầu tìm tại vị trí thứ 6
Profit Margin 8 E998=SEARCH(D999,D998) Tìm "margin" trong chuỗi "Profit Margin"
margin Profit Amount E999=REPLACE(D998,SEARCH(D999,D998),6,"Amount") Thay từ "Margin" bằng từ "Amount"
SUBSTITUTE
Trong một chuỗi văn bản, thế một chuỗi mới cho một chuỗi cũ mà bạn đã xác định
Cú pháp
SUBSTITUTE(text,old_text,new_text,instance_num)
Text là chuỗi hoặc tham chiếu đến ô chứa chuỗi văn bản cần thay thế.
Old_text là chuỗi văn bản cần thay thế.
New_text là chuỗi văn bản mới mà ta muốn thay cho old_text
Ví dụ 1
Dữ liệu Hàm
Số liệu doanh thuSố liệu chi phí E1048=SUBSTITUTE(D1048, "doanh thu", "chi phí") Thay thế "doanh thu" bằng "chi phí"
Quí 1, 2008 Quí 2, 2008 E1049=SUBSTITUTE(D1049, "1", "2", 1) Thay thế chuỗi "1" bằng chuỗi "2" với vị trí thay thế thứ 1
Quí 1, 2011 Quí 1, 2012 E1050=SUBSTITUTE(D1050, "1", "2", 3) Thay thế chuỗi "1" bằng chuỗi "2" với vị trí thay thế thứ 3
Instance_num xác định old_text thứ mấy cần thay thế bằng new_text. Nếu khai báo instance_num thì chỉ có old_text được khai báo là được thay thế, 
nếu không khai báo thì tất cả old_text trong chuỗi Text được thay bằng new_text.
Trần Thanh Phong 12
Microsoft Excel 2007 Công thức và Hàm
1051
1053
1054
1055
1056
1057
1058
1059
1060
1061
1133
1134
1135
1136
1137
1138
1139
1140
1141
1142
1143
1144
1145
1146
1153
1154
1155
1156
1157
1158
1159
1160
1161
1162
1163
C D E F G H I J K L M N
Các chuỗi Hàm
Babba-booey 1 E1054=LEN(D1054)-LEN(SUBSTITUTE(D1054,"B","")) Có phân biệt chữ HOA và thường
Babba-booey 4 E1055=LEN(D1055)-LEN(SUBSTITUTE(UPPER(D1055),"B","")) Không phân biệt chữ HOA và thường
Ví dụ 3 Đếm số lần xuất hiện của từ trong chuỗi văn bản
Các chuỗi Từ cần đếm Hàm
Blonde on Blonde Blonde 2 G1059=(LEN(D1059)-LEN(SUBSTITUTE(D1059,F1059,"")))/LEN(F1059)
Blonde on Blonde blonde 2 G1060=(LEN(D1060)-LEN(SUBSTITUTE(UPPER(D1060),UPPER(F1060),"")))/LEN(F1060)
T
Trả về văn bản từ giá trị do ta xác định
Cú pháp
T(value)
Value là giá trị muốn kiểm tra
Ghi chú
Nếu value là chuỗi hoặc tham chiếu đến ô chứa chuỗi hàm trả về chính value. Nếu value không là chuỗi hàm trả về "" (chuỗi rỗng). 
Ví dụ
Dữ liệu Hàm
Rainfall Rainfall E1143=T(D1143) Vì ký tự đầu tiên là chuỗi hàm trả về chuỗi đó
19 Vì đây là số hàm trả về "" (chuỗi rỗng)
TRUE Vì đây là giá trị luận lý, hàm trả về "" (chuỗi rỗng)
TEXT
Chuyển đổi một giá trị sang dạng văn bản theo định dạng qui định
Cú pháp
TEXT(value,format_text)
Value là con số, một biểu thức trả về kết quả là con số hoặc tham chiếu đến ô chứa số
Format_text là kiểu định dạng số dưới dạng văn bản đặt trong cặp dấu nháy kép
Ghi chú
Format_text không được chứa *. 
Ví dụ
Nhân viên Doanh thu Hàm
Ta không cần phải sử dụng hàm T vì Excel tự động thực hiện việc kiểm tra và tiến hành chuyển đổi khi cần. Hàm này tạo ra nhằm để tương thích với 
các chương trình bảng tính khác.
Định dạng ô dùng lệnh chỉ thay đổi định dạng không làm thay đổi giá trị. Nhưng dùng hàm TEXT sẽ chuyển con số sang dạng chuỗi luôn và không 
dùng để tính toán như số được nữa. 
Trần Thanh Phong 13
Microsoft Excel 2007 Công thức và Hàm
1164
1165
1166
1173
1174
1175
1176
1177
1178
1179
1180
1181
1182
1208
1209
1210
1211
1212
1213
1214
1215
1216
1217
1218
1219
1220
1221
1222
1223
1224
1225
1226
1227
1228
1229
C D E F G H I J K L M N
Buchanan 2800 Doanh thu bán hàng của Buchanan là $2800.00 F1164="Doanh thu bán hàng của " & D1164 &" là "&TEXT(E1164, "$0.00")
Dodsworth 40% Dodsworth bán được 40% trên tổng doanh thu. F1165=D1165&" bán được "&TEXT(E1165,"0%")&" trên tổng doanh thu."
TRIM
Chuẩn hóa các khoản trắng trong một văn bản bằng cách loại bỏ các khoảng trắng dư thừa và chỉ để lại một khoảng trắng giữa các từ
Cú pháp
TRIM(text)
Text là chuỗi văn bản hay một tham chiếu tới một ô chứa văn bản mà bạn muốn chuẩn hóa
Ví dụ
Chuỗi Sau khi dùng hàm
Chương trình Fulbright Chương trình Fulbright F1180=TRIM(D1180)
Xin chào Xin chào F1181=TRIM(D1181)
UPPER
Chuyển một chuỗi văn bản sang chữ HOA
Cú pháp
UPPER(text)
Text là một chuỗi hay một tham chiếu tới một ô chứa văn bản mà ta muốn chuyển đổi
Ví dụ
Các chuỗi Hàm
chào các bạn CHÀO CÁC BẠN F1215=UPPER(D1215)
nguyễn hoàng NGUYỄN HOÀNG F1216=UPPER(D1216)
VALUE
Chuyển một chuỗi văn bảng sang một số
Cú pháp
VALUE(text)
Text là chuỗi văn bản hay một tham chiếu đến ô chứa văn bản mà ta muốn chuyển đồi
Ghi chú
Text có thể định dạng thành con số, ngày, hoặc thời gian do Excel nhận dạng, nếu nhận dạng không được sẽ báo lỗi #VALUE!. 
Hàm này chúng ta thường không sử dụng, nó được tạo ra để tương thích với các chương trình bảng tính khác
Ví dụ
Hàm
1000 D1228=VALUE("$1,000") Chuyển chuỗi "$1,000" thành số
0.2 D1229=VALUE("16:48:00")-VALUE("12:00:00") Đổi giờ ra các số tuần tự và trừ nhau "16:48:00"-"12:00:00" (0.2 hoặc 4:48)
Trần Thanh Phong 14