Excel在经济管理中的应用课件(中)2.pptx

1、 第五章 Excel在数据统计与分析中的应用1 了解数组及数组公式 掌握常用数学和统计函数的基本用法 学会在实际事务中灵活使用数学和统计函数本章学习目标2 Excel的数组公式很有用，尤其在需要一次性获得多个运算结果时，数组公式就显得特别重要，了解Excel数组及数组公式可以为实际数据处理问题提供更多的解决方案。5.1.15.1.1认识数组和数组公认识数组和数组公式式Excel数组是以常量数据为元素的集合，其元素的数据类型可以是数值、文本和逻辑型。特别的，数组中也可以仅有一个元素，即为我们之前熟悉的单值常量。引入数组概念后，Excel中输入的公式当以Ctrl+Shift+Enter组合键确认时

2、即为数组公式，其中参与运算的数组可以是常量数组或区域数组。5.1 数组公式及数据处理应用3进行数组公式运算时，要先选择用来存放结果的单元格区域（可以是一个单元格），然后输入数组公式，按Ctrl+Shift+Enter组合键结束，Excel将在公式两边自动加上花括号“”。5.1.25.1.2数组的常见形数组的常见形式式1.常量数组用一对花括号“”把构成数组的常量数据括起来，就是常量数组。同行数据之间用逗号“，”分割，同列数据之间用分号“；”分割。2.区域数组区域数组就是通过对一组连续的单元格形成的矩形区域进行引用而得到的数组。3.内存数组内存数组是指在数组公式计算过程中生成的中间结果值。45.1

3、.35.1.3数组间的运算数组间的运算1.二维数组间的运算其结果由对各数组相同位置的元素进行相同的运算而获得，若参与运算的数组的行列数不匹配，结果数组的大小应该和最大的行列数匹配，但有效数据区域和最小的行列数匹配。2.二维数组与一维数组的运算若一维数组是行数组，其运算结果相当于原值扩展其行与二维数组行相同后，再与二维数组进行的运算；若一维数组是列数组，其运算结果相当于原值扩展其列与二维数组列相同后，再与二维数组进行的运算，实质归结为二维数组间的运算。3.一维数组间的运算5若同是行数组或同是列数组运算时，其运算规则同二维数组间的运算。若是行数组和列数组运算时，其运算结果相当于原值扩展行数组的行与

4、列数组的行相同，同时原值扩展列数组的列与行数组的列相同后，再进行的二维数组间的运算。4.数组与单一数据的运算其运算结果相当于原值扩展单一数据为与其运算的数组的行列数一样的数组后，再进行的运算。以上数组运算全部以加法示例，其他如减法、乘法及比较等运算雷同。统计统计销售额销售额案例要求案例要求 打开“案例5-1.xlsx”工作簿，“销售表”中记录了各产品的销售数量和销售单价。根据已提供数据，分别用非数组公式数组的概念案案例例5-16和数组公式两种方法，计算每种产品的销售金额以及所有产品的销售总额。本案例实现效果如图数组的声明7在Excel环境下，对数据进行基本处理和分析时，诸如求和、求平均值、求最

5、大最小值、计数等是最常见的要求，可以由Excel提供的常用数学和统计函数实现。5.2.1 5.2.1 企业产品总量统企业产品总量统计计常用实现求和函数如下：1.SUM格式：SUM(number1,number2,.)功能：计算参数列表中数值的和。说明：单元格引用区域中的空值、文本值和逻辑值都被按数值0处理。2.SUMIF格式：SUMIF(range,criteria,sum_range)5.2 常用数学统计函数及数据处理应用8功能：计算区域中满足单一条件的单元格数值的和。说明：range必需。根据条件进行计算的单元格区域。Criteria必需。对range指定区域的限定条件，其形式可以为数值、

6、表达式、单元格引用、文本或函数，任何文本条件或任何含有逻辑或数学符号的条件都必须使用双引号“”括起来。如果条件为数值，则无需使用双引号。sum_range可选。要求和的单元格区域，空值和文本值将被忽略。如果缺省，Excel会对在range参数中指定的单元格（即满足条件的单元格）求和。3.SUMIFS格式：SUMIFS(sum_range,criteria_range1,criteria1,criteria_range2,criteria2,.)功能：计算区域中满足多个条件的单元格数值的和。9说明：Sum_range必需。要求和的单元格区域。Criteria_range1必需。应用Criteri

