《EXCEL基础知识及在劳资工作中的常用技巧.ppt》由会员分享，可在线阅读，更多相关《EXCEL基础知识及在劳资工作中的常用技巧.ppt（40页珍藏版）》请在金锄头文库上搜索。
1、EXCELEXCEL基础知识及基础知识及在劳资工作中的常用技巧在劳资工作中的常用技巧20122012年年9 9月月2626日日一、EXCEL2007基本操作界面介绍二、常用快捷键及常用函数1、常用快捷键三、EXCEL2007常用功能点2、常用函数四、透视表介绍及基本操作（引用及函数概念介绍）标题栏标题栏菜单栏菜单栏工具栏工具栏名称框名称框 扩展箭头扩展箭头列标列标活动单元格活动单元格工作表标签工作表标签G22G:列表22：行号工作表插入按钮工作表插入按钮行号行号 滚动条滚动条 缩放控件缩放控件 页面视图控件页面视图控件工作表标签工作表标签滚动按钮滚动按钮快速访问工具栏快速访问工具栏Office
2、 Office 按钮按钮一、EXCEL2007基本操作界面介绍 帮助按钮帮助按钮插入函插入函数按钮数按钮函数编辑栏函数编辑栏一、EXCEL2007基本操作界面介绍EXCEL2003与2007交互界面：http:/ 如如“案例讲解案例讲解.xls”工作表：显示在工作簿窗口中的表格。显示在工作簿窗口中的表格。 如如“Sheet2”、“Sheet3”单元格：组成工作表的最小单位。组成工作表的最小单位。 如如“G22”单元格区域：连续的单元格。连续的单元格。 如如“A9:A16”行号： 如如“G22”的的22就是行号就是行号列表： 如如“G22”的的G就是列标就是列标公式：工作表中进行数值计算的等式工
3、作表中进行数值计算的等式 如如“=3*6-2 ”函数：预先定义，执行计算、分析等处理数据任务的特殊公式。预先定义，执行计算、分析等处理数据任务的特殊公式。 如求和如求和Sum（）、平均值（）、平均值Average（）、最大值（）、最大值Max（）等（）等基本概念介绍基本概念介绍一、EXCEL2007基本操作界面介绍基本概念介绍基本概念介绍算算术运算符运算符含含义（示例）（示例）+（加号）加法运算(3+3)（减号）减法运算(31)、负(1)*（星号）乘法运算(3*3)/（正斜线）除法运算(3/3)%（百分号）百分比(20%)（插入符号）乘幂运算(32)比比较运算符运算符含含义（示例）（示例）=（
4、等号）等于(A1=B1)（大于号）大于(A1B1)（小于号）小于(A1=（大于等于号）大于或等于(A1=B1)=（小于等于号）小于或等于(A1=B1)（不等号）不相等(A1B1)一、EXCEL2007基本操作界面介绍基本概念介绍基本概念介绍文本运算符文本运算符含含义（示例）（示例）&（和号）将两个文本值连接或串起来产生一个连续的文本值(“浙江”&“烟草)引用运算符引用运算符含含义（示例）（示例）:（冒号）区域运算符，产生对包括在两个引用之间的所有单元格的引用(B5:B15),（逗号）联合运算符，将多个引用合并为一个引用(SUM(B5:B15,D5:D15)（空格）交叉运算符产生对两个引用共有的
5、单元格的引用。(B7:D7C6:C8)二、常用快捷键及常用函数1、常用快捷键、常用快捷键快捷快捷键含含义Ctrl+C复制Ctrl+V粘贴Ctrl+X剪切Ctrl+A全选CTRL+P打印CTRL+F查找CTRL+H替代ALT+ENTER强行换行CTRL+滚轮放大缩小CTRL+S保存Ctrl+Z撤消上一步Ctrl+G定位F4重复上一步操作二、常用快捷键及常用函数引用的概念引用的概念类 型型解解 释绝对引用：单元格中的绝对单元格引用（例如$G$22）总是在指定位置引用单元格G22。如果如果公式所在公式所在单元格的位置改元格的位置改变，绝对引用的引用的单元格始元格始终保持不保持不变。相对引用：公式中的
6、相对单元格引用（例如A1）是基于包含公式和单元格引用的单元格的相对位置。如果公式所在如果公式所在单元格的位置改元格的位置改变，引用也随之改，引用也随之改变。混合引用： 具有绝对列和相对行，或是绝对行和相对列。绝对引用列采用$A1、$B1等形式。 案例讲解：案例讲解：单元格引用单元格引用 实际操作：实际操作：全省系统工资发放情况统计表全省系统工资发放情况统计表 F4键在修改引用类型中的妙用键在修改引用类型中的妙用二、常用快捷键及常用函数函数的概念函数的概念函数概念函数概念:是一些预定义的公式，它们使用一些称为参数的特定数值按特定的顺序或结构进行计算。函数结构：函数结构：=函数(参数,参数,)例如
7、：=sum(1,2,3)，=if(A1B1,”大于”,”不大于”)注意注意:“= ( ,”必须是英文的格式必须是英文的格式结构符结构符函数名称函数名称参数参数分隔符分隔符函数输入函数输入两种方式：两种方式：A选择函数B手工输入二、常用快捷键及常用函数2.1 Vlookup函数函数函数含函数含义：在表格或数值数组的首列查找指定的数值，并由此返回表格或数组中该数值所在行中指定列处的数值公公 式式：=VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)对对 应应 为为： =VLOOKUP（要查找的值，要查找的区域，返回数据在区域的第
8、几列数，精确匹配）说说 明：明： “要查找的值”一般情况下须位于“要查找的区域”的第一列“精确匹配”包括“1”和“0”，“0”即“一模一样”才进行匹配。常用范常用范围围： 人员信息查找、工资数据查找 例例 题题：案例操作二、常用快捷键及常用函数2.1 Vlookup函数函数二、常用快捷键及常用函数2.2 Datedif函数函数函数含函数含义： 返回两个日期之间的年月日间隔数语法公公 式式： =Datedif（start_date,end_date,unit）对对 应应 为为： =Datedif（起始日期,结束日期,返回类型）说说 明：明： 返回类型包括“Y”、“M”、“D”、“YM”、“YD”
9、、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
10、.2 Datedif函数函数起始日期结束日期返回类型函数名称结构符二、常用快捷键及常用函数2.3 Sum等函数等函数函数含函数含义：Sum（求和）、Average（平均值）、Max（最大值）、Min（最小值）公公 式式：=Sum(number1,number2,.)对对 应应 为为：=Sum(数字1,数字2,)说说 明：明： 常用范常用范围围： 日常工资统计、比赛分数统计 例例 题题：完成第三届卷烟商品营销职业技能竞赛评分表成绩统计二、常用快捷键及常用函数2.4 Rank等函数等函数函数含函数含义：返回一个数字在数字列表中的排位公公 式式：=RANK(number,ref,order)对对 应
11、应 为为： =RANK(需要排位的数字,为数字列表数组,排位的方式)说说 明：明： 排位的方式包括“1”和“0”，“1”为升序排列，“0”为降序排序常用范常用范围围： 民主测评排序、比赛成绩排序、绩效考核排序 例例 题题：完成第三届卷烟商品营销职业技能竞赛评分表统计名次排序二、常用快捷键及常用函数2.5 Left等函数等函数函数含函数含义：左截取函数Left、右截取函数Right、中间截取函数Mid公公 式式：=Left(string,length)、=Right(string,length)、=Mid（string,start_num,num_chars）对对 应应 为为：=Left(字符串
12、,截取个数)、=Right(字符串,截取个数)、=Mid（字符串,第几个开始,截取个数）说说 明：明： =left(“浙江烟草”,2) 显示为“浙江” =Mid（“浙江烟草”,2, 2） 显示为“江烟”常用范常用范围围： 身份证出生日期的截取、姓氏截取等 例例 题题：截取十八位身份证的出生年份、出生月份、出生日二、常用快捷键及常用函数2.6 Concatenate函数函数函数含函数含义：合并文本字符串。等同于” ”公公 式式：=Concatenate(text1,text2,.)等同于text1text2对对 应应 为为：=Concatenate(文本1,文本2,.)说说 明：明： =Conc
13、atenate(浙江,烟草） 显示为“浙江烟草” 等同于：=”浙江”烟草” =Concatenate(”钓鱼岛”,”是中国的” ,”固有领土”） 显示为“钓鱼岛是中国的固有领土” 等同于：=”钓鱼岛”&”是中国的”&”固有领土”常用范常用范围围： 出生日期的组合等 例例 题题：将上题截取的出生年份、出生月份、出生日组合成如下格式“1981-05-03”二、常用快捷键及常用函数2.7 IF函数函数函数含函数含义：执行真假值判断，根据逻辑计算的真假值，返回不同结果公公 式式：=IF(logical_test,value_if_true,value_if_false)对对 应应 为为：=IF(逻辑判
14、断,判断成立返回值,判断不成立返回值)说说 明：明： =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”)身份身份证提取提取性性
15、别=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,
16、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、对工资表筛选籍贯是杭州
17、、宁波、温州人员发放清单（文本筛选）；3、对工资表筛选个人实发3000元以上和高于平均收入清单（数字筛选）；4、对工资表筛选2012年1月籍贯非杭州且个人实发少于5000元的女性人员名单；5、对工资表筛选姓王且姓名只有两个字的人员名单；（“*”代表不限长度的任意内容，代表不限长度的任意内容，“？”代表仅为一个字符的任意内容）代表仅为一个字符的任意内容）三、EXCEL2007常用功能点3.3 分列分列三、EXCEL2007常用功能点3.4 删除重复项删除重复项三、EXCEL2007常用功能点3.5 数据有效性数据有效性三、EXCEL2007常用功能点3.6 合并计算合并计算三、EXCEL2007
18、常用功能点3.7 条件格式条件格式显示实发高于3000元的显示绩效工资在2000至4000元的显示籍贯是嘉兴地区的显示姓名重复的人员名单三、EXCEL2007常用功能点3.7 条件格式条件格式显示实发工资最高的十个显示实发工资高于平均值的思考：如仅需显示实发工资前6的如何操作？三、EXCEL2007常用功能点3.7 条件格式条件格式用数据条显示个人实发清除上述个人实发的数据条格式选择性粘贴选项选 项含含 义全部粘贴所复制的数据的所有单元格内容和格式数值仅粘粘贴在在单元格元格显示的数据示的数据值格式仅粘贴所复制数据的单元格格式转置将所复制数据的行将所复制数据的行变成列、列成列、列变成行成行三、E
19、XCEL2007常用功能点3.8 选择性粘贴选择性粘贴三、EXCEL2007常用功能点3.9 常用排版技巧常用排版技巧表格刚好有点大，想打表格刚好有点大，想打印在一个页面里！？印在一个页面里！？表格比较长，想每一页表格比较长，想每一页都打印标题行！？都打印标题行！？表格特别大，想固定住表格特别大，想固定住某一行或列进行查看！某一行或列进行查看！？如何给表格加入页码？如何给表格加入页码？三、EXCEL2007常用功能点3.9 常用排版技巧常用排版技巧如何将如何将工资表工资表按照按照部门分页打印？部门分页打印？如何快速地复制字体？如何快速地复制字体？1818位身份证号码输入后位身份证号码输入后显示
20、不正常？显示不正常？如何将文字竖直排版？如何将文字竖直排版？右键右键右键右键如何为数据注明信息？如何为数据注明信息？右键右键（或者在数字前加（或者在数字前加“”）四、透视表介绍及基本操作透视表的几个特点：透视表的几个特点：u是集排序、筛选、组合、汇总等多个功能于一体的交互式报表u有强大、灵活的数据处理功能u熟练掌握该项功能能给你的工作效率显著的提高u是excel的一个重点、难点u是衡量Excel掌握熟练程度的一个指标初识透视表初识透视表数据透视表的概念：数据透视表的概念：是一种对大量数据快速汇总和建立交叉列表的交互式动态表格， 能帮助用户分析、组织数据。四、透视表介绍及基本操作制作简单透视表制
21、作简单透视表第一步：选择数据区域第二步：插入数据透视表数据透视表第三步：确定四、透视表介绍及基本操作字段列表字段列表字段控制窗口字段控制窗口编辑显示窗口编辑显示窗口四、透视表介绍及基本操作第四步：选择字符段第四步：选择字符段第五步：调整顺序第五步：调整顺序手工筛手工筛选按钮选按钮四、透视表介绍及基本操作问 题 解决解决办法法 字段列表消失了怎么办？点击行、列字段区域内的任一位置即可。点击区域外位置则消失如何调整行、列字段顺序？可以。可以。点击字段，将箭头移至单元格黄色边框上，当箭头变成十字形点击鼠标左键即可拖动。能否对行字段进行合并？可以。可以。选择连续的字符段，点击“右键”选择“组合”，在“
22、编辑栏”编辑组合字符段名称，点击组合字符段名称前的“”即可。是否可以取消组合字段？可以。可以。选择组合字符段，点击“右键”选择“取消组合”行符段区域只能选一个字段吗？不是。不是。页、行、列、值字段均可多选。数据区域更新后透视表会自动更新吗？不会。不会。点击编辑显示窗口，点击“右键”选择“刷新”。值字段区域都可以进行哪些基本统计？包括计数、合计、平均值、最大值、最小值等。能否查看统计数据对应的清单？可以。可以。双击对应的数据即可。四、透视表介绍及基本操作模模拟操作操作一、建立工资表的透视表1、完成行字段为“部门”、列字段为“职级”的平均个人实发透视表 2、对上述进行筛选（选择男性，月份为2012年1月） 3、对行字段顺序进行调整（按照第四至第一专卖管理所顺序）排序 4、合并列字段“科级”和“股级” 为“股级及以上” 5、将“平均个人实发”改为计数二、新建一张透视表，统计个人实发3000至4000元的人数情况（行字段为“部门”、列字段为“职级”）谢谢大家！谢谢大家！
