Synthétiser les données à l'intersection de plages

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


Sujets que vous pourriez aussi aimer :


Consolider les données à l'intersection

Dans certaines formations Excel, nous avons appris à extraire de l'information selon des éléments de recherche. Et pour cela, nous avons exploité la fonction RechercheV ou encore les fonctions Index et Equiv imbriquées. Excel est définitivement puissant et regorge de surprises. La méthode que nous présentons ici propose de court-circuiter ces fonctions, à la syntaxe relativement complexe, pour produire en toute simplicité, des extractions puissantes et surprenantes. L'idée consiste à raisonner sur l'intersection de plages de cellules, soit sur des cellules croisées.

Source et présentation de la problématique
Pour débuter les démonstrations, nous proposons de réceptionner un classeur offrant des données numériques et textuelles à manipuler.

• Télécharger le classeur ventes-intersection.xlsx en cliquant sur ce lien,
• Double cliquer sur le fichier pour l'ouvrir dans Excel,
• Puis, cliquer sur le bouton Activer la modification du bandeau de sécurité,

Nous débouchons ainsi sur un classeur constitué de trois feuilles. Elles sont nommées respectivement : Ventes, Console et Catalogue.



La feuille active par défaut est la feuille Ventes. Elle synthétise les chiffres d'affaires réalisés par un aéroport. Les destinations sont énumérées en colonne B. Pour le trimestre en vigueur, les chiffres sont listés entre les colonnes C et E.

Vous notez la présence d'une petite console en colonne H. Au choix d'une période à suggérer par le biais d'une liste déroulante, ainsi que d'une destination selon le même mode opératoire, la consolidation du chiffre d'affaires réalisé, doit apparaître dans la cellule du total. Ce mécanisme ergonomique doit offrir une vue synthétisée et instantanée sur demande, afin de confronter les résultats.

Les périodes peuvent très bien concerner un mois isolé ou un trimestre les regroupant. Une destination peut très bien désigner une ville ou un ensemble correspondant à une catégorie. Nous y reviendrons.

• En bas de la fenêtre Excel, cliquer sur l'onglet Console pour activer sa feuille,
• Cliquer ensuite sur la cellule C5 pour la sélectionner,

Comme vous le remarquez, des références se suggèrent dans une liste déroulante préconçue. Sa vocation est de déterminer un produit. En retour, nous devons être capable d'extraire les informations situées au croisement de la référence et de l'information demandée. Cette dernière est stipulée par les titres situés au-dessus des zones de saisie.



Mais pour extraire de telles données, faut-il encore pouvoir se référer à une base de données.

• En bas de la fenêtre Excel, cliquer sur l'onglet Catalogue pour activer sa feuille,

La base de données, la voici. Les références à choisir y sont énoncées en colonne B. Et les données relatives à extraire sont placées dans les colonnes voisines.

Désigner l'intersection
Avant de produire des extractions en bonne et due forme, nous proposons de constater quelle méthode est proposée par Excel, pour restituer des données croisées. Ainsi, nous comprendrons le mécanisme à construire pour offrir des consoles ergonomiques d'extraction aux administrateurs.

• En bas de la fenêtre Excel, cliquer sur l'onglet Ventes pour revenir sur sa feuille,
• Cliquer sur l'étiquette de ligne 10 pour sélectionner les résultats de la ville de Rome,
• Tout en maintenant la touche CTRL enfoncée, cliquer sur l'étiquette de colonne E,

De la sorte, nous désignons en même temps les résultats pour le mois d'Août. Et au croisement se situe une valeur qui correspond à l'intersection des deux plages. Cette donnée rappelle le chiffre d'affaires réalisé pour la destination de Rome au mois d'Août.



Le besoin est d'extraire cette information sur demande. Pour des résultats dynamiques, nous exploitons habituellement les fonctions d'extraction. Mais dans sa syntaxe, Excel propose naturellement de matérialiser l'opérateur d'intersection afin d'extraire la ou les données situées au croisement. Pour le démontrer, nous proposons de nous placer dans une cellule arbitraire.

• Cliquer en dessous du total, sur la cellule H13 pour l'activer,
• Taper le symbole égal (=) pour débuter le calcul,
• Cliquer sur l'étiquette de ligne 10 pour spécifier l'intégralité de la ligne de Rome, entre autres,
• Enfoncer la barre d'espace du clavier pour créer une séparation dans la construction,
• Puis désigner la colonne intégrale du mois d'Août, en tapant ses références, soit : E:E,

