Excel也能按最大金额自动扣款？万万没想到Excel还有这操作.... 看下一篇VIP学员

点击上方蓝字
「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,"")
文章图片

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

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

推荐阅读

巩俐|巩俐与张艺谋28年前合照，那时巩俐清纯可人笑容甜美！

冰妹|墨香家男主害怕什么？师尊怕眼泪，羡羡怕家规，那怜怜呢？

易快讯TB荣耀7英寸大屏机型曝光，将支持DCI-P3广色域

穿衣搭配|《半是蜜糖半是伤》大结局，姐姐们的穿搭你还没学起来？

『周扒皮看热闹』油耗5.3L，从17.98万跌至8.49万，却仍被忽略，可惜的“日产造”

「小儿湿疹」小儿湿疹多久能治好做好护理防止复发

女性养生|女性再没钱，3种食物不能少，美容护肤，滋肝明目，延缓肌肤衰老

行李托运是不是和飞机一块到达目的地

天狼星是夜空中最亮的恒星肉眼看来,星空中最亮的恒星是

虎年的祝福语句虎年的祝福语句成语

篮球记者杨克己就像我当年崇拜乔丹一样，纳什回忆与科比做队友：年轻人崇拜科比

名下已有房贷在还款中，还能否再贷款

「小院食光」红烧猪蹄就该这样做，软烂咸香不油腻，吃起来特别过瘾！

邓紫棋不在▲可能是最坚硬的玻璃手机！红米Note7正式发布刷新手机质保记录

桑维亮到丹寨开展＂春节＂走访慰问

全省累计@湖北新增确诊病例0例湖北新增无症状感染者17例

singer的意思?singer怎么读?

低血糖症|糖尿病常见并发症——低血糖症

哪些人不宜食用餐包？餐包的副作用有哪些?

银质针疗法利和弊都有什么呢？