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考勤计算系统
楼主: jssy

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

[复制链接]

TA的精华主题

TA的得分主题

发表于 2011-1-10 22:21 | 显示全部楼层
主要步骤:
1、在D2构造数组公式一:
=INDIRECT("点菜单!B"&MAX(IF(INDIRECT("点菜单!B3:B"&MATCH("合计",点菜单!B:B,0)-1)<>"",ROW(INDIRECT("点菜单!B3:B"&MATCH("合计",点菜单!B:B,0)-1)),0)))

2、在F2:F86构造数组公式二、
=INDEX(A1:A87,SMALL(IF(ISERROR(FIND(D2,A2:$A$86,1)),87,ROW(A2:$A$86)),ROW(1:85)))&""

3、在点菜单表定义名称:
菜名=菜谱!$F$2:$F$86

4、在点菜单表设置数据有效性:
序列=菜名
将输入无效数据时显示出错警告的对勾去掉

5、在点菜单表C3构造价格公式,下拉:
=IF(B3="",0,VLOOKUP(B3,菜谱!A:B,2,0))

[ 本帖最后由 donghan 于 2011-1-10 22:22 编辑 ]

本帖子中包含更多资源

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

x

TA的精华主题

TA的得分主题

发表于 2011-1-11 15:17 | 显示全部楼层
有点难度
用了一个辅助列和一个名称

本帖子中包含更多资源

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

x

评分

参与人数 1技术 +1 收起 理由
jssy + 1 思路正确,公式存在细节问题!

查看全部评分

TA的精华主题

TA的得分主题

发表于 2011-1-11 17:57 | 显示全部楼层
答案说明:
1、宏表函数获取“点菜单”活动单元格的值
2、在“菜谱”工作表使用2列(C、D)辅助,公式计算列出菜谱
3、设置有效性
为了方便,使用了3个定义的名称。

附上附件供验证。

本帖子中包含更多资源

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

x

评分

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

查看全部评分

TA的精华主题

TA的得分主题

发表于 2011-1-11 22:13 | 显示全部楼层

本帖子中包含更多资源

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

x

评分

参与人数 1技术 +2 收起 理由
jssy + 2 答案正确!其实可以不必用宏表函数啥:)

查看全部评分

TA的精华主题

TA的得分主题

发表于 2011-1-12 01:43 | 显示全部楼层

TA的精华主题

TA的得分主题

发表于 2011-1-12 10:52 | 显示全部楼层
版主,辅助列有规定要放在“点菜”工作表吗??不然的话,我辅助列的公式得改用COLUMN()才可以哦~~
操作演示及结果文件附上(有点大)~~

----------------------------------------------------------------
还是把辅助列放到”点菜“工作表吧,免得扣分,数组公式如下:
  1. =INDEX(TRANSPOSE(Name),,SMALL(IF(ISERR(FIND(CELL("contents"),TRANSPOSE(Name))),86,COLUMN(A:CG)),COLUMN(A:CG)))&""
复制代码
放在23行,故有效性序列公式可省两个名称定义,如下:
  1. =OFFSET($A$23,,,,SUM(N($A$23:$CG$23<>"")))
复制代码
附件就不重新上传了!:loveliness:

[ 本帖最后由 zzmxy 于 2011-1-13 09:49 编辑 ]

本帖子中包含更多资源

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

x

评分

参与人数 1财富 +20 技术 +1 收起 理由
jssy + 20 + 1 思路正确,但公式细节存在问题!

查看全部评分

TA的精华主题

TA的得分主题

发表于 2011-1-12 13:50 | 显示全部楼层


[ 本帖最后由 李兴德 于 2011-1-13 00:39 编辑 ]

本帖子中包含更多资源

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

x

TA的精华主题

TA的得分主题

发表于 2011-1-13 15:24 | 显示全部楼层
来晚了,参与参与,请版主审阅

本帖子中包含更多资源

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

x

TA的精华主题

TA的得分主题

发表于 2011-1-13 20:04 | 显示全部楼层
我也试试,步骤在工作表中!

本帖子中包含更多资源

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

x

评分

参与人数 1技术 +1 收起 理由
jssy + 1 思路正确,但筛选公式的容错处理部分有误!

查看全部评分

TA的精华主题

TA的得分主题

发表于 2011-1-14 13:51 | 显示全部楼层
回复楼主  
         更新

[ 本帖最后由 wangzhongtu 于 2011-1-14 14:41 编辑 ]

本帖子中包含更多资源

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

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

本版积分规则

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

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

GMT+8, 2019-1-17 00:53 , Processed in 0.095129 second(s), 18 queries , Gzip On, MemCache On.

Powered by Discuz! X3.4

© 2001-2017 Wooffice Inc.

   

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

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

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