楼主
蒸蒸日上 |
请根据A列的队员组成B列的列队相迎。要求由上而下依次三人一组。如图：
8月底公布答案！
请大家积极参与，答对评5个魅力值！
 列队.rar
|
2楼
0Mouse |
B2公式： - =IF(ROW()-1>(COUNTA(A:A)-1)/3,"",INDIRECT("a"&ROW(A1)*3-1)&","&INDIRECT("a"&ROW(A1)*3)&","&INDIRECT("a"&ROW(A1)*3+1))
|
3楼
罗刚君 |
- =IF((ROW()-2)*3>COUNTA(A:A)-2,"",OFFSET(A$1,1+((ROW()-2)*3),)&","&OFFSET(A$1,2+((ROW()-2)*3),)&","&OFFSET(A$1,3+((ROW()-2)*3),))
|
4楼
Zaezhong |
- =IF(ROW(A1)<COUNTA(A:A)/3,OFFSET(A$2,3*ROW(A1)-3,)&","&OFFSET(A$2,3*ROW(A1)-2,)&","&OFFSET(A$2,3*ROW(A1)-1,),"")
不通用的 |
5楼
piny |
B2- =INDEX(A:A,ROW()*3-4)&","&INDEX(A:A,ROW()*3-3)&","&INDEX(A:A,ROW()*3-2)
下拉
~需考慮容錯嗎？比如A列存在空值或錯誤值？比如A列個數不定？~ |
6楼
天南地北 |
见附件！楼主没有提要求，呵呵
列队.rar
|
7楼
涅磐86970 |
=INDEX(A:A,ROW(A1)*3-1)&","&INDEX(A:A,ROW(A1)*3)&","&INDEX(A:A,ROW(A1)*3+1)
这答案是不是太2了- - |
8楼
larkzh |
暂时只会这样做。
- =IF(ISERR(INDEX($A$2:$A$10,1+(ROW()-2)*3)&","&INDEX($A$2:$A$10,2+(ROW()-2)*3)&","&INDEX($A$2:$A$10,3+(ROW()-2)*3)),"",INDEX($A$2:$A$10,1+(ROW()-2)*3)&","&INDEX($A$2:$A$10,2+(ROW()-2)*3)&","&INDEX($A$2:$A$10,3+(ROW()-2)*3))
列队.rar
|
9楼
yunqingzi |
在C列，D列，E列设置函数=INDIRECT("A"&(ROW()-1)*3+COLUMN()-1,TRUE)
然后B列=C1&","&D1&","&E1就可以得到这个效果。 |
10楼
hopeson2010 |
=REPLACE(REPLACE(PHONETIC(OFFSET(A$1,(ROW()-2)*3+1,,3)),3,,","),6,,",") |
11楼
xgg2001 |
=OFFSET(A$1,ROW(2:2)*3-2,)&","&OFFSET(A$1,ROW(2:2)*3-1,)&","&OFFSET(A$1,ROW(2:2)*3,) |
12楼
bluexuemei |
- =IF(ROW(A1)>COUNTA($A$2:$A$10)/3,"",OFFSET($A$2,ROW(A1)*3-3,)&","&OFFSET($A$3,ROW(A1)*3-3,)&","&OFFSET($A$4,ROW(A1)*3-3,))
|
13楼
wqy88 |
B2输入然后下拉：- =REPLACE(REPLACE(MID(PHONETIC($A$2:$A$10),ROW(1:1)*6-5,6),3,,","),6,,",")
|
14楼
天南地北 |
另外一种解法，比较通用一点，利用辅助列B列对应的输入“，”，然后在C2输入如下公式- =LEFT(PHONETIC(OFFSET(A1:B3,ROW(A1)*2-1,)),8)
列队.rar
|
15楼
wjh619993018 |
- B2=SUBSTITUTE(PHONETIC(OFFSET(A$1,3*ROW()-5,,3)),INDEX(A:A,3*ROW()-3),","&INDEX(A:A,3*ROW()-3)&",")
|
16楼
qyote |
学习学习 |
17楼
老百姓 |
|
18楼
孔昆友 |
我这个ip是不是都被k了 |
19楼
朱少伎 |
真好。 |