7、a1条件测试的区域。Criteria1必需。测试条件。Criteria_range1和Criteria1设置用于搜索某个区域是否符合特定条件的搜索对。一旦在该区域中找到了满足条件的项，将计算sum_range中相应值的和。Criteria_range2,criteria2,可选。附加的区域及其关联条件，最多可以输入127个区域/条件对。全部缺省时，功能同SUMIF。仅在与sum_range参数中的单元格同一位置的各条件区域的值满足各自相应的指定条件时，才将该单元格数值计入总和中。4.SUMPRODUCT10格式：SUMPRODUCT(array1,array2,array3,.)功能：在给定的

8、几组数组中，先进行数组间的乘法运算，最后把乘积结果集的元素相加。这是SUMPRODUCT函数的基本用法。SUMPRODUCT函数功能结合“5.1.3数组间的运算”一节的内容更易于理解。说明：Array1必需。Array2,array3,.可选。2到255个数组参数。全部缺省时，SUMPRODUCT函数的使用同SUM函数。数组参量间的分隔符为逗号“，”时，函数SUMPRODUCT将文本、逻辑型的数组元素作为0处理，并且要求各个数组参数的行列数必须匹配，否则，函数SUMPRODUCT将返回#VALUE!。如果希望处理行列数不匹配的数组参数的运算，需要把数11组间的分隔符由逗号“，”换为星号“*”。

9、数组间的乘法运算规则同“5.1.3数组间的运算”一节的讲解，若乘积运算的结果集中有错误值#N/A时，SUMPRODUCT将返回#N/A。参与运算的数据区域若有文本数据，会按0处理；若有逻辑数据时，FALSE按0处理，TREU按1处理。SUMPRODUCT函数还有两种扩展用法，介绍如下：（1）格式：SUMPRODUCT(criteria_array1)*(criteria_array2)*(criteria_array3)*.)功能：实现多条件计数。说明：criteria_array1必需。是条件，其实质是数组的比较运算，结果值是以TREU或FALSE为元素的数组。(criteria_array

10、2),(criteria_array3),.可选。需要满足的更多条件。若全部缺省，SUMPRODUCT函数的结果值为0。12（2）格式：SUMPRODUCT(criteria_array1)*(criteria_array2)*(criteria_array3)*.*sum_ array)功能：实现条件求和。说明：sum_ array必需。是求和区域。运算流程和条件计数雷同，只是增加了求和区域。最后的求和结果相当于把求和区域中满足这样条件的数计入总和，即比较运算符前的各数组中与该数相同位置上的元素同时满足各自对应的条件。运算中若有数组行列数不匹配的问题，运算规则与前面讲过的算术运算一样。统统计

11、企业产品组装计企业产品组装量量案例要求案例要求 打开“案例5-2.xlsx”工作簿，“sheet1”表中按月记录了某企业组装车间员工一季度组装某产品的数量，如图5-14所示，案案例例5-213根据已提供数据，完成以下操作：（1）“Sheet1”表中统计每位员工的一季度组装量。（2）“Sheet2”表中按组统计每月和一季度组装总量。（3）分别在“Sheet3”表、“Sheet4”表和“Sheet5”表中用不同的公式按组统计男女员工的一季度组装总量。本案例实现主要效果图如下：145.2.2 5.2.2 企业人数分段统计企业人数分段统计常用实现计数函数如下：1.COUNT 格式：COUNT(valu

12、e1,value2,.)功能：计算参数列表中数值的个数。说明：value1必需。要计算其中数值的个数的第一项，可以是常量或单元格引用。15value2,.可选。要计算其中数值的个数的其他项，最多可包含 255 个。如果参数为数值、逻辑值、日期或者代表数字的文本（例如，用引号引起的数字，如“1”），则将被计算在内。错误值或不能转换为数值的文本，则不会被计算在内。如果参数是一个数组或引用，则只计算其中的数值，空白单元格、逻辑值、文本或错误值将不计算在内。2.COUNTA 格式：COUNTA(value1,value2,.)功能：计算区域中非空单元格的个数。说明：COUNTA 函数计算包含任何类型信

