office办公自动化excel函数应用大全

目录

index函数用法和实例应用INDEX和MATCH函数应用

index函数用法和实例应用

容提要：本文通过直观的实例来认识Excel中index函数的使用方法，更进阶的用法可以参考文章最后的《INDEX和MATCH函数应用》。

Excel中INDEX函数是很常用的查找引用函数。通常情况下，嵌套其余函数一起使用，比如index+match。

在使用INDEX()函数时，第二、三参数一般情况与MATCH()函数配合使用，以实现动态查找引用的目的。

第一：index函数用法解释

INDEX函数的用法是返回列表或数组中的元素值，此元素由行序号和列序号的索引值确定。即行列交叉点所在的引用。

INDEX函数的语法为：INDEX(区域,行,列)

需要注意一点的是：此处的行列序号是相对于第一参数的区域而言，不是Excel工作表中的行或列序号。

如果区域只包含一行或一列，则相对应的参数row_num 或column_num可以相应省略1个。

第二：index函数简单举例应用

如下表所示，根据A1：D5单元格区域，使用index函数查找A8单元格对应的产品名称。

提供三种解法，任意一种均可。

一：=INDEX(A4:D4,1,4)

二：=INDEX(A4:D4,,4)

三：=INDEX(A1:D5,4,4)

解法一，在A4:D4一行中，查找第一行，第四列的值。

解法二，根据第一部分index函数解释，如果区域只包含一行，则对应的行参数可以省略，因此省略后就成为：=INDEX(A4:D4,,4)。

解法三：在A1:D5区域，查找第四行，第四列的值。

本文只是index函数的一个基础用法，更进阶的用法，请参考：INDEX和MATCH函数应用

INDEX和MATCH函数应用

容提要：本讲座通过案例讲解INDEX和MATCH函数嵌套使用的方法。

第一部分：INDEX和MATCH函数用法介绍

第一，MATCH函数用法介绍

MATCH函数也是一个查找函数。MATCH 函数会返回匹配值的位置而不是匹配值本身。在使用时，MATCH函数在众多的数字中只查找第一次出现的，后来出现的它返回的也是第一次出现的位置。

MATCH函数语法：MATCH(查找值，查找区域，查找模式)

可以通过下图来认识MATCH函数的用法：

=MATCH(41,B2:B5,0)，得到结果为4，返回数据区域B2:B5 中41 的位置。

=MATCH(39,B2:B5,1)，得到结果为2，由于此处无正确匹配，所以返回数据区域B2:B5 中(38) 的位置。注：匹配的查找值，MATCH 函数会查找小于或等于(39)的最大值。

=MATCH(40,B2:B5,-1)，得到结果为#N/A，由于数据区域B2:B5 不是按降序排列，所以返回错误值。

第二，INDEX函数用法介绍

INDEX函数的功能就是返回指定单元格区域或数组常量。如果同时使用参数行号和列号，函数INDEX返回行号和列号交叉处的单元格中的值。

INDEX函数语法：INDEX(单元格区域，行号，列号)

可以通过下图来认识INDEX函数的用法：

=INDEX(A1:C6,2,3)，意思就是返回A1:C6中行号是2 列号是3 ,即第二行与第三列的交叉处，也就是C2单元格的值，为84。

第二部分：INDEX和MATCH函数应用案例介绍

下图工作表所示的是一个产品的型号和规格的价格明细表。通过这个表的数据，进行一些对应的查询操作。

第一，单击B5单元格下拉按钮，选择型号，然后在B6单元格完成型号所在行号的查询。如下图所示：

随意选择一个型号，比如A0110，然后在B6单元格输入公式：

=MATCH($B$5,$D$4:$D$12,0)，得到结果1。

公式解释：用MATCH函数查找B5单元格这个型号在D4:D12区域中对应的位置。其中的0参数可以省略不写。MATCH函数中0代表精确查找，1是模糊查找。

第二，单击B9单元格下拉按钮，选择规格，然后在B10单元格完成规格所在列号的查询。如下图所示：

随意选择一个规格，比如101，然后在B10单元格输入公式：=MATCH(B9,E3:G3,0)，得到结果1。

第三，查询B6和B10单元格所对应的价格。

价格的查询，可以使用index函数完成，输入公式：=INDEX(E4:G12,B6,B10)可以得到结果为78。嵌套上面的match函数，可以将公式改为：

=INDEX(E4:G12,MATCH(B5,D4:D12,0),MATCH(B9,E3:G3,0))。大家可以变化C3中的型号来看看结果是否正确。

通过下面工作表的源数据，利用index函数实现行列汇总查询。

INDEX函数的帮助文件知道，如果将row_num 或column_num 设置为0，函数INDEX 则分别返回整个列或行的数组数值。通过用法说明，来实现上图的行列汇总。

首先，单击C3单元格下拉按钮，选择数据，比如选择A0111，然后在C4单元格进行C3单元格对应的行号查找，公式为：=MATCH(C3,E2:E10,)，得到结果为2，说明A0111在E2:E10区域的第二行。

然后对行号所对应的价格进行汇总求和。在C5单元格输入公式：

=SUM(INDEX(F2:H10,MATCH(C3,E2:E10,),))

