EXCEL常用操作技巧.pptx

EXCEL常用操作技巧,讲师：XXXXX年XX月XX日,课程编码：EXCEL常用操作技巧20110831,讲师简介,此次由授课讲师自行填写,课程目的,目标3,能了解EXCEL基本知识、基础功能使用及应用,目标2,目标1,能掌握EXCEL高级工具使用、一些快捷操作方式及常用公式的原理与应用,能掌握数据透视表的基本原理及使用方法，并通过数据透视表对业务分析给予支持,目录,一、EXCEL基本认知及应用二、EXCEL快捷操作及常用技巧三、常用EXCEL公式原理及应用四、数据透视表原理及应用,一、Excel基本认知,目前大家看到的全部就称为工作簿，工作簿里有工作表，工作表里有单元格,工作表,单元格,工具栏,显示比例,快捷工具栏,一、Office2007基本应用工具栏,一、Office2007基本应用开始选项卡,：单元格底纹颜色：单元格字体颜色：单元格格式刷：合并单元格：条件格式：定位条件,灵活使用可以让我们表格更生动以及突出重点内容,注意单击与双击的区别,自定义自动匹配格式，如重复标注颜色,定位条件的妙用：空值、可见单元格、对象,一、Office2007基本应用插入选项卡,：数据透视表：形状：SmartArt：折入图表区：艺术字：插入特殊符号,已有流程图绘制,根据排版需要添加另样字体,插入类似：这样的特殊符号,规则数据分析与归整利器,自定议图形手工绘制,一、Office2007基本应用页面布局选项卡,：打印区域：缩放比例：打印标题,打印固有标题，多页需重复出现标题,设定打印图表区域,打印的缩放效果,一、Office2007基本应用数据选项卡,：筛选：分列：数据有效性,约束单元格输入的范围及范围,按一定条件筛选表格内容,按特定字符进行自动分列,一、Office2007基本应用审阅选项卡,：新建批注：允许用户编辑区域：保护工作表,对允许编辑区域外其他区域进行限定编辑,对单元格内容进行批注说明,除约定区域外其他将不可编辑,一、Office2007基本应用视图选项卡,：分页预览：网格线：冻结空格：隐藏,选择行、列、单元格不同的冻结效果,有效看出打印区域及打印单张可见内容,打勾与不打勾的区别,将不想别人看到的表格隐藏起来，如公式辅助表,目录,一、EXCEL基本认知及应用二、EXCEL快捷操作及常用技巧三、常用EXCEL公式原理及应用四、数据透视表原理及应用,二、Excel快捷操作高级工具栏,“安装”代表安装高级工具插件；“卸载”代表卸载高级工具插件。注意：1、安装要求关掉所有Excel表格；2、vista以上版本需要以管理员身份运行方可安装成功。,二、Excel快捷操作宏安全性设置,开启宏功能，否则高级工具栏将无法使用。,二、Excel快捷操作高级工具栏,建立目录并具有超链接的功能1、工作表目录：指在本工作簿所有工作表建立目录及超链接。2、建立文件目录：指为指定文件夹的所有文件建立文件目录，支持文件包括：word、excel、rar、txt,批量新建或复制工作表可以一次性复制、粘贴多个工作表，可以大大提高我们模板类工作表的复制、粘贴,二、Excel快捷操作高级工具栏,工作簿、表的合并折分1、工作簿折分：指将一个工作为簿里的工作表折分成对应名称的工作簿并单独保存；2、工作表折分：指将一个工作表按一列不同字段在同一工作簿折分成不同的工作表；3、合并工作簿：指将一个文件夹内的不同工作簿的内容合并汇总到一个工作表或者工作簿内（工作簿会出错）,二、Excel常用技巧单元格选取范围快捷操作,CTRL、SHIRT、方向键组合应用快速选取单元格范围1、ctrl+方向键：可以从一个有内容的单元格跳到最后一个有内容的单元格（空白单元格之前），或者从空白单元格跳到另一个最后空白单元格（有内容单元格之前）。2、ctrl+shift+方向键：多了shift就多了连续选取的作用，就是说从一个内容的单元格连续选取到最后一个有内容的单元格，或者从一个空白的单元格跳到另一个空白单元格中去。利用这个功能可以有效地进行有特征的单元格，大大减少我们拖动的范围与幅度。,二、Excel常用技巧巧用“定位”功能批量填充,1、选取需要填充的单元格；2、Ctrl+G;点“定位条件”；然后选“空值”；点“确定”；3、输入“=”+向上/向下方向键（对上填充/对下填充）；4、同时按ctrl+回车键，完成填充。,空值,二、Excel常用技巧利用条件格式查找重复值,1、选择需要检查的行与列,格式包括字号、颜色以及底纹颜色等。,二、Excel常用技巧文本转换数字,文本的数据特征：左上角有个绿色的三角，无法求和。,在一个空单元格输入“1”右键选择复制,右键选择“选择性粘贴”,把数据选中，选择“乘”，数值。,二、Excel常用技巧数字转换文本,数值的数据特征：1、左上角无绿色的三角型符号2、输入完毕按回车会自动归右3、可以求和,利用公式TRIM来转换=TRIM（）,输完公式后右键复制（CTRL+V），然后选择性粘贴就可以变成文本。左上角会出现绿色的三角符号,二、Excel常用技巧超链接制作,利用工具制作：优点：简单明了，便于操作。缺点：比较死板，不能及时更新。,利用公式制作：优点：能及时更新，且可用于工作簿链接。缺点：复杂，对公式操作要求高,1、红色部门为固定部分，蓝色部分为变动部分2、#：代表本工作薄&：文本链接符：数字表名中固有的显示符！：工作表固有符号,设置链接部分,设置显示名称,指的是引用A2单元格的名称,链接到1工作表中的A1单元格,显示名称为“1”,二、Excel常用技巧巧用数据有效性限制录入内容,1、在来源里用“,”代表不同选择内容，如输入：你,我,他,2、在“输入信息”、“出错警告”处输入你想提示输入者信息。,目录,一、EXCEL基本认知及应用二、EXCEL快捷操作及常用技巧三、常用EXCEL公式原理及应用四、数据透视表原理及应用,三、常用EXCEL公式IF、TEXT,1、判断公式IF=IF(判断依据,依据为真显示结果,依据不成立显示结果)如=IF(01,“是”,“否”),最多可嵌套7个判断条件，超过无法显示正确结果。2、格式整理公式TEXT=TEXT(需整理数据,”需整理格式”)必须用“”将需整理格式围起来，如”YY-M-D”、”HH:MM”等。YY-M-D：如2011-8-1312:00:00经过整理之后就会显示为：2011-8-13HH:MM：指的是小时和时间整理格式也可以是具体的格式，如“0.85”代表两位小数,三、常用EXCEL公式VLOOKUP、SUMPRODUCT,3、VLOOKUP/HLOOKUP（匹配查询公式）=VLOOKUP(匹配内容,匹配数据源,对应行数，匹配方式)对应行数：指的是匹配内容与查询返回结果间隔的行数区域方式：FALSE：精准查找；TRUE：模糊查找。VLOOKUP:横向查找HLOOKUP:纵向查找4、条件求和/计数=SUMPRODUCT(列数据=条件1)*(列数据=条件2)*(求和数据列)*：代表多条件，如还有再多条件亦可使用求和有求和数据更，如计数则无求和数据列。,三、常用EXCEL公式RANK、COUNTIF、SUMIF,5、排序公式RANK=RANK(需排序单元格,排序数据范围,0/1)”0”:从大到小排序”1”:从小到大排序6、条件计数COUNTIF=COUNFIF(计数数据源,”计数依据”)必须用“”将计数依据围起来，如”0”、”0”等。条件求和SUMIF=SUMIF(条件数据源,”求和依据”,求和数据源)必须用“”将求和依据围起来，如”0”、”0”等。,三、常用EXCEL公式DATEDIF、MID,7、时间年、月、日间隔计算公式=DATEDIF(开始时间,结束时间,”Y”/”M”/”D”)“Y”:计算年间隔”M”:计算月间隔”D”:计算天间隔8、取某个字符之前的数值=MID(数据单元格,1,FIND（”某个字符”,数据单元格,1)-1)MID（数据，从第几位起，取几位数据）：提取数据公式FIND(某个字符，数据，1)：查找某个字符在数据里所处的位置。其实这个公式是一个相嵌组合公式，我们以后会多次使用到这种公式。,三、常用EXCEL公式TRIM、RIGHT、LEFT,9、去掉空格TRIM=TRIM(需整理的内容)可将带有空格的内容里的空格去掉，将公式选择性粘贴成数值即可10、从左或右取几位数RIGHTLEFT=RIGHT（取数源数据，从右起取几位）=LEFT（取数源数据，从左起取几位）如：=RIGHT(“ABCDE”,2)将会显示DE=LEFT(“ABCDE”,2)将会显示AB,目录,一、EXCEL基本认知及应用二、EXCEL快捷操作及常用技巧三、常用EXCEL公式原理及应用四、数据透视表原理及应用,数据透视表,1、选择插入“数据透视表”,2、选择需要整理数据源，一般会默认正确，如果有错误的话可手工选取,数据透视表是集排序、分类汇总等功能于一身的统计报表功能,可提供整理的字段。,可将字段直接拖至对应的红框内1、报表筛选：将字段拉至该框内，可提供筛选该字段数据功能2、列标签：将字段拉至该框内，可形成该字段的表头，并按此表头整理数据3、行标签：将字段拉至该框内，可形成该字段行，并按此行整理数据4、数值：将字段拉至该框内可统计该字段的数据，而且提供多种统计口径,1、将“提货方式”拖入报表筛选，并筛选“送货上门”2、将“付款方式”拖入列标签3、将“运输性质”拖入行标签4、将“开单金额”拖入数值，并选择“求和”方式统计数据,列标签,行标签,报表筛选,数值,可提供多种数据统计方式，可根据自己需要进行调整。,