[第16期]庆祝国庆

sahelo

山菊花老师说的太好了！

慢慢学习！

[此贴子已经被作者于2006-10-23 1:20:39编辑过]

gouweicao78

看山菊花的帖子总是让人舒服极了，妙语连珠，余音绕梁三月不绝于耳（要是以后耳鸣的话找你算账哦），呵呵。

daley

怎么快就结束啦，楼主将公式想得那么复杂，我来个最简单的，别笑话，我就只懂得这样算。

 

huangyongli

1. =SUM(IF(ISERROR(IF(FIND($H$3:$H$7,B3,1),$I$3:$I$7*$J$3:$J$7,)*IF(ISERROR(FIND("【"&$H$3:$H$7&"】*2",B3,1)),1,2)),,IF(FIND($H$3:$H$7,B3,1),$I$3:$I$7*$J$3:$J$7,)*IF(ISERROR(FIND("【"&$H$3:$H$7&"】*2",B3,1)),1,2)))

复制代码

jsxjd

山菊花 发表于 2006-10-22 17:21
本期的出题者应该是aw2006wa朋友，问题来自这个帖子。求助的原因是因为规格表比较大，不想一一去定义名称， ...

不用名称的公式：

1. =SUMPRODUCT(IF(ISERROR(MID(SUBSTITUTE(B3&"+","】+","】*1+"),FIND(H$3:H$7,SUBSTITUTE(B3&"+","】+","】*1+"))+3,1)),0,--MID(SUBSTITUTE(B3&"+","】+","】*1+"),FIND(H$3:H$7,SUBSTITUTE(B3&"+","】+","】*1+"))+3,1)),SUBTOTAL(6,OFFSET(I$2:J$2,ROW($1:$5),)))

复制代码

zengyidin

未看答案，自己琢磨了一个
C3=SUM(IF(ISERR(FIND(H$3:H$7&{"】+","】*"},B3&"+")),,{1,2})*I$3:I$7*J$3:J$7)  数组公式  下拉

zengyidin

再来个通用的
c3=sum(mid(substitute(if(right(b3)="】",b3&"*1",b3),"】+","】*1+")&rept(0,50),text(match(h$3:h$7,mid(substitute(if(right(b3)="】",b3&"*1",b3),"】+","】*1+")&"LCXYW",row($1:$30),1),),"[<"&len(substitute(if(right(b3)="】",b3&"*1",b3),"】+","】*1+"))&"]0;!3!3")+3,1)*i$3:i$7*j$3:j$7)    数组公式，下拉

zengyidin

c3=sum(mid(substitute(if(right(b3)="】",b3&" *1",b3),"】+","】 *1+")&rept(0,50),text(find(h$3:h$7,mid(sub stitute(if(right(b3)="】",b3&"*1",b3),"】+","】 *1+")&"LCXYW"),"[<"&len(subs titute(if(right(b3)="】",b3&"*1",b3),"】+","】 *1+"))&"]0;!3!3")+3,1)*i$3:i$7*j$3:j$7) 数组 公式，下拉

zengyidin

47楼公式有误，无法编辑，重发一个
c3=sum(mid(substitute(if(right(b3)="】",b3&" *1",b3),"】+","】 *1+")&rept(0,50),text(find(h$3:h$7,substitute(if(right(b3)="】",b3&"*1",b3),"】+","】 *1+")&"LCXYW"),"[<"&len(subs titute(if(right(b3)="】",b3&"*1",b3),"】+","】 *1+"))&"]0;!3!3")+3,1)*i$3:i$7*j$3:j$7) 数组 公 式，下拉

zengyidin

再简化一下,普通公式=SUMPRODUCT(MID(SUBSTITUTE(B3&"+","】+","】*1+")&REPT(0,50),TEXT(FIND(H$3:H$7,SUBSTITUTE(B3&"+","】+","】*1+")&"LCXYW"),"[<"&LEN(SUBSTITUTE(B3&"+","】+","】*1+"))&"]0;!3!3")+3,1)*I$3:I$7*J$3:J$7)