excel函数培训

1、EXCEL实用技巧培训主要内容函数的应用函数的应用1数据透视表数据透视表2图表的制作图表的制作3公式中的运算符公式中的运算符算术运算符：+ - * / %字符运算符：& 连接符比较运算符：= = 逻辑运算符：and or not 以函数形式出现优先级顺序：算术运算符字符运算符比较运算符逻辑函数符 (使用括号可确定运算顺序) 错误信息原因#！1.公式所产生的结果太长，该单元格容纳不下2.日期或时间格式的单元格中出现负值#DIV/0!公式中出现被零除的现象#N/A在函数或公式中没有可用数值时#NAME?在公式中出现excel不能识别的文本时#NULL!当试图为两个并不交叉的区域指定交叉点时

2、#NUM!当函数或公式中某个数字有问题时#REF!当单元格引用无效时#VALUE!使用错误的参数或运算对象类型 Excel的错误信息1、SUM函数行或列求和行或列求和 H3单元格单元格 =SUM(B3:G3)B11单元格单元格 =SUM(B3:B10)1、SUM函数区域求和区域求和 H11单元格单元格 =SUM(B3:G10)1、SUM函数鼠标拖拉法鼠标拖拉法选中存放求和结果的单元格，点击工具栏选中存放求和结果的单元格，点击工具栏中的中的“”（自动求和）按钮（自动求和）按钮用鼠标拖过需要求和的所有单元格，然后用鼠标拖过需要求和的所有单元格，然后回车即可回车即可注：如果需要求和的单元格是不连续的

3、，注：如果需要求和的单元格是不连续的，则可按住则可按住CtrlCtrl键分别拖过它们。键分别拖过它们。2、AVERAGE/TRIMMEAN函数AVERAGEAVERAGE函数函数用途：求算数平均数用途：求算数平均数使用方法：同使用方法：同SUMSUM函数相同，只需把刚才所有函数相同，只需把刚才所有“SUM”SUM”的部分替换成的部分替换成“AVERAGE”AVERAGE”=AVERAGE(B3:G3)=AVERAGE(B3:B10)=AVERAGE(B3:G10)2、AVERAGE/TRIMMEAN函数TRIMMEANTRIMMEAN函数函数用途：求数据集的内部平均值（即可以按照要用途：求数据

4、集的内部平均值（即可以按照要求去掉所有数据中最高的和最低的一些数据，求去掉所有数据中最高的和最低的一些数据，然后求平均值）然后求平均值）使用方法：使用方法：TRIMMEANTRIMMEAN（array,percent)array,percent)例，例，需要求内需要求内部平均值部平均值的所有数的所有数据区域据区域需要去除需要去除的数据占的数据占全部数据全部数据的比例的比例3、COUNT/COUNTA函数用途：计数用途：计数COUNTCOUNT统计所选中的单元格区域中数字的个数统计所选中的单元格区域中数字的个数COUNTACOUNTA统计所选中的单元格区域中非空单元统计所选中的单元格区域中非空单

5、元格的个数（包括数字、文字等）格的个数（包括数字、文字等）例，例，4、常用函数快捷方式在在excel表格的右下角有表格的右下角有一个常用函数快捷方式的一个常用函数快捷方式的区域（如同所示），可以区域（如同所示），可以右键点击该区域，使用六右键点击该区域，使用六种常用函数种常用函数这六种常用函数，分别是：这六种常用函数，分别是：平均值（平均值（AVERAGE）计数（计数（COUNTA）计数值（计数值（COUNT）最大值（最大值（MAX）最小值（最小值（MIN）求和（求和（SUM）5、ROUND/TRUNC函数功能：功能：ROUNDROUND函数函数对小数按照位数要求四舍五入对小数按照位数要求四舍

6、五入 （亦可通过设置（亦可通过设置“单元格格式单元格格式”实现同样功能）实现同样功能）ROUND(number,num_digits)number就是将要进行四舍五入的数字就是将要进行四舍五入的数字num_digits则是需要保留小数点后几位则是需要保留小数点后几位TRUNCTRUNC函数函数对小数进行取整对小数进行取整 （同（同INTINT函数功能）函数功能）TRUNC(number)number就是需要进行取整的数字就是需要进行取整的数字5、ROUND/TRUNC函数使用方法：使用方法：B2B2单元格单元格 =ROUND(A2,2)=ROUND(A2,2)C2C2单元格单元格 =ROUND

