excel表格怎么求等级

篇一：在Excel中,用函数公式自动生成某等级和等级的个数

在Excel中，用函数公式自动生成等级和符合条件的等级的总个数赵化中学 郑宗平

以后面表一、表二为例，给学生1至学生24的总分（J3:J26）区域评定等级；若评价的标准是：未达标（x＜235分），合格（235≤x＜245分），良好（245≤x＜260分），优秀（x≥260分）.

1、自动生成相应的评定等级.在评价部分的单元格I3键入函数公式(见后面表一茶色单元格以及公式栏)：=IF(H3<235,"未达标",IF(H3<245,"合格",IF(H3<260,"良好",IF(H3<=300,"优秀",""))))→敲回车即可评定出H3等级（优秀），后面学生2至学生26的总分（I4:I26）区域的等级可以在选定I3单元格后拖移或双击生成.

2、自动生成某等级的个数.如在评价部分的单元格I27键入函数公式(见后面表二青绿色单元格以及公式栏)：=COUNTIF(I3:I26,"未达标")&"人（未达标)"→敲回车后可以自动生成“未达标”的个数， "人（未达标)"是标明未达标这个等级名称的公式. 以上诸方法可以类推，拓展！

如用字母表示等级：=IF(H3<235,"C",IF(H3<245,"B",IF(H3<260,"B+",IF(H3<=300,"A",""))))就会自动生成字母表示的相应的等级；或用 =CONCATENATE(IF(H3>=260,"A",( IF(H3>=245,"B+",IF(H3>=235,"B","C")))))或用 =sumproduct((a1:a10="＊＊＊")*(b1:b10="A等级")). ??

合格及以上人数=COUNTIF(I3:I26,"合格")+COUNTIF(I3:I26,"良好")+COUNTIF(I3:I26,"优秀")&"人（合格）"或=COUNT(I3:I26-COUNTIF(I3:I26,"未达标")&"人（合格)" . 公式输入并不复杂，“+”后面可采用复制粘贴的办法，然后简单修改. ??

问题1：在Excel中，如何使学生的成绩自动生成某等级以及某等级的个数？

问题2：怎样同时给多科评定等级并计算符合不同条件的等级的总个数？

1、在一个单元格内用函数公式同时生成多科的评定等级. 若规定每科满足x＜60分评定为“差”，60≤x＜80分评定为“良”，x≥80分评定为“优”，当然也可以设置为字母等级.在H3单元格键入：=CONCATENATE(IF(E3>=80,"优",IF(E3>=60,"良","差")),IF(F3>=80,"优",IF(F3>=60,"良","差")),IF(G3>=80,"优",IF(G3>=60,"良","差")))（见后面表三）

2、计算符合不同条件的等级的总个数.

例如：计算良好及以上的人次，在H30单元格键入（下面的任意公式即可）：

公式1：=COUNT(E3:G29)-COUNTIF(E3:G28,"<60")&"人次（良好及以上）"（见后面表四）公式2：=COUNTIF(E3:G29">=70")&"人次（良好及以上）"依次类推!此公式比较简捷.

公式3：=COUNTIF(e3:g29,">=80")+ (COUNTIF(e3:g29,">=60") -COUNTIF(e3:g29,">=80"))&"人次（良好及以上）".

公式4：把各科等级评定分别放在三列(如表五的H3,I3,J3)中，用前面的办法生成等级后，且把同一行的三个单元格内部设置“无”，仿佛是一列单元格，实际上是三列单元格，可以“以假乱真”；在放置符合等级条件的总个数的单元格键入公式，如：=COUNTIF(h3:j29,"优")+ COUNTIF(h3:j29,"良")&"人次" →确定即可生成符合条件的总个数→提行单元格输入（良好及以上)→两单元格内部线条设置为“无”.（见后面表五） 附：表一至表五

在excel中，用函数公式生成等级和等级个数

1

表 一

2

表 二

在excel中，用函数公式生成等级和等级个数

表 三

表 四

表 五

2014年6月1日星期日

在excel中，用函数公式生成等级和等级个数 3

篇二：Excel表格自动计算技巧

Excel表格自动计算技巧

一、显示单元格例有计算式的结果的设置方法

