(完整版)EXCEL数据分析与处理(上)总结课件.ppt

1、数据输入数据整理计算和分析第一部分 工作环境和基本操作第二部分 输入和导入数据第三部分 数据整理和编辑第四部分 数据查询第五部分 常规计算和分析1.最佳EXCEL学习方法2.工作环境基本操作3.数据输入与导入：数据有效性4.数据整理与编辑：设置单元格、排序5.数据查询：筛选、条件格式6.常规计算分析：分类汇总、合并计算、数据透视表7.高级分析：模拟运算、单变量求解、规划求解、分析工具刚开始接触基本掌握常用功能熟练使用常用功能+数据透视表+公式-+数组公式+VBA简单编程EXCEL技能+专业知识+行业经验 主要介绍Excel2007工作环境和基本操作 熟悉界面设置、工作簿和工作表的操作以及窗口显

2、示等方面的内容。功能区旨在使 Excel 使用起来更简单，并帮助用户快速找到和使用所需的命令。功能区由选项卡、组和命令三部分组成 。您会发现您熟悉的命令都按照一定的方式组合在一起 Excel2007工作环境介绍功能区旨在使 Excel 使用起来更简单，并帮助用户快速找到和使用所需的命令。功能区由选项卡、组和命令三部分组成 。您会发现您熟悉的命令都按照一定的方式组合在一起 Excel2007工作环境介绍命令更多，但仅在需要时出现。Excel2007工作环境介绍需要时可提供更多选项 Excel2007工作环境介绍功能区上的每个按钮都有快捷方式。快捷方式需要的键通常更少例如，在 Excel 中可以使

3、用键提示居中对齐文本。Excel2007工作环境介绍 Excel2007工作环境介绍在 Excel 2007 中，可以打开用 Excel 早期版本（从 Excel 95 到 Excel 2003）创建的文件。Excel2007工作环境介绍新文件类型：.XLSX 工作区和工作区文件(.xlw)工作区文件仅记录了工作簿文件的路径和名称、排列位置等信息，但不是工作薄文件。Excel2007工作环境介绍新功能 工作表的行数从 65,536 增加到了 1,048,576，列数从 256 增加到了 16,384。数据更有用 由于新文件格式基于 XML，因此更容易与其他计算机和程序中的其他数据集进行集成，从

4、而可以对数据进行更多处理。例如，可以在专业化的程序中存储预算数据，选择所需的数据，然后将其导入到 Word 文档、Excel 工作表和 Access 数据库。Excel2007工作环境介绍修改个性设置修改默认文件位置最近打开的文档相关设置添加序列 Excel2003工作环境介绍打开方法：工具菜单选项 Excel基本操作冻结窗格 利用这个功能用户可以指定某些行列永远留在屏幕，不会因滚动工作表消失拆分窗口 用户可以在不同区域查看同一工作表的不同部分。多窗口协作用户可以在不同窗口中查看同一工作簿，互不影响，保持同步更新。并排比较页面设置项打印标题顶端标题行撤销和恢复注意重复只有一级、撤销可以有16级

5、或更多工作表切换快捷键工作表导航栏选当前数据区：;选定数据区域;定位到工作表区域的右下角保存：选定区域内循环录入数据（先选定范围，再回车）p数字填充p文本填充p文本+数字填充p等差填充p等比填充p按日期填充p特殊内容填充p自定义序列填充p使用菜单命令 p使用鼠标左键 p配合智能标记 p配合键 p根据范本填充 p使用鼠标右键在自动填充与排序功能中起到重要作用 导入序列进行更多的个性的填充1,2,34,5,6,.9:0010:00,11:00,12:00,.周一周二,周三,周四,.星期一星期二,星期三,星期四,.一月二月,三月,四月,.一月,四月七月,十月,一月,.1999 年 1 月,1999

