☰

具有多个标题的 Excel 嵌套查找函数

tag-icon • tag-icon microsoft-excel
具有多个标题的 Excel 嵌套查找函数

我希望这是发布 Excel 相关问题的正确渠道。

我目前陷入在 Excel 电子表格中执行查找功能的问题。

我拥有的数据结构如下：

在此处输入图片描述

我想创建一个查找函数，在其中指定：标题的名称、子标题的类型和行名称。它应该返回行名称前面的值。例如，如果我查找标题一、类型 A、val1-A，它应该返回 1。

有什么简单的方法可以完成这项任务吗？

我将在下面附上示例电子表格 https://we.tl/t-MOnNSGroRG

答案1

我设想数据从 A1 开始，其中“标题 1”位于范围的八列中央，或者这些单元格合并。看起来像是“标题 xxx”分组之间的两条线。因此每个分组有 15 行。

这将查找变量放在 K2:K4 中，并将结果放在 K5 中。

我还想象您给出的图片为了简化问题而被“简化”，并且实际上需要查找所有三个变量，而不仅仅是执行一堆嵌套的IF或一个漂亮的IFS。 考虑到“标题 xxx”术语对于这些标题行是唯一的，因为在 A 列的其他地方不共享。列对标题（“ABC”）的想法相同，并且它们实际上可以是任何东西，再次简化，但它们位于每对的最左边的列中（因此在 A2 中为“标题一”中的 A/B 列对（在选择中居中或合并）。

因此，所有变量确实需要查找，并且可以是任何东西，或多或少，所以不需要简化上述假设。

该公式在五个这样的组上进行了测试，因此其范围是所示的 A1:H75。由于它位于第一行LET，因此可以轻松访问并进行更改。

至于LET，如果您没有该函数，那么将各个部分替换到位是没有问题的。如果有，您可能希望为查找变量命名，也许在第二行，这样可以轻松更改查找表地址。也许不是，因为人们多久会将查找表带到其他地方？不过，我建议将 中另一个第 1 行的名称LET“HeaderRange”从显式范围更改为其前面的范围名称的单个列，INDEX以便它在范围名称调整时自动更改。

公式：

=LET(Range,  A1:H75,  HeaderRange,  A1:A75,
      HeaderRow,  MATCH($K$2,HeaderRange,0),
      valColumn,  MATCH($K$3,INDEX(Range,1+HeaderRow,SEQUENCE(COLUMNS(Range))),0),
      val,  INDEX(Range,SEQUENCE(11,1,HeaderRow+2,1),valColumn),
      values,  INDEX(Range,SEQUENCE(11,1,HeaderRow+2,1),valColumn+1),

      XLOOKUP($K$4,val,values)
     )

基本上，它使用MATCH数据范围最左边的列来定位“Header”变量所在的行。在该列上加 1 可得到“Type”变量所在的行。MATCH找到“val”变量所在的列并返回该列加上该列上的下一列。然后XLOOKUP查找与“val”变量关联的值并返回该值。

该公式不进行任何错误检查，其中最令人担忧的是，在同一个“Header xxx”部分中存在非唯一“val”标签，这超出了常见的拼写错误等。因此，如果该列中有两个或更多“val2-A”条目，它将无法捕获它们。如果可能存在，并且这不是需要纠正的错误，并且返回所有条目很重要，则FILTER可以在最后一步中使用。如果只需要一个，不一定是第一个或最后一个，而是其中的MAX，但无论哪个，人们都会根据需要继续包装，FILTER直到只返回一个。

相关内容