Extraire les correspondances dans des colonnes distinctes

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


Sujets que vous pourriez aussi aimer :


Extraire les correspondances sur des colonnes distinctes

Les bases de données consistent souvent en une énumération non organisée des flux d'information. Avec un gestionnaire comme Access, il est relativement simple d'exploiter les requêtes pour réunir ces informations par groupes et les agencer différemment.



Dans l'exemple illustré par la capture, nous travaillons sur un petit tableau de véhicules référencés sur la marque et le modèle. Pour y voir plus clair, nous choisissons de regrouper ces modèles par marque dans un tableau transposé, sur la droite de la feuille. Et nous allons le voir, c'est une formule matricielle qui livre la solution.

Source et présentation
Pour mener à bien cette étude, nous proposons tout d'abord de réceptionner un petit tableau de données.

• Télécharger le classeur extraire-colonnes-distinctes.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é,

Des automobiles sont donc listées dans un petit tableau entre les colonnes B et C et les lignes 4 à 15. La taille importe peu. La solution s'étend à construire très simplement à tout type de base de données. Sur la droite de la feuille, entre les colonnes E et I, il s'agit de regrouper et de lister les modèles par marque. Ces modèles sont distinctement énumérés en colonne E.



Si vous déployez la liste déroulante de la zone Nom, en haut à gauche de la feuille Excel, vous remarquez que les deux colonnes du tableau sont intitulées selon les titres de champs. Ces noms sont précieux pour simplifier la syntaxe de la formule matricielle à construire.

Extraire les modèles par marque
Pour extraire plusieurs correspondances dans des cellules distinctes de colonnes distinctes, nous pouvons utiliser une formule matricielle construite avec les fonctions Index et Petite.Valeur. La fonction Petite.Valeur doit générer un numéro de ligne coïncidant avec une énième correspondance. Une fois ce numéro de ligne transmis à la fonction INDEX, cette dernière retournera la donnée à extraire.

• Sélectionner la case du premier modèle à extraire, soit la cellule F4,
• Taper le symbole égal (=) pour débuter la construction de la formule matricielle,
• Inscrire la fonction de gestion des anomalies suivie d'une parenthèse, soit : SiErreur(,

Comme vous le savez, en cas de non correspondance, une fonction d'extraction répond par un code d'erreur. Ces erreurs pourraient survenir si nous choisissions de répliquer la formule sur un nombre de colonnes supérieur au nombre de modèles appartenant à une marque. Nous décidons ainsi et par anticipation de les détecter et de les neutraliser.

