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