Excel:使用公式仅对上面一行到下一个空白单元格求和?

Excel:使用公式仅对上面一行到下一个空白单元格求和?

我有点菜鸟,所以请耐心听我说。我正在制作一个电子表格,其中包含以各种价格买卖的各种产品。表格按产品排序,每笔完整的买卖交易之间都有一个空白行。我创建了一个列来计算每件产品的现金流。示例表如下所示:

Type | Product | Qty | Price | Total | Cashflow
BUY  | AA      | 2   | 0.50  | 1.00  | 
SELL | AA      | 2   | 1.50  | 3.00  | 2.00
(blank row)
BUY  | BB      | 5   | 0.10  | 0.50  | 
SELL | BB      | 5   | 1.00  | 5.00  | 4.50

我希望现金流列仅计算销售产品后的净亏损/利润(如果现金流一词使用不当,我深表歉意),因此交易类型为“购买”的行是空的。我使用了这个公式:

IF([@Type]="SELL",([@Total]-(OFFSET(F3, -1, -1))),"")

对于只有一行用于购买和一行用于销售的简单交易,此方法运行良好。问题是,有些产品的买入/卖出价格不同,此时抵消功能将不再起作用。示例:

Type | Product | Qty | Price | Total | Cashflow
BUY  | AA      | 2   | 0.50  | 1.00  | 
SELL | AA      | 2   | 1.50  | 3.00  | 2.00
(blank row)
BUY  | BB      | 3   | 0.20  | 0.60  | 
BUY  | BB      | 4   | 0.10  | 0.40  | 
SELL | BB      | 5   | 1.00  | 5.00  | 
SELL | BB      | 2   | 1.10  | 2.20  | 6.20

有没有办法为现金流制定一个公式,以便计算该产品所有销售额的总和减去所有购买额的总和,这样我就可以在产品交易的最后一行得到最终的利润/损失(上例中为 6.20)?例如,公式将确定从选定行到上方下一个空行的范围;然后从该范围内取 [@Type]="SELL" 的总和减去 [@Type]="BUY" 的总和

抱歉,如果我没解释清楚。如果你有任何想法,请告诉我

答案1

由于您自己构建电子表格,我假设您可以选择修改解决方案组件的设计方式。

步骤 1:一种方法是将Total列中的值计算为每条记录的负数BUY。因此,引入以下公式来计算总和

=IF([@Type]="BUY",-[@Qty]*[@Price],[@Qty]*[@Price])

步骤2:插入数据透视表,使用Product和进行聚合,Total如下所示

在此处输入图片描述

答案2

显然,这是一个数据透视表 - 但首先你需要将 BUY 值设为负数,这样总计列就变成

=IF([[Type ]]="BUY",-1,1)*[ [ Qty ] ]*[ [ Price ] ] 

然后在 Sheet3!$A$3 上创建数据透视表

数据透视表

这样你就得到了你想要的结果 - 然后将其放回到现金流列中,现金流变为

=IF(B4<>"","",GETPIVOTDATA(" Total ",Sheet3!$A$3," Product ",[ [ Product ] ]))

在此处输入图片描述

相关内容