让你的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))),"")

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

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

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

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

最后就实现了!

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

原文发布于微信公众号 - 阿凯的Excel(akexcel)

原文发表时间:2017-07-26

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏贾老师の博客

makecontext 理解与使用

1193
来自专栏服务端思维

提高服务端编码质量 - 用例检查列表

检查分页功能是否正常,分页边界是否有矫正机制,例如非数值类型,下限是否校验,上限是否设置允许最大值等。

931
来自专栏FreeBuf

SQL注入之骚姿势小记

写在前面 小记一下CTF那些日子和DROPS队友学到的SQL注入的骚姿势。 By 010 1、IN之骚 这个我也偶然发现的,也不知前辈们有没有早已总结好的套路了...

2926
来自专栏tkokof 的技术,小趣及杂念

小话游戏脚本(三)

在此就heSript实现过程中的一些解决方案和自己的想法陈列一番,由于自己编程水平实在拙劣,又没什么实际经验,所以导致相关的代码非常糟糕,所以竭诚欢迎大家批评...

391
来自专栏牛客网

面经总结

面试记录 头条 - 一面 - 自我介绍 - 连续子数组的最大和 - 二叉树任意两个节点之间路径的最大长度 - 二叉树的深度 - 一面上个周只记得这么多了 - 二...

3687
来自专栏Golang语言社区

golang语言是如何处理栈的

Go 1.4Beta1刚刚发布,在Go 1.4Beta1中,Go语言的stack处理方式由之前的"segmented stacks"改为了"continuous...

3278
来自专栏游戏开发那些事

【Unity游戏开发】Lua中的os.date和os.time函数

  最近马三在工作中经常使用到了lua 中的 os.date( ) 和 os.time( )函数,不过使用的时候都是不得其解,一般都是看项目里面怎么用,然后我就...

894
来自专栏Java技术

一步步带你了解ID发号器是什么、为什么、如何做!

上一篇文章《面试必备:如何将一个长URL转换为一个短URL?》中谈到如何将长地址URL转换为短地址URL,其中谈到了一个比较理想的解决方案就是使用发号器生成一个...

932
来自专栏机器学习从入门到成神

java.lang.StackOverflowError异常解决

版权声明:本文为博主原创文章,未经博主允许不得转载。 https://blog.csdn.net/sinat_35512245/articl...

942
来自专栏Seebug漏洞平台

从WordPress SQLi谈PHP格式化字符串问题

近日,WordPress爆出了一个SQLi漏洞,漏洞发生在WP的后台上传图片的位置,通过修改图片在数据库中的参数,以及利用php的sprintf函数的特性,在删...

38511

扫码关注云+社区