2017年Excel培训基础教程(二、公式).pptx

《2017年Excel培训基础教程(二、公式).pptx》由会员分享，可在线阅读，更多相关《2017年Excel培训基础教程(二、公式).pptx（28页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、2017年Excel培训基础教程（二）信息技术部1 公式与函数l在单元格建立公式l相对参照位址与绝对参照位址l常用公式介绍l不需要公式也能得知计算结果1-1 在在单元格建立公式元格建立公式当我们需要将工作表中的数字数据做加、减、乘、除等运算时，可以把计算的动作交给Excel的公式去做，省去自行运算的工夫，而且当数据有变动时，公式计算的结果还会立即更新。1-1-1 公式的表示法公式的表示法Excel的公式和一般数学公式差不多，数学公式的表示法的公式和一般数学公式差不多，数学公式的表示法为为：若将若将这这个公式改用个公式改用Excel表示，表示，则变则变成要在成要在A3单单元格中元格中输输入：入：

2、意思是意思是Excel会将会将Al单单元格的元格的值值十十A2单单元格的元格的值值，然后把，然后把结结果果显显示在示在A3单单元格中。元格中。1-1-2 输入公式入公式输入公式必须以等号等号“=”起首，例如=A1+A2，这样Excel才知道我们输入的是公式，而不是一般的文字数据。现在我们就来练习建立公式，我们已在其中输入了两个学生的成绩：我们打算在E2单元格存放“王书桓的各科总分”，也就是要将“王书桓”的英文、生物、理化分数加总起来，放到E2单元格中，因此将E2单元格的公式设计为“=B2十C2+D2”。请选定要输入公式的E2单元格，并将指针移到数据编辑列中输输入等号入等号”=”：函数方块在此输

3、入“”接着输入“=”之后的公式，请在单元格B2上单击，Excel便会将B2输入到数据编辑列中：此时B2被虚线框包围住B2自动输入到公式中再输入，+，然后选取C2单元格，继续输入，+，选取D2单元格，如此公式的内容便输入完成了：最后按下数据编辑列上的翰入钮 或按下 键，公式计算的结果马上显示在E2单元格中：数据编辑列会显示公式单元格显示公式计算的结果你也可以直接在E2储存格中，以键盘直接输入“=B2十C2十D2”、再按下Enter键来输入公式1-1-3 自自动更新更新结果果公式的计算结果会随着单元格内容的变动而自动更新。以上例来说，假设当公式建好以后，才发现”王书桓”的英文成绩打错了，应该是”9

4、0”分才对，当我们将单元格B2的值改成”90”，E2单元格中的计算结果立即从220更新为225：自动更新计算结果了1-2 相相对参照位址与参照位址与绝对参照位址参照位址1-2-1 相相对地址：地址：单元格元格 绝对地址：相地址：相对固定的固定的单元格元格公式中会运用到的地址有相对引用地址与绝对参照地址2种类型。相相对对引用地址的表示法例如：引用地址的表示法例如：Bl、C4；绝对绝对参照地址的表示法，参照地址的表示法，则须则须在在单单元格地址前面加上元格地址前面加上”$”符号，例如：符号，例如：$B$l、$C$4。1 1-2-2-2-2 实例说明实例说明 底下我们以实例为你说明相对引用地址与绝对

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

6、单元格，拉曳填满控点到下一栏，将公式复制到E2：E3储存格：计算结果不同了相对地址公式D2的公式=B2+C2，使用了相对位址，表示要计算D2往左找两个储存格(B2、C2)的总和，因此当公式复制到E2单元格后，便改成从E2往左找两个储存格相加，结果就变成C2和D2相加的结果：绝对地址公式D3的公式=$B$3十$C$3，使用了绝对位址，因此不管公式复制到哪里，Excel都是找出B3和C3的值来相加，所以D3和E3的结果都是一样的：1-2-3 混合参照混合参照如果在公式中同时使用相对引用与绝对参照，这种情形称为混合参照，例如：这种公式在复制后，绝对参照的部份(如$Bl的$B)不会变动，而相对引用的部

7、份则会随情况做调整。绝对参照相对参照绝对参照相对参照请依照下列步骤将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为21801-3 常用公式介常用公式介绍1-3-1 函数的格式函数的格式函数是Excel根据各种需要，预先设计好的运算公式，可让你节省自行设计公式的时间，底下我们就来看看如何运用Excel的

