楼主
嘉昆2011 |
声明：素材来源于Chandoo先生博客，链接如下，- http://chandoo.org/wp/2012/10/04/formula-forensics-no-030/?utm_source=feedburner&utm_medium=feed&utm_campaign=Feed%3A+PointyHairedDilbert+%28Chandoo.org+-+Learn+Excel+%26+Charting+Online%29
发帖初衷：
1，通过比较几组函数公式，加深对Index()，Match()，Countif()函数的常用变形公式的理解
2，其他相关函数：Large/Small()，Frequency()，Mode.Mult()
问题陈述：
对于一列文本数据，如何利用公式根据频率高低升序排序得到一组唯一序列？
1，为了便于测试和学习，定义一个动态名称标签DList：- =Sheet1!$A$2:INDEX(Sheet1!$A:$A,COUNTA(Sheet1!$A:$A))
2，看看提取文字序列经历了哪些演变，各组分函数的功能，能学到些什么
注：例子中IfError()函数主要起排错作用，主要关注IfError()里面的函数。
a, 2005年，Gvntw 在帖中归纳总结了Countif函数的经典用法- http://club.excelhome.net/dispbbs.asp?boardid=3&id=93747
- =INDEX(A:A,RIGHT(LARGE(COUNTIF(DList,">="&DList)*10^5+ROW(DList),ROW(1:1)),5))
算法思想：利用对关键字先加权后除权思想，实现对序列的升序排列
b, 2009年，Oscar 在帖中实现了相同的功能（升序排列）- http://www.get-digital-help.com/2009/03/27/sorting-text-cells-using-array-formula/
- =INDEX(DList,MATCH(SMALL(COUNTIF(DList,"<"&DList),ROW(1:1)),COUNTIF(DList,"<"&DList),0))
算法思想：比较字符间的相对大小，返回字符在序列间的相对位置
c, 2010年，Mike 在VLog中实现了单一升序功能- http://www.youtube.com/watch?v=IZLAzIYfMDU&feature=my_liked_videos&list=LLxSG8yOxCnMWykn8bLPzloQ
- =INDEX(DList,MATCH(0,COUNTIF(DList,"<"&DList)-SUM(COUNTIF(DList,D$1:D1)),0))
算法思想：在Oscar的基础上扣除已出现字符在序列中的次数，返回不重复的升序字符串
d, 2012年，Haseeb 在论坛中实现了根据频率高低获取不重复的字符串- http://chandoo.org/forums/topic/ranking-string-data-for-one-column
- =INDEX(DList,MODE(IF(ISNA(MATCH(DList,E$1:E1,0)),MATCH(DList,DList,0)*{1,1})))
算法思想：扣除已出现的字符，统计出频率最高的字符在哪个位置
注：*{1,1}模块是为防止Mode()只出现单一序列而设的除错功能，如{1,2,3,4,5,6,7,8,9,10}，Mode函数会报错
e, 2012年，Chandoo 在结合Oscar和Haseeb的思想上实现了根据频率高低获取不重复的升序字符串- =INDEX(DList,MATCH(MIN(MODE.MULT(IF(NOT(COUNTIF(G$1:G1,DList)),(COUNTIF(DList,"<"&DList)+{1})*{1,1}))),COUNTIF(DList,"<"&DList)+{1},0))
算法思想：加入Mode.Mult&Min函数是考虑了频率相等时，取字符较小的，即升序，然后返回在字符串中的相对位置。
上述公式中，变化最为精彩的算是Countif()和Match()函数间的相互配合，下面来看看几种常见变形。
Countif(Range, Criteria):在特定区域(Range)内统计满足条件(Criteria)的单元格数量，返回的维度应与满足条件(Criteria)的单元格相等。
1，- =COUNTIF(DList,DList)
- {1;2;3;3;1;2;3;3;3;3;2;2}
说明表示：各字符在序列中出现的频率
2，- =COUNTIF(DList,">="&DList)
- 或者
- =COUNTIF(DList,"<"&DList)+1
- {12;8;6;11;3;2;6;11;11;6;8;2}
- 或者
- {1;5;7;2;10;11;7;2;2;7;5;11}
说明表示：各字符串间可以比较大小，不考虑字母大小写，即数字大小反映升序或是降序
3，- =COUNTIF(DList,E$1:E3)
- {0;1;3}
说明表示：统计已经出现的次数；返回看看定义，仿佛清晰了许多
4，- =COUNTIF(G$1:G3,DList)
- {0;0;1;1;0;0;1;1;1;1;0;0}
说明表示：对于可扩展的区域，是否出现相应的值，出现为1，没有则为0。可用于去重复值
Match(lookup_value;lookup_array; [match_type]):返回查询值在查询数组中的相对位置，缺省(,0)为精确匹配；返回值的维度应与查询值维度一样。
1，- =MATCH(DList,DList,0)
- {1;2;3;4;5;6;3;4;4;3;2;6}
说明表示：字符串在序列中第一次出现时的相对位置
2，- =MATCH(DList,E$1:E3,0)
- {#N/A;#N/A;2;3;#N/A;#N/A;2;3;3;2;#N/A;#N/A}
说明表示：查询字符串数组在序列中首次出现的相对位置，没有找到则报错，可用于去重复
3，- =MATCH(DList,DList,0)=ROW(DList)-1
- {1;1;1;1;1;1;0;0;0;0;0;0}
说明表示：序列中的值是否为重复值，第一次出现记为1，重复出现记为0
最后，看了这么多，总得有点自己的想法，结合Gvntw和Mike的算法得出- =INDEX(A:A,IF(ROW(1:1)>SUM(N(MATCH(DList,DList,0)=ROW(DList)-1)),4^8,MOD(LARGE(COUNTIF(DList,DList)*(MATCH(DList,DList,0)=ROW(DList)-1)*10^8+(COUNTIF(DList,">="&DList)-SUM(COUNTIF(DList,I$1:I1)))*10^5+ROW(DList),ROW(1:1)),10^5)))&""
对Chandoo先生的公式，稍微改动，- =IFERROR(INDEX(DList,MATCH(MIN(MODE.MULT(IF(COUNTIF(J$1:J1,DList)=0,(COUNTIF(DList,"<"&DList)+1)*{1,1}))),COUNTIF(DList,"<"&DList)+1,0)),"")
欢迎大家交流自己的公式想法。
附件：
 SUF.rar
|
