Bí quyết để tính tổng số lượng từ trong phạm vi dữ liệu với các hàm trong Excel

Trong bài viết này, Siêu Marketing sẽ chỉ dẫn bạn phương pháp thực hiện việc đếm lượng từ trong Excel thông qua việc phối hợp hàm LEN với các hàm khác trong Excel và cung cấp các công thức để phân biệt từ viết hoa và thường khi đếm từ, nội dung cụ thể trong một ô hoặc một khu vực dữ liệu nào đó.

Các bước để đếm tổng từ trong một ô cụ thể

Áp dụng sự kết hợp giữa hàm LEN, SUBSTITUTE và TRIM để đếm từ trong một ô:

= LEN (TRIM (ô cần đếm)) – LEN (SUBSTITUTE (ô cần đếm, ” “, “”)) + 1

Với trường hợp cụ thể là ô bạn đang muốn tiến hành đếm từ.

Chẳng hạn, công thức sau đây sẽ dùng để tính số từ trong ô A2:

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

Sau đấy, bạn có khả năng kéo công thức này xuống để đếm từ cho những ô tiếp theo trong cột A:

Cách hoạt động của công thức đếm từ

Đầu tiên, hàm SUBSTITUTE được dùng để loại bỏ tất cả khoảng trống bằng cách thay thế chúng bằng chuỗi rỗng (“”) để hàm LEN có thể xác định được độ dài của chuỗi không chứa khoảng cách:

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

Sau đó, bạn sẽ lấy độ dài của chuỗi đã loại bỏ khoảng trống trừ đi tổng độ dài chuỗi và cộng thêm 1, bởi vì số lượng từ trong một ô luôn nhiều hơn số khoảng trống giữa chúng một đơn vị.

Hơn nữa, hàm TRIM giúp loại bỏ các khoảng trắng thừa, có thể có trong ô. Thỉnh thoảng, bảng tính của bạn có thể có các khoảng trắng không nhìn thấy được, như là các khoảng trống giữa từ hoặc khoảng trắng không cần thiết ở đầu hay cuối văn bản. Những khoảng trắng này có thể gây nên sai lệch trong việc đếm. Do đó, hàm TRIM sẽ được dùng trước khi tính tổng độ dài của chuỗi để đảm bảo chỉ giữ lại khoảng cách giữa các từ.

Biện pháp tinh chỉnh công thức cho việc xử lý chính xác các ô không chứa gì

Công thức để đếm từ trong Excel mà được mô tả ở trên có thể coi là hoàn hảo nếu không vì vấn đề nhỏ sau đây – nó mang lại kết quả là 1 đối với những ô không chứa gì. Bạn có thể khắc phục điều này bằng cách thêm một hàm IF để kiểm tra các ô rỗng:

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

Như hình minh họa phía trên, công thức này sẽ trả về giá trị 0 cho các ô trống và số lần đếm chính xác cho những ô khác.

Phương pháp đếm số lượng của một từ cụ thể trong một ô

Để tính số lần một từ hay chuỗi ký tự xuất hiện trong một ô, dùng công thức sau:

= (LEN (ô) – LEN (SUBSTITUTE (ô, từ cần tìm, “”))) / LEN (từ cần tìm)

Lấy một ví dụ, ta sẽ đếm số lần từ “moon” xuất hiện trong ô A2:

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

Thay vì gõ từ đang được tính toán trực tiếp vào công thức, bạn có thể nhập nó vào một ô bất kỳ và sau đó dùng ô này làm tham chiếu trong công thức của mình. Qua đó, bạn có thể tạo ra một công thức dẻo dai hơn cho việc tính từ trong Excel.

Lưu ý: Nếu dự định kéo công thức qua nhiều ô, đừng quên bổ sung dấu $ vào ô tham chiếu chứa từ được đếm. Ví dụ:

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

Hướng dẫn thiết lập công thức tính số lượng một đoạn văn bản cụ thể xuất hiện trong một ô

1. Hàm SUBSTITUTE sẽ thay thế từ được chọn trong phạm vi văn bản ban đầu bằng ký tự trống.

Bằng cách sử dụng ví dụ sau, từ trong ô B1 sẽ bị loại bỏ khỏi nội dung ở A2:

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

2. Tiếp theo, hàm LEN sẽ đo độ dài của đoạn văn bản sau khi đã loại bỏ từ muốn tìm.

