Consolider les données d'un tableau Excel

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


Sujets que vous pourriez aussi aimer :


Récolter les informations manquantes

Dans cet exercice Excel, nous proposons d'exploiter les fonctions d'extraction de bases de données à des fins utiles et concrètes. Nous devons travailler sur un tableau de données, fruit d'une importation quotidienne. Il s'agit d'automobiles issues d'un parc de véhicules d'une société. Mais certaines données sont manquantes.

Source et présentation de la problématique
Tout d'abord, nous proposons de réceptionner un classeur source hébergeant les données, pour focaliser l'attention sur la résolution du problème.

• Télécharger le classeur consolider-donnees-manquantes.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é,

Ce classeur est constitué de deux feuilles. La feuille active par défaut se nomme Encyclopedie. En quelques sortes, cette petite base de données est une encyclopédie des véhicules. Elle renseigne sur la marque de n'importe quel modèle. Et c'est précisément l'information manquante dans la feuille Recolte, à la réception des données du parc automobile.

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

Le tableau qui s'y trouve liste des véhicules à traiter. Ils sont référencés sur leur immatriculation en colonne B et sur le modèle en colonne D. Mais l'information sur la marque en colonne C est manquante.

Bien entendu, il n'est pas question de colmater les brèches manuellement. La seule information commune aux deux tableaux est le modèle. C'est cette donnée qui doit donc permettre de remonter la précision manquante pour chaque véhicule.

Bien sûr le calcul doit être dynamique pour permettre des traitements automatisés, quels que soient les nouveaux véhicules à traiter.



Consolider les données d'un tableau
Pour chaque marque à trouver, l'idée consiste donc à réaliser une recherche du modèle dans le tableau de la feuille Encyclopedie. Lorsque la correspondance est trouvée, l'information placée dans la première colonne de ce tableau doit être importée.

Nous devons donc exploiter l'une des fonctions d'extraction que propose Excel. La RechercheV ne peut pas être envisagée. Comme vous le savez, malgré sa puissance, cette fonction impose une contrainte. L'élément cherché doit nécessairement se trouver dans la première colonne du tableau. Or, le modèle est placé dans la seconde colonne du tableau de recherche.

C'est la raison pour laquelle nous envisageons d'exploiter la fonction Excel Index:

=Index(Tableau_de_recherche; Indice_ligne ; Indice_colonne)

Cette fonction requiert trois paramètres. En premier argument, la plage de cellules du tableau de recherche doit lui être passée. Puis il s'agit de lui indiquer le numéro de ligne et de colonne respectivement en deuxième et troisième paramètre. C'est ainsi que la fonction est capable d'extraire la donnée située au croisement.

Nous connaissons la colonne dans laquelle se situe l'information à extraire. La marque est positionnée dans la première des deux rangées du tableau de recherche. Mais l'information sur l'indice de ligne est variable. Elle dépend de la correspondance trouvée sur le modèle. Et c'est alors la fonction Excel Equiv qui entre en jeu :

=Equiv(Valeur_cherchée; Rangée_de_recherche ; Correspondance)

Elle requiert trois arguments et retourne la ligne où l'élément cherché est trouvé. Et précisément, cet élément doit être indiqué en premier paramètre. Dans notre cas, il s'agit du modèle à trouver. Ensuite, nous devons renseigner la colonne de recherche en deuxième paramètre. Les modèles sont recensés dans la colonne C de la feuille Encyclopedie. Le dernier argument est une valeur correspondant au mode de recherche à engager.

Lorsqu'une fonction d'extraction ne trouve pas l'élément qui lui est demandé, elle retourne une erreur. Pour éviter ces messages disgracieux, nous suggérons d'englober la formule dans la fonction de gestion d'erreur SiErreur.

