Excel数据处理技巧.ppt

Excel数据处理技巧,刘瑜,自我介绍,姓名：刘瑜部门：重庆移动城区三分公司综合部职位：人力资源管理办工地点：鸿园622室联系方式：手机箱Q:44070886,Excel数据处理技巧,数据输入及编辑技巧,表格格式及安全设置,公式及函数应用,数据组织与分析,1.使用序列方式输入数据,使用拖拉填充柄方式输入规律数据对于数值型数据对于文本型数据输入特殊的自定义序列预先通过工具菜单下的选项命令，自定义序列方式定义所需序列再使用拖拉方式输入序列内容,2.数据输入的其他技巧,快速在不同区域的单元格输入数据的方法先选择需要输入数据的特定区域直接输入数据（不要点击），然后按Ctrl+Enter结束在多张工作表中快速输入或编辑相同的数据信息使用Ctrl或Shift键选择多张工作表，即组合工作表然后在相应的区域输入或编辑内容编辑输入完成后取消成组工作表，用右键操作,3.控制或限制单元格数据录入,有效性检查：可以对单元格内的数据类型进行限制，并能核对输入单元格的数据定义域指定有效的单元格输入项选定要限制其数据有效性范围的单元格在“数据”菜单上，单击“有效性”命令，再单击“设置”选项卡指定所需的数据有效性类型，即设置有效性条件如整数、小数、日期、时间、序列、文本长度等,3.1设置输入无效数据时的响应方式,单击“出错警告”选项卡，选中“输入无效数据时显示出错警告”复选框请在“样式”框中选择下列选项之一：若要显示可输入无效数据的信息，请单击“信息”若要显示警告信息，但仍可输入无效数据，请单击“警告”若要阻止输入无效数据，请单击“停止”,4.灵活使用选择性粘贴命令,使用选择性粘贴有选择性的复制内容使用选择性粘贴完成文本数值转换数字的操作使用乘1或加0法完成数据的转换操作预先在某个单元格中输入1或0的数据，选择该数据按复制，再选择需要复制至的目标区域，打开选择性粘贴，使用运算功能中的乘或加使用选择性粘贴完成表格数据的行列转换操作在选择性粘贴中选择勾选“转置”功能,5.数据的排序,排序前选择数据的注意事项如果只按某列或某行数据排序，则可以选择某列名某行如果是对整个表格排序，则只需选择某列中的某个单元格即可进行排序按自定义的顺序进行排序通过工具菜单选项下的自定义序列命令预先定义排序顺序通过数据菜单下的排序命令中的选项选择预先定义的顺序迕行排序,Excel数据处理技巧,数据输入及编辑技巧,表格格式及安全设置,公式及函数应用,数据组织与分析,1.使用条件格式自动设置格式,什么是条件格式当单元格的值满足预先设定的条件时，自动将单元格设置成预先设定的格式条件格式设置方法选择需要设置条件的区域执行：格式-条件格式-设置条件及相应的格式灵活使用条件格式的手段将常量引用方式改为变量，即将具体值改为单元格引用的方式，会使条件更灵活设置,1.1多条件格式的规则,多条件设置规则条件格式从上到下顺序判断条件并执行最多指定三个条件，通过条件格式对话框中的“添加”按钮进行添加练习条件格式设置依据单元格是否为空，设置条件格式单元格为空的表达形式：=“”(等于双引号)掌握设置条件格式的顺序,1.2根据公式设置条件,依据公式设置条件格式使用公式作为条件格式的设置，可以设置灵活的条件格式条件公式应用案例实现隔行变色之条件参考公式：MOD(ROW(),2)=1（将单数行变色）使用组合键Ctrl+Shift+End由指定单元格开始选择至整张表格末尾（区域分离时适用）,2.保护工作表或工作簿元素,若要防止他人偶然或恶意更改、移动或删除重要数据，可以保护特定工作表或工作簿元素保护工作表元素取消任何您希望用户可对其进行更改的单元格的锁定：选择每一个单元格或区域，单击“格式”菜单上的“单元格”，再单击“保护”选项卡，然后清除“锁定”复选框隐藏任何不希望显示的公式：选中“隐藏”复选框在“工具”菜单上，指向“保护”，再单击“保护工作表”在“允许此工作表的所有用户迕行”列表中，选择需要用户更改的元素,保护工作簿元素,保护工作簿元素在“工具”菜单上，指向“保护”，然后单击“保护工作簿”请执行下列一项或多项操作：如果要保护工作簿的结构，请选中“结构”复选框，这样工作簿中的工作表将不能进行移动、删除、隐藏、取消隐藏或重新命名，而且也不能插入新的工作表如果要保护窗口以便在每次打开工作簿时使其具有固定的位置和大小，请选中“窗口”复选框若要禁止其他用户撤消工作簿保护，请键入密码,附：高效的Excel快捷键,使用键盘快捷方式定位到所需位置,Excel数据处理技巧,数据输入及编辑技巧,表格格式及安全设置,公式及函数应用,数据组织与分析,1.函数和公式,什么是函数Excel函数即是预先定义，执行计算、分析等处理数据任务的特殊公式。以常用的求和函数SUM为例，它的语法是“SUM(number1,number2,)”。什么是公式函数与公式既有区别又互相联系。如果说前者是Excel预先定义好的特殊公式，后者就是由用户自行设计对工作表进行计算和处理的公式。以公式“=SUM(E1:H1)*A1+26”为例，它要以等号“=”开始，其内部可以包括函数、引用、运算符和常量。,2.自动计算,使用自动求和使用标准工具栏上的自动求和功能键使用快捷键：Alt+=使用自动计算功能自动计算功能处于状态栏上的右下角区域使用右键单击该区域可以切换不同的计算方式：无、平均值、计数、计数值、最大值、最小值、求和当用户选择相应的计算区域时，在该区域会自动显示相应的结果,3.单元格引用,掌握单元格引用方式相对单元格地址引用格式：列标+行号，如A1绝对单元格地址引用格式：$列标+$行号,如$A$1（可以使用F4键盘进行相对绝对的切换）跨工作表或工作簿的引用计算跨工作表引用的格式：工作表名称！+单元格地址如引用工作表“一月”的单元格D3,使用公式：=一月！D3跨工作簿的引用格式：工作簿名称工作表名称！单元格地址如引用工作簿文件“工资表”中的工作表“一月”的单元格D3,使用公式：=工资表.xls一月！$D$3,4.应用例举,合并函数：CONCATENATE常用函数：if、COUNTIF查询引用类函数：VLOOKUP统计类函数：COUNTIF、SUMIF日期类函数：DATEDIF,CONCATENATE函数,将多个字符文本或单元格中的数据连接在一起，显示在一个单元格中。将若干文字串合并到一个文字串中，其功能与&运算符相同语法：CONCATENATE(Text1，Text),COUNTIF函数,COUNTIF函数可累计满足某一条件的对象在单元格区域中出现的次数，该条件可以是数值、文本或表达式，在人事、工资和成绩统计中有广泛用途语法：COUNTIF（range,criteria)说明：Range为条件范围，Criteria为条件，如果使用非单元格引用的条件，必须添加双引号,一列里剔重复,将IF函数和COUNTIF函数整合在一起运用，在一列数据里，能剔除重复的数据。语法：=IF(COUNTIF($A$1:A1,A1)=1,1,重复&A1),VLOOKUP_实现竖直查找,在表格或数值数组的首列查找指定的数值，并由此返回表格或数组当前行中指定列处的数值。当比较值位于数据表首列时，可以使用此函数。语法：VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)lookup_value为目标数值；table_array为给定区域，即查找区域；col_index_num为指定返回给定区域中某一列的序号；range_lookup为true时，进行近似匹配查找，为false时，进行精确匹配查找。,DATEDIF计算两个日期间的间隔,函数功能：返回两个日期之间的年月日间隔数基本语法：=DATEDIF(开始日期，结束日期，单位代码）单位代码说明（需要用引号括起）Y表示年M表示月D表示天,Excel数据处理技巧,数据输入及编辑技巧,表格格式及安全设置,公式及函数应用,数据组织与分析,1.数据透视表,什么是数据透视表从数据库中产生的一个动态汇总表格，可以对工作表中的大量数据进行快速汇总并建立一个交叉列表的交互式表格。如何创建数据透视表数据-数据透视表和数据透视图,2.图表在数据分析中作用,柱形图(Column):用来显示一段时间内数据的变化或者描述各个项目之间数据比较图表线性图(Line):主要用于以等时间间隔显示数据的变化趋势，强调的是时间性和变动率，而不是变动量饼形图(Pie):显示数据系列中的项目和该项目数值总和的比例关系。它只能显示一个系列的数据比例关系组合图,2.1图表结构,数值轴,绘图区,图标标题,图标区,网格线,图例,分类轴,数据系列,3.创建分析图表,确定分析目标明确表格数据的取值范围使用图表向导(ChartWizard)创建图表插入-图表-完成四个步骤操作（也可以直接执行工具栏中的图表向导按钮创建）使用一键成图创建图表选择数据后，按F11功能创建直接创建独立的图表,4.图表的编辑技巧,常规编辑技巧改变图表大小移动图表位置图表内其他对象编辑修改图表的源数据向图表添加数据删除图表内数据操作方法：右击图表，选择快捷菜单中的数