Làm thế nào để đếm ô có màu trong Excel?
Có nhiều cách để chúng ta có thể đếm ô dựa trên màu của ô trong excel.
- Phương pháp # 1 - Đếm ô có màu bằng phương pháp lọc với hàm tổng phụ
- Phương pháp # 2 - Đếm ô có màu bằng cách tạo hàm sử dụng mã VBA
Bây giờ chúng ta hãy thảo luận chi tiết từng phương pháp cùng với một ví dụ -
# 1 Sử dụng Phương pháp Bộ lọc với Hàm Tổng phụ
Như tôi đã nói, chúng tôi có nhiều phương pháp để đếm ô dựa trên màu của ô trong excel. Phương pháp này sử dụng chức năng Filter & Subtotal.
- Bước 1: Đầu tiên, chúng ta cần áp dụng một hàm tổng phụ, vì vậy khi kết thúc dữ liệu, hãy mở hàm SUBTOTAL trong excel.
- Bước 2: Khi chúng ta mở hàm SUBTOTAL, chúng ta sẽ được cung cấp một danh sách hàm dưới đây; vì chúng tôi đang đếm ô, hãy chọn hàm “ 2 - COUNT ”.
- Bước 3: Sau khi chọn phương pháp tổng phụ tiếp theo, chúng ta cần chọn một dải ô cần đếm.
- Bước 4: OK, bây giờ số lượng các ô này sẽ là 7.
- Bước 5: Đối với dữ liệu trên, cấu trúc áp dụng một tùy chọn bộ lọc.
- Bước 6: Nhấp vào danh sách bộ lọc thả xuống để chuyển đến “ Lọc theo màu”. Nó sẽ hiển thị tất cả các màu có sẵn để lọc.
- Bước 7: Chọn bất kỳ màu nào, và chúng ta sẽ có các hàng màu đó.
- Bước 8: Như bạn có thể thấy ở trên, hàm SUBTOTAL của chúng tôi chỉ hiển thị số lượng hàng đã lọc. Vì chỉ có hai hàng được đánh dấu bằng màu Cam, nên hàm SUBTOTAL chỉ hiển thị số lượng là ba; tương tự, chọn một màu khác và xem liệu nó có hoạt động hoàn hảo hay không.
Số lượng này là 2, vì vậy chúng tôi có một kết quả cập nhật.
# 2 Tạo Hàm bằng Mã hóa VBA
Như tôi đã nói với bạn, rất tiếc, chúng tôi không có bất kỳ hàm tích hợp nào có thể giúp chúng tôi đếm các ô dựa trên màu sắc trong excel. Chúng tôi đã nói rằng không có chức năng tích hợp sẵn, nhưng vẻ đẹp là chúng tôi có thể tạo chức năng của riêng mình bằng cách sử dụng mã hóa VBA.
Dưới đây là đoạn mã tôi đã viết để tạo một hàm đếm ô dựa trên màu sắc.
Mã:
Hàm ColorCount (ColorCell As Range, DataRange As Range) Dim Data_Range As Range Dim Cell_Color As Long Cell_Color = ColorCell.Interior.ColorIndex For Each Data_Range In DataRange If Data_Range.Interior.ColorIndex = Cell_Color Sau đó ColorCount = ColorCount + 1 End If Next Chức năng
Tất cả những gì bạn phải làm là sao chép mã trên và dán nó vào mô-đun VBA của bạn, như được hiển thị bên dưới.
Bây giờ quay lại trang tính nơi chúng ta có dữ liệu và tạo ba ô với màu sắc.
Đặt dấu bằng và mở chức năng mà chúng tôi đã tạo bằng cách sử dụng mã trên.
Đối số đầu tiên của hàm này là "chúng ta cần chọn ô màu mà chúng ta cần đếm", vì vậy trong trường hợp này, tôi sẽ chọn ô E2.
Đối số thứ hai là chúng ta cần đếm màu ô đã chọn trong phạm vi ô nào.
OK, đó là tất cả những gì chúng ta cần cho chức năng này. Đóng dấu ngoặc và nhấn phím enter để nhận số lượng màu ô đã chọn.
Tương tự, làm cho các ô màu khác. Chúng tôi nhận được kết quả sau đây.
Điều này được thực hiện bằng cách sử dụng VBA “ Hàm do người dùng xác định”, nó sẽ lặp qua một dải ô được cung cấp và đếm số ô có cùng màu với màu ô đã cho.
Sau đó, nó sẽ tính tổng tất cả các ô màu phù hợp và trả về kết quả là số lượng ô màu đó.
Như vậy, chúng ta có thể đếm các ô có màu trong excel. Ngoài ra còn có các phương pháp khác, nhưng chúng hơi phức tạp, vì vậy hãy thực hiện phương pháp dễ dàng khi bắt đầu học.
Những điều cần nhớ ở đây
- Hàm SUBTOTAL chỉ đếm các ô có thể nhìn thấy được, vì vậy khi bộ lọc được áp dụng, nó sẽ chỉ đếm những ô có màu đã lọc.
- Mã VBA dành cho hàm UDF vì không có hàm tích hợp sẵn nên chúng ta phải tạo riêng.
