快速提升系列培训Excel操作技巧办公类PPT课件.ppt

Excel在企业管理中的运用 主讲 顾承荣Jordan 20March2020 MonthlyFinancialReview China 1 大纲概述 基础操作数据整理常用函数公式数据分析图表演示综合演练 20March2020 MonthlyFinancialReview China 2 Excel篇 快捷键 2003到2007 2010 的变化 通用的快捷键Ctrl 字母数字的组合搜集大家知道的快捷键上课中碰到的快捷键自定义快捷键Alt 数字的组合推荐常用的快捷键 3 20March2020 MonthlyFinancialReview China Excel篇 快捷键 CTRL 通用的快捷键 Ctrl 数字键 Ctrl 1单元格设置Ctrl 2 3 4字体设置对应B I UCtrl 9 0隐藏行或者列Ctrl Shift 9 0取消隐藏行或者列 4 20March2020 MonthlyFinancialReview China Excel篇 快捷键 CTRL 通用的快捷键 Ctrl Fx键 Ctrl F3编辑自定义名称Ctrl F4相当于Alt F4Ctrl F9 F10窗口的操作键Ctrl F12打开文件 5 20March2020 MonthlyFinancialReview China Excel篇 快捷键 CTRL 通用的快捷键 Ctrl 字母键或者符号键 万能的Ctrl组合键Ctrl Ctrl D 6 请大家尝试争做CTRL键达人Ctrl这个功能键和不同的键位组合会产生很多的快捷方式 20March2020 MonthlyFinancialReview China Excel篇 快捷键 ALT 通用的快捷键 Alt 跟菜单有关 Alt F1插入图表Alt F2表格另存为Alt F4 微软通用 Alt F8运行宏Alt F11打开VBE编辑器Alt Enter自动填充键 7 20March2020 MonthlyFinancialReview China Excel篇 快捷键 SHIFT 通用的快捷键 Shift F键 Shift F2插入新批注Shift F3插入函数Shift F4选定区域移动Shift F5 Ctrl F HShift F8 Alt 鼠标单击Shift F10右击单元格菜单Shift F11添加新sheet 8 20March2020 MonthlyFinancialReview China Excel篇 快捷键 2007 2010 的快捷键 提供了自定义的快捷方式 9 20March2020 MonthlyFinancialReview China Excel篇 快捷键 CTRL SHIFT ALT 很多种组合 意想不到的效果不是创新 而是发现运用 10 20March2020 MonthlyFinancialReview China Excel篇 趣味基础 基础应该是每一门课程的基石 很好的掌握了它的话您就能够 充分的发挥其潜力 往往我们学习基础的时候 容易 犯困 所以我们需要的是趣味基础练习操作趣味基础题 11 20March2020 MonthlyFinancialReview China 大纲概述 基础操作数据整理常用函数公式数据分析图表演示综合演练 20March2020 MonthlyFinancialReview China 12 Excel篇 F4 多用途的F4键 相对引用 绝对引用的快捷引导者 重复功能Ctrl Alt Shift的好帮手 13 20March2020 MonthlyFinancialReview China Excel篇 数据整理高手 数据从系统里导出发现问题 数据无法相加设置了单元格格式后还是文本数据合并在一起不是在分开的单元格里面 14 小技巧 如何巧妙分割数据 分单元格 如何巧妙合并数据 合并在同一单元格 内容为文本 20March2020 MonthlyFinancialReview China Excel篇 慧眼识英雄 数据从系统里导出发现问题 15 问题 这两列数据一样吗 一样请说明出处不一样请说明理由 那里见到过 那种情况下会发生 数据A数据B20110913201109132011091420110914201109152011091520110916201109162011091720110917201109182011091820110919201109192011092020110920 20March2020 MonthlyFinancialReview China Excel篇 条件格式 2003到2007 2010 的变化 2003版本中的条件格式限定为3种 而且变化单一条件格式只要能达到目的即可2007版本为用户作出更加漂亮的报表搭建了平台条件格式的介绍条件格式的运用条件格式的综合运用结合日期格式做出双休日提醒报表双色报表 16 20March2020 MonthlyFinancialReview China Excel篇 条件格式 输入有效数据 条件格式通过条件格式 自动显示输入出错 不符合单元格格式 的数据 20March2020 MonthlyFinancialReview China 17 Excel篇 条件格式 输入有效数据 条件格式输入有效数据 条件格式的高级应用 0710版本 设置红绿灯报表 设计一个单元格 老板 选择 通过 或者 拒绝 当选择 通过 指定单元格显示 绿色 当选择 拒绝 指定单元格显示 红色 具体课堂分析 20March2020 MonthlyFinancialReview China 18 Excel篇 条件格式 输入有效数据 条件格式输入有效数据 条件格式的高级应用 03版本 要求A列日期在2007年4月2日之前的10天内 并且C列为 N 的行 用红色边框显示出来 具体操作请见课堂讲解 删除条件格式 点击 删除 选择条件 再确定即可 20March2020 MonthlyFinancialReview China 19 Excel篇 数据有效性 锁定输入内容 特定格式 特定内容 中断模式 20 20March2020 MonthlyFinancialReview China Excel篇 数据有效性 输入有效数据 设置 20March2020 MonthlyFinancialReview China 21 Excel篇 数据有效性 输入有效数据 设置 20March2020 MonthlyFinancialReview China 22 Excel篇 数据有效性 输入有效数据 输入信息 20March2020 MonthlyFinancialReview China 23 Excel篇 导入外部数据 20March2020 MonthlyFinancialReview China 24 Excel篇 数据的分级 数据表的折叠和展开组合功能快捷键 Alt Shift 左右 方向 25 20March2020 MonthlyFinancialReview China Excel篇 隐藏工作表03 07 10 隐藏工作表取消隐藏工作表 26 07 10版中隐藏 取消隐藏工作表在用一个交互功能中不足之处无法一次性 取消所有的隐藏工作表 VBA小代码解决问题 20March2020 MonthlyFinancialReview China Excel篇 保护文件 不让别人窃取你的成果如何保护您的工作表如何保护您的工作簿 27 20March2020 MonthlyFinancialReview China Excel篇 保护文件 完全保护为工作簿设置密码 单击 文件 菜单的 另存为 命令 击 工具 菜单 常规选项 在 打开权限密码 编辑框中 选定代表原密码的符号 如果要更改密码 请键入新密码 然后单击 确定 按钮 密码是区分大小写的 如果要取消密码 按DELETE键 然后单击 确定 按钮 也可以 只有03版本 工具 选项 安全性 28 20March2020 MonthlyFinancialReview China Excel篇 保护文件 20March2020 MonthlyFinancialReview China 29 大纲概述 基础操作数据整理常用函数公式数据分析图表演示综合演练 20March2020 MonthlyFinancialReview China 30 Excel篇 常用函数1 2003于2007中的常用函数是通用的 特殊常用函数IFERROR如果使用了 在2003中打开会显示 N A条件逻辑函数 日期函数 查询函数 常用 条件逻辑函数 IF AND OR日期函数 Date MONTH YEAR DAY 查询函数 MID LEFT RIGHT INDEX MATCH LOOKUP VL HL 31 20March2020 MonthlyFinancialReview China Excel篇 常用函数2 OFFSET函数的学习与妙用解决图形图表问题实现数据源的自动化 32 20March2020 MonthlyFinancialReview China Excel篇 常用函数3 绝对引用 A 1相对引用 A1 固定列 或者A 1 固定行 填写 的技巧 F4 33 练习99乘法口诀表 20March2020 MonthlyFinancialReview China Excel篇 常用函数4 Vlookup的使用紧缺查找 模糊查找Rand和Round 34 20March2020 MonthlyFinancialReview China 2020 3 20 35 Excel篇 演练篇1 友好表格的制作与分析双色表的制作财务人员必备两者结合设计制作人事查询系统 36 20March2020 MonthlyFinancialReview China Excel篇 演练篇2 多重数据表 表格的分析表格的月份或者区域的累加多了 如何在相同的表格 相同的格式下进行财务或者业务数据的累加 并且进行分析 37 合并 20March2020 MonthlyFinancialReview China 大纲概述 基础操作数据整理常用函数公式数据分析图表演示综合演练 20March2020 MonthlyFinancialReview China 38 Excel篇 数据透视表 2003于2007中的透视表格是不通用的 但是向下兼容 在2007表格中做的数据透视表 如果保存为03版的格式Excel会自动变成数值格式练习03 07中的PVtable的基础操作了解熟悉07版中的操作进入系统的练习加深至对数据的分析 39 20March2020 MonthlyFinancialReview China Excel篇 数据透视表 如果单单把数据透视表做成计数或者加总的工具那就 大材小用了PVtable是很好的分析工具百分比环比等加入计算公式添加数据透视图 40 20March2020 MonthlyFinancialReview China Excel篇 数据透视表 创建数据透视表 41 页 此标志区域中按钮将作为透视表分页符行 此标志区域中按钮将作为透视表的行字段列 此标志区域中按钮将作为透视表的列字段数据 此标志区域中按钮将作为透视表的显示汇总数据 20March2020 MonthlyFinancialReview China Excel篇 数据透视表 创建数据透视表 42 拖动左边的字段到相应的区域 20March2020 MonthlyFinancialReview China Excel篇 数据透视表 创建数据透视表 43 初步完成的数据透视表 20March2020 MonthlyFinancialReview China Excel篇 数据透视表 创建动态的数据透视表用户在完成数据透视表后 如果数据源增加了新的行或列 可以建立动态的数据透视表 来实时根据源数据的变化更新透视表 1 定义名称 创建列表 插入 名称 指定 选中 首行 创建列表 2 刷新数据 在数据透视表工具栏中选 刷新数据 20March2020 MonthlyFinancialReview China 44 Excel篇 数据透视表 改变数据透视表的布局 用鼠标左键点击要拖动的字段按钮 移动字段到相应位置即可 20March2020 MonthlyFinancialReview China 45 Excel篇 数据透视表 改变数据透视表的布局 分页面显示功能 1 选工具栏上 数据透视表 2 点击 分页显示 3 在 分页显示 中单击页字段 如 销售地区 4 销售地区 字段名称的各项对工作表命名 20March2020 MonthlyFinancialReview China 46 Excel篇 数据透视表 改变数据透视表的布局 显示和隐藏字段 1 双击页字段 品名 调出 数据透视表字段 对话框选择隐藏字段 2 利用页字段按钮 品名 拖到行区域 单击 品名 字段下拉按钮 选择隐藏字段 20March2020 MonthlyFinancialReview China 47 Excel篇 数据透视表 设置数据透视表格式 1 右键单击数据透视表 2 在弹出的快捷菜单中单击 表格选项 3 在 数据透视表选项 对话框的 格式选项 中勾选 保留格式 4 可以选中透视表数据区域进行格式设置 20March2020 MonthlyFinancialReview China 48 Excel篇 数据透视表 在数据透视表中排序 1 单击行字段 如 销售季度 选菜单栏上的 数据 排序 2 右键单击行字段 选择 顺序 可以进行字段在行向上的左移 右移等 3 自动排序 双击行字段 出现 数据透视表字段 单击 高级 出现 数据透视表字段高级选项 20March2020 MonthlyFinancialReview China 49 Excel篇 数据透视表 数据透视表的项目组合 1 指定项 用CTRL键帮助选择行字段下的几个项目 单击右键 组及显示明细数据 组合 再命名 2 数字项组合 选择要组合的行字段 按上述操作点击 组合 修改 步长 3 按日期或时间项组合 20March2020 MonthlyFinancialReview China 50 Excel篇 数据透视表 创建数据透视图 1 通过 数据透视表和数据透视图向导 2 先创建数据透视表 然后单击 数据透视表 工具栏中的 图表向导 创建数据透视图 20March2020 MonthlyFinancialReview China 51 Excel篇 数据分析控件法 利润敏感度利用滚动条或微调按钮进行利润敏感动态分析 20March2020 MonthlyFinancialReview China 52 Excel篇 数据分析控件法 使用单变量求解工具 逆向敏感分析例如 已知月销售额 求单价 首先需要明确公式 即事先有计算过程 月交易额 月交易量 单价 美元汇率月交易量 每次交易数量 每月交易次数应用单变量求解功能求单价 20March2020 MonthlyFinancialReview China 53 Excel篇 数据分析控件法 使用单变量求解工具 在图上求解利用图表更直观地进行 如 现有计算模型汇总将根据本年的实际发生额做10 的削减以得到来年的预算数据 拉动图中的条形柱 出现单变量求解窗口 20March2020 MonthlyFinancialReview China 54 Excel篇 数据分析控件法 使用单变量求解工具 规划求解1 确定了某个任务 研究如何使用最少的人力 物力和财力去完成 2 已经有了一定数量的人力 物力和财力 研究如何使它们获得最大的受益 1 决策变量 要求解的未知数2 约束条件 对决策变量的限制条件 3 目标 如利润最大或最小 20March2020 MonthlyFinancialReview China 55 Excel篇 数据分析控件法 规划求解 寻找成本最低的采购方案某公司需要采购一批赠品 可选4个品种 其原则是 全部赠品的总数量为4000件赠品A不能少于400件赠品B不能少于600件赠品C不能少于800件赠品D不能少于200件 但也不能多于1000件如何制定采购计划 既能满足需求 又能使采购成本最小 1 决策变量 用1 2 3 4分别表示4种赠品XXXX2 约束条件 3 目标 求采购总成本最小 20March2020 MonthlyFinancialReview China 56 Excel篇 数据分析控件法 规划求解 寻找成本最低的采购方案 20March2020 MonthlyFinancialReview China 57 Excel篇 数据分析控件法 规划求解 寻找成本最低的采购方案 规划求解极限值报告 规划求解敏感性报告 规划求解运算结果报告 20March2020 MonthlyFinancialReview China 58 大纲概述 基础操作数据整理常用函数公式数据分析图表演示综合演练 20March2020 MonthlyFinancialReview China 59 Excel篇 图表的制作 认