公式与函数.ppt

,单击此处编辑母版标题样式,单击此处编辑母版文本样式,第二级,第三级,第四级,第五级,*,*,单击此处编辑母版标题样式,单击此处编辑母版文本样式,第二级,第三级,第四级,第五级,*,*,单击此处编辑母版标题样式,单击此处编辑母版文本样式,第二级,第三级,第四级,第五级,*,*,单击此处编辑母版标题样式,单击此处编辑母版文本样式,第二级,第三级,第四级,第五级,*,*,3,公式与函数,在单元格建立公式,相对参照位址与绝对参照位址,函数的使用方法,插入函数的综合练习,-,奖金发放作业,不需要公式也能得知计算结果,3-1,在单元格建立公式,当我们需要将工作表中的数字数据做加、减、乘、除,等运算时，可以把计算的动作交给,Excel,的公式去做，省去自行运算的工夫，而且当数据有变动时，公式计算的结果还会立即更新。,3-1-1,公式的表示法,Excel,的公式和一般数学公式差不多，数学公式的表示法为：,若将这个公式改用,Excel,表示，则变成要在,A3,单元格中输入：,意思是,Excel,会将,Al,单元格的值十,A2,单元格的值，然后把结果显示在,A3,单元格中。,3-1-2,输入公式,输入公式必须以等号,“,=”,起首，例如,=Al+A2,，这样,Excel,才知道我们输入的是公式，而不是一般的文字数据。现在我们就来练习建立公式，请开殷范例文件,Ch03-01,，我们已在其中输入了两个学生的成绩：,我们打算在,E2,单元格存放,“,王书桓的各科总分”，也就是要将,“,王书桓”的英文、生物、,理化分数加总起来，放到,E2,单元格中，因此将,E2,单元格的公式设计为,“,=B2,十,C2+D2,”,。,请选定要输入公式的,E2,单元格，并将指针移到数据编辑列中输入等号”,=”,：,函数方块,在此输入“”,接着输入“,=”,之后的公式，请在单元格,B2,上单击，,Excel,便会将,B2,输入到数据编辑列中：,此时,B2,被虚线框包围住,B2,自动输入到公式中,再输入，,+,，然后选取,C2,单元格，继续输入，,+,，选取,D2,单元格，如此公式的内容便输入完成了：,最后按下数据编辑列上的翰入钮 或按下 键，公式计算的结果马上显示在,E2,单元格中：,数据编辑列会显示公式,单元格显示公式计算的结果,Tip:,若想直接在单元格中查看公式，可按下,+,键,键在,键的上方,，在公式和计算结果间做切换。,你也可以直接在,E2,储存格中，以键盘直接输入,“,=82,十,C2,十,D2,”、,再按下,Enter,键来输入公式,3-1-3,自动更新结果,公式的计算结果会随着单元格内容的变动而自动更新。以上例来说，假设当公式建好以后，才发现”王书桓”的英文成绩打错了，应该是”,90”,分才对，当我们将单元格,B2,的值改成”,90”,，,E2,单元格中的计算结果立即从,220,更新为,225,：,自动更新计算结果了,切换相对引用与绝对参照地址的快速键,键可循序切换单元格地址的参照类型，每,按一次,键，参照地址的类型就会改变，其,切换结果如右：,接着输入,“,+C3”,，再按下,F4,键将,C3,变成,$C$3,，最后按下 键，公式就建立完成了：,D3,的公式內容,D2,及,D3,的公式分别是由相对地址与绝对地址组成，但两者的计算结果却一样。到底它们差别在哪里呢？选定,D2,：,D3,单元格，拉曳填满控点到下一栏，将公式复制到,E2,：,E3,储存格：,计算结果不同了,相对地址公式,D2,的公式,=B2+C2,，使用了相对,位址，表示要计算,D2,往左找两个储存,格,(B2,、,C2),的总和，因此当公式复制,到,E2,单元格后，便改成从,E2,往左找两,个储存格相加，结果就变成,C2,和,D2,相,加的结果：,绝对地址公式,D3,的公式,=$B$3,十,$C$3,，使用了绝,对位址，因此不管公式复制到哪里，,Excel,都是找出,B3,和,C3,的值来相加，所以,D3,和,E3,的结果都是一样的：,3-2-3,混合参照,如果在公式中同时使用相对引用与绝对参照，这种情形称为混合参照，例如：,这种公式在复制后，绝对参照的部份,(,如,$Bl,的,$B),不会变动，而相对引用的部份则会随情况做调整。,绝对参照,相对参照,绝对参照,相对参照,我们继续沿用范例文件,Ch03-02,做练习，请依照下列步骤将,E3,单元格中的公式改成混合参照公式,=$B3+C3,：,双按,E3,单元格，将插入点移至”,=”,之后，接着按两次,F4,键，让,$B$3,变成,$B3,。,将插入点移至,“,+,”,之后，按,3,次,F4,键将,$C$3,变成,C3,，最后按下,enter,键，公式便输入完成。,接着选定,F3,，分别拉曳填满控点至,F3,及,E4,：,E4,单元格,F3,单元格,B4,单元格为,0,，,C4,单元格也为,0,B3,维持,1215,，,D3,为,2180,3-3,函数的使用方法,3-3-1,函数的格式,函数是,Excel,根据各种需要，预先设计好的运算公式，可让你节省自行设计公式的时间，底下我们就来看看如何运用,Excel,的函数。,每个函数都包含三个部份：函数名称、自变量和小括号。我们以加总函数,SUM,来说明：,SUM,即是函数名称，从函数名称可大略得知函数的功能、用途。,小括号用来括住自变量，有些函数虽没有自变量，但小括号还是不可以省略。,引敷是函数计算时所必须使用的数据，例如,SUM(1,，,3,，,5),即表示要计算,1-3-5,三个数字的总,和，其中的,1.3.5,就是自变量。,自变量的数据类型,函数的自变量不仅只有数字类型而已，也可以是文字或以下,3,项类别：,位址：如,SUM(B1,，,C3),即是要计算,B1,单元格的值十,C3,单元格的值。,范围：如,SUM(A1,：,A4),即是要加总,Al,：,A4,范围的值。,函数：如,SQRT(SUM(B1,：,B4,即是先求出,B1,：,B4,的总和后，再开平方根的结果。,3-3-2,使用函数方块输入函数,函数也是公式的一种，所以输入函数时，也必须以等号“,=”,起首,，,假设我们要在,B8,单元格运用,SUM,函数来计算班费的总支出。,首先选取存放计算结果的,B8,储存,格，并在数据编辑列中数入等号”,=”,接着按下函数方块右侧的下拉钮，在列,示窗中选取,SUM,，此时会开启函数引,数交谈窗来协助我们输入函数：,选取,SUM,函数,若按下此处，可取得函数的进阶说明,TIP,：函数方块列示窗只会显示最近用过的,10,个函数，若在函数方块列示窗中找不到想要的函数，可选取其他函数项目开启插入函数交谈窗来寻找欲使用的函数（稍后说明）。,这里会描述此函数的功能,再来就是要设定函数的自变量。先按下第一个自变量栏,Number 1,右侧的折迭钮 ，将函数自变量,交谈窗收起来，再从工作表中选取,B4,：,B6,当作自变量：,按,下自变量栏右侧的展开钮 ，再度将函数自变量交谈窗展开：,选取,B4,：,B6,当作自变量,TIP,：除了从工作表中选取单元格来设定自变量，你也可以直接在自变量栏中输入自变量，省下折迭、展开函数引，数交谈窗的痲烦。,选取的范围会,被虚线框围住,函数自变量交谈窗,目前被折迭起来,这里会显示计,算的结果,按下确定钮，函数的计算结果就会显示在,B8,单元格内：,刚才输入的,函数及公式,计算结果,3-3-3,用自动显示的函数列表输入函数,若已经知道要使用哪一个函数，或是函数的名称很长，我们还有更方便的输入方法。请直接在单元格内输入”,=”,再输入函数的第,1,个字母，例如”,S”,，单元格下方就会列出,S,开头的函数，如果还没出现要用的函数，再继续输入第,2,个字母，例如”,U”,，出现要用的函数后，用鼠标双按函数就会自动输入单元格了：,移动箭头键到函数上，会显示函数的说明,双按函数可自动输入单元格,TIP:,若一直没有出现函数列表，请切换到,文件,页次再按下选项钮，然后切换到公式页次，确认已勾选公式自动完成选项。,3-3-3,利用“自动求和”钮快速输入函数,在开始页次编辑区有一个自动加按钮 ，可让我们快速输入函数。例如当我们选取B8单元格，并按下 钮时，便会自动插入SUM函数，且连自变量都自动帮我们设定好了：,只要按下,enter,键，就可,以算出班费的总支出了,会自勤选取好函数引,数，你也可以自行重新,选取其他范围,这里会有函数的输入格式提示,除了加总功能之外，还提供数种常用的函数供我们选择使用，只要按下,钮旁边的下拉钮,，,即可选择要进行的计算：,可用来做这些,运算,若选此项，会,开启插入函敷,交谈窗,3-3-4,开启“插入函数”交谈窗输入需要的函数,插入函数交谈窗是,Excel,函数的大本营，当你在函数方块列示窗中找不到需要的函数时，就可从这里来输入函数。现在我们要练习透过插入函数交谈窗来输入函数，列出面包店各家门市的营业额排名。,请选取,C4,单元格，然后按下数据编辑列上的插入函数钮，你会发现数据编辑列自动输入等号”,=,”，并且开殷插入函数交谈窗：,可从这里选择函数的类别，如财,务、统计、文字、日期及时间等,列出,Excel,所提供的函数,按下此处可显示目前所选取函数的使用说明,函数的功能叙述,接着从插入函数交谈窗中选取,RANK.EQ,函数，进行门市营业额的排名：,1,、,选择统计类别,若不知道,Excel,是否提供你所要的函数，也可在此栏,输入中、英文关键词，再按下右侧的开始钮进行搜寻,2,、,选取此类别下的,RANK.EO,函数,3,按下确定钮，开启,函数引数交谈窗,开启函数自变量交谈窗后，如下图输入各自变量的内容：,在此输入,B4,2,输入要进行排名的单元格范围（在此我们要排名的范围,是固定在,B4,：,813,之间，所以使用绝对参照地址）,按下确定钮即可得到计算,结果。,计算出，,惠城精城门市,的营业额是所有门市的第,3,名,请选取,C4,单元格，并拉,曳其填满控点到,C13,储,存格。,计算出所有门市,的营业额排名,若想变更自变量设定，请选取函数所在的单元格，然后按下插入函数钮,展开函数自变量交谈窗来重新设定,RANK.EQ,与,RANK.AVG,函数的差异,用来算排名的函数有,RANK.EO,和,RANK.AVG,两个，而在,Excel 2007,（或之前）版本则只有,RANK,函数。,这,3,个函数都可用来排序，,RANK.AVG,和,RANK.EQ,的差异是在遇到相同数值时的处理方法不同，,RANK.AVG,会传回等级的平均值，,RANK,EQ,则会传回最高等级；,RANK,则是,Excel2007,之前版本的函数，在,Excel2010,仍可使用，其作用与,RANK.EQ,相同。,传回,3,、,4,的,平均等级,3.5,传回最高等级，所以,会有,2,个,3,，没有,4,结果与,RANK.,EQ,相同,3-4,函数综合练习,-,奖金发放作业,学会插入函数之后，我们来做个综合练习，让你对函数有更进一步的了解。假设我们要依年资来计算中秋节发放的奖金，年资未满,1,年的员工没有奖金，满,1,年以上未满,3,年则发放,5,，,000,元奖金，满,3,年以上则发给,8,，,000,元奖金。,如下图所示：,首先要进行年资的计算，请先选取,D2,储存格，在此要使用,DATEDIF,这倜函数来计算两个日期之间的年数、月数或天数，其格式如下：,DATEIF(,开始日期，结束日期，差距参数单位）,到职日,奖金基数基准日（我们以这个日期为基准，所以使用绝对参照位址）,求算两日期差距的整年数要使用“,Y,”参数,在,D2,单元格中输入“,=DATEDIF(B2,$C$2,”Y“),”，,然后按,Enter,DATEIF,的差距单位参数,参数,传回的值,Y,两日期差距的整年数，亦即满几年,M,两日期差距的整月敷，亦即满畿倜月”,D,两日期差距的整日数，亦即满畿天,YM,两日期之间的月数差距，忽略日期中的年和日,YD,两日期之间的天数差距，忽略日期中的年,MD,两日期之间的天数差距，忽略日期中的年和月,计算出”蒋文文”的年资后，请选取,D2,储存格，然后拉曳填满控黠至,Dll,储存格，即可算出所有员工的年资。,年资为,0,表示未满一年,计算出”蒋文文”的年资后，请选取,D2,储存格，然后拉曳填满控黠至,Dll,储存格，即可算出所有员工的年资。计算出年资後，就可以依年资来计算奖金，我们要用,IF,函数来判断应发放的奖金。,IF,函数可判断条件是否成立，如果所传回的值为,TRUE,时，就执行条件成立时的作业，反之则执行条件不成立时的作业。,IF(,判读式，条件成立时的作业，条件不成立时的作业）,当年资小于,1,年的条件成立，没有奖金,当年资小于,3,年的条件成立，奖金为,5000,；若条件不成立，就是满,3,年以上，奖金为,8000,在,E2,单元格中输入,”=IF(D21,0,IF(D23,5000,8000)”,就是好蒋文文的奖金后，请将,E2,单元格的填满控点拉曳到,E11,单元格，即可算出所有人的奖金。,3-5,不需输入公式也能得知计算结果,这一节要介绍一个超实用的自动计算功能，让你不需输入任何公式或函数的情况下，也能快速得到运算结果，其可计算的项目包括求和、平均值、最大值、最小值,等常用的类别。,如右图所示，只要选取,E2,：,E11,单元格，马上就会在状态栏看到计算结果：,选取,E2,：,E11,范围,状态列会自动算出此次奖金发放的总金额、平均值等计算结果,自动计算功能不仅会计算总和、平均值、项目个敷，还可以计算最大值、最小值、数字计数等。若要燮更目前计算的项目，可如下操作：,在状态栏按右键,选择所要的计算功,能，功能前显示打,勾符虢，表示含列,在状态列，此例请,加选最大值、最小,值,2,项,可看出此次奖金发放最少是,0,元，最都是,8000,元,