Remplir un tableau selon des critères croisés ou isolés

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


Sujets que vous pourriez aussi aimer :


Tableau et critères en cascade

Dans cette formation Excel, nous proposons de construire un outil capable de remplir un tableau en fonction de critères en cascade ou encore de conditions isolées. Nos travaux portent sur une base de données des véhicules. Selon les critères du client, le vendeur doit être en mesure d'extraire instantanément du parc, toutes les automobiles correspondantes.



Dans l'exemple illustré par la capture, le vendeur émet deux conditions. Le client souhaite uniquement une voiture de la marque Renault dont le prix est nécessairement inférieur à 12000 Euros. Et instantanément, tous les résultats sont extraits de la base de données.

Mais il pourrait affiner la recherche en recoupant les quatre conditions proposées. Il pourrait aussi n'émettre que l'un de critères pour sortir tous les véhicules d'une fourchette de prix ou de kilométrages. Vous l'avez compris, le défi de ce développement est d'offrir une plateforme ergonomique répondant aux choix recoupés ou non de l'utilisateur.

Source et présentation de la problématique
Pour débuter les travaux, il est tout d'abord nécessaire de réceptionner un classeur hébergeant les données à manipuler.

• Télécharger le classeur remplir-tableau-criteres.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é,

Le classeur résultant est composé de trois feuilles. Elles sont respectivement nommées : Recherche, Parc et sources.



La feuille Recherche est active par défaut. Elle offre la structure de la console d'extraction avec les zones de critères à émettre. Bien entendu, elle n'est pas encore fonctionnelle puisqu'il s'agit de l'objectif de cette formation.

La feuille sources est simplement utilisée pour nourrir la liste déroulante des marques en cellules C5 de la feuille Recherche.

La feuille Parc héberge la base des données à manipuler.



Les champs du tableau y sont organisés dans le même ordre que ceux de la zone d'extraction de la feuille Recherche. De fait, il sera plus simple de répliquer la formule finale d'extraction des informations concordantes.

Sur la droite de cette base de données, vous notez la présence de quatre colonnes vides.



Elles montrent la voie de l'astuce à construire pour permettre des repérages intermédiaires selon les conditions émises depuis la console de recherche.

Assembler les recherches textuelles
Quatre conditions sont permises. Deux critères potentiels sont textuels tandis que deux autres sont numériques. Pour simplifier la recherche correspondante, nous proposons tout d'abord de concaténer l'information sur la marque et le modèle dans la colonne G. Mais cette concaténation doit être conditionnelle. Si les deux conditions sont émises, les deux informations doivent être assemblées. Dans les cas restants, nous ne devons en restituer qu'une voire aucune. De cette manière, le repérage des correspondances en fonction des conditions émises sera trivial. Par exemple, si ni la marque ni le modèle ne sont renseignés, la concaténation conduira à une chaîne vide. Comparer les critères concaténés vides avec une chaîne résultante vide fournira bien tous les résultats. C'est l'astuce qui permet d'offrir une console permettant de recouper les conditions ou de les isoler.

Pour simplifier la syntaxe des formules, il est préférable d'attribuer des noms aux zones de critère.

• Sélectionner la cellule C5 de la feuille Recherche,
• Dans la zone Nom, taper l'intitulé Marque,
• Puis, valider nécessairement par la touche Entrée du clavier,

La cellule est désormais reconnue par son nom. Dans la formule à bâtir sur la feuille Parc, nous pourrons ainsi la désigner sans faire référence à sa feuille. De plus, elle sera considérée comme figée par défaut. Il ne sera donc pas nécessaire de mettre en oeuvre les références absolues.

• De la même façon, nommer la cellule E5 : Modele,
• Nommer la cellule C7 : Km, et la cellule E7 : Prix,
• En bas de la fenêtre Excel, cliquer sur l'onglet Parc pour activer sa feuille,
• Puis, cliquer sur la cellule G2 pour la sélectionner,
• Taper le symbole égal (=) pour initier le calcul,
• Saisir la fonction conditionnelle suivie d'une parenthèse, soit : Si(,
• Taper la fonction pour recouper les critères, suivie d'une parenthèse, soit : Et(,
• Saisir le nom du premier critère textuel, soit : Marque,
• Puis, taper l'inégalité suivante : <> '',

Nous cherchons à savoir si le critère est émis sur la marque depuis la console d'extraction.

• Taper un point-virgule (;) pour poursuivre l'énumération des conditions,
• Saisir le nom du second critère textuel, soit : Modele,
• Puis, taper la même inégalité : <> '',

Cette double condition consiste donc à savoir si les deux critères sont définis ensemble. Si tel est le cas, nous devons concaténer ces deux informations de base de données.

• Fermer la parenthèse de la fonction Et,
• Puis, taper un point-virgule (;) pour passer dans la branche Alors de la fonction Si,
• Sélectionner la première marque du tableau, soit la cellule B2,
• Taper le caractère de concaténation : &,
• Puis, sélectionner le premier modèle du tableau, soit la cellule C2,

Nous réalisons ainsi l'assemblage de la marque et du modèle, dans la mesure où ces deux conditions sont spécifiées ensemble. Dans le cas contraire, nous devons vérifier si seul l'un des critères est renseigné.

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

Plusieurs possibilités sont encore à envisager. Seule la marque ou seul le modèle peut être spécifié. Mais il est possible que ni l'un ni l'autre ne soit renseigné. Pour vérifier ces cas multiples, nous devons imbriquer une nouvelle fonction conditionnelle à l'intérieur de la première fonction Si.

• Saisir de nouveau la fonction conditionnelle suivie d'une parenthèse, soit : Si(,
• Saisir le critère suivant : Marque<>'',
• Puis, taper un point-virgule (;) pour passer dans la branche Alors de la deuxième fonction Si,
• Sélectionner la première marque du tableau, soit la cellule B2,

Ainsi, lorsque seule la condition sur la marque est émise depuis la console, la concaténation consiste simplement à récupérer cette information isolée, depuis la base de données.

• Taper un point-virgule (;) pour passer dans la branche Sinon de la deuxième fonction Si,
• Saisir de nouveau la fonction conditionnelle, suivie d'une parenthèse, soit : Si(,
• Puis, taper le critère suivant : Modele<>'',
• Taper un point-virgule (;) pour passer dans la branche Alors de la troisième fonction Si,
• Sélectionner le premier modèle du tableau, soit la cellule C2,

Ainsi, lorsque seul le critère sur le modèle est émis, nous restituons l'information correspondante de base de données sur sa ligne.

• Taper un point-virgule (;) pour passer dans la branche Sinon de la troisième fonction Si,
• Saisir deux guillemets, soit : '', pour conserver la cellule vide,

En effet, dans le cas restant, nous savons qu'aucun critère textuel n'est émis. Donc nous gardons la cellule vide. Et deux textes vides peuvent parfaitement se comparer, pour construire l'astuce que nous évoquions précédemment.

• Fermer les trois parenthèses des trois fonctions Si,
• Puis, valider la formule par le raccourci clavier CTRL + Entrée,

Comme vous le savez, cette technique permet de conserver la cellule du résultat active, pour l'exploiter dans la foulée.

• Double cliquer sur la poignée du résultat pour répliquer la logique sur tout le tableau,

La colonne G reste intégralement vide et pour cause. Aucun critère textuel n'est encore émis depuis la console. C'est donc la toute dernière branche de la dernière fonction Si qui a été déclenchée.

• En bas de la fenêtre Excel, cliquer sur l'onglet Recherche pour activer sa feuille,
• En cellule C5, choisir la marque Peugeot par exemple,

En affichant les résultats de la feuille Parc, vous constatez que toutes les marques ont été restituées dans la colonne G. C'est la branche Alors de la deuxième fonction Si qui a été déclenchée. La colonne est prête pour recevoir la comparaison avec la marque mentionnée depuis la console.

• En cellule E5 de la feuille Recherche, taper le modèle 308 par exemple,
• Puis, afficher la feuille Parc,

Notre formule a bien détecté que les deux critères textuels ont été émis. C'est donc la branche Alors de la première fonction Si qui a été déclenchée. De fait, pour chaque ligne de la base de données, elle a procédé à l'assemblage de la marque et du modèle. La colonne G est désormais prête à recevoir la comparaison sur l'assemblage des critères. La formule que nous avons bâtie est la suivante :

=SI(ET(Marque<>'';Modele<>'');B2&C2;SI(Marque<>'';B2;SI(Modele<>'';C2;'')))

Repérer les correspondances textuelles
Pour la mise en oeuvre du raisonnement en cascade, nous devons commencer par repérer les correspondances textuelles. Ce repérage doit se faire par des numéros incrémentés et doit intervenir en colonne H de la base de données. C'est une technique que nous avons souvent déployée, notamment au travers de la formation Excel pour extraire les données recoupées.

Pour chaque ligne de la base de données, si la correspondance concaténée est avérée, nous devons inscrire un chiffre de repérage à incrémenter. C'est la fonction Max à exploiter sur la plage variable du calcul qui autorise cette prouesse. La fonction Max renvoie la plus grande valeur d'une plage de cellules.

• Sélectionner la cellule H2 de la feuille Parc,
• Taper le symbole égal (=) pour initier le calcul,
• Saisir la fonction conditionnelle suivie d'une parenthèse, soit : Si(,
• Saisir le nom du premier critère textuel, soit : Marque,
• Taper le symbole de concaténation, soit : &,
• Saisir le nom du second critère textuel, soit : Modele,
• Taper le symbole égal (=) pour la condition à vérifier,
• Désigner la première cellule d'assemblage de la base de données, soit : G2,

Si vous ne pouvez pas la cliquer, il vous suffit de taper ses coordonnées au clavier. Grâce à cette condition, nous cherchons à savoir si l'assemblage des critères correspond à l'assemblage des informations sur la marque et le modèle, dans la base de données. Si cette condition est satisfaite, nous devons repérer la ligne de l'enregistrement par un numéro.

• Taper un point-virgule (;) pour passer dans la branche Alors de la fonction Si,
• Saisir la fonction retournant la plus grande valeur, suivie d'une parenthèse, soit : Max( ,
• Désigner la cellule située juste au-dessus du calcul, soit H1,
• Taper le symbole deux points (:) pour générer la plage H1:H1,
• Sélectionner la première des deux références et enfoncer la touche F4 du clavier,

Nous générons ainsi la plage de cellules $H$1:H1. La borne supérieure est figée tandis que la borne inférieure est libre de se déplacer avec le calcul répliqué. C'est ainsi que la plage d'analyse va grandir. Le chiffre 1 sera le premier numéro de repérage inscrit. Au fur et à mesure de la réplication, la plage les englobant, ces numéros seront incrémentés. Encore faut-il terminer la syntaxe du calcul.

• Dans la barre de formule, cliquer à la fin du calcul pour y replacer le point d'insertion,
• Fermer la parenthèse de la fonction Max,
• Puis, ajouter une unité, soit : +1, pour l'incrémentation récursive à produire,
• Taper un point-virgule (;) pour passer dans la branche Sinon de la fonction Si,
• Saisir deux guillemets, soit : '', pour conserver la cellule vide en cas de non correspondance,
• Fermer la parenthèse de la fonction Si,
• Valider la formule par le raccourci clavier CTRL + Entrée,
• Puis, double cliquer sur la poignée du résultat pour répliquer la logique sur tout le tableau,

Tels que nous avions conservé les critères, toutes les Peugeot 308 sont effectivement repérées par des numéros incrémentés. Si vous supprimez l'un ou les deux critères émis sur la feuille Recherche, vous remarquez que le repérage numérique de la feuille Parc s'actualise automatiquement. Comme nous le souhaitions, il autorise donc à recouper les critères mais aussi à isoler les conditions.

La formule de repérage que nous avons construite est la suivante :

=SI(Marque& Modele=G2;MAX($H$1:H1)+1;'')

