☰

Excel 公式无法与列引用一起使用

tag-icon • tag-icon microsoft-excel
Excel 公式无法与列引用一起使用

你好，

我有 7 场足球比赛的结果：

             B   D           

             Team A       
2001        0   - 12      
2002        1   - 5        
2003        1   - 11        
2004        4   - 1         
2005        1   - 6  
2006        0   - 5
2007        1   - 2

这个公式可以让我得到最好的足球成绩

=INDEX(B2:B8,SUMPRODUCT((B2:B8-D2:D8*1.01=MAX(B2:B8-D2:D8*1.01))*ROW(B2:B8))-ROW(B1))&" - "&INDEX(D2:D8,SUMPRODUCT((B2:B8-D2:D8*1.01=MAX(B2:B8-D2:D8*1.01))*ROW(B2:B8))-ROW(B1))

到目前为止，这个公式运行良好，因为结果值为 1-2。但是，由于我要添加 B8 以下的分数，因此我需要修改此公式，以便它始终引用最后一个包含数据的单元格。因此我尝试了这个：

=INDEX(B:B,SUMPRODUCT((B:B-D:D*1.01=MAX(B:B-D:D*1.01))*ROW(B:B))-ROW(B1))&" - "&INDEX(D:D,SUMPRODUCT((B:B-D:D*1.01=MAX(B:B-D:D*1.01))*ROW(B:B))-ROW(B1))

但它不起作用。我该如何修复它？

谢谢。

答案1

借用我在评论中链接的答案...打开名称管理器 (ctrl+F3) 并定义两个名称。定义range1为：

=Sheet1!$B$2:INDEX(Sheet1!$B:$B,MATCH(9.9E+307,Sheet1!$B:$B,1))

并range2作为

=Sheet1!$D$2:INDEX(Sheet1!$D:$D,MATCH(9.9E+307,Sheet1!$D:$D,1))

然后，您可以使用原始公式，并将范围替换为您刚刚创建的命名范围：

=INDEX(range1,SUMPRODUCT((range1-range2*1.01=MAX(range1-range2*1.01))*ROW(range1))-ROW(B1))&" - "&INDEX(range2,SUMPRODUCT((range1-range2*1.01=MAX(range1-range2*1.01))*ROW(range1))-ROW(B1))

奖金公式：

=INDEX(range1,MATCH(MAX(range1-range2),range1-range2,0))&" - "&INDEX(range2,MATCH(MAX(range1-range2),range1-range2,0))

注意，这是一个数组公式，必须用ctrl+Shift+Enter进行确认。

答案2

看起来不错

=INDEX(B:B, SUMPRODUCT((B:B-D:D*1.01=MAX(B:B-D:D*1.01))*ROW(B:B)))&"-"&INDEX(D:D, SUMPRODUCT((B:B-D:D*1.01=MAX(B:B-D:D*1.01))*ROW(B:B)))

如果需要排除第一个单元格这答案说最好的方法是手动指定单元格编号（例如B2:B1048576）

答案3

您是否意识到，如果您的数据中恰好有多行共享最大值，您的公式就会失败？

按照 Kyle 提供的链接中给出的设置之一来确定数据中最后使用的行，这个想法是合理的。

但是，您可能还希望考虑这种替代的、更短的构造，即使引用的某些单元格是空白的，它也能起作用，但需要注意的是，该部分的结果均不成立：

B2:B100-D2:D100*1.01

是负面的。

=LOOKUP(1,0/FREQUENCY(0,1/(1+((B2:B100-D2:D100*1.01)))),B2:B100&" - "&D2:D100)

显然，您可以修改此处引用的上行，但请确保不要将其弄得太大。此类构造（以及SUMPRODUCT，AGGREGATE以及任何需要 的设置CSE）计算传递给它们的所有单元格，无论从技术上讲是否超出这些范围内最后使用的单元格。

问候

相关内容