数据统计与分析

第4章数据统计与分析Excel2010主讲人姜永生4.1Excel2010概述Excel是MicrosoftOfficesystem中的电子表格程序。可以使用Excel创建工作簿并设置工作簿格式，以便分析数据。同时可以使用Excel跟踪数据，生成数据分析模型，编写公式以对数据进行计算，以多种方式透视数据，并以各种具有专业外观的图表来显示数据。4.1Excel2010概述4.1.1Excel2010的窗口组成

标题栏：包含了快速工具栏、控制窗口的三个按钮（最小化按钮、还原按钮、关闭按钮）、程序名称“MicrosoftExcel”、正在编辑的文档名称等。状态栏：包含当前文档的页数/总页数，总字数、语言种类、当前文档的插入/改写状态、页面视图、显示缩放比例。

4.1Excel2010概述4.1.1Excel2010的窗口组成

功能区各选项卡简介：♥“开始”选项卡“开始”选项卡中包括常用的剪贴板、字体、对齐方式、数字、样式、单元格和编辑7个组。♥“插入”选项卡“插入”选项卡包括表格、插图、图表、迷你图、链接、文本、符号和特殊符号8个组♥“页面布局”选项卡“页面布局”选项卡包括主题设置、页面设置、调整为合适大小、工作表选项、排列5个组

4.1Excel2010概述1．Excel2010的窗口组成

功能区各选项卡简介：♥“公式”选项卡“公式”选项卡包括数据库、定义的名称、公式审核、计算4组，用于实现在Excel2010文档中数据统计与计算等功能♥“数据”选项卡“数据”选项卡包括获取外部数据、连接、排序和筛选、数据工具和分级显示5个组，用于在Excel2010文档中进行数据统计和筛选方面的操作。♥“审阅”选项卡“审阅”选项卡包括校对、语言、中文简繁转换、批注、更改5个组，主要用于对Excel2010文档进行校对和更改等操作4.1Excel2010概述1．Excel2010的窗口组成

功能区各选项卡简介：♥“视图”选项卡视图”选项卡包括工作薄视图、显示、显示比例、窗口和宏5个组，主要用于设置Excel2010操作窗口的视图类型，以方便操作♥“加载项”选项卡“加载项”选项卡包括菜单命令一个分组，加载项是可为Excel2010安装附加属性，如自定义的工具栏或其它命令扩展。♥选项卡面板中的按钮

按钮分为上下（或左右）两部分，单击按钮时上下两部分间有分隔线。4.1Excel2010概述4.1.1Excel2010的窗口组成

功能区各选项卡简介：♥编辑栏、单元格、工作表

（1）编辑栏编辑栏左边为名称栏，用于显示活动单元格或区域的地址。（2）工作区工作区用于编辑和处理数据。（3）工作薄与工作表工作簿是计算和储存数据的文件，一个工作簿就是一个Excel文件，其扩展名为“.xlsx”或“.xls”。一个工作簿可以包含多个工作表，这样可使一个文件中包含多种类型的相关信息，用户可以将若干相关工作表组成一个工作簿。4.1Excel2010概述5.1.1Excel2010的窗口组成

（4）单元格Excel2010的工作表由1048576（2^20）行、16384（2^14）列组成，每一行列交叉处即为一单元格。一个单元格最多可以保存32000个字符，每个单元格的地址由列号和行号来引用。①单元格的引用工作表中单元格地址的引用方法分绝对引用、相对引用、混合引用。其区别是引用地址里是否有“$”符号，没有就是相对引用。

相对引用：以列标和行号组成，如A1、B8，其中不包含符号“$”。

4.1Excel2010概述5.1.1Excel2010的窗口组成

绝对引用：列标和行号前都加符号“$”，如$A$1。

混合引用：列标或行号前加符号，如$A1（列绝对，行相对）；A$1（列相对，行绝对）。

②单元格区域的引用单元格区域的引用与单元格相同，也适用绝对引用、相对引用、混合引用三种引用方式。

A10:A20，表示A列第10到第20行的一系列单元格。B15:E15，表示第15行从B列到E列的一系列单元格。A10:E20，表示A10到E20矩形区域中的所有单元格。③交叉区域的引用两个或多个单元格区域有交叉，其中部分单元格在两个单元格区域中都包含，在计算数据时会容易出现重复计算。4.1Excel2010概述5.1.1Excel2010的窗口组成

