excel 统计问题？

来源：蜘蛛抓取(WebSpider) 时间：2022-07-23 04:38 标签： 统计漏统

对于教师而言，经常需要用Excel进行学生成绩统计，会被一些常见问题难住。

碰到的难题主要有：如何统计不同分数段人数、如何在保持学号不变前提下进行排名、如何将百分制转换成不同分数段与如何用红色显示不及格的分数等，本文着重对这些问题的解决方法与技巧加以分析和讨论。

Excel统计学生成绩时的四个难题

假设在统计学生成绩时，我们需要统计出如图1所示的相关结果。

图1　学生成绩统计所需要的结果图

这里，假设学号、姓名、成绩等列及行15都已经事先输好，需要让Excel统计其他的相关数据结果。

这时，成绩统计中主要难解决的问题及它们在图中的位置如下：

问题1： 如何统计不同分数段的学生人数?(图中A16~E16)

问题2： 如何在保持学号顺序不变的前提下进行学生成绩名次排定?(图中F2~F13)

问题3： 如何将百分制转换成不同的等级分?(图中“等级1”与“等级2”列)

问题4： 如何使不及格的分数以红色显示?(图中红色显示部分，即第12行)

下面，针对上面提出的四个难题分别讨论解决的方法与技巧。

问题1、统计不同分数段的学生人数

统计不同分数段的学生人数是非常常见的需求，其所需结果如图1中A16~E16所示。

这里，假设需要统计90~100、80~89、70~79、60~69及低于60分五个不同分数段的人数。

通常，统计不同分数段最好的方法是利用COUNTIF函数。

其中有两个参数，第一个参数为统计的范围，一般最好用绝对引用；第二个参数为统计条件，条件非数字时要加引号。

对于其他在两个分数之间的分数段的人数统计，可以用两个COUNTIF( )函数相减。

如在A16单元格中输入公式：

当然也可以用COUNTIFS函数解决。

如果要统计80~89、70~79与60~69分数段的人数，只要利用自动填充柄将该公式复制到右边三个单元格，再把"<=100"与"<90"作相应的修改，就可以得到正确的结果。

实际上这类问题用FREQUENCY函数更为方便，只不过因为COUNTIF函数更常用罢了，关于FREQUENCY函数的用法，可以参考下面的教程。

问题2、保持学号顺序不变的前提下进行成绩排名

学生成绩排定在学生成绩统计中经常用到。

特别要强调的是，这里所谈的方法不是一般的排序，因为那样会使学生的学号顺序发生变化。

这里所需要的是在保持学号顺序不变的情况下进行学生成绩名次排定的功能，其所需结果如图1中F2~F13所示。

要进行保持学号顺序不变的情况下进行学生成绩名次的排定，最好使用RANK函数。

其中有三个参数，第一个参数为某个学生的成绩所在单元格；

第二个参数为整个班级成绩所在的区域；

第三个参数是可选的，表示统计方式，若不写或写0，则成绩高的名次靠前，一般都使用这种方式，如果写1，则成绩高的名次靠后，这种情况一般较少用。

为了在保持学号顺序不变的前提下进行学生成绩名次排定，可以在F2单元格中输入公式：

然后，利用自动填充柄将其复制到下方的几个单元格。

注意，这里$C$2:$C$13用的是绝对地址，是为了保证公式在复制时此处不变，因为作为第二个参数，这里都是指整个班级成绩所在的区域，这个区域是相同的。

3、将百分制转换成不同的等级分

将百分制转换成不同的等级分有多种不同的划分方法，其所需结果如图1中“等级1”与“等级2”列所示。

具体使用哪种等级划分方法可根据实际情况自己确定。

在百分制转换成不同的等级分时，一般使用IF函数。

其中有三个参数，第一个参数为条件，不能加引号；

第二个参数为条件成立时的结果，如果是显示某个值，如果显示内容不是数字则要加引号；

第三个参数为条件不成立时的结果，如果是显示某个值，如果显示内容不是数字同样要加引号。

该函数可以嵌套，即在第二个或第三个参数处可以再写一个IF函数。

为了得到“等级1”列所要的等级结果，可以在D2单元格中输入公式：

然后，利用自动填充柄将其复制到下方的几个单元格。

为了得到“等级2”列所要的等级结果，可以在E2单元格中输入公式：

然后，利用自动填充柄将其复制到下方的几个单元格。

同样，对于多个IF嵌套的用法，LOOKUP函数更有优势，只是因为IF更为常用罢了。

关于LOOKUP的区间匹配用法，可以参考下面的教程。

4、使不及格的分数以红色显示

统计学生成绩时经常需要将不及格的分数用红色显示，其结果如图1中红色显示部分(如第12行)。

使不及格的分数以红色显示需要使用“格式”菜单中的“条件格式”命令。

该命令会弹出一个对话框，其中要求确认条件与相应的格式。

对于“成绩”列，可先选中C2：C13，然后使用“格式”菜单中的“条件格式”命令，在弹出的对话框中，左边使用默认的“单元格数值”，中间选“小于”，右边填写60，然后单击右边的“格式”按钮，从中选择红色，最后单击两次“确定”按钮。

对于“等级1”列，可先选中D2：D13，然后使用“格式”菜单中的“条件格式”命令，在弹出的对话框中，左边使用默认的“单元格数值”，中间选“等于”，右边填写E，然后单击右边的“格式”按钮，从中选择红色，最后单击两次“确定”按钮。“等级2”列类似。

对于其他的一些统计计算要求，如怎样计算各分数段的百分比、如何计算机优良率与合格率等功能，应该比较简单，本文此处不赘述。

【导语】： 在日常对数据的统计过程中，我们经常会遇到指定条件的数据统计问题，如何根据条件统计数据？不少初学用户觉得非常棘手，接下来就跟着小编一起来学习吧。

SUMIFS 函数是一个数学与三角函数，用于计算其满足多个条件的全部参数的总量。 例如，可以使用 SUMIFS 计算一个国家/地区内 (1) 邮政编码为同一个且 (2) 利润超过了特定美元值的零售商的总量。

Criteria_range1 和 Criteria1 设置用于搜索某个区域是否符合特定条件的搜索对。 一旦在该区域中找到了项，将计算 Sum_range 中的相应值的和。

附加的区域及其关联条件。 最多可以输入 127 个区域/条件对。

要在 Excel 中使用这些示例，请拖动以选择表格中的数据，然后右键单击所选内容并选择“复制”。 在新的工作表中，右键单击单元格 A1，然后在“粘贴选项”下方选择“匹配目标格式”。

中查找名称"Tom" 。 然后计算 Sum_range A2:A9 中同时满足这两个条件的单元格的总量。 结果为 20。

显示0（零），而不是预期结果。

如果要测试文本值，如姓名，请确保将 Criteria1,2 用引号引起来。

Sum_range 的值 TRUE 和 FALSE 的求值方式不同，可能会在将其相加时导致意外结果。

Sum_range 中包含 TRUE 的单元格的求值结果为 1。 包含 FALSE 的单元格的求值结果为 0（零）。

在 criteria1,2 中使用问号 (?) 和星号 (*) 之类的通配符可以帮助找到相似但不精确的匹配项。

问号匹配任何单个字符。 星号匹配任意字符序列。 如果要查找实际的问号或星号，请在问号前键入波形符 (~)。

例如，= SUMIFS （A2： A9，B2： B9，"= A *"，C2： C9，"To？"） 将添加名称以 "To" 开头并以可能发生变化的最后一个字母结尾的所有实例。

SUMIFS 和 SUMIF 的参数顺序有所不同。 具体而言，sum_range 参数在 SUMIFS 中是第一个参数，而在 SUMIF 中，却是第三个参数。 这是使用这些函数时出现问题的一个常见原因。

如果要复制和编辑这些相似函数，请确保按正确的顺序放置参数。

对区域参数使用相同行数和列数。

IFS 函数检查是否满足一个或多个条件，且是否返回与第一个 TRUE 条件对应的值。IFS 可以取代多个嵌套 IF 语句，

并且可通过多个条件更轻松地读取。

IFS 函数允许测试最多 127 个不同的条件。

如果（A1 等于 1，则显示 1，如果 A1 等于 2，则显示 2，或如果 A1 等于 3，则显示 3）。

一般不建议对 IF 或 IFS 语句使用过多条件，因为需要按正确的顺序输入多个条件，这样一来，构建、测试和更新会

IF函数是我们常用的条件判定函数，但是当条件较多时，其弊端也显而易见。

这时我们就可以用IFS函数来替代IF函数，完成多条件判定工作。

以左边这个成绩表为例，现需要判断学生成绩等级，条件如右表所示；

首先将光标定位至C2单元格，点击插入函数按钮，找到IFS函数-确定；

此处可看见ifs函数的语法结构为=IFS（测试条件1，返回值1，测试条件2，返回值2……条件判断N，返回值N）

允许测试最多127个不同的条件，只要有一个为真就返回该真值；

结合右表在参数中输入：

点击确定，再下拉填充公式，即可快速得到所有学生的成绩等级~

返回列表或数据库中满足指定条件的记录字段（列）中的数值的乘积。

DPRODUCT 函数语法具有下列参数：

Database  必需。 构成列表或数据库的单元格区域。 数据库是包含一组相关数据的列表，其中包含相关信息的行为记录，而包含数据的列为字段。 列表的第一行包含每一列的标签。

Field  必需。 指定函数所使用的列。 输入两端带双引号的列标签，如 "使用年数" 或 "产量"；或是代表列表中列位置的数字（不带引号）：1 表示第一列，2 表示第二列，依此类推。

Criteria  必需。 包含所指定条件的单元格区域。 可以为参数 criteria 指定任意区域，只要此区域包含至少一个列标签，并且列标签下至少有一个在其中为列指定条件的单元格。

可以为参数 criteria 指定任意区域，只要此区域包含至少一个列标签，并且列标签下方包含至少一个用于指定条件的单元格。

例如，如果区域 G1:G2 在 G1 中包含列标签 Income，在 G2 中包含数量 ￥100,000，可将此区域命名为 MatchIncome，那么在数据库函数中就可使用该名称作为条件参数。

虽然条件区域可以位于工作表的任意位置，但不要将条件区域置于列表的下方。 如果向列表中添加更多信息，新的信息将会添加在列表下方的第一行上。 如果列表下方的行不是空的，Microsoft Excel 将无法添加新的信息。

确定条件区域没有与列表相重叠。

若要对数据库中的一个完整列执行操作，请在条件区域中的列标签下方加入一个空行。

复制下表中的示例数据，然后将其粘贴进新的 Excel 工作表的 A1 单元格中。 要使公式显示结果，请选中它们，按 F2，然后按 Enter。 如果需要，可调整列宽以查看所有数据。

此函数计算高度在 10 到 16 英尺之间的苹果树和所有梨树产量的乘积。

如果 number ≥ step，则返回 1；否则返回 0（零）。 可以使用此函数来筛选一组值。 例如通过对几个 GESTEP

函数进行求和，可计算超过阈值的值的计数。

GESTEP 函数语法具有下列参数：

Number：必需，要针对步骤进行测试的值。

我们在进行日常办公时，经常需要处理各种各样的表格，那么如何利用函数快速查看某项数据是否及格呢？

此处涉及GESTEP函数，下面教大家如何巧用WPS 2019 GESTEP函数快速查看及格数据。

先在单元格（F3）中输入及格值60。将光标定位到（D3）单元格，点击菜单栏插入函数，找到工程-GESTEP函

数，在弹出的对话框中有两项内容：“待测值”和“临界值”。

其中“待测值”是指你要检测的数值；

而“临界值”是你所定及格的数值；

我们在“待测值”中输入（C3）苏大强的分数，“临界值”输入（F3）及格线。

“待测值”大于等于“临界值”时结果为1，否则为0。

点击“确定”，返回结果0，代表苏大强不及格。

光标定位至（D3）单元格右下角，呈+字形时下拉复制公式，即可判断所有数据是否达到及格线了。

FILTER 函数可以基于定义的条件筛选一系列数据。

在以下示例中，我们使用公式 =FILTER(A5:D20,C5:C20=H2,"") 返回包含“苹果”（如单元格 H2 中所选定）的所有记录，如果没有“苹果”，则返回空字符串 ("")。

布尔值数组，其高度或宽度与数组相同

所含数组中的所有值都为空（筛选器不返回任何内容）时返回的值

可将数组视为一行值、一列值或几行值和几列值的组合。 在上述示例中，FILTER 公式的源数组为范围 A5:D20。
FILTER 函数将返回一个数组，如果该数组是公式的最终结果，则将溢出。 这意味着，当按“Enter”时，Excel 将动态创建相应大小的数组范围。 如果支持数据位于 Excel 表格中，若使用结构化引用，则从数组范围中添加或删除数据时，数组将自动重设大小。 有关详细信息，请参阅关于溢出数组行为的此文章。
如果数据集可能返回空值，请使用第三个参数 ([if_empty])。 否则将导致 #CALC! 错误 ，因为 Excel 当前不支持空数组。
如果 include 参数的任何值都是一个错误的值（#N/A、#VALUE 等）或无法转换为布尔值，则 FILTER 函数将返回一个错误。 ?
Excel 对工作簿之间的动态数据提供有限支持，并且仅当这两个工作簿时都处于打开状态时才支持此方案。 如果关闭源工作簿，刷新时，任何链接的动态数组公式都将返回 #REF! 错误 。

用于返回多个条件的 FILTER

用于返回多个条件并排序的 FILTER

请注意，这些函数都不需要绝对引用，因为它们仅存在于一个单元格中，并将其结果溢出到相邻单元格。

COUNTIFS 函数将条件应用于跨多个区域的单元格，然后统计满足所有条件的次数。

COUNTIFS 函数语法具有以下参数：

criteria1：必需，条件的形式为数字、表达式、单元格引用或文本，它定义了要计数的单元格范围。 

重要: 每一个附加的区域都必须与参数 criteria_range1 具有相同的行数和列数。 这些区域无需彼此相邻。

每个区域的条件一次应用于一个单元格。 如果所有的第一个单元格都满足其关联条件，则计数增加 1。 

如果所有的第二个单元格都满足其关联条件，则计数再增加 1，依此类推，直到计算完所有单元格。

如果条件参数是对空单元格的引用，COUNTIFS 会将该单元格的值视为 0。

您可以在条件中使用通配符， 即问号 (?) 和星号 (*)。 问号匹配任意单个字符，星号匹配任意字符串。 

如果要查找实际的问号或星号，请在字符前键入波形符 (~)。

在工作和生活中，我们经常要统计满足不同条件的数据，那么如何快速的统计满足不同条件的数据呢？

下面教大家一个小技巧，此处涉及COUNTIFS函数，以此产品地区销量表为例。

将光标放在(G4)处，点击插入函数-统计-COUNTIFS函数，弹出对话框，有两项参数。

“区域”指需要统计的区域；

“条件”是应要满足的条件，如果是文本需要用英文状态下双引号括住；

“区域”和“条件”可以自行添加。

“区域1”中输入（D3:D12），“条件1”中输入“江苏”。

“区域2”中输入（E3:E12），“条件2”中输入>5000。

意思是在区域D3至D12和区域E3至E12中，满足产地在”江苏”且销量大于5000的数据。

点击确定，就能得出满足规定条件的数据个数了。

在其参数组中，任何一个参数逻辑值为 TRUE，即返回 TRUE；所有参数的逻辑值为 FALSE，才返回 FALSE。 

参数必须能计算为逻辑值，如 TRUE 或 FALSE，或者为包含逻辑值的数组（ 用于建立可生成多个结果或可对在行

和列中排列的一组参数进行运算的单个公式。数组区域共用一个公式；数组常量是用作参数的一组常量）或引用。 
如果数组或引用参数中包含文本或空白单元格，则这些值将被忽略。 
如果指定的区域中不包含逻辑值，函数 OR 返回错误值 #VALUE!。 
可以使用 OR 数组公式来检验数组中是否包含特定的数值。若要输入数组公式，请按 Ctrl+Shift+Enter。 

OR函数是一个逻辑函数，它能为我们快速进行逻辑判断。

OR的中文含义是“或”的意思，它的使用也和这个意思相近。

在OR函数中，我们需要输入两个条件。但它的判断的形式是只要满足一个逻辑条件，则结果就会显示TURE。我们

以这个表格为例，来给大家详细解释：

某公司对升职设定了以下条件：

年龄大于35岁，或在本公司的工作年限要大于5年

我们用OR函数来筛选一下符合条件的员工。

我们选中（E2）单元格，点击插入函数，选择OR函数

在此，我们需要填入逻辑条件。

以这个表格为例，我们在逻辑值1填入B2>30

在逻辑值2填入C2>10，点击确定，我们看到结果出现了TURE。

因为小明既满足了年龄大于35岁，又满足了工作年限大于5年。

