Tổng hợp hàm xử lý văn bản và chuỗi ( Có ví dụ)
maidinhdan > 16-04-16, 01:17 PM
Các hàm xử lý chuỗi
Space (Num as Long) 'trả về chuỗi chỉ toàn khoảng trống với số khoảng trống được ấn định bởi tham số Num
VD: str = space (3) ---> str = " "
String (Num as Long, character) 'trả về một chuõi (theo dạng variant) gồm các ký tự lặp lại. Ký tự lặp lại là ký tự đầu của biểu thức chuổi được truyền ở tham số thứ hai của hàm (character). Tham số thứ nhất (Num) xác nhận số lần lặp lại.
VD: str = string (5, "a")
str = string (5, "abc")
str = string (5, 97)
cả 3 ví dụ này đều cho ra chuỗi "aaaaa"
Trim (String) ' cắt các khoảng trống ở 2 đầu chuỗi
VD: str = Trim (" Yêu em ") ----> str = "Yeu em"
LTrim (String) ' cắt các khoảng trống ở đầu chuỗi
RTrim (String) ' cắt các khoảng trống cuối chuõi
Len ( ) ' trả về chiều dài của chuỗi bao gồm các khoảng trống và các ký tự
VD: str = Len ("caulacboVB") ---> str = 10
Mid (string, start as Long, length) ' trích từ tham số 1(string) một chuỗi ở vị trí bắt đầu được xác định bởi tham số 2(start), với số ký tự được qui định bởi tham số 3(length). Nếu bỏ wa tham số length thì hàm Mid sẽ trích đến hết chuỗi.
VD: str = Mid ("caulacboVB", 1, 8) ---> str = "caulacbo"
InStr (start, string1, string2, compare) ' trả về vị trí bắt đầu của một chuỗi con cần tìm trong một chuỗi mẹ. tham số 1(start) xác định vị trí bắt đầu tìm, tham sô 2(string1) là chuỗi mẹ, tham số 3(string2) là chuỗi cần tìm, tham số 4(compare) mặc định là so sánh nhạy ký tự.
Khi bỏ wa tham số thứ nhất thì vị trí bắt đầu tìm mặc định là 1
VD: pos = InStr ("caulacboVB", "VB") ---> pos = 9
InStrRev (StringCheck as string, StringMatch as string, Start as Long, Compare) ' chức năng như InStr nhưng InStrRev hoạt động ngược lại từ cuối chuỗi và cú pháp khác hơn. Cả hai hàm đều là hàm tìm kiếm nhạy ký tự nên cần chú ý chữ thường và chữ HOA. InStrRev thường kết hơp với Mid để tách một tên File khỏi đường dẫn và tên mở rộng.
VD:
PathFile = "C:\temp\001.tmp"
Pos = InStrRev (pathFile, "\")
PathFile = Mid (PathFile, Pos + 1)
Pos = InStrRev (PathFile, ".")
PathFile = Mid (PathFile, 1, Pos - 1) --->PathFile = "001"
Left (String, Length as Long) ' trích từ đầu một chuỗi của tham số 1(String) với số lượng xác định bởi tham số 2(Length)
VD: str = Left ("caulacboVB", 6) ---> str = "caulac"
Right (String, Length as Long) ' như Left nhưng trích ngược từ cuối chuỗi
Replace (Expression as string, Find as string, Replace as string, start, count, compare) ' tìm trong tham số thứ 1(Expression) một chuỗi xác định bởi tham số 2(Find) và thay thế bằng một chuỗi được đặt ở tham số 3(Replace). Ba tham số còn lại là tùy chọn. Start qui định vị trí bắt đầu tìm chuỗi cần được thay, nếu bỏ wa mặc định la 1. Count qui định số lần thay thế trong chuỗi, nếu bỏ wa mặc định Replace sẽ tìm và thay thế cho đến hết chuỗi.
VD: str = "bcbcbc"
tmp = Replace (str, "b", "a") ---> str = "acacac"
tmp = Replace (str, "b", "a", 2) ---> str = "cacac"
tmp = Replace (str, "b", "a", 1, 1) ---> str = "acbcbc"
StrComp (String1, String2, Compare) ' dùng để so sánh 2 chuỗi.
Trị trả về: (String1 < String2) = -1; (String1 = String2) = 0; (String1 > String2) = 1
Like ' so sánh 2 chuỗi cho phép sử dụng biệt ngữ ( như dùng ký tự đại diện trong Dos) trị trả về = True nếu tương hợp
VD: "abcd" Like "*bcd" = True
"abcd" Like "a?cd" = True
"a1cd" Like "a#cd" = True
Chú ý hàm Like mặc định cũng là hàm nhạy ký tự, theo thiết lập Option Compare ở form hoặc module
Chr(charcode as Long) ' Chuyển mã Ascii thành ký tự
VD:
MyChar = Chr(65) ' Returns A.
MyChar = Chr(97) ' Returns a.
MyChar = Chr(62) ' Returns >.
MyChar = Chr(37) ' Returns %.
Asc(String as String) ' trả về mã Ascii của ký tự
VD: Asc ("a") = 65
ChrW(charcode) ' Chuyển mã Ascii thành ký tự (Hỗ trợ Unicode)
VD: ChrW(&H1EC7) = “ệ”
AscW (string) ' Chuyển ký tự thành mã Ascii (hỗ trợ Unicode)
VD: AscW(“ệ”) = 7879 = H1EC7
LCase(String) ' chuyển đổi chuỗi sang chữ in thường
UCase (String) ' ngược với LCase
Join (SourceArray, Delimiter) ' tạo chuỗi mới từ một mảng chuỗi (SourceArray) với các phần tử được phân định bởi tham số Delimiter
VD: Arr (0) = "a"
Arr (1) = "b"
Arr (2)= "c"
Print Join (Arr, " ") = "a b c"
Split (Expression as String, Delimiter, Count, Compare) ' tạo mảng chuỗi từ một chuỗi (Expression). Đặt tham số Delimiter để chuyên biệt chỗ ngắt, nếu bỏ wa tham số này mặc định Split sẽ tách tại các khoảng trống của chuỗi. Tham số Count qui định số lần tách. Ba tham số cuối là tùy chọn
VD: Dim str as string, Arr as Variant
str = "cau lac bo vb"
Arr = Split (str) ' dùng For duyệt mảng Arr sẽ cho ra : Arr ( 0 ) = "cau"; Arr ( 1 ) = "lac" ...Arr ( 3 ) = "vb"
Arr = Split (str, "a") ---> Arr (0) = "c"; Arr (1) = "u l"; Arr (2) = "c bo vb"
Arr = Split (str, " ", 2) ---> Arr (0) = "cau"; Arr(1) = "lac bo vb"
Filter (sourcearray, match [, include [, compare]]) ' Lọc mảng sourcesrray với giá trị lọc là match ; include: Lọc đảo (True hoặc False) ; compare: chỉ rõ kiểu dữ liệu để so sánh trong quá trình lọc.
Dùng cho tham số compare
vbUseCompareOption = –1 : Chế độ tùy chọn, VB sẽ tự động lựa lọai dữ liệu thích hợp
vbBinaryCompare = 0 : So sánh nhị phân
vbTextCompare = 1 : So sánh chuỗi
vbDatabaseCompare = 2 : So sánh dữ liệu
VD:
Dim selNames() As String
Dim Names(1 To 5) As String
Names(1) = "A"
Names(2) = "B"
Names(3) = "C"
Names(4) = "D"
Names(5) = "E"
selNames = Filter(Names, "A") ' Returns "A"
selNames = Filter(Names, "B", False) 'Returns "A" , "C", "D", "E"
StrReverse(expression as String) ' Đảo chuỗi expression
VD: StrReverse("1234567") ' Returns "7654321"
Trên Excel dùng được và Access cũng thế.
1. Hàm ASC()
Dùng để đổi các ký tự double-byte sang các ký tự single-byte cho những ngôn ngữ sử dụng bộ ký tự double-byte.
Cú pháp: = ASC(text)
text : Là chữ hoặc tham chiếu đến một ô có chứa chữ. Nếu text không chứa bất kỳ mẫu tự nào thuộc loại double-byte, thì text sẽ không được chuyển đổi.
Ví dụ:
= ASC("Excel") = Excel
2. Hàm BAHTTEXT()
Dùng để chuyển đổi một số thành dạng chữ của tiếng Thái, rồi thêm hậu tố "Bath" vào.
Cú pháp: = BAHTTEXT(number)
number : Là một số hoặc tham chiếu đến một ô có chứa số.
Ví dụ:
undefined
3. Hàm CHAR()
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ú pháp: = CHAR(number)
Number : là một mã số trong bảng mã ANSI.
Ví dụ: Để kết quả có được là ký tự bản quyền ©, chúng ta nhập: = CHAR(169)
5. Hàm CLEAN
Công dụng: Xóa tất cả những ký tự không in ra được (nonprintable characters) trong chuỗi văn bản
Công thức: =CLEAN(text)
___text: chuỗi văn bản cần xóa những ký tự không in ra được
Những ký tự không in ra được là những ký tự có mã số từ 1 đến 31 trong bảng mã ANSI.
Hàm CLEAN() thường được dùng để gỡ bỏ những dấu ngắt đoạn, ngắt câu (có được do nhấn phím Enter), nghĩa là nối những đoạn bị xuống hàng lại với nhau.
Ví dụ: Hình sau đây cho thấy công dụng của hàm CLEAN.
undefined
6. Hàm CODE
Hàm CODE() là ngược lại với hàm CHAR(), nghĩa là, cho 1 ký tự, CODE() sẽ cho biết mã số của ký tự đó. Nếu đối số của CODE() là một chuỗi, thì kết quả trả về sẽ là mã số của ký tự đầu tiên trong chuỗi đó.
Cú pháp: = CODE(text)
text : Là một ký tự hoặc một chuỗi (một câu)
Ví dụ:
CODE("a") = 97
CODE("A") = 65
7. Hàm CONCATENATE
Công dụng: Dùng nối nhiều chuỗi lại với nhau
Công thức: =CONCATENATE(text1,text2,...)
Ví dụ: =CONCATENATE("Giải pháp", "Excel", " - ", "Công cụ tuyệt vời của bạn) → Giải pháp Excel - Công cụ tuyệt vời của bạn
8. Hàm EXACT
Công dụng: Dùng để so sánh hai chuỗi với nhau.
Công thức: =EXACT(text1,text2)
Hàm EXACT phân biệt chữ thường và chữ hoa.
Nếu 2 chuỗi text1, text2 giống nhau hoàn toàn, hàm sẽ trả về TRUE; nếu không, sẽ trả về trị FALSE
Ví dụ:
=EXACT("Giải pháp", "Giải pháp") → TRUE
=EXACT("Giải pháp", "Giải Pháp") → FALSE
9. Hàm T
Công dụng: Trả về một chuỗi nếu trị tham chiếu là chuỗi, ngược lại, sẽ trả về chuỗi rỗng
Công thức: =T(value)
Hàm này ít khi được dùng.
10. Hàm VALUE
Công dụng: Dùng để đổi một chuỗi đại diện cho một số thành kiểu số
Công thức: =VALUE(text)
text phải là định dạng số, ngày tháng hoặc bất kỳ một thời gian nào miễn là được Excel công nhận.
Nếu 2 chuỗi text1, text2 giống nhau hoàn toàn, hàm sẽ trả về TRUE; nếu không, sẽ trả về trị FALSE
Ví dụ:
Để trích ra số 6500 trong SQA6500, bạn có thể dùng hàm RIGHT()
=RIGHT("SQA6500", 4) → 6500
Tuy nhiên kết quả do hàm RIGHT() có được sẽ ở dạng text, bạn không thể nhân chia cộng trừ gì với cái "6500" này được.
Để có thể tính toán với "6500", bạn phải đổi nó sang dạng số:
=VALUE(RIGHT("SQA6500",4)) → 6500
Cũng là 6500, nhưng bây giờ bạn có thể cộng trừ nhân chia với nó.
11. Hàm DOLLAR
Chuyển đổi một số thành dạng tiền tệ (dollar Mỹ), có kèm theo dấu phân cách hàng ngàn, và có thể làm tròn theo ý muốn.
Cú pháp: = DOLLAR(number, decimals)
Number : Số cần chuyển sang dạng tiền tệ
Decimals : Số số thập phân, mặc định là 2, nếu là số âm thì sẽ làm tròn về bên trái
Ví dụ:
= "Tổng chi phí ngoại tệ của năm 2007 là " & DOLLAR(1500670000, 0) → Tổng chi phí ngoại tệ của năm 2007 là $1,500,670,000
Ở đây, số tiền này đã được định dạng thành số nguyên (decimals = 0).
12. Hàm FIND và Hàm SEARCH
Công dụng: Dùng để tìm vị trí bắt đầu của một chuỗi con (substring) trong một chuỗi
Công thức:
__ =FIND(find_text, within_text [, start_num])
__ =SEARCH(find_text, within_text [, start_num])
___find_text: chuỗi văn bản cần tìm (chuỗi con)
___within_text: chuỗi văn bản chứa chuỗi cần tìm (chuỗi mẹ)
___start_num: vị trí bắt đầu tìm trong chuỗi within_text (mặc định là 1)
Một số lưu ý:
- Kết quả của hai hàm này là một con số, chỉ vị trí bắt đầu (tính từ start_num) của find_text trong within_text
- Dùng SEARCH() khi muốn tìm một chuỗi bất kỳ. Ví dụ: SEARCH(“e”, “Expenses”) sẽ cho kết quả là 1.
- Dùng FIND() khi muốn tìm chính xác một chuỗi có phân biệt chữ hoa, chữ thường. Ví dụ: FIND(“e”, “Expenses”) sẽ cho kết quả là 4.
- Nếu không tìm thấy find_text, hàm sẽ báo lỗi #VALUE
- Có thể dùng những ký tự đại diện như *, ? trong find_text của hàm SEARCH()
- Với hàm SEARCH(), nếu muốn tìm chính ký tự * hoặc ? thì gõ dấu ~ trước ký tự đó ( ~* hoặc là ~?)
Trích xuất họ và tên (ví dụ dùng để trích cho tên tiếng Anh, bỏ qua tên đệm)
Đây là dạng bài toán đã được rất nhiều bạn hỏi trên GPE.
Cách làm là dùng hàm FIND() để tìm những khoảng trắng phân cách giữa họ và tên, sau đó dùng hàm LEFT() để tách phần tên, và hàm RIGHT() để tách phần họ.
Để lấy phần tên (First Name), chúng ta dùng công thức sau (giả sử họ tên nằm ở cell A2):
=LEFT(A2, FIND(" ", A2) - 1)
Nghĩa là dùng hàm FIND() để tìm vị trí của ký tự trắng đầu tiên kể từ bên trái, ví dụ nó là vị trí thứ 5,
khi đó hàm LEFT() sẽ xác định được cái tên này gồm có 4 chữ (= 5-1).
Để lấy phần họ (Last Name), chúng ta dùng công thức:
=RIGHT(A2, LEN(A2) - FIND(" ", A2))
Bạn tự dịch câu này nhé!
Hình sau đây là một số ví dụ của bài vừa rồi.
undefined
Công thức ở D2: =RIGHT(A2, LEN(A2) - FIND(" ", A2)) & ", " & LEFT(A2, FIND(" ", A2) - 1)
13. Hàm FIXED
Công dụng: Chuyển đổi một số thành dạng văn bản (text), có hoặc không kèm theo dấu phân cách hàng ngàn, và có thể làm tròn theo ý muốn.
Công thức: =FIXED(number [,decimals] [,no_commas])
___number là số cần chuyển sang dạng text
___decimals là số số thập phân, mặc định là 2, nếu là số âm thì sẽ làm tròn về bên trái
___no_commas: TRUE hoặc FALSE, dùng TRUE nếu không muốn có dấu phân cách hàng ngàn, và FALSE (mặc định) thì có bao gồm cả dấu phân cách hàng ngàn
Bài tập: Bạn thử dùng hàm FIXED với con số 123456789,85 để ra lập công thức cho ra kết quả giống như cột B nhé.
undefined
HÀM TRÍCH XUẤT KÝ TỰ HOẶC CHUỖI (Extracting a Substring)
--------------------------------------------------------------------------------
Một giá trị chuỗi có thể bao gồm nhiều chuỗi nhỏ hoặc nhiều từ đơn lẻ.
Ví dụ trong một cột chứa Họ và Tên, có thể bạn chỉ muốn lọc ra Họ mà thôi, hoặc Tên mà thôi, để sắp xếp chẳng hạn;
hoặc có thể bạn muốn trích ra vài ký tự đầu trong một danh sách hàng hóa, để dựa vào đó làm ra mã số hàng hóa, v.v...
Excel cung cấp cho bạn 3 hàm sau đây, chuyên để trích xuất 1 từ, 1 chữ hoặc nhiều từ, nhiều chữ từ một chuỗi có sẵn: Hàm LEFT, RIGHT và MID.
14. Hàm LEFT
Công dụng: Dùng để trích xuất phần bên trái của một chuỗi một hoặc nhiều ký tự tùy theo sự chỉ định của bạn
Công thức: =LEFT(text [,num_chars])
___text: chuỗi văn bản cần trích xuất ký tự
___num_chars: số ký tự cần trích ra phía bên trái chuỗi text, mặc định là 1
- num_chars phải là số nguyên dương
- Nếu num_chars lớn hơn độ dài của chuỗi thì kết quả trả về sẽ là toàn bộ chuỗi text
Ví dụ:___=LEFT(“Karen Elizabeth Hammond”, 5) → Karen
15. Hàm TRIM
Công dụng: Xóa tất cả những khoảng trắng vô ích trong chuỗi văn bản, chỉ chừa lại những khoảng trắng nào dùng làm dấu cách giữa hai chữ.
Những khoảng trắng vô ích này có thể nằm ở bất kỳ đâu trong đoạn văn: ở đầu, ở cuối hoặc ở giữa...
Công thức: =TRIM(text)
___text: chuỗi văn bản cần xóa những khoảng trắng vô ích
Ví dụ: Hình sau đây cho thấy công dụng của hàm TRIM.
Mỗi chuỗi văn bản trong khối A2:A7 đều có những khoảng trắng vô ích, nằm ở đầu, ở giữa hoặc ở cuối.
Tôi đặt hàm TRIM ở cột C.
Để các bạn thấy công dụng của hàm TRIM, tôi có dùng thêm hàm LEN() đặt ở cột B và D, để đếm số ký tự trong chuỗi trước và sau khi thực hiện hàm TRIM
undefined
16. Hàm LEN
Công dụng: Dùng để đếm số ký tự trong một chuỗi văn bản
Công thức: =LEN(text)
___text: chuỗi văn bản mà bạn cần đếm số ký tự có trong đó
17. Hàm LOWER
Công dụng: Hàm LOWER đổi tất cả các ký tự trong một chuỗi văn bản thành chữ thường.
Công thức: =LOWER(text)
___với text là một chuỗi, hoặc tham chiếu đến một chuỗi cần định dạng
Ví dụ: Công thức sau đây sẽ sửa cell A2 (Giải pháp Excel) thành toàn là chữ thường
=LOWER(A2) → giải pháp excel
Hàm LOWER thường được dùng khi bạn cần định dạng lại một câu văn (thường là do chép ở đâu đó dán vào trong Excel) mà trong đó, chữ thường chữ hoa được gõ hoặc bị thay đổi lung tung...
18. Hàm MID
Công dụng: Dùng để trích xuất một chuỗi con (substring) từ một chuỗi
Công thức: =MID(text, start_num, num_chars])
___text: chuỗi văn bản cần trích xuất
___start_num: vị trí bắt đầu trích ra chuỗi con, tính từ bên trái sang
___num_chars: số ký tự của chuỗi con cần trích ra
- num_chars phải là số nguyên dương
- start_num phải là số nguyên dương
- Nếu start_num lớn hơn độ dài của chuỗi thì kết quả trả về sẽ là chuỗi rỗng
Ví dụ:___=MID(“Karen Elizabeth Hammond”, 7, 9) → Elizabeth
19. Hàm PROPER
Công dụng: Hàm PROPER đổi ký tự đầu tiên trong một chữ của một chuỗi văn bản thành chữ in hoa, còn tất cả các ký tự còn lại trong chuỗi đó trở thành chữ thường.
Công thức: =PROPER(text)
___với text là một chuỗi, hoặc tham chiếu đến một chuỗi cần định dạng
Ví dụ: Công thức sau đây sẽ định dạng lại chuỗi văn bản cell A7 (công cụ tuyệt vời của bạn) theo kiểu Proper
=PROPER(A7) → Công Cụ Tuyệt Vời Của Bạn
20. Hàm REPLACE
Công dụng: Dùng để thay thế một phần của chuỗi bằng một chuỗi khác, dựa vào số ký tự được chỉ định
Công thức: =REPLACE(old_text, start_num, num_chars, new_text)
___old_text: chuỗi văn bản cần được xử lý
___start_num: vị trí bắt đầu tìm cái sẽ thay thế, tính từ bên trái sang
___num_chars: số ký tự của chuỗi cần được thay thế
___new_text: chuỗi văn bản sẽ thay thế cho số ký tự đã chọn bởi start_num và num_chars
Cái khó của hàm này là xác định được bởi start_num và num_chars. Làm sao biết được bắt đầu từ đâu và thay thế bao nhiêu chữ? Tôi gợi ý nhé:
- Bạn dùng hàm FIND() hoặc SEARCH() để xác định vị trí bắt đầu (start_num)
- Dùng hàm LEN() để xác định số ký tự của chuỗi sẽ được thay thế (num_chars)
Ví dụ: đế thay số 2007 bằng 2008 trong câu Expense Budget for 2007
Dùng công thức như sau:
=REPLACE(A1, FIND("2007", A1), LEN("2007"), "2008")→ Expense Budget for 2008
Với A1 = Expense Budget for 2007
21. Hàm REPT
Công dụng: Dùng để lập đi lập lại (nhân bản) một ký tự hoặc một từ, với số lần được định trước.
Công thức: =REPT(text, times)
___text: ký tự (character), một con số hoặc một từ (string) cần được nhân bản
___times: số lần lập đi lập lại của text
- Nếu times = 0, hàm REPT() sẽ trả về một chuỗi rỗng
- Nếu text là một con số, REPT() sẽ làm tròn con số đó thành số nguyên
Điền thêm dấu chấm cuối hàng
Mời bạn xem hình sau:
undefined
Bạn sẽ nghĩ tôi dùng cách "căn đều hai biên" cho mấy cái cell ở cột B ?
Không đâu, tôi dùng hàm REPT() đấy. Tại B1, tôi gõ công thức:
=A1 & REPT(“.”, 20 - LEN(A1))
Rồi tôi kéo công thức xuống tới B5.
Bạn có thể thay con số 20 bằng số khác, tùy vào bạn muốn độ rộng của cột B là bao nhiêu...
Làm biểu đồ bằng hàm REPT
Mặc dù Excel 2007 đã có những biểu đồ rất đẹp (làm bằng Conditional Formating),
tuy nhiên, chúng ta có thể áp dụng hàm REPT() để làm chuyện này.
Mời bạn xem hình sau, và để ý công thức ở cell C12 nhé
undefined
Thêm một biểu đồ nữa:
undefined
Để làm được cái biểu đồ như thế này, tôi dùng font Webbings (một loại font symbol), chọn ký tự tương ứng trên bàn phím là "g", vì nó có hình dạng giống như một khối vuông; rồi tô màu xanh nhạt.
Đế lấy trị giá times cho hàm REPT(), tôi nhân giá trị phần trăm bên cột B cho 100, và để cho mấy "bar" này không quá lớn, tôi chia giá trị đó cho 5.
Và kết quả là như cái hình bạn đang xem đó.
22. Hàm RIGHT
Công dụng: Dùng để trích xuất phần bên phải của một chuỗi một hoặc nhiều ký tự tùy theo sự chỉ định của bạn
Công thức: =RIGHT(text [,num_chars])
___text: chuỗi văn bản cần trích xuất ký tự
___num_chars: số ký tự cần trích ra phía bên phải của chuỗi text, mặc định là 1
- num_chars phải là số nguyên dương
- Nếu num_chars lớn hơn độ dài của chuỗi thì kết quả trả về sẽ là toàn bộ chuỗi text
Ví dụ:___=RIGHT(“Karen Elizabeth Hammond”, 7) → Hammond
23. Hàm SUBSTITUTE
Công dụng: Dùng để thay thế một chuỗi này bằng một chuỗi khác. Hàm này cũng tương tự hàm REPLACE(), nhưng dễ sử dụng hơn.
Công thức: =SUBSTITUTE(text, old_text, new_text [,instance_num])
___text: chuỗi văn bản gốc, cần được xử lý
___old_text: chuỗi văn bản cần được thay thế
___new_text: chuỗi văn bản sẽ thay thế vào
___instance_num: số lần thay thế old_text bằng new_text, nếu bỏ qua thì tất cả old_text tìm được sẽ được thay thế bằng new_text
Ví dụ: Để thay số 2007 bằng 2008 trong câu Expense Budget for 2007
Dùng công thức như sau:
=SUBSTITUTE("Expense Budget for 2007", "2007", "2008") → Expense Budget for 2008
24. Hàm TEXT
Công dụng: Chuyển đổi một số thành dạng văn bản (text) theo định dạng được chỉ định
Công thức: =TEXT(number,format)
___number là số cần chuyển sang dạng text
___format: kiểu định dạng số hoặc ngày tháng năm
Ví dụ: Công thức sau sẽ dùng hàm AVERAGE() để tính số trung bình của mảng cell A1:A31, và dùng hàm TEXT để chuyển đổi kết quả ra dạng nhiệt độ
=”Nhiệt độ trung bình của lò phản ứng là “ & TEXT(AVERAGE(A1:A31), “#,##0.00°F”) → Nhiệt độ trung bình của lò phản ứng là 7,560.00°F
Thể hiện thời gian cập nhật bảng tính lần sau cùng
Có bao giờ bạn muốn biết được lần cuối cùng bảng tính này có thay đổi là khi nào không?
Một trong những cách làm là đặt chế độ tính toán của Excel là thủ công (manual calculation mode) và sử dụng hàm NOW() trong một cell nào đó (hàm này cho ra kết quả là ngày giờ hiện tại).
Như vậy, hàm NOW() sẽ không tự cập nhật trừ phi bảng tính được lưu (save), hoặc được tính toán lại (recalculate), và nhờ đó mà bạn luôn biết được lần cuối cùng bảng tính này có thay đổi là khi nào.
Ví dụ, bạn dùng công thức:
=”This workbook last updated: “ & NOW()
Tuy nhiên, nếu chỉ có chừng đó, thì e rằng kết quả bạn nhận được sẽ không như ý, vì có thể nó sẽ là như thế này:
This workbook last updated: 38572.51001
Do Excel luôn hiểu ngày và giờ là một con số, cho nên con số này: 38572.51001 được hiểu là: con số phía bên trái của dấu phân cách (38572) là số chỉ ngày, và con số phía bên phải của dấu phân cách (51001) là số chỉ giờ... Chỉ có ai rành lắm mới may ra đọc và hiểu được!
Vì vậy, ta nên sửa công thức trên bằng cách dùng hàm TEXT để định dạng nó ra kiểu ngày giờ đàng hoàng, chẳng hạn kiểu DD/MM/YYYY HH:MM
=”This workbook last updated: “ & TEXT(NOW(), “dd/mm/yy hh:mm”) → This workbook last updated: 08/08/2005 12:14:00 PM
25. Hàm UPPER
Công dụng: Hàm UPPER đổi tất cả các ký tự trong một chuỗi văn bản thành chữ in hoa.
Công thức: = UPPER(text)
___với text là một chuỗi, hoặc tham chiếu đến một chuỗi cần định dạng
Ví dụ:
Công thức sau đây sẽ nối chuỗi văn bản trong cell A5 (Giải pháp) và cell B5 (Excel) lại với nhau, ở giữa có một dấu cách, tất cả ký tự cell mới toàn là chữ in hoa.
=UPPER(A5) & " " & UPPER(B5) → GIẢI PHÁP EXCEL