Comment diviser un grand tableau en 1 clic sur Excel ?

Dans ce tutoriel, je vais vous montrer comment diviser un grand tableau Excel en plusieurs petits tableaux dans des onglets différents en utilisant VBA.

Cette méthode va nous permettre d'organiser et d'analyser efficacement des données, en simplifiant la lecture des informations.

Téléchargement

Vous pouvez télécharger le fichier d'exemple de cet article en cliquant sur le lien suivant :

Tutoriel Vidéo

1. Pourquoi diviser un tableau ?

Lorsque nous avons un grand tableau contenant une grande quantité de données, il peut être difficile de trouver et d'analyser rapidement les informations dont nous avons besoin.

Diviser celui-ci en plusieurs onglets distincts présente de nombreux avantages :

• Nous pouvons ainsi mieux organiser nos données,
• Faciliter la navigation et l'analyse à l’intérieur de ces données,
• Améliorer l'efficacité et la capacité à obtenir des informations,
• Etc.

Bien entendu, pour effectuer cette opération, nous allons devoir mettre en place une petite macro commande en VBA.

Il s’agit du langage de programmation intégré dans les applications de la suite office, qui permet de décupler les possibilités offertes par ces dernières, et en particulier sur Excel.

Ici, nous n’allons mettre en place que des commandes relativement simples, ce qui vous permettra de suivre ce tutoriel dans de bonnes conditions, même si vous n’avez de connaissances en VBA.

D’autant plus que je vais vous expliquer chacune de ces commandes dans le détail.

Mais si nous souhaitons en savoir davantage sur le VBA, n’hésitez pas à récupérer mon livre dédié à l’apprentissage du VBA en cliquant sur ce lien.

2. Création de la macro

Tout d’abord, pour créer notre macro, nous allons devoir ouvrir l’éditeur VBA, en utilisant le raccourci clavier [Alt]+[F11].

Cela va donc ouvrir ce dernier, directement sur le projet en cours, c’est-à-dire dans notre classeur Excel.

Pour ajouter une macro, nous allons maintenant devoir insérer une feuille de code, appelée un Module.

Pour cela, nous nous rendons dans le menu « Insertion » et nous cliquons sur « Module ».

Cela étant fait, pour créer la macro, nous saisissons le mot-clé « Sub », suivi du nom de la macro « DiviserTableau » », en saisissant la ligne suivante :

Sub DiviserTableau()

Lorsque nous validons en appuyant sur la touche [Entrée], Excel ajoute la ligne « End Sub », qui vient marquer la fin de la macro.

Maintenant, tout ce que nous allons saisir entre ces deux lignes sera exécuté à chaque fois que nous allons appeler la macro « DiviserTableau ».

Dans cette macro, nous allons commencer par déclarer une variable nommée « pTableau » que nous allons typer en tant que « Range », c'est-à-dire une plage de cellules. Nous allons ensuite utiliser la méthode « CurrentRegion » de l'objet « ActiveCell » pour récupérer la plage correspondante à toutes les cellules du tableau dans lequel se trouve la cellule sélectionnée.

    Dim pTableau As Range
    Set pTableau = ActiveCell.CurrentRegion  

Ensuite, nous allons déclarer une seconde variable nommée « listeValeurs » qui sera utilisée pour stocker les valeurs uniques de la colonne du tableau.

Nous allons également déclarer une variable « c » de type « Range » qui va nous permettre de passer en revue toutes les cellules de la colonne active du tableau :

    Dim listeValeurs As String
    Dim c As Range 

Nous allons maintenant utiliser une boucle « For Each » pour parcourir chaque cellule de la colonne active du tableau. Une boucle « For Each » permet en effet d'itérer sur chaque élément d'une collection ou d'un ensemble de données. Dans notre cas, nous utiliserons cette boucle pour itérer sur chaque cellule de la colonne active.

Comme nous l’avons vu dans le tutoriel qui lui est dédié, la boucle « For Each » se compose de trois parties : le mot clé « For Each », une variable qui permet de stocker chaque élément de la collection (la variable « c » que nous venons de déclarer), et la collection elle-même (la colonne active du tableau):

Pour obtenir la liste des cellules correspondantes à cette colonne, nous utilisons la fonction « Intersect », qui permet d’obtenir la liste des cellules situées à l'intersection de la plage du tableau (pTableau) et la colonne de la cellule active (ActiveCell.EntireColumn). Cela garantit que nous itérons uniquement sur les cellules de la colonne active du tableau.

For Each c In Intersect(rngTableau,  ActiveCell.EntireColumn)
Next

À l’intérieur de cette boucle, nous utilisons une instruction « If » pour vérifier si la valeur de la cellule n'est pas déjà présente dans la variable « listeValeurs ».

À l’instar de la fonction Excel SI(), l’instruction VBA « If » permet en effet d'exécuter un bloc de code conditionnel en fonction d'une condition spécifiée. Cela nous permet de prendre des décisions et de contrôler l'exécution du code en fonction de différentes situations.

