Synthétiser les données au clic sur une valeur

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


Sujets que vous pourriez aussi aimer :


Synthèse des résultats au clic de la souris

Nous avons déjà appris à créer des interactions avec les cellules de la feuille Excel au clic sur l'une d'entre elles. La solution avait consisté à sélectionner l'enregistrement intégral d'un tableau, à la sélection de l'une de ses cellules. Ici nous récidivons pour bâtir une application efficace et ergonomique. L'idée consiste à repérer et synthétiser toutes les informations d'une base de données Excel, au clic sur l'une de ses valeurs.



Nos travaux vont porter sur une base de données recensant des idées de sorties référencées sur la désignation, l'activité, le département et la ville. Dans l'exemple illustré par la capture ci-dessus, l'utilisateur souhaite connaître la proportion des hôtels et assimilés. Il clique sur l'une des cellules portant la mention. Instantanément, tous les champs correspondants ressortent explicitement. Dans le même temps, une synthèse numérique doit être livrée.

Source et présentation du concept
Pour amorcer la conception, nous avons tout d'abord besoin de réceptionner les données à manipuler.

• Télécharger le classeur synthese-sur-clic-souris.xlsm en cliquant sur ce lien,
• Double cliquer sur le fichier téléchargé pour l'ouvrir dans Excel,
• Puis, cliquer sur le bouton Activer la modification du bandeau de sécurité,

Nous débouchons donc sur l'unique feuille de ce classeur offrant une base de données relativement riche. L'extension du fichier (xlsm) n'est pas anodine. Elle permet la gestion des macros et notamment du code VBA. En effet, sur une feuille Excel, le recalcul automatique des cellules est enclenché à la moindre modification repérée. Mais ce phénomène ne se produit pas au changement de sélection. Et c'est bien sur cet événement que nous souhaitons produire les repérages et statistiques. Nous devrons donc glisser une ligne de code triviale générant cet événement.

Sur la droite de la base de données, vous notez la présence d'une petite zone de synthèse.



Elle doit renforcer et synthétiser le repérage dynamique. Elle doit d'abord rappeler le contenu de la cellule cliquée. Puis, elle doit indiquer le nombre de résultats strictement identiques dans la base de données et ceux contenant la valeur demandée.

Recalcul de la feuille au clic
Un clic sur une cellule est considéré comme un changement de sélection. Et cet événement est précisément géré par VBA, si tant est que nous en fassions la demande. Très simplement, nous allons donc générer la procédure permettant d'actualiser les calculs au clic de la souris. Dès lors, tous nos travaux se concentreront sur la feuille et les formules. Il s'agit de l'unique intervention du code VBA, au demeurant fort discret, comme vous allez le voir.

• Réaliser le raccourci clavier ALT + F11 pour basculer dans l'éditeur de code Visual Basic,
• Dans l'explorateur de projet sur la gauche de l'écran, double cliquer sur Feuil1 (bd_sorties),

Ainsi, nous affichons la feuille de code associée à l'onglet bd_sorties au centre de l'écran.

• En haut de cette feuille, déployer la liste déroulante de gauche,
• Puis, choisir l'élément Worksheet,

C'est ainsi que nous générons la procédure événementielle Worksheet_SelectionChange. Son code doit se déclencher à chaque changement de sélection, en d'autres termes à chaque clic sur la feuille.

• Entre les bornes de la procédure, ajouter l'instruction VBA suivante :

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Application.Calculate

End Sub

• Enregistrer les modifications (CTRL + S),
• Puis, fermer l'éditeur de code pour revenir sur la feuille,

La méthode Calculate de l'objet VBA Application force le recalcul de toute la feuille. Dans cette procédure événementielle, ce recalcul est donc désormais ordonné à chaque clic.

Coordonnées de la cellule active
Pour la construction du format dynamique et des calculs de synthèse, grâce à l'événement désormais géré, nous devons être en mesure de prélever les coordonnées de la cellule active. De cette manière, nous serons capables de pointer son contenu pour le comparer avec celui de toutes les cellules de la base de données.

