Pregunta ¿Cómo "aplanar" o "colapsar" una tabla 2D de Excel en 1D?


Tengo una tabla bidimensional con países y años en Excel. p.ej.

        1961        1962        1963        1964
USA      a           x            g           y
France   u           e            h           a
Germany  o           x            n           p

Me gustaría "aplanarlo", de modo que tengo País en la primera columna, Año en la segunda columna, y luego valor en la tercera columna. p.ej.

Country      Year       Value
USA          1961       a
USA          1962       x
USA          1963       g
USA          1964       y
France       1961       u
              ...

El ejemplo que presento aquí es solo una matriz de 3x4, pero el conjunto de datos reales que tengo es significativamente más grande (aproximadamente 50x40 más o menos).

¿Alguna sugerencia de cómo puedo hacer esto usando Excel?


35
2018-03-26 20:34


origen


Respuestas:


Puede usar la función de tabla dinámica de Excel para invertir una tabla dinámica (que es esencialmente lo que tiene aquí):

Buenas instrucciones aquí:

http://spreadsheetpage.com/index.php/tip/creating_a_database_table_from_a_summary_table/

Que enlaza con el siguiente código de VBA (ponerlo en un módulo) si no quiere seguir las instrucciones a mano:

Sub ReversePivotTable()
'   Before running this, make sure you have a summary table with column headers.
'   The output table will have three columns.
    Dim SummaryTable As Range, OutputRange As Range
    Dim OutRow As Long
    Dim r As Long, c As Long

    On Error Resume Next
    Set SummaryTable = ActiveCell.CurrentRegion
    If SummaryTable.Count = 1 Or SummaryTable.Rows.Count < 3 Then
        MsgBox "Select a cell within the summary table.", vbCritical
        Exit Sub
    End If
    SummaryTable.Select
    Set OutputRange = Application.InputBox(prompt:="Select a cell for the 3-column output", Type:=8)
'   Convert the range
    OutRow = 2
    Application.ScreenUpdating = False
    OutputRange.Range("A1:C3") = Array("Column1", "Column2", "Column3")
    For r = 2 To SummaryTable.Rows.Count
        For c = 2 To SummaryTable.Columns.Count
            OutputRange.Cells(OutRow, 1) = SummaryTable.Cells(r, 1)
            OutputRange.Cells(OutRow, 2) = SummaryTable.Cells(1, c)
            OutputRange.Cells(OutRow, 3) = SummaryTable.Cells(r, c)
            OutputRange.Cells(OutRow, 3).NumberFormat = SummaryTable.Cells(r, c).NumberFormat
            OutRow = OutRow + 1
        Next c
    Next r
End Sub

-Adán


35
2018-03-26 20:38



La respuesta de @Adam Davis es perfecta, pero por si acaso no tiene la menor idea que yo de Excel VBA, esto es lo que hice para que el código funcione en Excel 2007:

  1. Abra el libro de trabajo con la Matriz que debe aplanarse en una tabla y vaya a esa hoja de trabajo
  2. Presione Alt-F11 para abrir el editor de código VBA.
  3. En el panel izquierdo, en el cuadro Proyecto, verá una estructura en árbol que representa los objetos de Excel y cualquier código (llamados módulos) que ya existan. Haga clic derecho en cualquier lugar del cuadro y seleccione "Insertar -> Módulo" para crear un archivo de módulo en blanco.
  4. Copie y pegue el código de @Adman Davis desde arriba como está en la página en blanco que se abre y guárdelo.
  5. Cierre la ventana del editor de VBA y regrese a la hoja de cálculo.
  6. Haga clic en cualquier celda de la matriz para indicar la matriz con la que trabajará.
  7. Ahora necesitas ejecutar la macro. Donde esta opción variará de acuerdo con su versión de Excel. Como estoy usando 2007, puedo decirles que mantiene sus macros en la cinta "Ver" como el control más alejado a la derecha. Haga clic en él y verá una lista de macros lavadas, simplemente haga doble clic en la que se llama "ReversePivotTable" para ejecutarla.
  8. Luego mostrará una ventana emergente que le pedirá que le diga dónde crear la tabla aplanada. Solo apúntalo a cualquier espacio vacío y a tu hoja de cálculo y haz clic en "ok"