6、年 4 月1999 年 7 月,1999 年 10 月,2000 年 1 月,.1 月 15 日,4 月 15 日7 月 15 日,10 月 15 日,.1999,20002001,2002,2003,.1 月 1 日,3 月 1 日5 月 1 日,7 月 1 日,9 月 1 日,.Qtr3（或 Q3 或 Quarter3）Qtr4,Qtr1,Qtr2,.文本1,文本A文本1,文本A,文本3,文本A,.第 1 阶段第 2 阶段,第 3 阶段,.产品 1产品 2,产品 3,.输入分数 整数部分+空格+分子部分+”/”+分母部分输入特殊符号ALT+41420=对号 ALT+41409=错号XALT

7、+178=M2平方 AlT+179=M3输入身份证号码（超过15位精度的数字录入）把证件号码、银行帐号、零件编号等作为文本输入，以单引号开始的输入会认为是文本数据。在单元格中换行（Alt+Enter）神奇的选择性粘贴1.全部2.数值：只复制数值3.格式4.有效性验证5.选项：跳过空单元格转置：行列互换6.粘贴链接斜线表头单斜线表头多斜线表头什么是公式？公式是以等号“=“为引导，通过运算符按照一定顺序组合进行数据处理的等式。例如：【=5+2*3 】结果等于 2 乘 3 再加 5。公式的组成部分 函数：PI()函数返回值 pi：3.142.引用：A2 返回单元格 A2 中的值。常量：直接输入公式中

8、的数字或文本值，如 2。运算符：（脱字号）运算符表示将数字乘方，*（星号）运算符表示相乘。什么是公式？公式的组成要素：等号(=)、函数、引用、运算符和常量、名称。函数：函数是预先编写的公式，可以对一个或多个值执行运算，并返回一个或多个值。函数可以简化和缩短工作表中的公式，尤其在用公式执行很长或复杂的计算时。运算符：一个标记或符号，指定表达式内执行的计算的类型。有数学、比较、逻辑和引用运算符等。常量：不进行计算的值，因此也不会发生变化。例如:数字 210 以及文本“每季度收入”都是常量。表达式以及表达式产生的值都不是常量。函数是预定义的公式，通过使用一些称为参数的特定数值来特定的顺序或结构执行计

9、算。函数可用于执行简单或复杂的计算。函数的语法：ROUND 函数示例说明了函数的语法，它将单元格 A10 中的数字四舍五入。函数的结构:函数的结构以等号(=)开始，后面紧跟函数名称和左括号，然后以逗号分隔输入该函数的参数，最后是右括号。函数名称。如果要查看可用函数的列表，可单击一个单元格并按 Shift+F3。参数。参数可以是数字、文本、TRUE 或 FALSE 等逻辑值、数组、错误值或单元格引用。指定的参数都必须为有效参数值。参数也可以是常量、公式或其他函数。参数工具提示。在键入函数时，会出现一个带有语法和参数的工具提示。当函数以公式的形式出现，其结构如图：以等号开始，函数名，左括号、以半角

10、逗号隔开的参数，右括号。函数名：条件函数IF(logical_test,value_if_true,value_if_false)。引用:(F2:F5)返回单元区域F列第2行到5行中的值。常量:(50)直接输入公式中的数字或文本值，如 2。运算符:()逻辑运算符，进行比较大小的运算。参数：AVERAGE(F2:F5)50；SUM(G2:G5)；0。当函数做参数时，称为嵌套函数。部分函数没有参数。例如：Now(),Today(),Rand()。Excel的公式是为了有目的的计算结果，只能在单元格或内存中返回值，而不能对单元格进行删除或对其它单元格进行赋值。1、算术运算符2、比较运算符+（加号）加

