EXCEL数据分析处理

EXCEL数据分析处理

          Excel提供了强大的数据分析处理功能，利用它们可以实现对数据的排序、分类汇总、筛选及数据透视等操作。

      在进行数据分析处理之前，首先必须注意以下几个问题：

（1）避免在数据清单中存在有空行和空列。

（2）避免在单元格的开头和末尾键入空格。

（3）避免在一张工作表中建立多个数据清单，每张工作表应仅使用一个数据清单。

（4）在工作表的数据清单应与其他数据之间至少留出一个空列和一个空行，以便于检测和选定数据清单。

（5）关键数据应置于数据清单的顶部或底部。

2.3.1  数据排序2.3.1.1  数据排序的规则

        Excel允许对字符、数字等数据按大小顺序进行升序或降序排列，要进行排序的数据称之为关键字。不同类型的关键字的排序规则如下：

数值：按数值的大小。

字母：按字母先后顺序。

日期：按日期的先后。

汉字：按汉语拼音的顺序或按笔画顺序。

逻辑值：升序时FALSE排在TRUE前面，降序时相反。

空格：总是排在最后。

2.3.1.2  数据排序步骤

     （1）单击数据区中要进行排序的任意单元格。

     （2）单击【数据】菜单，选择【排序】项，系统将弹出【排序】对话框，如图2-35所示。

图2-35  【排序】对话框

       （3）在【排序】对话框中用下拉列表框选择要排序的关键字，关键字有“主要关键字”、“次要关键字”和“第三关键字”，根据需要分别选择不同的关键字；

（4）单击【确定】按钮，数据就按要求进行了排序。

当只有一个关键字时，可以单击工具栏上的升序按钮或降序按钮，进行自动排序。

2.3.1.3  自定义排序

在有些情况下，对数据的排序顺序可能非常特殊，既不是按数值大小次序、也不是按汉字的拼音顺序或笔画顺序，而是按照指定的特殊次序，如对总公司的各个分公司按照要求的顺序进行排序，按产品的种类或规格排序等等，这时就需要自定义排序。

利用自定义排序方法进行排序，首先应建立自定义序列，其方法可参阅第1章的有关内容。建立好自定义序列后，即可对数据进行排序，方法是：单击数据区中要进行排序的任意单元格，单击【数据】菜单，选择【排序】项，在弹出的【排序】对话框中单击【选项】按钮，系统弹出【排序选项】对话框，如图2-36所示，在【自定义排序次序】的下拉列表中，选择前面建立的自定义序列，然后单击【确定】按钮，即可对数据进行自定义排序。

图2-36  【排序选项】对话框

    2.3.2  数据的查找与筛选

      企业的管理人员经常需要在数据库或数据清单众多的数据中找出需要的数据，Excel提供了功能强大的数据查找与筛选工具。数据查找是指从原始数据中提取满足条件的数据记录，源数据不会改变，也不会被隐藏；数据筛选是指把数据库或数据清单中所有不满足条件的数据记录隐藏起来，只显示满足条件的数据记录。常用的数据查找与筛选方法有：记录单查找、自动筛选和高级筛选。

       下面结合实例说明各种查找方法的具体应用。

【例2—11】图2-37为某公司的部分商品销售记录清单。

图2-37  某公司的商品销售明细清单

      根据图2-37中的有关资料，可以分别采用记录单查找、自动筛选或高级筛选的方式查找或选择所需要的信息，如下所述：

    2.3.2.1  记录单查找

      记录单是查找和编辑数据的最简单的方法，利用记录单，不仅可以查找数据记录，还可以修改和删除记录、添加新的数据记录等。

         1．查找数据记录

      利用记录单查找数据记录的步骤如下：

（1）用鼠标单击数据清单或数据库中的任一非空单元格。

（2）单击【数据】菜单，选择【记录单】项，则系统弹出如图2-38所示的记录单。

图2-38  记录单

（3）单击记录单中的【条件】按钮，则弹出记录单条件对话框，如图2-39所示。

图2-39  记录单条件对话框

      （4）输入条件，比如要查找“张三”的销售记录，则在【销售人员】栏中输入“张三”，然后单击【上一条】按钮或【下一条】按钮，系统就逐次显示满足条件的记录行。

       还可以使用多个条件联合查找记录，此处不再叙述。

           2．修改或删除记录

       在图2-38所示的记录单中，即可对某一记录的各字段进行修改。若要删除显示的记录，只需单击记录单上的【删除】按钮即可。

            3．添加新的记录

       在图2-38所示的记录单中，单击记录单上的【新建】按钮，则出现各字段均为空白的新建记录单，在记录单中输入各字段的值，输入完毕后，单击【新建】按钮，即完成添加新记录。

