22.8.19
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.
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.
3. 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
4. Lưu bảng tính thành “Excel Macro-Enabled Workbook (.xlsm)”.
5. 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)
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.
Running a macro to count and sum cells colored using 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…
Để trao đổi và hỏi đáp thêm về chủ đề này, vui lòng truy cập vào diễn đàn các bạn nhé https://thuthuataccess.com/forum/forum-21.html
Không có nhận xét nào
Đăng nhận xét