11、法3+3（减号）减法31负数1*（星号）乘法3*3/（正斜杠）除法3/3%（百分号）百分比20%（脱字号）乘方32=（等号）等于 A1=B1（大于号）大于 A1B1（小于号）小于 A1=（大于等于号）大于或等于 A1=B1=（小于等于号）小于或等于 A1=B1（不等号）不等于A1B13、文本连接符可以使用与号(&)联接或连接一个或多个文本字符串，以生成一段文本。4、引用运算符:（冒号）区域运算符，生成对两个引用之间所有单元格的引用（包括这两个引用）B5:B15,（逗号）联合运算符，将多个引用合并为一个引用SUM(B5:B15,D5:D15)（空格）交集运算符，生成对两个引用中共有的单元格的引用

12、B7:D7 C6:C8在某些情况中，执行计算的次序会影响公式的返回值。计算次序公式按特定次序计算值。Excel 中的公式始终以等号(=)开头，这个等号告诉 Excel 随后的字符组成一个公式。等号后面是要计算的元素（即操作数），各操作数之间由运算符分隔。Excel 按照公式中每个运算符的特定次序从左到右计算公式。使用括号若要更改求值的顺序，请将公式中要先计算的部分用括号括起来。优先顺序优先顺序运算符运算符说明说明1:（冒号）引用运算符2（单个空格）3,（逗号）4负数（如 1）5%百分比6乘方7*和/乘和除8+和 加和减9&连接两个文本字符串（串连）10=比较运算符1112=14Excel函数一

13、共有11类：1.数据库函数：当需要分析数据清单中的数值是否符合特定条件时，可以使用数据库工作表函数。2.日期与时间函数：3.工程函数：工程工作表函数用于工程分析。4.财务函数：5.信息函数：可以使用信息工作表函数确定存储在单元格中的数据的类型。（IS）6.逻辑函数：使用逻辑函数可以进行真假值判断7.查询和引用函数：当需要在数据清单或表格中查找特定数值，或者需要查找某一单元格的引用时，可以使用查询和引用工作表函数。例如，如果需要在表格中查找与第一列中的值相匹配的数值，可以使用 VLOOKUP 工作表函数。如果需要确定数据清单中数值的位置，可以使用 MATCH 工作表函数。8.数学和三角函数：9.

14、统计函数：10.文本函数11.用户自定义函数。名称：代表单元格、单元格区域、公式、常量值或Excel表。名称是一种有意义的简写形式，它更便于您了解单元格引用、常量、公式的用途。应理解为有名字的公式，有名称规范和限制，注意使用相对引用接受公式标志选项：可以将数据列表第一行字段做字段名使用，而且公式会随着字段名称改变。=批发价*数量什么单元格引用？：用于表示单元格在工作表上所处位置的坐标集相对引用&绝对引用:快速切换引用样式：在编辑状态下选中引用的公式中的”A1”，重复按F4键；引用样式依次改变：=$A$1、=A$1、=$A1、=A1示例:在名称中使用常量与函数$A$1（绝对列和绝对行）$A$1

15、A$1（相对列和绝对行）C$1$A1（绝对列和相对行）$A3 A1（相对列和相对行）C3关于引用：绝对引用、相对引用和混合引用之间的区别 相对引用 公式中的相对单元格引用（如 A1）是基于包含公式和单元格引用的单元格的相对位置。如果公式所在单元格的位置改变，引用也随之改变。如果多行或多列地复制或填充公式，引用会自动调整。默认情况下，新公式使用相对引用。绝对引用 公式中的绝对单元格引用（如$A$1）总是在特定位置引用单元格。如果公式所在单元格的位置改变，绝对引用将保持不变。如果多行或多列地复制或填充公式，绝对引用将不作调整。默认情况下，新公式使用相对引用，您可能需要将它们转换为绝对引用。关于引用

16、：绝对引用、相对引用和混合引用之间的区别 相对引用 公式中的相对单元格引用（如 A1）是基于包含公式和单元格引用的单元格的相对位置。如果公式所在单元格的位置改变，引用也随之改变。如果多行或多列地复制或填充公式，引用会自动调整。默认情况下，新公式使用相对引用。绝对引用 公式中的绝对单元格引用（如$A$1）总是在特定位置引用单元格。如果公式所在单元格的位置改变，绝对引用将保持不变。如果多行或多列地复制或填充公式，绝对引用将不作调整。默认情况下，新公式使用相对引用，您可能需要将它们转换为绝对引用。混合引用Sheet2:Sheet6!A2:A51.便于引用多个工作表 如果要分析同一工作簿中多个工作表上

