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

1、EXCEL数据处理与分析讲师：梁国清EXCEL处理过程数据输入数据整理计算和分析数据信息第一部分 工作环境和基本操作第二部分 输入和导入数据第三部分 数据整理和编辑第四部分 数据查询第五部分 常规计算和分析提 纲最佳EXCEL学习方法工作环境基本操作数据输入与导入：数据有效性数据整理与编辑：设置单元格、排序数据查询：筛选、条件格式常规计算分析：分类汇总、合并计算、数据透视表高级分析：模拟运算、单变量求解、规划求解、分析工具EXCEL学习阶段刚开始接触基本掌握常用功能熟练使用常用功能+数据透视表+公式-+数组公式+VBA简单编程EXCEL技能+专业知识+行业经验特点：个人电脑中最广泛的办公软件最

2、好的表格处理软件一向具有良好的向下兼容性学习方法：循序渐进善用资源，学以致用多阅读，多实践学习进程EXCEL功能划分Excel数据操作与处理图表与图形公式与函数数据分析宏与VBA第一章 工作环境与基本操作 主要介绍Excel2007工作环境和基本操作 熟悉界面设置、工作簿和工作表的操作以及窗口显示等方面的内容。功能区旨在使 Excel 使用起来更简单，并帮助用户快速找到和使用所需的命令。功能区由选项卡、组和命令三部分组成 。您会发现您熟悉的命令都按照一定的方式组合在一起第一章 工作环境与基本操作Excel2007工作环境介绍功能区旨在使 Excel 使用起来更简单，并帮助用户快速找到和使用所需

3、的命令。功能区由选项卡、组和命令三部分组成 。您会发现您熟悉的命令都按照一定的方式组合在一起第一章 工作环境与基本操作Excel2007工作环境介绍命令更多，但仅在需要时出现 。第一章 工作环境与基本操作Excel2007工作环境介绍需要时可提供更多选项 第一章 工作环境与基本操作Excel2007工作环境介绍功能区上的每个按钮都有快捷方式。 快捷方式需要的键通常更少例如，在 Excel 中可以使用键提示居中对齐文本。第一章 工作环境与基本操作Excel2007工作环境介绍Excel 中的新页面布局视图(视图选项卡)列标题。行标题。页边距标尺。 第一章 工作环境与基本操作Excel2007工作

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

5、。例如，可以在专业化的程序中存储预算数据，选择所需的数据，然后将其导入到 Word 文档、Excel 工作表和 Access 数据库。第一章 工作环境与基本操作Excel2007工作环境介绍修改个性设置修改默认文件位置最近打开的文档相关设置添加序列第一章 工作环境与基本操作Excel2003工作环境介绍打开方法： 工具菜单选项第一章 工作环境与基本操作Excel基本操作让工作表永远显示标题行冻结&拆分窗口冻结窗格 利用这个功能用户可以指定某些行列永远留在屏幕，不会因滚动工作表消失拆分窗口 用户可以在不同区域查看同一工作表的不同部分。多窗口协作用户可以在不同窗口中查看同一工作簿，互不影响，保持同

6、步更新。并排比较在每一页上都打印行列标题或行列标志页面设置项打印标题顶端标题行第一章 工作环境与基本操作Excel基本操作撤销和恢复操作（Ctr+Z、Ctr+Y）撤销和恢复注意重复只有一级、撤销可以有16级或更多在多个工作表间切换工作表切换快捷键工作表导航栏基本快捷键操作全选当前数据区：;选定数据区域;定位到工作表区域的右下角保存：控制单元格的移动方向选定区域内循环录入数据（先选定范围，再回车）第二章 数据输入与导入 使用Excel进行数据处理分析的前提 本章主要掌握有关数据输入和导入的操作技巧，以及和数据输入和导入相关的列表、定义名称、数据有效性等功能技巧。第二章 数据输入与导入自动填充填充

7、种类数字填充文本填充文本+数字填充等差填充等比填充按日期填充特殊内容填充自定义序列填充填充方法使用菜单命令 使用鼠标左键 配合智能标记 配合键 根据范本填充 使用鼠标右键第二章 数据输入与导入序列 在自动填充与排序功能中起到重要作用自定义序列 导入序列进行更多的个性的填充初始选择扩展序列1, 2, 34, 5, 6,.9:0010:00, 11:00, 12:00,.周一周二, 周三, 周四,.星期一星期二, 星期三, 星期四,.一月二月, 三月, 四月,.一月, 四月七月, 十月, 一月,.1999 年 1 月, 1999 年 4 月1999 年 7 月, 1999 年 10 月, 2000

8、 年 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=错号XAL

9、T+178=M2平方 AlT+179=M3输入身份证号码（超过15位精度的数字录入） 把证件号码、银行帐号、零件编号等作为文本输入，以单引号开始的输入会认为是文本数据。在单元格中换行（Alt+Enter）第二章 数据输入与导入一些输入技巧神奇的选择性粘贴全部数值：只复制数值格式有效性验证选项：跳过空单元格转置：行列互换粘贴链接第二章 数据输入与导入斜线表头单斜线表头多斜线表头示例：灵活制作斜线表头第二章 数据输入与导入数据有效性设置选项卡输入信息出错警告第二章 数据输入与导入数据有效性了解有效性允许的条件数值限定输入数值范围序列列表限定输入数据日期限定输入日期范围时间限定输入时间范围自定义公式

10、：=LEFT（A1）=“A”（只允许输入字母A开头的文本）数据有效性可以被复制示例：限制输入重复数值限制重复订餐使用单元格下拉菜单规范输入第二章 数据输入与导入函数公式基础什么是公式？公式是以等号 “=“ 为引导，通过运算符按照一定顺序组合进行数据处理的等式。例如： 【 =5+2*3 】 结果等于 2 乘 3 再加 5。第二章 数据输入与导入函数公式基础 公式的组成部分函数：PI() 函数返回值 pi：3.142.引用：A2 返回单元格 A2 中的值。常量：直接输入公式中的数字或文本值，如 2。运算符：（脱字号）运算符表示将数字乘方，*（星号）运算符表示相乘。第二章 数据输入与导入函数公式基础

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

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

13、右括号。函数名：条件函数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的公式是为了有目的的计算结果，只能在单元格或内存中返回值，而不能对单元格进行删除或对其它单元格进行赋值。第二章 数据输入

14、与导入运算符类型1、算术运算符2、比较运算符算术运算符含义示例+（加号）加法3+3（减号）减法31负数1*（星号）乘法3*3/（正斜杠）除法3/3%（百分号）百分比20%（脱字号）乘方32比较运算符含义 示例=（等号）等于 A1=B1（大于号）大于 A1B1（小于号）小于 A1=（大于等于号）大于或等于 A1=B1=（小于等于号）小于或等于 A1=B1（不等号）不等于A1B1第二章 数据输入与导入运算符类型3、文本连接符可以使用与号 (&) 联接或连接一个或多个文本字符串，以生成一段文本。4、引用运算符引用运算符含义示例:（冒号）区域运算符，生成对两个引用之间所有单元格的引用（包括这两个引用）

15、B5:B15,（逗号）联合运算符，将多个引用合并为一个引用SUM(B5:B15,D5:D15)（空格）交集运算符，生成对两个引用中共有的单元格的引用B7:D7 C6:C8第二章 数据输入与导入公式运算的次序在某些情况中，执行计算的次序会影响公式的返回值。计算次序公式按特定次序计算值。Excel 中的公式始终以等号 (=) 开头，这个等号告诉 Excel 随后的字符组成一个公式。等号后面是要计算的元素（即操作数），各操作数之间由运算符分隔。Excel 按照公式中每个运算符的特定次序从左到右计算公式。使用括号若要更改求值的顺序，请将公式中要先计算的部分用括号括起来。优先顺序运算符说明1:（冒号）引

16、用运算符2（单个空格）3,（逗号）4负数（如 1）5%百分比6乘方7* 和 /乘和除8+ 和 加和减9&连接两个文本字符串（串连）10=比较运算符1112=14第二章 数据输入与导入函数分类Excel函数一共有11类：数据库函数：当需要分析数据清单中的数值是否符合特定条件时，可以使用数据库工作表函数 。日期与时间函数：工程函数：工程工作表函数用于工程分析。 财务函数：信息函数：可以使用信息工作表函数确定存储在单元格中的数据的类型。（IS） 逻辑函数：使用逻辑函数可以进行真假值判断查询和引用函数：当需要在数据清单或表格中查找特定数值，或者需要查找某一单元格的引用时，可以使用查询和引用工作表函数。

