Consejos de Excel: divida los datos en varias hojas de trabajo/libros de trabajo según el valor de la columna
Al administrar grandes conjuntos de datos en Excel, puede resultar muy beneficioso dividir los datos en varias hojas de trabajo en función de valores de columnas específicos. Este método mejora no sólo la organización de los datos sino que también mejora la legibilidad y facilita el análisis de los datos.
Suponga que tiene un registro de ventas grande que contiene varias entradas, como el nombre del producto y la cantidad vendida del primer trimestre. El objetivo es dividir estos datos en hojas de trabajo separadas según el nombre de cada producto para que el desempeño de las ventas individuales pueda analizarse por separado.
Divida los datos en varias hojas de trabajo según el valor de la columna
Divida los datos en varios libros de trabajo según el valor de la columna con código VBA
Divida los datos en varias hojas de trabajo según el valor de la columna
Normalmente, primero puede ordenar la lista de datos y luego copiarlos y pegarlos uno por uno en otras hojas de trabajo nuevas. Pero esto necesitará paciencia para copiar y pegar repetidamente. En esta sección, presentaremos dos métodos sencillos para abordar de manera eficiente esta tarea en Excel, ahorrándole tiempo y reduciendo la posibilidad de errores.
Divida los datos en varias hojas de trabajo según el valor de la columna con código VBA
1. Mantenga presionado el ALT + F11 teclas para abrir el Microsoft Visual Basic para aplicaciones ventana.
2. Hacer clic en recuadro > Móduloy pegue el siguiente código en la ventana del módulo.
Sub Splitdatabycol()
'updateby Extendoffice
Dim lr As Long
Dim ws As Worksheet
Dim vcol, i As Integer
Dim icol As Long
Dim myarr As Variant
Dim title As String
Dim titlerow As Integer
Dim xTRg As Range
Dim xVRg As Range
Dim xWSTRg As Worksheet
Dim xWS As Worksheet
On Error Resume Next
Set xTRg = Application.InputBox("Please select the header rows:", "Kutools for Excel", "", Type:=8)
If TypeName(xTRg) = "Nothing" Then Exit Sub
Set xVRg = Application.InputBox("Please select the column you want to split data based on:", "Kutools for Excel", "", Type:=8)
If TypeName(xVRg) = "Nothing" Then Exit Sub
vcol = xVRg.Column
Set ws = xTRg.Worksheet
lr = ws.Cells(ws.Rows.Count, vcol).End(xlUp).Row
title = xTRg.AddressLocal
titlerow = xTRg.Cells(1).Row
icol = ws.Columns.Count
ws.Cells(1, icol) = "Unique"
Application.DisplayAlerts = False
If Not Evaluate("=ISREF('xTRgWs_Sheet!A1')") Then
Sheets.Add(after:=Worksheets(Worksheets.Count)).Name = "xTRgWs_Sheet"
Else
Sheets("xTRgWs_Sheet").Delete
Sheets.Add(after:=Worksheets(Worksheets.Count)).Name = "xTRgWs_Sheet"
End If
Set xWSTRg = Sheets("xTRgWs_Sheet")
xTRg.Copy
xWSTRg.Paste Destination:=xWSTRg.Range("A1")
ws.Activate
For i = (titlerow + xTRg.Rows.Count) To lr
On Error Resume Next
If ws.Cells(i, vcol) <> "" And Application.WorksheetFunction.Match(ws.Cells(i, vcol), ws.Columns(icol), 0) = 0 Then
ws.Cells(ws.Rows.Count, icol).End(xlUp).Offset(1) = ws.Cells(i, vcol)
End If
Next
myarr = Application.WorksheetFunction.Transpose(ws.Columns(icol).SpecialCells(xlCellTypeConstants))
ws.Columns(icol).Clear
For i = 2 To UBound(myarr)
ws.Range(title).AutoFilter field:=vcol, Criteria1:=myarr(i) & ""
If Not Evaluate("=ISREF('" & myarr(i) & "'!A1)") Then
Set xWS = Sheets.Add(after:=Worksheets(Worksheets.Count))
xWS.Name = myarr(i) & ""
Else
xWS.Move after:=Worksheets(Worksheets.Count)
End If
xWSTRg.Range(title).Copy
xWS.Paste Destination:=xWS.Range("A1")
ws.Range("A" & (titlerow + xTRg.Rows.Count) & ":A" & lr).EntireRow.Copy xWS.Range("A" & (titlerow + xTRg.Rows.Count))
Sheets(myarr(i) & "").Columns.AutoFit
Next
xWSTRg.Delete
ws.AutoFilterMode = False
ws.Activate
Application.DisplayAlerts = True
End Sub3. Entonces presione F5 para ejecutar el código y aparecerá un cuadro emergente para recordarle que seleccione la fila del encabezado y luego haga clic en OK. Ver captura de pantalla:
4. En el segundo cuadro emergente, seleccione los datos de la columna en función de los cuales desea dividir y luego haga clic en OK. Ver captura de pantalla:
5. Todos los datos de la hoja de trabajo activa se dividen en varias hojas de trabajo según los valores de las columnas. Las hojas de trabajo resultantes reciben nombres de acuerdo con los valores en las celdas divididas y se colocan al final del libro. Ver captura de pantalla:
Divida los datos en varias hojas de trabajo según el valor de la columna con Kutools para Excel
Kutools for Excel trae característica inteligente – Dividir datos directamente en su entorno de Excel. Dividir datos en varias hojas de trabajo ya no es un desafío. Nuestra herramienta intuitiva divide automáticamente su conjunto de datos según el valor de columna elegido o el recuento de filas, lo que garantiza que cada pieza de información esté exactamente donde la necesita. Dígale adiós a la tediosa tarea de organizar manualmente sus hojas de cálculo y adopte una forma más rápida y sin errores de administrar sus datos.
Después de instalar Kutools for Excel, seleccione el rango de datos y luego haga clic en Kutools Más > Dividir datos para abrir el Dividir datos en varias hojas de trabajo caja de diálogo.
- Seleccione Columna específica opción en el Dividir basado en y elija el valor de la columna en función del cual desea dividir los datos en la lista desplegable.
- Si sus datos tienen encabezados y desea insertarlos en cada nueva hoja de trabajo dividida, verifique Mis datos tienen encabezados opción. (Puede especificar el número de filas de encabezado según sus datos. Por ejemplo, si sus datos contienen dos encabezados, escriba 2).
- Luego, puede especificar los nombres de las hojas de trabajo divididas, bajo el Nombre de la nueva hoja de trabajo sección, especifique la regla de nombres de hojas de trabajo en la lista desplegable Reglas, puede agregar la Prefijo or Sufijo para los nombres de las hojas también.
- Haga clic en el OK botón. Ver captura de pantalla:
Ahora, los datos de la hoja de trabajo se dividen en varias hojas de trabajo en un nuevo libro.
Divida los datos en varios libros de trabajo según el valor de la columna con código VBA
En ocasiones, en lugar de dividir los datos en varias hojas de trabajo, puede resultar más beneficioso dividir los datos en libros de trabajo separados según una columna clave. Aquí hay una guía paso a paso sobre cómo usar el código VBA para automatizar el proceso de dividir datos en varios libros de trabajo según un valor de columna específico.
1. Mantenga presionado el ALT + F11 teclas para abrir el Microsoft Visual Basic para aplicaciones ventana.
2. Hacer clic en recuadro > Móduloy pegue el siguiente código en el Ventana de módulo.
Sub SplitDataByColToWorkbooks()
' Updateby Extendoffice
Dim lr As Long
Dim ws As Worksheet
Dim vcol, i As Integer
Dim myarr As Variant
Dim title As String
Dim titlerow As Integer
Dim xTRg As Range
Dim xVRg As Range
Dim xWS As Workbook
Dim savePath As String
' Set the directory to save new workbooks
savePath = "C:\Users\AddinsVM001\Desktop\multiple files\" ' Modify this path as needed
Application.DisplayAlerts = False
Set xTRg = Application.InputBox("Please select the header rows:", "Kutools for Excel", Type:=8)
If TypeName(xTRg) = "Nothing" Then Exit Sub
Set xVRg = Application.InputBox("Please select the column you want to split data based on:", "Kutools for Excel", Type:=8)
If TypeName(xVRg) = "Nothing" Then Exit Sub
vcol = xVRg.Column
Set ws = xTRg.Worksheet
lr = ws.Cells(ws.Rows.Count, vcol).End(xlUp).Row
title = xTRg.Address(False, False)
titlerow = xTRg.Row
ws.Columns(vcol).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=ws.Cells(1, ws.Columns.Count), Unique:=True
myarr = Application.Transpose(ws.Cells(1, ws.Columns.Count).Resize(ws.Cells(ws.Rows.Count, ws.Columns.Count).End(xlUp).Row).Value)
ws.Cells(1, ws.Columns.Count).Resize(ws.Cells(ws.Rows.Count, ws.Columns.Count).End(xlUp).Row).ClearContents
For i = 2 To UBound(myarr)
Set xWS = Workbooks.Add
ws.Range(title).AutoFilter Field:=vcol, Criteria1:=myarr(i)
ws.Range("A" & titlerow & ":A" & lr).SpecialCells(xlCellTypeVisible).EntireRow.Copy
xWS.Sheets(1).Cells(1, 1).PasteSpecial Paste:=xlPasteAll
xWS.SaveAs Filename:=savePath & myarr(i) & ".xlsx"
xWS.Close SaveChanges:=False
Next i
ws.AutoFilterMode = False
Application.DisplayAlerts = True
ws.Activate
End Sub
3. Entonces presione F5 para ejecutar el código y aparecerá un cuadro emergente para recordarle que seleccione la fila del encabezado y luego haga clic en OK. Ver captura de pantalla:
4. En el segundo cuadro emergente, seleccione los datos de la columna en función de los cuales desea dividir y luego haga clic en OK. Ver captura de pantalla:
5. Después de dividir, todos los datos de la hoja de trabajo activa se dividen en varios libros de trabajo según los valores de las columnas. Todos los libros divididos se guardan en la carpeta que especificó. Ver captura de pantalla:
Artículos relacionados:
- Divida los datos en varias hojas de trabajo por recuento de filas
- Dividir de manera eficiente un gran rango de datos en varias hojas de cálculo de Excel en función de un recuento de filas específico puede optimizar la administración de datos. Por ejemplo, dividir un conjunto de datos cada 5 filas en varias hojas puede hacerlo más manejable y organizado. Esta guía ofrece dos métodos prácticos para realizar esta tarea de forma rápida y sencilla.
- Fusionar dos o más tablas en una según las columnas clave
- Suponiendo que tiene tres tablas en un libro de trabajo, ahora, desea fusionar estas tablas en una tabla basada en las columnas clave correspondientes para obtener el resultado como se muestra a continuación. Esta puede ser una tarea problemática para la mayoría de nosotros, pero, por favor, no se preocupe, en este artículo, presentaré algunos métodos para resolver este problema.
- Dividir cadenas de texto por delimitador en varias filas
- Normalmente, puede usar la función Texto a columna para dividir el contenido de la celda en varias columnas mediante un delimitador específico, como coma, punto, punto y coma, barra oblicua, etc. Pero, a veces, es posible que necesite dividir el contenido de la celda delimitada en varias filas y repita los datos de otras columnas como se muestra a continuación. ¿Tiene alguna buena manera de hacer frente a esta tarea en Excel? Este tutorial presentará algunos métodos efectivos para completar este trabajo en Excel.
- Divida el contenido de las celdas de varias líneas en filas/columnas separadas
- Supongamos que tiene contenido de celda de varias líneas que está separado por Alt + Enter, y ahora necesita dividir el contenido de varias líneas en filas o columnas separadas, ¿qué puede hacer? En este artículo, aprenderá cómo dividir rápidamente el contenido de una celda de varias líneas en filas o columnas separadas.
Las mejores herramientas de productividad de oficina
Mejore sus habilidades de Excel con Kutools for Excel y experimente la eficiencia como nunca antes. Kutools for Excel ofrece más de 300 funciones avanzadas para aumentar la productividad y ahorrar tiempo. Haga clic aquí para obtener la función que más necesita...
Office Tab lleva la interfaz con pestañas a Office y hace que su trabajo sea mucho más fácil
- Habilite la edición y lectura con pestañas en Word, Excel, PowerPoint, Publisher, Access, Visio y Project.
- Abra y cree varios documentos en nuevas pestañas de la misma ventana, en lugar de en nuevas ventanas.
- ¡Aumenta su productividad en un 50% y reduce cientos de clics del mouse todos los días!
