EXCEL函数培训教材大全学习教案

1、会计学1EXCEL函数培训教材大全函数培训教材大全建立数据清单建立数据清单建立数据清单的准则建立数据清单的准则使用记录单使用记录单数据排序数据排序对数据清单进行排序对数据清单进行排序创建自定义排序创建自定义排序数据筛选数据筛选自动筛选自动筛选高级筛选高级筛选分类汇总分类汇总分类汇总的计算方法分类汇总的计算方法汇总报表和图表汇总报表和图表插入和删除分类汇总插入和删除分类汇总插入图表表示数据插入图表表示数据复杂的数据链接复杂的数据链接使用数据透视表使用数据透视表创建数据透视表创建数据透视表编辑数据透视表编辑数据透视表controll toolbox usage (控件工具箱控件工具箱)课程大纲课程

2、大纲第1页/共69页算术运算符号算术运算符号运算符含义运算符含义示示 例例+(加号加号)加加2+3=5-(减号减号)减减3-1=2*(星号星号乘乘3*2=6/(斜杠斜杠)除除6/2=3%(百分号百分号)百分号百分号50%(脱字号脱字号)乘方乘方43=43=64第2页/共69页比较运算符比较运算符运算符含义运算符含义示示 例例=(等号等号)相等相等B1=C1,若若B1中单元格内的值确实与中单元格内的值确实与C1中的值中的值相等相等,则产生逻辑真值则产生逻辑真值TRUE,若不相等若不相等,则产生逻则产生逻辑假值辑假值FALSE(小于号小于号)小于小于B1(大于号大于号)大于大于B1C1,若若B1中

3、数值为中数值为6,C1中数值为中数值为4,则条件成则条件成立产生逻辑真值立产生逻辑真值TRUE,否则产生逻辑假值否则产生逻辑假值FALSE=(大于等于号大于等于号)大于等于大于等于B1=C1(不等号不等号)不等于不等于B1C1=(小于等于号小于等于号)小于等于小于等于B1=C1第3页/共69页引用运算符引用运算符运算符含义运算符含义示示 例例:区域运算符区域运算符,产生对包产生对包括在丙相引用之间的括在丙相引用之间的所有单元格的引用所有单元格的引用(A5:A15),联合运算符联合运算符,将多个引将多个引用合并为一个引用用合并为一个引用SUM(A5:A15,C5:C15)(空格空格)交叉运算符交

4、叉运算符,产生对两产生对两个引用构有的单元格个引用构有的单元格的引用的引用(B7:D7 C6:C8)例:A4=B4+C4+C4+E4+F4,可写为: A4=SUM(B4:F4)第4页/共69页第5页/共69页第6页/共69页第7页/共69页第8页/共69页逻辑值: FALSE :需要完全一致 TRUE: 相似值亦可转换第9页/共69页语法语法作用作用SUM(number1,number2, )返回单元格区域中所有数值的和返回单元格区域中所有数值的和ISPMT(Rate,Per,Nper,Pv)返回普通返回普通(无提保无提保)的利息偿还的利息偿还AVERAGE(Number1,Number2,

5、)计算参数的算术平均数计算参数的算术平均数,参数可以是数值或包含数值参数可以是数值或包含数值的名称数组和引用的名称数组和引用IF(Logical_test,Value_if_true,Value_if_false)执行真假判断执行真假判断,根据对指定条件进行逻辑评价的真假根据对指定条件进行逻辑评价的真假而返回不同的结果。而返回不同的结果。HYPERLINK(Link_location,Friendly_name)创建快捷方式创建快捷方式,以便打开文档或网络驱动器或连接连以便打开文档或网络驱动器或连接连接接INTERNETCOUNT(Value1,Value2, )计算参数表中的数字参数和包含数

6、字的单元格的个数计算参数表中的数字参数和包含数字的单元格的个数MAX(number1,number2, )返回一组数值中的最大值返回一组数值中的最大值,忽略逻辑值和文字符忽略逻辑值和文字符SIN (number)返回纪念品定角度的正弦值返回纪念品定角度的正弦值SUMIF (Range, Criteria, Sum_range)根据指定条伯对若干单元格求和根据指定条伯对若干单元格求和PMT(Rate,Nper,Pv,Fv,Type)返回在固定利率下返回在固定利率下,投资或贷款的等额分期偿还额投资或贷款的等额分期偿还额第10页/共69页注:Rate 贷款利率。Nper 该项贷款的付款总数。Pv 现

7、值,或一系列未来付款的当前值的累积和,也称为本金。Fv 为未来值,或在最后一次付款后希望得到的现金余额,如果省略 fv则假设其值为零,也就是一笔贷款的未来值为零。Type 数字 0 或 1,用以指定各期的付款时间是在期初还是期末。第11页/共69页主要用于分析和处理日期值和时间值,包括DATE、DATEVALUE、DAY、HOUR、TIME、TODAY、WEEKDAY 和 YEAR等。例：函数DATA语法：DATE(year,month,day)第12页/共69页第13页/共69页主要用于进行各种各样的计算,包括ABS、ASIN、COMBINE、COSLOG、PI、ROUND、SIN、TAN

8、和TUUNC等。例：ABS函数语法：ABS(number)第14页/共69页第15页/共69页3.在上图的对话框中,选择函数分类下面的函数类型及函数名下面的对应函数名。4. 单击确定按钮,弹出如图所示的相应的调用函数对话框：第16页/共69页第17页/共69页第18页/共69页第19页/共69页函数的使用函数的使用 (COUNTIF & SUMIF)(COUNTIF & SUMIF)SUMIFSUMIF函数函数函数名称：函数名称：SUMIFSUMIF主要功能：计算符合指定条件的单元格区域内的数值和。主要功能：计算符合指定条件的单元格区域内的数值和。使用格式：使用格式：SUMIF

9、SUMIF（Range,Criteria,Sum_RangeRange,Criteria,Sum_Range）参数说明：参数说明：RangeRange代表条件判断的单元格区域；代表条件判断的单元格区域；CriteriaCriteria为指定条件表达式；为指定条件表达式；Sum_RangeSum_Range代表需要计算的数值所在的单元格区域。代表需要计算的数值所在的单元格区域。第20页/共69页应用举例：如图应用举例：如图7 7所示所示, ,在在B64B64和和C64C64单元格中分别输入公式：单元格中分别输入公式：=SUBTOTAL(3,C2:C63)=SUBTOTAL(3,C2:C63)和和

10、=SUBTOTAL103,C2:C63),=SUBTOTAL103,C2:C63),并且将并且将6161行隐藏行隐藏起来起来, ,确认后确认后, ,前者显示为前者显示为6262（包括隐藏的行）（包括隐藏的行）, ,后者显示为后者显示为61,61,不不包括隐藏的行。包括隐藏的行。特别提醒：如果采取自动筛选特别提醒：如果采取自动筛选, ,无论无论function_numfunction_num参数选用什么类参数选用什么类型型,SUBTOTAL,SUBTOTAL函数忽略任何不包括在筛选结果中的行；函数忽略任何不包括在筛选结果中的行；SUBTOTALSUBTOTAL函数适函数适用于数据列或垂直区域用于

