Excel22 – 第153页

Excel 2019删除分级显示

在删除Excel分级显示效果时，不会删除工作表中的任何数据。如果用户想删除分级显示效果，可以按以下具体步骤进行操作。

打开“删除分级显示.xlsx”工作簿，切换至“数据”选项卡，单击“分级显示”组中的“取消组合”下三角按钮，在展开的下拉列表中选择“清除分级显示”选项，如图8-30所示。此时的工作表分级显示效果已经被删除，如图8-31所示。

图8-30　选择“清除分级显示”选项

图8-31　删除分级显示的效果

Excel 2019显示、隐藏分级显示

用户如果想显示或隐藏分级显示的各个级别，可以通过单击相应的分级显示数字按钮或显示/隐藏按钮来实现。

数字1的级别是最高的，单击按钮“1”，则显示最高一级的内容，而不显示其他明细数据。数字2的级别其次，单击按钮“2”，会同时显示1级和2级的内容，其他的编号依次类推。如果要显示所有级别的明细数据，单击数字最大的按钮即可。

在同一级别的数据内容中会包含多个分组，单击“显示”按钮可以展开显示相应分组中的明细数据，单击“隐藏”按钮则可以隐藏相应的分组数据。

此外还可以通过功能区的按钮来完成分级显示的隐藏或显示操作。

以“自动创建分级显示.xlsx”工作簿为例，工作表中已经建立了分级显示，局部展开前的工作表如图8-25所示。

图8-25　局部展开前

STEP01：如图8-26所示，选择B5单元格，切换至“数据”选项卡，在“分级显示”组中单击“显示明细数据”按钮。此时的工作表如图8-27所示。

图8-26　单击“显示明细数据”按钮

图8-27　显示分级显示

STEP02：如图8-28所示，此时如果选中B8单元格，切换至“数据”选项卡，在“分级显示”组中单击“隐藏明细数据”按钮，隐藏分级显示的工作表如图8-29所示。

图8-28　单击“隐藏明细数据”按钮

图8-29　隐藏分级显示

Excel 2019自动创建分级显示图解

如图8-19所示的工作表是一张已经分别按行方向和列方向设置了分类求和公式的数据表，在使用分级显示功能时，系统会从汇总公式中自动地判别出分级的位置，从而自动生成分级显示的样式。

自动创建分级显示的具体操作步骤如下。

STEP01：选择数据区域中的任意单元格，这里选择B2单元格，切换至“数据”选项卡，在“分级显示”组中单击“组合”下三角按钮，在展开的下拉列表中选择“自动建立分级显示”选项，如图8-20所示。

图8-19　目标数据

图8-20　选择“自动建立分级显示”选项

STEP02：此时，在原工作表的行标签左侧和列标签上方分别显示出了分级显示符和标识线，如图8-21所示。

图8-21　自动生成分组显示

除了使用以上操作方法外，还可以按“Ctrl+8”组合键，打开如图8-22所示的对话框，单击“确定”按钮，也可以快速地自动创建分级显示。

图8-22　警告对话框

在图8-21中的第10行“市场3汇总”并没有和其他的行一样自动生成2级显示，这是因为该行的公式只引用了上面的单独一行，因此不能自动生成相应的分级显示。这时则需要用户通过手动的方式修改“市场3汇总”的分级显示，具体操作步骤如下所示。

STEP01：选中第9行，切换至“数据”选项卡，在“分级显示”组中单击“组合”下三角按钮，在展开的下拉列表中选择“组合”选项，建立第2级分级显示，如图8-23所示。

STEP02：此时便会对第9行建立2级分级显示，效果如图8-24所示。

图8-23　对第9行进行组合

图8-24　手动修改分级显示的结果

Excel 2019创建列的分级显示图解

前面一节讲的是手动创建行的分级显示。用户也一定注意到，在图8-2“设置”对话框中有一个“明细数据的右侧”复选框。用户可以取消对该复选框的选择来实现创建列的分组显示。以如图8-11所示的工作表数据为例，具体操作步骤如下。

图8-11　积分反馈工作表

手动创建列的分级显示的具体操作步骤如下。

STEP01：切换至“数据”选项卡，单击“分级显示”组中的对话框启动器按钮，打开如图8-12所示的“设置”对话框。在对话框中取消勾选“明细数据的右侧”复选框，然后单击“确定”按钮完成设置。

图8-12　设置分级显示方向

STEP02：选中D列，切换至“数据”选项卡，在“分级显示”组中单击“组合”下三角按钮，在展开的下拉列表中选择“组合”选项，建立第2级分级显示，如图8-13所示。

STEP03：选中第F列至第M列，切换至“数据”选项卡，在“分级显示”组中单击“组合”下三角按钮，在展开的下拉列表中选择“组合”选项，建立第2级分级显示，如图8-14所示。

图8-13　对D列进行组合

图8-14　对F列至M列进行组合

STEP04：选中第G列至第M列，切换至“数据”选项卡，在“分级显示”组中单击“组合”下三角按钮，在展开的下拉列表中选择“组合”选项，建立第3级分级显示，如图8-15所示。

STEP05：选中第O列至第P列，切换至“数据”选项卡，在“分级显示”组中单击“组合”下三角按钮，在展开的下拉列表中选择“组合”选项，建立第2级分级显示，如图8-16所示。

图8-15　对G列至M列进行组合

图8-16　对O列至P列进行组合

STEP06：选中第P列，切换至“数据”选项卡，在“分级显示”组中单击“组合”下三角按钮，在展开的下拉列表中选择“组合”选项，建立第3级分级显示，如图8-17所示。

STEP07：建立分级显示的最终效果如图8-18所示。在工作表的上方出现了分级显示符及标识线，通过单击这些分级显示符按钮可方便地进行分组显示。

图8-17　对P列进行组合

图8-18　手动创建列分级显示

Excel 2019创建行的分级显示图解

对于那些层次关系的规律性不是很明显的数据内容，用户通常使用手工创建的方法进行分级显示。如图8-1所示的工作表是一张积分反馈表，数据内容都是文本类型的数据。如果按“积分”字段中数据的位数建立分级显示，就需要通过手动的方式创建分级显示。

图8-1　积分反馈表

手动创建行分级显示的具体操作步骤如下。

STEP01：切换至“数据”选项卡，单击“分级显示”组中的对话框启动器按钮，打开如图8-2所示的“设置”对话框。在对话框中取消勾选“明细数据的下方”复选框，然后单击“确定”按钮完成设置。

图8-2　“设置”对话框

STEP02：选中第4行，切换至“数据”选项卡，在“分级显示”组中单击“组合”下三角按钮，在展开的下拉列表中选择“组合”选项，建立第1级分级显示，如图8-3所示。

STEP03：选中第6行至第13行，切换至“数据”选项卡，在“分级显示”组中单击“组合”下三角按钮，在展开的下拉列表中选择“组合”选项，建立第2级分级显示，如图8-4所示。

图8-3　对第4行进行组合操作

图8-4　对第6～13行进行组合操作

STEP04：选中第7行至第13行，切换至“数据”选项卡，在“分级显示”组中单击“组合”下三角按钮，在展开的下拉列表中选择“组合”选项，建立第3级分级显示，如图8-5所示。

STEP05：选中第15行至第16行，切换至“数据”选项卡，在“分级显示”组中单击“组合”下三角按钮，在展开的下拉列表中选择“组合”选项，建立第2级分级显示，如图8-6所示。

图8-5　对第7～13行进行组合操作

图8-6　对第15～16行进行组合操作

STEP06：选中第16行，切换至“数据”选项卡，在“分级显示”组中单击“组合”下三角按钮，在展开的下拉列表中选择“组合”选项，建立第3级分级显示，如图8-7所示。

