|
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起来吧！ |
|
|