17、例如，如果需要在表格中查找与第一列中的值相匹配的数值，可以使用VLOOKUP工作表函数。如果需要确定数据清单中数值的位置，可以使用MATCH工作表函数。数学和三角函数：统计函数：文本函数用户自定义函数。 第二章 数据输入与导入名称名称：代表单元格、单元格区域、公式、常量值或Excel表。名称是一种有意义的简写形式，它更便于您了解单元格引用、常量、公式的用途。应理解为有名字的公式，有名称规范和限制，注意使用相对引用接受公式标志选项：可以将数据列表第一行字段做字段名使用，而且公式会随着字段名称改变。=批发价*数量第二章 数据输入与导入函数公式基础什么单元格引用？ ：用于表示单元格在工作表上所处位置

18、的坐标集相对引用&绝对引用:快速切换引用样式：在编辑状态下选中引用的公式中的”A1”，重复按F4键；引用样式依次改变：=$A$1、 =A$1、 =$A1、 =A1示例:在名称中使用常量与函数对于正复制的公式：如果引用是：它会更改为： $A$1（绝对列和绝对行）$A$1 A$1（相对列和绝对行）C$1 $A1（绝对列和相对行）$A3 A1（相对列和相对行）C3第二章 数据输入与导入函数公式基础关于引用：绝对引用、相对引用和混合引用之间的区别相对引用 公式中的相对单元格引用（如 A1）是基于包含公式和单元格引用的单元格的相对位置。如果公式所在单元格的位置改变，引用也随之改变。如果多行或多列地复制或

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

20、引用 公式中的绝对单元格引用（如 $A$1）总是在特定位置引用单元格。如果公式所在单元格的位置改变，绝对引用将保持不变。如果多行或多列地复制或填充公式，绝对引用将不作调整。默认情况下，新公式使用相对引用，您可能需要将它们转换为绝对引用。混合引用第二章 数据输入与导入函数公式基础三维引用样式Sheet2:Sheet6!A2:A5便于引用多个工作表 如果要分析同一工作簿中多个工作表上相同单元格或单元格区域中的数据，请使用三维引用。三维引用包含单元格或区域引用，前面加上工作表名称的范围。Excel 使用存储在引用开始名和结束名之间的任何工作表。|例如，=SUM(Sheet2:Sheet13!B5)

21、将计算 B5 单元格内包含的所有值的和，单元格取值范围是从工作表 2 到工作表 13。使用三维引用可以引用其他工作表中的单元格、定义名称，还可以通过使用下列函数来创建公式：SUM、AVERAGE、AVERAGEA、COUNT、COUNTA、MAX、MAXA、MIN、MINA、PRODUCT、STDEV、STDEVA、STDEVP、STDEVPA、VAR、VARA、VARP 和 VARPA。 三维引用不能用于数组公式中。 三维引用不能与交叉引用运算符（空格）一起使用，也不能用在使用了绝对交集的公式中。 第二章 数据输入与导入函数公式基础三维引用样式在移动、复制、插入或删除工作表时出现的情况 以下

22、示例演示在移动、复制、插入或删除三维引用中包括的工作表时出现的情况。该示例使用公式 =SUM(Sheet2:Sheet6!A2:A5) 对从 Sheet2 到 Sheet6 的每个工作表中的 A2 到 A5 单元格求和插入或复制如果在 Sheet2 和 Sheet6（本示例中的起止工作表）之间插入或复制工作表，Excel 将在计算中包含所添加的工作表中从单元格 A2 到 A5 的所有数值。 删除如果删除了 Sheet2 和 Sheet6 之间的工作表，Excel 将删除计算中相应的值。 移动如果将 Sheet2 和 Sheet6 之间的工作表移动到引用工作表区域之外的位置，Excel 将删除计

23、算中相应的值。 移动起止工作表如果将 Sheet2 或 Sheet6 移到同一工作簿中的其他位置，Excel 将对计算进行调整以包含它们之间的新工作表区域。 删除起止工作表如果删除了 Sheet2 或 Sheet6，Excel 将对计算进行调整以包含它们之间的工作表区域。第二章 数据输入与导入函数公式基础关于引用：引用其他工作表AVERAGE 工作表函数将计算同一个工作簿中名“市场”的工作表的 B1:B10 区域内的平均值。引用名为“市场”的工作表引用 B1 和 B10 之间的单元格（包括 B1 和 B10）！将工作表引用与单元格区域引用分开第二章 数据输入与导入导入外部数据导入文本数据导入W

