楼主
Zaezhong |
根据采编素材原帖的附件做了一些简单的修改：
数据源为A1:F5，该区域要么为空白要么就是1~10之间的数字，同时保证1~10每一个数最少出现一次
要求：
按照数据出现的次数进行格式化，出现次数多得排在前面，如果数据出现的次数相同就按照小小后大的顺序连接起来
要求没有辅组列和定义的名称，2003版需要通过，字符长度限制在160（包括等号）以内
得分：正确答案5技能分，每少8字符加1魅力
答案用代码框贴出，公式标明字符数目
答案汇总在10楼~~
 格式化频率排列数.rar
|
2楼
wcymiss |
- =SUBSTITUTE(TEXT(SUM(TEXT(MOD(SMALL(ROW(1:10)-COUNTIF(A1:F5,ROW(1:10))*100,ROW(1:10)),100),"[>9]!0")*10^(10-ROW(1:10))),REPT("!00",10)),"00","10")
|
3楼
sharprain |
数组公式 |
4楼
fangjianp |
公式长度160- =SUBSTITUTE(TEXT(SUBSTITUTE(SUM((9-RIGHT(SMALL(FREQUENCY(A1:F5,ROW(1:9))/1%+10-ROW(1:10),ROW(1:10))))*10^ROW(2:11))%,9,0&9)+REPT(1,11),REPT("\00",11)),0&200,10)
简化，长度：156- =SUBSTITUTE(TEXT(SUBSTITUTE(SUM((9-RIGHT(SMALL(COUNTIF(A1:F5,ROW(1:10))/1%-ROW(1:10),ROW(1:10))))*10^ROW(2:11))%,9,0&9)+REPT(1,11),REPT("\00",11)),0&200,10)
找不到其他方法。
再简化，公式长度：120- =SUBSTITUTE(TEXT(SUM(MOD(LARGE(ROW(1:10)%-COUNTIF(A1:F5,ROW(1:10)),ROW(1:10)),0.1)*10^ROW(2:11)),REPT("\00",10)),0&0,10)
以上公式长度都包括“=” |
5楼
wangg913 |
- =SUBSTITUTE(TEXT(SUM(MOD(LARGE(ROW(1:10)-COUNTIF(A1:F5,ROW(1:10))/1%,ROW(1:10)),10)*10^ROW(2:11))%,REPT("\00",10)),0&0,10)
|
6楼
bluexuemei |
- =TEXT(SUM((10-RIGHT(LARGE(COUNTIF(A1:F5,ROW(1:10))*100+10-ROW(1:10),ROW(1:10))))*10^({10;9;7;6;5;4;3;2;1;0})),"!00!0000"&REPT("!00",7))
数组公式 |
7楼
落雪绽菊 |
c9{=IF(--C9,C9,"")&IF(LEN(C9)=20,"",INDEX(TEXT(11-RIGHT(LARGE(--(COUNTIF(A1:F5,ROW(1:10))&TEXT(11-ROW(1:10),"00")),ROW(1:10)),2),"00"),-INT((LEN(C9)+1)/-2)))}，154字。
研究了好多天，没有头绪，最后只好用迭代，没有完全满足题目要求，迭代运算对字符操作支持得不是很好，只好用手按住F9。很希望能看看大侠们是如何做到的。 |
8楼
angellbxsc |
=SUBSTITUTE(TEXT(SUM(MOD(MOD(SMALL(ROW(1:10)-FREQUENCY(A1:F5,ROW(1:9))/1%,ROW(1:10)),10),10)*10^(10-ROW(1:10))),REPT("!00",10)),"00",10)
共136字符，经测试没发现问题。可获+3的魅力吧，呵呵。
为了精简答案可是花了不少心血。也长了点知识。
=SUBSTITUTE(TEXT(SUM(MOD(MOD(SMALL(ROW(1:10)-FREQUENCY(A:F,ROW(1:9))/1%,ROW(1:10)),10),10)*10^(10-ROW(1:10))),REPT("!00",10)),"00",10)
此公式少了行列标记，可少两个字符，134，只是如果区域下面有文本，就出现错误了。因此取巧了。思路并没有变化，没什么意义了。 |
9楼
Violet_Universe |
- =SUBSTITUTE(TEXT(SUM(RIGHT(LARGE((COUNTIF(A1:F5,ROW(1:10))/1%-ROW(1:10))/1%+ROW(1:10),ROW(1:10)))*10^(10-ROW(1:10))),REPT("\00",10)),"00",10)
公式包括等号141字符
下面这个131字符
- =SUBSTITUTE(TEXT(SUM(RIGHT(SMALL(10^(10-COUNTIF(A1:F5,ROW(1:10)))+ROW(1:10),ROW(1:10)))*10^(10-ROW(1:10))),REPT("\00",10)),"00",10)
|
10楼
Zaezhong |
本次的题目公式的结构基本是相同的，大多都是SUBSTITUTE(TEXT(构建部分,REPT),0&0,10)，构建部分也基本是COUNTIF乘以一个因子加减一个小部分将1~10这10个数字进行排序，公式的主题虽然相同，但是在构建部分还是略有不同。
以2楼吴姐的答案为例，答案如下- =SUBSTITUTE(TEXT(SUM(TEXT(MOD(SMALL(ROW(1:10)-COUNTIF(A1:F5,ROW(1:10))*100,ROW(1:10)),100),"[>9]!0")*10^(10-ROW(1:10))),REPT("!00",10)),"00","10")
其中的ROW(1:10)-COUNTIF(A1:F5,ROW(1:10))*100部分就是将1~10这10个数进行排列的主要部分，下面这样或许更好理解一些：-(COUNTIF(A1:F5,ROW(1:10))*100-ROW(1:10))，通过前面添加负号后用mod函数可以直接将1~10这10个数排列好顺序结果如下：{1;6;10;3;4;2;5;7;8;9}由于两位数在后面的相乘再相加过程中会出现进位的情况，所以先用Text函数将这个10转换为0，这样求了和以后用Text格式化后就会在组合而成的字符串中出现3个0，sum的结果中的0其实是10的变身，如果10应该排在最前面，那么sum结果就没有0，上面说的的3个0，前面两个其实应该是10，这样就有了后面的substitute替换的步骤。其余的大同小异。
第二种思路就是fangjianp用Frequency的方法方法如下- =SUBSTITUTE(TEXT(SUBSTITUTE(SUM((9-RIGHT(SMALL(FREQUENCY(A1:F5,ROW(1:9))/1%+10-ROW(1:10),ROW(1:10))))*10^ROW(2:11))%,9,0&9)+REPT(1,11),REPT("\00",11)),0&200,10)
用Frequency计算每一个数字出现的次数后用FREQUENCY(A1:F5,ROW(1:9))/1%+10-ROW(1:10)作为small的第一参数，将10个数字排序，目的与前面相同，只是这时排的是倒序，还有一个不同之处是该方法将每一个数都缩小一号，即1替换为0，2替换为1，依次类推，这样就不会出现10这个两位数了，substitute部分将原来的9替换为09的目的是防止后面将每一个数字加1还原的时候进位导致的错误，09加上1刚好为10，后面的rept(1,11),这里用11次是为了防止10出现的次数最多时第一位为0丢失，用11可以保护前面的0，后面的0200中的2来源是9的进位(9+1)和1加上1的和
第二中方法有一个缺点，那就是如果数据区域的数据是文本数字那么结果就会错误，不过这个不在本题的考察范围内！
附上答案汇总和正确与否判断，需要考虑10次数最多的情况:
欢迎大家指正~~
格式化频率排列数(汇总).rar
|