excel - 将多个不连续范围合并到一个命名范围中以清理公式

Question

我正在使用一个公式，效果非常好，但我想清理它。

=SUMIF(IB02R, A56, IB02P)+SUMIF(IB03R, A56, IB03P)+SUMIF(IB04R, A56, IB04P)

IB02R、IB03R、IB04R 和 IB02P、IB03P、IB04P 是我在名称管理器中定义的范围。他们查看整行。

IB02R 查看另一张纸的 B4-ND4IB02P 查看另一张纸的 B5-ND5

等等。

原始公式如下:

=SUMIF('2014'!$A$4:$ND$4,A75,'2014'!$A$5:$ND$5)+SUMIF('2014'!$A$7:$ND$7, A75,'2014'!$A$8:$ND$8)+SUMIF('2014'!$A$10:$ND$10,A75,'2014'!$A$11:$ND$11)

我想简化它以组合所有 R 和 P，这样我就可以使用 =SUMIF(IB0234R, A56, IB0234P)，而不用使用 3 个 sumif 语句。 IB0234R 和 IB0234P 是 IB02R 中包含的范围，依此类推。

该公式查找特定单元格与 3 个不同行中的每个单元格之间的匹配项。第 4,7 和 10 行。

如果这些行中的任意位置存在匹配项，则会分别对第 5,8 和 11 行中的相应值求和。

我的两个公式都有效，但为了可读性和清晰度，我想进行简化。

这可能吗？我尝试了几种不同的方法，但都没有成功。

Answer 1

这是你的公式:

=SUMPRODUCT((CHOOSE({1;2;3},'2014'!$A$4:$ND$4,'2014'!$A$7:$ND$7,'2014'!$A $10:$ND$10)=A56)*选择({1;2;3},'2014'!$A$5:$ND$5,'2014'!$A$8:$ND$8,'2014'!$A $11:$ND$11))

这需要一些花哨的命名范围-fu才能使其可读，但这绝对是可行的。

首先，将每个部分的“第一行”组合到另一个命名范围中，如下所示:

=CHOOSE({1;2;3},IB02R,IB03R,IB04R) - 我们称之为 IB00R

现在对“第二行”执行相同的操作:

=CHOOSE({1;2;3},IB02P,IB03P,IB04P) - 我们称之为 IB00P

现在公式变为:

=SUMPRODUCT((IB00R=A56)*IB00P)

要准确了解公式的工作原理，我建议单击“公式”选项卡上的“评估公式”，然后单步执行它，并单步进出指定范围。

编辑:好吧，现在我怀疑自己 - 不确定这是否正常工作。我知道它会起作用，因为我以前做过，但下面的公式可能不太正确。我稍后会弄清楚。

编辑2:正如所写，这不起作用。然而，有一个解决这个问题的方法，但我不记得它是什么。还在摆弄它。如果我无法弄清楚，我会删除这个答案。

编辑 3:现在工作。我忘记了，为了合并不连续的行，您必须使用 CHOOSE() 而不是 INDEX() 抱歉，错误的开始。