各位老师看看这题，谁能写出最简洁的公式？

@刘卓

先学一个能看懂的：=SUM((1-ISERR(FIND(ROW($1:$999)&{"箱","条","包"},A2)))*TRUNC(ROW($1:$999),1-LEN(ROW($1:$999)))*{500,10,1})

xzlu13014

丢丢表格 发表于 2021-3-22 10:56
你这个可以再简化

=SUM(IFERROR(MID(TEXT(MID("@"&A2,FIND({"包";"条";"箱"},A2)-{1,2,3},{2,3,4}),), ...

"@"是个啥啊

leaderdxp

我也来个裹脚步，哈哈，不过需要箱在前，条在中，包在后=IFERROR((LEFT(A2,FIND("箱",A2)-1)*50*10),0)+IFERROR(MID(A2,IFERROR(FIND("箱",A2),0)+1,IFERROR(FIND("条",A2),0)-IFERROR(FIND("箱",A2),0)-1),0)*10+IFERROR(MID(A2,MAX(IFERROR(FIND("箱",A2),0),IFERROR(FIND("条",A2),0))+1,IFERROR(FIND("包",A2),0)-MAX(IFERROR(FIND("箱",A2),0),IFERROR(FIND("条",A2),0))-1),0)*1

cjyjimmy

64数组公式：
=SUM(IFERROR(MIDB(10&A2,FINDB({"包","条","箱"},A2),2)*{1,10,500},))
这样好像也可以算出来，数组公式

leaderdxp

dim麻辣君 发表于 2021-3-21 19:29
110都还没写进，再研究下

118

太强大了，顶礼膜拜

大猫不吃鱼

cjyjimmy 发表于 2021-8-10 14:52
64数组公式：
=SUM(IFERROR(MIDB(10&A2,FINDB({"包","条","箱"},A2),2)*{1,10,500},))
这样好像也可以算 ...

数据中有三位数的情况

云南陶肖民

凑凑数
=SUM((CHOOSECOLS(TEXTSPLIT(SUBSTITUTE(SUBSTITUTE("%"&SUBSTITUTE(A2,"箱","/箱%"),"条","/条%"),"包","/包%"),"/","%",TRUE),2)={"箱","条","包"})*(CHOOSECOLS(TEXTSPLIT(SUBSTITUTE(SUBSTITUTE("%"&SUBSTITUTE(A2,"箱","/箱%"),"条","/条%"),"包","/包%"),"/","%",TRUE),1)*{500,10,1}))

安然祥和

因原附件可能损坏现再发一次附件

thdyyf

=EVALUATE(REPLACE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(REPLACE(A2,LEN(A2)+1,0,"0"),"箱","*500+"),"条","*10+"),"包","*1+"),1,0,"=")) 我初学，只好简单粗暴的来 =EVALUATE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,"箱","*500+"),"条","*10+"),"包","*1+")&0)还能简化学习了

橒♂蝣

=IFERROR(SUM(XLOOKUP(TEXTSPLIT(A2,SEQUENCE(10)-1,,1),{"箱","包","条"},{500,1,10})*TEXTSPLIT(A2,{"箱","包","条"},,1)),0)