Bài viết này sẽ hướng dẫn bạn cách đếm số ký tự trong Excel, từ đơn giản đến phức tạp. Bạn sẽ học được cách đếm tất cả ký tự trong một dải ô, cũng như cách đếm các ký tự đặc biệt trong một hoặc nhiều ô.
Hàm LEN trong Excel là công cụ cơ bản để đếm ký tự trong một ô. Tuy nhiên, khi kết hợp với các hàm khác như SUM, SUMPRODUCT, và SUBSTITUTE, LEN có thể giải quyết những bài toán phức tạp hơn. Chúng ta sẽ cùng tìm hiểu chi tiết các công thức này.
Đếm Tất Cả Ký Tự trong Một Dải Ô
Để đếm tổng số ký tự trong một vài ô, bạn có thể đếm số ký tự từng ô rồi cộng lại:
=LEN(A2)+LEN(A3)+LEN(A4)Hoặc:
=SUM(LEN(A2),LEN(A3),LEN(A4))
Đếm ký tự cơ bản
Đếm ký tự cơ bản
Tuy nhiên, cách này chỉ phù hợp với dải ô nhỏ. Với dải ô lớn, hàm SUMPRODUCT sẽ hiệu quả hơn, bằng cách cộng các mảng và trả về tổng:
=SUMPRODUCT(LEN(dải_ô))Ví dụ:
=SUMPRODUCT(LEN(A1:A7))
Đếm ký tự với SUMPRODUCT
Một cách khác là kết hợp hàm LEN với hàm SUM:
=SUM(LEN(A1:A7))Khác với SUMPRODUCT, hàm SUM không tự động tính toán mảng. Bạn cần nhấn Ctrl + Shift + Enter để biến nó thành công thức mảng.
Kết quả của hàm SUM cũng tương tự:
Đếm ký tự với SUM
Cơ chế hoạt động: Hàm LEN tính chiều dài chuỗi trong mỗi ô của dải ô, trả về một dãy số. SUMPRODUCT hoặc SUM sẽ cộng dãy số này để ra tổng số ký tự.
Ví dụ trên, dãy 7 số là chiều dài chuỗi của các ô A1 đến A7:
=SUMPRODUCT({28;29;27;23;27;29;30})Lưu ý: Hàm LEN tính tất cả ký tự, bao gồm chữ cái, số, dấu câu, ký tự đặc biệt và khoảng trắng.
Đếm Ký Tự Đặc Biệt trong Một Ô
Đôi khi, bạn chỉ cần đếm số lần xuất hiện của một ký tự, số, hoặc biểu tượng đặc biệt.
Để làm điều này, hãy kết hợp hàm LEN với hàm SUBSTITUTE:
=LEN(ô)-LEN(SUBSTITUTE(ô, ký_tự,""))Ví dụ: Bạn có danh sách các mặt hàng được giao, mỗi mặt hàng có mã riêng. Mỗi ô chứa nhiều mặt hàng, cách nhau bởi dấu phẩy hoặc khoảng trắng. Bạn cần đếm số lần xuất hiện của một mã cụ thể trong mỗi ô.
Giả sử danh sách mặt hàng ở cột B (từ B2) và bạn muốn đếm số lần xuất hiện của “A”:
=LEN(B2)-LEN(SUBSTITUTE(B2,"A",""))
Đếm ký tự đặc biệt
Cơ chế hoạt động:
LEN(B2): Đếm tổng chiều dài chuỗi trong ô B2.SUBSTITUTE(B2,"A",""): Thay thế tất cả “A” trong B2 bằng chuỗi rỗng.LEN(SUBSTITUTE(B2,"A","")): Đếm chiều dài chuỗi mới (không có “A”).- Lấy tổng chiều dài chuỗi ban đầu trừ đi chiều dài chuỗi mới để ra số lần xuất hiện của “A”.
Bạn có thể nhập ký tự cần đếm vào một ô riêng (ví dụ D1) và tham chiếu ô đó trong công thức:
Công thức đếm ký tự đặc biệt
Lưu ý: Hàm SUBSTITUTE phân biệt chữ hoa chữ thường.
Đếm Ký Tự Không Phân Biệt Chữ Hoa Chữ Thường
Để đếm không phân biệt chữ hoa chữ thường, hãy dùng hàm UPPER hoặc LOWER kết hợp với SUBSTITUTE:
=LEN(B2)-LEN(SUBSTITUTE(UPPER(B2),"A",""))Hoặc:
=LEN(B2)-LEN(SUBSTITUTE(SUBSTITUTE(B2,"A",""),"a",""))
Đếm ký tự không phân biệt chữ hoa chữ thường
Để linh hoạt hơn, bạn có thể nhập ký tự vào ô D1 và dùng công thức sau:
=LEN(B2)-LEN(SUBSTITUTE(SUBSTITUTE(B2,UPPER($D$1),""),LOWER($D$1),""))Hoặc:
=LEN(B2)-LEN(SUBSTITUTE(UPPER(B2),UPPER($C$1),""))
Đếm ký tự không phân biệt chữ hoa chữ thường
Đếm Chuỗi Con trong Một Ô
Để đếm số lần xuất hiện của một chuỗi con (ví dụ: “A2”, “SS”), hãy chia kết quả của công thức trên cho chiều dài của chuỗi con.
Công thức phân biệt chữ hoa chữ thường:
=(LEN(B2)-LEN(SUBSTITUTE(B2,$C$1,"")))/LEN($C$1)Công thức không phân biệt chữ hoa chữ thường:
=(LEN(B2)-LEN(SUBSTITUTE(LOWER(B2),LOWER($C$1),"")))/LEN($C$1)
Đếm chuỗi con
Đếm Ký Tự Đặc Biệt trong Một Dải Ô
Để đếm ký tự đặc biệt trong một dải ô, hãy kết hợp hàm LEN và SUBSTITUTE với hàm SUMPRODUCT:
=SUMPRODUCT(LEN(dải_ô)-LEN(SUBSTITUTE(dải_ô,ký_tự,"")))
Đếm ký tự đặc biệt trong dải ô
Ví dụ:
=SUMPRODUCT(LEN(B2:B8)-LEN(SUBSTITUTE(B2:B8,"A","")))Hoặc dùng hàm SUM (nhớ nhấn Ctrl + Shift + Enter):
=SUM(LEN(B2:B8)-LEN(SUBSTITUTE(B2:B8,"A","")))Bạn cũng có thể nhập ký tự vào ô D1 và tham chiếu nó:
=SUMPRODUCT(LEN(B2:B8)-LEN(SUBSTITUTE(B2:B8,D1,"")))Cơ chế hoạt động: Tương tự như đếm ký tự đặc biệt trong một ô, nhưng áp dụng cho cả dải ô và dùng SUMPRODUCT để tính tổng.
Đếm Ký Tự trong Dải Ô Không Phân Biệt Chữ Hoa Chữ Thường
Tương tự như trên, bạn có thể sử dụng hàm UPPER, LOWER kết hợp với SUBSTITUTE và SUMPRODUCT:
=SUMPRODUCT(LEN(B2:B8)-LEN(SUBSTITUTE(UPPER(B2:B8),"A","")))=SUMPRODUCT(LEN(B2:B8)-LEN(SUBSTITUTE(SUBSTITUTE((B2:B8),"A",""),"a","")))=SUMPRODUCT(LEN(B2:B8)-LEN(SUBSTITUTE(SUBSTITUTE((B2:B8),UPPER($E$1),""),LOWER($E$1),"")))
Đếm ký tự trong dải ô không phân biệt chữ hoa chữ thường
Hy vọng bài viết này giúp bạn nắm vững cách đếm ký tự trong Excel. Việc thành thạo các hàm và công cụ trong Excel sẽ giúp bạn xử lý dữ liệu hiệu quả hơn.
Discussion about this post