Hàm Len: Đếm các ký tự và đếm từ có điều kiện

Mặc dù tính năng đếm từ và ký tự trong Google Sheet được sử dụng trong một số ít trường hợp nhưng đây vẫn là chức năng mà một số người trong chúng ta mong đợi thấy ngay trong menu. Nhưng không giống như Google Docs, đối với Google Sheets, chính hàm LEN thực hiện điều đó.

Mặc dù có nhiều cách khác nhau để đếm ký tự trong bảng tính, bài đăng blog hôm nay sẽ đề cập đến hàm LEN vì mục đích chính của nó trong bảng là – à, đếm 🙂 Tuy nhiên, hàm này hiếm khi được sử dụng riêng lẻ. Dưới đây bạn sẽ tìm hiểu cách sử dụng Google Sheet LEN một cách chính xác và tìm các công thức được mong muốn nhất để tính toán các ký tự trong bảng tính.

Hàm LEN của Google Sheet – cách sử dụng và cú pháp

Mục đích chính và duy nhất của hàm LEN trong Google Sheet là lấy độ dài chuỗi. Nó đơn giản đến mức chỉ cần 1 đối số:

= LEN (văn bản)

  • nó có thể lấy chính văn bản trong dấu ngoặc kép:
    =LEN("Yggdrasil")
  • hoặc tham chiếu đến một ô có văn bản quan tâm:
    =LEN(A2)

Hãy xem liệu có bất kỳ điểm đặc biệt nào trong việc sử dụng hàm trong bảng tính không.

Số ký tự trong Google Sheet

Tôi sẽ bắt đầu với thao tác đơn giản nhất: đếm ký tự trong Google Trang tính theo cách phổ biến nhất – bằng cách tham chiếu một ô có văn bản bằng hàm LEN.

Mình nhập công thức vào B2 và copy xuống toàn bộ cột để đếm số ký tự trong mỗi hàng:

=LEN(A2)

Số lượng ký tự trong Google Trang tính.

Ghi chú. Hàm LEN tính toán tất cả các ký tự: chữ cái, số, dấu cách, dấu chấm câu, v.v.

Bạn có thể nghĩ rằng theo cách tương tự, bạn có thể đếm ký tự cho toàn bộ phạm vi ô, như thế này: LEN(A2:A6). Tuy nhiên, thật kỳ lạ, nó không chỉ hoạt động theo cách này.

Để tính tổng số ký tự trong một số ô, bạn nên gói LEN của mình trong SUMPRODVEL – hàm tính tổng số từ các phạm vi đã nhập. Trong trường hợp của tôi, phạm vi được hàm LEN trả về:

=SUMPRODUCT(LEN(A2:A6))

Kiểm đếm số lượng ký tự trong Google Trang tính với sự trợ giúp của SUMPRODUCT.

Tất nhiên, thay vào đó bạn có thể kết hợp hàm SUM. Nhưng SUM trong Google Sheet không xử lý mảng từ các hàm khác. Để nó hoạt động, bạn sẽ phải thêm một chức năng khác – ArrayFormula :

=ArrayFormula(SUM(LEN(A2:A6)))

Sử dụng ArrayFormula cùng với các hàm SUM và LEN để đếm các ký tự trong Google Trang tính.

Cách đếm các ký tự với Hàm LEN điều kiện không có khoảng trắng

Như tôi đã lưu ý ở trên, Hàm LEN đếm từng và mọi ký tự có điều kiện mà nó nhìn thấy bao gồm cả dấu cách.

Nhưng điều gì sẽ xảy ra nếu có thêm khoảng trắng được thêm vào do nhầm lẫn và bạn không muốn xem xét chúng cho kết quả?

Đối với những trường hợp như thế này, có hàm TRIM trong Google Sheet. Nó kiểm tra văn bản để tìm các khoảng trống ở đầu, cuối và lặp lại ở giữa. Khi TRIM được ghép nối với LEN, sau này không tính tất cả các khoảng trắng đó.

Đây là một ví dụ. Tôi đã thêm khoảng trắng ở các vị trí khác nhau trong cột A. Như bạn có thể thấy, khi riêng, Google Sheet LEN đếm tất cả:

=LEN(A2)

Đếm tất cả các ký tự bao gồm tất cả các khoảng trắng thừa.

Nhưng ngay sau khi bạn tích hợp TRIM, tất cả các khoảng trống thừa sẽ bị bỏ qua:

