9．2 销售业绩统计表的综合分析

9．2 销售业绩统计表的综合分析

Excel2010的表格创建好之后，可以对表格中的数据进行计算、排序、筛选、汇总等数据管理方面的工作，通过创建统计图表能显示出数据的分布状况，它使得用户的工作高效、灵活。

本节通过对9．1小节中“销售业绩统计表”的综合分析来学习Excel2010对表格的综合处理方法，要求计算出每个人的销售总额、销售排名，对销售排名进行排序，设置自动筛选和高级筛选、分类汇总和创建图表。

9．2．1 相关知识点

1）公式的使用

公式是对工作表中的数值执行计算的等式，可以用于返回信息、操作单元格内容以及测试条件等。 利用公式可对工作簿的工作表中单元格的数值进行加、减、乘、除等各种运算。

（1）公式的组成

公式以“＝”开头，后面跟表达式。 表达式由运算符和参与运算的数据对象组成。 每个数据对象可以是常量数值、单元格或引用的单元格区域、标志、名称等。

（2）公式的运算符

运算符对公式中的元素进行特定类型的运算。 Excel2010中包含了4种类型的运算符：算术运算符、比较运算符、文本运算符与引用运算符。

数学运算符：＋、－、∗、／、％、＾等。

比较运算符：＝、＞、＜、＞＝、＜＝、＜＞，其值为逻辑值：TRUE或FALSE。

文本运算符：＆（连接），将两个文本连接，其操作数可是带引号的文字，也可以是单元格地址。

引用运算符：：（冒号），（逗号）和（空格），具体含义见表9．1。

表9．1 引用运算符及其含义

 

运算的优先级：数学运算符＞文字运算符＞比较运算符。

（3）使用函数

函数的形式为：函数的名称（参数1，参数2，…），其中，参数可是常量、单元格、区域、区域名、公式或其他函数。

（4）常用函数

①求和函数SUM：表示对选择单元格或单元格区域进行加法运算，其函数语法结构为SUM（number1，number2，…）。

②平均值函数AVERAGE：将选择的单元格区域中的平均值返回到需要保存结果的单元格中，其语法结构为：AVERAGE（number1，number2，…）。

③条件函数IF：实现真假值的判断，它根据逻辑计算的真假值返回两种结果。 该函数的语法结构为：IF（logical＿test，value＿if＿true，value＿if＿false）。 其中，logical＿test表示计算结果为ture或false的任意值或表达式；value＿if＿true表示当logical＿test为ture值时返回的值；value＿if＿false表示当logical＿test为false值时返回的值。

④最大值函数MAX：返回自变量中所有数值型数据的最大值，其函数语法结构为MAX（number1，number2，…）。

⑤最小值函数MIN：返回自变量中所有数值型数据的最小值，其函数语法结构为MIN（number1，number2，…）。

⑥计数函数COUNT：返回自变量数据中数值型数据的个数。 参数可以是位置引用、名称等，含有的数据类型任意，但只有数值型数据才被计数。 其函数语法结构为COUNT（number1， number2，…）。

（5）公式常见显示错误原因与解决方法

①错误显示：＃＃＃＃＃

原因：单元格所包含的数字、日期或时间占位比单元格宽。

解决方案：拖动鼠标指针更改列宽。

②错误显示：＃DIV／0！

原因：除数为0，或在公式中除数使用了空单元格或是包含率值单元格的单元格引用。

解决方案：修改单元格引用，或者在用作除数的单元格中输入不为零的值。

③错误显示：＃VALUE！

原因：输入引用文本项的数学公式。 如果使用了不正确的参数或运算符，或者当执行自动更正公式功能时不能更正公式，都将产生错误信息＃VALUE！。

解决方案：这时应确认公式或函数所需的运算符或参数是否正确，检查公式引用的单元格中包含有效的数位。

④错误显示：＃REF！

原因：删除了被公式引用的单元格范围。

解决方案：恢复被引用的单元格范围，或是重新设定引用范围。

⑤错误显示：＃N／A

原因：无信息可用于所要执行的计算。 在建立模型时，用户可以在单元格中输入＃N／A，以表明正在等待数据。 任何引用含有＃N／A值的单元格都将返回＃N／A。

解决方案：在等待数据的单元格内填充数据。

⑥错误显示：＃NAME？

原因：在公式中使用了Excel所不能识别的文本，比如可能是输错了名称，或是输入了一个已删除的名称，如果没有将文字串括在双引号中，也会产生此错误值。

