office有三宝,Word,Excel和PPT。office 现在已经是办公室人士必会的技能,不会你都不好意思去面试好伐! 而office这三宝,当属Excel这个小婊砸最难搞,不过没关系,今天示尚君就帮大家一起来搞定这个小婊砸。 来,下边示尚君就来给大家普及一下办公室小宝贝们最常用的几个公式: 1.简单入门级 1.求和公式(求所选区域数字之和): =sum(B1:B12)——求B1到B12区域内数字之和。 2.平均数公式(求所选区域数字的平均值): =AVERAGE(B1:B12)——求B1到B12区域内数字的平均值。 3.排名公式(对区域内数字大小进行排名): =RANK(B1:B12)——将B1到B12区域内数字依数值大小进行排名。 4.等级公式(对所选数值按等值区分): =IF(B2>=85,'优',IF(B2>=75,'良',IF(B2>=60,'及格','不及格')))——自动为所选数值判定等级。 5.最高值公式(选出所选区域内数值的最大值): =MAX(B2:B12)——求B1到B12区域内数字的最大值。 6.最小值公式(选出所选区域内数值的最小值): =MIN(B2:B12)——求B1到B12区域内数字的最小值。 7.标准差公式(求所选区域数字的标准差): =STDEV(B2:B12)——求所选区域的数值波动情况,得出数字越小,说明数值波动越小。 8.条件求和公式(求一定条件下,所选区域符合该条件的数值之和): =SUMIF(B2:B12,'男',K2:K12)——比如B列存放的是性别,K列存放的是工资,则该公式求男职工的工资之和。 9.统计公式: =COUNT(B2:B12)——统计所选区域B2:B12的值的个数。 10.统计不重复的总数公式: =SUMPRODUCT(1/COUNTIF(A2:A8,A2:A8))——用COUNTIF统计出每人的出现次数,用1除的方式把出现次数变成分母,然后相加。 2.进阶级别 1.多条件求和公式(求多条件下,所选区域符合所有条件的数值之和): {=SUM(IF(C3:C32='男',IF(G3:G32=1,1,0))) }——假设C列存放职工性别,G列存放职工所在部门代码(1,2,3,4,5),则此函数返回的结果是求“1“部门的男职工人数;另外值得注意的是,这是个数组函数,输完后要按crtl+shift+enter 组合键产生{ },记住,”{ }”不能手工输入,只能由组合键产生。 2.用出生年月来计算年龄公式: =TRUNC((DAYS360(H6,'2016/5/5',FALSE))/360,0)——比如此公式H6为某职工的出生年月,2016/5/5为当前日期,求该职工从出生到目前为止的实际年龄。 3.用身份证号求出生年月日公式: =CONCATENATE(MID(E2,7,4),'/',MID(E2,11,2),'/',MID(E2,13,2))——比如此公式其中E2为某职工身份证号所在单元格,即可得出该职工的出生年月日。 4.身份证提取性别公式: =IF(LEN(C2)=15,IF(MOD(MID(C2,15,1),2)=1,'男','女'),IF(MOD(MID(C2,17,1),2)=1,'男”,'女'))——比如此公式其中C2为某职工身份证号所在单元格,即可得出该职工的性别。 5.扣除周末天数的工作日天数: =NETWORKDAYS.INTL(IF(B2<>——返回两个日期之间的所有工作日数,使用参数指示哪些天是周末,以及有多少天是周末,周末和任何指定为假期的日期不被视为工作日。 3.难度较高级别 1.用公式批量处理按行排序: 熟悉Excel表格的盆友都知道,由于Excel表格的排序功能无论是按列排序或者按行排序,都只能有一个主关键字,因此当多行之间主关键字不相同时,则不能采用排序功能,而采用公式: =LARGE($A1:$E1,COLUMN(A1))——假定你的数据在A至E列,请在F1单元格输入此式,则你原有数据将按行从大到小排序出现在F至J列。也可复制到原有数据外的其他地方。 若想从小到大进行排序,则可改成: =SMALL($A1:$E1,COLUMN(A1)) 2.用公式判断单元格里是否包含指定文本: 以下任一公式均可: =IF(COUNTIF(A1'*'&'指定文本','有','无') 或者 =IF(ISERROR(FIND('指定文本',A1,1)),'无','有') 利用这两个公式即可判断选定的单元格里是否含有指定文本(此公式假设这个单元格是A1)。 3.用公式为多个工作表生成一个目录工作表: 共有两步:1,用宏3.0取出各工作表的名称,方法如下:Ctrl+F3出现自定义名称对话框,取名为X,在“引用位置'框中输入公式: =MID(GET.WORKBOOK(1))+1,100) 确定 2,用HYPERLINK函数批量插入连接,方法如下:在目录工作表的A2单元格输入公式: =HYPERLINK('#'&INDEX(X,ROW())&'!A',INDEX(X,ROW())) 将公式向下填充,直到出错为止,目录就生成了。 以上就是示尚君为各位整理的办公室实用Excel公式,有些盆友看到比较复杂的公式就不想学了,这种心态可是不可取的哦,这种公式对于数据较多较复杂的表格来说可是很有用的,不想再做办公室小白,就把这些公式get起来吧! |
|