Recherches sur les premières lettres par formule Excel

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


Sujets que vous pourriez aussi aimer :


Recherches sur les premiers caractères

Pour évaluer la quantité de produits par catégorie, il est nécessaire de pouvoir enclencher une recherche et une extraction sur les premiers caractères concernés. Les articles d'un même type sont en effet généralement réunis par un préfixe de référence identique.



Dans l'exemple illustré par la capture, l'utilisateur choisit une catégorie à l'aide d'une liste déroulante. Cette catégorie est représentée par les six premiers caractères des références. En-dessous et instantanément, un calcul de dénombrement livre le volume d'articles correspondant à la catégorie ainsi ciblée.

Fichier source
Pour démontrer l'astuce permettant de dénombrer les enregistrements correspondant à la recherche effectuée sur les premiers caractères, nous proposons de récupérer un tableau de données.

• Télécharger le fichier compter-debut-sequence.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 découvrons le tableau référençant les produits d'une entreprise sur plus de 200 lignes. En consultant la première colonne des références (produit_ref), vous constatez que les articles d'un même type possèdent effectivement un code débutant par les six mêmes premiers caractères. C'est la raison pour laquelle l'utilisateur peut actionner une liste déroulante en cellule I4. En fonction du choix émis sur le groupe catégorie, nous devons livrer en cellule I7 le nombre de produits appartenant au même type.

Recherche matricielle sur les premiers caractères
Un raisonnement matriciel peut effectuer une recherche globale sur l'ensemble des données contenues dans une ou plusieurs rangées. Cette recherche sur les premiers caractères doit de plus aboutir sur le décompte de tous les enregistrements concordants. C'est la raison pour laquelle nous devons engager la fonction Excel SommeProd mais d'une façon dérivée. Nous devons lui passer des matrices conditionnelles de recherche. Chaque résultat trouvé sera repéré par un indicateur booléen à convertir en chiffre. Et chacun de ces chiffres sera additionné à l'issue pour fournir le décompte souhaité. Pour enclencher une recherche globale sur les premiers caractères, nous devons exploiter la fonction Excel Cherche.

• Sélectionner le résultat à trouver en cliquant sur sa cellule I7,
• Taper le symbole égal (=) pour initier la syntaxe de la formule matricielle,
• Inscrire la fonction matricielle suivie d'une parenthèse, soit : SommeProd(,
• Inscrire la fonction conditionnelle suivie d'une parenthèse, soit : Si(,
• Inscrire la fonction de test des anomalies suivie d'une parenthèse, soit : EstErreur(,

En effet, la fonction Cherche lorsqu'elle ne trouve pas, répond par des messages d'erreur. Ces erreurs intercalées sabotent le résultat de la somme finale produite par la fonction SommeProd. Nous devons donc les neutraliser avant d'enclencher le décompte sur les concordances.

• Inscrire la fonction de recherche suivie d'une parenthèse, soit : Cherche(,
• Désigner la catégorie à trouver en cliquant sur la cellule I4,
• Taper un point-virgule (;) pour passer dans l'argument du texte de recherche,

En guise de texte, nous allons lui passer une matrice, celle des références. Celle-ci est reconnue par le nom Ref. Vous pourrez le constater en consultant la zone Nom en haut à gauche de la feuille Excel.

• Désigner la matrice des références par son nom, soit : Ref,

Comme vous pouvez le voir, elle est instantanément surlignée par Excel, puisque désormais impliquée dans le calcul.



Nous devons maintenant valider ce critère.

• Fermer la parenthèse de la fonction Cherche,
• Puis, fermer la parenthèse de la fonction EstErreur,

Si la recherche est infructueuse, donc si la référence en cours d'analyse ne recoupe pas les six premiers caractères choisis, nous devons répondre par le chiffre 0. L'addition de plusieurs zéros conduit à zéro. C'est ainsi que la somme finale enclenchée par la fonction SommeProd sera en mesure d'exclure tous les produits non concordants.

• Taper un point-virgule (;) pour passer dans la branche Alors de la fonction Si,
• Inscrire le chiffre zéro (0) pour ignorer les erreurs de recherche,
• Taper un point-virgule (;) pour passer dans la branche Sinon de la fonction Si,

Dans le cas contraire, nous devons repérer les positions des enregistrements concordant avec la catégorie demandée. Pour cela, nous devons enclencher exactement la même recherche que celle du critère.

• Reproduire le calcul de recherche précédent, soit : Cherche(I4;Ref),

Lorsque les résultats concordent, la fonction Cherche répond par un indicateur booléen (Vrai). Toutes les non concordances sont neutralisées par le critère de la fonction conditionnelle. Pour que la fonction Excel SommeProd puisse additionner ces concordances, nous devons convertir ces booléens en chiffres. Et pour forcer cette conversion, il suffit de multiplier ces résultats par le chiffre 1.

