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":"hbase","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技巧:万金油公式
尹毓泽2988EXCEL表格公式隔行下拉 -
潘界盼18643672091 ______ 很多方法啊 A1 =INDEX(D:D,ROW(A1)*2-1) 下拉 或者 =INDIRECT("D"&ROW(A1)*2-1) 下拉
尹毓泽2988excel 公式自动下拉 -
潘界盼18643672091 ______ =a$9*b1 ,下拉即可.
尹毓泽2988EXCEL 如何实现下拉填充公式,保持公式部分内容不变? -
潘界盼18643672091 ______ 这个问题可以用“绝对引用”实现,公式引用,可以分为以下三种情况: 1、 相对引用: 公式中的相对单元格引用(如 A1)是基于包含公式和单元格引用的单元格的相对位置.如果公式所在单元格的位置改变,引用也随之改变.如果多行或多...
尹毓泽2988如何用excel做一个计算公式下拉菜单, -
潘界盼18643672091 ______ 只有用辅助列来实现 假设以辅助列来实现,比如说C列 在C列填入公式=if(A1="","",A1*2) 选中B列——数据,有效性,序列,来源中填入=Sheet1!C:C 即可 (EXCEL2003最好把C:C弄上行号)
尹毓泽2988excle中输入公式后,下拉实都变成了公式不是 -
潘界盼18643672091 ______ Excel中的公式下拉:比如你C1=A1+B1 你下拉C1则C2=A2+B2,C3=A3+B3....如果A B中无值按0处理 按照你的情况来看,感觉是输入公式前面没有输入“=” 比如C1=A1+B1你要在C1栏键入“=A1+B1”
尹毓泽2988求Excel公式,下拉自动填充
潘界盼18643672091 ______ B1输入="16-"&TEXT(INT(ROW(A2)/2),"000")&"-c"&IF(ISODD(ROW()),"c","") 公式下拉
尹毓泽2988Excel自动下拉公式 -
潘界盼18643672091 ______ 鼠标移到 这个单元格 右下角 变实心十字 下拉 不过公式可以改简单点: =sumproduct(C4:G4*$C$3:G$3)
尹毓泽2988在线等 在一张excel表格中引入另一张表格中带有公式内容的数值,须批量下拉. -
潘界盼18643672091 ______ 设置好函数后,可以使用$来固定数值的引用范围,在函数上按 F4,即可
尹毓泽2988EXCEL中,如何实现一个单元格内可以下拉选择好几个公式 -
潘界盼18643672091 ______ 如图设置
尹毓泽2988excel表中,公式下拉,如下 -
潘界盼18643672091 ______ =IF(INDIRECT("A"&ROW(A1)*3)="","",INDIRECT("B"&ROW(A1)*3)) 或 =IF(OFFSET($A$1,ROW(A1)*3-1,)="","",OFFSET($B$1,ROW(A1)*3-1,))