2.3.2.2  自动筛选与自定义筛选

          1．自动筛选

       记录单检索数据每次只能显示一个数据行，当查询的数据较多，或要把查询的结果汇总成表时，就需要使用筛选工具了。自动筛选提供了快速检索数据清单或数据库的方法，通过简单的操作，就能筛选出需要的数据。利用自动筛选查找数据的步骤如下：

（1）用鼠标单击数据清单或数据库中的任一非空单元格。

（2）单击【数据】菜单，选择【筛选】项，在【筛选】子菜单中选择【自动筛选】，则系统自动在数据清单的每列数据的标题旁边添加一个下拉列标标志，如图2-40所示。

图2-40  自动筛选的下拉列表标志

     （3）单击需要筛选的下拉列表，系统显示出可用的筛选条件，从中选择需要的条件，即可显示出满足条件的所有数据。例如，要查找所有彩电的销售记录，单击“商品”右边的下拉列表，从中选择“彩电”项，则所有的彩电销售记录就显示出来，而其他的数据则被隐藏，如图2-41所示。

图2-41  彩电销售清单的筛选结果

       如果有关彩电的销售记录很多，超过了10个，当需要只显示10个记录时，可单击“单价”、“数量”、“金额”等右边的下拉列表中的“前10个”项，系统弹出【自动筛选前10个】对话框，如图2-42所示。这里，在【显示】下拉列表中“最大”表示最大（最好）的前10个记录，“最小”表示最小（最差）的前10个记录。中间的编辑框中的数值表示显示的记录行数，系统默认值为10，但可以修改，根据需要输入数值即可。

图2-42  【自动筛选前10个】对话框

        若要恢复所有的记录，则单击“商品”右边的下拉列表中的“全部”项。若要取消【自动筛选】状态，则单击【数据】菜单，选择【筛选】项，在【筛选】子菜单中再次选择【自动筛选】。

           2．自定义筛选方式

       当在图2-40所示的下拉列表中选择“自定义”项时，则会弹出【自定义自动筛选方式】对话框，如图2-43所示，用户可根据具体条件对各栏进行设置。如要查找销售金额大于或等于“150000”且小于或等于“200000”的所有记录，则单击左上角的下拉箭头，选择“大于或等于”，右上角的条件值输入“150000”，单击左下角的下拉箭头，选择“小于或等于”，右下角的条件值输入“200000”，单击【确定】按钮，并选择“与”条件，则满足这些条件的所有记录就显示出来了，如图2-44所示。

图2-43  【自定义自动筛选方式】对话框

图2-44  【自定义自动筛选方式】筛选的结果

     2.3.2.3  高级筛选

       高级筛选可以使用较多的条件来对数据清单进行筛选，这些条件既可以是与条件，也可以是或条件，或与条件，与或条件的组合使用，还可以使用计算条件。

           1．一般情况下的高级筛选

       利用高级筛选对数据清单进行筛选的步骤如下：

      （1）首先应建立一个条件区域。在条件区域中，同一行中的条件是与条件，也就是这些条件必须同时满足；不同行中的条件是或条件，也就是这些条件只要满足其一即可。如需要查找张三销售彩电的所有记录，则建立条件区域如图2-45所示。

图2-45  建立条件区域

       （2）单击数据清单或数据库中的任一非空单元格，然后单击【数据】菜单，选择【筛选】子菜单中的【高级筛选】项，则系统弹出如图2-46所示的【高级筛选】对话框。

图2-46  【高级筛选】对话框

      （3）一般情况下，系统将自动给出了数据区域，用户只需在【条件区域】栏中输入条件区域（本例中为B19:C20，也可以用鼠标拾取单元格区域，此时在条件区域中将显示“销售明细清单!$B$19:$C$20”。

      （4）高级筛选结果可以显示在数据清单的原有区域中，也可以显示在工作表的其他空白单元格区域，系统默认的方式是在数据清单的原有区域中显示结果。若需要在工作表的其他空白单元格区域显示结果，则在【方式】项中选中“将筛选结果复制到其他位置”，并在【复制到】栏中输入需要显示筛选结果的单元格（开头的一个单元格即可）。图2-47为在原有区域显示的高级筛选结果。

