Cách đếm số lần xuất hiện của 1 ký tự trong Excel

Hoc tin hoc van phong o Thanh Hoa

Cách đếm số lần xuất hiện của 1 ký tự trong Excel

Cách đếm số lần xuất hiện của một ký tự trong ô dữ liệu Excel

Excel không có hàm nào có thể thực hiện trực tiếp chức năng đếm số ký tự trong ô dữ liệu. Để làm được việc này chúng ta cần phải sử dụng kết hợp hai hàm LEN và SUBSTITUTE.

1.1 Khái quát qua về hàm LEN và hàm SUBSTITUTE

Hàm LEN là hàm của Excel có tác dụng đếm số các ký tự có trong ô tham số. Giả dụ trong ô tham số A1 có các ký tự là “123456” thì khi ta dùng công thức =LEN(A1) sẽ cho ra kết quả là 6 vì trong A1 có chứa tất cả 6 ký tự. Lưu ý rằng LEN sẽ đếm tất cả các ký tự kể cả ký tự đặc biệt hay dấu cách.

Hàm SUBSTITUTE là hàm thay thế. Khi sử dụng hàm này cho một ô dữ liệu nó sẽ cho bạn khả năng thay thế một ký tự cụ thể nào đó trong ô dữ liệu bằng một ký tự khác. Ngoài ra chúng ta còn có thể sử dụng SUBSTITUTE để xóa đi các ký tự cụ thể ở trong chuỗi dữ liệu bằng cách để trống ký tự cần thay thế.

1.2 Kết hợp hàm LEN và hàm SUBSTITUTE

Chúng ta có hàm LEN dùng để tính số ký tự trong chuỗi và hàm SUBSTITUTE có thể xóa đi các ký tự nhất định trong chuỗi. Từ đó ta có thể làm một phép tính gộp đơn giản.

Đầu tiên ta sử dụng hàm LEN để tính số ký tự có trong chuỗi ký tự gốc. Sau đó chúng ta sử dụng hàm SUBSTITUTE để xóa đi các ký tự mà ta đang cần đếm rồi lại tiếp tục sử dụng hàm LEN để tính số ký tự trong chuỗi ký tự vừa bị xóa đó.

Từ đó ta có hai con số là tổng ban đầu cùng với tổng của chuỗi đã xóa ký tự. Lấy cái đầu trừ đi cái cuối ta sẽ tính được số lần xuất hiện của một ký tự trong chuỗi ký tự gốc.

Công thức kết hợp ở đây là: =LEN(chuỗi ký tự gốc)-LEN(SUBTITUTE(chuỗi ký tự gốc;”ký tự cần đếm”;””)

Chúng ta cùng thử xem ví dụ dưới đây khi sử dụng công thức kết hợp trên để tìm ra số lần xuất hiện của một ký tự trong Excel.

Ta muốn tìm số lần xuất hiện của ký tự n trong ô ký tự gốc “Nhân chi sơ tính bản thiện”. Từ đó có công thức như sau: =LEN(A2)-LEN(SUBSTITUTE(A2;B2;””))

Bấm Enter để thấy kết quả.

Sau đó các bạn có thể copy công thức đó xuống dưới để có thể đếm được ký tự đặc biệt của các dòng ví dụ bên dưới.

Cách đếm số lần xuất hiện của một ký tự trong mảng dữ liệu Excel

Trong phần 1 chúng ta đã biết cách kết hợp với hai hàm LEND và SUBSSTITUTE để có thể đếm được số lần xuất hiện của một ký tự trong ô dữ liệu.

Nhưng nếu bạn muốn đếm số ký tự xuất hiện trong một mảng dữ liệu gồm nhiều hàng và cột vậy thì chúng ta phải sử dụng một công thức khác: =SUMPRODUCT(LEN(mảng dữ liệu)-LEN(SUBSTITUTE(mảng dữ liệu;ký tự cần đếm;””)))

Công thức này gần giống với công thức ở phần 1 nhưng các bạn cần phải thay ô dữ liệu thành tham số mảng dữ liệu cần xét. Và sử dụng hàm SUMPRODUCT bao trùm lên tất cả hiệu số của hai hàm LEN.

Dựa vào đó ta làm ví dụ sau đây, chúng ta cùng đếm số lần xuất hiện của chữ “a” trong mảng dữ liệu từ A1 đến C3. Ta có công thức: =SUMPRODUCT(LEN(A1:C3)-LEN(SUBSTITUTE(A1:C3;”a”;””)))

Khi Enter để Excel thực hiện tính toán các bạn sẽ thấy kết quả nhận được là có tất cả 8 lần chữ “a” xuất hiện trong mảng dữ liệu.

Nếu bạn là người chưa từng làm quen với máy tính, chưa biết gì về tin học muốn tìm một khóa học để thành thạo các kỹ năng cơ bản về tin học văn phòng.
Nếu bạn là một dân văn phòng, muốn nâng cao hiệu suất công việc thì đừng bỏ qua các khóa học của trung tâm ATC!