表格里面的数据有效性怎么设置

我们都知道在Excel中如何利用数据验证功能制作级联选择列表，但是这个方法有一个小缺陷。今天我给大家介绍一下这个缺陷，以及应该如何制作无缺陷的级联列表。

制作级联列表

假设我们有数据如下：

A1:E1是5个事业部，下面列出了每个事业部的各部门。I列是将事业部转置成为列排列。

我们创建如下的名称（关于如何创建名称，请参见这篇文章）：

我们分别创建了一个叫做“事业部”的名称，包含所有事业部，和各个事业部的名称，包含各自的部门。

接下来，我们为事业部的输入单元格设置数据验证：

为级联的二级部门设置数据验证：

这里，我们通过一个函数INDIRECT将C2的选择值转化为对应的事业部名称。

这样我们就实现了级联选择：

有个小问题

这个级联列表有个小问题：

在上面的画面中，我们看到了一种状态，事业部是“美洲区域中心”，部门是“采购部”。

但是实际上，美洲区域中心根本没有采购部：

这个采购部是怎么选出来的？其实是当事业部是“产品设计制造本部”时，部门选择了“采购部”，然后又回头将事业部选择为“美洲区域中心”，就出现了这种状况。

这种级联列表没有办法在选择第一级（事业部）的时候就对下面的二级选择（部门）做出操作，只有手动选择二级部门时才会去根据第一级的选择做反应。

解决方法

大部分人对这个问题的解决方法是写程序。其实，如果我们使用组合框来做这个级联列表的话，就可以避免这种情况。

首先，我们做第一级选择列表。

在“开发工具”选项卡下，点击插入“组合框”：

通过鼠标拖拽在工作表中插入组合框：

在组合框中，点击右键，点击设置控件格式在其中，将数据源区域设置为：事业部，单元格连接设置为：$J$2

点击确定，第一级设置就完成了：

现在添加一个新的名称：选择事业部，

其中引用位置处使用公式：

=INDIRECT(INDEX(事业部,Sheet3!$J$2))

然后添加第二个组合框作为列表选择，并将其数据源和链接单元格进行如下设置：

点击确定，完成设置：

当做出第一级选择时，第二级选择就会跟着改变：

不会出现第一级选择改变了，第二级选择还保留原来的选项这种情况了。

怎么才能快速掌握好用的Excel技巧！

怎么才能做出高大上的专业图表！

怎么才能不用天天加班做数据！

怎么才能成为Excel高手！

如果你正在为这些问题而苦恼，可以了解一下“E学会”Excel学习俱乐部，免费学习19门售价超过3000元的Excel系统学习课程，免费获得商务图表设计手册，并且有专家一对一免费帮你诊断和设计工作表格模板。

赞 (0)

相关推荐

  • excel表格怎么去除数据有效性的设置?

    今天要和大家分享的是:Excel如何取消数据有效性的设置?下面是动画演示教程, 1.首先打开数据表格,发现单元格设定了数据有效性,只能输入是或者否,如下图所示: 2.选中设置有效性的数据区域,如下图所 ...

  • 表格数据有效性怎么设置多选（表格里面的数据有效性怎么设置）

    表格数据有效性怎么设置多选（表格里面的数据有效性怎么设置）

    Excel如何进行多层次联动数据有效性设置谢谢邀请.我是汤帅,一个PPT自由设计师,承接PPT定制.下拉菜单有时候因为原始数据过多,它的作用就并不是很明显.选项过于繁复,理性输入人会舍弃下拉选取数据, ...

  • 数据有效性怎么引用数据来源（表格里面的数据有效性怎么设置）

    数据有效性怎么引用数据来源（表格里面的数据有效性怎么设置）

    终于等到你了,我是爱踢汪.我们常用数据有效性做下拉菜单,应用数据做序列时,Excel默认只能引用一行或者一列数据.那如果你要引用三行或者三列数据,怎么办?用名称!采用名称,别说三列,就是1000列也可 ...

  • wps表格数据有效性怎么设置

    最近单位统一换了WPS办公软件，感觉比Office启动快，但是一些东西不太习惯，在wps表格数据有效性怎么设置呢? 回答： 这个问题，官方专门制作了动画演示教程，让您一看就会哦，wps表格数据有效性设 ...

  • excel表格怎么进行数据有效性设置

    excel表格中设置了数据有效性,可以提高表格填写的速度与准确率,那么excel表格怎么进行数据有效性设置呢? 操作方法 01 第一步,打开需要设置的excel表格,如图所示 02 第二步,选中菜单栏 ...

  • Excel跨表数据有效性如何设置和引用？

    在实际工作过程中,我们经常需要编制各种模板供其他人填写.所以,利用数据有效性制作可供选择的下拉菜单就是一个很常用的方法.一般来说,我们会制作一个包含全部基本信息的表,表单列示所有的备选项目,比如说姓名 ...

  • 在excel表格中利用数据有效性进行快速的输入

    为了减少重复输入相同数据,我们通常使用数据有效性来进行快速的输入,但是很多人不知道怎么操作,一起来学习吧! 步骤 1.数据有效性对于表格的输入文字是非常方便快捷的,学会灵活的应用它将会使你节省大量的时 ...

  • EXCEL如何添加数据有效性及设置下拉字体颜色

    EXCEl是一种很常用的文字编辑工具,它能帮助我们很轻松的制作自己所需要的表格,也能制作出自己所需要的很多文本.这是很多担任文职工作者的很重要的.很强大的文字编辑工具.不过对于初学者来说,EXCEl虽 ...

  • Excel如何取消数据有效性的设置？

    今天要和大家分享的是:Excel如何取消数据有效性的设置? 操作方法 01 首先打开数据表格,发现单元格设定了数据有效性,只能输入是或者否,如下图所示: 02 选中设置有效性的数据区域,如下图所示: ...