13、息（包括错误值和空文本(“”)）的单元格。例如，如果参数区域中包含由公式计算返回的空字符串，COUNTA函数计算该值。16COUNTA函数不会对空单元格进行计数。3.COUNTIF 格式：COUNTIF(range,criteria)功能：计算区域中满足指定条件的单元格的个数。说明：range必需。要进行计数的单元格区域，空值将被忽略。criteria必需。用于确定对哪些单元格计数的条件。4.COUNTIFS 格式：COUNTIFS(criteria_range1,criteria1,criteria_range2,criteria2,)功能：计算多个区域同一位置上同时满足各自条件的单元格的个

14、数。说明：criteria_range1,criteria1必需。在其中计算关联条件17的第一个区域和关联条件。criteria_range2,criteria2,.可选。附加的区域及其关联条件。若全部缺省，COUNTIFS函数的使用同COUNTIF函数。每一个附加的区域都必须与参数criteria_range1具有相同的行数和列数，这些区域无需彼此相邻。每个区域的条件一次应用于一个单元格。如果所有区域的第一个单元格都满足其关联条件，则计数增加1。如果所有区域的第二个单元格都满足其关联条件，则计数再增加1，依此类推，直到计算完所有单元格。5.FREQUENCY格式：FREQUENCY(data

15、_array,bins_array)功能：以一个垂直数组返回一组数据的频率分布。说明：Data_array必需。要计算频率的一组数值或对这组18数值的引用。如果data_array中不包含任何数值，则FREQUENCY返回一个零数组。Bins_array 必需。对data_array进行频率计算的一组间隔值。由于FREQUENCY返回一个数组，所以它必须以数组公式的形式输入。返回的数组中的元素比bins_array中的元素多一个。这额外元素返回最大的间隔值以上的值的计数。例如，在对输入到三个单元格中的三个值范围（间隔）进行计数时，要选中四个单元格返回FREQUENCY函数的计算结果。额外的单元

16、格将返回data_array中大于第三个间隔值的值的数量。函数FREQUENCY将忽略空白单元格和文本。分分段统计员工人段统计员工人数数案例要求案例要求 打开“案例5-3.xlsx”工作簿，“sheet1”表中按月案案例例5-319记录了某企业组装车间员工一季度组装某产品的数量，根据已提供数据，完成以下操作：（1）统计组装量分别在0-49、50-59、60-69、70-79、80-89、90-99、100及以上段的员工人数。（2）分别统计各月完成和未完成任务的员工人数，若组装量小于60，属于未完成，否则完成。本案例实现效果如图20215.2.3 5.2.3 企业人均产量统企业人均产量统计计常用

17、实现求平均值函数如下：1.AVERAGE格式：AVERAGE(number1,number2,.)功能：计算参数列表中数值的平均值（算术平均值）。说明：Number1必需。Number2,.可选。逻辑值（TRUE转为1，FALSE转为0）和直接键入到参数列表中代表数字的文本被计算在内，而错误值或不能转换为数值的文本，将会导致错误。如果区域或单元格引用参数包含文本、逻辑值或空单元格，则这些值将被忽略；但包含零值的单元格将被计算在内。2.AVERAGEIF格式：AVERAGEIF(range,criteria,average_range)22功能：计算区域中满足指定条件的单元格数值的平均值（算术平

18、均值）。说明：Range必需。根据条件进行计算的单元格区域。Criteria必需。用于确定对哪些单元格数值求平均值的条件。Average_range可选。计算平均值的实际单元格区域。如果省略，Excel会对range参数中符合指定条件的单元格数值（即应用条件的单元格）求平均值。如果average_range中的单元格为空单元格，AVERAGEIF将忽略它。3.AVERAGEIFS格式：AVERAGEIFS(average_range,criteria_range1,criteria1,criteria_range2,criteria2,.)功能：计算区域中满足多个条件的单元格数值的平均值23（

19、算术平均值）。说明：Average_range必需。要计算平均值的单元格区域。当有空值时，函数会把它去掉，不参与计算。4.ROUND格式：ROUND(number,num_digits)功能：将数值四舍五入到指定的位数。说明：number必需。要四舍五入的数值。num_digits必需。进行四舍五入运算时采用的位数。如果num_digits大于0（零），则将数值四舍五入到指定的小数位数。如果num_digits等于0，则将数值四舍五入到最接近的整数。如果num_digits小于0，则将数值四舍五入到小数点左边的相应位数。统计平均组装量统计平均组装量案案例例5-424案例要求案例要求 打开“案例