Le titre fusionné au-dessus du tableau peut en effet contrarier la sélection indépendante de la colonne. Bref, les plages de cellules sont mises en évidence par des jeux de couleurs. La donnée située à l'intersection, soit le CA pour Rome en Août, ressort de façon évidente.

• Valider cette syntaxe à l'aide de la touche Entrée du clavier.

Le résultat fourni n'est autre que la donnée située au croisement des deux plages spécifiées. Il s'agit bien du chiffre d'affaires réalisé au mois d'Août pour la destination de Rome. Dans cette syntaxe, nous n'avons utilisé aucun opérateur. C'est l'espace délimitant les plages de cellules énumérées qui fait office d'opérateur d'intersection. Et c'est bien la donnée croisée qui est extraite, sans même avoir à utiliser les fonctions d'extraction, à la construction plus complexe.

Ce mécanisme nous donne des idées, si d'aventure nous étions en mesure de spécifier ces plages dynamiquement. Et c'est ce que nous allons faire. Mais avant cela, nous proposons de poursuivre les remarques.

Désormais donc, nous souhaiterions étendre ce principe pour consolider les chiffres situés aux croisements de plusieurs villes et plusieurs mois.

• Sélectionner de nouveau la cellule H13,
• Taper le symbole égal (=) pour initier la syntaxe et remplacer l'ancienne,
• Désigner les villes de Paris et Londres en cliquant et glissant sur les étiquettes de ligne 6 et 7,
• Enfoncer la barre d'espace du clavier pour matérialiser l'opérateur d'intersection,
• Puis, désigner les mois de Juillet et Août en inscrivant les références, soit : D:E,

Toujours par les jeux de couleurs, nous identifions quatre cellules, soit quatre chiffres situés aux croisements des plages.

• Valider la syntaxe par la touche Entrée du clavier,

Cette fois, le résultat retourné conduit à une erreur. Nous pourrions penser que cette méthode a des limites. Mais il n'en est rien. Contrairement au contexte précédent, nous croisons plusieurs cellules, numériques qui plus est. Nous devons indiquer à Excel quelle est l'opération à engager.

