我在 Excel365 中跨行和列对动态数组数据求和时出现错误。
我有一个初始输入（不在表格中）：
A B C D E F G H I
1 data value1 value2 value3 value4 value5 value6 value7 value8
2 a 83 39 84 56 81 16 83 66
3 b 74 74 42 0 98 14 86 18
4 c 53 50 71 79 72 1 57 54
5 d 4 67 7 57 62 7 6 73
6 b 99 67 47 65 73 92 73 59
7 c 30 0 84 30 49 89 70 48
8 d 95 42 73 1 80 9 52 63
9 b 49 45 78 68 89 54 28 5
10 b 36 68 99 37 72 49 90 66
11 d 15 80 34 47 89 96 87 83
12 a 73 4 17 59 71 86 4 93
13 b 78 21 15 94 92 33 17 50
14 a 9 35 57 47 30 12 62 89
15 a 7 66 77 51 69 75 92 49
16 a 19 89 43 0 67 81 62 90
17 a 100 63 17 83 52 26 48 4
这些特定值并没有什么特别之处。在我的生产电子表格中，它是给定接口上的 MAC 地址数量，本质上是任意数据，对于此示例，我使用 RANDARRAY 函数生成这些数据，然后将其复制粘贴为值。
然后我将其转换为一系列溢出数组。
B20: "=B1:I1"
A21: "=A2:A17"
B21: "=B2:I17"
结果如下（我使用“ASCII 艺术”来显示结果...我认为它们被称为“溢出动态数组”？）
A B C D E F G H I
. --------------------------------------------------------------
20 data |value1 value2 value3 value4 value5 value6 value7 value8|
. --------------------------------------------------------------
. - --------------------------------------------------------------
21| a | | 83 39 84 56 81 16 83 66|
22| b | | 74 74 42 0 98 14 86 18|
23| c | | 53 50 71 79 72 1 57 54|
24| d | | 4 67 7 57 62 7 6 73|
25| b | | 99 67 47 65 73 92 73 59|
26| c | | 30 0 84 30 49 89 70 48|
27| d | | 95 42 73 1 80 9 52 63|
28| b | | 49 45 78 68 89 54 28 5|
29| b | | 36 68 99 37 72 49 90 66|
30| d | | 15 80 34 47 89 96 87 83|
31| a | | 73 4 17 59 71 86 4 93|
32| b | | 78 21 15 94 92 33 17 50|
33| a | | 9 35 57 47 30 12 62 89|
34| a | | 7 66 77 51 69 75 92 49|
35| a | | 19 89 43 0 67 81 62 90|
36| a | | 100 63 17 83 52 26 48 4|
. - --------------------------------------------------------------
在我的生产电子表格中，我在多张工作表中进行了一些数据处理，但我重新创建了这个示例，即使使用这些简单的表达式，也遇到了同样的问题
所以我的问题-我应用了新功能：
B40: "=B20#"
A41: "=UNIQUE(A21#)"
B41: "=SUMIFS(B21:B36,A21#,A41#)"
哪个工作正常：
<code>
. A B C D E F G H I
. --------------------------------------------------------------
40 data |value1 value2 value3 value4 value5 value6 value7 value8|
. --------------------------------------------------------------
. - ------
41| a | | 291|
42| b | | 336|
43| c | | 83|
44| d | | 114|
. - ------
但是如果我将 B41 更改为"=SUMIFS(B21#,A21#,A41#)"或，"=SUMIFS(B21#,A21#,A41#,B20#,A40#)"我会在所有溢出数组单元格表上得到 #VALUE! 的结果（尽管溢出数组大小符合预期）
我最终寻找的结果是有效的 2-D sumifs 在单元格 B41 中产生单个公式，并使用溢出数组填充所有行/列 {a} - {d} 和值 1-8：
A B C D E F G H I
. --------------------------------------------------------------
40 data |value1 value2 value3 value4 value5 value6 value7 value8|
. --------------------------------------------------------------
. - --------------------------------------------------------------
41| a | | 291 296 295 296 370 296 351 391|
42| b | | 336 275 281 264 424 242 294 198|
43| c | | 83 50 155 109 121 90 127 102|
44| d | | 114 189 114 105 231 112 145 219|
. - --------------------------------------------------------------
我意识到我可以简单地执行少量 sumif 并使我的样本数据集变得更容易，但我的生产数据集每张表和多张表有超过 2600 万个数据点，处理起来需要几个小时。我发现动态溢出数组更省时，即使没有其他原因，也只是因为更少的 RAM，即更少的交换文件使用量，以及可能更好的 L2 缓存性能。如果有一个不需要 2 小时才能关闭的工作簿就好了。
答案1
解决方案：单个数组公式中的多个 SUMIF
这相当于 2D SUMIF 给出数组答案
=MMULT(--(TRANSPOSE(A21#)=A41#),B21#)
怎么运行的：（这个解释比我希望的要长得多！）
MMULT 执行矩阵乘法，是 Excel 中为数不多的可以在单个数组公式中更改数组长度（和宽度）的方法之一。你想要将大型 2D 表缩小为较小的 2D 表这一事实已经暗示我 MMULTI 可能是可行的方法。
理解矩阵乘法的具体工作原理超出了这篇文章的范围，但你可以阅读更多内容这里。了解尺寸如何变化很重要。如果C = AB，矩阵A为墨西哥（行 x 列），则 B 必须有n行匹配 An列。如果 B 是恩智浦，则 C 的输出维度为mxn.nxp=mxp。
如果 A 是水平/行数组1 xnB 是垂直/列阵列nx 1，AB 的答案将是1 x 1在维度上，即单个标量值。这本质上与 Excel 中的点积或 SUMPRODUCT（a1 * b1 + a2 * b2 + ... + an * bn）相同，唯一的区别是 SUMPRODUCT 要求数组要么都是水平的，要么都是垂直的。如果您现在在 2D 中堆叠 B，即恩智浦，那么 MMULT(A, B) 实际上是在做页SUMPRODUCTs 一个接一个地输出尺寸1 经验值。
从 SUMIF 到 MMULT
任何 SUMIF 都可以表示为 SUMPRODUCT。该公式=SUMIF(A1:A10,"yes",B1:B10)相当于=SUMPRODUCT(--(A1:A10="yes"), B1:B10)--只是将布尔值 TRUE/FALSE 转换为数值 1/0。--(A1:A10="yes")变为 0 和 1 的掩码数组，选择将 B 中的哪些条目相加。同样，SUMIF/SUMPRODUCT 可以用 MMULT 完成，只要注意数组方向即可=MMULT(--(TRANSPOSE(A1:A10)="yes"), B1:B10)。
不幸的是，SUMIF 和 SUMPRODUCT 都不能扩展到二维，=SUMIF(A1:A10,"yes",B1:F10)即不是工作，无法提供 B11:F11 中的选择性总和数组。MMULT 来救援：
=MMULT(--(TRANSPOSE(A1:A10)="yes"), B1:F10)
就尺寸而言1 x 10 . 10 x 5 = 1 x 5. B1:F10 的每一列都通过相同的掩码数组进行矩阵乘法/“和积”。
最后，在令人费解的数学运算的最后一步，想象一下您想要总结上面的“是”结果和下面的“否”结果。您可以分成两行来做：
=MMULT(--(TRANSPOSE(A1:A10)="yes"), B1:F10)
=MMULT(--(TRANSPOSE(A1:A10)="no"), B1:F10)
但为什么不呢
=MMULT(--(TRANSPOSE(A1:A10)={"yes"; "no"}), B1:F10)
或者与你的具体情况相比
=MMULT(--(TRANSPOSE(A1:A10)=UNIQUE(A1:A10)), B1:F10)
就尺寸而言2 x 10.10 x 5 = 2 x 5。
要了解 Excel 如何处理不同大小的矩阵比较，单独测试可能会有所帮助=TRANSPOSE(A1:A10)={"yes"; "no"}。去掉“;no”。测试。替换为“,no”并测试。注意“;”是行分隔符，“,”是列分隔符（至少对于我的本地化设置而言）。
从 SUMIFS 到 MMULT
实现具有多个条件的数组输出 SUMIFS
=MMULT(TRANSPOSE(BITAND((A1:A10={"yes","no"}),(G1:G10="active"))),B1:F10)
关于 SUMIF 与 SUMPRODUCT/MMULT 效率的最后说明
如图所示，这些函数可以为 1D 情况提供等效答案。值得注意的是，SUMIF 可能更高效（使用更少的内存并且没有乘法指令）。如果条件为 FALSE，SUMIF 不执行任何操作，而 SUMPRODUCT 仍会与零相乘并将结果添加到总数中。现在比较多个 SUMIF（每个都在自己的单元格中）与 MMULT 并不那么容易。虽然 MMULTI 需要墨西哥内存来存储掩码和陣容额外的乘法，每个 SUMIF 将重复与条件比较mxnx (p-1)更多单元格读取和（慢速文本？）比较。速度比较会很好，但我更看好 MMULT。
答案2
抽象的
我没有办法改进@Mobus 的回答，我甚至不会尝试。
但是，我认为这个额外的努力会引起其他偶然发现这个帖子的人的兴趣。不确定是否值得了解我的实际生产数据，但不管怎样，页面空间很便宜。 如果你只想看我的最终结果，请向下滚动，包括截图。
介绍
我正在尝试使用运行 IOS XE 17.6 的 Catalyst 3850 和 9300 交换机为我的组织开发 IPv6 第一跳安全功能。IOS XE 使用所谓的“设备跟踪”，但关键是交换机必须跟踪少于 1000 个 IP，否则交换机会变得不稳定（没有记录任何地方除了 Cisco TAC 案例 634280103）。因此，如果我假设 IPv6 链路范围 (FE80::)、IPv6 全局范围 (2000::) 和 IPv4 地址都使用相同 MAC 地址的设备跟踪条目，那么使用设备跟踪功能我最多只能跟踪 333 个 MAC 地址。
在我的组织中，这会限制我什么？
假设：要么
- “面向客户端的接口，但没有网络模块上的接口”，或
- “客户端 VLAN 但不是管理 VLAN。”
方法
绘制图表。统计上有意义的时间段内所有交换机上的所有接口和 VLAN（所有安全功能至少都是基于接口的，有些具有基于 VLAN 的替代方法）。这意味着我需要一个 3D 图表，设备接口 x 时间戳，z 轴上有 #MAC。但我需要数据，也需要 Excel 的绘图功能。（是的，我知道 Matlab 会更好，但我已经自学了足够多的东西才能走到这一步）。由于我想看看各种选项在哪里中断，我需要看到高端极值，这样我还可以绘制最大阈值平面并查看超过我的最大阈值的数据点。
我编写了一些 Python 脚本来集成一些不同的管理服务器，以帮助完成此任务。这生成了两个 CSV 文件：一个记录每个交换机每个 VLAN 的 MAC 地址数量，另一个记录每个交换机每个接口的 MAC 地址 - 两者均以 15 分钟为间隔，持续一个月。任何为零的条目都会被转储，但这仍然导致接口 CSV 文件中有 2600 万行（VLAN 较小 - 只有 800 万行）。
回到 Python，我将其转换为两个带有标题行（VLAN 实际上相同）的新 CSV 文件：device_interface、device、model、interface、time1、time2、time3、time4、time5.....
这样就产生了大约 31600 条接口线和大约 13000 条 VLAN 线，均带有大约 2600 个时间戳。太棒了！我现在可以在 Excel 中打开它们了。
两个有趣的点：（1）我记录了收集整个电池数据的开始时间的时间戳，以及收集单个开关数据的时间戳；（2）一些数据收集电池不完整，但最终收集了足够的数据，从统计上使第二个问题消失（“用零替换，丢失的数据无关紧要”）。这使得 switch_interfaceXtime(n) 交集看起来像：x&excel-formatted-timestamp&MACs（x =“C”表示干净，“D”表示脏）。示例：C&44612.59375&27
好的，太棒了，包括数据中的所有空白，我现在已经在两个工作表中加载了大约 1 亿个数据点。这就是我遇到问题的地方，试图处理这么多数据。
我认为我原来的解决方案 (抱歉，我不再有它了) 会起作用，但是在它消耗了 60GB 的虚拟内存后我停止了该计算，并且我计算了完成时间并发现它将花费 3 年时间。我也感到沮丧，因为我所做的任何事情都需要很长时间才能应用 (通常需要几分钟)。回到绘图板，这产生了@Mobus 出色回答的问题。事实证明我原来的解决方案存在很多问题，请参阅我在@Mobus 回答下的评论，但很大一部分我使用 C/D 来应用条件格式并且我使用了函数OFFSET，它们都是易失性的，我认为这使我的解决方案达到 O(n^2) 或 1 亿 X 1 亿次计算，甚至可能更大 (O(n^3)？O(n^4)？)。
不幸的是，@Mobus 的解决方案在我正在处理的规模下不起作用。它在 13,000 行 VLAN 工作表上工作得很好，但 Excel 甚至懒得尝试 31,000 行接口工作表（一些对话框显示资源不足，无法完成操作）。
我尝试重新执行一些 mmult 函数和 sumproducts 等，看看是否可以得到溢出的结果，然后将所有内容塞入输出范围的左上角单元格中，但遇到了一些问题：
- 我的第一次尝试基本上是对你建议的重新讨论（直到后来我才意识到这一点），并且由于资源耗尽，它立即拒绝计算。
- 我的第二次尝试是看看我是否可以使用点积或叉积。我想看看我是否可以成功处理单个单元格，然后扩展到溢出数组。我思考我可能已经完成了，但尝试它消耗了太多内存，导致系统冻结。有一次我看到它在我的 16GB 机器上占用了 60GB，所以它至少有那么高。
在研究 sumproduct 公式时，我了解了 excel 的数组乘法机制（@Mobus mmult 解决方案中使用了该机制，但直到我做了这项工作，我才明白其基本原理）。这终于奏效了，尽管我的解决方案只计算了一个单元格，但我需要将其复制/粘贴到所有数百万个单元格中。复制/粘贴很容易，至少我只需要做一次（对吧……???）。
讨论
每次复制/粘贴到整个工作表都要花 5 分钟或 20 分钟，即使只复制一行。我没有试图找出差异背后的“原因”，尽管我认为这与页面交换有关。
计算（复制后）花了大半天的时间，根据各种观察，我估计每张工作表需要 9-10 个小时，我的 4x CPU 内核在两个超线程管道上的负载均为 90%，以 90% 的最大频率运行，几乎全部在 Linux 所谓的“用户空间”中运行。刚打开时，最终产品为 18.4GB。
我正在进行绘图方面的后续活动；我认为它不一定与引发这个话题的问题相关。
现在打开包含所有 9x 工作表的文件需要 45 分钟，保存和关闭每个文件现在需要大约 10-15 分钟；这比以前花费的几个小时要少得多。使用我的第一种方法：打开、保存或关闭至少需要 2 小时，只有 4x 工作表；我甚至从来没有尝试使用我的第一种方法来完成所有 9x 工作表。
公式可能可以改进以提高可读性，但我不想花上几个小时来验证。也许我可以使用sequence()来改进它，以创建正确大小的网格，然后……之后就不确定了。
无论如何，这些公式的优点在于它们 100% 非易失性。我在一些地方使用了 unique、sort 和 filter，但我认为它们也是非易失性的，因此进一步的研发不会受到影响。
结果
上图：截图显示了刚打开的文件的内存使用情况。
“...（原始）”标签：没有公式；这是严格的原始数据。
“...（精简）”选项卡：VLAN 工作表上的公式“非常相似”。
A1: ='Interface Data (Raw)'!A1
A2: =LET(x,'Interface Data (Raw)'!A:A,FILTER(x,(x>0)*(x<>A1)))
B1: ='Interface Data (Raw)'!B1
B2: =LET(x,'Interface Data (Raw)'!B:B,FILTER(x,(x>0)*(x<>B1)))
C1: ='Interface Data (Raw)'!D1
C2: =LET(x,'Interface Data (Raw)'!D:D,FILTER(x,(x>0)*(x<>C1)))
D1: =LET(x,'Interface Data (Raw)'!1:1,FILTER(x,LEFT(x,1)="4"))
D2: =LET(x,'Interface Data (Raw)'!E2:E31634,IF(x="",0,RIGHT(x,LEN(x)-FIND("&",x,5))))
D2 导致数组溢出，1 列的行数与 A、B 和 C 列的行数相等。D2 一直复制/粘贴到单元格 CTO2
A1: same as above
A2: same as above
A2: =UNIQUE('Interface Data (Streamlined)'!B2#)
B2: =SUM((--('Interface Data (Streamlined)'!$B$2#=$A2))*'Interface Data (Streamlined)'!D$2#)
B2 生成单个单元格，必须将其一直复制/粘贴到单元格 CTM1783
您通过无聊的细胞了解了这个想法。
B2 (upper): =SUM((--('Interface Data (Streamlined)'!$B$2#=$A2))*'Interface Data (Streamlined)'!D$2#*(NOT(RIGHT('Interface Data (Streamlined)'!C$2#,4)="/1/1")))
B2 (lower): =SUM((--('Interface Data (Streamlined)'!$B$2#=$A2))*'Interface Data (Streamlined)'!D$2#*(NOT(LEFT(RIGHT('Interface Data (Streamlined)'!$C$2#,4),3)<>"/1/")))
B2 也导致出现单个单元格，必须将其一直复制/粘贴到单元格 CTM1783
B3、B4 和 C3 并没有什么有趣的东西，上面没有显示出来。
A4: =--ISERROR(MATCH('VLAN Data (Streamlined)'!C2#,E2#,))
G1: =SEQUENCE(,1000,2000)
E2: =E1:ALR1
C4: =SUM(--('VLAN Data (Streamlined)'!$B$2#=$B4)*$A$4#*'VLAN Data (Streamlined)'!D$2#)
E2 是必需的，因为我不确定如何才能获得数组 {1,8,2000,2001...2999}，而且我忘了我可以直接开始输入“2000”和“2001”，然后拖动剩下的部分。哦，算了。
C4 也产生了一个单元格，必须将其一直复制/粘贴到单元格 CTN1785。有趣的是，预先计算到 A4 并没有明显加快整体计算速度。