本次是第二次分享广大网友向我提问的经典问题。
本周问题,利用名称管理器完成二维表的匹配返回!让你的表格动起来!
话说!本次解决方案相对复杂,能看明白并且自己会用的基本上函数使用没什么问题了!
解决方法为个人原创,有更好解决方案欢迎留言打脸!!
问题!
原表:
需求:想在某个单元格选择某个部门后,自动将有数量的产品列在下表中!
问题拆分!!
一、先解决如果公司固定,只是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))),"")
解决问题三:如何将某个单元格设置为不同部门可选
选中涂黄的单元格,设置数据有效性即可
第一步:选中单元格-单击有效性验证
第二步:选中序列-选中标题的行
最后就实现了!
感谢支持!下期不见不散!