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

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 (P1)

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.

1. Làm thế nào để đếm tổng số từ trong một ô

Để đếm từ trong Excel 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:

2. Công thức đếm từ trong Excel hoạt động như thế nào

Đầ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ừ.

3. Công thức cải tiến để xử lý đúng các ô trống

Công thức trên dùng để đếm 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.

4. Cách đếm từ trong Excel các từ cụ thể trong một ô

Để đế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”)

Cách đếm từ trong Excel các từ cụ thể trong một ô

Cách đếm từ trong Excel các từ cụ thể trong một ô

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)

5. Công thức đếm từ trong Excel với số lần xuất hiện của một văn bản cụ thể trong một ô

  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, “”)

  1. 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”.

  1. 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ừ).

  1. 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:

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Đ