文|仟樱雪

在日常的数据分析工作中，常需在excel中实现相关数据的排名操作，“Excel函数--常规、中国式排名（全1）”，讲解了常见的根据度量字段（成绩、收入、销量等）进行的基础排名操作；

本篇介绍升级版排名操作，在度量指标的基础上，增加根据部门、品类、平台等分类变量进行限定，实现常规排序、中国式排名分析；

一、数据需求

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

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

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

二、需求实现

1、数据分析--需满足某条件的常规排名

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

案例Excel实现：选中数据区域，点击“数据”菜单栏下的“排序”，会弹出排序对话框，勾选“数据包含标题”，选择需依据排序的“主要关键字”，选择升序或者降序，点击“添加条件，添加次要关键字“毛利率”，即可自动升序或者降序显示。

结果显示：

结果说明：

在各“品类”中“产品”，按照“毛利率”从低到高进行排列，例如，品类“厨房料理”下的四款产品，按照毛利率从低到高进行排列显示，毛利率最低为“天猫平台”的“隔热手套”，毛利率最低为“阿里”的“隔热手套”。

案例2：电商平台的产品销量报表，Excel分析需按照规定排名次序的品类条件，实现升序或者降序显示毛利率，以此直观判断各品类中，产品的销售业绩的好坏程度；

案例Excel实现1：选中数据区域，点击“数据”菜单栏下的“排序”，会弹出排序对话框，勾选“数据包含标题”，选择主要关键字后，添加次要关键字，选择需设置固定排名次序，“主要关键字”的次序选项的下拉框选项，选择“自定义序列”，

输入需设置的固定先后顺序，点击“添加”，确认，则设置主要关键字（品类）的按照设定的固定先后顺序进行排序：

结果显示：

结果说明：

在固定先后排名顺序的“品类”中“产品”，按照“毛利率”从低到高进行排列，例如，品类的排序规定只能按照：

“厨房料理，文体用品，生活竹炭，杯具，烟，电脑周边，发光玩具，卡册，手机周边，卫浴清洁”先后顺序，再次按照产品毛利率高低顺序进行排序，

例如：在品类排序中，“厨房料理”之前没规定时，排第2位，现在按照规定，排第1位，接着“厨房料理”下的四款产品，按照毛利率从低到高进行排列显示，毛利率最低为“天猫平台”的“隔热手套”，毛利率最低为“阿里”的“隔热手套”。

案例Excel实现2：在数据的最后一列增加“品类排名”列，使用countifs函数实现，在N列输入“=COUNTIFS($G$4:$G$20,G4,$M$4:$M$20,">"&M4)+1”，将函数下拉覆盖需排名行，即可实现“毛利率”数字排名显示；

函数技巧：countifs(需判定条件区域1，需判定条件1，需判定条件区域2，需判定条件2），

说明：结果就是毛利率，在G4:G20中，等于G4的范围内，M4:M20中大于M4的个数的排名，这个功能同样可以用countif函数实现，其含义就是统计h2-h18中大于h5的个数，个数为0，排第1，因此结果要加1，

注意：相同毛利率的排名先后，次序存在差异，会有相同的排位，即多个数据排名相同不分先后，重复排名的的存在将影响后续数据的排位；

例如，在本例中，“厨房料理”的分类中，按升序排序的毛利率列，排名2 出现了2次，后续排位直接是第4名，事实上该排位，分别是在第2名和第3名的，造成后面数据的排位超前递进，第4个产品直接排名第4名，因没有第3名，直接跳过1个名次，因此需进行中国式排名，消除此类排名的差异；

2、数据分析--需满足某条件的中国式排名

案例2：电商平台的产品销量报表，Excel分析需按照平台或者品类分组，升序或者降序显示毛利率、排名相同的不占排位的中国式排名，即上述“案例Excel实现2”中的重复出现的名次，但中国式排名就会有2、3排位的名次区分；

案例Excel实现1：在数据的最后一列增加“中国式品类排名”列，使用countifs+countif函数实现，在O列输入“=COUNTIFS($G$4:$G$20,G4,$M$4:$M$20,">"&M4)+1+COUNTIF($M$4:M4,M4)-1”

函数技巧：COUNTIFS+COUNTIF()的组合函数，公式的本质是统计了在归属的品类条件下，大于等于当前毛利率的个数。

（1）COUNTIFS($G$4:$G$20,G4,$M$4:$M$20,">"&M4)：首先判定满足当前的品类的条件下，即$G$4:$G$20中的品类等于G4，且M4:M20中的毛利率大于M4时，等于时，则记录为个数为0，所以+1处理，排第1名。

注意：G4、M4单元格不需锁定，

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

总结：

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

（注：2018.09.30,Excel常见分析大小坑总结，有用就给个小心心哟，后续持续更新ing）