首先：插入-名称-定义在弹出的对话框“当前工作薄的名称”中输入: X或“结果”的自定义名称，再在“引用位置”处粘贴＝EVALUATE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(计算!$C$1,"[","("),"]",")"),"×","*"),"÷","/")) 公式(注

意要有“＝”号。再对公式中“计算!$C$1”选择上，然后再到需要做公式的单元格中点击即可。如要相对引用，则要删除$字符。 已经OK，你在 C1输入表达式 比如 15+5×3 ，在D1中输入 =x 看看(应该是30)。

二、如何在Excel中输入计算式后另一单元显示计算结果 菜单--插入--名称--定义：输入AA(任意取名)，在下面输入公式: =EVALUATE($A$1)

然后在B1单元格输入公式：=AA

但本式不能识别如：[ 、×、÷ 等符号进行计算，使用第一种较好。

三、如何在Excel中编写自定义函数，象在表格中调用SUM()一样？ 第一种情况：

单元格 A1 = 2；单元格 B1 = 2；单元格 C1 = 2

单元格 D1=(A1+B1)×C1 显示结果为 8 ；

那么如何才能在单元格 E1中显示(2+2)×2的计算表达式，并且建立关联，当单元格D1变成 =(A1+BA)^C1 计算式后，显示结果为16；那么E1也就自动显示为 (2+2)^2的计算表达式，也就是说随着单元格D1的计算公式变化，单元格E1显示的计算表达式也随之变化。

第二种情况：

是当计算的单元格任意变化时，怎么办？比如说计算式E1=A1+B1+C1+D1，也有D2=A2+B2+C2，还有F3=A1+B1+C1+D1+E1时。如何将计算式变为计算表达式。即计算式可以在任意一个单元格，计算公式所引用数据的单元格可以任意变化，需要显示计算表达式的单元格也是任意的。 针对第一种问题，分两步做：

1，自定义一个函数

Public Function K(x As Range)

K = Right(x.Formula, Len(x.Formula) - 1)

End Function

自定义函数操作过程如下：

工具－宏－Visual Basic编辑器－右击－插入模块－再将上面的函数粘贴进去即可。

2，在E1中写入公式

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(k(D1),"A"&ROW(A1),A1),"B"&ROW(B1),B1),"C"&ROW(C1),C1)

其它往下拖拉即可。I当需要增加几个参数应用的单元格时，相应要增加几个：SUBSTITUTE( ，再相应增加几个如：,"F"&ROW(F1),F1) 。

工具--》宏--》安全性里面选择无（不建议使用），这样加载宏的时候系统不会每次都提示。

**自定义函数也可以换成使用宏表函数 GET.CELL()

第二个问题，请同理照推

四、 编辑技巧

1 分数的输入

如果直接输入“1/5”，系统会将其变为“1月5日”，解决办法是：先输入“0”，然后输入空格，再输入分数“1/5”。

2 序列“001”的输入

如果直接输入“001”，系统会自动判断001为数据1，解决办法是：首先输入“'”（西文单引号），然后输入“001”。

3 日期的输入

如果要输入“4月5日”，直接输入“4/5”，再敲回车就行了。如果要输入当前日期，按一下“Ctrl+；”键。

在Excel中填写表格时,怎样在一格中换行填字?

输入一行后按Alt+Enter就可以换行

5 自动切换输入法

在一张工作表中，往往是既有数据，又有文字，这样在输入时就需要来回在中英文之间反复切换输入法，非常麻烦。 如果你要输入的东西很有规律性，比如这一列全是单词，下一列全是汉语解释，你可以用以下方法实现自动切换。方法是：

（1）选中要输入英文的列，单击“数据”菜单，选择“有效性...”命令，在弹出的“数据有效性”对话框中，选中“输入法模式”选项卡，在“模式”框中选择“关闭（英文模式）”命令，单击“确定”按钮（如图12）。

（2）选中要输入汉字的列，在“有效数据”对话框中，单击“IME模式”选项卡，在“模式”框中选择“打开”命令，单击“确定”按钮。这样，当光标在前一列时，可以输入英文，在下一列时，直接可以输入中文，从而实现了中英文输入方式之间的自动切换。

