根据数字出现频率大小从左到右排列，相同频率的数字则按从左到右的顺序取数排列

拥梦者

按数字频率排序.rar (20.18 KB, 下载次数: 11)

2015-9-9 18:04 上传

点击文件名下载附件

问：有一系列多位数，请根据数字出现频率大小从左到右排列，相同频率的数字则按从左到右的顺序取数排列。如：65950141 ，则显示为516904，如果只用函数在一个单元格实现应该怎样写函数公式？

答：

D列数组公式为=LEFT(TEXT(SUM((0&MID(A2,29-DAY(LARGE(--((LEN(A2)-LEN(SUBSTITUTE(A2,ROW($1:$10)-1,""))+1)&"-"&(29-FIND(ROW($1:$10)-1,A2&"0123456789"))),ROW($1:$10))),1))*10^(10-ROW($1:$10))),REPT("0",10)),SUM(--(LEN(A2)-LEN(SUBSTITUTE(A2,ROW($1:$10)-1,""))>0)))，向下填充即可。

如果需要带频数显示则可以用E列数组公式=LEFT(TEXT(SUM((0&MID(A2,29-DAY(LARGE(--((LEN(A2)-LEN(SUBSTITUTE(A2,ROW($1:$10)-1,""))+1)&"-"&(29-FIND(ROW($1:$10)-1,A2&"0123456789"))),ROW($1:$10))),1))*10^(10-ROW($1:$10))),TEXT(SUM(HOUR(LARGE(--((LEN(A2)-LEN(SUBSTITUTE(A2,ROW($1:$10)-1,""))+1)&"-"&(29-FIND(ROW($1:$10)-1,A2&"0123456789"))&""&LEN(A2)-LEN(SUBSTITUTE(A2,ROW($1:$10)-1,""))&":0:0"),ROW($1:$10)))*10^(10-ROW($1:$10))),REPT("!0!!![!!0!!!]",10))),4*SUM(--(LEN(A2)-LEN(SUBSTITUTE(A2,ROW($1:$10)-1,""))>0)))，向下填充即可。

下面，就对两个公式作简要解释：

一、筛重复同频率顺次排列。

=LEFT(TEXT(SUM((-0&MID(A2,29-DAY(LARGE(--((LEN(A2)-LEN(SUBSTITUTE(A2,ROW($1:$10)-1,""))+1)&"-"&(29-FIND(ROW($1:$10)-1,A2&"0123456789"))),ROW($1:$10))),1))*10^(10-ROW($1:$10))),REPT("0",10)),SUM(--(LEN(A2)-LEN(SUBSTITUTE(A2,ROW($1:$10)-1,""))>0)))

（一）以上是数组公式，先分析一下解题思路。由题知应找出0-9在原数据中分别出现的次数，然后对频率从大到小排序，再将对应的0-9显示在对应位置，对于出现次数相同的数字依原数据出现顺序从左到右优先的原则显示。问题的难点有两个：一是频率数值和0-9要对应关联，否则排序后无法找到对应的数字；二是“顺次”。即相同频率的数值，左侧的依然排左。

（二）公式解读。

1.LEN(A2)-LEN(SUBSTITUTE(A2,ROW($1:$10)-1,""))+1是计算原数据中0-9出现的频率，“+1”是为了频率数组中不出现“0”，也是为了将这部分当作月份数据解决难点一做出的铺垫。对于len和substitute函数就不多解释了。

2.29-FIND(ROW($1:$10)-1,A2&"0123456789")是计算0-9在原数据中先后出现的位置，&"0123456789"是为了避免Find函数在原数据没有某些数字时出现错误值以影响计算的正常进行。“29-”的目的是将先后出现顺序的数组变为先出现的大，后出现的小，为了解决是排序中关联后难点二，同时也是为关联做准备，将这些位置化为小于29的日期数值，因为平年2月仅有28天，“2-29”的平年会报错。

3.--((LEN(A2)-LEN(SUBSTITUTE(A2,ROW($1:$10)-1,""))+1)&"-"&(29-FIND(ROW($1:$10)-1,A2&"0123456789"))),ROW($1:$10)是解决问题一和二的关键，将频率和0-9先后出现的位置关联起来，连接成日期数值，即“月-日”，年份自动补充为当年，“--”的作用是将文本型日期转为系统能识别的数值型日期，便于接下来的排序。

4.LARGE(--((LEN(A2)-LEN(SUBSTITUTE(A2,ROW($1:$10)-1,""))+1)&"-"&(29-FIND(ROW($1:$10)-1,A2&"0123456789"))),ROW($1:$10))是将第3步中的数值序列按从大到小的顺序依次排列，其中ROW($1:$10)是产生1-10的数组。关于日期大小的问题如有不清查的可查阅Excel帮助文档。

5.DAY(LARGE(--((LEN(A2)-LEN(SUBSTITUTE(A2,ROW($1:$10)-1,""))+1)&"-"&(29-FIND(ROW($1:$10)-1,A2&"0123456789"))),ROW($1:$10)))中是将第4步按频率从大到小排序了的日期数组取值，即取出“年-月-日”中的“日”作为数组，这是一个和0-9在原数据中出现位置相关联的数据。至于为何用day函数请大家回去认真阅读第2、3步。

