Tableau de bord dynamique pour analyses statistiques

Pour partager cette vidéo sur les réseaux sociaux ou sur un site, voici son url :


Sujets que vous pourriez aussi aimer :


Analyses statistiques dynamiques avec Excel

Dans cette formation Excel, nous proposons de monter un tableau de bord dynamique pour l'analyse statistique des informations de bases de données. Nous simulons ici le cas d'une société gérant un parc de véhicules d'occasion. Chaque voiture est référencée par son immatriculation, sa marque, son modèle et son prix de vente, dans le tableau situé entre les colonnes B et E. Pour une analyse statistique fine et pertinente, les fonctions de bases de données sont de mise, mais pas seulement.

Le tableau de bord quant à lui, débute à partir de la colonne H. En ligne 2, l'analyste dispose de trois leviers pour dépouiller les informations de synthèse, selon des critères recoupés.

Présentation du concept



L'utilisateur choisit par exemple une marque comme Peugeot, grâce à la dernière liste déroulante. C'est ce qu'illustre la capture ci-dessus de l'application finalisée. Tous les modèles concernés sont instantanément mis en valeur dans le tableau source, pour faciliter leur repérage. Des calculs de synthèse renseignent sur la proportion en nombre et en chiffre par rapport à l'ensemble du parc. Des représentations graphiques réagissent dynamiquement. Elles simplifient la lecture et renforcent l'interprétation.

L'analyste peut alors définir une fourchette de prix grâce à deux nouvelles listes déroulantes. Il repère instantanément les automobiles concernées. Il est intéressant de constater que les critères peuvent être recoupés ou non, à souhait. Dans tous les cas de figure, l'analyse est déclenchée sur demande, soit au choix dans les listes.

Classeur source à télécharger
Il s'agit de concentrer nos travaux sur l'élaboration du tableau de bord. C'est pourquoi nous proposons de récupérer les données à manipuler dans une feuille Excel offrant la structure mise en forme.

• Télécharger le classeur analyse-statistique-variable.xlsx en cliquant sur son lien,
• L'ouvrir dans Excel et cliquer sur le bouton Activer la modification du bandeau de sécurité,

Ce classeur est constitué de deux feuilles nommées respectivement Parc_auto et Sources. La seconde porte bien son nom. Elle héberge les sources de données qui ont servi à construire les listes déroulantes permettant de recouper les critères.

Notre attention se focalise donc naturellement sur la feuille Parc_auto. Vous notez la présence de ces listes déroulantes en H2, I2 et J2. Les deux premières permettent de dresser des conditions numériques sur les prix de vente avec des opérateurs d'inégalité. La dernière permet de sélectionner la marque sur laquelle l'analyse statistique doit opérer.



Les totaux de synthèse
Nous choisissons de débuter par les opérations les plus simples nécessaires à la construction des graphiques. En colonne K, respectivement en cellules K5 et K6, nous devons calculer les totaux. Plus spécifiquement, il s'agit d'évaluer le nombre total de véhicules et la somme globale des prix de vente. Ces deux opérations ne répondent à aucune condition. Elles permettront de rendre compte des proportions, une fois les extractions réalisées sur les critères recoupés.

• Cliquer sur la cellule K5 pour l'activer,
• Taper le symbole = pour démarrer le calcul,
• Saisir la fonction comptant les cellules non vides suivie d'une parenthèse, soit NbVal(,
• Cliquer sur l'étiquette de la colonne B pour la désigner intégralement, soit B:B,
• Fermer la parenthèse de la fonction NbVal,
• Retrancher une unité à ce décompte, soit : -1,
• Valider la formule en enfonçant la touche Entrée du clavier,

Nous obtenons le résultat de 88 véhicules par la formule suivante : =NBVAL(B:B)-1. Notre tableau est effectivement constitué de 89 lignes et l'énumération débute à partir de la deuxième. C'est la raison pour laquelle nous avons retranché une unité dans l'opération. Ainsi, nous excluons du décompte, la ligne de titre forcément considérée par la fonction NbVal. Pour un résultat dynamique capable d'évoluer si de nouvelles données venaient à apparaître à la suite du tableau, nous avons en effet spécifié l'intégralité de la colonne (B:B).