11、数据列或垂直区域, ,不适用于数据行或水平区域。不适用于数据行或水平区域。第21页/共69页第22页/共69页第23页/共69页TRUETRUE返回逻辑值返回逻辑值 TRUETRUE。语法语法TRUE( )TRUE( )说明说明可以直接在单元格或公式中键入可以直接在单元格或公式中键入值值 TRUETRUE，而可以不使用此函数。，而可以不使用此函数。函数函数 TRUE TRUE 主要用于与其他电子表主要用于与其他电子表格程序兼容。格程序兼容。第24页/共69页第25页/共69页第26页/共69页第27页/共69页第28页/共69页第29页/共69页第30页/共69页第31页/共69页第32页/共

12、69页第二步：选定需要筛选的数据清单中任一单元格；第三步：选择菜单”Data / Filter / Advanced Filter”第四步：在“条件区域”编辑框中，输入条件区域的引用，并包括条件标志。第五步：若要更改筛选数据的方式，可更改条件区域中的值，并再次筛选数据。注：如果需要将筛选结果放到另一位置，请选择”Copy to another Location”第33页/共69页请使用若要查找?（问号）任何单个字符例如，sm?th 查找“smith”和“smyth”*（星号）任何字符数例如，*east 查找“Northeast”和“Southeast”（波形符）后跟 ?、* 或 问号、星号或波