7、(A2,0)=ROUND(A2,0)D2D2单元格单元格 =TRUNC(A2) =TRUNC(A2) 或或 =INT(A2)=INT(A2)6、IF函数（COUNTIF/SUMIF）例，例，在在B12单元格中输入：单元格中输入： =IF(B11=60,”合格合格”,”不合格不合格”)逻辑判断式逻辑判断式当逻辑判断式为真当逻辑判断式为真时，返回的值时，返回的值当逻辑判断式为当逻辑判断式为假时，返回的值假时，返回的值6、IF函数（COUNTIF/SUMIF）COUNTIFCOUNTIF函数函数功能：统计符合条件要求的单元格的数量功能：统计符合条件要求的单元格的数量例，例， 在在B5B5单元格中输入

8、单元格中输入=countif(B2:B4,”20”),=countif(B2:B4,”20”),可可以求出在以求出在B2B2到到B4B4这三个单元格中大于这三个单元格中大于2020的数字有的数字有几个几个6、IF函数（COUNTIF/SUMIF）SUMIFSUMIF函数函数功能：统计符合条件要求的单元格中数字功能：统计符合条件要求的单元格中数字之和之和例，例，在在B5B5单元格中输入单元格中输入=sumif(B2:B4,”20”),=sumif(B2:B4,”20”),可以求出在可以求出在B2B2到到B4B4这三个单元格中大于这三个单元格中大于2020的数字之和的数字之和7、SUBTOTAL函

9、数7、SUBTOTAL函数为了分别统计各个专业的人数，可以在为了分别统计各个专业的人数，可以在D1单单元格中输入：元格中输入： =SUBTOTAL(3,D3:D30)然后利用然后利用“数据数据筛选筛选自动筛选自动筛选”功能即功能即可可7、SUBTOTAL函数用途：对筛选列表中的用途：对筛选列表中的可见单元格可见单元格进行运算进行运算语法及功能语法及功能SUBTOTAL（function_num,ref1,ref2,）所引用区域所引用区域Function_num对应功能对应功能1AVERAGE（求平均值）（求平均值）2COUNT（数值计数）（数值计数）3COUNTA（计数）（计数）4MAX（求最

10、大值）（求最大值）5MIN（求最小值）（求最小值）9SUM（求和）（求和）8、Vlookup/Hlookup函数例例1，excelexcel中有两张表格（如下图所示），分别中有两张表格（如下图所示），分别是分数等级表（上图）和成绩录入（下图）：是分数等级表（上图）和成绩录入（下图）：在成绩录入的在成绩录入的D4D4单元格单元格 输入：输入：=HLOOKUP(C4,=HLOOKUP(C4,分数等级表分数等级表!B1:K4,3)!B1:K4,3)在成绩录入的在成绩录入的D5D5单元格单元格 输入：输入：=HLOOKUP(C4,=HLOOKUP(C4,分数等级表分数等级表!B1:K4,4)!B1:K

11、4,4)8、Vlookup/Hlookup函数功能：功能：VlookupVlookup用于在表格或数值数组的首行查找指用于在表格或数值数组的首行查找指定的数值，并由此返回表格或数组当前列中指定的数值，并由此返回表格或数组当前列中指定行处的数值定行处的数值HlookupHlookup用于在表格或数值数组的首列查找指用于在表格或数值数组的首列查找指定的数值，并由此返回表格或数组当前行中指定的数值，并由此返回表格或数组当前行中指定列处的数值定列处的数值当比较值位于数据表的首行，并且要查找下面当比较值位于数据表的首行，并且要查找下面给定行中的数据时，请使用给定行中的数据时，请使用HlookupHloo

12、kup当比较值位于数据表的最左列，并且要查找右当比较值位于数据表的最左列，并且要查找右面给定列中的数据时，请使用面给定列中的数据时，请使用VlookupVlookup8、Vlookup/Hlookup函数使用方法：使用方法：HLOOKUP(lookup_value,table_array,row_index_num,rang_lookup)VLOOKUP(lookup_value,table_array,col_index_num,rang_lookup)所要所要查找查找的值的值用于查找数据的区域，用于查找数据的区域，所所要查找的数据必须与该区要查找的数据必须与该区域首行（或最左列）的数域首行

13、（或最左列）的数据进行比较据进行比较返回对应返回对应该行序号该行序号的单元格的单元格返回对应返回对应该列序号该列序号的单元格的单元格FALSE（精（精确匹配）或确匹配）或TRUE（近（近似匹配）似匹配）8、Vlookup/Hlookup函数例例2，excelexcel中有两张表格（如下图所示），分别是分数中有两张表格（如下图所示），分别是分数等级表二（左图）和成绩录入（右图）：等级表二（左图）和成绩录入（右图）：在成绩录入的在成绩录入的D4D4单元格输入：单元格输入：=VLOOKUP(C4,=VLOOKUP(C4,分数分数等级等级表表二二!A2:D11,3)!A2:D11,3)在成绩录入的在成

