Excel2007数据分析与处理

1、ExcelExcel数据分析与处理数据分析与处理本章主要内容1 Excel 2007概述2 Excel 2007的基本操作3 单元格的编辑 4 单元格的格式 5 数据处理6 创建数据图表7 页面设置与打印输出21 Excel 2007概述Excel 2007简介1.1 Excel 2007的启动与退出1.2 Excel 2007窗口的基本组成1.3 工作薄的基本操作3 Excel 2007简介4ExcelExcel的中文意思就是超越。的中文意思就是超越。 从从19831983年起，先后推出了年起，先后推出了Excel.0Excel.0、Excel 5.0Excel 5.0、Excel Exce

2、l 6.06.0、Excel 7.0Excel 7.0、Excel 97Excel 97，Excel 2000Excel 2000，Excel 2002Excel 2002，Excel 2003Excel 2003，直到现在的，直到现在的Excel 2007Excel 2007。 ExcelExcel具有十分友好的人机界面和强大的计算功能及出具有十分友好的人机界面和强大的计算功能及出色的图表功能色的图表功能，是最，是最出色的电子表格软件出色的电子表格软件。主要功能 能同时编辑多张工作表。 提供丰富的函数，对数据作复杂的数学分析和报表统计。 具有丰富的制图功能，能使图、文、表有机地结合。 可以从

3、不同角度对数据进行分析，对数据进行重组。 能与indows下运行的其它软件共享数据资源。1.1 Excel 2007的启动与退出启动启动“开始开始”“程序程序”“Microsoft Office”“Microsoft Office”Microsoft Office Excel 2007Microsoft Office Excel 2007在在“运行运行”对话框中输入对话框中输入“excel”excel”（机房里（机房里20032003与与20072007两个版本同时装的情况）两个版本同时装的情况）退出退出ExcelExcel是多文档的操作界面，关闭某工作薄与退出是多文档的操作界面，关闭某工作薄

4、与退出ExcelExcel是有区别的。是有区别的。61.2 Excel 2007窗口的基本组成7工作簿工作簿单单元元格格工作表标签工作表标签活动单元格地活动单元格地址、名称框址、名称框行号行号列号列号活动活动单元格单元格填充填充柄柄编辑栏编辑栏1.3 工作薄的基本操作新建工作表的缺省名Book1，Book2，Book3，保存 xlsx（注意后缀名，2003为xls）打开关闭82 Excel 2007的基本操作2.1 单元格的选择与活动单元格行、列的选择单元格的选择单元格区域的选择活动单元格的移动91. 选择行和列10单击行号单击行号选择整行单击列号单击列号选择整列单击行号或列号，然后拖动鼠单击

5、行号或列号，然后拖动鼠标到相邻的行号或列号；或者标到相邻的行号或列号；或者按按Shift键。键。选择相邻的行或列单击第一个行号或列标，按住单击第一个行号或列标，按住Ctrl，再单击其他的行号或列，再单击其他的行号或列号号选择不相邻的行或列2. 选定单元格定位一个单元格 直接鼠标单击某个单元格选定多个相邻的单元格用鼠标拖动或者Shift键选定多个不相邻的单元格 先选中一个单元格，然后按住Ctrl键的同时，点击别的需要选中的单元格113. 选定单元格区域单击选取区域左上角的单元格，按住Shift键，将鼠标指针指向要选取区域的右下角单元格或者用鼠标从左上角单元格滑动到右下角单元格12选定一个单元格区

6、域选择不相邻的单元格区域先选中一个单元格区域，然后按住先选中一个单元格区域，然后按住CtrlCtrl键，再选取其他区键，再选取其他区域域2 Excel 2007的基本操作2.2 数据的录入1. 数据类型2. 不同类型数据的录入3. 序列填充4. 给单元格添加批注131. Excel的数据类型数据类型：文本、数值、日期和时间，还有公式和函数等14输入文本数据文本输入指键盘上可键入的任何符号，文本默认为左对齐。当输入的字长度超出单元格宽度时，右边单元格无内容时，则扩展到右边列；否则截断显示。确认输入：回车或“输入”按钮。取消输入：Esc键或者“取消”按钮。15输入数值数据数值输入 数字09和符号+

7、、-、$、E、/、%、小数点(.) 、千分号(,)等数值默认为右对齐。单元格中默认只能显示11位有效数字，超过长度就用科学计数法来表示如果数字太长无法在单元格中显示，单元格将以“#”显示，此时需增加列宽。16如何输入身份证号？如何输入身份证号？输入日期和时间日期日期使用斜线“/”或连字符“-”输入。例如：“12/3/20”, “2012-3-20”按Ctrl+“;”（分号）键输入当天日期。时间用冒号“:”输入，一般以24小时格式表示时间，若以12小时格式表示，需在时间后加上A(AM)或P(PM)。A或P与时间之间要空一格。按Ctrl+Shift+“;”（分号）键输入当前时间。在同一单元格中可以

8、同时输入时间和日期，不过彼此之间要空一格。可使用函数,=now()17特殊字符的输入1.上标和下标的录入22+232.带圈数字录入3.其他符号18设置单元格的格式“开始”- “数字”选项组右下角的“设置单元格格式”按钮，打开设置格式对话框，来调整类型及相关的设置193. 序列填充当要输入重复的或者有规律的数据和序列时，使用自动填充功能来提高工作效率。有规律的数据指等差、等比、系统预定义的数据填充系列以及用户自定义的新序列。用填充柄实现快速填充：将鼠标指针指向要复制数据的单元格右下角的填充柄（鼠标指针变成十字），按住左键拖过要填充的单元格即可。20鼠标拖拽填充柄当选中单元格内容为纯字符、纯数字或

9、是公式时，填充相当于复制；当选中单元格内容为文字数值混合时，填充时文字不变，数字递增；如果有连续的单元格存在等差、等比关系时，在按相应关系填充，但按Ctrl键拖曳时则始终是复制。21 给单元格添加批注批注的作用解释说明打印时不会输出223 单元格的编辑3.1 单元格中数据的修改3.2 单元格、行、列的插入与删除3.3 单元格、行、列的复制与移动233.1 单元格中数据的修改 选择对象，再单击“开始”选项卡 下的“编辑”组中的“清除”（1）清除格式和内容（2）清除格式（3）清除内容（4）清除批注243.2 单元格、行、列的插入与删除插入方法 先选择位置，即插入的位置 再单击“开始”选项卡“插入”

10、 25注意 若插入的是行或列，则原来的若插入的是行或列，则原来的行或列顺延行或列顺延 若插入单元格，则出现若插入单元格，则出现“提示提示对话框对话框”，由用户选择：，由用户选择：“活活动单元格右移动单元格右移”，“活动单元活动单元格下移格下移”，“整行整行”或是或是“整整列列”3.2 单元格、行、列的插入与删除删除方法 选中对象，执行“开始” 选项卡“删除”命令注意 若删除单元格，会弹出“删除”对话框，由用户选择：“右侧单元格左移”，“下方单元格上移”，“整行”或是“整列”263.3单元格、行、列的复制与移动方法1：鼠标拖动 复制：将鼠标移到被选择对象的边框变成虚十字箭头，按住左键拖动到目的地

11、 移动：拖动虚十字箭头的同时按Ctrl键方法2：右键快捷菜单 复制：先复制，后粘贴 移动：先剪切，后粘贴方法3：快捷键27283.3 单元格、行、列的复制与移动选择性粘贴 复制：先复制，后选择性粘贴4 单元格的格式4.1 单元格格式设置4.2 工作表的设置294.1 单元格格式的设置1. 行高、列宽2. 字体的设置3. 对齐方式4. 表格线的设置（内边框与外边框）5. 图案的设置6. 条件格式的设置7. 自动套用格式8. 使用与创建单元格样式9. 合并单元格306. 条件格式的设置设置： “开始”选项卡“样式”组“条件格式” 按钮色阶关系格式（大于、小于等）编辑与管理条件格式317. 自动套用

12、格式8. 使用与创建单元格样式3 工作表的设置1. 添加工作表2. 删除工作表3. 重命名工作表4. 移动、复制工作表5. 设置工作表标签颜色331. 添加工作表342. 删除工作表删除的工作表不能再恢复！353. 重命名工作表 右键单击工作表标签，选择“重命名” 双击要重命名的工作表标签364. 移动、复制工作表在同一工作簿中移动工作表，直接拖动表标签；复制工作表，则用鼠标拖动表标签时按住ctrl键不同或相同工作薄之间功能区：“开始” “单元格”“格式”右键菜单方式375. 设置工作表标签颜色方法如下工作表标签，右键快捷菜单功能区命令：“开始”“单元格”“格式”385 数据处理5.1 数据处

13、理的相关概念与方法395.1 数据处理的相关概念与方法1. 公式2. 常用函数3. 排序4. 筛选5. 分类汇总6. 数据透视表401. 公式以“=”开始的单元格数据称为公式 公式中的运算符 算术运算符 比较运算符 文本运算符 &形成一个长文本，即文本的合并补充：公式的输入、复制41公式的输入、复制1) 单击要输入公式结果的单元格。2) 输入等号“=”。3) 输入公式，所输入的公式出现在编辑栏中。4) 按回车键或单击“输入”按钮。输入后的公式只能在编辑栏中看到，在单元格中只显示其结果。421 1、直接输入、直接输入公式的输入、复制1) 单击要输入公式结果的单元格。2) 输入等号“=”。