Puis, pour vérifier si la valeur est déjà stockée dans la variable « listeValeurs », nous pouvons nous reposer sur la fonction « InStr », qui permet de rechercher la position d’une chaîne à l’intérieur d’une autre chaîne.

Si celle-ci n’y figure pas, alors « InStr » renvoie 0.

Nous pouvons alors ajouter la valeur à la chaîne « listeValeurs » en utilisant l'esperluette pour concaténer les valeurs avec un point-virgule à la fin qui permettra de séparer toutes les valeurs.

If InStr(1, listeValeurs, c) = 0  Then
    listeValeurs = listeValeurs & c  & ";"
End If

Cela nous permet ainsi de construire la chaîne « listeValeurs » qui contient toutes les valeurs uniques de la colonne active, séparées par des points-virgules.

Maintenant, ce qu’il y a à savoir du résultat obtenu, c’est que la première valeur retournée sera le nom de l’en-tête du tableau.

Pour l’exclure, nous allons simplement décaler la plage des cellules que nous passons en revue d’une cellule vers le bas en ajoutant l’instruction « Offset » :

For Each c In Intersect(rngTableau,  ActiveCell.EntireColumn).Offset(1)

Puis, nous allons ajouter une seconde condition pour enregistrer la valeur de la cellule dans la variable « listeValeurs » qui va consister à vérifier que la valeur de « c » n’est pas nulle :

If InStr(1, listeValeurs, c) = 0  And c <> " " Then

Ensuite, nous déclarons une variable « positionChamp » de type « Integer » qui représentera la position du champ (c’est-à-dire de la colonne) que nous filtrerons ultérieurement. La valeur de « positionChamp » est déterminée en soustrayant la colonne du tableau à la colonne de la cellule sélectionnée, puis en ajoutant 1 pour compenser la numérotation des colonnes en VBA. Voici le code correspondant :

    Dim positionChamp As Integer
    positionChamp = ActiveCell.Column - pTableau.Column  + 1

Ensuite, nous utilisons une nouvelle boucle « For Each » pour itérer sur chaque valeur séparée par des points-virgules dans la chaîne « listeValeurs ».

Pour décomposer la chaîne à l'aide des points-virgules, nous utilisons la fonction « Split » qui permet de diviser une chaîne en un tableau contenant des sous-chaînes basées sur un délimiteur spécifié.

For Each valeur In Split(listeValeurs,  ";")
Next

À l'intérieur de la boucle, nous pouvons utiliser la variable « valeur » pour effectuer des opérations spécifiques ou des traitements supplémentaires sur chaque valeur extraite de la chaîne d'origine.

Lors de chacune de ces itérations, nous créons une nouvelle feuille en utilisant la méthode « Add » de l'objet « Sheets ». Pour placer cette feuille en dernière position du classeur, nous pouvons utiliser l'argument facultatif « After » pour indiquer que la nouvelle feuille doit être insérée après la dernière feuille existante.

Nous attribuons également à la nouvelle feuille le nom de la valeur correspondante.

        Set feuille =  Sheets.Add(After:=Sheets(Sheets.Count))
        feuille.Name = valeur

Ensuite, nous appliquons un filtre à la plage du tableau en utilisant la méthode « AutoFilter » et en spécifiant le numéro de champ (colonne) à filtrer ("positionChamp") et la valeur à utiliser comme critère de filtrage ("valeur").

        pTableau.AutoFilter Field:=positionChamp,  Criteria1:=valeur

Nous copions ensuite les cellules filtrées en utilisant la méthode « Copy » de la plage du tableau filtrée.

        pTableau.SpecialCells(xlCellTypeVisible).Copy 

Maintenant, nous collons les données copiées dans la nouvelle feuille en utilisant la méthode « PasteSpecial » et en spécifiant l'argument « xlPasteAll » pour coller toutes les caractéristiques (données, formules, mise en forme, etc.) :

        feuille.Range("A1").PasteSpecial  xlPasteAll 

Enfin, nous supprimons le filtre en utilisant à nouveau la méthode « AutoFilter » sur la plage du tableau pour afficher toutes les données et passer à l’analyser de la valeur suivante :

        pTableau.AutoFilter 

3. Exécuter la macro pour diviser le tableau en onglets

Maintenant que la macro est terminée, nous allons pouvoir la tester.

Pour cela, revenons sur la feuille contenant le tableau et sélectionnons l’une des cellules de la colonne sur laquelle nous souhaitons effectuer la division.

Ensuite, nous appuyons sur les touches [Alt]+[F8], afin d’afficher la boîte de dialogue « Macro » qui répertorie toutes les macros disponibles dans le classeur Excel.

Une fois la boîte de dialogue « Macro » ouverte, nous sélectionnons la macro « DiviserTableau » dans la liste des macros disponibles, puis nous cliquons sur le bouton « Exécuter » (ou double-cliquons sur la macro) pour lancer l'exécution de la macro.

Cela déclenche l'exécution de la macro « DiviserTableau », qui effectue toutes les opérations nécessaires pour diviser le tableau et créer des onglets distincts pour chaque valeur unique de la colonne sélectionnée.