17、相同单元格或单元格区域中的数据，请使用三维引用。三维引用包含单元格或区域引用，前面加上工作表名称的范围。Excel 使用存储在引用开始名和结束名之间的任何工作表。|例如，=SUM(Sheet2:Sheet13!B5)将计算 B5 单元格内包含的所有值的和，单元格取值范围是从工作表 2 到工作表 13。2.使用三维引用可以引用其他工作表中的单元格、定义名称，还可以通过使用下列函数来创建公式：SUM、AVERAGE、AVERAGEA、COUNT、COUNTA、MAX、MAXA、MIN、MINA、PRODUCT、STDEV、STDEVA、STDEVP、STDEVPA、VAR、VARA、VARP 和

18、VARPA。3.三维引用不能用于数组公式中。4.三维引用不能与交叉引用运算符（空格）一起使用，也不能用在使用了绝对交集的公式中。在移动、复制、插入或删除工作表时出现的情况 以下示例演示在移动、复制、插入或删除三维引用中包括的工作表时出现的情况。该示例使用公式=SUM(Sheet2:Sheet6!A2:A5)对从 Sheet2 到 Sheet6 的每个工作表中的 A2 到 A5 单元格求和插入或复制 如果在 Sheet2 和 Sheet6（本示例中的起止工作表）之间插入或复制工作表，Excel 将在计算中包含所添加的工作表中从单元格 A2 到 A5 的所有数值。删除 如果删除了 Sheet2 和

19、 Sheet6 之间的工作表，Excel 将删除计算中相应的值。移动 如果将 Sheet2 和 Sheet6 之间的工作表移动到引用工作表区域之外的位置，Excel 将删除计算中相应的值。移动起止工作表 如果将 Sheet2 或 Sheet6 移到同一工作簿中的其他位置，Excel 将对计算进行调整以包含它们之间的新工作表区域。删除起止工作表 如果删除了 Sheet2 或 Sheet6，Excel 将对计算进行调整以包含它们之间的工作表区域。关于引用：引用其他工作表AVERAGE 工作表函数将计算同一个工作簿中名“市场”的工作表的 B1:B10 区域内的平均值。引用名为“市场”的工作表 引用

20、B1 和 B10 之间的单元格（包括 B1 和 B10）！将工作表引用与单元格区域引用分开导入文本数据导入Word表格导入Access数据库数据数据导入Internet网页数据有选择的导入数据在导入时创建参数查询运用Excel的基本功能和简单函数对数据进行加工处理的方法，包括数据选取、数据格式设置、数据区域的转换以及数据排序等方法和技巧。调整数据存放的结构和数据显示的方式，使得数据表格更具可读性，以方便查阅和进一步分析处理。使用鼠标直接框选小范围单元格区域使用键配合选择大区域使用组合键或者组合键，选择当前数据区域使用键选择非连续单元格和非连续整行、整列使用组合键选择整行，然后用键进行扩展。注意

21、：避免输入法的干扰使用组合键，反向选择剩余的行；反之。使用+鼠标选取多工作表的相同区域。使用+G组合键选择特殊区域使用+G组合键选择特殊区域可以完成所有复杂的选定操作插入多个单元格常规操作使用鼠标右键更快捷的方法：选定区域、键、向下拖动分割箭头使用移动整列数据选择整列数据、键、拖动在选择区的一侧的十字移动键头使用移动整行数据选择整行数据、键、拖动在选择区的一侧的十字移动键头用组合键调用示例：同一个单元格里的文本设置多种格式“常规”就是没有任何数字格式在默认情况下，输入数字时，Excel会将该数据沿单元格右侧对齐。在输入分数时，为避免将输入的分数视作日期，需要在分数前键入0（零），再键入空格。E

