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考勤计算系统
查看: 18377|回复: 80

[Excel 函数与公式] 【69期】根据关键字设置数据有效性

[复制链接]

TA的精华主题

TA的得分主题

发表于 2011-1-2 23:03 | 显示全部楼层 |阅读模式
元旦假期,再出一道题为大家欢度新年!祝大家新年快乐,工作顺利,身体健康,学有所成!

其实这是一道函数和基本操作相结合的综合题,函数公式和基本操作本身应该并不难,但结合起来实现要求的功能,则需要花功夫了。祝大家答题顺利![em07]

题目说明:   



根据关键字设置数据有效性


                                                  
                                                      
答题要求:                                                        
1、根据所给文件中的“菜谱”工作表,在“点菜单”工作表中制作,根据菜名的关键字设置数据有效性,要求:在输入关键字后,数据有效性下拉列表中实时只出现包含有关键字的菜名列表供选择,当选定菜名后,自动取得相应的菜价。
2、只使用函数和基本操作实现相关设置,不得使用VBA。(可以使用辅助列)                       
3、写出操作步骤或GIF动画
4、补充说明:(1)“点菜单”中点菜时,可以从任意行开始,而不便连续点选;(2)可以任意插入行,而数据有效性不受影响。                                                  

                                             
                        
评分:                                                                       
1、结果正确得2分,精彩答案另加分。
2、为鼓励大家学习使用EXCEL2010,使用Excel2010完成本题并答案正确的,可以加分。

答案:
2003版:
2010版:

[ 本帖最后由 jssy 于 2011-2-6 20:55 编辑 ]

本帖子中包含更多资源

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

x

评分

参与人数 3财富 +30 鲜花 +4 技术 +2 收起 理由
418775812 + 2 太强大了
现在不在线 + 2
willin2000 + 30 + 2 竞赛题.

查看全部评分

TA的精华主题

TA的得分主题

发表于 2011-1-2 23:37 | 显示全部楼层

TA的精华主题

TA的得分主题

发表于 2011-1-3 15:47 | 显示全部楼层
操作步骤:

1、在点菜单B3:B17找到需要查找的值,放到固定位置I2。
公式:
I2=IF(OR(LEN(B3:B17)=1),INDEX(B3:B17,SMALL(IF(LEN(B3:B17)=1,ROW(1:15)),1)),"")

2、根据I2单元格中的字符,查出菜谱工作中所有对应的菜名,放到固定位置H4:H88。
公式:
H4:H88=INDEX(菜谱!$A$2:$A$87,SMALL(IF(ISNUMBER(FIND($I$2,菜谱!$A$2:$A$86)),ROW($1:$85),86),ROW($1:$85)))&""

3、定义动态序列名称。
公式:
辅助列=OFFSET(点菜单!$H$3,1,,SUM(--(LEN(点菜单!$H$4:$H$90)>0)),)

4、为点菜单B3:B17添加到数据有效性--序列--引用定义的名称,出错警告--输入无效数据时显示出错警告--去掉勾选。

5、点菜单c3:c17区域,查找菜品对应的价格。
公式:
=IF(B3<>"",VLOOKUP(点菜单!B3,菜谱!$A$2:$B$86,2,),"")

6、计算合计。
公式:
=IF(COUNT(C3:C17)>0,SUM(C3:C17),"")



补充:



[ 本帖最后由 zhangjimfu 于 2011-1-10 09:47 编辑 ]

本帖子中包含更多资源

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

x

TA的精华主题

TA的得分主题

发表于 2011-1-3 18:56 | 显示全部楼层
来参加[em01]

本帖子中包含更多资源

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

x

TA的精华主题

TA的得分主题