如果选中公式中的INDEX(F2:H10,MATCH(C3,E2:E10,),)部分，按下F9键，执行结果是{80,97,84}，可以看到就是对第二行的数据进行求和。

同样的方法，可以进行列汇总，在C9单元格输入公式：

=SUM(INDEX(F2:H10,,MATCH(C7,F1:H1,)))。

说明：查找行号和查找列号，只是过渡一下，帮助新手朋友加深对match函数的理解和使用，对熟悉的朋友，可以直接在行汇总一步完成公式的输入。

根据下面的工作表，进行区域汇总求和。

首先，分别在开始行号、结束行号、结束列号、结束列号选定需要求和的区域，比如A0110、A0111、201、301。此题可以套用下图的格式进行求和。

因此，在C20单元格输入求和公式：

=SUM(INDEX(F16:H24,MATCH(C16,E16:E24,),MATCH(C17,E16:E24,)):INDEX(F16:H24,MATCH (C18,F15:H15,),MATCH(C19,F15:H15,)))

从身份证中提取性别

=IF(MOD(MID(A2,15,3),2),"男","女")

根据出生年月计算年龄

=DATEDIF(A2,TODAY(),"y")&"周岁"

某个日期是星期几

=TEXT(A2,"aaaa")

某个日期所在月有几天

=DAY(EOMONTH(A2,0))

下一个工作日是哪天

=WORKDAY(A2,1,A5:A7)

区分指定日期是上中下旬

=LOOKUP(DAY(A2),{0,11,21},{"上旬","中旬","下旬"})

根据身份证号计算出生年月

=--TEXT(MID(A2,7,8),"0-00-00")

查找最后一条符合条件的记录

公式的模式化写法为：

=LOOKUP(1,0/(条件区域=条件),查询区域)

查询符合多个条件的记录

公式的模式化写法为：

=LOOKUP(1,0/((条件区域1=条件1)*(条件区域2=条件2)),查询区域)

逆向查询

LOOKUP函数最后的查询区域可以灵活写成任意一列，从右至左、自下而上查询，统统没问题。

如下图所示，要查询原料类别为"A3"的采购员姓名。

C13单元格公式为：

=LOOKUP(1,0/(B4:B11="A3"),A4:A11)

查询最后一笔业务日期

如下图所示，要查询不同业务员最后一笔业务的日期。

公式的模式化写法为：

=LOOKUP(1,0/(条件区域<>""),查询区域)

模糊查询考核等级

如下图所示，要根据E:F列的等级对照表，判断B列的考核成绩。

C2单元格公式为：

=LOOKUP(B4,E$5:F$8)

这种方法可以取代IF函数完成多个区间的判断查询，前提是对照表的首列，必须是升序处理。

提取单元格的数字

如下图所示，要从A列的混合容中提取出数值。

A2公式为

=-LOOKUP(1,-LEFT(A2,ROW($1:$99)))

首先用LEFT函数从A2单元格左起第一个字符开始，依次返回长度为

ROW($1:$99)也就是1至99的字符串，添加负号后，数值转换为负数，含有文本字符的字符串则变成错误值。

LOOKUP函数使用1作为查询值，在由负数、0和错误值构成的数组中，忽略错误值提取最后一个等于或小于1的数值。

最后再使用负号，将提取出的负数转为正数。

sumifs函数多条件求和实例

[日期：2011-12-07] 来源：IT部落窝作者：IT部落窝阅读：171540次[字体：大中小]

容提要：文章首先介绍sumifs函数基本用法，然后以一个综合的实例来剖析sumifs函数的详细深入使用。

第一部分，sumifs函数用法介绍

excel中sumifs函数是2007以后版本新增的多条件求和函数。

sumifs函数的语法是：SUMIFS(求和区域, 条件区域1,条件1, [条件区域2,条件2], ...) 说明：[]以的条件区域2、条件2为可选参数。最多允许127 个区域/条件对。

第二部分，sumifs函数实例介绍

项目一：客户A的销售额

=SUMIFS(C2:C10,A2:A10,A2)

项目二：客户A的1月份销售额

=SUMIFS(C2:C10,A2:A10,A2,B2:B10,B2)

项目三：客户A的1月份和3月份销售额

=SUM(SUMIFS(C2:C10,A2:A10,A2,B2:B10,{1,3}))

项目四：客户A和C的销售额

=SUM(SUMIFS(C2:C10,A2:A10,{"A","C"}))

项目五：客户A和C的1月份销售额合计

=SUM(SUMIFS(C2:C10,A2:A10,{"A","C"},B2:B10,B2))

项目六：客户A的1月份和客户C的3月份销售额合计

=SUM(SUMIFS(C2:C10,A2:A10,{"A","C"},B2:B10,{1,3}))

项目七：客户A和客户C的1月份\3月份\4月份销售额合计

=SUM(SUMIFS(C2:C10,A2:A10,{"A","C"},B2:B10,{1;3;4}))

项目八：客户A\B\C的1月份\3月份\4月份销售额合计

=SUM(SUMIFS(C2:C10,A2:A10,{"A","B","C"},B2:B10,{1;3;4}))

Sumifs函数与SumProduct函数都可以实现多条件求和。经测试，10000行数据，双条件求和，Sumifs函数比SumProduct快差不多三倍。