Name: Tableaux de bord Pilotez vos informations pour optimiser la prise de décision avec Excel (versions Microsoft 365...)
Rating: 4.4615 (13 reviews)

Introduction

Lorsque vous avez besoin d’obtenir une information rapidement, Excel vous permet d’obtenir des tableaux de synthèses sans avoir à concevoir de formules complexes. La méthode à mettre en œuvre consiste à réaliser des tableaux croisés dynamiques.

Les données sources d’un tableau croisé dynamique doivent toujours posséder la même structure :

Une ligne représente un enregistrement de la base de données.
Une colonne représente un champ.
La première ligne doit comporter les titres (noms des champs). Pour pouvoir croiser les données, la base de données doit au moins comporter deux champs à croiser plus un champ de données numériques.

Afin de ne pas rencontrer de problèmes dans la construction de vos tableaux croisés, respectez ces indications :

ne pas fusionner de cellules sur la ligne des titres,
deux champs ne doivent pas avoir le même nom,
un nom de champ ne doit pas être vide,
la base de données ne doit pas comporter de ligne ou de colonne vide,
ne pas introduire de lignes de sous-totaux dans la base,
dans les colonnes de valeurs numériques, privilégier le remplacement des cellules vides par des zéros.

Un rapport de tableau croisé dynamique est composé de cinq parties :

Zone de filtre
Zone des valeurs
Zone des étiquettes de lignes
Zone des étiquettes de colonnes
Zone des totaux

Créer un tableau croisé dynamique simple

Les données sources

Notre tableau statistique va être construit à partir du fichier GestProduction.xlsx.

Les données présentes dans la feuille RVLProd sont telles que ci-après.

Le tableau représente le relevé sur l’année des productions d’une petite confiserie artisanale.

Ce relevé doit nous permettre de calculer différents éléments :

Production totale pour chaque ligne
Production totale par opérateur
Total des temps d’arrêts par type de panne
Productivité moyenne par opérateur

Conception du tableau croisé

Notre premier objectif est de calculer la production totale pour chaque ligne. Dans ce cas, il va suffire de croiser deux informations : la ligne et le nombre d’unités produites.

Positionnez le curseur dans une cellule de la source, A1 par exemple.

Dans l’onglet Insertion - groupe Tableaux, cliquez sur Tableau croisé dynamique :

La fenêtre Créer un tableau croisé dynamique apparaît :

La plage des données à analyser est sélectionnée automatiquement et apparaît entourée d’une ligne de pointillés mouvants. L’emplacement Nouvelle feuille de calcul est sélectionné par défaut.

Cliquez directement sur OK.

Excel crée une nouvelle feuille de calcul dans laquelle apparaît la zone de rapport dans la partie gauche.

La boîte de dialogue Champs de tableau croisé dynamique est affichée dans la partie droite de la fenêtre.

Faites glisser le champ Ligne dans la zone Lignes et le champ NB Total Unités Produites dans ∑ Valeurs.

La fonction Somme a été attribuée par défaut au champ NB total Unités Produites. Le tableau croisé...

Mettre à jour un tableau croisé dynamique

Cas 1 : La plage source a le même nombre de lignes

Lorsque dans un tableau Excel contenant des formules, une donnée est modifiée, les formules sont automatiquement recalculées. Dans un tableau croisé dynamique, lorsque les données de la base de données sont modifiées, les tableaux croisés créés à partir de cette source ne sont pas mis à jour automatiquement. Il nous faut donc actualiser les tableaux croisés si les données sources ont été modifiées.

Cliquez dans le tableau croisé.

Dans l’onglet Analyse du tableau croisé dynamique - groupe Données, cliquez sur Actualiser puis sur Actualiser (AltF5) ou bien sur Actualiser tout (CtrlAltF5) si vous avez plusieurs tableaux croisés dynamiques.

Si vous ajoutez des lignes à la fin de la base de données, celles-ci ne seront pas prises en compte par une mise à jour.

Cas 2 : Le nombre de lignes de la plage source a été modifié

Généralement, la plage source n’est pas figée, si vous exportez mensuellement les nouvelles données, le nombre de lignes de la plage source augmentera chaque mois. La plage source doit donc être modifiée manuellement.

Cliquez dans le tableau croisé.

Analyse du tableau croisé dynamique - groupe Données - Changer la source de données

Excel présente les coordonnées de la plage source telle qu’elle était lors de la conception du tableau croisé.

Modifiez le numéro de la dernière ligne puis validez par OK.

Cette technique peut cependant être fastidieuse lorsque votre fonction nécessite de manipuler fréquemment de nombreux tableaux croisés...

Créer un tableau croisé utilisant des données horaires

Insérer le tableau

Nous souhaitons maintenant obtenir le temps de production réel moyen pour chaque ligne et pour chaque opérateur.

