Dans ce tutoriel, je vais vous montrer comment transformer un simple tableau d’interventions techniques en rapport automatique avec un tableau croisé dynamique.
Nous allons obtenir une synthèse claire du chiffre d’affaires par technicien, du nombre d’interventions par priorité, et de l’activité par mois, et tout cela sans écrire de formule compliquée.
Si vous avez déjà perdu du temps à faire des copier-coller, des SOMME.SI.ENS dans tous les sens, ou des tableaux qui deviennent impossibles à modifier dès qu’on ajoute une ligne, ce tutoriel va vous aider.
À la fin, nous saurons créer un tableau croisé dynamique propre, l’actualiser quand de nouvelles données arrivent, et éviter les erreurs classiques qui rendent les TCD difficiles à utiliser.
Et en bonus, nous verrons une petite macro VBA pour actualiser tous les tableaux croisés dynamiques du classeur en un clic.
Téléchargement
Tutoriel Vidéo
1. Présentation
Pour illustrer ce tutoriel, nous allons pouvoir utiliser le tableau suivant dans lequel nous retrouvons le suivi des interventions d’une entreprise de maintenance informatique :
Ici, chaque ligne correspond à une intervention, chaque colonne correspond à une information précise : le technicien, le type d’intervention, la priorité, le statut, la date, la durée et le montant facturé.
Avant de créer le tableau croisé dynamique, nous devons vérifier que notre base est propre. Un TCD fonctionne très bien quand les données sont organisées sous forme de liste.
Cela signifie que nous devons avoir une seule ligne d’en-têtes, aucune colonne vide, aucune cellule fusionnée, aucune ligne de total, et surtout une seule information par colonne.
Par exemple, nous avons une colonne « Date intervention », et non une colonne « Janvier », une colonne « Février » et une colonne « Mars ».
Ce détail est très important, parce que c’est le tableau croisé dynamique qui doit faire le regroupement par mois.
Nous cliquons dans le tableau, puis nous utilisons le raccourci [Ctrl]+[L]. Excel nous propose de créer un tableau structuré.
Nous vérifions que la plage commence bien en A6 et que la case « Mon tableau comporte des en-têtes » est cochée. Ensuite, nous validons avec « OK ».
Nous allons ensuite dans l’onglet « Création de tableau », puis nous nommons le tableau « Interventions ».
Ce nom sera beaucoup plus clair que « Tableau1 ».
L’autre avantage c’est que si nous ajoutons de nouvelles lignes sous le tableau, Excel agrandira automatiquement la source.
2. Créer notre premier tableau croisé dynamique
Maintenant que notre base est propre, nous pouvons créer notre premier tableau croisé dynamique.
Dans ce tutoriel, nous allons voir que la création d’un TCD est vraiment à la portée de tous. Nous allons volontairement partir des bases, avec une méthode simple, pour comprendre la logique des lignes, des colonnes, des valeurs et des filtres.
Mais il faut aussi savoir qu’un tableau croisé dynamique peut aller beaucoup plus loin : segments, chronologies, calculs personnalisés, regroupements avancés, mises en forme, tableaux de bord, actualisations automatiques… sans forcément que cela devienne beaucoup plus compliqué.
C’est pourquoi, si vous souhaitez approfondir le sujet et apprendre à créer des TCD vraiment sur mesure, je vous ai préparé une formation complète que vous trouverez en cliquant ici.
Nous cliquons dans le tableau « Interventions », puis nous nous rendons dans l’onglet « Insertion ».
Ensuite, nous cliquons sur « Tableau croisé dynamique ».
Excel détecte automatiquement la source. Si tout est bien préparé, nous devons voir le nom « Interventions » dans la zone de sélection.
Nous choisissons « Nouvelle feuille de calcul », car cela permet de bien séparer la base qui reste sur une feuille, et l’analyse qui se fait sur une autre.
Une fois que nous validons, Excel affiche une zone vide et, à droite, la liste des champs.
Ces champs correspondent aux colonnes de notre tableau : Technicien, Type intervention, Priorité, Statut, Zone, Date intervention, Durée min et Montant facturé.
Nous allons commencer par une analyse simple : le montant facturé par technicien.
Nous glissons le champ « Technicien » dans la zone « Lignes ».
Ensuite, nous glissons le champ « Montant facturé » dans la zone « Valeurs ».
Excel additionne automatiquement les montants, car il détecte que cette colonne contient des nombres.
Nous obtenons immédiatement le chiffre d’affaires par technicien.
Sans TCD, nous aurions pu faire la même chose avec une formule comme celle-ci :
=SOMME.SI.ENS(Interventions[Montant facturé];Interventions[Technicien];A2)
Cette formule est utile, mais elle suppose que nous ayons déjà une liste de techniciens dans une synthèse.
Avec le tableau croisé dynamique, Excel construit directement la liste et calcule les totaux.
Nous pouvons aussi compter les interventions.
Pour cela, nous retirons « Montant facturé » de la zone « Valeurs », puis nous glissons « ID intervention » dans cette même zone.
Excel affiche alors le nombre d’interventions par technicien.
L’équivalent en formule serait :
=NB.SI.ENS(Interventions[Technicien];A2)
Le tableau croisé dynamique a donc un gros avantage : nous pouvons passer d’une somme à un nombre, ou à une moyenne, sans réécrire toute notre analyse.
Pour modifier le calcul, lorsque le champ valeur contient des valeurs numériques, nous faisons un clic droit sur une valeur du TCD, puis nous choisissons « Synthétiser les valeurs par ».
Nous pouvons sélectionner « Somme », « Nombre », « Moyenne », « Max » ou « Min ».
Ici, nous avons affecté les numéros d’intervention dans le champ « Valeurs », or il s’agit de chaînes de caractères.
Nous ne pouvons donc qu’en effectuer le dénombrement.
3. Croiser les données pour analyser plus vite
Nous allons maintenant créer une analyse plus intéressante : le montant facturé par mois et par technicien :
- Dans la zone « Lignes », nous plaçons « Date intervention ».
- Dans la zone « Colonnes », nous plaçons « Technicien ».
- Dans la zone « Valeurs », nous plaçons « Montant facturé ».
Selon votre version d’Excel, les dates peuvent être regroupées automatiquement par mois.
Si ce n’est pas le cas, nous faisons un clic droit sur une date, puis nous cliquons sur « Grouper ».
Dans la fenêtre, nous sélectionnons « Mois », puis nous validons avec « OK ».
Nous obtenons maintenant un rapport mensuel, avec les techniciens en colonnes. C’est exactement le type de tableau que beaucoup de personnes essaient de construire manuellement avec des formules.
Avec des formules, nous aurions besoin d’un calcul plus long, par exemple :
=SOMME.SI.ENS(Interventions[Montant facturé];Interventions[Technicien];B$6;Interventions[Date intervention];">="&$A7;Interventions[Date intervention];"<="&FIN.MOIS($A7;0))
Cette formule additionne les montants pour un technicien donné et pour un mois donné.
Elle fonctionne, mais elle est plus difficile à écrire, surtout pour un débutant.
Avec le TCD, nous déplaçons simplement les champs.
Nous pouvons ensuite créer une autre analyse.
Petite astuce, pour créer un nouveau TCD très rapidement, il suffit de copier-coller les cellules, ou de dupliquer la feuille de calcul.
Nous retirons les dates et les techniciens, puis nous plaçons :
- « Priorité » dans les lignes,
- « Statut » dans les colonnes,
- et « ID intervention » dans les valeurs.
Nous obtenons une matrice qui indique combien d’interventions sont terminées, en attente ou en retard selon la priorité.
L’équivalent en formule serait :
=NB.SI.ENS(Interventions[Priorité];"Haute";Interventions[Statut];"En retard")
Ici encore, le TCD évite de créer une formule pour chaque combinaison.
Nous pouvons aussi analyser la durée moyenne par type d’intervention.
Nous plaçons :
- « Type intervention » dans les lignes,
- puis « Durée min » dans les valeurs.
Ensuite, nous faisons un clic droit sur les valeurs et nous choisissons « Synthétiser les valeurs par », puis « Moyenne ».
Nous obtenons ainsi une durée moyenne pour les dépannages, les maintenances, les installations et les contrôles.
4. Actualiser, éviter les erreurs et automatiser
Maintenant, imaginons que nous ayons besoin d’ajouter de nouvelle données dans la base, ou d’en modifier certaines :
Comme notre base est un tableau structuré, Excel étend automatiquement le tableau « Interventions ».
Mais attention : le tableau croisé dynamique ne se met pas toujours à jour tout seul.
Nous devons faire un clic droit dans le TCD, puis cliquer sur « Actualiser ».
Nous pouvons aussi utiliser le raccourci [Alt]+[F5] pour actualiser le tableau croisé dynamique sélectionné.
Si nous le souhaitons, nous pouvons également créer un bouton pour actualiser rapidement les données.
Pour cela, nous nous rendons dans le menu « Insertion » du ruban, puis nous choisisons « Zone de texte ».
Nous dessinons le bouton sur la feuille de calcul, puis nous saisissons le texte « Actualiser » :
Nous pouvons ensuite modifier son apparence :
Une fois que le bouton nous convient, nous pouvons effectuer un clic-droit sur celui-ci et cliquer sur « Affecter une macro ».
Nous saisissons le nom de cette dernière, par exemple « actualiserDonnees », puis nous cliquons sur « Nouvelle » pour créer un nouvelle macro et l’afficher directement.
Il ne reste plus qu’à créer une petite macro.
Pour générer cette macro rapidement, et si vous avez installé un application « IA Excelformation.fr » nous pouvons utiliser la fonctionnalité « Code Assistant » qui permet de créer du code VBA en exprimant la demande en français.
Nous nous plaçons à l’intérieur de la macro, puis nous effectuons un clic-droit afin de sélectionner l’option « Code Assistant – IA Excelformation.fr » :
Dans la fenêtre qui s’affiche, nous saisissons la demande à droite : « Actualiser le tableau croisé dynamique de la feuille active », puis nous cliquons sur le bouton « Envoyer la demande » :
L’IA va réfléchir quelques secondes, puis afficher le résultat dans la zone de gauche :
Il ne reste plus qu’à cliquer sur le bouton « Insérer » pour ajouter le contenu de la macro.
De retour sur la feuille du TCD, nous pouvons simplement cliquer sur le bouton pour ajouter les informations que nous venons d’insérer :
5. Conclusion
Dans ce tutoriel, nous avons vu que créer un tableau croisé dynamique n’est pas compliqué quand la source est bien préparée.
Le point le plus important à retenir est que le tableau source doit rester simple : pas de totaux, pas de cellules fusionnées, pas de mois répartis en colonnes, et pas de lignes vides.
À partir de là, Excel peut résumer les données rapidement, sans formules fragiles, et nous pouvons modifier l’analyse en quelques glisser-déposer.
Et avec la petite macro d’actualisation, nous pouvons rendre le fichier encore plus pratique pour une utilisation régulière.
