office办公自动化excel函数应用大全.docx
- 文档编号:9623621
- 上传时间:2023-05-20
- 格式:DOCX
- 页数:14
- 大小:344.34KB
office办公自动化excel函数应用大全.docx
《office办公自动化excel函数应用大全.docx》由会员分享,可在线阅读,更多相关《office办公自动化excel函数应用大全.docx(14页珍藏版)》请在冰点文库上搜索。
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函数多条件求和实例
[日期:
2021-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快差不多三倍。
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- office 办公自动化 excel 函数 应用 大全