Revenez dans la feuille RLVPRod puis positionnez le curseur dans une cellule de la source.

Dans l’onglet Insertion - groupe Tableaux - cliquez sur Tableau croisé dynamique.

Cliquez directement sur OK.

Faites glisser le champ OPERATEUR dans la zone Lignes, le champ LIGNE dans la zone Colonnes et le champ TEMPS PRODUCTION REEL dans la zone ∑ Valeurs.

Excel a utilisé la fonction Nombre pour synthétiser le champ TEMPS PRODUCTION REEL. Il est donc nécessaire de modifier la fonction à employer.

Déroulez le menu relatif au champ Nombre de TEMPS PRODUCTION REEL puis sélectionnez Paramètres des champs de valeurs.

Sélectionnez la fonction Moyenne puis modifiez le nom du champ :

Cliquez ensuite sur le bouton Format de nombre, dans la catégorie Heure, cliquez sur le format 13:30, puis validez par OK.

Cliquez sur OK une nouvelle fois pour revenir au tableau croisé.

Appliquer une mise en forme conditionnelle

Revenez sur le premier tableau croisé dynamique créé.

Considérons que nous souhaitons faire apparaître sur fond orange les noms des lignes dont le taux de perte est supérieur à 6 %. Une mise en forme conditionnelle est à appliquer à notre premier tableau croisé. Annulez le filtre appliqué à ce tableau en sélectionnant L’option Tous en B1.

Sélectionnez ensuite les cellules A4 à A9 du tableau croisé.

Dans l’onglet Accueil - groupe Styles, cliquez sur Mise en forme conditionnelle puis sur Nouvelle règle.

Cliquez sur le type Utiliser une formule pour déterminer...

Créer un tableau de bord utilisant plusieurs tableaux croisés dynamiques

Objectif

À partir du fichier TCD-TabBord.xlsx nous allons construire un tableau de bord de suivi en temps réel de nos produits et charges.

Les premières lignes de la feuille Données sont présentées ci-dessous. Les écritures de charges et de ventes sont intégrées dans la même liste.

Notre tableau de bord doit nous permettre de connaître, pour l’année complète ou pour un ou plusieurs mois :

le total des charges par poste,
le chiffre d’affaires par client,
le top 5 des clients,
la marge dégagée.

Un exemple est présenté ci-après :

Préparation

Au cours de l’année, le nombre de lignes de notre liste d’écritures va augmenter. Il est donc primordial de créer une plage de données variables :

Dans l’onglet Formules - groupe Noms définis, cliquez sur Gestionnaire de noms puis sur Nouveau.

Saisissez le nom ECRITURES puis entrez la formule dans la zone Fait référence à :

Validez par OK.

Chiffre d’affaires par client

Positionnez le curseur dans la feuille TabBord en A12.

Désactivez l’affichage du quadrillage (onglet Affichage - groupe Afficher).

Dans l’onglet Insertion - groupe Tableaux, cliquez sur Tableau croisé dynamique.

Saisissez les différentes informations en vous référant à l’écran ci-dessous :

Validez par OK.

Faites glisser les champs dans les différentes zones puis appliquez le filtre PRODUITS en B10.

Effectuez un clic droit sur l’un des nombres puis Format de nombre - Comptabilité - OK.

Modifiez les étiquettes du tableau :

Dépenses par type

Procédez de la même...

Conclusion

Il nous a paru utile de vous présenter dans ce chapitre un exemple d’utilisation de tableaux croisés dynamiques dans un tableau de bord. Cette solution, utilisant des tableaux croisés dynamiques, présente quelques avantages mais aussi quelques inconvénients qu’il faudra garder à l’esprit lorsque vous l’utiliserez.

Tout d’abord les points forts sont la possibilité d’utiliser quelques fonctionnalités qui seraient moins aisées à mettre en œuvre dans un tableau de bord construit uniquement à partir de formules.

L’utilisation des segments et des chronologies va permettre de filtrer simplement et rapidement les informations sources des tableaux croisés.
Un nouvel élément de synthèse saisi dans la plage source sera intégré automatiquement sur une nouvelle ligne dans le tableau croisé.
Les regroupements par dates sont plus aisés à construire qu’avec des formules.
Le fait de double cliquer sur une valeur de synthèse va créer une nouvelle feuille affichant le détail des écritures relatives à cette valeur.

Les inconvénients sont de plusieurs ordres :

Sans manipulation particulière, les nouvelles lignes ajoutées à la plage source ne sont pas prises en compte automatiquement dans les tableaux croisés.
Contrairement à des formules, la mise à jour des calculs et des synthèses n’est pas automatique lors de la modification des données ou lors de l’ajout de nouvelles données. Il faut soit effectuer une mise à jour manuelle, soit écrire une procédure Visual Basic qui va automatiser la mise à jour.
Les possibilités de présentation sont limitées par rapport à un tableau de bord conçu...