☰

如何按字母顺序将多列数据排列到新工作表中？

tag-icon • tag-icon microsoft-excel microsoft-excel-2010 microsoft-excel-2007
如何按字母顺序将多列数据排列到新工作表中？

如何将我已分成不同列的数据排序到新工作表中，并按字母顺序排列？我希望它们位于不同的列中（每个字母一列），但如果不这样做，将它们按字母顺序放在一列中也可以。图片是我想要的示例：

第一张表将根据类别将数据分成不同的列

第二张表将是“目录”，它从第一张表中提取所有数据并下订单

多谢！

答案1

我意识到 OP 没有包含 Office 365，但包含动态数组公式：

=LET(y,Sheet1!$A$2:$C$6,z,SEQUENCE(ROWS(y)*COLUMNS(y),,0),x,INDEX(y,INT(z)/3+1,MOD(z,3)+1),SORT(FILTER(x,LEFT(x)=A$1)))

将其放入 A2 并复制到 26 列

在此处输入图片描述

答案2

这会非常复杂，需要用到公式。你可能需要 VBA 来实现。

伪代码

for each cell in the source sheet
   if the cell has content
      get the first letter
      copy the cell into the corresponding column on the second sheet
   end if
next cell
' now all the data is in alphabetical columns on the second sheet
' next, sort each column
for the first 26 columns in the target sheet
  sort the data in that column
next column

答案3

您可以使用Power QueryExcel 2010+ 中的

  • 选择表格中的某个单元格
  • 数据 / 获取和转换 / 来自表/范围
  • 打开高级编辑器并记下第 2 行中的表名称
  • 将下面的 MCode 粘贴到高级编辑器中
    • 更改结果行 2 以反映正确的表名
  • 检查“应用步骤”窗口中的步骤来了解如何完成此操作。
  • 对条目进行排序
  • 取消透视以使其成为单个列
  • 按每个条目的首字母分组
  • 将与字母对应的所有条目提取到多列中
  • 转置结果

M 代码

let
    Source = Excel.CurrentWorkbook(){[Name="Table16"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"GEO", type text}, {"DEPORTE", type text}, {"POLITICA", type text}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {}, "Attribute", "Value"),
    #"Removed Columns" = Table.RemoveColumns(#"Unpivoted Columns",{"Attribute"}),
    #"Sorted Rows" = Table.Sort(#"Removed Columns",{{"Value", Order.Ascending}}),
    #"Added Custom" = Table.AddColumn(#"Sorted Rows", "Grouper", each Text.Start([Value],1)),
    #"Grouped Rows" = Table.Group(#"Added Custom", {"Grouper"}, {{"Grouped", each _, type table [Value=text, Grouper=text]}}),
    #"Added Custom1" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.Column([Grouped],"Value")),
    #"Extracted Values" = Table.TransformColumns(#"Added Custom1", {"Custom", each Text.Combine(List.Transform(_, Text.From), ";"), type text}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Extracted Values", "Custom", Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv), {"Custom.1", "Custom.2", "Custom.3"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Custom.1", type text}, {"Custom.2", type text}, {"Custom.3", type text}}),
    #"Removed Columns1" = Table.RemoveColumns(#"Changed Type1",{"Grouped"}),
    #"Transposed Table" = Table.Transpose(#"Removed Columns1"),
    #"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table", [PromoteAllScalars=true]),
    #"Changed Type2" = Table.TransformColumnTypes(#"Promoted Headers",{{"A", type text}, {"B", type text}, {"C", type text}, {"L", type text}, {"R", type text}, {"S", type text}})
in
    #"Changed Type2"

在此处输入图片描述

如果您必须使用没有 Power Query 的 Excel 2007，那么这里有可以完成这项工作的 VBA 代码。

  • 将数据收集到一个字典中，其中的键是字母表字母，内容是以该字母开头的项目的数组列表。

  • 数组列表很方便，因为它很容易排序，尽管也可以使用其他排序算法。

  • 确保更改wsRes、wsSrc和rRes以反映您想要的源和结果工作表和范围的位置。还请检查vSrc将引用正确的数据源范围。

Option Explicit
Option Compare Text
Sub reOrder()
    Dim wsSrc As Worksheet, wsRes As Worksheet, rRes As Range
    Dim vSrc, vRes, V, W
    Dim Dict As Object, AL As Object, alKeys As Object
    Dim I As Long, J As Long, sKey As String * 1
    
Set wsSrc = Worksheets("sheet13")
Set wsRes = Worksheets("sheet13")
    Set rRes = wsRes.Cells(1, 15)
    
With wsSrc
    vSrc = .Range(.Cells(1, 1), .Cells(.Rows.Count, 1).End(xlUp)).Resize(columnsize:=3)
End With

'Gather the data
Set Dict = CreateObject("Scripting.Dictionary")
For I = 2 To UBound(vSrc, 1)
    For J = 1 To UBound(vSrc, 2)
      If Trim(vSrc(I, J)) <> "" Then
        sKey = vSrc(I, J)
        If Not Dict.exists(sKey) Then
            Set AL = CreateObject("System.Collections.ArrayList")
            AL.Add vSrc(I, J)
            Dict.Add sKey, AL
        Else
            Dict(sKey).Add vSrc(I, J)
        End If
      End If
    Next J
Next I

'Size vRes
I = 0
For Each V In Dict
    I = IIf(I > Dict(V).Count, I, Dict(V).Count)
Next V

ReDim vRes(0 To I, 1 To Dict.Count)

'Sort the dictionary keys
Set alKeys = CreateObject("System.Collections.ArrayList")
For Each V In Dict.keys
    alKeys.Add V
Next V
alKeys.Sort

J = 0
For Each V In alKeys
    J = J + 1
        
        'Column Header
        vRes(0, J) = V
        I = 0
        
            'Data
            For Each W In Dict(V)
                I = I + 1
                vRes(I, J) = W
            Next W
Next V

'Write the results to worksheet
Set rRes = rRes.Resize(UBound(vRes, 1) + 1, UBound(vRes, 2))
Application.ScreenUpdating = False
With rRes
    .EntireColumn.Clear
    .Value = vRes
    With .Rows(1)
        .Font.Bold = True
        .HorizontalAlignment = xlCenter
    End With
    .EntireColumn.AutoFit
End With
End Sub

相关内容