La fonction Excel Adresse renvoie ces coordonnées en fonction d'un indice de ligne et d'un indice de colonne, respectivement passés en premier et deuxième paramètre. Et pour lui transmettre les indices de la cellule active car cliquée, nous devons exploiter la fonction Excel Cellule. Cette dernière, selon le paramètre qui lui est transmis, retourne l'information qui lui est demandée.

• En cellule arbitraire et temporaire G8, saisir la formule suivante :

=ADRESSE(CELLULE('ligne'); CELLULE('colonne'))

Grâce à la fonction Cellule, nous passons l'indice de ligne et l'indice de colonne de la cellule active à la fonction Adresse.

• Après validation, cliquer sur l'une des cellules de la base de données, par exemple D6,

Instantanément et grâce à notre procédure événementielle VBA, la fonction retourne désormais les nouvelles coordonnées de la cellule cliquée ($D$6). Mais pour des résultats statistiques et pour les besoins des comparaisons, il est nécessaire de connaître le contenu de cette cellule. Pour cela et comme vous le savez, nous devons exploiter la fonction Excel Indirect.

• En cellule G8, adapter le calcul comme suit :

=INDIRECT(ADRESSE(CELLULE('ligne'); CELLULE('colonne')))

• A validation, s'il apparaît, ignorer le message d'alerte,

En effet, en validant le calcul par le raccourci clavier CTRL + Entrée, nous conservons la cellule active. Excel nous informe la présence d'une référence circulaire. Le calcul ne peut être opéré dans la mesure où il attend le résultat de sa propre cellule. Mais dans la construction des résultats à suivre, cette mésaventure ne se produira plus.

Nous imbriquons donc la fonction Adresse dans la fonction Indirect. Cette dernière permet d'interpréter les coordonnées retournées pour restituer le contenu de la cellule ciblée.

• Cliquer de nouveau dans la base de données sur la cellule D6,

Cette fois, le contenu est restitué par le calcul en cellule G8. Cela signifie que nous sommes désormais en mesure d'interpréter la donnée cliquée par l'utilisateur sur la feuille Excel.

Repérer les données strictes et proches
Maintenant que les présentations sont faites, nous souhaitons offrir des résultats ergonomiques et faciles à interpréter. Dès lors que l'utilisateur clique sur une cellule, nous souhaitons surligner toutes les cases portant la même mention. Par exemple, un Hôtel/Restaurant est bien un Hôtel. Pour réaliser des comparaisons proches, nous proposons d'exploiter la fonction Excel Cherche :

=Cherche(Texte_Cherché; Texte_de_recherche)

En premier argument, en guise d'information cherchée, nous devons lui transmettre le contenu de la cellule cliquée. Il s'agit donc du calcul que nous avons éprouvé en cellule G8. En second argument, nous devons lui transmettre le texte dans lequel la recherche doit être opérée. Ce texte concerne chaque cellule de la base de données à passer en revue tour à tour. Et pour cela, il suffit de bâtir une règle de mise en forme conditionnelle. A chaque fois que la fonction Cherche renverra une valeur positive, la case concernée sera dynamiquement révélée.

• Sélectionner de nouveau la cellule G8,
• Dans sa barre de formule, sélectionner la syntaxe du calcul sans le symbole égal (=) en préfixe,
• La copier par le raccourci clavier CTRL + C,
• Puis, sortir de la barre de formule avec la touche Entrée pour ne pas détériorer le calcul,
• Sélectionner alors toutes les données de la base, sans les références, soit la plage B2:E998,
• Dans la section Styles du ruban Accueil, cliquer sur le bouton Mise en forme conditionnelle,
• En bas de la liste des propositions, choisir l'option Nouvelle règle,
• Dans la boîte de dialogue qui suit, sélectionner le type : Utiliser une formule pour ...,
• Puis, cliquer dans la zone de saisie juste en dessous pour l'activer,
• Taper le symbole égal (=) pour initier la syntaxe de la règle,
• Saisir la fonction de recherche suivie d'une parenthèse, soit : Cherche(,
• Coller alors (CTRL + V) la syntaxe précédemment copiée,
• Taper un point-virgule (;) pour passer dans l'argument du texte de recherche,
• Sélectionner la première cellule de la base de données, soit la cellule B2,
• Puis, enfoncer trois fois de suite la touche F4 du clavier pour la libérer totalement,