解决方案：如果使用了不存在的名称而产生这类错误，应确认使用的名称确实存在；如果是名称，函数名拼写错误就应改正过来；将文字串括在双引号中；确认公式中使用的所有区域引用都使用了冒号（：）。

2）单元格的引用

公式的引用是对工作表中的一个或一组单元格进行标识，从而告诉公式使用哪些单元格的值。 通过引用，可以在一个公式中使用工作表不同部分的数据，或者在几个公式中使用同一单元格的数值。 在Excel2010中，引用公式的常用方式包括相对引用、绝对引用与混合引用。

（1）相对引用

公式中的相对单元格引用（如A1），是基于包含公式和单元格引用的单元格的相对位置。如果公式所在单元格的位置改变，引用也将随之改变。 如果多行或多列地复制或填充公式，引用会自动调整。 默认情况下，新公式使用相对引用。

（2）绝对引用

公式中的绝对单元格引用（如﹩A﹩1）总是在特定位置引用单元格。 如果公式所在单元格的位置改变，绝对引用将保持不变。 如果多行或多列地复制或填充公式，绝对引用将不作调整。 默认情况下，新公式使用相对引用，可能需要将它们转换为绝对引用。 例如，如果将某个源单元格中的绝对引用复制或填充到目标单元格中，则这两个单元格中的绝对引用相同。

（3）混合引用

混合引用具有绝对列和相对行，或绝对行和相对列。 绝对引用列采用﹩A1、﹩B1等形式。绝对引用行采用A﹩1、B﹩1等形式。 如果公式所在单元格的位置改变，则相对引用将改变，而绝对引用则保持不变。 如果多行或多列地复制或填充公式，相对引用将自动调整，而绝对引用则不作调整。

9．2．2 操作步骤

1）计算销售总额

使用公式计算销售总额，要创建公式。 对于简单的公式，可以在单元格中直接输入，也可以在编辑栏中输入。

计算每位员工3个月的销售总额，操作步骤如下：

步骤1：单击要输入公式的单元格G3，由于要计算的销售总额和各月份的销售额直接相联系，公式中选用相对引用形式，在G3中输入公式“＝D3＋E3＋F3”，如图9．17所示。

 

图9．17 输入公式

步骤2：按回车键或单击编辑栏中的“输入”按钮结束公式的输入，单元格中显示公式运算的结果，如图9．18所示。

 

图9．18 得出结果

注意

计算销售总额也可使用“开始”选项卡“编辑”组中的按钮，单击该按钮，弹出的下拉菜单如图9．19左图所示。 执行求和命令，此时在G3单元格中显示出总额的计算公式 ＝SUM（D3：F3），如图9．22右图所示，按回车键即可得到与图9．18所示的相同结果。

 

图9．19 使用函数计算销售总额

步骤3：选定G3单元格，利用填充柄向下填充G3单元格中的公式，可得到后面员工的销售总额，如图9．20所示。 该填充利用的是相对引用。

 

图9．20 利用“填充柄”填充公式

步骤4：利用“格式刷”设置标题行中的“总额”和计算出的总额数据格式。 具体方法为：选中A2：F2中的某一个单元格（如F2）或单元格区域，单击“开始”选项卡“剪贴板”组中的“格式刷”按钮，如图9．23所示，然后单击“总额”所在的单元格。利用相同的方法选中A3：F26中的某一个单元格（如F10）或单元格区域，单击“开始”选项卡“剪贴板”组中的“格式刷”按钮，然后单击计算出的总额数据。设置结果如图9．21所示。

 

图9．21 利用“格式刷”设置格式和边框

2）计算销售排名

在使用Excel处理工作表时，经常要用函数和公式来自动处理大量的数据。

Excel2010将具有特定功能的一组公式组合在一起以形成函数。 与直接使用公式进行计算相比较，使用函数进行计算的速度更快，同时减少了错误的发生。

使用函数时，应遵循一定的语法格式，即以函数名称开始，后面跟左圆括号“（”，然后列出以逗号分隔的函数参数，最后以右圆括号“）”结束。 函数的一般语法格式如下：

function＿name（arg1，arg2，…）

其中，function＿name为函数名称，每个函数都拥有一个唯一的名称，例如AVERAGE和SUM等；arg1和arg2等为函数的参数，这些参数必须用圆括号括起来，有效的参数值可以是参数，也可以是数字、文本、TRUE或FALSE等逻辑值、错误值（如＃N／A）或单元格引用。 参数也可以是数组或其他函数。

