Hướng dẫn cách đếm ký tự, ký tự đặc biệt trong một ô hoặc một dải ô trong Excel – Thủ thuật Excel

Bài hướng dẫn này Học Excel Online sẽ giải thích cách đếm số ký tự trong Excel. Bạn sẽ biết được công thức đếm tất cả ký tự trong một dải ô, hay công thức dùng để đếm chỉ các ký tự đặc biệt trong một ô hay vài ô.

Trong bài hướng dẫn trước, chúng tôi đã giới thiệu hàm LEN trong Excel, hàm này cho phép chúng ta đếm tất cả ký tự trong một ô.

Chỉ dùng hàm LEN thôi cũng rất hiệu quả rồi, nhưng khi kết hợp với các hàm khác như hàm SUM, SUMPRODUCT, và SUBSTITUTE thì hàm LEN có thể giải được các bài toán phức tạp hơn rất nhiều. Tiếp tục trong bài hướng dẫn này, chúng ta sẽ xem xét kỹ hơn các công thức đơn giản và nâng cao để đếm số ký tự trong Excel.

CÁCH ĐẾM TẤT CẢ KÝ TỰ TRONG MỘT DẢI Ô

Khi nói đến việc đếm tất cả ký tự trong một vài ô thì giải pháp mà bạn nghĩ ngay đến đó chính là đếm số ký tự trong mỗi ô, rồi cộng các con số đó lại:

=LEN(A2)+(A3)+(A4)

Hay

=SUM(LEN(A2),LEN(A3),LEN(A4))

Các công thức trên có lẽ sẽ hiệu quả trong một dải ô nhỏ. Để đếm tất cả ký tự trong một dải ô lớn hơn, chúng ta nên nghĩ ra một công thức khác gọn hơn, ví dụ: hàm SUMPRODUCT, hàm này sẽ cộng các mảng lại với nhau rồi trả về tổng của các kết quả đó.

Đây là công thức chung trong Excel để đếm tất cả ký tự trong một dải ô:

=SUMPRODUCT(LEN(dải ô))

Và khi áp dụng nó sẽ trông như thế này:

=SUMPRODUCT(LEN(A1:A7))

Cách khác để đếm số ký tự trong một dải ô đó là sử dụng hàm LEN kết hợp với hàm SUM:

=SUM(LEN(A1:A7))

Không giống hàm SUMPRODUCT, hàm SUM không tính các mảng một cách mặc định, và bạn cần phải nhấn Ctrl+Shift+Enter để đưa nó về công thức mảng.

Khi được diễn giải trong ảnh chụp màn hình dưới đây, hàm SUM trả về cùng một kết quả số ký tự:

CÔNG THỨC ĐẾM SỐ KÝ TỰ TRONG MỘT DẢI Ô HOẠT ĐỘNG NHƯ THẾ NÀO

Đây là một trong những công thức dễ nhất để đếm số ký tự trong Excel. Hàm LEN tính toán chiều dài chuỗi cho mỗi ô trong một dải ô xác định rồi trả về một dãy số. Sau đó, hàm SUMPRODUCT hay SUM cộng các con số này với nhau và hiện kết quả là tổng số các ký tự.

Trong các ví dụ trên, một dãy gồm bảy con sốchính là chiều dài chuỗi trong các ô từ A1 đến A7 khi được cộng:

=SUMPRODUCT({28;29;27;23;27;29;30})

• Lưu ý. Hãy chú ý rằng hàm LEN trong Excel cộng tất cả các ký tự trong mỗi ô một cách tuyệt đối, kể cả chữ cái, chữ số, dấu câu, ký tự đặc biệt, và tất cả các khoảng trống (thụt đầu dòng, thụt cuối dòng và khoảng cách giữa các từ).

Xem ngay: lớp học VBA ở Hà Nội

LÀM THẾ NÀO ĐỂ ĐẾM CÁC KÝ TỰ ĐẶC BIỆT TRONG MỘT Ô

