Recherche conditionnelle sur de multiples colonnes

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


Sujets que vous pourriez aussi aimer :


Extractions conditionnelles multicolonnes

Cette nouvelle astuce Excel est l'occasion de découvrir comment réaliser l'extraction dans de multiples colonnes de toutes les valeurs les unes à la suite des autres, dès lors qu'elles correspondent à une condition émise.



Le tableau illustré par la capture relate les ventes réalisées par des équipes de commerciaux. Chaque équipe est constituée de deux commerciaux. Pour connaître l'impact d'un commercial sur les ventes, en vue de construire les meilleures équipes, l'utilisateur peut désigner l'un de ces noms à l'aide d'une liste déroulante placée sur la droite du tableau. Aussitôt, tous les CA des équipes dans lesquelles le commercial est intervenu, sont extraits les uns en dessous des autres. Et pour cela, c'est un raisonnement matriciel relativement simple qui est engagé.

Classeur Excel à télécharger
Pour la mise en place de cette solution, nous suggérons de récupérer un classeur offrant ces équipes avec leurs scores.

• Télécharger le classeur extractions-conditionnelles.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 trouvons le tableau des équipes. La plage des paires de commerciaux est reconnue sous le nom Equipes. La plage des chiffres d'affaires respectifs est reconnue sous le nom CA. Vous pouvez le constater en déployant la zone Nom en haut à gauche de la feuille Excel. Nous exploiterons ces noms pour simplifier la construction de la formule matricielle.

A droite du tableau et plus précisément en cellule G4, l'utilisateur peut désigner l'un de ces commerciaux avec une liste déroulante. Dès lors, tous les chiffres pour lesquels il a contribué, doivent être extraits les uns en-dessous des autres, à partir de la cellule G7.

La formule matricielle
Pour réaliser une extraction conditionnelle, nous avons besoin de la fonction Si dans la fonction Index. Mais pour que seuls soient retenus les indices de ligne dans lesquels se situe le commercial désigné, la condition doit intervenir dans la fonction Petite.Valeur, elle-même placée dans l'argument de la ligne de la fonction Index. C'est cette fonction Petite.Valeur qui va permettre d'énumérer les lignes concordantes les unes à la suite des autres pour produire ces extractions.

• Cliquer sur la cellule du premier résultat à extraire pour sélectionner la cellule G7,
• Taper le symbole égal (=) pour débuter la construction matricielle,
• Inscrire la fonction de gestion des anomalies suivie d'une parenthèse, soit : SiErreur(,

Comme vous le savez, une fonction d'extraction, lorsqu'elle ne trouve pas ce qui lui est demandé, répond par un message d'erreur. Pour prévoir large, nous souhaitons reproduire la logique de la formule matricielle jusqu'en cellule G14. Mais dans bien des cas, les extractions concordantes ne seront pas aussi nombreuses. Cette fonction de gestion va donc permettre de neutraliser ces messages d'erreur disgracieux.

