楼主
lrlxxqxa |
利用函数实现将左侧源数据变换为黄色区域效果。D1输入公式下拉填充；
回复正文中给出公式即可，不必上传附件。
答题截止至2011年12月24日18:00，正确+5魅力，好思路额外+技能分：）
 如何变换数据结构.rar
|
2楼
wcymiss |
貌似荣版有过类似的题呀！- =INDIRECT(TEXT(REPLACE(SMALL((ROW($2:$12)+(B$1:B$11=B$2:B$12)*{20,0}&{12,21})%%,ROW()),5,1,)*1000,"r0c0"),)&""
再写个92字符的，有限制，公式只能在D1输入：- =INDIRECT(TEXT(SMALL(--((B$1:B$11=B$2:B$12)*{9,0}&ROW($2:$12)&{2,3}),ROW()),"r0c![-0]"),)&""
|
3楼
piny |
方法一 139字元- =INDIRECT(TEXT(SUBSTITUTE(SUBSTITUTE(SMALL(999^(B$1:B$11=B$2:B$12)*{0,1}+ROW($2:$12)/1%+{4,2},ROW(A1)),"04","01"),"03","02"),"!r0c00"),)&""
方法二 133字元- =INDIRECT(TEXT(SUBSTITUTE(SUBSTITUTE(SMALL(999^(B$1:B$11=B$2:B$12)*{0,1}+ROW($2:$12)/1%+{4,2},ROW()),0&4,0&1),0&3,0&2),"!r0c00"),)&""
方法三 127字元- =INDIRECT(TEXT(RIGHT(SMALL((ROW($2:$12)+{1,0})/1%%+ROW($2:$12)/1%+{1,2}+(B$1:B$11=B$2:B$12)*{0,111197},ROW()),4),"!R0C00"),)&""
數組 下拉 |
4楼
天南地北 |
先回一个吧！公式比较长，答案在公交车上想得- =INDIRECT(TEXT(SUBSTITUTE(SMALL(ROW($2:$12)/1%+IF({1,0},31,IF(MATCH(B$2:B$12,B$2:B$12,)=ROW($1:$11),2,2599)),ROW()),31,"01"),"r0c00"),)
优化一下- =INDIRECT(SUBSTITUTE(TEXT(SMALL(ROW($2:$12)/1%+IF({1,0},11,IF(MATCH(B$2:B$12,B$2:B$12,)=ROW($1:$11),2,2599)),ROW()),"r0c00"),"c1","c0"),)&""
继续优化吧
- =INDIRECT(REPLACE(TEXT(SMALL(ROW($2:$12)/1%+IF({1,0},11,IF(B$2:B$12=B$1:B$11,9^4,2)),ROW()),"r00c00"),5,1,0),)&""
再优化
- =INDIRECT(REPLACE(TEXT(SMALL(IF(A$2:B$12=A$1:B$11,9^4,ROW($2:$12)/1%)+{11,2},ROW()),"r00c00"),5,1,0),)&""
本人认为此题目有两个难点，先第二列，再第一列；从第一行输入公式（难以引用上面生成的结果）
|
5楼
xgg2001 |
124字符- =INDIRECT(TEXT(SUBSTITUTE(SMALL(IF({1,0},ROW($2:$12)*10+1,IF(B$1:B$11<>B$2:B$12,ROW($2:$12)*10+0.1)),ROW()),0.1,2),"r0c0"),)
如果A列没有连续重复的姓名，简化成101- =INDIRECT(TEXT(SUBSTITUTE(SMALL(IF(A$1:B$11<>A$2:B$12,ROW($2:$12)*10+{1,0.1}),ROW()),0.1,2),"r0c0"),)
|
6楼
蜜语 |
我不会，来看看。
 |
7楼
wjc2090742 |
数组公式：- =INDIRECT("r"&SUBSTITUTE(SMALL(ROW($2:$12)+(MATCH(B$2:B$12,B$2:B$12,)<>ROW($1:$11))*{99,0}+{2,10}%,ROW()),".","c"),)&""
本题有3个地方可以取巧。1是取B列排序，用B$2:B$12=B$1:B$11替代match<>row部分；2是取A列不存在相邻行重复，在取巧1基础上简化4字符数；3是取公式在D列书写，可以根据公式书写位置调整参数，且与数据源无关。综合，数组公式，D1输入下拉：- =INDIRECT(TEXT(SMALL((ROW($2:$12)&(A$2:B$12=A$1:B$11)*99)+{3,2},ROW()),"r0c![-0]"),)&""
|
8楼
bluexuemei |
先抛个大砖头,- =INDIRECT(TEXT(RIGHT(SMALL((B$1:B$11=B$2:B$12)*{0,99870}+ROW($2:$12)*1010+{1001,2},ROW(A1)),3),"r0c0"),)&""
|
9楼
一嘟噜钥匙 |
- =INDIRECT(TEXT(RIGHT(SMALL(((MATCH(B$2:B$12,B$2:B$12,)-{0,0.1})*10^6+ROW($2:$12)*10+{1,2})*(TEXT(N(MATCH(B$2:B$12,B$2:B$12,)=ROW($1:$11)),"0;;99999999")*{0,1}+{1,0}),ROW()),5),"r0c0"),)&""
|
10楼
w83365040371 |
D1- =INDIRECT(TEXT(SMALL(IF(IF({1,0},1,MATCH(B$2:B$12,B:B,)=ROW($2:$12)),ROW($2:$12)/1%+{3,2},10^4),ROW()),"R#C![-00!]"),0)&""
|
11楼
sylzldd |
=INDIRECT(TEXT(REPLACE(SMALL(IF({1,0},ROW($2:$12)*10100+1,IF($B$1:$B$11=$B$2:$B$12,999999,ROW($2:$12)*10000+2)),ROW(1:1)),3,2,""),"r0c00"),)&"" |
12楼
鬼狐 |
=INDIRECT(TEXT(MOD(SMALL(IF({1,0},IF(MATCH(B$2:B$12,B:B,)=ROW($2:$12),ROW($2:$12),99),ROW($2:$12))*10010+{0,1}*1000+{2,1},ROW()),10^3),"r0c0"),)&""
给个最普通的解法吧~~ |
13楼
hopeson2010 |
- =INDIRECT(TEXT(MIN(IF(COUNTIF(INDIRECT("R[-"&ROW()-(ROW()>1)&"]C:r[-1]C",),A$2:B$12),13013,ROW($2:$12)*10^3+{1,2})),"R0c000"),)&""
|
14楼
hopeson2010 |
D1:D17区域数组：- =INDEX(T(OFFSET(C1,ROW(2:23)/2,-MOD(ROW(2:23),2)-1)),SMALL(IF(MATCH(T(OFFSET(C1,ROW(2:23)/2,-MOD(ROW(2:23),2)-1)),T(OFFSET(C1,ROW(2:23)/2,-MOD(ROW(2:23),2)-1)),)=ROW(1:22),ROW(1:22),23),ROW(1:22)))
|
15楼
zm0115 |
=INDIRECT(TEXT(RIGHT(SMALL(IF({1,1,0},IF(MATCH($B$2:$B$12,$B$2:$B$12,)=ROW($1:$11),((ROW($1:$11)-1)*3+COLUMN(A:C))*10^6+(1+MATCH($A$2:$A$12,$A$2:$A$12,))/1%+{2,1},99901301),IF(MATCH($B$2:$B$12,$B$2:$B$12,)<>ROW($1:$11),((ROW($1:$11)-1)*3+COLUMN(A:C))*10^6+(1+MATCH($A$2:$A$12,$A$2:$A$12,))/1%+1,99901301)),ROW(1:1)),5),"r0c00"),)&""
考虑了A，B列乱序。 |
16楼
pcwmmn |
好贴，值得收藏，大家都顶 |