[开_140] 区域转换练习题

titi012

=============================================================
区域转换：
数据源 A2:E11 可能为整数数字或空，数值在1-99之间。 区域大小不变，数据可变；
将数据源转换成 A15：E43(绿色区域)的形式
=============================================================
转换要求:
1，蓝色区一行内有两个或以上数时，转换两行或多行，一行一个数，位置与原位置对应；
2，蓝色区没有数时，不转换；
3，蓝色区的数值大于10时，按10折分，转换多行；
    如15，转换成10，5两行; 23则换成10，10，3三行;45则转换成10，10，10，10，5五行.
4，不用VBA、不要辅助列、不用自定义名称、2003版验证通过；
    A15输入公式右拉下拉、或 A15:E15输入公式下拉、或 A15:E43 多单元格数组公式皆可。
    公式需有容错处理。
5，题目截止 2012-12-30 12:00 。
=============================================================
建议评分规则：
暂不确定，待开贴时情况汇总。

delete_007

1. =IF(SUM(A$14:E14)<SUM(A$2:E$11),IF(COLUMN()=5-MOD(MATCH(-1%-SUM(A$14:E14),SUBTOTAL(9,OFFSET(F$12,-ROW($1:$50)/5,,,-MOD(ROW($5:$54),5)-1))-SUBTOTAL(9,OFFSET(A$2,,,11-ROW($1:$50)/5,5)))-1,5),-TEXT(SUM(A$14:E14)+LOOKUP(-1%-SUM(A$14:E14),SUBTOTAL(9,OFFSET(F$12,-ROW($1:$50)/5,,,-MOD(ROW($5:$54),5)-1))-SUBTOTAL(9,OFFSET(A$2,,,11-ROW($1:$50)/5,5))),"[>-10];-1!0"),""),"")

复制代码

A15:E15数组公式。
先给个长的公式，有空再来简化。
区域转换(delete_007).zip (14.33 KB, 下载次数: 35)

2012-12-3 17:08 上传

点击文件名下载附件

简化一下，A15数组公式：

1. =TEXT((1-COUNT(-T(OFFSET(A15,,-{1,2,3,4}))))*SUM(A$2:A$11*(SUM(--TEXT($A$14:$E14,"0;;;!0"))>=SUMIF(OFFSET($A$1,,,ROW($1:$10),5),"<>")),-TEXT(A$14:A14,"0;;;!0")),"[>9]1!0;[>];")

复制代码

swyrlbq

先上个出结果的，慢慢优化。

充分考虑极端情况，源数据区全为空，源数据区全为99，如果源数据区全为99，那么就要占据500行。公式为区域数组公式；
A15:E514=IF(MOD(SMALL(IF(MOD(SMALL((ROW(2:11)&COLUMN()/1%)+A2:E11,ROW(1:50)),100)-(COLUMN(A:J)-1)*10>0,ROW(1:50)*10+MOD(ROW(5:54),5)+1,999),ROW(1:500)),10)=COLUMN(),MOD(SMALL(ABS(TEXT(MOD(SMALL((ROW(2:11)&COLUMN()/1%)+A2:E11,ROW(1:50)),100)-(COLUMN(A:J)-1)*10,"[>10]1!0;[<=]"&7^9))+ROW(1:50)/1%%+COLUMN(A:J)/1%,ROW(1:500)),100),"")


改换一下思路，化整为零，动态引用各列，公式变为下拉式，不受行数量限制。285字符。
=IF(MOD(SMALL(IF(N(OFFSET($A$1,INT(ROW($5:$54)/5),MOD(ROW($5:$54),5)))+10>COLUMN($A:$J)*10,ROW($1:$50)*10+MOD(ROW($5:$54),5)+1,999),ROW(1:1)),10)=COLUMN(),MOD(SMALL(ROW($1:$10)/1%%+COLUMN($A:$J)/1%+ABS(TEXT(A$2:A$11-COLUMN($A:$J)*10+10,"[>10]1!0;[<=]"&9^9)),COUNT(A$14:A14)+1),100),"")

lishengping666

20121201区域转换.zip (21.74 KB, 下载次数: 22)

2012-12-6 11:04 上传

点击文件名下载附件

20121201区域转换_情景测试.zip (52.25 KB, 下载次数: 12)

2012-12-6 11:27 上传

点击文件名下载附件

公式如附件。
方法比较笨，不过测试了几组情景可以通过验证。
说明：未模拟所有的情景，不过此方法思路是可行的，关键是控制行列值构成的内存数组的设置。
主要思路如下：
1、将待转换的区域数据，数值大于10的按10拆分；
2、通过由行、列值放大一定倍数组成的内存数组，将上步拆发后的数据用large排序（达到的目标是：行数越靠前、列数越靠前的数据放大后越大）
3、用Index函数将上步转换后得到的一维数组按要求读到对应的单元格

E贝贝

1. =TEXT((0=COUNT(-T(OFFSET($A15,,COLUMN($A:A)-2))))*(SUM(OFFSET(A$2,,,MATCH(ROW()-15,MMULT(N(ROW($1:$10)>COLUMN($A:$J)),MMULT(INT(($A$2:$E$11-1)/10)+1,{1;1;1;1;1})))))-SUM(--IF(A$14:A14<"A",0&A$14:A14))),"[>10]!1!0;[>0]0;")

复制代码

1. =TEXT((1-COUNT(-T(OFFSET(A15,,-{1,2,3,4}))))*SUM($A$2:A$12*(SUM(--TEXT($A$14:$E14,"0;;;!0"))>=SUBTOTAL(9,OFFSET($A$1,,,ROW($1:$11),5))),-TEXT($A$14:A14,"0;;;!0")),"[>9]1!0;[>]0;")

复制代码

CLL1998

不用VBA,可以，但“不要辅助列、不用自定义名称”却似乎太难了。
绞尽脑汁，给一个用了辅助列及自定义名称的。
期待高手



Book121.rar (8.81 KB, 下载次数: 18)

2012-12-25 11:53 上传

点击文件名下载附件

w83365040371

1. =TEXT(MID(LARGE(TEXT((N(OFFSET($A$1,ROW($5:$54)/5,MOD(ROW($5:$54),5)))-COLUMN($A:$J)*10+10)%/100^MOD(ROW($5:$54),5),"[>];-100")+90-ROW($5:$54),ROW(A1))&0,2*COLUMN(B1),2),"[>9]1!0;[>];")

复制代码

delete_007

受wangg913版主委托，由我来做这个题目的总结。
评分建议如下：

(开-140)评分建议及总结.zip (924.43 KB, 下载次数: 83)

2013-1-13 10:21 上传

点击文件名下载附件

如有任何疑问或问题，请及时联系我或者wangg913版主。

titi012

delete_007 发表于 2013-1-11 17:40
受wangg913版主委托，由我来做这个题目的总结。
评分建议如下：

辛苦了,这段时间忙,很久没空上来.

awangtaotao

不会啊，不会啊