Nhóm hàm xử lý chuỗi thống kê theo điều kiện

• blog.hocexcel.online
• Excel cơ bản
• Các hàm xử lý chuỗi, kí tự trong Excel

• Chuyên mục:
• Excel cơ bản

Đây là bài số #38 trong loạt videos Hướng dẫn Excel từ đơn giản đến phức tạp

• Video bao gồm các bài hướng dẫn nhỏ sau đây:
• Các kiến thức liên quan:
  • Có thể bạn sẽ quan tâm đến những bài viết sau

Video bao gồm các bài hướng dẫn nhỏ sau đây:

• Hướng dẫn sử dụng hàm LEFT trong Excel
• Hướng dẫn sử dụng hàm RIGHT trong Excel
• Hướng dẫn sử dụng hàm MID trong Excel
• Hướng dẫn sử dụng hàm FIND trong Excel
• Hướng dẫn sử dụng hàm SEARCH trong Excel
• Hướng dẫn sử dụng hàm REPLACE trong Excel
• Hướng dẫn sử dụng hàm SUBSTITUTE trong Excel
• Hướng dẫn sử dụng hàm EXACT trong Excel
• Hướng dẫn sử dụng hàm TRIM trong Excel

• Các hàm Excel Phổ biến nhất trong công việc
• //windhampharmacy.com Antibiotics online
• Các Hàm excel cần thiết cho kế toán viên, kiểm toán viên
• Các Hàm excel cần thiết cho dân văn phòng
• Các Hàm Vlookup Hướng dẫn chi tiết trong excel 2007,2010,2013
• Hàm Hlookup Hướng dẫn chi tiết trong excel 2007,2010,2013
• Hàm Sum Hướng dẫn chi tiết trong excel 2007,2010,2013
• Hàm Count Hướng dẫn chi tiết trong excel 2007,2010,2013
• Hàm Max, Min Hướng dẫn chi tiết trong excel 2007,2010,2013
• Hàm IF Hướng dẫn chi tiết trong excel 2007,2010,2013 Hướng dẫn cơ bản Hàm If
• Hàm index, Hàm Match, Dsum Hướng dẫn chi tiết trong excel 2007,2010,2013
• Tạo mục lục tự động, sắp xếp tên theo thứ tự abc trong excel 2007, 2010, 2013
• Định dạng ngày tháng trong Excel2007, 2010 ,2013

Các kiến thức liên quan:

• Excel cơ bản

➤ Subscribe: //youtube.com/user/ductnguy?subscribe_confirmation=1

➤ Facebook group: //www.facebook.com/groups/569100319856001/

➤ Facebook page: //www.facebook.com/www.hocexcel.online

Có thể bạn sẽ quan tâm đến những bài viết sau

• Biểu đồ động trong Excel, cách thiết lập dữ liệu
• Đĩa DVD Excel Cơ bản đến phức tạp
• Điều khiển Word từ Excel – thay cho Mail Merge
• #89 Sử dụng SUMIF với dấu * và ? để đếm theo điều kiện
• Sử dụng tính năng Goal Seek trong Excel

Tác giả: dtnguyen [Nguyễn Đức Thanh]

@ Học Excel Online | DTNguyen.business

· · ·

Khóa học mới xuất bản

• Tất cả
• Python
• SQL
• VBA

Bài viết liên quan

Khóa học liên quan

Sản phẩm

Về tác giả

Danh mục khóa học

© Học Excel Online. All rights reserved.

Mục lục

1. 1. Hàm Upper[], Lower[], Proper[]
   2. Hàm Concatenate[]
   3. Hàm Find[]
   4. Hàm Search[]
   5. Hàm Exact[]
   6. Hàm Replace[]

1. Hàm Upper[], Lower[], Proper[]

Tiếp theo phần 1, IT For Student xin giới thiệu đến các bạn các hàm xử lý chuỗi phần 2. Trong đó, đầu tiên phải nhắc đến là 3 hàm cơ bản và khá thông dụng, bao gồm hàm UPPER, LOWER và PROPER. Ba hàm này thuộc nhóm hàm văn bản [text functions] có chức năng chuyển đổi các kiểu chữ khi xử lý văn bản và chuỗi.

