如果列总和不为零，则将列标题返回为数组（无空白行）

Question

我有一张表格，其中列有制造各种产品所需的组件。此表格会自动填充为另一张表格的输出数据。

对于这个例子，我们将使用这个：

S 小部件	M 小部件	L 小部件	XL 小部件	XXL 小部件
0	2	0	4	5
0	6	0	4	3
0	0	0	3	9
0	4	0	0	7
0	1	0	0	5

理想情况下，输出应如下所示：

成分	全部的
M 小部件	十三
XL 小部件	11
XXL 小部件	二十九

获取列 >0 且无间隙的标题列表的最佳方法是什么？我知道在完成组件列后如何完成总计列，但我在公式组件列上画了空白。

帮助？

Answer 1

使用 Office 365 我们可以利用 MMULT 和 FILTER 来返回所需的：

=LET(
    ttl,    A1:E1,
    val,    A2:E6,
    mmlt,   MMULT(TRANSPOSE(ROW(val)^0),val),
    CHOOSE({1,2},TRANSPOSE(FILTER(ttl,mmlt>0)),TRANSPOSE(FILTER(mmlt,mmlt>0))))

基本上MMULT(TRANSPOSE(ROW(val)^0),val)返回每列总和的水平数组。

然后首先使用过滤器对标题进行过滤，仅过滤总和大于 0 的标题。

然后我们在同一个过滤器上过滤总和的垂直数组。

我们使用 CHOOSE 来并排返回两者。

这将使用动态数组并将溢出一个公式的结果。

---

如果没有 Office 365，我们需要使用 INDEX/AGGREGATE：

=IFERROR(INDEX($A$1:$E$1,AGGREGATE(15,7,(COLUMN(A1:E1)-MIN(COLUMN(A1:E1))+1)/(MMULT(TRANSPOSE(ROW($A$2:$E$6)^0),$A$2:$E$6)>0),ROW($A1))),"")

并复制下来以获取列表。

我们仍然使用 MMULT 返回列总和的水平数组，但我们使用 AGGREGATE 返回总和大于 0 的每列的相对列号。

当我们将其复制下来时，它会返回第一个，然后是第二个，然后是第三个。

然后我们使用带有 INDEX 的 SUM 返回正确的列：

=IF(M2<>"",SUM(INDEX($A$2:$E$6,0,MATCH(M2,$A$1:$E$1,0))),"")

INDEX/MATCH 返回列标题与第一个公式的值匹配的完整列。