相册 – 第143页

Excel 2019单变量数据表运算图解

在单变量数据运算中，可以对一个单变量输入不同的值来查看它对一个或多个公式的变化。例如，根据产品的不同销售量来计算产品所取得的纯利润。

STEP01：打开“不同销售量纯利润计算表.xlsx”工作簿，在工作表中输入总成本、单位售价、预计销售量的实际数据，然后在B5单元格中输入公式“=B3*B4”，按“Enter”键返回即可计算出销售总收入。根据“纯利润=销售总收入-总成本”的公式，在B6单元格中输入公式“=B5-B2”，按“Enter”键返回即可计算出预计销售量为20000时的纯利润，如图7-8所示。

STEP02：在A8单元格和B8单元格中分别输入文本“实际销售量”和“纯利润”，在A10:A13单元格区域中分别输入如图7-9所示的销售量，并在单元格B9中输入公式“=B5-B2”，按“Enter”键返回计算结果即可。

STEP03：选择A9:B13单元格区域，切换至“数据”选项卡，在“预测”组中单击“模拟分析”下三角按钮，在展开的下拉列表中选择“模拟运算表”选项，打开“模拟运算表”对话框，如图7-10所示。

STEP04：在“输入引用列的单元格”文本框中输入单元格的引用地址为“$B$4”单元格，表示不同的销售量，然后单击“确定”按钮即可返回计算结果，如图7-11所示。此时的工作表如图7-12所示。

图7-8　计算总收入和纯利润

图7-9　输入销售量和公式

图7-10　选择“模拟运算表”选项

图7-11　设置引用列的单元格

图7-12　模拟运算表求解结果

Excel 2019求解二元一次方程图解

在Excel 2019中，单变量求解是提供的目标值，将引用单元格的值不断调整，直到达到所需要的公式的目标值时，变量值才能确定。利用单变量求解功能，可以求解二元一次方程，例如方程式为A=10-B，B=5+A。

STEP01：打开“求解方程式.xlsx”工作簿，在工作表中输入方程式，如图7-1所示。

STEP02：在A6单元格中输入公式“=10-B6”，按“Enter”键返回计算结果，如图7-2所示。

图7-1　输入方程式

图7-2　输入公式并得到A的值

STEP03：在B7单元格中输入公式“=B6-A6”，按“Enter”键返回计算结果，如图7-3所示。

STEP04：切换至“数据”选项卡，在“预测”组中单击“模拟分析”下三角按钮，在展开的下拉列表中选择“单变量求解”选项，打开“单变量求解”对话框，如图7-4所示。

图7-3　输入公式并得到B的值

图7-4　选择“单变量求解”选项

STEP05：在“单变量求解”对话框中设置目标单元格为“$B$7”，设置目标值为“6”，设置可变单元格为“$B$6”，然后单击“确定”按钮，如图7-5所示。

STEP06：随后会弹出如图7-6所示的“单变量求解状态”对话框，再次单击“确定”按钮返回工作表即可，此时可以求得B的值，如图7-7所示。

图7-5　单变量求解对话框

图7-6　单变量求解状态对话框

图7-7　求解结果

Excel 2019取消数据筛选

打开“主科目成绩表（筛选特定数值段）.xlsx”工作簿，在对工作表进行了数据筛选后，如果要取消当前数据范围的筛选或排序，则可以执行以下操作。

方法一：在D1单元格处单击筛选按钮，在展开的“筛选”列表中选择“从‘语文’中清除筛选”选项即可，如图6-60所示。

方法二：如果在工作表中应用了多处筛选，用户想要一次清除，则可以切换至“数据”选项卡，在“排序和筛选”组中单击“清除”按钮即可，如图6-61所示。

图6-60　清除筛选

图6-61　单击“清除”按钮

Excel 2019利用高级筛选删除重复数据

Excel有一个小小的缺陷，那就是无法自动识别重复的记录。虽说Excel中并没有提供清除重复记录这样的功能，但是可以利用它的高级筛选功能来达到相同的目的。

打开“成绩总计表.xlsx”工作簿，以该工作簿中的数据为例，筛选出“总分在450分以上且语文成绩在75分以上”的记录，具体操作步骤如下。

STEP01：在A18:B20单元格区域输入要进行筛选的条件，输入结果如图6-57所示。

图6-57　输入筛选条件

STEP02：如图6-58所示，在“方式”列表框中选择“将筛选结果复制到其他位置”单选按钮，设置引用的“列表区域”位置为“Sheet1!$A$1:$J$16”，引用的“条件区域”为“Sheet1! $A$19:$B$20”，并选择将筛选的结果复制到“Sheet1! $A$22”单元格处，然后勾选“选择不重复的记录”复选框，最后单击“确定”按钮完成高级筛选设置。最终结果如图6-59所示，筛选结果中重复的数据只显示唯一的一条记录。

图6-58　设置筛选区域

图6-59　删除重复记录结果

图解Excel 2019高级筛选

如果采用高级筛选方式则可将筛选出的结果存放于其他位置，以便分析数据。在高级筛选方式下可以实现同时满足两个条件的筛选。

仍以“主科目成绩表.xlsx”工作簿为例，筛选出“总分在245分以上且语文成绩在83分以上”的记录，具体操作步骤如下。

STEP01：在A17:B19单元格区域输入要进行筛选的条件，输入结果如图6-53所示。

STEP02：单击数据区域中的任意单元格，这里选择B2单元格，切换至“数据”选项卡，单击“排序和筛选”组中的“高级”按钮，打开“高级筛选”对话框，如图6-54所示。

图6-53　输入筛选条件

图6-54　单击“高级”按钮

STEP03：如图6-55所示，在“方式”列表框中选择“将筛选结果复制到其他位置”单选按钮，设置引用的“列表区域”位置为“Sheet1!$A$1:$F$14”，引用的“条件区域”为“Sheet1!$A$18:$B$19”，并选择将筛选的结果复制到“Sheet1! $D$21:$I$30”单元格区域处，最后单击“确定”按钮完成高级筛选设置。最终结果如图6-56所示。

图6-55　设置高级筛选

图6-56　高级筛选结果

Excel 2019筛选特定数值段步骤图解

在对数据进行数值筛选时，Excel 2019还可以进行简单的数据分析，并筛选出分析结果，例如筛选高于或低于平均值的记录。打开“主科目成绩表.xlxs”工作簿，如图6-49所示。以语文成绩数据为例，说明筛选高于平均值记录的具体操作步骤。

STEP01：在数据区域选择任意单元格，这里选择C2单元格。切换至“数据”选项卡，单击“排序和筛选”组中的“筛选”按钮，如图6-50所示。

图6-49　目标数据

图6-50　单击“筛选”按钮

STEP02：在D1单元格处单击筛选按钮，在展开的下拉列表中选择“数字筛选”选项，在展开的级联列表中选择“高于平均值”选项，如图6-51所示。此时，工作表中只显示语文成绩高于平均值的数据区域，如图6-52所示。

图6-51　设置数字筛选条件

图6-52　筛选出高于平均值的数据

Excel 2019按颜色进行筛选步骤图解

在Excel中不但可以按关键字进行排序和筛选，还可以按颜色进行排序和筛选。以下将通过实例说明如何按颜色进行排序和筛选操作。

STEP01：打开“商品统计.xlsx”工作簿，在数据区域选择任意单元格，这里选择B2单元格。切换至“数据”选项卡，单击“排序和筛选”组中的“筛选”按钮，如图6-44所示。

图6-44　单击“筛选”按钮

STEP02：如图6-45所示，如果是要按颜色进行排序，在C1单元格处单击筛选按钮，在展开的下拉列表中选择“按颜色排序”选项，在展开的级联列表中选择按“黄色”进行排序，即黄色单元格会在显示在工作表中的最顶端。按颜色排序结果如图6-46所示。

图6-45　选择排序依据

图6-46　按颜色排序结果

STEP03：如图6-47所示，如果是按颜色进行筛选，在C1单元格处单击筛选按钮，在展开的下拉列表中选择“按颜色筛选”选项，在展开的级联列表中选择按“黄色”进行筛选，即只有黄色单元格会显示出来。按颜色筛选的结果如图6-48所示。

图6-47　设置筛选颜色

图6-48　按颜色筛选结果

Excel 2019自动筛选数据步骤图解

使用Excel自动筛选功能，可以轻松地把符合某个条件的数据挑选出来。如图6-40所示，用户想把职位为“高级技师”的员工挑选出来。如何实现这一操作呢？

使用“自动筛选”功能把职位为“高级技师”的员工筛选出来的具体操作步骤如下。

STEP01：打开“员工工资统计表.xlsx”工作簿，选择数据区域中的任意单元格，这里选择B2单元格。切换至“数据”选项卡，单击“排序和筛选”组中的“筛选”按钮，如图6-41所示。

图6-40　员工工资统计表

图6-41　单击“筛选”按钮

STEP02：单击B1单元格处的筛选按钮，在展开的下拉列表中取消勾选“中级技师”复选框，然后单击“确定”按钮，如图6-42所示。此时，工作表中只显示职位为“高级技师”的员工，如图6-43所示。

图6-42　设置筛选条件

图6-43　筛选出符合条件的数据

Excel 2019按数据条件进行排序图解

排序是统计工作中经常涉及的一项工作，在Excel中可以将数据按单个条件进行排序、多个条件进行排序，还可以按自定义条件进行排序。

打开“数据条件排序.xlsx”工作簿，以该工作簿中的数据为例来讲解按数据条件进行排序的具体操作方法。

如果需要将数据按某一字段进行排序，此时可以使用按单个条件进行排序的方法。

按单个条件进行排序的具体操作步骤如下。

STEP01：选择数据区域中的任意单元格，这里选择B2单元格。切换至“数据”选项卡，在“排序和筛选”组中单击“排序”按钮，打开“排序”对话框。在“排序”对话框中单击“主要关键字”选择框右侧的下拉按钮，在展开的下拉列表中选择“工资”选项，然后单击“次序”选择框右侧的下拉按钮，在展开的下拉列表中选择“降序”选项，最后单击“确定”按钮，如图6-35所示。

STEP02：此时的数据已经按工资进行了降序排序，效果如图6-36所示。