Chi tiết:

- Hàm UPPER chuyển đổi tất cả các ký tự trong chuỗi văn bản thành chữ hoa.

- Hàm LOWER chuyển tất cả các ký tự trong chuỗi văn bản thành chữ thường.

- Hàm PROPER chuyển đổi chữ cái đầu tiên của mỗi từ thành chữ hoa, còn lại các chữ sau của mỗi từ đều là chữ thường. Ngoài ra, chữ cái đầu tiên đứng sau bất kỳ ký tự nào không phải là chữ cũng được viết hoa.

Cú pháp của hàm:

=UPPER[text]

=LOWER[text]

=PROPER[text]

Trong đó:

- text là chuỗi văn bản mà các bạn muốn chuyển đổi.

Ví dụ:

=UPPER["tiN hỌc cho Sinh Viên"] = TIN HỌC CHO SINH VIÊN

=LOWER[tiN hỌc cho Sinh Viên] = tin học cho sinh viên

=PROPER[tiN hỌc cho Sinh Viên] = Tin Học Cho Sinh Viên

=PROPER[tiN 22hỌc cho Sinh Viên] = Tin 22Học Cho Sinh Viên

Để được hướng dẫn chi tiết hơn về các hàm xử lý chuỗi [phần 2] có trong bài viết này, các bạn có thể tham khảo video dưới đây:

2. Hàm Concatenate[]

Trong Excel, khi muốn nối các chuỗi văn bản hoặc liên kết các ký tự ở các ô lại với nhau để tạo thành một chuỗi hợp nhất, chúng ta sử dụng hàm CONCATENATE. Sau đây IT For Student sẽ đưa ra những ví dụ cụ thể để các bạn có thể hiểu rõ hơn về hàm này!

Cú pháp của hàm:

=CONCATENATE[text1;[text2];…]

Trong đó:

- text1: bắt buộc, chuỗi cần nối thứ nhất.

- text2: không bắt buộc, là chuỗi cần nối tiếp theo [tối đa 255 chuỗi văn bản cần nối].

Cùng IT For Student xét ví dụ sau:

Sau đây, chúng ta dùng hàm CONCATENATE để nối Họ và Tên của học sinh ở hai cột khác nhau lại thành một chuỗi văn bản đầy đủ.

Theo hình bên dưới, tại ô B2 là Họ và ô C2 là Tên.

Chúng ta dùng hàm =CONCATENATE[B2;" ";C2] tại ô D2 để được kết quả là Họ và Tên đã được liên kết lại. Sau đó thực hiện kéo để sao chép công thức cho các dòng còn lại. 

Lưu ý, khi sử dụng hàm CONCATENATE, các chuỗi văn bản sẽ nối trực tiếp vào nhau. Vì thế nếu chúng ta muốn có khoảng trống để cách giữa các chuỗi văn bản, chúng ta sẽ nối chúng với ký tự đại diện cho dấu cách là " ". Dưới đây là hình minh hoạ nếu như chúng ta không sử dụng " " để nối Họ và Tên, ta có:

Một lưu ý khác, Các bạn có thể sử dụng dấu & thay vì dùng hàm CONCATENATE để nối chuỗi. Rất hữu ích, các bạn có thể tham khảo như hình bên dưới nhé!

3. Hàm Find[]

Hàm FIND là một hàm dùng để dò tìm vị trí của một ký tự hoặc một chuỗi văn bản con trong Excel. Kết quả trả về sẽ là vị trí của ký tự hoặc chuỗi văn bản con vừa dò tìm.

Cú pháp của hàm:

=FIND[find_text; within_text; [start_num]]

Trong đó:

- find_text: bắt buộc, là ký tự hoặc chuỗi văn bản con mà bạn muốn tìm vị trí.

- within_text: bắt buộc, chuỗi văn bản bạn dùng để tìm kiếm, có thể là tham số hoặc được gõ trực tiếp vào.

- start_num: không bắt buộc, vị trí của ký tự các bạn muốn bắt đầu tìm kiếm. Nếu để trống đối số này, mặc định sẽ tìm kiếm từ vị trí từ ký tự đầu tiên của chuỗi văn bản. 

