Extraire les données recoupées avec une seule formule

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


Sujets que vous pourriez aussi aimer :


Extraction complète des données

Extraire toutes les données attachées à une référence ou à des conditions croisées n'est pas une mince affaire. Néanmoins avec des techniques standards et des calculs intermédiaires de repérage, nous avions appris à importer ces informations affinées. Mais avec les calculs matriciels, il est possible de réaliser cette prouesse à l'aide d'une seule formule.



Dans l'exemple finalisé illustré par la capture, nous travaillons sur une base de données d'un parc automobile. Au choix d'une marque recoupée par un modèle, nous réalisons l'importation des kilométrages et prix de tous les véhicules correspondants.

Source et présentation
Une base de données est nécessaire pour réaliser cette étude.

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

Des véhicules sont donc recensés, notamment sur leur marque et leur modèle dans un tableau de près de 100 lignes et s'étendant de la colonne B à la colonne G. A l'inscription d'une marque et d'un modèle précis en cellules respectives I4 et J4, nous devons offrir l'extraction du détail correspondant sur le kilométrage et le prix. Cette extraction doit être faite dans les mêmes colonnes, à partir de la ligne 8.

Si vous déployez la liste déroulante de la zone Nom, en haut à gauche de la feuille Excel, vous notez que chaque colonne de la base est identifiée par son titre de champ. Naturellement, nous exploiterons ces noms dans la construction de la formule matricielle d'extraction.



Extraire sur des critères croisés
Pour extraire des données, la fonction Index est forcément nécessaire. Pour repérer les positions en ligne des enregistrements concordants, nous devons exploiter la fonction Equiv. Mais ces positions sont soumises à contraintes. Elles doivent honorer la marque et le modèle demandés. Une double fonction Si doit donc tester ces correspondances. Pour réunir ces données éparpillées les unes en dessous des autres dans le tableau d'extraction, nous devons déployer la fonction Grande.Valeur. En l'exerçant sur les positions décelées, elle ignorera toutes les données non correspondantes.

• Sélectionner une dizaine de cellules pour les kilométrages à extraire, soit la plage I8:I17,
• Taper le symbole égal (=) pour débuter la formule matricielle,
• Inscrire la fonction de gestion des anomalies suivie d'une parenthèse, soit : SiErreur(,

Cette fonction est nécessaire pour tester l'extraction et neutraliser les messages retournés en cas de recherches infructueuses.

• Taper la fonction d'extraction suivie d'une parenthèse, soit : Index(,
• Désigner les valeurs à extraire par le nom de plage, soit : Km,
• Taper un point-virgule (;) pour passer dans l'argument des positions à repérer en ligne,
• Saisir la fonction pour réunir ces données par ordre décroissant, soit : Grande.Valeur(,

Nous pourrions tout aussi bien exploiter la fonction Excel Petite.Valeur. Nous obtiendrions une extraction organisée dans l'ordre inverse des positions repérées.

• Inscrire la fonction conditionnelle suivie d'une parenthèse, soit : Si(,
• Désigner la plage des marques par son nom, soit : Marque,
• Inscrire le symbole de concaténation (&),
• Puis, désigner la plage des modèles par son nom, soit : Modèle,
• Taper le symbole égal (=) pour annoncer la condition à honorer,
• Cliquer sur la marque demandée, soit la cellule I4,
• Taper de nouveau le symbole de concaténation (&) pour joindre les conditions,
• Puis, cliquer sur le modèle demandé, soit la cellule J4,

Cette astuce permet de court-circuiter l'emploi d'une double fonction Si. Nous assemblons les critères émis sur la marque et le modèle. Et nous vérifions leur présence résultante dans les matrices réunies de la même façon.

• Taper un point-virgule (;) pour passer dans la branche Alors de la fonction Si,
• Inscrire la fonction pour trouver les positions concordantes, soit : Equiv(,
• En guise de valeur cherchée, désigner la plage des kilomètres par son nom, soit : Km,
• Taper un point-virgule (;) pour passer dans l'argument du tableau de recherche,
• Désigner de nouveau la même plage par son nom, soit : Km,

Ainsi inscrite dans un raisonnement matriciel, la fonction Equiv va trouver toutes les positions de chaque enregistrement. En effet, chaque kilométrage va être trouvé dans sa propre plage. Mais, comme elle est imbriquée dans la fonction conditionnelle, seules les positions concordant avec les critères recoupés sur la marque et le modèle seront retenues.

• Taper un point-virgule suivi du chiffre zéro, soit : ;0, pour réaliser une recherche exacte,
• Fermer la parenthèse de la fonction Equiv,
• Puis, fermer la parenthèse de la fonction Si,

Nous ne prenons pas le soin de renseigner la branche sinon de cette dernière. Ce cas ne nous intéresse pas. De plus, les anomalies seront gérées par la fonction SiErreur.

• Taper un point-virgule (;) pour passer dans l'argument du rang de la fonction Grande.Valeur,

Nous ne souhaitons pas extraire un seul kilométrage en fonction de son rang. Nous souhaitons extraire tous les kilométrages concordants. Dans ce raisonnement matriciel, nous devons donc être en mesure de prévoir tous les rangs potentiels repérés. Et pour cela, qui peut le plus peut le moins. Nous allons donc construire une matrice virtuelle de même hauteur que la base de données.

• Saisir la fonction donnant la ligne d'une cellule suivie d'une parenthèse, soit : Ligne(,

Ce sont tous les indices de rangs qui nous intéressent et pas seulement l'un d'entre eux. En guise de cellule, nous allons lui transmettre une matrice virtuelle. Elle doit donc être interprétée dans le calcul.

• Inscrire la fonction d'interprétation suivie d'une parenthèse, soit : Indirect(,
• Inscrire le chiffre 1 suivi du symbole deux point entre guillemets, soit : '1:',

Nous renseignons ainsi le point de départ de la matrice virtuelle. En débutant de la première ligne, nous réceptionnerons le premier rang, soit le plus grand. Donc, le dernier résultat trouvé dans le tableau sera extrait en premier. Le symbole deux points annonce la borne inférieure à suivre.

• Ajouter le symbole de concaténation (&) pour continuer la construction de cette matrice,
• Inscrire la fonction pour compter le nombre de lignes, suivie d'une parenthèse, soit : Lignes(,

Attention, il s'agit du pluriel de son homologue Ligne qui renvoie seulement l'indice d'une cellule.

• Désigner la matrice des kilomètres par son nom, soit : Km,
• Fermer la parenthèse de la fonction Lignes,
• Fermer la parenthèse de la fonction Indirect,
• Fermer la parenthèse de la fonction Ligne,
• Fermer la parenthèse de la fonction Grande.Valeur,
• Puis, fermer la parenthèse de la fonction Index,

Nous ne renseignons donc pas le troisième argument de cette dernière. Il s'agit de la position en colonne pour l'information à extraire. Mais en guise de tableau d'extraction, nous lui avons fourni la matrice Km en premier argument. Cette dernière n'est constituée que d'une colonne. C'est donc tout à fait naturellement que la fonction Index procèdera sur celle-ci.

• Taper un point-virgule (;) pour passer dans le second argument de la fonction SiErreur,
• Inscrire deux guillemets pour neutraliser et ignorer les anomalies,
• Fermer la parenthèse de la fonction SiErreur,
• Enfin, valider nécessairement le calcul par le raccourci clavier CTRL + MAJ + Entrée,

Les premiers résultats tombent. Les kilométrages sont parfaitement extraits, conformément aux attentes des critères recoupés. Si vous consultez le tableau source, c'est bien le kilométrage le plus bas dans la liste qui est d'abord extrait, puis les suivants dans l'ordre en remontant vers le haut de la colonne. Et nous avons produit cette extraction avec une seule formule matricielle :

{=SIERREUR(INDEX(Km; GRANDE.VALEUR(SI(Marque & Modèle= I4 & J4; EQUIV(Km; Km; 0)); LIGNE(INDIRECT('1:' & LIGNES(Km))))); '')}

Pour une organisation croissante des informations, respectant l'ordre établi dans la base de données, il suffit de remplacer la fonction Grande.Valeur par la fonction Petite.Valeur. Naturellement, il ne faut pas oublier de présélectionner la plage et de revalider le calcul par le raccourci clavier CTRL + MAJ + Entrée.

{=SIERREUR(INDEX(Km; PETITE.VALEUR(SI(Marque & Modèle= I4 & J4;EQUIV(Km; Km; 0));LIGNE(INDIRECT('1:' & LIGNES(Km)))));'')}



L'extraction des prix associés est une formalité désormais. Il suffit simplement d'adapter la matrice en premier argument de la fonction Index.

• Sélectionner la cellule de la précédente extraction, soit : I8,
• Sélectionner toute la syntaxe du calcul dans sa barre de formule,
• La copier avec le raccourci CTRL + C par exemple,
• Sortir de la barre de formule par le raccourci CTRL + MAJ + Entrée
• Sélectionner les cellules des prix à extraire, soit la plage J8:J17,
• Dans la barre de formule, coller (CTRL + V) la syntaxe copiée
• Puis, en premier paramètre de la fonction Index, remplacer la matrice Km par la matrice Prix,
• Valider alors la formule matricielle par le raccourci clavier CTRL + MAJ + Entrée,

Aussitôt, les prix associés sont extraits et regroupés. En cellules I4 et J4, si vous remplacez la marque Peugeot par la marque Renault et le modèle 308 par le modèle Clio, vous obtenez bien l'importation des informations sur les Renault Clio.



Identifier visuellement les données extraites
Pour une meilleure interprétation des résultats fournis par les formules matricielles d'extraction, nous proposons de construire une règle de mise en forme conditionnelle. Elle doit faire ressortir sur un fond de couleur la ligne entière de chaque enregistrement concordant. Ainsi, chaque véhicule associé à la demande sera clairement identifié. Pour cela, cette règle doit vérifier la double condition sur la marque et le modèle. Souvenez-vous néanmoins d'un principe important. Une règle ne peut raisonner de façon matricielle. Elle procède de façon chronologique. Nous allons donc poser ce double critère sur la première marque et le premier modèle, de manière à ce qu'ils soient ensuite tous passés en revue.

• Sélectionner toutes les données du tableau, soit la plage de cellules B4:G91,
• 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 ...,
• Dans la zone de saisie du dessous, inscrire la syntaxe suivante : =ET($C4=$I$4; $D4=$J$4),

Nous utilisons la fonction Et pour honorer la double condition sur la marque et le modèle. L'analyse débute à partir de la première marque comme à partir du premier modèle. Chacun d'entre eux est respectivement comparé à la valeur saisie en cellules I4 puis J4. Pour que tous les enregistrements soient passés en revue, les cellules du tableau sont libérées en ligne ($C4 et $D4). Elles progresseront ainsi avec l'analyse chronologique. En revanche leur colonne est conservée figée. Pour une même ligne, le critère est à honorer dans une colonne bien précise. Il s'agit maintenant d'associer un format à cette règle.

• 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 bleu pâle pour le fond des cellules,
• Activer ensuite l'onglet Police de la boîte de dialogue,
• Avec la liste déroulante, choisir un bleu foncé pour le texte,
• Valider ces attributs avec le bouton Ok,

La règle doit donc faire réagir les cellules des enregistrements extraits par les précédents calculs matriciels.

• Cliquer de nouveau sur le bouton Ok pour valider sa création,

Du retour sur la feuille, les enregistrements cherchés surgissent dynamiquement. Cet effet visuel automatique vient renforcer l'intérêt des précédentes extractions réalisées par les calculs matriciels.