Làm việc với Excel thì chúng ta đã quá quen thuộc với các hàm tính toán, thống kê theo giá trị số, ký tự, ngày tháng, … như SUMIF, AVERAGEIF, COUTIF. Chúng ta cũng hay gặp trường hợp thực tế làm việc phải tính toán, thống kê theo giá trị màu chữ hay màu nền ô. Tuy nhiên Excel chưa có sẵn hàm để thực hiện công việc này.
QuanTriExcel.info xin hướng dẫn các bạn viết hàm tính tổng, trung bình và đếm số lượng theo màu chữ và màu nền ô. Bạn hãy thực hiện lần lượt các bước sau:
1. Khởi động Microsoft Excel
2. Nhấn ALT + F11 để bắt đầu Visual Basic Editor
Trong menu Insert chọn Module
3. Nhập toàn bộ đoạn mã sau
‘Ham dem so luong theo mau nen chu
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
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
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
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
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
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 lại màn hình Excel
a. Thao tác với màu chữ
Để đếm số lượng, bạn dùng hàm: = CountCellsByFontColor (Vùng cần đếm, Vùng điều kiện cần đếm).
Ví dụ: =CountCellsByFontColor($B$2:B$10,A15) với $B$2:B$10 là vùng dữ liệu cần đếm và A15 là vùng điều kiện màu chữ.
Ví dụ: =CountCellsByFontColor($B$2:B$10,A15) với $B$2:B$10 là vùng dữ liệu cần đếm và A15 là vùng điều kiện màu chữ.
Để tính tổng, bạn dùng hàm: = 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, bạn dùng hàm: = 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, bạn dùng hàm: = CountCellsByBackColor (Vùng cần đếm, Vùng điều kiện cần đếm).
Ví dụ: =CountCellsByBackColor($D$2:DC$10,C17) với $D$2:DC$10 là vùng dữ liệu cần đếm và C17 là vùng điều kiện màu chữ.
Ví dụ: =CountCellsByBackColor($D$2:DC$10,C17) với $D$2:DC$10 là vùng dữ liệu cần đếm và C17 là vùng điều kiện màu chữ.
Để tính tổng, bạn dùng hàm: = SumCellsByBackColor(Vùng cần tính tổng, Vùng điều kiện cần tính tổng).
Để tính tổng, bạn dùng hàm: = AverageCellsByBackColor (Vùng cần tính TB, Vùng điều kiện cần tính TB).
Trên đây là QuanTriExcel.info khá đơn giản nhưng sẽ giúp ích cho các bạn trong thao tác và làm việc với Excel.
?DienDan.Edu.Vn cám ơn bạn đã quan tâm và rất vui vì bài viết đã đem lại thông tin hữu ích cho bạn. https://diendan.edu.vn/ ?Các bạn tham khảo hướng dẫn tải file trên Quản Trị Excel tại đây: http://www.quantriexcel.info/2018/11/huong-dan-tai-file-tren-quan-tri-excel.html
DienDan.Edu.Vn Cám ơn bạn đã quan tâm và rất vui vì bài viết đã đem lại thông tin hữu ích cho bạn.DienDan.Edu.Vn! là một website với tiêu chí chia sẻ thông tin,... Bạn có thể nhận xét, bổ sung hay yêu cầu hướng dẫn liên quan đến bài viết. Vậy nên đề nghị các bạn cũng không quảng cáo trong comment này ngoại trừ trong chính phần tên của bạn.Cám ơn.