• Forcer la conversion avec la multiplication par le chiffre 1, soit : *1,
• Fermer la parenthèse de la fonction Si,
• Puis, fermer la parenthèse de la fonction SommeProd,
• Enfin, valider nécessairement la formule matricielle par le raccourci CTRL + MAJ + Entrée,

La fonction SommeProd raisonne naturellement sur des matrices. Mais pour que son critère exploitant la fonction basique Cherche en fasse de même, nous devons forcer le raisonnement matriciel par ce raccourci.



Le résultat tombe et indique que 5 articles appartiennent à la gamme 3062VT. Vous pouvez facilement le constater en les repérant dans le tableau d'origine, à plus forte raison que les articles sont regroupés par catégories grâce à un tri. Si vous changez de gamme à l'aide de la liste déroulante en cellule I4, le résultat se met instantanément à jour. Nous avons donc parfaitement réussi à réaliser le dénombrement des enregistrements concordant avec les premiers caractères de recherche. La syntaxe complète de la formule matricielle que nous avons bâtie est la suivante :

{=SOMMEPROD(SI(ESTERREUR(CHERCHE(I4;Ref)); 0; CHERCHE(I4; Ref)*1))}

Bien sûr et vous l'aviez sans doute anticipé, nous avons poussé le raisonnement pour la bonne compréhension Mais nous aurions pu faire plus simple :

=SOMMEPROD(SI(ESTERREUR(CHERCHE(I4;Ref)); 0; 1))

Surligner les éléments d'une catégorie cherchée
Pour des résultats plus probants, nous proposons de mettre automatiquement en valeur la ligne de tous les enregistrements correspondant à un groupe cherché. Pour cela, il suffit de bâtir une règle de mise en forme conditionnelle exploitant la fonction de recherche, exactement de la même façon. Mais cette fois, le raisonnement n'est pas matriciel. Il est chronologique. Il doit donc débuter à partir de la première référence.

• Sélectionner toutes les données du tableau, soit la plage de cellules B4:G247,
• 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 ...,
• Puis, cliquer dans la zone de saisie du dessous,
• Dès lors, taper le symbole égal (=) pour initier la syntaxe de la règle,
• Inscrire la fonction de recherche suivie d'une parenthèse, soit : Cherche(,
• Désigner la valeur cherchée en cliquant sur sa cellule I4, ce qui donne : $I$4,
• Taper un point-virgule (;) pour passer dans l'argument du texte de recherche,
• Désigner la première référence en cliquant sur sa cellule B4,
• Enfoncer deux fois la touche F4 du clavier pour la libérer en ligne, ce qui donne : $B4,

Nous l'avons dit, l'analyse d'une mise en forme conditionnelle est chronologique. Nous devons donc débuter l'étude à partir de la première référence. Nous libérons sa ligne pour qu'elles soient toutes passées en revue. Mais pour chaque produit, le critère doit être vérifié sur la référence. C'est bien dans cette colonne B que les premiers caractères du groupe doivent être trouvés. C'est la raison pour laquelle nous conservons sa colonne figée.

• Fermer la parenthèse de la fonction Cherche,

Lorsque la concordance sur les premiers caractères est avérée, nous devons faire ressortir la ligne entière de façon explicitement différente.

• Pour cela, cliquer sur le bouton Format en bas de la boîte de dialogue,
• Dans la boîte de dialogue qui suit, activer l'onglet Police,
• Déployer la liste déroulante pour la couleur de texte,
• Dans la palette de couleurs, choisir un orange vif,
• Valider ce réglage par le bouton Ok,

Nous sommes de retour sur la première boîte de dialogue. Elle résume la situation. Tout enregistrement repéré comme concordant devra se parer de la couleur orange définie.

• Valider la création de la règle de mise en forme conditionnelle en cliquant sur le bouton Ok,

En déroulant le tableau, vous constatez que tous les produits de la catégorie mentionnée par la liste déroulante sont effectivement surlignés automatiquement.



Et bien sûr, si vous changez de groupe catégorie avec la liste déroulante, en même temps que le résultat du décompte s'ajuste, la mise en valeur dynamique vient parfaitement repérer les lignes des produits concernés. Ce repérage visuel recoupe parfaitement le calcul matriciel pour simplifier l'interprétation du résultat et valider sa cohérence.

Pour terminer, sachez que dans ce cas très particulier, le raisonnement matriciel n'est pas la seule issue. Un dénombrement classique impliquant la fonction Nb.Si et un caractère générique (WildCard) permet d'aboutir encore plus simplement à la solution : =NB.SI(Ref;I4 & "*").