【Excel公式教程】学废了!这个求和问题大神们给了11种解法,我竟然一个都看不懂……

公众号回复2016   下载office2016

之前发了一个求和的问题,数据源如图所示:

B列记录了每天的销售量,现在需要根据每种水果的单价和当天销量计算出当天的销售额。

水果的单价记录如下图所示:

这样的数据源记录方式,为后期的统计带来了不小的麻烦,原本以为只是一个简单的求和问题,谁知道现在是这样……

好在高手们给出了很多公式答案,整理了11个公式,结果都正确,但是一个公式都看不懂,不信的话就一起来看看这些公式,你能看懂几个……

01
公式1
=SUM(IFERROR(LEFT(MID(B2,FIND(","&E$2:E$7,","&B2)+LEN(E$2:E$7),99),FIND(",",MID(B2,FIND(","&E$2:E$7,","&B2)+LEN(E$2:E$7),99)&",")-1)*F$2:F$7,))

← 左右滑动查看完整公式 →

02
公式2
=SUM(TEXT(SUBSTITUTE(MID(SUBSTITUTE(B2,",",REPT(" ",99)),COLUMN($A:$F)*99-98,99),E$2:E$7,),"0;;0;!0")*F$2:F$7)

← 左右滑动查看完整公式 →

03
公式3
=SUM(MOD(SMALL(IFERROR(--MID(B2,FIND(","&E$2:E$7,","&B2)+LEN(E$2:E$7),{1,2,3}),)+ROW($1:$6)/1%%,3*ROW($1:$6)),10^4)*F$2:F$7)

← 左右滑动查看完整公式 →

04
公式4
=SUM(IFERROR(SUBSTITUTE(MID(SUBSTITUTE(B2,",",REPT(" ",99)),COLUMN(A:H)*99-98,90),E$2:E$7,"")*F$2:F$7,))

← 左右滑动查看完整公式 →

05
公式5
=SUM(MOD(SMALL(ROW($1:$6)*1000+IFERROR(--MID(","&$B2,FIND(","&$E$2:$E$7,","&$B2)+LEN($E$2:$E$7)+1,{1,2,3}),),ROW($1:$6)*3),1000)*$F$2:$F$7)

← 左右滑动查看完整公式 →

06
公式6
=SUM(TEXT(TRIM(MID(SUBSTITUTE(SUBSTITUTE(","&B2,","&E$2:E$7,","),",",REPT(" ",99)),COLUMN(A:S)*99-98,99)),"0;0;0;!0")*F$2:F$7)

← 左右滑动查看完整公式 →

07
公式7
=SUM((1-ISERR(FIND(","&E$2:E$7&COLUMN(A:OMB)&",",","&B2&",")))*COLUMN(A:OMB)*F$2:F$7)

← 左右滑动查看完整公式 →

08
公式8
=SUM(IFERROR(LEFT(SUBSTITUTE(MID(B2,FIND(","&E$2:E$7,","&B2)+LEN(E$2:E$7),9),",","  "),3)*F$2:F$7,))

← 左右滑动查看完整公式 →

09
公式9
=SUM(IFERROR(--TRIM(LEFT(SUBSTITUTE(MID(B2,FIND(","&$E$2:$E$7,","&B2)+LEN($E$2:$E$7),3),",","   "),3)),)*$F$2:$F$7)

← 左右滑动查看完整公式 →

10
公式10
=SUM(IF(IFERROR(FIND($E$2:$E$7&--MID(B2&"小",FIND(","&$E$2:$E$7,","&B2)+LEN($E$2:$E$7),{1,2,3})&",",B2&","),),--MID(B2&"格",FIND(","&$E$2:$E$7,","&B2)+LEN($E$2:$E$7),{1,2,3})*$F$2:$F$7))

← 左右滑动查看完整公式 →

11
公式11
=SUM(IFERROR(TRIM(LEFT(SUBSTITUTE(RIGHT($B2,LEN($B2)-FIND(","&$E$2:$E$7,","&$B2,1)-LEN($E$2:$E$7)+1),",",REPT(" ",9)),9)),)*$F$2:$F$7)

← 左右滑动查看完整公式 →

11个公式你看懂了几个?
留言PK一下

(0)

相关推荐