• Inscrire la fonction d'extraction suivie d'une parenthèse, soit : Index(,
• Désigner la colonne des modèles par son nom, soit : Modèle,

Ce sont en effet les modèles que nous souhaitons restituer à l'horizontale pour chaque marque.

• Taper un point-virgule (;) pour passer dans l'argument de l'indice de ligne,

Il s'agit maintenant de repérer chronologiquement les lignes des correspondances trouvées pour la marque cherchée. Pour respecter cette chronologie, nous devons utiliser la fonction Petite.Valeur.

• Inscrire cette fonction suivie d'une parenthèse, soit : Petite.Valeur(,

Une condition doit alors être observée en corrélation avec la marque inscrite en première colonne (E) de ce tableau d'extraction.

• Inscrire la fonction conditionnelle suivie d'une parenthèse, soit : Si(,
• Désigner la colonne des marques par son nom, soit : Marque,
• Taper le symbole égal (=) pour annoncer la condition à honorer,
• Puis, cliquer sur la première marque du tableau d'extraction, soit : E4,
• Enfoncer trois fois la touche F4 du clavier, ce qui donne : $E4,

De cette manière, nous la figeons en colonne et la libérons en ligne. En effet, pour les lignes du dessous, la logique doit se déplacer pour adapter la contrainte à la nouvelle marque. Pour les colonnes de droite en revanche, chaque extraction doit être faite en tenant compte de la même marque. Donc la cellule de la condition ne doit pas bouger en colonne.

• Taper un point-virgule (;) pour passer dans la branche Alors de la fonction Si,

La fonction Petite.Valeur doit travailler sur un tableau restreint par la condition posée par la fonction Si.

• Inscrire la fonction donnant l'indice de ligne suivie d'une parenthèse, soit : Ligne(,
• Désigner les modèles par le nom de la colonne, soit : Modèle,

En guise de cellule, nous transmettons à la fonction Ligne, l'ensemble de la rangée. Dans le cas d'un raisonnement matriciel, ce sont donc toutes les lignes qui vont être analysées tour à tour.

• Fermer la parenthèse de la fonction Ligne,
• Taper le symbole moins (-) pour annoncer la soustraction à suivre,
• Inscrire la fonction pour la plus petite valeur, suivie d'une parenthèse, soit : Min(,
• Désigner de nouveau l'intégralité des lignes pour la colonne Modèle, soit : Ligne(Modèle),
• Fermer la parenthèse de la fonction Min,
• Puis, ajouter une unité à ce résultat, soit : +1,

En résumé, lorsque la correspondance sur le test de la marque est avérée, nous renvoyons un numéro de ligne à partir d'un tableau des numéros de ligne relatifs ainsi créés. La dernière cellule est en ligne 15. La plus petite est en ligne 4. 15-4+1 = 12 : Nous travaillons bien sur un vecteur de 12 unités, celui des 12 modèles. Souvenez-vous, nous sommes effectivement dans l'argument de l'indice de ligne pour la fonction Index. Et la fonction Petite.Valeur opère exclusivement sur des données numériques.

• Fermer la parenthèse de la fonction Si,

La branche Sinon de la fonction Si n'est pas renseignée. Il n'est en effet pas nécessaire de tester le cas échéant. Seules les concordances nous intéressent. Pour le reste, les anomalies sont destinées à être neutralisées par la fonction SiErreur.

• Taper un point-virgule (;) pour passer dans l'argument du rang de la fonction Petite.Valeur,
• Inscrire la fonction comptant les colonnes d'une plage, soit : Colonnes(,
• Désigner la première marque du tableau d'extraction en cliquant sur sa cellule E4,
• Enfoncer le symbole deux points (:) pour générer la plage E4:E4,

Pour l'instant, cette plage ne fait référence qu'à la cellule de départ. Mais elle doit grandir en même temps que le calcul est répliqué sur les colonnes de droite. C'est ainsi que nous énumèrerons tous les rangs des correspondances respectives et chronologiques à restituer. Nous devrions ainsi obtenir la liste de tous les modèles d'une marque, dans des colonnes distinctes. Pour que cette plage grandisse, sa borne de départ doit être figée.

• Cliquer entre le E et le 4 de la première référence E4,
• Enfoncer la touche F4 du clavier, ce qui donne : $E$4:E4,
• Puis, cliquer à la toute fin de la syntaxe pour y replacer le point d'insertion,
• Fermer la parenthèse de la fonction Colonnes,
• Fermer la parenthèse de la fonction Petite.Valeur,
• Fermer la parenthèse de la fonction Index,

Nous ne prenons pas le soin de renseigner l'argument suivant de cette fonction Index. Il concerne l'indice de colonne pour l'extraction. Il est implicitement connu puisque nous avons transmis une unique colonne, celle des modèles à extraire.

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

Le premier modèle est aussitôt affiché. Et il s'agit effectivement du premier modèle (plus petit rang) pour la marque Citroën.

• Cliquer et glisser la poignée de la cellule à l'horizontale jusqu'en colonne I,

Nous obtenons l'extraction de tous les modèles de la marque Citroën.

• Cliquer et glisser la poignée de la sélection à la verticale jusqu'en ligne 6,

Nous livrons bien l'extraction de tous les modèles dans leur marque respective en respectant la chronologie des rangs, soit l'ordonnancement d'origine dans le tableau.



Grâce à cette formule matricielle, nous sommes donc parvenus à extraire l'information de toutes les correspondances en les réorganisant dans des colonnes séparées. La syntaxe du calcul que nous avons construit est la suivante :

{=SIERREUR(INDEX(Modèle; PETITE.VALEUR(SI(Marque=$E4; LIGNE(Modèle)-MIN(LIGNE(Modèle))+1); COLONNES($E$4:E4))); '')}