|
2017-07-28 阿忠凯 Excel之家ExcelHome 本周问题，利用名称管理器完成二维表的匹配返回，让你的表格动起来。 话本次解决方案相对复杂，能看明白并且自己会用的基本上函数使用没什么问题了。 解决方法为个人原创，有更好解决方案欢迎留言打脸—— 问题 原表： 需求：想在某个单元格选择某个部门后，自动将有数量的产品列在下表中！ 问题拆分： 一、先解决如果公司固定，只是A部门，如何能将含有数据的产品列在下表。 二、如何结果部门不同时，如何变成另外一个部门的数据 三、如何将某个单元格设置为不同部门可选（三个问题中最简单的一个） 解决问题一： 如何在确定A部门的情况，将产品列在下表。 思路：先查看B列哪个单元格有数据、查看有数据的单元格的在第几行，将行数从小到大进行排序，将A列对应行数的单元格拷贝到下面。 bingo！ 将每一句话用一个函数实现。 先查看B列哪个单元格有数据-> IF（判断条件，为真返回什么，为假返回什么）函数判断是否为空 查看有数据的单元格的在第几行-> Row（单元格）返回单元格的行数 将行数从小到大进行排序-> Small（列表，第几个最小的值）返回列表中的第几个最小的值 将A列对应行数的单元格拷贝到下面-> Index（列表，个数）返回列表中第几个值 口述思路： 先挨个单元格判断B列是否有数据，如果有返回单元格对应的行数，如果没有返回值为空。 代码实现： IF($B$2:$B$9="","",ROW($B$2:$B$9)） 返回IF函数中最小的那个数字（空不列入排序） SMALL(IF($B$2:$B$9="","",ROW($B$2:$B$9)),1) 在A列中返回B列有数字行数的值 INDEX($A$1:$A$9,SMALL(IF($B$2:$B$9="","",ROW($B$2:$B$9)),1)) 目前有个问题了，我需要依次返回第一个最小的值，第二个最小值，第N个最小值！怎么快速实现呢。 我们将数字1用Row（A1）替换，这样自动填充到第二个单元格的时候就会变成Row（A2） 所以目前完整公式为： =INDEX($A$1:$A$9,SMALL(IF($B$2:$B$9="","",ROW($B$2:$B$9)),ROW(A1))) 由于其中用到了数组函数，输入公式后要同时按住Ctrl+Shift+Enter 双击公式自动填充后效果如下： 如果B列只有三行数据，则返回值前三行是有正确输出的，但是后面的就会报错，我们想一个方法规避这种错误提醒。 Iferror（函数，如果有错误返回值显示什么） 所以将原有的公式外面包裹一个Iferror函数，如果有错误，显示空 =IFERROR(INDEX($A$1:$A$9,SMALL(IF($B$2:$B$9="","",ROW($B$2:$B$9)),ROW(A1))),"") 这样错误值就被隐藏掉了。 解决问题二： 如何结果部门不同时，如何变成另外一个部门的数据 原表： 上一个问题的公式： =IFERROR(INDEX($A$1:$A$9,SMALL(IF($B$2:$B$9="","",ROW($B$2:$B$9)),ROW(A1))),"") 我们希望当选择B部门的时候，是用C2：C9单元格作判断；C部门的时候，用D2：D9做判断。 如何方便快捷的更改选择的区域呢？且随着某个单元格的内容变化而变化呢？ 介绍两个小东西： 1、名称管理器 2、Indirect函数 名称管理器是啥，就是将某个区域命名为一个名字。 Indirect函数啥意思呢？含义此函数立即对引用进行计算，并显示其内容。通俗的讲。如果将B2：B9命名为部门A，则这个区域作为参数的时候，可以输入B2:B9，也可以输入Indirect（部门A）。大概就是就这个意思。 怎么做呢？ 第一步：选中列表区域 第二步：公式-根据所选内容创建
第三步：首行
至此，命名已经完成。 看一下我们之前编辑好的公式 =IFERROR(INDEX($A$1:$A$9,SMALL(IF($B$2:$B$9="","",ROW($B$2:$B$9)),ROW(A1))),"") 我们需要每次选择不同内容时候，涂红的区域跟着变化，所以用indirect函数实现。
部门所在的单元格是B12，所以进行引用 =IFERROR(INDEX($A$1:$A$9,SMALL(IF(INDIRECT($B$12)="","",ROW(INDIRECT($B$12))),ROW(A1))),"") 解决问题三 如何将某个单元格设置为不同部门可选 选中涂黄的单元格，设置数据有效性即可 第一步：选中单元格-单击有效性验证
第二步：选中序列-选中标题的行
最后就实现了！
|
|
|