计算销售排名，可使用RANK（）函数实现。

RANK（）函数用来返回指定数字在一列数字中的排位，格式如下：

RANK（number，ref，order）

其中，number为待排序的数字；ref为一列数字；order用于指定排位的方式，如果为0或忽略，降序；非零值，升序。

函数的输入方法有两种：直接输入函数和利用函数向导输入。 下面使用这两种方法实现函数的输入。

方法1：直接输入函数。

根据RANK（）函数的格式，为了使用填充柄统一计算出每个员工的销售排名，需要在第一位员工排名的单元格利用引用的方法写出公式，第一位员工的number为单元格地址G3（相对引用）。ref是比较的范围，在利用填充柄向下填充的过程中需要保持比较的范围不变，因此ref可使用混合引用表示的单元格区域G﹩3：G﹩26或使用绝对引用表示的单元格区域﹩G﹩3：﹩G﹩26。这里采用降序的方式排位，因此可选order为0。

通过上述的分析可得出第一位员工排名的公式为＝ RANK（G3，G﹩3：G﹩26，0）。

计算的具体步骤如下：

步骤1：在单元格H3或选中H3单元格在编辑栏中输入等号“＝”。

步骤2：在“＝”右侧输入函数本身“RANK（G3，G﹩3：G﹩26，0）”，如图9．22所示。

 

图9．22 直接输入函数

步骤3：输入完后，按回车键确认，得出第一位员工的排名。

步骤4：选中H3单元格，利用填充柄向下填充，得出其余员工的排名，如图9．23所示。

 

图9．23 计算排名

方法2：利用函数向导输入。

“利用函数向导输入”计算销售排名的具体步骤如下：

步骤1：单击要插入函数的单元格“H3”。

步骤2：单击“开始”选项卡“编辑”组中的按钮，在弹出的下拉列表中单击按钮。

步骤3：弹出“插入函数”对话框，如图9．24所示，选择列表设置“全部”。

步骤4：选择函数“RANK”，单击按钮，弹出“函数参数”对话框，如图9．25所示。

 

图9．24 “插入函数”对话框

 

图9．25 “函数参数”对话框

步骤5：在Number处输入单元格地址G3，ref处输入单元格区域G﹩3：G﹩26，order处输入0，然后单击按钮，得出第一位员工的排名。

步骤6：重复方法1中的步骤4，可得到图9．25所示的结果。

注意

“利用函数向导输入”函数，也可单击“公式”选项卡“函数库”组的下拉按钮，在弹出的下拉列表中选择中的函数，如图9．26所示。

注意

“直接输入函数”的方法适用于简单的函数。

对于较复杂的函数，为了避免在输入过程中产生错误，常常“利用函数向导”的输入方法。

3）数据排序

数据排序是指按一定规则对存储在工作表中的数据进行整理和重新排列。 数据排序可以为数据的进一步管理做好准备。 Excel2010的数据排序包括简单排序和高级排序等。

 

图9．26 “公式”选项卡中的“函数库”组

现对“销售业绩统计表”的“排名”按升序进行排序，使用“简单排序”的具体操作步骤如下：

步骤1：选中要进行排序的H列中任意一个单元格。

步骤2：在“数据”选项卡的“排序和筛选”组中单击按钮，如图9．27所示。

 

图9．27 “排序”按钮

步骤3：得到如图9．28所示的排序结果。

 

图9．28 “排序”结果

注意

在进行较多数据的排序时，常常使用“自定义排序”对数据排序。 具体步骤为：在“数据”选项卡的“排序和筛选”组中单击按钮，弹出如图9．29所示的“排序”对话框，在该对话框中可根据需要增加“添加条件”，单击“添加条件”，增加排序的“次要关键字”，如图9．30所示。

 

图9．29 “排序”对话框

 

图9．30 增加“次要关键字”

4）筛选数据

使用筛选功能可使数据表中仅显示那些满足显示条件的行，并隐藏那些不满足显示条件的行。 Excel中的筛选包括自动筛选和高级筛选。 筛选数据后，对于筛选过的数据的子集，不需要重新排列或移动就可以复制、查找、编辑、设置格式、制作图表和打印。

（1）自动筛选

自动筛选是按照选定的内容进行排序，适用于简单条件。 自动筛选的具体操作步骤如下：

步骤1：选定要进行筛选操作的工作表中的任意单元格，打开“数据”选项卡的“排序和筛选”组，单击按钮，即可看到每列旁边有一个下三角按钮，如图9．31所示。

 

