ExcelHome技术论坛

 找回密码
 免费注册

QQ登录

只需一步,快速开始

   
EH云课堂-专业的职场技能充电站 限时送,魔方网表将Excel变在线系统 Excel服务器-会Excel,做管理系统 Excel Home精品图文教程库
Excel不给力? 何不试试FoxTable! Excel 2016函数公式学习大典 高效办公必会的Office实战技巧 免费下载Excel行业应用视频
300集Office 2010微视频教程 Tableau-数据可视化工具 ExcelHome出品 - VBA代码宝免费下载 13门Excel免费公开课任你学
你的Excel 2010实战技巧学习锦囊 欲罢不能, 过目难忘的 Office 新界面 免费的Excel考勤计算系统
查看: 6233|回复: 24

[Excel 函数与公式] [第21期]春暖花开,恭喜你!

[复制链接]

TA的精华主题

TA的得分主题

发表于 2007-3-2 00:28 | 显示全部楼层 |阅读模式

难度:★★★


 

[此贴子已经被作者于2007-4-1 23:47:19编辑过]
单选投票, 共有 11 人参与投票

距结束还有: 5653 天13 小时40 分钟

您所在的用户组没有投票权限

本帖子中包含更多资源

您需要 登录 才可以下载或查看,没有帐号?免费注册

x

TA的精华主题

TA的得分主题

发表于 2007-3-3 06:28 | 显示全部楼层

答案已发送至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编辑过]

本帖子中包含更多资源

您需要 登录 才可以下载或查看,没有帐号?免费注册

x

TA的精华主题

TA的得分主题

发表于 2007-3-3 13:15 | 显示全部楼层

答案已发送,请评分

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

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

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编辑过]

本帖子中包含更多资源

您需要 登录 才可以下载或查看,没有帐号?免费注册

x

TA的精华主题

TA的得分主题

发表于 2007-3-3 18:29 | 显示全部楼层

答案已发送,请评分。少于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编辑过]

本帖子中包含更多资源

您需要 登录 才可以下载或查看,没有帐号?免费注册

x

TA的精华主题

TA的得分主题

发表于 2007-3-4 15:17 | 显示全部楼层

答案已经以邮件的形式发至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编辑过]

本帖子中包含更多资源

您需要 登录 才可以下载或查看,没有帐号?免费注册

x

TA的精华主题

TA的得分主题

发表于 2007-3-6 20:25 | 显示全部楼层

逍遥自在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编辑过]

本帖子中包含更多资源

您需要 登录 才可以下载或查看,没有帐号?免费注册

x

TA的精华主题

TA的得分主题

发表于 2007-3-7 10:57 | 显示全部楼层

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

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

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

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

 

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

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编辑过]

本帖子中包含更多资源

您需要 登录 才可以下载或查看,没有帐号?免费注册

x

TA的精华主题

TA的得分主题

发表于 2007-3-7 16:54 | 显示全部楼层

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编辑过]

本帖子中包含更多资源

您需要 登录 才可以下载或查看,没有帐号?免费注册

x

TA的精华主题

TA的得分主题

发表于 2007-3-9 13:06 | 显示全部楼层

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

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

 

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

 翻遍帖子作出来也不错。

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


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

本帖子中包含更多资源

您需要 登录 才可以下载或查看,没有帐号?免费注册

x

TA的精华主题

TA的得分主题

发表于 2007-3-9 20:47 | 显示全部楼层

答案已发送,请评分。

 麻烦老师给看看,谢谢!

[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编辑过]

本帖子中包含更多资源

您需要 登录 才可以下载或查看,没有帐号?免费注册

x
您需要登录后才可以回帖 登录 | 免费注册

本版积分规则

关注官方微信,每天学会一个新技能

手机版|关于我们|联系我们|ExcelHome

GMT+8, 2019-1-23 10:48 , Processed in 1.118986 second(s), 30 queries , Gzip On.

Powered by Discuz! X3.4

© 2001-2017 Wooffice Inc.

   

沪公网安备 31011702000001号 沪ICP备11019229号

本论坛言论纯属发表者个人意见,任何违反国家相关法律的言论,本站将协助国家相关部门追究发言者责任!     本站特聘法律顾问:徐怀玉律师 李志群律师

快速回复 返回顶部 返回列表