Thỉnh thoảng, thay vì phải đếm tất cả ký tự trong một ô, bạn có thể chỉ cần phải đếm sự xuất hiện của một ký tự, chữ số, hay biểu tượng đặc biệt.

Để đếm tần suất xuất hiện của một ký tự xác định trong một ô, hãy sử dụng hàm LEN kết hợp với hàm SUBSTITUTE:

=LEN(ô)-LEN(SUBSTITUTE(ô, ký tự,””))

Để hiểu rõ ví dụ hơn, hãy xem qua ví dụ dưới đây nhé.

Gỉả sử, bạn lưu giữ một cơ sở dữ liệu của những món hàng được giao, nơi mà mỗi loại hàng có ký hiệu nhận dạng riêng biệt. Và mỗi ô có một vài món hàng cách nhau bởi dấu phẩy, khoảng trống, hay bất cứ dấu phân cách nào. Nhiệm vụ của bạn là hãy đếm số lần xuất hiện của ký hiệu nhận dạng riêng biệt được xác định trong mỗi ô.

Gỉả sử rằng danh sách các món hàng nằm ở cột B (bắt đầu từ B2) và chúng ta sẽ đếm số lần xuất hiện của “A”, dưới đây là công thức:

=LEN(B2)-LEN(SUBSTITUTE(B2,”A”,””))

CÔNG THỨC ĐẾM KÝ TỰ NÀY TRONG EXCEL HOẠT ĐỘNG NHƯ THẾ NÀO

Để hiểu quy luật của công thức này, hãy chia nó ra thành từng phần nhỏ:

Đầu tiên, bạn đếm tổng chiều dài chuỗi của ô B2:

LEN(B2)

Sau đó, bạn dùng hàm SUBSTITUTE để loại bỏ số lần xuất hiện của “A” trong B2 bằng cách thay thế nó bằng một chuỗi rỗng(“”):

SUBSTITUTE(B2,”A”,””)

Sau đó, bạn đếm chiều dài chuỗi mà không có ký tự “A”:

LEN(SUBSTITUTE(B2,”A”,””))

Cuối cùng, bạn lấy tổng chiều dài chuỗi trừ cho chiều dài của chuỗi không có ký tự “A”.

Do đó, bạn tính được số ký tự “được loại bỏ”, kết quả này cũng là số lần xuất hiện của ký tự đó trong một ô.

Thay vì xác định ký tự bạn muốn đếm trong công thức, bạn có thể gõ nó vào một vài ô, rồi tham chiếu ô đó trong công thức. Bằng cách này, người dùng của bạn sẽ có khả năng đếm số lần xuất hiện của bất cứ ký tự nào họ nhập vào ô đó mà không phá công thức của bạn:

• Lưu ý. Hàm SUBSTITUTE của Excel là một hàm phân biệt dạng chữ, và vì thế các công thức trên đều phân biệt dạng chữ theo. Ví dụ, trong ảnh chụp màn hình ở trên, ô B3 có 3 lần xuất hiện của “A”-hai lần viết hoa, và một lần viết thường. Công thứ c chỉ đếm các ký tự viết hoa bởi vì chúng ta cung cấp “A” cho hàm SUBSTITUTE.

DÙNG HÀM KHÔNG PHÂN BIỆT DẠNG CHỮ TRONG EXCEL ĐỂ ĐẾM CÁC KÝ TỰ ĐẶC BIỆT TRONG MỘT Ô

Nếu bạn cần một phép đếm không phân biệt dạng chữ, hãy lồng ghép hàm UPPER vào hàm SUBSTITUTE để đổi ký tự xác định sang dạng viết hoa trước khi chạy phép thế. Và, hãy chắc rằng bạn đã nhập ký tự viết hoa vào công thức.

Ví dụ, để đếm các ký tự “A” và “a” trong ô B2, hãy dùng công thức này:

=LEN(B2)-LEN(SUBSTITUTE(UPPER(B2),”A”,””))

Cách khác đó là lồng ghép hàm SUBSTITUTE vào:

=LEN(B2)-LEN(SUBSTITUTE(SUBSTITUTE(B2,”A”,””),”a”,””))

Bạn có thể thấy qua ảnh chụp màn hình bên dưới, cả hai công thức đều đếm sự xuất hiện của ký tự xác định dưới dạng viết hoa và viết thường một cách hoàn hảo:

Trong vài trường hợp, bạn có thể cần phải đếm nhiều ký tự khác nhau trong một bảng, nhưng bạn lại không muốn phải điều chỉnh công thức mỗi lần tính toán. Trong trường hợp này, hãy lồng ghép hàm SUBSTITUE này vào hàm SUBSTITUTE khác, rồi gõ ký tự bạn muốn đếm vào một ô nào đó (trường hợp này là ô D1), và đổi giá trị của ô  này sang dạng viết hoa và viết thường bẳng cách sử dụng hàm UPPER và LOWER:

=LEN(B2)-LEN(SUBSTITUTE(SUBSTITUTE(B2, UPPER($D$1),””),LOWER($D$1),””))

Hãy luân phiên thay đổi ô nguồn và ô chứa hoặc ký tự viết thường hoặc ký tự viết hoa với nhau. Ví dụ:

=LEN(B2)_LEN(SUBSTITUTE(UPPER(B2),UPPER($C$1),””))

Điểm cộng của cách này đó là dù ta nhập ký tự viết thường hay viết hoa vào ô được tham chiếu thì công thức đếm ký tự không phân biệt dạng chữ của bạn đều  sẽ trả về kết quả đếm chính xác:

ĐẾM SỰ XUẤT HIỆN CỦA MỘT KÝ TỰ NHẤT ĐỊNH HAY CHUỖI CON TRONG MỘT Ô

Nếu bạn muốn đếm số lần xuất hiện của một sự kết hợp cụ thể các ký tự (ví dụ, một chuỗi ký tự nhất định, hay chuỗi con) trong một ô xác định, ví dụ, “A2” hay “SS” thì hãy chia số ký tự được trả về bởi công thức trên cho chiều dài của chuỗi con.

Công thức phân biệt dạng chữ:

=(LEN(B2)-LEN(SUBSTITUTE(B2,$C$1,””)))/LEN($C$1)

Công thức không phân biệt dạng chữ:

=(LEN(B2)-LEN(SUBSTITUTE(LOWER(B2),LOWER($C$1),””)))/LEN($C$1)

B2 là ô chứa cả một chuỗi ký tự, và C1 là chuỗi ký tự (chuỗi con) mà bạn muốn đếm.

LÀM THẾ NÀO ĐỂ ĐẾM CÁC KÝ TỰ ĐẶC BIỆT TRONG MỘT DẢI Ô

Giờ thì bạn đã biết công thức trong Excel dùng để đếm các ký tự trong một ô, bạn có thể muốn cải thiện nó thêm để tìm ra số lần xuất hiện của một ký tự nhất định trong một dải ô. Về việc này, chúng ta sẽ sử dụng hàm LEN trong Excel để đếm một ký tự đặc biệt trong một ô, điều này đã được nói đến trong ví dụ trên, rồi đặt nó vào hàm SUMPRODUCT, hàm này có thể giúp ta xử lý các mảng:

SUMPRODUCT(LEN(chuỗi)-LEN(SUBSTITUTE(chuỗi, ký tự,””)))

Trong ví dụ này, công thức có dạng như sau:

=SUMPRODUCT(LEN(B2:B8)-LEN(SUBSTITUTE(B2:B8,”A”,””)))

Và đây là công thức khác dùng để đếm các ký tự trong dải ô Excel:

=SUM(LEN(B2:B8)-LEN(SUBSTITUTE(B2:B8,”A”,””)))

So với công thức đầu tiên, điểm khác biệt dễ nhận thấy nhất đó chính là việc dùng hàm SUM thay vì hàm SUMPRODUCT. Điểm khác biệt khác đó chính là bạn phải nhấn Ctrl+Shift+Enter bởi vì không giống như hàm SUMPRODUCT, là hàm được tạo ra để xử lý các mảng, hàm SUM có thể xử lý các mảng chỉ khi được dùng trong công thức mảng.

