|
本期的出题者应该是aw2006wa朋友，问题来自这个帖子。求助的原因是因为规格表比较大，不想一一去定义名称，希望有个简便的办法。征得aw2006wa的同意，没把答案给他，把问题搬上了本期竞赛擂台。 收到的30多份答案中，有三分之一的朋友以捞分为目的，没有开动你那聪明的脑子，涮涮涮，为每个队定义一个名称，然后用Substitute()处理一下，最后让Evaluate()扮作端盘子的姑娘，你要什么就端什么，从Kevin手中换取二两银子。 居其次的有6位朋友，钻了题目的空子，一个劲地使用Substitute()嵌套，弄得山菊花哑口无言，直后悔没有增加彩旗队、杂技队、啦啦队、观礼队、Excel队……[em06] [em10] 值得高兴的是，我们收到了不少优秀答卷。 1、PRODUCT()+OFFSET()+MATCH() 先来看一下草草的答案。 【L】代表I3*J3，求I3*J3可用公式PRODUCT(I3:J3) 【C】代表I4*J4，求I4*J4可用公式PRODUCT(I4:J4) 返回引用I3:J3、I4:J4……可以用Offset()，行号用Match()求得。 OFFSET(I$2:J$2,MATCH("L",H$3:H$7,),)→I3:J3 OFFSET(I$2:J$2,MATCH("C",H$3:H$7,),)→I4:J4 综合以上思路，求【L】+【C】*2即是求： PRODUCT(OFFSET(I$2:J$2,MATCH("L",H$3:H$7,),))+PRODUCT(OFFSET(I$2:J$2,MATCH("C",H$3:H$7,),))*2 对比可知，公式的演变即是把“【”替换成“PRODUCT(OFFSET(I$2:J$2,MATCH("”，把“】”替换成“",H$3:H$7,),))”。 用Substitute()完成替换： SUBSTITUTE(SUBSTITUTE($B3,"【","PRODUCT(OFFSET(I$2:J$2,MATCH("""),"】",""",H$3:H$7,),))") 外面增加一个函数Evaluate()，把替换后的文本转换成数值，并把它定义为名称： rs=Evaluate(SUBSTITUTE(SUBSTITUTE($B3,"【","PRODUCT(OFFSET(I$2:J$2,MATCH("""),"】",""",H$3:H$7,),))")) 单元格中输入公式=rs便是。 原公式前面的“=”可以省略。 论坛上的Sumproduct()随处可见，而Product()却少得让好多人还不知道这个函数的存在，本来，这是我想为难一下大家的地方，chrisfang，方兄，不走这道，也笑眯眯地在罗马挥舞着双手。 2、INDEX()+MATCH() 先定义一个名称： data =Sheet1!$I$3:$I$7*Sheet1!$J$3:$J$7 把各队的人数保存到一内存数组中：data={64;192;256;192;144} 【L】+【C】*2可转化为INDEX(data,1)+INDEX(data,2)*2 INDEX()中的第二个参数“行号”，同样可以用MATCH()求得： MATCH("L",H$3:H$7,) MATCH("C",H$3:H$7,) ……等等 综合以上思路，把"【"和"】"分别替换为"INDEX(data,MATCH("""、""",$H$3:$H$7,0))"，外加EVALUATE： EVA=EVALUATE(SUBSTITUTE(SUBSTITUTE(Sheet1!$B3,"【","INDEX(data,MATCH("""),"】",""",$H$3:$H$7,0))")) 单元格公式： C3=EVA 佩服方兄之余，狼版主的公式更让我们眼前一亮，简洁明快，妙。 3、SUMPRODUCT()打出响亮牌。 俗语说，看佛容易割佛难。看northwolves的公式就有这种感受。 求【L】+【C】*2，本来就是求： SUMPRODUCT(($H$3:$H$7="L")*(I$3:I$7)*(J$3:J$7))+SUMPRODUCT(($H$3:$H$7="C")*(I$3:I$7)*(J$3:J$7))*2 知道了思路以后，看公式就好理解了： renshu=EVALUATE("="&SUBSTITUTE(SUBSTITUTE($B3,"【","SUMPRODUCT(($H$3:$H$7="""),"】",""")*(I$3:I$7)*(J$3:J$7))")) 把两个符号替换成两段文本，外面嵌套一个EVALUATE()，得到一个非常完美的公式。 涉及到构造内存数组，得力干将当数LOOKUP()，逍遥兄也将其派上用场了。 4、LOOKUP()+FIND() 不可否认，FIND()用得漂亮极了： FIND(H$3:H$7,B3)={2;6;12;#VALUE!;#VALUE!} 轻而易举，得到“梅县”参加演出的队别信息：由H3:H7中1、2、3队组成。结果中的数值表示L、C、X在B3“【L】+【C】*2+【X】*2”中出现的位置。 根据这个位置，提取演出队的数量： =--LOOKUP(FIND(H$3:H$7,B3)+3,ROW(INDIRECT("1:"&LEN(B3))),MID(B3,ROW(INDIRECT("1:"&LEN(B3))),1)) 单元格公式，用演出队个数*行*列，求和： C3=SUM(ISNUMBER(FIND(H$3:H$7,B3))*IF(ISNUMBER(x),x,1)*I$3:I$7*J$3:J$7) 让逍遥兄不满意的，就是这个1，虽然满足了题目要求，但演出队个数只限于1位数，使公式实用性大打折扣。于是又写了个公式二(各位自己看看)。 这不是答题的错，gvntw版主的答案也利用了一位数这个特别，使公式变得简便。 5、EVALUATE()作先锋。 第一步：把“【L】+【C】*2+【X】*2”换个表现形式：“{"L1";"C1*2";"X1*2"}”。 由文本变成数组，方便用序号1、2、3……提取各队信息。 公式是： =EVALUATE("{"""&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE($B3,"【",),"】",1),"+",""";""")&"""}") 公式进行三次替换，把两个符号“【”、“】”替换成空文本，即删掉它，把“+”替换成分号和引号“"";""”。 外加EVALUATE()函数把替换后的文本转换成数值，即数组。 把公式定义为名称xx。 数组元素有两个特点：L1、C1*2、X1*2第一个字符表示队的代号，最后一个字符表示队的数量。 根据队的代号，可以确定H3:J7中的行号，即确定演出队的行数与列数。 第二步：行数、列数、队数都确定了，最后结果也出来了。 C3=SUM(N(OFFSET(I$2,MATCH(LEFT(xx),H$3:H$7,),))*N(OFFSET(I$2,MATCH(LEFT(xx),H$3:H$7,),1))*RIGHT(xx)) 三部分结果分别是行数、列数、队数 数组公式，按三键Ctrl+Shift+Enter结束。 lg_cai严谨的作风总是值得我们学习的，他的答案在着力处理好演出队的个数问题。 6、FIND()、MID()成功合作。 第一步：把【L】+【C】*2+【X】*2处理成0【L*1+【C*2+【X*2+【。 每个队的个数总在两个“【”之间。 先定义一个名称str: str=0&SUBSTITUTE(SUBSTITUTE(Sheet1!$B12,"】*","*"),"】","*1")&"+【" 第二步：取得数值开始与结束位置。 文本中数值开始位置是： aa=FIND(Sheet1!$H$3:$H$7,str)+2 发挥FIND()第三个参数的作用，返回文本中各数值后面“【”的位置(文本中第二个到最后一个“【”的位置)： bb=IF(ISNUMBER(FIND("【",str,aa)),FIND("【",str,aa)) 第三步：在单元格中输入公式，取得各演出队合计人数： C3=SUM(IF(bb,MID(str,aa,bb-aa-1))*$I$3:$I$7*$J$3:$J$7) 数组公式，按三键Ctrl+Shift+Enter结束。 逍遥兄一定又在窃笑，38楼的公式写得惬意极了，其中的RIGHT()+FIND()用得妙，打个比方，就像小孩子一泡尿把敌人的大炮浇灭了。 7、RIGHT()+FIND() 公式RIGHT(B3&"+",LEN(B3)-FIND(H$3:H$7,B3))，结果为： {"1+C*2+X*2+";"2+X*2+";"2+";#VALUE!;#VALUE!} 各数组元素第一个加号“+”前面的数字就是各队的个数，来得好轻松。 当然，公式中的ISNUMBER()错误处理也是得意之处。 各位花点时间去品味品味，一定有益。 gouweicao78兄39楼的公式就不说了。用“【”、“】”替换改作“+”、“*”替换相对应的文本，技巧相同。 用原来的思路，公式是： rs=EVALUATE(SUBSTITUTE(SUBSTITUTE("+"&Sheet1!$B14,"+","+PRODUCT(OFFSET(I$2:J$2,MATCH("""),"*",""",H$3:H$7,),))*")) 好了，就说到这里了，谢谢大家的参与。最后把答案送给aw2006wa朋友，但愿你能满意。 | 
楼主: 山菊花
沪公网安备 31011702000001号 沪ICP备11019229号-2
发表于 2006-10-15 12:00
楼主