单元 4 数据统计和分析软件Excel 2010

学习目标 了解和掌握Excel 2010的基础知识与基本操作； 掌握工作表的建立、编辑以及格式化操作； 掌握函数和公式的应用； 掌握利用函数和工作表的数据进行分析、统计和应用； 掌握图表处理。

4.1 Excel 2010基础 4.1.1 Excel 2010工作表 Excel 2010是一个二维表格。这个二维“表格”称为工作簿，工作簿由若干张工作表组成，默认情况下新建的工作簿为三张工作表。但用户可根据需要修改默认值，默认值最少为1最多为255个工作表。 每个工作表有1 048 576行，16 348列组成。每个行与列的交叉点称为单元格，单元格所在位置称为单元格地址，如第B列与第5行交叉点的单元格地址是B5。工作表左上角的单元格为A1，右下角的单元格是XFD1048576。每个单元格可以容纳32 767个字符。

4.1.2 Excel 2010基本概念 1．Excel 2010的用户界面

2．Excel 2010专业术语 （1）工作簿 （2）工作表 工作簿是指Excel环境中用来储存并处理工作数据的文件。也就是说Excel文档就是工作簿。它是Excel工作区中一个或多个工作表的集合，其扩展名为.xlsx。 （2）工作表 工作表是用于存储和处理数据的一个二维电子表格。初始化时，工作簿中包含三张独立的工作表，分别命名为Sheet1、Sheet2、Sheet3，并在工作区显示工作表Sheet1，该表就是当前工作表。

（3）单元格和单元格区域 （4）单元格地址 单元格是组成工作表的最小单位，用户可以在单元格中输入各种各样类型的数据、公式和对象等内容。 单元格区域是一个矩形块，它是由工作表中相邻的若干个单元格组成的。 （4）单元格地址 每个单元格的行列位置称为单元格地址（或称坐标），单元格的地址表示方法是“列标行号”。例如，A3就代表A列的第3行的单元格。

（5）单元格引用 方法1： 方法2： 方法3： 通常单元格坐标有三种表示方法： 相对坐标（或称相对地址）。由列标和行号组成，如A1、B5、F6等。 方法2： 绝对坐标（或称绝对地址）。由列标和行号前全加上符号“$”构成，如$A$1、$B$5、$F$6等。 方法3： 混合坐标（或称混合地址）。由列标或行号中的一个前加上符号“$”构成，如A$1、$B5等。

4.1.3 Excel 2010基本操作 1．工作簿的操作 2．工作表的操作 （1）插入新工作表 （2）移动或复制工作表 （3）删除工作表 （4）重命名工作表 （5）改变工作表标签颜色 （6）更改新工作簿中的默认工作表数

3．单元格及单元格区域的操作 （1）选中单元格或单元格区域 （2）单元格（或单元格区域）的插入与删除 （3）清除 （4）移动与复制 （5）查找与替换

4．工作表的保护和共享 （1）保护工作簿的结构和窗口 （2）保护工作表 （3）保护单元格或单元格区域

4.2 工作表数据输入与工作表格式化 4.2.1 工作表数据输入基础 1．建立工作表 2．Excel 2010数据统计分析过程

1．使用“开始”选项卡“数字”组中的按钮快速格式化数字“格式” 2．使用“设置单元格格式”对话框设置数字格式 4.2.2 文本输入 在Excel中输入文本时，默认对齐方式是单元格内靠左对齐。在一个单元格内最多可以存放32 767个字符。 4.2.3 数字输入 输入数字时，自动将其单元格右对齐。有效数字包含0～9、+、-、（）、/ 、$、%、.、E、e等字符。 1．使用“开始”选项卡“数字”组中的按钮快速格式化数字“格式” 2．使用“设置单元格格式”对话框设置数字格式

分 类 说 明 常规 不包含特定的数字格式 数值 可用于一般数字的表示，包括千位分隔符、小数位数，还可以指定负数的 显示方式 货币 可用于一般货币值的表示，包括使用货币符号￥、小数位数、还可以指定 负数的显示方式 会计专用 与货币一样，只是小数或货币符号是对齐的 日期 把日期和时间序列数值显示为日期值 时间 把日期和时间序列数值显示为时间值 百分比 将单元格值乘以100并添加百分号，还可以设置小数点位置 分数 以分数显示数值中的小数，还可以设置分母的位数 科学记数 以科学记数法显示数字，还可以设置小数点位置 文本 在文本单元格格式中，数字作为文本处理 特殊 用来在列表或数据中显示邮政编码、电话号码、中文大写数字、中文小写 数字 自定义 用于创建自定义的数字格式

4.2.4 公式和函数输入 1．公式输入 （1）公式中的运算符 （2）公式的修改和编辑 4.2.4 公式和函数输入 1．公式输入 在Excel工作表的单元格输入公式时，必须以一个等号（=）作为开头，等号（=）后面的“公式”中可以包含各种运算符号、常量、变量、函数以及单元格引用等. （1）公式中的运算符 算术运算符 文本运算符 比较运算符 引用运算符 （2）公式的修改和编辑

2．函数输入 函数是Excel 2010内部预先定义的特殊公式，它可以对一个或多个数据进行数据操作，并返回一个或多个数据。函数的作用是简化公式操作，把固定用途的公式表达式用“函数”的格式固定下来，实现方便的调用。 函数含函数名、参数和括号三部分。

函 数 名 称 函 数 功 能 SUM（number1,number2,…） 计算参数中数值的总和 AVERAGE(number1,number2,…) 计算参数中数值的平均值 MAX(number1,number2,…) 求参数中数值的最大值 MIN(number1,number2,…) 求参数中数值的最小值 COUNT(value1,value2,…) 统计指定区域中有数值数据的单元格个数 COUNTA(value1,value2,…) 统计指定区域中非空值（即包括有字符的单元格）的单 元格数目（空值是指单元格里没有任何数据） COUNTIF(range,criteria) 计算指定区域内满足特定条件的单元格的数目 RANK(number,ref,order) 求一个数值在一组数值中的名次 YEAR(date) 取日期的年份 TODAY() 求系统的日期 IF （logical_test,valuel_if_true,value_if_fal se） 本函数对比较条件式进行测试，如果条件成立，则取第 一个值（即value_if_true），否则取第二个值（即 value_if_false） VLOOKUP(lookup_value,table_array,co l_index_num, range_lookup) 搜索表区域首行满足条件的元素，确定待检索单元格在 区域中的行序号，再进一步返回选定单元格的值 FV(rate,nper,pmt,pv,type) 求按每期固定利率及期满的本息总和 PMT(rate,nper,pv,fv,type) 求固定利率下贷款等额的分期偿还额

3．公式和函数的复制——单元格公式引用 （1）相对引用 在输入公式的过程中，除非用户特别指明，Excel一般是使用相对地址来引用单元格的位置。所谓相对地址是指如果将含有单元地址的公式复制到另一个单元格时，这个公式中的各单元格地址将会根据公式移动到的单元格所发生的行、列的相差值，也同样做有这个相差值的改变，以保证这个公式对表格其他元素的运算正确。

（2）绝对地址的使用 如果公式运算中，需要某个指定单元格的数值是固定的数值，在这种情况下，就必须使用绝对地址引用。所谓绝对地址引用，是指对于已定义为绝对引用的公式，无论把公式复制到什么位置，总是引用起始单元格内的“固定”地址。 在Excel中，通过在起始单元格地址的列号和行号前添加美元符“$”，如$A$1来表示绝对引用。

（3）混合地址引用 单元格的混合引用是指公式中参数的行采用相对引用、列采用绝对引用；或列采用绝对引用、行采用相对引用，如$A3、A$3。当含有公式的单元格因插入、复制等原因引起行、列引用的变化时，公式中相对引用部分随公式位置的变化而变化，绝对引用部分不随公式位置的变化而变化。

4.2.5 创建迷你图 1．迷你图的概念 2．迷你图的创建 3．迷你图的编辑与格式化（迷你图工具栏） 4.2.5 创建迷你图 1．迷你图的概念 迷你图是工作表单元格中的一个微型图表（不是对象），可提供数据的直观表示。使用迷你图可以显示一系列数值的趋势（如季节性增加或减少、经济周期），或者可以突出显示最大值和最小值。在数据旁边放置迷你图可达到最佳效果。 2．迷你图的创建 3．迷你图的编辑与格式化（迷你图工具栏）

