Excel函数的几例应用_excel函数的几例应用

2020-02-26 其他范文下载本文

Excel函数的几例应用由刀豆文库小编整理，希望给你工作、学习、生活带来方便，猜你可能喜欢“excel函数的几例应用”。

Excel函数的几例应用

Excel俗称电子表格，是集表格、计算和数据库为一身的优秀软件，其数据处理的核心是函数和数据库，本文仅介绍用Excel函数进行成绩统计和一些日常数据统计。

一、考试成绩的统计

1、假设A1：A53单元格存放某班学生的数学考试成绩，若要计算该科的总分、人平分、及格人数、及格率、优秀人数、优秀率。

(1)若要将该科的总分存放在A54单元格中，只需选定该单元格并输入公式：“＝SUM(A1：A53)”，回车（也可按“Ctrl+Shift+Enter”组合键）后即可计算出科总分(注：在A54单元格只显示计算结果。如要修改该公式，只需选定该单元格，在编辑栏内即可修改)。

(2)若要将该科人平分存放在A55单元格中，选定该单元格并输入公式：“＝AVERAGE(A1：A53)”，回车后即可计算出人平分(注：数组或单元格引用中的文字、逻辑值或空白单元格将被忽略，但单元格中的“0”则参与运算)。

(3)若要将该科的及格人数存放在A56中，选定该单元格并输入公式“＝COUNTIF(A1：A53，″＞＝60″)”回车，即可计算出该科的及格人数(空白单元格、文字、逻辑值、错误值均被忽略)。

(4)若要将该科的及格率存放在A57中，选定该单元格并输入公式“＝COUNTIF(A1：A53，″＞＝60″)/ COUNTIF(A1：A53)”回车，即可计算出该科的及格率(其中“COUNTIF(A1：A53)”是计算A1：A53单元格中的实际参考人数，空白单元格将被忽略)。(5)若要将该科的优秀人数、优秀率存放在A58、A59中，只需分别输入公式“＝COUNTIF(A1：A53，″＞＝80″)”和“＝COUNTIF(A1：A53，″＞＝80″)/COUNTIF(A1：A53)”回车，即可计算出该科的优秀人数和优秀率。

(6)若在B1：B53，„„，G1：G53区域中存放英语，„„，地理成绩，则只需选定A54：A59区域，移动空心十字光标至单元格右下角。待光标变成小实心十字光标时，按住鼠标左键沿行拖动至G列即可，放开鼠标左键后，则各科的科总分、人平分、及格人数、及格率、优秀人数、优秀率立即计算出来。

2、若A1：G1区域存放某个学生各科的成绩，需在H1中存放该学生的总分，选定该单元格并输入公式“=ＳＵＭ(A1：G1)”回车即可计算出该生各科的总分，按照上面介绍的方法沿列拖动鼠标即可将全班学生各科的总分均可计算出来。若某生未考试，但不希望在其总分单元格填入“0”，只需将公式修改为“＝IF(ISNUMBER(A1：G1)，SUM(A1：G1)，″″)”回车，单元格中会填入空格而不会出现“0”。

3、若要将该班学生的班级排名计算出来，但不希望打乱学生原来按学号的顺序，则可利用RANK函数轻松完成，该函数的最大优点是，可以处理重名次，可使数值相同的单元格计算出的名次相同。如上例在H1：H53区域中存放某班学生的总分，要将班级排名结果放在I1：I53区域中，只需在I1中输入公式“＝RANK(H1，$H$1：$H$53)”回车，即可计算出该生在班级中所排名次，然后沿列拖动复制，即将全班学生的排名计算出来。