• Sélectionner la cellule C5 de la feuille Recolte,
• Taper le symbole égal (=) pour initier la formule,
• Saisir la fonction de gestion d'erreur suivie d'une parenthèse, SiErreur(,
• Taper la fonction d'extraction suivie d'une parenthèse, Index(,
• En bas de la fenêtre Excel, cliquer sur l'onglet Encyclopedie pour activer sa feuille,
• Sélectionner les deux colonnes du tableau par les étiquettes, soit : Encyclopedie!B:C,

De cette manière, si des références venaient à être ajoutées à la suite de la base de données, elles seraient considérées naturellement. De plus, cette méthode permet de ne pas avoir à figer la plage. Le calcul d'extraction est destiné à être répliqué sur les lignes du dessous. Ces références intégrales ne bougeront donc pas.

• Taper un point-virgule (;) pour passer dans l'argument de l'indice de ligne,
• Saisir la fonction retournant le numéro de ligne cherché suivie d'une parenthèse, soit Equiv(,
• Cliquer sur l'onglet Recolte en bas de la fenêtre Excel pour activer sa feuille,
• Désigner le premier modèle à trouver en cliquant sur la cellule D5,

Il est possible que vous ne puissiez pas atteindre cette dernière en raison de la syntaxe qui déborde. Dans ces conditions, il suffit simplement de saisir les références (D5) au clavier. Cette fois, le nom de la feuille (Recolte!) en préfixe n'est pas nécessaire. La formule est bâtie sur cette dernière. La référence est donc implicite contrairement aux cellules de la feuille Encyclopedie qu'il faut bien préfixer dans la syntaxe.

• Taper un point-virgule (;) pour passer dans l'argument de la rangée de recherche,
• Cliquer de nouveau sur l'onglet Encyclopedie en bas de la fenêtre pour revenir sur sa feuille,
• Désigner la colonne de recherche intégrale par son étiquette, soit Encyclopedie!C:C,

Cette méthode procure les mêmes intérêts que ceux évoqués précédemment.

• Taper un point-virgule (;) pour passer dans le troisième argument de la fonction Equiv,
• Saisir le chiffre 0 pour une recherche avec correspondance exacte,
• Fermer la parenthèse de la fonction Equiv,

Une info-bulle nous aiguille durant la construction de la syntaxe de la formule. Et comme l'indique sa mention en gras, nous sommes de retour dans les paramètres de la fonction Index. A ce stade, nous sommes censés avoir retourné l'information dynamique sur l'indice de ligne du modèle cherché. Il ne nous reste plus qu'à fournir le numéro de colonne. Dès lors, la fonction Index pourra procéder à l'extraction de la donnée située au croisement. La marque à extraire est placée en première colonne du tableau de recherche.

• Taper un point-virgule suivi du chiffre 1, soit : ;1,
• Fermer la parenthèse de la fonction Index,

De fait, nous sommes de retour dans les bornes de la fonction SiErreur.

• Taper un point-virgule (;) pour passer dans son second argument,
• Saisir deux guillemets ('') pour conserver la cellule vide en cas de souci,
• Fermer la parenthèse de la fonction SiErreur,
• Puis, valider le calcul par le raccourci clavier CTRL + Entrée,

De cette manière, nous conservons la cellule du résultat active afin de répliquer sa logique dans la foulée. Comme vous le remarquez, le premier résultat tombe et il est cohérent.

• Double cliquer sur la poignée du résultat pour reproduire la logique sur la hauteur du tableau,

Comme vous pouvez le voir, toutes les marques qui manquaient jusqu'alors, ont parfaitement été extraites. Nous avons donc réussi à consolider les données du tableau. La formule que nous avons construite est la suivante :

=SIERREUR(INDEX(Encyclopedie!B:C;EQUIV(D5;Encyclopedie!C:C;0);1);'')

Si vous entrez un modèle erroné en colonne D, vous constatez que la marque correspondante reste muette, tel que nous l'avons programmé. Si vous remplacez un modèle par un autre, archivé en base de données, vous notez que la marque s'adapte automatiquement.