4.2.6 提高数据输入正确性和效率的方法 1．自动完成 2．选择列表 3．利用“自定义序列”自动充填数据 4．数据的自动填充 5．序列填充

6．数据有效性输入 （1）数据有效性的设置 （2）特定数据序列 有效性数据的输入提示信息和出错提示信息功能，是利用数据有效性功能，在用户选定的限定区域的单元格，或在单元格中输入了无效数据时，显示自定义的提示信息或出错提示信息。 （2）特定数据序列

7．特定区域内一组数字的快速输入 8．自定义输入 9．获取外部数据

4.2.7 工作表格式化 ① 单元格内的字体、字形、字号和颜色。 ② 单元格内的字符的对齐方式。 ③ 表格边框线和线型设置。 ④ 表格底纹和图案设置。 ⑤ 表格列宽和行高的设置。 ⑥ 数字格式的设置。 ⑦ 单元格可见性设置 ⑧ 单元格内容的保护。 ⑨ 单元格样式的设置。 ⑩ 条件格式设置。 ⑪ 套用表格格式。

4.3 工作表的数据统计和分析 4.3.1 数据统计与分析概述 4.3.2 学生成绩的统计分析——统计函数、IF函数、BANK函数、排序、筛选的应用 【例4-5】 【例4-6】 【例4-7】

4. 3. 3 职工工资的统计分析——日期函数和分类汇总的应用 4. 3 4.3.3 职工工资的统计分析——日期函数和分类汇总的应用 4.3.4 销售记录表的制作和分析——VLOOKUP函数和数据透视表的应用 1．VLOOKUP函数 VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)中： ① lookup_value是销售表中要查找的第一个品种所在的单元格。 ② table_array是被查找的价格表所在的区域。 ③ col_index_num是价格参数表区域中要输入的列数 ④ range_lookup中输入FALSE。

2．数据透视表 数据透视表是比“分类汇总”更为灵活的一种数据分析方法。它可以同时灵活变换多个需要统计的字段对一组数值进行统计分析，统计可以是求和、计数、平均值、最大值、最小值、乘积、数值计数、标准偏差、总体标准偏差、方差、总体方差。

3．切片器 切片器实际上就是将数据透视表中的每个字段单独创建为一个选取器，然后在不同的选取器中对字段进行筛选，完成与数据透视表字段中的筛选按钮相同的功能，但是切片器使用起来更加方便灵活。另外，创建的切片器可以应用到多个数据透视表中，或在当前数据透视表中使用其他数据透视表中创建的切片器。

4. 3. 5 银行存款利息计算——财务函数应用 4. 3. 6 购房贷款、银行利息计算——模拟运算表应用 4. 3 4.3.5 银行存款利息计算——财务函数应用 4.3.6 购房贷款、银行利息计算——模拟运算表应用 4.3.7 销售表合并统计计算——报表合并计算应用 4.3.8 学生成绩表统计分析——数据统计分析应用

4.4 Excel图表应用 4.4.1 图表概述 建立图表一般有以下步骤： ① 阅读、分析要建立图表的工作表数据，找出“比较”项。 4.4.1 图表概述 建立图表一般有以下步骤： ① 阅读、分析要建立图表的工作表数据，找出“比较”项。 ② 通过“插入”选项卡中的“图表”组中的命令按钮创建图表。 ③ 选择合适的图表类型。 ④ 最后对建立的图表通过“图表工具”进行编辑和格式化。

图 表 类 型 用 途 说 明 柱形图 用于比较一段时间中两个或多个项目的相对大小 条形图 在水平方向上比较不同类别的数据 折线图 按类别显示一段时间内数据的变化趋势 饼图 在单组中描述部分与整体的关系 XY散点图 描述两种相关数据的关系 面积图 强调一段时间内数值的相对重要性 圆环图 以一个或多个数据类别来对比部分与整体的关系，在中间有 一个更灵活的饼状图 雷达图 表明数据或数据频率相对于中心点的变化 曲面图 当第三个变量变化时，跟踪另外二个变量的变化，是一个三 维图 气泡图 突出显示值的聚合，类似于散点图 股价图 综合了柱形图的折线图，专门设计用来跟踪股票价格

4.4.2 图表建立操作 4.4.3 图表编辑和格式化 4.4.4 复杂图表