按列到列

Option Explicit

Sub ToColumn()
    
    Const SRC_RANGE As String = "A2:C6"
    Const DST_FIRST_CELL As String = "E2"
    
    Dim ws As Worksheet: Set ws = ActiveSheet ' improve!
    
    Dim srg As Range: Set srg = ws.Range(SRC_RANGE)
    Dim Data(): Data = ToCol(srg, True)
    
    Dim drg As Range: Set drg = ws.Range(DST_FIRST_CELL).Resize(UBound(Data, 1))
    drg.Value = Data

End Sub

Function ToCol( _
    ByVal rg As Range, _
    Optional ByVal ByColumns As Boolean = False) _
As Variant()
    
    Dim sData(), srCount As Long, scCount As Long
    Dim dData(), IsNotSingleCell As Boolean
    
    With rg.Areas(1)
        
        srCount = .Rows.Count
        scCount = .Columns.Count
        
        If srCount * scCount = 1 Then
            ReDim dData(1 To 1, 1 To 1): dData(1, 1) = .Value
        Else
            sData = .Value
            ReDim dData(1 To srCount * scCount, 1 To 1)
            IsNotSingleCell = True
        End If
    
    End With
    
    If IsNotSingleCell Then
        
        Dim sr As Long, sc As Long, dr As Long
        
        If ByColumns Then
            For sc = 1 To scCount
                For sr = 1 To srCount
                    dr = dr + 1
                    dData(dr, 1) = sData(sr, sc)
                Next sr
            Next sc
        Else
            For sr = 1 To srCount
                For sc = 1 To scCount
                    dr = dr + 1
                    dData(dr, 1) = sData(sr, sc)
                Next sc
            Next sr
        End If
    
    End If
        
    ToCol = dData
        
End Function

（旧版）配方溶液

按列

=INDEX($A$2:$C$6,
    MOD(ROWS($A$2:$A2)-1,ROWS($A$2:$A$6))+1,
    QUOTIENT(ROWS($A$2:$A2)-1,ROWS($A$2:$A$6))+1)

按行

=INDEX($A$2:$C$6,
    QUOTIENT(ROWS($A$2:$A2)-1,COLUMNS($A$2:$C$2))+1,
    MOD(ROWS($A$2:$A2)-1,COLUMNS($A$2:$C$2))+1)

按列

如果数据以A1开头...

E1:E15  =MOD(ROW()-1,5)+1
F1:F15  =QUOTIENT(ROW()-1,5)+1
G1:G15  =INDEX($A$1:$C$5,MOD(ROW()-1,5)+1,QUOTIENT(ROW()-1,5)+1)

......但事实并非如此。

I2:I16   =MOD(ROW()-2,5)+1
J2:J16  =QUOTIENT(ROW()-2,5)+1
K2       =INDEX($A$2:$C$6,
             MOD(ROWS($A$2:$A2)-1,ROWS($A$2:$A$6))+1,
             QUOTIENT(ROWS($A$2:$A2)-1,ROWS($A$2:$A$6))+1)

按行

如果数据以A1开头...

M1:M15  =QUOTIENT(ROW()-1,3)+1
N1:N15  =MOD(ROW()-1,3)+1
O1:O15  =INDEX($A$1:$C$5,QUOTIENT(ROW()-1,3)+1,MOD(ROW()-1,3)+1)

......但事实并非如此。

Q2:Q16  =QUOTIENT(ROW()-2,3)+1
R2:R16  =MOD(ROW()-2,3)+1
S2      =INDEX($A$2:$C$6,
            QUOTIENT(ROWS($A$2:$A2)-1,COLUMNS($A$2:$C$2))+1,
            MOD(ROWS($A$2:$A2)-1,COLUMNS($A$2:$C$2))+1)

使用TOCOL()函数。

=TOCOL(A1:C5,,TRUE)

函数引用：

TOCOL()

您可以在O365中尝试以下公式（正常输入）：
=INDEX($A$1:$C$5,0,CEILING(ROWS($A$1:A1)/5,1))
对于如下所示的设置：

如果您使用不同版本的Excel，则可能需要使用 CTRL+SHIFT+ENTER。

如果您希望结果仍在第一列中，则类似于下图（之前/之后）：

==〉

Sub test()
Dim rg As Range: Dim i As Integer

With ActiveSheet 'change as needed
    Set rg = .Range("A2", .Range("C2").End(xlDown)) 'change as needed
End With

For i = 1 To rg.Columns.Count - 1
    rg.End(xlDown).Offset(1, 0).Resize(rg.Rows.Count, 1).Value = _
    rg.Columns(1).Offset(0, i).Value: rg.Columns(1).Offset(0, i).ClearContents
Next

End Sub

rg是数据的范围（在本例中是从单元格A2到C6）。
它使用i作为偏移值进行循环，并且每次循环都用偏移范围值填充最后一个空白行。