[开_135][已总结]数字变换练习题

tyxh0916

1. =TEXT(MOD(SMALL(($A2:$G2&COLUMN($A:$G)&TEXT({1;2;3;4}&$A2:$G2,"[<11]9999;[<44]00;9999"))-10,COLUMN(A1)),100),"[=89] ;00")

复制代码

lsj128568

直观粗放型
I2=IF(COUNTA($H2:H2)+1>COUNT($A2:$G2)*3+SUM(COUNTIF($A2:$G2,{1,2,3})),"",RIGHT(SMALL(CHOOSE(MATCH(SMALL(IF($A2:$G2="",9,$A2:$G2),ROW($1:$7)),{0,1,4}),100+{10,20,30,1000},200+{0,10,20,30},300+{0,10,20,1000})+SMALL(IF($A2:$G2="",9,$A2:$G2),ROW($1:$7))+ROW($1:$7)*100,COLUMN(A1)),2))
右拉下拉

ff8feng

{:soso_e141:}参与一下
I2：AJ2多单元格数组：
=TEXT(RIGHT(SMALL(TEXT((A2:G2<"")*(A2:G2+{0;10;20;30}),"[>33]99999;[=]99999")+A2:G2/1%%+COLUMN(A:G)/1%,COLUMN()-8),2),"[<34]00;")

稍作修改，还能减掉几个字符
=TEXT(RIGHT(SMALL(TEXT((A2:G2<"")*(A2:G2+{0;10;20;30}),"[>33]9E+5;[=]9E+5")+A2:G2/1%%+COLUMN(A:G)/1%,COLUMN()-8),2),"00;;")

再砍掉几个
=TEXT(RIGHT(SMALL(TEXT((A2:G2<"")*({0;1;2;3}&A2:G2),"[>33]9E+5;[=]9E+5")+A2:G2/1%%+COLUMN(A:G)/1%,COLUMN()-8),2),"00;;")

衣袂飘飘

=RIGHT(TEXT(SMALL(A2:G2+IF(A2:G2="",9^9,IF(A2:G2,IF(A2:G2<4,ROW($1:$4)-1,{0;1;2;100000}),{1;2;3;100000}))*10+(COLUMN(A:G)^0*A2:G2)*10000+COLUMN(A:G)*1000+ROW($1:$4)*100,COLUMN(A:AB)),"[<800000]#;"),2)
多单元格数组公式，嘿嘿

xwscs

1. =TEXT(MOD(SMALL(TEXT((ROW($1:$4)-1&0)+IF(A2:G2="",30,A2:G2),"[=0]99999;[>33]9099;0")+A2:G2/1%%+COLUMN(A:G)/1%,COLUMN()-8),100),"[<34]00;")

复制代码

1. =TEXT(MOD(SMALL(TEXT(A2:G2,"[>3]"&{0;10;20;19999}&";[=0]"&{10;20;30;19999}&";"&{0;10;20;30}&";!1!9!9!9!9")+$A2:$G2*1000+COLUMN($A2:$G2)*100,COLUMN(A:AB)),100),"[<40]00;")

复制代码

xwscs

xwscs 发表于 2012-9-19 15:06

因为楼主嫌我的考拉太难看，所以赶紧换了，哈哈。

5blessyou

想不出啥好思路，等开贴看大家的

1. =TEXT(MOD(LARGE(-(A2:G2&COLUMN(A:G)&TEXT({5;6;7;8}&A2:G2,"[<51]e;[<84];e")),COLUMN()-8),-50),";00")

复制代码

PS:单行多单元下拉

hjj0451

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
评分建议及说明：
1：跟贴多个公式、多个楼层的按最优公式评分，不重复评分。
2：按照题目要求，答正确者即可获得30财富，最佳答案加1技术。以上评分建议是按照题目说明来进行的。
3：考虑到答题情况及题目思路实际，版主可考虑给10楼delete007由50财富改为加1技术分，不知是否妥当。
4：刚好2个思路并列最佳，恭喜头版和我祝福你同学，2人都是99字符，头版的思路可写为93字符：

1. =MID(SMALL((A2:G2&COLUMN(A:G)+ROW($1:$33)%)%^(A2:G2&""=RIGHT(ROW($1:$33))),COLUMN()-8)&0,5,2)

复制代码

对评分建议有意见的请跟贴。

hjj0451

