48个excel万能技巧
作者:bluebird118
Excel中有一个几乎“万能”的函数组合,她就是Index+small+if+row组合,也称为“万金油的组合公式”。在日常工作中的应用非常广泛,今天我们就来了解这个万金油公式。
很多朋友在Excel中用公式做查询的时候,都必然会遇到的一个麻烦问题:那就是一对多的查找问题。
大多数朋友做查询都是从vlookup、index-match组合入门的,然而遇到一对多查询的时候,如果不加辅助列,往往会束手无策,今天我们要讨论的Index+small+if+row组合,就是专门解决一对多查询的一个通用公式。
如果你能完全掌握这个思路的话,不夸张的说,在Excel中基本上就没有什么查询可以难住你了 。好了,言归正传,先看一个效果图,到底一对多查询是怎样的:
其中的公式是:=IFERROR(INDEX($A$1:$C$15,SMALL(IF($A$1:$A$15=$E$2,ROW($B$1:$B$15),10^3),ROW(B1)),COLUMN(B1)),"")
不过要想彻底理解这个公式,还得有一些储备知识,例如简单的数组应用,index、small、if和row这几个函数的基本用法也是要了解的。在之前的文章中,index、if、row函数都有介绍过。在这里也提一下:
首先,来看下index函数,这个函数基本功能是给出一个区域,然后根据对应的行列位置返回查找结果,index一共需要三个参数,第一个参数是数据范围,刚才说了,第三个参数是要查找的内容位于这个范围的第几列,本例中因为要对应查找三列数据,并且列的位置是对应的,所以用了column函数,如下图所示:三个红色的框,第一个是数据范围,第二个是第几行,第三个是第几列。
其中最难理解的就是第2个参数。接下来我们来展开第2个参数。这个参数的作用是要查找的内容位于数据区域的第几行,如果是一对一的查找,我们可以指定行号或者用match函数来取得行号,但这里是一对多,例如部门是蜀汉的,分别对应了五个行号,如下图所示:
重要提醒:对这五行数据在表格中的行号我特别标注出来了,分别是2、3、11、12和15,但请注意一点,如果index第一参数所给到的区域并不是从第一行开始的,那么得根据第一参数的区域内的行数。假如第一个参数是从第2行开始的话,那么对应的行数将减去1,分别是1、2、10、11、14。
实际上,这五个数据在index给到的范围中,分别位于第2、3、11、12和15行,也就是我们希望得到的结果。
对于蜀汉
第一次出现时应该是index(数据范围,2,列位置);
第二次出现时应该是index(数据范围,3,列位置);
第三次出现时应该是index(数据范围,11,列位置);
第四次出现时应该是index(数据范围,12,列位置);
第五次出现时应该是index(数据范围,15,列位置);
。。。。。。
从上面来看,其中发生改变的就是第2个参数,行数在发生变化。
这就是一对多的核心,请务必理解。
为了将这一想法变成结果,就出来了small+if+row,而这三个函数的组合实际上才是真正的万金油!
接下来我们来看small函数
其函数结构是SMALL(array,k),k 为返回的数据在数组或数据区域里的位置(从小到大),array查找的数据区域。也就是第k个最小的位置
Small函数的作用是在给出的一组数据中挑出指定的第几个最小值,第一个参数是一组数字,第二个参数是一个数字。来我们来看下面这个动图
图中有9个数,分别是1,2个2, 2个3,2个5,6、8.
如果公式是=SMALL(A$1:A$9,1),则返回第一个最小值1
如果公式是=SMALL(A$1:A$9,2),则返回第二个最小值2
如果公式是=SMALL(A$1:A$9,3),则返回第三个最小值2
如果公式是=SMALL(A$1:A$9,4),则返回第四个最小值3
如果公式是=SMALL(A$1:A$9,5),则返回第五个最小值3
如果公式是=SMALL(A$1:A$9,6),则返回第六个最小值5
这也是上图中显示的结果。
注意,这里的第二参数我们是手动输入的,因为把第一个公式下拉的话,第二参数1是不会变的,如果需要第二参数会自己变,就需要借助ROW函数!(row出现)看下图
这是一个公式下拉的结果,好像是对A列进行了排序一样的效果,有点意思吧~~~
说完row函数后。我们整体来看下这个公式中
我们想要的是2、3、11、12和15行,要通过small得到这几个数字,有个思路就是:
部门是蜀汉的,按对应的行号标注;
不是蜀汉的,都看作比数据中最大的行数要大的数(这个数其实是行号,因为一共15个数)。
而要实现这个目的,只能通过if实现。
(IF($A$1:$A$15=$E$2,ROW($B$1:$B$15),10^3),之前的公众号文章中也有介绍IF函数的,if函数的基本逻辑,想必大家也比较清楚,标黄色部分的意思是给定的区域数据,是否等于E2的值,如果是的话,将返回 ROW($B$1:$B$15),否则就返回10^3(这个数只要比数据的行数大即可,为了不超过数据区域最大的行数,这里往往是写的比较大的数据)
我们来看看IF($A$1:$A$15=$E$2,ROW($B$1:$B$15),10^3)这一段的运算结果吧:
从上图中,你注意到2、3、11、、12和15了吗?
到这里,small函数的这部分是不是明白了,也就是index函数的第2个参数。
如果明白了,我们接着来看。将small函数代入到index函数中,就是我们看到的公式=INDEX($A$1:$C$15,SMALL(IF($A$1:$A$15=$E$2,ROW($B$1:$B$15),10^3),ROW(B1)),COLUMN(B1))
","force_purephv":"0","gnid":"926daa0787cf7bd87","img_data":[{"flag":2,"img":[{"desc":"","height":"500","s_url":"https://p0.ssl.img.360kuai.com/t015dd80a00627586b1_1.gif","title":"","url":"https://p0.ssl.img.360kuai.com/t015dd80a00627586b1.gif","width":"730"},{"desc":"","height":"245","title":"","url":"https://p0.ssl.img.360kuai.com/t01fb7ab98f6b7148e1.jpg","width":"600"},{"desc":"","height":"326","title":"","url":"https://p0.ssl.img.360kuai.com/t019868e7b2be1db895.jpg","width":"554"},{"desc":"","height":"392","s_url":"https://p0.ssl.img.360kuai.com/t0156283e000c18c9b5_1.gif","title":"","url":"https://p0.ssl.img.360kuai.com/t0156283e000c18c9b5.gif","width":"597"},{"desc":"","height":"392","s_url":"https://p0.ssl.img.360kuai.com/t011f42d758b419ec75_1.gif","title":"","url":"https://p0.ssl.img.360kuai.com/t011f42d758b419ec75.gif","width":"597"},{"desc":"","height":"465","s_url":"https://p0.ssl.img.360kuai.com/t01043fee522baf4dc2_1.gif","title":"","url":"https://p0.ssl.img.360kuai.com/t01043fee522baf4dc2.gif","width":"955"}]}],"original":0,"pat":"art_src_1,fts0,sts0","powerby":"cache","pub_time":1676106538000,"pure":"","rawurl":"http://zm.news.so.com/2b33d8cd734056be6290fbede0903cca","redirect":0,"rptid":"a7d0520163259336","s":"t","src":"什么值得买","tag":[{"clk":"kcareer_1:excel","k":"excel","u":""}],"title":"EXCEL技巧:万金油公式
傅朋帖4779Excel表格入门小技巧,你知道哪些 -
胥卓裘18662448077 ______ 一、文件操作 1、为 excel 文件添加打开密码 文件 - 信息 - 保护工作簿 - 用密码进行加密. 2、为文件添加作者信息 在该 excel 文件图标上右键单击 - 属性 - 摘要 - 在作者栏中输入 3、让多人通过局域网共用 excel 文件 审阅 - 共享工作簿 - 在打开的...
傅朋帖4779EXCEL在工作中的一些小技巧 -
胥卓裘18662448077 ______ 招式一:把“快速访问工具栏”请下来. 设置方法如下:点击Office按钮 [img]img2005/40xo8244l522.jpg[/img] 在下拉菜单右下方点击“Excel选项”出现Excel选项界面如(图一)所示: [img]img2005/a8soar4120n8s.jpg[/img] 图一 点击左面“...
傅朋帖4779有哪些excel小技巧 -
胥卓裘18662448077 ______ 本人收藏的14个技巧,也是网上的,出处不详了,供您参照,希望能帮到您. 01 矩阵乘法 mmult输入=mmult(A,B),按shift+control+enter即可. 02 条件计数 Countif 03 快速求和?用“Alt + =” 04 快速选定不连续的单元格,按下“Shift+F8”...
傅朋帖4779EXCEL快速操作技巧有哪些?
胥卓裘18662448077 ______ Excel是我们经常使用的表格处理工具,用它处理,即使有再多的数据也不是什么难事. 不过,对于有较多数据的工作表来说,在具体的操作过程中却存在很多比较麻烦的...
傅朋帖4779excel使用技巧 -
胥卓裘18662448077 ______ 方法1,直接用筛选,每一列都设定筛选条件大于0 方法2,辅助列输入公式 =--(countif(d2:f2,">0")=3),公式填充数据区域,根据辅助列筛选,筛选值1 方法1,2筛选结果复制出来即可
傅朋帖4779excel技巧有哪些? -
胥卓裘18662448077 ______ excel的技巧非常多,浅显一点的主要包括公式的使用和搭配,深一点的如宏的使用等 你可以在网上搜索一些文档,指导你从完全不会用EXCEL迅速成长为EXCEL高手 但重点在于你在工作和学习中会用到哪些功能,凡是你经常用得到的,必然非常熟练;凡是你不怎么用得到的,过段时间要用了可能还要复习一下;凡是你根本用不着到,连学习和复习的必要都没有 所以完全可以根据你的需要,遇到什么问题需要解决了,就上百度问一问,查一查,总之EXCEL的功能比你想像中强大很多,很多你以为他解决不了的事情,其实都能够解决
傅朋帖4779用EXCEL制表的技巧有哪些 -
胥卓裘18662448077 ______ ESCEL的常用技巧 一、 基本方法1. 快速选中全部工作表 右键单击工作窗口下面的工作表标签,在弹出的菜单中选择“选定全部工作表”命令即可().2. 快速启动Excel 如果想在启动系统自动运行Excel,可以这样操作: 1.双击“我的电脑”...
傅朋帖4779Excel一些很有用的技巧和公式有哪些?怎样使用? -
胥卓裘18662448077 ______ 1、如何拆分或取消拆分窗口? 当我们给一个工作表输入数据时,在向下滚动过程中,尤其是当标题行消失后,有时会记错各列标题的相对位置.这时可以将窗口拆分为几部分,然后将标题部分保留在屏幕上不动,只滚动数据部分.其方法是...
傅朋帖4779电子表格应用技巧 -
胥卓裘18662448077 ______ 1、电子表格应用技巧很多,只有熟练操作了才能掌握. 2、打开Excel表,上面的文件、编辑、视图、插入、格式、工具、数据、窗口都要了解 3、尤其是初学者要对格式有所了解,才能简单制作表格. 4、只有掌握了才能运用自如,包括Word制表一样,也可以制作电子表格.
傅朋帖4779Excel表格的必学秘技 -
胥卓裘18662448077 ______ Excel表格的35招必学秘技 也许你已经在Excel中完成过上百张财务报表,也许你已利用Excel函数实现过上千次的复杂运算,也许你认为Excel也不过如此,甚至了无新意.但我们平日里无数次重复的得心应手的使用方法只不过是Excel全部技巧...