excel按类别排名技巧(骨灰级应用).pptxVIP

Excel按类别排名技巧(骨灰级)——深度应用，但绝不写VBA,只用函数适用情形按照类别属性，获得类别内排名Example:全国各县在各自所在省份内的人均GDP排名数据格式：县名 省份 人均GDP县1 …… ……县2 …… ……来一个复杂点的例子：求同行业内的PE ratio 排名最自然的想法：先按类别排序排序是为了让同类聚集在一起所以，先按类别进行排序跟着我的思路走：排名需要排名函数：Rank.EQ(number,ref,[order])Number:指参与排名的数据Ref:指在此数据区域内排名Order:0-降序，1-升序关键问题：让排名数据区域动起来number参数很好说，直接选择待排名的单元格就ok问题是ref,要让它能够标记当前的数据区域。可是，数据区是变化的，怎么办？我们引入offset()函数offset(reference,rows,cols,height,width)reference:引用的起始位置rows: 相对于起始位置，行的偏移量cols: 相对于起始位置，列的偏移量height:引用的数据区高度，即行数width:数据区的宽度，即列数offset就是用来标记数据区的。只要指定合适的参数，能让数据区跟着类别的变化一起变动就好。offset()的参数问题先从容易的开始，数据区就是要排名的区域。列数就是1列，所以，width=1.但行数不固定。因为每一类别的个体数可能不同，。怎么办？统计该类别的数量就好。函数countif()，对满足条件的单元格计数countif(range,criteria)range:指定为整个类别所在列criteria: 让它等于当前类别名称如此，数据区行数的问题解决了。以“全聚德”为列，待排名数据行数Height = countif(F:F, =C9)再回到offset, reference怎么办？对reference的继续思考：每个类别对应排名数据区的首行都是当前类别的第一个单元格所在行而列可以直接指定所以，接下来的工作就是把该类别第一行的行号求出来match()：在单元格区域中搜索指定项，然后返回该项在单元格区域中的相对位置match(lookup_value,lookup_array,match_type)Lookup_value:要查找的值lookup_array：被查找的区域match_type：我们选0，精确匹配Lookup_value: 指定为当前排名单元格对应的类别单元格Lookup_array：我们指定它为类别所在列的整列，如此，返回的就是行号你需要了解的：A1和R1C1有了行号和列，下一步只需要把它们变成单元格引用就行。单元格地址有两种引用方式：1、我们熟悉的A1，列+行号2、我们不熟悉的R1C1, R+行号 C+列号比如“全聚德”这行，类别在C列（第3列），市盈率在F列(第6列)，用match(C9,C:C,0)返回的行号是8. 对于排名数据区域的首个单元格可以按A1格式表示为F8, 也可以按R1C1格式表示为R8C6但是，F8或R8C6都是一个字符串，不可能每个单元格都手动指定。我们要把它变成地址引用，怎么办？Indirect()：专门负责把字符串变成引用地址Indirect(ref_text,a1)Ref_text:就是表示地址的字符串a1: true-a1格式，false-r1c1格式对上面F8或R8C6的引用可以表示成：Indirect(“F8”,true)或indirect(“R8C6”,false)一切具备，我们把offset中的各参数结合以上的函数，组装起来！Reference参数Reference=indirect(“F”match( C9,C:C ,0),true)你会发现，F列需要指定，如果我添加删除列呢？就需要手动去改“F”所以，我们需要找到更灵活的办法。我们用indirect(“R” match(C9,C:C ,0) ”C6”,false)R1部分：用该类别首行行号（等于待排名数据首行号）， match(C9,C:C ,0) C1部分：用待排名数据所在列号，6这时，”F”变成了”6”，6也是个字符，我们要让它动态地表示类别所在列号，怎么办？Cell(),可以返回单元格的很多信息，包括列号Cell(info_type,reference)Info_type:要返回的信息，我们选”col”,列号Reference:就是要获取哪个单元格的信息CELL(col,F9)=6我们重写indirect(“R” match(C9,C:C ,0)) ”C6”,false)为：indirect(“R” match(C9 , C:C ,0)) ”C” CELL(col,F9)=,false),这就是最终要填入offset的reference!Of