• Sélectionner de nouveau la cellule H13,
• Taper le symbole égal (=) pour initier la syntaxe et remplacer l'ancienne,
• Saisir la fonction pour l'addition suivie d'une parenthèse, soit : Somme(,
• Sélectionner les deux lignes 6 et 7, ce qui donne : 6:7,
• Enfoncer la barre d'espace pour l'opérateur d'intersection,
• Saisir les références pour Juillet et Août, soit : D:E,

Comme précédemment, les jeux de couleurs permettent d'identifier sans équivoque, les cellules placées au croisement des deux plages à sommer.

• Fermer la parenthèse de la fonction Somme,
• Puis, valider le calcul par la touche Entrée du clavier,

Cette fois, un résultat numérique est bien retourné. Et si vous sélectionnez les quatre cellules concernées par l'intersection, en consultant la barre d'état Excel, vous remarquez que l'addition des valeurs conduit bien au résultat extrait par la formule.



Cette technique est donc définitivement souple et efficace pour produire des résultats de synthèse, sans faire appel aux fonctions d'extraction.

Référencer les plages de cellules
Pour profiter de cette simplicité et produire des résultats dynamiques, il est donc primordial de pouvoir désigner des plages de cellules repérées. Pour qu'elles soient énumérées dans des listes déroulantes, elles doivent être reconnues par des noms. Nous devons donc nommer les colonnes, lignes ou groupes qui nous intéressent.

• Cliquer sur la première cellule du tableau, soit B5,
• Tout en maintenant la touche Maj (Shift) enfoncée, cliquer sur la dernière, soit F19,

Cette technique permet d'englober dans la sélection, toutes les cellules comprises entre la première et la dernière.

• En haut de la fenêtre Excel, cliquer sur l'onglet Formules pour activer son ruban,
• Dans la section Noms définis du ruban, cliquer sur le bouton Depuis sélection,

Une boîte de dialogue apparaît. Sa vocation est d'attribuer automatiquement des noms aux plages de cellules, en fonction des en-têtes de lignes et colonnes, soit des titres.

• Conserver cochées les cases Ligne du haut et Colonne de gauche,
• Puis, valider en cliquant sur le bouton Ok,

Une alerte apparaît incitant à remplacer la définition existante de la plage Total. Deux titres proposent en effet cette même désignation. Nous aurions dû les différencier. Mais il est intéressant de constater le conflit pour l'anticiper à l'avenir.

• Cliquer sur le bouton Oui pour poursuivre,
• En haut à gauche de la feuille Excel, déployer la liste déroulante des noms,

Elle était vierge jusqu'alors. Désormais, tous les titres du tableau sont représentés. Et si vous sélectionnez l'un d'entre eux, c'est la plage correspondante qui est désignée. Notez que les espaces dans les noms ont été remplacés par des Under score (_). Nous l'évoquons à chaque occasion, à plus forte raison en programmation VBA Excel, les caractères latins et les espaces ne sont pas autorisés dans les noms d'objet. Nous devrons agir en conséquence pour désigner une telle plage.

Mais pour des résultats de synthèse pertinents, nous ne souhaitons pas en rester là. Nous avons précédemment compris comment consolider de multiples données croisées. Sur la droite de la feuille, entre les colonnes M et N, vous notez la présence de données de référence. Dans la première des deux, les trois mois y sont énumérés, suivis de la mention Trimestre. L'objectif est effectivement de disposer d'une plage de cellules offrant un recoupement global, sur la totalité de l'exercice.

• Cliquer et glisser sur les étiquettes de colonne C, D et E pour les sélectionner,
• Dans la zone Nom en haut à gauche de la feuille, saisir : Trimestre,
• Puis, valider ce nom à l'aide de la touche Entrée du clavier,

Sans cette validation, cette dénomination n'est pas prise en compte.

Dans la deuxième colonne des données de référence, vous notez l'énumération des premières villes de destination. Elles sont suivies de catégories les globalisant et représentant un secteur géographique plus large. Il sera en effet intéressant de pouvoir confronter les chiffres d'affaires entre les deux Europes, l'Asie et les Amériques.

• Sélectionner les lignes 6 à 8 par les étiquettes,
• Dans la zone Nom, saisir Europe_Nord et valider par la touche Entrée du clavier,

Souvenez-vous, l'under score se réalise à l'aide de la touche 8 en haut du clavier. Nous devons repérer les autres secteurs géographiques de la même façon.

• Sélectionner les lignes 9 à 11 et les nommer : Europe_Sud,
• Sélectionner les lignes 12 à 15 et les nommer : Ameriques,
• Sélectionner enfin les lignes 16 à 18 et les nommer : Asie,

Désigner les intersections
Désormais, en colonne H, nous devons proposer de choisir une période et un secteur existants. Il s'agit de construire de simples listes déroulantes sur la base des données situées entre les colonnes M et N.

• Sélectionner la cellule de la période, soit H7,
• En haut de la fenêtre Excel, cliquer sur l'onglet Données pour activer son ruban,
• Dans la section Outils de données du ruban, cliquer sur le bouton Validation des données,
• Dans la section Autoriser de la boîte de dialogue qui suit, choisir Liste,
• Cliquer alors dans la zone de saisie Source qui se propose juste en-dessous,
• A la souris, désigner les quatre cellules des périodes soit la plage M4:M7,

Cette liste déroulante, construite manuellement, est désormais susceptible de proposer l'une des périodes ainsi prévues.

• Cliquer sur le bouton Ok de la boîte de dialogue pour valider ces réglages,

De retour sur la feuille, vous constatez qu'une liste déroulante se propose en effet. Si vous la déployez, les périodes y sont suggérées.

• Sélectionner maintenant la cellule des destinations, soit H9,
• Dans le ruban Données, cliquer sur le bouton Validation des données,
• Dans la section Autoriser de la boîte de dialogue, choisir Liste,
• Cliquer alors dans la zone Source juste en-dessous pour l'activer,
• Sélectionner ensuite les destinations, soit la plage de cellules N4:N14,
• Puis, valider la construction de la liste déroulante en cliquant sur le bouton Ok,

De retour sur la feuille, les destinations sont effectivement proposées sous forme de liste de choix.

Consolider les données au croisement
Maintenant que les plages peuvent être désignées par des listes déroulantes, les intersections vont surgir d'elles-mêmes. A priori, il ne nous reste plus qu'à reproduire les techniques précédentes pour enclencher l'opérateur de croisement. Mais en lieu et place des plages sélectionnées, nous devons mentionner leurs noms, issus des choix réalisés dans les listes. Cependant, ces noms seront interprétés comme des textes par Excel et non comme des plages. L'astuce consiste à les désigner dans la fonction Indirect. Comme son nom l'indique, cette fonction permet à Excel de considérer indirectement la plage mentionnée.

• Sélectionner la cellule du résultat de synthèse, soit H11,
• Taper le symbole égal (=) pour débuter la formule de consolidation,
• Saisir la fonction d'addition suivie d'une parenthèse, soit : Somme(,
• Taper la fonction pour interpréter les noms, suivie d'une parenthèse, soit : Indirect(,
• Désigner la période en cliquant sur la cellule H7,
• Fermer la parenthèse de la fonction Indirect,
• Enfoncer la barre d'espace pour enclencher l'opérateur d'intersection,
• Taper de nouveau la fonction d'interprétation suivie d'une parenthèse,   soit : Indirect(,
• Désigner la destination en cliquant sur la cellule H9,
• Fermer la parenthèse de la fonction Indirect, puis fermer la parenthèse de la fonction Somme,
• Enfin, valider le calcul en enfonçant la touche Entrée du clavier,

Dans le cas où aucun critère n'est défini par le biais des listes déroulantes, un message d'erreur apparaît fort naturellement. Aucune intersection ne peut être produite. Nous pallierons ce défaut dans l'enchaînement.

• A l'aide de la première liste déroulante, choisir Trimestre,
• Avec la seconde liste déroulante, choisir la ville de Paris,

Le résultat est cette fois consolidé. Et il est tout à fait cohérent dans la mesure où il correspond au total fourni en bout de ligne pour la ville de Paris. Les valeurs numériques croisées par les deux plages de cellules ainsi mentionnées, ont donc parfaitement été additionnées. Sachez que si des données textuelles sont incluses dans ces croisements, la fonction Somme a la faculté de les ignorer.

La formule que nous avons bâtie à ce stade est la suivante : =SOMME(INDIRECT(H7)INDIRECT(H9)).

• A l'aide de la seconde liste déroulante, sélectionner désormais le secteur Europe Nord,

Cette fois, le même calcul produit une erreur, bien que deux plages soient a priori explicitement sélectionnées. La raison est pourtant logique. Souvenez-nous, dans les noms attribués, Excel remplace systématiquement chaque espace par un under score (_). Il connaît bien la plage Europe_Nord mais pas Europe Nord.



Dans la formule, nous devons retravailler les plages nommées grâce à la fonction Subtitue. Nous l'avions présentée à l'occasion de la formation Excel sur les fonctions de traitement de textes.

SUBSTITUE(Texte_à_traiter;Texte_à_remplacer;Texte_de_replacement)

Sur un texte à passer en premier paramètre (Le nom de la plage), elle permet de remplacer un bout de texte, l'espace à mentionner en deuxième paramètre par un autre, l'Under Score à définir en dernier argument. De même, nous devons englober le tout dans la fonction de gestion des anomalies SiErreur : = SiErreur(Calcul_à_tenter; Traitement_du_défaut). Ainsi, nous gèrerons le cas où aucune plage n'est définie par le biais des listes déroulantes.

• Dans la barre de formule de la cellule H11, adapter la syntaxe du calcul, comme suit :

=SIERREUR(SOMME(INDIRECT(SUBSTITUE(H7;' ';'_')) INDIRECT(SUBSTITUE(H9;' ';'_')));'')

A validation, toutes les extractions consolidées s'opèrent désormais parfaitement. Nous avons donc démontré l'intérêt d'une méthode fort précieuse pour consolider les données numériques en simulant l'opérateur d'intersection.

Extraire du texte au croisement
Maintenant, nous allons voir que ce mécanisme est redoutable d'efficacité et de simplicité pour extraire de l'information de base de données, en se substituant avec brio aux fonctions de recherches. Jusqu'alors, nous avons agi sur des données numériques à consolider. Désormais, nous devons restituer l'information textuelle située au croisement d'une demande spécifique.

• En bas de la fenêtre Excel, cliquer sur l'onglet Console pour activer sa feuille,
• Puis, sélectionner la cellule C5,

Comme vous le constatez, cette dernière est munie d'une liste déroulante. Elle énumère toutes les références des articles archivés dans la feuille Catalogue. Sur demande spécifique d'une référence, toutes les informations attachées doivent être importées dans les zones prévues à cet effet. Chacune est judicieusement titrée comme les champs du tableau de données.

Donc, pour la mise en place du mécanisme d'importation par le croisement des plages, chaque référence doit désigner une plage en ligne, et chaque titre doit désigner une plage en colonne.

• En bas de la fenêtre Excel, cliquer sur l'onglet Catalogue pour activer sa feuille,
• Puis, sélectionner l'une des cellules du tableau, par exemple C3,
• Réaliser alors le raccourci clavier CTRL + A,

Cette combinaison de touches, dévoilée dans le support des trucs et astuces, permet de sélectionner toutes les cellules du tableau, lorsque l'une d'entre elles est présélectionnée. Le cas échéant, ce sont toutes les cellules de la feuille qui sont intégrées dans la sélection.

• En haut de la fenêtre Excel, cliquer sur l'onglet Formules pour activer son ruban,
• Dans la section Noms définis du ruban, cliquer sur le bouton Depuis sélection,
• Dans la boîte de dialogue, conserver cochées les cases Ligne du haut et Colonne de gauche,

Ainsi, chaque référence représente désormais une ligne reconnue par un nom. De même, chaque champ représente une colonne reconnue par son titre.

• Valider cette construction automatique de plages nommées en cliquant sur le bouton Ok,

Si vous déployez la liste déroulante de la zone Nom, vous remarquez que les plages référencées deviennent très nombreuses. Mais nous n'avons pas à les manipuler humainement. Toute la gestion doit s'automatiser, en commençant par la sélection dans la liste déroulante. Une remarque est néanmoins importante avant de débuter la mise en oeuvre. Les tirets, au même titre que les espaces, ne semblent pas autorisés dans les noms attribués aux plages de cellules. Ils ont automatiquement été remplacés par des Under Score. La fonction Substiue décidément précieuse, est donc de mise une fois encore.

• Revenir sur la feuille Console,
• Sélectionner la désignation à importer, soit la cellule C8,
• Taper le symbole égal (=) pour initier le calcul,
• Saisir la fonction de gestion d'erreur suivie d'une parenthèse, soit : SiErreur(,
• Saisir la fonction d'interprétation suivie d'une parenthèse, soit : Indirect(,
• Saisir la fonction de remplacement de texte, suivie d'une parenthèse, soit : Substitue(,
• Indiquer le texte à retravailler en cliquant sur la référence en C5,
• Taper un point-virgule (;) pour passer dans l'argument du texte à remplacer,
• Inscrire un tiret entre guillemets, soit : '-',
• Taper un point-virgule (;) pour passer dans l'argument du texte de remplacement,
• Saisir un Under Score entre guillemets, soit : '_',
• Fermer la parenthèse de la fonction Substitue,
• Fermer la parenthèse de la fonction Indirect,
• Enfoncer la barre d'espace pour matérialiser l'opérateur d'intersection,
• Saisir la fonction d'interprétation suivie d'une parenthèse, soit : Indirect(,
• Sélectionner le titre représentant la plage à recouper, soit la cellule C7,
• Fermer la parenthèse de la fonction Indirect,
• Taper un point-virgule (;) pour passer dans l'argument de la gestion d'erreur,
• Inscrire un espace entre guillemets, soit : ' ',
• Fermer la parenthèse de la fonction SiErreur,
• Puis, valider la formule à l'aide de la touche Entrée du clavier,

La désignation est aussitôt extraite. La formule est relativement simple. Seule l'imbrication de la fonction Substitue vient légèrement compliquer sa syntaxe :

=SIERREUR(INDIRECT(SUBSTITUE(C5;'-';'_'))INDIRECT(C7);' ')

Elle reste néanmoins triviale par rapport aux constructions imposées par les fonctions de recherche. De plus, son efficacité est redoutable.



Pour produire l'extraction complète, il s'agit de répliquer la même formule dans les autres champs. La seconde plage à croiser doit être adaptée sur le titre du champ.