Il s'agit désormais de reproduire le même principe pour le calcul du total des prix de vente. Inévitablement la formule doit cette fois opérer sur la colonne E.

• Cliquer sur la cellule K6 pour la sélectionner,
• Taper le symbole = pour débuter la formule,
• Saisir la fonction réalisant des additions suivie d'une parenthèse, soit Somme(,
• Cliquer sur l'étiquette de la colonne E pour la désigner intégralement, soit E:E,
• Fermer la parenthèse de la fonction Somme et valider le calcul par la touche Entrée,

Nous obtenons un total de 771 800 Euros. La somme comme d'autres fonctions Excel est capable d'ignorer les cellules de texte. Ainsi conçue, elle est dynamique au même titre que le calcul précédent.

Totaux selon des critères recoupés
Les mêmes opérations doivent être enclenchées en colonne I, respectivement en cellules I5 et I6. Mais cette fois, elles doivent considérer les critères émis par les listes déroulantes en les recoupant. Ce sont les fonctions de bases de données, respectivement BdNb et BdSomme, qui permettent de produire ces opérations en fonction d'une zone de critères. Il s'agit de la plage decellules H1:J2. Cette dernière reproduit scrupuleusement les noms des champs du tableau de la base de données. C'est ainsi que la correspondance est réalisée. Le critère mentionné s'applique sur la colonne du même nom.

Pour simplifier la mise en oeuvre des formules, nous avons nommé la plage de cellules représentant la base de données.

• Cliquer sur l'onglet Formules situé en haut de la fenêtre Excel pour activer son ruban,
• Dans la section Noms définis du ruban, cliquer sur le bouton Gestionnaire de noms,

Une boîte de dialogue apparaît. Elle synthétise tous les noms en vigueur attribués à des plages de cellules. Notez la présence du nom bdd représentant la plage de cellules B1:E89. Il s'agit de la base de données des véhicules sur laquelle doivent être enclenchées les fonctions de base de données. Nous pourrons donc la désigner par son nom et éviter de la sélectionner à la souris. Par la même occasion, nous simplifierons la syntaxe des formules.

• Cliquer sur le bouton Fermer pour fermer le gestionnaire de noms,

Les syntaxes des fonctions de bases de données sont les suivantes :

=BdNb(Base_de_données ; Champ ; Critères)
=BdSomme(Base_de_données ; Champ ; Critères)

Comme vous le constatez, elles requièrent exactement les mêmes arguments. Elles consistent à réaliser une opération sur une base de données en fonction d'une zone de critères. C'est la nature de l'opération qui change. La base de données est désormais reconnue sous le nom bdd. Le champ doit désigner l'entête de colonne sur laquelle l'opération doit être effectuée. Nous pourrons désigner la cellule E1. Enfin, la zone de critères est préconçue. Elle reproduit les noms de champs à l'identique. Il s'agit de la plage H1:J2.

• Cliquer sur la cellule I5 pour la sélectionner,
• Taper le symbole = pour débuter la formule,
• Saisir le nom de la fonction de base de données pour le dénombrement numérique, suivi d'une parenthèse, soit BdNb(,
• Saisir le nom de la base de données, soit : bdd,

Instantanément, vous remarquez que l'ensemble du tableau apparaît en surbrillance.

• Taper un point-virgule (;) pour passer dans l'argument du champ,
• Cliquer sur la cellule E1 pour désigner la colonne des prix de ventes,
• Taper un point-virgule (;) pour passer dans l'argument de la zone de critères,
• Sélectionner la plage de cellules H1:J2,
• Fermer la parenthèse de la fonction BdNb et valider le calcul avec la touche Entrée,

En l'absence de conditions définies dans la zone de critères, nous obtenons strictement le même résultat que le décompte effectué par la fonction NbVal. Puisqu'aucun enregistrement n'est exclu, tous les véhicules sont comptabilisés pour l'instant. La formule que nous avons construite est la suivante :

=BDNB(bdd;E1;H1:J2)

