Consigli per Excel: Suddividi i dati in più fogli di lavoro / cartelle di lavoro in base al valore della colonna

Quando si gestiscono grandi set di dati in Excel, può essere molto utile suddividere i dati in più fogli di lavoro in base a valori specifici delle colonne. Questo metodo migliora non solo l'organizzazione dei dati, ma aumenta anche la leggibilità e facilita l'analisi dei dati.

Supponiamo di avere un grande registro vendite contenente diverse voci come il nome del prodotto, la quantità venduta nel primo trimestre. L'obiettivo è dividere questi dati in fogli di lavoro separati in base a ciascun nome del prodotto, in modo che le prestazioni di vendita individuali possano essere analizzate separatamente.

Suddividi i dati in più fogli di lavoro in base al valore della colonna

• Utilizzando il codice VBA
• Utilizzando una funzionalità potente - Kutools per Excel

Suddividi i dati in più cartelle di lavoro in base al valore della colonna con il codice VBA

Suddividi i dati in più fogli di lavoro in base al valore della colonna

Normalmente, puoi prima ordinare l'elenco dei dati, quindi copiarli e incollarli uno per uno in altri nuovi fogli di lavoro. Ma questo richiederà molta pazienza per copiare e incollare ripetutamente. In questa sezione, introdurremo due metodi semplici per affrontare efficacemente questo compito in Excel, risparmiandoti tempo e riducendo il potenziale di errori.

Suddividi i dati in più fogli di lavoro in base al valore della colonna con il codice VBA

1. Tieni premuti i tasti ALT + F11 per aprire la finestra Microsoft Visual Basic for Applications.

2. Clicca su Inserisci > Modulo, e incolla il seguente codice nella finestra del Modulo.

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. Poi, premi F5 per eseguire il codice, e apparirà una finestra di dialogo per ricordarti di selezionare la riga dell'intestazione, quindi clicca OK. Vedi screenshot:

4. Nella seconda finestra di dialogo, seleziona i dati della colonna in base ai quali vuoi dividere, quindi clicca OK. Vedi screenshot:

5. Tutti i dati nel foglio di lavoro attivo sono divisi in più fogli di lavoro in base ai valori delle colonne. I fogli di lavoro risultanti sono nominati in base ai valori nelle celle divise e posizionati alla fine della cartella di lavoro. Vedi screenshot:

Suddividi i dati in più fogli di lavoro in base al valore della colonna con Kutools per Excel

Kutools per Excel introduce una funzionalità intelligente – Split Data direttamente nel tuo ambiente Excel. Dividere i dati in più fogli di lavoro non è più una sfida. Il nostro strumento intuitivo divide automaticamente il tuo set di dati in base al valore della colonna scelta o al conteggio delle righe, garantendo che ogni pezzo di informazione sia esattamente dove ti serve. Dì addio al compito noioso di organizzare manualmente i tuoi fogli di calcolo e abbraccia un modo più veloce e privo di errori per gestire i tuoi dati.

Dopo aver installato Kutools per Excel, seleziona l'intervallo di dati, quindi clicca su Kutools Plus > Split Data per aprire la finestra di dialogo Split Data into multiple worksheets.

1. Seleziona l'opzione Specifica colonna nella sezione Split based on, e scegli il valore della colonna in base al quale vuoi dividere i dati dall'elenco a discesa.
2. Se i tuoi dati contengono intestazioni e desideri inserirle in ogni nuovo foglio di lavoro diviso, seleziona l'opzione I dati contengono intestazioni. (Puoi specificare il numero di righe di intestazione in base ai tuoi dati. Ad esempio, se i tuoi dati contengono due intestazioni, digita 2.)
3. Quindi puoi specificare i nomi dei fogli di lavoro divisi, sotto la sezione New worksheets name, specifica la regola per i nomi dei fogli di lavoro dall'elenco a discesa Rules, puoi aggiungere anche un Prefisso o Suffisso per i nomi dei fogli.
4. Clicca il pulsante OK . Vedi screenshot:
   

Ora, i dati nel foglio di lavoro sono divisi in più fogli di lavoro in una nuova cartella di lavoro.

Suddividi i dati in più cartelle di lavoro in base al valore della colonna con il codice VBA

Occasionalmente, invece di dividere i dati in più fogli di lavoro, potrebbe essere più vantaggioso dividere i dati in cartelle di lavoro separate basate su una colonna chiave. Ecco una guida passo-passo su come utilizzare il codice VBA per automatizzare il processo di divisione dei dati in più cartelle di lavoro in base a un valore specifico della colonna.

1. Tieni premuti i tasti ALT + F11 per aprire la finestra Microsoft Visual Basic for Applications.

2. Clicca su Inserisci > Modulo, e incolla il seguente codice nella finestra del Modulo.

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. Poi, premi F5 per eseguire il codice, e apparirà una finestra di dialogo per ricordarti di selezionare la riga dell'intestazione, quindi clicca OK. Vedi screenshot:

4. Nella seconda finestra di dialogo, seleziona i dati della colonna in base ai quali vuoi dividere, quindi clicca OK. Vedi screenshot:

5. Dopo la divisione, tutti i dati nel foglio di lavoro attivo sono divisi in più cartelle di lavoro in base ai valori delle colonne. Tutte le cartelle di lavoro divise sono salvate nella cartella specificata. Vedi screenshot:

Articoli correlati: