Repérer les cellules identiques sur les premières lettres

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


Sujets que vous pourriez aussi aimer :


Repérer sur les mêmes premières lettres

Pour isoler rapidement tous les produits appartenant à une même famille, il est question d'établir une reconnaissance commune sur les premiers caractères.



Sur l'exemple illustré par la capture, deux types de codes produits sont inscrits dans deux colonnes séparées. Dans la première rangée, il s'agit de références textuelles. Dans la seconde, elles sont numériques. Nous le verrons en effet, les méthodes de reconnaissance diffèrent selon le cas. Sur la droite et dans une première case, l'utilisateur saisit une longueur. Instantanément, toutes les références ayant au moins ces N premiers caractères communs avec une autre, ressortent en vert. Mais il peut aussi définir des séquences précises à déceler. Et cette fois, les références embarquant ces suites de caractères, ressortent en gras. Les deux mises en valeur peuvent donc se cumuler.

Classeur Excel à télécharger
Pour développer ces techniques, nous suggérons d'appuyer les travaux sur un classeur Excel hébergeant ces différentes séries de références.

• Télécharger le classeur sequences-caracteres-identiques.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 retrouvons bien les deux rangées de codes produits. Sur la droite, l'utilisateur saisit une longueur en cellule G4. Il s'agit du nombre de premiers caractères que doivent avoir en commun les références, pour être repérées. Puis, il saisit une séquence textuelle et une séquence numérique en cellules G7 et G8. L'objectif est de faire ressortir dans les deux cas, les codes produits proposant exactement cet enchaînement de caractères.

Reconnaissance sur les premières lettres
Pour faire ressortir les références qui comptent les mêmes premières lettres, nous devons les dénombrer avec la fonction Nb.Si. Mais comme le critère du décompte n'est pas fixe, nous devons appeler les WildCards à la rescousse. La longueur de la séquence préliminaire est variable. Nous devons la chercher, peu importe ce qui se trouve après ces lettres.

• Sélectionner tous les codes textuels, soit la plage de cellules C4:C13,
• Dans la section Styles du ruban Accueil, cliquer sur le bouton Mise en forme conditionnelle,
• En bas 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 texte du dessous pour l'activer,
• Taper le symbole égal (=) pour initier la syntaxe de la formule,
• Inscrire la fonction de dénombrement suivie d'une parenthèse, soit : Nb.Si(,
• Désigner de nouveau les références à analyser, ce qui donne : $C$4:$C$13,
• Taper un point-virgule (;) pour passer dans l'argument du critère à compter,

Nous devons compter tous les codes produits pour lesquels d'autres références commencent par les mêmes premières lettres. Cette quantité dépend de l'information numérique saisie par l'utilisateur en cellule G4. Et pour analyser une chaîne sur ses premiers caractères, il existe la fonction Excel Gauche.

• Inscrire cette fonction suivie d'une parenthèse, soit : Gauche(,
• Désigner le premier code produit à comparer aux autres en cliquant sur sa cellule C4,

Comme toujours dans une mise en forme conditionnelle, la référence apparaît figée par défaut ($C$4). Or, elles doivent toutes être analysées.

• Enfoncer trois fois de suite la touche F4 du clavier pour faire disparaître les dollars,
• Taper un point-virgule (;) pour passer dans la longueur de découpe,
• Cliquer sur le choix de l'utilisateur en cellule G4,

Cette fois, nous conservons les dollars. Au gré de l'analyse chronologique de la règle, c'est toujours cette information qui doit être utilisée pour la découpe. Elle ne doit donc pas se déplacer.

• Fermer alors la parenthèse de la fonction Gauche,

Ce critère strict ne pourra jamais être trouvé dans la mesure où les codes produits sont composés d'autres caractères. C'est par le WildCard de l'astérisque que nous devons faire comprendre à la fonction Nb.Si que nous souhaitons trouver les cellules qui commencent par cette même séquence.

• Ajouter le symbole de concaténation suivi d'un astérisque entre guillemets, soit : & "*",
• Fermer la parenthèse de la fonction Nb.Si,
• Puis, ajouter le critère suivant : >1,

C'est en effet lorsque plusieurs cellules proposent cette même première suite de caractères que nous devons les repérer en couleur.

• Pour cela, cliquer sur le bouton Format en bas à droite de la boîte de dialogue,
• Dans celle qui suit, activer l'onglet Remplissage,
• Dans la palette de couleurs, choisir un vert clair pour le fond des cellules concordantes,
• Activer alors l'onglet Police de la boîte de dialogue,
• Avec la seconde liste déroulante, choisir un vert foncé pour la couleur du texte,
• Revenir sur la première boîte de dialogue en cliquant sur le bouton Ok,
• Valider la création de cette règle de mise en forme en cliquant de nouveau sur le bouton Ok,

Comme vous pouvez le voir, toutes les références qui coïncident au moins avec une autre sur les six premiers caractères, ressortent dans cette mise en forme explicitement différente. Il s'agit d'un bon moyen d'isoler visuellement des groupes de catégories cherchés.

Si vous changez cet indicateur en cellule G4 pour isoler par exemple uniquement les références qui commencent par les dix mêmes premiers caractères, naturellement vous constatez que certains codes sont exclus de la mise en valeur puisqu'ils ne sont plus recoupés avec au moins un autre.



Par contre, si vous tentez d'adapter cette syntaxe (=NB.SI($E$4:$E$13; GAUCHE(E4;$G$4) &"*")>1) sur les codes numériques, elle ne fonctionne pas et c'est fort logique. L'intervention de l'astérisque transforme le critère en une chaîne de texte incompatible dans ce contexte.

Comparaisons sur les premiers chiffres
Pour résoudre le problème dans le contexte de codes numériques, c'est comme souvent une technique matricielle qui permet de nous sortir de ce faux pas. L'idée est de passer une matrice conditionnelle à la fonction SommeProd. Il s'agit de confronter les premiers caractères de la cellule en cours d'analyse avec les premiers caractères de la matrice de tous les codes. Ce sont des indicateurs numériques nuls ou positifs qui seront renvoyés. Comme ils seront à l'issue additionnés par la fonction SommeProd, dès lors que le décompte atteint deux unités, nous saurons qu'il s'agit d'une référence concordant avec une autre, et donc à faire ressortir.

• Sélectionner tous les codes numériques, soit la plage de cellules E4:E13,
• Dans la section Styles du ruban Accueil, cliquer sur le bouton Mise en forme conditionnelle,
• En bas 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 texte du dessous pour l'activer,
• Taper le symbole égal (=) pour initier la syntaxe de la formule,
• Inscrire la fonction matricielle suivie d'une parenthèse, soit : SommeProd(,
• Ouvrir une nouvelle parenthèsepour accueillir une matrice conditionnelle,
• Inscrire la fonction Gauche suivie d'une parenthèse, soit : Gauche(,
• Désigner le premier code numérique en cliquant sur sa cellule E4, ce qui donne : $E$4,
• Comme précédemment, enfoncer trois fois la touche F4 du clavier, ce qui donne : E4,
• Taper un point-virgule (;) pour passer dans l'argument de la longueur de découpe,
• Désigner cette longueur en cliquant sur la cellule G4,
• Puis, fermer la parenthèse de la fonction Gauche,
• Dès lors, taper le symbole égal (=) pour annoncer le critère matriciel à honorer,

Nous l'avons dit, ce sont les N premiers caractères de chaque cellule passée en revue que nous souhaitons comparer au N premiers caractères de toutes les autres pour savoir s'il existe des concordances.

• Taper de nouveau la fonction Gauche suivie d'une parenthèse, soit : Gauche(,
• Désigner la matrice des codes numériques, ce qui donne : $E$4:$E$13,
• Taper un point-virgule (;) pour passer dans l'argument de la longueur de découpe,
• Comme précédemment, cliquer sur la cellule G4 du choix utilisateur, soit : $G$4,
• Fermer la parenthèse de la fonction Gauche,
• Puis, fermer la parenthèse de la matrice conditionnelle,