14、3) 单击在公式中出现的第一个单元格，其地址将出现在编辑栏中。4) 输入运算符。5) 按照第3、4步的方法，连续输入单元格地址和运算符，直到公式结束。6) 按回车键或单击“输入”按钮。 432 2、引用单元格输入、引用单元格输入即用鼠标单击要引用的即用鼠标单击要引用的单元格来完成输入单元格来完成输入公式的输入、复制如果其他单元格需要用同样的公式进行数据计算时，可以复制公式。操作步骤如下： 选定被复制公式的单元格，单击 “复制”。 选定要粘贴公式的单元格，单击“选择性粘贴”命令，打开“选择性粘贴”对话框，然后选择“公式”单选按钮。 单击“确定”按钮，要复制公式的单元格内显示按照该公式计算的结果。

15、44还可以用填充柄自动填充还可以用填充柄自动填充功能来实现复制公式。功能来实现复制公式。2. 常用函数函数实际上是一些预定义好的特殊的公式，以完成普通公式所不能完成的功能。函数的语法结构 =函数名（参数1，参数2，）函数名：如Sum()，Average()等，代表函数的功能，在系统中是唯一的。参数：它指明函数所操作的数据，可以为多种类型的数据，还可以是另一个（些）函数，另外，还可以为单元格区域的引用，如：A1:H3代表从A1到H3的这部分交叉单元格452. 常用函数与手工输入公式方法一样，在编辑栏中直接输入，如“=MAX(B2:D2)”461.1.手工输入函数手工输入函数2.2.使用使用 插入

16、函数插入函数 对话框输入对话框输入1) 选定要输入函数的单元格。选定要输入函数的单元格。2) 单击单击“插入函数插入函数”命令或者单击编辑栏左侧的命令或者单击编辑栏左侧的“插入函插入函数数”按钮按钮 ，打开，打开“插入函数插入函数”对话框。对话框。2. 常用函数INT ROUND IF(COUNTIF) Sum(SUMIF)averagemaxMinVLOOKUP47演示演示482. 常用函数int向下取整数，取最接近的小于参数向下取整数，取最接近的小于参数的整数的整数 如如=int(3.5)，结果为，结果为3=int(-6), 结果为结果为-5492. 常用函数round按指定位数四舍五入按

17、指定位数四舍五入如如=ROUND(123.4567,0) 结果为结果为123 =ROUND(123.4567,-2) 结果为结果为100 =ROUND(172.4567,-2) 结果为结果为200 =ROUND(123.4567,2) 结果为结果为123.46502. 常用函数if(条件条件,结果结果1,结果结果2)根据条件判断结果根据条件判断结果如果条件为真，整个函数的值为结果如果条件为真，整个函数的值为结果1，若条件为假，则函数值为结果若条件为假，则函数值为结果2如如 =if(1010.1, 1,0) 结果为结果为0512. 常用函数countif(单元格区域单元格区域,条件条件) 统计单

18、元格统计单元格区域中符号条件的单元格数量区域中符号条件的单元格数量如如=countif(A1:A100,1) 统计统计A1到到A100中，值大于中，值大于1的单元格个数的单元格个数 =countif(B2:B100,男男)统计统计B2到到B100中，值为中，值为 男男 的单元格个数的单元格个数 =countif(C2:C100,60)统计统计C2到到C100中，中，值为值为 60 的单元格个数的单元格个数表达式表达式文本文本数字数字演示演示3. 排序52排序种类排序种类简单排序简单排序多多关键字排序关键字排序自定义序列排序自定义序列排序3. 排序简单排序选中要排序的单元格区域“开始”- “编辑

19、”-“排序与筛选”-“升序”或者“降序”533. 排序多关键字排序选中要排序的单元格区域“开始”- “编辑”-“排序与筛选”-“自定义排序”54主要关键字主要关键字次要关键字次要关键字3. 排序自定义序列排序选中要排序的单元格区域还是选择“自定义排序”55自定义序列自定义序列 4.筛选只显示用户关心的数据，隐藏不满足条件的记录类似“淘”选中要排序的单元格区域中的任意一个单元格“开始”- “编辑”-“排序与筛选”-“筛选”56 4.筛选57简单筛选简单筛选自定义自定义筛选筛选 4.筛选58清除筛选清除筛选5. 分类汇总分类汇总是数据分析的一种方法步骤 先对分类汇总的字段 在要进行分类汇总的数据清

