如何在 Excel 中根据匹配的条件获取单元格值？

Question

我的 excel 文件中有 AC 列，如图所示。我的问题是创建一个像 EG 列中所示的表格。如何在 excel 中做到这一点？

Answer 1

下面的宏生成附图所示形状的输出。

'assumptions
'(1) the input data is in columns A,B,C
'(2) the output data is rendered in columns E,F,G
Sub ListByTeacher()
Dim noOfRows As Integer
Dim i As Integer
Dim j As Integer
Dim c_lv_array(1) As String ' c - course, lv - load value
Dim c As New Collection 'output table with values
Dim k As New Collection 'keys
Dim tmpVar As Variant

'[INPUT]
'identify no of rows with data
i = 1
Do While Len(Cells(i, 1).Value) > 0
    i = i + 1
Loop
noOfRows = i - 1

'Loop through input data rows and build the collection of teachers
For i = 2 To noOfRows
    c_lv_array(0) = Trim(CStr(Cells(i, 1).Value))
    c_lv_array(1) = Trim(CStr(Cells(i, 2).Value))
    On Error Resume Next
    tmpVar = c.Item(Trim(CStr(Cells(i, 3).Value))) 'if teacher in collection
    If IsArray(tmpVar) = True Then 'exists
        If InStr(1, tmpVar(0), c_lv_array(0), vbBinaryCompare) = 0 Then
            'c_lv_array(0) = c_lv_array(0) & ", " & tmpVar(0) 'use this line or below one
            c_lv_array(0) = tmpVar(0) & ", " & c_lv_array(0) 'reversed order to the above line
            c_lv_array(1) = CStr(CInt(c_lv_array(1)) + CInt(tmpVar(1)))
        End If
        c.Remove Trim(CStr(Cells(i, 3).Value))
        c.Add Item:=c_lv_array, Key:=Trim(CStr(Cells(i, 3).Value))
    End If
    tmpVar = Empty
    c.Add Item:=c_lv_array, Key:=Trim(CStr(Cells(i, 3).Value))
    k.Add Item:=Trim(CStr(Cells(i, 3).Value)), Key:=Trim(CStr(Cells(i, 3).Value))
Next i

'[OUTPUT]
'Render the result in s/s
Cells(1, 5) = "Teacher"
Cells(1, 6) = "Courses"
Cells(1, 7) = "Load"

j = 2
For i = 1 To c.Count
    tmpVar = c.Item(k.Item(i))
    Cells(j, 5) = k.Item(i)
    Cells(j, 6) = tmpVar(0)
    Cells(j, 7) = tmpVar(1)
    j = j + 1
Next i
End Sub

Answer 2

您确实应该为此制作一个数据透视表。

这样，您将能够将其添加到 AC 列的列表中，同时不断更新 EG 中的其他视点。

但它确实需要两件事：

1. 为了能够不断更新数据透视表中的值，应该将 A 至 C 列变成一个表格（CTRL + L）。
2. 数据透视表有其自己的工作表。