《公式与函数》PPT课件.ppt

《《公式与函数》PPT课件.ppt》由会员分享，可在线阅读，更多相关《《公式与函数》PPT课件.ppt（29页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、3 公式与函数l在单元格建立公式l相对参照位址与绝对参照位址l函数的使用方法l插入函数的综合练习-奖金发放作业l不需要公式也能得知计算结果3-1 3-1 在单元格建立公式在单元格建立公式当我们需要将工作表中的数字数据做加、减、乘、除等运算时，可以把计算的动作交给Excel的公式去做，省去自行运算的工夫，而且当数据有变动时，公式计算的结果还会立即更新。3-1-1 3-1-1 公式的表示法公式的表示法ExcelExcel的公式和一般数学公式差不多，数学公式的表示法为：的公式和一般数学公式差不多，数学公式的表示法为：若将这个公式改用若将这个公式改用ExcelExcel表示，则变成要在表示，则变成要在

2、A3A3单元格中输入：单元格中输入：意思是意思是ExcelExcel会将会将AlAl单元格的值十单元格的值十A2A2单元格的值，然后把结果显示在单元格的值，然后把结果显示在A3A3单元格中。单元格中。3-1-2 3-1-2 输入公式输入公式输入公式必须以等号“=”起首，例如=Al+A2，这样Excel才知道我们输入的是公式，而不是一般的文字数据。现在我们就来练习建立公式，请开殷范例文件Ch03-01，我们已在其中输入了两个学生的成绩：我们打算在E2单元格存放“王书桓的各科总分”，也就是要将“王书桓”的英文、生物、理化分数加总起来，放到E2单元格中，因此将E2单元格的公式设计为“=B2十C2+D

3、2”。请选定要输入公式的E2单元格，并将指针移到数据编辑列中输入等号”=”：函数方块在此输入“”接着输入“=”之后的公式，请在单元格B2上单击，Excel便会将B2输入到数据编辑列中：此时B2被虚线框包围住B2自动输入到公式中再输入，+，然后选取C2单元格，继续输入，+，选取D2单元格，如此公式的内容便输入完成了：最后按下数据编辑列上的翰入钮 或按下 键，公式计算的结果马上显示在E2单元格中：数据编辑列会显示公式单元格显示公式计算的结果 Tip:若想直接在单元格中查看公式，可按下 +键 键在 键的上方，在公式和计算结果间做切换。你也可以直接在E2储存格中，以键盘直接输入“=82十C2十D2”、

4、再按下Enter键来输入公式3-1-3 3-1-3 自动更新结果自动更新结果公式的计算结果会随着单元格内容的变动而自动更新。以上例来说，假设当公式建好以后，才发现”王书桓”的英文成绩打错了，应该是”90”分才对，当我们将单元格B2的值改成”90”，E2单元格中的计算结果立即从220更新为225：自动更新计算结果了3-2 3-2 相对参照位址与绝对参照位址相对参照位址与绝对参照位址3-2-1 3-2-1 相对与绝对参照的差异相对与绝对参照的差异公式中会运用到的地址有相对引用地址与绝对参照地址2种类型。相对引用地址的表示法例如：Bl、C4；而绝对参照地址的表示法，则须在单元格地址前面加上”$”符号

5、，例如：$B$l、$C$4。假设你要前往某地，但不知道该怎么走，于是就向路人打听。结果得知你现在的位置往前走，碰到第一个红绿灯后右转，再直走约100公尺就到了，这就是相对引用地址的概念。另外有人干脆将实际地址告诉你，假设为”中正路二段60号”，这就是绝对参照地址的概念，由于地址具有唯一性，所以不论你在什么地方，根据这个绝对参照地址，所找到的永远是同一个地点。将这两者的特性套用在公式上，代表相对引用地址会随着公式的位置而改变，而绝对参照位址则不管公式在什么地方，它永远指向同一个单元格。3-2-2 3-2-2 实例说明实例说明 底下我们以实例为你说明相对引用地址与绝对参照地址的使用方式。先选取D2

6、单元格，在其中输入公式”=B2十C2”并计算出结果，根据前面的说明，这是相对引用地址。以下我们要在D3单元格输入绝对参照地址的公式=$B$3+$C$2。选取D3单元格，然后在数据编辑列中输入”=B3”。按下 键，B3会切换成$B$3的绝对参照地址：你也可以直接在资料编辑列中输入=$B$3。切换相对引用与绝对参照地址的快速键 键可循序切换单元格地址的参照类型，每按一次 键，参照地址的类型就会改变，其切换结果如右：接着输入“+C3”，再按下F4键将C3变成$C$3，最后按下 键，公式就建立完成了：D3的公式內容D2及D3的公式分别是由相对地址与绝对地址组成，但两者的计算结果却一样。到底它们差别在哪

