Việc sử dụng màu sắc để phân loại dữ liệu trong Excel rất phổ biến. Tuy nhiên, các hàm đếm thông thường như COUNT, COUNTA, COUNTIF lại không thể đếm các ô dựa trên màu sắc. Bài viết này sẽ hướng dẫn bạn 3 cách đơn giản để đếm số ô được tô màu trong Excel, giúp bạn xử lý dữ liệu hiệu quả hơn.
Đề bài đếm theo màu
1. Kết Hợp Hàm SUBTOTAL và Lọc Màu
Phương pháp này tận dụng hai tính năng mạnh mẽ của Excel:
- Hàm SUBTOTAL: Linh hoạt trong việc tính tổng, đếm, và đặc biệt là bỏ qua các giá trị bị ẩn khi lọc.
- Lọc Màu (Filter by Color): Cho phép lọc dữ liệu dựa trên màu sắc của ô.
Khi kết hợp, ta lọc theo màu cần đếm, sau đó dùng SUBTOTAL để đếm các ô hiển thị (chính là các ô có màu được chọn).
Các bước thực hiện:
- Sử dụng hàm SUBTOTAL: Tại ô C11, nhập công thức
=SUBTOTAL(102, C2:C10). (102 là mã cho hàm COUNT, C2:C10 là vùng dữ liệu).
Đếm bằng hàm SUBTOTAL
- Lọc dữ liệu theo màu:
- Trong tab “Data”, chọn “Filter” để bật bộ lọc.
- Tại cột cần lọc, click vào mũi tên thả xuống, chọn “Filter by Color”, sau đó chọn màu muốn đếm.
Lọc theo màu
Kết quả lọc theo màu
Áp dụng bộ lọc
Minh họa quá trình lọc và đếm
2. Sử Dụng Hàm COUNTIF và GET.CELL
GET.CELL là hàm ẩn trong Excel, được sử dụng thông qua đặt tên vùng (Name Range). Hàm này giúp xác định màu của ô.
Các bước thực hiện:
- Đặt tên vùng cho hàm GET.CELL:
- Trong tab “Formulas”, chọn “Define Name”.
- Nhập tên vùng, ví dụ “MaMauNen”.
- Trong ô “Refers to”, nhập
=GET.CELL(38,Sheet1!$C2)(38 là mã cho màu nền, Sheet1!$C2 là ô bắt đầu, cố định cột C).
GET.CELL xác định mã màu
- Tạo cột phụ:
- Tại ô D2, nhập công thức
=MaMauNenvà kéo xuống. Cột này sẽ hiển thị mã màu của từng ô trong cột C.
- Tại ô D2, nhập công thức
Công thức xác định mã màu nền
- Sử dụng COUNTIF: Đếm số ô có mã màu cụ thể bằng hàm
COUNTIF. Ví dụ, để đếm số ô có mã màu là 6, sử dụng công thức=COUNTIF(D2:D10,6).
Lưu ý: GET.CELL chỉ xác định được màu tô trực tiếp, không áp dụng cho màu từ Conditional Formatting.
3. Sử Dụng VBA
Bạn có thể tạo hàm VBA tùy chỉnh để đếm ô theo màu. Đây là phương pháp phức tạp hơn, phù hợp với người dùng có kinh nghiệm VBA.
Kết Luận
Bài viết đã giới thiệu 3 cách đếm số ô được tô màu trong Excel, từ đơn giản đến phức tạp. Hy vọng những thủ thuật này sẽ giúp bạn nâng cao hiệu suất làm việc với Excel. Việc thành thạo Excel sẽ giúp bạn xử lý dữ liệu hiệu quả và chuyên nghiệp hơn.
Discussion about this post