8、函数。每个函数都包含三个部份：函数函数名、名、自自变变量和小括号量和小括号。=函数名函数名(自变量自变量)1 1-3-2-3-2 使用函数方块输入函数使用函数方块输入函数 函数也是公式的一种，所以输入函数时，也必须以等号“=”起首1-3-3 1-3-3 常用函数公式常用函数公式1、文本连接&2、逻辑函数（if/and/or/not)3、常用统计函数（Max/Min/large/Small/Count/counta/countblank/Countif/countifs）4、常用数学函数（Sum/Average/Sumif/Sumifs/Product/Sumproduct/rand/Int/r

9、ound）5、日期和时间函数（day/month/year/hour/minute/second/Today/Now/Workday/networkdays)6、其他常用函数（Vlookup/Index）l将多列或多内容合并1.文本连接&如：将邮箱名和邮箱后缀+合并为邮箱全称公式为=A2&”&B2注：字符串内容需要加英文双引号“”l条件函数ifl逻辑与函数and，逻辑或函数or，逻辑非函数not，返回值为true/falselIf（and（，），符合条件返回值，不符合条件返回值）lIf（or（，），符合条件返回值，不符合条件返回值）lIf（not（，），符合条件返回值，不符合条件返回值）注意：

10、返回值若为字符串，则需要加英文双引号“”2.条件与逻辑函数（if/and/or/not)lMax/Min 取最大最小，Large/Small 取所选区域中第K个最大值/最小值；lCountif/countifs为满足一个或多个条件的单元格数量；countif（选定区域，“条件”）；countifs（选定区域，“条件”，选定区域，“条件”）3、常用统计函数（Max/Min/large/Small/Count/counta/countblank/Countif/countifs）lSum求和，Sumif条件求和，Average平均数lProduct连乘、SumProduct对应列相乘再求和lRan

11、d随机数、Int取整、Round四舍五入lRand()*(b-a)+a round(number,k)4、常用数学函数 （Sum/Average/Sum if/Sum ifs/Product/Sum product/rand/Int/round）l变化的日期和时间 工作日 日期加减5、日期和时间函数（day/month/year/hour/minute/second/Today/Now/workday/networkday)lVlookup(lookup_value,table_array,col_index_num,range,lookup)lvlookup(需要比较值，被比较区域，返回被比

12、较区域的第几列/行，精确查找/模糊查找）l取矩阵交叉项Index(区域，行，列)7、其他常用函数（Vlookup/Index）Vlookup 时一般用绝对地址绝对地址若按下此处，可取得函数的进阶说明TIP：函数方块列示窗只会显示最近用过的10个函数，若在函数方块列示窗中找不到想要的函数，可选取其他函数项目开启插入函数交谈窗来寻找欲使用的函数（稍后说明）。这里会描述此函数的功能再来就是要设定函数的自变量。先按下第一个自变量栏Number 1右侧的折迭钮 ，将函数自变量交谈窗收起来，再从工作表中选取B4：B6当作自变量：按下自变量栏右侧的展开钮 ，再度将函数自变量交谈窗展开：选取B4：B6当作自变

13、量TIP：除了从工作表中选取单元格来设定自变量，你也可以直接在自变量栏中输入自变量，省下折迭、展开函数引，数交谈窗的痲烦。选取的范围会被虚线框围住函数自变量交谈窗目前被折迭起来这里会显示计算的结果按下确定钮，函数的计算结果就会显示在B8单元格内：刚才输入的函数及公式计算结果1-3-4 用自用自动显示的函数列表示的函数列表输入函数入函数p 若已若已经知道要使用哪一个函数，或是函数的名称很知道要使用哪一个函数，或是函数的名称很长，我，我们还有更方便的有更方便的输入方入方法。法。请直接在直接在单元格内元格内输入入”=”再再输入函数的第入函数的第1个字母，例如个字母，例如”S”，单元格元格下方就会列出

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

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

16、你会发现数据编辑列自动输入等号”=”，并且开殷插入函数交谈窗：可从这里选择函数的类别，如财务、统计、文字、日期及时间等列出Excel所提供的函数按下此处可显示目前所选取函数的使用说明函数的功能叙述1-4 不需不需输入公式也能得知入公式也能得知计算算结果果l这一节要介绍一个超实用的自动计算功能，让你不需输入任何公式或函数的情况下，也能快速得到运算结果，其可计算的项目包括求和、平均值、最大值、最小值等常用的类别。如右图所示，只要选取E2：E11单元格，马上就会在状态栏看到计算结果：选取E2：E11范围状态列会自动算出此次奖金发放的总金额、平均值等计算结果自动计算功能不仅会计算总和、平均值、项目个数，还可以计算最大值、最小值、数字计数等。若要变更目前计算的项目，可如下操作：在状态栏按右键选择所要的计算功能，功能前显示打勾符号，表示含列在状态列，此例请加选最大值、最小值2项可看出此次奖金发放最少是0元，最都是8000元