“瓜子壳属于什么垃圾”“湿巾居然是干垃圾”? 史上最严垃圾管理条例开始实施,上海的小伙伴们纷纷表示每天经受灵魂拷问:“你是什么垃圾”? 垃圾分类,魔都已崩溃!连奶茶都不敢喝了 更崩溃的是,分对不仅没奖,分错了还要挨罚。如果个人混合投放垃圾最高可罚200元;单位混装混运,最高可罚5万元。 什么?你说你幸好不是上海人?天真! 住建部表示,生活垃圾分类工作将逐步在46个重点城市展开... 听闻此讯,正在46城(之一)喝奶茶的小编,手有点抖... 为了不会领到罚单,我们发挥专长,用表格函数做了一个垃圾分类查询工具。输入垃圾关键字,就能查询有哪些相关垃圾,分别属于什么分类。 有了这个工具,我不禁笑出了声。 输入关键字“纸”可以查到,有的纸是属于可回收物,有的属于干垃圾。 输入关键字“骨”可以查到,有的骨头是湿垃圾,有的骨头是干垃圾。 生活中有成百上千种垃圾,肯定记不住,有查询工具就简单了。 经典通式“一查多” 垃圾分类查询工具是用 INDEX+SAMLL+IF 函数实现的,轻松做到“以一查多”,今天我们就来学习一下它。 虽然VLOOKUP函数经常用于查询,但它局限很大,只能返回一个结果,按字符模糊匹配查询还经常出错。 和大家分享这个公式是“一查多”经典通式,被广泛运用,可直接套用: =INDEX(被查区域,SMALL(IF(查询条件,行号数组,4^8),ROW(A1))) 大家记住它,掌握之后基本上没有什么查询不了的。 今天就和大家说说它的原理和配用方法。如下图,左边是数据表,在右边查询结果: 这个一查多应用实例比较典型:根据一个条件“部门”,查询多个结果“员工”,从上至下依次显示。 最终效果如下图 D2单元格中公式为: =INDEX(B:B,SMALL(IF($A$2:$A$10=$D$1,ROW($A$2:$A$10),4^8),ROW(A1))) 下面我们来解析这个函数公式,看起来很复杂,学会其实并不难。如果觉得第一遍看得不太懂,可以把文章收藏下来,慢慢理解。 没有函数基础没有看懂也没关系,这个函数公式可以直接套用,文末还有设计师已经做好的模板可以直接下载使用! 函数及公式解析 下面逐步简化,解析这个公式 公式是基于INDEX函数设计的。什么是INDEX函数? INDEX作用是:在某数据区域内,按指定的顺序号,返回对应的元素。 比如,要返回B列第2行的值“琪一”,则公式为=INDEX(B:B,2) 先看最里层, IF($A$2:$A$10=$D$1,ROW($A$2:$A$10),4^8) 最里层 IF 公式,是一个数组公式,它依次判断条件区域A2:A10里值是否等于D1表示的值: A2:A10里等于D1的,则返回该行行号;不等于D1的,则返回4^8。 4^8等于65536,基本上第4^8行没有数据了,是一个极大值。 IF函数结果返回一个数组:{2;极大值;极大值;3;4;极大值;极大值;极大值;极大值} 再向外一层是函数 SMALL(IF数组,ROW(A1)) 其中ROW(A1)代表A1的行号,就是1;ROW(A2)则代表A2的行号,也就是2。 所以SMALL(数组,X)返回 数组{2;极大值;极大值;5;6;极大值;极大值;极大值;极大值}中第1 、2、3……小的值,所以在D列查询结果中,第1个公式是第2行对应的值,第2个公式是第5行对应的值…… 即A2:A10部门中,第1次等于“财务”的行号是2,第2次的行号是5,第3次的行号是6…… 最外层是INDEX公式 INDEX(B:B,行号数组) INDEX的作用是,按上面SMALL取得的行号数组(顺序号),在员工姓名B列中,依次按第2、5、6顺序,依次把第2、5、6行对应的值(琪一、李四、王五)取出。 到这里,整个公式的逻辑功能就设计完成了! 既然叫数组公式,听起来就好像是很高级的样子。它和一般公式的区别是,编辑完公式后按Ctrl+Shift+Enter三键结束,而普通公式按Enter结束。 组合公式最上面三个返回2、5、6行的值,下面均返回第4^8行的值“0”,而不是空,这是因为空单元格转化为数值是0。 为了不显示0,在公式最后加 &'' 将值强制转化为文本,就返回空了: =INDEX(B:B,SMALL(IF($A$2:$A$10=$D$1,ROW($A$2:$A$10),4^8),ROW(A1)))&''。 到这里,公式编辑全部完成! 这便是“一查多”数组组合公式 =INDEX(被查区域,SMALL(IF(查询条件,行号数组,4^8),ROW(A1))) 掌握它,想查询哪里就可以查询哪里,想同时查询几个就可以查询几个! 技巧要一点一点积累 特别是原理性的内容. 一时做不到大神的水平没关系 我们准备了9套大神的模板给你~ 01 << 滑动查看下一张图片 >> 02 << 滑动查看下一张图片 >> 03 << 滑动查看下一张图片 >> 9份精心设计的表格模板 非稻壳会员特惠19.99元就可拥有全部模板 原均价14.9元/份(总价超过135元) 稻壳会员可以免费下载! 成为会员/购买模板后 点击下方按钮 文中高效实用表格模板 一键打包下载 下载完成模板还可以 一键同步至云文档 在电脑端也可以直接使用哦~ 下载步骤说明 Step1:点击链接进入文章下载页面 会员下载页 非会员下载页 Step2:一键下载模板 点击下方下载模板按钮 即可下载全部模板 下载同步过程需要一点时间 请耐心等待一下哦~ 下载完成的界面 ☟ Step3:无限次下载使用模板 在哪里可以找到这些下载的模板呢? 下载完成后,模板会一键同步到 云文档-我的模板 手机微信端/电脑端均可无限次下载使用 微信端下载模板在这里 两种方式可以找到 ☟ >>>1.下拉微信首页进入小程序菜单<<< 金山文档小程序-文档-我的模板 ☟ ☟ >>>2.稻壳儿公众号菜单栏<<< 模板工具-最新PPPT模板-金山文档/我的模板 ☟ 电脑端下载模板在这里 PC客户端-云文档-我的模板文件夹 ☟ 一次下载全部模板,手机/电脑同步使用 超省心哦~ |
|