20、单里，选取一个单元格。“数据” “分级显示” “分类汇总”，打开“分类汇总”对话框 在该对话框中，选择分类的字段，汇总方式（求和、平均等）以及选定汇总项，单击“确定”按钮。注意：可嵌套汇总596.数据透视表60数据透视表是一种可以快速汇总、分析大量数据表数据透视表是一种可以快速汇总、分析大量数据表格的交互式工具。格的交互式工具。 若要创建数据透视表，要求数据源必须是比较规则的数据, 如：数据记录中最好不要有空白单元格或各并单元格；每个字段中数据的数据类型必须一致. 数据越规则，数据透视表使用起来越方便.6162报表生成报表生成区域区域数据数据透视表字透视表字段列表段列表可以根据此字段对报表实可

21、以根据此字段对报表实现筛选，可以显示每类项现筛选，可以显示每类项目相关的报表目相关的报表此字段种的每类项目会成为列此字段中的每类项目会成为一行会自动计算此字段的汇总信息会自动计算此字段的汇总信息（如求和、计数、平均值、方（如求和、计数、平均值、方差等等差等等) )数据透视表字段的使用数据透视表字段的使用使用数据透视表查看摘要与明细信息63使用数据透视表组合数据64对日期分类组合对数值数据分类组合对文本字段分类组合注意：要将字段进行组合，要求此字段里所有数据必须为同一格式，并且不能有空值，任何一个单元格为空也将导致不能组合。数据透视表的数据汇总方式65在汇总方式中有一共有在汇总方式中有一共有 1

22、1 11 种函数，包括：求和、计数、数值计数、平种函数，包括：求和、计数、数值计数、平均值、最大值、最小值、乘积、标准偏差、总体标准偏差、方差、总体均值、最大值、最小值、乘积、标准偏差、总体标准偏差、方差、总体方差。方差。“数值数值”区域中的数据默认情况下通过以下方法对数据透视表中的基础区域中的数据默认情况下通过以下方法对数据透视表中的基础源数据进行汇总：对于数值使用源数据进行汇总：对于数值使用“求和求和”汇总方式，对于文本值使用汇总方式，对于文本值使用“计数计数”汇总方式。汇总方式。但是，我们可以手动更改汇总方式还可以自定义计算方式。但是，我们可以手动更改汇总方式还可以自定义计算方式。改变数

23、据透视表的值显示方式66 前面我们改变的数据透视表的汇总方式其实就相当于使用工作表函数对数据的统计汇总。 其实通过改变数据透视表的值显示方式，还可以对数据按照不同字段做相对比较。方法：将光标选中数据透视表中的数值字段，然后在“选项”工具面板的“活动字段”组中单击“字段设置”命令，在弹出的“值字段设置”对话框的选择“值显示方式”选项卡，然后在下拉列表中选择相应的值显示方式即可。如果我们希望同时查看计算字段的百分比和数量合计能不能做到呢？如果我们希望同时查看计算字段的百分比和数量合计能不能做到呢？在数据透视表中使用计算字段 如，需要按照金额的 6%计算税金。可以使用计算字段，向数据透视表添加一个新

24、的字段“税金”。67怎样删除添加字段？怎样删除添加字段？数据透视表的数据源控制与刷新 当我们使用数据透视表生成报告后，如果数据源变化了，数据透视表报告的信息会不会自动跟着变化呢？ 1.数据源的值更改了 2.数据源增加了新的记录，范围变化了 默认情况下数据透视表并不会自动更新。 1）我们可以通过数据透视表的“选项”工具面板里的“数据”组的“刷新”命令来刷新数据透视表。这样数据透视表报告就变成最新的了。 2）可以通过数据透视表的“选项”工具面板里的“数据”组的“更改数据源”命令来重新选择数据透视表所引用的数据范围。68建立数据透视图 使用数据透视表生成数据透视图696 编辑图表与数据分析处理图表是

25、将表格中的数据用图形表示出来，即数据的可视化。通过图表，就可以很容易看出Excel表格中数据的宏观走向，总结数据的规律或从图表中很快得出自己需要的结论图表是相互联系的，当数据发生变化，图表也相应地发生变化。Excel还提供多种图表类型，并支持用户的自定义类型。707.编辑图表与数据分析处理7.1 创建图表7.2 编辑图表71Excel中使用的图表类型图表的使用：（1）嵌入式图表嵌入式图表是浮在工作表之上，可以通过操作来改变图表的位置和大小。嵌入式图表的另一个特点就是图表和数据在一个工作表上，这样便于使用图表和数据。（2）图表工作表图表占据整个工作表，数据和图表在不同的工作表内，图表将新建一个工作表。但当打印一个图表工作表的时候会比较方便。72Excel中使用的图表类型列举7个大类；每个大类又细分为多种图表类型。73柱形图折线图饼图XY散点图面积图条形图圆环图柱形图柱形图是将各个数据点绘制成不同高度的柱形，以显示各种数据的大小，比较直观。274销额（万元） 2001802102202803203303404004503503