Day15 统计神器函数

一、统计函数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列产品的个数。

(五) 一对多查询

(六) 数据有效性

©著作权归作者所有,转载或内容合作请联系作者
  • 序言:七十年代末,一起剥皮案震惊了整个滨河市,随后出现的几起案子,更是在滨河造成了极大的恐慌,老刑警刘岩,带你破解...
    沈念sama阅读 204,293评论 6 478
  • 序言:滨河连续发生了三起死亡事件,死亡现场离奇诡异,居然都是意外死亡,警方通过查阅死者的电脑和手机,发现死者居然都...
    沈念sama阅读 85,604评论 2 381
  • 文/潘晓璐 我一进店门,熙熙楼的掌柜王于贵愁眉苦脸地迎上来,“玉大人,你说我怎么就摊上这事。” “怎么了?”我有些...
    开封第一讲书人阅读 150,958评论 0 337
  • 文/不坏的土叔 我叫张陵,是天一观的道长。 经常有香客问我,道长,这世上最难降的妖魔是什么? 我笑而不...
    开封第一讲书人阅读 54,729评论 1 277
  • 正文 为了忘掉前任,我火速办了婚礼,结果婚礼上,老公的妹妹穿的比我还像新娘。我一直安慰自己,他们只是感情好,可当我...
    茶点故事阅读 63,719评论 5 366
  • 文/花漫 我一把揭开白布。 她就那样静静地躺着,像睡着了一般。 火红的嫁衣衬着肌肤如雪。 梳的纹丝不乱的头发上,一...
    开封第一讲书人阅读 48,630评论 1 281
  • 那天,我揣着相机与录音,去河边找鬼。 笑死,一个胖子当着我的面吹牛,可吹牛的内容都是我干的。 我是一名探鬼主播,决...
    沈念sama阅读 38,000评论 3 397
  • 文/苍兰香墨 我猛地睁开眼,长吁一口气:“原来是场噩梦啊……” “哼!你这毒妇竟也来了?” 一声冷哼从身侧响起,我...
    开封第一讲书人阅读 36,665评论 0 258
  • 序言:老挝万荣一对情侣失踪,失踪者是张志新(化名)和其女友刘颖,没想到半个月后,有当地人在树林里发现了一具尸体,经...
    沈念sama阅读 40,909评论 1 299
  • 正文 独居荒郊野岭守林人离奇死亡,尸身上长有42处带血的脓包…… 初始之章·张勋 以下内容为张勋视角 年9月15日...
    茶点故事阅读 35,646评论 2 321
  • 正文 我和宋清朗相恋三年,在试婚纱的时候发现自己被绿了。 大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
    茶点故事阅读 37,726评论 1 330
  • 序言:一个原本活蹦乱跳的男人离奇死亡,死状恐怖,灵堂内的尸体忽然破棺而出,到底是诈尸还是另有隐情,我是刑警宁泽,带...
    沈念sama阅读 33,400评论 4 321
  • 正文 年R本政府宣布,位于F岛的核电站,受9级特大地震影响,放射性物质发生泄漏。R本人自食恶果不足惜,却给世界环境...
    茶点故事阅读 38,986评论 3 307
  • 文/蒙蒙 一、第九天 我趴在偏房一处隐蔽的房顶上张望。 院中可真热闹,春花似锦、人声如沸。这庄子的主人今日做“春日...
    开封第一讲书人阅读 29,959评论 0 19
  • 文/苍兰香墨 我抬头看了看天上的太阳。三九已至,却和暖如春,着一层夹袄步出监牢的瞬间,已是汗流浃背。 一阵脚步声响...
    开封第一讲书人阅读 31,197评论 1 260
  • 我被黑心中介骗来泰国打工, 没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留,地道东北人。 一个月前我还...
    沈念sama阅读 44,996评论 2 349
  • 正文 我出身青楼,却偏偏与公主长得像,于是被迫代替她去往敌国和亲。 传闻我的和亲对象是个残疾皇子,可洞房花烛夜当晚...
    茶点故事阅读 42,481评论 2 342

推荐阅读更多精彩内容

  • 做为互联网的一个工作人员,尤其是运营岗位,一天工作时间最多触碰的工具就是excel了,比如数据整理、分析、设计报表...
    韩利阅读 12,910评论 9 203
  • 按照用途分类出以下统计函数: AVEDEV 用途:返回一组数据与其平均值的绝对偏差的平均值,该函数可以评测数据(例...
    四方院祭司阅读 2,865评论 0 3
  • 在找工作的时候,大家很随意的就在简历上写这么一句“熟练使用Excel办公软件”,可能有些人的确是熟练使用,但罂粟姐...
    罂粟姐姐阅读 48,185评论 122 1,805
  • 本篇文章将介绍20个常用的EXCEL函数,请配合简介进行阅读 简介 SUM函数计算所有参数数值的和 ABS函数求出...
    HoPGoldy阅读 943评论 0 0
  • 学到了第15天,收获最大的除了认识了很多excel新面孔外,还有就是已经可以在百度准确想要搜索的内容了,再碰到问题...
    lu_bd58阅读 247评论 0 0