Excel公式的常用方法

培训课程

学历教育

不及格显红色
拖拉选择J2:J70，然后执行"格式"菜单下"条件格式"命令，弹出"条件格式对话框"。把条件设为小于60分的用红色显示，点击"格式"按钮，把颜色设为红色。再按"确定"按钮。
1、平均分（平均数）：AVERAGE
公式说明（结果）
=AVERAGE(A2:A56) ，拖动填充柄填充，便得到了每人的平均分。
=AVERAGE(A2:A6, 5) 计算所选数字与 5 的平均值］
=AVERAGE(A2:A56,H8:H56)
去掉几个最大值和几个最小值后的平均值
如50、46、48、46、46、52、52、40、40、42
去掉这组数据组中较大的3个数值和去掉这组数据组中3个数值较小的数值后的平均值？
公式
=(SUM(A2:A56)-(LARGE(A2:A56,1)+LARGE(A2:A56,2)+LARGE(A2:A56,3))-(SMALL(A2:A56,1)+SMALL(A2:A56,2)+SMALL(A2:A56,3)))/(COUNT(A2:A56)-6)
2、难度
（1）难度指试卷(题)的难易程度。一般用试卷(题)的得分率或答对率表示。一般用字母P表示，P越大表示试题越简单，P越小表示试题越难。难度值在0至1之间，一般在0.3~0.9之间。P>0.8试题太易；P<0.2时，试题太难。一份试卷应该由不同难度按一定比例组成。一般地说，P>0.8 、P<0.2的试题各占10%；P=0.2～0.4，和Ｐ＝0.6～0.８的试题各占20％；P>0.4、Ｐ<0.6的中等难度试题应占60%。整套试卷平均难度在0.4～0.6之间。
（2）计算公式:
P=平均分/满分值（或P=答对人数/总人数）
P=AVERAGE(A2:A56) /满分值
3、区分度
区分度是指试题对不同考生的知识、能力水平的鉴别程度。或是指试题对被试者情况的分辨能力的大小。如果一个题目的测试结果使水平高的考生答对(得高分)，而水平较低的考生答错(得低分)，它的区分能力就很强。题目的区分度反映了试题这种区分能力的高低。一般在 -1～+1之间，值越大区分度越好。试题的区分度在0.4以上表明此题的区分度很好，0.3 ～ 0.39表明此题的区分度较好，0.2 ～ 0.29表明此题的区分度不太好需修改，0.19以下表明此题的区分度不好应淘汰。
（1）先将分数排序，P1=27﹪高分组的难度，P2= 27﹪低分组的难度
区分度D =P1－ P2
或区分度 D = （27﹪高分组的平均分－ 27﹪低分组的平均分）÷满分值
以抽样55人算，27%=15人
P=(SUM(LARGE(A2:A56,1)+LARGE(A2:A56,2)+LARGE(A2:A56,3)+

LARGE(A2:A56,4)+LARGE(A2:A56,5)+LARGE(A2:A56,6)+LARGE

(A2:A56,7)+LARGE(A2:A56,8)+LARGE(A2:A56,9)+LARGE(A2:A56,10)+

LARGE(A2:A56,11)+LARGE(A2:A56,12)+LARGE(A2:A56,13)+LARGE

(A2:A56,14)+LARGE(A2:A56,15))-(SMALL(A2:A56,1)+SMALL(A2:A56,2)+SMALL(A2:A56,2)+SMALL(A2:A56,4)+

SMALL(A2:A56,5)+SMALL(A2:A56,6)+SMALL(A2:A56,7)+SMALL(A2:A56,8)+

SMALL(A2:A56,9)+SMALL(A2:A56,10)+SMALL(A2:A56,11)+SMALL(A2:A56,12)+

SMALL(A2:A56,13)+SMALL(A2:A56,14)+SMALL(A2:A56,15)))/（27%学生数×满分值）
4、标准差(用excrl直接求)
标准差是各数据偏离平均数的距离（离均差）的平均数，它是离差平方和平均后的方根。
公式说明（结果）=STDEV(A2:A56)
5、前3名分数总和
=(SUM((LARGE(A2:A56,1)+LARGE(A2:A56,2)+LARGE(A2:A56,3))))
6、后3名分数总和
=(SUM(SMALL(A2:A56,1)+SMALL(A2:A56,2)+SMALL(A2:A56,3)))
7、学生数（单元格数）：COUNT
公式说明（结果）
=COUNT(A2:A8) 所选列数据中包含数字的单元格的个数
=COUNT(A2:A8,2) 所选列数据中包含数字的单元格以及包含数值2的单元
8、及格人数：输入=COUNTIF(A2:A56,">=60") 回车确认即可。
计算A2到A56大于等于60分的人数
9、及格率：
输入=COUNTIF(A2:A56,">=60")/COUNT(A2:A56)   回车确认即可。
或=COUNTIF(A2:A56,">=60")/学生数
计算A2到A56大于等于60分的人数占百分比
10、优秀率：
=COUNTIF(A2:A56,">=80")/COUNT(A2:A56)   回车确认即可。
或=COUNTIF(A2:A56,">=60")/学生数
11、总分：SUM
公式说明（结果）
=SUM(3, 2) 将 3 和 2 相加
=SUM(A2:A4) 将所选列数据相加
=SUM(A2:A4, 15) 将所选列数据之和与 15 相加
=SUM(C8:C52,H8:H52)
12、最大值（最高分）：MAX
公式说明（结果）
=MAX(A2:A6) 所选列数字中的最大值
=MAX(A2:A6, 30) 所选列数字和 30 之中的最大值
=MAX(IF(A1:A10<>0,A1:A10)) 不等于0的最大值
13、最小值（最低分）：MIN
=MIN(A2:A6) 上面一组数字中的最小值 (27)
=MIN(A2:A6, 30) 上面一组数字和 30 之中的最小值
=MIN(IF(A1:A10<>0,A1:A10)) 不等于0的最小值
14、排出名次
输入公式=RANK(J2,J$2:J$70,0),然后拖动填充柄向下填充，即可得到每人在班中的名次。
15、用图表表示
为了让别人对各科的分数段有一个较直观的认识，可以考虑采用图表。单击“插入”菜单中“图表”命令，弹出“图表向导”对话框，在“图表类型”列表框中选择一种图型，如“饼图”，单击“下一步”，单击“数据区域”文本框右边的压缩列表框，拖拉选择B78：C82，再次点击该压缩列表框；单击“下一步”，输入图表标题，如“高一（1）班语文成绩分析图”；单击“下一步”，再单击“完成”。
16、分数段人数
考试成绩录入后，当前工作表的状态栏中已经显示了平均值(即平均分)、计数、求和这三个数值，右击状态栏可以从快捷菜单中选择最小值(最低分)、最大值(最高分)，
分数段人数
（1）60分以下人数：在C78单元格处输入公式=COUNTIF(C2:C77,"<36")，拖动填充柄向右填充至I78单元格处；
（2）60分～69分人数：
在C79单元格处输入公式=COUNTIF(C2:C77,">=60")-COUNTIF(C2:C77,">=70")，拖动填充柄向右填充；
（3）70分～79分人数：
在C80单元格处输入公式=COUNTIF(C2:C77,">=70")-COUNTIF(C2:C77,">=80")，拖动填充柄向右填充；
（4）80分~89分人数：
在C81单元格处输入公式=COUNTIF(C2:C77,">=80")-COUNTIF(C2:C77,">=90")，拖动填充柄向右填充；
（5）90分以上人数：在C82单元格处输入公式=COUNTIF(C2:C77,">=90")，拖动填充柄向右填充；
下面介绍获取成绩分布频数的二种方法：
(一)利用FREQUENCY函数
公式=FREQUENCY(A2:A56,B2:B6)     （56列，55列数据）
第1步：插入列
在右边空白位置插入区“间分割点、人数、百分比”这三列。
第2步：设置区间分割点
区间分割点的设置是极为重要的一个步骤，这里以应该参考"分数段"进行设置，例如"29"表示考试成绩小于或等于29分的分数个数，"39"表示考试成绩介于30～39分的分数个数，其余的依次类推。
第3步：输入数组公式
选择准备放置各分数段人数的单元格区域，这里选择"B2:B7"，选择结束后按下F2键，输入公式"=FREQUENCY(A2:A56,B2:B6)"。按下"Ctrl+Shift+Enter"组合键进行确认。
第4步：计算百分比
选中D2单元格，输入"＝C2/55"，将单元格数值设置为百分比格式，选中D2单元格，回车，即可得到百分比，再拖曳右下角的填充柄即可得到各个区间学生数的百分比。
(二)利用COUNTIF函数
第1步：插入列、设置区间分割点
首先，在右边空白位置插入区“间分割点、人数、百分比”这三列。设置区间分割点，这里的"59.9"表示考试成绩为满分60的学生数，"49.9"表示考试成绩在50～59之间的学生数，其余的依次类推。
第2步：计算满分的学生数
选中C2单元格，在这里输入公式"=COUNTIF(A$2:A$56,">"&$B2)"，这里的"A$2:A$56"表示需要计数的单元格区域，而"">"&$B2"则表示大于59.9分，
第3步：计算90～99分数区间段的学生数
选中C3单元格，在这里输入公式
"=COUNTIF(A$2:A$56,">"&$B3)-COUNTIF(A$2:A$56,">"&$B2)"，得到处于50～99分数区间段的学生人数。