EXCEL培训课程学习教案

《EXCEL培训课程学习教案》由会员分享，可在线阅读，更多相关《EXCEL培训课程学习教案（70页珍藏版）》请在装配图网上搜索。

1、会计学1EXCEL培训培训(pixn)课程课程第一页，共70页。创建包含引用或名称的公式创建包含函数的公式使用函数常用函数财务函数日期和时间函数数学与三角函数统计函数函数的输入手工输入利用函数向导输入求和计算扩充的自动求和条件求和建立数据清单建立数据清单建立数据清单的准则建立数据清单的准则使用记录单使用记录单数据排序数据排序对数据清单进行排序对数据清单进行排序创建自定义排序创建自定义排序数据筛选数据筛选自动筛选自动筛选高级筛选高级筛选分类汇总分类汇总分类汇总的计算方法分类汇总的计算方法汇总报表和图表汇总报表和图表插入和删除分类汇总插入和删除分类汇总插入图表表示数据插入图表表示数据复杂复杂(fz

2、)的数据链接的数据链接使用数据透视表使用数据透视表创建数据透视表创建数据透视表编辑数据透视表编辑数据透视表controll toolbox usage (控件工具箱控件工具箱)课程课程(kchng)大纲大纲第1页/共69页第二页，共70页。算术运算符号算术运算符号运算符含义运算符含义示示 例例+(加号加号)加加2+3=5-(减号减号)减减3-1=2*(星号星号乘乘3*2=6/(斜杠斜杠)除除6/2=3%(百分号百分号)百分号百分号50%(脱字号脱字号)乘方乘方43=43=64第2页/共69页第三页，共70页。比较运算符比较运算符运算符含义运算符含义示示 例例=(等号等号)相等相等B1=C1,若

3、若B1中单元格内的值确实与中单元格内的值确实与C1中的值相中的值相等等,则产生逻辑真值则产生逻辑真值TRUE,若不相等若不相等,则产生逻辑则产生逻辑假值假值FALSE(小于号小于号)小于小于B1(大于号大于号)大于大于B1C1,若若B1中数值为中数值为6,C1中数值为中数值为4,则条件成则条件成立产生逻辑真值立产生逻辑真值TRUE,否则产生逻辑假值否则产生逻辑假值FALSE=(大于等于号大于等于号)大于等于大于等于B1=C1(不等号不等号)不等于不等于B1C1=(小于等于号小于等于号)小于等于小于等于B1=C1第3页/共69页第四页，共70页。引用运算符引用运算符运算符含义运算符含义示示 例例

4、:区域运算符区域运算符,产生对包产生对包括在丙相引用之间的括在丙相引用之间的所有单元格的引用所有单元格的引用(A5:A15),联合运算符联合运算符,将多个引将多个引用合并为一个引用用合并为一个引用SUM(A5:A15,C5:C15)(空格空格)交叉运算符交叉运算符,产生对两产生对两个引用构有的单元格个引用构有的单元格的引用的引用(B7:D7 C6:C8)例:A4=B4+C4+C4+E4+F4,可写为: A4=SUM(B4:F4)第4页/共69页第五页，共70页。第5页/共69页第六页，共70页。: $A4列变行不变:A$4第6页/共69页第七页，共70页。第7页/共69页第八页，共70页。第8

5、页/共69页第九页，共70页。逻辑值: FALSE :需要(xyo)完全一致 TRUE: 相似值亦可转换第9页/共69页第十页，共70页。语法语法作用作用SUM(number1,number2, )返回单元格区域中所有数值的和返回单元格区域中所有数值的和ISPMT(Rate,Per,Nper,Pv)返回普通返回普通(无提保无提保)的利息偿还的利息偿还AVERAGE(Number1,Number2, )计算参数的算术平均数计算参数的算术平均数,参数可以是数值或包含数值的参数可以是数值或包含数值的名称数组和引用名称数组和引用IF(Logical_test,Value_if_true,Value_i

6、f_false)执行真假判断执行真假判断,根据对指定条件进行逻辑评价的真假而根据对指定条件进行逻辑评价的真假而返回不同的结果。返回不同的结果。HYPERLINK(Link_location,Friendly_name) 创建快捷方式创建快捷方式,以便打开文档或网络驱动器或连接连接以便打开文档或网络驱动器或连接连接INTERNETCOUNT(Value1,Value2, )计算参数表中的数字参数和包含数字的单元格的个数计算参数表中的数字参数和包含数字的单元格的个数MAX(number1,number2, )返回一组数值中的最大值返回一组数值中的最大值,忽略逻辑值和文字符忽略逻辑值和文字符SIN

7、(number)返回纪念品定角度的正弦值返回纪念品定角度的正弦值SUMIF (Range, Criteria, Sum_range)根据指定条伯对若干单元格求和根据指定条伯对若干单元格求和PMT(Rate,Nper,Pv,Fv,Type)返回在固定利率下返回在固定利率下,投资或贷款的等额分期偿还额投资或贷款的等额分期偿还额第10页/共69页第十一页，共70页。注:Rate 贷款利率。Nper 该项贷款的付款总数。Pv 现值,或一系列未来付款的当前值的累积和,也称为本金。Fv 为未来值,或在最后一次付款后希望得到的现金余额,如果省略 fv则假设其值为零,也就是(jish)一笔贷款的未来值为零。T

8、ype 数字 0 或 1,用以指定各期的付款时间是在期初还是期末。第11页/共69页第十二页，共70页。主要用于分析和处理(chl)日期值和时间值,包括DATE、DATEVALUE、DAY、HOUR、TIME、TODAY、WEEKDAY 和 YEAR等。例：函数DATA语法：DATE(year,month,day)第12页/共69页第十三页，共70页。第13页/共69页第十四页，共70页。主要(zhyo)用于进行各种各样的计算,包括ABS、ASIN、COMBINE、COSLOG、PI、ROUND、SIN、TAN 和TUUNC等。例：ABS函数语法：ABS(number)第14页/共69页第十五

9、页，共70页。第15页/共69页第十六页，共70页。3.在上图的对话框中,选择函数分类下面的函数类型及函数名下面的对应函数名。4. 单击确定(qudng)按钮,弹出如图所示的相应的调用函数对话框：第16页/共69页第十七页，共70页。第17页/共69页第十八页，共70页。第18页/共69页第十九页，共70页。第19页/共69页第二十页，共70页。函数函数(hnsh)(hnsh)的使用的使用 (COUNTIF & SUMIF) (COUNTIF & SUMIF)SUMIFSUMIF函数函数函数名称：函数名称：SUMIFSUMIF主要功能主要功能(gngnng)(gngnng)：计算符合指定条件的

10、单元格区域内的数值和。：计算符合指定条件的单元格区域内的数值和。使用格式：使用格式：SUMIFSUMIF（Range,Criteria,Sum_RangeRange,Criteria,Sum_Range）参数说明：参数说明：RangeRange代表条件判断的单元格区域；代表条件判断的单元格区域；CriteriaCriteria为指定条件表达式；为指定条件表达式；Sum_RangeSum_Range代表需要计算的数值所在的单元格区域。代表需要计算的数值所在的单元格区域。第20页/共69页第二十一页，共70页。应用举例：如图应用举例：如图7 7所示所示, ,在在B64B64和和C64C64单元格中

11、分别输入公式：单元格中分别输入公式：=SUBTOTAL(3,C2:C63)=SUBTOTAL(3,C2:C63)和和=SUBTOTAL103,C2:C63),=SUBTOTAL103,C2:C63),并且将并且将6161行隐藏行隐藏起来起来, ,确认后确认后, ,前者显示为前者显示为6262（包括隐藏的行）（包括隐藏的行）, ,后者显示为后者显示为61,61,不不包括隐藏的行。包括隐藏的行。特别提醒：如果采取自动筛选特别提醒：如果采取自动筛选, ,无论无论function_numfunction_num参数选用什么参数选用什么类型类型,SUBTOTAL,SUBTOTAL函数忽略任何不包括在筛选