13、形符例如，“fy91?”将会查找“fy91?”通配符通配符以下通配符可作为筛选以及查找和替换内容时的比较条件 :第34页/共69页第35页/共69页要分类汇总的列分类汇总步骤步骤:第36页/共69页要分类汇总的列分类汇总第37页/共69页第38页/共69页第39页/共69页数据透视表是交互式报表，可快速合并和比较大量数据。您可旋转其行和列以看到源数据的不同汇总，而且可显示感兴趣区域的明细数据。 源数据第三季度高尔夫汇总的源值数据透视表C2 和 C8 中源值的汇总第40页/共69页何时应使用数据透视表何时应使用数据透视表如果要分析相关的汇总值，尤其是在要合计较大的列表并对每个数字进行多种比较时，

14、可以使用数据透视表。在上面所述报表中，用户可以很清楚地看到单元格 F3 中第三季度高尔夫销售额是如何通过其他运动或季度的销售额或总销售额计算出来的。由于数据透视表是交互式的，因此，您可以更改数据的视图以查看更多明细数据或计算不同的汇总额，如计数或平均值。 数据是如何组织的数据是如何组织的在数据透视表中，源数据中的每列或字段都成为汇总多行信息的数据透视表字段。在上例中，“运动”列成为“运动”字段，高尔夫的每条记录在单个高尔夫项中进行汇总。数据字段（如“求和项：销售额”）提供要汇总的值。上述报表中的单元格 F3 包含的“求和项：销售额”值来自源数据中“运动”列包含“高尔夫”和“季度”列包含“第三季

15、度”的每一行。第41页/共69页第42页/共69页第43页/共69页第44页/共69页第45页/共69页创建图表图表是Excel为用户提供的强大功能,通过创建各种不同类型的图表,为分析工作表中的各种数据提供更直观的表示结果。l使用图表向导 下面我们先在工作薄中建立数据表如下页图所示,根据工作表中的数据,创建不同类型的图表。l图表的编辑 1.插入数据标志及增加图表标题 2.改变图表的文字、颜色和图案第46页/共69页第47页/共69页操作步骤如下： 1)选定工作表中包含所需数据的所有单元格。 2)单击常用工具栏中的图表向导按钮 ,弹出图（1-16）所示的对话框： 图表的使用图表的使用第48页/共

16、69页第49页/共69页图表的使用（续图表的使用（续2 2）4）然后继续单击下一步弹出图对话框 选择图表存放的位置,若要将图表放到另一个新的工作表上,请选中As new sheet单选框,然后作为新工作表插入文本框,键入新工作表的名字,默认为,如果选择As object in单选框,可将： 第50页/共69页第51页/共69页图表标题分类X轴数值Y轴第52页/共69页第53页/共69页数据标志数据标志说明说明category name category name 显示指定到数据点的分类显示指定到数据点的分类valuevalue显示数据点的值显示数据点的值percentage percentag

17、e 对于饼图、圆环图显示出对整体的百分比对于饼图、圆环图显示出对整体的百分比bubble sizebubble size对于离散图、气泡图显示出气泡的百分比对于离散图、气泡图显示出气泡的百分比第54页/共69页若要对标题进行编辑,可将鼠标移至标题的左下角处双击,即会出现右图所示对话框,用该对话框可以对标题的字体、字体的背景色等进行修改。第55页/共69页若要对图表区域进行修改,可将鼠标移至图表的空白区域双击鼠标,则出现图（1-23）对话框,在此对话框中可选定要修改的内容。第56页/共69页若要对图例进行修改,可将鼠标移至图例区双击鼠标,则出现如图（1-24）对话框。在此对话框中可对图例进行编辑

