Excel怎么根据条件统计数据
啊南 5135阅读 2020.06.18
【导语】: 在日常对数据的统计过程中,我们经常会遇到指定条件的数据统计问题,如何根据条件统计数据?不少初学用户觉得非常棘手,接下来就跟着小编一起来学习吧。
SUMIFS 函数是一个数学与三角函数,用于计算其满足多个条件的全部参数的总量。 例如,可以使用 SUMIFS 计算一个国家/地区内 (1) 邮政编码为同一个且 (2) 利润超过了特定美元值的零售商的总量。
语法
SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)
=SUMIFS(A2:A9,B2:B9,"=A*",C2:C9,"卢宁")
=SUMIFS(A2:A9,B2:B9,"<>香蕉",C2:C9,"卢宁")
参数名称 |
说明 |
---|---|
Sum_range (必需) |
要求和的单元格区域。 |
Criteria_range1 (必需) |
使用 Criteria1 测试的区域。 Criteria_range1 和 Criteria1 设置用于搜索某个区域是否符合特定条件的搜索对。 一旦在该区域中找到了项,将计算 Sum_range 中的相应值的和。 |
Criteria1 (必需) |
定义将计算 Criteria_range1 中的哪些单元格的和的条件。 例如,可以将条件输入为 32、">32"、B4、"苹果" 或 "32"。 |
Criteria_range2, criteria2, … (optional) |
附加的区域及其关联条件。 最多可以输入 127 个区域/条件对。 |
要在 Excel 中使用这些示例,请拖动以选择表格中的数据,然后右键单击所选内容并选择“复制”。 在新的工作表中,右键单击单元格 A1,然后在“粘贴选项”下方选择“匹配目标格式”。
已销售数量 |
产品 |
销售人员 |
---|---|---|
5 |
苹果 |
卢宁 |
4 |
苹果 |
Sarah |
15 |
香梨 |
卢宁 |
3 |
香梨 |
Sarah |
22 |
香蕉 |
卢宁 |
12 |
香蕉 |
Sarah |
10 |
胡萝卜 |
卢宁 |
33 |
胡萝卜 |
Sarah |
公式 |
说明 |
|
=SUMIFS(A2:A9, B2:B9, "=香*", C2:C9, "卢宁") |
计算以“香”开头并由“卢宁”售出的产品的总量。 它使用Criteria1中的通配符 *, "= A *"查找Criteria_range1 B2: B9 中的匹配产品名称,并在Criteria_range2 c2: C9 中查找名称"Tom" 。 然后计算 Sum_range A2:A9 中同时满足这两个条件的单元格的总量。 结果为 20。 |
|
=SUMIFS(A2:A9, B2:B9, "<>香蕉", C2:C9, "卢宁") |
计算卢宁售出的非香蕉产品的总量。 它通过在Criteria1" <>香蕉"中使用<>来排除香蕉,并在 " Criteria_range2 C2: C9" 中查找名称"Tom" 。 然后计算 Sum_range A2:A9 中同时满足这两个条件的单元格的总量。 结果为 30。 |
常见问题
问题 |
说明 |
---|---|
显示0(零),而不是预期结果。 |
如果要测试文本值,如姓名,请确保将 Criteria1,2 用引号引起来。 |
Sum_range 有值 TRUE 或 FALSE 时,结果不正确。 |
Sum_range 的值 TRUE 和 FALSE 的求值方式不同,可能会在将其相加时导致意外结果。 Sum_range 中包含 TRUE 的单元格的求值结果为 1。 包含 FALSE 的单元格的求值结果为 0(零)。 |
最佳做法
要执行的操作 |
说明 |
---|---|
使用通配符。 |
在 criteria1,2 中使用问号 (?) 和星号 (*) 之类的通配符可以帮助找到相似但不精确的匹配项。 问号匹配任何单个字符。 星号匹配任意字符序列。 如果要查找实际的问号或星号,请在问号前键入波形符 (~)。 例如,= SUMIFS (A2: A9,B2: B9,"= A *",C2: C9,"To?") 将添加名称以 "To" 开头并以可能发生变化的最后一个字母结尾的所有实例。 |
了解 SUMIF 和 SUMIFS 之间的区别。 |
SUMIFS 和 SUMIF 的参数顺序有所不同。 具体而言,sum_range 参数在 SUMIFS 中是第一个参数,而在 SUMIF 中,却是第三个参数。 这是使用这些函数时出现问题的一个常见原因。 如果要复制和编辑这些相似函数,请确保按正确的顺序放置参数。 |
对区域参数使用相同行数和列数。 |
Criteria_range 参数与 Sum_range 参数必须包含相同的行数和列数。 |
IFS 函数检查是否满足一个或多个条件,且是否返回与第一个 TRUE 条件对应的值。IFS 可以取代多个嵌套 IF 语句,
并且可通过多个条件更轻松地读取。
语法
IFS(logical_test1, value_if_true1, [logical_test2, value_if_true2], [logical_test3, value_if_true3],…)
logical_test1:(必需),计算结果为 TRUE 或 FALSE 的条件。
value_if_true1:(必需),当 logical_test1 的计算结果为 TRUE 时要返回结果。可以为空。
logical_test2…,logical_test127:(可选),计算结果为 TRUE 或 FALSE 的条件。
value_if_true2…,value_if_true127:(可选),当logical_testN的计算结果为 TRUE 时要返回结果。
每个value_if_trueN对应于一个条件logical_testN。可以为空。
说明
IFS 函数允许测试最多 127 个不同的条件。
例如:=IFS(A1=1,1,A1=2,2,A1=3,3)
如果(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个不同的条件,只要有一个为真就返回该真值;
结合右表在参数中输入:
(B2=100,"满分",B2>=95,"优秀",B2>=85,"良好",B2>=70,"较好",B2>=60,"及格",B2<60,"不及格")
点击确定,再下拉填充公式,即可快速得到所有学生的成绩等级~
本文介绍 Microsoft Excel 中 DPRODUCT 函数的公式语法和用法。
说明
返回列表或数据库中满足指定条件的记录字段(列)中的数值的乘积。
语法
DPRODUCT(database, field, criteria)
DPRODUCT 函数语法具有下列参数:
Database 必需。 构成列表或数据库的单元格区域。 数据库是包含一组相关数据的列表,其中包含相关信息的行为记录,而包含数据的列为字段。 列表的第一行包含每一列的标签。
Field 必需。 指定函数所使用的列。 输入两端带双引号的列标签,如 "使用年数" 或 "产量";或是代表列表中列位置的数字(不带引号):1 表示第一列,2 表示第二列,依此类推。
Criteria 必需。 包含所指定条件的单元格区域。 可以为参数 criteria 指定任意区域,只要此区域包含至少一个列标签,并且列标签下至少有一个在其中为列指定条件的单元格。
备注
可以为参数 criteria 指定任意区域,只要此区域包含至少一个列标签,并且列标签下方包含至少一个用于指定条件的单元格。
例如,如果区域 G1:G2 在 G1 中包含列标签 Income,在 G2 中包含数量 ¥100,000,可将此区域命名为 MatchIncome,那么在数据库函数中就可使用该名称作为条件参数。
虽然条件区域可以位于工作表的任意位置,但不要将条件区域置于列表的下方。 如果向列表中添加更多信息,新的信息将会添加在列表下方的第一行上。 如果列表下方的行不是空的,Microsoft Excel 将无法添加新的信息。
确定条件区域没有与列表相重叠。
若要对数据库中的一个完整列执行操作,请在条件区域中的列标签下方加入一个空行。
示例
复制下表中的示例数据,然后将其粘贴进新的 Excel 工作表的 A1 单元格中。 要使公式显示结果,请选中它们,按 F2,然后按 Enter。 如果需要,可调整列宽以查看所有数据。
树种 |
高度 |
年数 |
产量 |
利润 |
高度 |
---|---|---|---|---|---|
=苹果树 |
>10 |
<16 |
|||
=梨树 |
|||||
树种 |
高度 |
年数 |
产量 |
利润 |
|
苹果树 |
18 |
20 |
14 |
¥1,050 |
|
梨树 |
12 |
12 |
10 |
¥960 |
|
樱桃树 |
13 |
14 |
9 |
¥1,050 |
|
苹果树 |
14 |
15 |
10 |
¥750 |
|
梨树 |
9 |
8 |
8 |
¥770 |
|
苹果树 |
8 |
9 |
6 |
¥450 |
|
公式 |
说明 |
结果 |
|||
=DPRODUCT(A5:E11, "产量", A1:F3) |
此函数计算高度在 10 到 16 英尺之间的苹果树和所有梨树产量的乘积。 |
¥8,000 |
如果 number ≥ step,则返回 1;否则返回 0(零)。 可以使用此函数来筛选一组值。 例如通过对几个 GESTEP
函数进行求和,可计算超过阈值的值的计数。
语法
GESTEP(number, [step])
GESTEP 函数语法具有下列参数:
Number:必需,要针对步骤进行测试的值。
Step:可选,阈值。 如果省略 step 值,则 GESTEP 使用零。
说明
如果任何参数为非数值型, 则 GESTEP 返回 #VALUE! 错误值。
我们在进行日常办公时,经常需要处理各种各样的表格,那么如何利用函数快速查看某项数据是否及格呢?
此处涉及GESTEP函数,下面教大家如何巧用WPS 2019 GESTEP函数快速查看及格数据。
具体方法与步骤如下:
先在单元格(F3)中输入及格值60。将光标定位到(D3)单元格,点击菜单栏插入函数,找到工程-GESTEP函
数,在弹出的对话框中有两项内容:“待测值”和“临界值”。
其中“待测值”是指你要检测的数值;
而“临界值”是你所定及格的数值;
我们在“待测值”中输入(C3)苏大强的分数,“临界值”输入(F3)及格线。
“待测值”大于等于“临界值”时结果为1,否则为0。
点击“确定”,返回结果0,代表苏大强不及格。
光标定位至(D3)单元格右下角,呈+字形时下拉复制公式,即可判断所有数据是否达到及格线了。
FILTER 函数可以基于定义的条件筛选一系列数据。
在以下示例中,我们使用公式 =FILTER(A5:D20,C5:C20=H2,"") 返回包含“苹果”(如单元格 H2 中所选定)的所有记录,如果没有“苹果”,则返回空字符串 ("")。
-----------------------------------------------------------------------------------------------------------------------
语法
FILTER 函数基于布尔值 (True/False) 数组筛选数组。
=FILTER(array,include,[if_empty])
参数 |
描述 |
array 必需 |
要筛选的数组或区域 |
include 必需 |
布尔值数组,其高度或宽度与数组相同 |
[if_empty] 可选 |
所含数组中的所有值都为空(筛选器不返回任何内容)时返回的值 |
可将数组视为一行值、一列值或几行值和几列值的组合。 在上述示例中,FILTER 公式的源数组为范围 A5:D20。
FILTER 函数将返回一个数组,如果该数组是公式的最终结果,则将溢出。 这意味着,当按“Enter”时,Excel 将动态创建相应大小的数组范围。 如果支持数据位于 Excel 表格中,若使用结构化引用,则从数组范围中添加或删除数据时,数组将自动重设大小。 有关详细信息,请参阅关于溢出数组行为的此文章。
如果数据集可能返回空值,请使用第三个参数 ([if_empty])。 否则将导致 #CALC! 错误 ,因为 Excel 当前不支持空数组。
如果 include 参数的任何值都是一个错误的值(#N/A、#VALUE 等)或无法转换为布尔值,则 FILTER 函数将返回一个错误。 ?
Excel 对工作簿之间的动态数据提供有限支持,并且仅当这两个工作簿时都处于打开状态时才支持此方案。 如果关闭源工作簿,刷新时,任何链接的动态数组公式都将返回 #REF! 错误 。
示例
用于返回多个条件的 FILTER
在此示例中,我们使用乘法运算符 (*),以返回数组范围 (A5:D20) 中包含“苹果”且位于东部区域的所有值:=FILTER(A5:D20,(C5:C20=H1)*(A5:A20=H2),"")。
用于返回多个条件并排序的 FILTER
在此示例中,我们配合使用之前的 FILTER 函数和 SORT 函数,以返回数组范围 (A5:D20) 中包含“苹果”且位于东部区域的所有值,然后对 Units 进行降序排序:=SORT(FILTER(A5:D20,(C5:C20=H1)*(A5:A20=H2),""),4,-1)
在此示例中,我们配合使用 FILTER 函数和加法运算符 (+),以返回数组范围 (A5:D20) 中包含“苹果”或位于东部区域的所有值,然后对 Units 进行降序排序:=SORT(FILTER(A5:D20,(C5:C20=H1)+(A5:A20=H2),""),4,-1)。
请注意,这些函数都不需要绝对引用,因为它们仅存在于一个单元格中,并将其结果溢出到相邻单元格。
COUNTIFS 函数将条件应用于跨多个区域的单元格,然后统计满足所有条件的次数。
语法
COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2],…)
COUNTIFS 函数语法具有以下参数:
criteria_range1 :必需,在其中计算关联条件的第一个区域。
criteria1:必需,条件的形式为数字、表达式、单元格引用或文本,它定义了要计数的单元格范围。
例如,条件可以表示为 32、">32"、B4、"apples"或 "32"。
criteria_range2, criteria2,...:可选,附加的区域及其关联条件。 最多允许 127 个区域/条件对。
重要: 每一个附加的区域都必须与参数 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。
语法
OR(logical1,logical2,...)
Logical1,logical2,... 为需要进行检验的 1 到 30 个条件表达式。
说明
参数必须能计算为逻辑值,如 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。
根据值列表计算一个值(称为表达式),并返回与第一个匹配值对应的结果。
如果不匹配,则可能返回可选默认值。
语法
SWITCH(表达式、 value1、 result1、 [默认值或值 2,result2],...[默认或 value3,result3])
表达式 (必需) 表达式是值 (如数字、 日期或文本),将比较 value1...value126。
值 1...value126 ValueN是一个将与表达式进行比较的值。
result1...result126 ResultN是在相应的valueN参数与表达式匹配时要返回的值。
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,"星期
四",5,"星期五",6,"星期六",7,"星期日",8,"出错啦!") 。
按回车键,即可看到1匹配了星期一。
将光标定位到B2单元格右下角,呈+字形时下拉填充单元格,即可看到值都匹配完成了我们定义的结果。
返回逻辑值 TRUE。
语法
TRUE( )
说明
可以直接在单元格或公式中键入值 TRUE,而可以不使用此函数。函数 TRUE 主要用于与其他电子表格程序兼容。
EXCEL中的TRUE是逻辑函数,可判断我们设置的条件是否成立。
这是一个无参数的函数,希望基于条件返回值 TRUE 时,可使用此函数。
以此成绩单为例,现需要判断学生成绩是否及格。
具体方法与步骤如下:
将光标定位在D3处,输入=D3>=60。
回车确定,返回TRUE即及格。
将光标放在D3右下角呈十字形下拉填充公式,就能判断所有学生成绩是否达到及格线了。
在表格或数值数组的首列查找指定的数值,并由此返回表格或数组当前行中指定列处的数值。
默认情况下,表是升序的。
语法
VLOOKUP (lookup_value, table_array, col_index_num, [range_lookup])
Lookup_value为需要在数据表第一列中进行查找的数值。Lookup_value 可以为数值、引用或文本字符串。
当vlookup函数第一参数省略查找值时,表示用0查找。
Table_array为需要在其中查找数据的数据表。使用对区域或区域名称的引用。
col_index_num为table_array 中查找数据的数据列序号。col_index_num 为 1 时,返回 table_array 第一列的数
值,col_index_num 为 2 时,返回 table_array 第二列的数值,以此类推。
如果 col_index_num 小于1,函数 VLOOKUP 返回错误值 #VALUE!;如果 col_index_num 大于 table_array 的列
数,函数 VLOOKUP 返回错误值#REF!。
Range_lookup为一逻辑值,指明函数 VLOOKUP 查找时是精确匹配,还是近似匹配。如果为FALSE或0,则返回精
确匹配,如果找不到,则返回错误值 #N/A。如果 range_lookup 为TRUE或1,函数 VLOOKUP 将查找近似匹配值,
也就是说,如果找不到精确匹配值,则返回小于 lookup_value 的最大数值。如果range_lookup 省略,则默认为1。
都说vlookup是表格的一大神器,你到现在还不会用吗?
vlookup适用人群:频繁需要进行数据统计、筛选、核对的职场人...
使用场景举例:核对数据准确性,从另外一张表上获取数据信息。
比如这张数据表格,上面有所有工单号的信息,而我只想获取其中一部分工单号的信息,比如产品名称;那么问题
来了,如何快速查找填充产品名称呢?
鼠标定位到“产品名称”下第一个单元格,插入函数,选择“VLOOKUP”。
“查找值”:选择两个表拥有的共同列,即工单号C01-207所在单元格“A21”;
“数据表”:选中共同列(工单号)到目标列(产品名称)整个区域A2:F16,
再按住快捷键F4添加绝对引用$,目的是为了保持"数据表"这个区域不变;
“列数据”:目标列在整个框定区域的第几列,产品名称第6列,填6;
“匹配条件”分为有精确查找0和模糊查找1,此处需要精确查找,故输入0,再单击“确定”。
下拉填充,搞定。
关联标签:
导读索引
- 怎么使用excel SUMIFS函数快速统计符合条件的数据
- 怎么利用excel多条件判断对数据进行标注,ifs函数怎么用
- excel怎么快速计算符合条件单元格的乘积,DPRODUCT函数怎么用
- excel怎么快速标注数据是否满足条件,GESTEP函数怎么用
- excel怎么根据多个条件返回对应数据,FILTER函数使用技巧
- excel怎么按照条件统计不同的数据,COUNTIFS函数快捷统计技巧
- excel怎么按照条件对数据进行标示,OR函数怎么用
- excel条件函数SWITCH按照条件匹配结果,SWITCH函数如何使用
- excel根据条件判断数据是否符合怎么做,TRUE函数怎么用
- excel怎么根据条件快速提取数据,vlookup使用步骤