12、结果函数忽略任何不包括在筛选结果(ji gu)(ji gu)中的行；中的行；SUBTOTALSUBTOTAL函数适用于数据列或垂直区域函数适用于数据列或垂直区域, ,不适用于数据行或水平不适用于数据行或水平区域。区域。第21页/共69页第二十二页，共70页。第22页/共69页第二十三页，共70页。第23页/共69页第二十四页，共70页。TRUETRUE返回逻辑值返回逻辑值 TRUE TRUE。语法语法TRUE( )TRUE( )说明说明可以直接在单元格或公式可以直接在单元格或公式(gngsh)(gngsh)中键入值中键入值 TRUE TRUE，而可以不使用此函，而可以不使用此函数。函数数。函数

13、 TRUE TRUE 主要用于与其他电主要用于与其他电子表格程序兼容。子表格程序兼容。第24页/共69页第二十五页，共70页。第25页/共69页第二十六页，共70页。第26页/共69页第二十七页，共70页。第27页/共69页第二十八页，共70页。第28页/共69页第二十九页，共70页。第29页/共69页第三十页，共70页。第30页/共69页第三十一页，共70页。第31页/共69页第三十二页，共70页。第32页/共69页第三十三页，共70页。第二步：选定需要筛选的数据清单中任一单元格；第三步：选择菜单”Data / Filter / Advanced Filter”第四步：在“条件区域(qy)”

