☰

Excel Lambda 函数中具有结构化引用（无 INDIRECT）的动态表名称

tag-icon • tag-icon microsoft-excel worksheet-function
Excel Lambda 函数中具有结构化引用（无 INDIRECT）的动态表名称

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

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

多变的 初始值 初始单位
压力 1 自动取款机
体积 4 大号
大量的 10 G
温度 三十 C
气 0.7 大气压*升/克*摄氏度

我希望能够定义一个 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

答案1

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

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

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

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

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

在此处输入图片描述

答案2

无需使用 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")

答案3

使用非易失性方法将索引编入不同的表格或表单

使用文本索引到不同表的唯一方法多变的是根据问题使用 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)))

在哪里变量是表格第一列中的查找值，并且支柱是匹配的标题列。

如果您需要特定的表列，您可以使用多个变量扩展 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))

在哪里工作表名称工作表名称是否为文本值，我和杰是行和列索引。

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

相关内容