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经典代码实践指南
楼主: jssy

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

[复制链接]

TA的精华主题

TA的得分主题

发表于 2011-1-5 15:57 | 显示全部楼层
步骤见附件

本帖子中包含更多资源

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

x

评分

参与人数 1财富 +50 技术 +1 收起 理由
jssy + 50 + 1 思路正确,但辅助列引用区域有缺陷,价格公 ...

查看全部评分

TA的精华主题

TA的得分主题

发表于 2011-1-5 23:30 | 显示全部楼层
。。。。。。。。

[ 本帖最后由 草民1982111 于 2011-1-7 03:43 编辑 ]

本帖子中包含更多资源

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

x

TA的精华主题

TA的得分主题

发表于 2011-1-6 08:31 | 显示全部楼层
操作主要要点:
1、在点菜单工作表中:设置数据有效性时,必须在“出错警告”页取消“输入无效信息时显示出错警告”前面的勾,以便有效单元允许输入关键字;
2、在辅助列第1个单元使用CELL("contents")函数,CELL("contents")函数可自动引用工作表中任何单元第1个变化单元格的内容,也包括输入关键字单元格的内容;
3、辅助列的其它单元为调用以关键字的菜名
4、点菜单中数字有效引用辅助列

本帖子中包含更多资源

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

x

评分

参与人数 1财富 +20 技术 +1 收起 理由
jssy + 20 + 1 思路正确,但公式细节未处理好

查看全部评分

TA的精华主题

TA的得分主题

发表于 2011-1-6 11:56 | 显示全部楼层

本帖子中包含更多资源

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

x

TA的精华主题

TA的得分主题

发表于 2011-1-6 14:51 | 显示全部楼层
请先生指教!!!

本帖子中包含更多资源

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

x

TA的精华主题

TA的得分主题

发表于 2011-1-6 15:42 | 显示全部楼层
函数太菜了,只好偷山菊花老师的精华贴的函数[em07]
http://club.excelhome.net/viewthread.php?tid=160679&highlight=%CA%FD%BE%DD%D3%D0%D0%A7%D0%D4%2B%C9%BD%BE%D5%BB%A8
菜谱表使用三个辅助列:
C列辅助列,填充公式范围:C2:C86;填充的公式:C2输入:
=IF(COUNTIF(A2,"*"&$D$2&"*")>0,ROW(),"")
下拉填充.
D列辅助列,D2输入:
=INDIRECT(CELL("address"))
E列辅助列:填充公式范围:E1:E85;(只填充到E85是因为实际上要引用的只需要85行),E1输入:
=IF(SMALL(C:C,ROW())>MAX(C:C),"",INDEX(A:A,SMALL(C:C,ROW())))
下拉填充.
定义名称TT,TT=IF(ISERROR(菜谱!$E$1),"",菜谱!$E$1:INDEX(菜谱!$E:$E,COUNTIF(菜谱!$E:$E,"*")))
在点菜单表中,
选中B3,设置数据有效性,在出错警告处取消勾选"输入无效数据时显示出错警告",在设置中,有效性条件选中序列,输入定义的名称:=TT,确定后,选中B3下拉填充.
选中B4,输入:
=IF(B3="","",IF(ISERROR(VLOOKUP(点菜单!B3,菜谱!$A:$B,2,0)),"",VLOOKUP(点菜单!B3,菜谱!$A:$B,2,0)))
下拉填充.

本帖子中包含更多资源

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

x

评分

参与人数 1财富 +20 技术 +1 收起 理由
jssy + 20 + 1 筛选的公式可以再优化一些!

查看全部评分

TA的精华主题

TA的得分主题

发表于 2011-1-6 18:11 | 显示全部楼层
我的好麻烦呀~~版主不要嫌烦
1、在点菜单工作表E2输入以下公式
  1. =IF(COLUMN(C1)<LOOKUP("座",$B:$B,ROW(A:A)),OFFSET($B$2,COLUMN(A1),),"")&""
复制代码
2、在该单元格下方的E3输入以下公式
  1. =IF(COLUMN(C1)<LOOKUP("座",$B:$B,ROW(A:A)),INDEX(菜谱!$A:$A,SMALL(IF(ISERR(FIND(E$2,菜谱!$A$2:$A$86)),999,ROW($2:$86)),ROW(E1))),"")&""
复制代码
向下向右填充到足够大的区域
3、选择B3:B17单元格区域,数据选项卡——数据有效性——允许下拉中选中序列——输入以下公式=OFFSET($D$2,,MATCH($B3,$2:$2,)-4,COUNTIF(OFFSET($D$3,,MATCH($B3,$2:$2,)-4,99),">吖"))
4、在数据有效性选项卡中点击“出错警告”——取消勾选输入无效数据时显示出错信息——确定
5、隐藏表格的辅组列区域

说明:附件为2010文件,打开时需要计算,可能较慢

[ 本帖最后由 Zaezhong 于 2011-1-11 12:20 编辑 ]

本帖子中包含更多资源

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

x

TA的精华主题

TA的得分主题

发表于 2011-1-6 20:26 | 显示全部楼层
好像是这样,交卷!

本帖子中包含更多资源

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

x

TA的精华主题

TA的得分主题

发表于 2011-1-6 22:57 | 显示全部楼层

请老师指点:

本帖子中包含更多资源

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

x

TA的精华主题

TA的得分主题

发表于 2011-1-7 21:22 | 显示全部楼层
您需要登录后才可以回帖 登录 | 免费注册

本版积分规则

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

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

GMT+8, 2019-3-24 20:58 , Processed in 0.108803 second(s), 11 queries , Gzip On, MemCache On.

Powered by Discuz! X3.4

© 2001-2017 Wooffice Inc.

   

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

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

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