Excel技能培训[]PPT课件.pptx

ExcelTraining EfficientInputting录入技巧AccurateTargeting定位技巧LayoutOptimization页面优化技巧Security安全性保护PivotTable数据汇总 目录 Operation操作技巧 Function函数技巧 Chart图表技巧 Condition条件函数Retrieve取值函数Lookup查找函数Quotation引用函数 Internalcomparison薪酬竞争力比对图SalaryStructure架构及散点组合图 Category分类 Fill填充技巧Goto定位单元格Find Replace查找替换ConditionalFormat条件格式DataValidation数据有效性Printingsetup 打印设置 Pie bar column line XYScatter多种图样类型Compoundchart组合不同图标类型 Details细则 If Countif Sumif条件计数求和函数Mid Right Left Find 取值函数Proper Trim格式函数Indirect Index引用函数 数据透视表 数据透视 Excel2007工具栏速览 Home 系统设置自定义快捷键栏 快捷操作开始 基本操作 单元格格式插入 图 表 各类对象页面布局 打印设置公式 公式数据 排序 筛选 组合审阅 批注 保护 录入技巧 页面优化技巧 工具栏设置操作方法注释熟悉工具栏及菜单基本设置右键点选菜单或工具栏区域 自定义设置 页面设置去除滚动条 工作表标签 网格线视图 编辑栏 网格线 标题Excel选项 高级 此工作薄的显示选项最大化显示所选区域缩放级别修改工作表标签右键选中标签 着色或改名改名 快 Alt O H R 视图设置保存特定区域视图视图 自定义视图 常用区域操作 对常用区域进行自定义视图全屏显示 分页预览视图 全屏显示 分页预览打印设置自定义打印区域文件 打印区域 设置打印区域 常用区域操作 设置多工作表连续打印页面设置 页面 起始页 拼接其他文件 具体请百度设置分页打印 手工插入分页符 插入 分页符设置抬头行 页眉页脚等文件 页面设置 页眉页脚设置错误值 图表不打印页面设置 工作表 错误单元格打印 内事不决问百度 这几页的更为详细的方法请自行百度 Exercise1 定位技巧 普通单元格区域的快速定位操作方法注释选择连续单元格Shift 鼠标 方向键 大区域操作 选择不连续单元格Ctrl 鼠标 小区域操作 跳跃于连续单元格区域两端Ctrl 方向键 大区域操作 选择整行 整列非空单元格Ctrl Shift 方向键 大区域操作 选择整个有值表格Ctrl Shift 大区域操作 特殊单元格区域的准确定位 快F5 选择特殊区域 如 公式区域 编辑 定位 快F5 选中所有含公式区域 用汉字名称代替单元格地址插入 名称 定名 常用区域操作 条件格式 突出显示符合要求的单元格格式 条件格式 例 隔行标识颜色 查找与替换 快 ctrl F Ctrl H 特殊内容查找 红色字体 空格等 查找 选项 例 去除所有0值 筛选 自动筛选 快 Alt D F F 自定义筛选内容自定义 条件 例 选出所有经理 高级筛选设定筛选条件 例 找出不重复项 多区域视图冻结窗口 拆分窗口滚动条附近快速选择拆分 Exercise1 安全性设置技巧 活用数据有效性操作方法注释条件 提示 警告信息设定选择A1 数据 数据有效性 自定义公式 A列禁止输入多余空格 len a1 len trim a1 函trim 去多余空格 A列禁止输入重复数据 countif A A A1 1 函countif 条件计数 A列仅可输入数值 isnumber A1 函isnumber 数值判断 下拉框设置区域定名管理下拉框信息设置工作表保护 单元格可视性工作簿保护工具 选项 安全性 另存 工具 一般选项 单元格可视性设置单元格格式 保护 隐藏单元格 工作表 簿保护1 单元格格式 保护 锁定2 工作表 簿保护深度隐藏VBA VeryHidden快速进入VBAAlt F11Visible2veryhidden工作表快速备份复制 移动当前工作表右键工作表标签 移动或复制 快 Ctrl 鼠标拖动标签 Exercise1 C B从业人员Excel快捷键汇总1 C B从业人员Excel快捷键汇总2 这几页快捷键中 打星星的是强烈推荐记住的 能极大提高效率 C B从业人员Excel快捷键汇总3 PivotTable数据透视表 什么是数据透视表 数据透视表是一种对大量数据快速汇总和建立交叉列表的交互式表格 利用它可以很快地从不同角度对数据进行分类汇总 Count计数 Average平均 分类 汇总 汇总方式 可以按行和列分类 然后在中间汇总 数字有多种方式 求和 平均等 文字则只能count 计数 数据透视表使用步骤 数据透视表使用步骤1选中数据区域2插入 数据透视表3选择要添加的报表字段4根据需求进行字段与分组 并调整显示信息或运算方式 数据透视表技巧 通常我们都会记录每个离职员工的离职日期 具体到天 部门等信息 但如何在几秒钟之内快速生成各部门在月度或季度的总离职人数表 原数据表 月度人数统计表 月度离职人数按部门分布比例 Exercise2 数据透视表技巧 续 从普通的数据基础表到一般数据透视表选择数据区域 插入数据透视表 并选择相应字段 对离职日期进行月度分组 并进行顺序调整 在对字段进行字段设置 值显示方式 占同行数据总和的百分比 即可得出各月各部门离职人数占当月总离职人数的百分比 Exercise2 课后加强练习 数据透视表综合练习 用数据透视表完成以下要求 每个销售人员的订购量是多少 并将订购额从高到低排序特定国家地区销售人员的订购额 将国家地区设置为报表筛选项 按季度 月份统计人员的订购额 并显示季度汇总按订购金额3 计算 每个销售人员应得多少提成 并求出每季度最大的前三项5 其他 每位人员订购额占总订购额的比 等等 Exercise2 注意 本页主要为课后练习 透视表的具体操作步骤已附在相应的练习答案中 BasicFunctions基本函数 C B操作中常用六大函数 Vlookup Match If Ceiling 或Roundup或Round Indirect Index Function函数技巧 常用函数符号操作方法连接多个单元格 符号 函 Concatenate 绝对引用 快F4 F4切换引用状态 绝对 相对 混合引用选择性粘贴所有员工工龄加1选择性粘贴运算功能行列转置 跳开空格复制内容等选择性粘贴特殊功能计数函数 函 Count a Countif Countifs 计算数值单元格个数Count range 计算有值单元格个数Counta range 计算年龄大于30岁的员工人数Countif range criteria 函 countif条件计数 条件函数与取值函数的组合 函 If mid len find 条件函数If logical test value if true value if false 单元格长度Len text 身份证中截取生日Mid Text start num Num chars Left right取出已知日期的年 月 日Year Month 多条件求和Sumif Sumifs Exercise3 Function函数技巧 单元格格式优化操作方法全大写Upper text 全小写Lower text 调整至正常状态Proper text 去除多余空格Trim range 引用 选学 相对引用索引引用Index array row num column num 绝对引用indirect ref text a1 部分引用Offset reference row column Exercise3 当我们对函数的使用方法有疑问事 第一 用F1键调出帮助 第二 问百度 从现有数据库调取数据 vlookup 张三 数据表格区 3 年龄列号 0 Formula 张三丰 张三 2020 2 4 21 table array employeeinfo 从现有数据库调取数据 续 Vlookup查找取数通过对共有指示符 查找值 的匹配 建立不同数据表之间的准确连接Vlookup lookup value table array Col Index Num check 指示符数据表格区相对列数匹配方式E g VLOOKUP A6 employeeinfo A E 5 0 从现有数据库调取数据 续 注意事项 两张表格的指示符必须在格式上统一 数值型VS文本型 务必清理好空格和重复项数据表格区首列必须为指示符所在列在复制公式前 记住要对指示符以及数据表格区所在列进行合理固定如格式不统一 比如即有文本又有数值 从文本到数值 公式 value A6 或点击此处的智能标签在进行对有vlookup公式单元格进行复制前 绝对引用快捷键 F4 Vlookup公式的高阶用法1 模糊匹配 Vlookup在绩效考核成绩匹配中的运用已知 评估分数与绩效等级的转换表 A部门员工绩效分数求值 计算A部门员工绩效等级E g VLOOKUP E2 A 1 B 6 2 Vlookup lookup value table array Col Index Num Check Check value 1 或者省略 或者false当匹配方式的值为1 真或者省略时 返回模糊匹配结果 模糊匹配 返回数据表格区中 小于且最接近查找值的指示符所对应的数值注意 数据表格区的指示符 必须进行升序排列 2 1 0 1 2 A Z FALSE TRUE 否则无法模糊匹配 ConvertTable Exercise3 1 Vlookup公式的高阶用法2 矩阵匹配 年度调薪工具 Vlookup在年度调薪表格中的运用已知 1 员工绩效等级 其薪酬竞争力比率 CR值 2 根据上述两个条件设计的薪酬增长矩阵求值 员工调薪比率E g VLOOKUP K3 A 2 F 6 MATCH J3 B 2 F 2 0 1 1 1 调薪矩阵 vlookup指示符 Exercise3 2 Match求出所在列数 所要求出的薪酬增长率 Vlookup公式的高阶用法2 矩阵匹配 年度调薪工具 续 E g match K3 A 3 G 3 0 3MATCH lookup value lookup array match type 返回查找值在数据表格区的位置 e g MATCH b a b c 0 在a b c数列中 b位于第二位注意 若匹配方式为0 匹配返回数列中第一个与查找值精确配对值的位置 数据表格区不需要排序 Exercise3 2 调薪矩阵 Match的指示符 其他 年度调薪工具表制作基本流程 为每位员工进行薪酬竞争力分析 得到市场比对率 CR 现有工资总额 相应级别P50分位值根据CR 员工绩效考核结果 调薪矩阵计算其调薪比率 使用Vlookup match矩阵匹配法结合调薪方案调整个人测算报表 如入职时间 试用期 是否超同等级工资总额上限等对调薪前与调薪后 统计按照不同绩效和CR的人数及成本 特别关注调薪成本增长率当调薪测算OK时 调整打印列与打印格式 签阅报批执行 并与员工进行调薪沟通 由负责人编制审核调薪矩阵与调薪方案 Exercise3 3 其他常用应用 课后自行练习 员工税后工资的计算 UseVlookupFormulatocalculatetaxandtheaftertaxsalaryforemployees 使用Vlookup公式 计算员工应纳税额及税后工资UsingApproximatematching使用模糊查询的方法Build upareferencetable includingquickdeductionratio taxratiowithineachsalaryrange 需要建立参照表格 包含速算扣除率 税率区间等 Chart图表技巧 作图基本操作 C B常用图表类型 饼中饼图百分比堆叠柱状图平滑回归图点折线图组合图图表条件格式化极值的规避 饼中饼图 人工成本分析 Exercise4 1 百分比堆叠柱状图 收入组成分析 Exercise4 2 组合图 柱状散点图 薪酬架构回顾 Exercise4 3 Closing结束语 建立复杂EXCEL文件的基本原则 原则一 自变量单独成页员工数据可单独成页 将所有假设信息及各种自变量放在一页 原则二 计算区域内不得有任何自变量所有计算区域的公式所连接的自变量都在同一页 将所有未来可能变化的数据都转变为一种假设 放在自变量页去 原则三 将计算区域与测试区域分开在大型EXCEL程序中可能在得出最后结果以前有多轮的测试 将测试与计算区域分在不同的页上 避免混淆 原则四 尽可能简化公式或用简单的逻辑方法在大型EXCEL程序中 跑一些程序可能需要花数小时甚至数天的时间 为了能够减少跑程序的时间 在设置公式的时候尽可能简化公式或者简化逻辑方法 原则五 不要和其他EXCEL文件链接一个EXCEL文件尽可能不要和另外的EXCEL文件链接 降低出错概率 如果数据表特别庞大 请设置为 手动计算 Excel应用心得 双手开攻 减少冗余点击 3步内快捷完成大部分工作 例 添加多个月份独立工作表 迅速查找某月员工流动信息 井然有序 海量数据模块化管理 轻松定位所需内容 例 选中包含公式区域并保护 设定常用区域以便打印 另辟蹊径 少用函数 多了解Excel自带功能 例 多方法找出不重名记录 标识重复次数 汇总多月员工加班费 隔行插入工资表表头 万无一失 屏蔽无效数据 保护公式及内容不被修改 例 设置单元格内容不可见 不可输入重复数值或多余空格 风随影动 组合绝对与相对引用 统一公式 随贴随用 例 图表自动标识极端值 用统一公式快速找到不同列信息 财才兼备 兼顾实效性和美观性的作图 例 自动显示薪酬架构图中各点对应的人名 薪酬竞争力分析图 无他 唯手熟尔 Practicesmakeperfect 附录1 用Excel计算平均值 中位值 Average Median AVERAGE MEDIAN number1 number2 Theargumentsmusteitherbenumbersorbenames arrays orreferencesthatcontainnumbers 自变量必须以数字或者是命名 数组或者是包含数字的引用区域Ifanarrayorreferenceargumentcontainstext logicalvalues trueorfalse oremptycells thosevaluesareignored however cellswiththevaluezeroareincluded 如果数列中的自变量包含文本 逻辑数值