excel使用技巧

1、Office-excelOffice-excel软件应用软件应用姓名：李东明姓名：李东明电话：电话正大 版权所有培训目标培训目标21、掌握基本的EXCEL使用技巧，能够自己制作EXCEL模板。2、学会函数的使用方法，能使用基本的函数处理数据。3、掌握数据透析表与图表的的基本使用方法。金正大 版权所有Office-excelOffice-excel软件应用软件应用ExcelExcel使用技巧使用技巧ExcelExcel函数运用函数运用数据透析表及图表数据透析表及图表金正大 版权所有4单元格自定义类型单元格自定义类型 在在“开始开始”菜单栏下的菜单栏下的“数字数字”区域，

2、有设定好的一些格式，比如百分数，时间格式区域，有设定好的一些格式，比如百分数，时间格式，分数啊，会计格式啊等等，需要使用的话，直接单击就可以了另外，在看看，分数啊，会计格式啊等等，需要使用的话，直接单击就可以了另外，在看看“自定自定义义”设置，这就需要讲下通配符：设置，这就需要讲下通配符：一般我们常用到的通配符有一般我们常用到的通配符有“0”和和“#”，它代表的就是一个字符。，它代表的就是一个字符。注意哦：通配符要在英文半角情况下输入注意哦：通配符要在英文半角情况下输入 金正大 版权所有5快速给数字加上单位快速给数字加上单位 有时我们需要给输入的数值加上单位（如有时我们需要给输入的数值加上单位

3、（如“立方米立方米”等），少量的我们可以直接输入等），少量的我们可以直接输入而大量的如果一个一个地输入就显得太慢了。而大量的如果一个一个地输入就显得太慢了。下面的方法来实现单位的自动输入：下面的方法来实现单位的自动输入：先将数值输入相应的单元格中（注意先将数值输入相应的单元格中（注意:仅限于数值），然后在按住仅限于数值），然后在按住Ctrl键的同时键的同时选取需要加同一单位的单元格，单击选取需要加同一单位的单元格，单击“开始开始单元格单元格-格式格式”命令，打开命令，打开“单元格格式单元格格式”对话对话框（），在框（），在“数字数字”标签中，选中标签中，选中“分类分类”下面的下面的“自定义自定

4、义”选项，再在选项，再在“类型类型”下面的方下面的方框中输入框中输入#立方米，按下确定键后，单位（立方米）即一次性加到相应数立方米，按下确定键后，单位（立方米）即一次性加到相应数值的后面。值的后面。金正大 版权所有6数值开头的零如何显示数值开头的零如何显示在输入数值时如果开头是在输入数值时如果开头是0如何让它显示出来？如何让它显示出来？ 当我们在输入数值时，如编号如果开头是当我们在输入数值时，如编号如果开头是0的话，往往是显示不出来的。的话，往往是显示不出来的。例如例如：输入输入009527显示出来是显示出来是9527,0是显示不出来的。是显示不出来的。这里只要在前面加上一个单引号这里只要在前

5、面加上一个单引号“”（英文格式下）就可以显示出来（如身份证号码（英文格式下）就可以显示出来（如身份证号码也是相同办法）也是相同办法） 金正大 版权所有7在不同的单元格连续输入多个值在不同的单元格连续输入多个值我们有时需要在不同单元格内连续输入多个数据，但每次输入一个数据后按回车我们有时需要在不同单元格内连续输入多个数据，但每次输入一个数据后按回车键，活动单元格均默认下移一个单元格，非常不便。键，活动单元格均默认下移一个单元格，非常不便。方法方法:单击鼠标选定单元格，然后按住单击鼠标选定单元格，然后按住Ctrl键再次单击鼠标选定其他单元格按回车键再次单击鼠标选定其他单元格按回车键，光标自动停留在

6、第一个选中单元格，再输入数据，敲回车键。光标会自动移键，光标自动停留在第一个选中单元格，再输入数据，敲回车键。光标会自动移动到下一个选中单元格。动到下一个选中单元格。 金正大 版权所有8数据有效性数据有效性数据有效性数据有效性在我们日常操作在我们日常操作Excel数据时，可以借助数据有效性的功能来验证我们输入数据数据时，可以借助数据有效性的功能来验证我们输入数据 的有效性。的有效性。1、单击功能区、单击功能区“数据数据”，单击，单击“数据有效性数据有效性”根据我们实际需要进行设置。如图根据我们实际需要进行设置。如图金正大 版权所有动手操作动手操作9使用数据有效性，限制单元格使用数据有效性，限制

7、单元格只能输入只能输入18位身份证号码。位身份证号码。金正大 版权所有文本快速换行文本快速换行 我们在使用我们在使用ExcelExcel制作表格时经常会遇到需要在一个单元格输入一行或几行文字的情制作表格时经常会遇到需要在一个单元格输入一行或几行文字的情况，如果输入一行后敲回车键就会移到下一单元格，而不是换行。况，如果输入一行后敲回车键就会移到下一单元格，而不是换行。简便实用的操作方法可以实现换行简便实用的操作方法可以实现换行: :在选定单元格输入第一行内容后，在换行处按在选定单元格输入第一行内容后，在换行处按“Alt+“Alt+回车键回车键”，即可输入第二行内容，再按，即可输入第二行内容，再按