20、5-4.xlsx”工作簿，“sheet1”表中按月记录了某企业组装车间员工一季度组装某产品的数量，根据已提供数据，完成以下操作：（1）“sheet1”表中统计每位员工一季度的组装量和月平均组装量。（2）“sheet2”表中按组统计每月人均组装量和一季度人均组装量。（3）“sheet3”表中按组统计男女员工一季度人均组装量。（4）所有结果为整数。本案例实现效果如下图2526275.2.4 5.2.4 企业产量最值及排位统企业产量最值及排位统计计常用实现求大小位次的函数如下：1.MAX格式：MAX(number1,number2,.)功能：返回参数列表中的最大值。说明：number1必需，numb

21、er2,.可选。逻辑值和直接键入到参数列表中代表数字的文本被计算在内。如果参数是一个数组或引用，则只计算其中的数值，空白单元格、逻辑值或文本将被忽略。如果参数不包含任何数值，则MAX函数返回0。2.MIN格式：MIN(number1,number2,.)功能：返回参数列表中的最小值。283.RANK格式：RANK(number,ref,order)功能：返回某数值在一个数据集的大小排名。如果多个值具有相同的排位，则返回该组值的最高排位。说明：Number必需。要找到其排位的数值。Ref必需。要在其中查找排名的数据集。Ref中的非数字值会被忽略。Order可选。一个指定数值排位方式的数字。如果o

22、rder为0或省略，按照降序返回排名结果；如果order为1，按照升序返回排名结果。该函数与Excel早期版本兼容，在未来版本中可能不再使用，替代函数为以下两个函数。4.RANK.EQ29格式：RANK.EQ(number,ref,order)功能：同上。5.RANK.AVG格式：RANK.AVG(number,ref,order)功能：。返回某数值在一个数据集的大小排名。如果多个值具有相同的排位，则将返回平均排位。6.PERCENTRANK格式：PERCENTRANK(array,x,significance)功能：返回某个数值在一个数据集中的百分比排位，此处的百分比值范围为0到1(包含0和

23、1)。说明：Array必需。定义相对位置的数值数据区域。X必需。需要得到排位的数值。significance可选。指定返回的百分比值的有效位数。如果省略，则保留3位小数。30省略，则保留3位小数。如果数组里没有与x相匹配的值，函数PERCENTRANK将进行插值以返回正确的百分比排位。函数返回值在0到1(包含0和1)之间变化，最大的数排位是1，最小的数排位是0。该函数与Excel早期版本兼容，在未来版本中可能不再使用，替代函数为以下两个函数。7.PERCENTRANK.INC格式：PERCENTRANK.INC(array,x,significance)功能：同上。8.PERCENTRANK.

24、EXC格式：PERCENTRANK.EXC(array,x,significance)功能：返回某个数值在一个数据集中的百分比排位,此处的百分比值范围为0到1(不包含0和1)。31说明：函数返回值在0到1(不包含0和1)之间变化。9.LARGE格式：LARGE(array,k)功能：返回数据集中第k个最大值。说明：Array必需。需要确定第k个最大值的数据区域。K必需。返回值在数据区域中的排位（从大到小排）。例如：在有N个数的数据集中，LARGE(array,1)返回最大值，LARGE(array,N)返回最小值10.SMALL格式：SMALL(array,k)功能：返回数据集中第k个最小值。

25、说明：Array 必需。需要确定第k个最小值的数据区域。K 必需。返回值在数据区域中的排位（从小到大）。32例如：在有N个数的数据集中，SMALL(array,1)返回最小值，SMALL(array,N)返回最大值统计组装量最值和排量统计组装量最值和排量案案例例5-5案例要求案例要求 打开“案例5-5.xlsx”工作簿，“sheet1”表中按月记录了某企业组装车间员工一季度组装某产品的数量，根据已提供数据，在其中完成以下操作：（1）统计每位员工的一季度组装量、一季度组装量降序排名和一季度组装量百分比排名，百分比排名结果按百分比样式显示。（2）统计每月和一季度的最高最低组装量。在“sheet2”

