![根据 Excel 中另一个单元格的数据查找相应的列和行数据](https://linux22.com/image/1563167/%E6%A0%B9%E6%8D%AE%20Excel%20%E4%B8%AD%E5%8F%A6%E4%B8%80%E4%B8%AA%E5%8D%95%E5%85%83%E6%A0%BC%E7%9A%84%E6%95%B0%E6%8D%AE%E6%9F%A5%E6%89%BE%E7%9B%B8%E5%BA%94%E7%9A%84%E5%88%97%E5%92%8C%E8%A1%8C%E6%95%B0%E6%8D%AE.png)
好的,我有一个 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 必须包含“$”和空格。谢谢