Extraire sur des conditions croisées avec Excel

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


Sujets que vous pourriez aussi aimer :


Extraction sur conditions croisées

Extraire des données selon des critères à recouper est tout à fait réalisable par calculs Excel. Et nous l'avions d'ailleurs démontré dans une formation. Mais l'astuce avait consisté à concaténer les données pour produire une recherche groupée. Il s'était donc agi de bâtir une formule intermédiaire en amont. Ici, nous apportons la solution permettant l'extraction multicritère à l'aide d'une seule formule.



Dans l'exemple de la capture ci-dessus, une base de données des clients est proposée. Mais une fois n'est pas coutume, ces derniers ne sont pas reconnus par un identifiant unique. Pour les retrouver et extraire les informations associées, nous pouvons réaliser une recherche sur le nom. Mais ce dernier est porteur d'homonymes. C'est pourquoi, il convient d'apporter la solution permettant de restituer ces résultats sur la recherche croisée du nom et du prénom.

Source et problématique
Pour débuter les travaux, il convient premièrement de réceptionner ces données nécessaires pour accueillir nos travaux.

• Télécharger le classeur extraire-conditions-croisees.xlsx 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 sur l'unique feuille de ce classeur. Elle est nommée Clients. Et pour cause, elle référence les clients de l'entreprise dans un tableau matérialisé entre les colonnes B et F. Vous notez la présence d'une zone de recherche entre les colonnes I et J. Elle est destinée à stipuler le nom et le prénom du client dont il convient d'extraire les informations associées, entre les lignes 7 et 9, pour obtenir la civilité, le code postal et la ville.



En effet, comme nous l'avons évoqué, aucun identifiant ne référence le client de façon unique.



Et si vous consultez attentivement la base de données des clients, vous constatez que des homonymes existent. Ces doublons interdisent une extraction précise sur la recherche du nom seul. Les fonctions d'extraction retourneraient les informations du premier client trouvé. Et il ne s'agirait pas forcément de celles désirées.

L'enjeu consiste donc à extraire les données précisément cherchées, par croisement des critères sur le nom et le prénom, en une seule formule. Il n'est pas question de calcul intermédiaire ici.

Repérer les données cherchées
Avant d'apporter la solution par les calculs et afin d'étayer les démonstrations, nous proposons tout d'abord de mettre en valeur dynamiquement les enregistrements cherchés, mais aussi les doublons sur le nom. Ces mises en forme conditionnelles permettront de faire la lumière sur la précision de l'extraction que nous produirons, selon les conditions croisées.

Comme vous le savez, une mise en forme conditionnelle permet de faire réagir automatiquement les cellules, par des couleurs par exemple, en fonction du contenu qu'elles portent. Nous proposons tout d'abord de mettre en évidence la ligne du client, reconnu par son nom et son prénom. Nous nous soucierons ensuite de faire ressortir les homonymes.

A l'instar d'une mise en forme classique et statique, une mise en forme conditionnelle requière que les cellules concernées, soient préalablement désignées.

• Cliquer sur la première cellule de la base de données, soit B5,
• Tout en maintenant la touche MAJ (Shift) enfoncée, cliquer sur la dernière, soit F44,

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

• Dans la section Styles du ruban Accueil, cliquer sur le bouton Mise en forme conditionnelle,
• En bas de la liste, choisir 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 située juste en-dessous pour l'activer,
• Taper le symbole égal (=) pour débuter la syntaxe de la règle,
• Saisir la fonction Et suivie d'une parenthèse, soit Et(,

C'est elle en effet qui permet d'énumérer les conditions à recouper.

• Sélectionner le premier nom de la base de données, soit la cellule C5,
• Puis, enfoncer deux fois de suite la touche F4 du clavier,

De cette manière, nous conservons le dollar, seulement devant l'indice de colonne ($C5). En effet, chaque nom du tableau doit être comparé au nom saisi dans la zone de recherche. Cette analyse doit donc se déplacer vers le bas. En revanche, pour une même ligne, la comparaison doit être réalisée sur le nom. Donc, la cellule ne doit pas bouger en colonne.

• Taper le symbole égal (=) pour l'égalité du premier critère à vérifier,
• Désigner le nom cherché, soit la cellule I5, ce qui donne : $I$5,

Cette fois, les deux dollars doivent être conservés. En effet, pour chaque cellule passée en revue dans la base de données, la première comparaison doit être réalisée par rapport à cette cellule de référence. Elle ne doit donc pas bouger. Ce premier critère consiste à reconnaître toutes les lignes portant le même nom de famille. Mais dans le même temps, cette condition doit être recoupée avec l'égalité sur les prénoms.

• Taper un point-virgule (;) pour poursuivre l'énumération des conditions,
• Sélectionner le premier prénom de la base de données, soit la cellule D5,
• Enfoncer deux fois la touche F4 pour la figer seulement en colonne, soit : $D5,
• Taper le symbole égal (=) pour l'égalité du second critère à vérifier,
• Désigner le prénom cherché, soit la cellule J5, ce qui donne : $J$5,
• Fermer la parenthèse de la fonction Et,

La syntaxe de la règle est donc la suivante : =Et($C5=$I$5;$D5=$J$5). Elle consiste à repérer toutes les lignes portant à la fois le nom et le prénom de la recherche. Et pour les identifier, nous devons faire varier la mise enforme.

• En bas de la boîte de dialogue, cliquer sur le bouton Format,
• Dans la boîte de dialogue qui suit, activer l'onglet Remplissage,
• Dans la palette de couleurs, choisir un vert pâle,
• Puis, activer l'onglet Police de la boîte de dialogue,
• Avec la liste déroulante Couleur, choisir le violet,
• Valider ces réglages en cliquant sur le bouton Ok,

Nous sommes de retour sur la première boîte de dialogue qui résume le contexte. Un fond vert et une police violette doivent se déclencher sur toute la ligne recoupant le nom et le prénom cherchés.

• Valider cette première règle de format dynamique en cliquant sur le bouton Ok,

Aussitôt, le client concerné est identifié.

• En cellule J5, remplacer le prénom Martin par le prénom Jean, puis valider,

Instantanément, la ligne de son homonyme est identifiée. Notre règle de mise en forme conditionnelle est donc parfaitement fonctionnelle. Désormais, nous souhaitons faire ressortir ces doublons sur le nom dans le même temps. Le critère est plus simple. Il consiste à opérer la correspondance, seulement sur le nom de famille.

• Sélectionner de nouveau la plage de cellules B5:F44,
• Cliquer sur le bouton Mise en forme conditionnelle dans le ruban Accueil,
• En bas de la liste, choisir Nouvelle règle,
• Dans la boîte de dialogue qui suit, sélectionner le type : Utiliser une formule pour...,
• Dans la zone source, créer la syntaxe suivante : =$C5=$I$5,
• Lui associer un fond jaune clair avec une police rouge rubis par exemple,

Il est intéressant de définir un repérage distinct pour bien différencier les doublons avec les noms des vraies correspondances.

• Valider cette règle en cliquant sur le bouton Ok de la boîte de dialogue,

Tous les clients portant le nom cherché sont désormais repérés automatiquement. Mais malheureusement, cette règle écrase la première. Or nous souhaitons conserver les deux identifications distinctes. Tout est une question de hiérarchie. Et c'est pour démontrer son importance, que nous avons volontairement empilé les règles dans le mauvais ordre. La règle recoupant les deux conditions doit se déclencher en dernier.

• Sélectionner de nouveau tout le tableau, soit la plage de cellules B5:F44,
• Cliquer une fois encore sur le bouton Mise en forme conditionnelle dans le ruban Accueil,
• Tout en bas de la liste, choisir Gérer les règles,
• Dans la boîte de dialogue, cliquer sur la règle verte des conditions recoupées,
• Puis, cliquer sur la flèche orientée vers le haut (Monter),

Nous la plaçons ainsi en tête de la hiérarchie. Sa mise en forme, après être appliquée, doit prendre le pas, sur les autres règles.

• Cliquer sur le bouton Ok de la boîte de dialogue pour appliquer cette modification,

Désormais, les clients sont repérés distinctement.

Trouver la ligne des conditions croisées
Maintenant, entre les colonnes I et J, il s'agit d'extraire les informations du client correspondant précisément à la recherche formulée sur le nom et le prénom. A l'issue, les couleurs dynamiques que nous avons mises en place, nous aiderons à corroborer les résultats.

La fonction Excel Equiv permet de trouver la ligne d'un élément cherché dans une colonne. Et nous l'avons démontrée à maintes reprises. Mais selon cette définition, elle ne recherche donc qu'un critère et ce, que dans une seule rangée.

