Sử dụng màu sắc trong bảng tính Excel sẽ giúp dữ liệu của bạn sinh động, dễ phân biệt và tìm kiếm hơn. Vậy làm thế nào để đếm ô theo màu một cách nhanh chóng? Bài viết sau đây sẽ mách bạn 3 cách thực hiện tính năng này, chắc chắn sẽ hữu ích cho công việc của bạn đấy.

Bạn đang xem: Đếm các ô được tô màu trong excel

1. Kết hợp hàm SUBTOTAL và công cụ lọc theo màu

Cách này dựa trên 2 tính năng cực kỳ tiện lợi của Excel:

Hàm SUBTOTAL là hàm ứng dụng linh hoạt trong nhiều trường hợp khác nhau: tính tổng, trung bình cộng, đếm số ô,… Ở đây, bạn sử dụng hàm SUBTOTAL với chức năng đếm ô theo màu để phân biệt các ô với nhau.Công cụ lọc màu (Filter) trong Excel: giúp lọc các ô được tô theo màu, bạn có thể tùy chọn một hay nhiều màu sắc mà mình muốn hiển thị.

2. Dùng hàm SUBTOTAL để đếm ô theo màu.

Bước 1: Nhập công thức =SUBTOTAL(2;C2:C11)

Trong đó:

2: tương đương hàm COUNT, dùng để đếm các ô có màu trong vùng dữ liệu.C2:C11: vùng ô cần đếm.
*

Bước 2: Nhấn Enter và nhận kết quả.

*

3. Dùng bộ lọc Filter (Autofilter) để lọc các ô theo màu tương ứng.

Bước 1: Tại thẻ Data trên thanh công cụ, nhấn chọn Filter để áp dụng bộ lọc màu cho bảng dữ liệu.


*

*

*

4. Kết hợp hàm COUNTIF với hàm GET.CELL

Dùng hàm GET.CELL để xác định mã màu của ô.

Bước 1: Nhấp chuột vào ô E2. Sau đó, nhấn chọn Define Name trong tab Formulas


*

Bước 2: Hộp thoại New name xuất hiện.

*
Mục Name: nhập tên bất kỳ. Ở đây, mình nhập là KQScope: Workbook
Refers to: nhập công thức =GET.CELL(38;Sheet1!$E2)

Trong đó:

38: tham số xác định màu nền trong ô.Sheet1!$E2: là chỉ vị trí ô bắt đầu xác định màu. Ở đây, bạn chỉ 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 ở cột C)

Bước 3: Viết công thức =KQ vào ô F2 bên cạnh ô E2, nhấn OK để hiển thị kết quả. Sau đó, copy công thức cho các dòng còn lại trong cột F.


Dùng hàm COUNTIFS để đếm ô với điều kiện mã màu.

Nhập công thức =COUNTIFS(F2:F11;”=20″), rồi nhấn Enter để hiển kết quả.


Làm tương tự với các mã màu còn lại.

Lời kết

Như vậy, bạn có thể đếm ô theo màu bằng 2 cách:

Kết hợp hàm SUBTOTAL và công cụ lọc màu
Kết hợp hàm GET CELL và COUNTIFS

Hy vọng bạn đã nhận được những kiến thức hữu ích cho công việc của mình. Cám ơn bạn đã xem bài viết.

Xem thêm:

Việc sử dụng màu sắc để phân biệt các dữ liệu được nhiều người ưa thích sử dụng. Nhưng bạn không thể dùng hàm đếm thông thường như COUNT, COUNTA, COUNTIF để đếm những ô dựa theo màu sắc được. Trong bài viết này, Học Excel Online sẽ hướng dẫn các bạn 3 cách để có thể đếm số ô được tô màu trong Excel

Sử dụng hàm SUBTOTAL và công cụ lọc theo màu

Cách này dựa trên 2 nguyên tắc sau: Khi kết hợp 2 tính năng này, ta sẽ thấy Khi lọc theo màu, kết quả sẽ còn lại những ô được tô màu theo đúng màu được lọc. Những ô có màu khác sẽ được bỏ qua. Khi đó sử dụng hàm SUBTOTAL để đếm các ô còn lại sẽ cho bạn kết quả như ý muốn.Cách làm cụ thể như sau:Giả sử chúng ta có bảng dữ liệu được tô màu theo các nội dung như sau:
*
*
*
*
*
*
*
Tại ô D2 nhập công thức

=Ma
Mau
Nen

Đây chính là tên đã đặt ở bước 1. Filldown công thức từ ô D2 tới ô D10, ta sẽ có kết quả mã màu nền được sử dụng trong các ô.Lưu ý: Hàm Ma
Mau
Nen không dùng trực tiếp được mà phải dùng thông qua name range Tham chiếu khi đặt tên chính là ô sẽ trả về kết quả mã màu Hàm chỉ xác định được 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 chúng ta 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ó)

Sử dụng hàm tự tạo trong VBA

Nội dung này các bạn có thể tìm hiểu chi tiết tại bài viết dưới đây:

Hướng dẫn cách đếm và tính tổng dựa theo màu sắc của ô tính bằng VBA

Kết luận

Qua bài viết này, chúng ta có thể thấy một vấn đề tưởng khó thực hiện lại có thể làm được với nhiều cách khác nhau. Thật bất ngờ phải không nào. Việc thành thạo Excel sẽ giúp bạn luôn chủ động trong mọi tình huống, tăng khả năng giải quyết những công việc khó và nâng cao hiệu quả công việc.Nhằm giúp bạn tiết kiện thời gian, công sức và chi phí cho việc học Excel, chúng tôi xin giới thiệu với bạn khóa học EX101 – Excel từ cơ bản tới chuyên gia dành cho người đi làm. Giảng viên của khóa học này là thầy Nguyễn Đức Thanh – giảng viên về tin học văn phòng uy tín nhất tại Việt Nam. Với nội dung chi tiết, đầy đủ, dễ hiểu, bạn hoàn toàn có thể yên tâm về kiến thức sẽ có được khi tham gia học khóa học này. Ngoài ra bạn còn có thể học cùng với hàng nghìn học viên khác trong khóa học này, tham khảo những ví dụ thực tế thông qua việc thảo luận cùng giảng viên. Những điều này rất bổ ích, giúp bạn vừa học, vừa vận dụng được kiến thức vào trong các tình huống thực tế.Hãy nhanh tay đăng ký để nhận được ưu đãi từ Học Excel Online ngay nhé!