8、“Alt+“Alt+回车键回车键”输入第三行以此类推。输入第三行以此类推。金正大 版权所有11禁止复制隐藏行或列中的数据禁止复制隐藏行或列中的数据如果你复制了包含隐藏列如果你复制了包含隐藏列( (或行或行) )的一个数据区域，然后把它粘贴到一个新的工作表，那么的一个数据区域，然后把它粘贴到一个新的工作表，那么ExcelExcel把隐藏列也粘贴过来了。把隐藏列也粘贴过来了。要想避免这种情况，可以选取你要复制的数据区域，然后选择要想避免这种情况，可以选取你要复制的数据区域，然后选择“开始开始- -编辑编辑查找和选择查找和选择- -定定位位”命令命令(ctrl+G)(ctrl+G)，单击，单击“定位

9、条件定位条件”按钮，出现按钮，出现“定位条件定位条件”对话框，选中对话框，选中“可见单元可见单元格格”选项，再复制和粘贴这个选定区域就会得到你所希望的结果选项，再复制和粘贴这个选定区域就会得到你所希望的结果。金正大 版权所有12按笔画排序按笔画排序 在默认情况下，在默认情况下，Excel对中文字的排序方式是按照对中文字的排序方式是按照“字母字母”顺序的，以中文姓名为例，字顺序的，以中文姓名为例，字母顺序即按姓的拼音的首字母在母顺序即按姓的拼音的首字母在26个英文字母中出现的顺序进行排列，如果同姓，则依次个英文字母中出现的顺序进行排列，如果同姓，则依次计算名的第二、第三字。图中显示的表格包含了按

10、字母顺序排列的姓名数据计算名的第二、第三字。图中显示的表格包含了按字母顺序排列的姓名数据。然而，在我们的习惯中然而，在我们的习惯中，有时常常，有时常常是按照是按照“笔划笔划”的顺序来排列姓名的。这种排序的规则的顺序来排列姓名的。这种排序的规则是：按姓字的划数多少排列，同划数内的姓字按起笔顺序排列（横、竖、撇、捺、折），是：按姓字的划数多少排列，同划数内的姓字按起笔顺序排列（横、竖、撇、捺、折），划数和笔形都相同的字，按字形结构排列，先左右、再上下，最后整体字。如果姓字相同划数和笔形都相同的字，按字形结构排列，先左右、再上下，最后整体字。如果姓字相同，则依次看名第二、三字，规则同姓字，则依次看名

11、第二、三字，规则同姓字。1、单击数据区域中任意单元格，如单击数据区域中任意单元格，如A2。2、单击菜单单击菜单“数据数据”“排序排序”，出现，出现“排序排序”对话框。对话框。3、在在“排序排序”对话框中，选择对话框中，选择“主要关键字主要关键字”为为“姓名姓名”，排序方式为升序。，排序方式为升序。4、单击单击“排序排序”对话框中的对话框中的“选项选项”按钮，在出现的按钮，在出现的“排序选项排序选项”对话框中，对话框中，5、单击方法区域中的单击方法区域中的“笔划排序笔划排序”单选按钮，如图所示，单击单选按钮，如图所示，单击“确定确定”按钮按钮6、单击、单击“确定确定”按钮，关闭按钮，关闭“排序排

12、序”对话框对话框。金正大 版权所有13在工作日历中突显周休日在工作日历中突显周休日很很多用户喜欢使用多用户喜欢使用Excel制作工作日历，如图所示的是公司人力资源部的工作日历的一部分。如果能制作工作日历，如图所示的是公司人力资源部的工作日历的一部分。如果能把所有的周休日都醒目地标识出来，就能够避免一些时间安排上的问题了把所有的周休日都醒目地标识出来，就能够避免一些时间安排上的问题了。方法如下：方法如下：1、单击、单击A2并拖动光标到并拖动光标到A17以选定区域以选定区域A2:A17，单击菜单，单击菜单“开始开始”“条件格式条件格式”。在在“条件格式条件格式”对话框中，单击对话框中，单击“条件条

13、件1(1)”的下拉箭头，在列表中选择的下拉箭头，在列表中选择“公式公式”项，在右边的文本项，在右边的文本框中输入公式框中输入公式：=WEEKDAY(A2,2)52、单击、单击“格式格式”按钮，在按钮，在“单元格格式单元格格式”对话框的对话框的“字体字体”选项卡中选择选项卡中选择“字形字形”为为“加粗加粗”，颜，颜色为红色，单击色为红色，单击“确定确定”按钮。单击按钮。单击“条件格式条件格式”对话框的对话框的“确定确定”按钮，如图所示。按钮，如图所示。金正大 版权所有14多个表统一页眉页脚多个表统一页眉页脚如果在一个如果在一个Excel文件中含有多个工作表，如何将多个工作表文件中含有多个工作表，

14、如何将多个工作表一次设置成同样的页眉和页脚？一次设置成同样的页眉和页脚？ 把鼠标移到工作表的名称处（若你没有特别设置的话，把鼠标移到工作表的名称处（若你没有特别设置的话，ExcelExcel自动设置的名称是自动设置的名称是“sheet1sheet1、sheet2sheet2、sheet3.”sheet3.”），然后点右键，在弹出的菜单中选择），然后点右键，在弹出的菜单中选择“选择全部工作表选择全部工作表”的菜的菜单项，这时你的所有操作都是针对全部选中的工作表，不管是设置页眉和页脚还是打印你的单项，这时你的所有操作都是针对全部选中的工作表，不管是设置页眉和页脚还是打印你的工作表工作表。 金正大

15、版权所有15自动醒目的小计自动醒目的小计在在实际工作，常常需要在表格中使用小计行或小计列来汇总某类数据，如图所示。使用条件格式功能实际工作，常常需要在表格中使用小计行或小计列来汇总某类数据，如图所示。使用条件格式功能，能够快速地为所有小计行与小计列设置醒目的格式，能够快速地为所有小计行与小计列设置醒目的格式。1、单击、单击A1并拖动光标到并拖动光标到H11以选定区域以选定区域A1:H11，单击菜单，单击菜单“格式格式”“条件格式条件格式”。2、在、在“条件格式条件格式”对话框中，单击对话框中，单击“条件条件1(1)”的下拉箭头，在列表中选择的下拉箭头，在列表中选择“公式公式”项，在右边的文项，

16、在右边的文本框中输入公式：本框中输入公式：=($A1=小计小计)+(A$1=小计小计)03、单击、单击“格式格式”按钮，在按钮，在“单元格格式单元格格式”对话框的对话框的“字体字体”选项卡中选择选项卡中选择“字形字形”为为“加粗加粗”，颜，颜色为蓝色，单击色为蓝色，单击“确定确定”按钮。按钮。4、单击、单击“条件格式条件格式”对话框的对话框的“确定确定”按钮按钮金正大 版权所有16快速比较不同区域的数值（一）快速比较不同区域的数值（一）Excel的多窗口特性能够帮助用户比较不同区域的数据，但是当需要比较的区域较大时，人工比较不的多窗口特性能够帮助用户比较不同区域的数据，但是当需要比较的区域较大

17、时，人工比较不但费时而且准确率不高。此时如果利用条件格式功能，则能快速而又准确地完成对比工作。但费时而且准确率不高。此时如果利用条件格式功能，则能快速而又准确地完成对比工作。 在如下图所示的工作表中，源数据和校验数据分别位于在如下图所示的工作表中，源数据和校验数据分别位于A2 B21和和D2 E21，如果希望标记出，如果希望标记出与源数据不匹配的校验数据，方法如下与源数据不匹配的校验数据，方法如下：1、单击、单击D2并拖动光标到并拖动光标到E21以选定区域以选定区域D2:E21，单击菜单，单击菜单“格式格式”“条件格式条件格式”。2、在、在“条件格式条件格式”对话框中，单击对话框中，单击“条件

18、条件1(1)”的下拉箭头，在列表中选择的下拉箭头，在列表中选择“单元格数值单元格数值”项，运算项，运算符选择符选择“不等于不等于”，在右边的文本框中输入：，在右边的文本框中输入：=A23、单击、单击“格式格式”按钮，在按钮，在“单元格格式单元格格式”对话框的对话框的“图案图案”选项卡中选择单元格底纹颜色为淡蓝，选项卡中选择单元格底纹颜色为淡蓝，单击单击“确定确定”按钮。按钮。4、单击、单击“条件格式条件格式”对话框的对话框的“确定确定”按钮，关闭对话框按钮，关闭对话框。金正大 版权所有17快速比较不同区域的数值（二）快速比较不同区域的数值（二）上上页中介绍了利用条件格式快速对比不同区域中的数值

19、的一种方法。然而在实际工作中，需要对比的页中介绍了利用条件格式快速对比不同区域中的数值的一种方法。然而在实际工作中，需要对比的数据可能并不是位置一一对应的。例如，如图所示的表格中，需要把与账号二不匹配的账号一标记出数据可能并不是位置一一对应的。例如，如图所示的表格中，需要把与账号二不匹配的账号一标记出来，但两列账号的排列顺序并不相同，对于这种情况，条件格式的设置要相对复杂一些来，但两列账号的排列顺序并不相同，对于这种情况，条件格式的设置要相对复杂一些。1、单击单击A2并拖动光标到并拖动光标到A11以选定区域以选定区域A2:A11，单击菜单，单击菜单“格式格式”“条件格式条件格式”。2、在在“条

20、件格式条件格式”对话框中，单击对话框中，单击“条件条件1(1)”的下拉箭头，在列表中选择的下拉箭头，在列表中选择“公式公式”项，在右边的项，在右边的文本框中输入公式：文本框中输入公式：=OR(EXACT(A2,B$2:B$11)=FALSE3、单击单击“格式格式”按钮，在按钮，在“单元格格式单元格格式”对话框的对话框的“图案图案”选项卡中选择单元格底纹颜色为淡蓝，选项卡中选择单元格底纹颜色为淡蓝，单击单击“确定确定”按钮。按钮。4、单击单击“条件格式条件格式”对话框的对话框的“确定确定”按钮按钮。 在在上述公式中，上述公式中，EXACT函数用于比较两个文本字符串是否完全相函数用于比较两个文本字

21、符串是否完全相同，如完全相同则返回同，如完全相同则返回TRUE，否则返回，否则返回FALSE。使用。使用以下以下3个公式，个公式，也也能够实现同样的功能：能够实现同样的功能：=ISNA(MATCH(A2,B$2:B$11,)=ISNA(VLOOKUP(A2,B$2:B$11,1,)=NOT(OR(A2=B$2:B$11)如果如果使用公式使用公式=COUNTIF(B$3:B$12,A3)，会得到错误的结果。因为当，会得到错误的结果。因为当字符型数字的长度超过字符型数字的长度超过15位时，位时，COUNTIF函数会把从第函数会把从第16位开始的数位开始的数字都当作字都当作0计算计算。可以。可以按下

22、面的方法来创建公式：按下面的方法来创建公式：=COUNTIF(B$3:B$12,A3&*)=0这样就可以强制这样就可以强制COUNTIF使用文本方式进行计数。使用文本方式进行计数。金正大 版权所有Excel-Excel-快捷键快捷键18在工作表中移动到当前数据区域的边缘：在工作表中移动到当前数据区域的边缘：Ctrl+ Ctrl+ 箭头键箭头键移动到行首：移动到行首：HomeHome移动到工作表的开头：移动到工作表的开头： Ctrl Ctrl + Home+ Home移动到工作表的最后一个单元格：移动到工作表的最后一个单元格： Ctrl Ctrl +End+End向下移动一屏：向下移动一

23、屏：Page Down Page Down 向上移动一屏：向上移动一屏：Page UpPage Up向右移动一屏：向右移动一屏：Alt+Page Down Alt+Page Down 向左移动一屏：向左移动一屏：Alt+Page Up Alt+Page Up 移动到工作簿中下一个工作表：移动到工作簿中下一个工作表：Ctrl+Page Down Ctrl+Page Down 移动到工作簿中前一个工作表：移动到工作簿中前一个工作表：Ctrl+Page UpCtrl+Page UpF4F4键键可以重复前一次操作，在很多情况下起作用，比如在工作表内加入可以重复前一次操作，在很多情况下起作用，比如在工作

24、表内加入或删除一行，然后移动插入点并按下或删除一行，然后移动插入点并按下F4F4键以加入或删除另一行，根本不键以加入或删除另一行，根本不需要使用菜单。需要使用菜单。 金正大 版权所有自己做一个自己做一个EXCELEXCEL模板模板191、包含自动的编号、姓名、身份证号码、年龄、包含自动的编号、姓名、身份证号码、年龄、公积金基数、工资额、大写金额等。公积金基数、工资额、大写金额等。2、设置好打印格式确保打印到一页纸上，页脚有、设置好打印格式确保打印到一页纸上，页脚有第第*页页/共共*页等。页等。3、使用保护工作表只允许个人将身份证号码维护、使用保护工作表只允许个人将身份证号码维护到该表格里面，且

25、如果输入的不是到该表格里面，且如果输入的不是18位身份证号码位身份证号码则无法输入。则无法输入。金正大 版权所有OfficeOffice办公软件办公软件ExcelExcel使用技巧使用技巧ExcelExcel函数运用函数运用数据透析表及图表数据透析表及图表金正大 版权所有21相对引用相对引用& &绝对引用（绝对引用（$ $）相对相对引用引用&绝对绝对引用（引用（$）A1相对引用相对引用$A1绝对引用列绝对引用列A$1绝对引用行绝对引用行$A$1绝对引用行和列绝对引用行和列$在谁的前面就绝对引用谁在谁的前面就绝对引用谁F4是在四种引用间相互转换的快捷键是在四种引用间相互转

26、换的快捷键(在编辑栏输入公式时按下在编辑栏输入公式时按下F4功能键可进行切换功能键可进行切换)相对引用、绝对引用和混合引用是指在公式中使用单元格或单元格区域的地址时，当将公式向旁边复相对引用、绝对引用和混合引用是指在公式中使用单元格或单元格区域的地址时，当将公式向旁边复制时，地址是如何变化的。制时，地址是如何变化的。 具体情况举例说明：具体情况举例说明： 1、相对引用，复制公式时地址跟着发生变化，如、相对引用，复制公式时地址跟着发生变化，如C1单元格有公式：单元格有公式：=A1+B1 当将公式复制到当将公式复制到C2单元格时变为：单元格时变为：=A2+B2 当将公式复制到当将公式复制到D1单元

27、格时变为：单元格时变为：=B1+C12、绝对引用，复制公式时地址不会跟着发生变化，如、绝对引用，复制公式时地址不会跟着发生变化，如C1单元格有公式：单元格有公式：=$A$1+$B$1 当将公式复制到当将公式复制到C2单元格时仍为：单元格时仍为：=$A$1+$B$1 当将公式复制到当将公式复制到D1单元格时仍为：单元格时仍为：=$A$1+$B$13、混合引用，复制公式时地址的部分内容跟着发生变化，如、混合引用，复制公式时地址的部分内容跟着发生变化，如C1单元格有公式：单元格有公式：=$A1+B$1 当将公式复制到当将公式复制到C2单元格时变为：单元格时变为：=$A2+B$1 当将公式复制到当将公

28、式复制到D1单元格时变为：单元格时变为：=$A1+C$1 规律：加上了绝对地址符规律：加上了绝对地址符“$”的列标和行号为绝对地址，在公式向旁边复制时不会发生变化，没有加的列标和行号为绝对地址，在公式向旁边复制时不会发生变化，没有加上绝对地址符号的上绝对地址符号的金正大 版权所有22函数的种类函数的种类按照函数的来源，Excel 函数可以分为：n内置函数：只要启动了Excel，用户就可以使用它们。如：sum; averagen扩展函数：必须通过单击“工具加载宏”菜单命令加载，然后才能像内置函数那样使用。当你发现你某些函数不可用时，请执行这一步骤。如：iseven函数的使用必须执行“工具加载宏分

29、析工具库”方能使用金正大 版权所有23如何插入函数如何插入函数u在菜单栏公式里面插入函数，如下左图所示。u在单元格输入“=”输入函数名称插入公式，如下右图所示。金正大 版权所有24函数参数中的单元格引用函数参数中的单元格引用n根据公式所在单元格的位置发生变化时，单元格引用的变化情况，我们可以引用分为相对引用（如A2）、绝对引用（$A$2）和混合引用三种类型n混合引用有“绝对列和相对行”（如$A3:$E3）；或是“绝对行和相对列”（如A$3:E$3）两种形式。前者不论公式怎么复制,列都不变。 后者不论公式怎么复制，行号不变。n绝对符号的添加: 用F4放在公式中的单元格引用中进行切换n几种单元格引

30、用表示法： A2:B7（某一单元格区域）； 1:1（第一行）； 1:5(1至5行)； F:F（F列）；A：N（A到N列）金正大 版权所有25函数参数中标志函数参数中标志u由于Excel 工作表多数带有“列标志”。例如一张成绩统计表的首行通常带有“序号”、“姓名”、“数学”、“物理”等“列标志”(也可以称为字段)，如果单击“工具选项”菜单命令，在打开的对话框中单击“重新计算”选项卡，选中“工作簿选项”选项组中的“接受公式标志”选项，公式就可以直接引用“列标志”了。例如“B2:B46”区域存放着学生的物理成绩，而B1 单元格已经输入了“物理”字样，则求物理平均分的公式可以写成“=AVERAGE(物

31、理)”。u需要特别说明的是，公式引用“列标志”时的限制较多，它只能在当前数据列的下方引用，不能跨越工作表引用。从本质上讲，名称和标志都是单元格引用的一种方式。因为它们不是文本，使用时名称和标志都不能添加引号。函数参数中标志的使用函数参数中标志的使用方法：方法：金正大 版权所有26EXCELEXCEL函数的结构函数的结构uExcel 函数即是预先定义，执行计算、分析等处理数据任务的特殊公式特殊公式。Excel Excel 函数结构函数结构: :也有一些函数是没也有一些函数是没有参数的有参数的,如如ROW()左右括号成对出左右括号成对出现现单单一一结结构构嵌嵌套套结结构构参数与参数之间使用参数与参

32、数之间使用半角逗号半角逗号进行分隔进行分隔金正大 版权所有27函数参数常用符号或表示方法函数参数常用符号或表示方法n函数公式中的文本必须用半角引号，如：东南汽车;而非直接输入 东南汽车或“东南汽车”n连接符: & 如：东南& 汽车的值为 东南汽车n空值/空格的表示法: n 空值：n 空格： n相关数学符号:（不等于）;=（大于等于）;=（小于等于）n单元格引用表示法: A2; $A$2; $A2; A$2; A2:B7； 1:1； 1:5； F:F；A：N金正大 版权所有28RankRank函数函数u用途：返回一个数值在一组数值中的排位(如果数据清单已经排过序了，则数值的排位就

33、是它当前的位置)。u语法：RANK(需要计算其排位的数字，存放要比较的数字的区域，排序方式)u参数：排序方式为一数字，指明排位的方式。如果排序方式为0 或省略，则按降序排列的数据清单进行排位。如果排序方式不为零，当作按升序排列的数据清单进行排位。u注意：函数RANK 对重复数值的排位相同。但重复数的存在将影响后续数值的排位。如在一列整数中，若整数60 出现两次，其排位为5，则61 的排位为7(没有排位为6 的数值)。uRANK(number , ref , order)=RANK(B7,$B$7:$B$19,1)金正大 版权所有29SumSum求和函数求和函数u用途：返回某一单元格区域中所有数

34、字之和。u语法：SUM(number1，number2，.)。u语法格式：Number1，number2，.为1 到30 个需要求和的数值(包括逻辑值及文本表达式)、区域或引用。u注意：参数表中的数字、逻辑值及数字的文本表达式可以参与计算，其中逻辑值被转换为1、文本被转换为数字。如果参数为数组或引用，只有其中的数字将被计算，数组或引用中的空白单元格、逻辑值、文本或错误值将被忽略。SUM函数中的参数，即被求和的单元格或单元格区域不能超过30个。换句话说，SUM函数括号中出现的分隔符（逗号）不能多于29个，否则Excel就会提示参数太多。 u参数太多的突破方法：巧用括号法金正大 版权所有30AVE

35、RAGE函数函数u用途：计算所有参数的算术平均值。u语法：AVERAGE(number1，number2，.)。u参数：Number1、number2、.是要计算平均值的130个参数。u实例：如果A1：A5 区域命名为分数，其中的数值分别为100、70、92、47 和82，则公式“=AVERAGE(分数)”返回78.2。金正大 版权所有31ROUNDROUND对数值进取四舍五入对数值进取四舍五入u用途：按指定位数四舍五入某个数字。u语法：ROUND(需四舍五入的数字，保留的小数位)u注意：如果保留的小数位大于0，则四舍五入到指定的小数位；如果等于0，则四舍五入到最接近的整数；如果小于0，则在小

36、数点左侧按指定位数四舍五入。u实例：如果A1=65.25，则公式“=ROUND(A1，1)”返回65.3；=ROUND(82.149，2)返回82.15；=ROUND(21.5，-1)返回20；Round(A1,0)返回65。金正大 版权所有32INTINT无条件取整无条件取整u用途：将任意实数向下取整为最接近的整数。u语法：INT(实数)u参数：Number 为需要处理的任意一个实数。u实例：如果A1=16.24、A2=-28.389，则公式“=INT(A1)”返回16，=INT(A2)返回-29。金正大 版权所有33TRUNCTRUNC函数函数u用途：将数字的小数部分依需截去（不作四舍五入

37、），返回实数。u语法：TRUNC(需要截去小数部分的数字，保留小数的位数)u注意：TRUNC 函数可以按需要截取数字的小数部分，而INT函数则将数字向下舍入到最接近的整数。INT 和TRUNC 函数在处理负数时有所不同：TRUNC(-4.3)返回-4，而INT(-4.3)返回-5。u实例：如果A1=78.652，则公式“=TRUNC(A1，1)”返回78.6，=TRUNC(A1，2)返回78.65，=TRUNC(-8.963，2)返回8.96。金正大 版权所有34比较两个字符串是否相同比较两个字符串是否相同u用途：测试两个字符串是否完全相同。如果它们完全相同，则返回TRUE；否则返回FALSE

38、。EXACT 函数能区分大小写，但忽略格式上的差异。u语法：EXACT(text1，text2)。u参数：Text1 是待比较的第一个字符串，Text2 是待比较的第二个字符串。u实例：如果A1=得利卡、A2=富利卡、A3=戈蓝，则公式“=EXACT(A1，A2)”返回FALSE，=EXACT(A1，A3)返回FALSE，=EXACT(word，word)返回TRUE。EXACT:EXACT:比较两个字符串是否相同比较两个字符串是否相同金正大 版权所有35CONCATENATECONCATENATE文本合并文本合并n用途：将若干文字串合并到一个文字串中，其功能与&运算符相同。n语法：C

39、ONCATENATE(text1，text2，.)n参数：Text1，text2，.为1 到30 个将要合并成单个文本的文本项，这些文本项可以是文字串、数字或对单个单元格的引用。n实例：如果A1=98、A2=千米，则公式“=CONCATENATE(A1，A2)”返回“98 千米”，与公式“=A1&A2”等价。CONCATENATE金正大 版权所有36日期函数日期函数n用途：返回某日期的年份/月份/日期。n语法：YEAR/MONTH/DAY(日期值)n实例： 假设A2单元格存放的日期为2008-8-20u 公式“=YEAR(A2)返回2008”u 公式“ =MONTH(A2)返回8”u

40、公式“ =DAY(A2)返回20”YEARMONTHDAY日期函数日期函数金正大 版权所有37星期函数星期函数u用途：返回某日期的星期数。在默认情况下，它的值为1(星期天)到7(星期六)之间的一个整数。u语法：WEEKDAY(日期，返回类型)u参数：“返回类型 ”为确定返回值类型的数字，数字1 或省略则1 至7 代表星期天到数星期六，数字2 则1 至7 代表星期一到星期天（推荐使用），数字3 则0 至6 代表星期一到星期天。u实例：公式“=WEEKDAY(”2008/8/20“，2)”返回3(星期三)，=WEEKDAY(“2008/8/20”，3)返回2(星期三)。Weekday星期函数星期函

41、数金正大 版权所有38VALUEVALUE文字转换成数字文字转换成数字u用途：将表示数字的文字串转换成数字。u语法：VALUE(text)。u参数：Text 为带引号的文本，或对需要进行文本转换的单元格的引用。它可以是Excel 可以识别的任意常数、日期或时间格式。如果Text 不属于上述格式，则VALUE 函数返回错误值#VALUE!。VALUEVALUE金正大 版权所有39VALUEVALUE演示演示文本型态的数字不可计算文本型态的数字不可计算通过通过value函数转换就可以函数转换就可以计算了计算了金正大 版权所有40If:If:逻辑函数逻辑函数n功能：IF函数用于执行真假值判断后，根据

42、逻辑测试的真假值返回不同的结果。它的应用很广泛，可以使用函数 IF 对数值和公式进行条件检测。n语法格式： IF(条件,A,B)。其中“条件”表示计算结果为 TRUE 或 FALSE 的任意值或表达式，若为常量，视为True。条件参数可使用任何比较运算符。条件返回的结果为真的话，则执行A，否则执行B。nIF函数仅可以嵌套七层，超过将会出错。金正大 版权所有41IF IF 函数的简单案例一函数的简单案例一 性别为男者,称谓为XX先生;性别为女者,称谓为XX女士=IF(A10,负负,)&IF(A11,TEXT(TRUNC(ABS(A1),DBNum2G/通用格式通用格式)&元元&a

43、mp;IF(ROUND(A1,3)=ROUND(A1,),整整,)&IF(A1*10-FLOOR(A1,1)*10)/100.1,TEXT(RIGHT(TRUNC(A1*10),1),DBNum2G/通用格式通用格式)&角角&IF(ROUND(A1,3)=ROUND(A1,1),整整,)&IF(A1*100-FLOOR(A1*10,1)*10)/10082000,(A3-2000)*40%-10375,IF(A362000,(A3-2000)*35%-6375,IF(A342000,(A3-2000)*30%-3375,IF(A322000,(A3-2000)*

44、25%-1375,IF(A37000,(A3-2000)*20%-375,IF(A34000,(A3-2000)*15%-125,IF(A32500,(A3-2000)*10%-25,IF(A32000,(A3-2000)*5%,0)依依下述税率表下述税率表, ,利用利用 if if函数计算个人所得税函数计算个人所得税IF IF 函数的简单案例二函数的简单案例二 金正大 版权所有43Sumif:Sumif:条件求和函数条件求和函数u用途：根据指定条件对若干单元格、区域或引用求和。u语法：SUMIF(条件区域，条件，需求和的区域)u参数：条件是由数字、逻辑表达式等组成的判定条件。金正大 版权所有

45、44SumifSumif案例案例请统计人资组同仁的工资总额请统计人资组同仁的工资总额: :Sumif(A:A, Sumif(A:A, 人资组人资组,C:C)金正大 版权所有45CountifCountif：条件计数函数：条件计数函数u用途：计算区域中满足给定条件的单元格的个数。u语法：COUNTIF(统计区域，条件)u参数：“统计区域”为需要计算其中满足条件的单元格数目的单元格区域。“条件” 为确定哪些单元格将被计算在内的条件，其形式可以为数字、表达式或文本。CountifCountif：金正大 版权所有46Countif Countif 案例案例 以上述样表为据以上述样表为据, ,完成以下案

46、例完成以下案例: :n 例例1 1：依“师员”字段，统计师员级的人数 countif(C:C,师)；countif(C:C,员)n 例例2 2:统计全公司年资大于等于3年的人数及大于等于3年小于5年的人数 countif(I:I, =3)；countif(I:I, =3)- countif(I:I, =5)n 例例3 3:检测到职编号是否有重复值 countif(A:A, A2)Countif Countif 案例案例 金正大 版权所有47CountaCounta函数函数u用途：返回参数组中非空值的数目。利用函数COUNTA 可以计算数组或单元格区域中数据项的个数。u语法：COUNTA(单元格

47、区域1,单元格区域2) 说明：参数的个数为130 个。Counta金正大 版权所有48CountaCounta的思考案例的思考案例n例1：如果A1=6.28、A2=3.74，其余单元格为空，则公式“=COUNTA(A1:A7)”的计算结果等于？。n例2：统计C列的非空白单元格的表示方法为：？; 若整张EXCEL表的每个单元格都有数据,前述函数公式的结果为？CountaCounta的思考案例的思考案例金正大 版权所有49MODMOD：取余函数：取余函数u用途：返回两数相除的余数，其结果的正负号与除数相同。u语法：MOD(被除数，除数)u参数：除数不能为零。u实例：公式“=MOD(14，4)”返回

48、？ 2；“=MOD(5，-2)”返回？ -1。MODMOD：取余函数：取余函数金正大 版权所有50VlookupVlookup函数函数u返回表格或数组当前行中指定列处的数值。u语法：VLOOKUP(索引值，数据区域，列序号，查找方式)u参数： 索引值为需要在数据表第一列中查找的数值，它可以是数值、引用或文字串。 数据区域为需要在其中查找数据的数据表，可以使用对区域或区域名称的引用。 列序号 指数据区域 中待返回的匹配值的列序号。它等于1 时，返回数据区域 第一列中的数值；它等于 2时，返回数据区域第二列中的数值，以此类推。 查找方式为一逻辑值,指明函数VLOOKUP 返回时是精确匹配还是近似匹

49、配。如果为1或省略，则返回近似匹配值，也就是说，如果找不到精确匹配值，则返回小于索引值 的最大数值；如果为0，函数VLOOKUP 将返回精确匹配值。如果找不到，则返回错误值#N/A。Vlookup金正大 版权所有51截取字符串函数截取字符串函数u用途：根据指定的字符数返回文本串中的第一个或前几个字符。此函数用于双字节字符。u语法：LEFT(文本, 返回的字符数) Right(文本, 返回的字符数)u参数：返回的字符数必须大于或等于0。u实例：如果A1=电脑爱好者，则LEFT(A1，2)返回? ;Right(A1,3)返回?Left/RightLeft/Right：截取字符串函数：截取字符串函数

50、金正大 版权所有52LeftLeft与与rightright的综合运用的综合运用 如何利用Left及Right从身份证号中提取出生年份。LeftLeft与与rightright的综合运用的综合运用金正大 版权所有53MidMid：截取字符串函数：截取字符串函数u用途：MID 返回文本串中从指定位置开始的特定数目的字符，该数目由用户指定。u语法：MID(文本串,开始位置,返回字符的个数) u参数：开始位置是文本中要提取的第一个字符的位置，文本中第一个字符的开始位置为1，以此类推。u实例1：如果a1=东南(福建)汽车工业有限公司，则公式“=MID(A1，4，2)”返回“福建” 。u案例:利用MID

51、从身份证号中提取出生年份。思考：MID函数 与 Left与right的综合运用的区别MidMid：金正大 版权所有54计算字符长度计算字符长度u用途：LEN 返回文本串的字符数。u语法：LEN(text) 。u参数：Text 待要查找其长度的文本。u注意：此函数用于双字节字符，且空格也将作为字符进行统计。u实例：如果A1=电脑爱好者，则公式“=LEN(A1)”返回？LENLEN：金正大 版权所有55IfIf、LenLen及及midmid函数综合运用函数综合运用如果如果A A列里列里CMCM后没有后没有“00”00”的话，就把的话，就把“00“00“加到加到CMCM后后, ,但是如果但是如果CM

52、CM后后有有”00“00“话，就不加了（产生的效果如话，就不加了（产生的效果如B B列所示），怎么做？列所示），怎么做？公式提示：公式提示：=IF(MID(A1,3,2)00,CM00&MID(A1,3,LEN(A1)-2),A1)=IF(MID(A1,3,2)00,CM00&MID(A1,3,LEN(A1)-2),A1)金正大 版权所有56替换指定文本替换指定文本u用途：在文字串中用新文本替代旧文本。如果需要在一个文字串中替换指定的文本，可以使用函数SUBSTITUTE；如果需要在某一文字串中替换指定位置处的任意文本，就应当使用函数REPLACE。u语法：SUBSTITUTE

53、(需要替换其中字符的文本，需被替换的旧文本，用於替换的文本，第几次出现的旧文本)。u参数： 第几次出现的旧文本为一数值，用来指定以新的文本替换第几次出现的旧文本；如果指定了该参数，则只有满足要求的旧文本被替换；否则将用新文本替换所有出现的旧文本。SUBSTITUTESUBSTITUTE：替换指定文本：替换指定文本金正大 版权所有57替换指定文本替换指定文本SUBSTITUTESUBSTITUTE：替换指定文本：替换指定文本金正大 版权所有58ReptRept：补位函数：补位函数u用途：按照给定的次数重复显示文本。可以通过REPT 函数对单元格进行重复填充。u语法：REPT(需重复显示的文本，重复显示的次数)。u参数：重复显示的次数需为正数。u注意：REPT 函数的结果不能多于255 个字符。u实例：公式“=REPT(很好，2)”返回“很好很好”。ReptRept：金正大 版权所有59ReptRept函数与函数与lenlen函数的综合运用函数的综合运用金正大 版权所有60IndexIndex函数函数u用途：返回表格或区域中的数值。u语法：INDEX(单元格区域，行序号，列序号)返回数组中指定的单元格或单元格数组的数值。u案例：题一：题一：=INDE