• Inscrire alors la fonction d'extraction suivie d'une parenthèse, soit : Index(,
• Désigner la plage des chiffres d'affaires par son nom, soit : CA,

Ce sont en effet tous les chiffres d'affaires des équipes dans lesquelles le commercial désigné intervient, que nous souhaitons restituer.

• Taper un point-virgule (;) pour passer dans l'argument du numéro de ligne de la fonction Index,

Dans ce raisonnement matriciel, ce n'est pas une seule ligne qui nous intéresse mais toutes celles qui impliquent le commercial mentionné. Et c'est la fonction Petite.Valeur qui va nous permettre de les énumérer dans l'ordre.

• Inscrire le nom de cette fonction suivi d'une parenthèse, soit : Petite.Valeur(,

En premier argument, elle attend la matrice ou la plage sur laquelle elle doit exercer pour en extraire les plus petites valeurs dans l'ordre. Mais toutes les lignes du tableau ne sont pas concernées. Elles doivent vérifier la condition par rapport à l'existence du commercial désigné par l'utilisateur.

• Inscrire la fonction conditionnelle suivie d'une parenthèse, soit : Si(,
• Dès lors, construire le critère suivant : Equipes=$G$4,

Sur le tableau des équipes en effet, nous cherchons à trouver les lignes qui hébergent le nom du commercial choisi en cellule G4. Attention, cette dernière doit être figée avec les références absolues ($). Nous allons ensuite répliquer cette formule sur les lignes du dessous. Et malgré ce déplacement, le critère doit toujours être vérifié par rapport à ce nom placé en G4.

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

A chaque fois que ce critère est rencontré dans le tableau, nous souhaitons en ressortir les indices de ligne. Souvenez-vous en effet, nous sommes dans l'argument du numéro de ligne pour la fonction Index. Elle n'a pas besoin de connaître le numéro de colonne pour l'extraction puisque cette plage des CA n'est dotée que d'une seule colonne. Et pour ressortir ces numéros concordants, nous allons construire une matrice virtuelle débutant à partir de la première ligne du tableau et s'étendant jusqu'à la dernière.

• Inscrire la fonction donnant l'indice de ligne d'une cellule suivie d'une parenthèse, soit : Ligne(,

Nous l'avons dit, ce n'est pas un indice que nous souhaitons, mais tous les indices concordants avec le critère émis par la fonction Si. Et comme il s'agit d'une matrice virtuelle de numéros à construire, nous devons tout d'abord penser à l'interpréter.

• Inscrire la fonction d'interprétation suivie d'une parenthèse, soit : Indirect(,
• Inscrire le premier numéro de ligne suivi du symbole deux points entre guillemets : "1:",

Nous définissons ainsi la borne inférieure du tableau.

• Taper alors le symbole de concaténation (&) pour annoncer la borne supérieure à suivre,

Cette borne dépend du nombre d'éléments dans le tableau. C'est la fonction Lignes (Au pluriel) donc qui permet de les compter.

• Inscrire la fonction comptant les lignes d'un tableau, suivie d'une parenthèse, soit : Lignes(,
• Désigner le tableau des équipes par son nom, soit : Equipes,
• Puis, fermer la parenthèse de la fonction Lignes,
• Fermer la parenthèse de la fonction Indirect,
• Fermer la parenthèse de la fonction Ligne,
• Puis, fermer la parenthèse de la fonction Si,
• Dès lors, taper un point-virgule (;) pour passer dans le rang de la fonction Petite.Valeur,

Ce n'est pas un rang qui nous intéresse mais chaque rang de ligne concordante dans l'ordre. Et pour cela, nous pouvons exploiter la fonction Ligne au départ sur une cellule de la première ligne (Comme A1) pour fournir le deuxième puis le troisième résultat concordant, etc... au fil de la réplication de la formule sur les lignes du dessous.

• Inscrire la fonction pour l'indice de ligne suivie d'une parenthèse, soit : Ligne(,
• Cliquer sur une cellule de la première ligne comme A1,
• Fermer la parenthèse de la fonction Ligne,
• Fermer la parenthèse de la fonction Petite.Valeur,
• Puis, fermer la parenthèse de la fonction Index,
• Dès lors, taper un point-virgule (;) pour passer dans le second argument de la fonction SiErreur,
• Inscrire alors deux guillemets ("") pour garder la cellule vide s'il n'y a plus d'extraction,
• Fermer la parenthèse de la fonction SiErreur,
• Puis, valider la formule matricielle avec le raccourci clavier CTRL + MAJ + Entrée,

Comme vous pouvez l'apprécier, le premier résultat tombe. Et il s'agit bien du premier chiffre d'affaires de l'équipe dans laquelle intervient le commercial choisi. La mise en forme conditionnelle qui était prédéfinie en atteste.

• Cliquer et glisser la poignée du résultat jusqu'en cellule G14,

Cette fois, ce sont tous les résultats des équipes dans lequel le commercial participe qui tombent dans l'ordre les uns en dessous des autres et ce, grâce à la fonction Petite.Valeur dans ce raisonnement matriciel.

Et si vous changez de commercial en choisissant par exemple Strofobe, les valeurs concernées s'actualisent aussitôt tandis que la mise en forme conditionnelle de repérage s'ajuste dans le même temps. La syntaxe de la formule matricielle que nous avons construite pour réaliser cette extraction conditionnelle multicolonnes est la suivante :

=SIERREUR(INDEX(CA; PETITE.VALEUR(SI(Equipes=$G$4; LIGNE(INDIRECT("1:" & LIGNES(Equipes)))); LIGNE(A1))); "")