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:

• Hàm SUBTOTAL là một hàm rất linh hoạt trong Excel. Bạn có thể sử dụng trong việc tính tổng, đếm… đặc biệt hơn là nó cho phép bỏ qua các giá trị bị ẩn đi khi lọc bằng Filter hoặc sử dụng chức năng ẩn dòng (Hide).
• Công cụ lọc (Filter) trong Excel 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, 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:

Với mỗi 1 chi nhánh sẽ được tô theo 1 màu riêng. Khi muốn đếm theo màu, chúng ta sẽ thực hiện lần lượt 2 bước như sau:

Bước 1: Dùng hàm SUBTOTAL để đếm.

Sử dụng hàm SUBTOTAL tại vị trí ô C11:

=SUBTOTAL(102, C2:C10)

Trong đó:

• Tham số 102 là sử 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.

• Trong thẻ Data chọn Filter để áp dụng bộ lọc cho bảng

• 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:

Có thể thấy sau khi lọc theo màu tương ứng với chi nhánh Hà Nội, kết quả còn lại 3 dòng nội dung, và hàm SUBTOTAL trả về đúng kết quả bằng 3.

Các bạn có thể áp dụng tương tự bộ lọc cho 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 GET.CELL

Hàm Get.Cell là 1 hàm ẩn trong Excel, bình thường chúng ta không gọi hàm này lên được. Tuy nhiên có 1 cách để sử dụng đó là dùng thông qua việc đặt tên vùng (Name Range).

Hàm Get.Cell để xác định màu trong ô:

=Get.Cell(38, <ô tham chiếu>)

Trong ví dụ trên, để xác định xem các ô trong cột C được tô màu gì, chúng ta làm như sau:

• Bước 1: Trong thẻ Formulas, chọn Define Name, thực hiện thao tác đặt tên vùng như sau:

Tên được sử dụng: MaMauNen (Mã màu nền – viết không dấu)

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), trong đó 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)

Tại ô D2 nhập công thức

=MaMauNen

Đâ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 MaMauNen 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é!