加速数组公式

Question

我有以下公式，下面我会进行解释：

{=SUM(IF(($G$1:$L$1=$O$1)*($G$2:$L$2=$O$2)*($G$3:$L$3=$O$3)*($G$4:$L$4=$O$4)*($G$5:$L$5=$O$5)*($G$6:$L$6=$O$6)*($G$7:$L$7=$O$7);G21:L21))}

这是工作表的样子：

在G-L列下，我们有一个包含所有数据的“数据库”。每个季度会累加这些列（大约每个季度30列）。因此，几年后，我们将拥有一堆数据库列（1000多列原始数据）。为了演示方便，我仅包含了其中6列。

正如您所看到的，每列都包含特定参数，介于1-7行之间，这允许识别特定的CountryCode + Project Code + Category + Fiscal Year等。这使我们能够跟踪到唯一的特定项目并检索其数据。

然后，在O列中，我们要检索值的是一个特定的项目（您可以看到1-7行与G列下的相同（我们正在检索此特定项目的值）。

这里是我们的公式，我已经附在上面了。当我按下F2时，它看起来像这样。您可以看到IF语句首先简单地检查特定列是否与列O下预定义的标准相匹配，并总结所有在行1-7之间满足所有标准的列。

现在问题来了。我们有一个包含20个项目（例如O列）的工作表，并在那里使用此数组公式来检索值。问题在于，使用这种方式检索数据需要很长时间。我们还通过VBA采用了一种原则，即迭代所有单元格，然后插入公式，计算数组单元格，然后将结果值复制并粘贴到内部（以便我们不会得到完整的数组公式表）。但仍需要很长时间才能计算（大约1分钟）。

我想知道，如果有更好的解决方案如何以已经提到的格式检索数据（这意味着我们正在尝试找到特定的标准）？也许SUMIFS会更好？或sumproduct？甚至完全不同的解决方案？

我对任何可以加快过程的建议都持开放态度。

- Robert J.

---

O列中有多少行包含此公式？ - BrakNicku

你试过标准的SUMIFS()函数了吗？=SUMIFS(G21:L21;$G$1:$L$1;$O$1,$G$2:$L$2;$O$2,$G$3:$L$3;$O$3,$G$4:$L$4;$O$4,$G$5:$L$5;$O$5,$G$6:$L$6;$O$6,$G$7:$L$7;$O$7) - Scott Craner

Answer 1

我是一名帮助翻译的助手。以下是需要翻译的内容：

大约两周前，我遇到了类似的问题。起初我使用了一个辅助列/行。辅助列是将每个列中的7个字符串连接在一起。然后只使用IF函数来检查连接后的文本是否匹配。例如，假设辅助行是样本中的第8行，则单元格G8的公式应为

=CONCATENATE(G1,"|",G2,"|",G3,"|",G4,"|",G5,"|",G6,"|",G7)

并对其余内容执行相同操作，包括O列

=CONCATENATE(O1,"|",O2,"|",O3,"|",O4,"|",O5,"|",O6,"|",O7)

然后进行HLOOKUP。

=HLOOKUP(O8,G8:L21,14,0)

在我的案例中，计算时间从10分钟减少到了几秒钟！

或者，我还发现了一种不需要辅助列的方法，再次使用数组，但是思路基本相同，

按照您的示例，在O21中的公式将是

=SUM(IF(CONCATENATE(G1:L1,G2:L2,G3:L3,G4:L4,G5:L5,G6:L6,G7:L7)=CONCATENATE(O1,O2,O3,O4,O5,O6,O7),G21:L21))

（我没有在这个公式中添加“|”分隔符，但最好这样做）

但最终我更喜欢使用辅助列的方法。

供您参考

HTH

- Rosetta

---

一定会尝试并告诉您速度测试的结果！ - Robert J.

性能从17~25秒提高到不到10秒。谢谢 - Robert J.

很高兴听到这个好消息 :) - Rosetta

Answer 2

为了提高性能，请避免多次重复相同的计算。

这样我们就可以追踪一个唯一特定的项目并检索其数据。

如果7个值的组合是唯一的，则使用数组公式（通过Ctrl+Shift+Enter确认）仅在辅助单元格（例如O15）中计算所选项目的位置。

=MATCH(1;(G1:L1=O1)*(G2:L2=O2)*(G3:L3=O3)*(G4:L4=O4)*(G5:L5=O5)*(G6:L6=O6)*(G7:L7=O7);0)

在 O21 中使用以下公式并向下拖动：

=INDEX(G21:L21;1;$O$15)

- BrakNicku