Excel技能培训[].pptx

Excel Training2EfficientInputting录入技巧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引用函数数据透视表数据透数据透视3Excel2007工具栏速览Home：系统设置自定自定义快捷快捷键栏：快捷操作开始开始：基本操作、单元格格式插入插入：图、表、各类对象页面布局面布局：打印设置公式公式：公式数据数据：排序、筛选、组合审阅：批注、保护4录入技巧信息录入常用功能信息录入常用功能普遍操作普遍操作高效操作高效操作录入大量固定位数小数小键盘盲打Excel选项高级自动插入小数点修改输入内容鼠标双击单元格快*F2修改/插入注释右键-插入/编辑注释快Shift+F2另类注释：数据有效性特定区域输入相同内容复制，粘贴区域选择,输入,快Ctrl+Enter复制左/上单元格内容复制，粘贴快Ctrl+R/D单元格内换行?快Alt+Enter输入当前日期手工输入快Ctrl+;录入元天等数量单位直接在单元格内数入文字保留数值,单元格格式自定义录入年月日星期几格式?单元格格式自定义组合：yyyy年m月d日星期aaa5页面优化技巧工具工具栏设置置操作方法操作方法 注注释熟悉工具栏及菜单基本设置右键点选菜单或工具栏区域自定义设置页面面设置置去除滚动条、工作表标签、网格线视图编辑栏、网格线、标题Excel 选项高级此工作薄的显示选项最大化显示所选区域缩放级别修改工作表标签右键选中标签，着色或改名改名快:Alt,O,H,R视图设置置保存特定区域视图视图自定义视图常用区域操作对常用区域进行自定义视图全屏显示、分页预览视图全屏显示、分页预览打印打印设置置自定义打印区域文件打印区域设置打印区域常用区域操作设置多工作表连续打印页面设置页面起始页拼接其他文件，具体请百度设置分页打印(手工插入分页符)插入分页符设置抬头行、页眉页脚等文件页面设置页眉页脚设置错误值、图表不打印页面设置工作表错误单元格打印内事不决内事不决问百度，百度，这几几页的更的更为详细的方法的方法请自行百度自行百度Exercise 16定位技巧普通普通单元格区域的快速定位元格区域的快速定位操作方法操作方法注注释选择连续单元格Shift+鼠标/方向键大区域操作选择不不连续单元格Ctrl+鼠标小区域操作跳跳跃于连续单元格区域两端Ctrl+方向键大区域操作选择整行整行/整列非空整列非空单元格元格Ctrl+Shift+方向键大区域操作选择整个有整个有值表格表格Ctrl+Shift+*大区域操作特殊特殊单元格区域的准确定位元格区域的准确定位快快F5选择特殊区域(如：公式区域)编辑-定位快F5选中所有含公式区域用汉字名称代替单元格地址插入-名称-定名常用区域操作条件格式：条件格式：突出显示符合要求的单元格格式-条件格式例：隔行标识颜色查找与替找与替换快快:ctrl+F/Ctrl+H特殊内容查找(红色字体,空格等）查找-选项例：去除所有0值筛选-自自动筛选快快: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:数值判断下拉框设置区域定名管理下拉框信息设置工作表保置工作表保护、单元格可元格可视性性工作簿保护工具选项安全性另存工具一般选项单元格可视性设置单元格格式保护隐藏单元格/工作表/簿保护1.单元格格式保护锁定2.工作表/簿保护深度隐藏VBA-VeryHidden快速进入VBAAlt+F11Visible2veryhidden工作表快速工作表快速备份份复制、移动当前工作表右键工作表标签移动或复制快：Ctrl+鼠标拖动标签Exercise 18C&B从业人员Excel快捷键汇总1操作操作快捷键快捷键复制/剪切/粘贴Ctrl+C/Ctrl+X/Ctrl+V查找/替换Ctrl+F/Ctrl+H多张Excel表格间的切换Ctrl+Tab前后工作表间的切换Ctrl+PgUp&PgDn快速选中整行、整列有值单元格CtrlandShift+DirectionKey用下拉框挑选内容(仅对文本格式有效)Alt+单元格内的换行Alt+Enter取消上步操作/恢复上步操作Ctrl+Z/Y单元格格式(字体,对齐方式,边框等):Ctrl+1(FormatPainterFormatPainter)9C&B从业人员Excel快捷键汇总2操作操作快捷键快捷键新建/保存/另存Ctrl+N/Ctrl+S/F12在前一页插入新工作表Shift+F11编辑单元格F2插入/编辑注释Shift+F2复制上/左单元格Ctrl+D/R单元格、区域定名Ctrl+Shift+F3显示公式栏Shift+F3显示公式Ctrl+特定区域输入相同内容:1.选择区域 2.录入要录入的内容 3.Ctrl+Enter这几几页快捷快捷键中，打星星的是中，打星星的是强烈推荐烈推荐记住的住的,能极大提高效率。能极大提高效率。10操作操作快捷键快捷键隐藏当前单元格所在行列Ctrl+0(Zero)orCtrl+9取消隐藏当前单元格所在行列Ctrl+Shift+0orCtrl+Shift+9添加行、列Ctrl+Shift+“+”删除行、列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什么是数据透视表数据透视表是一种对大量数据快速汇总和建立交叉列表的交互式表格,利用它可以很快地从不同角度对数据进行分分类汇总.Count计数数Average平均平均分分类汇总汇总方式方式可以按行和列分类,然后在中间汇总,数字有多种方式（求和/平均等）,文字则只能count（计数）13数据透视表使用步骤数据透视表使用步骤1选中数据区域2插入数据透视表3选择要添加的报表字段4根据需求进行字段与分组，并调整显示信息或运算方式14数据透视表技巧通常我们都会记录每个离职员工的离职日期（具体到天）、部门等信息。但如何在几秒钟之内快速生成各部各部各部各部门门在月度或季度的在月度或季度的在月度或季度的在月度或季度的总总离离离离职职人数人数人数人数表？原数据表原数据表月度月度人数人数统计表表月度离月度离职人数按部人数按部门分布比例分布比例Exercise 215数据透视表技巧(续)l从普通的数据基从普通的数据基础表到一般数据透表到一般数据透视表表l选择数据区域数据区域 插入插入 数据透数据透视表，并表，并选择相相应字段字段l对离离职日期日期进行月度分行月度分组，并，并进行行顺序序调整整l在在对字段字段进行字段行字段设置置 值显示方式示方式 占同行数占同行数据据总和的百分比，即可得出各月各部和的百分比，即可得出各月各部门离离职人数占当人数占当月月总离离职人数的百分比人数的百分比Exercise 216课后加强练习：数据透视表综合练习用数据透用数据透视表完成以下要求：表完成以下要求：1.每个销售人员的订购量是多少，并将订购额从高到低排序2.特定国家地区销售人员的订购额（将国家地区设置为报表筛选项）3.按季度+月份统计人员的订购额，并显示季度汇总4.按订购金额3%计算，每个销售人员应得多少提成，并求出每季度最大的前三项5.其他：每位人员订购额占总订购额的比，等等Exercise 2+注意：本页主要为课后练习，透视表的具体操作步骤已附在相应的练习答案中。Basic Functions基本函数基本函数C&B操作中常用六大函数操作中常用六大函数-Vlookup、Match、If、Ceiling（或（或Roundup或或Round）、）、Indirect、Index18Function函数技巧常用函数符号常用函数符号操作方法操作方法连接多个单元格&符号函：Concatenate绝对引用：$快快 F4F4切换引用状态：绝对、相对、混合引用选择性粘性粘贴所有员工工龄加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/SumifsExercise 319Function函数技巧单元格格式元格格式优化化操作方法操作方法全大写Upper(text)全小写Lower(text)调整至正常状态Proper(text)去除多余空格Trim(range)引用引用(选学学)相对引用索引引用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 姓名姓名212024/5/21周二2122table_array:“employee info”从现有数据库调取数据（续）Vlookup 查找取数找取数通过对共有指示符指示符-“查找找值”的匹配，建立不同数据表之间的准确连接Vlookup(lookup_value,table_array,Col_Index_Num,check)指示符数据表格区相对列数 匹配方式E.g.=VLOOKUP($A6,employee info!$A:$E,5,0)Indicator 指示符指示符23从现有数据库调取数据（续）注注意事意事项:l两张表格的指指示示符符 必须在格格式上式上统一一(数值型VS文本型)，务必清理好空格和重复项l数据表格区数据表格区首列必须为指示符指示符所在列l在复制公式前，记住要对指示符指示符以及数据表格区数据表格区所在列进行合理固定如格式不如格式不统一，比如即有文本又有数一，比如即有文本又有数值：从文本到数从文本到数值：公式：公式=value(A6)或或点点击此此处的智能的智能标签在在进行行对有有vlookup公式公式单元格元格进行复制前：行复制前：($)绝对引用引用快捷快捷键:F4.24Vlookup公式的高阶用法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,否则无法模糊匹配Convert TableIndicator 指示符指示符Exercise 3.125Vlookup公式的高阶用法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求出所在列数求出所在列数所要求出的薪酬增所要求出的薪酬增长率率26Vlookup公式的高阶用法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，匹配返回数列中第一个第一个与查找值精确配精确配对值的位置，数据表格区不需要排序Exercise 3.2调薪矩薪矩阵Match的指示符的指示符27其他：年度调薪工具表制作基本流程为每位员工进行薪酬竞争力分析(得到市场比对率CR)=现有工资总额/相应级别P50分位值根据CR、员工绩效考核结果、调薪矩阵计算其调薪比率使用Vlookup+match矩阵匹配法结合调薪方案调整个人测算报表，如入职时间、试用期、是否超同等级工资总额上限等对调薪前与调薪后，统计按照不同绩效和CR的人数及成本，特别关注调薪成本增长率当调薪测算OK时，调整打印列与打印格式，签阅报批执行，并与员工进行调薪沟通由由负责人人编制制审核核调薪矩薪矩阵与与调薪方案薪方案绩效考核绩效考核得出得出考核结果考核结果现有工资进现有工资进行行CR比对比对制制作报表进行个人测作报表进行个人测算统计算统计+Exercise 3.328其他常用应用课后自行练习：员工税后工资的计算UseVlookupFormulatocalculatetaxandtheaftertaxsalaryforemployees.使用Vlookup公式，计算员工应纳税额及税后工资UsingApproximatematching使用模糊查询的方法Build-upareferencetable(includingquickdeductionratio,taxratiowithineachsalaryrange)需要建立参照表格(包含速算扣除率，税率区间等)Chart 图表技巧表技巧30作图基本操作C&B常用图表类型饼中中饼图百百分比堆叠柱状分比堆叠柱状图平滑回平滑回归图点点折折线图组合合图图表条件格式化表条件格式化极极值的的规避避31饼中饼图-人工成本分析Exercise 4.132百分比堆叠柱状图-收入组成分析Exercise 4.233组合图（柱状散点图）-薪酬架构回顾Exercise 4.3Closing结束束语35建立复杂EXCEL文件的基本原则原原则一：自一：自变量量单独成独成页员工数据可单独成页；将所有假设信息及各种自变量放在一页；原原则二：二：计算区域内不得有任何自算区域内不得有任何自变量量所有计算区域的公式所连接的自变量都在同一页；将所有未来可能变化的数据都转变为一种假设，放在自变量页去；原原则三：将三：将计算区域与算区域与测试区域分开区域分开在大型EXCEL程序中可能在得出最后结果以前有多轮的测试，将测试与计算区域分在不同的页上，避免混淆；原原则四：尽可能四：尽可能简化公式或用化公式或用简单的的逻辑方法方法在大型EXCEL程序中，跑一些程序可能需要花数小时甚至数天的时间，为了能够减少跑程序的时间，在设置公式的时候尽可能简化公式或者简化逻辑方法；原原则五：不要和其他五：不要和其他EXCEL文件文件链接接一个EXCEL文件尽可能不要和另外的EXCEL文件链接，降低出错概率；如果数据表特别庞大，请设置为“手动计算”36Excel应用心得1.“双手开攻”:减少冗余点击，3步内快捷完成大部分工作（例：添加多个月份独立工作表，迅速查找某月员工流动信息）2.“井然有序”:海量数据模块化管理，轻松定位所需内容(例：选中包含公式区域并保护，设定常用区域以便打印)3.“另辟蹊径”:少用函数，多了解Excel自带功能（例：多方法找出不重名记录(标识重复次数)，汇总多月员工加班费，隔行插入工资表表头）4.“万无一失”:屏蔽无效数据，保护公式及内容不被修改。(例：设置单元格内容不可见，不可输入重复数值或多余空格)5.“风随影动”:组合绝对与相对引用，统一公式，随贴随用（例：图表自动标识极端值，用统一公式快速找到不同列信息）6.“财才兼备:兼顾实效性和美观性的作图（例：自动显示薪酬架构图中各点对应的人名，薪酬竞争力分析图）无他，唯手熟无他，唯手熟尔!Practices make perfect!37附录1.用Excel计算平均值，中位值Average/Median:AVERAGE/MEDIAN(number1,number2,.)Theargumentsmusteitherbenumbersorbenames*,arrays,orreferencesthatcontainnumbers.自变量必须以数字或者是命名*，数组或者是包含数字的引用区域Ifanarrayorreferenceargumentcontainstext,logicalvalues(trueorfalse),oremptycells,thosevaluesareignored;however,cellswiththevaluezeroareincluded.如果数列中的自变量包含文本，逻辑数值(正确或错误)或者空单元格时，不加入平均值的计算；但”0”单元格加入计算。38附录2.用Excel计算百分位值Percentile/Quartile:PERCENTILE/Quartile(array,k)Arrayisthearrayorrangeofdatathatdefinesrelativestanding.数组即为界定相对名次的数组或一个区域的数值Kisthepercentilevalueintherange0.1,inclusive.参量即为0和1区域内的数值,包含0和1.E.g.30%ile=Percentile(array,0.3)例：三十百分位：=Percentile(数组,0.3)39附录3.数据录入相关技巧DefineName:Defineanexclusivenametoanarrayorarangeofcells区域命名：为一个数组或一个区域的单元格指定唯一的名字Why“DefineName”:Simplifyformula,categorizedatabase为何要“区域命名”:简化公式引用参数，拆分管理海量数据Process流程：1.Selectanarrayorarangeofcells;选择一个数组或者单元格区域2.“Insert”Name”Define”;“插入”“命名”“定义”3.Inthedialogbox,inputaname在对话框中输入命名OrchangehereTips技巧:1.Shortcutkey快捷键:Ctrl+Shift+F32.“Blank”isunacceptableinName命名不可有空格，可用下划线代替402024/5/21周二40