ExcelHome技术论坛

 找回密码
 免费注册

QQ登录

只需一步,快速开始

   
EH云课堂-专业的职场技能充电站 限时送,魔方网表将Excel变在线系统 Excel服务器-会Excel,做管理系统 Excel Home精品图文教程库
Excel不给力? 何不试试FoxTable! Excel 2016函数公式学习大典 高效办公必会的Office实战技巧 免费下载Excel行业应用视频
300集Office 2010微视频教程 Tableau-数据可视化工具 精品推荐-800套精选PPT模板,点击获取 ExcelHome出品 - VBA代码宝免费下载
你的Excel 2010实战技巧学习锦囊 欲罢不能, 过目难忘的 Office 新界面 Excel VBA经典代码实践指南
楼主: 山菊花

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

[复制链接]

TA的精华主题

TA的得分主题

发表于 2007-3-9 22:58 | 显示全部楼层

答案已发送,请评分。

------------------

前面有类似答案,不作详细点评。


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

本帖子中包含更多资源

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

x

TA的精华主题

TA的得分主题

发表于 2007-3-11 00:08 | 显示全部楼层

答案已发送,请老师两题都评点一下.谢谢!!!

 

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

1、第1题

=SUM(--(MATCH($B$2:$B$31&$C$2:$C$31,$B$2:$B$31&$C$2:$C$31,0)=ROW($B$2:$B$31)-1))-SUM(1/COUNTIF($B$2:$B$31,$B$2:$B$31))

思路不错。

前一部分的sum(),求得姓名与班次均不重复的记录数。后一部分的sum(),求得所有姓名的不重复记录数。

相减的结果得到重复的姓名数,即既上日班又上夜班的人数。

2、第2题

公式写复杂了,类似公式参见7楼chrisfang版主的答案。

 

[此贴子已经被山菊花于2007-4-1 0:09:11编辑过]

本帖子中包含更多资源

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

x

TA的精华主题

TA的得分主题

发表于 2007-3-14 08:59 | 显示全部楼层

 [em09][em09]答案。

答案已发送,请评分。

 

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

第1题结果不正确。

第2题解题思路正确,用下面公式返回得两种以上奖券的不重复姓名:

 =MMULT((a=TRANSPOSE(第2题!姓名))*(b<>TRANSPOSE(奖劵))*1,ROW(第2题!14:53)^0)

但其中先求得a、b两个结果,走了一段弯路。

a =LOOKUP(SMALL(IF(MATCH(第2题!姓名,第2题!姓名,0)=ROW(第2题!姓名)-1,ROW(第2题!姓名)-1),ROW(INDIRECT("1:"&SUM(1/COUNTIF(第2题!姓名,第2题!姓名))))),ROW(第2题!姓名)-1,第2题!姓名)

求得结果是B列中不重复的姓名。

b =LOOKUP(SMALL(IF(MATCH(第2题!姓名,第2题!姓名,0)=ROW(第2题!姓名)-1,ROW(第2题!姓名)-1),ROW(INDIRECT("1:"&SUM(1/COUNTIF(第2题!姓名,第2题!姓名))))),ROW(第2题!姓名)-1,第2题!$C$2:$C$41)

该结果是不重复姓名对应C列的结果,即每人第一次得到的奖券。

请参考gvntw版主的答案。

查找奖券内容的公式不够简洁,可能受前面思路影响,把问题想复杂了。


[此贴子已经被山菊花于2007-4-1 9:57:03编辑过]

本帖子中包含更多资源

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

x

TA的精华主题

TA的得分主题

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

占位

答案已发送,请评分。

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

答题正确,过程复杂。

1、求出所有名字第一次出现的行号

=(MATCH(B2:B41,B2:B41,0)=A2:A41)*A2:A41

2、求出得到两种以上奖券的人第一次出现的行号

=(MATCH(B2:B41,B2:B41,0)<A2:A41)*(MATCH(B2:B41&C2:C41,B2:B41&C2:C41,0)=A2:A41)*MATCH(B2:B41,B2:B41,0)