Ở đây, ví dụ LEN (SUBSTITUTE (A2, $B$1, “”)) sẽ cung cấp độ dài văn bản tại ô A2 mà không kể đến từ “moon”.

3. Số giá trị thu được ở trên sẽ bị trừ đi từ tổng số ký tự của đoạn văn bản gốc:

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

Thêm vào đó, kết quả này chính là tổng số ký tự tạo nên từ xuất hiện, trong trường hợp này là 12 (với “moon” xuất hiện 3 lần, mỗi từ chứa 4 ký tự).

4. Cuối cùng, số lượng này sẽ được phân chia theo chiều dài của từ cần tìm. Nói cách khác, tổng ký tự tạo nên các từ cần tìm sẽ được chia cho số ký tự của từ đó mỗi lần xuất hiện. Như trong trường hợp trên, 12 chia cho 4, ta có kết quả 3.

Không chỉ dừng lại ở việc đếm số lượng một từ cụ thể trong một ô, công thức này cũng có thể áp dụng để tính số lần một chuỗi con bất kỳ xuất hiện. Ví dụ: tính số lần “pick” xuất hiện trong ô A2:

Công thức để đếm số lượng từ cụ thể trong ô có phân biệt chữ hoa chữ thường:

Bạn có thể đã biết, hàm SUBSTITUTE trong Excel xử lý dữ liệu dựa trên cả chữ hoa và chữ thường, do đó công thức sử dụng SUBSTITUTE mặc định cũng sẽ phân biệt điều này trong khi đếm từ:

Công thức để đếm từ cụ thể trong ô không phân biệt chữ hoa chữ thường:

Nếu muốn tính toán số lần xuất hiện từ không kể chữ hoa hay chữ thường, sử dụng hàm UPPER hoặc LOWER trong SUBSTITUTE để biến đổi cả văn bản gốc và từ cần tìm về cùng một dạng:

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

Hoặc:

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

Ví dụ, để tính số lần từ trong ô B1 xuất hiện ở ô A2 với mọi dạng từ, dùng công thức:

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

Dưới đây là hình minh họa, công thức cho cùng một kết quả bất chấp việc từ được gõ như thế nào, chẳng hạn như viết hoa (ô B1), chữ thường (ô D1), hay viết hoa chữ cái đầu (ô C1):

Phương pháp tính tổng số từ trong một khu vực dữ liệu

Để xác định tổng số từ có trong một vùng cụ thể, có thể kết hợp công thức đếm từ của một ô với hàm SUMPRODUCT hoặc SUM:

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

Hoặc phương pháp sử dụng hàm SUM:

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

Hàm SUMPRODUCT là một trong những hàm ít ỏi của Excel có thể tính toán mảng mà không cần công thức mảng, chỉ cần nhấn Enter sau khi nhập.

Đối với hàm SUM, nó cần được dùng với công thức mảng.

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

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

Kỹ thuật đếm từ xác định trong một khu vực cụ thể

Nếu nhu cầu của bạn là đếm số lần một từ hay chuỗi văn bản xuất hiện trong một ô cụ thể, hãy áp dụng phương pháp tương tự – sử dụng công thức đếm từ đã nhắc ở trên kết hợp với hàm SUM hoặc SUMPRODUCT:

= SUMPRODUCT((LEN(range) – LEN(SUBSTITUTE(range, từ, “”))) / LEN(từ))

Hoặc bạn có thể dùng:

= SUM((LEN(range) – LEN(SUBSTITUTE(range, từ, “”))) / LEN(từ))

Đừng quên dùng Ctrl + Shift + Enter để hoàn thành nhập công thức mảng SUM.

Ví dụ, để tính tất cả những lần từ trong ô C1 xuất hiện trong khoảng từ A2 đến A4, hãy sử dụng công thức sau:

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

Hãy nhớ rằng hàm SUBSTITUTE sẽ phân biệt chữ hoa và chữ thường nên công thức trên cũng sẽ làm điều tương tự:

Để công thức không bị ảnh hưởng bởi sự khác biệt giữa chữ hoa và chữ thường, bạn có thể dùng các hàm UPPER hoặc LOWER:

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

Hoặc sử dụng:

= 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 Siêu Marketing.

Trương Thành Tài

    [submission_id id-lien-he]

    0
      0
      Đơn hàng
      Đơn hàng trốngQuay lại Shop