excel - 根据颜色和日期对单元格求和

Question

我有以下内容:

日期 ------- 费用

Jan      £500
Jan      £600
Feb      £300
Feb      £600
March    £1000
March    £500

成本单元格的颜色根据其当前状态(已确认的绿色、未确认的白色、半确认的黄色)而不同，我需要一个公式来汇总所有成本，例如绿色和 2 月。

我知道这种颜色函数需要 VBA，并且找到了一个有用的名为 colorfunction() 的函数，它允许我使用以下公式对颜色单元格进行求和/计数:

colorfunction(A1, B1:B5, FALSE)

A1 是用于比较范围的颜色，FALSE/TRUE 返回总和或计数结果。

但是我不能将此自定义函数用于 MONTH() 公式或 SUMIF。
我可能完全把这个复杂化了，所以请指出我在试图解决这个问题时犯的任何愚蠢的错误。

Answer 1

将此函数添加到您的 VBA 模块以返回单元格内部颜色索引:

    Function ColorIndex(rng As Range, _
                    Optional text As Boolean = False) As Variant
    Dim cell As Range, row As Range
    Dim i As Long, j As Long
    Dim iWhite As Long, iBlack As Long
    Dim aryColours As Variant

    If rng.Areas.Count > 1 Then
        ColorIndex = CVErr(xlErrValue)
        Exit Function
    End If

    iWhite = WhiteColorindex(rng.Worksheet.Parent)
    iBlack = BlackColorindex(rng.Worksheet.Parent)

    If rng.Cells.Count = 1 Then
        If text Then
            aryColours = DecodeColorIndex(rng, True, iBlack)
        Else
            aryColours = DecodeColorIndex(rng, False, iWhite)
        End If

    Else
        aryColours = rng.Value
        i = 0

        For Each row In rng.Rows
            i = i + 1
            j = 0

            For Each cell In row.Cells
                j = j + 1

                If text Then
                    aryColours(i, j) = _
                      DecodeColorIndex(cell, True, iBlack)
                Else
                    aryColours(i, j) = _
                      DecodeColorIndex(cell, False, iWhite)
                End If

            Next cell

        Next row

    End If

    ColorIndex = aryColours

End Function


Private Function WhiteColorindex(oWB As Workbook)

Dim iPalette As Long
    WhiteColorindex = 0
    For iPalette = 1 To 56
        If oWB.Colors(iPalette) = &HFFFFFF Then
            WhiteColorindex = iPalette
            Exit Function
        End If
    Next iPalette
End Function

Private Function BlackColorindex(oWB As Workbook)
Dim iPalette As Long
    BlackColorindex = 0
    For iPalette = 1 To 56
        If oWB.Colors(iPalette) = &H0 Then
            BlackColorindex = iPalette
            Exit Function
        End If
    Next iPalette
End Function

Private Function DecodeColorIndex(rng As Range, _
                                  text As Boolean, _
                                  idx As Long)
Dim iColor As Long
    If text Then
        iColor = rng.Font.ColorIndex
    Else
        iColor = rng.Interior.ColorIndex
    End If
    If iColor < 0 Then
        iColor = idx
    End If
    DecodeColorIndex = iColor
End Function

然后获取颜色索引为 14(绿色)的所有单元格的计数，使用 sumproduct 如下:

=SUMPRODUCT(--(ColorIndex(B1:B100000)=14),B1:B100000)

这将返回范围 B1:B100000 中所有单元格的总和，颜色为 14(绿色)

最终示例应如下所示:



此外，如果您更喜欢 Sumifs 而不是 Sumproduct yopu 可以选择使用辅助列。在成本旁边的列中输入 =ColorIndex(B1)然后向下拖动



然后在另一个单元格中输入公式
=SUM(SUMIFS(B1:B10,C1:C10,14,A1:A10,{"FEB","MARCH"}))
用您想要总结的月份替换月份(您的原始标题表明这是您的最终目标)。

这将汇总助手行表示索引为 14(绿色)且月份为 2 月或 3 月的成本值