让你的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 条评论
登录 后参与评论

相关文章

来自专栏nummy

reStructuredtext快速入门

reStructuredText是一种reStructuredText是一种轻量级的文本标记语言,简单易读,所见即所得的文本标记语言。

542
来自专栏信安之路

【作者投稿】奇葩webshell技巧

前段时间看XDCTF的一道web题,发现了一种很奇特的构造webshell的方法。

890
来自专栏CDA数据分析师

提问 | 1、SPSS的文字类型处理问题 2、如何剔除不满三年的样本?

来自经管之家 提问1: SPSS的文字类型处理问题,求大神教!! 在EXCEL中这样的数据类型转到SPSS怎么就成字符型再改为日期型还空白了呢? ? 解答: 这...

17510
来自专栏守候书阁

编写自己的代码库(javascript常用实例的实现与封装--续)

这个系列的上一篇文章(编写自己的代码库(javascript常用实例的实现与封装))总结了34个常见的操作。但是在开发中,常见的实例又何止这么多个,经过这些日子...

693
来自专栏前端说吧

vuex - 学习日记

35011
来自专栏企鹅号快讯

教您最简单粗暴的MATLAB入门级爬虫2

感谢吴老师的《数据新闻与网络数据挖掘》课程 感谢各位捧场的小伙伴们 本文适用对象为编程零基础者 仅供学习交流使用,请勿用于商业用途。 通过这样的方法,可以类似得...

1776
来自专栏张善友的专栏

MongoDB 聚合管道(Aggregation Pipeline)

管道概念 POSIX多线程的使用方式中, 有一种很重要的方式-----流水线(亦称为“管道”)方式,“数据元素”流串行地被一组线程按顺序执行。它的使用架构可参考...

17510
来自专栏乐沙弥的世界

MongoDB执行计划获取(db.collection.explain())

803
来自专栏linux驱动个人学习

C++静态链接

C++ 的一些语言特性使之必须由编译器和链接器共同支持才能完成工作。最主要的有两个方面,一个是C++ 的重复代码消除,还有一个就是全局构造与析构。另外由于C++...

611
来自专栏MongoDB中文社区

优化MongoDB复合索引

对于一个MongoDB的复杂查询,如何才能创建最好的索引?在本篇文章中,我将展现一种给读请求定制的索引优化方法,这种方法会考虑读请求中的比较,排序以及范围过滤运...

561

扫描关注云+社区