14、编辑框中，输入条件区域(qy)的引用，并包括条件标志。第五步：若要更改筛选数据的方式，可更改条件区域(qy)中的值，并再次筛选数据。注：如果需要将筛选结果放到另一位置，请选择”Copy to another Location”第33页/共69页第三十四页，共70页。请使用若要查找?（问号）任何单个字符例如，sm?th 查找“smith”和“smyth”*（星号）任何字符数例如，*east 查找“Northeast”和“Southeast”（波形符）后跟 ?、* 或 问号、星号或波形符例如，“fy91?”将会查找“fy91?”通配符通配符以下通配符可作为筛选以及以下通配符可作为筛选以及(yj)查

15、找和替换内容时的比较条件查找和替换内容时的比较条件 :第34页/共69页第三十五页，共70页。第35页/共69页第三十六页，共70页。要分类(fn li)汇总的列分类(fn li)汇总步骤步骤:第36页/共69页第三十七页，共70页。要分类(fn li)汇总的列分类(fn li)汇总第37页/共69页第三十八页，共70页。第38页/共69页第三十九页，共70页。第39页/共69页第四十页，共70页。数据透视表是交互式报表，可快速合并和比较(bjio)大量数据。您可旋转其行和列以看到源数据的不同汇总，而且可显示感兴趣区域的明细数据。 源数据第三季度高尔夫汇总的源值数据透视表C2 和 C8 中源值

16、的汇总第40页/共69页第四十一页，共70页。何时应使用数据透视表何时应使用数据透视表如果要分析相关的汇总值，尤其是在要合计较大的列表并对每个数字进行多种比较时，可以使用数据透视表。在上面所述报表中，用户可以很清楚地看到单元格如果要分析相关的汇总值，尤其是在要合计较大的列表并对每个数字进行多种比较时，可以使用数据透视表。在上面所述报表中，用户可以很清楚地看到单元格 F3 中第三季度高尔夫销售额是如何通过其他运动或季度的销售额或总销售额计算中第三季度高尔夫销售额是如何通过其他运动或季度的销售额或总销售额计算(j sun)出来的。由于数据透视表是交互式的，因此，您可以更改数据的视图以查看更多明细数

17、据或计算出来的。由于数据透视表是交互式的，因此，您可以更改数据的视图以查看更多明细数据或计算(j sun)不同的汇总额，如计数或平均值。不同的汇总额，如计数或平均值。 数据是如何组织的数据是如何组织的在数据透视表中，源数据中的每列或字段都成为汇总多行信息的数据透视表字段。在上例中，在数据透视表中，源数据中的每列或字段都成为汇总多行信息的数据透视表字段。在上例中，“运动运动”列成为列成为“运动运动”字段，高尔夫的每条记录在单个高尔夫项中进行汇总。字段，高尔夫的每条记录在单个高尔夫项中进行汇总。数据字段（如数据字段（如“求和项：销售额求和项：销售额”）提供要汇总的值。上述报表中的单元格）提供要汇总

