ECEL基础培训讲义

ECEL基础培训讲义基础培训讲义课程目的课程目的1、常见操作及技巧函数使用2、数据管理与分类3、外部数据导入4、数据保护5、宏和VBA简介绪言Excel用途数据分析列出数据对数据执行计算对数据点进展比较展示数据突出数据点之间关系的方式熟悉Excel程序窗口三要素工作薄、工作表、单元格标题栏菜单栏状态栏Excel三要素【名称名称】框框插入函数插入函数程序和工作薄标题栏程序和工作薄标题栏【编辑编辑】栏栏程序窗口按钮程序窗口按钮菜单栏菜单栏【常用常用/格式格式】 工具栏工具栏工作薄窗口工作薄窗口状态栏状态栏任务窗格任务窗格讲解内容F 常见操作及技巧常见操作及技巧F 数据管理与分类函数使用数据管理与分类函数使用F 外部数据导入外部数据导入F 数据保护数据保护F 宏和宏和VBAVBA简介简介第一局部第一局部常见操作及技巧 输入技巧 工作薄表操作技巧 使用函数 数据有效性输入技巧快速输入一样内容选中输入CtrlEnter如何实现快速输入较多重复内容工具自动更正键入时更换如何输入超过11位的数字在输入数字时在前面加上一个单引号“ 如何在单元格中硬换行AltEnter自动填充公式工具选项编辑设置扩展数据列表格式及公式工作簿与工作表模板工作簿模板为特定的任务和工程创立自定义的工作簿模板工作簿模板的存储位置工作表模板在工作簿中创立特定的工作表工作表模板的存储位置 C:Program FilesMicrosoft OfficeOffice10XLStart 设置Excel模板的细节文本数据图形公式格式的设置单元格和工作表格式页面格式打印区域单元格样式工作簿中所含工作表的数量和类型隐藏和保护Excel模板与窗体窗体的创立窗体和窗体控件窗体的保护使用窗体简化工作案例演示房屋按揭计算表进销存管理表差旅费明细表费用报告表个人所得税计算表工作薄表操作技巧共享工作薄“工具共享工作簿命令，接着在出现的“共享工作簿对话框中，选择“编辑选项卡，然后选中“允许多用户同时编辑.选项并单击确定按钮发送工作薄多个工作表之间切换按下“Ctrl“TAB 在翻开的工作薄间切换按下“Ctrl“Page Down 可切换到工作薄的下一 个工作表。按下“Ctrl“Page Up 可切换到工作薄的上一 个 工作表。Excel函数及应用函数及应用 一些常用的工作表函数SUM 求和MIN&MAX 在所选区域中找到最小大值AVERAGE 取所选趋于中值的平均值CUUNTIF 对满足条件的所有值进展计数SUMIF 对满足条件的所有值进展相加VLOOKUP&HLOOKUP 在表中查找一个值PMT 计算特定贷款项的支付值NOW 以一个数字返回当前时间或日期TODAY 返回当前日期CONCATENATE 在单一单元格中将所有单元格值连接起来LEFT&RIGHT 从单元格值的最左右边返回一个字符 的特定数字投资计算函数函数名称 函 数功 能 EFFECT 计算实际年利息率计算实际年利息率 FV 计算投资的未来值计算投资的未来值 FVSCHEDULE 计算原始本金经一系列复利率计算之后的未来值计算原始本金经一系列复利率计算之后的未来值 IPMT 计算某投资在给定期间内的支付利息计算某投资在给定期间内的支付利息 NOMINAL 计算名义年利率计算名义年利率 NPER 计算投资的周期数计算投资的周期数 NPV 在已知定期现金流量和贴现率的条件下计算某项投资的净现值在已知定期现金流量和贴现率的条件下计算某项投资的净现值 PMT 计算某项年金每期支付金额计算某项年金每期支付金额 PPMT 计算某项投资在给定期间里应支付的本金金额计算某项投资在给定期间里应支付的本金金额 PV 计算某项投资的净现值计算某项投资的净现值 XIRR 计算某一组不定期现金流量的内部报酬率计算某一组不定期现金流量的内部报酬率 XNPV 计算某一组不定期现金流量的净现值计算某一组不定期现金流量的净现值 债券及其他金融函数函数名称 函 数功 能 ACCRINTM 计算到期付息证券的应计利息计算到期付息证券的应计利息 COUPDAYB 计算从付息期间开始到结算日期的天数计算从付息期间开始到结算日期的天数 COUPDAYS 计算包括结算日期的付息期间的天数计算包括结算日期的付息期间的天数 COUPDAYSNC 计算从结算日期到下一个付息日期的天数计算从结算日期到下一个付息日期的天数 COUPNCD 计算结算日期后的下一个付息日期计算结算日期后的下一个付息日期 COUPNUM 计算从结算日期至到期日期之间的可支付息票数计算从结算日期至到期日期之间的可支付息票数 COUPPCD 计算结算日期前的上一个付息日期计算结算日期前的上一个付息日期 CUMIPMT 计算两期之间所支付的累计利息计算两期之间所支付的累计利息 CUMPRINC 计算两期之间偿还的累计本金计算两期之间偿还的累计本金 DISC 计算证券的贴现率计算证券的贴现率 DOLLARDE 转换分数形式表示的货币为十进制表示的数值转换分数形式表示的货币为十进制表示的数值 DOLLARFR 转换十进制形式表示的货币分数表示的数值转换十进制形式表示的货币分数表示的数值 数据有效性数据有效性定义数据验证定义有效数据范围 设置输入信息 设置出错警告 设置自定义类型的有效数据 圈释无效数据 检查数据的有效性建立验证数据内容，即设置“有效数据 “有效数据是用于规定某个单元格或单元格区域中的数据是否正确和能否满足要求的规那么，并为不满足规那么的数据设置报告的方法 对一些重要的数据最好为它规定有效性规那么 小技巧实现下拉菜单数据有效性序列如何将公式结果转换为数值编辑选择性粘贴“数值行列互换如何将数据合并、分列第二局部第二局部数据管理与分类 自动筛选和高级筛选 分类汇总 数据透视表 自动筛选和高级筛选使复杂表格简单化自动筛选筛选不重复记录数据筛选高级筛选有效的展示数据组及分级显示数据的排序数据排序的规定按一列排序按多列排序自定义排序的方法对数据进展分类汇总创立简单的分类汇总 单列自动分类汇总多列自动分类汇总 创立多级分类汇总 分级显示数据 去除分类汇总 数据透视图在现有数据透视表的根底上创立数据透视图直接为工作表创立数据透视图调整与修改数据透视图 数据透视图的调整修改数据透视图类型组织图目标图循环图棱锥图射线图维恩图第三局部第三局部数据的导入 导入文本文件数据 在工作表中导入Word表格 在工作表中导入Access文件 导入HTML文件中的表格数据 从较简单的导入文本文件数据，导入Word文档数据，导入Access数据库数据到较复杂的导入HTML文件中的表格数据，再到复杂的导入OLAP多维数据等方法，可以说，Excel 2002可以使用绝大多数不同格式的数据，使处理数据方便快捷从文本文件中获取数据从文本文件中获取数据 选择“文件菜单中的“翻开命令，在“文件类型下拉表中选择“文本文件即可 执行“数据菜单中“获取外部数据子菜单上的“导入文本文件命令。从Web导入表格数据数据导入方法在一个新的Excel文件中，选择菜单栏的“数据导入外部数据新建Web查询，翻开“新建Web查询窗口，图中可以看到但凡有表格的地方其左上角都会出现一个黑色的小箭头，只要单击就可以使其变成小勾，表示该箭头指的表格将被导入Excel中。全部选定后可以先通过“保存查询按钮将该Web查询单独保存，这样可在以后其他工作簿中重复调用。现在单击“导入按钮，选择保存在当前工作表中，还是新建一个工作表。当需要查询数据时，只要翻开Excel文件，Excel会自动翻开一个“查询刷新对话框，选定“启用自动刷新，Excel会自动连通网站对所有表格数据进展更新，您需要的更新数据就出现了。您也可以通过菜单中的“数据更新数据命令进展更新。 第四局部第四局部数据的保护 单元格的保护 工作表的保护 工作簿的保护保护特殊单元格取消整个工作表锁定特殊单元格设定锁定对所在工作表进展保护设置取消保护密码第五局部第五局部宏和VBA简介 宏及宏的平安性 VBA简介什么是宏 宏是一段定义好的操作，它可以是一段程序代码，也可以是一连串的指令集合。宏的作用可以使频繁执行的动作自动化 节省时间，提高工作效率，又能减少失误 宏的构造Sub 主界面主界面() 主界面主界面 Macro Coren Lee 记录的宏记录的宏 2002-11-5 Sheets(主界面主界面).SelectEnd Sub 小技巧Excel中用自动运行宏提高工作效率 在日常工作中，我们经常需要在每次翻开同一个Excel文件时都进展一些例行的操作，如改变表格的格式、更新报表日期、打印文件、对工作表进展保护或取消保护等等。Excel的自动运行宏“AutoOpen可在文件翻开后立即完成这些例行的操作任务，既快速又准确。 认识VBA VBA是一个通用程序语言，可共享Microsoft各种相关的重要软件。 以VBA编写的程序允许用户将其复制到Visual Basic中加以调试，用Visual Basic宏来控制Excel。 当记录宏不能满足用户需要或用记录宏无法记录命令时，可以用VBA语言创造宏来控制工作簿的各项操作。 创立用户对话框、工具按钮或自定义命令，把Excel提供的多个工具按钮连接起来，作为一个工具按钮。 利用重复语句控制循环宏内操作，利用不同参数执行某个相关功能模块，使宏指令完成一系列复杂操作。来连接多个宏。 VBA提供了许多内部函数，还允许用户自定义函数来简化对工作簿、工作表、图表等复杂操作。Visual Basic编辑器用来编辑宏程序或制作更高阶的整合性应用程序附属于Office之下，使用较少的系统资源，到达编辑应用程序的目的。例如：在Excel中打印工资表Sub InsertTitle() Cells(Selection.Row, Selection.Column).Select Range(Selection, Selection.End(xlToRight).Select ActiveCell.Offset(2, 0).Range(A1).Select Do Until ActiveCell = Selection.Insert Shift:=xlDown Range(Selection, Selection.End(xlToRight).Select ActiveCell.Offset(2, 0).Range(A1).Select Loop Application.CutCopyMode = False End Sub例如：保存并关闭系统Private Sub Workbook_BeforeClose(Cancel As Boolean) On Error Resume Next Dim abc abc = MsgBox(您确认要关闭本系统吗？, vbQuestion + vbYesNo + vbDefaultButton2, 确认) If abc = vbYes Then Worksheets(股票收益计算器).Unprotect Password:=1 Worksheets(股票收益计算器).Range(G13).FormulaR1C1 = 0 Worksheets(股票收益计算器).Protect DrawingObjects:=True, Contents:=True, Scenarios:=True, Password:=1 Sheets(说明).Select Else Cancel = True End If End SubEND谢谢大家！