excel - Excel通过公式逻辑过滤列表

Question

我想滚动浏览我的数千封电子邮件日志列表，找到所有名为“Yoda”的人，并获取 Yoda 所在的 ID 号。

我已经尝试过用于 vlookups 和索引匹配的公式，但它不起作用，因为我得到了重复的 ID。如何将我的列表过滤为公式，以便我可以获取 Yoda 的所有 ID？我将把 ID 的公式放在另一个工作表中。

电子邮件日志：

Character       ID

[e-mail]  789
[e-mail]  664
[e-mail]  113
[e-mail] 115
[e-mail] 998
[e-mail]  446
[e-mail] 500

我希望在我的新工作表中看到的是：

Yoda    Luke   Anakin   Jabba

789     113    115      998
664     500
446

标签： excelexcel-formula

Answer 1

您可以使用自 2010 年以来在 Excel 中可用的Power Queryaka相当简单地做到这一点。对于早期版本，VBA 将是一个不错的选择。Get & Transform

除了创建两个自定义列之外，所有操作都可以从用户界面完成。自定义列需要在创建自定义列对话框中输入公式。

在电源查询中：

• 添加自定义列以删除@domain电子邮件的一部分。为列命名CharName

---

Text.Start([Character],Text.PositionOf([Character],"@"))

• 删除原始Characters列
• 通过...分组CharName

• Table添加将结果转换为列表的自定义列

---

Table.Column([Count],"ID")

• 在结果IDs列中，选择列右上方的双箭头，然后Extract Values使用逗号分隔符选择 to

• 通过分隔符（逗号）拆分该列，并将创建新列

• 删除原始Table列

• 转置整个表格
• 将第一行的内容提升为 Headers

这是M代码：

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Character", type text}, {"ID", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "CharName", each Text.Start([Character],Text.PositionOf([Character],"@"))),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Character"}),
    #"Grouped Rows" = Table.Group(#"Removed Columns", {"CharName"}, {{"Count", each _, type table [ID=number, CharName=text]}}),
    #"Added Custom1" = Table.AddColumn(#"Grouped Rows", "IDs", each Table.Column([Count],"ID")),
    #"Extracted Values" = Table.TransformColumns(#"Added Custom1", {"IDs", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Extracted Values", "IDs", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"IDs.1", "IDs.2", "IDs.3"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"IDs.1", Int64.Type}, {"IDs.2", Int64.Type}, {"IDs.3", Int64.Type}}),
    #"Removed Columns1" = Table.RemoveColumns(#"Changed Type1",{"Count"}),
    #"Transposed Table" = Table.Transpose(#"Removed Columns1"),
    #"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table", [PromoteAllScalars=true]),
    #"Changed Type2" = Table.TransformColumnTypes(#"Promoted Headers",{{"Yoda", Int64.Type}, {"Luke", Int64.Type}, {"Anakin", Int64.Type}, {"Jabba", Int64.Type}})
in
    #"Changed Type2"

这是来自原始数据的最终结果：

如果您更喜欢 VBA 方法，您可以为每个字符创建一个 ID 集合字典。您需要通过下面的代码来理解它，并使其适应您的特定工作簿和工作表设置。

Option Explicit
'Set reference to Microsoft Scripting Runtime
Sub charIDs()
    Dim wsSrc As Worksheet, wsRes As Worksheet, rRes As Range
    Dim vSrc As Variant, vRes As Variant
    Dim D As Dictionary, COL As Collection, sKey As String
    Dim I As Long, J As Long
    Dim V As Variant

Set wsSrc = Worksheets("Source")
Set wsRes = Worksheets("Results")
    Set rRes = wsRes.Cells(1, 1)

With wsSrc
    vSrc = .Range(.Cells(1, 1), .Cells(.Rows.Count, 2).End(xlUp))
End With

Set D = New Dictionary
    D.CompareMode = TextCompare

'Create a dictionary of collections of Id's for each character
For I = 2 To UBound(vSrc, 1)
    sKey = Split(vSrc(I, 1), "@")(0)
    If Not D.Exists(sKey) Then
        Set COL = New Collection
        COL.Add vSrc(I, 2)
        D.Add Key:=sKey, Item:=COL
    Else
        D(sKey).Add vSrc(I, 2)
    End If
Next I

'create results array
I = 0
For Each V In D.Keys
    I = IIf(I > D(V).Count, I, D(V).Count)
Next V

ReDim vRes(0 To I, 1 To D.Count)

'Populate
J = 0
For Each V In D.Keys
    J = J + 1
    vRes(0, J) = V
    For I = 1 To D(V).Count
        vRes(I, J) = D(V)(I)
    Next I
Next V

'size and fill results range
Set rRes = rRes.Resize(UBound(vRes, 1) + 1, UBound(vRes, 2))
With rRes
    .EntireColumn.Clear
    .Value = vRes
    With .Rows(1)
        .Font.Bold = True
        .HorizontalAlignment = xlCenter
    End With
    .EntireColumn.AutoFit
End With

End Sub