图2-47  在原有区域显示的高级筛选结果

      当需要显示原始的全部数据时，可以单击【数据】菜单，选择【筛选】子菜单中的项目，在【筛选】子菜单中选择【全部显示】即可。

     同样的方法可以进行建立或条件、与条件与或条件的组合使用情况下的高级筛选。

           2．计算条件情况下的高级筛选

      在有些情况下，筛选的条件不是一个常数，而是一个随数据清单中数据变化的计算结果，此时无法直接利用高级筛选进行数据筛选。不过，我们可以通过计算条件的方法解决。以例2-20为例（见图2-37），这里要找出销售额大于平均销售额的所有记录。步骤如下：

     （1）在数据清单以外的任一空单元格内输入平均值计算公式，比如在单元格H20中输入公式“=AVERAGE(E3:E16)”，这里要特别注意的是存放平均值计算公式的单元格的列标不能与数据清单的任一列标相同，如图2-48所示。

图2-48  计算条件情况下的高级筛选

      （2）设置条件区域，条件区域的列表可以是除数据清单中数据标题以外的任何文本，而筛选条件可在单元格B20中输入“=E3>$H$20”，这里要特别注意：必须以绝对引用的方式引用销售额平均值，以相对引用的方式引用数据清单中的数据。

     （3）按照前面介绍的步骤进行高级筛选，其中高级筛选的数据区域为$A$2:$G$16；高级筛选的条件区域为$B$19:$C$20，则筛选结果如图2-48所示。

2.3.3  数据的分类与汇总

      在对数据进行分析时，常常需要将相同类型的数据统计出来，这就是数据的分类与汇总。在对数据进行汇总之前，应特别注意的是：首先必须对要汇总的关键字进行排序。

2.3.3.1  进行分类汇总

      例如，在例2-11中，要按地区进行自动分类汇总，其步骤如下：

      （1）首先对“地区”进行排序，排序方法见前面所述。

      （2）单击数据清单或数据库中的任一非空单元格，然后单击【数据】菜单，选择【分类汇总】项，系统弹出如图2-49所示的【分类汇总】对话框。

图2-49  【分类汇总】对话框

    （3）在【分类汇总】对话框中，【分类字段】选项下选择“地区”，【汇总方式】选项下选择“求和”，【选定汇总项】选项下选定“数量”和“金额”，单击【确定】按钮，则分类汇总的结果如图2-50所示。

图2-50  按地区分类汇总结果

       在图2-50中，左上角有3个按钮，按钮1表示1级汇总，显示全部的销售数量和销售金额汇总；按钮2表示2级汇总，显示各地区的全部销售数量和销售金额汇总；按钮3表示3级汇总，显示各地区的销售数量和销售金额的汇总明细及汇总额（即图2-50所示的汇总结果）。

图2-50中，左边的滑动按钮为隐藏明细按钮，单击此按钮，则将隐藏本级的明细数据，同时变为显示明细按钮，再单击按钮，则将显示本级的全部明细数据，同时变为。

在上述自动分类汇总的结果上，还可以再进行分类汇总，例如再进行另一种分类汇总，两次分类汇总的关键字可以相同，也可以不同，其分类汇总方法与前面的是一样的，此处不再介绍。

2.3.3.2  分类汇总的撤消

如果不再需要分类汇总结果，可在图2-49所示的【分类汇总】对话框中单击【全部删除】，即可撤消分类汇总。

2.3.4  数据透视表

数据透视表是用于快速汇总大量数据的交互式表格，用户可以旋转其行或列以查看对源数据的不同汇总，也可以通过显示不同的页来筛选数据，还可以显示所关心区域的数据明细。通过对源数据表的行、列进行重新排列，使得数据表达的信息更清楚明了。

2.3.4.1  建立数据透视表

以例2-11的数据为例，建立数据透视表的步骤如下：

（1）首先，要保证数据源是一个数据清单或数据库，即数据表的每列必须有列标。

（2）单击数据清单或数据库中的任一非空单元格，然后单击【数据】菜单，选择【数据透视表和图表报告】项，则系统弹出【数据透视表和数据透视图向导—3步骤之1】对话框，如图2-51所示，根据待分析数据来源及需要创建何种报表类型，进行相应的选择，然后单击【下一步】按钮，系统弹出【数据透视表和数据透视图向导—3步骤之2】对话框，如图2-52所示；

图2-51  【数据透视表和数据透视图向导—3步骤之1】对话框