如果某些学生未参加考试，又不希望在其存放名次的单元格内显示出错信息(如：#VALUE！)，则可将公式修改为“＝IF(ISNUMBER(H1)，RANK(H1，$H$1:$H$53), ″″)”。

4、成绩等第计算，如学校采用等第评定考试成绩，若标准为：考试分数高于或等于85分为A等；考试分数低于85分且高于或等于70分为B等；考试分数低于70分且高于或等于60分为C等；考试分数低于60分为D等；没有参加考试的不划等第。假设分数存放在B列，计算结果存入C列，则用Excel计算等第的公式如下：“＝IF(B1＞＝85，″A″，IF(B1＞＝70，″B″，IF(B1＞＝60，″C″，IF(ISNUMBER(B1)，″D″，IF(ISBLANK(B1)，″″)))))，这是一个IF函数嵌套公式，式中第二个IF语句是第一个IF语句的参数，第三个IF语句则是第二个IF语句的参数，以此类推。如果第一个逻辑判断表

达式B1＞＝85为TRUE(真)，则C1单元格式被填入“A”；如果为FALSE(假)，则计算第二个IF语句“IF(B1＞＝70”；以此类推直至计算结束。其中ISNUMBER函数在B1为空时返回FALSE(假)，接着执行最后一个IF语句，否则在C1单元格中填入“D”。ISBLANK函数在B1为空时返回TRUE(真)，则在C1单元格中填入一个鉴定会格。使用ISNUMBER函数和ISBLANK函数，可防止某个学生没有参加考试(即考试成绩为空)，但仍然给他评定为D等的情况发生。如需计算其他学生的成绩等第，只需将公式复制即可。

如果成绩等第划分标准发生了变化，只须改变逻辑判断式中的值(85，70，60)即可，也可将A、B、C、D分别换成优秀、良好、及格、不及格。

5、若要将学生成绩按分数段进行统计则可利用FREQUENCY函数，假设B1：B50共有50个学生的数学成绩，那么可在A51：A55单元格分别输入59、69、79、89、100，则表示统计59(包括59)分以下、60—69、70—79、80—89、90—100分数段的成绩个数。

若要将计算结果存入B51：B55，则选中B51：B55单元格，在编辑栏内输入公式“＝FREQUENCY(B1：B50，$A$51：$A$55)”,输完后让光标停在公式末尾，按下SHIFT＋CTRL键敲回车(这是生成数组公式的关键环节)，Excel会自动在公式两侧加上大括号，公式成为“{＝FREQUENCY(B1：B50，$A$51：$A$55)}”,B51:B55单元格中立刻计算出各分数段中成绩的个数。

6、若要查询最高分和最低分，则可利用MAX(MIN)函数。假设C1：C42区域中存放着42名学生的考试成绩，则可选中一个空白单元格，在编辑栏输入公式“＝MAX(C1：C42)”回车后即可计算出其中的最高分，如果将上述公式中的函数名改为MIN，其他都不变，就可以计算出C1：C42区域中的最低分。

二、一些日常生活中的数据统计

1、以最常见的工资表统计为例，它的特点是需要对行内若干个单元格求和，并要自动扣除“水电费”，“税金”等。根据习惯，这些项目并不输负数，这时可以构造如下公式：“＝SUM(A2：C2，—D2，—E2)”。其中A2：C2引用是收入，而D2，E2表示支出。由Excel不允许在单元格引用前面加负号，所以，应在表示支出的单元格前加负号，这样即可计算出正确结果。即使支出数据所在的单元格连续，也必须用逗号将它们逐个隔开。

2、某单位要统计工资报表中工资总额大于等于1000元的人数，男职工的人数和女职工人数。假设工资总额存放在工作表的F列，职工性别放在工作表B列，该单位有300人。则具体计算公式为：统计工资总额大于等于1000元的人数可用公式“＝COUNTIF(F1：F300，″＞＝1000″)”，统计男职工人数可用公式 “＝COUNTIF(B1：B300，″＝男″)”，统计女职工人数可用公式：“＝COUNTIF(B1：B300，″＝女″)”。

又如要统计工资表中职称为中级员工的工资总额。假设工资总额存放在工作表的F列，员工的的职称存放在C列，则计算公式为“＝SUMIF(C1：C300，″中级″，F1：F300)”，其中“C1：C300”为提供逻辑判断的区域，“中级”为判断条件即只统计C1：C300区域中职称为“中级”的单元格，F1：F300为实际求和的区域。

3、评比打分统计。一般比赛采用打分的方法进行评价，为了防止个别人的极端行为，一般计算平均分要去掉若干最高分和最低分。假若B1：B10区域存放某位参赛者的比赛得分，则去掉一个最高分和一个最低分后的平均分计算公式为“＝TRIMMEAN(B1：B10，0.2)”。公式中的0.2表示10个数据中去掉2个(10×0.2)，即一个最高分和一个最低分。若只有7位评委打分，则只需将0.2改成2/7即可。

4、如今贷款买房子或车子的人越来越多，计算某一贷款的月偿还金额是考虑贷款的重要依据，Excel提供的PMT函数是完成这一任务的好工具。其语法格式为：PMT(rate,nper,pv,fv,type)，语法中的rate是贷款利率；nper为贷款偿还期限；pv是贷款本金；fv为最后一次付款后剩余的贷款金额，如果省略，则认为它的值为零；type为0或1，用来

指定付款时间是在月初还是月末，如果省略，则认为其值为0，1为月初，0或忽略为月末。假如你为购房贷款十万元，如果年利率为7%，每月末还款，采用十年还清方式时，月还款额计算公式为“＝PMT(7%/12,120,-100000)”。其结果为-1,161.08，就是你每月须还贷款1161.08元。

5、在日常计算中，经常需要对数据进行四舍五入，这时可利用四舍五入函数ROUND，其语法为：ROUND(number,num-digits)，参数number是需要四舍五入的数字；num-digits为指定的位数，numbe将按此位数进行四舍五入，如果num-digits大于0，则四舍五入到指定的小数位；如果num-digigits等于0，则四舍五入到最接近的整数；如果num-digits小于0，则在小数点左侧按指定位数四舍五入。

假设Excel工作表中D2 = 356.68，E2 = 128.12，需要将D2与E2之和乘以0.1，将计算结果四舍五入取整数后，再将这个结果乘以1.36得到最终结果（保留2位小数）。

一般的做法是选中某个单元格（如F2），使用“单元格格式”，“数字”选项卡的“小数位数”，将F2的小数位数设为零，然后在其中输入公式“F2＝(D2+E2)*0.1”。再将G2单元格的小数位数设成2位，最后在G2中输入“G2＝F2*1.36”。值得注意的是通过上述方法设置的“小数位数”，只能将单元格数值的显示结果四舍五入（显示48），并不能对存放的数值进行四舍五入（实际存放的是48.45），也就是说数值的显示结果与实际存放结果并不完全一致，在上述公式“G2＝F2*1.36”中，单元格F2中实际参与运算的是48.45，而不是48。

要解决这个问题并不难，只需在G2单元格内输入公式“=(ROUND(D2+E2)*0.1,0)*1.36”，就可计算出正确结果65.28。式中ROUND函数按指定位数对“(D2+E2)*0.1”进行四舍五入，函数中的参数0将“(D2+E2)*0.1”四舍五入到最接近的整数。A2=89

A3=85 A4=23 A5=80

=COUNTIF(A2:A5,“>=80”)

统计的学生某科成绩，>=80优（合格）的条件。

完整表达%

=COUNTIF(A2:A5,“>=80”)/COUNT(A2:A5)*100

但有一个问题，如果学生缺考该怎样计分母，因为如果未输入分数（含0），结果将不一样，建议COUNT(A2:A5)可直接输入实际人数符合学校评估要求！

其实有一数组FREQUENCY更方便！

三、EXCEL画斑马线

一、行线

选中需要用斑马线的区域，打开选项中的“条件格式”输入“=MOD(ROW(),2)=0”，在“格式”中选中需要填充的颜色或样式，确定。注：=MOD(ROW(),2)=0 为画偶数线；注：=MOD(ROW(),2)=1 为画奇数线；

一、列线