Extraire toutes les données d'un critère 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 selon condition

Nous avons appris à exploiter les fonctions Excel Petite.Valeur et Grande.Valeur dans un raisonnement matriciel. Cette fois, nous les mettons à contribution pour permettre l'extraction de tous les enregistrements correspondant à un critère émis. Et vous allez le constater, la démarche n'est pas très éloignée de ce que nous avons étudié.



Dans l'exemple finalisé illustré par la capture ci-dessus, nous réalisons l'extraction de tous les biens immobiliers au choix d'une ville par le biais d'une liste déroulante. Mais cette fois, nous ne passons pas par des calculs intermédiaires de repérage. Une seule formule matricielle permet d'isoler et de rapatrier les données des enregistrements concordants.

Source et présentation de la problématique
Pour réaliser cette étude, nous avons besoin de réceptionner la base de données des biens immobiliers.

• Télécharger le classeur extraction-informations-condition.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é,

Nous réceptionnons une petite base de données de quelques biens immobiliers, référencés entre les colonnes B et H de la feuille Extraire. On y trouve le niveau de détail sur la ville et le prix notamment ou encore sur la superficie et le nombre de pièces.



Sur la droite de cette source de données, vous notez la présence du tableau de synthèse. La cellule K6 est dotée d'une liste déroulante. Au choix de l'une des villes, nous devons procéder à l'extraction de tous les biens immobiliers géographiquement concordants. Et pour cela, nous proposons de réceptionner premièrement tous les prix attachés. Il s'agit en effet de valeurs numériques que les fonctions Petite.Valeur et Grande.Valeur sont capables de manipuler sur la base d'un raisonnement matriciel. Dès lors, nous n'aurons plus qu'à formuler le recoupement sur la ville et le prix pour rapatrier toutes les autres informations attachées.

Si vous déployez la liste déroulante de la zone Nom, en haut à gauche de la feuille Excel, vous notez que chaque colonne est reconnue par son titre.



Nous exploiterons ces noms pour simplifier la syntaxe des formules matricielles.

Extraire tous les prix d'une ville
Nous avons déjà exploité la fonction Grande.Valeur pour extraire les premières meilleures valeurs d'une plage, tout en respectant une ou plusieurs contraintes recoupées. Ici, il ne s'agit pas de limiter l'extraction aux premières données. Dès lors qu'elles correspondent au critère émis sur la ville, elles doivent toutes être importées. Cependant, leur nombre varie selon la ville désignée. L'astuce consiste à utiliser une matrice désignant l'ensemble des lignes de la base en guise de rang pour la fonction Grande.Valeur. Selon les concordances repérées, tous les rangs variables et dynamiques seront ainsi considérés.

• Sélectionner tous les premier prix à trouver, soit la plage de cellules J9:J20,
• Taper le symbole égal (=) pour initier la syntaxe de la formule matricielle,
• Inscrire la fonction des grandes valeurs suivie d'une parenthèse, soit : Grande.Valeur(,
• Saisir la fonction conditionnelle suivie d'une parenthèse, soit : Si(,

En effet, nous sommes dans l'argument de la matrice à partir de laquelle il s'agit d'extraire les prix. Mais cette extraction est conditionnelle. Dans le cadre d'un raisonnement matriciel, nous pouvons restreindre cette sélection par imbrication d'une fonction conditionnelle.

• Désigner la colonne des villes par son nom, soit : Ville,
• Taper le symbole égal (=) pour annoncer la condition à honorer,
• Sélectionner la ville choisie par l'utilisateur en cliquant sur sa cellule K6,
• Enfoncer la touche F4 du clavier pour la figer, ce qui donne : $K$6,

La logique est destinée à être reproduite sur les lignes du dessous pour extraire tous les prix. Ce critère doit être vérifié par rapport à cette cellule de référence. Donc, elle ne doit pas bouger.

• Taper un point-virgule (;) pour passer dans la branche Alors de la fonction Si,
• Désigner la matrice des prix par son nom, soit : Prix,

Dans ce raisonnement matriciel, toutes les lignes des colonnes impliquées vont être analysées respectivement. En conséquence, seuls les prix pour lesquels la condition est satisfaite sur la colonne des villes, seront conservés.

• Taper un point-virgule (;) pour passer dans la branche Sinon de la fonction Si,
• Saisir le chiffre zéro (0) pour ignorer l'enregistrement en cas de non concordance,
• Fermer la parenthèse de la fonction Si,
• Puis, taper un point-virgule (;) pour passer dans le rang pour la fonction Grande.Valeur,
• Inscrire la fonction pour l'indice de ligne d'une cellule, suivie d'une parenthèse, soit : Ligne(,

L'objectif est en effet de pouvoir construire une matrice virtuelle constituée du même nombre de lignes que la base de données. Ainsi, nous considèrerons tous les rangs potentiels pour une extraction complète.

