Excel也能按最大金额自动扣款？万万没想到Excel还有这操作....

VIP学员的问题，左边为要发的钱，右边为要扣的钱。根据姓名，累计F列的金额，累计的最大值小于B列应发的。

比如卢子，应发50000，右边有2条记录，只能扣款48000这1条记录。

比如路人，应发90000，右边有3条记录，只能扣除48000+30000=78000这2条记录。

说白了就是你买东西，卡里有钱产品就自动扣款，钱不够就扣款失败。

思路，先累计右边每个人的金额，再跟左边的应发比较，最后获取小于左边最大值的金额。

通过SUMIF函数，用混合引用的方法，下拉区域逐渐变大，从而起到累计的效果。

=SUMIF(E$4:E4,E4,F$4:F4)

再用VLOOKUP函数查找姓名的应发金额。

=VLOOKUP(E4,A:B,2,0)

累计的金额跟应发的金额比较，小于应发的显示本身，否则显示空白。

=IF(G4<=H4,G4,"")

再将这3条公式合并起来。

=IF(SUMIF(E$4:E4,E4,F$4:F4)<=VLOOKUP(E4,A:B,2,0),SUMIF(E$4:E4,E4,F$4:F4),"")

最后，扣款的金额借助LOOKUP函数查找最后一个非空单元格的对应值，结果就出来了。

=LOOKUP(1,0/((G:G<>"")*(E:E=A4)),G:G)

本来问题到此结束，这时VIP会员又提出了一个要求，希望能将已经扣除的金额做标记。

原先我是在单元格标记1。

=IF(SUMIF(E$4:E4,E4,F$4:F4)<=VLOOKUP(E4,A:B,2,0),1,"")

在写文章的时候，发觉用条件格式更为直观。在使用条件格式的时候，跟单元格写公式有所区别，都需要将字母用美元符号固定死。

=SUMIF($E$4:$E4,$E4,$F$4:$F4)<=VLOOKUP($E4,$A:$B,2,0)

选择区域E4:G8，点条件格式→新建规则。

点使用公式确定要设置格式的单元格，将刚刚的公式复制粘贴进去，设置填充颜色，确定。

最终结果就出来了。

作者：卢子，清华畅销书作者，《Excel效率手册 早做完，不加班》系列丛书创始人，个人公众号：Excel不加班（ID：Excelbujiaban）