Repérer un critère numérique recoupé
Nous devons poursuivre la création de ces repères intermédiaires en cascade. Il s'agit maintenant de pouvoir isoler les véhicules dont le kilométrage est inférieur à celui mentionné dans la console. Bien entendu, si cette condition n'est pas émise, les résultats intermédiaires précédents doivent être restitués fidèlement. Dans le cas contraire, le critère doit être recoupé avec les précédents résultats.

• Sélectionner la cellule I2 de la feuille Parc,
• Taper le symbole égal (=) pour initier le calcul,
• Saisir la fonction conditionnelle suivie d'une parenthèse, soit : Si(,
• Taper le nom du premier critère numérique, soit : Km,
• Puis, taper l'égalité suivante : ='',

Si cette condition est vérifiée, elle stipule que le critère sur le kilométrage n'est pas mentionné. Dans ce cas, nous devons retranscrire fidèlement les précédents résultats. Dans le cas contraire, nous devons repérer les automobiles correspondant aux conditions croisées.

• Taper un point-virgule (;) pour passer dans la branche Alors de la fonction Si,
• Sélectionner ou inscrire les coordonnées du précédent résultat, soit : H2,
• Taper un point-virgule (;) pour passer dans la branche Sinon de la fonction Si,

Un nouveau critère est nécessaire. Il est double qui plus est. Il doit repérer chaque ligne d'un numéro incrémenté, dans la mesure ou le précédent calcul a déjà repéré l'enregistrement comme concordant et pour lequel le kilométrage est bien inférieur à la valeur inscrite.

• Saisir de nouveau la fonction conditionnelle suivie d'une parenthèse, soit : Si(,
• Taper la fonction pour recouper les conditions, suivie d'une parenthèse, soit : Et(,
• Inscrire la première condition comme suit : H2<>'',

Nous cherchons premièrement à vérifier que la ligne en cours est déjà concordante, grâce au calcul intermédiaire précédent.

• Taper un point-virgule (;) pour poursuivre l'énumération des critères,
• Sélectionner le premier kilométrage de la base de données, soit la cellule E2,
• Taper le symbole inférieur suivi du symbole égal, soit : <=, pour l'inégalité à vérifier,
• Saisir le nom du critère pour le kilométrage, soit : Km,
• Puis, fermer la parenthèse de la fonction Et,

Lorsque l'enregistrement était déjà concordant et que son kilométrage est bien inférieur à la condition émise, nous devons de nouveau repérer la ligne par un numéro incrémenté. La technique est strictement identique. Seule la plage de cellule doit être adaptée.

• Taper un point-virgule (;) pour passer dans la branche Alors de la fonction Si,
• Puis, inscrire l'opération suivante : Max($I$1:I1)+1,
• Taper un point-virgule (;) pour passer dans la branche Sinon de la fonction Si,
• Saisir deux guillemets, soit : '', pour garder la cellule vide en cas d'échec,

En effet, même si la correspondance avait été établie sur les critères textuels, dans la mesure où le kilométrage est au-delà des bornes, nous devons exclure l'enregistrement.

• Fermer les deux parenthèses des fonctions Si,
• Puis, valider la formule par le raccourci clavier CTRL + Entrée,
• Ensuite, double cliquer sur la poignée du résultat pour répercuter la formule sur le tableau,

A ce stade, les résultats retournés repérés par les numéros incrémentés sont strictement identiques. En effet, nous n'avons pas émis de condition sur le kilométrage.

• En cellule C7 de la feuille Recherche, taper 100000,

De retour sur la feuille Parc, vous notez que le repérage diffère. Des véhicules ont été exclus, par recoupement des conditions. De fait, les résultats s'affinent et notre outil commence à prendre forme.

La formule que nous avons bâtie est la suivante :

=SI(Km='';H2;SI(ET(H2<>'';E2<=Km);MAX($I$1:I1)+1;''))

Le principe est tout à fait identique pour le dernier calcul intermédiaire en colonne J. Il concerne le prix. Et pour poursuivre ce raisonnement entonnoir permettant la déduction des correspondances par éliminations, nous devons tout d'abord nous assurer que le critère sur le prix est émis. Si tel n'est pas le cas, les résultats précédents produits en cascade, doivent être fidèlement restitués. Dans le cas contraire en revanche, cette nouvelle condition doit être repérée pour conduire au repérage incrémenté et affiné.