=LEN(TRIM(A2))

Đếm ký tự không có khoảng trắng thừa trong Google Trang tính.

Bạn có thể đi xa hơn và làm cho công thức của bạn bỏ qua ngay cả những khoảng trắng đơn giữa các từ. Hàm SUBSTITUTE sẽ hỗ trợ. Mặc dù mục đích chính của nó là thay thế một ký tự này bằng một ký tự khác, nhưng có một mẹo nhỏ để làm cho nó giảm khoảng trắng hoàn toàn:

= SUBSTITUTE (text_to_search, search_for, Replace_with, [occurrence_number])

  • text_to_search là phạm vi bạn làm việc với: cột A hoặc A2 chinh xac.
  • search_for phải là một ký tự khoảng trắng trong dấu ngoặc kép: “”
  • Replace_with nên chứa dấu ngoặc kép trống. Nếu bạn định bỏ qua dấu cách, bạn cần thay thế chúng bằng không có gì theo nghĩa đen (chuỗi trống): “”
  • Occurence_number thường được sử dụng để chỉ định cá thể để thay thế. Nhưng vì tôi đang mô tả cách đếm các ký tự không có tất cả các khoảng trắng, tôi khuyên bạn nên bỏ qua đối số này vì nó là tùy chọn.

Bây giờ hãy thử và tập hợp tất cả những thứ này vào Google Sheets LEN và bạn sẽ thấy rằng không có dung lượng nào được tính đến:

=LEN(SUBSTITUTE(A2, " ", ""))

Đếm các ký tự không có tất cả khoảng trắng trong Google Trang tính.

Cách đếm các ký tự cụ thể với hàm LEN

Cùng một cặp trong Excel thì hàm LEN và SUBSTITUTE được sử dụng bất cứ khi nào bạn cần đếm các ký tự, chữ cái hoặc số cụ thể.

Trong các ví dụ của tôi, tôi sẽ tìm ra số lần xuất hiện của chữ cái ‘s’. Và lần này, tôi sẽ bắt đầu với một công thức làm sẵn:

=LEN(A2)-LEN(SUBSTITUTE(A2, "s", ""))