• Cliquer sur la cellule I6 pour la sélectionner,
• Taper le symbole = pour débuter la formule,
• Saisir le nom de la fonction de base de données pour les additions, suivi d'une parenthèse, soit BdSomme(,
• Saisir le nom de la base de données, soit bdd,
• Taper un point-virgule (;) pour passer dans l'argument du champ,
• Cliquer sur la cellule E1 pour la désigner,
• Taper un point-virgule (;) pour passer dans l'argument de la zone de critères,
• Sélectionner la plage de cellules H1:J2,
• Fermer la parenthèse de la fonction BdSomme,
• Puis, valider le calcul avec la touche Entrée du clavier,

Là encore, le résultat retourné par la fonction de base de données est identique à celui fourni par son homologue, la fonction Somme.

• A l'aide de la liste déroulante en J2, choisir la marque Renault,

Désormais les résultats divergent. 28 véhicules sur les 88 de départ correspondent. La somme de leurs chiffres conduit à un résultat de 201 400 contre 771 800 pour l'ensemble du Parc. Les fonctions de bases de données font leur oeuvre. Elles réalisent les mêmes opérations que leurs homologues mais en considérant les critères énoncés. Il en résulte des calculs sélectifs.

• A l'aide de la liste déroulante en I2, choisir le critère<=13000,

Encore une fois, les données de synthèse s'ajustent. Elles fournissent les résultats pour les véhicules de la marque Renault dont le prix de vente est inférieur à 13 000 Euros.

• A l'aide de la liste déroulante en H2, choisir le critère>=9000,

Il ne reste plus que 3 véhicules de la marque Renault dont le prix de vente est compris entre 9 000 et 13 000 Euros. Notre tableau de bord commence à prendre forme. Les choix croisés permettent d'obtenir des résultats de synthèse précieux pour l'analyse des données. La formule que nous avons bâtie est la suivante :

=BDSOMME(bdd; E1; H1:J2)



Les listes déroulantes associées aux fonctions de bases de données offrent une grande ergonomie. Sur ordres simples, elles permettent de réaliser de nombreuses analyses pertinentes.

Analyse graphique
Pour simplifier l'interprétation des données, nous proposons d'associer des représentations graphiques aux résultats de synthèse. Sous forme d'anneaux, nous souhaitons afficher les proportions dynamiques. Il s'agit de représenter d'un côté la proportion du nombre de véhicules répondant aux critères par rapport aux véhicules restants. D'un autre côté, nous souhaitons offrir l'affichage visuel de la part des chiffres pour les automobiles répondant aux critères, par rapport aux chiffres des véhicules restants.

Pour représenter ces parts graphiquement, un calcul intermédiaire est nécessaire en colonne L. Il consiste à réaliser les différences entre les deux résultats. Nous obtiendrons ainsi les valeurs pour les véhicules exclus des conditions.

• Sélectionner la cellule L5 et taper le symbole = pour débuter le calcul,
• Cliquer sur la cellule K5 pour la désigner,
• Taper le symbole moins (-) pour enclencher la soustraction,
• Cliquer sur la cellule I5 pour l'inclure dans l'opération,
• Valider le calcul par le raccourci clavier CTRL + Entrée pour garder la cellule active,
• Tirer la poignée du résultat sur la cellule L6 pour répliquer le calcul sur les prix de vente,

Nous obtenons ainsi les différences entre les nombres de voitures et les prix de vente. Ce sont ces valeurs que nous devons exploiter respectivement avec les résultats des fonctions de bases de données. Comme vous le savez, avant de construire un graphique Excel, il convient de sélectionner les données à représenter.

• Cliquer sur la cellule I5 pour la sélectionner,
• Puis, tout en maintenant la touche CTRL enfoncée, cliquer sur la cellule L5,

Cette astuce permet d'inclure plusieurs cellules qui ne sont pas adjacentes, dans une même sélection.

• Cliquer sur l'onglet Insertion en haut de la fenêtre Excel pour activer son ruban,
• Dans la section Graphiques du ruban, cliquer sur la flèche des graphiques en secteurs,
• Dans la liste, cliquer sur la représentation en anneau comme le suggère la capture,