18、,如图案、字体及位置。第57页/共69页复选框复选框 可通过选中或清除来打开或关闭的选项。您可以在一个工作表中同时选中多个复选框。 文本框文本框 可以向其中键入文字的框。命令按钮命令按钮 单击可启动某项操作的按钮。选项按钮选项按钮 用于从一组选项中选择某一选项的按钮。列表框列表框 包含项目列表的框。组合框组合框 含有下拉列表框的文本框。您可以从列表中选择,或在框中键入所需内容。切换按钮切换按钮 单击后保持按下状态,再单击时又弹起的按钮。微调按钮微调按钮 可附加在单元格上或文本框上的按钮。若要增加数值,请单击向上箭头；若要减小数值,请单击向下箭头。滚动条滚动条 可通过单击滚动箭头或拖动滚动块来滚

19、动数据区域的一种控件。单击滚动箭头与滚动块之间的区域时,可以滚动整页数据。标签标签 添加到工作表或窗体中,用以提供有关控件、工作表或窗体信息的文本。图像图像 将图片嵌入到窗体中的控件。其他控件其他控件其他 ActiveX 控件的列表。第58页/共69页关于宏关于宏如果经常在 Microsoft Excel 中重复某项任务,那么可以用宏自动执行该任务。宏是一系列命令和函数,存储于 Visual Basic 模块中,并且在需要执行该项任务时可随时运行。例如,如果经常在单元格中输入长文本字符串,则可以创建一个宏来将单元格格式设置为文本可自动换行。录制宏录制宏 在录制宏时,Excel 在您执行一系列命

20、令时存储该过程的每一步信息。然后即可运行宏来重复所录制的过程或“回放”这些命令。如果在录制宏时出错,所做的修改也会被录制下来。Visual Basic 在附属于某工作薄的新模块中存储每个宏。第59页/共69页使宏易于运行使宏易于运行 可以在“宏”对话框的列表中选择所需的宏并运行宏。如果希望通过单击特定按钮或按下特定组合键来运行宏,可将宏指定给某个工具栏按钮、键盘快捷键或工作表中的图形对象。管理宏管理宏 宏录制完后,可用 Visual Basic 编辑器查看宏代码以进行改错或更改宏的功能。例如,如果希望用于文本换行的宏还可以将文本变为粗体,则可以再录制另一个将单元格文本变为粗体的宏,然后将其中的

21、指令复制到用于文本换行的宏中。“Visual Basic 编辑器”是一个为初学者设计的编写和编辑宏代码的程序,而且提供了很多联机帮助。不必学习如何编程或如何用 Visual Basic 语言来对宏进行简单的修改。利用“Visual Basic 编辑器”,您可以编辑宏、在模块间复制宏、在不同工作簿之间复制宏、重命名存储宏的模块或重命名宏。第60页/共69页宏安全性宏安全性 Excel 对可通过宏传播的病毒提供安全保护。如果您与其他人共享宏,则可使用数字签名来验证其他用户,这样就可保证其他用户为可靠来源。无论何时打开包含宏的工作簿,都可以先验证宏的来源再启用宏。第61页/共69页创建宏创建宏 录制

22、宏将安全级设置为“中”或“低”。 操作方法在“工具”菜单上,单击“选项”。 单击“安全性”选项卡。 在“宏安全性”之下,单击“宏安全性”。 单击“安全级”选项卡,再选择所要使用的安全级。 在“工具”菜单上,指向“宏”,再单击“录制新宏”。 在“宏名”框中,输入宏 的名称。 注意注意宏名的首字符必须是字母,其他字符可以是字母、数字或下划线。宏名中不允许有空格；可用下划线作为分词符。宏名不允许与单元格引用重名,否则会出现错误信息显示宏名无效。如果要通过按键盘上的来运行宏, 请在“快捷键”框中,输入一个字母。可用 Ctrl+字母（小写字母）或 Ctrl+Shift+字母（大写字母）,其中字母可以是键盘上的任意字母键。快捷键字母不允许是数字或特殊字符（如 或 #）。 注释注释 当包含宏的工作簿打开时,宏快捷键优先于任何相当的 Microsoft Excel 的默认快捷键。第62页/共69页在“保存在”框中,单击要存放宏的地址。 如果要使宏在使用 Excel 的任何时候都可用,请选中“个人宏工作簿”。如果要添加有关宏的说明,请在“说明”框中键入该说明。单击“确定”。 如果要使宏相对于活动单元格位置运行,请用相对单元格引用来录制该宏。在“停止录制”工具栏上,单击“相对引用” 以将其