Nếu không muốn ghi trong công thức một cách cố định trong công thức, thì tất nhiên bạn có thể nhập nó vào một ô nào đó, ví dụ là ô D1, rồi tham chiếu ô đó vào công thức đếm ký tự của mình:

=SUMPRODUCT(LEN(B2:B8)-LEN(SUBSTITUTE(B2:B8,D1,””)))

CÔNG THỨC ĐẾM KÝ TỰ NÀY HOẠT ĐỘNG NHƯ THẾ NÀO

Chắc bạn còn nhớ, hàm SUBSTITUTE được dùng để thay thế tất cả sự xuất hiện của ký tự xác định (trong ví dụ này là ký tự “A”) bằng một chuỗi ký tự rỗng (“”).

Sau đó, chúng ta cung cấp chuỗi ký tự được trả về bởi hàm SUBSTITUTE cho hàm LEN trong Excel để nó tính chiều dài chuỗi không có ký tự “A”. Rồi sau đó, chúng ta lấy tổng chiều dài chuỗi ký tự trừ cho chuỗi không có ký tự “A” đó. Kết quả của các phép tính này chính là một mảng các số ký tự, trong đó mỗi ô cho một số ký tự.

Cuối cùng, hàm SUMPRODUCT tính tổng các con số trong mảng rồi trả về tổng số ký tự xác định trong một dải ô.

MỘT CÔNG THỨC KHÔNG PHÂN BIỆT DẠNG CHỮ ĐỂ ĐẾM CÁC KÝ TỰ ĐẶC BIỆT TRONG MỘT DẢI Ô

Có lẽ, bạn đã biết hàm SUBSTITUTE là hàm phân biệt dạng chữ, hàm này cũng được dùng cho công thức đếm ký tự có phân biệt dạng chữ.

Để làm cho công thức không phân biệt dạng chữ nữa, hãy làm theo các bước đã được diễn giải trong ví dụ trước: mục CÔNG THỨC KHÔNG PHÂN BIỆT DẠNG CHỮ DÙNG ĐỂ ĐẾM CÁC KÝ TỰ ĐẶC BIỆT TRONG MỘT Ô.

Cụ thể là, bạn có thể sử dụng một trong những công thức dưới đây để đếm số ký tự đặc biệt trong một dải ô mà không phân biệt dạng chữ:

• Sử dụng hàm UPPER và gõ ký tự in hoa:

=SUMPRODUCT(LEN(B2:B8)-LEN(SUBSTITUTE(UPPER(B2:B8),”A”,””)))

• Sử dụng hàm SUBSTITUTE trong hàm SUBSTITUTE:

=SUMPRODUCT(LEN(B2:B8)-LEN(SUBSTITUTE(SUBSTITUTE((B2:B8),”A”,””),”a”,””)))

• Sử dụng hàm UPPER và hàm LOWER, hãy gõ hoặc ký tự viết hoa hoặc ký tự viết thường vào một ô nào đó, rồi tham chiếu ô đó trong công thức của bạn:

=SUMPRODUCT(LEN(B2:B8)-LEN(SUBSTITUTE(SUBSTITUTE((B2:B8),UPPER($E$1),””), LOWER($E$1),””)))

Ảnh chụp màn hình bên dưới diễn giải cách sử dụng công thức cuối cùng:

Để có thể ứng dụng tốt Excel vào trong công việc, chúng ta không chỉ nắm vững được các hàm mà còn phải sử dụng tốt cả các công cụ của Excel. Những hàm nâng cao giúp áp dụng tốt vào công việc như SUMIFS, COUNTIFS, SUMPRODUCT, INDEX + MATCH… Những công cụ thường sử dụng là Data validation, Conditional formatting, Pivot table…

Toàn bộ những kiến thức này các bạn đều có thể học được trong khóa học