Il convient ensuite :

• De le positionner dans le cadre situé en dessous des résultats statistiques,
• De le redimensionner sur la hauteur de ce cadre,
• De le redimensionner sur la moitié de la largeur de ce cadre,
• De supprimer sa légende,
• De déplacer son titre à l'intérieur de l'anneau,
• De modifier son titre en : Ratio Nb. Véhicules,
• De réaliser quelques réglages de mise en forme pour améliorer la représentation,

Les rubans contextuels Création et Format proposent de nombreuses options permettant de personnaliser un graphique. Nous ne nous attardons pas sur ces techniques. Il ne s'agit pas de l'objectif de cette application. La formation Excel pour interpréter les graphiques démontre de nombreuses solutions avancées à ce sujet.

• Désormais, sélectionner ensemble les cellules I6 et L6,
• Comme précédemment, construire un graphique en anneau sur cette source de données,
• Le disposer à droite du précédent, sur la hauteur du cadre et la moitié de la largeur,
• Lui attribuer les mêmes réglages et changer son titre en : Ratio Prix de vente,

Comme vous le savez, un graphique Excel est dynamiquement lié à ses données. Si elles évoluent, la représentation s'adapte automatiquement. Comme ces données sont elles mêmes liées aux critères imposés sur la base de données, les représentations évoluent en fonction des choix de l'analyste.

• Supprimer les conditions en I2 et J2,

Les résultats de synthèse offerts par les fonctions de bases de données s'ajustent automatiquement. 27 véhicules pour un montant total de 421 800 Euros proposent un prix de vente supérieur ou égal à 9 000 Euros.



De fait, les graphiques conçus sur ces données dynamiques, se redessinent. Notre tableau de bord est de plus en plus dynamique. Grâce à ces représentations, nous renforçons considérablement l'interprétation visuelle des résultats statistiques.

Repérer visuellement les enregistrements concordants
Nous souhaitons améliorer encore l'efficacité du tableau de bord dynamique. Pour cela, il convient de faire ressortir toutes les lignes du tableau source qui correspondent aux choix recoupés. Cette mise en valeur doit être dynamique elle aussi afin de réagir aux critères posés par l'analyste. Il s'agit donc de bâtir des règles de mise en forme conditionnelle. Ces règles doivent correspondre aux conditions de la zone de critères, pour déclencher automatiquement les couleurs.

Mais tantôt trois critères peuvent être croisés, tantôt deux et tantôt un seul. Il y a au total 7 possibilités. Il s'avère complexe de prévoir tous ces cas dans une seule formule de critère. L'astuce consiste à envisager toutes les possibilités une par une. Il suffit d'exploiter la fonction Excel ET pour recouper toutes les conditions à remplir pour chaque cas. Dès que l'une d'entre elles n'est pas satisfaite, la règle se neutralise et ne déclenche aucune mise en valeur. Ainsi, par le jeu des superpositions, en prévoyant tous les cas possibles, une mise en forme conditionnelle en remplacera une autre. Il suffit pour chacune d'entre elles de prévoir les mêmes réglages de mise en valeur. Nous donnerons ainsi l'illusion qu'une seule règle existe.

Un souci se dresse néanmoins. Les règles de mise en forme conditionnelle doivent être bâties en fonction des conditions posées dans la zone de critères. Par exemple, avec le dernier réglage que nous avons émis, il s'agit de vérifier que le prix de vente est supérieur ou égal à 9 000 Euros quand dans le même temps, aucune condition n'est posée ni sur la fourchette supérieure du prix de vente, ni sur la marque. La traduction en règle de mise en forme conditionnelle serait la suivante :

=ET($E2>=$H$2; $I$2=''; $J$2='')

Cependant, la donnée inscrite en H2 n'est pas un nombre. Elle est assemblée avec son opérateur d'inégalité. Donc la comparaison entre la cellule E2 et la cellule H2 ne peut pas aboutir. Nous devons préalablement purger les cellules H2 et I2 de leurs opérateurs afin de ne restituer que les valeurs numériques. Alors, la comparaison pourra aboutir pour construire le critère.

