chapter-05公司名称函数Excel与数据处理(第3版)[杜茂康][电子教案]

华信教育资源网www.hxedu.com.cnExcel与数据处理第3版华信教育资源网www.hxedu.com.cn认识函数好比EXCEL的武林秘笈，如果要成为EXCEL高手，谁都必须学习它。函数也是EXCEL与Word之类软件中的表格的主要区别，它使EXCEL具有强大的运算能力。EXCEL提供了涉及各个不同领域的函数接近400个。运用它们解决工作中的实际问题，能够极大提高工作效率，使枯燥的工作变得轻松、简单。但学习是一个枯燥而艰辛的过程！当然嘛，谁让你想成为高手呢？华信教育资源网www.hxedu.com.cn本章学习目标1、掌握数组公式的用法2、掌握名称的用法3、掌握常用工作表函数的用法4、掌握常见统计函数的用法5、掌握条件函数的用法，特别是：If\sumif\countif\等6、掌握解查找引用类函数的用法7、掌握常见日期、文本类函数华信教育资源网www.hxedu.com.cn5.1数组公式及其应用1、概述普通公式只执行一个简单计算，并且返回一个运算结果。数组公式能够同时对一组或两组以上的数据进行计算，计算的结果可能是一个，也可能是多个。在数组公式中使用的数据称为数组参数，数组参数可以是一个数据区域，也可以是数组常量。华信教育资源网www.hxedu.com.cn5.1数组公式及其应用2、数组公式的建立方法（1）选中需要保存数组公式结果的单元格或单元格区域。（2）输入公式的内容。（3）按Ctrl+Shift+Enter键。华信教育资源网www.hxedu.com.cn5.1数组公式及其应用3、用数组公式计算两个数据区域的乘积【例5.1】某茶叶店经销多种茶叶，已知各种茶叶的单价以及12月份各种茶叶的销量如图所示，计算各种茶叶的销售额。华信教育资源网www.hxedu.com.cn5.1数组公式及其应用4、数组扩展在公式中用数组作为参数时，所有的数组必须是同维的。如果数组参数或数组区域的维数不匹配，Excel会自动扩展该参数。比如，LG空调售价为7654元，重庆各商场的销售数据如下图的B、C两列所示。现要计算各商场的销售总额。华信教育资源网www.hxedu.com.cn5.1数组公式及其应用【例5.2】某次期末考试的学生成绩表如图5.2所示，其中有四科成绩，现要计算各科成绩的总分，以及各科成绩的综合测评分数。总分的计算方式为各科成绩的总和，综合测评分的计算方式为“政治经济学*0.2+(技术经济学+计算机基础+PASCAL语言程序设计)*0.8”。华信教育资源网www.hxedu.com.cn5.1数组公式及其应用【例5.3】假设某商场A型电视机售价一样，现有一个28天的销售记录，计算每天的销售额，如图5.3所示。D列销售额的计算方法如下：华信教育资源网www.hxedu.com.cn5.1数组公式及其应用5、二维数组涉及许多行(或列)数据处理的数组公式，就是所谓的二维数组。合理地运用二维数组的运算功能，会提高数据处理的能力，有时在不同工作表之间进行数据汇总时会特别有效。华信教育资源网www.hxedu.com.cn5.1数组公式及其应用案例【例5.4】某商场秋季进行换季服装大降价，所有服装都在打折，打折的比例随时都在调整，计算打折后的新价格。如图5.4所示。华信教育资源网www.hxedu.com.cn5.1数组公式及其应用【例5.5】出版社要统计2008年发往各地新华书店的各类图书的销售量，并由此计算出各类图书的总销售额。设每个省都把本地各新华书店的销售量统计在一个工作表中，最后有许多工作表的数据要进行汇总，图5.5该出版社2008年各类图书在四川、重庆两者的销量情况表（实际的应用更加复杂）。华信教育资源网www.hxedu.com.cn5.1数组公式及其应用用数组公式汇总图书华信教育资源网www.hxedu.com.cn5.2公式的循环引用1、循环引用的概念如果公式引用了自己所在的单元格，不论是直接的还是间接的，都称为循环引用。在一般情况下，很少使用循环引用来处理问题。但对于迭代求解一类问题，如求数的阶乘、数列求和等问题，可以用循环引用求解。若循环引用的条件或迭代次数设置不当，EXCEL会报告一个错误信息。华信教育资源网www.hxedu.com.cn5.2公式的循环引用【例5.6】下图是循环引用的一个事例。在A1单元格中输入的公式“=A2+A3”；在A2单元格中输入数字2；在A3单元格中输入公式“=A1*0.3”，见图(a)。这样，A3单元格的值依赖于A1，而A1单元格的值又依赖于A3，它们形成了间接的循环引用，在未进行迭代求解情况下的结果如图（b）所示。该循环引用是可解的，进行迭代求解最后的结果如图（c）所示。华信教育资源网www.hxedu.com.cn5.3名称5.3.1、名称概述可以给一个单元格或单元格区域取一个名称，这个名称可以出现在公式中，用来指代引用的单元格或单元格区域，这会使公式的意义更加明确。名称其实是一个标识符，由字母或下划线开头的一个或多个字符组成。名称不区分字母的大小写，比如ABC、abc、Abc都是同一个名称。在Excel中，还可以用汉字作为名称。可以为同一单元格或单元格区域定义多个不同的名称。名称一旦定义就可以在同一工作薄的不同工作表之间共用。华信教育资源网www.hxedu.com.cn5.3名称2、名称的意义在Excel中，同一个工作簿中的名称是共享的，在一个工作表中定义的名称，可以被另一个工作表的公式引用，这使公式具有更强大的功能，它能够利用单元格的名称在不同的工作表中查找到正确的单元格或单元格区域。名称给单元格的引用带来了许多方便，因为它使人们不必记住单元格在工作表中的引用位置，用名称就能找到它。华信教育资源网www.hxedu.com.cn5.3.2名称的定义在Excel中，可以为一个独立的单元格、连续的单元格区域或许多不连续的单元格构成的单元格组合定义一个名称（或多个名称）定义名称有多种方法，如定义、粘贴、指定或标志等华信教育资源网www.hxedu.com.cn5.3.2名称的定义(1)、使用命令定义单元格或单元格区域的名称【例5.7】在下图中，B2单元格被命名为“存款利率”，在D5中输入的公式是“=C5*存款利率”，其中的“存款利率”就是B2单元格的名称。B2单元格的名称定义方法如下：华信教育资源网www.hxedu.com.cn5.3.2名称的定义（1）单击“公式”|“定义的名称”组中|“定义名称”命令按钮.（2）在弹出的“新建名称”对话框的“引用位置”输入要定义名称的单元格引用位置。（3）在“在当前工作簿中的名称”中输入名称华信教育资源网www.hxedu.com.cn5.3.2名称的定义(2)、使用指定的方式定义名称“指定”的方式一次可定义多个名称：可以将表格的首行或首列指定为相应的列或行的名称。方法（1）选中要指定名称的单元格区域。（2）单击“公式”|“定义的名称”组中|“根据所选内容创建”命令按钮。（3）在弹出的“以选定区域创建名称”对话框中，选中“首行”、“最左列”的复选框。这样就把该表的首行、首列指定成了相应的列、行的名称华信教育资源网www.hxedu.com.cn5.3.2名称的定义指定首行、最左列为名称的案例【例5.8】图5.10是某建筑工地的工人奖金统计表，一共有几百个工人，其中的某些工人会随时查询自己的奖金，这就需要在A列的几百行数据中进行查找。名字能够很好地解决这类问题，方法如下。华信教育资源网www.hxedu.com.cn5.3.2名称的定义(3)用名称栏定义名称工作表全选按钮（工作表列标A左边的按钮）上面的编辑框称为名称栏，它随时显示当前单元格的名称或引用。它可以用来定义名称。【例5.9】在图5.11中，C4:C8区域中输入的是数组公式：“=数量*单价”，其定义方法请见图中的标注。名称栏华信教育资源网www.hxedu.com.cn5.3.3名称的应用定义一个工作薄级的名字后，该名字就能被本工作簿中的各个工作表直接引用。【例5.10】图5.12是某蔬菜超市的销售记录。该超市把蔬菜单价存放在一张工作表中，如图5.12（a）所示，每种蔬菜的销售记录则保存在不同的工作表中，如图5.12（b）所示。现以小白菜的销售金额计算为例，说明名字的跨表引用问题。华信教育资源网www.hxedu.com.cn5.3.3名称的应用1）通过名称在不同的工作表之间传递数据通过名称查找蔬菜单价指定A2:B12最左边为名称华信教育资源网www.hxedu.com.cn5.3.3名称的应用2)名称结合Indirect函数的应用指定A2:B12的最左列为名称；用名称查找蔬菜单价，见D列；若不用名称，单价要从C列的几百个数据中去查找！这种方法虽然比直接从工作表的单元格中查找数据方便了不少，但输入量仍然较大。用它处理成千上万行的数据查询仍然低效率。最佳方法是用名称和Indirect函数相结合，非常方便，高效。华信教育资源网www.hxedu.com.cn5.3.3名称的应用某蔬菜商在一个工作表中保存蔬菜的单价，在另一工作表中保存销售记录，现要查找销售记录表中各蔬菜的单价。指定A2:B11区域的“最左列”为名称在D2中输入公式：=INDIRECT(B2)向下复制该公式！华信教育资源网www.hxedu.com.cn5.3.3名称的应用2）名称与Indirect函数结合应用Indirect函数与名称相结合，可使数据查找更方便。Indirect函数的用法是：Indirect(x)其中的X可以是单元格引用或名称。若X为单元格引用，它必须用“”引起来，如：=INDIRECT(“A1”)，将返回A1单元格中的内容若X为单元格名称，它将返回该名称所对应的单元格的内容。华信教育资源网www.hxedu.com.cn5.3.3名称的应用3)应用行列交叉点查找数据所谓行列交叉点指的是由[行的名称列的名称]确定的单元格。把一个工作表的首行、首列指定为名称之后，可以使用名称引用行、列交叉点对应单元格中的数据。案例【例5.11】图5.13是一个学生成绩表，用指定的方式把它的首行、首列指定成了名字。华信教育资源网www.hxedu.com.cn5.3.3名称的应用1、建立如下表格并选中A1:F8区域2、选择“公式”|“定义的名称”|“根据所选内容创建”命令4、指定名字对话框中首行和最左列项为名称现在做试验1、选中K2:K8,输入“=化学”,按“Ctrl+Shift+Enter”2、选中A10:F10,输入“=李四”,按“Ctrl+Shift+Enter”3、在L2单元格中输入公式“=李达化学”华信教育资源网www.hxedu.com.cn5.3.3名称的应用4)引用其他工作簿中的数据当一个公司的数据来源于不同的分公司或部门时，往往需要对不同分公司或部门的数据进行汇总，这就要涉及多个工作薄的数据操作。名称可以很好地解决这个问题。【例5.12】采虹电视经销全国各省，每季度都要统计各种型号电视的销售数量。各省的销售代理每季度向采虹厂提供一个含有各种型号电视的汇总统计表。现在对各省的报表数据进行汇总统计。华信教育资源网www.hxedu.com.cn指定A3:B8区域”最左列”为名称指定A3:B8区域”最左列”为名称B3单元格中输入公式“=四川.xls!_21英寸+重庆.xls!_21英寸”；在B4单元格中输入公式“=四川.xls!_25英寸+重庆.xls!_25英寸”，其余以此类推……华信教育资源网www.hxedu.com.cn5.3.4定义常量名称1)常量名称的意义用名字代表常量，如圆周率3.14159，自然对数的底数e等，然后在整个工作簿中引用这些常量名，会给数据处理带来许多方便，这使用户在运用这些数据时不必关心它所在的工作表或单元格，直接用其名字就可以了。常量名称的另一个好处:当常量值变化时,只需要修改常量定义就行了,不必须在各具工作表的公式中寻找常量值并进行