【69期】根据关键字设置数据有效性

jssy

元旦假期，再出一道题为大家欢度新年！祝大家新年快乐，工作顺利，身体健康，学有所成！

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

题目说明：   



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


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

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

答案：
2003版：
2010版：

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

ws19569858

- -新手坐等答案

zhangjimfu

操作步骤：

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

mn860429

来参加

wcymiss

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


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


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

jxrbz

新年快乐！
没有看到动画，所以答题离题了。本想用2010版本再做一个，但这台电脑上没有，只好用2003版做最后一个答案。
建议：版主开放权限，用户可以编辑、删除自己上传的压缩文件。有助于版面整齐明了。


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

jackysyh

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

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

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

gdfcx

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

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

涅磐86970

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

duwei9981

答案见附件！
制作步骤：
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 编辑 ]