☰

Excel：如何将每随机数量的 N 行从一列转置到多列？

tag-icon • tag-icon microsoft-excel worksheet-function spreadsheet google-spreadsheets
Excel：如何将每随机数量的 N 行从一列转置到多列？

我有一组以 ABCD 为答案的多项选择题，所有答案都堆叠在一列中，但并非每个问题的答案数量都相同。

我的表格不是对每个第 n 个值进行转置，而是有随机数量的行包含答案 (2-5)，并且这些数据之间有随机数量的空行 (2-3)。我如何将所有这些行转置为干净的列？

基本上从 原始col 到 已排序

答案1

鉴于您的问题没有固定数量的答案，并且问题之间的空行在 1 到 2 之间变化，您必须匹配 AE 的第 n 个实例。
我更喜欢使用偏移量，但根据您的设置，这是通过数组公式完成的，即这里描述得很好。

我没有看到您的单元格的坐标，因此我将参考您的工作簿的副本： 在此处输入图片描述

  • 在列中A我有 ABCD 选项、空行和它们之间的一些文本。
  • 在列中乙我对选择有真正的答案。
  • 柱子C用于问题编号。
  • 列德福格是根据第二张屏幕截图所做的选择。

有趣的部分出现在最后提到的列下面。公式如下： {=INDEX($B:$B,SMALL(IF(D$1=$A:$A,ROW($A:$A)-ROW($A$1)+1),$C2))} 应该输入Ctrl+Shift+Enter，因为它是一个数组公式。输入后，您可以将其复制粘贴到其他单元格。

您必须重新调整工作簿中的此公式。这里没有太多变量：

  • $B:$B — 问题答案。
  • $A:$A — 在此公式中使用了两次。响应您的第一张屏幕截图和第一列，即选择键。
  • $A$1 — 上述列中第一个将被移动的单元格。
  • $C2 — 在列中冻结的问题编号。
  • D$1 — 标题中的选项与第二张截图中的一样。

答案2

这也可以通过 Power Query 来实现，可在 Windows Excel 2010+ 和 Excel 365（Windows 或 Mac）中使用

使用 Power Query

  • 在数据表中选择一些单元格
  • Data => Get&Transform => from Table/Range或者from within sheet
  • 当 PQ 编辑器打开时：Home => Advanced Editor
  • 记下表格姓名在第 2 行
  • 将下面的 M 代码粘贴到您所看到的位置
  • 将第 2 行的表名改回最初生成的表名。
  • 阅读评论并探索Applied Steps以了解算法

M 代码

let

//Change next line to reflect actual data source
    Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],

//Change next line to reflect actual Column Names
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}}),
    #"Removed Blank Rows" = Table.SelectRows(#"Changed Type", 
        each not List.IsEmpty(List.RemoveMatchingItems(Record.FieldValues(_), {"", null}))),

//Split column 1 into two columns to separate questions and the answer letter
    newTable = Table.FromColumns(
        {List.Transform(#"Removed Blank Rows"[Column1], each if List.Contains({"A".."Z"}, _) then null else _ )} &
        {List.Transform(#"Removed Blank Rows"[Column1], each if List.Contains({"A".."Z"}, _) then _ else null)} & 
        {#"Removed Blank Rows"[Column2]},
        type table[Question=nullable text, Answer Letter=nullable text, Answer text=nullable text]),

//Fill down the Questions column and remove the rows containing nulls in the Answer letter column
    #"Filled Down" = Table.FillDown(newTable,{"Question"}),
    #"Filtered Rows" = Table.SelectRows(#"Filled Down", each ([Answer Letter] <> null)),

//Pivot on the Answer letter column with no aggregation
    #"Pivoted Column" = Table.Pivot(#"Filtered Rows", List.Distinct(#"Filtered Rows"[#"Answer Letter"]), "Answer Letter", "Answer text")
in
    #"Pivoted Column"

来源
在此处输入图片描述

结果
在此处输入图片描述

答案3

完全同意已经给出的解决方案。如果你还没有手动完成任务，而是在等待现成的解决方案，那么我将提供我自己的版本。

打开宏录制。

指定用于启动的“热键”（例如，Ctrl-g）

开始按顺序按下按钮，这样你就可以复制并转置一组答案到正确的位置：

Ctrl-Home, 
Right Arrow, 
Ctrl-Down Arrow, 
Ctrl-Shift-Down Arrow (the first group of questions is highlighted), 
Ctrl-C (copy to clipboard), 
Ctrl-End, 
Ctrl-Left Arrow, 
Ctrl-Alt-V (specify "transposed"), 
Enter, 
and again Ctrl-Home, 
Right Arrow, 
Ctrl-Down Arrow, 
Ctrl-Shift-Down Arrow 
and Delete (the first group of questions is deleted).

停止宏录制。

您可能需要更改一两行代码来选择表格末尾的第一个空单元格。例如

    ActiveCell.SpecialCells(xlLastCell).Select
    Range("A" & (Selection.Row + 1)).Select

我希望您选择的昵称表明这对您来说不是一个问题。

现在按Ctrl-g直到到达列表末尾。现在 B 列已清除，其中的所有答案都收集在页面底部的表格中。

相关内容