我们选中单元格下方的实心十字小标下拉，就可以得到所有人能否升职的结果。可以看到小华不满足>30的年龄条

件，但是工作年限满足了10年，所以他的结果也是TURE。

OR函数的特点就是只要有一个逻辑条件满足，就会出现TURE。

根据值列表计算一个值（称为表达式），并返回与第一个匹配值对应的结果。

如果不匹配，则可能返回可选默认值。

表达式 （必需） 表达式是值 （如数字、 日期或文本），将比较 value1...value126。

ResultN ，而且必须为每个相应的valueN参数提供。

默认 （可选） 默认值为valueN表达式中没有匹配的情况下返回的值。

有没有相应的resultN表达式标识默认参数 （请参见示例）。默认值必须是最后一个参数的函数中。

因为功能仅限于 254 参数，您可以使用参数值和结果达 126 的对。

SWITCH函数的主要作用是根据表达式计算一个值，并返回与该值所匹配的结果。

如果不匹配，则返回可选默认值。

也就是说，当我们定义值“1”的匹配结果为“星期一”。可以使用SWITCH函数将“1”转化为“星期一”。

接下来在这个表中实际操作看看帮助大家理解。

选中B2单元格，点击插入函数。在查找框中查找并选中SWITCH函数，点击确定。

此时弹出对话框，必填参数共3项。

“表达式”是要计算的表达式，输入A2；

“值”与“结果”即定义一个值和对应一个结果，至少定义一组，最多126组；

此处要注意的是，“结果”的值前后必须加上双引号。

在值1输入1，结果1输入"星期一"，代表1=星期一。

值2输入2，结果3输入"星期二"，代表2=星期二。

依次输入要定义的值即可。

但这样输入比较麻烦，我们可以在单元格内输入公式=SWITCH(A2,1,"星期一",2,"星期二",3,"星期三",4,"星期

按回车键，即可看到1匹配了星期一。

将光标定位到B2单元格右下角，呈+字形时下拉填充单元格，即可看到值都匹配完成了我们定义的结果。

可以直接在单元格或公式中键入值 TRUE，而可以不使用此函数。函数 TRUE 主要用于与其他电子表格程序兼容。 

EXCEL中的TRUE是逻辑函数，可判断我们设置的条件是否成立。

这是一个无参数的函数，希望基于条件返回值 TRUE 时，可使用此函数。

以此成绩单为例，现需要判断学生成绩是否及格。

回车确定，返回TRUE即及格。

将光标放在D3右下角呈十字形下拉填充公式，就能判断所有学生成绩是否达到及格线了。

在表格或数值数组的首列查找指定的数值，并由此返回表格或数组当前行中指定列处的数值。

默认情况下，表是升序的。

Lookup_value为需要在数据表第一列中进行查找的数值。Lookup_value 可以为数值、引用或文本字符串。

当vlookup函数第一参数省略查找值时，表示用0查找。

Table_array为需要在其中查找数据的数据表。使用对区域或区域名称的引用。

Range_lookup为一逻辑值，指明函数 VLOOKUP 查找时是精确匹配，还是近似匹配。如果为FALSE或0，则返回精

也就是说，如果找不到精确匹配值，则返回小于 lookup_value 的最大数值。如果range_lookup 省略，则默认为1。

都说vlookup是表格的一大神器，你到现在还不会用吗？

vlookup适用人群：频繁需要进行数据统计、筛选、核对的职场人...

使用场景举例：核对数据准确性，从另外一张表上获取数据信息。

比如这张数据表格，上面有所有工单号的信息，而我只想获取其中一部分工单号的信息，比如产品名称；那么问题

来了，如何快速查找填充产品名称呢？

鼠标定位到“产品名称”下第一个单元格，插入函数，选择“VLOOKUP”。

“查找值”：选择两个表拥有的共同列，即工单号C01-207所在单元格“A21”；

“数据表”：选中共同列（工单号）到目标列（产品名称）整个区域A2:F16，

再按住快捷键F4添加绝对引用$，目的是为了保持"数据表"这个区域不变；

“列数据”：目标列在整个框定区域的第几列，产品名称第6列，填6；

“匹配条件”分为有精确查找0和模糊查找1，此处需要精确查找，故输入0，再单击“确定”。

我要回帖

更多关于 统计漏统 的文章

 

随机推荐