24、ord表格导入Access数据库数据数据导入Internet网页数据有选择的导入数据在导入时创建参数查询第三章 数据编辑和整理使数据表更具可读可用性运用Excel的基本功能和简单函数对数据进行加工处理的方法，包括数据选取、数据格式设置、数据区域的转换以及数据排序等方法和技巧。调整数据存放的结构和数据显示的方式，使得数据表格更具可读性，以方便查阅和进一步分析处理。第三章 数据编辑和整理选取单元格区域使用鼠标直接框选小范围单元格区域使用键配合选择大区域使用组合键或者组合键，选择当前数据区域使用键选择非连续单元格和非连续整行、整列使用组合键选择整行，然后用键进行扩展。注意：避免输入法的干扰使用组合键

25、，反向选择剩余的行；反之。使用+鼠标选取多工作表的相同区域。使用+G组合键选择特殊区域第三章 数据编辑和整理选取单元格区域使用+G组合键选择特殊区域可以完成所有复杂的选定操作第三章 数据编辑和整理插入单元格区域插入多个单元格常规操作使用鼠标右键更快捷的方法：选定区域、键、向下拖动分割箭头使用移动整列数据选择整列数据、键、拖动在选择区的一侧的十字移动键头使用移动整行数据选择整行数据、键、拖动在选择区的一侧的十字移动键头第三章 数据编辑和整理设置单元格格式 无论为单元格应用了何种数字格式，都只会改变单元格的显示形式，而不会改变单元存储的真正内容，而可能是原始内容经过变化后的一种形式。用组合键调用示

26、例：同一个单元格里的文本设置多种格式第三章 数据编辑和整理设置单元格格式数字“常规”就是没有任何数字格式在默认情况下，输入数字时，Excel会将该数据沿单元格右侧对齐。在输入分数时，为避免将输入的分数视作日期，需要在分数前键入0（零），再键入空格。Excel只保留15位的数字精度。如果有内置数字格式中还找不到就在原有格式基础上自定义：自定义数字格式的代码组成规则：“大于条件值”格式；“小于条件值”格式；“等于条件值”格式；文本格式第三章 数据编辑和整理设置单元格格式默认条件下条件为零，组成结构如下： 常用的数字位置标识符 G/通用格式:不设置任何格式，不变化#：数字占位符，只显示有效数值而不显

27、示无意义的零。0：数字占位符，如果数字位数少于格式中的零的个数，则不足位置将显示无意义的零，与#组合控制小数点位数。?：数字占位符，当设置为等宽字体时，为无意义的零在小数点两边添加空格，以便使小数点对齐。示例：奇妙的自定义数字格式自定义数字格式经典实例正数格式；负数格式；零值格式；文本格式第三章 数据编辑和整理设置单元格格式常用的数字位置标识符 % : 百分数， ： 千位分隔符。E : 科学计数符号“文本”：显示文本 ：文本占位符，引用原始文本颜色：颜色代码颜色 n:显示调色板中颜色，0n56，条件值：设置格式条件示例：奇妙的自定义数字格式自定义数字格式经典实例第三章 数据编辑和整理设置单元格

28、格式日期和时间Excel将日期和时间视为数字处理。使用时间分隔符按格式录入。与1900年1月1日的天数差，时间存储为小数部分，时间被看作天的一部分。如果要在同一单元格中同时键入时期和时间，需要在其间用空格分隔。如果要输入十二小时制的时间，需在时间后键入一个空格，然后键入AM或PM。时间和日期可以相加、相减，并可以包含到其他运算中。如果要在公式中使用时期或时间，需要用带引号的文本形式输入日期或时间值。如果要输入系统当前日期，可按CTRL+；(分号)。如果要输入系统当前时间，可按CTRL+SHIFT+：(冒号)。第三章 数据编辑和整理设置单元格格式日期格式：格式代码:m,mm,mmm,mmmm:月 ,英文月d,dd :日期ddd,dddd：英文星期aaaa,aaa:中文星期yy,yyyy:年份h，hh:小时m,mm,s,ss:分，秒示例：日期格式第三章 数据编辑和整理数据区域转换单元格文本数据分列 1、 使用数据选项卡分列向导示例：单元格文本数据分列第三章 数据编辑和整理