Google Sheets按另一列值计算差额：嵌套公式逻辑求助

嘿，我懂你这种卡在嵌套公式逻辑里的感觉——明明知道要按用户分组计算，就是理不清怎么把累计买入、卖出和当前行关联起来对吧？先确认下你的需求：只有「卖出」行需要计算差额，买入行留空，而且差额是这笔卖出金额减去该用户到这笔交易前的累计投入（也就是所有买入总和减去之前已经卖出的总和），看你给的约翰例子完全符合这个逻辑：两次买入共$150，卖出$200，差额$50。

下面直接给你可用的公式，我会拆解开讲清楚每部分的作用，方便你调整：

假设你的表头在第1行（A1=姓名，B1=操作，C1=买入金额，D1=卖出金额，E1=差额），在E2单元格输入以下公式，然后下拉填充整列：

=IF(B2="卖出", D2 - SUMIFS($C$2:C2, $A$2:A2, A2, $B$2:B2, "买入") + SUMIFS($D$2:D1, $A$2:D1, A2, $B$2:D1, "卖出"), "")

公式逻辑拆解： #

• IF(B2="卖出", ..., "")：先判断当前行是不是卖出操作，是就计算差额，否则留空，刚好匹配你只在卖出行显示差额的需求；
• SUMIFS($C$2:C2, $A$2:A2, A2, $B$2:B2, "买入")：计算到当前行为止，和当前用户姓名相同、且操作是「买入」的所有金额总和（也就是该用户累计投入的钱）；
• SUMIFS($D$2:D1, $A$2:D1, A2, $B$2:D1, "卖出")：计算到上一行为止，和当前用户姓名相同、且操作是「卖出」的所有金额总和（也就是该用户之前已经收回的钱）；
• 最后用当前卖出金额D2减去「累计投入 - 已收回金额」，得到的就是这笔卖出带来的差额（盈利/亏损）。

注意事项： #

如果你的金额列是带$的文本格式，公式可能无法正常计算，建议先把单元格格式改成「货币」类型（直接在格式菜单里选，不用手动输入$）。如果已经是文本格式，可以把公式里的C2换成VALUE(SUBSTITUTE($C$2:C2, "$", ""))，D2同理，用来提取纯数值。

进阶：一次性填充整列（不用下拉） #

如果你的数据量比较大，不想手动下拉，可以用ARRAYFORMULA一次性处理所有行，直接在E2单元格输入：

=ARRAYFORMULA(IF(B2:B="卖出", D2:D - SUMIFS(C2:C, A2:A, A2:A, B2:B, "买入", ROW(A2:A), "<="&ROW(A2:A)) + SUMIFS(D2:D, A2:A, A2:A, B2:B, "卖出", ROW(A2:A), "<"&ROW(A2:A)), ""))

这个公式会自动遍历所有行，不用再下拉填充，Google Sheets和新版Excel（365）都支持，旧版Excel需要按Ctrl+Shift+Enter触发数组公式。

对了，你提到一开始标了Excel但实际是Google Sheets，确实两者的这些核心函数几乎没差异，上面的公式在两个工具里都能正常用。

内容的提问来源于stack exchange，提问作者Ryan B