STEP07：建立分级显示的最终效果如图8-8所示。在工作表的左侧出现了分级显示符及标识线，通过单击这些分级显示符号按钮即可方便地进行分组显示。

图8-7　对第16行进行组合操作

图8-8　创建分级显示效果

用户单击分级显示符“1”按钮，就能看到第1级的显示效果，如图8-9所示；单击分级显示符“2”按钮，便可以看到第2级的显示效果，如图8-10所示。第3级相同。

图8-9　选择第1级显示

图8-10　选择第2级显示

Excel 2019 z-检验分析图解

z-检验工具对具有已知方差的平均值进行双样本z-检验。通过使用z-检验工具，可以检验两个总体平均值之间不存在差异的空值假设，而不是单方或双方的其他假设。而且，也可以使用z-检验确定两个汽车模型的性能差异。下面通过具体实例来详细讲解利用z-检验分析数据的操作技巧。

已知某公司两个部门的收入数据，使用“双样本平均差检验”分析工具对两个部门的收入数据进行分析，分析两个部门收入情况有无明显差异，具体操作步骤如下。

STEP01：打开“z-检验分析.xlsx”工作簿，切换至“数据”选项卡，然后在“分析”组中单击“数据分析”按钮，打开如图7-105所示的“数据分析”对话框。在“分析工具”列表框中选择“z-检验：双样本平均差检验”选项，然后单击“确定”按钮。

图7-105　选择双样本平均差检验分析工具

STEP02：随后会打开“z-检验:双样本平均差检验”对话框，在“输入”列表区域设置变量1的区域为“$B$2:$B$13”，设置变量2的区域为“$C$2:$C$13”，设置变量1的方差为“5”，变量2的方差为“8”，并设置α的值为“0.05”，然后在“输出选项”列表中单击选中“新工作表组”单选按钮，最后单击“确定”按钮即可，如图7-106所示。

STEP03：此时，工作表中会显示“z-检验:双样本平均差检验”的分析结果，如图7-107所示。

图7-106　设置属性

图7-107　z-检验分析结果

Excel 2019 t-检验分析图解

t-检验工具用于判断每个样本，检验样本总体平均值是否相等。t-检验工具共分为3个工具，分别是平均值的成对二样本分析、双样本等方差假设和双样本异方差假设。

平均值的成对二样本分析

平均值的成对二样本分析可以确定取自处理前后的观察值是否具有相同总体平均值的分布。当样本中出现自然配对的观察值时，可以使用此工具成对检验。在“数据分析”对话框中，选择“t-检验:平均值的成对二样本分析”选项，然后单击“确定”按钮，打开“t-检验:平均值的成对二样本分析”对话框，如图7-99所示。

“平均值的成对二样本分析”对话框中的各项属性设置如下。

变量1的区域：需要统计的第1个样本。
变量2的区域：需要统计的第2个样本。
假设平均差：两个平均值之间的假设差异。
标志：指定数据的范围是否包含标签。
α(A)：表示检验的置信水平。
输出区域：存放统计结果的单元格区域，可以单击“输出区域”右侧的压缩按钮选择数据区域。
新工作表组：新建一个工作表，并将数据分析结果存放在新建工作表中。
新工作簿：新建一个工作簿，并将数据分析结果存放在新建工作簿中。

双样本等方差假设

t-检验先假设两个数据集具有相同方差的分布，也称作同方差t-检验。可以使用t-检验来确定两个样本是否具有相同总体平均值的分布。在“数据分析”对话框中，选择“t-检验：双样本等方差假设”选项，然后单击“确定”按钮，打开“t-检验:双样本等方差假设”对话框，如图7-100所示。

图7-99　“t-检验：平均值的成对二样本分析”对话框

图7-100　“t-检验：双样本等方差假设”对话框

“t-检验：双样本等方差假设”对话框中的各项设置介绍如下。

