商务智能与数据可视化分析基础 课件 第3章

第3章数据分析基础之Excel商务智能与数据可视化分析基础1本章内容数据透视表数据透视表的创建数据透视表的修改数据透视表的可视化Excel中的统计分析常用统计分析函数数据分析工具描述性统计分析相关分析预测分析Excel中的图表类型与展示201数据透视表数据透视表的创建数据透视表的修改数据透视表的可视化第3章数据分析基础之Excel第一步:插入数据透视表。导入素材文件，切换到“Sheet1”工作表中，可看到该工作表中的源数据。点击“插入”菜单栏，再单击“数据透视表”按钮，出现“来自表格或区域的数据透视图”1.1数据透视表的创建Excel中的数据透视表是一种可以快速汇总、分析和处理大量数据的交互式表，它可以从不同角度对相同的数据进行处理和分析。数据透视表就像一个万花筒，通过旋转这个特别的万花筒，可以从中获得数据的不同视图展现，但原始数据并未发生变化。3第二步：在“表/区域”中选择数据源所在的单元格区域或所在列。长按鼠标键拖动选择A列到D列，表区域将显示“Sheet1!$A:$D”1.2数据透视表的创建4第三步：点击“现有工作表”，然后点击“位置（L）”输入框，让光标停留在该框，并清空该框文本，接着鼠标任意单击非数据源区域中的单元格，例如：鼠标单击“F2”单元格，输入框将自动填入“Sheet1!$F$2”，然后点击【确定】按钮1.3数据透视表的创建5第四步：添加字段到数据透视表中。在“数据透视表字段”任务窗勾选日期、质量等级和年三个，并且将日期拖放至“值”区域，年拖放至“列”区域，质量等级拖放至“行”区域1.4数据透视表的创建6第五步：查看数据透视表报表。在之前选择的位置“F2”，显示了添加字段后的数据透视表报表。1.5数据透视表的创建7（1）透视表中行的拖动选中要移动的字段“中度污染”，将鼠标指针移至该单元格的下边框线上，此时可看到鼠标指针变为十字形状；接着勾选该字段的复选框。按住鼠标左键，将该字段拖放至“严重污染”字段单元格的上边框线上。1.6数据透视表的修改8（2）透视表中列的排序除了可以对行进行拖动，还可以对某列的数值进行排序，例如，需要将2017年的分布数据降序排列，则使用鼠标右击“2017年”列中的任意单元格，在弹出的快捷菜单中选择“排序”→“降序”选项即可。1.7数据透视表的修改9Excel中创建数据透视图的方法很方便，具体的操作步骤如下，首先选中之前创建的数据透视表，然后单击“数据透视表分析”→“数据透视图”按钮。1.8数据透视表的可视化10簇状柱形图1.9数据透视表的可视化111202Excel中的统计分析常用统计分析函数数据分析工具描述性统计分析相关分析预测分析第3章数据分析基础之Excel在统计学中对原始数据进行一定的运算，得出某些代表性的数字，以反映数据某些方面的特征，这种数字称为统计量。常见的统计量见下：

总数：在一组数据中，所有数值的合计数。

最大值：在一组数据中，数值变量的最大值。

最小值：在一组数据中，数值变量的最小值。

极差：在一组数据中，数值变量的最大值与最小值的差值。

算术平均值：反映出的是一组数据的数学期望值。

算术平均值的计算公式：

中位数：又称中值，是按顺序排列的一组数据中居于中间位置的数，代表一个样本、种群或概率分布中的一个数值，其可将数值集合划分为相等的上下两部分。对于有限的数集，可以通过把所有观察值高低排序后找出正中间的一个作为中位数。如果观察值是偶数个，通常取最中间的两个数值的平均数作为中位数。2.1常用统计分析函数13众数：在样本数据中，选择出现次数最多的数值。从统计分布上看，它是具有明显集中趋势的数值，代表数据的一般水平。一组数据中可以有多个众数，也可以没有众数。如果一组数据中有重复出现的数，则有众数；如果没有重复出现的数，则没有众数。标准差：方差的算术平方根，标准差能反映一个数据集的离散程度。标准差较大，代表大部分数值和其算术平均值之间的差异较大；标准差较小，代表这些数值较接近算术平均值。方差：用来度量随机变量和其数学期望（均值）之间的偏离程度。偏度：又称偏态、偏态系数，是统计数据分布偏斜方向和程度的度量，是统计数据分布非对称程度的数字特征。峰度：又称峰态系数，用于表示概率密度分布曲线在平均值处的峰值高低的特征数。直观看来，峰度反映了峰部的尖度。2.2常用统计分析函数14Excel中常用统计分析函数2.3常用统计分析函数序号函数名功能格式1COUNT统计参数中包含数字的个数COUNT(value1,[value2],...)例如：COUNT(C2:C8)2COUNTIF统计所选取区域中满足指定条件的单元格个数COUNTIF(range,criteria)例如：COUNTIF(B2:B8,B23MAX返回一组数字中的最大值MAX(number1,[number2],...)例如：MAX(C2:C8)4MIN返回一组数字中的最小值MIN(number1,[number2],...)例如：MIN(C2:C8)5AVERAGE返回一组数字中的算术平均值AVERAGE(number1,[number2],...)例如：AVERAGE(A2:A6)6MEDIAN返回一组数字中的中间数值MEDIAN(number1,[number2],...)例如：MEDIAN(C2:C8)7MODE返回一组数字出现频率最多的数值MODE(number1,[number2],...)例如：MODE(C2:C9)，8RANK返回一个数字或一组数字中的排位RANK(number,ref,[order])例如：RANK(C2,C2:C9)，9VAR计算基于给定样本的方差VAR(number1,[number2],...)例如：VAR(C2:C9)10STDEV.S计算基于给定样本的标准偏差STDEV.S(number1,[number2],...)例如：STDEV.S(C2:C9)15Excel中有专门用于数据分析的工具库，可以完成方差分析、相关系数分析、协方差分析、描述统计分析、移动平均分析等。调出数据分析窗口的步骤如下：

步骤1：在打开Excel文件后，单击“文件”菜单按钮，在弹出的菜单中选择“选项”命令，弹出“Excel选项”对话框。2.4数据分析工具16

步骤2：在弹出的“Excel选项”对话框中，首先选择“加载项”选项，在“管理：”下拉列表中选择“Excel加载项”选项；然后单击“转到”按钮，弹出“加载项”对话框。

步骤3：在弹出的“加载项”对话框中，勾选“分析工具库”复选框，并单击“确定”按钮。2.5数据分析工具17步骤4：打开Excel工作文档，单击“数据”→“数据分析”按钮，弹出“数据分析”对话框。2.6数据分析工具18描述性统计分析是统计学中的基础内容，主要用于研究数据的基本统计特征，从而掌握数据的整体分布形态。它是对数据进行正确统计、推断的先决条件，分析结果对进一步的数据建模起到了关键性的指导和参考作用。频数分析：频数又称次数，是指同一数值在一组数据中出现的次数。使用该分析方法可以将零散、分散的数据进行有次序的整理，从而形成一系列能反映数据总体中各组之间的单位分布状况的数列。在变量分配数列中，频数表示对应的组标志值的作用程度，频数值越大，表示该组标志值对总体水平的作用越大，反之亦然。

单项式频率分析又称单项式分组的频数分析，它主要运用Excel中的COUNTIF函数对数据进行频数分析。描述统计：描述统计分析主要包括描述总体分析和分布形态分析。其中，分布形态分析主要通过偏度和峰度分析方法来分析数据的分布情况；而描述总体分析主要通过Excel中的描述分析工具，对数据进行整体的描述性分析，包括均值、中位数、众数、方差等。2.7描述性统计分析19Excel导入素材文件，单击“数据”→“数据分析”按钮（如果没有“数据分析”按钮，则可以通过“文件”→“选项”命令来设置），在弹出的“数据分析”对话框中选择“描述统计”选项，单击“确定”按钮，弹出“描述统计”对话框。2.8描述性统计分析20在“描述统计”对话框的“输入区域”输入框中输入“$C$2:$C$20”，并勾选“汇总统计”“平均数置信度”“第K大值”“第K小值”复选框，然后单击“确定”按钮，显示描述统计分析结果。2.9描述性统计分析21在统计学中，相关关系是一种确定性关系，可以使用相关分析方法来研究两个或多个随机变量之间的相关性，以确定变量之间的方向和密切程度。（1）按相关程度划分，可以将相关关系分为完全相关、不完全相关和零相关三种类型。•

完全相关：完全相关关系是指变量之间的关系是一一对应的，即一个变量的数量发生变化完全是由另一个变量的数量变化决定的。该类型的相关关系为函数关系，是相关关系中的一种特例。

•不完全相关：不完全相关是指变量之间的关系并非一一对应的，即两个现象之间的关系介于完全相关和不相关之间。

•零相关：零相关关系又称为不相关关系，是指两个变量之间彼此互不影响，其数量变化各自独立的关系.2.10相关分析22在Excel中打开素材，单击“F2”单元格，先输入“=CORREL(B:B,C:C)”，再按回车键，得到y与x1的相关系数为0.954，接近1，表示这两个变量高度相关。2.11相关分析232.12相关分析获得y、x1、x2变量之间的相关系数24打开素材，单击“数据”→“数据分析”按钮。在弹出的“数据分析”对话框中选择“相关系数”选项，弹出“相关系数”对话框，在“输入区域”输入框中输入“$B$2:$D$12”，并单击“确定”按钮。（2）按相关形式划分按相关形式划分相关关系，可以分为线性相关和非线性相关两种类型。

•线性相关：线性相关是指一个变量在增加或减少时，另一个变量随之会发生大致均等的增加或减少变化，其图形中所表现的观测点会分布在某一条直线附近。•非线性相关：非线性相关是指一个变量在增加或减少时，另一个变量也随之发生不均等的增加或减少变化，其图形中所表现的观测点会分布在某一曲线附近。（3）按相关关系涉及的因素划分按相关关系涉及的因素划分，可以分为单相关、复相关和偏相关三种类型。

•单相关：单相关又称为一元相关，是指两个变量之间的相关关系，即仅限于一个变量与另一个变量之间的依存关系。•复相关：复相关又称为多元相关，是指3个或3个以上变量间的相关关系。•偏相关：偏相关是指某一变量和多种变量相关时，当假定其他变量不变，其中两个变量的相关关系。2.13相关分析25（1）回归分析回归分析通过最小二乘法拟合进行分析，主要用于确定一个或多个变量的变化对另一个变量的影响程度。运用统计学的方法获得其数学模型，以确定自变量与因变量之间的关系，并通过自变量的给定值来推算或估计因变量的值。在Excel中可以使用数据分析工具来进行回归分析，具体操作步骤如下。打开素材文件，单击“数据”→“数据分析”按钮。在弹出的“数据分析”对话框中选择“回归”选项，弹出“相关系数”对话框，设置“Y值输入区域”为“$B$2:$B$12”，“X值输入区域”为“$C$2:$D$12”。2.14预测分析26点击【确定】按钮，可以获得y与x1、x2回归分析结果。2.15预测分析27趋势线分析法是建立在散点图图表的基础上的一种分析方法，主要通过为散点图添加趋势线的方法，来达到一元线性回归分析的目的。Excel中的散点图的趋势线包括对数、指数、多项式、线性等类型，不同类型的趋势线所使用的分析方法也各不相同，用户需要根据分析目的来选择相应的趋势线。步骤1：绘制散点图。打开素材文件，拖曳鼠标选中B、C、D列单元格，选择“插入”→“散点图”→“更多散点图”选项，在弹出的“插入图表”对话框中选择“XY散点图”选项，并单击“确定”按钮。2.16预测分析28步骤2：添加趋势线。选择“图表设计”→“添加图表元素”→“趋势线”→“线性预测”选项，在弹出的“添加趋势线”对话框中选择“x1”选项，单击“确定”按钮。2.17预测分析29步骤3：设置趋势线格式。右击趋势线，在弹出的快捷菜单中选择“设置趋势线格式”命令，在工作表右侧弹出的“设置趋势线格式”窗格中，选中“线性”单选按钮，勾选“显示公式”和“显示R平方值”两个复选框。2.18预测分析30步骤4：显示预测分析结果。重复步骤2，在弹出“添加趋势线”对话框中选择“x2”选项，继续执行步骤3，得到预测分析结果。2.19预测分析31（2）移动平均法移动平均法是一种简单、平滑的预测技术，它的基本思想是根据时间序列资料、逐项推移，依次计算包含一定项数的序时平均值，以反映长期趋势。打开素材文件，单击“数据”→“数据分析”按钮，在弹出的“数据分析”对话框中选择“移动平均”选项，弹出“移动平均”对话框，设置“输入区域”为“$B$2:$B$12”，“输出区域”为“$F$2”，勾选“图表输出”和“标准误差”两个复选框，并单击“确定”按钮，获得移动平均预测结果。2.20预测分析323303Excel中的图表类型与展示主要图表类型主要图表展示第3章数据分析基础之Excel3.1主要图表类型34Excel中的主要图表类型及其说明一个图表由多个图表元素组成，不同的图表可以具有不同的图表元素。通常的图表元素主要有：图表区：图表区与整个图表区域等同，其他图表元素都位于图表区中。选择图表区就选中了整个图表，图表选中后四周会显示边框和8个控制点，可以使用鼠标拖动控制点可以调整图表大小。图表标题：图表顶部的文字，用于描述图表的含义。图例：图表标题下方带有色块的文字，用于标识不同的数据系列。绘图区：图中的浅灰色部分，作为数据系列的背景，数据系列、数据标签、网格线等图表元素位于绘图区。数据系列：图中位于绘图区的同一种颜色的所有矩形构成一个数据系列，每个数据系列对应数据源中的一行或一列数据。数据系列中的每个矩形代表一个数据点，对应数据源中的某个单元格的值。数据标签：数据系列顶部的数字，用于标识数据点的值。坐标轴及其标题：坐标轴包括主要横坐标轴、主要纵坐标轴、次要横坐标轴、次要纵坐标轴4种。3.2主要图表类型35柱形图打开素材文件，切换到“柱形图”工作表。选中A、B、C三列单元格，单击“插入”→“推荐的图表”按钮，在弹出的“插入图表”对话框中选择“所有图表”→“柱形图”→“簇状柱形图”选项，单击“确定”按钮，插入并得到簇状柱形图。3.3主要图表展示36条形图、折线图绘制条形图、折线图的操作与绘制柱形图的操作类似，只需在“所有图表”选项卡中选择“条形图”或“折线图”选项，并单击“确定”按钮即可。3.4主要图表展示37XY散点图打开素材文件，切换到“XY散点图”工作表。选中A、B、C、D四列单元格，单击“插入”→“推荐的图表”按钮，在弹出的“插入图表”对话框中选择“XY散点图”选项，单击“确定”按钮，得到XY散点图。3.5主要图表展示38气泡图步骤1：打开素材文件，切换到“气泡图”工作表。选中B2:D12区域，单击“插入”→“推荐的图表”按钮，在弹出的“插入图表”对话框中选择“气泡图”选项，单击“确定”按钮，得到初始气泡图。3.6主要图表展示39步骤2：编辑气泡图。选中气泡图，单击“图表设计”→“选择数据”按钮，在弹出的“选择数据源”对话框中单击“编辑”按钮，设置“X轴系列值”为“=气泡图!$B$2:$B$12”，“Y轴系列值”为“=气泡图!$D$2:$D$12”，“系列气泡大小”为“=气泡图!$C$2:$C$12”，最后单击“确定”按钮。3.7主要图表展示40步骤3：给气泡图添加数据标签。选中气泡图并右击，在弹出的快捷菜单中选择“添加数据标签”命令，得到有标签的气泡图。3.8主要图表展示41步骤4：给气泡图设置数据标签格式。单击气泡图，单击鼠标右键，选择“设置数据标签格式”选项，在弹出的窗格中，将标签位置选择为“居中”，得到标签位置居中的气泡图。3.9主要图表展示42饼图、圆环图、面积图

打开素材文件，切换到“饼图、圆环图、面积图”工作表。选