☰

1.VBA UDF 宏：

tag-icon • tag-icon microsoft-excel worksheet-function power-query microsoft-excel-365
1.VBA UDF 宏：

我有一张表，从中拉出一串唯一值，然后我想拉出所有键并使用 textjoin 合并它们。但是 Filter 函数将条件与单个值进行比较，而我希望条件查找整行。

在正常情况下，我只会坚持使用单一值并让它用表格拖动城镇，但在这种情况下，源是一个唯一函数，所以它不能是一个表格。

A 乙 C 德 埃 F
1 物品 钥匙 物品 钥匙
2 富 苹果 富 苹果、橙子
3 酒吧 苹果 酒吧 苹果、橙子
4 富 橙子
5 酒吧 橙子
6 酒吧 苹果

现在我有：

  1. A1:B6 是一个名为 Table1 的表
  2. 为了项目： 
    =UNIQUE(A1:A)
  3. 为了键结果列，从第 2 行开始： 
    =TEXTJOIN(",", TRUE, FILTER(Table1[Key],Table1[Item]=E2))
=TEXTJOIN(",", TRUE, FILTER(Table1[Key],Table1[Item]=E3))
=TEXTJOIN(",", TRUE, FILTER(Table1[Key],Table1[Item]=E4))
=TEXTJOIN(",", TRUE, FILTER(Table1[Key],Table1[Item]=E5))
=TEXTJOIN(",", TRUE, FILTER(Table1[Key],Table1[Item]=E6))

但是数据是动态的，所以我不能继续做 D5、D6 等，而且我也不想只是拖拽未使用的单元格，因为这会减慢文件的速度。

所以我正在寻找类似 =TEXTJOIN(",", TRUE, FILTER(Table1[Key],Table1[Item]=乙：乙)) 仅对非空 D 单元格加法，因此结果将是无需在 E3、E4 等中输入公式。可能吗？

另外，如果有多个键，即对于 Row 来说，该怎么办=TEXTJOIN(",", TRUE, FILTER(Table2[Key1] & " - " & Table2[Key2], Table1[Item]=E2))？

A 乙 C 德 埃 F
1 物品 钥匙 键2 物品 钥匙
2 富 苹果 葡萄 富 苹果 - 葡萄，橙子 - 苹果
3 酒吧 苹果 李子 酒吧 苹果 - 李子，橙子 - 测试
4 富 橙子 苹果
5 酒吧 橙子 测试
6 酒吧 苹果 李子

请注意，我有一个类似的有关 Web 应用程序的 Google Sheets 特定问题。

答案1

您可以执行以下操作：使用 ，对 数组中的每一行BYROW()应用自定义计算，检查 中的每个值是否等于 ，返回一个分隔列表，每行一个输出，分别有。LAMBDA()xxTable1[Item]Table1[Key]

在此处输入图片描述

