[第21期]春暖花开，恭喜你!

山菊花

难度：★★★

 

[此贴子已经被作者于2007-4-1 23:47:19编辑过]

fjlhgs

答案已发送至dsjb@21cn.com，请山版主评阅。

=========================

祝贺fjlhgs坐上沙发。答案有水平，值得学习。

1、利索的三板斧

X =MATCH(XM,XM,0)*10^LEN(S)+MATCH(JQ,JQ,0)

把姓名和奖券信息组合成一组数字，为以后的分析、统计提供了方便，思路很好。

因为XM、JQ没有使用动态引用，10^LEN(S)可用100取代。

Y =IF(MATCH(X,X,0)=RR,X)

剔除重复数字(对应题目姓名与奖券都相同)后的结果。

Z =SMALL(IF(FREQUENCY(Y,RR*10^LEN(S))>1,ROW(INDIRECT("1:"&S+1))),ROW()-1)-1

用FREQUENCY()统计各人的奖券数量，返回符合条件的行号。

如果不用自定义名称S，公式为：

Z =SMALL(IF(FREQUENCY(Y,RR*100)>1,ROW(XM)),ROW()-1)-2

从X到Y到Z，思路清晰、公式简洁，值得学习。

2、花开了

有了Z，顺理成章，即得到姓名：

F2=IF(ISNUMBER(Z),INDEX(XM,Z),"")

3、雾气朦朦阴雨菲

G2=IF($F2="","",IF(COLUMN()-6>SUM(--(Y>Z*10^LEN(S))*(Y<(Z+1)*10^LEN(S))),"",INDEX(JQ,RIGHT(INDEX(Y,SMALL(IF((Y>Z*10^LEN(S))*(Y<(Z+1)*10^LEN(S)),RR),COLUMN()-6)),LEN(S)))))

IF((Y>Z*10^LEN(S))*(Y<(Z+1)*10^LEN(S)),RR)思路独到，但与上面公式相比，显得逊色。

可把公式改为如下(见附件AE:AK)：

=IF($F2="","",INDEX($C:$C,SMALL(IF(INT(Y/100)=Z,RR+1,65536),COLUMN(A:A)))&"")

[此贴子已经被山菊花于2007-3-21 13:00:14编辑过]

封州大少

答案已发送,请评分

(水平有限,只好用定义名称一步步做,做的太复杂了,有时间现思考一下简化的办法)

================================

1、返回姓名，倾力打造

皆因脑子中有了MMUT()，便有了以下的连环公式：

data3=LOOKUP(SMALL(IF(MATCH(data1&data2,data1&data2,)=ROW(data1)-1,ROW(data1)-1),ROW(INDIRECT("1:"&SUM(N(MATCH(data1&data2,data1&data2,)=ROW(data1)-1))))),ROW(data1)-1,data1)

把姓名与奖券都相同的多余记录剔除，如第13条等记录，保留不重复的记录{1;2;3;4;5;6;7;8;9;10;11;12;14;15;16;17;20;21;22;23;25;29;30;32;34;35;37;38;39;40}，返回这些记录对应的姓名。

mmult=MMULT(--(data3=LOOKUP(COLUMN(INDIRECT("c1:c"&ROWS(data3),)),ROW(INDIRECT("1:"&ROWS(data3))),data3)),ROW(INDIRECT("1:"&ROWS(data3)))^0)

根据名单data3计算重复次数，即得不同奖券的次数。

F2=IF(ROW(1:1)>SUM(--((mmult>1)*MATCH(data3,data3,)=ROW(INDIRECT("1:"&ROWS(data3))))),"",INDEX(data3,SMALL(IF((mmult>1)*MATCH(data3,data3,)=ROW(INDIRECT("1:"&ROWS(data3))),ROW(INDIRECT("1:"&ROWS(data3)))),ROW(1:1))))

公式的核心部分是：

F2=INDEX(data3,SMALL(IF((mmult>1)*MATCH(data3,data3,)=ROW(INDIRECT("1:"&ROWS(data3))),ROW(INDIRECT("1:"&ROWS(data3)))),ROW(1:1)))

