☰

Excel 2007：使用 INDEX 和 MATCH 进行数组公式查找仅查看第一行

tag-icon • tag-icon microsoft-excel worksheet-function
Excel 2007：使用 INDEX 和 MATCH 进行数组公式查找仅查看第一行

我有一张工作表“2011-11”，C 列中有一系列数字。每个数字都表示一种给定的货币，由 B 列中的代码标识。我想要做的是编写一个公式（在单元格 C14 中），将每个数字（来自 C4:C13）除以相关汇率，将其转换为英镑 (GBP)，然后对这些数字求和。

汇率存储在单独的工作表“GBP”中。汇率每月都会变化，因此有一个表格，其中垂直列出货币代码，水平列出月份。

我有一个公式，对于单个单元格 C4，可以正确查找汇率：

=INDEX(GBP!$A$4:$BE$42,MATCH($B4,GBP!$A$4:$A$42,0),MATCH(DATE(LEFT(A1,4),MID(A1,6,2),31),GBP!$A$3:$CE$3,1))

因此，我希望通过更改第一个中的引用范围MATCH以指向$B4:$B13，将整个内容插入SUM公式并将其作为数组公式输入，从而得到正确的答案：

{=SUM(C4:C13/INDEX(GBP!$A$4:$BE$42,MATCH($B4:$B13,GBP!$A$4:$A$42,0),MATCH(DATE(LEFT(A1,4),MID(A1,6,2),31),GBP!$A$3:$CE$3,1)))}

但是，虽然已经快完成了，但这是在将第 4 行的汇率应用到每一行。非常感谢您的帮助！可以这样做吗，还是我需要插入额外的列？

一些额外的说明：

  1. 我尝试使用SUMPRODUCT而不是SUM，但这没有什么区别

  2. A1的值为“2011-11”

答案1

我的第一个猜测部分正确，但却是错误的。

问题 1

查找值

DATE(LEFT(A1,4),MID(A1,6,2),31)

基于 2011-11 的值，因此相当于

DATE(2011,11,31)

这将给出不一致的值。对于 10 月，它给出 31Oct11。对于 11 月，它给出 1Dec11。要获得一致的值，例如任何月份的最后一天，请使用：

DATE(LEFT(A1,4),MID(A1,6,2)+1,0)

问题 2

如果你将初始公式复制到下列，你

=INDEX(GBP!$A$4:$BE$42,MATCH($B4,GBP!$A$4:$A$42,0),
 MATCH(DATE(LEFT(A1,4),MID(A1,6,2),31),GBP!$A$3:$CE$3,1))

所有副本都会得到“#VALUE！”因为 A1 变成了 B1、C1 等等。

你需要

=INDEX(GBP!$A$4:$BE$42,MATCH($B4,GBP!$A$4:$A$42,0),
 MATCH(DATE(LEFT($A$1,4),MID($A$1,6,2),31),GBP!$A$3:$CE$3,1))
                 ^ ^          ^ ^

问题 3

在你的数组公式中

=SUM(C4:C13/INDEX(GBP!$A$4:$BE$42,MATCH($B4:$B13,GBP!$A$4:$A$42,0),
 MATCH(DATE(LEFT(A1,4),MID(A1,6,2),31),GBP!$A$3:$CE$3,1)))}

您有五个范围，希望 Excel 使用其中两个作为数组。我找不到任何建议 Excel 是否足够聪明来猜出这是您想要的。

我能提供的最好方法是将 D4 设置为：

=INDEX(GBP!$A$4:$BE$42,MATCH($B4,GBP!$A$4:$A$42,0),
 MATCH(DATE(LEFT($A$1,4),MID($A$1,6,2)+1,0),GBP!$A$3:$CE$3,1))

将此公式复制到该列并使用以下命令计算总数：

{=SUM(C4:C13/D4:D13)}

但是，我是一个合格的 Excel VBA 程序员；我不是 Excel 高级用户。其他人也许可以告诉你如何实现你的目标。

相关内容