VIP学员的问题,左边为要发的钱,右边为要扣的钱。根据姓名,累计F列的金额,累计的最大值小于B列应发的。 比如卢子,应发50000,右边有2条记录,只能扣款48000这1条记录。 比如路人,应发90000,右边有3条记录,只能扣除48000+30000=78000这2条记录。 说白了就是你买东西,卡里有钱产品就自动扣款,钱不够就扣款失败。 思路,先累计右边每个人的金额,再跟左边的应发比较,最后获取小于左边最大值的金额。 通过SUMIF函数,用混合引用的方法,下拉区域逐渐变大,从而起到累计的效果。
再用VLOOKUP函数查找姓名的应发金额。
累计的金额跟应发的金额比较,小于应发的显示本身,否则显示空白。
再将这3条公式合并起来。
最后,扣款的金额借助LOOKUP函数查找最后一个非空单元格的对应值,结果就出来了。
本来问题到此结束,这时VIP会员又提出了一个要求,希望能将已经扣除的金额做标记。 原先我是在单元格标记1。
在写文章的时候,发觉用条件格式更为直观。在使用条件格式的时候,跟单元格写公式有所区别,都需要将字母用美元符号固定死。
选择区域E4:G8,点条件格式→新建规则。 点使用公式确定要设置格式的单元格,将刚刚的公式复制粘贴进去,设置填充颜色,确定。 最终结果就出来了。 作者:卢子,清华畅销书作者,《Excel效率手册 早做完,不加班》系列丛书创始人,个人公众号:Excel不加班(ID:Excelbujiaban) |
|