☰

查找多个匹配项

tag-icon • tag-icon microsoft-excel
查找多个匹配项

我的电子表格的第一列是球队名称列表。接下来的几列包含球员姓名。一名球员可能同时效力于多支球队。最后，我有一列是球员姓名列表。

我想查看球员名单，找出每个球员在哪支球队。顺序并不重要

我怎样才能做到这一点？

例如，给定：

red | tom | bob | sally | emma
blue | tom | george | bill | sally
green | george | bob
yellow | sally| arthur | george | emma

我希望的结果是：

tom | red | blue
bob | red | green
sally | red | blue | yellow
george | blue | green | yellow
arthur | yellow
emma | yellow | red

答案1

您需要为此启用 VBA。然后您需要在执行完其他操作后将其粘贴到 VBA 编辑器中：

Sub CreateWorksheet_TransposedListing(inputData As Range, worksheetName As String)
    AddNumberedSheet worksheetName
    Dim new_sheet As Worksheet
    Set new_sheet = Sheets(Sheets.Count)
    Dim nRowDx As Integer, nColDx As Integer
    Dim sValue As String, sHeader As String, sAddress As String
    For nRowDx = 1 To inputData.Rows.Count
        For nColDx = 1 To inputData.Columns.Count
            If nColDx = 1 Then
                sValue = Trim(inputData.Cells(nRowDx, nColDx).Value)
            Else
                sHeader = Trim(inputData.Cells(nRowDx, nColDx).Value)
                sAddress = FindNextHeaderCell(new_sheet.Name, sHeader)
                If sAddress = "" Then Exit Sub
                new_sheet.Range(sAddress) = sValue
            End If
        Next
    Next
End Sub

Function FindNextHeaderCell(sSheet As String, sRowHeaderName As String) As String
    Dim nRowDx As Integer, nColDx As Integer
    For nRowDx = 1 To 32766
        If IsEmpty(Worksheets(sSheet).Cells(nRowDx, "A")) Then
            Worksheets(sSheet).Cells(nRowDx, "A") = sRowHeaderName
            FindNextHeaderCell = Worksheets(sSheet).Cells(nRowDx, "B").Address
            Exit Function
        ElseIf Worksheets(sSheet).Cells(nRowDx, "A") = sRowHeaderName Then
            For nColDx = 2 To 32766
                If IsEmpty(Worksheets(sSheet).Cells(nRowDx, nColDx)) Then
                    FindNextHeaderCell = Worksheets(sSheet).Cells(nRowDx, nColDx).Address
                    Exit Function
                End If
            Next
            If nColDx > 32766 Then
                MsgBox "This result is larger than VBA will support. Results have been truncated."
                FindNextHeaderCell = ""
                Exit Function
            End If
        End If
    Next
    If nRowDx > 32766 Then
        MsgBox "This result is larger than VBA will support. Results have been truncated."
    End If
    FindNextHeaderCell = ""
End Function

Sub AddNumberedSheet(Optional sWorksheetName As String, Optional bSelectWorksheet As Boolean)
    Dim sheet_name As String, num_text As String
    Dim i As Integer, new_num As Integer, max_num As Integer
    Dim new_sheet As Worksheet
    max_num = 0
    For i = 1 To Sheets.Count
        sheet_name = Sheets(i).Name
        If Left$(sheet_name, Len(sWorksheetName)) = sWorksheetName Then
            num_text = Mid$(sheet_name, Len(sWorksheetName) + 1)
            new_num = Val(num_text)
            If new_num > max_num Then max_num = new_num
        End If
    Next i
    Set new_sheet = Sheets.Add(after:=Sheets(Sheets.Count))
    new_sheet.Name = sWorksheetName & Format$(max_num + 1)
    If bSelectWorksheet Then new_sheet.Select
End Sub

然后你需要添加一个方法来调用它。例如，如果你有一个按钮，那么你可以使用如下方法：

Sub Button1_Click()
    CreateWorksheet_TransposedListing Range("A1:E4"), "TestSheet"
End Sub

相关内容