思路解析：
本题主要有2种方法:
－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－                       
法一：间接相加判断剔除法
　　如除7、10楼外的其他各楼层。   
－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－                     
　　0-9都加上{0;10;20;30}，再用逻辑判断或TEXT剔除不需要的(0+0及{4,5,9}+30)，用SMALL取出。下面主要分析作为SMALL第1参数的部分，别的比较简单。在此基础上的A2:G2为第一权重，列号为第2权重，以按原数据大小顺序依次取出，这里不分析，属于基础知识。               
　　A2:G2都加上{0;10;20;30}，得到4行7列的数组，不许要的0+0只能出现在数组第一行，{4,5,9}+30只能出现在数组第4行，判断剔除。               
　　以原数据第1行为例，用公式=IF(A2:G2="",999,IF((A2:G2+{0;1;1;1})*((A2:G2<4)+{1;1;1;0}),A2:G2*101+{0;10;20;30},999))得到下面的4行7列数组，各列对应+{0;10;20;30}，一目了然：               
　　                         
　　其中最后一列是空单元格容错，其余对应原始数据的0(G40)、4(E43、H43)、5(D43)、9(C43)、2楼CHOOSE({1;2;3;4},A2:G2=0,,,A2:G2>3)+(A2:G2=""))只是上面的变形：               
　　
　　
　　4楼的TEXT({1;2;3;4}&$A2:$G2,"[<11]9999;[<44]00;9999")：               
　　
　　
　　5楼的=A2:G2*0.10001+COLUMN(A:G)%+(MOD({1;2;3;4}&A2:G2,44)<11)+{0;1;2;3}%%：
　　               
　　MOD({1;2;3;4}&A2:G2,44)<11比较妙，一下就排除了所有不需要的信息。        
　　
　　13楼的TEXT((A2:G2<"")*(A2:G2+{0;10;20;30}),"[>33]9E+5;[=]9E+5")：               
　　         
　　
　　17楼的TEXT({5;6;7;8}&A2:G2,"[<51]e;[<84];e")：               
　　 　
　　利用的是日期序列5、6、7、8、50、84、85、86、87、88、89的年份(1900)。               
　　关于MOD(LARGE(-(A2:G2&COLUMN(A:G)&TEXT({5;6;7;8}&A2:G2,"[<51]e;[<84];e")),COLUMN()-8),-50)：               
　　举例，假设一个负的四位数为-19ab，相当于-1900-ab,-1900必能被-50整除，MOD为0。Mod(-ab,-50)=mod(100-ab,50)-50。TEXT赋值为1900的，如-51900、-461900的可被-50整除，MOD为0，TEXT设置显示为空。
               
－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－                       
法二：直接列举剔除法：
　　如7楼、10楼，利用ROW(1:33)，不容易看出来，容易受思维习惯影响而采用法一。     
－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－              
　　以7楼头版的公式为例解析一下：        
　　首先看看ROW(1:33)的数字规律：这33个数可分解为，1-3加了{0;10;20;30}，0加了{10;20;30}，4-9加了{0;10;20}。与题目要求取的数量(为0则加10、20、30，1-3时则分别加0、10、20、30；4-9则分别加0、10、20)完全一致，利用它直接就达到效果。这是思路核心。        
　　 　　
　　先看看第一个公式的核心部分，=IF(RIGHT(ROW($1:$33))-10*(A2:G2="")-A2:G2,1,A2:G2%+COLUMN(A:G)%%+ROW($1:$33)%%%)        
　　这是1个33行7列的由1和按权重组合成的必要信息组成的数组，不满足需要的返回1，后面的步骤MID取出"",满足条件的返回A2:G2%+COLUMN(A:G)%%+ROW($1:$33)%%%，赋予权重用MID取数。  
　　
　　分析下IF条件：RIGHT(ROW($1:$33))-10*(A2:G2="")-A2:G2：      
　　当A2:G2=""，IF条件判断结果<0,返回1;        
　　当A2:G2=0，如上玫瑰红色对应的部分(也就是row(1:33)的10、20、30)才为0，否则返回1;        
　　当A2:G2=1-3,如上黄色对应的部分(也就是row(1:33)的1-3、11-13、21-23、31-33)才为0，否则返回1;        
　　当A2:G2=4-9亦然，只有A2:G2与ROW(1:33)的尾数相同时，我们才需要结果，否则返回1。        
　　
　　10\20\30计算中丢掉了末尾的0,最后补0,=MID(SMALL(上面的结果,COLUMN()-8)&0,7,2)
    　   
　　条件进一步简化为RIGHT(ROW($1:$33))+10-(1&A2:G2)，A2:G2为空返回正数，其余与上面相同，得到第2个公式。
　　上面的公式结构为if(a-b,1,y),可把其简化为y^(a=b),同时把y提取了一个%到外面，%计算优先顺序先于^，(A2:G2+COLUMN(A:G)%+ROW($1:$33)%%)%^((1&A2:G2)-RIGHT(ROW($1:$33))=10)，得到第3个公式。
　　公式还可以缩短6字符到93字符，SMALL第一参数写为(A2:G2&COLUMN(A:G)+ROW($1:$33)%)%^(A2:G2&""=RIGHT(ROW($1:$33)))，如果A2:G2是文本格式，&""还可以去掉，90字符。        
　　10楼公式原理基本相同，不同在加权取数上。

－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－
以上就是对本题两种思路的简要分析。
此题属于技巧性题目，不象上一题有个值得借鉴和推广使用的方法论。利用ROW(1:33)这个思路不容易想到，人常常受制于思维定式，容易被提问的方式和字眼迷惑，顺着提问方式走，这时来个脑筋急转弯就显出智慧。7楼和17楼解决问题的过程中体现的小技巧值得学习，都是函数达人。
如还有完全不同的思路可跟贴 。