¡Terminaste! La primera columna será las filas, la segunda columna serán las columnas, la tercera columna serán los datos.


17
2017-10-21 20:26



En Excel 2013, es necesario seguir los siguientes pasos:

  • seleccionar datos y convertir a la tabla (Insertar -> Tabla)
  • llamar al Editor de consultas para la tabla (Power Query -> De la tabla)
  • seleccionar columnas que contienen años
  • en el menú contextual seleccione 'Desenfocar columnas'-mando.

Oficina de soporte: columnas sin pivote (Power Query)


6
2017-08-14 22:19



Aplanar una matriz de datos (aka Mesa) se puede lograr con una fórmula de matriz¹ y dos fórmulas estándar.

Flatten table into columns

La fórmula de matriz¹ y dos fórmulas estándar en G3: I3 son,

=IFERROR(INDEX(A$2:A$4, MATCH(0, IF(COUNTIF(G$2:G2, A$2:A$4&"")<COUNT($1:$1), 0, 1), 0)), "")
=IF(LEN(G3), INDEX($B$1:INDEX($1:$1, MATCH(1E+99,$1:$1 )), , COUNTIF(G$3:G3, G3)), "")
=INDEX(A:J,MATCH(G3,A:A,0),MATCH(H3,$1:$1,0))

Rellene según sea necesario.

Si bien las fórmulas de matriz pueden afectar negativamente el rendimiento debido a su cálculo cíclico, el entorno de trabajo descrito de 40 filas × 50 columnas no debe afectar demasiado el rendimiento con un retraso de cálculo.


¹ Las fórmulas de matriz deben completarse con Ctrl+Cambio+Enter↵. Una vez ingresados ​​correctamente en la primera celda, pueden llenarse o copiarse o correctos como cualquier otra fórmula. Intente y reduzca las referencias de columna completa a rangos que representen más de cerca la extensión de sus datos reales. Las fórmulas de matriz mastican los ciclos de cálculo de forma logarítmica, por lo que es una buena práctica reducir los intervalos de referencia a un mínimo. Ver Pautas y ejemplos de fórmulas de matriz para más información.


4
2017-10-07 02:59



Para cualquiera que quiera usar la tabla dinámica para hacer esto y siga la siguiente guía: http://spreadsheetpage.com/index.php/tip/creating_a_database_table_from_a_summary_table/

Si desea hacerlo en Excel 2007 o 2010, primero debe habilitar el Asistente de tabla dinámica.

Para encontrar la opción, debe ir a "Opciones de Excel" a través del ícono de la Ventana Principal de Excel, y ver las opciones seleccionadas en la sección "personalizar", luego seleccionar "Comandos No en la Cinta" del menú desplegable "Elegir Comandos desde:" y "Asistente de tablas dinámicas y gráficos dinámicos" debe agregarse a la derecha ... vea la imagen a continuación.

Una vez hecho esto, debe haber un pequeño icono de asistente pivotante en el menú de la barra rápida en la parte superior de la ventana de Excel, luego puede seguir el mismo proceso que se muestra en el enlace anterior.

enter image description here


2
2018-02-16 13:53



Es posible que la solución VBA no sea aceptable en algunas situaciones (por ejemplo, no puede incrustar macro debido a razones de seguridad, etc.). Para estas situaciones, y también en general, prefiero usar fórmulas sobre macro.