=BYROW(D1#,LAMBDA(x, TEXTJOIN(", ",1,FILTER(Table1[Key],Table1[Item]=x,","))))

或者，使用MAP()

=VSTACK(",",DROP(MAP(D1#,LAMBDA(x,TEXTJOIN(", ",1,IF(x=Table1[Item],Table1[Key],"")))),1))

这只是一种替代方案，不需要像BYROW()应该做的那样，您想要实现的是使用MAP()函数：

=LET(
     _LastRow, MATCH(2,1/(D:D<>"")),
     VSTACK(", ",MAP(D2:INDEX(D:D,_LastRow), LAMBDA(x,
     TEXTJOIN(", ",1,IF(Table1[Item]=x,Table1[Key],""))))))

答案2

以下是不依赖 365 的替代方案（用户可以用这个)：

  1. VBA UDF 宏
  2. Power Query

1.VBA UDF 宏：

  • 优点 - 即使源数据有数千行，也可以在一秒钟内加载
  • 警告 - 需要启用宏，并且所有更改都需要纯粹在代码中完成

指示：

  1. 将以下代码粘贴到 VBA 编辑器模块中
  2. 通过以下任一方式在您想要输出的单元格中使用它：
    • 1 范围代表一切，例如=ConcatenateKeysForItems(Table1[#All])或=ConcatenateKeysForItems(a1:c6)
    • 1 个项目范围和 1 个键范围，例如=ConcatenateKeysForItems(Table1[[#All],[Item]], Table1[[#All],[Key]])或=ConcatenateKeysForItems(a1:a6, b1:b6)
    • 1 个项目范围、1 个 key1 范围和 1 个 key2 范围，例如=ConcatenateKeysForItems(Table1[[#All],[Item]],Table1[[#All],[Key]],Table1[[#All],[Key2]])或=ConcatenateKeysForItems(a1:a6, b1:b6, c1:c6)
  3. 选修的
    1. 修改第一行以更改分隔符和排除项
    2. 如果您希望它在每次更新源数据时自动更新（就像原生公式一样），那么下面还有另一段代码，您可以将其粘贴到 VBA 编辑器的 ThisWorkbook 中
Function ConcatenateKeysForItems(rng As Range, Optional rngKey1 As Range = Nothing, Optional rngKey2 As Range = Nothing) As Variant
    Dim ws As Worksheet, disallowedKeys As Variant, sepInternal As String, sepExternal As String
    sepInternal = " - "
    sepExternal = ","
    disallowedKeys = Array("0", "-1")
    Set ws = rng.Worksheet
    
    If UBound(disallowedKeys) = -1 Then
        disallowedKeys = Array("")
    End If
    If rngKey1 Is Nothing And rngKey2 Is Nothing And rng.Columns.Count = 1 Then
        ' Since there's only one column, simply return the unique items
        ConcatenateKeysForItems = Application.WorksheetFunction.Unique(rng.Columns(1))
        Exit Function
    End If
    
    Dim dict As Object
    Set dict = CreateObject("Scripting.Dictionary")
    
    Dim item As Variant, keyValue As Variant, value2 As Variant
    Dim combinedValue As String
    Dim row As Range
    Dim colCount As Long
    colCount = rng.Columns.Count
    
    For Each row In rng.Rows
        item = row.Cells(1, 1).Value
        If Not dict.Exists(item) Then
            dict.Add item, CreateObject("Scripting.Dictionary")
        End If
        
        If Not rngKey1 Is Nothing Then
            keyValue = rngKey1.Cells(row.row - rng.row + 1, 1).Value
        Else
            keyValue = row.Cells(1, 2).Value ' Get keyValue from second column if rngKey1 is Nothing
        End If
      
        ' Determine value2 and create combinedValue
        If Not rngKey2 Is Nothing Or (colCount > 2 And rngKey1 Is Nothing) Then
            If Not rngKey2 Is Nothing Then
                value2 = rngKey2.Cells(row.row - rng.row + 1, 1).Value
            Else
                ' Use the third column for value2 only if rngKey1 is Nothing and rng has more than 2 columns
                value2 = row.Cells(1, 3).Value
            End If
            combinedValue = keyValue & sepInternal & value2
            
            If Not dict(item).Exists(combinedValue) Then
                dict(item).Add combinedValue, Array(keyValue, value2, combinedValue)
            End If
        Else
            ' If rngKey2 is Nothing and colCount <= 2, or rngKey1 is not nothing but colCount <= 2
            If Not dict(item).Exists(keyValue) Then
                dict(item).Add keyValue, Array(keyValue, "", "")
            End If
        End If
    Next row
    
    ' Prepare the output array based on the conditions
    Dim output() As Variant
    Dim outputCols As Long
    If Not rngKey2 Is Nothing Or (colCount > 2 And rngKey1 Is Nothing) Then
        outputCols = 4 ' Item, Keys, Values, Key-Value Combinations
    Else
        outputCols = 2 ' Item, Keys
    End If
    ReDim output(1 To dict.Count, 1 To outputCols)
    
    Dim k As Long: k = 1
    For Each item In dict.Keys
        Dim allKeys As String: allKeys = ""
        Dim allValues As String: allValues = ""
        Dim allCombinations As String: allCombinations = ""
        Dim disallowedPrevent As Boolean
        For Each keyValue In dict(item).Keys
            Dim details As Variant
            details = dict(item)(keyValue)
            ' If first entry or not disallowed
            If allKeys = "" Or IsError(Application.Match(details(0) & "", disallowedKeys, 0)) Then
                disallowedPrevent = False
                ' If not first entry or not disallowed
                If allKeys <> "" And Not IsError(Application.Match(allKeys, disallowedKeys, 0)) Then
                    disallowedPrevent = True
                End If
                allKeys = IIf(disallowedPrevent, "", allKeys & IIf(allKeys = "", "", sepExternal)) & details(0)
                If outputCols = 4 Then
                    allValues = IIf(disallowedPrevent, "", allValues & IIf(allValues = "", "", sepExternal)) & details(1)
                    allCombinations = IIf(disallowedPrevent, "", allCombinations & IIf(allCombinations = "", "", sepExternal)) & details(2)
                End If
            End If
        Next keyValue
        output(k, 1) = item  ' Item names
        output(k, 2) = allKeys  ' Keys
        If outputCols = 4 Then
            output(k, 3) = allValues ' Values (for ranges with more than 2 columns or when rngKey2 is specified)
            output(k, 4) = allCombinations ' Key-Value Combinations
        End If
        k = k + 1
    Next item
    
    ConcatenateKeysForItems = output
End Function

使用它将输出同步到输入 - 更改第一行以定义输入（例如 Sheet1 中的 Table1）和输出（例如 E7）：

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    Const source_rg As String = "Table1", source_sh As String = "Sheet1", target_cell As String = "E7"
    Dim tbl As ListObject
    On Error Resume Next ' In case there's no such table
    Set tbl = Sh.ListObjects(source_rg)
    On Error GoTo 0 ' Turn back on regular error handling
    
    ' Check if the changed range intersects with the table
    If Not tbl Is Nothing Then
        If Not Intersect(Target, tbl.Range) Is Nothing Then
            ' If change is within Table1, recalculate your specific cell
            ThisWorkbook.Worksheets(source_sh).Range(target_cell).Calculate
        End If
    End If
End Sub

2. Power Query

  • 优点 - 更简单，不需要启用宏
  • 警告 - 当源数据有很多行时，Excel 会崩溃（数千行已经太多了）

指示：

  1. 选择您的原始桌子并数据=>来自表/范围（在下面获取和转换数据）
  2. 右键单击最后一步并选择在之后插入步骤
  3. 将上面的公式替换为（并重命名上一步名称)：
    • 如果您只有 1 个关键列，那么：= Table.Group(#"Previous Step Name", {"Item"}, {{"CombinedKeys", each Text.Combine(List.Distinct(List.Transform(Table.Column(_, "Key"), Text.From)), ","), type text}})
    • 如果有多个键列，则：= Table.Group(#"Previous Step Name", {"Item"}, {{"CombinedKeys", each Text.Combine(List.Distinct(List.Transform(Table.ToRecords(_), each Text.From([Key1]) & " - " & Text.From([Key2]))), ","), type text}})
  4. 退出 Power Query 并选择保持

PS 如果您想过滤掉某些键，例如“0”和“-1”，请按以下步骤操作：

= Table.Group(#"Previous Step Name", {"Item"}, {{"CombinedKeys", each Text.Combine(
    let
        currentRecords = Table.ToRecords(_),
        nonZeroNonNegativeOneRecords = List.Select(currentRecords, each Text.From([Key1]) <> "0" and Text.From([Key1]) <> "-1"),
        zeroOrNegativeOneRecords = List.Select(currentRecords, each Text.From([Key1]) = "0" or Text.From([Key1]) = "-1"),
        validZeroOrNegativeOneRecords = List.Select(zeroOrNegativeOneRecords, each List.IsEmpty(List.Select(nonZeroNonNegativeOneRecords, (nr) => nr[Item] = _[Item]))),
        combinedRecords = List.Combine({nonZeroNonNegativeOneRecords, validZeroOrNegativeOneRecords}),
        combinedTexts = List.Distinct(List.Transform(combinedRecords, each Text.From([Key1]) & " - " & [Key2]))
    in
        combinedTexts, ","), type text}})

相关内容