图2-52  【数据透视表和数据透视图向导—3步骤之2】对话框

（3）默认情况下，系统自动将选取整个数据清单作为数据源，如果数据源区域需要修改，则可直接输入“选定区域”，或单击【浏览】按钮，从其他的文件中提取数据源。确定数据源后，单击【下一步】按钮，系统弹出【数据透视表和数据透视图向导—3步骤之3】对话框，如图2-53所示。

图2-53  【数据透视表和数据透视图向导—3步骤之3】对话框

（4）在【数据透视表和数据透视图向导—3步骤之3】对话框中，单击【版式】按钮，出现【数据透视表和数据透视图向导—版式】对话框，如图2-54所示。

（5）【数据透视表和数据透视图向导—版式】对话框中，再根据需要，将右边的字段按钮拖到左边的图上，这里，将“销售人员”拖到“行(R)”图上，将“商品”拖到“列(C)”图上，将“数量（台）”和“金额（元）”拖到“数据(D)”图上，如图2-55所示。

图2-54  【数据透视表和数据透视图向导—版式】对话框

图2-55  设置数据透视表的版式

（6）设置好版式后，单击【确定】按钮，则系统就返回到图2—44所示的【数据透视表和数据透视图向导—3步骤之3】对话框，然后单击【完成】按钮，数据透视表就完成了，如图2-56所示。

这样，通过图2-56的数据透视表，即可看出每个销售人员所销售商品的种类、数量、销售额及其合计数，从而以此为基础可很方便地对每个销售人员的销售业绩进行评价。

图2-56  各个销售人员销售商品的数据透视表

2.3.4.2  数据的透视分析

在图2-56所建立的数据透视表上，可以很方便地进行多角度的统计与分析。比如要了解李四所销售商品的情况，可在“销售人员”下拉列标中只选中“李四”，然后单击“确定”按钮，则李四的销售情况如图2-57所示。

图2-57  李四的销售情况汇总

还可以建立透视图，方法是：单击数据透视表中的任一单元格，单击鼠标右键，在快捷菜单中选择【数据透视图】项，则系统自动显示出数据透视图，从而得到每个销售人员的更为直观的销售情况。

       Excel中的基本求和与条件求和

　　大家都知道，在Excel诸多函数中，使用最多的是求和类函数。今天我们就以图1所示的数据表格为例详细讨论一下求和的问题。

　　本文所涉及函数如下：

　　1、SUM（Number1,Number2……）
　　2、SUMIF（Range,Criteria,Sum_Range）
　　3、数组公式

　　按图2所示的要求，我们来看看具体的统计过程：

　　一、基本求和——用SUM函数

　　1、选中F307单元格，输入公式：=SUM(F2:F302)，即可求出所有工程的“建筑面积”之和。

　　2、用“填充柄”将公式复制到G307单元格中，就可以求出所有工程的“工程造价”之和。

　　注意：如果要临时查看一下某些数据的和，可以这样操作：鼠标右击状态栏，在随后出现的快捷菜单（如图3）中，选定“求和”选项（通常是默认选项）。

　　然后选中需要查看“和”的数值单元格（区域），状态栏中即刻显示出相应的和（如图4）。

        避免Excel产生求和误差

　　在用Excel进行有关数据汇总时，往往会出现误差，即自动求和结果比实际手工求和的结果大。

　　在财务工资表中，涉及到“工资税金”的计算问题，笔者依照“工资税金”的计算方法，制定出Excel的计算公式，根据每个职工的收入项进行相关的计算后，将运算结果自动填入到每个职工的“税金”单元格内。但在进行“税金栏目”纵向汇总求和时，却发现自动求和的结果比手工实际求和的结果多几分钱。

　　经过分析发现，在用Excel求和计算时（尤其是含有小数位的数据），一般会在单元格的“格式属性”中设置保留两位小数，而Excel的自动计算功能往往会精确到小数点后两位数以上，只不过是由于用户的设置原因，在表中看不出来而已。这样，Excel在求和时是按精确位相加的，而不是按显示的两位小数相加，这就产生了舍入误差。

　　解决办法：在工资表的每个职工“税金栏目”计算公式里，事先加入“四舍五入”函数，即：工资税金=ROUND（税金计算公式，2），计算结果保留两位小数。使得Excel在数据计算时，按“显示位”得出计算结果，避免按“精确位”相加所带来的隐性误差，使出现的问题迎刃而解。