14、绩录入的E4E4单元格输入：单元格输入：=VLOOKUP(C4,=VLOOKUP(C4,分数分数等级等级表表二二!A2:D11,4)!A2:D11,4)8、Vlookup/Hlookup函数上面两个例子，在下拉完上面两个例子，在下拉完成其他类似函数运算时，成其他类似函数运算时，出现了问题出现了问题我们在我们在D4D4单元格输入了单元格输入了 =VLOOKUP(C4,=VLOOKUP(C4,分数分数等级等级表表二二!A2:D11,3)!A2:D11,3)，需要需要D5D5单单元格按照公式元格按照公式=VLOOKUP(C5,=VLOOKUP(C5,分数分数等级等级表表二二! !A2:D11A2:D

15、11,3),3)进行运算，进行运算，然而在下拉时，系统会自然而在下拉时，系统会自动将动将D5D5单元格的公式变为单元格的公式变为=VLOOKUP(C5,=VLOOKUP(C5,分数分数等级等级表表二二! !A3:D12A3:D12,3),3)8、Vlookup/Hlookup函数在在D4D4单元格输入单元格输入 = =VLOOKUP(C4,VLOOKUP(C4,分数分数等级等级表表二二!A2:D11,3)!A2:D11,3)之后，用之后，用鼠标选中公式中鼠标选中公式中A A2:D112:D11部分，然后按部分，然后按F4F4键，即可键，即可看到看到A A2:D112:D11部分变为了部分变为了

16、$A$2:$D$11$A$2:$D$11，这样在下拉这样在下拉就不会出现问题了，即就不会出现问题了，即D5D5单元格按照公式单元格按照公式= =VLOOKUP(C5,VLOOKUP(C5,分数分数等级等级表表二二! !$A$2:$D$11$A$2:$D$11,3),3)运算，运算， D6 D6单元格按照公式单元格按照公式 = =VLOOKUP(C6,VLOOKUP(C6,分数分数等级等级表表二二! !$A$2:$D$11$A$2:$D$11,3),3)运算，运算， 以此类推以此类推9、MOD函数在美化表格中的应用功能：设置间隔式底纹功能：设置间隔式底纹例，例，9、MOD函数在美化表格中的应用选

17、中需要设置间隔式底纹的区域选中需要设置间隔式底纹的区域“格式格式条件格式条件格式”，弹出的，弹出的“条件格式条件格式”对话框（如图）对话框（如图）在左侧的下拉菜单中选择在左侧的下拉菜单中选择“公式公式”，并在，并在右侧区域输入右侧区域输入=MOD(ROW()，2),然后点击然后点击“格式格式”按钮按钮9、MOD函数在美化表格中的应用在弹出的在弹出的“单元格格式单元格格式”对话框中，选择对话框中，选择“图案图案”选项卡，然后选择需要的颜色，选项卡，然后选择需要的颜色，“确定确定”点击点击“条件格式条件格式”对话框中的对话框中的“确定确定”按按钮钮2 2、 数据透视表数据透视表 数据透视表是一种特

18、殊形式的表，它可以把源数据数据透视表是一种特殊形式的表，它可以把源数据的行和列进行互换后汇总并显示汇总结果。特别是用于分析，的行和列进行互换后汇总并显示汇总结果。特别是用于分析，组织复杂的数据。组织复杂的数据。1. 1. 创建数据透视表创建数据透视表 创建数据透视表，可以按下述操作步骤进行：创建数据透视表，可以按下述操作步骤进行： 在数据清单中单击任意一个单元格，然后执行在数据清单中单击任意一个单元格，然后执行“数据数据”“数据透视表和数据透视图数据透视表和数据透视图”命令，打开如图命令，打开如图1.401.40所示的对话框。所示的对话框。图图1.40 “1.40 “数据透视表和数据透视图向导

19、数据透视表和数据透视图向导-3-3步骤之步骤之1”1”对话框对话框 在此对话框中指定待分析数据的数据源和所创建的报表类型。例如，选择“Microsoft Office Excel数据列表或数据库”单选项，然后单击“下一步”按钮，打开如图5.41所示的对话框。图1.41 “数据透视表和数据透视图向导-3步骤之2”对话框 在此对话框的“选定区域”文本框中指定数据源的区域，再单击“下一步”按钮，打开如图1.42所示的对话框。图1.42 “数据透视表和数据透视图向导-3步骤之3”对话框 在此对话框中选择数据透视表的显示位置，然后单击“完成”按钮。这时，出现数据透视表的设置版式，并在屏幕上显示一个包含字