变量1的区域：需要统计的第1个样本。
变量2的区域：需要统计的第2个样本。
假设平均差：两个平均值之间的假设差异。
标志：指定数据的范围是否包含标签。
α(A)：表示检验的置信水平。
输出区域：存放统计结果的单元格区域，可以单击“输出区域”右侧的压缩按钮选择数据区域。
新工作表组：新建一个工作表，并将数据分析结果存放在新建工作表中。
新工作簿：创建一个工作簿，并将数据分析结果存放在新建工作簿中。

双样本异方差假设

双样本异方差假设先假设两个数据集具有不同方差的分布，也称作异方差t-检验。与上面的“等方差”一样，可以使用t-检验来确定两个样本是否具有相同总体平均值的分布。当两个样本存在截然不同的对象时，可使用此检验。在“数据分析”对话框中，选择“t-检验：双样本异方差假设”选项，然后单击“确定”按钮，打开“t-检验：双样本异方差假设”对话框，如图7-101所示。

图7-101　“t-检验：双样本异方差假设”对话框

“双样本异方差假设”对话框中的各项设置介绍如下。

变量1的区域：需要统计的第1个样本。
变量2的区域：需要统计的第2个样本。
假设平均差：两个平均值之间的假设差异。
标志：指定数据的范围是否包含标签。
α(A)：表示检验的置信水平。
输出区域：存放统计结果的单元格区域，可以单击“输出区域”右侧的压缩按钮选择数据区域。
新工作表组：新建一个工作表，并将数据分析结果存放在新建工作表中。
新工作簿：新建一个工作簿，并将数据分析结果存放在新建工作簿中。

以上简单介绍了3种工具的使用方法，下面通过实例来具体讲解应用技巧。

打开“t-检验分析.xlsx”工作簿，已知两个零件的测试数值，使用“双样本等方差假设分析工具”对测试数据进行分析，具体操作步骤如下所示。

STEP01：切换至“数据”选项卡，然后在“分析”组中单击“数据分析”按钮，打开如图7-102所示的“数据分析”对话框。在“分析工具”列表框中选择“t-检验:双样本等方差假设”选项，然后单击“确定”按钮。

图7-102　选择双样本等方差假设分析工具

STEP02：随后会打开“t-检验:双样本等方差假设”对话框，在“输入”列表区域设置变量1的区域为“$B$2:$B$15”，设置变量2的区域为“$C$2:$C$15”，勾选“标志”复选框，并设置α的值为“0.05”，然后在“输出选项”列表中单击选中“新工作表组”单选按钮，最后单击“确定”按钮，如图7-103所示。

图7-103　设置属性

STEP03：此时，工作表中会显示“t-检验:双样本等方差假设”的分析结果，如图7-104所示。

图7-104　“t-检验：双样本等方差假设”分析结果

Excel 2019 F-检验分析图解

“F-检验双样本方差”分析工具，通过双样本F-检验对两个样本总体的方差进行比较。此分析工具可以进行双样本F-检验，又称为方差齐性检验，用来比较两个样本总体的方差是否相等。下面通过具体的实例来详细讲解利用“F-检验双样本方差”工具来分析数据的有关技巧。

图7-96　选择F-检验分析工具

STEP01：打开“F-检验分析.xlsx”工作簿，切换至“数据”选项卡，然后在“分析”组中单击“数据分析”按钮，打开如图7-96所示的“数据分析”对话框。在“分析工具”列表框中选择“F-检验双样本方差”选项，然后单击“确定”按钮。

STEP02：随后会打开“F-检验双样本方差”对话框，在“输入”列表区域设置变量1的区域为“$B$2:$K$2”，设置变量2的区域为“$B$3:$K$3”，并设置α的值为“0.05”，然后在“输出选项”列表中单击选中“输出区域”单选按钮，设置输出区域为“$A$5”单元格，最后单击“确定”按钮，如图7-97所示。

图7-97　设置“F-检验　双样本方差”属性

STEP03：此时，工作表中会显示“F-检验双样本方差”的分析结果，如图7-98所示。

图7-98　计算分析结果

