如何将多列转置为具有重复 ID 的单列？

Question 1

使用如下数据：

在G1进入：

=INDEX(A$2:A$9999,ROUNDUP(ROWS($1:1)/4,0))

在H1进入：

=INDEX($B$1:$E$1,MOD(ROUNDUP(ROWS($1:1)/4,0)-1,4)+1)

在I1进入：

=OFFSET($B$2,ROUNDUP(ROWS($1:1)/4,0)-1,MOD(ROWS($1:1)-1,4))

向下复制这些单元格：

Answer

使用如下数据：

在G1进入：

=INDEX(A$2:A$9999,ROUNDUP(ROWS($1:1)/4,0))

在H1进入：

=INDEX($B$1:$E$1,MOD(ROUNDUP(ROWS($1:1)/4,0)-1,4)+1)

在I1进入：

=OFFSET($B$2,ROUNDUP(ROWS($1:1)/4,0)-1,MOD(ROWS($1:1)-1,4))

向下复制这些单元格：

Question 2

有很多方法可以解决这个问题，但就灵活性，我会POWER QUERY在这里使用毫不费力地实现自动化从而节省时间和精力. Power Query，有Windows Excel 2010+和Excel 365 (Windows or Mac)。观看演示以供参考。耗时不到2分钟去执行任务。

要使用 Power Query，请按照以下步骤操作：

首先将源范围转换为表格并进行相应命名，在此示例中，我将其命名为Table1

Data接下来，从Tab --> Get & Transform Data--> Get Data--> From Other Sources-->打开一个空白查询Blank Query

上面的命令可以Power Query打开窗口，现在从HomeTab --> Advanced Editor--> 删除您看到的任何内容并粘贴以下内容M-Code，然后按Done

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"ID"}, "Date", "A/B/C"),
    #"Changed Type" = Table.TransformColumnTypes(#"Unpivoted Other Columns",{{"ID", Int64.Type}, {"Date", type date}, {"A/B/C", type text}})
in
    #"Changed Type"

最后，将其导回Excel--> 单击Close & Load或Close & Load To--> 第一个单击的将创建一个New Sheet具有所需输出的，而后者将提示一个窗口询问您将结果放在何处。

使用现代 Excel 版本（MS365即动态数组公式）也可以完成，只需输入一个条目。以下公式可以简化，为了演示，按步骤显示变量。

• 单元格中使用的公式H3

=LET(
     _Data, Table1[#All],
     _Dates, TAKE(_Data,1,-COLUMNS(_Data)+1),
     _IDs, TAKE(_Data,-ROWS(_Data)+1,1),
     _Context, DROP(_Data,1,1),
     _TransformOne, TOCOL(IFS(_Context<>"",_IDs&"|"&_Dates),3),
     _TransformTwo, TOCOL(_Context,3),
     _Output, HSTACK(TEXTBEFORE(_TransformOne,"|"),TEXTAFTER(_TransformOne,"|"),_TransformTwo),
     _Headers, HSTACK(TAKE(_Data,1,1),"Date","A/B/C"),
     VSTACK(_Headers,_Output))

_Data--> 一个变量来保存包括标题在内的整个数据范围，因此这是唯一需要更改的公式；Table1[#All]
_Dates--> 使用函数从标题中提取日期TAKE()。TAKE(_Data,1,-COLUMNS(_Data)+1)
_IDs--> 使用函数从数据体中提取 id TAKE()。TAKE(_Data,-ROWS(_Data)+1,1)
_ContextDROP()--> 使用函数提取数据体上下文DROP(_Data,1,1)
_TransformOne--> 转换数据，即使用IDs&将&重复Datesn 次并垂直重组。 TOCOL()IFS()TOCOL(IFS(_Context<>"",_IDs&"|"&_Dates),3)
_TransformTwo--> 使用-将其转换_Context为单个数组TOCOL()TOCOL(_Context,3)
_Output--> 根据请求的输出输出数据，这里使用TEXTBEFORE()&TEXTAFTER()将数据分成_TransformTwo两列。-- --> 使用--HSTACK(TEXTBEFORE(_TransformOne,"|"),TEXTAFTER(_TransformOne,"|"),_TransformTwo) _Headers合并标题HSTACK()HSTACK(TAKE(_Data,1,1),"Date","A/B/C")
VSTACK(_Headers,_Output)--> 使用VSTACK()函数附加_Headers和_Output。

另外，不要使用上面的内容，而是将上面的内容放入Name Manager并将其命名为UNPIVOT()，以便可以在工作簿中的任何位置使用它。在此特定情况下，使用

=UNPIVOT(Table1[#All])

Excel

Answer