办公小技巧:巧做Excel智能下拉菜单

CFan 电脑爱好者 2021-12-13 10:01应用 标签:菜单 技巧 智能 办公

在Excel中,我们经常用到自定义下拉菜单。普通方法制作的自定义下拉菜单有个小缺点,就是下拉菜单无法自动将数据源的列表去重,而且使用的函数也比较复杂。其实,只要用好Power Query,外加INDIRECT函数,即可轻松制作出能够自动去重的智能下拉菜单(图1)。

2016A-ZNCD-1

小提示:以下操作均在Excel 2019中完成。

首先,选中数据区域中的任意单元格,切换到“数据”选项卡,点击“从表格”,在弹出的对话框中直接点击“确定”后就会出现“Power Query编辑器”窗口。在这个窗口中选中“入库品名”列,在“主页”选项卡下,点击“删除列→删除其他列”,这时窗口中只包含了“入库品名”列,再点击“删除行→删除重复项”,这样,“入库品名”列中就剩下不重复项了。点击“关闭并上载”,这时就会在工作簿中新创建一个工作表,这个工作表里面保存的就是没有重复项的“入库品名”列(图2)。

2016A-ZNCD-2

接下来,选择原数据工作表(即Sheet1),切换到“公式”选项卡,点击“名称管理器”,这时就会看到“名称管理器”窗口中有两个名称,其中“表1_2”就是制作下拉菜单所用到的名称(图3)。

2016A-ZNCD-3

点击“关闭”按钮。选中需要显示下拉菜单的单元格(如F2),切换到“数据”选项卡,点击“数据验证→数据验证”,在弹出窗口“设置”选项卡“验证条件”的“允许”处选择“序列”,“来源”处输入“=INDIRECT("表1_2")”,这样,当点击F2单元格时就会出现相应的下拉菜单(图4)。

2016A-ZNCD-4

这时,无论在B列增加或删除入库品名,只需要右击Sheet2中的A列,选择“刷新”,那么在F2处的下拉菜单也会相应地实时更新。

最后,在“合计数量”对应的单元格中输入公式“=SUMIF(B:B,$F$2,D:D)”,就能按下拉菜单选项进行统计了。