Excel 2019方差分析图解

方差分析通常分为两种情况，一种是单因素方差分析，另一种是双因素方差分析。而双因素方差分析又可分为“无重复双因素方差分析”和“可重复双因素方差分析”两种。下面以“方差分析.xlsx”工作簿为例，逐一对这几种分析工具进行介绍。

单因素方差分析

STEP01：打开“方差分析.xlsx”工作簿，切换至“单因素方差分析”工作表。在主页将功能区切换至“数据”选项卡，然后在“分析”组中单击“数据分析”按钮，打开如图7-85所示的“数据分析”对话框。在“分析工具”列表框中选择“方差分析：单因素方差分析”选项，然后单击“确定”按钮。

图7-85　选择单因素方差分析工具

STEP02：随后会打开“方差分析：单因素方差分析”对话框，在“输入”列表区域设置输入区域为“$B$2:$D$7”，在“分组方式”列表中单击选中“列”单选按钮，并勾选“标志位于第一行”复选框，设置α的值为“0.05”。然后在“输出选项”列表中单击选中“输出区域”单选按钮，设置输出区域为“$E$2”单元格，最后单击“确定”按钮，如图7-86所示。

图7-86　“方差分析：单因素方差分析”对话框

对话框中各个设置项的作用简要介绍如下。

1）输入区域：在此输入待分析数据区域的单元格引用。该引用必须由两个或两个以上按列或行组织的相邻数据区域组成。例如这里选择单元格区域为“$B$2:$D$7”。

2）分组方式：指出输入区域中的数据是按行还是按列排列。例如这里选择分组方式为“列”。

3）标志位于第一行/列：如果输入区域的第1行中包含标志项，则可以选中“标志位于第一行”复选框；如果输入区域的第1列中包含标志项，则可以选中“标志位于第一列”复选框；如果输入区域没有标志项，则该复选框不会被选中，Excel将在输出表中生成数据标志。

4）α：根据需要指定显著性水平，例如这里输入“0.05”。

5）输出区域：如果用户选择“输出区域”单选按钮，则在右侧的文本框中输入或选择一个单元格地址。如果用户希望输出至新的工作表组中，则选择“新工作表组”单选按钮；如果用户希望输入至新的工作簿中，则选中“新工作簿”单选按钮。例如这里选择“输出区域”，在右侧的文本框中输入“$E$2”。

STEP03：此时，工作表中会显示“方差分析：单因素方差分析”的分析结果，如图7-87所示。

无重复双因素分析

切换至“双因素方差分析–无重复性”工作表，如图7-88所示的数据表中说明的是某一种产品在5种不同的地点区域的销售情况。不在同区域、不同产品的情况下对销售的影响，即考察的区域不同、销售的产品不同对销售量是否有其显著影响。这两种因素组合只会产生一个销售数据，所以这是一个无重复双因素方差分析。因此，应该使用“无重复双因素分析”工具进行分析，其具体操作步骤如下。

图7-87　单因素方差分析结果

图7-88　“无重复性”数据表

STEP01：切换至“数据”选项卡，然后在“分析”组中单击“数据分析”按钮，打开如图7-89所示的“数据分析”对话框。在“分析工具”列表框中选择“方差分析：无重复双因素分析”选项，然后单击“确定”按钮。

STEP02：随后会打开“方差分析：无重复双因素分析”对话框，在“输入”列表区域设置输入区域为“$B$2:$F$5”，设置α的值为“0.05”，然后在“输出选项”列表中单击选中“输出区域”单选按钮，设置输出区域为“$A$6”单元格，最后单击“确定”按钮，如图7-90所示。

图7-89　选择无重复双因素方差分析工具

图7-90　“无重复双因素分析”对话框

STEP03：此时，工作表中会显示“方差分析：无重复双因素分析”的分析结果，如图7-91所示。

可重复双因素分析

