运用EXCEL实现数据的自动排列.docx

运用EXCEL实现数据的自动排列.docx

《运用EXCEL实现数据的自动排列.docx》由会员分享，可在线阅读，更多相关《运用EXCEL实现数据的自动排列.docx（8页珍藏版）》请在冰豆网上搜索。

运用EXCEL实现数据的自动排列

一、运用排序功能

   以销售人员的产品销售为例，对业务员的销售额与销售回款额进行排序，基本业务数据如图1所示。

图1

   基本排序步骤如下：

   1、按照员工销售额进行排序，方法为先选定要排序的数据区域，可以包含标题，也可以不包含标题，本例包含，然后选择数据菜单下的排序，出现一个排序对话框，如图2所示。

图2

   2、在主要关键字里输入员工销售额，确定后结果如图3所示

图3

   备注：

   同时我们还可以根据其排序有关要求做更加明细的排序设置，其功能可以在排序选项中来设置。

如图4所示。

图4

   4、然后在排名表中分别输入名次，如图5所示。

图5

   5、对排序数据进行有关优化，在现实生活中，企业业务员的业绩不仅要考虑其它销售额，同时还要考虑其回款，所以我们要加上第二关键字，销售回款额，设置第二关键字销售回款额。

方法及运行结果如图6、7所示。

图6

图7

   5、然后在排名中分别输入1、2、3….等。

   通过上述方面我们可以非常简单地对有关数据进行排序，但是这种方法改变了原来的数据结构与序列。

其实在现实生活中我们不能轻易改变有关数据结构序列的，那样会给其它数据处理带来麻烦。

为此我们可以采用EXCEL中的函数来解决上述问题的不方便。

在EXCEL中RANK（）正好可以解决这个问题。

  二、运用RANK（）实现数据的动态排序。

   1、RANK（）的基本语法：

   Rank（）的功能是返回一个数值在一组数值中的排位。

数值的排位是与数据清单中其他数值的相对大小（如果数据清单已经排过序了，则数值的排位就是它当前的位置）。

   其基本语法为RANK（number,ref,order），其中Number为需要找到排位的数字；Ref为包含一组数字的数组或引用。

Ref中的非数值型参数将被忽略。

Order 为一数字，指明排位的方式。

如果order为0或省略，MicrosoftExcel将ref当作按降序排列的数据清单进行排位。

如果order不为零，MicrosoftExcel将ref当作按升序排列的数据清单进行排位。

   其它说明：

函数RANK对重复数的排位相同。

但重复数的存在将影响后续数值的排位。

例如，在一列整数里，如果整数10出现两次，其排位为5，则11的排位为7（没有排位为6的数值）。

   2、上述例子的基本处理步骤：

   a.建立有关数据的EXCEL表格，如图8所示

图8

   b.按照业务员的产品销售额进行排序。

   方法为在排名中输入RANK（），其单元格F4：

F13公式下表所示

排名

=RANK（D4,$D$4:

$D$13,0）

=RANK（D5,$D$4:

$D$13,0）

=RANK（D6,$D$4:

$D$13,0）

=RANK（D7,$D$4:

$D$13,0）

=RANK（D8,$D$4:

$D$13,0）

=RANK（D9,$D$4:

$D$13,0）

=RANK（D10,$D$4:

$D$13,0）

=RANK（D11,$D$4:

$D$13,0）

=RANK（D12,$D$4:

$D$13,0）

=RANK（D13,$D$4:

$D$13,0）

   3、运行结果如图9所示。

图9

   c.再按照员工销售额排名的基础上，再按回款额降排列。

如图10所示。

图10

   d.在此基础上你可以通过相同数据比较得出最终的排名，结果如图11所示

图11

   e.优化模型，把有关销售额区域单元格与回款额单元格进行名称定义，然后就可以在公式中直接引进名称而不必考虑有关数据的区域位置。

   方法如下：

   1．对销售额、回款额数据区域分别定义为销售额、回款额。

方法为首先选定销售额区域，然后选择插入菜单，名称、定义功能如图12所示

图12

   2、然后在名称对话框中输入销售额，即为员工销售额区域定义名称为销售额。

回款额定义方法相同。

   3、然后在销售额和回款额排名中输入公式如下表所示。

=RANK（D4,销售额,0）

=RANK（E4,回款额,0）

=RANK（D5,销售额,0）

=RANK（E5,回款额,0）

=RANK（D6,销售额,0）

=RANK（E6,回款额,0）

=RANK（D7,销售额,0）

=RANK（E7,回款额,0）

=RANK（D8,销售额,0）

=RANK（E8,回款额,0）

=RANK（D9,销售额,0）

=RANK（E9,回款额,0）

=RANK（D10,销售额,0）

=RANK（E10,回款额,0）

=RANK（D11,销售额,0）

=RANK（E11,回款额,0）

=RANK（D12,销售额,0）

=RANK（E12,回款额,0）

=RANK（D13,销售额,0）

=RANK（E13,回款额,0）

   4、运行结果如图13所示。