excel函数大全完整版
本文已影响2.24W人
本文已影响2.24W人
excel函数大全完整版,相信大家都想快速地提高自己的Excel水平,那么最直接有效的的方法就是掌握一些小技巧。就是可以拿来即用,下面是excel函数大全完整版。
excel函数大全完整版1
一、数字处理
1、取绝对值 =ABS(数字)
2、取整 =INT(数字)
3、四舍五入 =ROUND(数字,小数位数)
二、判断公式
1、把公式产生的错误值显示为空
公式:=IFERROR(A2/B2,"")
说明:如果是错误值则显示为空,否则正常显示。
2、IF多条件判断返回值
公式:=IF(AND(A2<500,B2=" 未到期"),"补款","")
说明:两个条件同时成立用AND,任一个成立用OR函数。
三、统计公式
1、统计两个表格重复的内容
公式:=COUNTIF(Sheet15!A:A,A2)
说明:如果返回值大于0说明在另一个表中存在,0则不存在。
2、统计不重复的总人数
公式:=SUMPRODUCT(1/COUNTIF(A2:A8,A2:A8))
说明:用COUNTIF统计出每人的出现次数,用1除的方式把出现次数变成分母,然后相加。
四、求和公式
1、隔列求和
公式:=SUMIF($A$2:$G$2,H$2,A3:G3) 或 =SUMPRODUCT((MOD(COLUMN(B3:G3),2)=0)*B3:G3)
说明:如果标题行没有规则用第2个公式。
2、单条件求和
公式:=SUMIF(A:A,E2,C:C)
说明:SUMIF函数的基本用法
3、多条件模糊求和
公式:=SUMIFS(C2:C7,A2:A7,A11&"*",B2:B7,B11)
说明:在sumifs中可以使用通配符*
5、多表相同位置求和
公式:=SUM(Sheet1:Sheet19!B2)
说明:在表中间删除或添加表后,公式结果会自动更新。
6、按日期和产品求和
公式:=SUMPRODUCT((MONTH($A$2:$A$25)=F$1)*($B$2:$B$25=$E2)*$C$2:$C$25)
说明:SUMPRODUCT可以完成多条件求和
五、查找与引用公式
1、单条件查找公式
公式:=VLOOKUP(B11,B3:F7,4,FALSE)
说明:查找是VLOOKUP最擅长的,基本用法
2、双向查找公式
公式:=INDEX(C3:H7,MATCH(B10,B3:B7,0),MATCH(C10,C2:H2,0))
说明:利用MATCH函数查找位置,用INDEX函数取值
3、查找最后一条符合条件的记录。
公式:
说明:0/(条件)可以把不符合条件的变成错误值,而lookup可以忽略错误值
4、按数字区域间取对应的值
公式:
公式说明:VLOOKUP 和LOOKUP函数都可以按区间取值,一定要注意,销售量列的数字一定要升序排列。
六、字符串处理公式
1、多单元格字符串合并
公式:=PHONETIC(A2:A7)
说明:Phonetic函数只能对字符型内容合并,数字不可以。
2、截取左部分
公式:=LEFT(D1,LEN(D1)-3)
说明:LEN计算出总长度,LEFT从左边截总长度-3个
3、截取-前的部分
公式:=Left(A1,FIND("-",A1)-1)
说明:用FIND函数查找位置,用LEFT截取。
4、截取字符串中任一段的公式
公式:=TRIM(MID(SUBSTITUTE($A1,"",REPT("",20)),20,20))
说明:公式是利用强插N个空字符的方式进行截取
5、字符串查找
公式:=IF(COUNT(FIND("河南",A2))=0,"否","是")
说明:用来判断查找是否成功。
6、字符串查找一对多
公式:=IF(COUNT(FIND({"辽宁","黑龙江","吉林"},A2))=0,"其他","东北")
说明:设置FIND第一个参数为常量数组,用COUNT函数统计FIND查找结果
excel函数大全完整版2
一、Excel工作表函数:求和类。
(一)Sum。
功能:计算指定的单元格区域中所有数值的和。
语法结构:=Sum(值1,值2……值N)。
目的:计算总“月薪”。
方法:
在目标单元格中输入公式:=SUM(1*G3:G12),并用Ctrl+Shift+Enter填充。
解读:
如果直接用Sum函数进行求和,结果为0,究其原因就在于“月薪”为文本型的数值,如果不想调整数据类型,可以给每个参数乘以1将其强制转换为数值类型,然后用Sum函数进行求和。
(二)Sumif。
功能:对满足条件的单元格求和,即单条件求和。
语法结构:=Sumif(条件范围,条件,[求和范围]),当“条件范围”和“求和范围”相同时,可以省略“求和范围”。
目的:根据“性别”计算总“月薪”。
方法:
在目标单元格中输入公式:=SUMIF(D3:D12,J3,G3:G12)。
解读:
由于“条件范围”和“求和范围”不相同,所以不能省略参数“求和范围”。
(三)Sumifs。
功能:对一组给定条件指定的单元格求和。
语法结构:=Sumifs(求和范围,条件1范围,条件1,条件2范围,条件2……)
目的:根据“性别”统计相应“学历”下的总“月薪”。
方法:
在目标单元格中输入公式:=SUMIFS(G3:G12,D3:D12,J3,F3:F12,K3)。
解读:
参数“条件范围”和“条件”必须成对出现,否则公式无法正确执行!
二、Excel工作表函数:判断类。
(一)If。
功能:判断是否满足某个条件,如果满足返回一个值,如果不满足则返回另一个值。
语法结构:=If(判断条件,条件为真时的`返回值,条件为假时的返回值)。
目的:判断“月薪”的档次,如果≥4000,则返回“高”,如果≥3000,则返回“中”,否则返回“低”。
方法:
在目标单元格中输入公式:=IF(G3>=4000,"高",IF(G3>=3000,"中",IF(G3<3000,"低")))。
解读:
If函数除了单独判断外,还可以嵌套使用,但多级嵌套时,需要理清逻辑关系,否则容易出错!
(二)Ifs。
功能:判断是否满足一个或多个条件并返回与第一个TRUE条件对应的值。
语法结构:=Ifs(条件1,返回值1,条件2,返回值2……)。
目的:判断“月薪”的档次,如果≥4000,则返回“高”,如果≥3000,则返回“中”,否则返回“低”。
方法:
在目标单元格中输入公式:=IFS(G3>=4000,"高",G3>=3000,"中",G3<3000,"低")。
解读:
参数中的“条件”和“返回值”必须成对出现,但该函数仅能应用于16及以上版本的Excel中,在WPS高版本中也可以使用哦!
三、Excel工作表函数:查找类。
(一)Vlookup。
功能:搜索表区域首列满足条件的元素,确定待检索单元格在区域中的的序号,再进一步返回选定单元格的值。
语法结构:=Vlookup(查询值,数据范围,返回值列数,查询模式),查询模式分为精准查询和模糊查询。
目的:根据“员工姓名”查询对应的“月薪”。
方法:
在目标单元格中输入公式:=VLOOKUP(J3,B3:G12,6,0)。
解读:
参数“返回值列数”要根据“数据范围”来确定,是返回值所在的的相对列数。
(二)Lookup。
功能:从单行或单列或单数组中查找一个值。
Lookup函数具有两种语法结构:向量形式和数组形式。
1、向量形式。
功能:从单行或单列中查找查找指定的值,返回第二个单行或单列中相同位置的值。
语法结构:=Lookup(查找值,查找值所在范围,[返回值所在范围]),当“查找值所在范围”和“返回值所在范围”相同时,可以省略“返回值所在范围”。
目的:根据“员工姓名”查询对应的“月薪”。
方法:
1、以“员工姓名”为主要关键字进行升序排序。
2、在目标单元格中输入公式:=LOOKUP(J3,B3:B12,G3:G12)。
解读:
在使用Lookup函数查询数据时,首次要以“查询值”为主要关键字进行升序排序,否则无法得到正确的结果。
2、数组形式。
功能:从指定的范围第一列或第一行中查询指定的值,返回指定范围中最后一列或最后一行对应位置上的值。
语法:=Lookup(查找值,查询范围)。
重点解读:
从“功能”中可以看出,Lookup函数的数组形式,查找值必须在查询范围的第一列或第一行中,返回的值必须是查询范围的最后一列或最后一行对应的值。即:查找值和返回值在查询范围的“两端”。
目的:根据“员工姓名”查询对应的“月薪”。
方法:
1、以“员工姓名”为主要关键字进行升序排序。
2、在目标单元格中输入公式:=LOOKUP(J3,B3:G12)。
解读:
查询值必须在数据范围的第一列,返回值必须在数据范围得最后一列。
excel函数大全完整版3
1、IF函数条件判断
IF函数是最常用的判断类函数之一,能完成非此即彼的判断。
如下图,考核得分的标准为9分,要判断B列的考核成绩是否合格。
=IF(B4>=9,”合格”,”不合格”)
IF,相当于普通话的“如果”,常规用法是:
IF(判断的条件,符合条件时的结果,不符合条件时的结果)
2、多条件判断
如果部门为生产、岗位为主操 有高温补助。在D列使用公式:
=IF(AND(B2=”生产”,C2=”主操”),”有”,”无”)
AND函数对两个条件判断,如果同时符合,IF函数返回“有”,否则为无。
3、条件求和
使用SUMIF函数计算一班的总成绩:
=SUMIF(D2:D5,F2,C2:C5)
SUMIF用法是:
=SUMIF(条件区域,指定的求和条件,求和的区域)
用通俗的话描述就是:
如果D2:D5区域的班级等于F2单元格的“一班”,就对C2:C5单元格对应的区域求和。
4、多条件求和
要统计部门为生产,并且岗位为主操的补助总额。
公式为:
=SUMIFS(D2:D9,B2:B9,F2,C2:C9,G2)
SUMIFS用法是:
=SUMIFS(求和的区域,条件区域1,指定的求和条件1,条件区域2,指定的求和条件2,……)
5、条件计数
如下图,要统计指定店铺的业务笔数。也就是统计B列中有多少个指定的店铺名称。
=COUNTIF(B2:B12,E3)
COUNTIF函数统计条件区域中,符合指定条件的单元格个数。常规用法为:
=COUNTIF(条件区域,指定条件)
excel表格的基本操作快捷键大全
Alex大叔年6月水瓶座运势完整版
抖音come back home early是什么歌 抖音come back home early歌词完整版
Alex大叔年6月射手座运势完整版
excel如何提取数字部分内容
Alex大叔年6月天蝎座运势完整版
excel表格怎么批量提取数据
excel供应链经营数据分析
yeezy350 Cloud White Reflective反光版发售价及发售时间
小时代歌曲大全盘点 《我好想你》完整版MV首播
全国奇葩派出所名称大全(完整版)
excel从多张表中提取数据
苹果电脑excel表格快捷键大全
excel某一列提取部分数字
xxxtentacion抖音很火的歌叫什么 jocelyn flores歌词
excelsior是什么牌子 excelsior鞋子尺码偏大吗
大明风华全部歌曲大全 完整版歌词及演唱者介绍
excel如何从一个表格中提取想要的数据
excel按条件提取数据
抖音中笨李白的是什么歌 believer完整版歌词
excel身份证格式怎么调整
excel函数公式大全 excel函数常用公式
excel表格整体格式调整
excel口红什么牌子 excel口红怎么样
coccinelle是什么牌子?coccinelle哪个国家的?coccinelle什么档次?
2015维多利亚的秘密内衣秀完整版
excel提取多个表格数据
Alex大叔年6月双鱼座运势完整版
任嘉伦新歌Dreamers Never Sleep歌词含义 完整版歌词介绍
大牌明星演译AlexanderMcQueen全新升级升級手袋系列产品
怎样在excel表格中提取我想要的数据
吴亦凡freestyle表情包高清无水印完整版打包
Alex大叔年6月双子座运势完整版