Nous proposons de réaliser ces traitements intermédiaires dans les cellules du dessous, soit H3 et I3. La formation Excel sur le traitement des chaînes de caractères nous avait appris la fonction Substitue. Elle permet de remplacer un texte par un autre dans une cellule, selon la syntaxe suivante :

=SUBSTITUE(Cellule; Ancien_texte ; Nouveau_texte)

La cellule correspond à la chaîne de caractères à traiter. Il s'agit donc des cellules H2 et I2. L'ancien texte est le fragment à remplacer. Il s'agit respectivement des opérateurs : >= et <=. Le nouveau texte est le fragment de remplacement. Comme il s'agit de supprimer ces occurrences, nous indiquerons une chaîne vide par deux guillemets ('').

• Sélectionner la cellule H3 et taper le symbole = pour débuter le calcul,
• Saisir le nom de la fonction de remplacement suivi d'une parenthèse, soit Substitue(,
• Cliquer sur la cellule H2 pour indiquer le texte à traiter,
• Taper un point-virgule (;) pour passer dans l'argument du texte à remplacer,
• Saisir les symboles de l'inégalité entre guillemets, soit : '>=',
• Taper un point-virgule (;) pour passer dans l'argument du texte de remplacement,
• Saisir deux guillemets, soit : '' pour supprimer ces symboles,
• Fermer la parenthèse de la fonction Substitue et valider le calcul,

Le résultat semble satisfaisant dans la mesure où seule la valeur numérique est extraite. Mais un défaut attire l'oeil. Le nombre est aligné à gauche dans sa cellule. Cela signifie qu'Excel continue de considérer cette donnée comme un texte. En conséquence, aucune comparaison numérique ne peut être effectuée.

Nous devons englober cette syntaxe dans la fonction Excel CNum. Cette dernière permet de convertir explicitement la donnée en valeur numérique. Tant qu'à faire, nous allons englober le tout dans une fonction de gestion d'erreur SiErreur. En effet, si aucun critère n'est émis, le traitement retournera un message disgracieux qu'il convient d'intercepter.

• En H3, adapter la formule comme suit :

=SIERREUR(CNUM(SUBSTITUE(H2; '>='; '')); '')

Le résultat est le même mais cette fois la donnée est alignée à droite. Excel la considère donc bien comme une valeur numérique. En I3, il convient de reproduire le même traitement. Mais cette fois, il s'agit de remplacer les symboles : <=.

• En I3, construire la formule suivante :

=SIERREUR(CNUM(SUBSTITUE(I2; '<='; '')); '')



Puisque nous disposons désormais des données numériques nécessaires et dynamiques pour bâtir les critères, il est de temps de produire les mises en forme conditionnelles.

La première règle consiste à vérifier que le prix de vente du véhicule est bien supérieur à la valeur numérique du premier critère posé, tandis que les deux autres conditions sont vides.

• Sélectionner l'ensemble des données du tableau, soit la plage de cellules B2:E89,
• Dans le ruban Accueil, cliquer sur le bouton Mise en forme conditionnelle,
• Dans la liste qui suit, choisir Nouvelle règle,
• Dans la boîte de dialogue qui apparaît, sélectionner le dernier type de règle : Utiliser une formule pour déterminer...,
• Cliquer dans la zone de saisie située juste en dessous pour l'activer,
• Taper le symbole = pour débuter la syntaxe du critère de la règle,
• Saisir la fonction recoupant les conditions suivie d'une parenthèse, soit ET(,
• Cliquer sur le premier critère pour lequel il s'agit de vérifier la présence, soit $H$2,
• Taper le symbole inférieur suivi du symbole supérieur et de deux guillemets, soit <>'',

Cette première condition consiste à vérifier que la cellule n'est pas vide, donc que le premier critère est posé.

• Taper un point-virgule (;) pour poursuivre l'énumération des critères,
• Construire la condition suivante : $I$2='',

Elle consiste à vérifier que le deuxième critère n'est pas proposé.

