求助：根据数量重量邮费价格表求参考邮费

wqjun

需求，在A3中，提取含数量：1袋或者2袋等不定。再提取重量：800克、2kg、3000g等不定数值，然后根据快递价格表算出参考邮费再B3列显示

求助：根据数量重量邮费价格表求参考邮费.xlsx.zip (10.55 KB, 下载次数: 22)

2024-10-29 09:34 上传

点击文件名下载附件

pjiang

压缩包里面有文件吗？

hxj119

1. =LET(a,VALUE(REGEXP(A3,"\d+(?=袋)")),b,REGEXP(A3,"\d+(?=g|kg|克|千克)")/1000^REGEXP(A3,"\d+(?=g|克)",1),c,LEFT(INDEX(TEXTSPLIT(A3,{","," "}),3),2),d,FILTER(快递价格!$B$2:$C$6,ISNUMBER(FIND(c,快递价格!$A$2:$A$6))),a*SUM(INDEX(d,1),MAX(0,ROUNDUP(b,0)-1)*INDEX(d,2)))

复制代码

loirol

仅附件例子，自定义函数

leepoison

1. =MAP(A3:A5,LAMBDA(x,LET(a,FILTER(快递价格!B2:C6,ISNUMBER(FIND(REGEXP(x,"[一-龟]+(?=省)"),快递价格!A2:A6))),b,EVALUATE(REGEXP(SUBSTITUTES(x,{"袋","kg"},{"*","000"}),"\d+\*\d+"))/1000,IF(b<=1,CHOOSECOLS(a,1),CHOOSECOLS(a,1)+(b-1)*CHOOSECOLS(a,2)))))

复制代码

leepoison

hxj119 发表于 2024-10-28 14:11

第二个4公斤 湖北应该是10+3*3=19

zgller1

=LET(a,REGEXP(A3,"\d+(?=[克kgg])"),b,REGEXP(A3,"\d+(?=袋)"),c,IF(ISNUMBER(FIND("kg",A3)),a*b,(a/1000)*b),d,CONCAT(IF(ISNUMBER(FIND(REGEXP(A3,"[一-龟]+(?=省)"),快递价格!A2:A6)),快递价格!B2:B6,"")),e,CONCAT(IF(ISNUMBER(FIND(REGEXP(A3,"[一-龟]+(?=省)"),快递价格!A2:A6)),快递价格!C2:C6,"")),IF(c>1,d+(c-1)*e,d))

hxj119

leepoison 发表于 2024-10-29 10:41
第二个4公斤 湖北应该是10+3*3=19

楼主没有详细描述计算费用！

我的理解：2袋就是两个包裹，分开计算求和
=(10+3)*2

hxj119

如果2袋2kg是按一个包裹重量计算。公式如下：

1. =LET(a,VALUE(REGEXP(A3,"\d+(?=袋)")),b,REGEXP(A3,"\d+(?=g|kg|克|千克)")/1000^REGEXP(A3,"\d+(?=g|克)",1),c,LEFT(INDEX(TEXTSPLIT(A3,{","," "}),3),2),d,FILTER(快递价格!$B$2:$C$6,ISNUMBER(FIND(c,快递价格!$A$2:$A$6))),SUM(INDEX(d,1),MAX(0,ROUNDUP(a*b,0)-1)*INDEX(d,2)))

复制代码

华尔街操盘手

b3下拉

1. =LET(a,TEXTSPLIT(SUBSTITUTE(A3,"kg","000g"),{","," ","袋","克","g"},CHAR(10)),
   
2. 省,LEFT(INDEX(a,1,3),LEN(INDEX(a,1,3))-1),
   
3. 重量,PRODUCT(--INDEX(a,2,{1;2}))/1000,SUMPRODUCT(HSTACK(1,MAX(重量,1)-1),XLOOKUP("*"&省&"*",快递价格!A:A,快递价格!B:C,,2)))

复制代码