Bài trả lời này học tập Excel Online sẽ lý giải cách đếm số cam kết tự vào Excel. Các bạn sẽ biết được công thức đếm toàn bộ ký tự vào một dải ô, tốt công thức dùng làm đếm chỉ những ký tự đặc biệt trong một ô tuyệt vài ô.
Trong bài xích hướng dẫn trước, công ty chúng tôi đã giới thiệu hàm LEN trong Excel, hàm này mang đến phép bọn họ đếm toàn bộ ký tự vào một ô.
Chỉ dùng hàm LEN thôi cũng rất tác dụng rồi, tuy vậy khi kết hợp với các hàm khác như hàm SUM, SUMPRODUCT, và SUBSTITUTE thì hàm LEN rất có thể giải được những bài toán phức hợp hơn khôn xiết nhiều. Liên tiếp trong bài xích hướng dẫn này, chúng ta sẽ để ý kỹ hơn những công thức đơn giản dễ dàng và cải thiện để đếm số cam kết tự trong Excel.
Xem nhanh
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ự vào một vài ba ô thì giải pháp mà bạn nghĩ ngay mang đến đó đó là đếm số ký kết tự trong mỗi ô, rồi cộng các con số kia lại:
=LEN(A2)+(A3)+(A4)
Hay
=SUM(LEN(A2),LEN(A3),LEN(A4))
Các bí quyết trên có lẽ sẽ tác dụng trong một dải ô nhỏ. Để đếm tất cả ký tự vào một dải ô phệ hơn, bọn họ nên nghĩ về ra một cách làm khác gọn gàng hơn, ví dụ: hàm SUMPRODUCT, hàm này đang cộng những mảng lại với nhau rồi trả về tổng của các công dụng đó.
Đây là cách làm chung trong Excel nhằm đế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ư vậy này:
=SUMPRODUCT(LEN(A1:A7))
Cách khác nhằm đếm số ký tự trong một dải ô đó là thực hiện hàm LEN kết hợp với hàm SUM:
=SUM(LEN(A1:A7))
Không giống như hàm SUMPRODUCT, hàm SUM không tính những mảng một giải pháp mặc định, và bạn phải nhấn Ctrl+Shift+Enter để đưa nó về phương pháp mảng.
Khi được diễn giải trong hình ảnh chụp màn hình hiển thị dưới đây, hàm SUM trả về cùng một kết quả số ký kết tự:
CÔNG THỨC ĐẾM SỐ KÝ TỰ vào MỘT DẢI Ô HOẠT ĐỘNG NHƯ THẾ NÀO
Đây là trong số những công thức dễ dàng nhất để đếm số ký tự vào Excel. Hàm LEN tính toán chiều nhiều năm chuỗi cho mỗi ô vào một dải ô xác định rồi trả về một hàng số. Sau đó, hàm SUMPRODUCT tuyệt SUM cộng các con số này cùng với nhau và hiện công dụng là tổng số những ký tự.
Trong các ví dụ trên, một dãy bao gồm bảy con sốchính là chiều nhiều năm chuỗi trong những ô tự A1 mang lại A7 khi được cộng:
=SUMPRODUCT(28;29;27;23;27;29;30)
Lưu ý. Hãy để ý rằng hàm LEN vào Excel cùng tất cả những ký tự trong mỗi ô một giải pháp tuyệt đối, tất cả chữ cái, chữ số, vết câu, cam kết tự sệt biệt, và toàn bộ các khoảng không (thụt đầu dòng, thụt cuối chiếc và khoảng cách giữa các từ).LÀM THẾ NÀO ĐỂ ĐẾM CÁC KÝ TỰ ĐẶC BIỆT trong MỘT Ô
Thỉnh thoảng, rứa vì phải đếm toàn bộ ký tự vào một ô, chúng ta có thể chỉ rất cần phải đếm sự xuất hiện thêm của một cam kết tự, chữ số, hay biểu tượng đặc biệt.
Để đếm tần suất lộ diện của một ký kết tự xác định trong một ô, hãy thực hiện hàm LEN kết hợp với hàm SUBSTITUTE:
=LEN(ô)-LEN(SUBSTITUTE(ô, ký kết tự,””))
Để nắm rõ ví dụ hơn, hãy nhìn qua ví dụ sau đây nhé.
Gỉả sử, bạn lưu duy trì một cơ sở dữ liệu của không ít món mặt hàng được giao, chỗ mà mỗi các loại hàng bao gồm ký hiệu nhấn dạng riêng rẽ biệt. Cùng mỗi ô tất cả một vài ba món hàng giải pháp nhau bởi dấu phẩy, khoảng chừng trống, hay bất kể dấu phân làn nào. Nhiệm vụ của người tiêu dùng là hãy đếm số lần lộ diện của cam kết hiệu dấn dạng đơn nhất được khẳng định trong từng ô.
Gỉả sử rằng danh sách các món hàng nằm ở vị trí cột B (bắt đầu trường đoản cú B2) và chúng ta sẽ đếm số lần lộ diệ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 vào EXCEL HOẠT ĐỘNG NHƯ THẾ NÀO
Để phát âm quy lý lẽ của phương pháp này, hãy phân tách nó ra thành từng phần nhỏ:
Đầu tiên, chúng ta đếm tổng chiều lâu năm chuỗi của ô B2:
LEN(B2)
Sau đó, chúng ta dùng hàm SUBSTITUTE để loại trừ số lần mở ra của “A” vào B2 bằng phương pháp thay cụ nó bằng một chuỗi rỗng(“”):
SUBSTITUTE(B2,”A”,””)
Sau đó, bạn đếm chiều nhiều năm chuỗi mà không có ký tự “A”:
LEN(SUBSTITUTE(B2,”A”,””))
Cuối cùng, các bạn lấy tổng chiều nhiều năm chuỗi trừ mang lại chiều dài của chuỗi không tồn tại ký tự “A”.
Do đó, bạn tính được số cam kết tự “được các loại bỏ”, tác dụng này cũng chính là số lần mở ra của ký kết tự kia trong một ô.
Thay vì xác minh ký tự bạn có nhu cầu đếm trong công thức, chúng ta có thể gõ nó vào trong 1 vài ô, rồi tham chiếu ô đó trong công thức. Bằng phương pháp này, người tiêu dùng của bạn sẽ có khả năng đếm số lần xuất hiện của bất kể ký từ nào bọn họ nhập vào ô đó mà không phá bí quyết của bạn:
Lưu ý. Hàm SUBSTITUTE của Excel là một hàm riêng biệt dạng chữ, và chính vì như vậy các cách làm trên đều biệt lập dạng chữ theo. Ví dụ, trong hình ảnh chụp screen ở trên, ô B3 tất 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 sản phẩm công nghệ c chỉ đếm các ký từ bỏ viết hoa bởi vì chúng ta cung ứng “A” mang đến 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 minh bạch dạng chữ, hãy gắn ghép hàm UPPER vào hàm SUBSTITUTE để đổi cam kết tự xác định sang dạng viết hoa trước lúc chạy phép thế. Và, hãy có lẽ bạn sẽ nhập ký kết tự viết hoa vào công thức.
Ví dụ, nhằm đếm những ký trường đoản cú “A” cùng “a” trong ô B2, hãy dùng bí quyết này:
=LEN(B2)-LEN(SUBSTITUTE(UPPER(B2),”A”,””))
Cách khác chính 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 hình ảnh chụp màn hình bên dưới, cả hai cách làm đều đếm sự xuất hiện thêm của ký tự xác minh dưới dạng viết hoa cùng viết thường xuyên một biện pháp hoàn hảo:
Trong vài ngôi trường hợp, bạn cũng có thể cần đề nghị đếm những ký tự khác biệt trong một bảng, nhưng chúng ta lại không thích 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 tích hợp hàm SUBSTITUE này vào hàm SUBSTITUTE khác, rồi gõ cam kết tự bạn muốn đếm vào một trong những ô nào đó (trường thích hợp này là ô D1), với đổi quý hiếm của ô này lịch sự dạng viết hoa cùng viết thường xuyên 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 biến hóa ô nguồn với ô chứa hoặc cam kết tự viết thường hoặc cam kết tự viết hoa cùng với nhau. Ví dụ:
=LEN(B2)_LEN(SUBSTITUTE(UPPER(B2),UPPER($C$1),””))
Điểm cộng của bí quyết này đó là dù ta nhập ký kết tự viết thường tuyệt viết hoa vào ô được tham chiếu thì công thức đếm ký tự không phân minh dạng chữ của bạn đều đang trả về công dụng đếm thiết yếu xác:
ĐẾM SỰ XUẤT HIỆN CỦA MỘT KÝ TỰ NHẤT ĐỊNH tốt CHUỖI bé TRONG MỘT Ô
Nếu bạn có nhu cầu đếm số lần xuất hiện của một sự kết hợp cụ thể các cam kết tự (ví dụ, một chuỗi ký kết tự độc nhất vô nhị định, giỏi chuỗi con) vào một ô xác định, ví dụ, “A2” hay “SS” thì nên chia số cam kết tự được trả về bởi bí quyết trên đến chiều nhiều năm 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 rành mạch dạng chữ:
=(LEN(B2)-LEN(SUBSTITUTE(LOWER(B2),LOWER($C$1),””)))/LEN($C$1)
B2 là ô đựng cả một chuỗi cam kết tự, với 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ì chúng ta đã biết phương pháp trong Excel dùng làm đếm các ký tự trong một ô, chúng ta cũng có thể muốn nâng cao nó thêm để tìm ra số lần lộ diện của một ký tự nhất định trong một dải ô. Về bài toán này, chúng ta sẽ áp dụng hàm LEN trong Excel nhằm đếm một ký tự đặc biệt quan trọng trong một ô, điều này đã được nói tới trong lấy ví dụ trên, rồi đặt nó vào hàm SUMPRODUCT, hàm này có thể giúp ta xử lý những mảng:
SUMPRODUCT(LEN(chuỗi)-LEN(SUBSTITUTE(chuỗi, cam kết tự,””)))
Trong ví dụ như này, công thức tất 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 làm đếm các ký tự trong dải ô Excel:
=SUM(LEN(B2:B8)-LEN(SUBSTITUTE(B2:B8,”A”,””)))
So với cách làm đầu tiên, điểm khác hoàn toàn dễ nhận ra nhất đó chính là việc dùng hàm SUM thay do hàm SUMPRODUCT. Điểm khác biệt khác đó chính là bạn nên nhấn Ctrl+Shift+Enter cũng chính vì không y như hàm SUMPRODUCT, là hàm được tạo ra để xử lý các mảng, hàm SUM rất có thể xử lý những mảng chỉ khi được sử dụng trong công thức mảng.
Nếu không muốn ghi trong phương pháp một cách thắt chặt và cố định trong công thức, thì tất nhiên chúng ta có thể nhập nó vào trong 1 ô làm sao đó, lấy một ví dụ là ô D1, rồi tham chiếu ô đó vào bí quyết đế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ế toàn bộ sự mở ra của ký tự khẳng định (trong lấy ví dụ này là ký kết tự “A”) bởi một chuỗi cam kết tự rỗng (“”).
Sau đó, bọn họ cung cung cấp chuỗi cam kết tự được trả về do hàm SUBSTITUTE mang lại hàm LEN trong Excel nhằm nó tính chiều nhiều năm chuỗi không có ký từ bỏ “A”. Rồi sau đó, chúng ta lấy tổng chiều lâu năm chuỗi ký kết tự trừ mang đến chuỗi không có ký từ bỏ “A” đó. Tác dụng của các phép tính này chính là một mảng những số cam kết tự, trong đó mỗi ô cho một trong những 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ố cam kết 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ẽ, chúng ta đã biết hàm SUBSTITUTE là hàm rõ ràng dạng chữ, hàm này cũng khá được dùng cho công thức đếm ký tự có phân biệt dạng chữ.
Để tạo nên công thức không minh bạch dạng chữ nữa, hãy có tác dụng theo quá trình đã được diễn giải trong lấy 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à, chúng ta có thể sử dụng giữa những công thức tiếp sau đây để đếm số ký tự đặc trưng trong một dải ô nhưng không minh bạch 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 vào 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 cam kết tự viết hoa hoặc ký tự viết thường vào một ô nào đó, rồi tham chiếu ô kia 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 dưới diễn giải cách thực hiện 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 những hàm cơ mà còn đề xuất sử dụng tốt cả các công cố của Excel. Gần như hàm nâng cao giúp áp dụng xuất sắc vào quá trình như SUMIFS, COUNTIFS, SUMPRODUCT, INDEX + MATCH… Những phép tắc thường sử dụng là Data validation, Conditional formatting, Pivot table…