大家好,这一章给大家分享一个函数的嵌套案例,非常经典.
这是来自于一个粉丝的提问,
下方是一份计件金额表格:
我现在想要计算每一个人的平均计件金额.
例如:
张三的计件平均金额应该是100+300/2=250
李四的平均金额为:300/2+300/3=250
算出每一个人的平均金额,应该如何去做?
函数分解,你可以直接观看下方的视频解析和下载素材
函数解析:
首先
=FIND(E2,$B$2:$B$7)
用FIND函数去找当前E2单元格张三在B列种哪些单元格中存在
用ISNUMBER函数进行数值判断,如果为TRUE代表B列种对应的单元格中存在当前条件:
=ISNUMBER(FIND(E2,$B$2:$B$7))
用判断结果和计件金额进行相乘,就等于把所有满足为张三的计件进行:
=ISNUMBER(FIND(E2,$B$2:$B$7))*$C$2:$C$7
由于,计件金额是多人分摊,所以需要用把每一行调取出来的金额进行人数的分摊除法:
这个时候需要找规律:
譬如B2单元格没有"、"代表只有一个负责人,
B3单元格一个"、",分隔两个负责人.
所以你可以用LEN函数:
LEN($B$2:$B$7)-LEN(SUBSTITUTE($B$2:$B$7,"、",""))+1)
算出每一行在B列种分别有几个负责人,
然后用:
ISNUMBER(FIND(E2,$B$2:$B$7))*$C$2:$C$7
除以
LEN($B$2:$B$7)-LEN(SUBSTITUTE($B$2:$B$7,"、",""))+1)
代表均摊的金额:
最后,把所有的内容进行相加:
=SUMPRODUCT(ISNUMBER(FIND(E2,$B$2:$B$7))*$C$2:$C$7/(LEN($B$2:$B$7)-LEN(SUBSTITUTE($B$2:$B$7,"、",""))+1))
就等于是最后的计件金额: