Excel经验分享
如何在电子表选定范围内,统计任意字符出现的次数
根据日常统计需要,大概有三种情景:
情景一:
要查找的词完全占据一个单元格时,
直接使用函数:COUNTIF(range,criteria),即COUNTIF(查找范围,查找词),
示例:统计保卫人员“罗应光”本周内实际值班的次数,见下表:
操作方法:
选择任意空白单元格,在内输入:
=countif(查找范围,“查找词”) */如果没有正常统计结果,把查找词换为对应单元格地址
情景二:
要查找的词是某一单元格内容的一部分且在一个单元格中只出现一次,也是使用COUNTIF函数,第二个参数是查找词加通配符,即COUNTIF(查找范围,“查找词+通配符”);
查找词+通配符的形式如:“上海南站”“上海北站”“上海东站”可以统一使用“上海*”来表示,查找词与通配符是顺序排列的,即“上海*”表示的字段是“上海”开头之后为任意字符的意思。
示例:统计所有姓王的人员值班总数
任然可以使用countif()函数操作,如下图
要注意的是,在我使用的office2013版的Excel2013中,在countif(range,criteria)函数的criteria参数位置直接输入需要查找或统计的字符是不会出结果的,只能输入字符所在的单元格地址,另外 “字符+通配符” 的形式提示函数错误。其他版本的excel是否有这个问题,不是很清楚。
纠正:字符的输入两边必须加“”号,应该是受数字类型的限制,之上的错误主要原因就是没有在字符串两边加“”,Excel里所有符号都应该是英文符号,不能使用中文符号。
情景三
要查找的词在一个单元格中出现多次,使用SUMPRODUCT函数,即SUMPRODUCT(LEN(查找范围)-LEN(SUBSTITUTE(查找范围,查找词,替换后的值)))。
其中主要用到的有三个函数,分别为
-
SUMPROUCT(array1,array2,array3…),具体用法参看后文SUMPRODUCT函数
-
SUBSTITUTE(),用新字符替换查找字符
-
LEN(),返回字符数
此用法灵活性比较高,关键是根据需要统计的字符,合理的选择“替换后的值”,最好使替换前和替换后字符相减后值为“1”,当然也可以选择最终统计完之后除以差值。
示例:
SUMPRODUCT 函数
SUMPRODUCT函数返回相应范围或数组的个数之和。 默认操作是乘法,但也可以执行加减除运算。
本示例使用 SUMPRODUCT 返回给定项和大小的总销售额:
SUMPRODUCT 匹配项 Y/大小 M 的所有实例并求和,因此对于此示例,21 加 41 等于 62。
语法
若要使用默认操作 (乘法) :
=SUMPRODUCT (array1, [array2], [array3], …)
SUMPRODUCT 函数语法具有下列参数:
参数 | 说明 |
---|---|
array1 必需 | 其相应元素需要进行相乘并求和的第一个数组参数。 |
[array2], [array3],… 可选 | 2 到 255 个数组参数,其相应元素需要进行相乘并求和。 |
执行其他算术运算
像往常一样使用 SUMPRODUCT,但请将分隔数组参数的逗号替换为所需的算术运算符 (*、/、+、-) 。 执行所有操作后,结果将像往常一样进行求和。
注意: 如果使用算术运算符,请考虑将数组参数括在括号中,并使用括号对数组参数进行分组以控制算术运算的顺序。
备注
-
数组参数必须具有相同的维数。 否则,函数 SUMPRODUCT 将返回 #VALUE! 错误值 #REF!。 例如,=SUMPRODUCT (C2:C10,D2:D5) 将返回错误,因为范围的大小不同。
-
SUMPRODUCT 将非数值数组条目视为零。
-
为获得最佳性能,SUMPRODUCT 不应与完整列引用一同使用。 请考虑 =SUMPRODUCT (A:A,B:B) ,在此函数将 A 列中的 1,048,576 个单元格乘以 B 列中的 1,048,576 个单元格,然后再添加它们。
示例 1
若要使用上面的示例列表创建公式,请键入 =SUMPRODUCT (C2:C5,D2:D5) 并按 Enter。 列 C 中的每个单元格乘以 D 列中同一行中的对应单元格,结果将相加。 杂货的总量为 $78.97。
若要编写提供相同结果的较长公式,请键入 =C2D2+C3D3+C4D4+C5D5,然后按 Enter。 按 Enter 后,结果相同:$78.97。 单元格 C2 乘以 D2,其结果将添加到单元格 C3 乘以单元格 D3 的结果,以此类比。
示例 2
以下示例使用 SUMPRODUCT 按销售代理返回总净销售额,其中按代理计算总销售额和费用。 在这种情况下,我们将使用一个Excel表, 它使用结构化引用而不是标准引用Excel区域。 在这里,你将看到按名称引用"销售、支出"和"代理"范围。
公式为:=SUMPRODUCT ( ( (Table1[Sales]) + (Table1[Expenses]) ) * (Table1[Agent]=B8) ) ,它返回单元格 B8 中列出的代理的所有销售和费用的总和。
示例 3
此示例中,我们想要返回给定区域销售的特定商品的总数。 在这种情况下,东部区域销售了多少个水果?
此处的公式为:=SUMPRODUCT ( (B2:B9=B12) * (C2:C9=C12) *D2:D9) 。 它首先将"东部"的出现次数乘以匹配切分次数。 最后,对"销售额"列中相应行的值进行汇总。 若要了解如何Excel,请选择公式单元格,然后转到"公式">"公式>计算"。
( (B2:B9=B12) * (C2:C9=C12) *D2:D9) 。 它首先将"东部"的出现次数乘以匹配切分次数。 最后,对"销售额"列中相应行的值进行汇总。 若要了解如何Excel,请选择公式单元格,然后转到"公式">"公式>计算"。