Cách đếm số ô và tính tổng dựa trên màu nền trên file Excel

Như các bạn đã biết thì trong các bảng tính Excel, để làm nổi bật các giá trị, dữ liệu trong ô tính thì chúng ta thường sử dụng cách tô màu nền Background. Điều này giúp chúng ta dễ dàng làm việc và hạn chế nhầm lẫn giữa các ô tính hơn.

Tuy nhiên, đối với những bảng tính dài thì việc đếm các giá trị tiêu biểu vẫn sẽ gặp đôi chút khó khăn, nếu chúng ta thực hiện theo cách thủ công.

Chính vì thế, để hỗ trợ các bạn trong việc đếm ô và tính tổng các giá trị ô có màu Background tương ứng mà bạn đã thiết lập, thì trong bài viết ngày hôm nay mình sẽ chia sẻ với bạn thủ thuật sử dụng code VBA và hàm Colorfunction, để đếm ô và tính tổng các giá trị tương ứng trong ô dựa trên màu sắc.

Nghe đã thấy rất thú vị rồi phải không nào 😀 Vậy thì bắt đầu luôn nha các bạn!

Đọc thêm:

• Cách tô màu giá trị MIN, MAX trên bảng tính Excel, đơn giản!
• Hướng dẫn tô màu giá trị theo điều kiện trong Excel đơn giản
• Tự động tô màu hàng, cột khi click chuột vào ô trong Excel
• Cách tô màu xen kẽ các dòng, các cột trong Excel cực kỳ đơn giản

#1. Đếm số ô theo màu nền Background

+ Bước 1: Đầu tiên bạn mở file Excel của mình lên => Sau đó nhấn tổ hợp phím Alt + F11 để mở trình VBA.

+ Bước 2: Hộp thoại Microsoft Visual Basic for Application hiện lên. Tại đây bạn click menu Insert => rồi chọn Module.

+ Bước 3: Tiếp theo bạn Copy đoạn code sau….

Function ColorFunction(rColor As Range, rRange As Range, Optional SUM As Boolean)
Dim rCell As Range
Dim lCol As Long
Dim vResult
lCol = rColor.Interior.ColorIndex
If SUM = True Then
For Each rCell In rRange
If rCell.Interior.ColorIndex = lCol Then
vResult = WorksheetFunction.SUM(rCell, vResult)
End If
Next rCell
Else
For Each rCell In rRange
If rCell.Interior.ColorIndex = lCol Then
vResult = 1 + vResult
End If
Next rCell
End If
ColorFunction = vResult
End Function

… và Paste vào hộp thoại Module như hình bên dưới:

+ Bước 4: Bây giờ bạn quay trở lại file Excel của mình. Lúc này bạn nhập công thức vào vị trí hiển thị số ô chứa Background tương ứng.

Chúng ta sẽ sử dụng công thức sau:

=colorfunction(giá trị ô đầu tiên cần đếm;quét chọn toàn bộ bảng cần tính, sau đó nhấn F4 để cố định;FALSE)

Áp dụng vào ví dụ trên ta được công thức: =colorfunction(B2;$B$2;$E$7;FALSE)

+ Bước 5: Ta được kết quả như hình dưới. Cuối cùng là bạn Fill công thức xuống danh sách để áp dụng cho tất cả bảng tính.

Lưu ý: Các giá trị sẽ mặc định đếm màu lần lượt theo định dạng cột đầu tiên trên bảng tính. Do đó để xuất kết quả được chính xác bạn cần tạo màu tại ô biểu diễn theo thứ tự tương ứng.

Và đây là kết quả đếm số ô theo màu nền Background trên bảng tính.

#2. Tính tổng giá trị các ô có cùng màu nền Background

Để tính tổng các giá trị của ô có chung màu nền Background bạn cũng thực hiện công thức tương tự. Chỉ sửa kết quả xuất trong công thức là TRUE (giá trị thật), chứ không phải FALSE (giá trị sai).

Áp dụng vào ví dụ cụ thể: =colorfunction(B2;$B$2;$E$7;TRUE)

Rồi nhấn Enter để được kết quả như hình dưới.

Cuối cùng bạn Fill ô công thức xuống danh sách để áp dụng cho các ô còn lại thôi.

#3. Lời kết

Như vậy là mình vừa trình bày với các bạn rất chi tiết cách đếm số ô theo màu và tính tổng các giá trị trong ô dựa trên màu sắc của ô trong Excel rồi nha.

Đến đây thì bài hướng dẫn đếm số ô cùng màu và tính tổng dựa trên màu nền trong file Excel của mình cũng xin được tạm dừng. Hi vọng thủ thuật này sẽ hữu ích trong công việc của các bạn.

Chúc các bạn thành công !

CTV: Lương Trung – Blogchiasekienthuc.com