A ce stade, la matrice conditionnelle répond par des booléens (Vrai ou Faux). Pour qu'ils puissent être additionnés par la fonction SommeProd, afin de déterminer quels sont les codes possédant les mêmes débuts de séquences, nous devons les convertir en chiffres.

• Pour forcer la conversion, multiplier ce résultat par 1, soit : *1,
• Dès lors fermer la parenthèse de la fonction SommeProd,
• Puis, ajouter le critère suivant : >1,

Seuls nous intéressent en effet les codes qui se recoupent sur les N premiers chiffres.

• Cliquer sur le bouton Format en bas à droite de la boîte de dialogue,
• Dans celle qui suit, activer l'onglet Remplissage,
• Dans la palette de couleurs, choisir un vert clair pour le fond des cellules concordantes,
• Activer alors l'onglet Police de la boîte de dialogue,
• Avec la seconde liste déroulante, choisir un vert foncé pour la couleur du texte,
• Revenir sur la première boîte de dialogue en cliquant sur le bouton Ok,
• Valider la création de cette règle de mise en forme en cliquant de nouveau sur le bouton Ok,

Si vous aviez choisi une longueur de 10 unités, de retour sur la feuille, vous avez la désagréable sensation de penser que cette règle ne fonctionne pas puisqu'aucun code n'est révélé par la mise en forme. Cela tient au fait qu'aucune référence numérique ne propose la même suite sur 10 chiffres consécutifs en début de chaîne.



En revanche, si vous remplacez cette longueur par le chiffre 6, à validation, vous constatez que tous les codes proposant une séquence identique en préfixe sont bien remontés. La syntaxe complète que nous avons construite est la suivante :

=SOMMEPROD((GAUCHE(E4;$G$4)=GAUCHE($E$4:$E$13; $G$4))*1)>1

Repérer une séquence dans les cellules
Le défi suivant est simple. Il consiste à faire ressortir en gras toutes les cellules de texte portant la séquence mentionnée en cellule G7. Quand il s'agit de textes, rien n'est plus facile. La fonction Excel Cherche permet de déceler la présence d'un fragment dans une chaîne.

• Sélectionner de nouveau tous les codes textuels, soit la plage de cellules C4:C13,
• Dans la section Styles du ruban Accueil, cliquer sur le bouton Mise en forme conditionnelle,
• En bas 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 texte du dessous pour l'activer,
• Bâtir alors la syntaxe suivante : =CHERCHE($G$7;C4),

Nous cherchons le fragment renseigné en cellule G7 ($G$7) dans tous les codes textuels en partant de la cellule C4, puisque l'analyse est chronologique. Dès que la fonction Cherche répond positivement, nous devons faire ressortir les cellules concordantes en gras.

• Pour cela, cliquer sur le bouton Format en bas à droite de la boîte de dialogue,
• Activer alors l'onglet Police de celle qui suit,
• Dans la section Style, cliquer sur l'attribut Gras,
• Revenir sur la première boîte de dialogue en cliquant sur le bouton Ok,
• Puis, valider la règle en cliquant de nouveau sur le bouton Ok,

De retour sur la feuille, vous constatez que toutes les cellules accueillant cette séquence sont effectivement repérées en gras. Bien sûr, si vous modifiez la séquence de recherche, les repérages visuels s'adaptent automatiquement.

Repérer les séquences de chiffres
Pour les cellules numériques, le problème est tout autre selon les versions d'Excel. Avec les actuelles, la précédente syntaxe peut être adaptée comme suit : =CHERCHE($G$8;E4). Il convient bien sûr de lui associer un style gras. Mais dans les autres cas, pour trouver la séquence exacte en début de chaîne, il faut travailler sur des nombres convertis en textes, grâce à la fonction Excel Texte, pour trouver la concordance avec la série de chiffres fournie en cellule G8 :

=GAUCHE(TEXTE(E4;"00"); NBCAR($G$8)) = TEXTE($G$8;"0")