Hướng dẫn cách tính số lượng ký tự và ký tự đặc biệt trong một ô hoặc một loạt ô trong Excel

Trong bài viết này, Siêu Marketing sẽ chỉ dẫn bạn cách thực hiện việc đếm số lượng các ký tự trong Excel, bao gồm cách tìm ra số lượng ký tự trong một loạt ô cũng như chỉ đếm các ký tự đặc biệt trong một hoặc nhiều ô cụ thể.

Trong hướng dẫn trước của chúng tôi, chức năng LEN trong Excel đã được giới thiệu, cho phép chúng ta xác định tổng số ký tự có trong một ô bất kỳ.

Dùng riêng lẻ hàm LEN đã rất tiện lợi, tuy nhiên khi kết hợp nó với những hàm khác như SUM, SUMPRODUCT và SUBSTITUTE, chức năng của LEN được mở rộng, giúp giải quyết nhiều vấn đề phức tạp hơn. Qua hướng dẫn tiếp theo này, các công thức đơn giản cũng như phức tạp hơn sẽ được phân tích chi tiết, hướng dẫn bạn cách đếm ký tự trong Excel.

CÁCH TÍNH TỔNG SỐ KÝ TỰ TRONG MỘT LOẠT Ô

Để đếm tổng số ký tự trong vài ô, bạn thường nghĩ đến việc tính số ký tự của từng ô rồi cộng lại:

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

Hoặc

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

Những công thức trên có thể hữu ích cho một dãy ô nhỏ. Đối với dãy ô lớn hơn, một giải pháp gọn gàng hơn, chẳng hạn như sử dụng hàm SUMPRODUCT, có thể được áp dụng để tính tổng các mảng kết quả.

Đây là công thức tổng quát trong Excel để đếm ký tự của một loạt ô:

=SUMPRODUCT(LEN(loạt ô))

Công thức này khi được áp dụng sẽ như sau:

=SUMPRODUCT(LEN(A1:A7))

Một phương pháp khác để đếm ký tự trong loạt ô là dùng hàm LEN cùng với hàm SUM:

=SUM(LEN(A1:A7))

Điểm khác biệt là hàm SUM không xử lý mảng tự động như SUMPRODUCT, và bạn phải sử dụng tổ hợp phím Ctrl+Shift+Enter để biến đổi nó thành công thức mảng.

Hàm SUM sẽ cho kết quả đếm ký tự tương tự như đã thể hiện trong hình ảnh dưới đây:

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

Đây là một phương pháp dễ dàng để đếm ký tự trong Excel: Hàm LEN đo lường độ dài chuỗi của từng ô trong một loạt ô đã được định trước, sau đó trả về một chuỗi các số. Tiếp theo, SUMPRODUCT hoặc SUM sẽ cộng tổng số này lại để đưa ra kết quả cuối cùng là tổng số ký tự của loạt ô.

Trong các ví dụ đã nêu, bảy số liệu được cộng lại đại diện cho chiều dài của chuỗi trong các ô từ A1 đến A7 như sau:

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

  • Chú ý. Đáng chú ý rằng, hàm LEN trong Excel đếm tất cả mọi thứ xuất hiện trong ô, bất kể đó là chữ, chữ số, dấu câu, ký tự đặc biệt, hay cả khoảng trắng ở đầu, cuối và giữa các từ.

PHƯƠNG PHÁP ĐẾM CÁC KÝ TỰ ĐẶC BIỆT TRONG MỘT Ô

Đôi khi bạn không cần đếm mọi ký tự trong một ô mà chỉ cần tìm số lượng của một ký tự, chữ số hoặc biểu tượng cụ thể nào đó.

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

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

Một ví dụ cụ thể sẽ làm rõ hơn cách sử dụng công thức này.

Giả sử bạn có một danh sách hàng hóa vận chuyển, nơi mỗi hàng hóa có một mã đặc trưng. Trong mỗi ô, các mặt hàng được phân cách bởi dấu phẩy, khoảng trống, hoặc một loại dấu phân cách nào đó. Nhiệm vụ của bạn là đếm số lần một ký hiệu cụ thể xuất hiện trong ô.

Hãy tưởng tượng rằng chúng ta có danh sách sản phẩm được liệt kê trong cột B, khởi đầu từ cell B2, và bây giờ chúng ta cần tính toán tần suất xuất hiện của ký tự “A”. Công thức dưới đây sẽ giúp thực hiện điều đó:

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

