varias - Cómo consolidar datos de múltiples columnas de Excel, todo en una sola columna
extraer datos de varias hojas excel (6)
Guarde su libro de trabajo. Si este código no hace lo que desea, la única manera de regresar es cerrar sin guardar y volver a abrir.
Seleccione los datos que desea enumerar en una columna. Debe ser columnas contiguas. Puede contener celdas en blanco.
Presione Alt + F11 para abrir el VBE
Presione Control + R para ver el Explorador de proyectos
Navegue al proyecto para su libro de trabajo y elija Insertar - Módulo
Pegue este código en el panel de códigos
Sub MakeOneColumn()
Dim vaCells As Variant
Dim vOutput() As Variant
Dim i As Long, j As Long
Dim lRow As Long
If TypeName(Selection) = "Range" Then
If Selection.Count > 1 Then
If Selection.Count <= Selection.Parent.Rows.Count Then
vaCells = Selection.Value
ReDim vOutput(1 To UBound(vaCells, 1) * UBound(vaCells, 2), 1 To 1)
For j = LBound(vaCells, 2) To UBound(vaCells, 2)
For i = LBound(vaCells, 1) To UBound(vaCells, 1)
If Len(vaCells(i, j)) > 0 Then
lRow = lRow + 1
vOutput(lRow, 1) = vaCells(i, j)
End If
Next i
Next j
Selection.ClearContents
Selection.Cells(1).Resize(lRow).Value = vOutput
End If
End If
End If
End Sub
Presione F5 para ejecutar el código
Digamos que tengo una hoja de Excel con 4 columnas de datos y 20,000 filas de datos en cada columna.
¿Cuál es la manera más eficiente de obtenerlo para que todos esos datos se consoliden en una columna (IE: 80,000 filas de datos en la columna A en lugar de 20,000 filas de datos distribuidas en 4 columnas).
Además, cómo implementar esa solución. Lo que quiero decir es que, si su solución no es una "fórmula" sino VBA, ¿cómo implemento esa solución?
¡Gracias!
No mencionó si está usando Excel 2003 o 2007, pero puede tener un problema con el n. ° de filas en Excel 2003 con un tope de 65.536. Si usa 2007, el límite es 1.048.576.
Además, ¿puedo preguntar cuál es su objetivo final para su análisis? Si necesita realizar muchos cálculos estadísticos sobre sus datos, le recomendaría que se mude fuera del entorno de Excel a algo que sea más apropiado para la manipulación y análisis de datos, como R.
Hay una variedad de opciones para conectar R a Excel, incluyendo
Independientemente de lo que elija usar para mover datos dentro / fuera de R, el código para cambiar de formato ancho a largo es bastante trivial. Disfruto de la función de melt() del paquete de remodelación . Ese código se vería así:
library(reshape)
#Fake data, 4 columns, 20k rows
df <- data.frame(foo = rnorm(20000)
, bar = rlnorm(20000)
, fee = rnorm(20000)
, fie = rlnorm(20000)
)
#Create new object with 1 column, 80k rows
df.m <- melt(df)
Desde allí, puede realizar cualquier cantidad de operaciones estadísticas o gráficas. Si usa el complemento RExcel anterior, puede iniciar todo esto y ejecutarlo dentro de Excel. La comunidad R es muy activa y puede ayudar a resolver todas las preguntas que pueda tener.
¡Buena suerte!
Aquí es cómo lo hace con algunas fórmulas simples de Excel, y no necesita VBA sofisticado. El truco es usar la fórmula OFFSET. Por favor mira esta hoja de cálculo de ejemplo:
La mejor y simple solución a seguir:
Seleccione el rango de las columnas que desea copiar en una sola columna
Copie el rango de celdas (columnas múltiples)
Abra el Bloc de notas ++
Pegar el rango seleccionado de celdas
Presiona Ctrl + H, reemplaza / t por / n y haz clic en reemplazar todo
todas las columnas múltiples caen bajo una sola columna
ahora copia lo mismo y pega en excel
Solución simple y efectiva para aquellos que no quieren perder tiempo codificando en VBA
Echa un vistazo a Blockspring : necesitas instalar el complemento, pero solo es otra función a la que llamas así:
=BLOCKSPRING("twodee-array-reduce","input_array",D5:F7)
El código fuente y otros detalles están aquí . Si esto no se ajusta y / o desea construir mi solución, puede fork mi función (Python) o utilizar otro lenguaje de scripting admitido ( Ruby , R , JS , etc.).
La formula
=OFFSET(Sheet1!$A$1,MOD(ROW()-1,COUNT(Sheet1!$A$1:$A$20000)),
(ROW()-1)/COUNT(Sheet1!$A$1:$A$20000))
colocado en cada celda de su segundo libro de trabajo recuperará la celda apropiada de la hoja de origen. Sin macros, copia simple de una hoja a otra para reformatear los resultados.
Tendrá que modificar los rangos en la función COUNT para que coincida con la cantidad máxima de filas en la hoja de origen. Ajuste para los encabezados de columna según sea necesario.
Si necesita algo distinto de un 0 para celdas vacías, puede preferir incluir un condicional.
Una secuencia de comandos para reformatear los datos puede ser más eficiente, pero 20k filas ya no es un límite real en un libro de Excel moderno.