Đếm các từ cụ thể trong một phạm vi trong Excel

Bài viết này giải thích một công thức để đếm số lần một từ cụ thể xuất hiện trong một phạm vi ô trong Excel.

Làm cách nào để đếm các từ cụ thể trong một phạm vi ô trong Excel?

Công thức chung

=SUMPRODUCT((LEN(rng)-LEN(SUBSTITUTE(rng,txt,"")))/LEN(txt))

Lập luận

Làm thế nào để sử dụng công thức này?

Giả sử bạn muốn đếm số từ “công cụ” trong phạm vi B5: B8, bạn có thể lấy nó xuống như sau.

Chọn một ô trống, nhập công thức bên dưới vào đó rồi nhấn đăng ký hạng mục thi Chìa khóa.

Lời khuyên: Bạn có thể sao chép và dán trực tiếp công thức vào ô đã chọn.

=SUMPRODUCT((LEN(B5:B8)-LEN(SUBSTITUTE(B5:B8,"tool","")))/LEN("tool"))

Ghi chú:

• 1. Vui lòng thay đổi phạm vi B5: B8 và từ “công cụ” theo nhu cầu của riêng bạn;
• 2. Nếu từ không được tìm thấy, nó sẽ trả về 0;
• 3. Công thức phân biệt chữ hoa chữ thường;
• 4. Nếu bạn muốn đếm từ cụ thể trong một phạm vi ô và bỏ qua chữ hoa, vui lòng sử dụng hàm UPPER bên trong SUBSTITUTE như công thức dưới đây được hiển thị:
• =SUMPRODUCT((LEN(B5:B8)-LEN(SUBSTITUTE(UPPER(B5:B8),UPPER("tool"),"")))/LEN("tool"))
• 

Công thức này hoạt động như thế nào?

=SUMPRODUCT((LEN(B5:B8)-LEN(SUBSTITUTE(B5:B8,"tool","")))/LEN("tool"))

• 1. LEN(B5:B8): Hàm LEN tính tổng độ dài chuỗi cho mỗi ô trong phạm vi B5: B8, nó sẽ được trả về dưới dạng các số mảng {47; 23; 23; 15};
• 2. LEN(SUBSTITUTE(B5:B8,"tool","")): Ở đây hàm SUBSTITUTE loại bỏ từ cụ thể “công cụ” khỏi mỗi ô trong phạm vi B5: B8, sau đó hàm LEN tính toán độ dài cho từng ô trong phạm vi B5: B8 mà không có từ cụ thể “công cụ”. Nó trả về các số mảng là {35; 19; 19; 15};
• 3. (LEN(B5:B8)-LEN(SUBSTITUTE(B5:B8,"tool","")): Phần này của công thức bây giờ được chuyển thành {47; 23; 23; 15} - {35; 19; 19; 15}. Sau khi trừ, kết quả là {12; 4; 4; 0};
• 4. LEN("tool"): Hàm LEN ở đây tính toán độ dài cho “công cụ” và trả về kết quả 4;
• 5. LEN(B5:B8)-LEN(SUBSTITUTE(B5:B8,"tool","")))/LEN("tool"): Như đã đề cập ở trên, phần này sẽ hiển thị là {12; 4; 4; 0} / 4. Chia 4 cho mỗi số của mảng và có kết quả là {3; 1; 1; 0};
• 6. SUMPRODUCT({3;1;1;0}): Hàm SUMPRODUCT tính tổng các số trong mảng và trả về tổng số “công cụ” trong phạm vi B5: B8.

Các chức năng liên quan

Công thức liên quan