Conseils Excel : divisez les données en plusieurs feuilles de calcul/classeurs en fonction de la valeur de la colonne
Lors de la gestion de grands ensembles de données dans Excel, il peut être très avantageux de diviser les données en plusieurs feuilles de calcul en fonction de valeurs de colonnes spécifiques. Cette méthode améliore non seulement l'organisation des données, mais améliore également la lisibilité et facilite l'analyse des données.
Supposons que vous disposiez d'un enregistrement de ventes volumineux contenant plusieurs entrées telles que le nom du produit et la quantité vendue au cours du premier trimestre. L'objectif est de diviser ces données en feuilles de calcul distinctes en fonction du nom de chaque produit afin que les performances de vente individuelles puissent être analysées séparément.
Diviser les données en plusieurs feuilles de calcul en fonction de la valeur de la colonne
Divisez les données en plusieurs classeurs en fonction de la valeur de la colonne avec le code VBA
Diviser les données en plusieurs feuilles de calcul en fonction de la valeur de la colonne
Normalement, vous pouvez d'abord trier la liste de données, puis les copier et les coller une par une dans d'autres nouvelles feuilles de calcul. Mais cela nécessitera votre patience pour copier et coller à plusieurs reprises. Dans cette section, nous présenterons deux méthodes simples pour accomplir efficacement cette tâche dans Excel, vous faisant gagner du temps et réduisant le risque d'erreurs.
Divisez les données en plusieurs feuilles de calcul en fonction de la valeur de la colonne avec le code VBA
1. Maintenez le ALT + F11 clés pour ouvrir le Microsoft Visual Basic pour applications fenêtre.
2. Cliquez insérer > Moduleet collez le code suivant dans la fenêtre Module.
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. Puis appuyez F5 pour exécuter le code, et une boîte de dialogue apparaît pour vous rappeler de sélectionner la ligne d'en-tête, puis de cliquer sur OK. Voir la capture d'écran:
4. Dans la deuxième boîte de dialogue, sélectionnez les données de colonne sur lesquelles vous souhaitez diviser, puis cliquez sur OK. Voir la capture d'écran:
5. Toutes les données de la feuille de calcul active sont divisées en plusieurs feuilles de calcul en fonction des valeurs des colonnes. Les feuilles de calcul résultantes sont nommées en fonction des valeurs des cellules divisées et sont placées à la fin du classeur. Voir capture d'écran :
Divisez les données en plusieurs feuilles de calcul en fonction de la valeur de la colonne avec Kutools for Excel
Kutools for Excel apporte une fonctionnalité intelligente – Diviser les données directement dans votre environnement Excel. Diviser les données en plusieurs feuilles de calcul n'est plus un défi. Notre outil intuitif divise automatiquement votre ensemble de données en fonction de la valeur de colonne ou du nombre de lignes choisi, garantissant que chaque élément d'information se trouve exactement là où vous en avez besoin. Dites adieu à la tâche fastidieuse consistant à organiser manuellement vos feuilles de calcul et adoptez un moyen plus rapide et sans erreur de gérer vos données.
Après l'installation de Kutools for Excel, sélectionnez la plage de données, puis cliquez sur Kutools Plus > Diviser les données ouvrir le Diviser les données en plusieurs feuilles de calcul boite de dialogue.
- Sélectionnez Colonne spécifique option dans la Fractionner basé sur et choisissez la valeur de colonne sur laquelle vous souhaitez diviser les données dans la liste déroulante.
- Si vos données comportent des en-têtes et que vous souhaitez les insérer dans chaque nouvelle feuille de calcul fractionnée, veuillez vérifier Mes données ont des en-têtes option. (Vous pouvez spécifier le nombre de lignes d'en-tête en fonction de vos données. Par exemple, si vos données contiennent deux en-têtes, veuillez saisir 2.)
- Ensuite, vous pouvez spécifier les noms de feuille de calcul fractionnés, sous le Nouveau nom de feuille de calcul , spécifiez la règle des noms de feuille de calcul dans la liste déroulante Règles, vous pouvez ajouter le Préfixe or Suffixe pour les noms de feuille également.
- Cliquez OK bouton. Voir la capture d'écran:
Désormais, les données de la feuille de calcul sont divisées en plusieurs feuilles de calcul dans un nouveau classeur.
Divisez les données en plusieurs classeurs en fonction de la valeur de la colonne avec le code VBA
Parfois, plutôt que de diviser les données en plusieurs feuilles de calcul, il peut être plus avantageux de diviser les données en classeurs distincts basés sur une colonne clé. Voici un guide étape par étape sur la façon d'utiliser le code VBA pour automatiser le processus de fractionnement des données en plusieurs classeurs en fonction d'une valeur de colonne spécifique.
1. Maintenez le ALT + F11 clés pour ouvrir le Microsoft Visual Basic pour applications fenêtre.
2. Cliquez insérer > Moduleet collez le code suivant dans le Fenêtre du module.
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. Puis appuyez F5 pour exécuter le code, et une boîte de dialogue apparaît pour vous rappeler de sélectionner la ligne d'en-tête, puis de cliquer sur OK. Voir la capture d'écran:
4. Dans la deuxième boîte de dialogue, sélectionnez les données de colonne sur lesquelles vous souhaitez diviser, puis cliquez sur OK. Voir la capture d'écran:
5. Après le fractionnement, toutes les données de la feuille de calcul active sont divisées en plusieurs classeurs en fonction des valeurs des colonnes. Tous les classeurs fractionnés sont enregistrés dans le dossier que vous avez spécifié. Voir capture d'écran :
Articles connexes:
- Diviser les données en plusieurs feuilles de calcul par nombre de lignes
- Diviser efficacement une large plage de données en plusieurs feuilles de calcul Excel en fonction d'un nombre de lignes spécifique peut rationaliser la gestion des données. Par exemple, diviser un ensemble de données toutes les 5 lignes en plusieurs feuilles peut le rendre plus gérable et organisé. Ce guide propose deux méthodes pratiques pour accomplir cette tâche rapidement et facilement.
- Fusionner deux ou plusieurs tables en une seule en fonction des colonnes clés
- En supposant que vous ayez trois tables dans un classeur, vous souhaitez maintenant fusionner ces tables en une seule table en fonction des colonnes clés correspondantes pour obtenir le résultat comme illustré ci-dessous. Cela peut être une tâche difficile pour la plupart d'entre nous, mais ne vous inquiétez pas, cet article, je vais vous présenter quelques méthodes pour résoudre ce problème.
- Diviser les chaînes de texte par délimiteur en plusieurs lignes
- Normalement, vous pouvez utiliser la fonction Texte en colonne pour diviser le contenu des cellules en plusieurs colonnes par un délimiteur spécifique, tel qu'une virgule, un point, un point-virgule, une barre oblique, etc. Mais, parfois, vous devrez peut-être diviser le contenu des cellules délimitées en plusieurs lignes et répétez les données des autres colonnes comme illustré ci-dessous. Avez-vous de bons moyens de gérer cette tâche dans Excel ? Ce didacticiel présentera quelques méthodes efficaces pour effectuer ce travail dans Excel.
- Diviser le contenu des cellules multilignes en lignes/colonnes séparées
- Supposons que vous ayez un contenu de cellule multiligne séparé par Alt + Entrée, et que vous deviez maintenant diviser le contenu multiligne en lignes ou colonnes séparées, que pouvez-vous faire ? Dans cet article, vous apprendrez à diviser rapidement le contenu des cellules multilignes en lignes ou colonnes séparées.
Meilleurs outils de productivité bureautique
Améliorez vos compétences Excel avec Kutools for Excel et faites l'expérience d'une efficacité comme jamais auparavant. Kutools for Excel offre plus de 300 fonctionnalités avancées pour augmenter la productivité et gagner du temps. Cliquez ici pour obtenir la fonctionnalité dont vous avez le plus besoin...
Office Tab apporte une interface à onglets à Office et facilite grandement votre travail
- Activer l'édition et la lecture par onglets dans Word, Excel, PowerPoint, Publisher, Access, Visio et Project.
- Ouvrez et créez plusieurs documents dans de nouveaux onglets de la même fenêtre, plutôt que dans de nouvelles fenêtres.
- Augmente votre productivité de 50% et réduit des centaines de clics de souris chaque jour!
