文|仟樱雪

在日常的数据分析工作中，我们常见的需根据产品的销量、收入、数量等进行升序或降序常规排列、中国式排名（同一排名的字段按照某一字段分先后）分析；

或者升级版的在度量指标的基础上，增加根据部门、品类、平台等分类变量的条件，进行常规排序、中国式排名分析；

或者深层次的Excel可视化分析时需设置自动显示TOPn排名的某指标等的常规排序、中国式排名分析，因此综合Excel函数常见的实现方式，可自动实现排名。

一、数据需求

1、常见数据分析，直接进行总量的常规排名、中国式排名；

2、常见数据分析，需满足某条件的常规排名、中国式排名；

3、常见的数据可视化报表设计，需自动显示TOPn常规排名、中国式排名；

二、需求实现

1、数据分析--总量的常规排名

案例1：电商平台的产品销量报表，Excel分析需升序或者降序显示销量，以此直观判断销售业绩的好坏程度；

案例Excel实现1：选中数据区域，点击“数据”菜单栏下的“排序”，会弹出排序对话框，选择需依据排序的字段，选择升序或者降序，“销量”即可自动升序或者降序显示。

案例Excel实现2：在数据的最后一列增加“销量总排名”列，使用rank函数实现，在N列输入“=RANK(I4,$I$4:$I$20,0)”，将函数下拉覆盖需排名行，即可实现“销量”数字排名显示；

函数技巧：rank(需排名的数据，排名数据区域，升序1或者降序0）

说明：升序或者降序：一般用0或者省略表示数据排序是降序排列的；用不为0的数据表示数据排序时升序排列。

注意：rank函数的排序会导致重复数，会有相同的排位，即多个数据排名相同不分先后，重复排名的的存在将影响后续数据的排位； 

例如，在本例中在按降序排序的销量列中，排名1 出现了2次，事实上后面的1个排名第1的排位，是在第2名，造成后面数据的排位都超前递进，第3个产品直接排名第3名，因为没有第2名，直接跳过1个名次，因此需进行中国式排名，消除此类排名的差异；

2、数据分析--总量的中国式排名

中国式排名的名次是连续的，这种排名方式更加符合日常数据分析的习惯，也被称作中国式排名，即同一排名的字段按照某一字段分先后）。

案例2：电商平台的产品销量报表，Excel分析需升序或者降序显示销量、排名相同的不占排位的中国式排名，即常规排名（N列）没有第2名，但中国式排名就会有2排位的名次；

案例Excel实现1：在数据的最后一列增加“销量总排名2”列，使用rank函数实现，在O列输入“=SUMPRODUCT(($I$4:$I$20>=I4)/COUNTIF($I$4:$I$20,$I$4:$I$20))”

函数技巧：SUMPRODUCT+COUNTIF()的组合函数，公式的本质是统计了大于等于当前销量的个数。

（1）SUMPRODUCT(($I$4:$I$20>=I4)：首先判定I4到I20函数的销售量，是否大于等于当前的I4销量，如果成立返回TRUE，否则返FALSE，选中公式的这部分内容，然后按F9（台式机）即可看到显示的TRUE和FALSE的判定结果，在Excel计算中，TRUE是计量为1，而FALSE计量为0的；

注意：sumproduct函数，是将给定的几组数据，数据间对应的元素相乘，并返回乘积之和的函数运算。

（2）COUNTIF($I$4:$I$20,$I$4:$I$20)：将I4到I20单元格的销量进行计数统计，countif(条件区域，需判定区域）；

例如，可见销量为“8”件的只有1个，因此是各个TRUE和FALSE对应的除以1，之后以sumproduct求和得出8件的拍是第2名；

案例Excel实现2：if函数+需排序列的升序和降序简单处理，先讲数据按照“数据”-“排序”-“升序或者降序”处理后，再在最后P列新增“销量排序3”，然后在P5单元格输入数字1，在P4单元格输入“=IF(I5=I4,P4,P4+1)”；

函数技巧：先讲数据升序排序后直接显示第1名即可，然后使用if函数判断，第2个单元格的销量数据是否和第1个单元格的数据相等，如果相等就返回与第1个排名一样的排名，如果不等就在第1个排名基础上“+1”为第2名。

案例Excel实现3：透视表实现中国式汇总排名，选中数据区域，点击“插入”-“插入数据透视表”，选择本sheet页建立透视表，将产品“品类”拖累进行字段，“收入”和“销量”拖入值字段区域汇总求和，再次拖入“销量”字段进入值字段区域选择汇总求和，会出现2个销量求和字段，将另外一个命名为“中国式排名”；

选中另外1个销量字段，右键，“值显示方式”--“降序排列”，选择分类依据“品类”即可生成中国式排名字段；

结果显示：

特殊说明：以上3中中国式排名，仍存在相同名次的缺点，不能严格区分名次等级。

案例Excel实现4：在数据的最后一列增加“销量总排名4”列，使用rank+countif函数实现，在Q列输入“=RANK(I4,$I$4:$I$20,0)+COUNTIF($I$4:I4,I4)-1”

函数技巧：RANK+COUNTIF()的组合函数，公式的本质是将销量按照从高到底排序后，再计数处理所得。

（1）RANK(I4,$I$4:$I$20,0)：首先将I4到I20函数的销售量，是按照从高到低进行排序；

（2）COUNTIF($I$4:I4,I4)-1：统计的是I4这个数据在I列第一个单元格到当前统计单元格出现的个数，记为1，存在重复的数据则累计，所以需-1，去重处理；

结果显示的是排名均按照从高到低，等差递增，不存在相同名次或者跳级错差名次。

总结：

1、Excel函数实现的排名自动显示：在每次复制公式后需注意数据范围的修改与锁定，避免漏掉数据参与排序，造成排序范围错误；

2、Excel透视表的排名自动显示：需注意在每次透视表的字段名称的修正、排序字段的数据源范围的选择。

（注：2018.07.08,Excel常见分析大小坑总结，后续持续更新ing）