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

The original table with cells colored based on value.

Trong bài viết này, chúng ta cùng đếm các ô tính bằng màu sắc và tính tổng các ô được tô màu. Những giải pháp này hiệu quả đối với cả ô tính tô màu thủ công và ô tính được định dạng theo điều kiện. Đồng thời, bạn cũng biết cách lọc các ô tính bằng nhiều màu trong Excel 2010, 2013, 2016.

Cách đếm và tính tổng bằng màu trong trang tính Excel

Giả sử bạn có bảng liệt kê danh sách đơn đặc hàng của công ty, trong đó cột Delivery được tô màu dựa trên giá trị của chúng: “Due in X Days” được tô màu cam, “Delivered” được tô màu xanh lá và “Past Due” được tô màu đỏ.

The original table with cells colored based on value.

Những gì chúng ta muốn là tự động tính các ô theo màu sắc, tức là tính số ô màu đỏ, xanh lá cây và cam trong bảng tính. Hãy tiếp tục với 5 bước dưới đây và bạn sẽ biết số ô mỗi màu và tổng của chúng.

  1. Mở bảng tính Excel và nhấn Alt + F11 để mở Visual Basic Editor (VBE).
  2. Nhấp chuột phải vào tên bảng tính dưới Project-VBAProject ở phía tay phải của màn hình, sau đó chọn Insert > Module từ danh sách tùy chọn.

Click Insert > Module to add a new user-defined function to your worksheet.

  1. Thêm đoạn mã sau vào bảng tính.
Function GetCellColor(xlRange As Range)

Dim indRow, indColumn As Long

Dim arResults()

Application.Volatile

If xlRange Is Nothing Then

Set xlRange = Application.ThisCell

End If

If xlRange.Count > 1 Then

ReDim arResults(1 To xlRange.Rows.Count, 1 To xlRange.Columns.Count)

For indRow = 1 To xlRange.Rows.Count

For indColumn = 1 To xlRange.Columns.Count

arResults(indRow, indColumn) = xlRange(indRow, indColumn).Interior.Color

Next

Next

GetCellColor = arResults

Else

GetCellColor = xlRange.Interior.Color

End If

End Function

Function GetCellFontColor(xlRange As Range)

Dim indRow, indColumn As Long

Dim arResults()

Application.Volatile

If xlRange Is Nothing Then

Set xlRange = Application.ThisCell

End If

If xlRange.Count > 1 Then

ReDim arResults(1 To xlRange.Rows.Count, 1 To xlRange.Columns.Count)

For indRow = 1 To xlRange.Rows.Count

For indColumn = 1 To xlRange.Columns.Count

arResults(indRow, indColumn) = xlRange(indRow, indColumn).Font.Color

Next

Next

GetCellFontColor = arResults

Else

GetCellFontColor = xlRange.Font.Color

End If

End Function

Function CountCellsByColor(rData As Range, cellRefColor As Range) As Long

Dim indRefColor As Long

Dim cellCurrent As Range

Dim cntRes As Long

Application.Volatile

cntRes = 0

indRefColor = cellRefColor.Cells(1, 1).Interior.Color

For Each cellCurrent In rData

If indRefColor = cellCurrent.Interior.Color Then

cntRes = cntRes + 1

End If

Next cellCurrent

CountCellsByColor = cntRes

End Function

Function SumCellsByColor(rData As Range, cellRefColor As Range)

Dim indRefColor As Long

Dim cellCurrent As Range

Dim sumRes

Application.Volatile

sumRes = 0

indRefColor = cellRefColor.Cells(1, 1).Interior.Color

For Each cellCurrent In rData

If indRefColor = cellCurrent.Interior.Color Then

sumRes = WorksheetFunction.Sum(cellCurrent, sumRes)

End If

Next cellCurrent

SumCellsByColor = sumRes

End Function

Function CountCellsByFontColor(rData As Range, cellRefColor As Range) As Long

Dim indRefColor As Long

Dim cellCurrent As Range

Dim cntRes As Long

Application.Volatile

cntRes = 0

indRefColor = cellRefColor.Cells(1, 1).Font.Color

For Each cellCurrent In rData

If indRefColor = cellCurrent.Font.Color Then

cntRes = cntRes + 1

End If

Next cellCurrent

CountCellsByFontColor = cntRes

End Function

Function SumCellsByFontColor(rData As Range, cellRefColor As Range)

Dim indRefColor As Long

Dim cellCurrent As Range

Dim sumRes

Application.Volatile

sumRes = 0

