Consejos de Excel: Dividir datos en varias hojas de cálculo / libros según el valor de una columna

Al gestionar grandes conjuntos de datos en Excel, puede ser muy beneficioso dividir los datos en varias hojas de cálculo basadas en valores específicos de una columna. Este método no solo mejora la organización de los datos, sino que también aumenta la legibilidad y facilita el análisis de datos.

Supongamos que tienes un gran registro de ventas que contiene múltiples entradas, como el nombre del producto y la cantidad vendida del primer trimestre. El objetivo es dividir estos datos en hojas de cálculo separadas basadas en cada nombre de producto para que se pueda analizar el rendimiento de las ventas individualmente.

Dividir datos en varias hojas de cálculo basadas en el valor de una columna

• Usando código VBA
• Usando una función poderosa - Kutools para Excel

Dividir datos en varios libros basados en el valor de una columna con código VBA

Dividir datos en varias hojas de cálculo basadas en el valor de una columna

Normalmente, puedes ordenar la lista de datos primero y luego copiar y pegar uno por uno en otras hojas de cálculo nuevas. Pero esto requerirá tu paciencia para copiar y pegar repetidamente. En esta sección, presentaremos dos métodos sencillos para abordar eficientemente esta tarea en Excel, ahorrándote tiempo y reduciendo el potencial de errores.

Dividir datos en varias hojas de cálculo basadas en el valor de una columna con código VBA

1. Mantén presionadas las teclas ALT + F11 para abrir la ventana de Microsoft Visual Basic para Aplicaciones.

2. Haz clic en Insertar > Módulo, y pega 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 Sub

3. Luego, presiona F5 para ejecutar el código, y aparecerá un cuadro de diálogo para recordarte que selecciones la fila de encabezado, y luego haz clic en Aceptar. Ver captura de pantalla:

4. En el segundo cuadro de diálogo, selecciona los datos de la columna que deseas dividir, luego haz clic en Aceptar. Ver captura de pantalla:

5. Todos los datos en la hoja de trabajo activa se dividen en varias hojas de cálculo basadas en los valores de la columna. Las hojas de trabajo resultantes se nombran de acuerdo con los valores en las celdas divididas y se colocan al final del libro. Ver captura de pantalla:

Dividir datos en varias hojas de cálculo basadas en el valor de una columna con Kutools para Excel

Kutools para Excel trae una función inteligente – Dividir Datos directamente a tu entorno de Excel. Dividir datos en varias hojas de cálculo ya no es un desafío. Nuestra herramienta intuitiva divide automáticamente tu conjunto de datos basándose en el valor de columna elegido o el recuento de filas, asegurando que cada pieza de información esté exactamente donde la necesitas. Di adiós a la tediosa tarea de organizar manualmente tus hojas de cálculo y adopta una manera más rápida y libre de errores para gestionar tus datos.

Después de instalar Kutools para Excel, selecciona el rango de datos, y luego haz clic en Kutools Plus > Dividir Datos para abrir el cuadro de diálogo Dividir Datos en varias hojas de cálculo.

1. Selecciona la opción Especificar columna en la sección Dividir basado en, y elige el valor de columna en el que deseas dividir los datos desde la lista desplegable.
2. Si tus datos tienen encabezados y quieres insertarlos en cada nueva hoja de cálculo dividida, marca la opción Mis datos contienen encabezados. (Puedes especificar el número de filas de encabezado según tus datos. Por ejemplo, si tus datos contienen dos encabezados, escribe 2.)
3. Luego puedes especificar los nombres de las hojas de cálculo divididas, bajo la sección Nombre de las hojas de cálculo nuevas, especifica la regla de nombres de las hojas de trabajo desde la lista desplegable Reglas, también puedes agregar el Prefijo o Sufijo para los nombres de las hojas.
4. Haz clic en el Aceptar botón. Ver captura de pantalla:
   

Ahora, los datos en la hoja de trabajo se dividen en varias hojas de cálculo en un nuevo libro.

Dividir datos en varios libros basados en el valor de una columna con código VBA

Ocasionalmente, en lugar de dividir los datos en varias hojas de cálculo, puede ser más beneficioso dividir los datos en libros separados basados en 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 basados en un valor específico de una columna.

1. Mantén presionadas las teclas ALT + F11 para abrir la ventana de Microsoft Visual Basic para Aplicaciones.

2. Haz clic en Insertar > Módulo, y pega el siguiente código en la Ventana del 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. Luego, presiona F5 para ejecutar el código, y aparecerá un cuadro de diálogo para recordarte que selecciones la fila de encabezado, y luego haz clic en Aceptar. Ver captura de pantalla:

4. En el segundo cuadro de diálogo, selecciona los datos de la columna en la que deseas dividir, luego haz clic en Aceptar. Ver captura de pantalla:

5. Después de dividir, todos los datos en la hoja de trabajo activa se dividen en varios libros basados en los valores de la columna. Todos los libros divididos se guardan en la carpeta que especificaste. Ver captura de pantalla:

Artículos Relacionados: