提示:其它语言是由 Google 机器翻译的。 你可以访问 English 版本。
登录
x
or
x
x
马上登记
x

or

如何将每列5或n行从一列转置到多列?

doc转换每个5行1

假设您在列A中有很长的数据,现在想要将A列中的每个5行转置为多列,例如将A1:A5转换为C6:G6,A6:A10转换为C7:G7,等等下面的截图显示。 你如何处理这个任务,而无需在Excel中重复复制和粘贴?

将每列5或n行从一列转换为具有公式的多列

将每列5或n行从一列转换为具有VBA代码的多列

使用Kutools for Excel将每列5或n行从一列转换为多列


将每列5或n行从一列转换为多列:

要将每列5或n行从一列转换为多列, Kutools for Excel's 变换范围 实用程序可以帮助您尽快解决这项工作。 它还可以帮助您将一系列数据转换为单个行或一列。

doc转换每个5行10

Kutools for Excel:比200方便的Excel加载项,可以在60天免费试用。 下载并免费试用现在!



在Excel中,可以应用以下公式将每列中的每n行转置为多列,具体操作如下:

1。 例如,将下列公式输入到要放置结果的空白单元格C1中, = INDEX($ A:$ A,ROW(A1)* 5-5 + COLUMN(A1)),看截图:

doc转换每个5行2

注意:在上面的公式中, 答: 是要转置的列引用,并且 A1 是使用列的第一个单元格,即数字 5 表示您的数据将找到的列数,您可以将其更改为您的需要。 列表的第一个单元格必须位于工作表的第一行。

2。 然后将填充手柄右移到五个单元格,然后将填充手柄向下拖动到单元格范围,直到显示0,请参见截图:

doc转换每个5行3


如果您无法正确应用公式,以下VBA代码也可以帮助您。

1。 按住 ALT + F11 键打开 Microsoft Visual Basic for Applications 窗口。

2。 点击 插页 > 模块,并将以下代码粘贴到 模块 窗口。

VBA代码:将每个5或n行从一列转置为多列:

Public Sub TransposeData()
'updateby Extendoffice 20151207
    Dim xLRow As Long
    Dim xNRow As Long
    Dim i As Long
    Dim xUpdate As Boolean
    Dim xRg As Range
    Dim xOutRg As Range
    Dim xTxt As String
    On Error Resume Next
    xTxt = ActiveWindow.RangeSelection.Address
    Set xRg = Application.InputBox("Please select data range(only one column):", "Kutools for Excel", xTxt, , , , , 8)
    Set xRg = Application.Intersect(xRg, xRg.Worksheet.UsedRange)
    If xRg Is Nothing Then Exit Sub
    If (xRg.Columns.Count > 1) Or _
       (xRg.Areas.Count > 1) Then
        MsgBox "the used range only contain one column", , "Kutools for Excel"
        Exit Sub
    End If
    Set xOutRg = Application.InputBox("please select output range(specify one cell):", "Kutools for Excel", xTxt, , , , , 8)
    If xOutRg Is Nothing Then Exit Sub
    Set xOutRg = xOutRg.Range(1)
    xUpdate = Application.ScreenUpdating
    Application.ScreenUpdating = False
    xLRow = xRg.Rows.Count
    For i = 1 To xLRow Step 5
        xRg.Cells(i).Resize(5).Copy
        xOutRg.Offset(xNRow, 0).PasteSpecial Paste:=xlPasteAll, Transpose:=True
        xNRow = xNRow + 1
    Next
    Application.ScreenUpdating = xUpdate
End Sub

3。 粘贴代码后,请按 F5 键运行它,并弹出提示框提醒您选择要转置的列,请参阅屏幕截图:

doc转换每个5行4

4。 然后点击 OK,然后选择一个您希望将结果放入另一个弹出框的单元格,请参阅截图:

doc转换每个5行5

5。 并点击 OK,该列中的数据已转换为您需要的五列,请参阅截图:

doc转换每个5行6

注意:在上面的代码中,你可以改变数字 5 到你需要的其他号码。


如果你有 Kutools for Excel,其 变换范围 实用程序,您可以快速将一列或一行转换为多列和多行。

Kutools for Excel : 与超过300方便的Excel加载项,在60天免费试用没有限制.

安装后 Kutools for Excel请按照以下步骤进行:

1。 选择列中的数据,然后单击 Kutools > Range > 变换范围,看截图:

doc转换每个5行7

2。 在 变换范围 对话框中选择 单列到范围 选项下 变换类型,然后检查 固定值 在下面 每条记录的行数,然后指定要转置到的列数 固定值 框中,看截图:

doc转换每个5行8

3。 然后点击 Ok 按钮,在弹出的框中,请选择一个单元格输出结果,参见截图:

doc转换每个5行9

4。 然后点击 OK 按钮,并且您的列数据已按每个5行进行转置,如下面的屏幕截图所示:

doc转换每个5行6

立即下载并免费试用Kutools for Excel!


Kutools for Excel:比200方便的Excel加载项,可以在60天免费试用。 下载并免费试用现在!


Kutools for Excel - 最佳办公生产力工具提高80%的生产力

  • 重用: 快速插入 复杂的公式,图表 以及你以前用过的任何东西; 加密单元格 密码; 创建邮件列表 并发送电子邮件...
  • 超级方程式酒吧 (轻松编辑多行文字和公式); 阅读布局 (轻松读取和编辑大量单元格); 粘贴到过滤范围...
  • 合并单元格/行/列 不丢失数据; 分裂细胞含量; 组合重复的行/列...防止重复的细胞; 比较范围...
  • 选择复制或唯一 行; 选择空行 (所有细胞都是空的); 超级查找和模糊查找 在许多工作簿中; 随机选择......
  • 精确复制 多个单元格而不更改公式参考; 自动创建参考 多张表; 插入项目符号,复选框等等......
  • 提取文本,添加文本,按位置删除, 删除空间; 创建和打印分页小计; 在单元格内容和注释之间转换...
  • 超级过滤器 (将过滤方案保存并应用到其他工作表); 高级排序 按月/周/日,频率等; 特殊过滤器 用粗体,斜体......
  • 结合工作簿和工作表; 根据键列合并表; 将数据拆分为多个表格; 批量转换xls,xlsx和PDF...
  • 超过300强大的功能。 支持Office / Excel 2007-2019和365。 支持所有语言。 在您的企业或组织中轻松部署。 全功能60天免费试用。
kte tab 201905

Office选项卡为Office提供选项卡式界面,使您的工作更轻松

  • 在Word,Excel,PowerPoint中启用选项卡式编辑和阅读,Publisher,Access,Visio和Project。
  • 在同一窗口的新选项卡中打开并创建多个文档,而不是在新窗口中。
  • 通过50%提高您的工作效率,每天为您减少数百次鼠标点击!
官方底部
Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    Maha · 3 months ago
    Thanks alot _/\_ Awesome job with =INDEX($A:$A,ROW(B1)*5-5+COLUMN(B1))
    worked perfectly
  • To post as a guest, your comment is unpublished.
    Arun Balaji VN · 3 months ago
    I tried only the the formula option and it worked great. Till now have been using (Transpose formula with ctrl+shift+enter) but there the disadvantage for we have delete many extra rows. when the rows to be transposed for large in the order of lakhs, then deleting them in excel is either impossible or takes a lot of time.
    this approach made the life easier by only copying the required rows... Thanks
  • To post as a guest, your comment is unpublished.
    Sian · 1 years ago
    This is fricking AWESOME!!! Thank you SO much. Your example was exactly what I needed and it worked beautifully.
  • To post as a guest, your comment is unpublished.
    hamidreza · 1 years ago
    =INDEX($A:$A,ROW(A1)*5-5+COLUMN(A1))
    It was very simple and really useful. Thank you so much
  • To post as a guest, your comment is unpublished.
    Thank · 1 years ago
    It was great! Thank you! You saved me hours of manual work!
  • To post as a guest, your comment is unpublished.
    xplode · 2 years ago
    Thank you so much!
    It was really helpful. :)
  • To post as a guest, your comment is unpublished.
    MTVN · 2 years ago
    What formula to transpose every 5 rows to a single column?
  • To post as a guest, your comment is unpublished.
    hgimongu · 2 years ago
    Hi!

    May I ask you how to add Blank cell delimits records, instead of 5 as the data I am handling hasn't got fix number of rows. I believe the part which I should change may be around this part of the code:

    For i = 1 To xLRow Step 5
    xRg.Cells(i).Resize(5).Copy
    xOutRg.Offset(xNRow, 0).PasteSpecial Paste:=xlPasteAll, Transpose:=True
    xNRow = xNRow + 1

    Best