indRefColor = cellRefColor.Cells(1, 1).Font.Color

For Each cellCurrent In rData

If indRefColor = cellCurrent.Font.Color Then

sumRes = WorksheetFunction.Sum(cellCurrent, sumRes)

End If

Next cellCurrent

SumCellsByFontColor = sumRes

End Function

  1. Lưu bảng tính thành “Excel Macro-Enabled Workbook (.xlsm)”.
  2. Như vậy, tất cả những thao tác lặt vặt đã thực hiện xong, chỉ cần thêm hàm được chỉ định, chọn ô tính muốn hiển thị công thức và gõ công thức CountCellsByColor.

=CountCellsByColor(range, color code)

Trong ví dụ này, chúng ta sử dụng công thức = CountCellsByColor (F2:F14, A17) trong đó F2: F14 là dãy chứa các ô có màu bạn muốn đếm và A17 là ô có màu nền nhất định, trong ví dụ đó là các ô màu đỏ.

Tương tự, bạn viết công thức để đếm các ô màu khác, như màu vàng và màu xanh lá cây trong bảng của chúng tôi.

The formula to count cells by background color

Nếu bạn có dữ liệu số trong ô màu, như cột Qty, bạn có thể thêm giá trị dựa vào màu bằng cách sử dụng hàm SumCellsByColor

=SumCellsByColor(range, color code) The formula to sum cells by background color

Như đã trình bày, chúng tôi sử dụng công thức =SumCellsByColor (D2:D14, A17) trong đó D2: D14 là dãy và A17 là ô với một mẫu màu.

Tương tự, bạn có thể đếm ô và tính tổng các ô tính bằng màu thông qua hàm CountCellsByFontColor SumCellsByFontColor.

The formulas to count and sum cells by font color

Chú ý. Sau khi áp dụng mã VBA, bạn sẽ cần thêm màu cho một số ô khác theo cách thủ công, tổng và số ô được tô màu sẽ không được tính lại tự động để phản ánh những thay đổi.

Trên thực tế, đó là đặc điểm bình thường của tất cả các Macro, VBA scripts và hàm User-Defined. Vấn đề là tất cả các chức năng đó sẽ đi kèm với sự thay đổi dữ liệu của một bảng tính và Excel không nhận thấy sự thay đổi màu chữ hoặc màu sắc của ô này. Vì vậy, sau khi tô màu các ô một cách thủ công, chỉ cần đặt con trỏ chuột vào bất kỳ ô nào, nhấn F2 và Enter, số ô và tổng của chúng sẽ được cập nhật.

Tính tổng và đếm số ô bằng màu sắc trên toàn bộ bảng tính:

Đoạn VB bên dưới được viết theo câu trả lời của Connor (một bậc thầy Excel) và thực hiện chính xác những gì Connor yêu cầu, cụ thể là đếm và tính tổng các ô cùng màu trong tất cả trang tính của bảng tính. Vì vậy, ở đây có mã số:

Function WbkCountCellsByColor(cellRefColor As Range)

Dim vWbkRes

Dim wshCurrent As Worksheet

Application.ScreenUpdating = False

Application.Calculation = xlCalculationManual

vWbkRes = 0

For Each wshCurrent In Worksheets

wshCurrent.Activate

vWbkRes = vWbkRes + CountCellsByColor(wshCurrent.UsedRange, cellRefColor)

Next

Application.ScreenUpdating = True

Application.Calculation = xlCalculationAutomatic

WbkCountCellsByColor = vWbkRes

End Function

Function WbkSumCellsByColor(cellRefColor As Range)

Dim vWbkRes

Dim wshCurrent As Worksheet

Application.ScreenUpdating = False

Application.Calculation = xlCalculationManual

vWbkRes = 0

For Each wshCurrent In Worksheets

wshCurrent.Activate

vWbkRes = vWbkRes + SumCellsByColor(wshCurrent.UsedRange, cellRefColor)

Next

Application.ScreenUpdating = True

Application.Calculation = xlCalculationAutomatic

WbkSumCellsByColor = vWbkRes

End Function

Bạn sử dụng Macro tương tự như mã trước đó và sử dụng kết quả của các công thức =WbkCountCellsColor()=WbkSumCellsByColor(). Chỉ cần nhập công thức vào bất kỳ ô trống nào trên trang tính nằm ngoài vùng dữ liệu được chọn, xác định địa chỉ ô tính chứa màu bạn muốn vào ngoặc đơn, ví dụ = WbkSumCellsByColor (A1), và công thức sẽ hiển thị tổng của tất cả các ô được tô cùng trong bảng tính của bạn.

