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? 

Hôm nay Kênh phần mềm việt sẽ giới thiệu với mọi người cách tự viết hàm để có thể tính tổng, tính trung bình, đếm số lượng theo màu chữ, màu nền ô. Dưới đây là các bước thực hiện.

1. Khởi động Microsoft Excel.

2. Nhấn ALT + F11 để bắt đầu Visual Basic Editor.

Vào Menu Inser\Module

3. Nhập toàn bộ đoạn mã phía dưới sau vào màn hình bên phải

'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

'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