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 đỏ.
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.
- Mở bảng tính Excel và nhấn Alt + F11 để mở Visual Basic Editor (VBE).
- 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.
- 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 |
- Lưu bảng tính thành “Excel Macro-Enabled Workbook (.xlsm)”.
- 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.
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) 
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 và SumCellsByFontColor.
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() và =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.
Cách đếm và tính tổng các ô màu định dạng có điều kiện:
Mã 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
- Thêm đoạn mã trên vào trang tính như đã giải thích ở ví dụ.
- 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ố.
- 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.
- Nhấn Alt+F8 để mở danh sách macros trong bảng tính.
- Chọn SumCountByConditionalFormat và nhấp vào Run.
Kết quả hiển thị như sau.
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…
