excel-formula - Excel - 小计,但仅在满足特定条件时(SUMIF/AVERAGEIF)
问题描述
编辑:该文档的设置就像一系列仪表板。有些如果链接到许多图表的数据在数据透视表中,有些只是常规表。因为图表太多,我使用一些用户选择按钮和字段来更改这些图表中显示的数据。一些用户在 Office 2016 中打开该文件。
我发现的所有解决方案要么限于 365,要么涉及创建我试图避免的新数据表或列,因为这意味着相当多的返工。相反,我只是使用了一组嵌套的 IFS,最终将考虑将这些特定的数据透视表更改为具有索引查找的常规表,以使实际数据能够位于多个列中。
我目前使用 SUBTOTAL 函数来求和、计数或平均一个范围内的一堆单元格。我以前手动过滤范围,所以我只对我想要的行进行总计,但是现在需要能够同时查看多个标准。即在下面的示例中,我之前手动过滤范围以仅包括“Apple”,但现在我需要能够同时分别总计“Apple”、“Orange”、“Banana”。
小计字段用于图表,我有一个单元格 (F5),其中包含一个对应于 SUM、COUNT 或平均值(9、2 或 1)的数字,用于链接到“汇总表”中的小计公式工作簿中的其他功能,我仍然需要能够保留该功能。
我的工作表如何设置的示例:
原始数据
产品类别 | 销售数量 | 日期 |
---|---|---|
苹果 | 4 | 21 年 1 月 9 日 |
橙子 | 3 | 21 年 6 月 9 日 |
香蕉 | 2 | 21 年 10 月 9 日 |
苹果 | 6 | 14/9/21 |
橙子 | 6 | 20/9/21 |
苹果 | 5 | 29/9/21 |
我要匹配的标准在汇总表的第 1 列(产品类型)中。
基本上,然后我希望能够最终将数据显示为Totals:
$F$5 = 9
对于每一行: SUBTOTAL($F$5,SalesQTY)
汇总表
产品类别 | 结果(每月销售额) |
---|---|
苹果 | 15 |
橙子 | 9 |
香蕉 | 2 |
或作为平均值:
$F$5 = 1
对于每一行: SUBTOTAL($F$5,SalesQTY)
产品类别 | 结果(每次销售的平均数量) |
---|---|
苹果 | 5 |
橙子 | 4.5 |
香蕉 | 2 |
或作为Count:
$F$5 = 2
对于每一行: SUBTOTAL($F$5,SalesQTY)
产品类别 | 结果(# 销售交易) |
---|---|
苹果 | 2 |
橙子 | 2 |
香蕉 | 1 |
有什么方法可以将 SUMIF 和 SUBTOTAL 结合起来,同时还能保留在平均值、总和和计数之间切换的能力?
解决方案
这是在 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
)
我一般写了这个,所以你可以只为某些产品、最小数量、日期范围或其他条件添加过滤器。在上面,我设置了过滤器以通过所有内容。
推荐阅读
- azure - 在 yaml 中克隆存储库时如何添加“DefaultCollection”
- apache-kafka - Kafka_configs.sh 是否有能力改变分区?
- r - 有没有一种方法可以让我使用 Shiny 在 R 中制作两个侧面板和额外的测试脚本?
- c# - 如何使用递归在三角形内创建三角形
- java - Selenium 错过了 Instagram 用户关注者列表中“关注”按钮上的随机元素
- javascript - 从数组中选择特定的 JSON 数据
- python - 如何在python中使我的变量成为颜色?
- heroku - 未解决的导入`core::task::Wake`
- java - java import 语句有问题,错误的类文件包含错误的类
- tensorflow - TensorFlow 逐层执行时间与网络层时间