主页 / computer / 问题 / 1713772
Accepted
Preston Maness
Asked: 2022-03-31 21:54:39 +0800 CST

Excel Lambda 函数中的动态表名称，具有无间接的结构化引用

  • 772

是否可以定义一个 excel lambda 函数，该函数接受一个参数作为表的“动态”名称，然后在结构化引用中使用，而不使用 INDIRECT 函数？
我希望能够定义一个帮助方法，允许用户实际上使用一个表来定义一个字典结构，其中一个字符串作为键，一个元组作为值，然后，挑选出特定的部分元组。

下面是一个名为“变量”的示例表：

多变的 初始值 初始单位
压力 1 自动取款机
体积 4 大号
大量的 10 G
温度 30 C
气体 0.7 大气压*L/g*C

我想要的行为是能够定义一个用户可以调用的 lambda，如下所示：

=l.Variable("Variables","mass","Initial Value")

（结果是10）

到目前为止，我只能通过使用以下INDIRECT函数来实现这个特定的界面：

=LAMBDA(Table,Var,Prop,
    INDEX(
        INDIRECT(Table & "[#All]"),
        MATCH(Var,INDIRECT(Table & "[[#All],[Variable]]"),0),
        MATCH(Prop,INDIRECT(Table & "[#Headers]"),0)
    )
)

如果我想避免使用INDIRECT，我必须硬编码特定的表名：

=LAMBDA(Var,Prop,
    INDEX(
        Variables[#All],
        MATCH(Var,Variables[[#All],[Variable]],0),
        MATCH(Prop,Variables[#Headers],0)
    )
)

我运气不好？有没有更好的方法来做我想做的事情？

我已链接到使用硬编码方法和INDIRECT以下方法的工作簿：

https://www.dropbox.com/s/344rfrrzhjjr6z2/superuser-lambda-question.xlsx?dl=0

microsoft-excel worksheet-function

3 个回答

  1. 您可以在没有 INDIRECT 的情况下相当容易地从表中查找任意数据。您只需使用 [#All] 限定符而不只是将表名作为字符串传递完整的表引用，然后使用 INDEX 自己提取 [#Header] 和 [#Data] 部分。

    我已将其分解为多个 lambda，这就是我自己实现它的方式。我还包括了一些错误检查。

    您可以根据需要进行调整，以完全按照您的意愿工作。

    getTableColumn = lambda(tableall, colname, let(
   header, index(tableall, 1, 0),
   data, let(rcount, rows(tableall), index(tableall, 2, 0):index(tableall, rcount, 0)),
   colIndex, xmatch(colname, header),
   if(isnumber(colIndex), index(data, 0, colIndex), "column not found")));

TableLookup = lambda(tableAll, keyColName, dataColName, keyVal, let(
   keyCol, getTableColumn(tableAll, keyColName),
   dataCol, getTableColumn(tableAll, dataColName),
   keyFound, index(keyCol, 1)<>"column not found",
   dataFound, index(dataCol, 1)<>"column not found",
   ifs(
       not(keyFound), "key column not found",
       not(dataFound), "data column not found",
       and(keyFound, dataFound), xlookup(keyVal, keyCol, dataCol, "key not found"))
    ));

    因此，将此 lambda 用于您的原始示例：

    =TableLookup(Variable[#All], "Variable", "Initial Value", "mass")
    • 2
  2. Best Answer

    接受一个作为表的“动态”名称的参数

    Excel 中将文本参数转换为范围的最佳方法是使用 INDIRECT 函数。
    （其他方法是 OFFSET 和自定义 VBA 函数，但在你的情况下两者都会更复杂）

    但是，您仍然可以使用 FILTER 而不是 INDEX 和 MATCH 来改进您的公式。

    下面的示例仅显示了您所需公式的一部分，因为我的 Excel 还没有 LAMBDA。

    =FILTER(INDIRECT(E1&"["&G1&"]"),INDIRECT(E1&"[Variable]")=F1)

    在此处输入图像描述

    • 1

  3. 将 INDEX 索引到不同的表格或表格中的非易失方法

    使用文本变量索引到不同表的唯一方法是根据问题使用 INDIRECT。避免 INDIRECT 的原因当然是因为它是易变的，这意味着它会在每一个可能的机会重新计算（INDIRECT 可以引用任何单元格，因此依赖于每个单元格）。易失性函数会影响性能，尤其是对于大型工作簿。

    对于有限数量的预定义表格或工作表，您可以使用 CHOOSE 和 LET（LET 需要 Excel 365）。对于表：

    =LET(tableall,CHOOSE(MATCH(table,{"Table1","Table2","Table3"},0),Table1[#All],Table2[#All],Table3[#All]),INDEX(tableall,MATCH(var,INDEX(tableall,,1),0),MATCH(prop,INDEX(tableall,1,),0)))

    其中var是表第一列中的查找值，prop是匹配的标题列。

    如果您需要特定的表列，您可以使用多个变量扩展 LET，例如：

    =LET(tableindex,MATCH(table,{"Table1","Table2","Table3"},0),tableall,CHOOSE(tableindex,Table1[#All],Table2[#All],Table3[#All]),tablehdr,CHOOSE(tableindex,Table1[#Headers],Table2[#Headers],Table3[#Headers]),INDEX(tableall,MATCH(var,INDEX(tableall,,1),0),MATCH(prop,tablehdr,0)))

    或者，如果您只需要 VLOOKUP 而不是完整的 INDEX：

    =LET(tableall,CHOOSE(MATCH(table,{"Table1","Table2","Table3"},0),Table1[#All],Table2[#All],Table3[#All]),INDEX(tableall,VLOOKUP(var,tableall,MATCH(prop,INDEX(tableall,1,),0),FALSE))

    最后，一旦 LAMDA 进入 Excel 365 的生产版本，您基本上可以将上述定义为命名范围函数并使用参数调用它，例如=TABLEINDEX("Table1","Mass","Initial Value")

    作为仅供参考，您可以执行相同操作以非易失性方式动态处理不同的表格：

    =LET(sheet,CHOOSE(MATCH(sheetname,{"Sheet1","Sheet2","Sheet3"},0),Sheet1!$1:$10000,Sheet2!$1:$10000,Sheet3!$1:$10000),INDEX(sheet, i, j))

    其中sheetname是作为文本值的 sheetname，i和j是行和列索引。

    PS。如果您没有 Excel 365，则上述所有操作都可以在没有 LET 的情况下完成，您只需要替换/复制主公式中每个命名 LET 变量的公式（计算量更大）。

    • 1

相关问题