发表于 2011-1-3 23:23 | 显示全部楼层
操作步骤:
1、添加辅助工作表,在对应“点菜单”表里的行内写入公式。公式利用find函数,将不包含关键字的菜谱排除;利用small函数的第二参数,将“菜谱”表内的列菜单变为行菜单。
2、定义名称“菜单”,名称的公式将辅助行后面的无效数据利用countif统计去除。并且名称内的行引用都为相对引用。
3、建立数据有效性的序列,并设置不进行错误警告。
4、价格列添加vlookup公式,并进行容错处理。


再添加个利用宏表函数的,不知算不算违规:
利用REFTEXT及ACTIVE.CELL()这两个宏表函数,结合now()易失函数,取出活动单元格的数据进行find函数判断。其他都和上述差不多。优点:只用一行辅助,运算速度可能较快


[ 本帖最后由 wcymiss 于 2011-1-3 23:50 编辑 ]

本帖子中包含更多资源

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

x

TA的精华主题

TA的得分主题

发表于 2011-1-4 09:18 | 显示全部楼层
新年快乐!
没有看到动画,所以答题离题了。本想用2010版本再做一个,但这台电脑上没有,只好用2003版做最后一个答案。
建议:版主开放权限,用户可以编辑、删除自己上传的压缩文件。有助于版面整齐明了。


[ 本帖最后由 jxrbz 于 2011-1-6 16:30 编辑 ]

本帖子中包含更多资源

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

x

TA的精华主题

TA的得分主题

发表于 2011-1-4 12:53 | 显示全部楼层

回复 1楼 jssy 的帖子

利用了辅助表,在excel2007环境下操作的

P.S.在做题的过程中发现个问题,我想在辅助表中找出每一列最后一个非错误单元格的位置,于是用lookup("座",....)这个公式,本来想用的是lookup(char(65536),....)但发现不行,不知道是什么原因

[ 本帖最后由 jackysyh 于 2011-1-4 12:57 编辑 ]

本帖子中包含更多资源

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

x

TA的精华主题

TA的得分主题

发表于 2011-1-4 16:04 | 显示全部楼层
操作步骤:
1、在菜谱中添加D列作为辅助列,通过索引关键字,产生一个序列,供点菜单中B列数据有效性使用;
2、在B3:B17单元格设置数据有效性,序列来源:=OFFSET(INDIRECT("菜谱!D2"),,,COUNTIF(INDIRECT("菜谱!D:D"),">"""));
3、勾掉显示出错警告信息;
4、C列用公式SUMIF查找价格,最后合计;
5、可以任意插入行;可以从任意行开始,包括在下一行点选完后可以返回上一行重新点选。

[ 本帖最后由 gdfcx 于 2011-1-7 10:13 编辑 ]

本帖子中包含更多资源

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

x

评分

参与人数 1技术 +2 收起 理由
jssy + 2 答案基本正确!

查看全部评分

TA的精华主题

TA的得分主题

发表于 2011-1-4 16:41 | 显示全部楼层

试试。。。
倒是用的2010。。但除了iferror没用到相应功能- -

本帖子中包含更多资源

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

x

TA的精华主题

TA的得分主题

发表于 2011-1-5 10:36 | 显示全部楼层
答案见附件!
制作步骤:
1.添加辅助列将菜单按关键字分类
2.选中点菜单中B3:B17设置数据有效性-序列-来源输入公式=OFFSET($A$22:$A$38,,MATCH(B3,$A$21:$Q$21,)-1) -确认
3.在单价列内输入公式=IF(ISNA(VLOOKUP(B3,菜谱!A:B,2,0)),0,VLOOKUP(B3,菜谱!A:B,2,0))
4.单价汇总:=SUM(C3:C17)
更新红色字体(返回0值自动出现-)

[ 本帖最后由 duwei9981 于 2011-1-15 08:45 编辑 ]

本帖子中包含更多资源

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

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

本版积分规则

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

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

GMT+8, 2019-3-21 01:34 , Processed in 1.472367 second(s), 24 queries , Gzip On.

Powered by Discuz! X3.4

© 2001-2017 Wooffice Inc.

   

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

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

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