蓝色部分是data3中得奖券大于1的不重复名单对应的记录号(见附件R:R)，根据记录号，从data3中返回对应的姓名，完成了第一项任务。

mmult中的公式LOOKUP(COLUMN(INDIRECT("c1:c"&ROWS(data3),)),ROW(INDIRECT("1:"&ROWS(data3))),data3)可使用TRANSPOSE()简化：

mmult=MMULT(--(data3=TRANSPOSE(data3),ROW(INDIRECT("1:"&ROWS(data3)))^0)

2、奖券内容，信手拈花

G2=INDEX($C:$C,SMALL(IF((data1=$F2)*MATCH(data1&data2,data1&data2,)=ROW(data1)-1,ROW(data1),65536),COLUMN(A1)))&""

以data1=$F2为条件，提取C:C中不重复的记录，这是解决该问题的常规用法，封州大少在这方面一定没花多少脑筋。

 

===========================================

mmult中的公式LOOKUP(COLUMN(INDIRECT("c1:c"&ROWS(data3),)),ROW(INDIRECT("1:"&ROWS(data3))),data3)可使用TRANSPOSE()简化：

mmult=MMULT(--(data3=TRANSPOSE(data3),ROW(INDIRECT("1:"&ROWS(data3)))^0)

狂晕,一直都想当然地认为TRANSPOSE只支持单元格区域,不会支持内存数组,想到过这个函数,但连试都懒得去试

[此贴子已经被作者于2007-3-21 16:04:32编辑过]

yeatol

答案已发送，请评分。少于40个字符不能发送！所以再打几个字！

===============================================

1、轻描淡写，暗藏玄机

(为了让公式简短些，把原始数据B2:B41、C2:C41定义为名称XM、JQ。)

data1 =FREQUENCY(MATCH(XM&JQ,XM&JQ,),MATCH(XM&JQ,XM&JQ,))

MATCH(XM&JQ,XM&JQ,)返回的结果是姓名和奖券为条件第一次出现的记录号。

FREQUENCY()返回相同“姓名+奖券”在数据中出现的次数。

data2 =FREQUENCY(MATCH(XM,XM,),MATCH(XM,XM,))

MATCH(XM,XM,)是区域中姓名首次出现的记录号。

FREQUENCY()返回相同姓名在数据中出现的次数。

阿斯匹林是一种古老的退热止痛药物，从临床应用中发现，它对心血管具有副作用。后来，聪明的医生借阿斯匹林的副作用应用于心血管病的治疗，收到了很好的效果。

FREQUENCY()用于返回某个区域中数据的频率分布，即上面说的出现的次数。但从另一个角度看，它的结果可分为两类，一类是大于0的数，一类是等于0的数。筛选出大于0的项，即是对应“姓名+奖券”在区域中不重复的记录。

2、独具慧眼，笔到神来

答案中定义的第三个名称是data：

data=T(OFFSET(第2题!$B$2,SMALL(IF((data1<>data2)=TRUE,ROW(第2题!A$2:A$41)),ROW(INDIRECT("1:"&SUM(--(data1<>data2=TRUE)))))-2,))

选择V2:V41，输入公式中彩色标识的部分：

=IF(data1<>data2,ROW($2:$41))

按Ctrl+Shift+Enter结束。

为本列设置自动筛选。筛选大于0的值，结果为：

山版主评贴的风格都是别具一格呀！

[此贴子已经被作者于2007-3-21 17:41:58编辑过]

liujy

答案已经以邮件的形式发至dsjb@21cn.com，请山菊花老师评分。

====================================

1、就事论事

自定义名称：

判断=ISNUMBER(MATCH(姓名&"春",姓名&奖券,))+ISNUMBER(MATCH(姓名&"暖",姓名&奖券,))+ISNUMBER(MATCH(姓名&"花",姓名&奖券,))+ISNUMBER(MATCH(姓名&"开",姓名&奖券,))+ISNUMBER(MATCH(姓名&"风",姓名&奖券,))+ISNUMBER(MATCH(姓名&"和",姓名&奖券,))+ISNUMBER(MATCH(姓名&"日",姓名&奖券,))+ISNUMBER(MATCH(姓名&"丽",姓名&奖券,))

结果返回一个数组，每个元素表示对应姓名得到不同奖券的数量。

下一步，利用公式：

IF((MATCH(姓名,姓名,)=ROW(姓名)-1)*(判断>1),ROW(姓名),65536)

取得奖券数量大于1的不重复姓名的行号。

第三步，用上面的结果返回A列中的姓名：

F2=INDEX(B:B,SMALL(IF((MATCH(姓名,姓名,)=ROW(姓名)-1)*(判断>1),ROW(姓名),65536),ROW(1:1)))&""

2、节外生枝

从严格意义上说，题目不允许这么做，题目提供的条件是“不同的文字”，不是“春暖花开风和日丽”8个字。

既然答了，我们借此聊一下这方面的话题，也许有实际意义。

上面公式连接连续使用了8个结构相同的嵌套函数。如此有规律的公式，一定可以简化，写公式时要有这个敏感，事实上，Mmult()是解决该问题的最佳选择：

判断=MMULT(--ISNUMBER(MATCH(姓名&{"春","暖","花","开","风","和","日","丽"},姓名&奖券,)),{1;1;1;1;1;1;1;1})

这样，是不是简便了许多？

如果题目不仅仅是“春暖花开风和日丽”，而且还有“生机盎然万象更新”，公式则应该写为：

判断=MMULT(--ISNUMBER(MATCH(姓名&MID("春暖花开风和日丽生机盎然万象更新",COLUMN($A:$P),1),姓名&奖券,)),ROW($1:$16)^0)

 

[此贴子已经被山菊花于2007-3-21 20:51:46编辑过]

逍遥自在2005

逍遥自在2005连续交了四份答卷。在求得正确答案的基础上拓宽思路，提高要求，以内存数组的形式展示结果，使题目异彩绽放。在解答题目后还给自定义名称添加注释，让人一目了然，风格让人亲切。

下面公式来自答案3：

1、一样的公式，一样的思路

sz =MATCH(x,x,)*100+MATCH(y,y,) 

与fjlhgs的思路完全一致，把B:C列数据组合成一组数字信息，成功完成数据的分析过程。

ch =SMALL(IF(FREQUENCY(sz,sz),sz),ROW(INDIRECT("1:"&SUM(N(FREQUENCY(sz,sz)>0))))) 

剔除sz中重复的信息。

jg =IF(FREQUENCY(LOOKUP(SMALL(IF(FREQUENCY(sz,sz),ROW(x)-1),ROW(INDIRECT("1:"&COUNT(1/FREQUENCY(sz,sz))))),ROW(x)-1,sz),ROW(x)*100)>1,ROW(x),65536)

取得完全符合题意的姓名所在的行号。

2、不一样的公式，为了不一样的结果

xm =LOOKUP(SMALL(jg,ROW(x)-1),ROW(第2题!$B:$B),第2题!$B:$B)&""

jj =LOOKUP(RIGHT(SMALL(IF(SMALL(jg,ROW(x)-1)-1=TRANSPOSE(INT(ch/100)),TRANSPOSE(ch),(SMALL(jg,ROW(x)-1)-1)*100+99),(ROW(x)-2)*COUNT(ch)+COLUMN(第2题!$A:$H)),2)+1,ROW(第2题!$C:$C),第2题!$C:$C)&""

两个公式，如果作为结果，已经很不错了，见P:X列。

3、意犹未尽，追求完美

=INDEX(IF({0,0,0,0,0,0,0,0,1},xm,jj),ROW(x)-1,IF({1,0,0,0,0,0,0,0,0},COLUMNS(F:N),COLUMN(F:N)-6))

由不等宽的两个数组xm、jj，构造出新的数组作为最后结果，好功力!

 

[此贴子已经被山菊花于2007-3-31 0:32:59编辑过]

chrisfang

答案已发送至邮箱，请查收。

解答一：姓名列公式使用了多单元格数组公式，为内存数组的输出。姓名和奖券的错误值均没有进行处理。

解答二：姓名列公式使用了普通数组公式，减少了一个定义名称。错误值同样没有处理。

解答三：在解答二的基础上，在公式中增加了错误值的处理。

 

===================

data=IF((MATCH(第2题!$B$2:$B$41,第2题!$B$2:$B$41,)<>ROW(第2题!$B$2:$B$41)-1)*(MATCH(第2题!$B$2:$B$41&第2题!$C$2:$C$41,第2题!$B$2:$B$41&第2题!$C$2:$C$41,)=ROW(第2题!$B$2:$B$41)-1),1+MATCH(第2题!$B$2:$B$41,第2题!$B$2:$B$41,))

以剔除只有一种奖券的人为切入口，上面的公式返回得到两种以上奖券姓名首次出现的行号。

以常规思路，将data结果压缩为一个纯数字数组，提供单元格公式调用：

order=SMALL(data,ROW(INDIRECT("1:"&SUM(--ISNUMBER(data)))))

F2=INDEX(B:B,SMALL(IF(FREQUENCY(order,order),order),ROW(INDIRECT("1:"&SUM(--(FREQUENCY(order,order)>0))))))

这一步，可以改为以下公式，省去中间结果order：

F2=INDEX(B:B,MIN(IF(data>MATCH(F1,B:B,),data)))

数组公式，按Ctrl+Shift+Enter结束。

参见附件1Z列结果。

 

[此贴子已经被山菊花于2007-3-31 21:32:49编辑过]

gvntw

1、姓名

F2=INDEX(B:B,SMALL(IF((MATCH(B$2:B$41,B$2:B$41,)=ROW($2:$41)-1)*(MMULT((B$2:B$41=TRANSPOSE(B$2:B$41))*(C$2:C$41<>TRANSPOSE(C$2:C$41)),ROW($2:$41)^0)),ROW($2:$41),65536),ROW(1:1)))&""

答案没有使用自定义名称，感觉中，公式也不是特别的长，可读性还是很强的。

从列表中根据给定的条件返回姓名，公式根据条件返回两个数组：

A、姓名在B:B区域中是否首次出现：

MATCH(B$2:B$41,B$2:B$41,)=ROW($2:$41)-1

B、B2:B41每一人对应得到其它奖券的张数：

MMULT((B$2:B$41=TRANSPOSE(B$2:B$41))*(C$2:C$41<>TRANSPOSE(C$2:C$41)),ROW($2:$41)^0)

如果符合两个条件：姓名首次出现+其它奖券张数大于0，则返回对应的行号。

最后用INDEX()根据行号返回B列对应位置的姓名。

 2、奖券

根据F列的姓名列表，筛选以姓名为条件的不重复奖券。方法比较常见，这里不多说了，详见附件。

 

[此贴子已经被山菊花于2007-3-31 22:55:03编辑过]

mxqchina

答案已发送，请评分。请评第二题

惭愧呀,翻遍贴子才作出来

 

======================

 翻遍帖子作出来也不错。

走了点弯路，参见上面帖子，有时间可把思路重新梳理一遍。

[此贴子已经被山菊花于2007-3-31 23:02:36编辑过]

chat163

答案已发送，请评分。

 麻烦老师给看看，谢谢！

[em04]

================

谢谢chat163，给我们提供了一个新的思路：

相同奖券的张数：

t =MMULT(--(data2&data3=TRANSPOSE(data2&data3)),ROW(data2)^0)

得到奖券总张数：

=COUNTIF(data2,data2)

两个公式的结果是相同行数的一维数组，可对应比较，如果相同奖券张数等于得到奖券总张数，则表示得到的奖券只有一种，按要求，应该剔除。

下面公式得到符合条件的所有姓名所在的行号：

=IF((t<COUNTIF(data2,data2))*(MATCH(data2,data2,0)=ROW(data2)-1),ROW(data2))

返回奖券内容的公式写得也不错：

G2=INDEX($C:$C,MIN(IF((COUNTIF($F2:F2,data3)=0)*(data2=$F2),ROW(data2),65536)))

见附件：

 

[此贴子已经被山菊花于2007-3-31 23:40:57编辑过]