问:原数据在A1:An,希望从左到右每5个数一行的向下排列。
答:=IF(COLUMN(A1)>5,"",OFFSET($A$1,ROW(A1)*5+COLUMN(A1)-6,)&"")
=IF(COLUMN(A1)>5,"",INDEX($A:$A,ROW(A1)*5+COLUMN(A1)-5,)&"")
=IF(COLUMN(A1)>5,"",INDIRECT("a"&(COLUMN(A1)+5*ROW(A1)-5)))&""
右拉下拉
问:原数据在A1:An,希望从上到下每5个数一列的向右排列。
答:=IF(ROW(A1)>5,"",OFFSET($A$1,ROW(A1)+COLUMN(A1)*5-6,))&""
=IF(ROW(A1)>5,"",INDEX($A:$A,ROW(A1)+COLUMN(A1)*5-5,))&""
=IF(ROW(A1)>5,"",INDIRECT("a"&(ROW(A1)+5*COLUMN(A1)-5)))&""
下拉右拉
问:原数据在第一行,从A1开始,希望从左到右每5个数一行的向下排列。
答:=IF(COLUMN(A1)>5,"",OFFSET($A$1,,COLUMN(A1)+ROW(A1)*5-6)&"")
=IF(COLUMN(A1)>5,"",INDEX($1:$1,,COLUMN(A1)+ROW(A1)*5-5)&"")
下拉右拉
问:原数据在第一行,从A1开始,希望从上到下每5个数一列的向右排列。
答:=IF(ROW(A1)>5,"",OFFSET($A$1,,COLUMN(A1)*5+ROW(A1)-6)&"")
=IF(ROW(A1)>5,"",INDEX($1:$1,,COLUMN(A1)*5+ROW(A1)-5)&"")
下拉右拉
答:=IF(COLUMN(A1)>5,"",OFFSET($A$1,ROW(A1)*5+COLUMN(A1)-6,)&"")
=IF(COLUMN(A1)>5,"",INDEX($A:$A,ROW(A1)*5+COLUMN(A1)-5,)&"")
=IF(COLUMN(A1)>5,"",INDIRECT("a"&(COLUMN(A1)+5*ROW(A1)-5)))&""
右拉下拉
问:原数据在A1:An,希望从上到下每5个数一列的向右排列。
答:=IF(ROW(A1)>5,"",OFFSET($A$1,ROW(A1)+COLUMN(A1)*5-6,))&""
=IF(ROW(A1)>5,"",INDEX($A:$A,ROW(A1)+COLUMN(A1)*5-5,))&""
=IF(ROW(A1)>5,"",INDIRECT("a"&(ROW(A1)+5*COLUMN(A1)-5)))&""
下拉右拉
问:原数据在第一行,从A1开始,希望从左到右每5个数一行的向下排列。
答:=IF(COLUMN(A1)>5,"",OFFSET($A$1,,COLUMN(A1)+ROW(A1)*5-6)&"")
=IF(COLUMN(A1)>5,"",INDEX($1:$1,,COLUMN(A1)+ROW(A1)*5-5)&"")
下拉右拉
问:原数据在第一行,从A1开始,希望从上到下每5个数一列的向右排列。
答:=IF(ROW(A1)>5,"",OFFSET($A$1,,COLUMN(A1)*5+ROW(A1)-6)&"")
=IF(ROW(A1)>5,"",INDEX($1:$1,,COLUMN(A1)*5+ROW(A1)-5)&"")
下拉右拉