18、的值。上述报表中的单元格 F3 包含的包含的“求和项：销售额求和项：销售额”值来自源数据中值来自源数据中“运动运动”列包含列包含“高尔夫高尔夫”和和“季度季度”列包含列包含“第三季度第三季度”的每一行。的每一行。第41页/共69页第四十二页，共70页。第42页/共69页第四十三页，共70页。第43页/共69页第四十四页，共70页。第44页/共69页第四十五页，共70页。第45页/共69页第四十六页，共70页。创建图表图表是Excel为用户提供的强大功能,通过创建各种不同类型的图表,为分析工作表中的各种数据提供更直观的表示结果。使用图表向导 下面我们先在工作薄中建立数据表如下页图所示,根据工作表

19、中的数据,创建不同类型的图表。图表的编辑 1.插入(ch r)数据标志及增加图表标题 2.改变图表的文字、颜色和图案第46页/共69页第四十七页，共70页。第47页/共69页第四十八页，共70页。操作步骤如下： 1)选定工作表中包含所需数据的所有(suyu)单元格。 2)单击常用工具栏中的图表向导按钮 ,弹出图（1-16）所示的对话框： 图表图表(tbio)的使用的使用第48页/共69页第四十九页，共70页。第49页/共69页第五十页，共70页。图表图表(tbio)(tbio)的使用（续的使用（续2 2）4）然后继续(jx)单击下一步弹出图对话框 选择图表(tbio)存放的位置,若要将图表(t

20、bio)放到另一个新的工作表上,请选中As new sheet单选框,然后作为新工作表插入文本框,键入新工作表的名字,默认为,如果选择As object in单选框,可将： 第50页/共69页第五十一页，共70页。第51页/共69页第五十二页，共70页。图表(tbio)标题分类X轴数值Y轴第52页/共69页第五十三页，共70页。第53页/共69页第五十四页，共70页。数据标志数据标志说明说明category name category name 显示指定到数据点的分类显示指定到数据点的分类valuevalue显示数据点的值显示数据点的值percentage percentage 对于饼图、圆环

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

22、,可将鼠标移至图例(tl)区双击鼠标,则出现如图（1-24）对话框。在此对话框中可对图例(tl)进行编辑,如图案、字体及位置。第57页/共69页第五十八页，共70页。复选框复选框 可通过选中或清除来打开可通过选中或清除来打开(d ki)或关闭的选项。您可以在一个工作表中同时选中多个复或关闭的选项。您可以在一个工作表中同时选中多个复选框。选框。 文本框文本框 可以向其中键入文字的框。可以向其中键入文字的框。命令按钮命令按钮 单击可启动某项操作的按钮。单击可启动某项操作的按钮。选项按钮选项按钮 用于从一组选项中选择某一选项的按钮。用于从一组选项中选择某一选项的按钮。列表框列表框 包含项目列表的框。

23、包含项目列表的框。组合框组合框 含有下拉列表框的文本框。您可以从列表中选择含有下拉列表框的文本框。您可以从列表中选择,或在框中键入所需内容。或在框中键入所需内容。切换按钮切换按钮 单击后保持按下状态单击后保持按下状态,再单击时又弹起的按钮。再单击时又弹起的按钮。微调按钮微调按钮 可附加在单元格上或文本框上的按钮。若要增加数值可附加在单元格上或文本框上的按钮。若要增加数值,请单击向上箭头；若要减小数值请单击向上箭头；若要减小数值,请单击向下箭头。请单击向下箭头。滚动条滚动条 可通过单击滚动箭头或拖动滚动块来滚动数据区域的一种控件。单击滚动箭头与滚动块可通过单击滚动箭头或拖动滚动块来滚动数据区域的

