Trong quá trình làm việc với Excel, bạn có thể gặp phải nhu cầu đếm số lần xuất hiện của một ký tự nào đó trong một ô, một dãy hay một phạm vi dữ liệu. Ví dụ, bạn muốn biết có bao nhiêu chữ cái a trong một câu, có bao nhiêu số 0 trong một số, hay có bao nhiêu dấu phẩy trong một địa chỉ. Trong bài viết này, chúng tôi sẽ hướng dẫn bạn cách đếm số lần xuất hiện ký tự trong Excel bằng các hàm và công thức khác nhau. Bạn có thể áp dụng các cách này cho các ký tự bất kỳ, như chữ cái, số, dấu câu, khoảng trắng hay ký tự đặc biệt.
1. Cách đếm số lần xuất hiện ký tự trong excel
Để đếm số lần xuất hiện ký tự trong excel, bạn có thể sử dụng các hàm sau:
1.1. Hàm đếm ký tự trong excel LEN kết hợp với SUBSTITUTE
Hàm LEN trả về độ dài của một chuỗi, tức là số ký tự trong chuỗi đó. Hàm SUBSTITUTE thay thế tất cả các lần xuất hiện của một ký tự bằng một ký tự khác trong một chuỗi.
Công thức để đếm số lần xuất hiện của một ký tự trong một ô bằng hàm LEN và SUBSTITUTE là:
=LEN(text)-LEN(SUBSTITUTE(text;”ký_tự”;””))
Trong đó:
– text là ô chứa chuỗi cần đếm
– ký_tự là ký tự cần đếm số lần xuất hiện
Ví dụ: Cho bảng dữ liệu 1:
Yêu cầu: Tính tổng số ký tự theo yêu cầu trong từng ô B2, B3, B4 và B5.
Cách thực hiện:
– Bước 1: Nhập công thức =LEN(B3)-LEN(SUBSTITUTE(B3;”h”;””)) vào ô D3.
Nhập công thức Len và SUBSTITUTE vào D3
– Bước 2: Nhấn enter sẽ thu được kết quả là 1 tức là ký tự h chỉ xuất hiện 1 lần trong ô B3.
Kết quả sau khi nhập công thức
– Bước 3: Thực hiện công thức tương tự với các ô bên dưới sẽ thu được kết quả như sau:
1.2. Cách đếm số lần xuất hiện ký tự trong excel bằng hàm SUM kết hợp hàm LEN
Hàm SUM tính tổng các giá trị trong một phạm vi hoặc danh sách các ô. Hàm LEN trả về độ dài của một chuỗi.
Công thức để đếm số lần xuất hiện của một ký tự trong nhiều ô bằng hàm SUM và LEN là:
Công thức =SUM(LEN(Text);LEN(Text);LEN(Text))
Trong đó:
– text là ô chứa ký tự cần đếm.
Ví dụ: Cho bảng 2:
Yêu cầu: Đếm tổng số ký tự của ô B14, B15 và B16.
Cách thực hiện:
– Bước 1: Nhập công thức đếm ký tự =SUM(LEN(B14);LEN(B15);LEN(B16)) vào ô C14.
Nhập công thức vào ô C14
– Bước 2: Sau khi nhập xong công thức, nhấn enter sẽ thu được kết quả là 31. Tức là tổng số ký tự trong bảng 2 là 31.
Tổng số ký tự trong bảng 2 là 31
1.3. LEN – Hàm đếm ký tự trong ô
Hàm LEN trả về độ dài của một chuỗi, tức là số ký tự trong chuỗi đó.
Công thức để đếm số ký tự trong một ô bằng hàm LEN là:
=LEN(text)
Trong đó:
– text là ô chứa ký tự cần đếm.
Ví dụ: Cho bảng dữ liệu 3:
Yêu cầu: Đếm số ký tự trong từng ô trong bảng 3 bằng cách dùng hàm LEN.
Cách thực hiện:
– Bước 1: Đặt con trỏ chuột vào ô C23, nhập công thức =LEN(B23) vào ô này.
Nhập công thức LEN
– Bước 2: Nhấn enter sẽ thu được kết quả là 12, tức là tổng số ký tự trong ô B23 là 12 ký tự.
Kết quả sau khi nhập công thức
– Bước 3: Kéo thả con trỏ chuột xuống bên dưới sẽ thu được bảng kết quả như hình dưới đây:
1.4. Các dùng hàm COUNT để đếm giá trị trong excel
Hàm COUNT trả về số lượng các ô chứa các giá trị số trong một phạm vi hoặc danh sách các ô.
Công thức để đếm số lượng các ô chứa các giá trị số bằng hàm COUNT là:
=COUNT(Value1, Value2,….).
Trong đó:
– Value 1: Là giá trị đầu tiên để tham chiếu hoặc vùng chọn để đếm ô giá trị.
– Value 2: Là tham số không bắt buộc, chỉ có thể để đếm tối đa 255 tham số, ô tham chiếu hoặc vùng chọn bổ sung mà bạn muốn đếm giá trị ô.
Ví dụ: Cho bảng dữ liệu 4:
Yêu cầu: Đếm tổng số ô chứa dữ liệu số trong bảng trên
Cách thực hiện:
– Bước 1: Nhập công thức =COUNT(C34:C37) vào ô C38.
Nhập công thức vào C38
– Bước 2: Nhấn enter, kết quả trả lại là 3. Tức là số lượng ô chứa ký tự là số là 3.
Kết quả trả lại là 3
1.5. Các dùng hàm đếm ký tự bất kỳ trong excel COUNTIF
Hàm COUNTIF trả về số lượng các ô thỏa mãn một điều kiện cho trước trong một phạm vi hoặc danh sách các ô.
Công thức để đếm số lượng các ô chứa một ký tự bất kỳ bằng hàm COUNTIF là:
=COUNTIF(range, criteria)
Trong đó:
– Range: Vùng dữ liệu cần đếm
– Criteria: Điều kiện để đếm
Ví dụ: Cho bảng dữ liệu 5:
Yêu cầu: Đếm số sản phẩm có số lượng trên 50 chiếc.
Cách đếm ký tự trong ô excel:
– Bước 1: Ở ô C46 nhập công thức =COUNTIF(C42:C45;”>50″).
Nhập công thức COUNTIF
– Bước 2: Nhấn enter, kết quả trả về là 3 tức là có 3 sản phẩm có số lượng trên 50 chiếc.
Kết quả là 3
1.6. Các dùng hàm đếm số ký tự trong excel COUNTIFS
Hàm COUNTIFS trả về số lượng các ô thỏa mãn nhiều điều kiện cho trước trong một hoặc nhiều phạm vi hoặc danh sách các ô.
Công thức để đếm số lượng các ô chứa một ký tự bất kỳ và có một điều kiện khác bằng hàm COUNTIFS là:
=COUNTIFS(range1, criteria1, range2, criteria2,…)
Trong đó:
– range1, range2…: Có thể có tối đa 127 dãy các ô để đếm. Chúng có thể là ô chứa số, text, tên, mảng, hay tham chiếu đến các ô chứa số, ô rỗng sẽ được bỏ qua.
– criteria1, criteria2…: Có thể có tối đa 127 điều kiện để đếm.
Ví dụ: Cho bảng số liệu 6:
Yêu cầu: Đếm tổng số sản phẩm ở kho 1 có số lượng trên 10 chiếc.
Cách đếm số ký tự trong chuỗi:
– Bước 1: Viết công thức =COUNTIFS(C52:C55;”>10″;D52:D55;”=1″) vào ô C56.
Nhập công thức COUNTIFS
– Bước 2: Nhấn enter sẽ thu được kết quả là 2, tức là trong bảng trên có 2 sản phẩm thỏa mãn điều kiện là số lượng trên 10 chiếc và ở kho số 1.
Có 2 sản phẩm thỏa mãn điều kiện là số lượng trên 10 chiếc và ở kho số 1
>> Xem thêm: 3 Công thức tính giờ làm việc đơn giản nhất trong Excel
2. Đếm số ký tự cụ thể trong một phạm vi dữ liệu Excel
Công thức hàm đếm ký tự excel:
=SUMPRODUCT(LEN(range)-LEN(SUBSTITUTE(range, character,””))). Trong đó:
– Range : Phạm vi cần đếm
– Character: Ký tự cần đếm
Ví dụ: Cho bảng số liệu 7:
Yêu cầu: Đếm tổng số ký tự a, o và b có trong bảng 7.
Cách thực hiện hàm đếm số ký tự trong ô excel:
– Bước 1: Nhập công thức =SUMPRODUCT(LEN(B3:B6)-LEN(SUBSTITUTE(B3:B6;”a”;””))) vào ô D4.
Nhập công thức SUMPRODUCT và LEN vào ô D4
– Bước 2: Nhấn enter sẽ thu được kết quả là 4 tức là tổng số ký tự a có trong bảng 7 là 4.
Tổng số ký tự a có trong bảng 7 là 4
– Bước 3: Thực hiện công thức tương tự với các ô bên dưới sẽ thu được kết quả như sau:
3. Cách đếm tổng số ký tự trong một phạm vi
Muốn đếm tổng số ký tự trong một phạm vi bạn dùng công thức:
=LEN(range)+LEN(range)+LEN(range)
Hoặc là:
=SUM(LEN(range),LEN(range),LEN(range))
Hàm đếm số ký tự excel trên áp dụng với phạm vi cần đếm nhỏ. Để đếm tổng số ký tự trong phạm vi lớn hơn, ta nên dùng hàm SUMPRODUCT.
Đây là công thức chung để đếm các ký tự trong một phạm vi:
= SUMPRODUCT(LEN(range))
Ví dụ: Cho bảng số liệu 7:
Yêu cầu: Đếm tổng số ký tự trong bảng trên bằng hàm SUMPRODUCT kết hợp cùng hàm LEN.
Cách đếm số lần xuất hiện ký tự trong excel:
– Bước 1: Nhập công thức =SUMPRODUCT(LEN(B4:B6)) vào ô B8.
Nhập công thức vào ô B8
– Bước 2: Nhấn enter sẽ thu được kết quả là 34, tức là tổng số ký tự trong bảng 7 là 34.
Tổng số ký tự trong bảng 7 là 34
>> Xem thêm: Hướng dẫn cách sửa lỗi Excel không tự nhảy công thức
4. Đếm ký tự trong nhiều ô tính
Bạn dùng lệnh đếm số ký tự trong excel =SUMPRODUCT(LEN(range))
Chú ý: Để hiển thị kết quả, bạn nhấn Ctrl + Shift + Enter.
Công thức này được sử dụng để giúp bạn xác định xem hàng nào vượt quá ký tự trước khi nhập dữ liệu bảng biểu. Bạn chỉ cần nhập công thức vào cột phụ và kéo sao chép công thức xuống hết các dòng là được.
Ví dụ: Cho bảng số liệu 8:
Yêu cầu: Đếm tổng số ký tự ở hai ô là Tên mặt hàng và Kho hàng.
Cách đếm số lần xuất hiện ký tự trong excel:
– Bước 1: Nhập công thức =SUMPRODUCT(LEN(B17:C17)) vào ô D17.
Nhập công thức SUMPRODUCT và LEN
– Bước 2: Nhấn enter sẽ thu được kết quả là 7 tức là tổng số ký tự trong hai ô Tên mặt hàng và Kho hàng là 7.
Kết quả sau khi nhập công thức
– Bước 3: Sao chép công thức xuống những ô bên dưới sẽ thu được kết quả như sau:
5. Kết luận
Trong bài viết này, tôi đã hướng dẫn bạn cách đếm số lần xuất hiện ký tự trong Excel bằng các hàm và công thức khác nhau. Bạn có thể áp dụng các cách này cho các ký tự bất kỳ, như chữ cái, số, dấu câu, khoảng trắng hay ký tự đặc biệt. Hy vọng bài viết này sẽ giúp bạn làm việc với Excel hiệu quả hơn. Và đừng quên bạn hoàn toàn có thể nắm trọn kiến thức về học Kế toán online, hệ thống hàm Excel cần thiết và quan trọng nhất tại các khoá học Excel giúp bạn nắm trọn bí quyết thống kê, lưu trữ số liệu và lập báo cáo một cách chính xác, nhanh chóng nhất.
Cảm ơn các bạn đã theo dõi và chúc thành công!
>> Xem thêm: Trọn bộ tin học văn phòng cơ bản học phí ưu đãi chỉ từ 99K
Tags: Excel