3、根据前两步的结果,求出不重复的姓名所在行号:

=SMALL(IF(ISNUMBER((MATCH((MATCH(B2:B41,B2:B41,0)=A2:A41)*A2:A41,(MATCH(B2:B41,B2:B41,0)<A2:A41)*(MATCH(B2:B41&C2:C41,B2:B41&C2:C41,0)=A2:A41)*MATCH(B2:B41,B2:B41,0),0)>0)*(MATCH(B2:B41,B2:B41,0)=A2:A41)*A2:A41),--TEXT((MATCH((MATCH(B2:B41,B2:B41,0)=A2:A41)*A2:A41,(MATCH(B2:B41,B2:B41,0)<A2:A41)*(MATCH(B2:B41&C2:C41,B2:B41&C2:C41,0)=A2:A41)*MATCH(B2:B41,B2:B41,0),0)>0)*(MATCH(B2:B41,B2:B41,0)=A2:A41)*A2:A41,"0;;41;"),41),ROW(1:41))

这一步的处理复杂了。根据第2步的结果,可以这样生成结果:

F26=IF(F25="","",INDEX(B:B,MIN(IF((MATCH(B$2:B$41,B$2:B$41,)<A$2:A$41)*(MATCH(B$2:B$41&C$2:C$41,B$2:B$41&C$2:C$41,0)=A$2:A$41)*MATCH(B$2:B$41,B:B,)>MATCH(F25,B:B,),(MATCH(B$2:B$41,B$2:B$41,)<A$2:A$41)*(MATCH(B$2:B$41&C$2:C$41,B$2:B$41&C$2:C$41,0)=A$2:A$41)*MATCH(B$2:B$41,B:B,),65536))))&""

返回奖券内容的公式有新意,学习:

G2=INDEX($C$2:$C$65536,SMALL((($B$2:$B$41<>F2)+(MATCH($B$2:$B$41&$C$2:$C$41,$B$2:$B$41&$C$2:$C$41,0)<>$A$2:$A$41))*100+$A$2:$A$41,COLUMN($A:$H)))&""

在A列序号的基础上,姓名不等于F2的,加100(让其排在后面),如果奖券结果不是第一次出现的,也加上100。最后结果按从小到大排序,根据返回的行号,用INDEX()取交叉值就是所要的结果。


[此贴子已经被山菊花于2007-4-1 11:19:37编辑过]

本帖子中包含更多资源

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

x

TA的精华主题

TA的得分主题

发表于 2007-3-16 09:04 | 显示全部楼层

答案已发送,请评分。论坛出了什么问题,怎么有字数限制啊?。。。

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

1、灵感

既然是奖品,那一定物有所值。把不同的奖品以不同的数值取代:

MATCH(C$2:C$41,C$2:C$41,),效果如下图:


[此贴子已经被山菊花于2007-4-1 13:43:03编辑过]

本帖子中包含更多资源

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

x

TA的精华主题

TA的得分主题

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

答案已发送,这贴真难发啊。

发,发,发。

 

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

与7楼、14楼的公式有点相似,但也不尽相同:

出现第二种以上奖券时,返回姓名第一次出现的行号:

A=IF(MATCH(第2题!$B$2:$B$41,第2题!$B$2:$B$41,0)<>MATCH(第2题!$B$2:$B$41&第2题!$C$2:$C$41,第2题!$B$2:$B$41&第2题!$C$2:$C$41,0),MATCH(第2题!$B$2:$B$41,第2题!$B$2:$B$41,0),100)

排序:

B=SMALL(A,ROW(第2题!$1:$40))

在单元格公式中,用“IF(MATCH(B,B,0)=ROW(1:40),B,100)”取出不重复的行号。

在公式中使用常量,不合题意:

J={"风";"和";"日";"丽";"春";"暖";"花";"开"}