En effet, le contenu de la cellule cliquée doit être cherché dans toutes les cellules de la base de données. En la libérant totalement, nous la laissons suivre le raisonnement chronologique de la mise en forme conditionnelle. Celle-ci passe en revue et tour à tour, toutes les cellules sélectionnées.

• Fermer la parenthèse de la fonction Cherche,

L'inégalité du critère n'est effectivement pas nécessaire (>0). Dans une règle de mise en forme conditionnelle, la condition est implicite. Si la fonction cherche retourne une valeur positive, la règle est considérée comme satisfaite.

Désormais, si le contenu cliqué est effectivement trouvé, nous devons faire ressortir les cellules correspondantes dans des attributs de format spécifiques.

• Cliquer sur le bouton Format en bas de la boîte de dialogue,
• Dans la boîte de dialogue qui suit, activer l'onglet Remplissage,
• Dans la palette de couleurs, choisir un jaune pâle,
• Activer alors l'onglet Police de la boîte de dialogue,
• Avec la liste déroulante, choisir un rouge foncé pour la couleur du texte,
• Puis, valider ces réglages de format par le bouton Ok,

Nous sommes de retour sur la première boîte de dialogue. Elle rappelle les attributs de format dynamique à appliquer aux cellules satisfaisant la règle.

• Cliquer de nouveau sur le bouton Ok pour valider la création de cette règle,

De retour sur la feuille, la première information de la base de données surgit dans cette mise en forme spécifique. Dans une sélection, la première cellule est effectivement considérée comme active par défaut. Elle est donc exploitée par la règle pour rechercher son contenu.

• Cliquer par exemple sur la cellule D4,

Elle contient le terme restaurant. Son contenu est automatiquement analysé et comparé aux autres cellules de la base de données. Et instantanément, toutes les informations concordantes sont repérées.



Si vous cliquez sur la cellule D6, ce sont toutes les idées de sorties des hôtels qui surgissent automatiquement et dynamiquement.

Synthèse du contenu cliqué
Pour renforcer les résultats de cette solution fort ergonomique, nous proposons donc d'associer des synthèses numériques. Elles doivent intervenir en colonne H du petit tableau de synthèse. Nous devons tout d'abord rappeler le contenu cliqué et cherché. Pour cela, il suffit de répliquer la formule mettant en jeu les fonctions Indirect, Adresse et Cellule. Pour les dénombrements qui suivent, la fonction Nb.Si est tout à fait appropriée : =Nb.Si(Plage_où_chercher; Critère_à_dénombrer).

En premier argument, nous devons lui passer la plage de cellules sur laquelle le dénombrement doit être réalisé. Il s'agit des cellules de la base de données. En second paramètre, nous devons lui indiquer la valeur à y chercher. Ce deuxième argument doit donc faire référence au contenu cliqué.

• En cellule H2, recréer la formule suivante :

=INDIRECT(ADRESSE(CELLULE('ligne'); CELLULE('colonne')))

• En cellule H3, taper la syntaxe suivante :

=NB.SI(B2:E998; H2)

A validation, des indications de références circulaires peuvent surgir. Les raisons sont les mêmes que précédemment. Puisque la cellule est active, la formule se mord la queue.

• En cellule H4, réaliser le calcul suivant :

=NB.SI(B2:E998; '*'& H2 & '*')

C'est le symbole de l'étoile qui permet, par concaténation, de faire une recherche approximative sur le contenu cliqué. Ce dernier est effectivement restitué en cellule H2. Grâce à la fonction Nb.Si, il doit en résulter le dénombrement.

• Cliquer sur la cellule E4 pour la sélectionner,

Nous cherchons donc à obtenir des informations, notamment de proportions, sur la ville de Valence.

Comme vous pouvez le voir, les résultats visuels et numériques obtenus sont précieux. Toutes les informations concordantes sont dynamiquement repérées dans la base de données. La synthèse numérique surgit au clic sur la valeur. Elle démontre qu'il y a plus de résultats proches que de résultats stricts. En effet, d'autres villes comme Bourg-les-Valence ou encore Porte-Les-Valence sont présentes.