excel公式 12个Excel函数公式整理【优秀5篇】
今天精选出12个excel函数公式,虽然它们不并常用,但需要用时你还真不好搜到,所以赶紧收藏起来吧,为了帮助大家更好的写作excel公式,精优范文整理分享了5篇12个Excel函数公式整理。
12个Excel函数公式整理 篇一
1、动态获取A列最后一个数字
=LOOKUP(1,0/(A2:A100>0),A2:A100)
2、不重复个数公式
=SUMPRODUCT(1/COUNTIF(A2:A7,A2:A7))
3、提取唯一值公式
=IFERROR(INDEX(A:A,SMALL(IF(MATCH(A$2:A$7,A$2:A$7,0)=ROW(A$1:A$6),ROW(A$2:A$7)),ROW(A1))),"")
4、中国式排名公式
=SUMPRODUCT(($D$4:$D$9>=D4)*(1/COUNTIF(D$4:D$9,D$4:D$9)))
5、提取字符串任一位中的数字
A1的值为 ABC123.45FE ,截取结果:123.45
数组公式:{=LOOKUP(9^9,MID(A1,MATCH(1,MID(A1,ROW(1:9),1)^0,0),ROW(1:9))*1)}
6、金额大写转换公式
=TEXT(LEFT(RMB(A2),LEN(RMB(A2))-3),"[>0][dbnum2]G/通用格式元;[<0]负[dbnum2]G/通用格式元;") & TEXT(RIGHT(RMB(A2),2),"[dbnum2]0角0分;整")
7、个人所得税计算公式
假如A2中是应税工资,则计算个税公式为:
=5*MAX(A2*{0.6,2,4,5,6,7,9}%-{21,91,251,376,761,1346,3016},)
8、一对多查找包含公式
=COUNT(FIND({"AAA花苑","CCC龙廷"},A2))
9、Vlookup多表查找公式
工资表模板中,每个部门一个表。
在查询表中,要求根据提供的。姓名,从销售~综合5个工作表中查询该员工的基本工资。
方法1:
=IFERROR(VLOOKUP(A2,服务!A:G,7,0),IFERROR(VLOOKUP(A2,人事!A:G,7,0),IFERROR(VLOOKUP(A2,综合!A:G,7,0),IFERROR(VLOOKUP(A2,财务!A:G,7,0),IFERROR(VLOOKUP(A2,销售!A:G,7,0),"无此人信息")))))
方法2
=VLOOKUP(A2,INDIRECT(LOOKUP(1,0/COUNTIF(INDIRECT({"销售";"服务";"人事";"综合";"财务"}&"!a:a"),A2),{"销售";"服务";"人事";"综合";"财务"})&"!a:g"),7,0)
10、Sumif多表求和公式
工作表名称常常是不规则的,如下图所示
分析:
首先我们需要用宏表函数取得表名
公式 - 名称管理器 - 新建名称 - 在新建名称中输入名称“sh”,然后“引用位置”框中输入公式:
=MID(GET.WORKBOOK(1),FIND("]",GET.WORKBOOK(1))+1,99)&T(now())
最终公式为:
=SUMPRODUCT((SUMIF(INDIRECT(sh&"!b:b"),D2,INDIRECT(sh&"!c:c"))))
11、筛选后连续序号公式
插入辅助列,输入公式 =1。加入这列是方便在序号列使用公式统计行数。
在序号列输入公式
=SUBTOTAL(2,B$1:B2)
12、用QQ聊天的excel公式
=HYPERLINK("tencent://Message/?websiteName=q&Menu=yes&Uin="&C3,"点击联系他")
最常用的Excel函数公式 篇二
字符串处理公式
1、多单元格字符串合并
公式:c2
=PHONETIC(A2:A7)
说明:Phonetic函数只能对字符型内容合并,数字不可以。
2、截取除后3位之外的部分
公式:
=LEFT(D1,LEN(D1)-3)
说明:LEN计算出总长度,LEFT从左边截总长度-3个
3、截取-前的部分
公式:B2
=Left(A1,FIND("-",A1)-1)
说明:用FIND函数查找位置,用LEFT截取。
4、截取字符串中任一段的公式
公式:B1
=TRIM(MID(SUBSTITUTE($A1," ",REPT(" ",20)),20,20))
说明:公式是利用强插N个空字符的方式进行截取
参加试验检测考试加购买开通微试验题库
5、字符串查找
公式:B2
=IF(COUNT(FIND("河南",A2))=0,"否","是")
说明: FIND查找成功,返回字符的位置,否则返回错误值,而COUNT可以统计出数字的个数,这里可以用来判断查找是否成功。
6、字符串查找一对多
公式:B2
=IF(COUNT(FIND({"辽宁","黑龙江","吉林"},A2))=0,"其他","东北")
说明:设置FIND第一个参数为常量数组,用COUNT函数统计FIND查找结果
最常用的Excel函数公式 篇三
零、随机数
1、随机数函数:
=RAND
首先介绍一下如何用RAND函数来生成随机数(同时返回多个值时是不重复的)。RAND函数返回的随机数字的范围是大于0小于1。因此,也可以用它做基础来生成给定范围内的随机数字。
生成制定范围的随机数方法是这样的,假设给定数字范围最小是A,最大是B,公式是:=A+RAND*(B-A)。
举例来说,要生成大于60小于100的随机数字,因为(100-60)*RAND返回结果是0到40之间,加上范围的下限60就返回了60到100之间的数字,即=60+(100-60)*RAND。
2、随机整数
=RANDBETWEEN(整数,整数)
如:=RANDBETWEEN(2,50),即随机生成2~50之间的任意一个整数。
上面RAND函数返回的0到1之间的随机小数,如果要生成随机整数的话就需要用RANDBETWEEN函数了,如下图该函数生成大于等于1小于等于100的随机整数。
这个函数的语法是这样的:=RANDBETWEEN(范围下限整数,范围上限整数),结果返回包含上下限在内的整数。注意:上限和下限也可以不是整数,并且可以是负数。
12个Excel函数公式整理 篇四
一、数字处理
1、取绝对值
=ABS(数字)
2、取整
=INT(数字)
3、四舍五入
=ROUND(数字,小数位数)
二、判断公式
1、把公式产生的错误值显示为空
公式:C2
=IFERROR(A2/B2,"")
说明:如果是错误值则显示为空,否则正常显示。
2、IF多条件判断返回值
公式:C2
=IF(AND(A2<500,B2="未到期"),"补款","")
说明:两个条件同时成立用AND,任一个成立用OR函数。
三、统计公式
1、统计两个表格重复的内容
公式:B2
=COUNTIF(Sheet15!A:A,A2)
说明:如果返回值大于0说明在另一个表中存在,0则不存在。
2、统计不重复的总人数
公式:C2
=SUMPRODUCT(1/COUNTIF(A2:A8,A2:A8))
说明:用COUNTIF统计出每人的出现次数,用1除的方式把出现次数变成分母,然后相加。
最常用的Excel函数公式 篇五
求和公式
1、隔列求和
公式:H3
=SUMIF($A$2:$G$2,H$2,A3:G3)
或
=SUMPRODUCT((MOD(COLUMN(B3:G3),2)=0)*B3:G3)
说明:如果标题行没有规则用第2个公式
2、单条件求和
公式:F2
=SUMIF(A:A,E2,C:C)
说明:SUMIF函数的基本用法
3、单条件模糊求和
公式:详见下图
说明:如果需要进行模糊求和,就需要掌握通配符的使用,其中星号是表示任意多个字符,如"*A*"就表示a前和后有任意多个字符,即包含A。
4、多条件模糊求和
公式:C11
=SUMIFS(C2:C7,A2:A7,A11&"*",B2:B7,B11)
说明:在sumifs中可以使用通配符*
5、多表相同位置求和
公式:b2
=SUM(Sheet1:Sheet19!B2)
说明:在表中间删除或添加表后,公式结果会自动更新。
6、按日期和产品求和
公式:F2
=SUMPRODUCT((MONTH($A$2:$A$25)=F$1)*($B$2:$B$25=$E2)*$C$2:$C$25)
说明:SUMPRODUCT可以完成多条件求和
考括坟籍,博采群议。上面的5篇12个Excel函数公式整理是由精优范文精心整理的excel公式范文范本,感谢您的阅读与参考。