24、一种控件。单击滚动箭头与滚动块之间的区域时之间的区域时,可以滚动整页数据。可以滚动整页数据。标签标签 添加到工作表或窗体中添加到工作表或窗体中,用以提供有关控件、工作表或窗体信息的文本。用以提供有关控件、工作表或窗体信息的文本。图像图像 将图片嵌入到窗体中的控件。将图片嵌入到窗体中的控件。其他控件其他其他控件其他 ActiveX 控件的列表。控件的列表。第58页/共69页第五十九页，共70页。关于宏关于宏如果经常如果经常(jngchng)在在 Microsoft Excel 中重复某项任务中重复某项任务,那么可以用宏自动执行该任务。宏是一系列命令和函数那么可以用宏自动执行该任务。宏是一系列命令

25、和函数,存储于存储于 Visual Basic 模块中模块中,并且在需要执行该项任务时可随时运行。并且在需要执行该项任务时可随时运行。例如例如,如果经常如果经常(jngchng)在单元格中输入长文本字符串在单元格中输入长文本字符串,则可以创建一个宏来将单元格格式设置为文本可自动换行。则可以创建一个宏来将单元格格式设置为文本可自动换行。录制宏录制宏 在录制宏时在录制宏时,Excel 在您执行一系列命令时存储该过程的每一步信息。然后即可运行宏来重复所录制的过程或在您执行一系列命令时存储该过程的每一步信息。然后即可运行宏来重复所录制的过程或“回放回放”这些命令。如果在录制宏时出错这些命令。如果在录制

26、宏时出错,所做的修改也会被录制下来。所做的修改也会被录制下来。Visual Basic 在附属于某工作薄的新模块中存储每个宏。在附属于某工作薄的新模块中存储每个宏。第59页/共69页第六十页，共70页。使宏易于运行使宏易于运行 可以在可以在“宏宏”对话框的列表中选择所需的宏并运行宏。如果希望通过单击特定按钮或按下特定组合键来运行宏对话框的列表中选择所需的宏并运行宏。如果希望通过单击特定按钮或按下特定组合键来运行宏,可将宏指定给某个工具栏按钮、键盘可将宏指定给某个工具栏按钮、键盘(jinpn)快捷键或工作表中的图形对象。快捷键或工作表中的图形对象。管理宏管理宏 宏录制完后宏录制完后,可用可用 V

27、isual Basic 编辑器查看宏代码以进行改错或更改宏的功能。例如编辑器查看宏代码以进行改错或更改宏的功能。例如,如果希望用于文本换行的宏还可以将文本变为粗体如果希望用于文本换行的宏还可以将文本变为粗体,则可以再录制另一个将单元格文本变为粗体的宏则可以再录制另一个将单元格文本变为粗体的宏,然后将其中的指令复制到用于文本换行的宏中。然后将其中的指令复制到用于文本换行的宏中。“Visual Basic 编辑器编辑器”是一个为初学者设计的编写和编辑宏代码的程序是一个为初学者设计的编写和编辑宏代码的程序,而且提供了很多联机帮助。不必学习如何编程或如何用而且提供了很多联机帮助。不必学习如何编程或如何

28、用 Visual Basic 语言来对宏进行简单的修改。利用语言来对宏进行简单的修改。利用“Visual Basic 编辑器编辑器”,您可以编辑宏、在模块间复制宏、在不同工作簿之间复制宏、重命名存储宏的模块或重命名宏。您可以编辑宏、在模块间复制宏、在不同工作簿之间复制宏、重命名存储宏的模块或重命名宏。第60页/共69页第六十一页，共70页。宏安全性 Excel 对可通过宏传播的病毒提供安全保护。如果您与其他人共享宏,则可使用数字签名来验证(ynzhng)其他用户,这样就可保证其他用户为可靠来源。无论何时打开包含宏的工作簿,都可以先验证(ynzhng)宏的来源再启用宏。第61页/共69页第六十二

29、页，共70页。创建宏创建宏 录制宏录制宏将安全级设置为将安全级设置为“中中”或或“低低”。 操作方法操作方法在在“工具工具”菜单上菜单上,单击单击“选项选项”。 单击单击“安全性安全性”选项卡。选项卡。 在在“宏安全性宏安全性”之下之下,单击单击“宏安全性宏安全性”。 单击单击“安全级安全级”选项卡选项卡,再选择所要使用的安全再选择所要使用的安全级。级。 在在“工具工具”菜单上菜单上,指向指向“宏宏”,再单击再单击“录制新宏录制新宏”。 在在“宏名宏名”框中框中,输入宏输入宏 的名称。的名称。 注意注意宏名的首字符必须是字母宏名的首字符必须是字母,其他字符可以是字母、数字或下划线。宏名中不允许