20、段名的“数据透视表字段列表”任务窗格和“数据透视表”工具栏，如图5.43所示。数据透视表由4个区域构成，分别是页字段区域、行字段区域、列字段区域和数据项区域。“数据透视表字段列表”任务窗格中提供了源数据清单所包含的字段名按钮，可以根据需要单击字段名按钮，并将其拖放到相应的区域中，这样就可以创建数据透视表。图1.43 数据透视表的设置版式2. 删除数据透视表 如果要删除数据透视表，单击透视表中的任意一个单元格，在“数据透视表”工具栏上单击“数据透视表”按钮，则弹出“数据透视表”下拉菜单，在菜单中选择“选定”命令下的“整张表格”命令，最后执行“编辑”“清除”“全部”命令，即可删除数据透视表。 将表

21、格中的数据用图表的方式展示，可以使数据更清楚，更利于理解，并且可以从图表上直接了解到数据之间的关系和数据变化的趋势效果。1.7.1 建立图表建立图表 Excel 2003提供两种方式建立图表：一是创建嵌入式图表，即把建立的图表作为源数据所在工作表的对象插入到该工作表中，可以同时显示图表及其源数据；另一种是把建立的图表绘制成一个独立的图表工作表。图表与其源数据相互关联，当改变工作表中的数据时，图表会自动更新。3 电子表格的图表化电子表格的图表化图表相关名词术语图例图例数据标记数据标记数据系列数据系列类型轴类型轴 (X)刻度线刻度线值轴（值轴（Y）1. 创建默认形式的图表工作表 在工作表中选定要创

22、建图表的数据，然后按下F11键，就可以在一个新工作表中建立一个系统默认的图表。2. 使用图表工具栏中的按钮创建图表 首先需要在窗口中显示图表工具栏，然后选定用于创建图表的数据区域，单击图表工具栏中的“图表类型”按钮右侧的下三角按钮，在弹出的“图表类型”下拉列表中选择一种图表类型，如图5.44所示，即可快速地在当前工作表中插入一个图表。图1.44 图表工具栏3. 使用图表向导创建图表 使用图表向导创建图表，具体操作步骤如下： 选定要创建图表的数据区域。 单击常用工具栏中的“图表向导”按钮或单击“插入”“图表”命令，出现如图1.45所示的“图表向导”对话框。该对话框中有两个选项卡：“标准类型”和“

23、自定义类型”，可以从中选择图表类型和子类型。图1.45 “图表向导-4步骤之1-图表类型”对话框 单击“下一步”按钮，出现如图1.46所示的对话框。在“数据区域”文本框内指定创建图表的数据区域，然后选择系列产生在“行”或“列”选项。图1.46 “图表向导-4步骤之2-图表源数据”对话框 单击“下一步”选项，出现如图1.47所示的第三个对话框。该对话框中有6个选项卡，分别用于设置标题、坐标轴、网格线、图例、数据标志和数据表。 单击“下一步”按钮，出现图表向导的最后一个对话框，见图1.48。在该对话框中选择建立图表的位置，是嵌入工作表还是图表工作表。 单击“完成”按钮，即可创建图表。 图1.47

24、“图表向导-4步骤之3-图表选项”对话框 图1.48 “图表向导-4步骤之4-图表位置”对话框1.7.2 编辑图表编辑图表 在图表创建好之后，一般要根据实际需要对其进行编辑或修改。1. 图表区域的选定 在编辑过程中，有时需要对整个图表进行操作，而有时需要对其中的某一个元素进行操作，这样就要分别选取不同的图表区域。（1）选定整个图表区域 单击图表中的空白区域，在边框内出现8个控制柄，表明已经选定图表区域。 （2）选定绘图区 绘图区是放置图表主体的背景区域，即以两条坐标轴为界的矩形区域。单击图表中坐标轴之内的空白区域，即可选定绘图区。（3）选定数据系列 数据系列是指在图表中的一组相关数据点，来源于

25、工作表中的一行或一列。图表中的每一数据系列都具有特定的颜色或图案，单击图表中某个数据系列中的一个图项，该系列中的每个图项中间出现一个控制柄。（4）选定数据点 数据点对应的是工作表的一个单元格中的数据。在选定数据系列的情况下，单击系列中的某个数据点，即可选定该数据点。 另外，也可以单击图表工具栏中“图表对象”列表框右侧的下三角按钮，从下拉列表中选择要选定的图表元素。2. 移动图表 用户可以在工作簿窗口中任意移动嵌入式图表，具体方法是： 在图表上单击以选定该图表，这时图表的周围出现一个边框，同时出现8个控制柄。 在图表上按住鼠标左键拖动，鼠标指针变成十字箭头形状，并且会出现一个虚框显示当前的位置。 拖动到所需的位置后，松开鼠标即