Cách đếm tổng số từ trong vùng dữ liệu bằng kết hợp các hàm Excel

Bài viết này Học Excel Online hướng dẫn bạn cách đếm từ trong Excel bằng cách sử dụng hàm LEN kết hợp với các hàm Excel khác và cung cấp các công thức phân biệt chữ hoa và chữ thường để đếm các từ, văn bản cụ thể trong một ô hoặc vùng dữ liệu.

Để đếm các từ trong một ô, sử dụng sự kết hợp của các hàm LEN, SUBSTITUTE và TRIM:

= LEN (TRIM (ô)) – LEN (SUBSTITUTE (ô, ” “, “”)) + 1

Trường hợp đang ở tại ô bạn muốn đếm từ.

Ví dụ, để đếm các từ trong ô A2, sử dụng công thức này:

= LEN (TRIM (A2)) – LEN (SUBSTITUTE (A2, ” “, “”)) + 1

Và sau đó, bạn có thể sao chép công thức xuống đếm từ trong các ô khác của cột A:

Đầu tiên, bạn sử dụng hàm SUBSTITUTE để loại bỏ tất cả các khoảng trắng trong ô bằng cách thay thế chúng bằng một chuỗi ký tự rỗng (“”) để hàm LEN  trả về chiều dài của chuỗi mà không có khoảng trắng:

LEN (SUBSTITUTE (A2, ” “, “”))

Sau đó, bạn trừ chiều dài chuỗi không có khoảng trắng từ tổng chiều dài của chuỗi, và cộng 1 vào kết quả, vì số trừ trong một ô tương đương với số khoảng trắng cộng với 1.

Ngoài ra, bạn sử dụng hàm TRIM để loại bỏ khoảng trắng trong ô, nếu có. Đôi khi bảng tính có thể chứa nhiều khoảng trắng vô hình, ví dụ như hai hay nhiều khoảng trắng giữa các từ, hoặc khoảng trắng vô tình đánh vào đầu hoặc cuối của văn bản (tức là các khoảng trống đầu và cuối). Tất cả những khoảng trống đó có thể khiến bạn đếm sai. Để không để chuyện này xảy ra, trước khi tính tổng chiều dài của chuỗi, ta sử dụng hàm TRIM để bỏ tất cả các khoảng trắng, ngoại trừ khoảng cách giữa các từ.

Công thức trên dùng để đếm các từ trong Excel có thể được gọi là hoàn hảo nếu không có nhược điểm sau – nó trả kết quả đếm là 1 cho các ô trống. Để khắc phục điều này, bạn có thể thêm một lệnh IF để kiểm tra các ô trống:

= IF (A2 = ” “, 0, LEN (TRIM (A2)) – LEN (SUBSTITUTE (A2, ” “, “”)) + 1)

Như bạn thấy trong hình trên, công thức sẽ trả về số không cho các ô trống và số đếm từ chính xác cho các ô còn lại.

Để đếm số lần xuất hiện của một từ, văn bản hoặc chuỗi con xuất hiện trong một ô, sử dụng công thức sau:

= (LEN (ô) -LEN (SUBSTITUTE (ô, từ, “”))) / LEN (từ)

Ví dụ: hãy tính số lần xuất hiện “moon” trong ô A2:

= (LEN (A2) -LEN (SUBSTITUTE (A2, “moon”, “”))) / LEN (“moon”)

Thay vì nhập từ được đếm trực tiếp trong công thức, bạn có thể gõ nó trong một ô, và tham chiếu ô đó trong công thức của bạn. Kết quả là, bạn sẽ có được một công thức linh hoạt hơn để đếm số từ trong Excel.

Chú ý: Nếu bạn có kế hoạch sao chép công thức của bạn vào nhiều ô, hãy chắc chắn thêm vào ô tham chiếu chứa từ để đếm dấu $. Ví dụ:

= (LEN (A2) -LEN (SUBSTITUTE (A2, $B$1, “”))) / LEN ($B$1)

1. Hàm SUBSTITUTE loại bỏ từ được chỉ định khỏi văn bản ban đầu.

Trong ví dụ này, chúng ta loại bỏ từ nhập vào ô B1 từ văn bản gốc nằm trong A2:

SUBSTITUTE (A2, $B$1, “”)

2. Sau đó, hàm LEN tính chiều dài của chuỗi văn bản mà không có từ được chỉ định.

Trong ví dụ này, LEN (SUBSTITUTE (A2, $B$1, “”)) trả về chiều dài của văn bản trong ô A2 sau khi xóa tất cả từ “moon”.

3. Sau đó, số trên được trừ khỏi tổng chiều dài của chuỗi văn bản ban đầu:

(LEN (A2) -LEN (SUBSTITUTE (A2, $B$1, “”))))

Kết quả của  việc này là số ký tự chứa trong tất cả các lần xuất hiện của từ được chọn, trong ví dụ là 12 (3 lần xuất hiện từ “moon”, 4 ký tự mỗi từ).

