excel - VBA:计数出现一对值的次数

Question

要从中生成:

那:

我有这段代码:

Sub missing()
Dim ws, wsOut As Worksheet
Set ws = ActiveWorkbook.Sheets("Table1")
Set wsOut = ActiveWorkbook.Sheets("output")

lastRow = ws.Range("G" & Rows.Count).End(xlUp).Row
lastRowOut = wsOut.Range("G" & Rows.Count).End(xlUp).Row + 1

For i = 1 To lastRow
    If (ws.Cells(i, 10).Value = "") _
    And _
    ((ws.Cells(i, 7).Value = "Peking") Or _
    (ws.Cells(i, 7).Value = "Tokio") Or _
    (ws.Cells(i, 7).Value = "London")) _
    And _
    ((ws.Cells(i, 8).Value = "A") Or _
    (ws.Cells(i, 8).Value = "B") Or _
    (ws.Cells(i, 8).Value = "C")) _
    Then
        wsOut.Range("B" & lastRowOut & ":C" & lastRowOut).Value = ws.Range("G" & i & ":H" & i).Value
        wsOut.Range("A" & lastRowOut).Value = i
        lastRowOut = lastRowOut + 1
    End If
Next i
End Sub

我试图实现一个代码来生成另外这个标记为红色的输出:

所以我正在尝试计算并列出发生的每一对。我试图在 if 语句中实现“countifs”，但它失败了。实际表中“城市”中有 40 多个条目，“部门”中有 10 多个条目，总共有 6.000 多个条目。如果有人可以帮助我解决这个问题，我会很高兴。提前谢谢各位!

Answer 1

正如您在评论中提到的那样，您似乎不知道如何录制宏:您转到“开发人员”功能区(您可能需要先启用它)，在第一部分中，您单击“录制宏”，然后您只需开始做你想记录的事情(在这种情况下，插入一个数据透视表)。
我刚刚做到了，使用范围“A1:B8”作为输入并创建您正在寻找的数据透视表，然后自动创建以下代码(请注意这是自动添加的代码，其中有很多多余的行、参数……:可以将其用作起点，但尝试通过修改、删除……部分来学习它是非常有用的):

Sub Macro1()
'
' Macro1 Macro
'

'
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        "Sheet1!R1C1:R8C2", Version:=6).CreatePivotTable TableDestination:= _
        "Sheet1!R2C4", TableName:="PivotTable3", DefaultVersion:=6
    Sheets("Sheet1").Select
    Cells(2, 4).Select
    With ActiveSheet.PivotTables("PivotTable3")
        .ColumnGrand = True
        .HasAutoFormat = True
        .DisplayErrorString = False
        .DisplayNullString = True
        .EnableDrilldown = True
        .ErrorString = ""
        .MergeLabels = False
        .NullString = ""
        .PageFieldOrder = 2
        .PageFieldWrapCount = 0
        .PreserveFormatting = True
        .RowGrand = True
        .SaveData = True
        .PrintTitles = False
        .RepeatItemsOnEachPrintedPage = True
        .TotalsAnnotation = False
        .CompactRowIndent = 1
        .InGridDropZones = False
        .DisplayFieldCaptions = True
        .DisplayMemberPropertyTooltips = False
        .DisplayContextTooltips = True
        .ShowDrillIndicators = True
        .PrintDrillIndicators = False
        .AllowMultipleFilters = False
        .SortUsingCustomLists = True
        .FieldListSortAscending = False
        .ShowValuesRow = False
        .CalculatedMembersInFilters = False
        .RowAxisLayout xlCompactRow
    End With
    With ActiveSheet.PivotTables("PivotTable3").PivotCache
        .RefreshOnFileOpen = False
        .MissingItemsLimit = xlMissingItemsDefault
    End With
    ActiveSheet.PivotTables("PivotTable3").RepeatAllLabels xlRepeatLabels
    ActiveWorkbook.ShowPivotTableFieldList = True
    With ActiveSheet.PivotTables("PivotTable3").PivotFields("City")
        .Orientation = xlRowField
        .Position = 1
    End With
    With ActiveSheet.PivotTables("PivotTable3").PivotFields("Dep")
        .Orientation = xlRowField
        .Position = 2
    End With
    ActiveSheet.PivotTables("PivotTable3").AddDataField ActiveSheet.PivotTables( _
        "PivotTable3").PivotFields("Dep"), "Count of Dep", xlCount
End Sub

顺便说一句:从名称“PivotTable3”可以看出，我的第一次尝试没有成功 :-)