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ọcExcel Online sẽ hướng dẫn các bạn 3 cáchđể có thểđếmđược cácô theo màu sắc.
Bạn đang xem: Đếm các ô đượ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àutheođú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ạisẽ 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đểđếmVùngô cầnđếm là vùng C2:C10Kế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ảngCó 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.
Xem thêm: Vẽ Báo Tường Đẹp Đơn Giản Bằng 4 Cách Vẽ Hoa Dây Trang Trí Báo Tường Đẹp Nhất
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 trongExcel,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: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 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.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ấymộ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ọcExcel, chúng tôi xin giới thiệu với bạn khóa họcEX101 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ế.