Nội Dung Bài Viết

Bài viết này hướng dẫn bạn cách đếm số lượng ô và tính tổng giá trị trong các ô dựa trên màu sắc trong Excel. Phương pháp này áp dụng cho cả ô được tô màu thủ công và ô được định dạng theo điều kiện. Bạn cũng sẽ học cách lọc ô theo nhiều màu trong Excel 2010, 2013 và 2016.

Mở Đầu

Việc đếm và tính tổng các ô dựa trên màu sắc trong Excel có thể rất hữu ích trong việc phân tích và theo dõi dữ liệu. Ví dụ, bạn có thể sử dụng màu sắc để đánh dấu các trạng thái khác nhau của đơn hàng (đã giao, sắp giao, quá hạn) và sau đó dễ dàng tính tổng số lượng hoặc giá trị của các đơn hàng theo từng trạng thái. Bài viết này sẽ cung cấp cho bạn các kỹ thuật VBA để thực hiện điều này một cách hiệu quả.

Bảng dữ liệu với các ô được tô màu theo giá trị.

Hướng Dẫn Chi Tiết

Giả sử bạn có một bảng dữ liệu đơn hàng, cột “Giao Hàng” được tô màu theo trạng thái: “Sắp Giao” (cam), “Đã Giao” (xanh lá) và “Quá Hạn” (đỏ). Mục tiêu là đếm số ô theo từng màu và tính tổng giá trị trong các ô đó.

1. Mở VBA Editor:

Mở file Excel và nhấn Alt + F11 để mở Visual Basic Editor (VBE).

2. Thêm Module:

Trong VBE, click chuột phải vào tên bảng tính dưới mục Project-VBAProject ở bên phải màn hình, chọn Insert > Module.

Chọn Insert > Module để thêm hàm do người dùng định nghĩa.

3. Thêm Mã VBA:

Sao chép và dán đoạn mã VBA sau vào module vừa tạo:

Function GetCellColor(xlRange As Range)
    '... (Mã VBA được cung cấp trong bài gốc)
End Function

Function GetCellFontColor(xlRange As Range)
    '... (Mã VBA được cung cấp trong bài gốc)
End Function

Function CountCellsByColor(rData As Range, cellRefColor As Range) As Long
    '... (Mã VBA được cung cấp trong bài gốc)
End Function

Function SumCellsByColor(rData As Range, cellRefColor As Range)
    '... (Mã VBA được cung cấp trong bài gốc)
End Function

Function CountCellsByFontColor(rData As Range, cellRefColor As Range) As Long
    '... (Mã VBA được cung cấp trong bài gốc)
End Function

Function SumCellsByFontColor(rData As Range, cellRefColor As Range)
    '... (Mã VBA được cung cấp trong bài gốc)
End Function

Function WbkCountCellsByColor(cellRefColor As Range)
    '... (Mã VBA được cung cấp trong bài gốc)
End Function

Function WbkSumCellsByColor(cellRefColor As Range)
    '... (Mã VBA được cung cấp trong bài gốc)
End Function

Sub SumCountByConditionalFormat()
    '... (Mã VBA được cung cấp trong bài gốc)
End Sub

4. Lưu File:

Lưu file Excel dưới định dạng “Excel Macro-Enabled Workbook (.xlsm)”.

5. Sử Dụng Hàm:

Sử dụng hàm CountCellsByColor để đếm ô theo màu:

=CountCellsByColor(vùng_dữ_liệu, ô_mẫu_màu)

Ví dụ: =CountCellsByColor(F2:F14, A17) trong đó F2:F14 là vùng dữ liệu chứa các ô cần đếm, A17 là ô chứa màu mẫu (màu đỏ trong ví dụ).

Công thức đếm ô theo màu nền.

Tương tự, bạn có thể đếm ô màu vàng và xanh lá. Để tính tổng giá trị trong các ô màu, sử dụng hàm SumCellsByColor:

=SumCellsByColor(vùng_dữ_liệu, ô_mẫu_màu)

Ví dụ: =SumCellsByColor(D2:D14, A17)

Công thức tính tổng ô theo màu nền.

Bạn cũng có thể sử dụng CountCellsByFontColor và SumCellsByFontColor để đếm và tính tổng dựa trên màu chữ.

Công thức đếm và tính tổng ô theo màu chữ.

Lưu ý: Khi thay đổi màu ô thủ công sau khi đã áp dụng mã VBA, bạn cần nhấn F2 và Enter trên một ô bất kỳ để cập nhật kết quả.

Tính Tổng và Đếm Ô Màu Trên Toàn Bộ Bảng Tính

Sử dụng hàm WbkCountCellsByColor và WbkSumCellsByColor để đếm và tính tổng các ô cùng màu trên toàn bộ bảng tính. Ví dụ: =WbkSumCellsByColor(A1) sẽ tính tổng tất cả các ô có cùng màu với ô A1 trong toàn bộ bảng tính.

Tùy Chỉnh Hàm và Lấy Mã Màu

Các hàm GetCellFontColor và GetCellColor trả về mã màu chữ và mã màu nền của một ô.

Công thức lấy mã màu.

Đếm và Tính Tổng Ô Màu Định Dạng Có Điều Kiện

Sử dụng macro SumCountByConditionalFormat để xử lý các ô được tô màu bằng định dạng có điều kiện. Chọn vùng dữ liệu, giữ Ctrl và chọn một ô có màu mẫu, sau đó nhấn Alt + F8, chọn macro SumCountByConditionalFormat và nhấn Run.

Chạy macro để đếm và tính tổng ô được tô màu bằng định dạng có điều kiện.

Kết quả sẽ hiển thị số lượng ô (Count), tổng giá trị (Sum) và mã màu (Color).

Kết quả đếm, tổng và mã màu của các ô được tô màu bằng định dạng có điều kiện.

Kết Luận

VBA cung cấp một công cụ mạnh mẽ để xử lý dữ liệu dựa trên màu sắc trong Excel. Hy vọng bài viết này đã giúp bạn nắm vững cách sử dụng VBA để đếm và tính tổng ô theo màu, hỗ trợ việc phân tích dữ liệu hiệu quả hơn.