选择列

Excel公式

Microsoft 365 2022

=CHOOSECOLS(A1:G11,1,3,5,2)

或者使用带有行分隔符的数组

=CHOOSECOLS(A1:G11,{1;3;5;2})

或使用具有正确列分隔符的阵列或前两者的组合。

Microsoft 365 2021

=LET(d,A1:G11,c,{1,3,5,2},
    r,SEQUENCE(ROWS(d)),
INDEX(d,r,c))

• 根据您的小数点分隔符，数组列分隔符（数组{1,3,5,2}中的逗号）可能会有所不同。

到目前为止，我遇到了{1.3.5.2}、{1\3\5\2}或{1@3@5@2}。

• 要使其在任何语言环境下都能工作，您可以使用带有行分隔符的TRANSPOSE（目前还没有TOROW）：

=LET(d,A1:G11,rc,{1;3;5;2},
    r,SEQUENCE(ROWS(d)),c,TRANSPOSE(rc),
INDEX(d,r,c))

旧版本

=INDEX(A1:G11,ROW(A1:G11)-ROW(A1)+1,{1,3,5,2})

如果您的数据从第1行开始，您可以通过以下方式进行简化：

=INDEX(A1:G11,ROW(A1:G11),{1,3,5,2})

• 与2021相同，您可以通过使用TRANSPOSE({1;3;5;2})使两者适用于任何语言环境。

VBA

复制包括公式和格式

Sub CopyColumns()

    Dim Cols(): Cols = Array(1, 3, 5, 2)

    Dim wb As Workbook: Set wb = ThisWorkbook ' workbook containing this code
    Dim ws As Worksheet: Set ws = wb.Sheets("Sheet1")
    
    Dim srg As Range: Set srg = ws.Range("A1").CurrentRegion
    
    Dim dfCell As Range: Set dfCell = ws.Range("I1")
    
    Dim Col
    
    For Each Col In Cols
        Debug.Print "Copying """ & srg.Columns(Col).Address(0, 0) _
            & """ to """ & dfCell.Address(0, 0) & """..."
        srg.Columns(Col).Copy dfCell
        Set dfCell = dfCell.Offset(, 1)
    Next Col
 
    MsgBox "Columns copied.", vbInformation

End Sub

仅复制值（使用函数）

功能

Function ChooseColumns( _
    ByVal SourceRange As Range, _
    ByVal ChosenColumns As Variant) _
As Variant
    
    Dim rCount As Long: rCount = SourceRange.Rows.Count
    Dim scCount As Long: scCount = SourceRange.Columns.Count
    
    Dim sData()
    
    If rCount * scCount = 1 Then
        ReDim sData(1 To 1, 1 To 1): sData(1, 1) = SourceRange.Value
    Else
        sData = SourceRange.Value
    End If
        
    Dim dcCount As Long:
    dcCount = UBound(ChosenColumns) - LBound(ChosenColumns) + 1
    
    Dim dData(): ReDim dData(1 To rCount, 1 To dcCount)
        
    Dim sCol, r As Long, dc As Long
    
    For Each sCol In ChosenColumns
        dc = dc + 1
        For r = 1 To rCount
            dData(r, dc) = sData(r, sCol)
        Next r
    Next sCol

    ChooseColumns = dData

End Function

使用函数（Using the Function）

Sub CopyColumnsUsingFunction()

    Dim Cols(): Cols = Array(1, 3, 5, 2)

    Dim wb As Workbook: Set wb = ThisWorkbook ' workbook containing this code
    Dim ws As Worksheet: Set ws = wb.Sheets("Sheet1")
    
    Dim srg As Range: Set srg = ws.Range("A1").CurrentRegion
    
    Dim Data(): Data = ChooseColumns(srg, Cols)
    
    Dim dfCell As Range: Set dfCell = ws.Range("I1")
    
    dfCell.Resize(UBound(Data, 1), UBound(Data, 2)).Value = Data
    
    MsgBox "Columns copied.", vbInformation

End Sub

列表分隔符

Sub ShowMySeparators()
    With Application
        Debug.Print "Alternate: " & .International(xlAlternateArraySeparator)
        Debug.Print "Column: " & .International(xlColumnSeparator)
        Debug.Print "Decimal: " & .International(xlDecimalSeparator)
        Debug.Print "List: " & .International(xlListSeparator)
        Debug.Print "Row: " & .International(xlRowSeparator)
        Debug.Print "Thousands: " & .International(xlThousandsSeparator)
    End With
End Sub

我的系统结果

Alternate: @
Column: ,
Decimal: .
List: ;
Row: ;
Thousands: ,