切换至“双因素方差分析–可重复性”工作表，如图7-92所示的工作表是一份农作物增收数据，其中化肥和复合肥是两个因素。目的是要考察化肥和复合肥的不同组合方案对农作物的收成是否有显著差异。从表中可以看到，每两个因素的组合都有4个数据，所以这是一个可重复性双因素方差分析，因此应该使用“可重复双因素分析”工具进行分析。

STEP01：切换至“数据”选项卡，然后在“分析”组中单击“数据分析”按钮，打开如图7-93所示的“数据分析”对话框。在“分析工具”列表框中选择“方差分析：可重复双因素分析”选项，然后单击“确定”按钮。

STEP02：随后会打开“方差分析：可重复双因素分析”对话框，在“输入”列表区域设置输入区域为“$A$1:$C$9”，在“每一样本的行数”文本框中输入“4”，并设置α的值为“0.05”，然后在“输出选项”列表中单击选中“输出区域”单选按钮，设置输出区域为“$A$11”单元格，最后单击“确定”按钮，如图7-94所示。

图7-91　无重复双因素方差分析结果

图7-92　农作物增收数据

图7-93　选择可重复双因素分析选项

图7-94　“可重复双因素分析”对话框

对话框中“每一样本的行数”项指的是每一样本的行数，因为在本实例中每一种方案都有4行数据，所以这里输入4。

STEP03：此时，工作表中会显示“方差分析：可重复双因素分析”的分析结果，如图7-95所示。

图7-95　可重复双因素方差分析结果

Excel 2019回归分析图解

回归分析工具通过对一组观察值使用“最小二乘法”直线拟合来执行线性回归分析。本工具可以用来分析单个因变量是如何受一个或几个自变量的值影响的。下面通过具体的实例来详细讲解有关回归分析的操作技巧。

已知某公司2018年销售收入明细表，使用回归分析工具对收入、成本与费用进行分析。输入的原始数据如图7-81所示。

STEP01：切换至“数据”选项卡，然后在“分析”组中单击“数据分析”按钮，打开如图7-82所示的“数据分析”对话框。在“分析工具”列表框中选择“回归”选项，然后单击“确定”按钮。

图7-81　原始数据

图7-82　选择回归分析工具

STEP02：随后会打开“回归”对话框，在“输入”列表区域中设置Y值输入区域为“$A$2:$A$13”，设置X值输入区域为“$B$2:$B$13”，勾选“标志”复选框与“置信度”复选框，并设置置信度为“95%”。然后在“输出选项”列表区域中单击选中“新工作表组”单选按钮，在“残差”列表区域中依次勾选“残差”复选框、“残差图”复选框、“标准残差”复选框及“线性拟合图”复选框，最后在“正态分布”列表区域中勾选“正态概率图”复选框。设置完成后单击“确定”按钮即可返回工作表，如图7-83所示。

图7-83　设置属性

提示：在回归分析对话框中，各个属性值的含义如下。

1）Y值输入区域：独立变量的数据区域。

2）X值输入区域：一个或多个独立变量的数据区域。

3）标志：指定数据的范围是否包含标签。

4）常数为零：是否选择一个为零的常量。

5）置信度：表示置信水平。

6）输出区域：存放统计结果的单元格区域，可以单击“输出区域”右侧的压缩按钮选择数据区域。

7）新工作表组：新建一个工作表，并将数据分析结果存放在新建工作表中。

8）新工作簿：新建一个工作簿，并将数据分析结果存放在新建工作簿中。

9）残差：指定在统计结果中是否显示预测值与观察值的差值。

10）残差图：指定在统计结果中是否显示残差图的显示方式。

11）标准残差：指定在统计结果中是否显示标准残差的显示方式。

12）线性拟合图：指定在统计结果中是否显示线性拟合图的显示方式。

13）正态概率图：指定在统计结果中是否显示正态概率图的显示方式。

STEP03：此时，工作簿中会自动新建一个工作表，工作表中显示的回归分析效果如图7-84所示。

图7-84　回归分析的结果