| 一份最全的excel下拉列表攻略 | 您所在的位置：网站首页 › 表格怎么设置下拉菜单选项文字颜色怎么添加 › 一份最全的excel下拉列表攻略 |
一份最全的excel下拉列表攻略
|
一级下拉列表
初级下拉列表
方法一：
首先，在Excel中选中需要添加下拉菜单的所有单元格，然后数据 - 数据工具 - 数据验证，在允许中设为序列，填入需要加入的下拉列表的值，确定即可搞定。 注意填入列表是值之间需要用英文的,隔开.本方法适合列表长度较少的情况下使用，例如只有是，否这种列表
按照上述步骤点击来源右边的按钮可以选取现有区域作为下拉列表的值。 这种方法适合列表里数据较多，或者Excel中已有列表数据的情况下使用 当下拉列表的长度是不固定的，可能隔一段时间就会在下拉列表中添加一些值。如果按照上述两种方法需要频繁的改动下拉列表的范围，非常麻烦。这个时候我们可以使用公式来实现可以随时添加和减少的下拉列表。只要在数据验证的来源对话框中写入下列公式： 这个公式表示我的下拉列表放在 drop down list 这一个 sheet 中的A列（A1单元格是标题，所以从A2开始）。快速修改的话，只需要将sheet名称改掉与列序号改掉即可（同一个sheet将sheet名称直接删掉即可）。 公式的详解为： COUNTA函数功能是返回参数列表中非空的单元格个数,COUNTA(‘drop down list’!$A:$A) 可以返回A列的非空的单元格有多少OFFSET函数的功能为以指定的引用为参照系，通过给定偏移量得到新的引用。简单来说就是可以改变选取的单元格和选取的范围。OFFSET(‘drop down list’!$A$2,COUNTA(‘drop down list’!$A:$A)-1)表示将数据范围改为A2单元格到A2单元格向下COUNTA(‘drop down list’! A : A: A:A)-1个单元格（因为是从A2开始的，所以要减1）这样每次修改列表时，counta都会计算列表中值的个数，来源中的列表区域也会自动变化 去除下拉列表的空白（不连续列表）对于列表前后有空白，但是列表连续的情况可以用上述数量有变化的下拉列表公式解决 如果列表中间有空白，如下所示，选取区域后列表里会出现很多空白如何处理呢？
增加辅助列 C，在 C1 中输入公式：=INDEX(B:B,SMALL(IF( B : B: B:B"",ROW( B : B: B:B),4^8),ROW(1:1)))&"" 按 Ctrl +Shift + Enter 让数组生效 向下拖动公式，就得到一列去除了所有空值的列表 现在我们用上述去除空白的公式配合 C 列使用就可以了 函数详解： IF + row 函数：读取 B 列不为空的行值，为空返回 4^8（4 的 8 次方）,4 的 8次方 = 65536，是 2003 版Excel 的最大行数，所以被当成惯例使用。目的是为了配合 Small 函数使用。row()函数返回当前行值SMALL 函数：将 B 列行值与当前行值比较，取其小（空值被定义成了最大行值 65536）Index 函数：读取 B 列中的非空行&""：连接空值，将结果转换为文本值按Ctrl +Shift + Enter：数组公式，必须这样按才能生效，公式前后会自动出现 {} 二级下拉列表 通过建立公式名称的下拉裁断选中所有包含信息源的单元格（记住，不要选择空白单元格哦！不然，也会被添加进入菜单中的。如果各列的长度不一致，可以先选取整个区域，在定位条件中 ，勾选常量后即可选取所有非空单元格）。选好后选择公式 - 定义的名称 - 根据所选内容创建，只勾选首行，然后确定。接下来就的步骤与1级下拉菜单一样，需要进入数据验证并在来源中需要输入**=INDIRECT(A2)**，最后下拉将刚才设置好的格式填充到下面单元格即可。 具体步骤可参考下述动图，动图来源: https://blog.csdn.net/qq_42196749/article/details/87919404 通过建立公式名称来建立二级下拉菜单会产生非常多的自定义名称，而且步骤较为复杂。而通过公式建立二级下拉菜单会更加灵活和简便。如下图所示，在B列中填入公式： =OFFSET($K$1,1,MATCH(A2,$K$1:M$1,0)-1,COUNTA(OFFSET($K$1,1,MATCH(A2,$K$1:$M$1,0)-1,99)))得到二级下拉列表 公式详解： OFFSET(reference,rows,cols,height,width)，其中reference为起始单元格，rows,cols,height,width分别对应行，列，高度和宽度变化。对应到公式中，第一个$K$1为reference，表示的是序列区域（下拉列表的值区域）的起始单元格之后的1表示将K1在行方向上加1，即K2(因为第一行是部门，所以二级下拉菜单的值从第二行开始）之后使用MATCH(A2,$K$1:$M$1,0)-1这个函数去匹配部门所在的位置，根据位置进行偏移。例如如果是行政部，那么MATCH(A2,$K$1:$M$1,0)-1的的值为0，就保持在K列。如果为财务部，当前的选定单元格向右移一格，变为L2单元格之后的COUNTA(OFFSET($K$1,1,MATCH(A2,$K$1:$M$1,0)-1,99))和一级下拉菜单一样，根据当前列值的数量选取序列的范围。其中99是设置counta的范围，如果列表个数有变化可酌情添加和减少 三级下拉菜单三级下拉菜单的制作和二级下拉菜单一致，只是要有一个三级下拉菜单与对应的二级菜单的关系，并在公式中引用二级下拉菜单的内容即可 总结公式方法适用于经常有数量变化的列表，按照这种方法建立的下拉菜单后续修改会比较简便 而普通的方法操作简单，适用于简单的下拉菜单的制作 个人公众号：Smilecoc的杂货铺，欢迎关注！ |
注意无法通过将忽略空值勾选上实现。“忽略空值”仅指当有效性单元格为空值时不出现出错信息，也就是允许单元格为空
这种情况只能够通过做辅助列先去除空格后再选取范围。假设不连续的列表在B列：
【本文地址】