• En cellule J2, construire le calcul suivant :

=SI(Prix='';I2;SI(ET(I2<>'';F2<=Prix);MAX($J$1:J1)+1;''))

• Puis, le répliquer sur la hauteur du tableau,

En tapant une condition sur le prix dans la feuille Recherche, 12000 par exemple, vous notez l'affinage des points de repérages sur la feuille Parc.



Ce sont les numéros de cette dernière colonne qui repèrent les automobiles correspondant à tous les cas de figure, que les critères soient formulés ou non.

Restituer les résultats des critères en cascade
Ce sont donc ces derniers numéros que nous devons rechercher pour produire l'extraction des véhicules concordant avec les contraintes du client. La fonction d'extraction est bien connue. Elle se nomme Index :

=Index(Tableau_de_recherche; numero_ligne; numero_colonne)

En guise de tableau de recherche, nous allons lui passer la colonne du champ à partir de laquelle la donnée doit être importée. Nous pourrons ainsi facilement répliquer le calcul pour les autres champs. Le numéro de ligne dépend de la position de ces numéros incrémentés. Nous devons les rechercher grâce à la fonction Excel Equiv :

=Equiv(Valeur_cherchée; Colonne_de_recherche; Mode_de_recherche)

La valeur cherchée n'est autre que ce numéro incrémenté. C'est pourquoi, nous lui passerons la fonction Ligne avec la cellule A1 en paramètre. Cette dernière retourne l'indice de ligne d'une cellule. En répliquant le calcul vers le bas, elle fournira et donc recherchera tous les numéros dans la suite logique. La colonne de recherche de ces numéros est forcément la colonne J de la feuille Parc. Le mode de recherche doit être fixé à zéro (0) pour une correspondance exacte et non approximative. De retour dans la fonction Index, le numéro de colonne est connu, il s'agira dans tous les cas de la première. En effet, nous allons définir le tableau de recherche sur la colonne précise des informations à retourner.

Enfin, il est judicieux d'imbriquer l'ensemble de ce calcul d'extraction dans la fonction Excel SiErreur. Cette dernière permet d'intercepter et de gérer les anomalies. En effet, une fonction de recherche, lorsqu'elle ne trouve aucune concordance, retourne un message d'erreur. Ainsi, nous les neutraliserons.

