一、统计函数Subtotal
(一) 基本用法 Subtotal
共有11个统计功能,比较常用有两个功能:3 count非空单元格数量,9 求和,筛选后求和。
TIPS: ·在单元格输入=Subtotal( ,就会提示上述语法 ·此函数并不是“一个函数”,而是“一群函数” ·此函数是Excel中唯一一个能统计用户可见单元格的函数
(二) Subtotal隐藏值 =SUBTOTAL(9,B2:B8):9-SUM.S(含隐藏值)如果单元格被隐藏了,合计的时候会将其计算在内。 =SUBTOTAL(109,B2:B8):109-SUM.S(不含隐藏值),与上相反,被隐藏了就不会补统计。 以上两个有一个共性,如果筛选单元格,会自动求和筛选数据的值。
(三) Subtotal与Sum的区别 Subtotal与Sum有共同的求和功能,但又有以下不同。此功能在核算销售业绩的时候非常好用,用sum求出来的是总计金额,想看某个销售的业绩,还得自己去选择单元格,十分不方便,用了subtotal去求和,大大提高了工作效率。 注意: SUBTOTAL,只对筛选数据结果数据进行求和 SUM,不受筛选结果影响,会对所有数据进行求和
(四) Subtotal计数 =SUBTOTAL(2,B2:B8) 统计个数(2-COUNT) =SUBTOTAL(3,B2:B8) 非空单元格数量(3-COUNTA)
(五) 筛选后填充 =SUBTOTAL(3,$B$2:B2),往下拖动会生成连续的序号,如果筛选序号会进行相应的变化。 注意:SUBTOTAL筛选的最后一行,默认为统计行,会将第一种产品筛选出来,如果要纠正,=SUBTOTAL(3,$B$2:B2)*1,这样就能保证数据的准确性。
(六) 避开同类计算 如果希望统计以下表格中的统计行,在合计栏输入=SUBTOTAL(9,B2:B9),往右拉,此函数会跳过同类函数,只统计小计行的金额。
二、统计函数(Countif、Countifs)
(一) 基本用法 Count: 计算参数列表中的数字项的个数 CountA: 计算参数列表中非空的项个数 Countblank: 计算参数列表中空单元格的个数
1、COUNT:计算区域中数字的单元格个数。
①如果参数为数字、日期或者代表数字的文本,则将被计算在内;
②逻辑值和直接键入到参数列表中代表数字的文本被计算在内;
③如果参数为错误值或不能转换为数字的文本,则不会被计算在内;
④如果参数是一个数组或引用,则只计算其中的数字。数组或引用中的空白单元格、逻辑值、文本或错误值将不计算在内。
2、COUNTA:计算区域中非空单元格的个数。
①参数值可以是任何类型,可以包括空字符(""),但不包括空白单元格;
②如果参数是数组或单元格引用,则数组或引用中的空白单元格将被忽略;
③如果不需要统计逻辑值、文字或错误值,请使用函数COUNT。
3、COUNTBLANK:计算区域中空单元格的个数。
①包含返回 ""(空文本)的公式的单元格会计算在内;
②包含零值的单元格不计算在内。
Countif使用,=COUNTIF(明细表!C:C,A2)前面是判断区域,后面是计数的条件。
注意: 1、COUNTIF函数的第一参数绝对引用,是为了公式向下填充时,保持引用范围不变; 2、COUNTIF函数的第二参数直接使用相对引用待统计单元格,公式向下填充时,D2依次变为D3、D4…… CountifS使用 COUNTIFS函数将条件应用于跨多个区域的单元格,并计算符合所有条件的次数。即多条件计数。 COUNTIFS(criteria_range1,criteria1,criteria_range2,criteria2,…) COUNTIFS(条件区域1,条件1,条件区域2,条件2…) 条件区域1:必需。在其中计算关联条件的第一个区域。 条件1:必需。要进行计数的第一个条件。 条件区域2:可选。在其中计算关联条件的第二个区域。 条件2:可选。要进行计数的第二个条件。
(二) 模糊条件计数 *通配符,可以通过*去统计个数。
(三) 文本 非空 真空数据个数 注意: * 代替任意多个字符 <> 代表非空数据 = 代表真空数据 说明 公式 统计文本单元格个数 2 =COUNTIF($A$2:$A$10,"*") 统计非空数据个数 7 =COUNTIF($A$2:$A$10,"<>") 统计真空数据个数 2 =COUNTIF($A$2:$A$10,"=")
(四) 按产品统计序号 如A、B、C、D,需要按产品去统计: =COUNTIF($C$2:C2,C2),用此公式就可以按序号排列。 =C2&COUNTIF($C$2:C2,C2) COUNTIF函数的统计区域是$C$2:C2,第一个C2是行绝对引用,第二个C2,是相对引用。 当公式向下复制时,就会变成$C$2:C3、$C$2:C4……一个不断扩展的区域,从这个动态区域中统计C列产品的个数。
(五) 一对多查询
(六) 数据有效性