首页
学习
活动
专区
工具
TVP
发布
社区首页 >专栏 >让你的Excel表动起来!!!

让你的Excel表动起来!!!

作者头像
用户1332619
发布2018-03-08 17:23:22
1.6K0
发布2018-03-08 17:23:22
举报
文章被收录于专栏:阿凯的Excel阿凯的Excel

本次是第二次分享广大网友向我提问的经典问题。

本周问题,利用名称管理器完成二维表的匹配返回!让你的表格动起来!

话说!本次解决方案相对复杂,能看明白并且自己会用的基本上函数使用没什么问题了!

解决方法为个人原创,有更好解决方案欢迎留言打脸!!

问题!

原表:

需求:想在某个单元格选择某个部门后,自动将有数量的产品列在下表中!

问题拆分!!

一、先解决如果公司固定,只是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))),"")

解决问题三:如何将某个单元格设置为不同部门可选

选中涂黄的单元格,设置数据有效性即可

第一步:选中单元格-单击有效性验证

第二步:选中序列-选中标题的行

最后就实现了!

感谢支持!下期不见不散!

本文参与 腾讯云自媒体分享计划,分享自微信公众号。
原始发表:2017-07-26,如有侵权请联系 cloudcommunity@tencent.com 删除

本文分享自 阿凯的Excel 微信公众号,前往查看

如有侵权,请联系 cloudcommunity@tencent.com 删除。

本文参与 腾讯云自媒体分享计划  ,欢迎热爱写作的你一起参与!

评论
登录后参与评论
0 条评论
热度
最新
推荐阅读
领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档