EXCEL基础培训讲义.ppt

此课程归员工培训中心所有，任何个人不得占为己有，未经许可不得向外传播，违者必究 EXCEL基础知识培训 员工培训中心 2004年11月15日 NE002003b V1.1 此课程归员工培训中心所有，任何个人不得占为己有，未经许可不得向外传播，违者必究 课程目的 1、常见见操作及技巧（函数使用） 2、数据管理与分类类 3、外部数据导导入 4、数据保护护 5、宏和VBA简简介 此课程归员工培训中心所有，任何个人不得占为己有，未经许可不得向外传播，违者必究 绪言 Excel用途（数据分析） 列出数据 对数据执行计算 对数据点进行比较 展示数据（突出数据点之间关系的方式） 熟悉Excel程序窗口 三要素（工作薄、工作表、单元格） 标题栏 菜单栏 状态栏 此课程归员工培训中心所有，任何个人不得占为己有，未经许可不得向外传播，违者必究 Excel三要素 工作簿工作簿 工作表工作表 单元格和单元格和 区域区域 此课程归员工培训中心所有，任何个人不得占为己有，未经许可不得向外传播，违者必究 【名称】框 插入函数 程序和工作薄标题栏 【编辑】栏 程序窗口按钮 菜单栏 【常用/格式】 工具栏 工作薄窗口 状态栏 任务窗格 此课程归员工培训中心所有，任何个人不得占为己有，未经许可不得向外传播，违者必究 讲解内容 F常见见操作及技巧 F数据管理与分类类（函数使用） F外部数据导导入 F数据保护护 F宏和VBA简简介 此课程归员工培训中心所有，任何个人不得占为己有，未经许可不得向外传播，违者必究 第一部分 常见操作及技巧 输入技巧 工作薄（表）操作技巧 使用函数 数据有效性 此课程归员工培训中心所有，任何个人不得占为己有，未经许可不得向外传播，违者必究 输入技巧 快速输入相同内容 选中输入CtrlEnter 如何实现快速输入较多重复内容 工具自动更正键入时更换 如何输入超过11位的数字 在输入数字时在前面加上一个单引号“ ” 如何在单元格中硬换行 AltEnter 自动填充公式 工具选项编辑（设置）扩展数据列表 格式及公式 此课程归员工培训中心所有，任何个人不得占为己有，未经许可不得向外传播，违者必究 工作簿与工作表模板 工作簿模板 为特定的任务和项目创建自定义的工作簿模 板 工作簿默认的模板名为book.xlt 工作簿模板的存储位置 工作表模板 在工作簿中创建特定的工作表 工作表默认的模板名为sheet.xlt 工作表模板的存储位置 C:Program FilesMicrosoft OfficeOffice10XLStart 此课程归员工培训中心所有，任何个人不得占为己有，未经许可不得向外传播，违者必究 轻轻轻轻松完成松完成规规规规范化的范化的报报报报表表 Office Office XPXP提供的模板功能，提供的模板功能，让让让让您您轻轻轻轻松松 完成完成规规规规范化的公文范化的公文 模板和模板和MM上的模板上的模板 模板模板库还库还库还库还 有有这这这这么多么多类类类类型的模板可供型的模板可供 我我们选择们选择们选择们选择 ，而且微，而且微软软软软公司随公司随时时时时更新更新 内容以内容以满满满满足广大用足广大用户户户户的需求的需求 。 此课程归员工培训中心所有，任何个人不得占为己有，未经许可不得向外传播，违者必究 设置Excel模板的细节 文本 数据 图形 公式 此课程归员工培训中心所有，任何个人不得占为己有，未经许可不得向外传播，违者必究 格式的设置 单元格和工作表格式 页面格式 打印区域 单元格样式 工作簿中所含工作表的数量和类型 隐藏和保护 此课程归员工培训中心所有，任何个人不得占为己有，未经许可不得向外传播，违者必究 Excel模板与窗体 窗体的创建 窗体和窗体控件 窗体的保护 使用窗体简化工作 此课程归员工培训中心所有，任何个人不得占为己有，未经许可不得向外传播，违者必究 案例演示 房屋按揭计算表 进销存管理表 差旅费明细表 费用报告表 个人所得税计算表 此课程归员工培训中心所有，任何个人不得占为己有，未经许可不得向外传播，违者必究 工作薄（表）操作技巧 共享工作薄 “工具共享工作簿”命令，接着在出现的“共 享工作簿”对话框中，选择“编辑”选项卡，然后选 中“允许多用户同时编辑”选项并单击确定按 钮 发送工作薄 多个工作表之间切换 按下“Ctrl”“TAB” 在打开的工作薄 间切换 按下“Ctrl”“Page Down” 可切换到工作薄的 下一 个工作表。 按下“Ctrl”“Page Up” 可切换到工作薄的 上一 个 工作表。 此课程归员工培训中心所有，任何个人不得占为己有，未经许可不得向外传播，违者必究 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多维数据等方法，可以说，Excel 2002可以使用绝大多 数不同格式的数据，使处理数据方便快捷 此课程归员工培训中心所有，任何个人不得占为己有，未经许可不得向外传播，违者必究 从文本文件中获取数据 选择“文件”菜单中的“打开命令， 在“文件类型”下拉表中选择“文本 文件”即可 执行“数据”菜单中“获取外部数据 ”子菜单上的“导入文本文件”命令 。 此课程归员工培训中心所有，任何个人不得占为己有，未经许可不得向外传播，违者必究 从Web导入表格数据 数据导入方法 在一个新的Excel文件中，选择菜单栏的“ 数据导入外部数据新建Web查询”，打开“ 新建Web查询”窗口，图中可以看到凡是有表 格的地方其左上角都会出现一个黑色的小箭头 ，只要单击就可以使其变成小勾，表示该箭头 指的表格将被导入Excel中。全部选定后可以 先通过“保存查询”按钮将该Web查询单独保存 ，这样可在以后其他工作簿中重复调用。现在 单击“导入”按钮，选择保存在当前工作表中， 还是新建一个工作表。 当需要查询数据时，只要打开Excel文件，Excel会自动打开 一个“查询刷新”对话框，选定“启用自动刷新”，Excel会自动连通 网站对所有表格数据进行更新，您需要的更新数据就出现了。您 也可以通过菜单中的“数据更新数据”命令进行更新。 此课程归员工培训中心所有，任何个人不得占为己有，未经许可不得向外传播，违者必究 第四部分 数据的保护 单元格的保护 工作表的保护 工作簿的保护 此课程归员工培训中心所有，任何个人不得占为己有，未经许可不得向外传播，违者必究 坚坚坚坚如磐石的安全性如磐石的安全性 设设设设置文档保置文档保护护护护 为了防止文件被他人更改，为了防止文件被他人更改， 我们可以使用我们可以使用Office XPOffice XP提供提供 的的“ “文件保护文件保护” ”功能功能 文件打开保护文件打开保护 文件修改保护文件修改保护 建议只读方式保护建议只读方式保护 此课程归员工培训中心所有，任何个人不得占为己有，未经许可不得向外传播，违者必究 保护特殊单元格 取消整个工作表锁定 特殊单元格设定锁定 对所在工作表进行保护 设置取消保护密码 坚坚坚坚如磐石的安全性如磐石的安全性 单单单单元格保元格保护护护护 此课程归员工培训中心所有，任何个人不得占为己有，未经许可不得向外传播，违者必究 第五部分 宏和VBA简介 宏及宏的安全性 VBA简介 此课程归员工培训中心所有，任何个人不得占为己有，未经许可不得向外传播，违者必究 什么是宏 宏是一段定义好的操作，它可以是一 段程序代码，也可以是一连串的指令 集合。 宏的作用可以使频繁执行的动作自动化 节省时间，提高工作效率，又能减少失误 此课程归员工培训中心所有，任何个人不得占为己有，未经许可不得向外传播，违者必究 宏的结构 Sub 主界面() 主界面 Macro Coren Lee 记录的宏 2002-11-5 Sheets(“主界面“).Select End Sub 宏名宏名 批注批注 宏程序宏程序 此课程归员工培训中心所有，任何个人不得占为己有，未经许可不得向外传播，违者必究 开始录制宏开始录制宏 操作步骤操作步骤 完成宏录制完成宏录制 运行宏运行宏 录制宏录制宏 需要经常重复执行的某项任务，就可需要经常重复执行的某项任务，就可 以把执行这些任务的步骤全部录制在以把执行这些任务的步骤全部录制在 宏里，把宏变为可自动执行的任务宏里，把宏变为可自动执行的任务 执行宏的操作执行宏的操作 执行宏的命令执行宏的命令 在工具栏上设置宏按钮在工具栏上设置宏按钮 使用窗体控件使用窗体控件 使用图形对象执行宏使用图形对象执行宏 此课程归员工培训中心所有，任何个人不得占为己有，未经许可不得向外传播，违者必究 小技巧 Excel中用自动运行宏提高工作效率 在日常工作中，我们经常需要在每次打开 同一个Excel文件时都进行一些例行的操作， 如改变表格的格式、更新报表日期、打印文件 、对工作表进行保护或取消保护等等。Excel 的自动运行宏“AutoOpen”可在文件打开后立 即完成这些例行的操作任务，既快速又准确。 此课程归员工培训中心所有，任何个人不得占为己有，未经许可不得向外传播，违者必究 坚坚坚坚如磐石的安全性如磐石的安全性 宏的安全性宏的安全性 宏，就是一段定宏，就是一段定义义义义好的操作，它可以是一段程序代好的操作，它可以是一段程序代码码码码，它也可以是一，它也可以是一连连连连串的串的 指令集合。宏的作用可以使指令集合。宏的作用可以使频频频频繁繁执执执执行的行的动动动动作自作自动动动动化，它化，它执执执执行一行一组动组动组动组动 作比用作比用户户户户 一步一步一步一步执执执执行要快得多，而且是完全按用行要快得多，而且是完全按用户户户户所所设设设设定好的要求，准确无定好的要求，准确无误误误误地地执执执执 行。行。这样这样这样这样 ，既能，既能节节节节省省时间时间时间时间 ，提高工作效率，又能减少失，提高工作效率，又能减少失误误误误，起到事半功倍的，起到事半功倍的 作用。作用。 你可能听你可能听说过说过说过说过 由由计计计计算机病毒引起的潜在威算机病毒引起的潜在威胁胁胁胁。“ “病毒病毒” ”是一种在后台工作的是一种在后台工作的隐隐隐隐 藏宏或者藏宏或者软软软软件程序，件程序，经经经经常常骚扰计骚扰计骚扰计骚扰计 算机用算机用户户户户，并且，并且毁毁坏重要的数据文件以及坏重要的数据文件以及应应应应 用用软软软软件。件。 宏的保宏的保护护护护方法：方法： 安全安全级级级级【高高】通常适用于用通常适用于用户户户户没有安装防病毒没有安装防病毒软软软软件的件的时时时时候候 选择选择选择选择 安全安全级为级为级为级为 【中中】时时时时，用，用户户户户可以确保所使用的宏命令是安全的可以确保所使用的宏命令是安全的 安全安全级级级级【低低】的情况下，的情况下，Office XPOffice XP将不将不对对对对宏作出任何宏作出任何检查检查检查检查 此课程归员工培训中心所有，任何个人不得占为己有，未经许可不得向外传播，违者必究 认识VBA VBA是一个通用程序语言，可共享Microsoft各种相关的重 要软件。 以VBA编写的程序允许用户将其复制到Visual Basic中加以 调试，用Visual Basic宏来控制Excel。 当记录宏不能满足用户需要或用记录宏无法记录命令时， 可以用VBA语言创造宏来控制工作簿的各项操作。 创建用户对话框、工具按钮或自定义命令，把Excel提供的 多个工具按钮连接起来，作为一个工具按钮。 利用重复语句控制循环宏内操作，利用不同参数执行某个 相关功能模块，使宏指令完成一系列复杂操作。来连接多 个宏。 VBA提供了许多内部函数，还允许用户自定义函数来简化 对工作簿、工作表、图表等复杂操作。 此课程归员工培训中心所有，任何个人不得占为己有，未经许可不得向外传播，违者必究 Visual Basic编辑器 用来编辑宏程序或制作更高阶的整合性应用程序 附属于Office之下，使用较少的系统资源，达到编辑应用程序的 目的。 此课程归员工培训中心所有，任何个人不得占为己有，未经许可不得向外传播，违者必究 示例：在Excel中打印工资表 Sub InsertTitle() Selection.CurrentRegion.Select Cells(Selection.Row, Selection.Column).Select Range(Selection, Selection.End(xlToRight).Select Selection.Copy ActiveCell.Offset(2, 0).Range(“A1“).Select Do Unt