Excel:使用 LOOKUP 或 SUMPRODUCT 代替 AverageIFS

Question

我有一个电子表格，其中包含大量数据列和所有数据旁边的结果列。我正在尝试创建一个公式，如果相邻列中的数据落在指定范围内，该公式将给出结果列的平均值。

当我想将范围放在一列数据上时，我可以使用此功能，但现在无法将范围放在多列数据上。

我的电子表格如下所示:

在另一张表中是所有数据，基本上我试图获取当 $FTSEMIB 数据在 50 到 0.19 之间且银行数据在 50 到 -15 之间时的结果平均值。

我能够通过使用这个平均值来做到这一点:

=AVERAGEIFS(Data!$BHV$10:$BHV$119,Data!$HQ$10:$HQ$119,"<"&G25,Data!$HQ$10:$HQ$119,">"&H25,Data!$TK$10:$TK$119,"<"&G26,Data!$TK$10:$TK$119,">"&H26)

但我真的想通过使用 Lookup 或 Sumproduct 函数来调整这一点，该函数在 F25 和 F26 中查找标题，然后将范围应用于它们，然后仅在适合两个范围时才对相邻结果进行平均。

我尝试使用这个数组公式来执行此操作，之前只有一个类别可以应用范围，但现在我在添加多个类别时遇到了问题

=IFERROR(AVERAGE(IF((Data!$HQ$7:$LZ$7=F$25)*(Data!$HQ$11:$LZ$119>=$H25)*(Data!$HQ$11:$LZ$119<=$G25)*(Data!$BHV$11:$BHV$119<>"")*(Data!$QK$7:$UT$7=F$26)*(Data!$QK$11:$UT$119>=$H26)*(Data!$QK$11:$UT$119<=$G26),Data!$BHV$11:$BHV$119)),"")

Answer 1

此屏幕截图显示了我尝试复制您的设置:

D 列中的公式基于以下步骤:

1. 查找类别对应的列号

   匹配(A2,F1:H1,0)

2. 返回列中的数据

   INDEX(F2:H5,0,MATCH(A2,F1:H1,0))

3. 根据返回的列计算结果的平均值

   AVERAGEIFS($I$2:$I$5,INDEX($F$2:$H$5,0,MATCH(A2,$F$1:$H$1,0)),"<"&B2,INDEX($F $2:$H$5,0,MATCH(A2,$F$1:$H$1,0)),">"&C2)

4. 现在有两列

   AVERAGEIFS($I$2:$I$5,INDEX($F$2:$H$5,0,MATCH(A2,$F$1:$H$1,0)),"<"&B2,INDEX($F $2:$H$5,0,MATCH(A2,$F$1:$H$1,0)),">"&C2, 索引($F$2:$H$5,0,MATCH(A3,$F$1:$ H$1,0)),"<"&B3,INDEX($F$2:$H$5,0,MATCH(A3,$F$1:$H$1,0)),">"&C3)