30、其他字符可以是字母、数字或下划线。宏名中不允许(ynx)有有空格；可用下划线作为分词符。空格；可用下划线作为分词符。宏名不允许宏名不允许(ynx)与单元格引用重名与单元格引用重名,否则会出现错误信息显示宏名无效。否则会出现错误信息显示宏名无效。如果要通过按键盘上的来运行宏如果要通过按键盘上的来运行宏, 请在请在“快捷键快捷键”框中框中,输入一个字母。可用输入一个字母。可用 Ctrl+字母（字母（小写字母）或小写字母）或 Ctrl+Shift+字母（大写字母）字母（大写字母）,其中字母可以是键盘上的任意字母键。其中字母可以是键盘上的任意字母键。快捷键字母不允许快捷键字母不允许(ynx)是数字或特

31、殊字符（如是数字或特殊字符（如 或或 #）。）。 注释注释 当包含宏的工作簿打开时当包含宏的工作簿打开时,宏快捷键优先于任何相当的宏快捷键优先于任何相当的 Microsoft Excel 的默认快捷的默认快捷键。键。第62页/共69页第六十三页，共70页。在“保存在”框中,单击要存放宏的地址。 如果要使宏在使用 Excel 的任何时候都可用,请选中“个人宏工作簿”。如果要添加有关宏的说明,请在“说明”框中键入该说明。单击“确定”。 如果要使宏相对于活动单元格位置运行,请用相对单元格引用来录制(l zh)该宏。在“停止录制(l zh)”工具栏上,单击“相对引用” 以将其选中。Excel 将继续用

32、“相对引用”录制(l zh)宏,直至退出 Excel 或再次单击“相对引用” 以将其取消。执行需要录制(l zh)的操作。 在“停止录制(l zh)”工具栏上,单击“停止录制(l zh)” 。 用 Microsoft Visual Basic 创建宏在 Microsoft Excel 的“工具”菜单上,指向“宏”,再单击“Visual Basic 编辑器”。 在“插入”菜单上,单击“模块”。将代码键入或复制到模块的代码窗口中。 如果要在模块窗口中运行宏 请按 F5。 编写完宏后,请单击“文件”菜单上的“关闭并返回到 Microsoft Excel”。第63页/共69页第六十四页，共70页。 创

33、建启动宏自动例如：Auto_Activate）是在启动 Excel 时自动运行的。有关自动宏的详细信息,请参阅 Visual Basic“帮助” 复制宏的一部分以创建另一个宏将安全级设置为“中”或“低”。 请打开(d ki)要复制的宏所在的工作簿。 在“工具”菜单上,指向“宏”,再单击“宏”。 在“宏名”框中,输入要复制的宏的名称。 单击“编辑”。 在宏中选取要复制的程序行。 若要复制整个宏,请确认在选定区域中包括了“Sub”和“End Sub”行。在“常用”工具栏 上,单击“复制” 。 切换到要放置代码的模块。 单击“粘贴” 。 提示您可在任何时候通过在 Visual Basic 编辑器 (

34、Alt+F11) 中打开(d ki)个人宏工作簿文件 (Personal.xls) 来对其进行查看。由于 Personal.xls 是一个总是打开(d ki)着的隐藏工作簿,所以如果要复制其中的某个宏,必须先显示该工作簿。第64页/共69页第六十五页，共70页。附加内容附加内容宏的录制宏的录制(l zh)与运用与运用第65页/共69页第六十六页，共70页。附加附加(fji)内容内容宏的录制与运用宏的录制与运用第66页/共69页第六十七页，共70页。第67页/共69页第六十八页，共70页。第68页/共69页第六十九页，共70页。感谢您的观看感谢您的观看(gunkn)。第69页/共69页第七十页，共70页。