Trong hướng dẫn này, chúng ta sẽ thảo luận đếm số lần xuất hiện của một ký tự hoặc từ trong một chuỗi cụ thể và phạm vi chuỗi trong Excel. Thường xuyên, chúng ta phải tính tần suất ký tự trong một ô hoặc phạm vi dữ liệu chứa lượng lớn dữ liệu. Để làm cho việc đếm này dễ dàng hơn, chúng ta sẽ thảo luận một số công thức dễ sử dụng trong bài viết này.
Tải file thực hành
Bạn có thể tải file thực hành chúng ta dùng để chuẩn bị bài viết này.
5 phương pháp đếm số lần xuất hiện ký tự trong chuỗi trong Excel
1. Tìm tổng số lần xuất hiện ký tự trong chuỗi trong Excel dùng SUMPRODUCT và LEN
Nếu muốn biết tổng số ký tự trong một ô, hàm LEN phục vụ mục đích. Nhưng khi phải biết tổng ký tự trong một phạm vi, bạn có thể kết hợp SUMPRODUCT và LEN. Ví dụ, có tập dữ liệu tên sách và muốn biết tổng số ký tự trong phạm vi. Các bước:
Các bước:
- Trước hết, nhập công thức dưới:
=SUMPRODUCT(LEN(B5:B9))Ở đây, hàm LEN trả về số ký tự trong chuỗi văn bản và hàm SUMPRODUCT trả về tổng tích của phạm vi tương ứng.
- Đây là tổng số ký tự trong phạm vi chỉ định.
Lưu ý:
Hàm LEN Excel đếm tất cả ký tự mỗi ô, bao gồm chữ cái, số, khoảng trắng, ký hiệu, dấu câu, v.v.
2. Kết hợp SUBSTITUTE và LEN tính tổng xuất hiện ký tự cụ thể trong chuỗi (Phân biệt chữ hoa/thường)
Đôi khi cần biết tần suất một ký tự trong ô Excel. Trong tình huống này, dùng LEN và SUBSTITUTE đồng thời. Vấn đề là SUBSTITUTE phân biệt chữ hoa/thường nên nếu tìm ký tự thường, chỉ đếm được ký tự thường. Ví dụ, trong tập tên sách, chỉ tìm số lần ký tự 'a'. Các bước:
Các bước:
- Trước hết, nhập công thức sau:
=LEN(B5)-LEN(SUBSTITUTE(B5,"a",""))
Phân tích công thức:
➤ LEN(B5)
Hàm LEN đếm ký tự trong Ô B5.
➤ SUBSTITUTE(B5,"a","")
Hàm SUBSTITUTE thay thế tất cả ký tự 'a' bằng rỗng ("").
➤ LEN(SUBSTITUTE(B5,"a",""))
Bây giờ, công thức SUBSTITUTE được bao bởi LEN cho số ký tự còn lại (loại trừ tất cả 'a').
➤ LEN(B5)-LEN(SUBSTITUTE(B5,"a",""))
Cuối cùng, công thức trừ hai độ dài trước tính được tổng số ký tự chỉ định 'a'.
- Cuối cùng, đây là số lần ký tự mong đợi:
3. Dùng kết hợp SUBSTITUTE và LEN tính xuất hiện ký tự cụ thể (Không phân biệt chữ hoa/thường)
Ngược lại phương pháp trước, bạn có thể đếm ký tự bất kể chữ hoa/thường. Lúc đó, thêm UPPER hoặc LOWER vào công thức trước. Giờ tìm cả 'A" và 'a' trong tập tên sách. Các bước:
Các bước:
- Nhập công thức dưới trước.
=LEN(B5)-LEN(SUBSTITUTE(UPPER(B5),"A",""))Ở đây, hàm UPPER chuyển chuỗi thành chữ in hoa. Tất cả 'a' thành 'A' và đếm tương ứng. Phần còn lại hoạt động tương tự ví dụ trước.
- Cuối cùng, kết quả sau nếu nhập công thức đúng.
4. Tính số lần xuất hiện ký tự đơn trong phạm vi trong Excel
Đôi khi cần tính tổng xuất hiện ký tự cụ thể trong phạm vi chuỗi. Ví dụ, muốn biết tần suất 'A' hoặc 'a' trong phạm vi dữ liệu.
Các bước:
- Nhập công thức sau trước.
=SUM(LEN(B5:B11)-LEN(SUBSTITUTE(B5:B11,"a","")))Ở đây, hàm SUM cộng tất cả số trong phạm vi ô. Phần còn lại hoạt động như trước và trả về tổng số ký tự 'a' trong toàn bộ phạm vi.
- Kết quả, tổng số lần như sau:
5. Đếm số lần xuất hiện ký tự (Văn bản hoặc Chuỗi con) trong phạm vi
Tương tự phương pháp trước, bạn có thể muốn biết tần suất văn bản cụ thể trong phạm vi dữ liệu. Trong tình huống đó, dùng kết hợp các hàm. Phương pháp này kết hợp SUM, LEN, và SUBSTITUTE. Ví dụ, có phạm vi dữ liệu tên màu và muốn biết tần suất màu 'Green' từ phạm vi.
Các bước:
- Nhập công thức dưới trước.
=SUM(LEN(B5:B11)-LEN(SUBSTITUTE(B5:B11,"Green","")))/LEN("Green")Trong công thức trên, cần chia số ký tự cho độ dài văn bản/chuỗi con chỉ định. Nếu không, mỗi ký tự trong văn bản sẽ được đếm riêng lẻ.
- Cuối cùng, tổng số lần 'Green' trong phạm vi chỉ định.
Lưu ý:
Công thức trên phải nhập như công thức mảng. Để nhập công thức mảng trong Excel Windows, nhấn CTRL+SHIFT+ENTER.
Kết luận
Trong thảo luận trên, tôi cố gắng giải thích các phương pháp dễ dàng và phù hợp tìm số ký tự trong chuỗi hoặc phạm vi chuỗi trong Excel. Hy vọng bạn thấy tất cả hữu ích. Nếu có thắc mắc về các phương pháp, hãy cho tôi biết.
Các bài viết liên quan
- Cách tìm ký tự trong chuỗi trong Excel
- Xuất hiện đầu tiên của giá trị trong phạm vi trong Excel (3 cách)
- Cách dùng hàm FIND trong Excel (7 ví dụ phù hợp)
- Dùng hàm LEN trong Excel (Công thức và mã VBA)