EXCEL数据分析处理.docx

《EXCEL数据分析处理.docx》由会员分享，可在线阅读，更多相关《EXCEL数据分析处理.docx（16页珍藏版）》请在三一办公上搜索。

1、EXCEL数据分析处理Excel提供了强大的数据分析处理功能，利用它们可以实现对数据的排 序、分类汇总、筛选及数据透视等操作。在进行数据分析处理之前，首先必须注意以下几个问题：（1）避免在数据清单中存在有空行和空列。（2）避免在单元格的开头和末尾键入空格。（3）避免在一张工作表中建立多个数据清单，每张工作表应仅使用一 个数据清单。（4）在工作表的数据清单应与其他数据之间至少留出一个空列和一个 空行，以便于检测和选定数据清单。（5）关键数据应置于数据清单的顶部或底部。2.3.1数据排序2.3.1.1数据排序的规则Excel允许对字符、数字等数据按大小顺序进行升序或降序排列，要进 行排序的数据称之

2、为关键字。不同类型的关键字的排序规则如下：数值：按数值的大小。字母：按字母先后顺序。日期：按日期的先后。汉字：按汉语拼音的顺序或按笔画顺序。逻辑值：升序时FALSE排在TRUE前面，降序时相反。空格：总是排在最后。2.3.1.2数据排序步骤（1）单击数据区中要进行排序的任意单元格。（2）单击【数据】菜单，选择【排序】项，系统将弹出【排序】对话 框，如图2-35所示。2.3.2.1记录单查找记录单是查找和编辑数据的最简单的方法，利用记录单，不仅可以查找 数据记录，还可以修改和删除记录、添加新的数据记录等。1. 查找数据记录利用记录单查找数据记录的步骤如下：（1）用鼠标单击数据清单或数据库中的任一

3、非空单元格。（2）单击【数据】菜单，选择【记录单】项，则系统弹出如图2-38所 示的记录单。图2-38记录单(3) 单击记录单中的【条件】按钮，则弹出记录单条件对话框，如图2-39所示。图2-39记录单条件对话框(4) 输入条件，比如要查找“张三”的销售记录，则在【销售人员】栏中输入“张三”，然后单击【上一条】按钮或【下一条】按钮，系统 就逐次显示满足条件的记录行。还可以使用多个条件联合查找记录，此处不再叙述。2. 修改或删除记录 在图2-38所示的记录单中，即可对某一记录的各字段进行修改。若要 删除显示的记录，只需单击记录单上的【删除】按钮即可。3.添加新的记录在图2-38所示的记录单中，单

4、击记录单上的【新建】按钮，则出现各 字段均为空白的新建记录单，在记录单中输入各字段的值，输入完毕后， 单击【新建】按钮，即完成添加新记录。2.3.2.2自动筛选与自定义筛选1.自动筛选记录单检索数据每次只能显示一个数据行，当查询的数据较多，或要把 查询的结果汇总成表时，就需要使用筛选工具了。自动筛选提供了快速 检索数据清单或数据库的方法，通过简单的操作，就能筛选出需要的数 据。利用自动筛选查找数据的步骤如下：(1) 用鼠标单击数据清单或数据库中的任一非空单元格。(2) 单击【数据】菜单，选择【筛选】项，在【筛选】子菜单中选择 【自动筛选】，则系统自动在数据清单的每列数据的标题旁边添加一个下拉列

5、标标志，如图2-40所示。图2-40自动筛选的下拉列表标志（3）单击需要筛选的下拉列表，系统显示出可用的筛选条件，从中选 择需要的条件，即可显示出满足条件的所有数据。例如，要查找所有彩 电的销售记录，单击“商品”右边的下拉列表，从中选择“彩电”项， 则所有的彩电销售记录就显示出来，而其他的数据则被隐藏，如图2-41 所示。图2-41彩电销售清单的筛选结果如果有关彩电的销售记录很多，超过了 10个，当需要只显示10个记录 时，可单击“单价”、“数量”、“金额”等右边的下拉列表中的“前 10个”项，系统弹出【自动筛选前10个】对话框，如图2-42所示。这 里，在【显示】下拉列表中“最大”表示最大（