6、把数据彻底隐藏起来

工作表部分单元格中的内容不想让浏览者查阅，只好将它隐藏起来了。

1.选中需要隐藏内容的单元格(区域)，执行“格式→单元格”命令，打开“单元格格式”对话框，在“数字”标签的“分类”下面选中“自定义”选项，然后在右边“类型”下面的方框中输入“；；；”(三个英文状态下的分号)。

2.再切换到“保护”标签下，选中其中的“隐藏”选项，按“确定”按钮退出。

3.执行“工具→保护→保护工作表”命令，打开“保护工作表”对话框，设置好密码后，“确定”返回。

经过这样的设置以后，上述单元格中的内容不再显示出来，就是使用Excel的透明功能也不能让其现形。

提示：在“保护”标签下，请不要清除“锁定”前面复选框中的“∨”号，这样可以防止别人删除你隐藏起来的数据。

7、在Excel中自定义函数

Excel函数虽然丰富，但并不能满足我们的所有需要。我们可以自定义一个函数，来完成一些特定的运算。下面，我们就来自定义一个计算梯形面积的函数：

1.执行“工具→宏→Visual Basic编辑器”菜单命令(或按“Alt+F11”快捷键)，打开Visual Basic编辑窗口。

2.在窗口中，执行“插入→模块”菜单命令，插入一个新的模块——模块1。

3.在右边的“代码窗口”中输入以下代码：

Function V(a,b,h)V = h*(a+b)/2End Function

4.关闭窗口，自定义函数完成。

以后可以像使用内置函数一样使用自定义函数。

提示：用上面方法自定义的函数通常只能在相应的工作簿中使用。

8、单元格内容的合并

解决办法是：在C行后插入一个空列（如果D列没有内容，就直接在D列操作），在Ｄ1中输入“=B1&C1”，D1列的内容就是B、C两列的和了。选中D1单元格，用鼠标指向单元格右下角的小方块“■”，当光标变成"＋"后，按住鼠标拖动光标向下拖到要合并的结尾行处，就完成了B列和C列的合并。这时先不要忙着把B列和C列删除，先要把D列的结果复制一下，再用“选择性粘贴”命令，将数据粘贴到一个空列上。这时再删掉B、C、D列的数据。

下面是一个“&”实际应用的例子。用AutoCAD绘图时，有人喜欢在EXCEL中存储坐标点，在绘制曲线时调用这些参数。存放数据格式为“x，y”的形式，首先在Excel中输入坐标值，将x坐标值放入A列，y坐标值放入到B列，然后利用“&”将A列和B列合并成C列，在C1中输入：=A1&"，"&B1，此时C1中的数据形式就符合要求了，再用鼠标向下拖动C1单元格，完成对A列和B列的所有内容的合并（如图3-4）。

合并不同单元格的内容，还有一种方法是利用CONCATENATE函数，此函数的作用是将若干文字串合并到一个字串中，具体操作为“=CONCATENATE（B1,C1）”。比如，假设在某一河流生态调查工作表中，B2包含“物种”、B3包含“河鳟鱼”，B7包含总数45，那么： 输入“=CONCATENATE（"本次河流生态调查结果："，B2，""，B3，"为"，B7，"条/公里。"）” 计算结果为：本次河流生态调查结果：河鳟鱼物种为45条/公里。

9、条件显示

我们知道，利用If函数，可以实现按照条件显示。一个常用的例子，就是教师在统计学生成绩时，希望输入60以下的分数时，能显示为“不及格”；输入60以上的分数时，显示为“及格"。这样的效果，利用IF函数可以很方便地实现。 假设成绩在A2单元格中，判断结果在A3单元格中。那么在A3单元格中输入公式： =if（A2<60，“不及格”，“及格”） 同时，在IF函数中还可以嵌套IF函数或其它函数。

例如，如果输入： =if（A2<60，“不及格”，if（A2<=90，“及格”，“优秀"）） 就把成绩分成了三个等级。

如果输入 =if（A2<60，“差"，if（A2<=70，“中”，if（A2<90，“良”，“优”））） 就把成绩分为了四个等级。

