Excel欄位資料每30筆切割移到隔壁欄
回答 (5)
2015-07-22 5:14 am
✔ 最佳答案
Excel最多到256列10000/30 = 333.33333333
會超出範圍...
2015-07-21 9:39 pm
B1
=IF(ROW()>30,"",OFFSET($A$1,(COLUMN(A1)-1)*30+ROW()-1,))
往下往右複製
2015-07-24 13:32:01 補充：
補充-如為兩欄
C1
=IF(ROW()>30,"",OFFSET($A$1,(ROUND(COLUMN(A1)/2,)-1)*30+ROW()-1,MOD(COLUMN(B1),2)))
往下往右複製
=IF(ROW()>30,"",OFFSET($A$1,(COLUMN(A1)-1)*30+ROW()-1,))
往下往右複製
2015-07-24 13:32:01 補充：
補充-如為兩欄
C1
=IF(ROW()>30,"",OFFSET($A$1,(ROUND(COLUMN(A1)/2,)-1)*30+ROW()-1,MOD(COLUMN(B1),2)))
往下往右複製
2015-07-22 8:30 pm
2015-07-22 3:06 am
Sub xx()
x = Application.RoundUp([A65536].End(xlUp).Row / 30, 0)
k = 3
y = 1
For i = 1 To x
Range("A" & y & ":B" & y + 29).Copy Cells(1, k)
k = k + 2
y = y + 30
Next
End Sub
x = Application.RoundUp([A65536].End(xlUp).Row / 30, 0)
k = 3
y = 1
For i = 1 To x
Range("A" & y & ":B" & y + 29).Copy Cells(1, k)
k = k + 2
y = y + 30
Next
End Sub
2015-07-22 1:02 am
您好，再請問一下，如果A、B欄都有資料，且須綁一起對照，譬如A欄是人名，B欄是電話，共一萬多筆，這函數要怎麼修改成每30筆連同人名+電話移到(C+D)欄...(E+F)...(G+H)...
收錄日期: 2021-05-04 01:39:44
原文連結 [永久失效]:
https://hk.answers.yahoo.com/question/index?qid=20150721000015KK03442