• En bas de la fenêtre Excel, cliquer sur l'onglet Recherche pour activer sa feuille,
• Puis, sélectionner la première cellule d'extraction, soit la cellule B12,
• Taper le symbole égal (=) pour débuter la formule,
• Saisir la fonction de gestion d'erreur, suivie d'une parenthèse, soit : SiErreur(,
• Taper la fonction d'extraction suivie d'une parenthèse, soit : Index(,
• En bas de la fenêtre Excel, cliquer sur l'onglet Parc pour activer sa feuille,
• Cliquer ensuite sur l'étiquette de colonne A, ce qui donne : Parc!A:A,

Ainsi, en guise de tableau de recherche, nous indiquons à la fonction Index que l'élément à extraire se situe dans les immatriculations. Bien sûr, cette immatriculation dépend de l'emplacement des numéros de repérage. Et comme nous l'expliquions, c'est la fonction Equiv qui va nous aider à les retrouver dynamiquement.

• Taper un point-virgule (;) pour passer dans l'argument du numéro de ligne,
• Saisir la fonction retournant l'indice de ligne suivie d'une parenthèse, soit : Equiv(,
• Saisir la fonction donnant la ligne d'une cellule, suivie d'une parenthèse, soit : Ligne(,
• Sélectionner une cellule de la première ligne, par exemple A1,

Ainsi, le numéro retourné par la fonction Ligne pour la première recherche sera le chiffre 1. C'est donc ce numéro que la fonction Equiv va tenter de trouver. Et comme nous répliquerons ce calcul sur les lignes du dessous, la cellule A1 deviendra A2 puis A3 etc... La recherche s'effectuera donc sur les chiffres 2 puis 3 etc..., soit sur les numéros incrémentés de repérage.

• Fermer la parenthèse de la fonction Ligne,
• Puis, taper un point-virgule (;) pour passer dans l'argument de la colonne de recherche,
• Sur la feuille Parc, cliquer sur l'étiquette de la colonne J,
• Puis, enfoncer la touche F4 du clavier pour la figer, ce qui donne : Parc!$J:$J,

En effet, contrairement à la première colonne fournie pour l'extraction (A:A), celle des numéros incrémentés ne doit pas bouger en même temps que le calcul sera répliqué sur la droite, pour extraire les autres informations de champs. Ces numéros sont immuablement renseignés en colonne J.

• Ensuite, taper un point-virgule suivi du chiffre 0, soit : ;0, pour une recherche exacte,
• Puis, fermer la parenthèse de la fonction Equiv,

De fait, nous sommes de retour dans les arguments de la fonction Index qui l'encapsule.

• Taper un point-virgule (;) pour passer dans l'argument de l'indice de colonne,
• Saisir le chiffre 1 pour indiquer l'unique colonne d'extraction stipulée en premier paramètre,
• Fermer la parenthèse de la fonction Index,
• Taper un point-virgule (;) pour passer dans le second argument de la fonction SiErreur,
• Saisir deux guillemets ('') pour conserver la cellule vide en cas d'anomalie,
• Fermer la parenthèse de la fonction SiErreur,
• Puis, valider la formule par le raccourci clavier CTRL + Entrée,

Le premier résultat tombe. Et il s'agit bien de la première immatriculation repérée par le dernier calcul intermédiaire en colonne J.

• Cliquer et glisser la poignée du résultat à l'horizontal jusqu'en colonne G,
• Cliquer alors sur l'icône de la balise active qui se propose en bas à droite de la sélection,
• Dans la liste, choisir l'option : Recopier les valeurs sans la mise en forme,

Des formats sont en effet prédéfinis sur ces zones d'extraction. D'ailleurs et aussitôt, en même temps que l'importation du détail du premier véhicule concordant, nous récupérons le formatage sur le prix et le kilométrage.

• Désormais, cliquer et glisser la poignée de la sélection à la verticale sur plusieurs dizaines de lignes vers le bas,

Un second enregistrement apparaît.

• Dans la zone de critères, augmenter la condition sur le prix à 15000,

Une troisième automobile apparaît.

• Dans la zone de critères, supprimer la condition 308 du modèle,

Aussitôt, quatre nouvelles voitures sont extraites. Elles répondent exactement aux conditions du client (Marque Peugeot de moins de 100 000 Km pour moins de 15 000 Euros).



Ces quelques manipulations nous ont permis de démontrer la souplesse et la puissance de l'outil que nous venons de construire. Quatre contraintes peuvent être imposées pour la recherche et l'extraction. Elles peuvent toutes être recoupées pour produire des résultats précis et affinés. Mais elles peuvent aussi se combiner sur un nombre inférieur ou être exploitées seules, pour offrir un plus large éventail de propositions.

Compter le nombre de résultats
Pour parachever la conception de l'outil de gestion des véhicules d'occasion, nous proposons de livrer un résultat de synthèse intéressant en cellule C9. Il s'agit d'indiquer le nombre d'automobiles correspondant à la demande. Pour cela, nous pouvons trivialement exploiter la fonction Excel NB sur la colonne G. Cette dernière compte les cellules numériques sur une plage de cellules. A chaque prix restitué, elle dénombrera un véhicule supplémentaire.

• En cellule C9, saisir la formule suivante puis la valider :

=NB(G:G)& ' véhicule(s) trouvé(s)'

Nous comptons les résultats numériques sur la colonne G. Nous concaténons ce score avec une indication explicite entre guillemets.



Une mise en forme conditionnelle se déclenche aussitôt pour mettre en valeur le résultat statistique. Notre outil Excel d'extraction est abouti.