while macro loop for code excel vba excel-vba

for - loops macros excel



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

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?

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

El aplanamiento de una matriz de datos (también conocida como tabla ) se puede lograr con una fórmula de matriz¹ y dos fórmulas estándar.

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.

Formulas Las fórmulas de matriz deben completarse con Ctrl + Shift + 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. Consulte Pautas y ejemplos de fórmulas de matriz para obtener más información.

En Excel 2013, es necesario seguir los siguientes pasos:

  • seleccionar datos y convertir a la tabla ( Insertar -> Tabla )
  • llamar a Query Editor para la tabla ( Power Query -> From Table )
  • seleccionar columnas que contienen años
  • en el menú contextual, seleccione el comando '' Unpivot Columns ''.

Oficina de soporte: columnas sin pivote (Power Query)

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.

La solución VBA puede no ser aceptable en algunas situaciones (p. Ej., No puede incrustar macro por 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)
  • Comentario 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: Objetivo : 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.
  • Comentario en M3: Objetivo : 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 supera 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.
  • Comentario en R4: Opcional : utilice la columna K para la gestión 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.

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.

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

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