EXCEL基础知识及在劳资工作中的常用技巧.ppt

单击此处编辑母版标题样式,单击此处编辑母版文本样式,第二级,第三级,第四级,第五级,*,*,EXCEL,基础知识及,在劳资工作中的常用技巧,2012,年,9,月,26,日,一、,EXCEL2007,基本操作界面介绍,二、常用快捷键及常用函数,1,、常用快捷键,三、,EXCEL2007,常用功能点,2,、常用函数,四、透视表介绍及基本操作,（引用及函数概念介绍）,标题栏,菜单栏,工具栏,名称框,扩展箭头,列标,活动单元格,工作表标签,G22,G:,列表,22,：行号,工作表插入按钮,行号,滚动条,缩放控件,页面视图控件,工作表标签,滚动按钮,快速访问工具栏,Office,按钮,一、,EXCEL2007,基本操作界面介绍,帮助按钮,插入函,数按钮,函数编辑栏,一、,EXCEL2007,基本操作界面介绍,EXCEL2003,与,2007,交互界面：,号：,如“,G22”,的,22,就是行号,列 表：,如“,G22”,的,G,就是列标,公 式：,工作表中进行数值计算的等式 如“,=3*6-2,”,函 数：,预先定义，执行计算、分析等处理数据任务的特殊公式。,如求和,Sum,（）、平均值,Average,（）、最大值,Max,（）等,基本概念介绍,一、,EXCEL2007,基本操作界面介绍,基本概念介绍,算术运算符,含义（示例）,+,（加号）,加法运算,(3+3),（减号）,减法运算,(31),、,负,(1),*,（星号）,乘法运算,(3*3),/,（正斜线）,除法运算,(3/3),%,（百分号）,百分比,(20%),（插入符号）,乘幂运算,(32),比较运算符,含义（示例）,=,（等号）,等于,(A1=B1),（大于号）,大于,(A1B1),（小于号）,小于,(A1=,（大于等于号）,大于或等于,(A1=B1),=,（小于等于号）,小于或等于,(A1B1,”,大于,”,”,不大于,”),注意,:,“,=,(,”必须是英文的格式,结构符,函数名称,参数,分隔符,函数输入,两种方式：,A,选择,函数,B,手工,输入,二、常用快捷键及常用函数,2.1,Vlookup,函数,函数含义在表格或数值数组的首列查找指定的数值，并由此返回表格或数组中该数值所在行中指定列处的数值,公,式,：,=VLOOKUP,(,lookup_value,table_array,col_index_num,range_lookup,),对 应 为,：,=VLOOKUP,（要查找的值，要查找的区域，返回数据在区域的第几列数，精确匹配）,说,明：,“要查找的值,”,一般情况下,须,位于“要查找的区域”的第一列,“精确匹配”包括“1”和,“0”,，,“,0,”,即,“一模一样”,才进行匹配,。,常用范围：,人员信息查找、工资数据查找,例,题,：,案例操作,二、常用快捷键及常用函数,2.1,Vlookup,函数,二、常用快捷键及常用函数,2.2,Datedif,函数,函数含义,：,返回两个日期之间的年,月,日间隔数语法,公,式,：,=Datedif（start_date,end_date,unit）,对 应 为,：,=Datedif（,起始日期,结束日期,返回类型）,说,明：,返回类型包括,“Y”,、,“M”,、,“D”,、,“YM”,、,“YD”,、,MD,Y,时间段中的整年数，,M,时间段中的整月数，,D,时间段中的天数。,YM,日期中月数的差。忽略日期中的日和年。,YD,日期中天数的差。忽略日期中的年。,MD,日期中天数的差。忽略日期中的月和年。,常用范围：,工龄计算、年龄计算、年休计算。,例,题,：,计算出生日期为,1983-4-1,人的年龄,公式：,=DATEDIF(1973-4-1,TODAY(),Y),计算日期为,1983-4-1,和当前日期的间隔月份数,公式：,=DATEDIF(1973-4-1,TODAY(),M),计算日期为,1983-4-1,和当前日期的间隔天数,公式：,=DATEDIF(1973-4-1,TODAY(),D),二、常用快捷键及常用函数,2.2,Datedif,函数,起始日期,结束日期,返回类型,函数名称,结构符,二、常用快捷键及常用函数,2.3,Sum,等函数,函数含义,：,Sum,（求和）、,Average,（平均值）,、,Max,（最大值,）、,Min,（,最小值,）,公,式,：,=Sum(number1,number2,.),对 应 为,：,=,Sum(,数字,1,数字,2,),说,明：,常用范围：,日常工资统计、比赛分数统计,例,题,：,完成,第三届卷烟商品营销职业技能竞赛评分表,成绩统计,二、常用快捷键及常用函数,2.4,Rank,等函数,函数含义,：,返回一个数字在数字列表中的排位,公,式,：,=RANK(number,ref,order),对 应 为,：,=,RANK(,需要排位的数字,为数字列表数组,排位的方式,),说,明：,排位的方式包括“,1,”和“,0,”，,“,1,”为,升序排列，,“,0,”为降序排序,常用范围：,民主测评排序、比赛成绩排序、绩效考核排序,例,题,：,完成,第三届卷烟商品营销职业技能竞赛评分表统计,名次排序,二、常用快捷键及常用函数,2.5,Left,等函数,函数含义,：,左截取函数,Left,、右截取函数,Right,、中间截取函数,Mid,公,式,：,=Left(string,length),、,=Right(string,length),、,=Mid,（,string,start_num,num_chars,）,对 应 为,：,=Left(,字符串,截取个数,),、,=Right(,字符串,截取个数,),、,=Mid,（字符串,第几个开始,截取个数）,说,明：,=left(“,浙江烟草”,2),显示为“浙江”,=Mid,（,“,浙江烟草”,2,2,）显示为“江烟”,常用范围：,身份证出生日期的截取、姓氏截取等,例,题,：,截取十八位身份证的出生年份、出生月份、出生日,二、常用快捷键及常用函数,2.6,Concatenate,函数,函数含义,：,合并文本字符串。等同于,”,”,公,式,：,=Concatenate(text1,text2,.),等同于,text1,text2,对 应 为,：,=,Concatenate(,文本,1,文本,2,.),说,明：,=,Concatenate,(,浙江,烟草,）,显示为“浙江烟草”,等同于：,=,”,浙江,”,”,烟草,”,=,Concatenate,(,”,钓鱼岛,”,”,是中国的,”,”,固有领土,”,）显示为“钓鱼岛是中国的固有领土”,等同于：,=,”,钓鱼岛,”&”,是中国的,”&”,固有领土,”,常用范围：,出生日期的组合等,例,题,：,将上题截取的出生年份、出生月份、出生日组合成如下格式“,1981-05-03,”,二、常用快捷键及常用函数,2.7,IF,函数,函数含义,：,执行真假值判断，根据逻辑计算的真假值，返回不同结果,公,式,：,=IF(logical_test,value_if_true,value_if_false),对 应 为,：,=IF(,逻辑判断,判断成立返回值,判断不成立返回值,),说,明：,=if,(A1=60,”,及格,”,”,不及格,”,）,即如果,A1,单元格的值大于或等于,60,，返回及格，否则返回不及格,常用范围：,非常广泛！,例,题,：,二、常用快捷键及常用函数,2.7,IF,函数,二、常用快捷键及常用函数,2.8,常用公式组合,功 能,公 式,个税计算公式,=MAX(,A1,-3500)*0.03,0.1,0.2,0.25,0.3,0.35,0.45-0,105,555,1005,2755,5505,13505,0),出生日期计算年龄,=DATEDIF(,A1,TODAY(),”Y”),身份证提取,性别,=IF(LEN(,A1,)=15,IF(MOD(MID(,A1,15,1),2)=1,男,女,),IF(MOD(MID(,A1,17,1),2)=1,男,女,),身份证提取出生,日期,=IF(LEN(,A1,)=15,CONCATENATE(19,MID(,A1,7,2),-,MID(,A1,9,2),-,MID(,A1,11,2),CONCATENATE(MID(,A1,7,4),-,MID(,A1,11,2),-,MID(,A1,13,2),身份证计算,年龄,=DATEDIF(IF(LEN(,A1,)=15,CONCATENATE(19,MID(,A1,7,2),-,MID(,A1,9,2),-,MID(,A1,11,2),CONCATENATE(MID(,A1,7,4),-,MID(,A1,11,2),-,MID(,A1,13,2),TODAY(),y),三、,EXCEL2007,常用功能点,3.1,排序功能,排序功能,1,、对,工资表,按照职级、系数分别进行排序；,2,、对,工资表,按照姓名的拼音顺序排序；,3,、对,工资表,按照职级升序、系统降序排序；,4,、对,工资表,按照籍贯顺序（杭州、宁波、温州、嘉兴、湖州、绍兴、金华、衢州、丽水、台州、舟山）、职级升序、系数升序排序。,三、,EXCEL2007,常用功能点,3.2,筛选功能,筛选功能,1,、对,工资表,筛选,2012,年,2,月工资发放清单（日期筛选）；2,、对,工资表,筛选籍贯是杭州、宁波、温州人员发放清单（文本筛选）；,3,、对,工资表,筛选个人实发,3000,元以上和高于平均收入清单（数字筛选）；,4,、对,工资表,筛选,2012,年,1,月籍贯非杭州且个人实发少于,5000,元的女性人员名单；,5,、对,工资表,筛选姓王且姓名只有两个字的人员名单；,（“,*,”代表不限长度的任意内容，“？”代表仅为一个字符的任意内容）,三、,EXCEL2007,常用功能点,3.3,分列,三、,EXCEL2007,常用功能点,3.4,删除重复项,三、,EXCEL2007,常用功能点,3.5,数据有效性,三、,EXCEL2007,常用功能点,3.6,合并计算,三、,EXCEL2007,常用功能点,3.7,条件格式,显示实发高于,3000,元的,显示绩效工资在,2000,至,4000,元的,显示籍贯是嘉兴地区的,显示姓名重复的人员名单,三、,EXCEL2007,常用功能点,3.7,条件格式,显示实发工资最高的十个,显示实发工资高于平均值的,思考：如仅需显示实发工资前,6,的如何操作？,三、,EXCEL2007,常用功能点,3.7,条件格式,用数据条显示个人实发,清除上述个人实发的数据条格式,选择性粘贴选项,选 项,含 义,全部,粘贴所复制的数据的所有单元格内容和格式,数值,仅粘贴在单元格显示的数据值,格式,仅粘贴所复制数据的单元格格式,转置,将所复制数据的行变成列、列变成行,三、,EXCEL2007,常用功能点,3.8,选择性粘贴,三、,EXCEL2007,常用功能点,3.9,常用排版技巧,表格刚好有点大，想打印在一个页面里！？,表格比较长，想每一页都打印标题行！？,表格特别大，想固定住某一行或列进行查看！？,如何给表格加入页码？,三、,EXCEL2007,常用功能点,3.9,常用排版技巧,如何将,工资表,按照部门分页打印？,如何快速地复制字体？,18,位身份证号码输入后显示不正常？,如何将文字竖直排版？,右键,右键,如何为数据注明信息？,右键,（或者在数字前加,“,”,）,四、透视表介绍及基本操作,透视表的几个特点：,是集排序、筛选、组合、汇总等多个功能于一体的交互式报表,有强大、灵活的数据处理功能,熟练掌握该项功能能给你的工作效率显著的提高,是,excel,的一个重点、难点,是衡量,Excel,掌握熟练程度的一个指标,初识透视表,数据透视表的概念：,是一种对大量数据快速汇总和建立交叉列表的交互式动态表格，能帮助用户分析、组织数据。,四、透视表介绍及基本操作,制作简单透视表,第一步：选择数据区域,第二步：,插入,数据透视表,数据透视表,第三步：确定,四、透视表介绍及基本操作,字段列表,字段控制窗口,编辑显示窗口,四、透视表介绍及基本操作,第四步：选择字符段,第五步：调整顺序,手工筛选按钮,四、透视表介绍及基本操作,问 题,解决办法,字段列表消失了怎么办？,点击行、列字段区域内的任一位置即可。点击区域外位置则消失,如何调整行、列字段顺序？,可以。,点击字段，将箭头移至单元格黄色边框上，当箭头变成十字形点击鼠标左键即可拖动。,能否对行字段进行合并？,可以。,选择连续的字符段，点击“右键”选择“组合”，在“编辑栏”编辑组合字符段名称，点击组合字符段名称前的“”即可。,是否可以取消组合字段？,可以。,选择组合字符段，点击“右键”选择“取消组合”,行符段区域只能选一个字段吗？,不是。,页、行、列、值字段均可多选。,数据区域更新后透视表会自动更新吗？,不会。,点击编辑显示窗口，点击“右键”选择“刷新”。,值字段区域都可以进行哪些基本统计？,包括计数、合计、平均值、最大值、最小值等。,能否查看,统计数据对应,的清单？,可以。,双击对应的数据即可。,四、透视表介绍及基本操作,模拟操作,一、建立,工资表,的透视表,1,、完成行字段为“部门”、列字段为“职级”的平均个人实发透视表,2,、对上述进行筛选（选择男性，月份为,2012,年,1,月）,3,、对行字段顺序进行调整（按照第四至第一专卖管理所顺序）排序,4,、合并列字段“科级”和“股级”为“股级及以上”,5,、将“,平均个人实发”改为计数,二、新建一张透视表，统计个人实发,3000,至,4000,元的人数情况（,行字段为“部门”、列字段为“职级”）,谢谢大家！,