☰

根据 Excel 中另一个单元格的数据查找相应的列和行数据

tag-icon • tag-icon microsoft-excel
根据 Excel 中另一个单元格的数据查找相应的列和行数据

好的，我有一个 Excel 文件，其中包含几个表格，类似于图中和下面链接中的表格。

表格截图 在此处输入图片描述

https://www.dropbox.com/s/967glpkyfjfb6iy/Test.xlsx?dl=0

我想要做的是能够输入一个值D2：N21范围并让其返回列中单元格的对应值乙&德以及日期第 1 行并让它们显示在单元格中B26，C26&D26分别。

我试过使用指数匹配公式，但我似乎无法让它工作。我猜我遗漏了一些东西。任何帮助都将不胜感激。

答案1

这能无需使用任何 VBA 即可完成。但是，这需要的不仅仅是查找/引用函数，因为这些函数只能在一个维度上工作（大多数其他函数也是如此）。

SUMPRODUCT()是一个可以处理二维数组（只需简单比较即可生成）的函数。


解决方案仅需要以下两个公式：

公式1输入B26并按 Ctrl 键输入/填充/复制粘贴到B26:C26：

=IF(ISERROR($D26),NA(),INDEX(B:B,SUMPRODUCT(MAX(($D$2:$N$21=$A26)*(ROW($D$2:$N$21))))))

二级方程式进入D26：

=INDEX($1:$1,COLUMN($D$2:$N$21)-1+MATCH($A26,INDEX($D$2:$N$21,MAX(1,SUMPRODUCT(MAX(($D$2:$N$21=A26)*(ROW($D$2:$N$21))))-ROW($D$2:$N$21)+1),1):INDEX($D$2:$N$21,MAX(1,SUMPRODUCT(MAX(($D$2:$N$21=A26)*(ROW($D$2:$N$21))))-ROW($D$2:$N$21)+1),COLUMNS($D$2:$N$21)),0))

公式2的美化版本为：

=
INDEX(
  ($1:$1),
  COLUMN($D$2:$N$21)-1
  +MATCH(
    $A26,
    INDEX(
      $D$2:$N$21,
      MAX(1,SUMPRODUCT(MAX(($D$2:$N$21=A26)*(ROW($D$2:$N$21))))-ROW($D$2:$N$21)+1),
      1
    )
    :INDEX(
      $D$2:$N$21,
      MAX(1,SUMPRODUCT(MAX(($D$2:$N$21=A26)*(ROW($D$2:$N$21))))-ROW($D$2:$N$21)+1),
      COLUMNS($D$2:$N$21)
    ),
    0
  )
)

请注意，如果表中的美元金额是唯一的，则可以使用不太复杂的公式。由于您没有指定在发现重复项时要做什么，因此我编写了一个在发现重复项时仍然有效的最简单的公式。

此公式恰好提取最底行中包含匹配项的最左侧匹配值。可以对其进行修改以返回某些特定的替代值。

A26如果需要某些通知/操作，则可以使用以下公式来检测表中输入的值是否存在重复项：

=SUMPRODUCT(MAX((D2:N21=A26)*(ROW(D2:N21))))<>SUMPRODUCT(SUM((D2:N21=A26)*(ROW(D2:N21))))


对于唯一值的情况，MAX()当然不再需要公式 1 中的函数，简化的公式 2 将是：

=INDEX($1:$1,IFERROR(1/(1/(SUMPRODUCT(($D$2:$N$21=A26)*(COLUMN($D$2:$N$21))))),NA()))


笔记：

  • 美化的公式确实可以起作用。
  • 美化版本中的括号($1:$1)用于强制$1:$1将其保持在自己的行上。
  • 虽然我选择#N/A在找不到输入的美元金额时显示错误，但可以将其更改为其他任何内容。

答案2

我认为所有查找/引用函数都会在列或行上进行搜索。我会使用“自定义函数”，即模块中的 VBA 函数。您需要 3 个，一个用于 B26、C26 和 D26。这是 D26（日期）的一个。在 D26 中，您将有“=zDate(A26)”。模块：

Option Explicit

Function zDate$(param$)
  Dim icol&, searchRange As Range, cellRange As Range
  Set searchRange = Range("D2:N21")
  Set cellRange = searchRange.Find(param, , xlValues, xlWhole)
  icol = cellRange.Column ' column of found cell
  zDate = Cells(1, icol) ' returns date
End Function

编辑：@robinCTS 的回答真是太棒了。我打算买配套的书。我被感动了，想创作

=INDEX(A1:N21,1,MAX(SUMPRODUCT((D2:N21=A26)*(COLUMN(D2:N21)) )))

但它不检查重复项，我不明白“$bbbnn.nn”格式。在我的格式中，A26 必须包含“$”和空格。谢谢

相关内容