图9．31 显示下拉列表框

步骤2：如要筛选“销售（1）部”的销售情况，单击C列中的下三角按钮，弹出如图9．32所示的下拉菜单。

步骤3：在该下拉菜单中取消“全选”，选中“销售（1）部”，如图9．33所示。

 

图9．32 下拉菜单

 

图9．33 选中“销售（1）部”

步骤4：单击按钮，筛选结果如图9．34所示。

 

图9．34 “自动筛选”结果

注意

自动筛选也可以对多个字段进行叠加筛选，只要先在一个字段中设置筛选条件，然后在筛选结果中再对另一个字段设置筛选条件即可。

（2）高级筛选

如果要通过复杂的条件来筛选单元格区域，就要使用高级筛选命令。 在使用高级筛选命令前，必须在数据表中创建一个条件区域。

注意

条件区域必须具有列标签。 多个条件之间若是“且”的关系，对应条件写在同一行；多个条件之间若是“或”的关系，对应条件应写在不同行。

例如：使用“高级筛选”筛选出“销售（1）部”且“销售排名”在“前10”的员工记录。 操作步骤如下：

步骤1：输入列标签与筛选条件，单击要进行筛选操作工作表中的任意单元格，单击“数据”选项卡“排序和筛选”组中的按钮，如图9．35所示，弹出如图9．36所示的“高级筛选”对话框。

 

图9．35 输入列标签与筛选条件

步骤2：在“高级筛选”对话框中单击“列表区域”右侧的压缩对话框按钮，选定列表区域Sheet1！﹩A﹩2：﹩H﹩26，然后单击“条件区域”右侧的压缩对话框按钮，选定条件区域Sheet1！﹩E﹩28：﹩F﹩29，如图9．37所示。

 

图9．36 “高级筛选”对话框

 

图9．37 选定相应的区域

步骤3：单击按钮，得到如图9．38所示的结果。

 

图9．38 “高级筛选”结果

注意

若要取消“高级筛选”的结果，可单击“数据”选项卡“排序和筛选”组中的“清除”按钮。

5）分类汇总

分类汇总是对数据清单进行数据分析的一种方法，它是对数据库中指定的字段进行分类，然后统计同一类记录的有关信息。 统计的内容可以由用户指定，也可以统计同一类记录的记录条数，还可以对某些数值段求和、求平均值、求极值等。

在执行分类汇总之前，首先应对数据列表进行排序，将数据列表中关键字相同的一些记录集中在一起。

例如：对“销售业绩统计表”按“部门”汇总出各部门的销售总额的平均值。 操作步骤如下：

步骤1：选中“部门”所在列中的任意单元格，然后对该列进行升序或降序排序（以升序为例），排序后的结果如图9．39所示，可看出关键字相同的一些记录集中在一起。

步骤2：选定数据列表中任意一个单元格。

 

图9．39 按“部门”排序

步骤3：单击“数据”选项卡上“分级”显示组中的“分类汇总”按钮，打开如图9．40所示的“分类汇总”对话框。

 

图9．40 “分类汇总”对话框

步骤4：在“分类汇总”对话框中选择“分类字段”为“部门”，“汇总方式”为“平均值”，“选定汇总项”为“总额”，然后单击按钮，得到如图9．41所示的“分类汇总”结果。

 

图9．41 “分类汇总”结果

注意

若要取消“分类汇总”的结果，恢复到数据列表的初始状态，操作步骤为：选定分类汇总数据列表中任意一个单元格，然后打开“分类汇总”对话框，单击该对话框中的“全部删除”按钮，最后单击“确定”按钮，即可清除分类汇总。

6）创建图表

为了能更加直观地表达表格中的数据，可将数据以图表的形式表示。 通过图表可以清楚地了解各个数据的大小以及数据的变化情况，方便对数据进行对比和分析。

Excel2010自带有各种各样的图表，如柱形图、折线图、饼图、条形图、面积图、散点图等，各种图表各有优点，适用于不同的场合。 图表的组成如图9．42所示。

图表分为两类：嵌入式图表和独立图表。 嵌入式图表与源数据放在同一张工作表中，打印时同时打印。 独立图表与源数据放在不同的工作表中，打印时是分开打印。

现在创建“销售业绩统计表”前两位员工“总额”的嵌入式图表。 具体操作步骤如下：

步骤1：选定用于创建图表的数据B2：B4，按住Ctrl键同时选定C2：C4区域。

