☰

使用工作表函数将 Excel 中的多列转换为一列标题和一列所有数据

tag-icon • tag-icon microsoft-excel-2010 worksheet-function
使用工作表函数将 Excel 中的多列转换为一列标题和一列所有数据

给定 N 个带有标题和下方列表的列，我想将其转换为数据库格式，其中标题在第一列，列表项在第二列。

Column1    Column2    Column3    Column4    Column5
Data1      Data1      Data3
Data2                 Data4

因此这些数据就变成了

Column1    Data1
Column1    Data2
Column2    Data1
Column3    Data3
Column3    Data4

我想使用 Excel 公式来做到这一点，以便当我添加数据列时，这些信息将自动更新，而无需运行宏或数据透视表。

这将是两个公式，每个列一个，可以直接拖下来。答案很可能是某种形式的索引/匹配/countif 数组怪物。

不反对添加辅助列。这个页面让我看了一半： http://www.extendoffice.com/documents/excel/1897-excel-repeat-cell-value-x-times.html#a1

答案1

因此，我找到的链接级联成了一个解决方案，但它需要 2 个以上的公式。

http://www.extendoffice.com/documents/excel/1897-excel-repeat-cell-value-x-times.html#a1

下面是我创建的示例表的链接： https://drive.google.com/file/d/0B4hGhOqw8ilUOVdkMGVLLVR2Uzg/view?usp=sharing

这涵盖了标题迁移，但为了达到这一点，还需要 2 列数据，这些数据也需要公式。

对于标题为静态的人，您需要一种方法将标题放入一列，我的标题已经是从另一列转置的，所以我已经完成了，但是像这样：

=OFFSET(FirstHeader,0,ROW()-CurrentRowNumber)&""

可以将列标题合并到一列中。现在您有：

ColumnHeaders
Column1
Column2
Column3

从：

Column1    Column2    Column3    BlankColumn    BlankColumn
Data1      Data1      Data3
Data2                 Data4

现在我需要计算每列的数据项数量。我从这个 LittleMonstrosity 中得到了这个数据：

=IF(B2<>"",SUMPRODUCT(--(INDIRECT(ADDRESS(FirstRowIndex,MATCH(B2,$A$1:$AA$1,0))&":"&ADDRESS(LastRowIndex,MATCH(B2,$A$1:$AA$1,0)))<>"")),"")

由于内存限制，第一行和最后一行索引是我将接受的列表大小的预定义界限。类似于只允许 A 到 AA 的列。也许我稍后会将边界框移出，这样我就可以从一个位置更改它。

因此使用这个我现在得到：

CountIndices   ColumnHeaders
2              Column1
1              Column2
2              Column3

但我需要实际的起始索引而不仅仅是计数，所以我在起始索引的第一行中放入 1，并将这个小怪物包裹在其中：

=IFERROR(A2+LittleMonstrosity,"")

要得到：

StartIndices   ColumnHeaders
1              Column1
3              Column2
4              Column3
6

只有现在我才能使用我找到的链接中的示例代码来派生我的第一个数据列：

=VLOOKUP(ROWS($B$2:$B2),DataRange,2)

再次将这些边界内置到 DataRange 中。很快我甚至会在合理范围内将其动态化。

现在我有了第一个标题列：

A              B                M         N
StartIndices   ColumnHeaders    Header    Data
1              Column1          Column1
3              Column2          Column1
4              Column3          Column2
6                               Column3
                                Column3

现在，为了获取每列的数据，我们构建一个计数器，计算配对标题在其上方的列中出现的次数，并在使用此公式找到正确的列后使用该计数器对数据进行索引，请记住，在此示例中，标题位于 M 列：

=IF(M2<>"",INDEX(DataRange,COUNTIF($M$2:$M2,M2),MATCH(M2,HeaderRange,0)),"")

现在我终于明白了：

A              B                M         N
StartIndices   ColumnHeaders    Header    Data
1              Column1          Column1   Data1
3              Column2          Column1   Data2
4              Column3          Column2   Data1
6                               Column3   Data3
                                Column3   Data4

相关内容