CÁCH THỨC LÀM VIỆC CỦA CÔNG THỨC ĐẾM KÝ TỰ TRONG EXCEL

Tìm hiểu cách hoạt động của công thức bằng cách phân tích nó thành các bước sau:

Lần đầu tiên, ta tính độ dài của chuỗi ở ô B2:

LEN(B2)

Kế tiếp, sử dụng hàm SUBSTITUTE để thay thế “A” trong B2 bằng chuỗi trống (“”) và loại bỏ nó:

SUBSTITUTE(B2,”A”,””)

Đo độ dài của chuỗi sau khi đã loại bỏ ký tự “A”:

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

Để kết thúc, ta sẽ trừ độ dài ban đầu của chuỗi đi độ dài của chuỗi mà không chứa “A”.

Qua đó, ta có thể tính ra được số ký tự đã được “gỡ bỏ”, và đây chính là số lần ký tự đó xuất hiện trong ô đã chọn.

Ví dụ để không cần thiết phải chỉ định trực tiếp ký tự cần đếm, bạn có thể nhập nó vào một ô nhất định và sau đó tham chiếu ô đó trong công thức. Phương pháp này cho phép người dùng có khả năng đếm số lần xuất hiện của bất kỳ ký tự nào mà không làm hỏng công thức gốc:

  • Chú ý: Hàm SUBSTITUTE trong Excel phân biệt cả chữ hoa và chữ thường, do vậy, các công thức trên đều tùy thuộc vào điều đó. Chẳng hạn, như người ta thấy trong hình chụp màn hình phía trên, ô B3 có ba lần xuất hiện của “A”—hai chữ hoa, một chữ thường. Công thức sẽ chỉ đếm chữ hoa vì chúng ta đã sử dụng “A” trong hàm SUBSTITUTE.

ĐỂ ĐẾM KÝ TỰ MÀ KHÔNG PHÂN BIỆT CHỮ HOA THƯỜNG TRONG EXCEL, SỬ DỤNG HÀM KHÔNG PHÂN BIỆT CHỮ

Nếu bạn mong muốn kết quả đếm không phân biệt chữ hoa hay chữ thường, bạn nên kết hợp hàm UPPER vào SUBSTITUTE để biến đổi chữ cái đang xem xét thành chữ hoa trước khi thực hiện thay thế. Hãy đảm bảo rằng bạn chèn ký tự chữ hoa vào công thức.

Chẳng hạn, nếu muốn biết số các “A” hoặc “a” trong ô B2:

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

Một cách tiếp cận khác là kết hợp nhiều hàm SUBSTITUTE lại với nhau:

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

Như bạn có thể quan sát trong hình ảnh dưới đây, cả hai phương pháp trên đều thu được kết quả đếm chính xác cho cả chữ hoa và chữ thường:

Trong một số tình huống, bạn có thể cần tính đếm nhiều ký tự khác nhau trong một danh sách, nhưng lại không muốn chỉnh sửa công thức mỗi khi tính toán. Trong tình huống này, hãy ghép nối các hàm SUBSTITUTE lại với nhau, nhập ký tự bạn muốn đếm vào một ô khác (đối với ví dụ này là ô D1), và biến đổi chữ đó thành chữ hoa và chữ 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),””))

Bạn nên linh hoạt đổi chỗ ô nguồn với ô chứa kết quả, chứa ký tự viết thường hay viết hoa tùy vào trường hợp. Ví dụ:

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

Ưu điểm của phương pháp này là dù bạn nhập ký tự chữ hoa hay chữ thường vào ô được tham chiếu, công thức đếm ký tự không phân biệt dạng chữ vẫn đưa ra kết quả đếm đúng.

CÁCH THỨC ĐẾM LẦN XUẤT HIỆN CỦA KÝ TỰ HAY CHUỖI KÝ TỰ TẠI MỘT Ô NÀO ĐÓ

Đối với việc đếm tần suất của một chuỗi ký tự cụ thể (tức là chuỗi con), ta sẽ phải thực hiện phép chia kết quả độ dài chuỗi ký tự do công thức trên cung cấp cho chiều dài chuỗi con đó nếu muốn biết số lần xuất hiện trong ô cụ thể như “A2” hay “SS”.

Đây là công thức có tính đến dạng chữ:

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

Công thức không tính đến dạng chữ:

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

B2 là vị trí gồm chuỗi ký tự cần quan sát trong khi C1 chính là chuỗi bạn đang tìm số lần xuất hiện.

CÁC BƯỚC ĐỂ ĐẾM KÝ TỰ ĐẶC BIỆT QUA PHẠM VI NHIỀU Ô

Nắm được cách đếm số ký tự trong một ô từ Excel, có lẽ bạn sẽ muốn mở rộng nó đến việc xác định số lần một ký tự cụ thể xuất hiện qua toàn bộ dãy các ô. Sử dụng hàm LEN – đã được giới thiệu qua ví dụ trên, để thực hiện việc này cho một ô – và kết hợp nó với hàm SUMPRODUCT cho phạm vi các ô, chúng ta có thể xử lý mảng số liệu:

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

Công thức trong trường hợp này sẽ là:

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

Đoạn sau giới thiệu một công thức khác để tính số ký tự qua dãy ô trong Excel:

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

Công thức này khác với công thức ban đầu ở chỗ nó áp dụng hàm SUM thay cho SUMPRODUCT. Khác biệt tiếp theo là cần áp dụng Ctrl+Shift+Enter do hàm SUM không tự xử lý mảng như SUMPRODUCT mà chỉ khi nằm trong công thức mảng, mới có thể làm vậy.

Và không cần phải gắn kết chặt chẽ một ký tự cố định với công thức, bạn hoàn toàn có thể nhập ký tự đó vào một ô khác, ví dụ D1, rồi gọi ô đó vào công thức đếm của bạn:

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

GIẢI THÍCH CÔNG THỨC ĐẾM KÝ TỰ

Như bạn có thể nhớ, hàm SUBSTITUTE được dùng để thay thế tất cả các lần xuất hiện của một ký tự cụ thể (trong trường hợp này là “A”) với một chuỗi rỗng (“”).

Sau đó chúng ta tính độ dài chuỗi ký tự không có ký tự “A” bằng cách truyền chuỗi đã qua SUBSTITUTE cho hàm LEN của Excel. Tiếp đến, ta lấy tổng độ dài chuỗi ban đầu và trừ đi độ dài chuỗi không chứa “A”. Phép toán này cho ra một mảng các độ dài, với mỗi giá trị ứng với một ô.

Cuối cùng, hàm SUMPRODUCT thực thi việc cộng tổng các giá trị trong mảng, cung cấp tổng số lần ký tự cụ thể xuất hiện qua một dải ô.

THỰC HIỆN CÔNG THỨC KHÔNG PHÂN BIỆT CHỮ HOA CHỮ THƯỜNG ĐỂ ĐẾM KÝ TỰ TRONG DẢI Ô

Bạn có lẽ đã biết rằng, hàm SUBSTITUTE phụ thuộc vào việc phân biệt hoa thường, và nó cũng được sử dụng cho việc đếm số ký tự mà phân biệt cả hai dạng này.

Để biến công thức trở nên không phân biệt dạng chữ, hãy làm theo hướng dẫn từ ví dụ trước: phần GIẢI THÍCH CÔNG THỨC KHÔNG PHÂN BIỆT DẠNG CHỮ KHI ĐẾM KÝ TỰ TRONG MỘT Ô.

Để cụ thể hóa, bạn có thể dùng một trong các công thức sau để tính số ký tự đặc biệt trong một dải ô mà không cần quan tâm đến việc chữ đó là hoa hay thường:

Các biến thể về cách viết công thức:

  • Để biến ký tự thành chữ in hoa, áp dụng hàm UPPER:

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

  • Thực hiện thay thế lồng ghép hai hàm SUBSTITUTE:

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

  • Kết hợp sử dụng hàm UPPER và LOWER. Nhập ký tự in hoa hoặc thường vào một ô, sau đó liên kết ô đó vào phép tính:

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

Mô tả quá trình sử dụng công thức cuối cùng thông qua hình ảnh dưới đây:

Để làm chủ Excel trong công việc, không chỉ việc hiểu biết về các hàm quan trọng mà còn cần thành thạo việc sử dụng các công cụ đi kèm như Data validation, Conditional formatting, Pivot table… Các hàm như SUMIFS, COUNTIFS, SUMPRODUCT, INDEX + MATCH đều hỗ trợ tối ưu công việc. Bạn có thể học tất cả những kiến thức này thông qua khóa học.

Trương Thành Tài
0
    0
    Đơn hàng
    Đơn hàng trốngQuay lại Shop