步骤2：打开“插入”选项卡，单击“图表”组中“柱形图”按钮，如图9．43所示，然后单击“三维柱形图”中“三维簇状柱形图”按钮，得到如图9．44所示的嵌入式图表。

 

图9．42 图表的组成

 

图9．43 选择图表类型

 

图9．44 创建图表效果

注意

若要编辑图表，可通过如图9．45所示的“设计”选项卡中的命令设置。

若要修改图表布局，可通过如图9．46所示的“布局”选项卡中的命令设置。

若要设置图表格式，可通过如图9．47所示的“格式”选项卡中的命令设置。

 

图9．45 “设计”选项卡

 

图9．46 “布局”选项卡

 

图9．47 “格式”选项卡

9．2．3 案例小结

本节从Excel2010的高级操作入手，详细讲述了公式和函数、数据的管理与分析，以及用图表分析数据等内容，使读者对Excel的学习更加深入，应用更加熟练。

习 题

1．填空题

（1）工作簿是Excel2010中计算和存储数据的文件，扩展名为_____。

（2）每个工作簿内最多可以有_____个工作表，当前工作的只有一个，称为_____。

（3）按_____键可以同时选中多个连续的工作表，按_____键则同时选中多个不连续工作表。

（4）在Excel2010中，A1和A2单元格中的数字分别是“1”和“5”，选定这两个单元格之后，用填充柄填充到“A3：A5”单元格区域，A4单元格中的值是_____。

（5）在Excel2010中，按_____键可以输入当前日期。

（6）在Excel2010中，日期型数据在单元格内自动_____（左／右）对齐。

（7）在Excel2010中，函数“＝AVERAGE（1，2，3）”的值是_____。

（8）要使单元格的列宽正好显示出数据，可以在列标的分隔线上_____（单／双）击。

（9）在单元格中输入公式“＝IF（1＋1＝2，“天才””奇才”）”后，显示结果是_____。

2．选择题

（1）在Excel2010中，当公式中出现被“0”（零）除的情况时，会产生（ ）错误信息。

A．“＃＃＃＃＃＃” B．“＃VALUE！” C．“＃DIV／0！” D．“＃NAME？”

（2）在Excel2010中，公式“＝MIN（4，3，2，1）”的值是（ ）。

A．1 B．2 C．3 D．4

（3）函数AVERAGE（A1：B5）相当于（ ）。

A．求（A1：B5）区域的最小值 B．求（A1：B5）区域的平均值

C．求（A1：B5）区域的最大值 D．求（A1：B5）区域的总和

（4）在Excel2010中，（ ）是绝对地址，可以在复制或填充公式时，系统不会改变公式中的绝对地址。

A．A1 B．﹩A1 C．A﹩1 D．﹩A﹩1

（5）在Excel2010中，公式“＝IF（1＜2，3，4）”的值是（ ）。

A．4 B．3 C．2 D．1

（6）在Excel2010中，求最小值的函数是（ ）。

A．IF B．COUNT C．MIN D．MAX

（7）在Excel2010工作表中，用于表示单元格绝对引用的符号是（ ）。

A．＃ B．％ C．— D．﹩

（8）在Excel2010中，与公式“＝SUM（A1：A3，B1）”等价的公式是（ ）。

A．“＝A1＋A3＋B1” B．“＝A1＋A2＋A3”

C．“＝A1＋A2＋A3－B1” D．“＝A1＋A2＋A3＋B1”

（9）在Excel2010中，单元格区域A1：B3共有（ ）个单元格。

A．4 B．6 C．8 D．10

3．判断题（1）在选定区域内移动活动单元格可以使用鼠标左键或者方向键。 （ ）

（2）在工作表的保护中，密码区分大小写。 （ ）

（3）对数字格式的数据，Excel默认为右对齐。 （ ）

（4）在Excel2010中，MIN和SUM函数功能相同。 （ ）

（5）在Excel2010中，条件格式可以对多个条件设置格式。 （ ）

（6）在Excel2010中，MAX是求最大单元格地址的函数。 （ ）

（7）在Excel2010中，SUM函数的参数必须是单元格引用。 （ ）

（8）在Excel2010中，默认的单元格引用方式是混合地址。 （ ）

（9）在Excel2010中，公式填充或复制时，公式中的混合地址不变。 （ ）

（10）在Excel2010中，工作簿包含工作表。 （ ）

（11）在Excel2010中，A1～A2是一个单元格区域引用。 （ ）