26、表中完成排名前五位的一季度组装量和末五位的一季度组装量的统计。本案例实现效果如下图3334企业车间分组按性别的各项统企业车间分组按性别的各项统计计案例要求案例要求 打开“案例5-6.xlsx”工作簿，“sheet1”表中按月记录了某企业组装车间员工一季度组装某产品的数量，根据已提供数据，在“sheet2”表中完成以下操作：案案例例5-635（1）按组分性别统计一季度组装量。（2）按组分性别统计人数。（3）按组分性别统计一季度人均组装量。（4）所有结果为整数。本案例实现效果如下图36由多表给出基础数据的统由多表给出基础数据的统计计案例要求案例要求 打开“案例5-7.xlsx”工作簿，“sheet

27、1”、“sheet2”和“sheet3”表中分别记录了某企业组装车间员工一季度各月组装某产品的数量。根据已提供数据，在“sheet4”表中统计每位员工的一季度组装量、月平均组装量和一季度组装量降序排名；在“sheet5”表中完成以下操作：（1）按组统计每月的组装量及一季度组装量。（2）按组统计月平均组装量。（3）所有结果为整数。本案例实现效果如下图案案例例5-73738395.3.1 5.3.1 企业车间组装量分企业车间组装量分析析打开“课外实验5-1.xlsx”工作簿，“sheet1”表中按月记录了某企业组装车间员工一季度组装某产品的数量。根据已提供数据，在“Sheet2”中完成以下操作：（

28、1）统计组装量分别在0-39、40-49、50-59、60-69、70-79、80-89、90-99、100及以上分段的员工人数。（2）统计达标和不达标人数，组装量小于60为不达标，否则达标。（3）统计各月最高、最低量和人均组装量，平均值保留两位小数。（4）统计达标率，用百分比形式显示。最终效果如图5.3本章课外实验40415.3.2 5.3.2 企业车间组装量汇企业车间组装量汇总总打开“课外实验5-2.xlsx”工作簿，“sheet1”表中按月记录了某企业组装车间员工一季度组装某产品的数量。根据已提供数据，在“Sheet2”表中完成以下操作：（1）按组分性别统计每月的组装量。（2）按组统计一

29、季度组装量。在“Sheet3”表中按组分性别统计每月的人均组装量，保留两位小数。最终效果如图4243 第六章 ExcelExcel在企业生产和经营管理中的应用在企业生产和经营管理中的应用Excel在数据统计与分析中的应用45 掌握企业生产管理中常用表格的建立方法 掌握日期时间函数的基本用法，能够解决生产周期、工时数的计算问题 熟练使用IF函数解决企业生产与经营过程中的常见问题 掌握OFFSET函数的用法，能够核算生产成本本章学习目标Excel在数据统计与分析中的应用46 企业生产和经营管理中会用到大量表格，表格的制作水平直接关系到企业的生产和管理效率。本节将介绍使用EXCEL如何设计和制作出符

30、合企业生产经营管理规律的表格。6.1 设计企业生产管理常用表格6.1.1生产部门业务能力分析表 对于生产部门来说，业务能力的强弱是非常重要的，所以对生产部门业务能力的分析也是非常必要和必须的。通过对业务能力的分析，帮助决策者尽快做出调整，最大限度地保障企业生产效率。Excel在数据统计与分析中的应用47 案例要求案例要求 某企业将设计一款生产部门业务能力分析表，用于对生产部门业务开展情况进行打分，从而起到激励或整改的目的。案例案例6-1生产部门业务能力分析表生产部门业务能力分析表Excel在数据统计与分析中的应用486.1.2 设计产品成本核算系统 生产成本的分析和计划对于企业的经营决策起着极

31、其重要的作用，本节主要讲解如何设计产品成本核算系统。设计过程中会用到TODAY、YEAR、MONTH、DAY和CONCATENATE函数，这5个函数的语法及用法介绍如下。1TODAY格式：TODAY()功能：TODAY()函数用于返回当前日期2YEAR格式：YEAR(serial_number)功能：该函数用于取日期中的年。说明：参数serial_number为日期。Excel在数据统计与分析中的应用493MONTH格式：MONTH(serial_number)功能：该函数用于取日期中的月。4DAY格式：DAY(serial_number)功能：该函数用于取日期中的日。5CONCATENATE