Estoy tratando de describir mi solución a continuación.

  • datos de entrada como se muestra en la pregunta (B2: F5)
  • column_header (C2: F2)
  • row_header (B3: B5)
  • data_matrix (C3: F5)
  • no_of_data_rows (I2) = COUNTA (row_header) + COUNTBLANK (row_header)
  • no_of_data_columns (I3) = COUNTA (column_header) + COUNTBLANK (column_header)
  • no_output_rows (I4) = no_of_data_rows * no_of_data_columns
  • el área de semilla es K2: M2, que está en blanco pero referenciada, por lo tanto, no debe borrarse
  • K3 (arrastre por decir K100, vea la descripción de comentarios) = FILA () - FILA ($ K $ 2) <= no_output_rows
  • L3 (arrastre por decir, L100, vea la descripción de comentarios) = IF (K3, IF (CONTEO ($ L $ 2: L2, L2)
  • M3 (arrastre por decir M100, vea la descripción de comentarios) = IF (K3, IF (M2 <no_of_data_columns, M2 + 1,1), "-")
  • N3 (arrastre por decir N100, vea la descripción de comentarios) = INDICE (row_header, L3)
  • O3 (arrastre por decir O100, vea la descripción de comentarios) = INDICE (column_header, M3)
  • P3 (arrastre, digamos P100, consulte la descripción de comentarios) = INDEX (data_matrix, L3, M3)
  • Comenta en K3: Opcional: Marque si se espera que no. de las filas de salida se ha logrado. No es obligatorio, si solo se prepara esta tabla se limita a no. de filas de salida
  • Comentario en L3: Gol: Cada RowIndex (1 .. no_of_data_rows) debe repetir no_of_data_columns veces. Esto proporcionará la búsqueda de índice para los valores row_header. En este ejemplo, cada RowIndex (1 .. 3) debe repetir 4 veces. Algoritmo: Compruebe cuántas veces ha ocurrido RowIndex todavía. Si es inferior a no_of_data_columns veces, continúe usando ese RowIndex, de lo contrario, incremente el RowIndex. Opcional: Marque si se espera que no. de las filas de salida se ha logrado.
  • Comenta en M3: Gol: Cada ColumnIndex (1 .. no_of_data_columns) debe repetirse en un ciclo. Esto proporcionará una búsqueda de índice para los valores de column_header. En este ejemplo, cada ColumnIndex (1 .. 4) debe repetirse en un ciclo. Algoritmo: Si ColumnIndex excede no_of_data_columns, reinicie el ciclo en 1, de lo contrario, incremente ColumnIndex. Opcional: Marque si se espera que no. de las filas de salida se ha logrado.
  • Comenta en R4: Opcional: Utilice la columna K para el manejo de errores, como se muestra en la columna L y la columna M. Compruebe si el valor buscado IsBlank para evitar el "0" incorrecto en la salida debido a la entrada en blanco en data_matrix.

0
2017-08-29 06:38



Desarrollé otra macro porque necesitaba refrescar la tabla de salida con bastante frecuencia (la tabla de entrada estaba llena por otra) y quería tener más información en mi tabla de salida (más columnas copiadas y algunas fórmulas)

Sub TableConvert()

Dim tbl As ListObject 
Dim t
Rows As Long
Dim tCols As Long
Dim userCalculateSetting As XlCalculation
Dim wrksht_in As Worksheet
Dim wrksht_out As Worksheet

'##block calculate and screen refresh
Application.ScreenUpdating = False
userCalculateSetting = Application.Calculation
Application.Calculation = xlCalculationManual

'## get the input and output worksheet
Set wrksht_in = ActiveWorkbook.Worksheets("ressource_entry")'## input
Set wrksht_out = ActiveWorkbook.Worksheets("data")'## output.


'## get the table object from the worksheet
Set tbl = wrksht_in.ListObjects("Table14")  '## input
Set tb2 = wrksht_out.ListObjects("Table2") '## output.

'## delete output table data
If Not tb2.DataBodyRange Is Nothing Then
    tb2.DataBodyRange.Delete
End If

'## count the row and col of input table

With tbl.DataBodyRange
     tRows = .Rows.Count
     tCols = .Columns.Count
End With

'## check every case of the input table (only the data part)
For j = 2 To tRows '## parse all row from row 2 (header are not checked)
    For i = 5 To tCols '## parse all column from col 5 (first col will be copied in each record)
        If IsEmpty(tbl.Range.Cells(j, i).Value) = False Then
            '## if there is time enetered create a new row in table2 by using the first colmn of the selected cell row and cell header plus some formula
            Set oNewRow = tb2.ListRows.Add(AlwaysInsert:=True)
            oNewRow.Range.Cells(1, 1).Value = tbl.Range.Cells(j, 1).Value
            oNewRow.Range.Cells(1, 2).Value = tbl.Range.Cells(j, 2).Value
            oNewRow.Range.Cells(1, 3).Value = tbl.Range.Cells(j, 3).Value
            oNewRow.Range.Cells(1, 4).Value = tbl.Range.Cells(1, i).Value
            oNewRow.Range.Cells(1, 5).Value = tbl.Range.Cells(j, i).Value
            oNewRow.Range.Cells(1, 6).Formula = "=WEEKNUM([@Date])"
            oNewRow.Range.Cells(1, 7).Formula = "=YEAR([@Date])"
            oNewRow.Range.Cells(1, 8).Formula = "=MONTH([@Date])"
        End If
   Next i
Next j
ThisWorkbook.RefreshAll

'##unblock calculate and screen refresh
Application.ScreenUpdating = True 
Application.Calculate
Application.Calculation = userCalculateSetting

End Sub

0
2017-08-16 10:21



función ReversePivotTable actualizada para que pueda especificar el número de columnas y filas de encabezado

Sub ReversePivotTable()
'   Before running this, make sure you have a summary table with column headers.
'   The output table will have three columns.
    Dim SummaryTable As Range, OutputRange As Range
    Dim OutRow As Long
    Dim r As Long, c As Long

    Dim lngHeaderColumns As Long, lngHeaderRows As Long, lngHeaderLoop As Long

    On Error Resume Next
    Set SummaryTable = ActiveCell.CurrentRegion
    If SummaryTable.Count = 1 Or SummaryTable.Rows.Count < 3 Then
        MsgBox "Select a cell within the summary table.", vbCritical
        Exit Sub
    End If
    SummaryTable.Select

    Set OutputRange = Application.InputBox(prompt:="Select a cell for the 3-column output", Type:=8)
    lngHeaderColumns = Application.InputBox(prompt:="Header Columns")
    lngHeaderRows = Application.InputBox(prompt:="Header Rows")
'   Convert the range
    OutRow = 2
    Application.ScreenUpdating = False
    'OutputRange.Range("A1:D3") = Array("Column1", "Column2", "Column3", "Column4")
    For r = lngHeaderRows + 1 To SummaryTable.Rows.Count
        For c = lngHeaderColumns + 1 To SummaryTable.Columns.Count
            ' loop through all header columns and add to output
            For lngHeaderLoop = 1 To lngHeaderColumns
                OutputRange.Cells(OutRow, lngHeaderLoop) = SummaryTable.Cells(r, lngHeaderLoop)
            Next lngHeaderLoop
            ' loop through all header rows and add to output
            For lngHeaderLoop = 1 To lngHeaderRows
                OutputRange.Cells(OutRow, lngHeaderColumns + lngHeaderLoop) = SummaryTable.Cells(lngHeaderLoop, c)
            Next lngHeaderLoop

            OutputRange.Cells(OutRow, lngHeaderColumns + lngHeaderRows + 1) = SummaryTable.Cells(r, c)
            OutputRange.Cells(OutRow, lngHeaderColumns + lngHeaderRows + 1).NumberFormat = SummaryTable.Cells(r, c).NumberFormat
            OutRow = OutRow + 1
        Next c
    Next r
End Sub

0
2017-12-06 18:02