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

CHÀO MỪNG BẠN ĐẾN VỚI THƯ VIỆN HỌC TẬP & ĐÀO TẠO CƠ BẢN

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

Excel

Bắt đầu sử dụng Excel

Công thức và hàm

Nhập và phân tích dữ liệu

Định dạng dữ liệu

Xử lý sự cố

Excel 2016

Bài viết này chúng tôi 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.

6. Công thức đếm từ trong Excel cụ thể trong một ô phân biệt in hoa in thường

Có thể bạn đã biết, hàm Substitute trong Excel 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 hàm Substitute theo mặc định là chữ in hoa và chữ in thường:

7. Công thức đếm từ trong Excel cụ thể trong một ô không phân biệt in hoa 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):

ông thức đếm từ trong Excel cụ thể trong một ô không phân biệt in hoa in thường

ông thức đếm từ trong Excel cụ thể trong một ô không phân biệt in hoa in thường

8. Đếm tổng số từ trong một vùng dữ liệu

Để 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)

9. Đếm từ trong Excel các từ cụ thể trong một vùng dữ liệu

Nếu bạn muốn đếm từ trong Excel 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ớ, hàm 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))

Chúc bạn thực hiện thành công.

Bạn có thể thành thạo ngay kỹ năng tin học văn phòng từ giảng viên FPT- Arena chỉ với 280,000Đ