Bạn đang xem bài viết Hướng Dẫn Các Cách Đếm Ô Màu Trong Excel được cập nhật mới nhất trên website Hoisinhvienqnam.edu.vn. Hy vọng những thông tin mà chúng tôi đã chia sẻ là hữu ích với bạn. Nếu nội dung hay, ý nghĩa bạn hãy chia sẻ với bạn bè của mình và luôn theo dõi, ủng hộ chúng tôi để cập nhật những thông tin mới nhất.
SỬ DỤNG HÀM SUBTOTAL VÀ CÔNG CỤ LỌC THEO MÀU
Cách này được thực hiện dựa trên 2 nguyên tắc sau:
Hàm SUBTOTAL là một hàm được đánh giá rất linh hoạt trong Excel. Bạn có thể dùng hàm này trong việc tính tổng, đếm… đặc biệt hơn là cho phép bỏ qua các giá trị bị ẩn đi khi lọc bằng Filter hay sử dụng chức năng ẩn dòng (Hide).
Công cụ lọc (Filter) trong Excel thì có khả năng hỗ trợ bạn lọc theo các ô được tô màu.
Khi kết hợp 2 tính năng này, bạn sẽ thấy khi lọc theo màu, kết quả là sẽ còn lại các ô được tô màu theo đúng màu được lọc. Trong khi đó, những ô có màu khác sẽ được bỏ qua. Khi dùng hàm SUBTOTAL để đếm các ô còn lại sẽ cho bạn kết quả như ý muốn.
Bước 1: Dùng hàm SUBTOTAL để đếm.
Dùng hàm SUBTOTAL tại vị trí ô C11:
=SUBTOTAL(102, C2:C10)
Trong đó:
Tham số 102 là dùng cho hàm COUNT để đếm
Vùng ô cần đếm là vùng C2:C10
Kết quả thu được = 9 là 9 ô.
Bước 2: Lọc dữ liệu theo màu và xem kết quả
Tiếp theo đó, chúng ta sử dụng công cụ Filter (Auto Filter) để lọc dữ liệu theo màu sắc.
Chọn thẻ Data chọn Filter để áp dụng bộ lọc cho bảng
Tiếp tục chọn thực hiện lọc theo màu với chức năng Filter by Color:
Kết quả sau khi lọc theo màu như sau:
Các bạn có thể áp dụng tương tự bộ lọc này cho các màu khác để xem kết quả tự thay đổi cho màu đó.
SỬ DỤNG HÀM COUNTIF KẾT HỢP VỚI HÀM chúng tôi
Hàm chúng tôi là 1 hàm ẩn trong Excel, bình thường chúng ta sẽ không gọi hàm này lên được. Tuy nhiên có 1 cách để sử dụng đó là dùng hàm thông qua việc đặt tên vùng (Name Range).
Hàm chúng tôi để xác định màu trong ô:
Bước 1: Trong thẻ Formulas, chọn Define Name, thao tác đặt tên vùng như sau:
Trong thẻ Refers to: viết hàm =GET.CELL(38, Sheet2!$C2)
+ 38 là tham số xác định mã màu nền trong ô
+ Sheet2!$C2 là ô C2 tại Sheet2 (ô bắt đầu), với cố định cột C, không cố định dòng 2 (để hàm có thể sử dụng cho các dòng khác trong cột C)
Bước 2: Sau khi đặt tên, chúng ta sẽ đặt công thức tại cột D (cột phụ làm căn cứ xác định kết quả của hàm Get.Cell)
=MaMauNen
Đây là tên đã đặt ở bước 1. Filldown công thức từ ô D2 tới ô D10, ta sẽ có được kết quả mã màu nền được sử dụng trong các ô.
Lưu ý:
Hàm MaMauNen không nên dùng trực tiếp được mà phải dùng thông qua name range
Tham chiếu khi đặt tên là ô sẽ trả về kết quả mã màu
Hàm chỉ xác định màu nền được tô trực tiếp với Fill Color, không xác định được màu do chức năng Conditional formatting tạo ra.
Cuối cùng bạn muốn đếm ô theo màu nào chỉ cần dựa trên kết quả mã màu đã có để đếm (đếm theo kết quả số trong cột D với hàm COUNTIF, điều kiện là mã màu đã có)
Hướng Dẫn Cách Đếm Các Ký Tự Trong Một Ô (Hoặc Phạm Vi Ô) Trong Excel
Trong Excel mặc định có hàm LEN có thể sử dụng để đếm số ký tự trong một ô, nhưng bạn cũng có thể kết hợp với các hàm khác để tính tổng số ký tự trong một ô, phạm vi hoặc số lần một ký tự cụ thể xuất hiện trong một ô.
Đếm tất cả các ký tự trong một ô
Nếu bạn chỉ muốn biết tổng tất cả các ký tự trong một ô của Excel thì có thể sử dụng hàm LEN. Hàm LEN nhận một đối số, có thể là văn bản trong dấu ngoặc kép hoặc tham chiếu ô đến ô có văn bản.
= LEN (A2)
Một mình hàm LEN tách riêng trong Excel có thể không thực hiện được nhiều tính năng, nhưng khi kết hợp với các hàm khác thì có thể thực hiện được những tính năng ấn tượng như: Đếm số từ trong một ô hoặc tách họ và tên.
Lưu ý: Hàm LEN sẽ đếm tất cả các ký tự trong một ô, có thể là ký tự đặc biệt, số, dấu chấm câu và ký tự khoảng trắng (đầu, cuối và dấu cách kép giữa các từ).
Vì hàm LEN đếm mọi ký tự trong một ô nên đôi khi bạn có thể nhận được kết quả sai trong trường hợp có thêm khoảng trắng trong ô.
Để tránh việc sử dụng hàm LEN đếm các khoảng trắng thừa, trước tiên bạn có thể sử dụng hàm TRIM để loại bỏ bất kỳ khoảng trắng đầu, cuối và kép nào trong ô, sau đó sử dụng hàm LEN để tính số từ thực tế.
= LEN (TRIM (A2))
Đếm tất cả các ký tự trong một dải ô
Ý nghĩa của công thức trên sẽ là
Bạn cũng có thể sử dụng hàm LEN để đếm tổng số ký tự trong toàn bộ phạm vi.
Ví dụ bạn có một bảng dữ liệu, thay vì đếm số ký tự trong mỗi ô bạn muốn biết có bao nhiêu ký tự trong toàn bộ phạm vi.
= SUMPRODUCT (LEN (A2: A7)))
– Đầu tiên là hàm LEN trong công thức sẽ lấy toàn bộ phạm vi ô và đếm các ký tự trong mỗi ô. Lúc này kết quả của hàm LEN sẽ là {22; 21; 23; 23; 23; 31} và mỗi số này đại diện cho số ký tự trong ô.
Do đó khi bạn sử dụng hàm SUMPRODUCT kết hợp thì hàm LEN sẽ chỉ cần thêm tất cả các số này.
Có thể có bạn thắc mắc tại sao không dùng hàm SUM thay vì hàm SUMPRODUCT là dó đây là một mảng và hàm SUMPRODUCT có thể xử lý mảng nhưng hàm SUM thì không.
= SUM(LEN(A2: A7))
Đếm các ký tự cụ thể trong một ô
Như đã nói ở trên, tiện ích thực sự của hàm LEN là khi được sử dụng kết hợp với các hàm khác trong Excel. Nếu bạn muốn đếm các ký tự cụ thể trong một ô (đó có thể là một chữ cái, số, ký tự đặc biệt hoặc ký tự khoảng trắng), bạn có thể thực hiện điều đó bằng tổ hợp công thức.
Mặc dù không có công thức tích hợp nào để tính số từ nhưng bạn có thể đếm các ký tự khoảng trắng và sau đó sử dụng công thức đó để biết tổng số từ trong ô.
=LEN(A2)-LEN(SUBSTITUTE(A2,” “,””))+1
Công thức trên sẽ đếm tổng số ký tự khoảng trắng và sau đó thêm 1 vào số đó để có số từ và đây là cách công thức này hoạt động:
– Hàm SUBSTITUTE được sử dụng để thay thế tất cả các ký tự khoảng trắng bằng một khoảng trống. Sau đó, hàm LEN được sử dụng để đếm tổng số ký tự khi không có ký tự khoảng trắng.
– Kết quả của hàm LEN(SUBSTITUTE(A2,” “,””)) sau đó được trừ cho LEN(A2). Điều này sẽ cho tổng số ký tự khoảng trắng có trong ô.
– Số 1 được thêm vào công thức và tổng số từ sẽ nhiều hơn tổng số ký tự khoảng trắng (vì hai từ được phân tách bằng một ký tự).
Lưu ý: Trong trường hợp có bất kỳ dấu cách nào ở đầu, ở cuối hoặc kép, bạn sẽ nhận được kết quả sai. Trong trường hợp này, tốt nhất nên sử dụng hàm TRIM cùng với hàm LEN.
Bạn cũng có thể sử dụng logic tương tự để tìm một ký tự, từ hoặc cụm từ cụ thể trong một ô.
= LEN (B2) -LEN (SUBSTITUTE (B2, “A”, “”))
Công thức trên một lần nữa sử dụng cùng một logic – tìm độ dài của văn bản trong một ô có và không có ký tự mà bạn muốn đếm, sau đó lấy hiệu của hai ký tự này.
Đếm các ký tự cụ thể bằng công thức phân biệt chữ hoa chữ thường
Có một vấn đề với công thức được sử dụng để đếm các ký tự cụ thể trong một ô đó là hàm SUBSTITUTE không phân biệt được chữ hoa chữ thường. Điều này có nghĩa là chữ “A” không bằng “a”. Đây là lý do tại sao bạn nhận được kết quả sai trong ô C5 (kết quả đáng lẽ phải là 3).
=LEN(B2)-LEN(SUBSTITUTE(B2,”A”,””))+LEN(B2)-LEN(SUBSTITUTE(B2,”a”,””))
50+ khách hàng doanh nghiệp lớn trong nhiều lĩnh vực như: Vietinbank, Vietcombank, BIDV, VP Bank, TH True Milk, VNPT, FPT Software, Samsung SDIV, Ajinomoto Việt Nam, Messer,…
Thực hiện tương tự và thay thế ký tự A, a bằng các ký tự khác khi bạn muốn kiểm tra.
Đếm ký tự hoặc chữ số trước và sau số thập phân
Đếm ký tự hoặc chữ số trước số thập phân:
= LEN(INT(A2))
Đếm ký tự hoặc chữ số sau số thập phân:
=LEN(A2)-FIND(“.”,A2)
Lưu ý: Các công thức này chỉ dành cho các chữ số có nghĩa trong ô. Trong trường hợp bạn có các số 0 ở đầu hoặc ở cuối hoặc đã sử dụng định dạng số tùy chỉnh để hiển thị nhiều hơn hoặc ít hơn các số, các công thức trên sẽ vẫn cung cấp cho bạn các chữ số có nghĩa trước và sau số thập phân.
Hướng dẫn cách sắp xếp dữ liệu trong Excel theo màu chi tiết Hướng dẫn cách thao tác trình bày định dạng bảng dữ liệu trong Excel chuẩn nhất Hướng dẫn cách xử lý lỗi dữ liệu dạng số trong Excel nhanh chóng
Với sứ mệnh: ” Mang cơ hội phát triển kỹ năng, phát triển nghề nghiệp tới hàng triệu người “, đội ngũ phát triển đã và đang làm việc với những học viện, trung tâm đào tạo, các chuyên gia đầu ngành để nghiên cứu và xây dựng lên các chương trình đào tạo từ cơ bản đến chuyên sâu xung quanh các lĩnh vực: Tin học văn phòng, Phân tích dữ liệu, Thiết kế, Công nghệ thông tin, Kinh doanh, Marketing, Quản lý dự án…
Gitiho tự hào khi được đồng hành cùng:
Cách Đếm Các Ô Được Tô Màu Trong Google Sheets Bằng Power Tools Miễn Phí
Nếu bạn đánh dấu dữ liệu bằng màu trong Google Sheets, bạn có thể nhận ra rằng thiếu công thức để tổng hợp loại thông tin này. Đây là lý do tại sao chúng ta thêm một hàm tùy chỉnh để xem phông chữ và màu nền của các ô. Bạn có thể tìm thấy trong số các tính năng thông minh của Power Tools. Phiên bản Pro hiện tại miễn phí, vì vậy bạn có thể có tiện ích này chỉ với một cú nhấp chuột tại Google Sheets Store.
Cách đếm các ô được tô màu
Hãy thực hiện một trong những việc làm phổ biến nhất đối với các ô màu trong bảng tính: đếm các ô có cùng định dạng. Ở đây mình có một danh sách và mình muốn biết số lần học sinh thi đậu của mỗi bài kiểm tra, tức là đếm tất cả các ô màu xanh lá cây trong một cột:
1. Pattern cell: Chức năng này sẽ dùng phông chữ và màu nền của ô được chọn và tìm các ô khác giống nó. mình chọn B2 như ví dụ.
2. Source range: Vùng dữ liệu được phủ màu bạn muốn kiểm tra: Mình chọn các cột thể hiện kết quả kiểm tra.
3. Use function: Khi bạn muốn đếm các ô có định dạng cụ thể, tốt nhất nên chọn hàm COUNTA bởi vì chỉ có nó hoạt động với các giá trị không phải là số. Bằng cách này, chúng ta có thể chắc chắn rằng định dạng của dữ liệu trong các ô sẽ không ảnh hưởng kết quả.
4. Insert function after: Làm thế nào để áp dụng nó. Chúng ta có thể chọn đếm các ô trong mỗi hàng, cột, hoặc trong tất cả các ô đã chọn. Vì mình muốn đếm các ô màu xanh lá cây cho mỗi bài kiểm tra, mình chọn sau mỗi cột ( Each column). Nếu mình muốn một kết quả tổng quát cho cả ba bài kiểm tra, mình sẽ chọn toàn bộ vùng dữ liệu ( Entire range).
= COUNTA là một trong những tính năng của Google Sheets mà bạn chọn để sử dụng với các ô được tô màu.
#B6d6a8 là mã màu của ô được dùng cho thuật toán, tức là màu nền của ô được chọn.
#000000 là màu chữ của ô được chọn. Nếu bạn chỉ muốn dùng màu nền, bạn có thể xóa giá trị này. Sau đó công thức sẽ như sau: = COUNTA (valuesByColor (“# b6d7a8”, “”, B2: B40)).
D2: D40 là vùng dữ liệu có các ô được đánh dấu màu cần đếm. Nó là cột D trong ví dụ của mình.
Làm thế nào để cộng các ô theo màu?
Bạn A đang theo dõi các đơn hàng thiết bị cho lớp học. Bạn A ghi chú những vật được đặt hàng bằng màu vàng, vật đang chuyển tới bằng màu xanh dương, và thiết bị đã giao màu xanh lá cây:
1. Bạn A chọn C2 là ô mẫu để xác định định dạng của các ô mình muốn tính toán.
2. Bạn A chọn toàn bộ vùng dữ liệu để kiểm tra.
3. Sau đó chọn hàm SUM để cộng các số của ô màu xanh lá cây.
4. Để xem số lượng hàng đã giao của mỗi sản phẩm, áp dụng hàm cho mỗi hàng (each row)
Cập nhật kết quả
Bạn có nhớ rằng Google Sheets không có các chức năng làm việc với màu sắc không? Điều này có nghĩa là chúng không xem sự thay đổi định dạng ô như là một lý do để tính lại công thức của bạn. Tin vui là bạn có thể sử dụng hai giải pháp rất đơn giản sau.
1. Bạn có thể thay đổi bất kỳ giá trị nào trong vùng dữ liệu. Bạn đang đếm các ô màu xanh lá cây trong A1: C254. Bạn chỉ cần thêm ký tự vào bất kỳ ô nào trong vùng này, và sau đó xóa nó để có được kết quả cập nhật.
2. Nếu bạn có nhiều công thức valuesByColor trong trang tính, hãy nhấp vào tùy chọn Refresh ở bên cạnh Sum By Color trong Power Tools để cập nhật tất cả các công thức trong một lần.
Nguồn: Ablebits, dịch và biên tập bởi Hocexcel Online.
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
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
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:
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.
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ự.
=SUMPRODUCT({28;29;27;23;27;29;30})
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:
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ừ).
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.
=LEN(ô)-LEN(SUBSTITUTE(ô, ký 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:
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 ô.
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ỏ:
LEN(B2)
Đầu tiên, bạn đếm tổng chiều dài chuỗi của ô B2:
SUBSTITUTE(B2,”A”,””)
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(“”):
LEN(SUBSTITUTE(B2,”A”,””))
Sau đó, bạn đếm chiều dài chuỗi mà không có ký tự “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 ô.
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.
=LEN(B2)-LEN(SUBSTITUTE(UPPER(B2),”A”,””))
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(SUBSTITUTE(B2,”A”,””),”a”,””))
Cách khác đó là lồng ghép hàm SUBSTITUTE vào:
=LEN(B2)-LEN(SUBSTITUTE(SUBSTITUTE(B2, UPPER($D$1),””),LOWER($D$1),””))
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(UPPER(B2),UPPER($C$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ụ:
ĐẾ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.
=(LEN(B2)-LEN(SUBSTITUTE(B2,$C$1,””)))/LEN($C$1)
Công thức phân biệt dạng chữ:
=(LEN(B2)-LEN(SUBSTITUTE(LOWER(B2),LOWER($C$1),””)))/LEN($C$1)
Công thức không phân biệt dạng chữ:
LÀM THẾ NÀO ĐỂ ĐẾM CÁC KÝ TỰ ĐẶC BIỆT TRONG MỘT DẢI Ô
SUMPRODUCT(LEN(chuỗi)-LEN(SUBSTITUTE(chuỗi, ký tự,””)))
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:
=SUM(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:
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.
=SUMPRODUCT(LEN(B2:B8)-LEN(SUBSTITUTE(B2:B8,D1,””)))
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:
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 Ô.
=SUMPRODUCT(LEN(B2:B8)-LEN(SUBSTITUTE(UPPER(B2:B8),”A”,””)))
Sử dụng hàm UPPER và gõ ký tự in hoa:
=SUMPRODUCT(LEN(B2:B8)-LEN(SUBSTITUTE(SUBSTITUTE((B2:B8),”A”,””),”a”,””)))
Sử dụng hàm SUBSTITUTE trong hàm SUBSTITUTE:
=SUMPRODUCT(LEN(B2:B8)-LEN(SUBSTITUTE(SUBSTITUTE((B2:B8),UPPER($E$1),””), LOWER($E$1),””)))
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:
Để 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
Cập nhật thông tin chi tiết về Hướng Dẫn Các Cách Đếm Ô Màu Trong Excel trên website Hoisinhvienqnam.edu.vn. Hy vọng nội dung bài viết sẽ đáp ứng được nhu cầu của bạn, chúng tôi sẽ thường xuyên cập nhật mới nội dung để bạn nhận được thông tin nhanh chóng và chính xác nhất. Chúc bạn một ngày tốt lành!