Excel数据处理技巧.ppt

1、Excel数据处理技巧, 刘 瑜,自我介绍,姓名：刘 瑜 部门： 重庆移动城区三分公司综合部 职位：人力资源管理 办工地点：鸿园622室 联系方式： 手机邮箱：QQ:44070886,Excel数据处理技巧,数据输入及编辑技巧,表格格式及安全设置,公式及函数应用,数据组织与分析,1.使用序列方式输入数据, 使用拖拉填充柄方式输入规律数据 对于数值型数据 对于文本型数据 输入特殊的自定义序列 预先通过工具菜单下的选项命令，自定义序列方式定义所需序列 再使用拖拉方式输入序列内容,2. 数据输入的其他技巧, 快速在不同区域的单元格输入数据的方法 先

2、选择需要输入数据的特定区域 直接输入数据（不要点击），然后按Ctrl+Enter结束 在多张工作表中快速输入或编辑相同的数据信息 使用Ctrl或Shift键选择多张工作表，即组合工作表 然后在相应的区域输入或编辑内容 编辑输入完成后取消成组工作表，用右键操作,3. 控制或限制单元格数据录入, 有效性检查： 可以对单元格内的数据类型进行限制，并能核对输入单元格的数据定义域 指定有效的单元格输入项 选定要限制其数据有效性范围的单元格 在“数据”菜单上，单击“有效性”命令，再单击“设置”选项卡 指定所需的数据有效性类型，即设置有效性条件如整数、小数、日期、时间、序列、文本长度等,3.1 设置输入无效

3、数据时的响应方式, 单击“出错警告”选项卡，选中“输入无效数据时显示出错警告”复选框 请在“样式”框中选择下列选项之一： 若要显示可输入无效数据的信息，请单击“信息” 若要显示警告信息，但仍可输入无效数据，请单击“警告” 若要阻止输入无效数据，请单击“停止”,4. 灵活使用选择性粘贴命令, 使用选择性粘贴有选择性的复制内容 使用选择性粘贴完成文本数值转换数字的操作 使用乘1或加0法完成数据的转换操作 预先在某个单元格中输入1或0的数据，选择该数据按复制，再选择需要复制至的目标区域，打开选择性粘贴，使用运算功能中的乘或加 使用选择性粘贴完成表格数据的行列转换操作 在选择性粘贴中选择勾选“转置”功

4、能,5. 数据的排序, 排序前选择数据的注意事项 如果只按某列或某行数据排序，则可以选择某列名某行 如果是对整个表格排序，则只需选择某列中的某个单元格即可进行排序 按自定义的顺序进行排序 通过工具菜单选项下的自定义序列命令预先定义排序顺序 通过数据菜单下的排序命令中的选项选择预先定义的顺序迕行排序,Excel数据处理技巧,数据输入及编辑技巧,表格格式及安全设置,公式及函数应用,数据组织与分析,1. 使用条件格式自动设置格式, 什么是条件格式 当单元格的值满足预先设定的条件时，自动将单元格设置成预先设定的格式 条件格式设置方法 选择需要设置条件的区域 执行：格式-条件格式-设置条件及相应的格式

5、灵活使用条件格式的手段将常量引用方式改为变量，即将具体值改为单元格引用的方式，会使条件更灵活设置,1.1 多条件格式的规则, 多条件设置规则 条件格式从上到下顺序判断条件并执行 最多指定三个条件，通过条件格式对话框中的“添加”按钮进行添加 练习条件格式设置 依据单元格是否为空，设置条件格式 单元格为空的表达形式：=“”(等于双引号) 掌握设置条件格式的顺序,1.2 根据公式设置条件, 依据公式设置条件格式 使用公式作为条件格式的设置，可以设置灵活的条件格式 条件公式应用案例 实现隔行变色之条件 参考公式：MOD(ROW(),2)=1（将单数行变色） 使用组合键Ctrl+Shift+End由指定

6、单元格开始选择至整张表格末尾（区域分离时适用）,2. 保护工作表或工作簿元素, 若要防止他人偶然或恶意更改、移动或删除重要数据，可以保护特定工作表或工作簿元素 保护工作表元素 取消任何您希望用户可对其进行更改的单元格的锁定：选择每一个单元格或区域，单击“格式”菜单上的“单元格”，再单击“保护”选项卡，然后清除“锁定”复选框 隐藏任何不希望显示的公式：选中“隐藏”复选框 在“工具”菜单上，指向“保护”，再单击“保护工作表” 在“允许此工作表的所有用户迕行”列表中，选择需要用户更改的元素,保护工作簿元素, 保护工作簿元素 在“工具”菜单上，指向“保护”，然后单击“保护工作簿” 请执行下列一项或多项

