Hướng dẫn dùng hàm màu sắc trong excel 11

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.

Bạn đang xem: Hàm màu sắc trong excel

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 để đếmVùng ô cần đếm là vùng C2:C10

Tham số 102 là sử dụng cho hàm COUNT để đếmVù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

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, )

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:

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)

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 rangeTham chiếu khi đặt tên chính là ô sẽ trả về kết quả mã màuHà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.

Hàm MaMauNen không dùng trực tiếp được mà phải dùng thông qua name rangeTham chiếu khi đặt tên chính là ô sẽ trả về kết quả mã màuHàm chỉ xác định được màu nền được tô trực tiếp với Fill Color,

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.

Xem thêm: Trẻ Sơ Sinh Bú Bao Nhiêu Ml Sữa Một Lần, Lượng Ăn Của Trẻ Sơ Sinh Bao Nhiêu Là Phù Hợp

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ế.