Excel 高级函数汇总—大数据处理必备最常用的统计软件非SPSS、Stata 莫属了,但它们各有不足。SPSS 的数据录入比较繁琐,能完成的统计功能局限在已经设计好的“分析” 模块里。SPSS 的作图功能也是不敢恭维。Stata 轻巧便捷,然而需要自己输入命令,新手上手需要一个过渡期。以上两个软件的计算功能也称不上便捷。
其实,excel 就可以承担起相当一部分计算、数据录入、数据筛选,甚至是统计、作图的功能。利用excel 强大的“函数”功能,可以实现诸如计算两个指定日期相差多少天、计数符合某条件的单元格有多少个、在复杂的病理报告中自动选出含有某个免疫组化阳性的蛋白、按特定需求查单元格内容等任务。
excel 的函数分为以下几大类,分别是计算类(如求绝对值、余数)、统计类(如求和、求标准差、t 检验)、时间类、条件语句(如果⋯则⋯)、逻辑类(返回真/假,包括和/或/非/ 异或等)、等级排序类、计数单元格个数类、文本类(如查、替换文本中的某字符串)、查类(如查某内容的单元格的位置)等。利用这些函数的组合可以实现多种功能,且直接记录在excel 表格中,无需像SPSS 等软件还要转抄结果。
1.计算(针对数值)
余数=MOD
绝对值=ABS ,高斯取整(向下取整)=INT 截断取整=TRUNC(数
值, 保留小数位数)
【保留的小数位数中,0 表示保留到个位数,-1 表示保留到十位数,依次类推】
四舍五入=ROUND(数值,保留小数位数)
乘方=POWER(数值,幂),开方=SQRT 或=数值^(1/2)
e 的乘方=EXP ,自然对数=LN
对数=LOG(数值,底),10 为底对数=LOG10
阶乘=FACT
随机数(≥0,<1)=RAND ,随机数(任意范围)=RANDBETWEEN
(较小数, 较大数)
最大公约数=GCD(数值1,数值2,⋯),最小公倍数=LCM(数值1, 数值2,⋯)
2.统计(针对数据区)
【“数组” 用{},如{2,3,5,8;1,4,7} ,半角的分号表示换行,
逗号表示换列】
求和(相加)=SUM ,相乘=PRODUCT
求算术平均数= AVERAGE ,求几何平均数=GEOMEAN ,求调和平均数
=HARMEAN
求方差=VAR 【除以自由度n-1 】,=VARP 【除以样本量n 】
求标准差=STDEV 【除以自由度n-1 】,=STDEVP 【除以样本量n 】
求平均差(每个数值与算术平均数的差的绝对值的算术平均数) =AVEDEV
求最大值 =MAX ,求最小值 =MIN
求排名第 n 的数值是多少 =LARGE( 数据区 ,n) 【算法是把选定数值排成一排,再选出第 n 个数,因此
10,9,9,8,7 在n 为 3 时返回 9】
求排名倒数第 n 的数值是多少
=SMALL( 数据区 ,n) 求中位数 =MEDIAN ,求众数(较小的) =MODE 求四分位数
=
QUARTILE ( 数据区 ,k ) 【 k 取 0~4 , 最小值 0,较小四分位数 1 ,中位数 2 ,较大四分位数 3
, 最大值
4 】 求百分位数 = PERCENTILE ( 数据
区 ,k )
【 k 取 0~1 , 若 k 不是 1/(n-1) 的倍数, 函数使用插值法来确定第 k 个
百
分位数的值,如 PERCENTILE({1,2,3,4},0.3) 为 1.9 】 求每个数值与均值的差的平方和 =DEVSQ 求偏斜度 =SKEW ,求峰值 =KURT
2.1. 在某条件下求和
当数据区 1 满足某条件下求数据区 2 的对应区域的和: =SUMIF( 数据区
1, 条件 ,数据区 2)
【条件可以是 "男"、A2 ,数据区 2 必须和数据区 1 对应,若无数据区 2 默认对满足条件的数据区 1 求和】
3.逻辑算符
【单独使用返回TRUE/FALSE ,可用于IF 函数】
同时满足n 个条件为真:=AND(条件1, 条件2,⋯)满足其中一个条件即为真:=OR(条件1, 条件2,⋯)
两条件真假不同则为真(异或):=XOR(条件1,条件2, ⋯)不满足条件为真:=NOT(条件)
单元格A1 与A2 相同(不区分大小写):=A1=A2
单元格A1 与A2 相同(区分大小写):=EXACT(A1,A2)
4.条件语句
=IF(条件,若真则如何,若假则如何)【返回的可以是单元格内容、文本、数值,甚至是区域】【条件可嵌套逻辑算符】
当选定单元格或结果出现错误(ERROR )时如何=IFERROR(单元格或计
算式, 出现错误会如何)
【例=IFERROR(A2, " 哈哈"),=IFERROR(1+A2, B3)】
【错误类型包括#N/A 、#VALUE! 、#REF! 、#DIV/0! 、#NUM! 、
#NAME? 或#NULL! 】
5.等级排序
求D3 数值在选定数值的排位(降序排):=RANK(D3, 数据区,0)
【重复数值返回较小排位,9 在10,9,9,8,7,6 中排第 2 】
求D3 数值在选定数值的排位(升序排):=RANK(D3, 数据区,1)重复数值返回较小排位,9 在10,9,9,8,7,6 中排第 4 】
【数据区后为0 降序排,数据区后为 1 升序排】
6.求各种类型的单元格的个数
【 3 个函数COUNT 是计数数值类型的单元格个数,COUNTA 是计数各种类型的单元格个数,COUNTIF 是计数在某条件下的单元格个数——各种函数后的IF 都是指在某条件下,如SUMIF 】
【任何函数后面加“ A ”表示不忽略非数值型内容,如COUNTA 、MAXA 、MINA ,文本型、真空、FALSE 为0 ,TRUE 为 1 】
真空单元格个数:=COUNTIF(数据区,"=")
【数据区A:G 为 A 列到G 列所有单元格,19:21 为19 行到21 行所有单元格】
非空单元格个数:=COUNTIF(数据区,"<>")文本型单元格个数:=COUNTIF(数据区,"*")非空文本型单元格个数:=COUNTIF(数据区,">=!")所有单元格个数:=COUNTIF(数据区," <>""")【单元格含有"时公式不成立】
逻辑值为TRUE 的单元格个数:=COUNTIF(数据区,TRUE)
等于50 的单元格个数:=COUNTIF(数据区,50)不等于50 的单元格个数:=COUNTIF(数据区,"<>50")大于50 的单元格个数:excel的随机数函数
版权声明:本站内容均来自互联网,仅供演示用,请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198,我们将在24小时内删除。
发表评论