32、格式：concatenate(text1,text2,.）功能：把多个字符文本或数值连接在一起，实现合并的功能。说明：参数Text1，Text2可以是文本或者数值，最多为 255 项，但是项与项之间必须用逗号隔开。Excel在数据统计与分析中的应用 案例要求案例要求 某公司计划设计一个产品成本核算系统，用于分析生产成本，从而对企业经营进行决策。案例案例6-2设计产品成本核算设计产品成本核算系统系统Excel在数据统计与分析中的应用516.1.3 设计新产品开发测试情况登记表 要想使企业在市场上能够立于不败之地，就必须对产品经常进行更新换代。新产品开发系统一般包括两个表格：新产品开发测试情况登记

33、表、新产品开发测试统计表。案例要求案例要求 一般企业在新产品研究成功之前都要进行大量的测试，每一次的结果都是非常重要的，本节就来学习如何设计一张表格对开发测试情况进行记录。案例案例6-3设计新产品开发测试情况登记表设计新产品开发测试情况登记表Excel在数据统计与分析中的应用526.1.3 设计新产品开发测试统计表 上节课介绍的开发测试情况登记表，可以记录信息。本节将对如何快速高效地统计这些结果进行介绍。案例要求案例要求 公司要求设计新产品开发测试统计表，对已经收集上来的记录进行统计。案例案例6-4设计新产品开发测试统计表设计新产品开发测试统计表Excel在数据统计与分析中的应用536.2 日

34、期时间函数的应用 日期时间函数是企业经营管理过程中经常使用的函数，正确使用日期时间函数才会使经济业务处理得更准确，通过对本节的学习可以掌握日期时间函数的用法，以及在企业生产和经营管理中的应用。6.2.1 产品交货日期计算 在企业生产中，有时知道生产任务的开始日期和完成生产所需的工作日，需要了解完成任务的日期，可以用WORKDAY()函数实现，该函数的语法及用法介绍如下。Excel在数据统计与分析中的应用54WORKDAY格式：WORKDAY(start_date,days,holidays)功能：WORKDAY()函数用于返回某日期（起始日期）之前或之后相隔指定工作日的某一日期的日期值，工作日

35、不包括周末和专门指定的假日，在计算发票到期日、预期交货时间或工作天数时，可以使用函数WORKDAY来扣除周末或假日。说明：参数start_date表示开始日期，参数days表示在参数start_date之前或之后指定的工作日天数，参数holidays表示需要排除在外的节假日。Excel在数据统计与分析中的应用55 案例要求案例要求 某企业接到若干订单，从接到订单开始，要求若干个工作日后必须交货，现需要根据开始日期和完成任务所需工作日，计算出完成任务的日期。案例案例6-5产品交货日期计算产品交货日期计算6.2.2 生产任务工作日计算 在企业生产中，有时知道生产任务的开始日期和计划完成日期，需要对

36、生产任务的工作日进行管理。可以用NETWORKDAYS()、TODAY()和WORKDAY()函数编写“开始日期”、“结束日期”和“工作日”及“当前日期”的函数关系式。下面简单介绍NETWORKDAYS()和TODAY()函数的语法及用法。Excel在数据统计与分析中的应用561、NETWORKDAYS格式：NETWORKDAYS(start_date,end_date,holidays)功能：NETWORKDAYS函数用于返回参数 start_date 和 end_date 之间完整的工作日数值，工作日不包括周末和专门指定的假期，可以使用函数 NETWORKDAYS，根据某一特定时期内雇员的

37、工作天数，计算其应计的报酬。说明：参数start_date表示开始日期，参数end_date表示结束日期，参数holidays在工作日中排除的特定日期。2、TODAYTODAY函数用于返回当前日期。该值在打开工作簿或重新参与运算时随着系统日期的变化而变化。Excel在数据统计与分析中的应用57 案例要求案例要求 某企业接到若干订单，从接到订单开始，要求若干个工作日后必须交货，现已知开始日期和完成日期，需要计算出完成任务所需工作日。案例案例6-6生产任务工作日计算生产任务工作日计算6.2.3 工时数计算 在企业生产中，经常会根据工作开始和结束的时间，来计算工作时数，但如果上夜班，工作结束时间会小

38、于开始时间，简单地加减运算往往不能得到正确结果。本节将介绍如何解决此类问题。Excel在数据统计与分析中的应用58 案例要求案例要求 某企业每天要统计员工的工作开始时间和结束时间，并计算出每天的工时数。案例案例6-7工时数工时数计算计算Excel在数据统计与分析中的应用596.3 企业经营管理 除了前面介绍的日期时间函数外，还有很多EXCEL函数经常应用到企业的生产和经营管理中，本节将通过案例对几个常用函数进行介绍。6.3.1 药品研发阶段成本计算 在企业的经营管理中，经常会根据产品的月成本、产品开发周期，计算产品在不同开发阶段的总成本，从而进行调控。要解决此类问题，需要用到OFFSET函数，

39、该函数语法及用法如下。Excel在数据统计与分析中的应用60OFFSETOFFSET格式：OFFSET(reference,rows,cols,height,width)功能：OFFSET函数以指定的引用为参照系，通过给定偏移量得到新的引用。返回的引用可以为一个单元格或单元格区域。说明：参数Reference作为偏移量参照系的引用区域，可以是单元格或区域。参数Rows表示相对于偏移量参照系的左上角单元格，进行上下偏移的行数，参数Cols则表示左（右）偏移的列数。参数Height表示所要返回的引用区域的行数，参数width表示所要返回的列数。Excel在数据统计与分析中的应用61 案例要求案例要

40、求 某公司研发的每种药品经过三个研发阶段。已有每种药品按月列出的成本表，已知每个研发阶段的时长，如何计算每种药品在每个研发阶段的总成本。案例案例6-8药品研发阶段成本计算药品研发阶段成本计算6.3.2 产品订单数量与单价的管理 在企业的生产与经营管理中，经常会按照产品订单的数量制定单价，本节将介绍此类问题的解决办法。Excel在数据统计与分析中的应用62 案例要求案例要求 某公司的产品单价与订购数量和付款方式有关，定价规则如下：（1）公司为产品A定价，订购数量在500件以下（含500件）的订单单价为25元，订购数量在500件以上的订单单价为20元。（2）公司为产品B定价，订购数量在500件以上

41、（不含500件）或一次性付款的订单单价为15元，否则单价为20元。换句话说，订购数量在500件以下（含500件）并且分期支付的订单单价为20元，否则单价为15元。编写单价与订单数量和付款方式之间的函数关系式，并计算货款总额。案例案例6-9产品订单数量与单价的管理产品订单数量与单价的管理Excel在数据统计与分析中的应用63 案例要求案例要求 某公司有10家连锁商店，已知连锁商店的各类产品的销售总额。打开案例6-10.xlsx工作簿，有“销售总额”、“商店销售总额”和“产品销售总额”工作表，查询各商店的销售总额，查询各类产品的销售总额。案例案例6-10连锁店与各类产品销售情况汇总表连锁店与各类产

42、品销售情况汇总表6.3.3 连锁店与各类产品销售情况汇总表 在企业的经营管理中，经常会在各类情况表中搜索需要的信息进行汇总。Excel中的LOOKUP、VLOOKUP、HLOOKUP函数可以解决此类问题，本节将重点讲述这3个函数在企业经营管理中的应用。第七章 ExcelExcel在市场销售管理中的应用在市场销售管理中的应用Excel在数据统计与分析中的应用65 员工销售量查询 库存产品查询 产品批发零售销售额及利润计算 销售金额等级评价与奖金 员工业绩考核管理 编制进货单本章学习目标Excel在数据统计与分析中的应用66员工销售量查询会用到MATCH、ADDRESS和INDIRECT函数，这3

43、个函数介绍如下：（1）函数MATCH可在单元格区域中搜索指定项，然后返回该项在单元格区域中的相对位置。语法如下：MATCH(lookup_value,lookup_array,match_type)（2）函数ADDRESS可以根据指定行号和列号获得工作表中的某个单元格的地址。语法如下：ADDRESS(row_num,column_num,abs_num,a1,sheet_text)7.1员工销售量查询Excel在数据统计与分析中的应用67（3）INDIRECT函数返回由文本字符串指定的引用。语法如下：INDIRECT(ref_text,a1)员工销售量查询案例要求打开案例7-1.xlsx工作簿

44、，进行如下操作：（1）在“员工销售量”工作表中，有10位员工的产品销售数据，根据该数据在“员工销售量查询”工作表中，利用数据验证设置“月份”与“姓名”的下拉选择列表，从而可以选择员工的姓名及月份。案例案例7-1Excel在数据统计与分析中的应用68（2）在“员工销售量查询”工作表中，选定员工的姓名及销售月份后，通过ADDRESS和MATCH函数找到所要查询信息在工作簿中单元格的位置；根据查询信息所在位置的单元格，通过INDIRECT函数查询出本月销售量。7.2 7.2 库存产品查库存产品查询询对库存产品进行查询需要用到一些函数，如ADDRESS、MATCH、ROW、COLUMN、INDIREC

45、T 和INDEX函数等，其中ROW、COLUMN、INDIRECT和INDEX函数的语法和用法如下。Excel在数据统计与分析中的应用69（1）函数ROW返回引用的行号。语法如下：ROW(reference)（2）函数COLUMN返回引用的列标。语法如下：COLUMN(reference)（3）函数INDIRECT返回由文本字符串指定的引用，该函数立即对引用进行计算，并显示其内容。语法如下：INDIRECT(ref_text,a1)（4）函数INDEX返回指定的行与列交叉处的单元格引用。语法如下：INDEX(reference,row_num,column_num,area_num)Excel

46、在数据统计与分析中的应用70 库存产品查询案例要求 打开案例7-2.xlsx工作簿，进行如下操作：（1）在“库存表”工作表中给出了产品信息，根据该信息在“产品库存查询”工作表中输入抽查编号，通过ADDRESS和MATCH函数找到所要抽查的编号在工作簿中单元格的位置。（2）根据B2单元格中查询出的结果，通过ROW和COLUMN函数显示所查询出的单元格所在的行和列。（3）通过INDEX函数找到与抽查编号所对应的产品名称和库存量。案案例例7-2Excel在数据统计与分析中的应用717.3 7.3 产产品批发零售销售额及利润计算品批发零售销售额及利润计算统计产品的销售额和销售利润，会用到两个查找函数：

47、VLOOKUP（按列查找）和HLOOKUP（按行查找）。这两个函数的语法如下。VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)HLOOKUP(lookup_value,table_array,row_index_num,range_lookup)Excel在数据统计与分析中的应用72 产品批发零售销售额及利润计算案例要求打开案例7-3.xlsx工作簿，在“价格表”工作表中已经给出了家电产品的进货、批发和零售价格。进行如下操作：（1）在“销售额统计”工作表中，完成批发价格、零售价格、批发金额、零售金额和进货金额的计算。（2）