=IF(COUNT(MATCH(J,IF(F2=$B$2:$B$41,$C$2:$C$41),0))>=COLUMN(A:H),INDEX(IF(F2=$B$2:$B$41,$C$2:$C$41),SMALL(IF(ISNA(MATCH(J,IF(F2=$B$2:$B$41,$C$2:$C$41),0)),"",MATCH(J,IF(F2=$B$2:$B$41,$C$2:$C$41),0)),COLUMN(A:H))),"")

 


[此贴子已经被山菊花于2007-4-1 14:13:48编辑过]

本帖子中包含更多资源

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

x

TA的精华主题

TA的得分主题

发表于 2007-3-17 22:07 | 显示全部楼层

答案已发送,请评分。                                    

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

做得复杂了。   


[此贴子已经被山菊花于2007-4-1 14:44:37编辑过]

本帖子中包含更多资源

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

x

TA的精华主题

TA的得分主题

发表于 2007-3-19 11:14 | 显示全部楼层

答案以邮件的形式发至dsjb@21cn.com

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

1、剔除各人重复奖券的记录:

ROW=SMALL(IF(MATCH(DATA&"|"&OFFSET(DATA,,1),DATA&"|"&OFFSET(DATA,,1),)=ROW(DATA)-1,ROW(DATA)),ROW(INDIRECT("1:"&COUNT(1/(MATCH(DATA&"|"&OFFSET(DATA,,1),DATA&"|"&OFFSET(DATA,,1),)=ROW(DATA)-1)))))

N=LOOKUP(ROW,ROW(DATA),DATA)

前者是不重复记录对应的行号,后者是对应的姓名。

2、计算得到不同奖券的张数:

ROW_NAME=MMULT(TRANSPOSE(--(N=TRANSPOSE(N))),ROW^0)

3、返回得到2种以上奖券的记录,并剔除重复的结果:

ROW_C=IF((ROW_NAME>=2)*(MATCH(N,N,)=ROW(INDIRECT("1:"&ROWS(N)))),ROW)

4、生成名单:

F2=LOOKUP(SMALL(ROW_C,ROW(INDIRECT("1:"&COUNT(ROW_C)))),ROW(DATA),DATA)

提取奖券内容,设计了两个公式:

G2=INDEX($C:$C,SMALL(IF(ISNUMBER(1/(MATCH($F2&OFFSET(DATA,,1),DATA&OFFSET(DATA,,1),)=ROW(DATA)-1)),ROW(DATA),65536),COLUMN(A:A)))

Q2=LOOKUP(SMALL(IF(N=$P2,ROW),COLUMN(INDIRECT("c1:c"&COUNT(1/(N=$P2)),))),ROW(DATA),OFFSET(DATA,,1))

 两个公式各有奥妙之处,值得学习。


[此贴子已经被山菊花于2007-4-1 15:15:14编辑过]

本帖子中包含更多资源

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

x

TA的精华主题

TA的得分主题

发表于 2007-3-21 09:12 | 显示全部楼层

答案已发送,请版主评分,谢谢。答案已发送,请版主评分,谢谢。答案已发送,请版主评分,谢谢。

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

答第1题

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

data4=SUMPRODUCT((N(MATCH(第1题!$B$2:$B$31&第1题!$C$2:$C$31,第1题!$B$2:$B$31&第1题!$C$2:$C$31,)=ROW(第1题!$B$2:$B$31)-1)))

E2=data4-SUM(1/COUNTIF(B2:B31,B2:B31))

与12楼答案相同,正确。

[此贴子已经被山菊花于2007-4-1 15:21:03编辑过]

TA的精华主题

TA的得分主题

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

答案发送了,不过水平有限啊,请山菊花老师评定。谢谢了。

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

QUOTE:

答案错了。

——山菊花

[此贴子已经被山菊花于2007-4-1 15:25:07编辑过]
您需要登录后才可以回帖 登录 | 免费注册

本版积分规则

关注官方微信,高效办公专列,每天发车

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

GMT+8, 2019-3-24 21:22 , Processed in 0.095039 second(s), 16 queries , Gzip On, MemCache On.

Powered by Discuz! X3.4

© 2001-2017 Wooffice Inc.

   

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

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

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