=Equiv(Valeur_cherchée; Colonne_de_recherche; Mode_de_correspondance)

Dans notre problématique, nous devons chercher deux éléments tout en les recoupant, sachant que la recherche s'effectue dans des colonnes indépendantes. C'est un calcul matriciel, enclenché dans le deuxième argument de la fonction Equiv, celui de la colonne de recherche, qui apporte la solution. Et comme le mécanisme est fort inhabituel, une fois n'est pas coutume, nous proposons d'exploiter l'assistant fonction. Et pour y voir plus clair, nous proposons d'abord d'adapter les informations de recherche.

• En cellule I5, remplacer le nom Bonbeur par le nom Hamalibou,
• En cellule J5, remplacer le prénom Jean par le prénom Arlette,
• Puis, sélectionner la cellule J7 de la civilité à retourner,
• En haut de la fenêtre Excel, cliquer sur l'onglet Formules pour activer son ruban,
• Dans la section Bibliothèque de fonctions, cliquer sur le bouton Recherche et référence,
• Dans la liste, choisir la fonction Equiv,

Une boîte de dialogue apparaît. Elle propose d'aider à renseigner les trois arguments de la fonction Equiv, à l'aide de trois zones de saisie.

• Dans la zone Valeur cherchée, taper le chiffre 1,

Ce choix est tout à fait inhabituel. Logiquement, nous aurions dû désigner la cellule du nom ou celle du prénom afin de trouver la ligne correspondante. Mais, nous ne pouvons pas spécifier les deux à la fois. Ce chiffre fait office de valeur booléenne. Il est équivalent à Vrai. Il consiste à identifier la ligne de la correspondance que nous allons établir entre deux colonnes de recherche, dans l'argument suivant.

• Cliquer dans la zone Tableau de recherche pour l'activer,
• Ouvrir une parenthèse,
• Puis, désigner la colonne C (C:C),

Il est préférable d'écrire ses références plutôt que de cliquer sur l'étiquette de colonne C, en raison de la fusion de cellules en ligne 2.

• Taper l'opérateur égal (=) pour la correspondance à établir sur le nom,
• Puis, désigner la cellule du nom à trouver, soit I5,
• Enfoncer la touche F4 du clavier pour la figer, ce qui donne : $I$5,

En effet, dans un calcul matriciel, bien que toutes les lignes d'une matrice et de l'autre soient passées en revue, la comparaison doit toujours être établie par rapport à cette même cellule du nom.

• Fermer ensuite la parenthèse,

Nous venons de définir la comparaison à réaliser dans la première matrice, soit dans la colonne du nom. Nous devons recouper cette dernière avec le critère sur la seconde matrice, celle de la colonne du prénom.

• Taper le symbole étoile (*) du pavé numérique,

C'est lui dans ce contexte, qui permet d'effectuer l'analyse recoupée sur les deux matrices.

• Ouvrir une nouvelle parenthèse et désigner la colonne du prénom, soit D:D,
• Taper le symbole égal (=) pour la correspondance à établir sur le prénom,
• Puis, désigner la cellule du prénom cherché, soit la cellule J5,
• Enfoncer alors la touche F4 du clavier pour la figer, ce qui donne : $J$5,
• Puis, fermer la parenthèse de cette seconde matrice de correspondance,
• Dans la zone type, taper le chiffre 0 pour réaliser une recherche sur les valeurs exactes,

La synthèse fournie par l'assistant fonction est très intéressante. Sur la droite de l'argument tableau de recherche, il offre un résultat sous forme de matrice. Une position est repérée par le chiffre 1. Ce dernier indique que la recherche est fructueuse à cet emplacement sur les conditions recoupées, ainsi définies. Et précisément, dans l'argument de la valeur cherchée, en connaissance de cause, nous lui avons transmis ce chiffre. L'assistant retourne lui-même le chiffre 1 pour Vrai. En d'autres termes, il confirme que la recherche est un succès. Et sous la zone Type, vous remarquez la présence du chiffre 8. Il s'agit de la position du client ainsi strictement repéré.

• Cliquer sur le bouton Ok l'assistant pour valider ce calcul,

