浅谈电子表格中的公式和函数

浅谈电子表格中的公式和函数

excel是微软办公室软件之一。可以处理各种数据、统计分析和辅助决策。它广泛应用于管理、统计、金融等领域。Excel中公式函数,Excel提供了涉及不同领域的函数以及公式功能,正确的使用函数和公式,可以提高运算速度和准确度。因此,在Excel教学过程中,公式和函数方面的教学成为了教学重点和难点。1以公式和函数的概念来考虑,提高学生的实践能力。根据今后Excel中的数值运算是由公式和函数完成的,在Excel教学中,首先要让学生们弄清楚公式和函数的概念,使得在以后的实际应用中能够根据自己的需要进行正确的选用。1.1运算运算符的分级Excel中的公式是指对表中的数据进行计算的等式,它是必须以“=”开始。例如,“=B3+C3-D4”就是一个公式。公式中的运算对象可以是常量、单元格或单元格区域的引用、函数等。运算符表明完成的是何种计算,在Excel中包含如下四种类型的运算符:1)算术运算符:+(加)、-(减)、*(乘)、/(除)、%(百分号)、^(乘方)。2)比较运算符:=(等于)、>(大于)、<(小于)、>=(大于等于)、<=(小于等于)、<>(不等于)。比较运算公式返回的结果为TRUE(真)或FALSE(假)。例如,在A1单元格中输入公式“=3>5”后,A1单元格显示的值为FALSE。3)文本运算符:&是文本连接符,例如:A1单元格中的内容为“’111111”,在B1单元格中输入“=”密码:”&A1”后,B1单元格中的内容为“密码:111111”。4)引用运算符:区域运算符“:”,它是对两引用之间(含该两引用本身)的所有单元格的引用;联合运算符“,”,它是将多个引用合并为一个引用;交叉运算符“”(空格),它是产生同属于两引用区域的引用。这些运算符的优先级由高到低如图1所示。若优先级相同,则按从左至右的顺序运算。1.2函数功能简介函数是Excel提供的用于数值计算和数据处理的现成的公式。它是由函数名和参数组成的,其语法形式为:“函数名(参数1,参数2……)”,其中函数的参数可以是常量、单元格或单元格区域、其它函数等。Excel中提供了两百多个函数,这些函数功能很强大。根据函数功能或运算数的特点,分成数据库函数、日期和时间函数、工程函数、财务函数、信息函数、逻辑函数、查找函数、数学函数、外部函数、统计函数、文本和数据函数等11大类。函数的输入可以直接输入(前提是该函数已经较为熟悉),也可以选择插入函数方法。2对案例进行设计在Excel教学中,一般需要在讲解中配合多些实例操作,使得讲解更为透彻和易于理解,但是课堂时间毕竟有限,所以需要对这些案例进行精心的设计。同时,由于Excel中的公式和函数在实际应用中较为广泛,而且会碰到多种情况,在实际教学中可能无法面面俱到,需要做的是通过一些例题的讲解,开拓学生的思维,让他们掌握方法。2.1单元格相对引用1)公式输入“=”开头Excel中公式应用比较多,在教学中强调输入公式时一定要输入“=”。在教学中,可以以如下的例子加以说明,要计算“3/10”的值,首先在A1单元格内输入“3/10”,结果得到的是如图2(a)的结果,是一个日期,正确的做法应该是在A2单元格内输入“=3/10”,这是因为题目要求是求值,所以应该输入公式,而且公式必须以“=”开头,该结果如图2(b)。2)公式中有单元格引用在Excel中进行公式输入时,有的运算数可能不是常量,而是单元格的引用。在教学中,可采用如下的实例如图3加以讲解。图3中的实例可以看出如果要求TCL的销售量小计,若不使用函数,则在F3单元格内应该输入“=C3+D3+E3”,其中C3、D3、E3是单元格的相对引用,且C3、D3、E3这些单元格地址建议不是从键盘输入,而是用鼠标单击所需单元格即可,而运算符必须从键盘输入。在实际教学中,有的同学在F3内会输入“=85+55+78”,那么讲授时可以强调所需数据都是来源于现有的单元格,就可以直接使用单元格引用了,便于数据的更新,例如,若E3中的内容修改为80,若F3单元格内输入的是“=C3+D3+E3”,则F3的内容会自动更新为一新值,而若F3单元格内输入的是“=85+55+78”,则意味着F3单元格的内容需要人工修改,否则数据有误。接下来,使用公式复制,即拖拽填充柄就完成了其他品牌的“销售量小计”列的计算,此外,在此处可以配合讲解单元格的相对引用的概念:公式中的相对单元格引用是基于包含公式和单元格引用的单元格的相对位置。强调并展示如果公式所在单元格的位置改变,引用也随之改变。类似的,可以在F7单元格内输入“=F3+F4+F5+F6”,完成销售量总计的计算。在G3单元格内要求填入的是销售金额,应该就是求值:销售量小计*单价,因此,在G3单元格内输入“=B3*F3”,再使用公式复制即可完成“销售金额”列的计算。在计算销售量比例,即使用公式“销售量小计/销售量总计”,这个公式看似很简单,例如,有的同学可能是这样做的:在计算TCL的销售量比例时,在H3单元格内输入“=F3/F7”,再使用公式复制,这时H4等单元格会出现如图4所示的错误,该错误“#DIV/0!”是由于H3单元格公式中除数为单元格相对引用导致的。可以具体展开如下分析:在计算TCL的销售量比例时,在H3单元格中输入“=F3/F7”;在计算康佳的销售量比例时,在H4单元格中输入“=F3/F7”;类似的分析,得出所要求输入的公式中除数F7是不变的,因此,F7单元格的引用应该是绝对引用,即在H3单元格中输入“=F3/$F$7”,再进行公式复制即可。3)公式中有常量在教学中,要强调Excel公式中除了单元格引用外,还有可能会出现常量的使用,例如图5中的实例,总评成绩的计算公式为“=平时*25%+期中*15%+期末*60%”。因此,先计算学号为001的总评成绩,即在E2单元格中输入“=B2*25%+C2*15%+D2*60%”,再进行公式复制即可计算出其他人的总评成绩。2.2平均值的函数Excel中函数有两百多个,在课堂教学时特别是公共课教学时不可能把所有函数都作一一介绍,这就需要教师引导学生在使用函数时要注意函数功能的说明以及参量的要求。例如,在课堂教学时,可以以图6中的实例对函数的使用加以说明。若要计算学号“001”的平均分,除了在F3单元格内输入公式“=(B3+C3+D3+E3)/4”外,则更多的而且更为方便的是在F3单元格中使用求平均值的函数average,具体操作是,选择F3单元格后,选择“插入”菜单中的“函数”,在教学中,鼓励学生在插入函数对话框中自己去找求算术平均值的函数,并要求学生仔细观察“插入函数”对话框。注意当无法确定所需函数的种类时,可以选择类别为“全部”,当每选定一个函数时,窗口的列表框下方会显示该函数的功能。当函数确定后,出现如图7的函数参数窗口,要求仔细阅读函数参数的说明信息,再进行参数的设置即相关的单元格或单元格区域的选择,也有可能参数是常量。这样,确定后在F3单元格中使用的是函数“=AVERAGE(B3:E3)”,再进行公式复制,即可完成“平均分”列的计算。类似的,使用sum函数求总分,max函数求最高分,min函数求最低分,rank函数求排名。其中,rank函数较为复杂,在教学时可以要求学生先看看每一个参数的说明,谈谈他们的理解,再给予相应的解答,强调Re参数是一组数的引用,在本题中这组参与排名的数的区域是固定不变的,所以该单元格区域的引用应该是绝对引用。因为Excel中的函数比较多,在教学中只能介绍一些常用的简单函数,因此,一定要求学生注意观察和理解“插入函数”对话框中出现的函数功能说明文字以及“函数