Excel百科

专业的excel知识必备博客

当前位置:首页 / Excel公式 / 史上最实用的excel表格公式,你知道多少?

史上最实用的excel表格公式,你知道多少?

栏目:Excel公式   日期:2020-04-03   浏览量:579
摘要:

如果能够掌握 MS Excel表格公式,定能事半功倍,更灵活运用你的 MS Excel 资料数据库。

史上最实用的excel表格公式,你知道多少?在Excel表格中,我们常常会利用Excel公式来统计一些报表或数据等,这时就少不了要用到加、减、乘、除法。那么我们又如何利用公式来对一些数据进行乘法计算呢?怎样快速而又方便的来算出结果呢?下面一起来看看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()))

1 条评论

微信扫一扫,分享到朋友圈

史上最实用的excel表格公式,你知道多少?

1 条评论

一位WordPress评论者

嗨,这是一条评论。
要开始审核、编辑及删除评论,请访问仪表盘的“评论”页面。
评论者头像来自Gravatar

发表评论

Excel百科微信:pbi365
Copright © 2020 Excel百科 All Rights Reserved. 粤ICP备13050212号 站点地图