22、xcel只保留15位的数字精度。如果有内置数字格式中还找不到就在原有格式基础上自定义：自定义数字格式的代码组成规则：默认条件下条件为零，组成结构如下：常用的数字位置标识符 G/通用格式:不设置任何格式，不变化#：数字占位符，只显示有效数值而不显示无意义的零。0：数字占位符，如果数字位数少于格式中的零的个数，则不足位置将显示无意义的零，与#组合控制小数点位数。?：数字占位符，当设置为等宽字体时，为无意义的零在小数点两边添加空格，以便使小数点对齐。示例：奇妙的自定义数字格式自定义数字格式经典实例常用的数字位置标识符%:百分数，：千位分隔符。E :科学计数符号“文本”：显示文本 ：文本占位符，引用原

23、始文本颜色：颜色代码颜色 n:显示调色板中颜色，0n56，条件值：设置格式条件示例：奇妙的自定义数字格式自定义数字格式经典实例Excel将日期和时间视为数字处理。使用时间分隔符按格式录入。与1900年1月1日的天数差，时间存储为小数部分，时间被看作天的一部分。如果要在同一单元格中同时键入时期和时间，需要在其间用空格分隔。如果要输入十二小时制的时间，需在时间后键入一个空格，然后键入AM或PM。时间和日期可以相加、相减，并可以包含到其他运算中。如果要在公式中使用时期或时间，需要用带引号的文本形式输入日期或时间值。如果要输入系统当前日期，可按CTRL+；(分号)。如果要输入系统当前时间，可按CTRL

24、+SHIFT+：(冒号)。日期格式：格式代码:m,mm,mmm,mmmm:月,英文月d,dd:日期ddd,dddd：英文星期aaaa,aaa:中文星期yy,yyyy:年份h，hh:小时m,mm,s,ss:分，秒示例：日期格式单元格文本数据分列 1、使用数据选项卡分列向导示例：单元格文本数据分列单元格文本数据分列2、构建内存数组实现=EVALUATE(&SUBSTITUTE(构造内存数组分列!$A1,)&)=INDEX(a,COLUMN(A:A)1、用SUBSTITUTE函数将分隔符“，”替换为“”，”，再在数据两端加“”和“”“”,构成一个文本数组2、用EVALUATE宏表函数生成数组得到结果

25、3、用INDEX函数取得各个元素。示例：构建内存数组分列单元格文本数据分列3、操作：填充-内容重排(两端对齐)示例：单元格文本数据分行多列数据合并成一列操作：1、利用粘贴板功能，进行两次粘贴2、利用运算符或Concatenate函数示例：1、多列数据合并成一列2、运用文本运算符或函数合并数据 行列区域直接转换利用选择性粘贴-转置功能示例：行列区域直接转换多行多列数据合并成单列操作：1、先行后列：利用粘贴板功能，进行两次粘贴，内容重排2、先列后行：先行列转置再进行先行后列操作示例：多行多列数据按行转为单列数据 包含标题的数据表排序当前选定区域排序自定义序列排序多条件排序用辅助列保留原始顺序示例：

26、返回排序前的状态自定义序列排序快速制作工资条RANK 函数返回一个数字在数字列表中的排位。数字的排位是其大小与列表中其他值的比值（如果列表已排过序，则数字的排位就是它当前的位置）。语法RANK(number,ref,order)Number 为需要找到排位的数字。Ref 为数字列表数组或对数字列表的引用。Ref 中的非数值型参数将被忽略。Order 为一数字，指明排位的方式。如果 order 为 0（零）或省略，Microsoft Excel 对数字的排位是基于 ref 为按照降序排列的列表。如果 order 不为零，Microsoft Excel 对数字的排位是基于 ref 为按照升序排列的列表。=RANK(A3,A2:A6,1)3.5 在上表中的排位(3)=RANK(A2,A2:A6,0)7 在上表中的排位(1)