Ví dụ:

Hãy tìm vị trí chữ V trong chuỗi "Tin Học Văn Phòng"

Ta có công thức =FIND["V";"Tin Học Văn Phòng""] => Kết quả trả về là 9, tức chữ "V" nằm ở vị trí thứ 9 trong chuỗi "Tin Học Văn Phòng". 

Lưu ý:

- Hàm FIND phân biệt chữ hoa và chữ thường.

- Hàm FIND không cho phép sử dụng ký tự đại diện.

- Nếu find_text bị bỏ trống, kết quả trả về là ký tự đầu tiên của chuỗi văn bản dùng để tìm kiếm within_text.

Ví dụ: =FIND["";"IT For Student"] => trả về là 1.

- Nếu find_text có nhiều ký tự, kết quả trả về sẽ là vị trí của ký tự đầu tiên.

Ví dụ: =FIND["or";"IT For Student"] => trả về là 5. Vì "o" là ký tự đầu tiên trong "or" nên kết quả trả về là vị trí của "o".

- Nếu find_text không có trong within_text thì kết quả trả về là giá trị lỗi #VALUE!

Ví dụ: =FIND["a";"IT For Student"] => trả về là lỗi #VALUE! vì không có ký tự "a" nào trong chuỗi "IT For Student"

- Nếu within_text có chứa nhiều lần xuất hiện của find_text thì kết quả trả về là lần xuất hiện đầu tiên.

Ví dụ: =FIND["n";"tin học cho sinh viên"] => kết quả trả về là 3. Mặc dù có nhiều lần ký tự "n" xuất hiện trong chuỗi "tin học cho sinh viên" nhưng kết quả trả về là lần đầu tiên xuất hiện của "n".

- Nếu start_num chứa nhiều ký tự hơn within_text; hoặc bé hơn hoặc bằng 0, kết quả trả về là giá trị lỗi #VALUE!

Ví dụ 1: =FIND["I";"IT For Student";30] => kết quả trả về là giá trị lỗi #VALUE! vì start_num là 30, lớn hơn số lượng ký tự của chuỗi văn bản "IT For Student" là 14.

Ví dụ 2: =FIND["I";"IT For Student";0] => kết quả trả về là giá trị lỗi #VALUE! vì start_num = 0.

4. Hàm Search[]

Tương tự như hàm FIND, hàm SEARCH cũng trả về vị trí cũng một ký tự hoặc một chuỗi con. Tuy nhiên, hàm SEARCH không phân biệt chữ hoa hay chữ thường và cho phép sử dụng các ký tự đại diện trong find_text.

Cú pháp của hàm:

=SEARCH[find_text;within_text;[start_num]]

Trong đó:

- find_text: bắt buộc, là ký tự hoặc chuỗi văn bản con mà bạn muốn tìm vị trí.

- within_text: bắt buộc, chuỗi văn bản bạn dùng để tìm kiếm, có thể là tham số hoặc được gõ trực tiếp vào.

- start_num: không bắt buộc, vị trí của ký tự các bạn muốn bắt đầu tìm kiếm. Nếu để trống đối số này, mặc định sẽ tìm kiếm từ vị trí từ ký tự đầu tiên của chuỗi văn bản.

Đối với phần lưu ý, hàm SEARCH và hàm FIND tương đối giống nhau. Tuy nhiên, chỉ có 2 sự khác nhau cần phải nắm. Để các bạn hiểu hơn về sự khác nhau này, dưới đây IT For Student sẽ đưa ra ví dụ:

1. Hàm SEARCH không phân biệt chữ hoa và chữ thường như hàm FIND

Ví dụ: Hãy tìm vị trí chữ V chuỗi "Tin Học Văn Phòng"

Ta có hàm: =SEARCH["v";"Tin Học Văn Phòng"] => kết quả trả về là 9.

Ở đây, ta nhập ký tự "v" thường thì kết quả vẫn được trả về vị trí của "V" hoa vì khi sử dụng hàm SEARCH sẽ không phân biệt điều này.

2. Hàm SEARCH cho phép sử dụng các ký tự đại diện

Khác với hàm FIND, hàm SEARCH cho sử dụng các ký tự thay thế.

- Dấu chấm hỏi [?] đại diện cho 1 ký tự

- Dấu sao [*] đại diện cho 1 chuỗi ký tự

Ví dụ: Hãy tìm vị trí của "sinh viên năm 2021" trong chuỗi văn bản "Tin học cơ bản cho sinh viên năm 2021"

Ta có hàm: =SEARCH["sinh*2021";"Tin học cơ bản cho sinh viên năm 2021"] => Kết quả trả về là 20 vì "s" là ký tự đầu tiên của chuỗi ký tự "sinh viên năm 2021", nằm ở vị trí 20.

Ở đây, trong đối số find_text, chúng ta không cần phải điền nguyên chuỗi ký tự "sinh viên năm 2021" vì hàm SEARCH cho phép sử dụng ký tự đại diện. Ký tự dấu sao [*] trong ví dụ này đã thay thế cho chuỗi ký tự nằm giữa "sinh" và "2021", vì thế chúng ta chỉ cần điền "sinh*2021".

5. Hàm Exact[]

Khi muốn so sánh các dữ liệu trong Excel, ta sử dụng hàm EXACT. Cụ thể, hàm EXACT kiểm tra hai chuỗi dữ liệu được cung cấp xem có giống nhau hay không, có phân biệt ký tự viết hoa và ký tự viết thường. Kết quả trả về là TRUE khi hai giá trị giống nhau hoặc FALSE khi hai giá trị khác nhau [có tính chữ hoa chữ thường, thậm chí là khoảng trắng].

Cú pháp của hàm:

=EXACT[Text1;Text2]

Trong đó:

Text1, Text2: [hai tham số bắt buộc] là hai chuỗi văn bản cần so sánh

Ví dụ:

So sánh hai dữ liệu ở cột C3 và D3.

Ta có hàm: =EXACT[C3;D3] => Kết quả trả về tại E3 là TRUE, có nghĩa hai dữ liệu này bằng nhau. Thực hiện kéo sao chép công thức ở các dòng còn lại, những kết quả trả về là FALSE vì hàm EXACT phân biệt cả chữ hoa và chữ thường.

6. Hàm Replace[]

Nếu để tìm kiếm vị trí của một hoặc một chuỗi ký tự trong Excel, chúng ta dùng hàm FIND hoặc SEARCH như đã giới thiệu ở trên. Trong trường hợp muốn tìm và thay thế các ký tự đó thành các ký tự mới, ta dùng đến REPLACE. Cùng theo dõi cú pháp và ví dụ để hiểu hơn về hàm này bạn nhé!

Cú pháp của hàm:

=REPLACE[old_text;start_num;num_chars;new_text]

Trong đó:

- old_text: chuỗi văn bản gốc mà các bạn muốn thay thế các ký tự.

- start_num: vị trí của ký tự đầu tiên mà các bạn muốn bắt đầu thay thế trong chuỗi văn bản gốc, được tính từ trái sang phải.

- num_chars: số lượng ký tự trong chuỗi văn bản gốc mà các bạn muốn thay thế.

- new_text: là chuỗi văn bản mới để thay thế.

Ví dụ:

Dùng công thức để thay năm 2020 thành năm 2021 trong chuỗi "Sinh viên IT khoá 2020" 

Ta có hàm: =REPLACE[C2;19;4;2021] 

Trong đó C2 là tham chiếu của chuỗi văn bản gốc "Sinh viên IT khoá 2020" , 19 là vị trí bắt đầu của "2020" trong chuỗi văn bản gốc, 4 là số lượng ký tự của "2020", và 2021 là năm cần thế vào. Kết quả trả về tại ô D2 là "Sinh viên IT khoá 2021"

Trên đây là những thông tin về các hàm số xử lý chuỗi [phần 2] trong Excel, hi vọng hữu ích với các bạn. Ngoài ra, các bạn có thể tham khảo thêm nhiều bài viết khác của It for Student trên website hoặc xem video hướng dẫn chi tiết trên kênh youtube www.youtube.com/itforstudent. Chúc các bạn thành công!