- ?
Excel逻辑函数大全,函数应用大战你准备好了没?
萧然
展开
FALSE和TRUE函数:返回逻辑值
这两个函数的功能是返回逻辑值FALSE和TRUE,都没有参数。
FALSE函数的表达式是FALSE(),
TRUE函数的表达式是TRUE()。
实际应用
用户可以使用这两个函数来直接返回逻辑值,也可以使用公式来返回逻辑值,如图:
上面的公式“=IF(FALSE(),1,2)”中,首先FALSE函数返回逻辑值FALSE,然后用IF函数取得数值2。
AND函数:进行交集运算
AND函数的功能是对多个逻辑值进行交集运算,函数的返回值是逻辑值。当所有参数的逻辑值为真时,返回TRUE;只要有一个参数的逻辑值为假,即返回FALSE.
AND函数的表达式是AND(logical1,logical2, ...)。
参数logical1、logical2……表示待检测的1~30个条件值,各条件值可为TRUE或FALSE。
下面的例子显示的是一次考试成绩单,教师想找出三门成绩都在平均分以上的学生,结果显示为TRUE是三科成绩都超过平均分的学生,原始数据如图所示。
在单元格E2中输入公式
“=AND(B2>=AVERAGE($B$2:$B$11),C2>=AVERAGE($C$2:$C$11),D2>=AVERAGE($D$2:$D$11))”,
首先通过三个公式来获得三个逻辑值,然后使用AND函数得到最后的结果。最后利用自动填充功能,得到其他单元格的数据:
NOT函数:取反
NOT函数的功能是对参数值求反。
NOT函数的表达式是NOT(logical)
参数logical表示一个可以计算出TRUE或FALSE的逻辑值或逻辑表达式。
在某个数据库输入的过程中,用户输入长度值,但是为了保证长度的值必须大于0,用户可以使用NOT函数来控制这个条件:
在单元格B2中输入公式“=IF(NOT(A2>0),"请输入正值",A2)”。如果A2中的数据不大于0,将会提示“请输入正值”;如果大于0,那么接受用户的输入值长度。
函数求值
在实际运用中,函数的应用可能会是多种形式。有时函数的形式比较简单,可以直接用前面讲解的数学函数来求解。但是有时比较复杂,是间断函数或者其他形式。这个例子讲解的就是这种情况,函数的求解规则"
用户在本例中需要根据上面的条件,求解各种不同的参数条件下的函数结果。其中不同的参数条件:
步骤1:在单元格C10中输入公式“=IF(OR(A10=0,A10=B10),0,IF(A10<=1/2*B10,A10*2,IF(A10
分析上面的公式:上面的公式就是根据前面的函数求解条件列出,首先利用OR(A10=0,A10=B10)分析了两种情况:“P=0”和“P=T”,这两种情况的函数结果都是0。因此,满足上面两种情况中的任何一种情况,函数的结果都是0。除了这两种情况,接着考虑其他的情况。IF(A10<=1/2*B10,A10*2,IF(A10
步骤2:完成其他单元格的内容。利用自动填充功能来计算其他单元格的内容:
好了以上就是一些比较重要的一些逻辑函数,大家可能在学习和办公中遇得到,欢迎大家进行补充!
- ?
常用7个Excel函数,可直接套用,新手必备!
陈又晴
展开
你还在因为不会使用Excel统计函数而烦恼吗?在我们操作插入表格函数的时候,时常因为“找不到对象”以及“不能插入对象”而烦恼。
那么今天就为你们介绍7个经常使用的表格统计公式,直接复制函数即可帮你解决问题。
一、计算数据合(注:函数里表格坐标,要按照实际表格坐标调整)
函数:=SUM(B2:F2)
二、计算表格总平均值(注:函数表格位置要按实际调整)
函数:=AVERAGE(G2:G7)
三、查找表格相同值(注:函数里表格坐标,要按照实际表格坐标调整)
函数:=IF(COUNTIF(A:A,A2)>1,"相同","")
四、计算表格不重复的个数(注:函数里表格坐标,要按照实际表格坐标调整)
函数:=IF(COUNTIF(A2:A99,A2)>1," 首次重复","")
五、计算出男生表格总平均成绩(注:函数里表格坐标,要按照实际表格坐标调整)
函数:=AVERAGEIF(B2:B7,"男",C2:C7)
六、计算出成绩排名(注:函数里表格坐标,要按照实际表格坐标调整)
函数:=SUMPRODUCT((B$2:B$7>B2)/COUNTIF(B$2:B$7,B$2:B$7))+1
以上就是今天为大家分享的所有Excel函数啦!,其实在我们统计表格中,还有很多函数,小编只是写了其中一些,如果大家知道一些实用性非常强的函数,欢迎在评论区积极分享。
- ?
Excel函数公式:Excel常用函数公式——基础篇(六)
一刀
展开
一、WEEKDAY函数。
作用:给出指定日期对应的星期。
语法:=WEEKDAY(日期或对日期的引用,统计方式)。
统计方式有:
一般情况下我们使用2,符合我们的日常习惯,暨星期一为1,……星期天为7。
方法:
在目标单元格中输入公式:=WEEKDAY(C3,2)。
二、ISERROR函数。
作用:主要用于测试函数式返回的结果是否有错误,如果有错,返回TRUE ,否则返回FALSE。
语法:=ISERROR(值或表达式)。
方法:
在目标单元格中输入公式:=ISERROR(D3/E3)。
三、SUBTOTAL函数。
作用:根据分类代码返回汇总情况。
语法:=SUBTOTAL(汇总代码,值或对值的引用)。
汇总代码有:
其中1-11包含隐藏值,101-111忽略隐藏值。
方法:
在目标单元格中输入公式:=SUBTOTAL(1,D3:D9)、=SUBTOTAL(3,D3:D9)
四、TEXT函数。
作用:根据指定的格式将相应的数字转换为文本格式。
语法:=TEXT(值,格式)。
方法:
在目标单元格中输入公式:=TEXT(D3,"$0.00")。
五、VALUE函数。
作用:讲一个代表数值的文本字符串转换为数值型。
语法:=VALUE(文本字符串)。
方法:
在目标单元格中输入公式:=VALUE(D3)。
- ?
Excel函数公式:查找函数LOOKUP的神应用和技巧
傅山芙
展开
Excel中,数据查询从来不是一个新鲜的话题,基本上每天都在用,但是高效快捷的查询技巧,并不是每个人都会的。本节结合实例,学习LOOKUP函数如何搞定各种查询问题。
一、逆向查询。
目的:查询对应人员的学号。
方法:
在目标单元格中输入公式:=LOOKUP(1,0/(B3:B9=H3),A3:A9)。
解读:我们先来看,B3:B9=H3,也就是说判断B3:B9中的值是否等于H3,因此判断结果是{0,0,1,0,0,0,0},因为之后第二个值等于H3中的值。{0,0,1,0,0,0,0}作为分母,被0除,得出的结果就是{错误值,错误值,0,错误值,错误值,错误值,错误值}。在这个数组中进行查找,会查找不到,那么将会匹配比1小的最大值,也就是0,所以就查找到了H3对应值的位置。
万能公式:
=LOOKUP(1,0/(查找区域=查找值),返回区域)。
二、单条件查询。
目的:根据序号查找对应的姓名。
方法:
在目标单元格中输入公式:=LOOKUP(1,0/(A3:A9=H3),B3:B9)。
三、多条件查询。
目的:查询“王东”的成绩。
方法:
在目标单元格输入公式:=LOOKUP(1,0/((B3:B9=H3)*(D3:D9=I3)),C3:C9)。
万能公式:
=Lookup(1,0/((条件1)*(条件2)……条件N),返回值的范围)。
四、根据指定的部分内容,查找全部内容。
目的:根据身份证号的后四位,查询身份证号码。
方法:
在对应的目标单元格输入公式:=LOOKUP(1,0/(RIGHT(C3,4)=I3),C3:C9)。
解读:
利用函数RIGHT提取身份证号的后4位,然后和单元格I3中的值比较,得到数据组,然后进一步的对比,得出结果。
- ?
excel函数公式的巧妙运用
Sally
展开
excel今天文章主题仍然是if函数,在上两篇文章excel关于函数if的巧妙运用excel关于if函数的嵌套使用中,第一篇文章,我们介绍了怎样运用if函数在两种不同结果之间进行判断计算的操作过程,第二篇文章中我们更近一步,函数if与函数if本身的嵌套使用,从而运用if函数解决了应对三种不同结果进行判断的解决方法。(今天这篇文章承接上面两篇文章的内容而来,感兴趣的朋友可以通过链接去看看)。
今天我们再更进一步,介绍一下面对三种以上情况结果下,函数if的运用方法。咱们外甥打灯笼,照旧按老规矩来,运用实例来说话。
今天我们的实例是这样的,我们已知业务员的等级分为A级、B级、C级、D级四个等级,分别对应的奖金为10000元、9000元、8000元和7000元,现在我们有四个业务员,分别是丁一、牛二、张三、李四,他们正好对应了A级、B级、C级、D级四个等级,这时就要求我们运用if函数根据四个业务员的等级来判断他们各自的奖金。excel工作表具体如下所示:
实例图表具体操作方法一(方法一继承了文章“excel关于if函数的嵌套使用”中的方法):
在G4单元格输入“=IF(F4="A级",10000,IF(F4="B级",9000,IF(F4="C级",8000,7000)))”(ps:if函数中的标点符号在英文输入法状态下输入),按回车键,得到业务员丁一的应发奖金数,然后通过填充柄拖拽的方式向下拖拽,我们就能到其他业务员的应发奖金数了。具体操作可参考下图:
实例图表方法评价:运用if函数三层嵌套的方式成功解决了现有问题,但是一旦面对答案更多的情况,肯定会让人难以忍受,而且在excel中最多只能嵌套七层,所以这种方法限制太大了。
具体操作方法二(方法二继承了文章“excel关于函数if的巧妙运用”中的方法,使用函数if最简单的方式进行叠加运算):
在G4单元格输入“=IF(F4="A级",10000,0)+IF(F4="B级",9000,0)+IF(F4="C级",8000,0)+IF(F4="D级",7000,0)”,按回车键,得到业务员丁一的应发奖金数,然后通过填充柄拖拽的方式向下拖拽,我们就能到其他业务员的应发奖金数了。具体操作可参考下图:
实例图表方法评价:使用函数if最简单的方式进行叠加运算的方法成功解决了现有问题,且该方法excel并没有进行限制,仅仅需要不断进行复制并修改相关数据的方式就能进行叠加运算,但是局限性也相当明显,一旦面对答案太多的情况,就不再适用了。
具体操作方法三:方法三是真正实用的方法,需要运用到函数vlookup。
在G4单元格输入“=VLOOKUP(F4,$B$4:$C$7,2,0)”,按回车键,得到业务员丁一的应发奖金数,然后通过填充柄拖拽的方式向下拖拽,我们就能到其他业务员的应发奖金数了。具体操作可参考下图:
实例图表方法评价:与函数if的两种方法相比,面对答案太多的情况,函数vlookup的局限性大大减小,堪称相对完美的方法了。
今天的分享也就到此结束了。觉得对你们有用的小伙伴们请点赞关注吧!您的鼓励是我前进的动力,也希望擅长运用办公软件的小伙伴们能够不吝赐教,积极的留言,教会小编更多的excel运用的小技巧,欢迎一起来探讨学习!!
- ?
Excel函数公式:Excel常用函数公式——基础篇(七)
日13
展开
一、LOOKUP函数。
作用:提取查询。
语法:=LOOKUP(查询值,查询范围)
方法:
在目标单元格中输入公式:=LOOKUP(9^9,C:C)、=LOOKUP(9^9,D:D)。
解读:
当需要查询最后一条相关记录时,查询值用一个很大的值9^9来替代,实现向下匹配。
二、TEXT函数。
作用:返回间隔分钟数。
语法:=TEXT(值,格式代码)。
方法:
在目标单元格中输入公式:=TEXT(G6-G4,"[m]分钟")。
三、INDEX+MATCH组合函数。
作用:动态查询所需的值。
方法:
在目标单元格中输入公式:=INDEX($B$3:$F$9,MATCH($B$13,$B$3:$B$9,0),MATCH(C$12,$B$2:$F$2,0))。
四、SUM函数。
作用:合并单元格求和。
方法:
在目标单元格中输入公式:=SUM(D3:D9)-SUM(E4:E9)。
五、NETWORKDAYS.INTL函数。
作用:计算两个日期之间的工作日天数。
语法:=NETWORKDAYS.INTL(开始日期,结束日期,周末方式,其它节假日)。
周末统计方式有:
方法:
在目标单元格中输入公式:=NETWORKDAYS.INTL(C3,D3,1)、=NETWORKDAYS.INTL(C3,D3,1,F4:F6)。
解读:
从实际的应用中给我们可以看出,如果省略第四个参数,默认为没有请假情况。
- ?
7天,整理出常用的Excel函数公式大全(共21个)
詹绯
展开
在HR同事电脑中,经常看到海量的Excel表格,员工基本信息、提成计算、考勤统计、合同管理....看来再完备的HR系统也取代不了Excel表格的作用。一周前,交给小助理木炭一个任务,尽可能多的收集HR工作中的Excel公式。进行了整理编排,于是有了这篇本平台史上最全HR的Excel公式+数据分析技巧集。
一、员工信息表公式
1、计算性别(F列)
=IF(MOD(MID(E3,17,1),2),"男","女")
2、出生年月(G列)
=TEXT(MID(E3,7,8),"0-00-00")
3、年龄公式(H列)
=DATEDIF(G3,TODAY(),"y")
4、退休日期 (I列)
=TEXT(EDATE(G3,12*(5*(F3="男")+55)),"yyyy/mm/dd aaaa")
5、籍贯(M列)
=VLOOKUP(LEFT(E3,6)*1,地址库!E:F,2,)
注:附带示例中有地址库代码表
6、社会工龄(T列)
=DATEDIF(S3,NOW(),"y")
7、公司工龄(W列)
=DATEDIF(V3,NOW(),"y")&"年"&DATEDIF(V3,NOW(),"ym")&"月"&DATEDIF(V3,NOW(),"md")&"天"
8、合同续签日期(Y列)
=DATE(YEAR(V3)+LEFTB(X3,2),MONTH(V3),DAY(V3))-1
9、合同到期日期(Z列)
=TEXT(EDATE(V3,LEFTB(X3,2)*12)-TODAY(),"[<0]过期0天;[<30]即将到期0天;还早")
10、工龄工资(AA列)
=MIN(700,DATEDIF($V3,NOW(),"y")*50)
11、生肖(AB列)
=MID("猴鸡狗猪鼠牛虎兔龙蛇马羊",MOD(MID(E3,7,4),12)+1,1)
二、员工考勤表公式
1、本月工作日天数(AG列)
=NETWORKDAYS(B$5,DATE(YEAR(N$4),MONTH(N$4)+1,),)
2、调休天数公式(AI列)
=COUNTIF(B9:AE9,"调")
3、扣钱公式(AO列)
婚丧扣10块,病假扣20元,事假扣30元,矿工扣50元
=SUM((B9:AE9={"事";"旷";"病";"丧";"婚"})*{30;50;20;10;10})
四、员工数据分析公式
1、本科学历人数
=COUNTIF(D:D,"本科")
2、办公室本科学历人数
=COUNTIFS(A:A,"办公室",D:D,"本科")
3、30~40岁总人数
=COUNTIFS(F:F,">=30",F:F,"<40")
五、其他公式
1、提成比率计算
=VLOOKUP(B3,$C$12:$E$21,3)
2、个人所得税计算
假如A2中是应税工资,则计算个税公式为:
=5*MAX(A2*{0.6,2,4,5,6,7,9}%-{21,91,251,376,761,1346,3016},)
3、工资条公式
=CHOOSE(MOD(ROW(A3),3)+1,工资数据源!A$1,OFFSET(工资数据源!A$1,INT(ROW(A3)/3),,),"")
注:
A3:标题行的行数+2,如果标题行在第3行,则A3改为A5
工资数据源!A$1:工资表的标题行的第一列位置
4、Countif函数统计身份证号码出错的解决方法
由于Excel中数字只能识别15位内的,在Countif统计时也只会统计前15位,所以很容易出错。不过只需要用 &"*" 转换为文本型即可正确统计。
=Countif(A:A,A2&"*")
六、利用数据透视表完成数据分析
1、各部门人数占比
统计每个部门占总人数的百分比
2、各个年龄段人数和占比
公司员工各个年龄段的人数和占比各是多少呢?
3、各个部门各年龄段占比
分部门统计本部门各个年龄段的占比情况
4、各部门学历统计
各部门大专、本科、硕士和博士各有多少人呢?
5、按年份统计各部门入职人数
每年各部门入职人数情况
附:HR工作中常用分析公式
1.【新进员工比率】=已转正员工数/在职总人数
2.【补充员工比率】=为离职缺口补充的人数/在职总人数
3.【离职率】(主动离职率/淘汰率=离职人数/在职总人数=离职人数/(期初人数+录用人数)×100%
4.【异动率】=异动人数/在职总人数
5.【人事费用率】=(人均人工成本*总人数)/同期销售收入总数
6.【招聘达成率】=(报到人数+待报到人数)/(计划增补人数+临时增补人数)
7.【人员编制管控率】=每月编制人数/在职人数
8.【人员流动率】=(员工进入率+离职率)/2
9.【离职率】=离职人数/((期初人数+期末人数)/2)
10.【员工进入率】=报到人数/期初人数
11.【关键人才流失率】=一定周期内流失的关键人才数/公司关键人才总数
12.【工资增加率】=(本期员工平均工资—上期员工平均工资)/上期员工平均工资
13.【人力资源培训完成率】=周期内人力资源培训次数/计划总次数
14.【部门员工出勤情况】=部门员工出勤人数/部门员工总数
15.【薪酬总量控制的有效性】=一定周期内实际发放的薪酬总额/计划预算总额
16.【人才引进完成率】=一定周期实际引进人才总数/计划引进人才总数
17.【录用比】=录用人数/应聘人数*100%
18.【员工增加率】 =(本期员工数—上期员工数)/上期员工数
【好东西记得分享哦!!!】
【好东西记得分享哦!!!】
【好东西记得分享哦!!!】
- ?
HR常用的Excel函数公式大全(共21个)
仇亚男
展开
一、员工信息表公式
1、计算性别(F列)
=IF(MOD(MID(E3,17,1),2),"男","女")
2、出生年月(G列)
=TEXT(MID(E3,7,8),"0-00-00")
3、年龄公式(H列)
=DATEDIF(G3,TODAY(),"y")
4、退休日期(I列)
=TEXT(EDATE(G3,12*(5*(F3="男")+55)),"yyyy/mm/dd aaaa")
5、籍贯(M列)
=VLOOKUP(LEFT(E3,6)*1,地址库!E:F,2,)
注:附带示例中有地址库代码表
6、社会工龄(T列)
=DATEDIF(S3,NOW(),"y")
7、公司工龄(W列)
=DATEDIF(V3,NOW(),"y")&"年"&DATEDIF(V3,NOW(),"ym")&"月"&DATEDIF(V3,NOW(),"md")&"天"
8、合同续签日期(Y列)
=DATE(YEAR(V3)+LEFTB(X3,2),MONTH(V3),DAY(V3))-1
9、合同到期日期(Z列)
=TEXT(EDATE(V3,LEFTB(X3,2)*12)-TODAY(),"[<0]过期0天;[<30]即将到期0天;还早")
10、工龄工资(AA列)
=MIN(700,DATEDIF($V3,NOW(),"y")*50)
11、生肖(AB列)
=MID("猴鸡狗猪鼠牛虎兔龙蛇马羊",MOD(MID(E3,7,4),12)+1,1)
二、员工考勤表公式
1、本月工作日天数(AG列)
=NETWORKDAYS(B$5,DATE(YEAR(N$4),MONTH(N$4)+1,),)
2、调休天数公式(AI列)
=COUNTIF(B9:AE9,"调")
3、扣钱公式(AO列)
婚丧扣10块,病假扣20元,事假扣30元,矿工扣50元
=SUM((B9:AE9={"事";"旷";"病";"丧";"婚"})*{30;50;20;10;10})
四、员工数据分析公式
1、本科学历人数
=COUNTIF(D:D,"本科")
2、办公室本科学历人数
=COUNTIFS(A:A,"办公室",D:D,"本科")
3、30~40岁总人数
=COUNTIFS(F:F,">=30",F:F,"<40")
五、其他公式
1、提成比率计算
=VLOOKUP(B3,$C$12:$E$21,3)
2、个人所得税计算
假如A2中是应税工资,则计算个税公式为:
=5*MAX(A2*{0.6,2,4,5,6,7,9}%-{21,91,251,376,761,1346,3016},)
3、工资条公式
=CHOOSE(MOD(ROW(A3),3)+1,工资数据源!A$1,OFFSET(工资数据源!A$1,INT(ROW(A3)/3),,),"")
注:
A3:标题行的行数+2,如果标题行在第3行,则A3改为A5
工资数据源!A$1:工资表的标题行的第一列位置
4、Countif函数统计身份证号码出错的解决方法
由于Excel中数字只能识别15位内的,在Countif统计时也只会统计前15位,所以很容易出错。不过只需要用 &"*" 转换为文本型即可正确统计。
=Countif(A:A,A2&"*")
六、利用数据透视表完成数据分析
1、各部门人数占比
统计每个部门占总人数的百分比
2、各个年龄段人数和占比
公司员工各个年龄段的人数和占比各是多少呢?
3、各个部门各年龄段占比
分部门统计本部门各个年龄段的占比情况
4、各部门学历统计
各部门大专、本科、硕士和博士各有多少人呢?
5、按年份统计各部门入职人数
每年各部门入职人数情况
- ?
说说常用的excel函数公式大全有哪些,如何使用?看了你就知道!
Odessa
展开
我们都知道excel函数公式很强大,运用好了对我们制表很有帮助,但是excel函数公式实在是太多了,根本记不住,下面跟大家分享一些常常会用到的函数公式。
一、对于数字的处理:
1、取绝对值
=ABS(数字)
2、取整
=INT(数字)
3、四舍五入
=ROUND(数字,小数位数)
二、统计公式:
1、统计两个表格重复的内容
公式:B2
=COUNTIF(Sheet15!A:A,A2)
说明:如果返回值大于0说明在另一个表中存在,0则不存在。
2、统计不重复的总人数
公式:C2
=SUMPRODUCT(1/COUNTIF(A2:A8,A2:A8))
说明:用COUNTIF统计出每人的出现次数,用1除的方式把出现次数变成分母,然后相加。
在完成excel表格统计之后,以免后期不小心修改数据,我们可以将excel表格转换成pdf格式。高版本的office可以直接将excel另存为pdf,低版本或者想要批量转换的可以用迅捷pdf转换器来完成转换。
三、求和公式
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可以完成多条件求和
好啦,以上就是比较常用的excel公式啦,当然,excel函数公式远远不止这些,但是以上公式如果能熟练应用也会非常提高工作效率哦。
- ?
工作中最常用的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除的方式把出现次数变成分母,然后相加。
四、求和公式
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可以完成多条件求和
五、查找与引用公式
1、单条件查找公式
公式1:C11
=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、多条件查找
公式:详见下图
说明:公式原理同上一个公式
5、指定区域最后一个非空值查找
公式;详见下图
说明:略
6、按数字区域间取对应的值
公式:详见下图
公式说明:VLOOKUP和LOOKUP函数都可以按区间取值,一定要注意,销售量列的数字一定要升序排列。
六、字符串处理公式
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查找结果
七、日期计算公式
1、两日期相隔的年、月、天数计算
A1是开始日期(2011-12-1),B1是结束日期(2013-6-10)。计算:
相隔多少天?=datedif(A1,B1,"d") 结果:557
相隔多少月? =datedif(A1,B1,"m") 结果:18
相隔多少年? =datedif(A1,B1,"Y") 结果:1
不考虑年相隔多少月?=datedif(A1,B1,"Ym") 结果:6
不考虑年相隔多少天?=datedif(A1,B1,"YD") 结果:192
不考虑年月相隔多少天?=datedif(A1,B1,"MD") 结果:9
datedif函数第3个参数说明:
"Y" 时间段中的整年数。
"M" 时间段中的整月数。
"D" 时间段中的天数。
"MD" 天数的差。忽略日期中的月和年。
"YM" 月数的差。忽略日期中的日和年。
"YD" 天数的差。忽略日期中的年。
2、扣除周末天数的工作日天数
公式:C2
=NETWORKDAYS.INTL(IF(B2
说明:返回两个日期之间的所有工作日数,使用参数指示哪些天是周末,以及有多少天是周末。周末和任何指定为假期的日期不被视为工作日