EXCEL函数进阶教程

Excel函数应用之统计函数（***2001年06月26日09:47）编者语：Excel是办公室自动化中非常重要的一款软件，很多巨型国际企业都是依靠Excel进行数据管理。它不仅仅能够方便的处理表格和进行图形分析，其更强大的功能体现在对数据的自动处理和计算，然而很多缺少理工科背景或是对Excel强大数据处理功能不了解的人却难以进一步深入。编者以为，对Excel函数应用的不了解正是阻挡普通用户完全掌握Excel的拦路虎，然而目前这一部份内容的教学文章却又很少见，所以特别组织了这一个《Excel函数应用》系列，希望能够对Excel进阶者有所帮助。《Excel函数应用》系列，将每周更新，逐步系统的介绍Excel各类函数及其应用，敬请关注！Excel的统计工作表函数用于对数据区域进行统计分析。例如，统计工作表函数可以用来统计样本的方差、数据区间的频率分布等。是不是觉得好像是很专业范畴的东西？是的，统计工作表函数中提供了很多属于统计学范畴的函数，但也有些函数其实在你我的日常生活中是很常用的，比如求班级平均成绩，排名等。在本文中，主要介绍一些常见的统计函数，而属于统计学范畴的函数不在此赘述，详细的使用方法可以参考Excel帮助及相关的书籍。在介绍统计函数之前，请大家先看一下附表中的函数名称。是不是发现有些函数是很类似的，只是在名称中多了一个字母A？比如，AVERAGE与AVERAGEA；COUNT与COUNTA。基本上，名称中带A的函数在统计时不仅统计数字，而且文本和逻辑值（如TRUE和FALSE）也将计算在内。在下文中笔者将主要介绍不带A的几种常见函数的用法。一、用于求平均值的统计函数AVERAGE、TRIMMEAN1、求参数的算术平均值函数AVERAGE语法形式为AVERAGE(number1,number2,...)其中Number1,number2,...为要计算平均值的1～30个参数。这些参数可以是数字，或者是涉及数字的名称、数组或引用。如果数组或单元格引用参数中有文字、逻辑值或空单元格，则忽略其值。但是，如果单元格包含零值则计算在内。2、求数据集的内部平均值TRIMMEAN函数TRIMMEAN先从数据集的头部和尾部除去一定百分比的数据点，然后再求平均值。当希望在分析中剔除一部分数据的计算时，可以使用此函数。比如，我们在计算选手平均分数中常用去掉一个最高分，去掉一个最低分，XX号选手的最后得分，就可以使用该函数来计算。语法形式为TRIMMEAN(array,percent)其中Array为需要进行筛选并求平均值的数组或数据区域。Percent为计算时所要除去的数据点的比例，例如，如果percent=0.2，在20个数据点的集合中，就要除去4个数据点（20x0.2），头部除去2个，尾部除去2个。函数TRIMMEAN将除去的数据点数目向下舍为最接近的2的倍数。3、举例说明：示例中也列举了带A的函数AVERAGEA的求解方法。求选手Annie的参赛分数。在这里，我们先假定已经将该选手的分数进行了从高到底的排序，在后面的介绍中我们将详细了解排序的方法。图1二、用于求单元格个数的统计函数COUNT语法形式为COUNT(value1,value2,...)其中Value1,value2,...为包含或引用各种类型数据的参数（1～30个），但只有数字类型的数据才被计数。函数COUNT在计数时，将把数字、空值、逻辑值、日期或以文字代表的数计算进去；但是错误值或其他无法转化成数字的文字则被忽略。如果参数是一个数组或引用，那么只统计数组或引用中的数字；数组中或引用的空单元格、逻辑值、文字或错误值都将忽略。如果要统计逻辑值、文字或错误值，应当使用函数COUNTA。举例说明COUNT函数的用途，示例中也列举了带A的函数COUNTA的用途。仍以上例为例，要计算一共有多少评委参与评分（用函数COUNTA），以及有几个评委给出了有效分数（用函数COUNT）。图2三、求区域中数据的频率分布FREQUENCY由于函数FREQUENCY返回一个数组，必须以数组公式的形式输入。语法形式为FREQUENCY(data_array,bins_array)其中Data_array为一数组或对一组数值的引用，用来计算频率。如果data_array中不包含任何数值，函数FREQUENCY返回零数组。Bins_array为一数组或对数组区域的引用，设定对data_array进行频率计算的分段点。如果bins_array中不包含任何数值，函数FREQUENCY返回data_array元素的数目。看起来FREQUENCY的用法蛮复杂的，但其用处很大。比如可以计算不同工资段的人员分布，公司员工的年龄分布，学生成绩的分布情况等。这里以具体示例说明其基本的用法。以计算某公司的员工年龄分布情况为例说明。在工作表里列出了员工的年龄。这些年龄为28、25、31、21、44、33、22和35，并分别输入到单元格C4:C11。这一列年龄就是data_array。Bins_array是另一列用来对年龄分组的区间值。在本例中，bins_array是指C13:C16单元格，分别含有值25、30、35、和40。以数组形式输入函数FREQUENCY，就可以计算出年龄在25岁以下、26~30岁、31~35岁、36~40岁和40岁以上各区间中的数目。本例中选择了5个垂直相邻的单元格后，即以数组公式输入下面的公式。返回的数组中的元素个数比bins_array（数组）中的元素个数多1。第五个数字1表示大于最高间隔(40)的数值（44）的个数。函数FREQUENCY忽略空白单元格和文本值。{=FREQUENCY(C4:C11,C13:C16)}等于{2;2;2;1;1}图3四、一组用于求数据集的满足不同要求的数值的函数1、求数据集的最大值MAX与最小值MIN这两个函数MAX、MIN就是用来求解数据集的极值（即最大值、最小值）。函数的用法非常简单。语法形式为函数（number1,number2,...），其中Number1,number2,...为需要找出最大数值的1到30个数值。如果要计算数组或引用中的空白单元格、逻辑值或文本将被忽略。因此如果逻辑值和文本不能忽略，请使用带A的函数MAXA或者MINA来代替。2、求数据集中第K个最大值LARGE与第k个最小值SMALL这两个函数LARGE、SMALL与MAX、MIN非常想像，区别在于它们返回的不是极值，而是第K个值。语法形式为：函数(array,k)，其中Array为需要找到第k个最小值的数组或数字型数据区域。K为返回的数据在数组或数据区域里的位置（如果是LARGE为从大到小排，若为SMALL函数则从小到大排）。说到这，大家可以想得到吧。如果K=1或者K=n（假定数据集中有n个数据）的时候，是不是就可以返回数据集的最大值或者最小值了呢。3、求数据集中的中位数MEDIANMEDIAN函数返回给定数值集合的中位数。所谓中位数是指在一组数据中居于中间的数，换句话说，在这组数据中，有一半的数据比它大，有一半的数据比它小。语法形式为MEDIAN(number1,number2,...)其中Number1,number2,...是需要找出中位数的1到30个数字参数。如果数组或引用参数中包含有文字、逻辑值或空白单元格，则忽略这些值，但是其值为零的单元格会计算在内。需要注意的是，如果参数集合中包含有偶数个数字，函数MEDIAN将返回位于中间的两个数的平均值。4、求数据集中出现频率最多的数MODEMODE函数用来返回在某一数组或数据区域中出现频率最多的数值。跟MEDIAN一样，MODE也是一个位置测量函数。语法形式为MODE(number1,number2,...)其中Number1,number2,...是用于众数（众数指在一组数值中出现频率最高的数值）计算的1到30个参数，也可以使用单一数组（即对数组区域的引用）来代替由逗号分隔的参数。5、以上函数的示例以某单位年终奖金分配表为例说明。在示例中，我们将利用这些函数求解该单位年终奖金分配中的最高金额、最低金额、平均金额、中间金额、众数金额以及第二高金额等。详细的公式写法可从图中清楚的看出，在此不再赘述。图4五、用来排位的函数RANK、PERCENTRANK1、一个数值在一组数值中的排位的函数RANK数值的排位是与数据清单中其他数值的相对大小，当然如果数据清单已经排过序了，则数值的排位就是它当前的位置。数据清单的排序可以使用Excel提供的排序功能完成。语法形式为RANK(number,ref,order)其中Number为需要找到排位的数字；Ref为包含一组数字的数组或引用。Order为一数字用来指明排位的方式。如果order为0或省略，则Excel将ref当作按降序排列的数据清单进行排位。如果order不为零，MicrosoftExcel将ref当作按升序排列的数据清单进行排位。需要说明的是，函数RANK对重复数的排位相同。但重复数的存在将影响后续数值的排位。嗯，这就好像并列第几的概念啊。例如，在一列整数里，如果整数10出现两次，其排位为5，则11的排位为7（没有排位为6的数值）。2、求特定数值在一个数据集中的百分比排位的函数PERCENTRANK此PERCENTRANK函数可用于查看特定数据在数据集中所处的位置。例如，可以使用函数PERCENTRANK计算某个特定的能力测试得分在所有的能力测试得分中的位置。语法形式为PERCENTRANK(array,x,significance)其中Array为彼此间相对位置确定的数字数组或数字区域。X为数组中需要得到其排位的值。Significance为可选项，表示返回的百分数值的有效位数。如果省略，函数PERCENTRANK保留3位小数。3、与排名有关的示例仍以某单位的年终奖金分配为例说明，这里以员工Annie的排名为例说明公式的写法。奖金排名的公式写法为：=RANK(C3,$C$3:$C$12)百分比排名的公式写法为：=PERCENTRANK($C$3:$C$12,C3)图5以上我们介绍了Excel统计函数中比较常用的几种函数，更多的涉及专业领域的统计函数可以参看附表以及各种相关的统计学书籍。附表：蜻函数名称滚函数说明野语法形式号AVEDEV挨返回一组数据踏与其均值的绝晌对偏差的平均决值，即离散度妨。谅AVEDEV罢(numbe丑r1,num谎ber2,堪...)挥AVERAG页E蓄返回参数算术折平均值。扮AVERAG聋E(numb贷er1,nu羽mber2,脏...)棵AVERAG刑EA访计算参数清单附中数值的平均兴值（算数平均酷值）。不仅数卡字，而且文本责和逻辑值（暮如福TRUE续跳和园FALS夫E堂）也将计算在巡内。亏AVERAG哭EA(val争ue1,va劈lue2,.否..)蓝BETADI肢ST务返即回样Beta推壳分布累积函数勤的函数值率。度Beta击故分布累积函数罪通常用于研究狭样本集合中某煌些事物的发生蜜和变化情况。大BETADI堂ST(x,a崇lpha,b恭eta,A,思B)早BETAIN缘V嚷返溜回面beta狐绩分布累积函数乎的逆函数值。万即，如哭果更proba鼓bility柳=BET壮ADIST(朋x,...梳)隶，图则急BETAI销NV(pro彻babili算ty,...痕)=攻x匆。顶beta杏狗分布累积函数被可用于项目设摘计，在给定期伸望的完成时间折和变化参数后烈，模拟可能的沉完成时间。倡BETAIN穷V(prob附abilit消y,alph犁a,beta浑,A,B)刻BINOMD宫IST洋返回一元二项剑式分布的概率耳值。冷BINOMD欧IST(nu巧mber_s娱,trial湾s,prob侧abilit搏y_s,cu轻mulati耳ve)厚CHIDIS纳T逢返惕回博脱γ蜘2展伶分布的单尾概诉率锤。深γ坟2信称分布坏与阳均γ阵2蓝裤检验相关。使逢用捆叠γ悟2壁粗检验可以比较曾观察值和期望懂值。倍CHIDIS式T(x,de拉grees_另freedo伸m)基CHIINV就返衰回篇纵γ珠2番液分布单尾概率石的逆函数。浪CHIINV男(proba疑bility继,degre朗es_fre重edom)抗CHITES华T亮返回独立性检祝验值。函奉数饭CHITE缠ST扯么返静回译羽γ广2刻晓分布的统计值疏及相应的自由负度。敞CHITES轻T(actu扩al_ran洒ge,exp热ected_粘range)嘉CONFID歉ENCE旷返回总体平均巾值的置信区间趋。置信区间是竖样本平均值任足意一侧的区域棚。摔CONFID气ENCE(a竖lpha,s令tandar音d_dev,践size)虽CORREL慢返回单元格区同域聪array蚀1咐为和句array院2蔽挪之间的相关系睡数。使用相关能系数可以确定坊两种属性之间疤的关系。梳CORREL浇(array第1,arra旺y2)驾COUNT坝返回参数的个请数。利用函完数奏COUNT窃年可以计算数组筝或单元格区域熄中数字项的个否数。桨COUNT(岛value1锁,value筒2,...菠)牛COUNTA傻返回参数组中馅非空值的数目筒。利用函受数置COUNTA习帐可以计算数组塘或单元格区域味中数据项的个投数。拾COUNTA努(value献1,valu街e2,..踪.)销COVAR去返回协方差，悬即每对数据点冠的偏差乘积的娘平均数，利用造协方差可以决斯定两个数据集统之间的关系。吹COVAR(乓array1珍,array厌2)炉CRITBI构NOM您返回使累积二讯项式分布大于武等于临界值的来最小值。此函姨数可以用于质坚量检验。托CRITBI摇NOM(tr费ials,p绢robabi疗lity_s献,alpha灌)央DEVSQ线返回数据点与借各自样本均值索偏差的平方和役。屯DEVSQ(吧number躲1,numb援er2,..明.)稻EXPOND读IST泳返回指数分布从。使用函脂数楚EXPON堡DIST盒汽可以建立事件弯之间的时间间科隔模型。寇EXPOND很IST(x,做lambda茎,cumul矩ative)哥FDIST祖返赔回倦F隶御概率分布。使办用此函数可以册确定两个数据判系列是否存在撕变化程度上的垫不同。阀FDIST(燥x,degr哀ees_fr蚕eedom1新,degre象es_fre循edom2)换FINV厘返列回铲F级笋概率分布的逆昆函数值。球FINV(p辽robabi龄lity,d腐egrees轿_freed银om1,de剩grees_蜡freedo傲m2)揪FISHER而返回耀点亦x哪韵的左Fishe迈r罢端变换。该变换遵生成一个近似缩正态分布而非幸偏斜的函数。品FISHER鹿(x)皇FISHER韵INV久返夹回忘Fishe番r锐痕变换的逆函数采值。使用此变灰换可以分析数屿据区域或数组师之间的相关性稀。厅FISHER患INV(y)难FORECA睛ST巩根据给定的数氏据计算或预测累未来值。贡FORECA薄ST(x,k栗nown_y天's,kno捆wn_x's连)纵FREQUE冠NCY掩以一列垂直数轻组返回某个区稻域中数据的频严率分布。猜FREQUE考NCY(da瓜ta_arr街ay,bin取s_arra池y)僻FTEST很返左回烈F竭创检验的结果护。蚁F者步检验返回的是租当数叫组没1俊秧和数雷组罗2过残的方差无明显消差异时的单尾曲概率。可以使灰用此函数来判殊断两个样本的肥方差是否不同穿。醋FTEST(蚊array1味,array缓2)腾GAMMAD掩IST兼返回伽玛分布章。可以使用此食函数来研究具淡有偏态分布的撤变量。伽玛分惧布通常用于排辣队分析。决GAMMAD叨IST(x,叙alpha,漆beta,c跟umulat珍ive)借GAMMAI括NV致返回伽玛分布刘的累积函数的栋逆函数。柔GAMMAI滥NV(pro笑babili修ty,alp付ha,bet换a)槽GAMMAL友N甚返回伽玛函数饼的自然对数丹，紫Γ没(x付)现。端GAMMAL眨N(x)架GEOMEA概N路返回正数数组莲或数据区域的突几何平均值。袍GEOMEA沙N(numb哨er1,nu毁mber2,顾...)嫂GROWTH微根据给定的数胸据预测指数增泽长值。担GROWTH饲(known原_y's,k的nown_x上's,new冲_x's,c备onst)鱼HARMEA瑞N叫返回数据集合因的调和平均值柔。调和平均值详与倒数的算术盈平均值互为倒敏数。勉HARMEA累N(numb病er1,nu鞭mber2,舅...)滨HYPGEO窑MDIST子返回超几何分脊布。笔HYPGEO慈MDIST(她sample探_s,num恰ber_sa闭mple,

解popula称tion_s亡,numbe积r_popu段lation袭)成INTERC介EPT通利用已知闸的亭x旋内值柄与无y装似值计算直线亡与笋y旦勤轴的截距。怎INTERC夸EPT(kn扛own_y'睁s,know运n_x's)觉KURT绣返回数据集的燥峰值。促KURT(n医umber1妖,numbe棒r2,..乐.)洽LARGE葵返回数据集里鼓第窗k削别个最大值。使畅用此函数可以廊根据相对标准飞来选择数值。粘LARGE(新array,胳k)章LINEST贷使用最小二乘跳法计算对已知树数据进行最佳县直线拟合，并攀返回描述此直稀线的数组。泄LINEST打(known盏_y's,k渡nown_x醒's,con折st,sta批ts)灭LOGEST谁在回归分析中葬，计算最符合纵观测数据组的灵指数回归拟合绳曲线，并返回咸描述该曲线的计数组。绕LOGEST今(known贴_y's,k堵nown_x盘's,con丈st,sta宏ts)释LOGINV经返壤回猎x逮府的对数正态分芽布龙累积函数的逆艰函数。羊LOGINV绞(proba辛bility元,mean,毯standa障rd_dev脊)宏LOGNOR舟MDIST株返茂回叉x饱惠的对数正态分庙布的累积函数隔。辜LOGNOR音MDIST(糠x,mean疫,stand扔ard_de躺v)昏MAX浮返回数据集中草的最大数值。事MAX(nu续mber1,窃number庙2,...)剥MAXA办返回参数清单欧中的最大数值涝。侵MAXA(v饲alue1,扫value2厅,...)谋MEDIAN服返回给定数值贺集合的中位数番。中位数是在猪一组数据中居胀于中间的数。泥MEDIAN参(numbe斯r1,num浙ber2,唐...)渐MIN掘返回给定参数驼表中的最小值旁。哀MIN(nu蒜mber1,聚number巾2,...扶)昂MINA马返回参数清单舅中的最小数值和。导MINA(v健alue1,蛮value2届,...)欠MODE漫返回在某一数数组或数据区域钓中出现频率最彩多的数值。会MODE(n辈umber1捷,numbe违r2,..叮.)备NEGBIN摩OMDIST喂返回负二项式惕分布。夺NEGBIN德OMDIST丽(numbe戚r_f,nu卫mber_s有,proba毅bility错_s)召NORMDI六ST贪返回给定平均早值和标准偏差蛾的正态分布的谷累积函数。晌NORMDI挑ST(x,m趋ean,st猛andard放_dev,c患umulat泉ive)玉NORMIN氏V上返回给定平均块值和标准偏差络的正态分布的锅累积函数的逆弯函数。矩NORMIN怠V(prob越abilit耍y,mean聪,stand窜ard_de询v)傻NORMSD需IST产返回标准正态装分布的累积函漫数，该分布的以平均值翁为帐陵0暮，标准偏差材为肤朽1河。揭NORMSD叮IST(z)般NORMSI豆NV似返回标准正态找分布累积函数敏的逆函数。该嘱分布的平均值广为达抚0倒，标准偏差塞为曾好1妨。韵NORMSI哥NV(pro出babili赌ty)腊PEARSO框N然返四回栏Pears阶o岛n使（皮尔生）乘覆积矩相关系数导，邻r已，这是一个范宽围明在咸-1.0窝银到可1.0述装之间（包盲括舰-1.0深像和程1.0的控在内）的无量拒纲指数，反映爸了两个数据集哨合之间的线性翻相关程度。晒PEARSO膏N(arra肯y1,arr蕉ay2)惹PERCEN何TILE苏返回数值区域酒的僵K荡师百分比数值点征。可以使用此厌函数来建立接透受阀值。例如急，可以确定得耐分排名馆在斥胡90饺瓜个百分点以上吴的检测侯选人桨。践PERCEN姨TILE(a素rray,k更)久PERCEN乘TRANK恢返回特定数值锹在一个数据集纪中的百分比排谢位。此函数可垂用于查看特定党数据在数据集且中所处的位置补。例如，可以旦使用函芹数鸦PERCE条NTRANK践书计算某个特定斜的能力测试得遭分在所有的能卸力测试得分中演的位置。拔PERCEN御TRANK(尘array,隐x,sign蚁ifican凑ce)浆PERMUT课返回从给定数谅目的对象集合和中选取的若干浓对象的排列数木。排列可以为迁有内部顺序的材对象或为事件无的任意集合或辨子集。排列与野组合不同，组瓦合的内部顺序救无意义。此函扛数可用于彩票译计算中的概率榆。絮PERMUT灵(numbe辟r,numb吃er_cho来sen)败POISSO侧N岗返回泊松分布厌。泊松分布通巧常用于预测一己段时间内事件部发生的次数，友比如一分钟内股通过收费站的灶轿车的数量。如POISSO丘N(x,me鸭an,cum谈ulativ喇e)俘PROB门返回一概率事侮件组中落在指伯定区域内的事削件所对应的概音率之和。如果辰没有给乘出歼upper末_limi输t姨，则返谅回焦x_ra坏nge钓计内值等规于凭lower寻_limit揪先的概率。咐PROB(x糕_range腾,prob_聚range,黄lower_忽limit,谁upper_痒limit)漂QUARTI畅LE廉返回数据集的士四分位数。四同分位数通常用禾于在销售额和呢测量值数据集起中对总体进行厉分组。例如，魔可以使用函拣数洪QUART舞ILE圈质求得总体中另前全25%临序的收入值。权QUARTI吸LE(arr绒ay,qua竞rt)羡RANK减返回一个数值威在一组数值中搏的排位。数值肿的排位是与数向据清单中其他炕数值的相对大培小（如果数据赔清单已经排过半序了，则数值建的排位就是它钞当前的虚位置）。朱RANK(n煌umber,闸ref,or删der)极RSQ除返回根瓦据注known粒_y's汉婆和傲known脏_x's记坟中数据点计算积得出年的芝Pears歼on碧迎乘积矩相关系袜数的平方。有掩关详细信息，堤请参阅函茫数短REARS戒O喂N杰。籍R截窃平方值可以解际释努为仰y谱信方差熊与赛x虑类方差的比例。祖RSQ(kn她own_y'鼠s,know字n_x's)体SKEW赤返回分布的偏逢斜度。偏斜度舒反映以平均值锹为中心的分布帐的不对称程度凤。正偏斜度表月示不对称边的会分布更趋向正秒值。负偏斜度尖表示不对称边信的分布更趋向喊负值。筑SKEW(n昂umber1倾,numbe靠r2,...钩)暂SLOPE泡返回根虾据泪known夺_y's捞顽和倘known涌_x's提膀中的数据点拟兵合的线性回归锈直线的斜率。遇斜率为直线上钥任意两点的重绢直距离与水平方距离的比值，假也就是回归直微线的变化率。兆SLOPE(耗known_瞧y's,kn席own_x'句s)蒜SMALL欣返回数据集中寻第宗k带岗个最小值。使微用此函数可以发返回数据集中颤特定位置上的晚数值。猾SMALL(驴array,匆k)动STANDA纪RDIZE骂返回叮以甲mean末我为平均值，妖以柴stand判ard-de动v嘉接为标准偏差的妻分布的正态化广数值。院STANDA垃RDIZE(铺x,mean庄,stand低ard_de违v)阵STDEV坚估算样本的标厚准偏差。标准虏偏差反映相对思于平均值万（磁mea排n秋）的离散程度薄。典STDEV(戏number纹1,numb利er2,..览.)庙STDEVA乞估算基于给定狂样本的标准偏辫差。标准偏差灾反映数值相对好于平均值遮（寿mea燕n创）的离散程度短。文本值和逻风辑值（粗如闹TRUE伯签或挑FALS东E横）也将计算在惕内。群STDEVA超(value扇1,valu疫e2,...吗)站STDEVP猜返回以参数形芬式给出的整个狠样本总体的标筒准偏差。标准老偏差反映相对错于揭平均值鹅（惠mea未n百）的离散程度膏。谋STDEVP盐(numbe熔r1,num调ber2,.锣..)尼STDEVP狼A违计算样本总体设的标准偏差。仁标准偏差反映谷数值相对于平冰均值什（洪mea曲n勉）的离散程度渡。黑STDEVP肌A(valu问e1,val叼ue2,..路.)霉STEYX烫返回通过线性瓦回归法计越算恭y左唉预测值时所产让生的标准误差泰。标准误差用嘴来度量根据单详个饲x嫂宏变量计算出气的盯y瞎柜预测值的误差亚量。扎STEYX(恩known_逼y's