vba - 无需 VBA 的复杂数据透视式汇总

Question

给定一组这样的数据:

User    Item1 Desc.    Item1 Sel.    Item2 Desc.    Item2 Sel.    Item3 Desc.    Item3 Sel.
UserA   Item 1         Yes           Item 2         Yes           Item 3         Yes
UserB   Item 1         Yes           Item 2         No            Item 3         No
UserB   Item 1         Yes           Item 2         Yes           Item 3         No
UserC   Item 1         Yes           Item 2         No            Item 3         Yes
UserA   Item 1         No            Item 2         Yes           Item 3         Yes
UserA   Item 1         Yes           Item 2         No            Item 3         Yes

有没有办法可以使用内置的 Excel 工具、函数或公式来生成这样的输出

Item    UserA    UserB    UserC
Item1   1        0        0
Item2   1        1        1
Item3   0        2        0

Items 和 Users 都在哪里合并，并且显示的值是表中 No 的计数？

我被要求为我的一位同事准备这个，并最终创建了下面的 VBA 函数。它又快又脏，当然需要重构，但它确实满足了我的需要。

Sub SummarizeData()

Dim wksSrc As Worksheet
Dim dicAssoc As Object
Dim l As Long
Dim i As Integer
Dim rngCell As Range
Dim v As Variant

    Set dicAssoc = CreateObject("Scripting.Dictionary")
    With ThisWorkbook.Sheets.Add()
        .Name = "Results"
        Set wksSrc = ThisWorkbook.Sheets("data")
        For l = 2 To 184
            If Not dicAssoc.exists(wksSrc.Range("C" & l).Value) Then
                dicAssoc.Add wksSrc.Range("C" & l).Value, wksSrc.Range("C" & l).Value
            End If
        Next l
        .Range("A1:A26").Value = ThisWorkbook.Sheets("Sheet5").Range("A1:A26").Value
        l = 2
        For Each v In dicAssoc.items
            .Cells(1, l).Value = v
            l = l + 1
        Next v
        For Each rngCell In wksSrc.Range("A2:CT184").Cells
            For l = 2 To 26
                If rngCell.Value = .Cells(l, 1).Value Then
                    If rngCell.Offset(0, 1).Value = "No" Then
                        For i = 2 To 148
                            If .Cells(1, i).Value = wksSrc.Range("C" & rngCell.Row).Value Then
                                .Cells(l, i).Value = .Cells(l, i).Value + 1
                            End If
                        Next i
                    End If
                End If
            Next l
        Next rngCell
    End With
End Sub

我最初尝试在没有代码的情况下解决这个问题，但经过一些尝试和错误后，我决定最好不要在这个项目上浪费一整天的时间。我想我可以尝试 DCOUNTA，但这需要我提取多行额外的数据，这会浪费空间:

DCOUNT 标准示例:

Item1 Sel.    Item2 Sel.    Item3 Sel.    User
No                                        UserA
              No                          UserA
                            No            UserA
No                                        UserB
              No                          UserB
                            No            UserB

诸如此类。大约有 25 个项目和大约 25 个用户，这将是一个巨大的 block ，其中大部分必须手动创建。到那时，我可能会开始用手计算所有的东西。

我还考虑过使用 VLOOKUP、HLOOKUP、INDEX 和 INDIRECT 的一些组合，但我永远无法得到计算全部否的结果；它总是只捕获它找到的第一行。

我想知道是否有一种相对简单的方法可以不使用 VBA 生成此摘要。

Answer 1

使用 SUMPRODUCT 公式可以轻松实现您想要的结果。

请参阅随附的示例和屏幕截图。

要查找项目 1 和用户 1 的“否”编号，我使用的公式是

=SUMPRODUCT(($A$2:$A$7=$B$11)*($C$2:$C$7="No"))

同样，其余的事情你也必须这样做。

示例文件:

http://wikisend.com/download/725440/Gaffi.xlsx

快照: