如何根据不同列的文本值减去一些值?

如何根据不同列的文本值减去一些值?

我有一个电子表格,用于跟踪两个人之间的付款。它如下所示:

电子表格

我希望Amount从回顾人员Left to pay数量中减去该列的值,具体取决于从该列的下拉列表中选择了谁的姓名Who paid?

例如 -E5的值为JillD5的值为£500。 由于E5Jill它的值,因此我希望D5将 的值从单元格 中 Jills 剩余的待付值中减去I5

我知道我需要使用IF公式,但我不太明白。有人能告诉我怎么做吗?

答案1

2500 是固定值吗?如果是,请尝试以下公式:

=25000-SUMIF($E$5:$E$12,H4,$D$5:$D$12)

在此处输入图片描述

如果没有,则添加辅助单元格:

=$H$1-SUMIF($E$5:$E$12,H4,$D$5:$D$12)

在此处输入图片描述

答案2

您需要将“初始”Left to pay值存储到某个单元格中,然后像这样计算值:

LeftToPay = InitialLeftToPay - SUM(AllPaymentsOfPerson)

尽量不要使用水平表(左表),因为这不是 Excel(和其他表格)的用途。我建议格式化您的表格(命名表改名表格的使用)像这样:

在此处输入图片描述

然后Left to pay列中的公式是

Office 365 
=[@[Initial value]]-SUM(Payments[Amount]*(Payments[Who]=[@Who]))

Older versions
Array formula has to be used. Formula above must be confirmed by CTRL+SHIFT+ENTER

怎么运行的:

Payments[Who]=[@Who]

将公式所在行的Left to pay表格列中的值与表格中的整个列进行比较。结果是带有值的数组(与列大小相同)。WhoWhoPaymentsPayments WhoTRUE/FALSE

如果在数值计算中使用TRUE/FALSE值,它们将被视为0/1值。

{"Jill","Jack","Jill","","",""}="Jack"
={FALSE,TRUE,FALSE,FALSE,FALSE,FALSE}={0,1,0,0,0,0}

所以:

SUM(Payments[Amount]*(Payments[Who]=[@Who])

SUMPayments Amount列值乘以上一步的结果(用值1或处理数组0)。

SUM({250,500,100,0,0,0}*{0,1,0,0,0,0})
=SUM({0,500,0,0,0,0} )=500

最后Ammount从中减去这个“想要”值的总和Initial value

=25000-500=24500

相关内容