De retour sur la feuille, le résultat est tout à fait surprenant. C'est un message d'erreur qui est retourné malgré les bons pressentiments que nous pouvions avoir depuis l'assistant. #N/A signifie Not Availiable, soit Non disponible. Tel que nous l'avons validée, la formule a réalisé un calcul classique, sans tenir compte des matrices dont il s'agit de recouper les lignes entre elles.



Pour qu'un calcul matriciel prenne effet, une manipulation tout à fait particulière doit être réalisée au clavier.

• Double cliquer sur la cellule J7 pour activer sa modification,
• Puis, valider avec le raccourci clavier CTRL + MAJ + Entrée,

La touche MAJ est aussi connue sous la désignation anglaise Shift. De fait, la conclusion du calcul est toute autre. Et si vous consultez la formule, sa syntaxe est encadrée d'accolades : {=EQUIV(1;(C:C=$I$5)*(D:D=$J$5);0)}

Elles représentent ce calcul matriciel, dont la vocation est de recouper les lignes des matrices désignées. Et le résultat retourné est cohérent. Le client cherché est bien placé sur la ligne 8 de la feuille. Si vous modifiez le prénom Arlette par Gisèle, vous remarquez que l'identification de ligne se déplace, en même temps d'ailleurs que la mise en forme conditionnelle.

Notre formule est donc tout à fait fonctionnelle, mais elle n'est pas achevée.

Extraire l'information d'une ligne
C'est la fonction Excel Index qui permet de restituer l'information située au croisement d'une ligne et d'une colonne :

=Index(Tableau_de_recherche; Indicle_de_ligne; Indice_de_colonne)

La ligne est variable et nous venons de la trouver. Nous devons donc imbriquer ce calcul matriciel de la fonction Equiv dans le deuxième argument de la fonction Index. La colonne est fixe. Pour la civilité, dans le tableau de recherche, il s'agit de la première colonne. Ce numéro doit être renseigné en troisième argument de la fonction Index. Le tableau de recherche est compris entre les colonnes B et F. Cette plage (B:F) doit être renseignée en premier paramètre de la fonction.

• Sélectionner de nouveau la cellule J7,
• Dans la barre de formule, adapter sa syntaxe comme suit :

=INDEX(B:F;EQUIV(1;(C:C=$I$5)*(D:D=$J$5);0);1)

• Et surtout valider avec le raccourci CTRL + MAJ + Entrée,

La civilité Madame est fort logiquement retournée. En revanche, si vous supprimez l'une des informations de recherche, une erreur surgit. Nous l'avons appris à nos dépends, une fonction d'extraction, lorsqu'elle ne trouve pas la valeur demandée, renvoie une erreur. Nous devons donc l'imbriquer dans une fonction capable de les gérer :

=SiErreur(Calcul_a_essayer;Action_si_erreur)

• De fait, par la barre de formule, adapter la syntaxe comme suit :

=SIERREUR(INDEX(B:F;EQUIV(1;(C:C=$I$5)*(D:D=$J$5);0);1);'')

• Valider cette modification nécessairement par le raccourci CTRL + MAJ + Entrée,

Les deux guillemets dans le second argument de la fonction SiErreur indiquent de conserver la cellule vide, en cas d'anomalie. Dans le cas contraire, l'extraction est bien produite.

La formule matricielle finalisée est donc la suivante :

{=SIERREUR(INDEX(B:F;EQUIV(1;(C:C=$I$5)*(D:D=$J$5);0);1);'')}

Comme nous avons judicieusement figé les cellules impliquées dans le calcul, nous pouvons adapter la formule en toute simplicité, pour extraire le code postal et la ville.

• Tirer la poignée de la cellule J7 sur les deux cellules du dessous,
• Dans la barre de formule de J8, remplacer la colonne de recherche 1 par l'indice 4,
• Puis, valider par le raccourci CTRL + MAJ + Entrée,
• Dans la barre de formule de J9, remplacer la colonne de recherche 1 par l'indice 5,
• Puis, valider par le raccourci CTRL + MAJ + Entrée,

Désormais, si vous modifiez les éléments de recherche, vous avez le plaisir de constater que le client correspondant aux conditions recoupées, est parfaitement extrait.



Dans l'exemple de la capture ci-dessus, en l'absence du calcul matriciel, la recherche sur le nom se serait arrêtée sur le premier client. Ici, c'est bien le suivant qui est précisément pointé.