答案1
答案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
表格列中的值与表格中的整个列进行比较。结果是带有值的数组(与列大小相同)。Who
Who
Payments
Payments
Who
TRUE/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])
是SUM
将Payments
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