EXCEL函数大全

ysfabm

6.名次值统计\r\n在工资统计和成绩统计等场合，往往需要知道某一名次(如工资总额第二、第三)的员工的工资是多少。这种统计的操\r\n作方法如下。 \r\n\r\n(1)函数分解 \r\nLARGE 函数返回数据集中第K 个最大值。使用此函数可以根据相对标准来选择数值。 语法：LARGE(array,k) \r\nArray 为需要从中选择第K 个最大值的数组或数据区域； K 为返回值在数组或数据单元格区域中的位置(从大到小排)。SMALL 函数返回数据集中第K 个最小值。使用此函数可以返回数据集中特定位置上的数值。法：SMALL(array,k) Array 为需要找到第K 个最小值的数组或数字型数据区域；K为返回的数据在数组或数据区域里的位置(从小到大)。\r\n\r\n(2)实例分析\r\n假设C2:C688 区域存放着员工的工资，首先在D 列选取空白单元格D3，在其中输入公式“=LARGE(C2:C688,D2)”。其中D2 作为输入名次变量的单元格，如果你在其中输入3，公式就可以返回C2:C688 区域中第三大的数值。 \r\n如果我们把上述公式修改为“=SMALL(C2:C688,D1)”，然后在D1 单元格中输入6，就可以获得C2:C688 区域倒数第六(小)的数值。 为方便起见，你可以给C2:C688 区域定义一个名称“职工工资”。此后可以把上述公式修改为“=LARGE(职工工资,D2)”或“=SMALL(职工工资,D1)”。 \r\n\r\n7.位次阈值统计\r\n与上例相似，在工资统计和成绩统计等场合，需要知道排名达到总体的前1/3 的工资总额或分数(称为“阈值”)是多\r\n少。这种统计的操作方法如下： \r\n\r\n(1)函数分解 \r\nPERCENTILE 函数返回区域中数值的第K 个百分点的值。可以使用此函数来建立接受阈值。 语法：PERCENTILE(array,k) Array 为定义相对位置的数组或数据区域；K为0 到1 之间的百分点值，包含0和1。\r\n\r\n(2)实例分析 \r\n假设C2:C200 区域存放着学生的考试成绩，首先在D列选取空白单元格D3，在其中输入公式 “=PERCENTILE(C2:C200,D2)”。其中D2 作为输入百分点变量的单元格，如果你在其中输入0.33，公式就可以返回名次达到前1/3 所需要的成绩。

ysfabm