再比如，公式： =if（SUM（A1：A5>0，SUM（A1：A5），0） 此式就利用了嵌套函数，意思是，当A1至A5的和大于0时，返回这个值，如果小于0，那么就返回0。 还有一点要提醒你注意：以上的符号均为半角，而且IF与括号之间也不能有空格。

创建条件格式

可以使用六种逻辑符号来设计一个条件格式：>（大于）、>=（大于等于）、<（小于）、<=（小于等于）、=（等于）、<>（不等于），如果你觉得这些符号不好记，就干脆使用“>”或“>=”号来表示。

11、在Ｅxcel中小数点无法输入，按小数点，显示的却是逗号，无论怎样设置选项都无济于事，该怎么办？

这是一个比较特殊的问题，我曾为此花了十几个小时的时间，但说白了很简单。在Ｗindows的控制面板中，点击“区域设置”图标，在弹出的“区域设置属性”对话面板上在“区域设置”里选择“中文（中国）”，在“区域设置属性”对话面板上在“数字”属性里把小数点改为“.”（未改前是“，”），按“确定”按钮结束。这样再打开Excel就一切都正常了。

12、如何快速地将数字作为文本输入？

在输入数字前加一个单引号“”’，可以强制地将数字作为文本输入。

13、如何对工作簿进行安全保护？

如果你不想别人打开或修改你的工作簿，那么想法加个密码吧。打开工作薄，选择“文件”菜单中的“另存为”命令，选取“选项”，根据用户的需要分别输入“打开文件口令”或“修改文件D令”，按“确定”退出。工作簿（表）被保护之后，还可对工作表中某些单元格区域的重要数据进行保护，起到双重保护的功能，此时你可以这样做：首先，选定需保护的单元格区域，选取“格式”菜单中的“单元格”命令，选取“保护”，从对话框中选取“锁定”，单由“确定”按钮退出。然后选取“工具”菜单中的“保护”命令，选取“保护工作表”，根据提示两次输入口令后退出。

注意：不要忘记你设置有“口令”。

14、工作表保护的口令忘记了怎么办？

如果你想使用一个保护了的工作表，但口令又忘记了，有办法吗？有。选定工作表，选择“编辑”＼“复制”、“粘贴”，将其拷贝到一个新的工作薄中（注

篇三：用EXCEL统计各分数段人数

用EXCEL统计各分数段人数

前面我们介绍了Excel常用函数的功能和使用方法 ，现在我们学以致用，介绍一系列用这些函数实现的数据统计实例解析。今天介绍教师最常用的各学科相应分数段学生人数的统计。

教师常常要统计各学科相应分数段的学生人数，以方便对考试情况作全方位的对比分析。在Excel中，有多种函数可以实现这种统计工作，笔者以图1所示的成绩表为例，给出多种统计方法。文章末尾提供.xls文件供大家下载参考。

文章导读：

方法一：用COUNTIF函数统计

这是最常用、最容易理解的一种方法，我们用它来统计“语文”学科各分数段学生数。

如果某些学科（如体育），其成绩是不具体数值，而是字符等级（如“优秀、良好”等），我们也可以用COUNTIF函数来统计各等级的学生人数。

方法二：用DCOUNT函数统计

这个函数不太常用，但用来统计分数段学生数效果很不错。我们用它统计“数学”学科各分数段学生数。 方法三：用FREQUENCY函数统计

这是一个专门用于统计某个区域中数据的频率分布函数，我们用它来统计“英语”学科各分数段学生数。 方法四：用SUM函数统计

我们知道SUM函数通常是用来求和的，其实，他也可以用来进行多条件计数，我们用它来统计“政治”学科各分数段的学生数。

（图1 图片较大，请拉动滚动条观看）

方法一：用COUNTIF函数统计

这是最常用、最容易理解的一种方法，我们用它来统计“语文”学科各分数段学生数。函数功能及用法介绍

①分别选中C63、C67单元格，输入公式：=COUNTIF(C3:C62,"<60")和=COUNTIF(C3:C62,">=90")，即可统计出“语文”成绩“低于60分”和“大于等于90”的学生人数。

②分别选中C64、C65和C66单元格，输入公式：

=COUNTIF(C3:C62,">=60")-COUNTIF(C3:C62,">=70")、

