Tự xây dựng hàm làm việc với màu trong Excel
(Thủ thuật VBA trong Excel)
– Để tính tổng, trung bình, đếm số lượng theo một điều kiện ta thường sử dụng các hàm như: SUMIF, AVERAGEIF, COUTIF. Tuy nhiên những hàm này được sử dụng để tính toán, thống kê theo điều kiện là các giá trị như: số, ký tự, ngày tháng… Trong thực tế công việc có những trường hợp ta phải tính toán, thống kê không theo giá trị mà theo định dạng như: màu chữ, màu nền ô. Ví dụ như bạn muốn tính tổng các ô trong một vùng có màu chữ là đỏ, hay tính trung bình của những ô có màu nền là vàng. Với những trường hợp này trong Excel hiện chưa có hàm nào để thực hiện công việc này. Vậy phải tính toán thủ công nếu gặp phải trường hợp như này sao?
Function CountCellsByBackColor(rData As Range, cellRefColor As Range) As Long
Dim indRefColor As Long
Dim cellCurrent As Range
Dim cntRes As Long
cntRes = 0
indRefColor = cellRefColor.CellS(1, 1).Interior.ColorIndex
For Each cellCurrent In rData
If indRefColor = cellCurrent.Interior.ColorIndex Then
cntRes = cntRes + 1
End If
Next cellCurrent
CountCellsByBackColor = cntRes
End Function
‘Ham tinh tong gia tri theo mau nen chu
Function SumCellsByBackColor(rData As Range, cellRefColor As Range)
Dim indRefColor As Long
Dim cellCurrent As Range
Dim sumRes
sumRes = 0
indRefColor = cellRefColor.CellS(1, 1).Interior.ColorIndex
For Each cellCurrent In rData
If indRefColor = cellCurrent.Interior.ColorIndex Then
sumRes = WorksheetFunction.Sum(cellCurrent, sumRes)
End If
Next cellCurrent
SumCellsByBackColor = sumRes
End Function
‘Ham tinh trung binh gia tri theo mau nen chu
Function AverageCellsByBackColor(rData As Range, cellRefColor As Range)
Dim indRefColor As Long
Dim cellCurrent As Range
Dim sumRes
Dim i As Long
sumRes = 0
indRefColor = cellRefColor.CellS(1, 1).Interior.ColorIndex
For Each cellCurrent In rData
If indRefColor = cellCurrent.Interior.ColorIndex Then
sumRes = WorksheetFunction.Sum(cellCurrent, sumRes)
i = i + 1
End If
Next cellCurrent
AverageCellsByBackColor = sumRes / i
End Function
‘Ham dem so luong theo mau chu
Function CountCellsByFontColor(rData As Range, cellRefColor As Range) As Long
Dim indRefColor As Long
Dim cellCurrent As Range
Dim cntRes As Long
cntRes = 0
indRefColor = cellRefColor.CellS(1, 1).Font.ColorIndex
For Each cellCurrent In rData
If indRefColor = cellCurrent.Font.ColorIndex Then
cntRes = cntRes + 1
End If
Next cellCurrent
CountCellsByFontColor = cntRes
End Function
‘Ham tinh tong gia tri theo mau chu
Function SumCellsByFontColor(rData As Range, cellRefColor As Range)
Dim indRefColor As Long
Dim cellCurrent As Range
Dim sumRes
sumRes = 0
indRefColor = cellRefColor.CellS(1, 1).Font.ColorIndex
For Each cellCurrent In rData
If indRefColor = cellCurrent.Font.ColorIndex Then
sumRes = WorksheetFunction.Sum(cellCurrent, sumRes)
End If
Next cellCurrent
SumCellsByFontColor = sumRes
End Function
‘Ham tinh trung binh gia tri theo mau chu
Function AverageCellsByFontColor(rData As Range, cellRefColor As Range)
Dim indRefColor As Long
Dim cellCurrent As Range
Dim sumRes
Dim i As Long
sumRes = 0
indRefColor = cellRefColor.CellS(1, 1).Font.ColorIndex
For Each cellCurrent In rData
If indRefColor = cellCurrent.Font.ColorIndex Then
sumRes = WorksheetFunction.Sum(cellCurrent, sumRes)
i = i + 1
End If
Next cellCurrent
AverageCellsByFontColor = sumRes / i
End Function
4. Quay trở lại màn hình Excel và sử dụng các hàm vừa tạo
a. Thao tác với màu chữ
– Đếm số lượng
Cú pháp: = CountCellsByFontColor (Vùng cần đếm, Vùng điều kiện cần đếm)
Ví du: =CountCellsByFontColor($C$3:C$12,B16)
Trong đó:
+ $C$3:C$12: Là vùng dữ liệu cần đếm
+ B16: Vùng điều kiện màu chữ
– Tính tổng
Cú pháp: = SumCellsByFontColor(Vùng cần tính tổng, Vùng điều kiện cần tính tổng)
– Tính trung bình
Cú pháp: = AverageCellsByFontColor (Vùng cần tính TB, Vùng điều kiện cần tính TB)
b. Thao tác với màu nền
– Đếm số lượng
Cú pháp: = CountCellsByBackColor (Vùng cần đếm, Vùng điều kiện cần đếm)
Ví du: =CountCellsByBackColor($C$3:C$12,B16)
Trong đó:
+ $C$3:C$12: Là vùng dữ liệu cần đếm
+ B16: Vùng điều kiện màu chữ
– Tính tổng
Cú pháp: = SumCellsByBackColor(Vùng cần tính tổng, Vùng điều kiện cần tính tổng)
– Tính trung bình
Cú pháp: = AverageCellsByBackColor (Vùng cần tính TB, Vùng điều kiện cần tính TB)
Tin học văn phòng