Conseils Excel : Diviser les données en plusieurs feuilles de calcul / classeurs selon la valeur d’une colonne
Lorsque vous gérez de grands jeux de données dans Excel, il peut être très utile de diviser les données en plusieurs feuilles de calcul en fonction des valeurs d’une colonne spécifiée. Cette méthode améliore non seulement l’organisation des données, mais renforce également leur lisibilité et facilite l’analyse.
Imaginez que vous disposiez d’un vaste registre de ventes comprenant de nombreuses entrées, telles que le nom du produit et la quantité vendue au premier trimestre. L’objectif ? Répartir ces données en feuilles de calcul distinctes, une par produit, pour analyser individuellement les performances commerciales.
Diviser les données en plusieurs feuilles de calcul selon la valeur d’une colonne
Diviser les données en plusieurs classeurs selon la valeur d’une colonne avec un code VBA
Diviser les données en plusieurs feuilles de calcul selon la valeur d’une colonne
Généralement, vous pouvez trier préalablement votre liste de données, puis les copier-coller une par une dans de nouvelles feuilles de calcul. Toutefois, cette approche exige beaucoup de patience en raison du grand nombre d’opérations de copier-coller nécessaires. Dans cette section, nous vous présentons deux méthodes simples pour accomplir cette tâche efficacement dans Excel, vous permettant ainsi de gagner du temps et de réduire les risques d’erreurs.
Diviser les données en plusieurs feuilles de calcul selon la valeur d’une colonne avec un code VBA
1. Maintenez les touches ALT + F11 enfoncées pour ouvrir la fenêtre Microsoft Visual Basic pour Applications.
2. Cliquez sur Insertion > Module, puis collez le code suivant dans la fenêtre du 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. Appuyez ensuite sur la touche F5 pour exécuter le code. Une boîte de dialogue s’affiche alors afin que vous sélectionniez la ligne d’en-tête ; cliquez ensuite sur OK. Voir la capture d’écran :
4. Dans la deuxième boîte de dialogue, veuillez sélectionner les données de la colonne que vous souhaitez utiliser comme critère de division, puis cliquez sur OK. Voir la capture d’écran :
5. Toutes les données de la feuille active sont réparties dans plusieurs feuilles de calcul, en fonction des valeurs de la colonne sélectionnée. Les nouvelles feuilles prennent pour nom les valeurs figurant dans les cellules divisées et sont insérées à la fin du classeur. Voir la capture d’écran :
Diviser les données en plusieurs feuilles de calcul selon la valeur d’une colonne avec Kutools pour Excel
Kutools pour Excel propose une fonctionnalité intelligente : Diviser les données, directement dans votre environnement Excel. Fractionner des données en plusieurs feuilles de calcul n’est désormais plus un défi ! Notre outil intuitif divise automatiquement votre jeu de données selon la valeur choisie dans une colonne ou selon un nombre de lignes, garantissant que chaque élément se trouve exactement là où vous en avez besoin. Dites adieu à la fastidieuse tâche d’organisation manuelle de vos feuilles de calcul et adoptez une méthode plus rapide, sans erreur, pour gérer vos données.
Après avoir installé Kutools pour Excel, sélectionnez la plage de données, puis cliquez sur KUTOOLS PLUS > Diviser les données pour ouvrir la boîte de dialogue Diviser les données en plusieurs feuilles de calcul.
- Sélectionnez l’option Spécifier la colonne dans la section Critère de division, puis choisissez, dans la liste déroulante, la valeur de la colonne sur laquelle vous souhaitez fractionner les données.
- Si vos données comportent des en-têtes et que vous souhaitez les inclure dans chaque nouvelle feuille fractionnée, cochez l’option Inclure les titres. (Vous pouvez indiquer le nombre de lignes d’en-tête en fonction de votre jeu de données. Par exemple, si vos données contiennent deux lignes d’en-tête, saisissez 2.)
- Vous pouvez ensuite définir le critère de fractionnement « Nom de la feuille de calcul ». Dans la section Nom des feuilles de calcul créées, sélectionnez la règle « Nom de la feuille de calcul » dans la liste déroulante « Règles ». Vous pouvez également ajouter un Préfixe ou un Suffixe aux noms des feuilles.
- Cliquez sur le bouton OK. Voir la capture d’écran :
Les données de la feuille de calcul sont désormais réparties sur plusieurs feuilles dans un nouveau classeur.
Diviser les données en plusieurs classeurs selon la valeur d’une colonne avec un code VBA
Parfois, plutôt que de diviser les données en plusieurs feuilles de calcul, il est plus avantageux de les répartir dans des classeurs distincts selon une colonne clé. Découvrez notre guide pas à pas pour automatiser cette opération à l’aide d’un code VBA, en fractionnant vos données en plusieurs classeurs selon les valeurs d’une colonne spécifiée.
1. Maintenez la touche ALT + F11 enfoncée pour ouvrir la fenêtre Microsoft Visual Basic pour Applications.
2. Cliquez sur Insertion > Module, puis collez le code suivant dans la 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. Appuyez ensuite sur la touche F5 pour exécuter le code. Une boîte de dialogue s’affiche alors afin que vous sélectionniez la ligne d’en-tête ; cliquez ensuite sur OK. Voir la capture d’écran :
4. Dans la deuxième boîte de dialogue, veuillez sélectionner les données de la colonne que vous souhaitez utiliser comme critère de division, puis cliquez sur OK. Voir la capture d’écran :
5. Après le fractionnement, toutes les données de la feuille active sont réparties en plusieurs classeurs selon les valeurs de la colonne. Tous les classeurs séparés sont enregistrés dans le dossier que vous avez spécifié. Voir la capture d’écran :
Articles associés :
- Diviser les données en plusieurs feuilles de calcul par nombre de lignes
- Diviser efficacement un grand Plage de données en plusieurs feuilles Excel selon un nombre spécifique de lignes peut rationaliser la gestion des données. Par exemple, fractionner un jeu de données toutes les 5 lignes en plusieurs feuilles le rend plus facile à gérer et mieux organisé. Ce guide propose deux méthodes pratiques pour accomplir cette tâche rapidement et facilement.
- Fusionner deux tableaux ou plus en un seul sur la base de Colonne clé
- Supposons que vous disposiez de trois tableaux dans un classeur et que vous souhaitiez désormais les fusionner en un seul tableau en vous appuyant sur les Colonne clé correspondants afin d’obtenir le résultat illustré dans la capture d’écran ci-dessous. Cette tâche peut s’avérer délicate pour la plupart d’entre nous, mais ne vous inquiétez pas : cet article présente plusieurs méthodes permettant de résoudre ce problème.
- Fractionner des chaînes de texte délimitées en plusieurs lignes
- Généralement, vous pouvez utiliser la fonctionnalité « Convertir le texte en colonnes » pour fractionner le contenu d’une cellule en plusieurs colonnes à l’aide d’un délimiteur spécifique, comme une virgule, un point, un point-virgule, une barre oblique, etc. Toutefois, il arrive parfois que vous deviez diviser ce même contenu délimité en plusieurs lignes tout en répétant les données des autres colonnes, comme le montre la capture d’écran ci-dessous. Disposez-vous de solutions efficaces pour réaliser cette opération dans Excel ? Ce tutoriel vous présente plusieurs méthodes performantes pour accomplir cette tâche facilement.
- Fractionner le contenu multiligne d’une cellule en lignes ou colonnes distinctes
- Supposons que vous ayez un contenu multiligne dans une cellule, séparé par Alt + Entrée, et que vous souhaitiez désormais le fractionner en lignes ou colonnes distinctes. Que faire dans ce cas ? Cet article vous explique comment diviser rapidement ce contenu multiligne en lignes ou colonnes séparées.
Meilleurs outils de productivité Office
Boostez vos compétences Excel avec Kutools pour Excel et découvrez une efficacité inégalée.Kutools pour Excel propose plus de 300 fonctionnalités avancées pour améliorer votre 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 rend votre travail bien plus facile
- Activez 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 vous fait économiser des centaines de clics de souris chaque jour !
Tous les compléments Kutools. Un seul installateur
Kutools for Office regroupe les compléments pour Excel, Word, Outlook et PowerPoint, ainsi que Office Tab Pro, ce qui en fait le choix idéal pour les équipes travaillant à travers les applications Office.
- Suite tout-en-un— Compléments Excel, Word, Outlook et PowerPoint + Office Tab Pro
- Un seul installateur, une seule licence— installation en quelques minutes (compatible MSI)
- Fonctionne mieux ensemble— productivité optimisée dans toutes les applications Office
- Essai gratuit de 30 jours avec toutes les fonctionnalités— aucune inscription, aucune carte bancaire
- Meilleur rapport qualité-prix— économisez par rapport à l’achat de compléments individuels