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

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

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

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

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

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

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

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

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

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

=IF(G4

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

=IF(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)

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

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

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

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

最终结果就出来了。

作者:卢子

  • 发表于:
  • 原文链接https://kuaibao.qq.com/s/20200629A0D9MT00?refer=cp_1026
  • 腾讯「云+社区」是腾讯内容开放平台帐号(企鹅号)传播渠道之一,根据《腾讯内容开放平台服务协议》转载发布内容。
  • 如有侵权,请联系 yunjia_community@tencent.com 删除。

扫码关注云+社区

领取腾讯云代金券