2020年新版SUMIF、COUNTIF和SUMPRODUCT函数在成绩统计中的应用.docx

? IPS 卷格-[BookS *JCL4* 西虚A1 B | C1 D1 ~ E1班2 ? IPS 卷格-[BookS *J CL4 * 西虚 A 1 B | C 1 D 1 ~ E 1 班 2 — 4 5 —- 6 ―h 7 — 8 一 9 一 10 — 11 一 12 —- 13 一 Z KJ. \ J- 123123 12 3 123 sc- -( c c zf rkzl\ c zk rx 姓赵钱孙李周吴郑王冯陈褚魏 一 - - - - _ 名-4 1 1 123456789012 d— ^1 ^1 号 考 学 W 5 1 T 9 \、口 O0二J越圜占 ［绘團? ”曲自选團形? + 三 - * A- JI? SUMIF COUNTI!和SUMPRODU函数在成绩统计中的应用 在学校组织的学年考试考务工作中，为了便于评卷实行流水作业和避免学生作 弊，经常将同一年级不同班级的学生拆散混编。 在考试登分完毕之后，如何做到 不改变原始表格的状态而进行分班成绩统计呢？利用 SUMIF COUNTI!和 SUMPRODU这三个函数，一切都将会变得十分轻松。下面举例说明。 一、造册登分 登分册一般有班级、考号、及语文、数学等学科名称，我们可以把这个工作表建 立在Sheetl，并将其重命名为“登分册”，将学生成绩输入其中（见图一）。 将Sheet2命名为“统计表”，在其中设置好班级、科目、平均分、及格人数、 及格率、优生人数、优生率等统计信息列标题，输入班级和需要统计的科目容（如 图二）。 竄匚）回冈 宋体Z III董養辰□同J*.? 』0 I』? ■*』I」Bq声B312345 宋体 Z III董養辰□同J *.? 』0 I 』? ■*』I 」Bq声 B3 1 2 3 4 5 6 7 8 g 10 11 12 13 14 15 班级 一⑴ 一⑶ 一⑴ -⑵ 一⑶ 平均分 及格人数及格率 G H 优■生人數优生率 图二 0 FPS 舂格-?] : 立件② 麟② 视團迫 插入① 格式? 工貝① 数据? 窗口迪 帮助⑩ ^WPG Office?... -SA 险―一-输入要躍索的內容-一 ■幻?i」二?*#51心二I矍s胡肮島，/吟「I岂￡ ：九 和 庐| 险―一-输入要躍索的內容-一 ■幻? ?、登刑:.狡计表 ?活 自选團形(a) * \ A 口。俎 4)園jS| A = 二、建立函数 在“统计表”中的C2单元格输入数组公式=SUMIF登分册!$A$2: A$13, —（ 1） ,登分册!$D$2: D$13）,按住Ctrl和Shift键回车可以计算出一 （1）班的总分, 将公式向下复制到各单元格，并将其中的“一（1） ”班分别改为相应班级名称。 这个数组公式的含义是：在“登分册”工作表中查找“一（1）班对应的成绩， 并返回一（1）班所有成绩之和。如图三 来体 ▼丄12 ▼ 文咋F 痛涓EI观国I巧扌卸■■⑴林式〔⑴工星⑺r? （D）窗口釦帮助fHi 1」?二拙?,囚「iJ 2 .蟹「醫胛匕* /巧 孩更? 計u=辿1? % 12 3 4 5 6. 7 目文文文学学学 科语语谙数数数 12 3-123 班二一 一一一 在“统计表” D2单元格输入公式=ROUND（C2/COUNT登分册!$A$2: A$13,统计 表!A2）,2）计算出一（1）班的平均分，向下拖动复制公式到相应单元格。该公式 的含义是：将C2中的总分除以“登分册” A列中与A2显示的值（一（1）班）相 同的人数，并保留两位小数。 在E2中输入数组公式计算及格人数。该数组函数为=SUMPRODUC登分册!$A$2: A$13=统计表!$A2）*（登分册!$D$2： D$13=60）），其含义是：若“登分册”工作 表A列中有与“统计表” A2相同的信息，即“一（1）班”，则返回与“一（1） 班”相对应的成绩于或等于60分的人数（如图四）。将公式向下复制后，在 F2 单元格中输入公式=E2/COUNTIF登分册!$A$2 : A$13,统计表!$A2）计算该班的及 格率。 址生人数优生率BD EF ； H辺 ion %辿氏 ^ST^TODUCT （（-^ WI $ A$2: $ A$1 3=^：计表!抓 2）*（登好册! $T1$2:$D$1 AMO））｝B Z Q巨盏妄口閨?￡ 址生人数优生率 B D E F ； H 辺 ion % 辿氏 ^ST^TODUCT （（-^ WI $ A$2: $ A$1 3=^：计表!抓 2）*（登好册! $T1$2:$D$1 AMO））｝ B Z Q巨盏妄口閨?￡ % *黑鲁 町虚越鞍H~- 7= * 审卯雄裘楼-『成址纯计一如變*1 岚睜皿丼nfft■麻複桓诃旳fl占豆弋|、了 ? T 12 ? ]|玄件佬）|崭掾（I】机圧