编辑:该文档被设置为一系列仪表板。如果链接到许多图形的数据是在数据透视表中，有些只是普通的表。因为有很多图表，所以我使用一些用户选择按钮和字段来更改这些图表中显示的数据。部分用户在office 2016中打开该文件。
我找到的所有解决方案要么限于365，要么涉及创建新的数据表或列，我试图避免，因为这将意味着相当多的返工。相反，我只是使用了一组嵌套的IFS，并最终将考虑将这些特定的枢轴更改为带有索引查找的常规表，以使实际数据能够在多个列中。
我目前使用SUBTOTAL函数来求和，计数或平均范围内的一堆单元格。我以前是手动过滤范围，所以我只合计我想要的行，但是需要能够一次查看几个标准。例如，在下面的例子中，我之前手动过滤范围，只包括"apple";但是现在我需要能够将"苹果"，"橘子"，"香蕉"加起来。分别，同时。
小计字段用于图表，我有一个单元格(F5)，其中包含一个与SUM、COUNT或Average(9,2或1)相对应的数字，用于"汇总表"中的小计公式。它链接到工作簿中的其他功能，我需要仍然能够保留该功能。
表格设置示例:
原始数据
| 产品类型 | 销售数量 | 日期 | 苹果 | 4 | 1/9/21 | 橙色
|---|---|---|
| 3 | 6/9/21 | |
| 2 | 10/9/21 | |
| 6 | 14/9/21 | |
| 6 | 20/9/21 | |
| 5 | 29/9/21 |
下面是一个在excel 365中创建动态汇总表的公式。如果您使用的是早期版本的excel，则公式将有所不同，并且必须手动添加或删除行。我猜您的桌子是Data_Table。
=LET(
Column_Product, Data_Table[Product Type],
Column_QTY, Data_Table[Estimated],
Column_Date, Data_Table[Date]
Column_Key, Column_Product,
Column_Filter1, Column_QTY,
Column_Filter2, Column_Date,
List_Filter1, UNIQUE(Column_Product),
List_Filter2, 1,
Categories, SORT(UNIQUE(Column_Key)),
Array_BoolKey, (TRANSPOSE(Column_Key)=Categories)+0,
Mask1, TRANSPOSE(ISNUMBER(XMATCH(Column_Filter1,List_Filter1))),
Mask2, TRANSPOSE(Column_Filter2>List_Filter2),
Array_BoolMasked, Array_BoolKey*Mask1*Mask2,
Masked_QTY, IFERROR(Array_BoolMasked*TRANSPOSE(Column_QTY),0),
Masked_Date, IFERROR(Array_BoolMasked*TRANSPOSE(Column_Date),0),
Array_Ones, SEQUENCE(COLUMNS(Array_BoolMasked),1,1,0),
Months, DATEDIF(MIN(Column_Date),MAX(Column_Date),"M"),
Body_Count, MMULT(Array_BoolMasked, Array_Ones),
Body_Sum_QTY, MMULT(Masked_FtModeled, Array_Ones),
Body_Average_PerSale, Body_Sum_QTY/Body_Count,
Body_Sum_QTY_PerMo, MMULT(Masked_FtModeled, Array_Ones)/Months,
Total_Count, IFERROR(SUM(Body_Count_Lines),"-"),
Total_QTY_PerMo, IFERROR(SUM(Body_Sum_QTY)/Months,"-"),
Total_Average_PerSale, IFERROR(SUM(Body_Sum_QTY)/Total_Count,"-"),
Array_Seq, {1,2,3,4,5},
Array_Header, CHOOSE(Array_Seq, "Product Type", "Sales Per Month", "Average QTY per Sale", "# Sales Transactions"),
Array_Body, CHOOSE(Array_Seq, Categories, Body_Sum_QTY_PerMo, Body_Average_PerSale, Body_Count),
Array_Total, CHOOSE(Array_Seq, "Total", Total_QTY_PerMo, Total_Average_PerSale, Total_Count),
Range1,Array_Header,
Range2,Array_Body,
Range3,Array_Total,
Rows1,ROWS(Range1), Rows2,ROWS(Range2), Rows3,ROWS(Range3), Cols1,COLUMNS(Range1),
RowIndex, SEQUENCE(Rows1 + Rows2 + Rows3), ColIndex,SEQUENCE(1, Cols1),
RangeTable,IF(
RowIndex<=Rows1,
INDEX(Range1,RowIndex,ColIndex),
IF(RowIndex<=Rows1+Rows2,
INDEX(Range2,RowIndex-Rows1,ColIndex),
INDEX(Range3,RowIndex-Rows1-Rows2,ColIndex)
)),
Return, RangeTable,
Return
)
我一般地写这个，所以你可以添加过滤器只针对某些产品，或最小数量，或日期范围，或其他条件。上面，我设置了过滤器来传递所有内容。
解决方案:
为了避免在工作表中依赖于此字段的许多其他表，并且当一些用户使用较旧(非365)版本的excel打开此文件时，我选择了一系列嵌套的IF (CountIF, SumIF, AverageIF)语句来代替。