• Saisir la fonction d'interprétation suivie d'une parenthèse, soit : Indirect(,
• Inscrire le point de départ de la plage virtuelle comme suit : '1:',
• Taper le caractère de concaténation pour faire suivre la borne inférieure,
• Saisir la fonction comptant les lignes d'une plage, suivie d'une parenthèse, soit : Lignes(,
• Désigner l'une des colonnes par son nom, par exemple celle des villes, soit : Ville,
• Fermer la parenthèse de la fonction Lignes,

Nous venons donc de construire une plage dont la hauteur est équivalente à celles des colonnes de la base de données. Nous sommes dans l'argument du rang de la fonction Grande.Valeur. Dans un raisonnement matriciel, ligne à ligne pour chaque matrice, tous les rangs des enregistrements concordants seront extraits dans l'ordre décroissant.

• 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, valider nécessairement la formule par le raccourci clavier CTRL + MAJ + Entrée,

Comme vous pouvez le voir, des prix sont extraits dans un ordre décroissant. Cet ordre est imposé par le fonctionnement même de la fonction Grande.Valeur. Et en y regardant de plus près, nous constatons que ces prix extraits respectent parfaitement la condition émise sur la ville choisie. C'est donc bien une unique formule, certes matricielle, qui a permis l'extraction groupée de toutes les données correspondantes.

La syntaxe complète de la formule matricielle que nous avons bâtie est la suivante :

{=GRANDE.VALEUR(SI(Ville=$K$6; Prix; 0);LIGNE(INDIRECT('1:' & LIGNES(Ville))))}

Vous notez la présence des accolades qui encadrent la syntaxe. Elles indiquant qu'il s'agit d'un raisonnement matriciel. Elles ont été générées par le raccourci clavier.

Extraire les données attachées
Désormais, nous devons enrichir le tableau de synthèse de tout le détail attaché à ces prix. Mais ces informations sont aussi liées au choix de la ville. Cette extraction doit donc honorer deux conditions. Toujours dans un raisonnement matriciel que nous avons d'ailleurs déjà démontré, la fonction Equiv permet de réaliser cette prouesse. Elle ne doit pas être implémentée de façon classique. En guise de valeur cherchée pour son premier argument, nous allons lui fournir une indication booléenne sous forme de chiffre. Le chiffre 1 signifie Vrai. En deuxième argument, en guise de colonne de recherche, nous allons lui passer des matrices conditionnelles. Le critère à honorer sur la colonne des prix doit être recoupé à celui sur la colonne des villes. Chaque enregistrement concordant répondra par le chiffre 1 satisfaisant la valeur de test passée en premier paramètre. Pour désigner la colonne de l'information à extraire, nous devons imbriquer la fonction Equiv dans la fonction Index :

=Index(Tableau_de_recherche; Indice_de_ligne; Indice_de_colonne)

Il s'agit de techniques désormais classiques. La fonction Index extrait la donnée située au croisement d'une ligne et d'une colonne. La ligne variable sera retournée par la fonction Equiv, selon le raisonnement que nous venons de décrire. La colonne est fixe. Elle dépend de l'emplacement de l'information à retourner. Comme vous le savez, en cas de recherche infructueuse, les fonctions d'extraction génèrent des erreurs. Pour les neutraliser, nous engloberons la syntaxe dans la fonction SiErreur.

• Sélectionner la case de la première donnée à extraire, soit la cellule K9,
• Taper le symbole égal (=) pour initier la formule,
• Inscrire la fonction de gestion des anomalies suivie d'une parenthèse, soit : SiErreur(,
• Saisir la fonction d'extraction suivie d'une parenthèse, soit : Index(,
• Taper la fonction d'interprétation suivie d'une parenthèse, soit : Indirect(,

En effet, nous devons désigner la colonne de recherche grâce au titre du tableau de synthèse pour produire une formule parfaitement dynamique et réplicable. Mais pour que ce titre soit interprété comme une plage, cette fonction est nécessaire.

• Sélectionner le titre au-dessus du calcul en cliquant sur sa cellule K8,
• Enfoncer deux fois de suite la touche F4 du clavier, ce qui donne : K$8,

Ainsi, nous la libérons en colonne et la conservons figée en ligne. En effet, pour le calcul répliqué sur les lignes du dessous, la plage à considérer est mentionnée par cette cellule qui ne doit pas changer de ligne. En revanche, pour le calcul répliqué sur les colonnes de droite, la plage d'extraction doit s'adapter en fonction du titre. La cellule doit donc se déplacer à l'horizontale avec le calcul.

• Fermer la parenthèse de la fonction Indirect,
• Taper un point-virgule (;) pour passer dans l'argument de la ligne à trouver,
• Saisir la fonction de recherche de position suivie d'une parenthèse, soit : Equiv(,
• Saisir le chiffre 1 en guise de test booléen,
• Taper un point-virgule (;) pour passer dans l'argument des matrices conditionnelles,
• Ouvrir une parenthèse pour accueillir la première matrice conditionnelle,
• Désigner la colonne des prix par son nom, soit : Prix,
• Taper le symbole égal (=) pour annoncer la condition matricielle à honorer,
• Désigner le premier prix extrait dans le tableau de synthèse en cliquant sur sa cellule J9,
• Enfoncer trois fois la touche F4 du clavier, ce qui donne : $J9,

Cette fois, nous figeons la colonne et libérons la ligne. Pour les autres détails à extraire sur la droite, c'est en effet toujours le même prix qui doit être considéré. Pour la suite, sur les lignes du dessous en revanche, ce sont bien les autres prix qui doivent servir de base d'extraction.

• Fermer la parenthèse de cette première matrice conditionnelle,
• Taper le symbole de l'étoile (*) pour annoncer la condition à recouper sur la ville,
• Ouvrir une nouvelle parenthèse pour accueillir la seconde matrice conditionnelle,
• Désigner la rangée des villes par son nom de colonne, soit : Ville,
• Taper le symbole égal (=) pour annoncer le critère recoupé à honorer,
• Sélectionner la ville choisie par l'utilisateur en cliquant sur sa cellule K6,
• Enfoncer la touche F4 du clavier pour la figer complètement, ce qui donne : $K$6,

Ce critère doit être vérifié dans tous les cas, malgré la réplication en ligne et en colonne. Cette cellule doit rester totalement figée.

• Fermer la parenthèse de cette seconde matrice conditionnelle,
• Puis, taper un point-virgule suivi du chiffre 0, soit : ;0 , pour une recherche exacte,
• Fermer la parenthèse de la fonction Equiv,
• Fermer la parenthèse de la fonction Index,

Comme la recherche est réalisée dans une unique colonne mentionnée en premier argument de la fonction Index, il n'est pas nécessaire de lui indiquer l'indice de colonne pour l'extraction.

• Taper un point-virgule (;) pour passer dans le second argument de la fonction SiErreur,
• Inscrire deux guillemets ('') pour garder la cellule vide en cas d'anomalie,
• Fermer la parenthèse de la fonction SiErreur,
• Valider la formule matricielle par le raccourci clavier CTRL + MAJ + Entrée,
• Tirer la poignée du résultat à la verticale jusqu'en ligne 20,
• Tirer la poignée de la sélection à l'horizontale jusqu'en colonne M,

Comme vous pouvez le voir, tout le détail des biens attachés est fidèlement extrait sur la base d'une unique formule matricielle ayant livré les prix répondant à la condition émise.

Bien entendu, si vous modifiez la ville, les résultats s'actualisent aussitôt. Tous les biens correspondants sont parfaitement rapatriés.

La formule matricielle que nous avons bâtie, avec ce test logique enclenché dans la fonction Equiv, est la suivante :

{=SIERREUR(INDEX(INDIRECT(K$8); EQUIV(1; (Prix=$J9)*(Ville=$K$6); 0));'')}



Bien sûr, le nombre de résultats extraits varie selon la ville choisie.

Identification visuelle
Pour parachever l'application, nous proposons d'exploiter une règle de mise en forme conditionnelle. Son objectif est de repérer visuellement et dynamiquement les enregistrements dont les prix ont été extraits par la formule matricielle exploitant la fonction Grande.Valeur.

• Sélectionner toutes les données du tableau, soit la plage de cellules B4:H38,
• 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, construire l'expression suivante : =$B4=$K$6,

L'analyse d'une mise en forme conditionnelle est chronologique. Nous désignons donc la première ville (B4) à comparer avec le choix utilisateur (K6). Nous libérons sa ligne ($B4) pour que toutes les villes de la base de données soient considérées tour à tour. Lorsque les villes coïncident, la ligne complète de l'enregistrement doit surgir.

• 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 jaune pâle par exemple,
• Activer ensuite l'onglet Police de la boîte de dialogue,
• Avec la liste déroulante, choisir un rouge foncé pour le texte,
• Puis, valider ces attributs par le bouton Ok,

Nous sommes de retour sur la première boîte de dialogue qui illustre l'apparence dont se pareront les enregistrements confirmant la règle.

• Cliquer sur le bouton Ok pour valider la création de la règle de mise en forme conditionnelle,

Tous les biens immobiliers répondant à la ville choisie sont effectivement mis en lumière. Si vous changez de ville, les repérages se déplacent. Ces indicateurs visuels renforcent la pertinence des extractions matricielles. Elles facilitent le recoupement et la vérification des informations synthétisées.