Microsoft Excel là một ứng dụng chính cho bất cứ ai phải làm việc liên quan đến con số, từ sinh viên đến kế toán. Nhưng tính hữu ích của nó còn mở rộng ra ngoài cơ sở dữ liệu lớn, nó có thể làm được rất nhiều điều tuyệt vời với văn bản. Các hàm Excel được liệt kê dưới đây sẽ giúp bạn phân tích, chỉnh sửa, chuyển đổi, thay đổi văn bản và tiết kiệm nhiều giờ thực hiện công việc nhàm chán và lặp đi lặp lại.
Các hàm định dạng văn bản trong Excel
Chỉnh sửa không phá hủy (Non-Destructive Editing)
Một trong những nguyên tắc sử dụng chức năng văn bản Excel là chỉnh sửa không phá hủy. Điều này có nghĩa là bất cứ khi nào sử dụng một hàm để thay đổi văn bản trong một hàng hoặc cột, văn bản đó sẽ vẫn không thay đổi và văn bản mới sẽ được đặt trong một hàng hoặc cột mới. Định dạng này rất hữu ích, đặc biệt khi làm việc với một bảng tính lớn.
Một cách để tận dụng tính năng này là lưu bảng tính ban đầu vào trang tính đầu tiên trong tài liệu và các bản sao được chỉnh sửa sau đó trong các trang tính khác. Bằng cách đó, bất kể đã thực hiện bao nhiêu chỉnh sửa, bạn sẽ luôn có dữ liệu ban đầu đang làm việc.
Các ký tự byte đơn và byte kép
Một số hàm được thảo luận ở đây đề cập đến các bộ ký tự byte đơn và byte kép. Trong một số ngôn ngữ như Trung Quốc, Nhật Bản và Hàn Quốc, mỗi ký tự (hoặc một số ký tự) sẽ có hai khả năng hiển thị: một là được code bằng hai byte (ký tự byte kép) và một được code bằng một byte (ký tự byte đơn). Bạn có thể thấy sự khác biệt trong các ký tự này ở đây:
Như ở hình trên, các ký tự byte kép lớn hơn và thường dễ đọc hơn. Tuy nhiên, trong một số tình huống, yêu cầu một hoặc nhiều loại mã hoá này. Có một số hàm dưới đây liên quan đến các ký tự một byte và byte kép.
Nhóm hàm xử lý chuỗi ký tự
Hàm CHAR và UNICHAR
Hàm CHAR lấy một số ký tự và trả về ký tự tương ứng. Ví dụ, nếu có một danh sách các số ký tự, hàm CHAR sẽ biến chúng thành các ký tự thường sử dụng. Cú pháp khá đơn giản:
=CHAR([text])
Đối số [text] có thể có dạng của một tham chiếu ô hoặc một ký tự, do vậy =CHAR(B7) và =CHAR(84) đều hoạt động. Lưu ý rằng khi sử dụng hàm CHAR, nó sẽ sử dụng mã hóa mà máy tính được thiết lập, do đó, =CHAR(84) có thể khác với nhau đối với người sử dụng máy tính Windows và máy Mac.
Nếu số đang chuyển đổi là số ký tự Unicode và sử dụng Excel 2013, bạn sẽ cần phải sử dụng hàm UNICHAR. Các phiên bản trước của Excel không có hàm này.
Hàm CODE và UNICODE
Hàm CODE và UNICODE có chức năng ngược lại so với hàm CHAR và UNICHAR. Những hàm này lấy một ký tự và trả lại số mã hoá mà bạn đã chọn (hoặc cài đặt mặc định trên máy tính). Một điều quan trọng cần lưu ý là nếu chạy hàm này trên một chuỗi chứa nhiều ký tự, nó sẽ chỉ trả về tham chiếu ký tự cho ký tự đầu tiên trong chuỗi. Cú pháp rất giống nhau:
=CODE([text])
Trong trường hợp này, [text] là một ký tự hoặc một chuỗi. Và nếu muốn tham chiếu Unicode thay vì mặc định của máy tính, bạn sẽ sử dụng hàm UNICODE (trong phiên bản Excel 2013 hoặc mới hơn).
Nhóm hàm phân tích văn bản
Các hàm trong phần này sẽ giúp bạn có được thông tin về văn bản trong một ô.
Hàm LEN
LEN là một hàm rất đơn giản, nó sẽ trả về chiều dài của một chuỗi, được sử dụng để đếm số chữ trong một một loại các ô khác nhau. Đây là cú pháp:
=LEN([text])
Đối số [text] là ô hoặc các ô mà bạn muốn đếm. Trong ví dụ dưới đây, sử dụng hàm LEN trên một ô có chứa tên thành phố “Austin”, nó trở về kết quả là 6. Khi sử dụng cho tên thành phố "South Bend", nó sẽ trả về kết quả 10. Một khoảng cách được tính như một ký tự với hàm LEN, vì vậy hãy ghi nhớ nếu bạn đang sử dụng nó để đếm số chữ trong một ô nhất định.
Hàm LENB thực hiện chức năng tương tự nhưng làm việc với các ký tự byte kép. Nếu muốn đếm một loạt bốn ký tự byte kép sử dụng hàm LEN, kết quả sẽ là 8, với LENB là 4 (nếu có một DBCS kích hoạt như là ngôn ngữ mặc định).
Hàm FIND
Chắc có nhiều người thắc mắc tại sao cần sử dụng hàm FIND trong khi có thể sử dụng CTRL + F hoặc Edit > Find. Câu trả lời nằm ở tính cụ thể mà bạn đang tìm kiếm. Thay vì tìm kiếm toàn bộ tài liệu, bạn có thể chọn bất cứ ký tự nào của mỗi chuỗi. Cú pháp sau sẽ giúp làm sáng tỏ định nghĩa khó hiểu này:
=FIND([find_text], [within_text], [start_num])
[find_text] là chuỗi đang tìm kiếm, [within_text] là ô hoặc các ô trong đó Excel sẽ tìm kiếm văn bản đó, và [start_num] là ký tự đầu tiên mà nó sẽ xem xét. Điều quan trọng cần chú ý là chức năng này phân biệt chữ hoa và chữ thường.
Ở ví dụ này là bảng số ID của mỗi học sinh, gồm một chuỗi chữ số sáu ký tự. Mỗi ID bắt đầu với một chữ số, chữ M cho "nam", một dãy gồm hai chữ cái để cho biết mức độ thành tích của học sinh (HP là thành tích cao, SP cho trung bình, LP cho thấp và UP/XP là không rõ), và một chuỗi cuối cùng gồm hai con số. Hãy sử dụng hàm FIND để tìm học sinh có thành tích cao. Dưới đây là cú pháp chúng ta sẽ sử dụng:
=FIND("HP", A2, 3)
Hàm này sẽ cho biết liệu HP có xuất hiện sau ký tự thứ ba trong ô không. Áp dụng cho tất cả các ô trong cột ID, chúng ta có thể nhìn thấy ngay kết quả có thành tích cao hay không (lưu ý rằng 3 được trả lại bởi hàm này là ký tự mà HP được tìm thấy).
Giống như hàm LEN và LENB, FINDB được sử dụng cho cùng một mục đích như FIND, chỉ khác là cho bộ ký tự byte kép. Nếu đang sử dụng DBCS và chỉ định ký tự thứ tư với FIND, việc tìm kiếm sẽ bắt đầu ở ký tự thứ hai nhưng với hàm FINDB hoàn toàn có thể giải quyết vấn đề này.
Lưu ý, hàm FIND phân biệt chữ hoa và chữ thường, do đó bạn có thể tìm kiếm một chữ viết hoa cụ thể. Nếu không muốn phân biệt chữ hoa chữ thường có thể sử dụng hàm SEARCH, lấy các đối số giống nhau và trả về cùng một giá trị.
Hàm EXACT
Nếu cần phải so sánh hai giá trị để xem chúng có giống nhau không, EXACT là hàm mà bạn cần. Khi sử dụng hàm EXACT với hai chuỗi, nó sẽ trả về TRUE nếu giống hệt nhau, và FALSE nếu khác. Vì hàm EXACT phân biệt chữ hoa và chữ thường, nó sẽ trả về FALSE nếu chuỗi có "Test" và "test". Đây là cú pháp cho EXACT:
=EXACT([text1], [text2])
Cả hai đối số đều rất dễ hiểu, nó là những chuỗi mà bạn muốn so sánh. Trong bảng tính ví dụ dưới đây, chúng ta sẽ sử dụng chúng để so sánh hai điểm SAT và Reported với cú pháp sau:
=EXACT(G2,F2)
Lặp lại công thức cho mỗi hàng trong cột cho chúng ta có kết quả:
Nhóm hàm chuyển đổi văn bản
Các hàm này lấy các giá trị từ một ô và biến chúng thành một định dạng khác, ví dụ, từ một số đến một chuỗi hoặc từ một chuỗi đến một số.
Hàm TEXT
Hàm TEXT chuyển đổi dữ liệu số thành văn bản và cho phép định dạng nó bằng những cách cụ thể. Điều này có thể hữu ích nếu bạn đang định sử dụng dữ liệu Excel trong tài liệu Word. Hãy xem cú pháp và sau đó xem cách sử dụng nó:
=TEXT([text], [format])
Đối số [format] cho phép chọn cách muốn số xuất hiện trong văn bản. Có một số toán tử khác nhau có thể sử dụng để định dạng văn bản, nhưng chúng ta sẽ tuân thủ các nguyên tắc đơn giản ở đây. Hàm TEXT thường được sử dụng để chuyển đổi các giá trị tiền tệ, vì vậy chúng ta sẽ bắt đầu với điều đó.
Trong ví dụ này, chúng ta sẽ biến con số trong cột “Tuition” thành giá trị tiền tệ. Dưới đây là cú pháp:
=TEXT(G2,"$#,###")
Sử dụng chuỗi định dạng này sẽ cho con số được đứng trước bởi biểu tượng đô la và bao gồm một dấu phẩy sau hàng trăm. Đây là điều xảy ra khi áp dụng vào bảng tính:
Mỗi số bây giờ đã được định dạng đúng. Bạn có thể sử dụng hàm TEXT để định dạng số, giá trị tiền tệ, ngày, giờ.
Hàm FIXED
Tương tự như TEXT, hàm FIXED có đầu vào và định dạng dưới dạng văn bản, tuy nhiên, hàm FIXED chuyển đổi các số thành văn bản và cung cấp một vài tùy chọn cụ thể để định dạng và làm tròn đầu ra. Đây là cú pháp:
=FIXED([number], [decimals], [no_commas])
Đối số [number] chứa tham chiếu đến ô muốn chuyển đổi sang văn bản. Đối số [decimals] là một đối số tùy chọn cho phép chọn số phần thập phân được giữ lại trong chuyển đổi. Nếu đây là 3, bạn sẽ nhận được một số như 13,482. Nếu sử dụng một số âm cho thập phân, Excel sẽ làm tròn số. Chúng ta sẽ xem xét điều đó trong ví dụ dưới đây. Đối số [no_commas], nếu đặt thành TRUE, sẽ loại trừ các dấu phẩy từ giá trị cuối cùng.
Chúng ta sẽ sử dụng điều này để làm tròn số học phí đã sử dụng trong ví dụ trước cho phần nghìn gần nhất.
=FIXED(G2, -3)
Hàm VALUE
Hàm này ngược lại với hàm TEXT, nó lấy bất kỳ ô nào và biến thành một số. Điều này đặc biệt hữu ích nếu nhập một bảng tính hoặc sao chép và dán một số lượng lớn dữ liệu và được định dạng dưới dạng văn bản. Dưới đây là cú pháp:
=VALUE([text])
Excel sẽ công nhận những định dạng đã được chấp nhận về các số, thời gian và ngày tháng, sau đó sẽ chuyển đổi chúng thành những con số để có thể sử dụng với các hàm và công thức số.
Hàm DOLLAR
Tương tự như hàm TEXT, hàm DOLLAR chuyển giá trị sang văn bản, nhưng nó cũng thêm một dấu đô la và chọn số thập phân:
=DOLLAR([text], [decimals])
Nếu để trống đối số [decimals], nó sẽ mặc định là 2. Nếu đưa một số âm cho đối số [decimals], số này sẽ được làm tròn từ bên trái của số thập phân.
Hàm ASC
Như trước chúng ta thảo luận về ký tự byte đơn và byte kép, đây là cách bạn chuyển đổi giữa chúng. Cụ thể, hàm này chuyển đổi các ký tự byte kép thành ký tự byte đơn. Nó có thể được sử dụng để tiết kiệm một số không gian trong bảng tính. Đây là cú pháp:
=ASC([text])
Hàm này khá đơn giản, chỉ cần chạy hàm ASC trên bất kỳ văn bản nào muốn chuyển đổi. Để xem nó hoạt động, chúng ta sẽ chuyển đổi bảng tính có chứa một số chữ katakana của Nhật, chúng thường được thể hiện dưới dạng các ký tự byte kép. Hãy thay đổi chúng thành ký tự byte đơn.
Hàm JIS
Hàm JIS thực hiện chức năng ngược lại so với hàm ASC, nó chuyển đổi ký tự byte đơn thành byte kép. Giống như ASC, cú pháp rất đơn giản:
=JIS([text])
Nhóm hàm chỉnh sửa văn bản
Một trong những điều hữu ích nhất bạn có thể làm với văn bản trong Excel là lập trình thực hiện các sửa đổi đối với nó. Các hàm sau đây sẽ giúp bạn nhập văn bản và nhận được vào định dạng chính xác nhất.
Hàm UPPER, LOWER và PROPER
Đây đều là những hàm rất đơn giản và dễ hiểu. Hàm UPPER biến văn bản thành chữ viết hoa, hàm LOWER biến văn bản thành chữ thường, và hàm PROPER viết hoa chữ cái đầu tiên trong mỗi từ. Đây là cú pháp:
=UPPER/LOWER/PROPER([text])
Chọn ô hoặc dải ô mà văn bản cho đối số [text].
Hàm CLEAN
Nhập dữ liệu vào Excel thường rất dễ, nhưng đôi khi người dùng gặp phải những ký tự không mong muốn. Điều này phổ biến nhất khi có các ký tự đặc biệt trong tài liệu gốc mà Excel không thể hiển thị. Thay vì xem qua tất cả các ô chứa các ký tự đó, bạn có thể sử dụng hàm CLEAN với cú pháp sau:
=CLEAN([text])
Đối số [text] chỉ đơn giản là vị trí của văn bản muốn dọn sạch. Trong bảng tính ví dụ này, chúng ta sẽ thêm một vài ký tự không thể in được tới các tên trong Cột A. Sử dụng hàm CLEAN để chuyển văn bản tới Cột G mà không có các ký tự đó:
Bây giờ, Cột G chứa tên mà không có ký tự không in được. Lệnh này không chỉ hữu ích cho văn bản mà còn hữu ích đối với các con số trong các công thức.
Hàm TRIM
Mặc dù hàm CLEAN đã loại bỏ các ký tự không in được, nhưng hàm TRIM sẽ loại bỏ thêm khoảng trắng ở đầu hoặc cuối chuỗi văn bản khi sao chép văn bản từ một tài liệu Word hoặc văn bản thuần túy, chỉ cần sử dụng cú pháp này:
=TRIM([text])
Nhóm hàm thay thế văn bản
Đôi khi, người dùng sẽ cần phải thay thế các chuỗi cụ thể trong văn bản bằng một chuỗi các ký tự khác. Sử dụng các công thức Excel nhanh hơn nhiều so với tìm và thay thế, đặc biệt khi đang làm việc với một bảng tính rất lớn.
Hàm SUBSTITUTE
Nếu đang làm việc với rất nhiều văn bản, đôi khi bạn sẽ cần thực hiện một số thay đổi lớn, như nhét một chuỗi văn bản cho một chuỗi văn bản khác, ghi sai ngày tháng trong các hoá đơn hoặc gõ tên sai. Đó là những gì hàm SUBSTITUTE được sử dụng với cú pháp:
=SUBSTITUTE([text], [old_text], [new_text], [instance])
Đối số [text] chứa vị trí của các ô muốn thực hiện thay thế, [old_text] và [new_text] là văn bản cũ và mới. Đối số [instance] cho phép chỉ định một đối tượng cụ thể của văn bản cũ để thay thế. Vì vậy, nếu chỉ muốn thay thế đối tượng thứ ba của văn bản cũ, bạn phải nhập "3" cho đối số này. SUBSTITUTE sẽ sao chép tất cả các giá trị khác.
Ví dụ, chúng ta sẽ sửa lỗi chính tả trong bảng tính dưới dây. Giả sử "Honolulu" vô tình bị đánh vần là "Honululu". Đây là cú pháp được sử dụng để sửa nó:
=SUBSTITUTE(D28, "Honululu", "Honolulu")
Sau khi kéo công thức cho các ô xung quanh, bạn sẽ thấy tất cả các ô trong cột D đã được sao chép, ngoại trừ các ô chứa từ sai chính tả "Honululu" được thay thế bằng chính tả đúng.
Hàm REPLACE
Hàm REPLACE giống như hàm SUBSTITUTE, nhưng thay vì thay thế một chuỗi ký tự cụ thể, nó sẽ thay thế các ký tự ở một vị trí cụ thể. Đây là cú pháp của hàm REPLACE:
=REPLACE([old_text], [start_num], [num_chars], [new_text])
Đối số [old_text] là nơi chỉ định các ô muốn thay thế văn bản, [start_num] là ký tự đầu tiên muốn thay thế, và [num_chars] là số ký tự sẽ được thay thế. Đối số [new_text] là văn bản mới sẽ được chèn vào các ô, nó cũng có thể là một tham chiếu ô.
Ví dụ, trong bảng tính dưới đây, ID sinh viên có các ký tự HP, SP, LP, UP và XP. Chúng ta muốn loại bỏ chúng và thay đổi tất cả các thành NP. Dưới đây là cú pháp:
=REPLACE(A2, 3, 2, "NP")
Tất cả các chuỗi hai kí tự từ Cột A đã được thay thế bằng "NP" trong cột G.
Nhóm các hàm thao tác văn bản khác
Ngoài việc thay đổi chuỗi, bạn cũng có thể thực hiện những thay đổi nhỏ trong một chuỗi (hoặc sử dụng các chuỗi nhỏ hơn để tạo thành những chuỗi lớn hơn). Đây là một số hàm văn bản được sử dụng phổ biến nhất trong Excel.
Hàm CONCATENATE
Hàm CONCATENATE được sử dụng khi có nhập hai ô với nhau. Đây là cú pháp:
=CONCATENATE([text1], [text2], [text3]...)
Điều khiến cho hàm CONCATENATE trở nên hữu ích là các đối số [text] có thể là văn bản thuần như "Arizona" hoặc các tham chiếu ô như "A31". Bạn thậm chí có thể sử dụng cả hai. Điều này có thể giúp bạn tiết kiệm rất nhiều thời gian khi cần kết hợp hai cột văn bản, như cần tạo cột "Full Name” từ "First Name" và cột "Last Name". Dưới đây là cú pháp:
=CONCATENATE(A2, " ", B2)
Lưu ý, đối số thứ hai là là khoảng trống. Nếu không có khoảng trắng này, tên sẽ được nối trực tiếp, không có dấu cách giữa tên và họ.
Bây giờ chúng ta có một cột với tên đầy đủ của mọi người. Bạn có thể dễ dàng sử dụng lệnh này để kết hợp mã vùng và số điện thoại, tên và số nhân viên, thành phố và tiểu bang, hoặc thậm chí là các ký hiệu và số tiền tệ.
Ngoài ra, bạn có thể rút ngắn hàm CONCATENATE thành một dấu hiệu. Để tạo công thức ở trên bằng cách sử dụng ký hiệu "&", chúng ta sẽ gõ:
=A2 & " " & B2
Bạn cũng có thể sử dụng nó để kết hợp các tham chiếu ô và các dòng văn bản, như sau:
=E2 & ", " & F2 & ", USA"
Hàm LEFT và RIGHT
Thông thường, nhiều người chỉ muốn làm việc với chỉ một vài ký tự đầu tiên (hoặc cuối cùng) của một chuỗi văn bản. LEFT và RIGHT cho phép bạn làm điều đó bằng cách chỉ trả lại một số ký tự nhất định bắt đầu từ ký tự trái hoặc phải trong một chuỗi. Đây là cú pháp:
=LEFT/RIGHT([text], [num_chars])
Đối số [text] là văn bản gốc và [num_chars] là số ký tự muốn trả lại. Chúng ta hãy xem ví dụ khi nào cần thực hiện hai hàm trên. Giả sử bạn đã nhập một số địa chỉ và mỗi địa chỉ đều chứa cả chữ viết tắt của tiểu bang và quốc gia. Chúng ta có thể sử dụng LEFT để có được chỉ các chữ viết tắt, sử dụng cú pháp này:
=LEFT(E2, 2)
Hàm MID
Hàm MID giống như LEFT và RIGHT, nhưng cho phép bạn kéo các ký tự ra khỏi vị trí giữa một chuỗi, bắt đầu từ vị trí chỉ định. Đây là cú pháp:
=MID([text], [start_num], [num_chars])
Đối số [start_num] là ký tự đầu tiên sẽ được trả về. Điều này có nghĩa là nếu muốn ký tự đầu tiên trong một chuỗi được bao gồm trong kết quả của một hàm, đây sẽ là "1". Đối số [num_chars] là số ký tự sau khi ký tự bắt đầu sẽ được trả lại. Trong bảng tính ví dụ, bây giờ chúng ta đã thêm tiêu đề vào tên, nhưng sẽ tách những người đó ra khỏi họ "Mr. Martin " để trả về kết quả dưới dạng" Martin ". Đây là cú pháp:
=MID(A2, 5, 15)
Ở đây, chúng ta sẽ sử dụng "5" làm ký tự bắt đầu, bởi vì chữ cái đầu tiên của tên người đó là chữ thứ năm ("Mr." chiếm 4 khoảng trắng). Hàm sẽ trả lại 15 chữ cái tiếp theo.
Hàm REPT
Nếu cần phải lấy một chuỗi và lặp lại nó một số lần bạn nên sử dụng hàm REPT. Cung cấp cho hàm REPT một chuỗi ("abc") và một số (3) lần muốn lặp lại, và Excel sẽ cho kết quả chính xác những gì bạn yêu cầu ("abcabcabc"). Dưới đây là cú pháp rất dễ dàng:
=REPT([text], [number])
Đối số [text] là chuỗi cơ sở; [number] là số lần bạn muốn nó lặp lại.
Xem thêm: Tổng hợp những phím tắt đắt giá trong Microsoft Excel