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