6、最好）的前10个记录，“最小”表示最小（最差）的前10个记录。中间的编辑框中的数值表 示显示的记录行数，系统默认值为10，但可以修改，根据需要输入数值 即可。图2-42【自动筛选前10个】对话框 若要恢复所有的记录，则单击“商品”右边的下拉列表中的“全部” 项。若要取消【自动筛选】状态，则单击【数据】菜单，选择【筛选】 项，在【筛选】子菜单中再次选择【自动筛选】。2.自定义筛选方式当在图2-40所示的下拉列表中选择“自定义”项时，则会弹出【自定 义自动筛选方式】对话框，如图2-43所示，用户可根据具体条件对各 栏进行设置。如要查找销售金额大于或等于“150000”且小于或等于“200000 ”

7、的所有记录，则单击左上角的下拉箭头，选择“大于或等 于”，右上角的条件值输入“150000”，单击左下角的下拉箭头，选择“小于或等于”，右下角的条件值输入“200000”，单击【确定】按钮， 并选择“与”条件，则满足这些条件的所有记录就显示出来了，如图 2-44所示。图2-43【自定义自动筛选方式】对话框图2-44【自定义自动筛选方式】筛选的结果2.3.2.3高级筛选高级筛选可以使用较多的条件来对数据清单进行筛选，这些条件既可以 是与条件，也可以是或条件，或与条件，与或条件的组合使用，还可以 使用计算条件。1.一般情况下的高级筛选利用高级筛选对数据清单进行筛选的步骤如下：(1) 首先应建立一个

8、条件区域。在条件区域中，同一行中的条件是与 条件，也就是这些条件必须同时满足；不同行中的条件是或条件，也就 是这些条件只要满足其一即可。如需要查找张三销售彩电的所有记录， 则建立条件区域如图2-45所示。图2-45建立条件区域(2) 单击数据清单或数据库中的任一非空单元格，然后单击【数据】 菜单，选择【筛选】子菜单中的【高级筛选】项，则系统弹出如囹-46 所示的【高级筛选】对话框。图2-46【高级筛选】对话框（3）一般情况下，系统将自动给出了数据区域，用户只需在【条件区 域】栏中输入条件区域（本例中为B19:C20，也可以用鼠标拾取单元格 区域，此时在条件区域中将显示“销售明细清单!$B$19

9、:$C$20”。（4）高级筛选结果可以显示在数据清单的原有区域中，也可以显示在 工作表的其他空白单元格区域，系统默认的方式是在数据清单的原有区 域中显示结果。若需要在工作表的其他空白单元格区域显示结果，则在【方式】项中选中“将筛选结果复制到其他位置”，并在【复制到】栏 中输入需要显示筛选结果的单元格（开头的一个单元格即可）。图2-47 为在原有区域显示的高级筛选结果。图2-47在原有区域显示的高级筛选结果当需要显示原始的全部数据时，可以单击【数据】菜单，选择【筛选】 子菜单中的项目，在【筛选】子菜单中选择【全部显示】即可。同样的方法可以进行建立或条件、与条件与或条件的组合使用情况下的 高级筛选

10、。2.计算条件情况下的高级筛选在有些情况下，筛选的条件不是一个常数，而是一个随数据清单中数据 变化的计算结果，此时无法直接利用高级筛选进行数据筛选。不过，我 们可以通过计算条件的方法解决。以例2-20为例（见图2-37），这里 要找出销售额大于平均销售额的所有记录。步骤如下：（1）在数据清单以外的任一空单元格内输入平均值计算公式，比如在 单元格H20中输入公式“二AVERAGE（E3:E16）”，这里要特别注意的是存 放平均值计算公式的单元格的列标不能与数据清单的任一列标相同，如 图2-48所示。图2-48计算条件情况下的高级筛选（2）设置条件区域，条件区域的列表可以是除数据清单中数据标题以

11、外的任何文本，而筛选条件可在单元格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）首先

12、对“地区”进行排序，排序方法见前面所述。（2）单击数据清单或数据库中的任一非空单元格，然后单击【数据】 菜单，选择【分类汇总】项，系统弹出如图2-49所示的【分类汇总】 对话框。图2-49【分类汇总】对话框（3）在【分类汇总】对话框中，【分类字段】选项下选择“地区”，【汇总方式】选项下选择“求和”，【选定汇总项】选项下选定“数量” 和“金额”，单击【确定】按钮，则分类汇总的结果如图2-50所示。swooHiwm 三3SW00忻t密I字fHg M首r元J情1U菱5岖2M3-7-1 彩屯noa3EtSHB-T-2 至日60LTD机2003 ?* 心10KCB-7- 1 电的哉1TOiL的EO3OB