Hãy chia nó thành nhiều phần để hiểu cách hoạt động của nó:

  1. SUBSTITUTE (A2, “s”, “”) tìm kiếm ký tự ‘s’ trong A2 và thay thế tất cả các lần xuất hiện bằng “không có gì” hoặc chuỗi trống (“”).
  2. LEN (SUBSTITUTE (A2, “s”, “”) tính ra số lượng tất cả các ký tự trừ ‘s’ trong A2.
  3. LEN (A2) đếm tất cả các ký tự trong A2.
  4. Cuối cùng, bạn trừ một cái cho cái kia.

Sự khác biệt kết quả cho thấy có bao nhiêu ‘s’ trong ô:

Google Trang tính: đếm các ký tự cụ thể.

Ghi chú. Bạn có thể thắc mắc tại sao B1 lại nói chỉ có 1 ‘s’ trong A2 trong khi bạn có thể thấy 3?

Vấn đề là, hàm SUBSTITUTE phân biệt chữ hoa chữ thường. Tôi yêu cầu nó lấy tất cả các trường hợp của ‘s’ ở dạng chữ thường và nó đã làm như vậy.

Để làm cho nó bỏ qua chữ viết hoa và xử lý các chữ cái ở cả chữ thường và chữ hoa, bạn sẽ phải gọi thêm một hàm Google Sheet để được trợ giúp: LOWER.

Nó đơn giản như Google Sheets LEN và TRIM vì tất cả những gì nó yêu cầu là văn bản:

=LOWER(text)

Và tất cả những gì nó làm là chuyển toàn bộ chuỗi văn bản thành chữ thường. Thủ thuật này là chính xác những gì bạn cần để làm cho Google Sheet đếm các ký tự cụ thể bất kể chữ viết hoa của chúng:

=LEN(A2)-LEN(SUBSTITUTE(LOWER(A2), "s", ""))

Google Trang tính: đếm các ký tự cụ thể của tất cả các trường hợp văn bản.

Lưu ý. Và như trước đây, để đếm tổng số ký tự cụ thể trong phạm vi, hãy bọc LEN của bạn trong SUMPRODUCT:

=SUMPRODUCT(LEN(A2:A7)-LEN(SUBSTITUTE(LOWER(A2:A7), "s", "")))

Đếm số lần xuất hiện của một ký tự cụ thể trong một phạm vi.

Đếm từ trong Excel, Google Sheet với hàm LEN

Khi có nhiều từ trong ô, rất có thể bạn sẽ cần có số của chúng thay vì độ dài chuỗi trong Excel & Google Sheet.

Và mặc dù có nhiều cách để làm như vậy, hôm nay tôi sẽ đề cập đến cách hàm LEN thực hiện công việc.

Nhớ cái gì đó công thức tôi đã sử dụng để đếm các ký tự cụ thể trong Google Sheet? Trên thực tế, nó cũng sẽ có ích ở đây. Bởi vì tôi sẽ không đếm từ theo nghĩa đen. Thay vào đó, tôi sẽ đếm số khoảng cách giữa các từ và sau đó chỉ cần thêm 1. Hãy xem:

=LEN(A2)-LEN(SUBSTITUTE((A2), " ", ""))+1

  1. LEN (A2) đếm số tất cả các ký tự trong ô.
  2. LEN (SUBSTITUTE ((A2), “”, “”)) xóa tất cả các khoảng trắng khỏi chuỗi văn bản và đếm các ký tự còn lại.
  3. Sau đó, bạn trừ một cái cho cái kia, và sự khác biệt bạn nhận được là số khoảng trắng trong ô.
  4. Vì các từ luôn nhiều hơn khoảng trắng trong một câu, nên bạn thêm 1 vào cuối.

Cách đếm số từ trong Google Trang tính.

Cách đếm các từ cụ thể

Cuối cùng, tôi muốn chia sẻ một công thức hàm LEN mà bạn có thể sử dụng để đếm các từ cụ thể.

Ở đây tôi có Bài hát của Mock Turtle từ Cuộc phiêu lưu của Alice ở xứ sở thần tiên:

Các từ lặp lại trong bảng tính.

Tôi muốn biết từ ‘sẽ’ xuất hiện bao nhiêu lần trong mỗi hàng. Tôi tin rằng bạn sẽ không ngạc nhiên nếu tôi nói với bạn rằng công thức tôi cần bao gồm các chức năng tương tự như trước đây: hàm LEN, SUBSTITUTE và LOWER:

=(LEN(A2)-LEN(SUBSTITUTE(LOWER(A2), "will", "")))/LEN("will")

Công thức có thể trông đáng sợ nhưng tôi có thể đảm bảo với bạn rằng nó rất dễ hiểu, vì vậy hãy chịu đựng tôi 🙂

  1. Vì trường hợp văn bản không quan trọng với tôi, tôi sử dụng LOWER (A2) để chuyển mọi thứ thành chữ thường.
  2. Sau đó đi SUBSTITUTE (LOWER (A2), “will”, “”))) – nó loại bỏ tất cả các lần xuất hiện của ‘will’ bằng cách thay thế chúng bằng các chuỗi rỗng (“”).
  3. Sau đó, tôi trừ số ký tự không có từ ‘will’ vào tổng chiều dài chuỗi. Số tôi nhận được tính tất cả các ký tự trong tất cả các lần xuất hiện của ‘will’ trong mỗi hàng.Vì vậy, nếu ‘will’ xuất hiện một lần, số là 4 vì có 4 chữ cái trong từ. Nếu nó xuất hiện hai lần, con số là 8, v.v.
  4. Cuối cùng, tôi chia số này cho độ dài của từ đơn ‘will’.

Google Trang tính: cách đếm các từ cụ thể.

Lưu ý. Và một lần nữa, nếu bạn muốn nhận tổng số lần xuất hiện của từ ‘will’, chỉ cần bao gồm toàn bộ công thức bởi SUMPRODUCT:

=SUMPRODUCT((LEN(A2:A7)-LEN(SUBSTITUTE(LOWER(A2:A7), "will", "")))/LEN("will"))

Đếm bao nhiêu lần một từ nhất định xuất hiện trong phạm vi.

Như bạn có thể thấy, tất cả các trường hợp đếm ký tự này đều được giải quyết bằng cùng một mẫu của các hàm giống nhau trong Excel & Google Sheet: LEN, SUBSTITUTE, LOWER và SUMPRODUCT. Dịch vụ kế toán tại Hà Nội chúc các bạn thực hiện thành công.

Bình chọn

Xếp hạng 5 / 5. Tổng số bình chọn 8

Trả lời