excel - COUNTIFS VLOOKUP 返回值？

Question

我被公式困住了。本质上，我想要做的是根据从另一张纸中提取的数据来计算特定值在一张纸中出现的次数。

例如我有三张。一张表包含作业列表和与客户关联的代码。第二张表包含客户列表和客户详细信息。第三张是我的结果表。

我想统计每家公司的“How Heards”。例如，在下面的表 1 中，Apple 有 3 个客户。如果我们使用客户端代码 ID 并转到表 2，我们可以看到总共有 2 个 Online 和 0 个 Facebook。此结果显示在表 3 上。结果表。

表 1 示例

表 2 示例

表 3 示例(我希望计算结果如下)

Answer 1

为了实际为这个问题提供合适的答案，这里有一些 VBA 代码，允许工作簿创建与其自身的 ADO 连接并使用 SELECT、DISTINCT、WHERE、INNER JOIN、GROUP BY 和 ORDER 生成报告BY 子句。

Sub Inner_Join()
    Dim cnx As Object, rs As Object
    Dim sWS1 As String, sWS2 As String, sWB As String, sCNX As String, sSQL As String
    Dim ws1TBLaddr As String, ws2TBLaddr As String

    'Collect some string literals that will be used to build SQL
    ws1TBLaddr = Worksheets("Sheet1").Cells(1, 1).CurrentRegion.Address(0, 0)
    sWS1 = Worksheets("Sheet1").Name
    ws2TBLaddr = Worksheets("Sheet2").Cells(1, 1).CurrentRegion.Address(0, 0)
    sWS2 = Worksheets("Sheet2").Name
    sWB = ThisWorkbook.FullName

    'Build the connection string
    'The first is for 64-bit Office; the second is more universal
    sCNX = "Provider=Microsoft.Jet.OLEDB.12.0;Data Source=" & sWB _
        & ";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"";"
    sCNX = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & sWB _
        & ";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"";"
    'Debug.Print sCNX

    'Create the necessary ADO objects
    Set cnx = CreateObject("ADODB.Connection")  'late binding; for early binding add
    Set rs = CreateObject("ADODB.Recordset")    'Microsoft AxtiveX Data Objects 6.1 library

    'Open the connection to itself
    cnx.Open sCNX

    With Worksheets("Sheet3")
        'Clear the reporting area
        .Cells(1, 1).CurrentRegion.ClearContents

        'get [Business Name] list from Sheet1
        sSQL = "SELECT DISTINCT w1.[Business Name]"
        sSQL = sSQL & " FROM [" & sWS1 & "$" & ws1TBLaddr & "] w1"
        sSQL = sSQL & " ORDER BY w1.[Business Name]"
        'Debug.Print sSQL

        'Populate Sheet3!A:A
        rs.Open sSQL, cnx
        Do While Not rs.EOF
            'Debug.Print rs.Fields("Business Name")
            .Cells(.Rows.Count, 1).End(xlUp).Offset(1, 0) = rs.Fields("Business Name")
            rs.MoveNext
        Loop
        rs.Close

        'get [How Heard] list from Sheet2
        sSQL = "SELECT DISTINCT w2.[How Heard]"
        sSQL = sSQL & " FROM [" & sWS2 & "$" & ws2TBLaddr & "] w2"
        sSQL = sSQL & " WHERE w2.[How Heard] NOT LIKE 'None'"
        sSQL = sSQL & " ORDER BY w2.[How Heard]"
        'Debug.Print sSQL

        'Populate Sheet3!1:1
        rs.Open sSQL, cnx
        Do While Not rs.EOF
            'Debug.Print rs.Fields("How Heard")
            .Cells(1, .Columns.Count).End(xlToLeft).Offset(0, 1) = rs.Fields("How Heard")
            rs.MoveNext
        Loop
        rs.Close

        'start by seeding zeroes for all
        With .Cells(1, 1).CurrentRegion
            With .Resize(.Rows.Count - 1, .Columns.Count - 1).Offset(1, 1)
                .Cells = 0
            End With
        End With

        'get the counts for the [Business Name]×[How Heard] combinations
        sSQL = "SELECT  COUNT(w1.[Business Name]), w1.[Business Name], w2.[How Heard]"
        sSQL = sSQL & " FROM [" & sWS1 & "$" & ws1TBLaddr & "] w1"
        sSQL = sSQL & " INNER JOIN [" & sWS2 & "$" & ws2TBLaddr & "] w2 ON w1.[Client Code] = w2.[Client Code]"
        sSQL = sSQL & " WHERE w2.[How Heard] <> 'None'"
        sSQL = sSQL & " GROUP BY w1.[Business Name], w2.[How Heard]"
        'Debug.Print sSQL

        'Populate Sheet3 data matrix area
        rs.Open sSQL, cnx
        With .Cells(1, 1).CurrentRegion
            Do While Not rs.EOF
                'Debug.Print rs.Fields(0) & ":" & rs.Fields(1) & ":" & rs.Fields(2)
                .Cells(Application.Match(rs.Fields(1), .Columns(1), 0), _
                       Application.Match(rs.Fields(2), .Rows(1), 0)) = rs.Fields(0)
                rs.MoveNext
            Loop
        End With
        rs.Close
    End With

Final_Cleanup:
    Set rs = Nothing
    cnx.Close: Set cnx = Nothing

End Sub

结果应类似于以下内容。