2-Excel公式与函数

说明这部分应提供与演示文稿相对应的讲义。模块二Excel公式与函数》导学本讲概述熟练掌握Excel公式与函数的使用，Excel数据的排序、筛选和分类汇总。学习重难点熟练掌握Excel公式与函数的使用，Excel数据的排序、筛选和分类汇总关键词：公式函数排序筛选分类汇总第二讲Excel公式与函数1.单元格引用电子工作表中的每个单元格都对应着一个唯一的列标和行号，由列标（字母大、小写等效）和行号组成该单元格的地址。例如，B16就是一个单元格的地址，该单元格处于列标为B的列与行号为16的行的交点位置。单元格引用就是单元格的地址表示。单元格的地址表示可以细分为相对地址表示、绝对地址表示和混合地址，分别也称做相对引用、绝对引用和混合引用，它们在书写格式和含义上有所不同。相对引用：就是直接用列标和行号所表示的单元格地址。如B3、F8、K23等都是对应单元格的相对地址引用。绝对引用：分别在列标和行号的前面加上$字符所表示的单元格地址称为绝对引用。如$C$10就是第C列和第10行交点位置单元格的绝对引用，该单元格的相对引用为C10。混合引用：就是列标或行号之一采用绝对地址的单元格引用。如$F7就是一个混合地址引用，F$5也是一个混合地址引用，它们一个列标采用绝对地址，一个行号采用绝对地址。上面的单元格地址引用只限于在同一个工作表中使用，若要引用不同工作表中的单元格，则必须在单元格地址表示的前面加上工作表名和后缀一个！字符。这就构成了单元格地址的三维表示，包括工作表、列和行。如Sheet1！B3是Sheet1工作表中的第B列第3行的单元格的相对地址表示（相对引用）；Sheet2！$D$13是Sheet2工作表中的第D列第13行的单元格的绝对引用。2.Excel公式在Excel中，所谓公式就是一个运算表达式，它由运算对象和运算符按照一定的规则和需要连接而成。运算对象可以是常量，即直接表示出来的数字、文本和逻辑数据。如602为数字常量，“副教授”为文本常量，TRUE和FALSE为逻辑常量真和假。运算对象还可以是单元格引用，如Al、B$2等；还可以是公式或函数，如（B7+C7)和SUM(F3:F10)等。公式中的运算符包括算术、比较、文本连接和逻辑四种类型。算术运算符：有加+、减-、乘*、除/、百分号％、乘幂∧6个。如C3+5就是一个加法公式，它的值是C3单元格的值与常量5之和。比较运算符：有等于=、大于、大于等于=、小于、小于等于=、不等于共6个。如$F5=60就是一个比较公式（条件)，当单元格F5的值大于或等于60时，其结果为逻辑值TRUE，否则为逻辑值FALSE。文本连接运算符：只有一个运算符号&，它把前后两个文本连接成一个文本。如C3&D3就是一个文本连接公式，若C3单元格的值为“张三”，D3单元格的值为“同学”，运算后得到的值为“张三同学”。逻辑运算符：有3个，分别为逻辑与（AND)、或（OR)、非(NOT)，运算符中的字符大小写均可。参与运算的对象在其后面的圆括号内给出，“与”、“或”运算可以带有多个对象，对象之间用逗号分开，而“非”运算只能带有一个对象。逻辑运算中的每个对象可以是一个逻辑常量TRUE或FALSE，可以是一个比较运算公式（因其运算结果是一个逻辑值)，也可以是任何数子或文本（系统把0看做逻辑值假，非0值看做逻辑值真)，还可以是一个结果为逻辑值的函数或另一个逻辑公式。3.Excel函数Excel函数可以作为公式中一个运算对象，也可以作为整个公式来使用。在Excel中提供给用户直接使用的函数有许多种类，每种类别中又包含有各种具体的函数。从大类上讲有数学与三角函数、统计函数、逻辑函数、日期与时间函数、财务函数、文本函数等。这里只介绍一些常用且少量的几个函数，如表4-1所示，其他大量的函数请参考Excel的“帮助”系统。表4-1Excel中的部分函数函数名类别功能AVERAGE(x)统计求出X所表示的一组数值的平均值COUNT(x)统计求出X所表示的—组数字数据的个数COUNTA(x)统计求出X所表示的一组非空数据的个数MAX(x)统计求出X所表示的~•组数值中的最大值MIN(x)统计求出X所表示的一组数值中的最小值SUM(x)数值计算求出X所表示的一组数值之和RAND()数值计算返回0到1之间的一个随机数IF(x，y，z)逻辑当表示条件的x为真时执行y操作，否则执frz操作NOW()日期与时间返回系统当前的日期和时间TODAY()日期与时间返回系统当前的日期YEAR(x)曰期与时间返回X所表示日期中的年份MONTH(x)日期与时间返回X所表示日期中的月份DAY(x)曰期与时间返回X所表示日期中月内的天数4.在单元格中输入公式在一个单元格中，不仅能够输入和使用直接表示出来的数字、文本和逻辑数据，而且能够输入和使用公式，由系统根据公式计算出相应的值并显示出来。为了区别于直接输入的数字或文本，在输入公式时必须以等号=作为前导（先导、前缀）符号，如在某单元格中输入的公式“=b2*c2”。在向一个单元格输入公式时，其输入的公式同时在单元格和“编辑栏”的“文本编辑框”中显示出来，当输入结束并按下Tab或Enter键，或单击“编辑栏”中的确认按钮后，在单元格中只显示出公式计算的结果（值)。当再次选择此单元格时，将在此单元格显示公式的值，而在“编辑栏”的“文本编辑框”中显示其公式。若需要编辑此公式，可双击此单元格或单击文本编辑框使光标停留其中，接着可插入、修改和删除公式的内容。在工作表中某列的第一个单元格中输入公式求出值，若需要应用此公式求出该列其他单元格的值时，则可以使用填充柄的功能。即单击求出值的单元格，然后在该单元格的右下角出现一个填充柄，单击填充柄并向下拖曳到该列最后一个单元格后松开，这样就完成了所有公式的运算。演示5.单元格中函数的输入方法(1)在单元格中输入函数：此种方法与输入公式相同，即先输入一个等号=作为先导符，再输入函数，然后按Tab或Enter键，或单击编辑栏中的确认按钮结束输入。(2)从对话框中输入函数：在单击选择要输入函数的单元格后，单击编辑栏中的插入函数按钮fx或者单击“公式”选项卡中“函数库”组内的“插入函数”按钮或其他类别的函数按钮。当单击任一类别的函数按钮时，将随即打开一个包括该类别所有函数的下拉菜单，供用户选择；当单击插入函数按钮fx后，将打开“插入函数”对话框，如图4-5所示。若输入的函数存在于“选择函数”列表框中，则单击它后再单击“确定”按钮进入下一步；若不在，则从“选择类别”下拉列表框中挑选函数类别，再从“选择函数”中挑选出函数，接着进入下一步。图4-5“插入函数”对话框单元格的函数输入还可以通过“开始”选项卡中“编辑”组内的“自动求和”下拉菜单按钮“∑自动求和”来实现。课后练习1.当进行Excel2010中的分类汇总时，必须事先按分类字段对数据表进行_______。A.求和B.筛选C.查找D.排序2.在Excel2010中，对数据表进行排序时，在“排序”对话框中能够指定的排序关键字个数限制为公式为_______。A.1个B.2个C.3个D.任意3.在Excel2010的自动筛选中，每个标题上的下三角按钮都对应一个_______。A.下拉菜单B.对话框C.窗口D.工具栏答案：DDA