Excel技能培训

1、Excel Training2 Efficient Inputting录入技巧 Accurate Targeting定位技巧 Layout Optimization页面优化技巧 Security 安全性保护 Pivot Table 数据汇总目录Operation操作技巧操作技巧Function函数技巧函数技巧Chart图表技巧图表技巧 Condition条件函数 Retrieve取值函数 Lookup查找函数 Quotation引用函数 Internal comparison 薪酬竞争力比对图 Salary Structure架构及散点组合图Category分类分类 Fill 填充技巧 Go

2、to 定位单元格 Find & Replace查找替换 Conditional Format条件格式 Data Validation 数据有效性 Printing setup. 打印设置 Pie, bar, column, line, XY Scatter多种图样类型 Compound chart组合不同图标类型Details细则细则 If, Countif, Sumif条件计数 求和函数 Mid, Right, Left, Find,$,& 取值函数 Proper, Trim格式函数 Indirect, Index引用函数 数据透视表数据透视数据透视3Excel 2007 工

3、具栏速览Home：系统设置 自定自定义义快捷快捷键栏键栏：快捷操作开始开始：基本操作、单元格格式 插入插入：图、表、各类对象页页面布局面布局：打印设置 公式公式：公式数据数据：排序、筛选、组合 审阅审阅：批注、保护4录入技巧信息录入常用功能信息录入常用功能普遍操作普遍操作高效操作高效操作录入大量固定位数小数小键盘盲打Excel选项高级自动插入小数点修改输入内容鼠标双击单元格快* F2修改/插入注释右键-插入/编辑注释快 Shift+F2 另类注释：数据有效性特定区域输入相同内容复制，粘贴区域选择,输入,快Ctrl + Enter复制左/上单元格内容复制，粘贴快 Ctrl+R/D单元格内换行?快

4、Alt + Enter输入当前日期手工输入快Ctrl + ;录入元天等数量单位直接在单元格内数入文字保留数值, 单元格格式自定义录入年 月 日 星期几格式?单元格格式自定义组合：yyyy年m月d日星期aaa5页面优化技巧 工具栏设置工具栏设置操作方法操作方法 注释注释 熟悉工具栏及菜单基本设置右键点选菜单或工具栏区域自定义设置 页面设置页面设置 去除滚动条、工作表标签、网格线视图编辑栏、网格线、标题 Excel选项高级此工作薄的显示选项 最大化显示所选区域缩放级别 修改工作表标签右键选中标签，着色或改名改名快:Alt,O,H,R 视图设置视图设置 保存特定区域视图视图自定义视图常用区域操作对常

5、用区域进行自定义视图 全屏显示、分页预览视图全屏显示、分页预览 打印设置打印设置 自定义打印区域文件打印区域设置打印区域常用区域操作 设置多工作表连续打印页面设置页面起始页拼接其他文件，具体请百度 设置分页打印(手工插入分页符)插入分页符 设置抬头行、页眉页脚等文件页面设置页眉页脚 设置错误值、图表不打印页面设置工作表错误单元格打印内事不决问百度，这几页的更为详细的方法请自行百度Exercise 16定位技巧 普通单元格区域的快速定位普通单元格区域的快速定位操作方法操作方法 注释注释 选择连续连续单元格Shift + 鼠标/方向键大区域操作 选择不连续不连续单元格Ctrl + 鼠标小区域操作

6、跳跃跳跃于连续单元格区域两端Ctrl + 方向键大区域操作 选择整行整行/整列非空单元格整列非空单元格Ctrl + Shift + 方向键大区域操作 选择整个有值表格整个有值表格Ctrl + Shift + *大区域操作 特殊单元格区域的准确定位特殊单元格区域的准确定位快快F5 选择特殊区域(如：公式区域)编辑-定位快F5 选中所有含公式区域 用汉字名称代替单元格地址插入-名称-定名常用区域操作 条件格式：条件格式： 突出显示符合要求的单元格格式-条件格式例：隔行标识颜色 查找与替换查找与替换快快:ctrl + F / Ctrl + H 特殊内容查找(红色字体,空格等）查找-选项例：去除所有0

7、值 筛选筛选-自动筛选自动筛选快快:Alt,D,F,F 自定义筛选内容自定义：条件例：选出所有经理 高级筛选设定筛选条件例：找出不重复项 多区域视图多区域视图 冻结窗口、拆分窗口 滚动条附近快速选择拆分Exercise 17安全性设置技巧 活用数据有效性活用数据有效性操作方法操作方法 注释注释 条件、提示、警告信息设定选择A1,数据数据有效性自定义公式 A列禁止输入多余空格=len($a1)=len(trim($a1)函trim：去多余空格 A列禁止输入重复数据=countif($A:$A,$A1)=1函countif:条件计数 A列仅可输入数值=isnumber($A1)函isnumber:

8、数值判断 下拉框设置区域定名管理下拉框信息 设置工作表保护、单元格可视性设置工作表保护、单元格可视性 工作簿保护工具选项安全性另存工具一般选项 单元格可视性设置单元格格式保护隐藏 单元格/工作表/簿保护1.单元格格式保护锁定2.工作表/簿保护 深度隐藏 VBA-Very Hidden 快速进入VBA Alt+F11 Visible 2 very hidden 工作表快速备份工作表快速备份 复制、移动当前工作表右键工作表标签移动或复制快：Ctrl+鼠标拖动标签Exercise 18C&B从业人员Excel快捷键汇总 1操作操作快捷键快捷键复制/剪切/粘贴Ctrl+C/Ctrl+X/Ctr

9、l +V查找/替换Ctrl+F/Ctrl+H多张Excel表格间的切换Ctrl + Tab前后工作表间的切换Ctrl + PgUp & PgDn快速选中整行、整列有值单元格Ctrl and Shift + Direction Key用下拉框挑选内容(仅对文本格式有效) Alt + 单元格内的换行Alt + Enter取消上步操作 / 恢复上步操作Ctrl + Z / Y单元格格式(字体, 对齐方式, 边框等): Ctrl + 1 ()9C&B从业人员Excel快捷键汇总 2操作操作快捷键快捷键新建 / 保存 / 另存Ctrl + N/ Ctrl + S / F12在前一页插入新

10、工作表Shift + F11编辑单元格F2插入/编辑注释Shift + F2复制上/左单元格Ctrl + D/R单元格、区域定名Ctrl + Shift + F3显示公式栏Shift + F3显示公式Ctrl + 特定区域输入相同内容:1.选择区域 2.录入要录入的内容 3. Ctrl + Enter这几页快捷键中，打星星的是强烈推荐记住的, 能极大提高效率。10操作操作快捷键快捷键隐藏当前单元格所在行列Ctrl + 0 (Zero) or Ctrl + 9取消隐藏当前单元格所在行列Ctrl + Shift + 0 or Ctrl + Shift + 9添加行、列Ctrl + Shift +

11、“+”删除行、列Ctrl + “-”选择性粘贴Alt (hold)+ E,S筛选Alt (hold)+ D,F,F修改当前工作表表名Alt (hold)+ O,H,R输入当前日期/时间Ctrl +; (分号) Ctrl+shift+;仅选中可见区域Alt +; (分号)跳至特定位置Ctrl + G/F5拼写检查F7重新计算F9 一般如数据特别庞大，请将“自动计算”设置为手动计算C&B从业人员Excel快捷键汇总 3Pivot Table数据透视表数据透视表12什么是数据透视表 数据透视表是一种对大量数据快速汇总和建立交叉列表的交互式表格,利用它可以很快地从不同角度对数据进行分类分类汇总

12、汇总. Count计数计数Average平均平均分类分类汇总汇总汇总汇总方式方式可以按行和列分类, 然后在中间汇总, 数字有多种方式（求和/平均等）, 文字则只能count（计数）13数据透视表使用步骤数据透视表使用步骤1 选中数据区域2 插入数据透视表3 选择要添加的报表字段4 根据需求进行字段与分组，并调整显示信息或运算方式14数据透视表技巧 通常我们都会记录每个离职员工的离职日期（具体到天）、部门等信息。但如何在几秒钟之内快速生成表？原数据表原数据表月度月度人数人数统计统计表表月度离月度离职职人数按部人数按部门门分布比例分布比例Exercise 215数据透视表技巧(续)l 从普通的数据

13、基础表到一般数据透视表从普通的数据基础表到一般数据透视表l 选择数据区域选择数据区域 插入插入 数据透视表，并选择数据透视表，并选择相应字段相应字段l 对离职对离职日期日期进行月度分组进行月度分组 ，并进行顺序调整，并进行顺序调整l 在对字段进行字段设置在对字段进行字段设置 值显示方式值显示方式 占同行数占同行数据总和的百分比，即可得出各月各部门离职人数占当据总和的百分比，即可得出各月各部门离职人数占当月总离职人数的百分比月总离职人数的百分比Exercise 216课后加强练习：数据透视表综合练习用数据透视表完成以下要求：用数据透视表完成以下要求：1. 每个销售人员的订购量是多少，并将订购额从

14、高到低排序2. 特定国家地区销售人员的订购额（将国家地区设置为报表筛选项）3. 按季度+月份统计人员的订购额，并显示季度汇总4. 按订购金额3%计算，每个销售人员应得多少提成，并求出每季度最大的前三项5. 其他：每位人员订购额占总订购额的比，等等Exercise 2+注意：本页主要为课后练习，透视表的具体操作步骤已附在相应的练习答案中。Basic Functions基本函数基本函数C&B操作中常用六大函数操作中常用六大函数-Vlookup、Match、If 、Ceiling（或（或Roundup或或Round）、）、 Indirect、Index18Function 函数技巧 常用函数

15、符号常用函数符号操作方法操作方法 连接多个单元格 & 符号函：Concatenate 绝对引用：$ 快快 F4F4切换引用状态：绝对、相对、混合引用 选择性粘贴选择性粘贴 所有员工工龄加1选择性粘贴运算功能 行列转置、跳开空格复制内容等选择性粘贴特殊功能 计数函数计数函数函：Count(a)/Countif/Countifs 计算数值单元格个数Count (range) 计算有值单元格个数Counta (range) 计算年龄大于30岁的员工人数Countif (range, criteria)函：countif 条件计数 条件函数与取值函数的组合条件函数与取值函数的组合函：If/mi

16、d,len,find 条件函数If (logical_test, value_if_true, value_if_false) 单元格长度Len (text) 身份证中截取生日Mid (Text, start_num, Num_chars)/ Left / right 取出已知日期的年、月、日Year(), Month(), 多条件求和 Sumif / SumifsExercise 319Function 函数技巧 单元格格式优化单元格格式优化操作方法操作方法 全大写Upper (text) 全小写Lower (text) 调整至正常状态Proper (text) 去除多余空格Trim (ra

17、nge) 引用引用(选学选学) 相对引用 索引引用Index (array, row_num, column_num) 绝对引用indirect (ref_text,a1) 部分引用Offset (reference, row, column)Exercise 3当我们对函数的使用方法有疑问事：第一，用F1键调出帮助；第二，问百度20从现有数据库调取数据4入职日期入职日期=vlookup( “张张三三” ”,“数数据表格据表格区区” ”,3 (年年龄龄列号列号)， 0)Formula:3 年龄年龄3 年龄年龄2 部门部门1 姓名姓名张三丰张三丰张三张三1 姓名姓名21table_array:

18、“employee info”从现有数据库调取数据（续）Vlookup 查查找取数找取数通过对共有指示符指示符 -“-“查找值查找值”的匹配，建立不同数据表之间的准确连接Vlookup ( lookup_value,table_array, Col_Index_Num, check) 指示符 数据表格区相对列数 匹配方式E.g. = VLOOKUP($A6,employee info!$A:$E,5,0) Indicator 指示符指示符22从现有数据库调取数据（续）注注意事项意事项:l两张表格的 指指示示符符 必须在 格格式上统式上统一一 (数值型 VS 文本型)，务必清理好空格和重复项l数

19、据表格区数据表格区首列必须为指示符指示符所在列l在复制公式前，记住要对指示符指示符以及数据表格区数据表格区所在列进行合理固定如格式不统一，比如即有文本又有数值：如格式不统一，比如即有文本又有数值：从文本到数值：公式从文本到数值：公式=value(A6) 或或 点击此处的智能标签点击此处的智能标签在进行对有在进行对有vlookup公式单元格进行复制前：公式单元格进行复制前： ($) 绝对引用绝对引用 快捷键快捷键: F4.23Vlookup公式的高阶用法1 模糊匹配Vlookup 在在绩效考绩效考核成绩匹配中核成绩匹配中的运的运用用已已知知： 评估分数与绩效等级的转换表、A部门员工绩效分数求求值

20、值： 计算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, 否则无法模糊匹配Convert TableIndicator 指示符指

21、示符Exercise 3.124Vlookup公式的高阶用法2 矩阵匹配年度调薪工具Vlookup 在在年度调年度调薪薪表格表格中中的运用的运用已已知知：1.员工绩效等级，其薪酬竞争力比率（CR值）2.根据上述两个条件设计的薪酬增长矩阵求求值值：员工调薪比率E.g. =VLOOKUP(K3,$A$2:$F$6,MATCH(J3,$B$2:$F$2,0)+1,1)1)调薪矩阵调薪矩阵vlookup指指示符示符Exercise 3.2Match求出所在列数求出所在列数所要求出的薪酬增长率所要求出的薪酬增长率25Vlookup公式的高阶用法2 矩阵匹配年度调薪工具（续）E.g. match ($K3

22、,$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，匹配返回数列中第一个第一个与查找值精确配对精确配对值的位置，数据表格区不需要排序Exercise 3.2调薪矩阵调薪矩阵Match的指示符的指示符26其他：年度调薪工具表制作基本流程为每位员工进行薪酬竞争力分析(得到市场比对率CR) =现有工资总额/相应级别P50分位值根据CR、员工绩效考核结果、调薪矩阵计算其调薪比率使用V

23、lookup+match 矩阵匹配法结合调薪方案调整个人测算报表，如入职时间、试用期、是否超同等级工资总额上限等对调薪前与调薪后，统计按照不同绩效和CR的人数及成本，特别关注调薪成本增长率当调薪测算OK时，调整打印列与打印格式，签阅报批执行，并与员工进行调薪沟通由负责人编制审由负责人编制审核调薪矩阵与调核调薪矩阵与调薪方案薪方案绩效考核绩效考核得出得出考核结果考核结果现有工资进现有工资进行行CR比对比对制制作报表进行个人测作报表进行个人测算统计算统计+Exercise 3.327其他常用应用课后自行练习：员工税后工资的计算 Use Vlookup Formula to calculate ta

24、x and the after tax salary for employees.使用Vlookup公式，计算员工应纳税额及税后工资Using Approximate matching使用模糊查询的方法Build-up a reference table (including quick deduction ratio, tax ratio within each salary range)需要建立参照表格(包含速算扣除率，税率区间等)Chart 图表技巧图表技巧29作图基本操作C&B常用图表类型饼饼中饼图中饼图百百分比堆叠柱状图分比堆叠柱状图平滑回归图平滑回归图点点折线图折线图组组合

25、图合图图图表条件格式化表条件格式化极极值的规避值的规避30饼中饼图 - 人工成本分析Exercise 4.131百分比堆叠柱状图 - 收入组成分析Exercise 4.232组合图（柱状散点图）- 薪酬架构回顾Exercise 4.3Closing结束语结束语34建立复杂EXCEL文件的基本原则 原则一：自变量单独成页原则一：自变量单独成页员工数据可单独成页；将所有假设信息及各种自变量放在一页； 原则二：计算区域内不得有任何自变量原则二：计算区域内不得有任何自变量所有计算区域的公式所连接的自变量都在同一页；将所有未来可能变化的数据都转变为一种假设，放在自变量页去； 原则三：将计算区域与测试区域

26、分开原则三：将计算区域与测试区域分开在大型EXCEL程序中可能在得出最后结果以前有多轮的测试，将测试与计算区域分在不同的页上，避免混淆； 原则四：尽可能简化公式或用简单的逻辑方法原则四：尽可能简化公式或用简单的逻辑方法在大型EXCEL程序中，跑一些程序可能需要花数小时甚至数天的时间，为了能够减少跑程序的时间，在设置公式的时候尽可能简化公式或者简化逻辑方法； 原则五：不要和其他原则五：不要和其他EXCEL文件链接文件链接一个EXCEL文件尽可能不要和另外的EXCEL文件链接，降低出错概率；如果数据表特别庞大，请设置为“手动计算”35Excel应用心得1. “双手开攻”: 减少冗余点击，3步内快捷

27、完成大部分工作 （例：添加多个月份独立工作表，迅速查找某月员工流动信息）2. “井然有序”: 海量数据模块化管理，轻松定位所需内容 (例：选中包含公式区域并保护，设定常用区域以便打印)3. “另辟蹊径”: 少用函数，多了解Excel自带功能（例：多方法找出不重名记录(标识重复次数)，汇总多月员工加班费，隔行插入工资表表头）4. “万无一失”: 屏蔽无效数据，保护公式及内容不被修改。(例：设置单元格内容不可见，不可输入重复数值或多余空格)5. “风随影动”: 组合绝对与相对引用，统一公式，随贴随用（例：图表自动标识极端值，用统一公式快速找到不同列信息）6. “财才兼备: 兼顾实效性和美观性的作图

28、（例：自动显示薪酬架构图中各点对应的人名，薪酬竞争力分析图）无他，唯手熟尔无他，唯手熟尔! ! Practices make perfect!36附录 1. 用Excel计算平均值，中位值 Average / Median:AVERAGE / MEDIAN(number1,number2,.) The arguments must either be numbers or be names*, arrays, or references that contain numbers. 自变量必须以数字或者是命名*，数组或者是包含数字的引用区域If an array or reference argument contains text