7、操作： 如果要保护工作簿的结构，请选中“结构”复选框，这样工作簿中的工作表将不能进行移动、删除、隐藏、取消隐藏或重新命名，而且也不能插入新的工作表 如果要保护窗口以便在每次打开工作簿时使其具有固定的位置和大小，请选中“窗口”复选框 若要禁止其他用户撤消工作簿保护，请键入密码,附：高效的Excel快捷键,使用键盘快捷方式定位到所需位置,Excel数据处理技巧,数据输入及编辑技巧,表格格式及安全设置,公式及函数应用,数据组织与分析,1. 函数和公式,什么是函数 Excel函数即是预先定义，执行计算、分析等处理数据任务的特殊公式。以常用的求和函数SUM为例，它的语法是“SUM(number1,num

8、ber2,)”。 什么是公式 函数与公式既有区别又互相联系。如果说前者是Excel预先定义好的特殊公式，后者就是由用户自行设计对工作表进行计算和处理的公式。 以公式“=SUM(E1:H1)*A1+26”为例，它要以等号“=”开始，其内部可以包括函数、引用、运算符和常量。,2. 自动计算, 使用自动求和 使用标准工具栏上的自动求和功能键 使用快捷键：Alt+= 使用自动计算功能 自动计算功能处于状态栏上的右下角区域 使用右键单击该区域可以切换不同的计算方式：无、平均值、计数、计数值、最大值、最小值、求和 当用户选择相应的计算区域时，在该区域会自动显示相应的结果,3. 单元格引用, 掌握单元格引用

9、方式 相对单元格地址引用格式：列标+行号，如A1 绝对单元格地址引用格式：$列标+$行号,如$A$1（可以使用F4键盘进行相对绝对的切换） 跨工作表或工作簿的引用计算 跨工作表引用的格式：工作表名称！+单元格地址 如引用工作表“一月”的单元格D3,使用公式：=一月！D3 跨工作簿的引用格式：工作簿名称工作表名称！单元格地址 如引用工作簿文件“工资表”中的工作表“一月”的单元格D3,使用公式：=工资表.xls一月！$D$3,4. 应用例举, 合并函数：CONCATENATE 常用函数：if、 COUNTIF 查询引用类函数：VLOOKUP 统计类函数：COUNTIF、SUMIF 日期类函数：DA

10、TEDIF,CONCATENATE函数, 将多个字符文本或单元格中的数据连接在一起，显示在一个单元格中。将若干文字串合并到一个文字串中，其功能与&运算符相同 语法： CONCATENATE(Text1，Text),COUNTIF函数, COUNTIF函数可累计满足某一条件的对象在单元格区域中出现的次数，该条件可以是数值、文本或表达式，在人事、工资和成绩统计中有广泛用途 语法： COUNTIF（range,criteria) 说明：Range为条件范围，Criteria为条件，如果使用非单元格引用的条件，必须添加双引号,一列里剔重复,将IF函数和COUNTIF函数整合在一起运用，在一列数据里，能

11、剔除重复的数据。 语法： =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

12、时，进行精确匹配查找。,DATEDIF计算两个日期间的间隔, 函数功能：返回两个日期之间的年月日间隔数 基本语法： =DATEDIF(开始日期，结束日期，单位代码） 单位代码说明（需要用引号括起） Y表示年 M表示月 D表示天,Excel数据处理技巧,数据输入及编辑技巧,表格格式及安全设置,公式及函数应用,数据组织与分析,1. 数据透视表,什么是数据透视表 从数据库中产生的一个动态汇总表格，可以对工作表中的大量数据进行快速汇总并建立一个交叉列表的交互式表格。 如何创建数据透视表 数据 - 数据透视表和数据透视图,2. 图表在数据分析中作用, 柱形图(Column):用来显示一段时间内数据的变化

13、或者描述各个项目之间数据比较图表 线性图(Line):主要用于以等时间间隔显示数据的变化趋势，强调的是时间性和变动率，而不是变动量 饼形图(Pie):显示数据系列中的项目和该项目数值总和的比例关系。它只能显示一个系列的数据比例关系 组合图,2.1 图表结构,数值轴,绘图区,图标标题,图标区,网格线,图例,分类轴,数据系列,3. 创建分析图表, 确定分析目标 明确表格数据的取值范围 使用图表向导(Chart Wizard)创建图表 插入 - 图表 - 完成四个步骤操作（也可以直接执行工具栏中的图表向导按钮创建） 使用一键成图创建图表选择数据后，按F11功能创建直接创建独立的图表,4. 图表的编辑技巧, 常规编辑技巧 改变图表大小 移动图表位置 图表内其他对象编辑 修改图表的源数据 向图表添加数据 删除图表内数据操作方法：右击图表，选择快捷菜单中的数