文|仟樱雪
在日常的数据分析工作中，我们常见的需根据产品的销量、收入、数量等进行升序或降序常规排列、中国式排名（同一排名的字段按照某一字段分先后）分析；
或者升级版的在度量指标的基础上，增加根据部门、品类、平台等分类变量进行常规排序、中国式排名分析；
或者深层次的Excel可视化分析时需设置自动显示TOPn排名的某指标等的常规排序、中国式排名分析，因此综合Excel函数常见的实现方式，可自动实现排名。
一、数据需求
1、常见数据分析，直接进行总量的常规排名、中国式排名；
2、常见数据分析，需按照分类进行常规排名、中国式排名；
3、常见的数据可视化报表设计，需自动显示TOPn常规排名、中国式排名；
二、需求实现
1、数据分析的总量的常规排名
案例1：电商平台的产品销量报表，Excel分析需升序或者降序显示销量，以此直观判断销售业绩的好坏程度；
案例Excel实现1：选中数据区域，点击“数据”菜单栏下的“排序”，会弹出排序对话框，选择需依据排序的字段，选择升序或者降序，“销量”即可自动升序或者降序显示。
案例Excel实现2：在数据的最后一列增加“销量总排名”列，使用rank函数实现，在N列输入“=RANK(J3,$J$3:$J$19)”，将函数下拉覆盖需排名行，即可实现“销量”数字排名显示；
函数技巧：rank(需排名的数据，排名数据区域，升序或者降序）
说明：升序或者降序：一般用0或者省略表示数据排序是降序排列的；用不为0的数据表示数据排序时升序排列。
注意：rank函数的排序会导致重复数，会有相同的排位，即多个数据排名相同不分先后，重复排名的的存在将影响后续数数据的排位；
例如，在本例中在按降序排序的销量列中，排名1 出现了3次，事实上后面的2个排名第1的排位，分别是在第2名和第2名的，造成后面数据的排位都超前递进，第4个产品直接排名第4名，因为没有第2名和第3名，直接跳过2个名次，因此需进行中国式排名，消除此类排名的差异；
2、数据分析的总量的中国式排名
中国式排名的名次是连续的，这种排名方式更加符合日常数据分析的习惯，也被称作中国式排名，即同一排名的字段按照某一字段分先后）。
案例2：电商平台的产品销量报表，Excel分析需升序或者降序显示销量、排名相同的不占排位的中国式排名，即常规排名（J列）没有第2、3名，但中国式排名就会有2、3排位的名次；
案例Excel实现1：在数据的最后一列增加“销量总排名2”列，使用rank函数实现，在M列输入“=SUMPRODUCT(($J$3:$J$19>=J3)/COUNTIF($J$3:$J$19,$J$3:$J$19))”
函数技巧：SUMPRODUCT+COUNTIF()的组合函数，公式的本质是统计了大于等于当前销量的个数。
（1）SUMPRODUCT(($J$3:$J$19>=J3)：首先判定J3到J19函数的销售量，是否大于等于当前的J3销量，如果成立返回TRUE，否则返回FALSE，选中公式的这部分内容，然后按F9即可看到显示的TRUE和FALSE的判定结果，在Excel计算中，TRUE是计量为1，而FALSE计量为0的；
注意：sumproduct函数，是将给定的几组数据，数据间对应的元素相乘，并返回乘积之和的函数运算。
（2）COUNTIF($J$3:$J$19,$J$3:$J$19)：将J3到J19单元格的销量进行计数统计，countif(条件区域，需判定区域）；
例如，可见销量为“8”件的只有1个，因此是各个TRUE和FALSE对应的除以1，之后以sumproduct求和得出8件的拍是第2名；
案例Excel实现2：if函数+需排序列的升序和降序简单处理，先讲数据按照“数据”-“排序”-“升序或者降序”处理后，再在最后P列新增“销量排序3”，然后在P3单元格输入数字1，在P4单元格输入“IF(J4=J3,P3,P3+1)”；
函数技巧：先讲数据升序排序后直接显示第1名即可，然后使用if函数判断，第2个单元格的销量数据是否和第1个单元格的数据相等，如果相等就返回与第1个排名一样的排名，如果不等就在第1个排名基础上“+1”为第2名
案例Excel实现3：透视表实现中国式汇总排名，选中数据区域，点击“插入”-“插入数据透视表”，选择本sheet页建立透视表，将产品“品类”拖累进行字段，“收入”和“销量”拖入值字段区域汇总求和，再次拖入“销量”字段进入值字段区域选择汇总求和，会出现2个销量求和字段，将另外一个命名为“中国式排名”；
选中另外1个销量字段，右键，“值显示方式”--“降序排列”，选择分类依据“品类”即可生成中国式排名字段；
结果显示：
总结：
1、Excel函数实现的排名自动显示：在每次复制公式后需注意数据范围的修改与锁定，避免漏掉数据参与排序，造成排序范围错误；
2、Excel透视表的排名自动显示：需注意在每次透视表的字段名称的修正、排序字段的数据源范围的选择。
（注：2018.07.08,Excel常见分析大小坑总结，后续持续更新ing）
网友评论