7、里呢？选定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 3-2-3 混合参照混合参照如果在公式中同时使用相对引用与绝对参照，这种情形称为混合参照，例如：这种公式在复制后，绝对参照的部份(如$Bl

8、的$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单元格也为0B3维持1215，D3为21803-3 3-3 函数的使用方法函数的使用方法3-3-1 3-3-1 函数的格式函数的格式函数是Excel根

9、据各种需要，预先设计好的运算公式，可让你节省自行设计公式的时间，底下我们就来看看如何运用Excel的函数。每个函数都包含三个部份：函数名称、自变量和小括号。我们以加总函数SUM来说明：SUM即是函数名称，从函数名称可大略得知函数的功能、用途。小括号用来括住自变量，有些函数虽没有自变量，但小括号还是不可以省略。引敷是函数计算时所必须使用的数据，例如SUM(1，3，5)即表示要计算1-3-5三个数字的总和，其中的就是自变量。自变量的数据类型函数的自变量不仅只有数字类型而已，也可以是文字或以下3项类别：位址：如SUM(B1，C3)即是要计算B1单元格的值十C3单元格的值。范围：如SUM(A1：A4)

10、即是要加总Al：A4范围的值。函数：如SQRT(SUM(B1：B4即是先求出B1：B4的总和后，再开平方根的结果。3-3-2 3-3-2 使用函数方块输入函数使用函数方块输入函数p函数也是公式的一种，所以输入函数时，也必须以等号函数也是公式的一种，所以输入函数时，也必须以等号“=”=”起首起首，假设我们要在假设我们要在B8B8单元格运用单元格运用SUMSUM函数来计算班费的总支出。函数来计算班费的总支出。首先选取存放计算结果的 B8 储存格，并在数据编辑列中数入等号”=”接着按下函数方块右侧的下拉钮，在列示窗中选取SUM，此时会开启函数引数交谈窗来协助我们输入函数：选取SUM函数若按下此处，可

11、取得函数的进阶说明TIP：函数方块列示窗只会显示最近用过的10个函数，若在函数方块列示窗中找不到想要的函数，可选取其他函数项目开启插入函数交谈窗来寻找欲使用的函数（稍后说明）。这里会描述此函数的功能再来就是要设定函数的自变量。先按下第一个自变量栏Number 1右侧的折迭钮 ，将函数自变量交谈窗收起来，再从工作表中选取B4：B6当作自变量：按下自变量栏右侧的展开钮 ，再度将函数自变量交谈窗展开：选取B4：B6当作自变量TIP：除了从工作表中选取单元格来设定自变量，你也可以直接在自变量栏中输入自变量，省下折迭、展开函数引，数交谈窗的痲烦。选取的范围会被虚线框围住函数自变量交谈窗目前被折迭起来这里

12、会显示计算的结果按下确定钮，函数的计算结果就会显示在B8单元格内：刚才输入的函数及公式计算结果3-3-3 3-3-3 用自动显示的函数列表输入函数用自动显示的函数列表输入函数p 若已经知道要使用哪一个函数，或是函数的名称很长，我们还有更方便的输入方若已经知道要使用哪一个函数，或是函数的名称很长，我们还有更方便的输入方法。请直接在单元格内输入法。请直接在单元格内输入”=”=”再输入函数的第再输入函数的第1 1个字母，例如个字母，例如”S”S”，单元格，单元格下方就会列出下方就会列出S S开头的函数，如果还没出现要用的函数，再继续输入第开头的函数，如果还没出现要用的函数，再继续输入第2 2个字母，

13、例个字母，例如如”U”U”，出现要用的函数后，用鼠标双按函数就会自动输入单元格了：，出现要用的函数后，用鼠标双按函数就会自动输入单元格了：移动箭头键到函数上，会显示函数的说明双按函数可自动输入单元格TIP:若一直没有出现函数列表，请切换到文件页次再按下选项钮，然后切换到公式页次，确认已勾选公式自动完成选项。3-3-3 3-3-3 利用利用“自动求和自动求和”钮快速输入函数钮快速输入函数 在开始页次编辑区有一个自动加按钮 ，可让我们快速输入函数。例如当我们选取B8单元格，并按下 钮时，便会自动插入SUM函数，且连自变量都自动帮我们设定好了：只要按下enter键，就可以算出班费的总支出了会自勤选取

14、好函数引数，你也可以自行重新选取其他范围这里会有函数的输入格式提示 除了加总功能之外，还提供数种常用的函数供我们选择使用，只要按下 钮旁边的下拉钮，即可选择要进行的计算：可用来做这些运算若选此项，会开启插入函敷交谈窗3-3-4 3-3-4 开启开启“插入函数插入函数”交谈窗输入需要的函数交谈窗输入需要的函数插入函数交谈窗是Excel函数的大本营，当你在函数方块列示窗中找不到需要的函数时，就可从这里来输入函数。现在我们要练习透过插入函数交谈窗来输入函数，列出面包店各家门市的营业额排名。请选取C4单元格，然后按下数据编辑列上的插入函数钮，你会发现数据编辑列自动输入等号”=”，并且开殷插入函数交谈窗

15、：可从这里选择函数的类别，如财务、统计、文字、日期及时间等列出Excel所提供的函数按下此处可显示目前所选取函数的使用说明函数的功能叙述接着从插入函数交谈窗中选取RANK.EQ函数，进行门市营业额的排名：1、选择统计类别若不知道Excel是否提供你所要的函数，也可在此栏输入中、英文关键词，再按下右侧的开始钮进行搜寻2、选取此类别下的 RANK.EO函数3按下确定钮，开启 函数引数交谈窗开启函数自变量交谈窗后，如下图输入各自变量的内容：在此输入B42输入要进行排名的单元格范围（在此我们要排名的范围 是固定在B4：813之间，所以使用绝对参照地址）按下确定钮即可得到计算结果。计算出，惠城精城门市的

16、营业额是所有门市的第3名请选取C4单元格，并拉曳其填满控点到C13储存格。计算出所有门市的营业额排名若想变更自变量设定，请选取函数所在的单元格，然后按下插入函数钮 展开函数自变量交谈窗来重新设定RANK.EQ与RANK.AVG函数的差异用来算排名的函数有RANK.EO和RANK.AVG两个，而在Excel 2007（或之前）版本则只有RANK函数。这3个函数都可用来排序，RANK.AVG和RANK.EQ的差异是在遇到相同数值时的处理方法不同，RANK.AVG会传回等级的平均值，RANKEQ则会传回最高等级；RANK则是Excel2007之前版本的函数，在Excel2010仍可使用，其作用与RA

17、NK.EQ相同。传回3、4的平均等级3.5传回最高等级，所以会有2个3，没有4结果与RANK.EQ相同3-4 3-4 函数综合练习函数综合练习-奖金发放作业奖金发放作业n学会插入函数之后，我们来做个综合练习，让你对函数有更进一步的了解。假设我们要依年资来计算中秋节发放的奖金，年资未满1年的员工没有奖金，满1年以上未满3年则发放5，000元奖金，满3年以上则发给8，000元奖金。如下图所示：首先要进行年资的计算，请先选取D2储存格，在此要使用DATEDIF这倜函数来计算两个日期之间的年数、月数或天数，其格式如下：DATEIF(开始日期，结束日期，差距参数单位）到职日奖金基数基准日（我们以这个日期

18、为基准，所以使用绝对参照位址）求算两日期差距的整年数要使用“Y”参数在D2单元格中输入“=DATEDIF(B2,$C$2,”Y“)”，然后按Entern DATEIF的差距单位参数参数 传回的值Y两日期差距的整年数，亦即满几年M两日期差距的整月敷，亦即满畿倜月”D两日期差距的整日数，亦即满畿天YM 两日期之间的月数差距，忽略日期中的年和日YD 两日期之间的天数差距，忽略日期中的年MD 两日期之间的天数差距，忽略日期中的年和月计算出”蒋文文”的年资后，请选取D2储存格，然后拉曳填满控黠至Dll储存格，即可算出所有员工的年资。年资为0表示未满一年计算出”蒋文文”的年资后，请选取D2储存格，然后拉曳

19、填满控黠至Dll储存格，即可算出所有员工的年资。计算出年资後，就可以依年资来计算奖金，我们要用IF函数来判断应发放的奖金。IF函数可判断条件是否成立，如果所传回的值为TRUE时，就执行条件成立时的作业，反之则执行条件不成立时的作业。IF(判读式，条件成立时的作业，条件不成立时的作业）当年资小于1年的条件成立，没有奖金当年资小于3年的条件成立，奖金为5000；若条件不成立，就是满3年以上，奖金为8000在E2单元格中输入”=IF(D21,0,IF(D23,5000,8000)”就是好蒋文文的奖金后，请将E2单元格的填满控点拉曳到E11单元格，即可算出所有人的奖金。3-5 3-5 不需输入公式也能

20、得知计算结果不需输入公式也能得知计算结果l这一节要介绍一个超实用的自动计算功能，让你不需输入任何公式或函数的情况下，也能快速得到运算结果，其可计算的项目包括求和、平均值、最大值、最小值等常用的类别。如右图所示，只要选取E2：E11单元格，马上就会在状态栏看到计算结果：选取E2：E11范围状态列会自动算出此次奖金发放的总金额、平均值等计算结果 自动计算功能不仅会计算总和、平均值、项目个敷，还可以计算最大值、最小值、数字计数等。若要燮更目前计算的项目，可如下操作：在状态栏按右键选择所要的计算功能，功能前显示打勾符虢，表示含列在状态列，此例请加选最大值、最小值2项可看出此次奖金发放最少是0元，最都是8000元