Tùy chỉnh các chức năng để tô màu nền ô, tô màu chữ và mã màu sắc

Ở đây, bạn sẽ tìm thấy một bản tóm tắt tất cả các chức năng chúng ta đã sử dụng trong ví dụ này cũng như một vài cái chức năng mới để lấy mã màu.

Chú ý. Hãy nhớ rằng tất cả các công thức chỉ hoạt động nếu bạn đã thêm chức năng do người dùng định nghĩa vào bảng tính Excel như đã trình bày ở trên.

Chức năng đếm theo màu sắc:

CountCellsByColor (phạm vi, mã màu) – đếm các ô có màu nền được chỉ định.

Trong ví dụ trên, chúng tôi sử dụng công thức sau để đếm các ô theo màu =CountCellsByColor (F2: F14, A17), trong đó F2:F14 là vùng dữ liệu đã chọn và A17 là ô có màu nền cần thiết. Bạn có thể sử dụng tất cả các công thức khác được liệt kê dưới đây theo cách tương tự.

CountCellsByFontColor (phạm vi, mã màu) – đếm các ô với màu chữ được chỉ định.

Công thức tính tổng bằng màu sắc:

SumCellsByColor (range, color code) – tính tổng của các ô có cùng màu nền.

SumCellsByFontColor (range, color code) – tính tổng của các ô có cùng màu chữ.

Công thức để lấy mã màu:

GetCellFontColor (cell) – trả về mã màu chữ của một ô xác định.

GetCellColor (cell) – trả về mã màu nền của một ô xác định.

The formula to get the color code

Cách đếm và tính tổng các ô màu định dạng có điều kiện:

VBA dưới đây sẽ hiển thị số ô màu và tổng các giá trị trong ô, bất kể loại định dạng có điều kiện nào được sử dụng.

Sub SumCountByConditionalFormat()

Dim indRefColor As Long

Dim cellCurrent As Range

Dim cntRes As Long

Dim sumRes

Dim cntCells As Long

Dim indCurCell As Long

cntRes = 0

sumRes = 0

cntCells = Selection.CountLarge

indRefColor = ActiveCell.DisplayFormat.Interior.Color

For indCurCell = 1 To (cntCells – 1)

If indRefColor = Selection(indCurCell).DisplayFormat.Interior.Color Then

cntRes = cntRes + 1

sumRes = WorksheetFunction.Sum(Selection(indCurCell), sumRes)

End If

Next

MsgBox “Count=” & cntRes & vbCrLf & “Sum= ” & sumRes & vbCrLf & vbCrLf & _

“Color=” & Left(“000000”, 6 – Len(Hex(indRefColor))) & _

Hex(indRefColor) & vbCrLf, , “Count & Sum by Conditional Format color”

End Sub

Cách sử dụng mã để đếm số ô màu và tính tổng giá trị của chúng

  1. Thêm đoạn mã trên vào trang tính như đã giải thích ở ví dụ.
  2. Chọn một dãy hoặc nhiều vùng dữ liệu bạn cần nếu bạn có dữ liệu dạng số.
  3. Nhấn giữ phím Ctrl, chọn một ô tính với màu cần thao tác, thả phím Ctrl.
  4. Nhấn Alt+F8 để mở danh sách macros trong bảng tính.
  5. Chọn SumCountByConditionalFormat và nhấp vào Run.

Running a macro to count and sum cells colored using conditional formatting

Kết quả hiển thị như sau.

The count, sum and color code of cells colored with conditional formatting

Trong ví dụ này, chúng ta chọn cột Qty. và có các con số sau:

  • Count là số ô tính trong một màu cụ thể, như số ô màu đỏ trong ví dụ được đánh dấu là “Past Due”.
  • Sum là tổng giá trị các ô màu đỏ trong cột Qty, cụ thể là tổng số ô “Past Due”.
  • Color là mã màu của ô được chọn, cụ thể là ô D2.

Qua bài viết này chúng ta thấy việc sử dụng VBA trong Excel đem lại hiệu quả rất nhiều trong công việc đúng không nào. Không chỉ có vậy, VBA còn làm được nhiều điều thú vị hơn nữa như: giúp bạn tăng tốc trong quá trình xử lý trên excel, giúp lập các báo cáo tự động, điều khiển các chương trình khác như word, power point, outlook… giúp biến file excel của bạn trở thành 1 phần mềm chuyên nghiệp…