两个单元格区域间用空格分隔，如（C1:C8A4:E4）例“=sum（C1:C8A4:E4）”表示计算（C1:C8）与（A4:E4）两个区域的数值之和。④不同工作表中单元格的引用引用另外一个不同工作表中的单元格，采用格式为：工作表名！单元格名。

如Sheet2！A4，表示引用工作表名为Sheet2中的A4单元格。

例“=average(Sheet3!B1:B10)”，表示利用函数计算本工作簿的“Sheet3”工作表中的B1:B10的平均值。

例“=sum(Sheet2:Sheet13!B5)”，表示把包括工作表Sheet2和工作表Sheet13之间所有工作表中的B5单元格的数据相加。4.1Excel2010概述4.1.2Excel2010的常用操作♥“文件”选项卡“文件”菜单在整个窗口的左上角。选择“文件”命令，弹出保存、另存为、打开、关闭、信息、最近所有文件、新建、打印、保存并发送、选项等常用的设置。

4.1Excel2010概述4.1.2Excel2010的常用操作♥视图设置Excel2010工作表包含三种视图模式，即普通视图、分页预览和页面布局视图，可以通过单击状态栏中右方的视图切换按钮，在三种视图模式之间进行切换。♥单元格和区域的选择（1）选择单元格在工作表中用粗边框框住的单元格称为“当前活动单元格”，可以通过鼠标指针单击某单元格使其成为“当前活动单元格”；也可以用键盘上四个光标箭头移动“当前活动单元格”。（2）选择单元格区域选择单元格区域即选定多个单元格

4.1Excel2010概述4.1.2Excel2010的常用操作♥单元格和区域的选择（3）选定特殊区域的快捷方式单击工作表左侧相应的行号选定一整行；

单击工作表上方相应的列号选定一整列；

单击工作表左上角的全选框选定整个工作表；

选定不连续的区域，则在选中一个区域之后，按住Ctrl键，再用上述方法选定其它区域；

选定连续的区域即在选中一个区域之后，按住Shift键，再用上述方法选定其它区域。

4.1Excel2010概述4.1.2Excel2010的常用操作♥“设置单元格格式”对话框打开“设置单元格格式”对话框的常用方法有三种。方法一：切换到“开始”选项卡，单击“数字”、“字体”、“对齐方式”组右下角的“对话框启动器”按钮。方法二：单击“开始”→“格式”按钮，弹出“格式”选项菜单，选择“设置单元格格式”命令。方法三：光标指向单元格，单击右键在弹出的快捷菜单中选择“设置单元格格式”命令。打开“设置单元格格式”的对话框，其中包括“数字”、“对齐”、“字体”、“边框”、“填充”、“保护”6个选项卡

4.1Excel2010概述4.1.2Excel2010的常用操作♥“设置单元格格式”对话框（1）“数字”选项卡用于设置单元格中数字的类型与格式，Excel内置了许多数字格式，供选择，如数值、日期、货币等。（2）“对齐”选项卡“对齐”选项卡中的设置，可以在单元格中对文本和数字进行定位、更改方向并指定文本控制功能。（3）“字体”选项卡“字体”选项卡，可以设置所选单元格中的字符的字体属性，通过“预览”框可预览所做的设置。4.1Excel2010概述4.1.2Excel2010的常用操作♥“设置单元格格式”对话框（4）“边框”选项卡Excel中，可以给单个单元格、单元格区域周围加边框，还可以从单元格的左上角到右下角或从单元格的左下角到右上角画线；可更改线条样式、线条粗细或线条颜色。（5）“填充”选项卡“填充”选项卡上的设置，可以为选定的单元格设置背景色（6）“保护”选项卡“保护”选项卡提供“锁定”、“隐藏”两个选项，用来保护工作表中的数据和公式。4.2数据的输入4.2.1文本输入Excel文本包括中文、英文、数字或其他键盘能键入的符号，文本输入时默认为左对齐。某些数字如电话号码、邮政编码常常当作字符处理。此时只须在输入数字前加上一个撇号“`”，Excel将把它当作文本看待，采用左对齐。如果输入的文本长度超出单元格宽度，若右边单元格无内容，则扩展到右边列；否则，截断显示。若出现“#####”字样，则表示列宽不足。

4.2数据的输入4.2.2数值输入

Excel中，输入的数值可以是数字0～9、+、-、（）、/、E、e、$、%、小数点“.”和千分位符号“,”等，数值输入时默认为右对齐。在输入分数时，为避免与日期相混淆，要求在数字前加一个0与空格。输入负数时，可将负数置于括号“（）”中。Excel数值输入与数值显示未必相同，如输入数字长度超出单元格宽度（采用“常规”格式的数字长度为11位），Excel自动以科学计数法表示。4.2数据的输入4.2.3日期时间输入

日期型数据，用斜杠“/”或减号“-”分隔日期的年、月、日部分。时间型数据，系统自动以24小时制表示。如果要以12小时制表示，则在输入的时间后加一个空格，然后再输入AM（上午）或PM（下午）。Excel将日期和时间视为数字处理。在键入了Excel可以识别的日期或时间数据后，单元格的格式会从“常规”数字格式改为内置的日期或时间格式。默认状态下，日期和时间项在单元格中右对齐。如果Excel不能识别输入的日期或时间格式，输入的内容将被视作文本，并在单元格中左对齐。4.2数据的输入4.2.3日期时间输入

如果要在同一单元格中同时键入日期和时间，日期与时间之间要用空格分隔。输入当前日期按组合键“Ctrl+；”，输入当前时间按组合键“Ctrl+Shift+：”。4.2数据的输入4.2.4设置单元格数据类型

常规单元格不包括任何特定的数据格式，通常系统按输入的数据进行自动判别。给单元格输入数据，可以先设置单元格数据类型，然后再输入数据。♥设置单元格的数据类型♥自定义数字格式要创建自定义数字格式，可使用“设置单元格格式”对话框中的“数字”选项卡，请在“类别”列表中单击“自定义”选项，然后在“类型”文本框中键入自定义数字格式。4.2数据的输入4.2.5公式与函数输入♥输入公式输入公式时，有以下的规则：（1）在输入公式前，先输入一个“=”号或“+”号。（2）公式中使用的运算符主要有四种：算术运算符、比较运算符、文本运算符、引用运算符。算术运算符包括：+、-、*、/、%（百分数）和^（乘方）；比较运算符包括：=、＞、＞＝、＜、＜＝、；文本运算符：＆，用于连接两个文本；

引用运算符包括：:（冒号）、，（逗号）、空格。括号运算：在Excel中，括号只有一种，即圆括号“（）”，平常书写中的其它括号均要转换为圆括号才能参加运算。4.2数据的输入4.2.5公式与函数输入♥输入函数

Excel提供了许多内置函数，对数据运算和分析带来了方便。内置的函数包括：财务、日期与时间、数学与三角函数、统计、查找与引用、数据库、文本、逻辑、信息等14大类。函数的语法形式为：函数名（参数1，参数2，…），其中的参数可以是常量、单元格、区域、区域名或其它函数。（1）函数的输入方法：直接输入法，粘贴函数法。（2）自动求和，Excel提供了“自动求和”功能，可以快捷地批量输入sum函数。4.2数据的输入4.2.5公式与函数输入

♥公式审核

Excel2010提供了公式审核功能，用于追踪引用单元格，显示公式、错误检测等。通过“公式”选项卡中的“公式审核”组所提供的相应功能，来检查公式使用情况。

4.2数据的输入4.2.6数据的快速输入

Excel中输入有规律的数据，可以考虑使用数据自动输入功能，方便、高效输入等差、等比、自定义序列的数据。常用快速填充数据的方法主要有以下几种：♥利用鼠标拖动填充♥利用命令填充♥选择列表填充♥自定义序列♥同时给多个单元格输入数据♥同时给多个工作表中输入数据♥数据有效性输入

4.3数据的编辑4.3.1数据的编辑♥数据修改数据修改有3种方法：一是选择要修改的单元格，直接重新输入；二是双击要修改的单元格，在单元格中修改；三是选定要修改的单元格，然后在编辑栏内进行修改。♥数据删除数据删除有两个概念：数据清除和数据删除。数据清除是把单元格的内容清除，使之成为空单元格，数据清除后单元格本身仍留在原位置不变。数据删除是把单元格连同单元格的全部内容都从工作表中删除。

4.3数据的编辑4.3.1数据的编辑♥数据移动和复制在Excel中，复制和移动单元格或单元格区域中的数据，既可以在同一张工作表中进行，也可以在不同的工作表之间进行。数据移动和数据复制都有可以：利用剪贴板移动；利用鼠标拖动。选择性粘贴是指一个单元格含有多种特性，如内容、格式、批注等，另外它还可能是一个公式，含有有效规则等，数据复制时往往只须复制它的部分特性；此外复制数据的同时还可以进行算术运算、行列转置等。这些都可以通过选择性粘贴来实现。

4.3数据的编辑4.3.1数据的编辑♥合并与拆分单元格Excel表格编辑过程中，通常会合并与拆分单元格，以符合编辑表格的需求，如表格的标题等。合并与拆分单元格，常用的方法如下：方法一：利用“开始”选项卡的快速合拼功能。方法二：利用“设置单元格格式”对话框。

4.3数据的编辑4.3.1数据的编辑♥单元格、行、列的插入Excel编辑表格式，根据需要可随时插入单元格、行、列，以调整表格满足实际需求。♥查找和替换查找与替换的使用方法与Word中相同，查找与替换是指从当前活动单元格开始搜索指定的内容。♥给单元格增加批注虽然每个单元格可以输入多达32000个字符，但往往不能够同时表达所有的含义，可以给单元格增加批注，来标记与说明。

4.3数据的编辑4.3.1数据的编辑♥撤消操作♥快速设置表格格式通过“设置单元格格式”对话框，可以设置表格与单元格格式，Excel2010提供了更加快捷的方式。切换到“开始”选项卡，在“样式”组中包含“套用表格格式”、“单元格格式”等选项，可快速的帮用户设置选中表格或单元格格式。♥单元格名称单元格名称是指给单元格重新定义的名称。

两大功能

定位：可通过名称快速查找单元格的位置。

计算：单元格或单元格区域的名称，可以直接引用到公式中，以便直观的看到公式的含义。

4.3数据的编辑4.3.2拆分或冻结窗格

窗格是文档窗口的一部分，以垂直或水平条为界限并由此与其他部分分隔开。在实际工作中，有时需要多窗口操作，或锁定Excel工作表中的表格标题。切换到“视图”选项卡，单击“拆分”与“冻结窗格”按钮，可实现分隔窗口或标题锁定功能。

4.4数据统计与分析4.4.1数据的排序、筛选与分类汇总

♥数据的排序（1）快速排序（2）多条件排序（3）按笔划排序（4）自定义排序

♥分类汇总分类汇总是对数据库中的数据进行分类统计。分类汇总需要分两步完成，先排序再汇总。

首先对数据按分类的字段排序，

其次执行分类汇总命令.

4.4数据统计与分析4.4.1数据的排序、筛选与分类汇总

♥自动筛选与高级筛选（1）自动筛选Excel2010使用自动筛选来筛选数据，可以快速、方便地查找和使用单元格区域数据的子集。

执行自动筛选操作，数据清单中必须有列标号。（2）自定义自动筛选自动筛选完成后，若不能达到所需的要求，则可选择“自定义筛选”命令，实现用自定义条件筛选所需的数据。4.4数据统计与分析4.4.1数据的排序、筛选与分类汇总

♥自动筛选与高级筛选（3）高级筛选执行高级筛选命令，数据清单必须有列标题，同时需要建立条件区域来设置复杂的筛选条件。

①条件区域在使用“高级筛选”命令之前，必须指定一个条件区域，设置出符合条件的行或列，以定义筛选条件。条件区域至少有两行组成，第一行为字段名，从第二行起输入查找条件。条件区域中，若有多个条件（复合条件），需要在各条件间进行逻辑运算，其中包括“与”运算和“或”运算。

4.4数据统计与分析4.4.1数据的排序、筛选与分类汇总

“与”运算即多个条件表达式中若有一个条件为假，则其值为假。在条件区域输入时，“与”运算的条件表达式要放置在同一行。“或”运算即多个条件表达式中若有一个条件为真，则其值为真。在条件区域输入时，“或”运算的条件表达式要放置在不同行。

4.4数据统计与分析4.4.1数据的排序、筛选与分类汇总

②应用高级筛选③清除数据清单的筛选对于不再需要的筛选操作，可以将其功能清除，恢复数据清单原来的状态。4.4数据统计与分析4.4.2数据透视表

数据透视表是一种可以快速汇总大量数据的交互式方法。使用数据透视表可以深入分析数值数据，它可以同时灵活变换三组需要统计的字段对一组数值进行分析和统计，统计可以是求和、平均值、计数、最大、最小等。数据透视表建立:单击“插入”→“数据透视表”按钮，通过操作向导来完成创建任务，最容易出错的是“数据透视表字段列表”中“列”与“行”标签、统计“数值”项的选择与“值字段设置”。

4.4数据统计与分析4.4.3常用函数的使用

♥条件分支IF函数IF函数用于判断条件表达式（logical_test）是否成立，同时返回指定的值。函数格式为：IF(logical_test,[value_if_true],[value_if_false])♥频率分布统计函数