• Taper un point-virgule (;) pour poursuivre l'énumération des critères,
• Construire la condition suivante : $J$2='',

Elle consiste à vérifier que le critère sur la marque n'est pas émis.

• Taper un point-virgule (;) pour poursuivre l'énumération des critères,
• Cliquer sur le premier prix de vente à comparer, soit sur la cellule E2,
• Enfoncer deux fois la touche F4 du clavier pour ne la figer qu'en colonne, soit : $E2,

En effet, pour toutes les colonnes d'une même ligne, le critère doit être posé sur le prix de vente, soit en colonne E qui ne doit pas bouger. Mais pour chaque véhicule, c'est bien le prix de vente correspondant qui doit être évalué. Donc la ligne doit se déplacer en même temps que l'analyse parcourt le tableau vers le bas.

• Taper le symbole supérieur suivi du symbole égal, soit >=, pour l'inégalité à vérifier,
• Cliquer sur la cellule H3 pour désigner la valeur de comparaison, ce qui donne : $H$3,
• Fermer la parenthèse de la fonction Et pour terminer la syntaxe du critère de la règle,

Lorsque toutes ces conditions sont vérifiées ensemble, il s'agit de déclencher une mise en valeur dynamique. Cette dernière doit consister à remplir les enregistrements correspondants d'une couleur de fond afin de les repérer aisément.

• Cliquer sur le bouton Format situé en bas de la boîte de dialogue,
• Dans la nouvelle boîte de dialogue qui suit, activer l'onglet Remplissage,
• Dans la palette des couleurs, choisir un vert pâle par exemple,
• Cliquer alors sur l'onglet Police pour définir les attributs de texte,
• Dans la liste déroulante des couleurs, choisir un violet assez foncé,
• Cliquer sur le bouton Ok pour valider ces réglages de mise en forme,

Nous sommes de retour sur la première boîte de dialogue qui illustre la mise en valeur qui sera déclenchée lorsque toutes les conditions posées seront vérifiées ensemble.

• Valider cette première règle de mise en forme conditionnelle en cliquant sur le bouton Ok,

Comme seul le critère sur le prix de vente est établi pour l'instant, notre première règle se déclenche instantanément. Elle repère efficacement toutes les lignes des véhicules qui correspondent. Si vous modifiez cette fourchette inférieure à l'aide de la première liste déroulante, vous constatez que la mise en valeur dynamique s'adapte instantanément.

La syntaxe que nous avons construite est la suivante :

=ET($H$2<> ''; $I$2=''; $J$2=''; $E2>=$H$3)

Il s'agit maintenant de bâtir les six autres règles sur la même plage de cellules, qu'il convient de bien resélectionner. Pour chacune d'entre elles, les réglages de mise en forme doivent être identiques.

• Créer la règle suivante pour répondre au critère seul de la fourchette supérieure :

=ET($H$2=''; $I$2<>''; $J$2=''; $E2<=$I$3)

• Créer la règle répondant à la condition sur la marque seule :

=ET($H$2=''; $I$2=''; $J$2<>''; $C2=$J$2)

• Créer la règle vérifiant la fourchette de prix :

=ET($H$2<>''; $I$2<>''; $J$2=''; $E2>=$H$3; $E2<=$I$3)

• Créer la règle vérifiant le seuil inférieur et la marque :

=ET($H$2<>''; $I$2=''; $J$2<>''; $E2>=$H$3; $C2=$J$2)

• Créer la règle vérifiant le seuil supérieur et la marque :

=ET($H$2=''; $I$2<>''; $J$2<>''; $E2<=$I$3; $C2=$J$2)

• Enfin, créer la règle recoupant les trois critères à la fois :

=ET($H$2<>''; $I$2<>''; $J$2<>''; $E2>=$H$3; $E2<=$I$3; $C2=$J$2)



Comme vous le constatez, tous les cas sont désormais prévus. Dès qu'un levier est actionné, les résultats de synthèse sont livrés, les graphiques s'ajustent et les mises en valeur réagissent. Nous avons donc bâti un tableau de bord dynamique fonctionnel et ergonomique.