EXCEL基础知识及在工作中的常用技巧

1、EXCELEXCEL基础知识及基础知识及在工作中的常用技巧在工作中的常用技巧2一、EXCEL2007基本操作界面介绍二、常用快捷键及常用函数 1、常用快捷键三、EXCEL2007常用功能点 2、常用函数四、透视表介绍及基本操作 （引用及函数概念介绍）标题栏标题栏菜单栏菜单栏工具栏工具栏名称框名称框 扩展箭头扩展箭头列标列标活动单元格活动单元格工作表标签工作表标签G22G:列表 22：行号工作表插入按钮工作表插入按钮行号行号 滚动条滚动条 缩放控件缩放控件 页面视图控件页面视图控件工作表标签工作表标签滚动按钮滚动按钮快速访问工具栏快速访问工具栏OfficeOffice 按钮按钮一、EXCEL20

2、07基本操作界面介绍 帮助按钮帮助按钮插入函插入函数按钮数按钮函数编辑栏函数编辑栏4一、EXCEL2007基本操作界面介绍工作薄：Excel文档就是工作簿。文档就是工作簿。 如如“案例讲解案例讲解.xls”工作表：显示在工作簿窗口中的表格。显示在工作簿窗口中的表格。 如如“Sheet2”、“Sheet3”单元格：组成工作表的最小单位。组成工作表的最小单位。 如如“G22”单元格区域：连续的单元格。连续的单元格。 如如“A9:A16”行 号： 如如“G22”的的22就是行号就是行号列 表： 如如“G22”的的G就是列标就是列标公 式：工作表中进行数值计算的等式工作表中进行数值计算的等式 如如“=

3、3*6-2 ”函 数：预先定义，执行计算、分析等处理数据任务的特殊公式。预先定义，执行计算、分析等处理数据任务的特殊公式。 如求和如求和Sum（）、平均值（）、平均值Average（）、最大值（）、最大值Max（）等（）等基本概念介绍基本概念介绍5一、EXCEL2007基本操作界面介绍基本概念介绍基本概念介绍算术运算符算术运算符含义（示例）含义（示例）+（加号）加法运算 (3+3)（减号）减法运算 (31)、负 (1)*（星号）乘法运算 (3*3)/（正斜线）除法运算 (3/3)%（百分号）百分比 (20%)（插入符号）乘幂运算 (32)比较运算符比较运算符含义（示例）含义（示例）=（等号）等

