☰

如何使用 Excel 进行这种非常具体的连接并将数组转换为列表？

tag-icon • tag-icon microsoft-excel worksheet-function vba
如何使用 Excel 进行这种非常具体的连接并将数组转换为列表？

首先，对于任何语法错误，我深感抱歉，因为我的母语不是英语。

我的问题如下：我有一个非常大的数据集，其中包含给定年份（列）中给定城市（行）的人口数据。我想转换此表，使城市和年份连接在一起（例如：洛杉矶 2001，其下是洛杉矶 2002，等等），并将人口数据放在右侧，也放在单个列中（总共两个）。

我对我需要的东西做了一个模型，由于我的英语水平有限，我只能解释这么多。图像

  • 我尝试使用连接函数，但没有什么效果。我的 Excel 版本没有 TOCOL 函数。

  • 手工完成是不可能的，因为它太大了。

  • 如果您不知道完美的解决方案，一些指示会大有帮助。如果知道 Excel 是否是合适的工具，我将不胜感激。

答案1

尝试：

=HSTACK(TOCOL(A2:A6&B1:E1),TOCOL(B2:E6))

答案2

对于旧版本的 Excel，您可以使用如屏幕截图所示的公式。
二维转换为列

或者，如果没有这个G2:J6区域，您可以输入L2：

=INDEX($A$2:$A$6 & $B$1:$E$1,(ROW(A1)-1)/4+1,MOD(ROW(A1)-1,COLUMNS($B$2:$E$6))+1)

答案3

这不是您问题的完整答案。我有一个解决方案，可以将值数组转换为列表。
使用以下公式=TRANSPOSE(HSTACK(C3:F3,C4:F4,C5:F5,C6:F6,C7:F7))

显示 transpose-hstack 公式的屏幕截图

请让我知道，如果你有任何问题。

答案4

由于您明确指出您的 Excel 版本没有 HSTACK 或 TOCOL 之类的函数，并且您还标记了VBA，请打开 Visual Basic 编辑器 (Alt+F11) 并将以下代码粘贴到新模块中：

Option Explicit

Sub UnpivotData()

'Load the current data range into an array
    Dim currentData As Variant
    currentData = ActiveSheet.Range("A1").CurrentRegion.Value

'Set the dimensions of the output data
    Dim lRows As Long, data() As Variant
    lRows = (UBound(currentData, 1) - 1) * (UBound(currentData, 2) - 1)
    ReDim data(1 To lRows, 1 To 2)

'Transform the data, row by row, column by column
    Dim i As Long, j As Long, rowId As Long
    For i = 2 To UBound(currentData, 1)
        For j = 2 To UBound(currentData, 2)
            rowId = rowId + 1
            data(rowId, 1) = currentData(i, 1) & currentData(1, j)
            data(rowId, 2) = currentData(i, j)
        Next j
    Next i

'Write the transformed data to the output range
    With ActiveSheet.Range("H1")
        .CurrentRegion.ClearContents
        .Resize(UBound(data, 1), UBound(data, 2)).Value = data
    End With

End Sub

无论数据范围内有多少行或多少列，上述代码都会输出所需的结果。只需调整两个范围引用即可满足您的需求。

图1：运行代码后的结果... 取消透视数据

相关内容