在Excel中,用函数公式自动生成某等级和等级的个数.docVIP

在Excel中，用函数公式自动生成等级和符合条件的等级的总个数 赵化中学 郑宗平 问题1：在Excel中，如何使学生的成绩自动生成某等级以及某等级的个数？ 以后面表一、表二为例，给学生1至学生24的总分（J3:J26）区域评定等级；若评价的标准是：未达标（x＜235分），合格（235≤x＜245分），良好（245≤x＜260分），优秀（x≥260分）. 1、自动生成相应的评定等级.在评价部分的单元格I3键入函数公式(见后面表一茶色单元格以及公式栏)：=IF(H3235,未达标,IF(H3245,合格,IF(H3260,良好,IF(H3=300,优秀,))))→敲回车即可评定出H3等级（优秀），后面学生2至学生26的总分（I4:I26）区域的等级可以在选定I3单元格后拖移或双击生成. 2、自动生成某等级的个数.如在评价部分的单元格I27键入函数公式(见后面表二青绿色单元格以及公式栏)：=COUNTIF(I3:I26,未达标)人（未达标)→敲回车后可以自动生成“未达标”的个数， 人（未达标)是标明未达标这个等级名称的公式. 以上诸方法可以类推，拓展！ 如用字母表示等级：=IF(H3235,C,IF(H3245,B,IF(H3260,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,未达标)人（合格) . 公式输入并不复杂，“+”后面可采用复制粘贴的办法，然后简单修改. …… 问题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,良)人次 →确定即可生成符合条件的总个数→提行单元格输入（良好及以上)→两单元格内部线条设置为“无”.（见后面表五） 附：表一至表五 2014年6月1日 星期日