6.29-DAY(LARGE(--((LEN(A2)-LEN(SUBSTITUTE(A2,ROW($1:$10)-1,""))+1)&"-"&(29-FIND(ROW($1:$10)-1,A2&"0123456789"))),ROW($1:$10)))即将第5步的数组变为0-9在原数据中出现位置的数组。为什么用29-只是为了还原第2步的操作。如果大家认为这减过去减过来很多余，可以实测一下。

7.0&MID(A2,29-DAY(LARGE(--((LEN(A2)-LEN(SUBSTITUTE(A2,ROW($1:$10)-1,""))+1)&"-"&(29-FIND(ROW($1:$10)-1,A2&"0123456789"))),ROW($1:$10))),1)是根据第6步的位置数组用mid函数取出原数据中0-9的数字，0&的作用是避免数组空值在下一步运算出现，将空值转为了“0”。

8.10^(10-ROW($1:$10))是为了产生{1000000000;100000000;10000000;1000000;100000;10000;1000;100;10;1}数组，便于在下一步将数值数组转换为一串数字。

9.SUM((0&MID(A2,29-DAY(LARGE(--((LEN(A2)-LEN(SUBSTITUTE(A2,ROW($1:$10)-1,""))+1)&"-"&(29-FIND(ROW($1:$10)-1,A2&"0123456789"))),ROW($1:$10))),1))*10^(10-ROW($1:$10)))是为了将第7步的数组组合成一串数值。这个不多说，不清楚的可以脑补一下。

10.TEXT(SUM((0&MID(A2,29-DAY(LARGE(--((LEN(A2)-LEN(SUBSTITUTE(A2,ROW($1:$10)-1,""))+1)&"-"&(29-FIND(ROW($1:$10)-1,A2&"0123456789"))),ROW($1:$10))),1))*10^(10-ROW($1:$10))),REPT("0",10))是利用text函数将数值转为文本型，主要是避免原数据中0的出现最高，排序最左边，数值时会自动省去的问题，为最后一步截取数据做准备。REPT("0",10)是产生“0000000000“。

11.SUM(--(LEN(A2)-LEN(SUBSTITUTE(A2,ROW($1:$10)-1,""))>0))是计算原数据中0-9中有几个数字出现过，这个不多解释。

12.LEFT(TEXT(SUM((0&MID(A2,29-DAY(LARGE(--((LEN(A2)-LEN(SUBSTITUTE(A2,ROW($1:$10)-1,""))+1)&"-"&(29-FIND(ROW($1:$10)-1,A2&"0123456789"))),ROW($1:$10))),1))*10^(10-ROW($1:$10))),REPT("0",10)),SUM(--(LEN(A2)-LEN(SUBSTITUTE(A2,ROW($1:$10)-1,""))>0)))即是用left函数将第10步的结果截取第11步产生的位数，这就是最后结果。

二、带频数显示的顺序排列。

=LEFT(TEXT(SUM((0&MID(A2,29-DAY(LARGE(--((LEN(A2)-LEN(SUBSTITUTE(A2,ROW($1:$10)-1,""))+1)&"-"&(29-FIND(ROW($1:$10)-1,A2&"0123456789"))),ROW($1:$10))),1))*10^(10-ROW($1:$10))),TEXT(SUM((MONTH(LARGE(--((LEN(A2)-LEN(SUBSTITUTE(A2,ROW($1:$10)-1,""))+1)&"-"&(29-FIND(ROW($1:$10)-1,A2&"0123456789"))),ROW($1:$10)))-1)*10^(10-ROW($1:$10))),REPT("!0!!![!!0!!!]",10))),4*SUM(--(LEN(A2)-LEN(SUBSTITUTE(A2,ROW($1:$10)-1,""))>0)))

（一）以上是数组公式，也先分析一下解题思路。可考虑在上面第10步的格式中做文章，也就是将REPT("0",10)是产生“0000000000“替换为"0![!4!]0![!2!]0![!2!]0![!2!]0![!1!]0![!1!]0![!1!]0![!0!]0![!0!]0![!0!]"的样式，方括号中应为频数，做好这部分替换进去就大功告成了。问题的难点三是每个原数据中频数不固定，需要计算出来，并且关联数字动态排序。由上述第1、3、4步可得知，经排序的日期数组中月份即为频数+1，可用month函数将月份取出-1就得到频数数组，再格式化即可。

（二）公式解读。

1. TEXT(SUM((MONTH(LARGE(--((LEN(A2)-LEN(SUBSTITUTE(A2,ROW($1:$10)-1,""))+1)&"-"&(29-FIND(ROW($1:$10)-1,A2&"0123456789"))),ROW($1:$10)))-1)*10^(10-ROW($1:$10))),REPT("!0!!![!!0!!!]",10)))中其余的前面已讲过，不赘述。不同的只是将day函数换为了month函数，目的是取出月份，再减1即为频数数组。REPT("!0!!![!!0!!!]",10)是生成

text函数的格式要求，即为"!0!!![!!0!!!]!0!!![!!0!!!]!0!!![!!0!!!]!0!!![!!0!!!]!0!!![!!0!!!]!0!!![!!0!!!]!0!!![!!0!!!]!0!!![!!0!!!]!0!!![!!0!!!]!0!!![!!0!!!]"。

2. 4*SUM(--(LEN(A2)-LEN(SUBSTITUTE(A2,ROW($1:$10)-1,""))>0))中的4*是取值的要求，自己测试看看吧。
有兴趣的小伙伴看看吧！