龙空技术网

制作 Excel 下拉菜单,如何自动去除列表区域中的跳空行和重复项?

Excel学习世界 245

前言:

目前你们对“表格下拉项内容怎么修改”大体比较关切,我们都想要剖析一些“表格下拉项内容怎么修改”的相关知识。那么小编在网络上搜集了一些关于“表格下拉项内容怎么修改””的相关知识,希望同学们能喜欢,朋友们快快来了解一下吧!

很多同学会觉得 Excel 单个案例讲解有些碎片化,初学者未必能完全理解和掌握。不少同学都希望有一套完整的图文教学,从最基础的概念开始,一步步由简入繁、从入门到精通,系统化地讲解 Excel 的各个知识点。

现在终于有了,以下专栏,从最基础的操作和概念讲起,用生动、有趣的案例带大家逐一掌握 Excel 的操作技巧、快捷键大全、函数公式、数据透视表、图表、打印技巧等……学完全本,你也能成为 Excel 高手。

制作下拉菜单的时候,如果列表区域内有空行怎么办?先删除空行?如果列表经常更新,经常会产生新的空行,难道每次都重新设置吗?

有些老读者说有套路公式?那如果空行都是间隔跳空的呢?套路公式未必能见效。

来看看我们今天的解法。

案例:

将下图 1 中 C 列的区域制作成下拉菜单,放在 A 列。要求去除区域中的空值和重复项,且不更改顺序。

效果如下图 2 所示。

解决方案:

要去除下拉菜单中的空值,有个套路公式 offset,但是,它只能解决连续的序列末尾多出来的连续空行,中间穿插空行则不行。

我们可以先验证一下 offset 公式。

1. 在 D1 单元格中输入以下公式 --> 下拉复制公式:

=OFFSET($C$1,,,COUNTA($C$1:$C$11))

公式释义:

简言之,就是以 C1 单元格为起点,向下偏移行数为 C 列中的非空单元格数;本例中非空单元格数为 5 个,所以从“郑喜定”起,一共提取 5 行,这样就会包含其中的空单元格

2. 在 E1 单元格中输入以下公式 --> 向下拖动公式:

=OFFSET($C$1,,,SUMPRODUCT(N(LEN($C$1:$C$11)>0)),)

具体就不详解了。

总之遇到跳空也是不行。

还有一个办法就是用万精油公式,但是很不推荐,因为下拉菜单设置的时候不能直接引用数组公式,且万精油很难理解,对于大部分同学来说不友好。何况随着 Excel 版本的升级,有越来越多的新函数可以替代万金油功能了。

下面是今天要讲的方法。

1. 选中 C 列的不连续列表区域 --> 选择菜单栏的“数据”-->“从表格”

2. 在弹出的对话框中保留默认设置 --> 点击“确定”

3. 在 PQ 中选择菜单栏的“主页”-->“删除行”-->“删除重复项”

4. 在 PQ 中选择菜单栏的“主页”-->“删除行”-->“删除空行”

5. 选择菜单栏的“主页”-->“关闭并上载”-->“关闭并上载至”

6. 在弹出的对话框中选择“表”--> 选择“现有工作表”及所需上传至的位置 --> 点击“加载”

绿色的表格是整理好的菜单序列。

7. 按 Ctrl+F3,可以看到两个表格,其中绿色的表格对应的名称是“表1_2”,记住它。

8. 选中 A2 单元格 --> 选择菜单栏的“数据”-->“数据验证”-->“数据验证”

9. 在弹出的对话框中选择“设置”选项卡 --> 按以下方式设置 --> 点击“确定”:

允许:选择“序列”来源:输入 =indirect("表1_2")

好了,下拉菜单设置好了。

这样设置的好处在于:如果 C 列的名单有更新,只要在 E 列的表格处刷新一下,A 列的下拉菜单会自动随之更新。

10. 在 C13 单元格中输入“aaa”。

11. 选中绿色表格的任意单元格 --> 右键单击 --> 在弹出的菜单中选择“刷新”

A 列的下拉菜单中自动新增了“aaa”。

标签: #表格下拉项内容怎么修改 #如何删除下拉选项框 #如何删除下拉菜单选项 #怎么删除下拉选择