五、函数应用案例── 管理计算\r\n企业、学校等单位均存在许多管理计算问题，例如计算一个学期有几个授课日、企业在多少个工作日之后交货等等。下面介绍有关问题的几种计算方法。 \r\n\r\n1.授课日数\r\n\r\n(1)函数分解 \r\nNETWORKDAYS 函数专门用于计算两个日期值之间完整的工作日数值。这个工作日数值将不包括双休日和专门指定的其他各种假期。 语法：NETWORKDAYS (Start_date,End_date,Holidays) Start_date 表示开始日期；End_date为终止日期，Holidays 表示作为特定假日的一个或多个日期。这些参数值既可以手工输入，也可以对单元格的值进行引用。 \r\n\r\n(2)实例分析 \r\n假设新学期从2003 年9 月1 日开始到2004 年1 月15 日结束，希望知道本学期有多少个授课日，也就是排除双休日和国家法定假日外的授课工作日。这就是计算授课日数或工作日数的问题。首先打开一个空白工作表，在A1、B1、C1 单元格输入“开学时间”、“结束时间”、“法定节日”，然后在其下面的单元格内输入“2003-9-1”、“2004-1-15”、“2003-10-1” “2003-10-2”、“2003-10-3”和“2004-1-1”(后四项必须在C 列的“法定假日”下)。 接着可以选中D2 单元格，输入公式“=NETWORKDAYS(A2,B2,C2:C5)”。公式中A2 引用的是学期(或工作)的开始日期，B2引用的是学期结束的日期，C2:C5区域引用的是作为法定假日的多个日期。输入结束回车即可获得结果95， 即2003 年9 月1 日到2004 年1 月15 日，排除四个法定假日后的实际授课日是95 天。 \r\n\r\n\r\n2.折旧值计算\r\n无论单位还是家庭，许多固定资产和耐用消费品都存在折旧问题，随着使用时间的延长，其残值在不断减少。假设某单位有一批2000 年购进原价8 500 元/每台的电脑，预计使用寿命6 年， 寿命期结束时的资产残值约为1 000 元，要求使用第二年内的折旧值。 \r\n\r\n(1)函数分解 \r\nDB 函数使用固定余额递减法，计算一笔资产在给定期间内的折旧值。 语法：DB(cost,salvage,life,period,month) \r\nCost 为资产原值；Salvage为资产在折旧期末的价值(也称为资产残值)；Life为折旧期限(有时也称作资产的使用寿\r\n命)；Period为需要计算折旧值的期间。Period必须使用与life 相同的单位；Month 为第一年的月份数，如省略，则假设为12。 \r\n\r\n(2)实例分析 \r\n为了在参数改变以后仍能进行计算，我们打开一个空白工作表，在A1、B1、C1、D1、E1 单元格输入“电脑原值”、“资产残值”、“使用寿命”、“折旧时间”和“折旧值”，然后在其下面的单元格内输入“8500”、“1000”、“6”、“2 ”。\r\n然后选中E2 单元格在其中输入公式“=DB(A2,B2,C2,D2)”，回车后即可得到结果“￥1,785.00”，就是说使用期第二年的折旧值为1 785 元。如果你要计算其他设备或财产的折旧值，只需改变A2、B2、C2、D2单元格内的数值即可。 \r\n\r\n3.客流均衡度计算\r\n假设某超市周一到周六的客流人数是16 359、17 254、18 654、15 398 、21 689 和220 867，总经理需要知道这种情\r\n况下的客流分布是否平坦。可以按如下方法计算：

ysfabm

(1)函数分解 \r\nKURT 函数返回数据集的峰值。峰值反映与正态分布相比某一分布的尖锐度或平坦度。正峰值表示相对尖锐的分布。负峰值表示相对平坦的分布。 语法：KURT(number1,number2,...) Number1,number2,...是用于计算峰值的1～30 个参数。也可以不使用这种用逗号分隔参数的形式，而用单个数组或数组引用的形式。 \r\n\r\n(2)实例分析 \r\n打开一个空白工作表，在A1 单元格中输入“一周客流统计”，然后将上述数据依次输入A2、A3等单元格。然后选中A8 单元格，在其中输入公式“=KURT(D27)”，回车即可获得结果“-1.719218897”，这说明超市的客流分布与正态分布相比是相对平坦的。假如星期天搞特价促销，客流增加到了50 867 人，则计算结果就会变为“5.45379941”。说明超市的客流分布与正态分布相比比较尖锐了，特价促销对客流的影响还是非常大的。 需要指出的是，KURT函数在教育统计等领域也有广泛用途，假如把函数引用的区域修改为许多分数的集合，就可以知道考试成绩的分布是否尖锐或平坦。 \r\n\r\n4.销售额预测\r\n假设某超市周一到周日的日销售额分别为13、17、16、15、19、21和22(万元)，总经理需要预测今后一周内的日销\r\n售额的最高值和最低值。可以按如下方法进行预测： \r\n\r\n(1)函数分解 \r\nTREND 函数返回一条线性回归拟合线的值。即找到适合已知数组known_y\'s 和known_x\'s的直线(用最小二乘法)，并返回指定数组new_x\'s 在直线上对应的y 值。语法：TREND(known_y\'s,known_x\'s,new_x\'s,const) Known_y\'s 是关系表达式y=mx+b 中已知的y 值集合；Known_x\'s 是关系表达式y=mx+b 中已知的可选x值集合；New_x\'s 为需要函数 TREND返回对应y 值的新x值；Const 为一逻辑值，用于指定是否将常量b 强制设为0。 \r\n\r\n(2)实例分析 \r\n首先要打开一个空白工作表，在A1 单元格中输入“日销售额”，然后将上述数据依次输入A2、A3 至A8 单元格。然后选中B2 至B8 区域，在Excel 的编辑栏输入公式“=TREND(A2:A”，回车即可在B2 至B8 区域获得7 个结果， 其中最高销售额为21.64 万元，最低销售额为13.5 万元。 与KURT 函数一样，TREND函数可以用于教育统计中的学生入学数的峰值和低谷，铁路运输领域的客流高峰和低谷等的预测。 \r\n\r\n5.客流与营业额的相关分析\r\n\r\n(1)函数分解 \r\nCORREL 函数返回单元格区域array1 和array2 之间的相关系数。使用相关系数可以确定两种属性之间的关系。 \r\n语法：CORREL(array1,array2) Array1 为第一组数值单元格区域；Array2为第二组数值单元格区域。

