INDEX 函数 办公技巧 22年10月28日 编辑 汪站长职场达人 取消关注 关注 私信 END 基础语法 INDEX函数有两个基础语法,分别为: INDEX(array,row_num,[column_num]) INDEX(reference,row_num,[column_num],[area_num]) 参数row_num,row是行的意思,表示行号,同理,column_num表示列号。第一个语法:对于一个多行多列的数组或区域,指定它的行号、列号,即可提取它对应的位置。 第二个语法:第一个参数有多个区域,可以通过第4个参数来选定其中的第几个区域,然后再在这个区域中指定行列信息。公式如下:=INDEX((A1:B3,D2:G8,B6:B20),4,3,2) 这个公式表示从(A1:B3,D2:G8,B6:B20)这3个区域中选择第2个区域,即D2:G8区域,然后提取此区域的第4行第3列的值,也就是F5单元格的信息。 提取纵向、横向及二维区域中的值 本节将讲解INDEX函数在一维和二维区域中的引用方式。 1.对于纵向区域引用 如图25-1所示,D11:D18单元格区域为基础数据源。在F12单元格中输入公式: =INDEX(D11:D18,3) ENTERTITLE 图25-1对于纵向区域引用 得到D11:D18单元格区域中的第3行的值,结果为D13单元格的“黄承彦”。 注意,INDEX是提取第1个参数中的第n个值,而不是提取第1个参数中不为空部分的第n个值。例如,公式“=INDEX(D9:D18,5)”,虽然D9、D10单元格全都是空值,但它的结果不会从D11单元格开始往下数第5个值。而是从选取的区域D9:D18中取第5个值,也就是返回D13单元格的值。 前面是对于单元格区域的引用,当第1个参数是数组时是否也可以呢?在G12单元格中输入公式: =INDEX({“许褚”;”张飞”;”黄承彦”;”徐庶”;”郭嘉”;”黄权”;”马超”;”庞统”},2) 在一个数组中,英文状态下的分号分隔表示纵向排列,英文状态下的逗号分隔表示横向排列。我们可以看到,最终的结果是提取该数组中的第2行的值,返回结果为“张飞”。 提示:当需要从单元格区域中引用值,并把它们变成数组形式的时候,不需要一个个手动输入,尤其在数据量多的时候。可以先引用单元格区域,然后选中该参数后按【F9】键执行一次计算,如图25-2所示。 ENTERTITLE 图25-2将区域转化为数组 2.对于横向区域引用如图25-3所示,D21:K21单元格区域为一个横向的基础数据源。在F24单元格中输入公式: =INDEX(D21:K21,1) ENTERTITLE 图25-3对于横向区域引用 返回结果为“许褚”,是这个区域中的第一个值,现在一切正常,我们继续计算,在F25单元格中输入公式: =INDEX(D21:K21,7)、 返回结果为“马超”,计算到这里,发现问题了吗? INDEX的第2个参数明明是row_num,表示的是第几行,而D21:K21这个区域只有1行,我们提取第7行的结果应该得到错误值。、 我们再看一下Excel的帮助文件,其中有这么一句话:“如果数组只包含一行或一列,则相对应的参数row_num或column_num为可选参数。” 这句话可以换个角度理解,如果只有一行或一列,我们可以只指定一个参数,而这个参数代表的是一个序列数。就像上体育课站队,如果只站成一列纵队,老师会说第3位同学出来,那么“黄承彦”就走出来了。没有必要说第1列第3位同学,因为只有一列。同样,如果站成一行横队,老师喊第7位同学出来,那么“马超”就走出来了,而没必要说第1行第7位同学。 横向的数组是否也可以使用同样的引用?我们操作试试,在G24单元格中输入公式: =INDEX({“许褚”,”张飞”,”黄承彦”,”徐庶”,”郭嘉”,”黄权”,”马超”,”庞统”},4) 返回的结果是横向数组中的第4个值“徐庶”,操作正确。 3.对于二维区域引用 如图25-4所示,D28:G36单元格区域是二维数据区域。在I29单元格中输入公式,即可得到数据区域中的第2行第4列的值,即3000。=INDEX($D$29:$G$36,2,4) ENTERTITLE 图25-4对于二维区域引用 INDEX还可以引用多行多列的数组,在J29单元格中输入公式: =INDEX({“一组”,”马岱”,42403,4000;”一组”,”黄月英”,42403,3000;”一组”,”黄忠”,42422,3000;”一组”,”黄盖”,42451,6000;”二组”,”孙乾”,42403,8000;”二组”,”许褚”,42424,5000;”二组”,”张飞”,42437,7000;”二组”,”黄承彦”,42438,5000},3,1) 从这个数组中提取第3行第1列的数据是“一组”。 一个多行多列的数组是先按行来排列,一行排列完再排列下一行,所以看到的都是先逗号后分号。 4.对于整行或整列的引用 这时再仔细读读Excel的帮助信息,其中有一句话是“如果将row_num或column_num设置为0(零),函数INDEX则分别返回整个列或行的数组数值”。 还以图25-4中的数据为例,编写公式:=SUM(INDEX(D29:G36,0,4)) 公式返回结果为“41000”,INDEX函数中row_num参数为0,说明选择了第0行。第0行到底是第几行呢?这里没有明确的指定,所以INDEX就把全部的行都引用过来。column_num参数为4,所以返回结果为D29:G36单元格区域的第4列,即G29:G36的销售金额列。 在引用时,一整列的内容无法在一个单元格中完整地展示出来,但这并不影响在公式最外面套一个SUM函数,因此整个公式表示对销售金额的求和,即“41000”。这个SUM函数仅用于辅助大家理解公式的结果。 公式可编写为: =SUM(INDEX(D29:G36,5,0)) 公式返回结果为“50403”,表示对区域中的第5行的引用,即D33:G33区域。由于日期的本质就是数字,2016/2/3相当于数字42403,因此该区域求和为42403+8000=50403。 对整行或整列引用的知识了解即可,构造区域一般习惯性地使用OFFSET函数,有时会用到INDIRECT函数。 案例:制作工资条 下面使用INDEX函数来做一个工资条。在实际工作中,如学生分数、员工工资等都是对他人保密的,所以发纸质明细时,需每个人一条信息。如果用A4纸打印,每人一张,明显太浪费,每个工资条只需两行就够了。我们把所有人的信息都打印在一张纸上,中间留出空行,打印出来之后分别裁剪就行了,如图25-5所示,A1:G9单元格区域是基础数据,我们根据此基础数据信息变成右侧打印版的形式。 ENTERTITLE 图25-5工资条效果 工资条具体怎么制作呢? 有的人利用重复编号然后排序的方案,这种操作方法每次都要做一遍,会重复工作。 有的人使用VBA的技术,能一键完成,那没有VBA基础的人要怎样制作呢?我们用函数的方法来搞定。 首先观察规律:右侧的第1,4,7,10,…行都是引用基础数据源的第1行数据,可以在I列的相应行位置标注上数字1;右侧的第3,6,9,12,…行都是空白行,可以在I列相应行位置放一个较大的数字,如999,这个数字大于原始数据的总行数即可;右侧的第2,5,8,11,…行分别引用基础数据源的第2,3,4,5,…行,可以在I列相应行位置依次输入数字2,3,4,5,… 至此在I列各单元格依次输入数字:1,2,999,1,3,999,1,4,999,1,5,999,… 这些数字有什么作用呢?它们实际就是即将引用的基础数据的第几行。在J1单元格中输入公式:=INDEX(A:A,$I1)并将公式向右向下复制,如图25-6所示。 ENTERTITLE 图25-6基础思路分解 至此整个思路都讲完了,此时对公式是否有了基本的理解?我们开始实质性地构造I列的数字列,取代刚才的手工输入。 首先是处理1,4,7,10,…行,它们是公差为3的等差数列。进一步说,它们除以3的余数都是1,那么I1单元格的公式为: =IF(MOD(ROW(),3)=1,1,0) 利用之前讲的IF函数并列的思路,让每一个IF公式只做一件事情,这里判断当前行是否为1,4,7,10,…行,如果是就标记为1,不是就为0。 其次第3,6,9,12,…行的特点是除以3的余数都为0,于是完善公式:=IF(MOD(ROW(),3)=1,1,0)+IF(MOD(ROW(),3)=0,999,0) 最后处理第2,5,8,11,…行,先输入公式:=IF(MOD(ROW(),3)=1,1,0)+IF(MOD(ROW(),3)=0,999,0)+IF(MOD(ROW(),3)=2,???,0) 整体的结构是和前面一致的,我们在最关键的部分暂时先写上“???”,如何从2,5,8,11,…变成2,3,4,5,…呢? 日常工作中,大家也会遇到这种处理有规律的数字的情况,这里教大家一个放之四海皆准的秘籍:无论你的数字序列是什么样的,都先把它还原到1,2,3,4,…的基准序列。 实际操作一下。2,5,8,11,…它们的公差是3,要变成1,2,3,4,…首先要缩小3倍,我们先写下“=???/3”。 那么几除以3等于1呢?当然是3除以3等于1,所以公式变为“=3/3”。继续操作。被除数3是怎么得出的?当前行位于第2行,所以2+1=3,即“=(ROW()+1)/3”。 上一步我们可以进行验证,当位于第5行时,=(5+1)/3=2,当位于第8行时,=(8+1)/3=3。成功变成1,2,3,4,…了,那怎么变成2,3,4,5,…呢?可以直接+1,所以这部分的公式合成为: =(ROW()+1)/3+1 将它组合进最终的公式:=IF(MOD(ROW(),3)=1,1,0)+IF(MOD(ROW(),3)=0,999,0)+IF(MOD(ROW(),3)=2,(ROW()+1)/3+1,0) 为了增加公式的可读性,将它进行适当的换行排版,如图25-7所示。 ENTERTITLE 图25-7公式排版 面就可以套入INDEX函数完成最后的步骤了,在J1单元格中输入以下公式,并向右复制到P1单元格,向下复制到第n行,得到的效果如图25-8所示。=INDEX(A:A,IF(MOD(ROW(),3)=1,1,0)+IF(MOD(ROW(),3)=0,999,0)+IF(MOD(ROW(),3)=2,(ROW()+1)/3+1,0)) ENTERTITLE 图25-8完成效果1 这里发现第3,6,9,12,…行的结果并不是空白,都是数字0。这是因为引用的相应单元格,如A999单元格是空白的,所以为0。屏蔽掉所以这些行的数字0,将原来的公式稍微变化一下:=INDEX(A:A,IF(MOD(ROW(),3)=1,1,0)+IF(MOD(ROW(),3)=0,999,0)+IF(MOD(ROW(),3)=2,(ROW()+1)/3+1,0))&”” 只是在原有公式的最后增加了“&”””,将原来公式的结果连接一个空文本,3,6,9,12,…行就变成空白了,如图25-9所示。 ENTERTITLE 图25-9完成效果2 这是否意味着只要是0&””结果就返回空白?我们来做个试验,如图25-10所示。 ENTERTITLE 图25-10空白连接示意 在A2单元格中输入公式“=0&”””,可以看到结果为0,而且在单元格中是默认左对齐的,说明这个结果是文本型的数字0,所以0&””返回空白的想法是错的。 在A3单元格中输入公式“=C3&”””,其中C3是空单元格,这时候连接上一个空文本,得到的结果也就是空。 在A4单元格中输入公式“=C4”,C4也是空单元格,返回结果为数字0,这并不是说C4单元格是0,而是说它是空白的。 提示;使用“&”””方式屏蔽空白的方法诞生于Excel2003及之前版本,那时候没有IFERROR函数,很多时候需要通过ISERROR等函数做出判断后再二次返回结果,导致公式很长。高手们就在摸索中发现了这种连接空白的方式减少字符。 只是通过这种方式得到的结果,都是文本型的值,包括文本型数字,在需要二次计算的时候会有一定的麻烦,请谨慎使用。 公式都完成了,最后利用“格式刷”做一下整体的格式调整。 (1)选择原始数据的A1:G2单元格区域,单击一下【格式刷】按钮。(2)单击一下J1单元格,这时候J1:P2单元格区域便调整完成,与A1:G2单元格区域一致了。 (3)选择J1:P3单元格区域,单击一下【格式刷】按钮,然后向下刷n行,完成格式设置。 本文中所使用的公式并不是最短的,而是我认为逻辑操作上比较简单的,如果想让公式更加简短,可以使用IF函数嵌套:=INDEX(A:A,IF(MOD(ROW(),3)=1,1,IF(MOD(ROW(),3)=0,999,(ROW()+1)/3+1)))&”” 或者借用CHOOSE函数: =INDEX(A:A,CHOOSE(MOD(ROW(),3)+1,999,1,(ROW()+1)/3+1))&”” END 微信扫一扫加关注,Office自学网官方微信公众号, 专注分享软件安装和视频教程,让你一号在手,办公无忧! 给TA打赏 共{{data.count}}人 人已打赏