48、在“销售利润”工作表中，完成进货金额、批发金额、零售金额和利润的计算。案案例例7-3Excel在数据统计与分析中的应用737.4 7.4 销销售金额等级评价与奖金售金额等级评价与奖金对销售金额等级进行评价和奖金管理，需要用到查找函数VLOOKUP或LOOKUP，选择函数CHOOSE。简单介绍LOOKUP和CHOOSE函数如下。（1）LOOKUP 函数从单行或单列区域（区域:工作表上的两个或多个单元格。区域中的单元格可以相邻或不相邻。）或数组（数组:用于建立可生成多个结果或可对在行和列中排列的一组参数进行运算的单个公式。数组区域共用一个公式；数组常量是用作参数的一组常量。）返回值。它有两种语法形

49、式：向量形式和数组形式。Excel在数据统计与分析中的应用747.4 7.4 销销售金额等级评价与奖金售金额等级评价与奖金向量形式：在单行区域或单列区域（称为“向量”）中查找值，然后返回第二个单行区域或单列区域中相同位置的值。当要查询的值列表较大或者值可能会随时间而改变时，使用该形式。语法如下。LOOKUP(lookup_value,lookup_vector,result_vector)注意：result_vector参数必须与lookup_vector参数大小相同。数组形式：在区域（称为“数组”）的第一行或第一列中查找指定的值，然后返回区域的最后一行或最后一列中相同位置的值；当要查询的值列

50、表较小或者值在一段时间内保持不变时，使用该形式。语法如下。LOOKUP(lookup_value,array)Excel在数据统计与分析中的应用757.4 7.4 销销售金额等级评价与奖金售金额等级评价与奖金（2）选择函数CHOOSE，语法如下。CHOOSE(index_num,value1,value2,.)参数index_num是必需的，必须是介于1到254之间的数字，或是包含1到254之间的数字的公式或单元格引用。如果index_num为1，则CHOOSE返回value1；如果为2，则CHOOSE返回value2，以此类推；如果index_num小于1或大于列表中最后一个值的索引号，则