ysfabm

(2)实例分析 \r\n\r\n假设一个超市要分析客流量与营业额是否相关。首先运行Excel 打开一个空白工作表，在A1 至A31 单元格输入八月份的每日客流人数，然后在B1 至B31 输入八月份每日的营业额，再将上述两个区域的名称定义为“日客流人数”和“日营业额”。 接下来就可以选中工作表中的某个空白单元(例如B32)， 作为存储运算结果的位置。在Excel 的编辑栏输入公式“=CORREL(日客流人数,日营业额)”，回车后即可在公式所在单元格看到相关系数的计算结果。 上式中CORREL 函数返回“日客流人数”和“日营业额”两个数据集合的相关系数，实际应用中必须根据要分析的数据集合对引用区域进行修改。 与其他计算不同，CORREL函数计算出的相关系数必须进行分析，才能得出两个数值之间是否相关的结论。统计理论根据各种因素(如“日客流人数”和“日营业额”)相互影响的关系，把相关分为正相关、负相关和零相关三种类型。\r\n\r\n所谓正相关就是两个因素的变化方向相同，即同时变大或变小，例如气温和冷饮销量就是正相关；负相关就是两个因素的变化方向相反，即一个变大(小)另一个变小(大)，例如气温上升和羽绒服销量就是负相关；零相关就是两个因素的变化方向无规律，即不存在相互之间影响的情况，例如学生的考试成绩和面粉的销量就是零相关。\r\n\r\n 为了帮助不太熟悉统计理论的用户掌握CORREL 函数的使用，这里使用相关程度分析的理论修改公式“=CORREL(日客流人数,日营业额)”，使之成为下面这种形式，从而更加直观的给出两列数据相关程度的结论。 “=IF((ABS(CORREL(日客流人数,日营业额)))<=0.3,\"相关程度低\",IF((ABS(CORREL(日客流人数,日营业额)))<=0.5,\"相关程度一般\",IF((ABS(CORREL(日客流人数,日营业额)))<=0.7,\"相关程度较高\",IF((ABS(CORREL(日客流人数,日营业额)))<=0.9,\"相关程度高\",IF((ABS(CORREL(日客流人数,日营业额)))<=1,\"相关程度极高\"))))” 公式中的“CORREL(日客流人数,日营业额)”部分还是计算日客流人数和日营业额两列数据的相关系数。\r\n\r\n由于这里只需要了解相关程度，所以使用ABS 函数返回相关系数的绝对值。整个公式中由左往右的下一个IF 语句就是上一个IF 语句的参数。例如第一个逻辑判断表达式“(ABS(CORREL(日客流人数,日营业额)))<=0.3”为“真”(成立)，则公式所在单元格就会被填入“相关程度低”；如果第一个逻辑判断表达式“(ABS(CORREL(日客流人数,日营业额)))<=0.3”为“假”(不成立)，则计算第二个IF 语句“IF((ABS(CORREL(日客流人数,日营业额)))<=0.5”；以此类推直至计算结束。

ysfabm

终于发完了,大家慢慢看