=COUNTIF(C3:C62,">=70")-COUNTIF(C3:C62,">=80")和

=COUNTIF(C3:C62,">=80")-COUNTIF(C3:C62,">=90")，即可统计出成绩在60-69分、70-79分、80-89分区间段的学生人数。

注意：同时选中C63至C67单元格，将鼠标移至C67单元格右下角，成细十字线状时，按住左键向右拖拉至I列，就可以统计出其它学科各分数段的学生数。

如果某些学科（如体育），其成绩是不具体数值，而是字符等级（如“优秀、良好”等），我们可以用COUNTIF函数来统计各等级的学生人数

如果某些学科（如体育），其成绩是不具体数值，而是字符等级（如“优秀、良好”等），我们可以用COUNTIF函数来统计各等级的学生人数。

①在K64至K67单元格中，分别输入成绩等级字符（参见图2）。

②选中L64单元格，输入公式：=COUNTIF($L$3:$L$62,K64)，统计出“优秀”的学生人数。

③再次选中L64单元格，用“填充柄”将上述公式复制到L65至L67单元格中，统计出其它等级的学生人数。

上述全部统计结果参见图1。

（图片较大，请拉动滚动条观看）

方法二：用DCOUNT函数统计

这个函数不太常用，但用来统计分数段学生数效果很不错。我们用它统计“数学”学科各分数段学生数。

①分别选中M63至N72单元格区域（不一定非得不这个区域），输入学科名称（与统计学科名称一致，如“数学”等）及相应的分数段（如图2）。

②分别选中D63、D64……D67单元格，输入公式：=DCOUNT(D2:D62,"数学",M63:N64)、

=DCOUNT(D2:D62,"数学",M65:N66)、=DCOUNT(D2:D62,"数学",M67:N68)、=DCOUNT(D2:D62,"数学",M69:N70)、=DCOUNT($D$2:$D$62,"数学",M71:N72)，确认即可。

注意：将上述公式中的“DCOUNT”函数换成“DCOUNTA”函数，同样可以实现各分数段学生人数的统计。

方法三：用FREQUENCY函数统计

这是一个专门用于统计某个区域中数据的频率分布函数，我们用它来统计“英语”学科各分数段学生数。 函数功能及用法介绍

①分别选中O64至O67单元格，输入分数段的分隔数值（参见图2）。

②同时选中E63至E67单元格区域，在“编辑栏”中输入公式：=FREQUENCY(E3:E62,$O$64:$O$67)，输入完成后，按下“Ctrl+Shift+Enter”组合键进行确认，即可一次性统计出“英语”学科各分数段的学生人数。 注意：①实际上此处输入的是一个数组公式，数组公式输入完成后，不能按“Enter”键进行确认，而是要按“Ctrl+Shift+Enter”组合键进行确认。确认完成后，在公式两端出现一个数组公式的标志“{}”（该标志不能用键盘直接输入）。②数组公式也支持用“填充柄”拖拉填充：同时选中E63至E67单元格区域，将鼠标移至E67单元格右下角，成细十字线状时，按住左键向右拖拉，就可以统计出其它学科各分数段的学生数。 方法四：用SUM函数统计

我们知道SUM函数通常是用来求和的，其实，他也可以用来进行多条件计数，我们用它来统计“政治”学科各分数段的学生数。 函数功能及用法介绍

①分别选中P64至P69单元格，输入分数段的分隔数值（参见图2）。

②选中F63单元格，输入公式：=SUM(($F$3:$F$62>=P64)*($F$3:$F$62<P65))，输入完成后，按下“CTRL+SHIFT+ENTER”组合键进行确认，统计出成绩在0-59区间的学生人数。

③再次选中F63单元格，用“填充柄”将上述公式复制到F64至F67单元格中，统计出其它各分数段的学生人数。

注意：用此法统计时，可以不引用单元格，而直接采用分数值。例如，在F64单元格中输入公式：=SUM(($F$3:$F$62>=60)*($F$3:$F$62<70))，也可以统计出成绩在60-69分之间的学生人数。

注意：①为了表格整体的美观，我们将M至P列隐藏起来：同时选中M至P列，右击鼠标，在随后出现的快捷菜单中，选“隐藏”选项。