Friday, May 31, 2013

Hàm xử lý văn bản trong excel 2010

HÀM XỬ LÝ VĂN BẢN
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 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
=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

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
=DOLLAR Chuyển định dạng số thành tiền tệ

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
=FIXED Chuyển một số sang định dạng văn bản
=LEFT(X,n) Hàm LEFT lấy n ký tự từ bên trái sang của văn bản X
=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

Hàm MID 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])=MID(X, m, n)
Hàm MID lấy n ký tự trong chuỗi X bắt đầu từ vị trí m.
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

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
Hàm RIGHT 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])=RIGHT(X,n)

Hàm RIGHT lấy n ký tự từ bên phải sang của văn bản X

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
=REPT Lặp lại một chuỗi
=SUBSTITUTE Thay thế một chuỗi xác định

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à ~?)
=TEXT Chuyển một số sang text.

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 - Kiểm tra dữ liệu kiểu text
Công thức: =T(value)
Hàm này ít khi được dùng.

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
HÀM THAY THẾ CHUỖI NÀY BẰNG CHUỖI KHÁC (Substituting One Substring for Another)
Có nhiều chương trình có khả năng tìm kiếm một số đoạn văn và thay thế nó bằng đoạn văn khác.
Excel cũng có khả năng làm chuyện đó bằng cách dùng hàm. Đó là hàm REPLACE và hàm SUBSTITUTE.
=TRIM Xóa những ký tự trắng bên trong chuỗi.
=UPPER Chuyển ký tự thường thành hoa.

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ó.
*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)
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é!
*Trích xuất họ, tên đệm và tên (ví dụ với tên tiếng Anh, phần tên đệm được viết tắt)
Cách làm giống như bài Trích xuất họ và tên ở trên, tuy nhiên có khác một chút, để trích thêm phần tên đệm.
Giả sử Họ và Tên (full name) nằm ở cell A2, và đang có giá trị là Karen E. Hammond
Đầu tiên, như bài trên, dùng công thức sau để tách phần Tên (first name):
=LEFT(A2, FIND(" ", A2) - 1) → Karen
Công thức FIND(" ", A2) sẽ cho kết quả là 6, là vị trí của khoảng trắng đầu tiên (sau chữ Karen).
Để tìm vị trí của khoảng trắng thứ hai, thì bạn phải gán vị trí bắt đầu tìm (start_num) là 7, hoặc là bằng kết quả của FIND(" ", A2) cộng thêm 1:
=FIND(" ", A2, FIND(" ",A2) + 1)
Rồi dùng kết quả của công thức này làm tham số cho hàm RIGHT() để trích ra phần Họ (last name):
=RIGHT(A2, LEN(A2) - FIND(" ", A2, FIND(" ", A2) +1)) → Hammond
Để trích phần tên đệm, dùng hàm FIND() để tìm vị trí của dấu chấm (.) rồi đưa vào trong công thức của hàm MID() để tìm ký tự đứng trước dấu chấm:
=MID(A2, FIND(".", A2) - 1, 1) → E

1 comment:

  1. Học Excel nâng cao chắc mới biết tới mấy hàm này. Mình mới tự học Excel nên chỉ biết tới những hàm cơ bản, mà còn chưa nhớ hết. Bài viết hữu ích!

    ReplyDelete