EXCEL基础培训讲义.ppt

此课程归员工培训中心所有，任何个人不得占为己有，未经许可不得向外传播，违者必究EXCEL基础知识培训基础知识培训员工培训中心2004年11月15日NE002003b V1.1此课程归员工培训中心所有，任何个人不得占为己有，未经许可不得向外传播，违者必究课程目的课程目的1、常常见见操作及技巧（函数使用）操作及技巧（函数使用）2、数据管理与分数据管理与分类类3、外部数据外部数据导导入入4、数据保数据保护护5、宏和宏和VBAVBA简简介介此课程归员工培训中心所有，任何个人不得占为己有，未经许可不得向外传播，违者必究绪言Excel用途（数据分析）列出数据对数据执行计算对数据点进行比较展示数据（突出数据点之间关系的方式）熟悉Excel程序窗口三要素（工作薄、工作表、单元格）标题栏菜单栏状态栏此课程归员工培训中心所有，任何个人不得占为己有，未经许可不得向外传播，违者必究Excel三要素工作簿工作簿工作簿工作簿工作表工作表工作表工作表单元格和单元格和单元格和单元格和区域区域区域区域此课程归员工培训中心所有，任何个人不得占为己有，未经许可不得向外传播，违者必究【名称名称】框框插入函数插入函数程序和工作薄标题栏程序和工作薄标题栏【编辑编辑】栏栏程序窗口按钮程序窗口按钮菜单栏菜单栏【常用常用/格式格式】工具栏工具栏工作薄窗口工作薄窗口状态栏状态栏任任务务窗窗格格此课程归员工培训中心所有，任何个人不得占为己有，未经许可不得向外传播，违者必究讲解内容F常常见见操作及技巧操作及技巧F数据管理与分数据管理与分类类（函数使用）（函数使用）F外部数据外部数据导导入入F数据保数据保护护F宏和宏和VBAVBA简简介介此课程归员工培训中心所有，任何个人不得占为己有，未经许可不得向外传播，违者必究第一部分第一部分常见操作及技巧输入技巧工作薄（表）操作技巧使用函数数据有效性此课程归员工培训中心所有，任何个人不得占为己有，未经许可不得向外传播，违者必究输入技巧快速输入相同内容选中输入CtrlEnter如何实现快速输入较多重复内容工具自动更正键入时更换如何输入超过11位的数字在输入数字时在前面加上一个单引号“”如何在单元格中硬换行AltEnter自动填充公式工具选项编辑（设置）扩展数据列表格式及公式此课程归员工培训中心所有，任何个人不得占为己有，未经许可不得向外传播，违者必究工作簿与工作表模板工作簿模板为特定的任务和项目创建自定义的工作簿模板工作簿默认的模板名为book.xlt工作簿模板的存储位置工作表模板在工作簿中创建特定的工作表工作表默认的模板名为sheet.xlt工作表模板的存储位置C:ProgramFilesMicrosoftOfficeOffice10XLStart此课程归员工培训中心所有，任何个人不得占为己有，未经许可不得向外传播，违者必究轻轻松完成松完成规规范化的范化的报报表表Office Office Office Office XPXPXPXP提提提提供供供供的的的的模模模模板板板板功功功功能能能能，让让让让您您您您轻轻轻轻松松松松完完完完成成成成规规规规范化的公文范化的公文范化的公文范化的公文模板和模板和模板和模板和MM上的模板上的模板上的模板上的模板 模模模模板板板板库库库库还还还还有有有有这这这这么么么么多多多多类类类类型型型型的的的的模模模模板板板板可可可可供供供供我我我我们们们们选选选选择择择择，而而而而且且且且微微微微软软软软公公公公司司司司随随随随时时时时更更更更新新新新内容以内容以内容以内容以满满满满足广大用足广大用足广大用足广大用户户户户的需求的需求的需求的需求 。此课程归员工培训中心所有，任何个人不得占为己有，未经许可不得向外传播，违者必究设置Excel模板的细节文本数据图形公式此课程归员工培训中心所有，任何个人不得占为己有，未经许可不得向外传播，违者必究格式的设置单元格和工作表格式页面格式打印区域单元格样式工作簿中所含工作表的数量和类型隐藏和保护此课程归员工培训中心所有，任何个人不得占为己有，未经许可不得向外传播，违者必究Excel模板与窗体窗体的创建窗体和窗体控件窗体的保护使用窗体简化工作此课程归员工培训中心所有，任何个人不得占为己有，未经许可不得向外传播，违者必究案例演示房屋按揭计算表进销存管理表差旅费明细表费用报告表个人所得税计算表此课程归员工培训中心所有，任何个人不得占为己有，未经许可不得向外传播，违者必究工作薄（表）操作技巧共享工作薄“工具共享工作簿”命令，接着在出现的“共享工作簿”对话框中，选择“编辑”选项卡，然后选中“允许多用户同时编辑.”选项并单击确定按钮发送工作薄多个工作表之间切换按下“Ctrl”“TAB”在打开的工作薄间切换按下“Ctrl”“PageDown”可切换到工作薄的下一个工作表。按下“Ctrl”“PageUp”可切换到工作薄的上一个工作表。此课程归员工培训中心所有，任何个人不得占为己有，未经许可不得向外传播，违者必究Office XPOffice XP模板设计与应用模板设计与应用模板设计与应用模板设计与应用自动完成繁琐重复的操作，使工作自动化自动完成繁琐重复的操作，使工作自动化自动完成繁琐重复的操作，使工作自动化自动完成繁琐重复的操作，使工作自动化 存储存储存储存储ExcelExcel模板模板模板模板 ExcelExcel模板与窗体的应用模板与窗体的应用模板与窗体的应用模板与窗体的应用此课程归员工培训中心所有，任何个人不得占为己有，未经许可不得向外传播，违者必究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 转换十进制形式表示的货币分数表示的数值转换十进制形式表示的货币分数表示的数值 此课程归员工培训中心所有，任何个人不得占为己有，未经许可不得向外传播，违者必究Office ExcelOffice Excel函数与应用函数与应用函数与应用函数与应用利用自身集成的功能处理庞大而复杂的计算利用自身集成的功能处理庞大而复杂的计算利用自身集成的功能处理庞大而复杂的计算利用自身集成的功能处理庞大而复杂的计算 调用函数格式调用函数格式调用函数格式调用函数格式 函数在实际中的应用函数在实际中的应用函数在实际中的应用函数在实际中的应用此课程归员工培训中心所有，任何个人不得占为己有，未经许可不得向外传播，违者必究数据有效性数据有效性定义数据验证定义有效数据范围设置输入信息设置出错警告设置自定义类型的有效数据圈释无效数据检查数据的有效性建立验证数据内容，即设置“有效数据”“有效数据”是用于规定某个单元格或单元格区域中的数据是否正确和能否满足要求的规则，并为不满足规则的数据设置报告的方法对一些重要的数据最好为它规定有效性规则此课程归员工培训中心所有，任何个人不得占为己有，未经许可不得向外传播，违者必究小技巧实现下拉菜单数据有效性序列如何将公式结果转换为数值编辑选择性粘贴“数值”行列互换如何将数据合并、分列此课程归员工培训中心所有，任何个人不得占为己有，未经许可不得向外传播，违者必究第二部分第二部分数据管理与分类自动筛选和高级筛选分类汇总数据透视表此课程归员工培训中心所有，任何个人不得占为己有，未经许可不得向外传播，违者必究自动筛选和高级筛选使复杂表格简单化自动筛选筛选不重复记录数据筛选高级筛选有效的展示数据组及分级显示此课程归员工培训中心所有，任何个人不得占为己有，未经许可不得向外传播，违者必究数据的排序数据排序的规定按一列排序按多列排序自定义排序的方法此课程归员工培训中心所有，任何个人不得占为己有，未经许可不得向外传播，违者必究对数据进行分类汇总创建简单的分类汇总单列自动分类汇总多列自动分类汇总创建多级分类汇总分级显示数据清除分类汇总此课程归员工培训中心所有，任何个人不得占为己有，未经许可不得向外传播，违者必究数据透数据透数据透数据透视视视视表表表表数数据据透透视视表表是是一一种种对对大大量量数数据据快快速速汇汇总总和和建建立立交交叉叉列列表表的的交交互互式式表表格格。可可以以转转换换行行和和列列以以查查看看源源数数据据的的不不同同汇汇总总结结果果，可可以以显显示示不不同同页页面面以以筛筛选选数数据据，还还可可以以根根据据需需要要显显示示区区域域中中的的明明细细数据。数据。轻轻易易发挥发挥个人生个人生产产力力准确提供信息决策准确提供信息决策准确提供信息决策准确提供信息决策此课程归员工培训中心所有，任何个人不得占为己有，未经许可不得向外传播，违者必究数据透视图在现有数据透视表的基础上创建数据透视图直接为工作表创建数据透视图调整与修改数据透视图数据透视图的调整修改数据透视图类型此课程归员工培训中心所有，任何个人不得占为己有，未经许可不得向外传播，违者必究组织图目标图循环图棱锥图射线图维恩图轻轻易易发挥发挥个人生个人生产产力力轻轻轻轻易表达您的想法易表达您的想法易表达您的想法易表达您的想法提供更多的工具，提供更多的工具，提供更多的工具，提供更多的工具，让让让让您表达想要的想法您表达想要的想法您表达想要的想法您表达想要的想法:数据数据数据数据库图库图库图库图表表表表全全全全新新新新的的的的Office Office XP XP 加加加加入入入入的的的的数数数数据据据据库库库库图图图图表表表表功功功功能能能能更更更更方方方方便便便便我我我我们们们们制制制制作作作作像像像像是是是是：组组组组织织织织图图图图、目目目目标标标标图图图图、循循循循环环环环图图图图、棱棱棱棱锥锥锥锥图图图图、射射射射线线线线图图图图、维维维维恩恩恩恩图图图图等等等等等等等等，有有有有了了了了这这这这项项项项功功功功能能能能的的的的帮帮帮帮助助助助，相相相相信信信信我我我我们们们们在在在在传传传传达达达达讯讯讯讯息息息息上上上上将将将将会会会会更更更更加加加加容容容容易易易易 此课程归员工培训中心所有，任何个人不得占为己有，未经许可不得向外传播，违者必究第三部分第三部分数据的导入导入文本文件数据在工作表中导入Word表格在工作表中导入Access文件导入HTML文件中的表格数据从较简单的导入文本文件数据，导入Word文档数据，导入Access数据库数据到较复杂的导入HTML文件中的表格数据，再到复杂的导入OLAP多维数据等方法，可以说，Excel2002可以使用绝大多数不同格式的数据，使处理数据方便快捷此课程归员工培训中心所有，任何个人不得占为己有，未经许可不得向外传播，违者必究从文本文件中获取数据从文本文件中获取数据选择“文件”菜单中的“打开命令，在“文件类型”下拉表中选择“文本文件”即可执行“数据”菜单中“获取外部数据”子菜单上的“导入文本文件”命令。此课程归员工培训中心所有，任何个人不得占为己有，未经许可不得向外传播，违者必究从Web导入表格数据数据导入方法在一个新的Excel文件中，选择菜单栏的“数据导入外部数据新建Web查询”，打开“新建Web查询”窗口，图中可以看到凡是有表格的地方其左上角都会出现一个黑色的小箭头，只要单击就可以使其变成小勾，表示该箭头指的表格将被导入Excel中。全部选定后可以先通过“保存查询”按钮将该Web查询单独保存，这样可在以后其他工作簿中重复调用。现在单击“导入”按钮，选择保存在当前工作表中，还是新建一个工作表。当需要查询数据时，只要打开Excel文件，Excel会自动打开一个“查询刷新”对话框，选定“启用自动刷新”，Excel会自动连通网站对所有表格数据进行更新，您需要的更新数据就出现了。您也可以通过菜单中的“数据更新数据”命令进行更新。此课程归员工培训中心所有，任何个人不得占为己有，未经许可不得向外传播，违者必究第四部分第四部分数据的保护单元格的保护工作表的保护工作簿的保护此课程归员工培训中心所有，任何个人不得占为己有，未经许可不得向外传播，违者必究坚坚如磐石的安全性如磐石的安全性设设设设置文档保置文档保置文档保置文档保护护护护为了防止文件被他人更改，为了防止文件被他人更改，为了防止文件被他人更改，为了防止文件被他人更改，我们可以使用我们可以使用我们可以使用我们可以使用Office XPOffice XP提供提供提供提供的的的的“文件保护文件保护文件保护文件保护”功能功能功能功能 文件打开保护文件打开保护文件打开保护文件打开保护 文件修改保护文件修改保护文件修改保护文件修改保护 建议只读方式保护建议只读方式保护建议只读方式保护建议只读方式保护此课程归员工培训中心所有，任何个人不得占为己有，未经许可不得向外传播，违者必究保护特殊单元格取消整个工作表锁定特殊单元格设定锁定对所在工作表进行保护设置取消保护密码坚坚如磐石的安全性如磐石的安全性单单单单元格保元格保元格保元格保护护护护此课程归员工培训中心所有，任何个人不得占为己有，未经许可不得向外传播，违者必究第五部分第五部分宏和VBA简介宏及宏的安全性VBA简介此课程归员工培训中心所有，任何个人不得占为己有，未经许可不得向外传播，违者必究什么是宏宏是一段定义好的操作，它可以是一段程序代码，也可以是一连串的指令集合。宏的作用可以使频繁执行的动作自动化 节省时间，提高工作效率，又能减少失误此课程归员工培训中心所有，任何个人不得占为己有，未经许可不得向外传播，违者必究宏的结构Sub 主界面主界面()主界面主界面 Macro Coren Lee 记录的宏记录的宏 2002-11-5 Sheets(主界面主界面).SelectEnd Sub宏名宏名宏名宏名批注批注批注批注宏程序宏程序宏程序宏程序此课程归员工培训中心所有，任何个人不得占为己有，未经许可不得向外传播，违者必究录制宏录制宏需要经常重复执行的某项任务，就可需要经常重复执行的某项任务，就可需要经常重复执行的某项任务，就可需要经常重复执行的某项任务，就可以把执行这些任务的步骤全部录制在以把执行这些任务的步骤全部录制在以把执行这些任务的步骤全部录制在以把执行这些任务的步骤全部录制在宏里，把宏变为可自动执行的任务宏里，把宏变为可自动执行的任务宏里，把宏变为可自动执行的任务宏里，把宏变为可自动执行的任务 执行宏的操作执行宏的操作 执行宏的命令执行宏的命令执行宏的命令执行宏的命令 在工具栏上设置宏按钮在工具栏上设置宏按钮在工具栏上设置宏按钮在工具栏上设置宏按钮 使用窗体控件使用窗体控件使用窗体控件使用窗体控件 使用图形对象执行宏使用图形对象执行宏使用图形对象执行宏使用图形对象执行宏 此课程归员工培训中心所有，任何个人不得占为己有，未经许可不得向外传播，违者必究小技巧Excel中用自动运行宏提高工作效率在日常工作中，我们经常需要在每次打开同一个Excel文件时都进行一些例行的操作，如改变表格的格式、更新报表日期、打印文件、对工作表进行保护或取消保护等等。Excel的自动运行宏“AutoOpen”可在文件打开后立即完成这些例行的操作任务，既快速又准确。此课程归员工培训中心所有，任何个人不得占为己有，未经许可不得向外传播，违者必究坚坚如磐石的安全性如磐石的安全性宏的安全性宏的安全性宏的安全性宏的安全性宏宏宏宏，就就就就是是是是一一一一段段段段定定定定义义义义好好好好的的的的操操操操作作作作，它它它它可可可可以以以以是是是是一一一一段段段段程程程程序序序序代代代代码码码码，它它它它也也也也可可可可以以以以是是是是一一一一连连连连串串串串的的的的指指指指令令令令集集集集合合合合。宏宏宏宏的的的的作作作作用用用用可可可可以以以以使使使使频频频频繁繁繁繁执执执执行行行行的的的的动动动动作作作作自自自自动动动动化化化化，它它它它执执执执行行行行一一一一组组组组动动动动作作作作比比比比用用用用户户户户一一一一步步步步一一一一步步步步执执执执行行行行要要要要快快快快得得得得多多多多，而而而而且且且且是是是是完完完完全全全全按按按按用用用用户户户户所所所所设设设设定定定定好好好好的的的的要要要要求求求求，准准准准确确确确无无无无误误误误地地地地执执执执行行行行。这这这这样样样样，既能，既能，既能，既能节节节节省省省省时间时间时间时间，提高工作效率，又能减少失，提高工作效率，又能减少失，提高工作效率，又能减少失，提高工作效率，又能减少失误误误误，起到事半功倍的作用。，起到事半功倍的作用。，起到事半功倍的作用。，起到事半功倍的作用。你你你你可可可可能能能能听听听听说说说说过过过过由由由由计计计计算算算算机机机机病病病病毒毒毒毒引引引引起起起起的的的的潜潜潜潜在在在在威威威威胁胁胁胁。“病病病病毒毒毒毒”是是是是一一一一种种种种在在在在后后后后台台台台工工工工作作作作的的的的隐隐隐隐藏藏藏藏宏宏宏宏或或或或者者者者软软软软件件件件程程程程序序序序，经经经经常常常常骚骚骚骚扰扰扰扰计计计计算算算算机机机机用用用用户户户户，并并并并且且且且毁毁毁毁坏坏坏坏重重重重要要要要的的的的数数数数据据据据文文文文件件件件以以以以及及及及应应应应用用用用软软软软件。件。件。件。宏的保宏的保宏的保宏的保护护护护方法：方法：方法：方法：安全安全安全安全级级级级【高高高高】通常适用于用通常适用于用通常适用于用通常适用于用户户户户没有安装防病毒没有安装防病毒没有安装防病毒没有安装防病毒软软软软件的件的件的件的时时时时候候候候 选择选择选择选择安全安全安全安全级为级为级为级为【中中中中】时时时时，用，用，用，用户户户户可以确保所使用的宏命令是安全的可以确保所使用的宏命令是安全的可以确保所使用的宏命令是安全的可以确保所使用的宏命令是安全的 安全安全安全安全级级级级【低低低低】的情况下，的情况下，的情况下，的情况下，Office XPOffice XP将不将不将不将不对对对对宏作出任何宏作出任何宏作出任何宏作出任何检查检查检查检查 此课程归员工培训中心所有，任何个人不得占为己有，未经许可不得向外传播，违者必究认识VBAVBA是一个通用程序语言，可共享Microsoft各种相关的重要软件。以VBA编写的程序允许用户将其复制到VisualBasic中加以调试，用VisualBasic宏来控制Excel。当记录宏不能满足用户需要或用记录宏无法记录命令时，可以用VBA语言创造宏来控制工作簿的各项操作。创建用户对话框、工具按钮或自定义命令，把Excel提供的多个工具按钮连接起来，作为一个工具按钮。利用重复语句控制循环宏内操作，利用不同参数执行某个相关功能模块，使宏指令完成一系列复杂操作。来连接多个宏。VBA提供了许多内部函数，还允许用户自定义函数来简化对工作簿、工作表、图表等复杂操作。此课程归员工培训中心所有，任何个人不得占为己有，未经许可不得向外传播，违者必究VisualBasic编辑器用来编辑宏程序或制作更高阶的整合性应用程序附属于Office之下，使用较少的系统资源，达到编辑应用程序的目的。此课程归员工培训中心所有，任何个人不得占为己有，未经许可不得向外传播，违者必究示例：在Excel中打印工资表SubInsertTitle()Cells(Selection.Row,Selection.Column).SelectRange(Selection,Selection.End(xlToRight).SelectSelection.CopyActiveCell.Offset(2,0).Range(A1).SelectDoUntilActiveCell=Selection.InsertShift:=xlDownRange(Selection,Selection.End(xlToRight).SelectSelection.CopyActiveCell.Offset(2,0).Range(A1).SelectLoopApplication.CutCopyMode=FalseEndSub此课程归员工培训中心所有，任何个人不得占为己有，未经许可不得向外传播，违者必究示例：保存并关闭系统PrivateSubWorkbook_BeforeClose(CancelAsBoolean)OnErrorResumeNextDimabcabc=MsgBox(您确认要关闭本系统吗？,vbQuestion+vbYesNo+vbDefaultButton2,确认)Ifabc=vbYesThenWorksheets(股票收益计算器).UnprotectPassword:=1Worksheets(股票收益计算器).Range(G13).FormulaR1C1=0Worksheets(股票收益计算器).ProtectDrawingObjects:=True,Contents:=True,Scenarios:=True,Password:=1Sheets(说明).SelectActiveWorkbook.SaveElseCancel=TrueEndIfEndSub此课程归员工培训中心所有，任何个人不得占为己有，未经许可不得向外传播，违者必究VBAVBA帮助你完成从必然王国到自由王国的飞跃帮助你完成从必然王国到自由王国的飞跃帮助你完成从必然王国到自由王国的飞跃帮助你完成从必然王国到自由王国的飞跃充分发挥充分发挥充分发挥充分发挥Office XPOffice XP的强大功能，成为的强大功能，成为的强大功能，成为的强大功能，成为OfficeOffice应用的真正高手应用的真正高手应用的真正高手应用的真正高手此课程归员工培训中心所有，任何个人不得占为己有，未经许可不得向外传播，违者必究END