统计计算常用excel技巧 轻松搞定统计分析的Excel函数公式实用技巧解读

统计分析,在Excel中是非常常见的,但是如何有效、高效的统计分析,却是每个伙伴犯难的事情……今天,小编给大家整理了一些常见的统计分析用的函数公式,希望对伙伴们的学习工作有所帮助。


一、身份证号码类。

(一)提取性别。

统计计算常用excel技巧 轻松搞定统计分析的Excel函数公式实用技巧解读(1)

方法:

在目标单元格中输入公式:=IF(MOD(MID(C3,17,1),2),"男","女")。

解读:

1、身份证号码共计18位,其中第17位代表性别,如果除以2余数为1,则为“男”性,如果余数为0,则为“女”性。所以首先用Mid函数提取第17位数值。

2、Mod函数为求余函数,其语法结构为:=Mod(被除数,除数)。被除数÷除数的结果,即商为Mod函数的结果。

3、用Mod函数计算出结果之后,利用IF函数判断,如果余数为1,则返回“男”,如果余数为0,则返回女。


(二)提取出生年月

统计计算常用excel技巧 轻松搞定统计分析的Excel函数公式实用技巧解读(2)

方法:

在目标单元格中输入公式:=TEXT(MID(C3,7,8),"0!/00!/00")。

解读:

1、身份证号码中的第7位至14位(长度为8)为出生年月,所以用Mid函数提取。

2、用MId函数提取的仅为一串数字,需要对其“美化”,所以用Text函数对其设置格式。


(三)计算年龄

统计计算常用excel技巧 轻松搞定统计分析的Excel函数公式实用技巧解读(3)

方法:

在目标单元格中输入公式:=DATEDIF(E3,TODAY(),"y")、=DATEDIF(TEXT(MID(C7,7,8),"0!/00!/00"),TODAY(),"y")。

解读:

1、年龄就是当前年份减去出生年份,而在Excel函数中,Datedif函数就是按照指定的类型返回两个日期之间的间隔数。其语法结构为=Datedif(开始日期,结束日期,统计方式)。常见的统计方式有“Y”、“M”、“D”;分别为“年”、“月”、“日”。

2、如果在现有的数据中已经有出生年月,则用公式=DATEDIF(E3,TODAY(),"y")实现,否则要从身份证号码中提取出生年月,则用公式=DATEDIF(TEXT(MID(C7,7,8),"0!/00!/00"),TODAY(),"y")。实现。


二、常用汇总类。

(一)求和类

1、单条件求和

统计计算常用excel技巧 轻松搞定统计分析的Excel函数公式实用技巧解读(4)

方法:

在目标单元格中输入公式:=SUMIF(C3:C9,H3,D3:D9)、=SUMIF(C3:C9,H3,E3:E9)。

解读:

1、从示例中可以看出目的为:按性别统计“总销量”和“总销售额”,暨分别计算“男”、“女”销售员的总销量和总销售额。

2、Sumif函数为单条件求和函数,语法结构为:=Sumif(条件范围,条件,求和范围)。


2、多条件求和。

统计计算常用excel技巧 轻松搞定统计分析的Excel函数公式实用技巧解读(5)

方法:

在目标单元格中输入公式:=SUMIFS(D3:D9,C3:C9,H3,D3:D9,">"&I3)、=SUMIFS(E3:E9,C3:C9,H3,D3:D9,">"&I3)。

解读:

多条件求和函数和单条件求和函数类似,为Sumifs函数,语法结构为:=Sumifs(求和范围,条件1范围,条件1,条件2范围,条件2……条件N范围,条件N)。


(二)最值类

统计计算常用excel技巧 轻松搞定统计分析的Excel函数公式实用技巧解读(6)

方法:

在目标单元格中输入公式:=MAXIFS(D3:D9,C3:C9,H3)、=MINIFS(D3:D9,C3:C9,H3)。

解读:

1、如果没有附加条件,求最大值或最小值,可以使用Max函数或Min函数。

2、如果有附加条件,求最大值或最小值,则必须使用Maxifs函数或Minifs函数。其语法结构是相同的,暨=函数名(数值范围,条件区域1,条件1,条件区域2,条件2,……,条件区域N,条件N)。


(三)平均值类。

统计计算常用excel技巧 轻松搞定统计分析的Excel函数公式实用技巧解读(7)

方法:

在目标单元格中输入公式:=AVERAGE(D3:D9)、=AVERAGEIF(C3:C9,H5,D3:D9)、=AVERAGEIFS(E3:E9,C3:C9,H5)。

解读:

1、计算平均值的函数可以分为两类,普通类(Average)和条件类(Averageif、Averageifs)。

2、Averageif函数为单条件求平均值,语法结构为:=Averageif(条件范围,条件,数值范围);Averageifs函数为多条件求平均值,语法结构为:=Averageifs(数值范围,条件1范围,条件1,条件2范围,条件2……条件N范围,条件N)。


(四)个数统计类。

1、一般个数统计。

统计计算常用excel技巧 轻松搞定统计分析的Excel函数公式实用技巧解读(8)

方法:

在目标单元格中输入公式:=COUNTA(B3:B9)、=COUNTBLANK(B3:B9)、=COUNTA(D3:D9)、=COUNTIF(D3:D9,">5000")、=COUNTIFS(C3:C9,"男",D3:D9,">5000")。

解读:

文本个数类统计:Counta函数;空单元格个数统计:Countblank函数;数值类个数统计:Count函数;单条件计数函数:Countif;多条件计数函数:Countifs函数。


2、分段统计。

统计计算常用excel技巧 轻松搞定统计分析的Excel函数公式实用技巧解读(9)

方法:

1、在目标单元格中输入公式:=FREQUENCY(D3:D9,H3:H9)。

2、Ctrl Shift Enter填充。

解读:

1、函数Frequency的功能为:计算数值在指定区域内出现的频次。语法结构为:=Frequency(数据范围,统计值范围)。

2、公式=FREQUENCY(D3:D9,H3:H9)的意思为:≤2000的数为0;2001-3000之间的数为2;3001-4000之间的数为0;4001至5000之间的数为1;5001至6000之间的数为1;6001至7000之间的数为2;7001至8000之间的数为1。


结束语:

本文从实际出发,解读了常用的统计类函数,具有很高的实用价值,如果亲有不同的见解或看法,欢迎在留言区留言讨论哦!

,

免责声明:本文仅代表文章作者的个人观点,与本站无关。其原创性、真实性以及文中陈述文字和内容未经本站证实,对本文以及其中全部或者部分内容文字的真实性、完整性和原创性本站不作任何保证或承诺,请读者仅作参考,并自行核实相关内容。文章投诉邮箱:anhduc.ph@yahoo.com

    分享
    投诉
    首页