4、于 (A1=B1)（大于号）大于 (A1B1)（小于号）小于 (A1=（大于等于号）大于或等于 (A1=B1)=（小于等于号）小于或等于 (A1=B1)（不等号）不相等 (A1B1)6一、EXCEL2007基本操作界面介绍基本概念介绍基本概念介绍文本运算符文本运算符含义（示例）含义（示例）&（和号）将两个文本值连接或串起来产生一个连续的文本值 (“宁波”&“轨道交通)引用运算符引用运算符含义（示例）含义（示例）:（冒号）区域运算符，产生对包括在两个引用之间的所有单元格的引用 (B5:B15),（逗号）联合运算符，将多个引用合并为一个引用 (SUM(B5:B15,D5:D15) （空格）交叉运算

5、符产生对两个引用共有的单元格的引用。(B7:D7 C6:C8)7二、常用快捷键及常用函数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重复上一步操作8二、常用快捷键及常用函数引用的概念引用的概念类类 型型解解 释释绝对引用：单元格中的绝对单元格引用（例如 $G$22）总是在指定位置引用单元格G22。 如果如果公式所在单元格的位置改变，绝对引用的单元格始终保持不变。公式所在单元格的

6、位置改变，绝对引用的单元格始终保持不变。相对引用：公式中的相对单元格引用（例如 A1）是基于包含公式和单元格引用的单元格的相对位置。如果公式所在单元格的位置改变，引用也随之改变。如果公式所在单元格的位置改变，引用也随之改变。混合引用： 具有绝对列和相对行，或是绝对行和相对列。绝对引用列采用$A1、$B1等形式。9二、常用快捷键及常用函数函数的概念函数的概念函数概念函数概念:是一些预定义的公式，它们使用一些称为参数的特定数值按特定的顺序或结构进行计算。函数结构：函数结构：=函数(参数,参数,)例如：=sum(1,2,3)， =if(A1B1,”大于”,”不大于”)注意注意:“= ( ,”必须是英

7、文的格式必须是英文的格式结构符结构符函数名称函数名称参数参数分隔符分隔符函数输入函数输入两种方式：两种方式：A选择 函数B手工 输入10二、常用快捷键及常用函数2.1 Vlookup函数函数函数含义函数含义：在表格或数值数组的首列查找指定的数值，并由此返回表格或数组中该数值所在行中指定列处的数值公公 式式：=VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)对对 应应 为为： =VLOOKUP（要查找的值，要查找的区域，返回数据在区域的第几列数，精确匹配）说说 明：明： “要查找的值”一般情况下须位于“要查找的区域”的第一列

8、 “精确匹配”包括“1”和“0”，“0”即“一模一样”才进行匹配。常用范围常用范围：人员信息查找、工资数据查找 例例 题题：案例操作11二、常用快捷键及常用函数2.1 Vlookup函数函数12二、常用快捷键及常用函数2.2 Datedif函数函数函数含义函数含义：返回两个日期之间的年月日间隔数语法公公 式式：=Datedif （start_date,end_date,unit）对对 应应 为为： =Datedif （起始日期, 结束日期, 返回类型）说说 明：明： 返回类型包括“Y”、“M”、“D”、“YM”“YM”、“YD”“YD”、MD MD Y时间段中的整年数，M时间段中的整月数，D时

9、间段中的天数。YM YM 日期中月数的差。忽略日期中的日和年。日期中月数的差。忽略日期中的日和年。 YD YD 日期中天数的差。忽略日期中的年。日期中天数的差。忽略日期中的年。 MDMD 日期日期中天数的差。忽略日期中的月和年。中天数的差。忽略日期中的月和年。常用范围常用范围：工龄计算、年龄计算、年休计算。工龄计算、年龄计算、年休计算。 例例 题题：计算出生日期为1983-4-1人的年龄 公式：=DATEDIF(1973-4-1,TODAY(),Y)计算日期为1983-4-1和当前日期的间隔月份数 公式：=DATEDIF(1973-4-1,TODAY(),M)计算日期为1983-4-1和当前日

10、期的间隔天数 公式：=DATEDIF(1973-4-1,TODAY(),D)13二、常用快捷键及常用函数2.2 Datedif函数函数起始日期结束日期返回类型函数名称结构符14二、常用快捷键及常用函数2.3 Sum等函数等函数函数含义函数含义：Sum（求和）、Average（平均值）、Max（最大值）、Min（最小值）公公 式式：=Sum(number1,number2, .) 对对 应应 为为：=Sum(数字1, 数字2,) 说说 明：明： 常用范围常用范围：日常工资统计、比赛分数统计日常工资统计、比赛分数统计15二、常用快捷键及常用函数2.4 Rank等函数等函数函数含义函数含义：返回一个

11、数字在数字列表中的排位公公 式式：=RANK(number,ref,order)对对 应应 为为： =RANK(需要排位的数字,为数字列表数组,排位的方式)说说 明：明： 排位的方式包括“1”和“0”，“1”为升序排列，“0”为降序排序 常用范围常用范围：民主测评排序、比赛成绩排序、绩效考核排序民主测评排序、比赛成绩排序、绩效考核排序16二、常用快捷键及常用函数2.5 Left等函数等函数函数含义函数含义：左截取函数Left、右截取函数Right、中间截取函数Mid公公 式式：=Left(string, length) 、=Right (string, length) 、=Mid（string

12、,start_num,num_chars）对对 应应 为为：=Left(字符串, 截取个数) 、=Right (字符串, 截取个数) 、=Mid（字符串,第几个开始, 截取个数）说说 明：明： =left=left(“(“宁波轨道宁波轨道” ,2) 2) 显示为显示为“宁波宁波” ” =Mid=Mid（“宁波轨道宁波轨道” ,2 2, 2 2） 显示为显示为“轨道轨道”常用范围常用范围：身份证出生日期的截取、姓氏截取等身份证出生日期的截取、姓氏截取等 例例 题题：截取十八位身份证的出生年份、出生月份、出生日17二、常用快捷键及常用函数2.6 Concatenate函数函数函数含义函数含义：合并

13、文本字符串。等同于” ”公公 式式：=Concatenate (text1, text2, .) 等同于等同于text1text2对对 应应 为为：=Concatenate (文本文本1,文本文本2, .) 说说 明：明： = =Concatenate (“(“宁波宁波”,“”,“轨道轨道”） 显示为显示为“宁波轨道宁波轨道” ” 等同于：等同于：= =”宁波宁波”轨道轨道” = =Concatenate ( (”钓鱼岛钓鱼岛” ,”是中国的是中国的” ,”固有领土固有领土”） 显示为显示为“钓鱼岛是中国的固有领土钓鱼岛是中国的固有领土” ” 等同于：等同于：= =”钓鱼岛钓鱼岛”&”是中国的

14、是中国的”&”固有领土固有领土”常用范围常用范围：出生日期的组合等出生日期的组合等 例例 题题：将上题截取的出生年份、出生月份、出生日组合成如下格式“1981-05-03”18二、常用快捷键及常用函数2.7 IF函数函数函数含义函数含义：执行真假值判断，根据逻辑计算的真假值，返回不同结果公公 式式：=IF(logical_test,value_if_true,value_if_false)对对 应应 为为：=IF(逻辑判断逻辑判断,判断成立返回值判断成立返回值,判断不成立返回值判断不成立返回值)说说 明：明： =if=if (A1=60(A1=60, ”及格 ”,”不及格 ”） 即如果即如果A

15、1A1单元格的值大于或等于单元格的值大于或等于6060，返回及格，否则返回不及格，返回及格，否则返回不及格 常用范围常用范围：各种吧各种吧 例例 题题：19二、常用快捷键及常用函数2.7 IF函数函数20二、常用快捷键及常用函数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,

16、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),CONCAT

17、ENATE(MID(A1,7,4),-,MID(A1,11,2),-,MID(A1,13,2),TODAY(),y)21三、EXCEL2007常用功能点3.1 排序功能排序功能排序功能排序功能1、对工资表按照职级、系数分别进行排序；2、对工资表按照姓名的拼音顺序排序；3、对工资表按照职级升序、系统降序排序；4、对工资表按照籍贯顺序（杭州、宁波、温州、嘉兴、湖州、绍兴、金华、衢州、丽水、台州、舟山）、职级升序、系数升序排序。22三、EXCEL2007常用功能点3.2 筛选功能筛选功能筛选功能筛选功能1、对工资表筛选2012年2月工资发放清单（日期筛选）；2、对工资表筛选籍贯是杭州、宁波、温州人员

18、发放清单（文本筛选）；3、对工资表筛选个人实发3000元以上和高于平均收入清单（数字筛选）；4、对工资表筛选2012年1月籍贯非杭州且个人实发少于5000元的女性人员名单；5、对工资表筛选姓王且姓名只有两个字的人员名单； （“*”代表不限长度的任意内容，代表不限长度的任意内容，“？”代表仅为一个字符的任意内容）代表仅为一个字符的任意内容）23三、EXCEL2007常用功能点3.3 分列分列24三、EXCEL2007常用功能点3.4 删除重复项删除重复项25三、EXCEL2007常用功能点3.5 数据有效性数据有效性26三、EXCEL2007常用功能点3.6 合并计算合并计算27三、EXCEL2

19、007常用功能点3.7 条件格式条件格式显示实发高于3000元的显示绩效工资在2000至4000元的显示籍贯是嘉兴地区的显示姓名重复的人员名单28三、EXCEL2007常用功能点3.7 条件格式条件格式显示实发工资最高的十个显示实发工资高于平均值的思考：如仅需显示实发工资前6的如何操作？29三、EXCEL2007常用功能点3.7 条件格式条件格式用数据条显示个人实发清除上述个人实发的数据条格式30选择性粘贴选项选选 项项含含 义义全部粘贴所复制的数据的所有单元格内容和格式数值仅粘贴在单元格显示的数据值仅粘贴在单元格显示的数据值格式仅粘贴所复制数据的单元格格式转置将所复制数据的行变成列、列变成行

20、将所复制数据的行变成列、列变成行三、EXCEL2007常用功能点3.8 选择性粘贴选择性粘贴31三、EXCEL2007常用功能点3.9 常用排版技巧常用排版技巧表格刚好有点大，想打表格刚好有点大，想打印在一个页面里！？印在一个页面里！？表格比较长，想每一页表格比较长，想每一页都打印标题行！？都打印标题行！？表格特别大，想固定住表格特别大，想固定住某一行或列进行查看！？某一行或列进行查看！？如何给表格加入页码？如何给表格加入页码？32三、EXCEL2007常用功能点3.9 常用排版技巧常用排版技巧如何将如何将工资表工资表按照按照部门分页打印？部门分页打印？如何快速地复制字体？如何快速地复制字体？

21、1818位身份证号码输入后位身份证号码输入后显示不正常？显示不正常？如何将文字竖直排版？如何将文字竖直排版？右键右键右键右键如何为数据注明信息？如何为数据注明信息？右键右键（或者在数字前加（或者在数字前加“”）33四、透视表介绍及基本操作透视表的几个特点：透视表的几个特点：u是集排序、筛选、组合、汇总等多个功能于一体的交互式报表u有强大、灵活的数据处理功能u熟练掌握该项功能能给你的工作效率显著的提高u是是excel的一个重点、难点的一个重点、难点u是衡量是衡量Excel掌握熟练程度的一个指标掌握熟练程度的一个指标初识透视表初识透视表数据透视表的概念：数据透视表的概念：是一种对大量数据快速汇总和建立交叉列表的交互式动态表格， 能帮助用户分析、组织数据。34四、透视表介绍及基本操作制作简单透视表制作简单透视表第一步：选择数据区域第二步：插入数据透视表数据透视表第三步：确定35四、透视表介绍及基本操作字段列表字段列表字段控制窗口字段控制窗口编辑显示窗口编辑显示窗口36四、透视表介绍及基本操作第四步：选择字符段第四步：选择字符段第五步：调整顺序第五步：调整顺序手工筛手工筛选按钮选按钮37四、透视表介绍及基本操作问