4. Cuối cùng,  kết quả trên được chia cho chiều dài của từ. Nói cách khác, bạn chia số ký tự chứa trong tất cả các lần xuất hiện của từ mục tiêu cho số ký tự chứa trong một lần xuất hiện của từ đó. Trong ví dụ này, 12 chia cho 4, và ta nhận được kết quả là 3.

Ngoài việc đếm số từ nhất định trong một ô, bạn có thể sử dụng công thức này để đếm các lần xuất hiện của bất kỳ văn bản nào (chuỗi con). Ví dụ: bạn có thể đếm số lần chữ “pick” xuất hiện trong ô A2:

Có thể bạn đã biết, Excel SUBSTITUTE là một hàm phân biệt in hoa in thường, và do đó công thức đếm từ dựa trên SUBSTITUTE theo mặc định là chữ in hoa và chữ in thường:

Nếu bạn cần đếm cả lần xuất hiện chữ hoa và chữ thường của một từ nhất định, hãy sử dụng hàm UPPER hoặc LOWER bên trong SUBSTITUTE để chuyển đổi văn bản gốc và văn bản bạn muốn đếm vào cùng một trường hợp.

= (LEN (ô) -LEN (SUBSTITUTE (UPPER (ô), UPPER (văn bản), “”))) / LEN (văn bản)

Hoặc là

= (LEN (ô) -LEN (SUBSTITUTE (LOWER (ô), LOWER (văn bản), “”)))) / LEN (văn bản)

Ví dụ, để đếm số lần xuất hiện của từ ở ô B1 trong ô A2 trong mọi trường hợp, sử dụng công thức này:

= (LEN (A2) -LEN (SUBSTITUTE (LOWER (A2), LOWER ($B$1), “”))) / LEN ($B$1)

Như hình minh họa dưới đây, công thức trả về cùng một số bất kể từ được gõ in hoa (ô B1), chữ thường (ô D1) hoặc trường hợp chữ hoa ở đầu (ô C1):

Để biết có bao nhiêu từ trong một vùng nào đó, hãy lấy công thức đếm tổng số từ trong một ô và kết hợp hàm SUMPRODUCT hoặc SUM:

= SUMPRODUCT (LEN (TRIM (phạm vi)) – LEN (SUBSTITUTE (phạm vi, ” “, “”)) + 1)

Hoặc là

= SUM (LEN (TRIM (phạm vi)) – LEN (SUBSTITUTE (phạm vi, ” “, “”)) + 1)

SUMPRODUCT là một trong số ít các hàm Excel có thể xử lý mảng, và bạn hoàn thành công thức theo cách thông thường bằng cách nhấn phím Enter.

Nếu dùng hàm SUM trong tính toán các mảng, thì nó nên được sử dụng như một mảng, nghĩa là hoàn thành bằng cách nhấn Ctrl + Shift + Enter thay vì gõ Enter bình thường.

Ví dụ: đếm tất cả các từ trong khoảng A2: A4, sử dụng một trong các công thức sau:

= SUMPRODUCT (LEN (TRIM (A2: A4)) – LEN (SUBSTITUTE (A2: A4, ” “, “”)) + 1)

= SUM (LEN (TRIM (A2: A4)) – LEN (SUBSTITUTE (A2: A4, ” “, “”)) + 1)

Nếu bạn muốn đếm một từ hoặc văn bản xuất hiện bao nhiêu lần trong một ô, hãy sử dụng cách tương tự – lấy công thức đếm các từ cụ thể trong một ô, và kết hợp nó với hàm SUM hoặc SUMPRODUCT:

= SUMPRODUCT ((LEN (range) -LEN (SUBSTITUTE (phạm vi, từ, “”))) / LEN (từ))

Hoặc là

= SUM ((LEN (range) -LEN (SUBSTITUTE (phạm vi, từ, “”))) / LEN (từ))

Hãy nhớ nhấn Ctrl + Shift + Enter để hoàn thành công thức mảng SUM.

Ví dụ, đếm tất cả các lần xuất hiện của từ được nhập vào ô C1 trong phạm vi A2: A4, sử dụng công thức này:

=SUMPRODUCT((LEN(A2:A4)-LEN(SUBSTITUTE(A2:A4, C1,””)))/LEN(C1))

Bạn nên nhớ, SUBSTITUTE là một hàm phân biệt chữ hoa chữ thường và do đó công thức trên phân biệt giữa chữ hoa và chữ thường:

Để làm cho công thức không phân biệt các trường hợp in hoa in thường, hãy sử dụng hàm UPPER hoặc LOWER:

= SUMPRODUCT (LEN (A2: A4) -LEN (SUBSTITUTE ((UPPER (A2: A4)), UPPER (C1), “”)))) / LEN (C1))

Hoặc là

= SUMPRODUCT ((LEN (A2: A4) -LEN (SUBSTITUTE ((LOWER (A2: A4)), LOWER (C1), “”)))) / LEN (C1))

Nguồn: Ablebits, dịch và biên tập bởi Hocexcel Online.