主页 / coding / 问题 / 79102111
Accepted
Miaka3
Asked: 2024-10-18 20:36:24 +0800 CST

Excel 公式：根据最小值过滤组

  • 772

Excel 公式

最好使用 Excel（非 VBA）数组公式或类似公式，由于数据的大小和数量，我需要减少优化性能所需的最少资源（每列超过 200,000 条记录）。

我在 A 列（辅助：排名）、B 列（国家）、C 列（顺序）和 D 列（名称）中有一组数据，我想根据唯一的“名称”及其最低匹配值（最小值）“顺序”对组进行过滤，以仅返回具有最小/最小值的组的国家、顺序和名称。

通过使用辅助列 A（排名），我能够通过在列 A（排名）等于“1”的值时过滤 B2:D13 来实现我想要的结果。不幸的是，它需要大量的 CPU、内存和线程，因此需要更长的时间来处理数据。请参阅下面的公式和当前输出结果的示例。

有没有办法将 A 列（排名）嵌套或合并到 F 列（结果）UNIQUE(FORMULA 函数中？

Rank by Group FORMULA (A): =SUMPRODUCT((D2=$D$2:$D$13)*(C2>$C$2:$C$13))+1
Desired Output FORMULA (F): =UNIQUE(FILTER(B2:D13,A2:A13=1)

在此处输入图片描述

按组名升序显示范围 (B2:C13) 中的所有值，然后按顺序 (1-10) 显示。

在此处输入图片描述

excel

1 个回答

  1. Best Answer

    这里有两种使用 Excel 公式的方法MS365，Power Query我最好使用后者。

    在此处输入图片描述

    • 单元格中使用的公式F1

    =LET(a, D2:D13, b, C2:C13, VSTACK(B1:D1,FILTER(B2:D13,MINIFS(b,a,a)=b)))

    或者Power Query将范围转换为结构化引用（又名表格）后，在空白查询的高级编辑器中添加以下内容：

    在此处输入图片描述

    let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    GroupBy = Table.Group(Source, {"Name"}, {{"Order_Min", each List.Min([Order]), type number}, {"All", each _, type table [Country=text, Order=number, Name=text]}}),
    Expand = Table.ExpandTableColumn(GroupBy, "All", {"Country", "Order"}, {"Country", "Order"}),
    Conditions = Table.AddColumn(Expand, "Custom", each if [Order_Min] = [Order] then 1 else null),
    FilterRows = Table.SelectRows(Conditions, each [Custom] = 1),
    RemoveCols = Table.SelectColumns(FilterRows,{"Country", "Order_Min", "Name"})
in
    RemoveCols

    按照 OP 的建议进行更新：变量a给出排序后的数组。基于此也可以完成先前的输出。

    =LET(
     a, SORT(B2:D13,{3,2}),
     b, DROP(a,,2),
     c, UNIQUE(b),
     FILTER(a,1-ISNA(XMATCH(INDEX(a,,2),
     MAP(c,LAMBDA(x,MIN(FILTER(INDEX(a,,2),x=b))))))))
    • 0

相关问题