韩老师讲一种二级联动菜单的做法,这样做出来的菜单,不管是一级还是二级,选项都是可以随意添加、删除、修改的。 效果如下: 实现方法 第一步:定义名称 1、定义一级菜单名称: 在【公式】菜单下,选择【定义名称】,在【新建名称】对话框内输入:
如下图: 2、定义二级菜单名称: 在【公式】菜单下,选择【定义名称】,在【新建名称】对话框内输入:
如下图: 此处插播一则广告: 广告过后,继续学习…… 第二步: 数据验证设置一级二级菜单 1、设置一级菜单: 鼠标放在要做一级菜单的单元格,在【数据】菜单中选【数据验证】,在跳出的【数据验证】对话框中的【设置】选择卡中,选择验证条件为允许【序列】,来源为“=一级”,确定,如下图: 2、设置二级菜单: 鼠标放在要做二级菜单的单元格,在【数据】菜单中选【数据验证】,在跳出的【数据验证】对话框中的【设置】选择卡中,选择验证条件为允许【序列】,来源为“=二级”,确定,如下图: 通过以上两步的设置,不管使添加选项还是删除选项,一级与二级菜单都跟着改变。 公式解析 1、一级菜单公式: =OFFSET(源数据!$A$1,,,1,COUNTA(源数据!$1:$1)) 该公式的含义是:以数据源表中的A2单元格为基准,偏移到1行、COUNTA(源数据!$1:$1)列的区域。其中,COUNTA(源数据!$1:$1)的返回值随着第一行数据的增多二增多,可以实现一级菜单的动态数量变化。 2、二级菜单公式: =OFFSET(源数据!$A$2,,MATCH(联动菜单!$A2,一级,0)-1,COUNTA(OFFSET(源数据!$A$2,,MATCH(联动菜单!$A2,一级,0)-1,200))) 该公式的含义是:以数据源表中的A2单元格为基准,偏移到0行、MATCH(联动菜单!$A2,一级,0)-1列以后的单元格,然后取该单元格所在列的行数,列数为200的区域,此处200为比较的数,可以根据实际情况变化。 如以下表格是一个报名表,工作表名称为sheet1,要求给列名参赛项目增加下拉菜单选项,以方便用户快速输入相关内容又可以避免用户输入不合格数据。 步骤1、先新建一个辅助的sheet2工作表,专门放比赛项目; 步骤2、将sheet2工作表的内容由区域转换为表,即选中sheet2的数据单元格区域,点击菜单的【插入】-【工作表】; 步骤3、点击菜单的【公式】-【名称管理器】,新建一个名称,任意命名,引用位置为sheet2表的A2:A10单元格区域,点击【确定】; 接下来给sheet1工作表的比赛项目设置数据有效性。 步骤4、选中sheet1工作表的比赛项目列,点击菜单【数据】-【数据有效性】-【数据有效性】; 步骤5、在跳出的【数据有效性】对话框中,选择【设置】,有效条件选择【序列】,在激活来源时,按快捷键F3键调用出粘贴名称菜单,选择上一步我们新建的名称即【比赛项目】; 步骤6、选择好后,点击【确定】; 这样就可以看到,sheet1工作表的比赛项目自动更新下拉菜单制作完成。 此时,如果在sheet2工作表添加一个比赛项目,比如拔河,可以看到在sheet1的比赛项目列的下拉菜单就会自动添加更新上去。 同样,如果我们如果在sheet2工作表删除一个比赛项目,比如乒乓球,可以看到在sheet1的比赛项目列的下拉菜单也会自动删除这个项目。 |
|