13、-T-I i W 调|23MLDQ西泌L20LEOSCW-T-1 W 船SOdEffi3-7 a IZ 岬 JBZHI汹KK3-7-14 母和isanTOI 3 4 6 T 8 9101 l1213H151617lftls30212smMHOIT20D0XSflQO 15CWKI岳六 耳六&WW hskb 就zsaxBI WOO率四 i two李四 IIOOOO李皿 ”理MB李EB5五玉 壬壬王A9&K532000我津 汇金MOLJWWSit图2-50按地区分类汇总结果在图2-50中，左上角有3个按钮，按钮1表示1级汇总，显示全部的 销售数量和销售金额汇总；按钮2表示2级汇总，显示各地区的全部

14、销 售数量和销售金额汇总；按钮3表示3级汇总，显示各地区的销售数量 和销售金额的汇总明细及汇总额（即图2-50所示的汇总结果）。图2-50中，左边的滑动按钮-为隐藏明细按钮，单击此按钮，则将隐藏 本级的明细数据，同时-变为显示明细按钮*，再单击,按钮，则将显示 本级的全部明细数据，同时.变为-。在上述自动分类汇总的结果上，还可以再进行分类汇总，例如再进行另 一种分类汇总，两次分类汇总的关键字可以相同，也可以不同，其分类 汇总方法与前面的是一样的，此处不再介绍。2.3.3.2分类汇总的撤消如果不再需要分类汇总结果，可在图2-49所示的【分类汇总】对话框中单击【全部删除】，即可撤消分类汇总。2.3

15、.4数据透视表数据透视表是用于快速汇总大量数据的交互式表格，用户可以旋转其行或列以查看对源数据的不同汇总，也可以通过显示不同的页来筛选数据，还可以显示所关心区域的数据明细。通过对源数据表的行、列进行重新排列，使得数据表达的信息更清楚明了。2.3.4.1建立数据透视表以例2-11的数据为例，建立数据透视表的步骤如下：（1）首先，要保证数据源是一个数据清单或数据库，即数据表的每列必须有列标。（2）单击数据清单或数据库中的任一非空单元格，然后单击【数据】 菜单，选择【数据透视表和图表报告】项，则系统弹出【数据透视表和 数据透视图向导一3步骤之1】对话框，如图2-51所示，根据待分析数 据来源及需要创

16、建何种报表类型，进行相应的选择，然后单击【下一步】 按钮，系统弹出【数据透视表和数据透视图向导一3步骤之2】对话框，如图2-52所示；图2-51【数据透视表和数据透视图向导一3步骤之1】对话框图2-52【数据透视表和数据透视图向导一3步骤之2】对话框 （3）默认情况下，系统自动将选取整个数据清单作为数据源，如果数 据源区域需要修改，则可直接输入“选定区域”，或单击【浏览】按钮， 从其他的文件中提取数据源。确定数据源后，单击【下一步】按钮，系 统弹出【数据透视表和数据透视图向导一3步骤之3】对话框，如图2-53所示。图2-53【数据透视表和数据透视图向导一3步骤之3】对话框(4) 在【数据透视表

17、和数据透视图向导一3步骤之3】对话框中，单 击【版式】按钮，出现【数据透视表和数据透视图向导一版式】对话框，如图2-54所示。(5) 【数据透视表和数据透视图向导一版式】对话框中，再根据需要， 将右边的字段按钮拖到左边的图上，这里，将“销售人员”拖到“行 (R) ”图上，将“商品”拖到“列(C) ”图上，将“数量(台)”和“金额(元)”拖到“数据(D) ”图上，如图2-55所示。图2-54【数据透视表和数据透视图向导一版式】对话框图2-55设置数据透视表的版式（6）设置好版式后，单击【确定】按钮，则系统就返回到图244所示的【数据透视表和数据透视图向导一3步骤之3】对话框，然后单击【完成】按钮

18、，数据透视表就完成了，如图2-56所示。这样，通过图2-56的数据透视表，即可看出每个销售人员所销售商品 的种类、数量、销售额及其合计数，从而以此为基础可很方便地对每个销售人员的销售业绩进行评价。123 45-6?图2-56各个销售人员销售商品的数据透视表2.3.4.2数据的透视分析在图2-56所建立的数据透视表上，可以很方便地进行多角度的统计与分析。比如要了解李四所销售商品的情况，可在“销售人员”下拉列标中只选中“李四”，然后单击“确定”按钮，则李四的销售情况如图2-57所示。图2-57李四的销售情况汇总还可以建立透视图，方法是：单击数据透视表中的任一单元格，单击鼠标右键，在快捷菜单中选择【数据透视图】项，则系统自动显示出数据透视图，从而得到每个销售人员的更为直观的销售情况。