Comment identifier les cellules identiques en 1 clic sur Excel (couleur selon valeur) ?

Comment identifier rapidement les cellules identiques en un seul clic sur Excel ? Bonjour à tous, c'est Alex d'excelformation.fr et dans ce tutoriel, je vais vous montrer comment créer une petite macro VBA capable de détecter toutes les valeurs en double dans une sélection et de les colorer automatiquement. La couleur qui sera alors appliquée sera différente pour chacune des valeurs. Par exemple ici, nous sélectionnons les cellules de la colonne TASH et en cliquant sur Distinguer les valeurs, nous voyons que chaque valeur présente dans cette colonne aura une couleur qui lui est propre. Nous allons pouvoir faire la même chose pour les opérateurs afin de bien nous rendre compte que la couleur rouge correspondra à toutes les tâches affectées à Alice, la verte, celles affectées à Bob, etc. Alors restez bien jusqu'à la fin de cette vidéo pour découvrir comment visualiser rapidement chaque doublon distinct grâce à des codes couleurs uniques en quelques étapes simples. Alors vous trouverez très régulièrement de nouvelles vidéos sur la chaîne alors pensez à vous abonner et surtout à activer la clochette de notification afin d'être sûr de ne manquer aucune astuce et de devenir rapidement un pro d'Excel. Comme d'habitude, si vous voulez être sûr de bien comprendre tout ce que nous allons voir dans le cadre de cette vidéo, vous pouvez télécharger le support de ce tutoriel ainsi que son corrigé en suivant le lien disponible dans la description de la vidéo ce qui vous permettra de réaliser les opérations en même temps que moi pour bien comprendre tout ce que nous allons voir dans cette vidéo. Alors pour illustrer ce tutoriel, nous allons pouvoir utiliser le tableau que nous avons actuellement à l'écran qui reprend des données simples sur la liste des tâches à mettre en place pour un projet. Ce tableau contient plusieurs valeurs en double dans la colonne tâches mais également dans la colonne opérateurs étant donné qu'une personne pourrait être amenée à réaliser plusieurs tâches. Notre objectif sera donc ici de détecter ces valeurs en double et de leur appliquer une valeur unique pour chacune de ces valeurs doublons de façon à les identifier visuellement. Alors pour réaliser cette opération, nous allons bien entendu devoir mettre en place une petite macro en VBA VBA pour Visual Basic for Application qui est un langage de programmation intégré à Excel mais également dans la plupart des applications de la suite Office et qui permet de créer des automatismes que nous appelons des macros. Ces macros peuvent donc effectuer des tâches répétitives de manière automatique ce qui sera très utile pour accélérer des processus comme dans notre cas où nous voulons détecter des doublons et appliquer des couleurs de manière automatique fonctionnalité qui n'est pas prévue par défaut par Excel. En effet, imaginez si nous devions analyser chacune des valeurs de ce tableau afin de colorer manuellement chaque doublon cela pourrait prendre un temps fou surtout sur des grandes bases de données alors qu'avec le VBA, il va nous suffire de mettre en place quelques lignes de code pour pouvoir automatiser ce processus et réaliser ses opérations en un seul clic. Alors évidemment dans le cadre de ce tutoriel nous n'allons faire qu'effleurer l'étendue de la puissance de VBA mais si vous souhaitez aller plus loin je vous invite à consulter mon livre dédié au VBA pour les grands débutants en suivant le lien disponible dans la description de la vidéo. Et pour faciliter l'exécution de notre macro nous allons vouloir créer un bouton qui nous permettra de lancer d'un simple clic la coloration des cellules sans avoir besoin de passer par le menu VBA. Et donc depuis Excel, pour insérer un bouton nous allons nous rendre dans le menu insertion et sur la droite nous allons dérouler texte afin de sélectionner zone de texte. Ensuite nous allons pouvoir dessiner le bouton directement sur la feuille de calcul à l'endroit et à la taille désirée et nous allons saisir le texte descriptif comme par exemple distinguer les valeurs pour indiquer la fonction de ce bouton. Si nous le souhaitons, nous pouvons maintenant mettre en forme ce bouton en centrant le texte horizontalement et verticalement et si nous nous rendons dans le menu format de forme nous allons également pouvoir sélectionner l'un des styles proposés par Excel afin de mettre en forme rapidement les différents éléments de ce bouton. Ici le bouton est bleu mais nous pouvons facilement sélectionner un autre style pour personnaliser son apparence. Maintenant que notre bouton est mis en forme nous allons pouvoir lui attribuer une macro commande automatiquement lorsque nous cliquerons sur celui-ci. Pour cela, nous effectuons un clic droit et nous sélectionnons affecter une macro. Sur la boîte de dialogue qui s'affiche, nous retrouvons ici une zone qui ici est vide mais qui permet de retrouver toutes les macros enregistrées dans le classeur. Donc ici nous allons créer cette première macro en saisissant le nom, donc par exemple distinguer doux blond et pour créer automatiquement la macro et l'afficher nous allons cliquer sur le bouton nouvelles. A ce moment-là, Excel va lancer l'éditeur de code de Visual Basic Editor et nous créer la macro avec le nom que nous venons d'insérer directement à l'intérieur de celui-ci. Nous avons ici deux lignes, une ligne qui commence par sub qui vient donc démarrer la macro et une ligne qui finit par end sub c'est-à-dire qu'il va ici s'agir de la fin de la macro. De cette manière, tout ce que nous allons saisir entre ces deux lignes sera exécuté automatiquement à chaque fois que nous cliquerons sur le bouton lié à cette macro. Nous allons maintenant pouvoir entrer dans le vif du sujet à savoir l'écriture du code à l'intérieur de notre macro. Il faut savoir que la macro que nous allons créer sera constituée de trois étapes principales. La première étape va consister à créer un dictionnaire qui va permettre de compter chaque occurrence de valeur. Nous reviendrons un peu plus tard sur ce terme de dictionnaire. La deuxième étape va permettre d'identifier les doublons et enfin la troisième étape va consister à appliquer une couleur unique pour chacune de ces valeurs doublons. Pour commencer, nous allons déclarer quatre variables que nous aurons besoin d'utiliser tout au long de notre macro. Il faut savoir qu'en VBA mais dans n'importe quel autre langage de programmation également, une variable est un espace de stockage temporaire en mémoire qui permet de conserver une donnée pendant l'exécution de la macro afin de pouvoir l'appeler et la retraiter. Chaque variable a un type qui va permettre de définir la nature de la donnée qu'elle pourra contenir comme un nombre, un texte ou encore un objet. Cela va donc nous permettre de manipuler et d'utiliser ces données facilement tout au long de notre programme. Alors la première variable que nous allons créer en utilisant le mot clé DIM suivi du nom de cette variable que nous allons appeler tout simplement C comme cellule va représenter chacune des cellules que nous allons analyser à l'intérieur de notre sélection. Nous allons donc donner un type à cette variable comme nous venons de le voir avec le mot clé AS et ici nous allons vouloir que cette variable permette de manipuler des cellules. Nous allons donc la typer en tant que range, ce qui signifie qu'elle peut faire référence à une cellule ou à une plage de plusieurs cellules à l'intérieur de notre feuille de calcul. En utilisant cette variable C dans notre code, nous pourrons vérifier chaque cellule individuellement dans la plage de sélection afin d'appliquer les actions que nous souhaitons mettre en place. Pour déclarer la deuxième variable, nous saisissons donc une virgule et celle-ci nous allons tout simplement l'appeler valeur et définir que son type est de type variant, c'est-à-dire que son type pourra être adapté automatiquement en fonction du contenu de celle-ci. Cette variable valeur va permettre de stocker la valeur contenue dans chaque cellule que nous parcourons afin de pouvoir examiner le contenu de ces cellules et de vérifier si cette valeur apparaît plusieurs fois dans notre sélection. Nous allons maintenant revenir sur une notion que nous avons abordée rapidement tout à l'heure car nous allons créer une variable que nous allons appeler dictionnaire qui va être un objet spécial qui va fonctionner comme une sorte de liste ou de tableau avancé mais qui va nous permettre d'associer chaque élément que nous allons appeler clé à une valeur spécifique. Dans notre macro, le dictionnaire nous servira à stocker chaque valeur de cellule et le nombre de fois qu'elle apparaît dans la sélection. Nous allons donc typer cette variable en tant que object. Et enfin, la dernière variable que nous allons instancer ici est la variable couleur index qui est un compteur de type integer, c'est-à-dire qu'il s'agit d'une variable qui permet de stocker un nombre entier que nous allons utiliser pour attribuer une couleur unique pour chaque doublon détecté. Excel dispose en effet de plusieurs couleurs intégrées que l'on peut appliquer avec des indices de 1 à 56 ce qui va nous permettre de définir une couleur différente pour chaque valeur dupliquée en utilisant les indices de couleurs disponibles dans Excel. Maintenant que nous avons créé nos variables, nous pouvons nous placer à la ligne et nous allons créer le dictionnaire en utilisant la méthode createObject de VBA. Nous allons définir le dictionnaire en utilisant le mot-clé set qui permet d'attribuer un élément à un objet, puis le nom de notre objet, c'est-à-dire ici dictionnaire. Et comme je viens de vous l'expliquer, nous allons utiliser la méthode createObject pour définir que celui-ci est un objet. Pour cela, entre parenthèses et entre guillemets, nous allons saisir l'argument scripting.dictionary. Nous fermons les guillemets et la parenthèse et maintenant notre variable dictionnaire sera bien gérée comme un dictionnaire. Ensuite, nous allons fixer l'index de couleur de base en affectant à couleurIndex la valeur de 3. En effet, chaque couleur dans Excel a un index et nous démarrons ici à 3 pour éviter les couleurs de base qui sont les valeurs 1 et 2 qui correspondent au blanc et au noir. Ensuite, couleurIndex sera incrémentée automatiquement à chaque fois que nous allons affecter une couleur afin de pouvoir passer à la suivante. Maintenant, nous allons encore une fois passer à la ligne et nous allons pouvoir créer une première boucle afin de parcourir chaque cellule de la sélection pour pouvoir compter les occurrences de chaque valeur présente. Pour mettre en place une boucle, nous utilisons la syntaxe forEach qui est une structure de boucle en VBA. Cette syntaxe signifie que nous allons parcourir chaque élément d'une collection et ici la collection est la plage des cellules sélectionnées. Donc forEach, c, in, selection qui permet donc d'affecter à chaque passage dans la boucle la cellule qu'on a en cours d'analyse à la variable c qui représente donc une cellule individuelle à l'intérieur de cette sélection. Pour fermer notre boucle, nous saisissons ensuite l'instruction next. Nous nous plaçons maintenant à l'intérieur de cette boucle et l'objectif va donc être qu'à la fin de celle-ci notre dictionnaire contienne chaque valeur unique rentrée dans sélection avec le nombre total de fois où ces valeurs uniques apparaissent. Ensuite, nous allons vérifier si la cellule c n'est pas vide avant d'effectuer toute autre action. Pour cela, nous allons utiliser la fonction VBAIsEmpty qui renvoie à true si la cellule est vide et false si elle contient une valeur. Donc if isEmpty de c et donc si cette valeur est égale à false, c'est-à-dire que la cellule n'est pas vide et donc qu'elle contient quelque chose, alors nous allons pouvoir mettre en place notre traitement. Nous fermons le if avec un endif et nous nous plaçons à l'intérieur de celui-ci. Maintenant, nous allons vérifier si la couleur contenue dans la cellule c existe déjà dans notre dictionnaire et pour cela, il suffit d'utiliser la méthode existe de l'objet dictionnaire. En effet, ici, le dictionnaire est comme une liste où chaque valeur de cellule est associée à une clé unique, ça c'est ce que nous avons vu tout à l'heure, et cette méthode existe nous renverra à true si la clé est déjà présente dans le dictionnaire et elle nous renverra à false dans le cas contraire. Donc if dictionary.exists c c'est-à-dire que nous cherchons la valeur de c dans notre dictionnaire, si ce résultat est égal à true, alors la valeur existe bien dans le dictionnaire et donc dans ce cas, nous allons pouvoir incrémenter la valeur de celui-ci de plus 1. Pour cela, nous allons rappeler cette entrée dans le dictionnaire, donc dictionnaire avec pour clé c est égal à sa propre valeur, donc encore une fois dictionnaire de c auquel nous ajoutons 1. En revanche, si nous n'avons pas trouvé la clé dans notre dictionnaire, alors il s'agit de la première fois que nous voyons la valeur de la cellule. Et donc dans ce cas, nous allons tout simplement définir que la valeur de c dans notre dictionnaire est égale à 1. Donc ensuite, nous pouvons fermer le if avec un if. Et voilà, donc cette première boucle est terminée et nous allons maintenant nous retrouver à cet endroit du code avec notre dictionnaire bien alimenté avec toutes les valeurs que nous retrouvons dans la sélection et un décompte du nombre de fois que celle-ci apparaît. Nous allons pouvoir mettre ensuite une seconde boucle qui va permettre d'appliquer une couleur unique à chaque valeur qui apparaît au moins deux fois dans la sélection, en mettant en place la même structure de boucle que celle que nous avons mise en place juste avant, c'est-à-dire avec un for each. Donc for each c in selection. Nous fermons avec le next et nous nous plaçons à l'intérieur de cette boucle. Ici, nous allons vouloir regarder si la cellule c a effectivement du contenu. Donc if is empty c est égale à false, c'est-à-dire que la cellule n'est pas vide, alors dans ce cas-là, nous allons pouvoir mettre en place notre traitement. Ce traitement va tout d'abord consister à regarder si la valeur de la cellule apparaît plus d'une fois à l'intérieur de notre dictionnaire. Donc nous appelons cette valeur la dictionnaire de la valeur de c est supérieure à 1, il apparaît donc plus d'une fois. Et donc dans ce cas-là, cela veut dire que nous voulons colorer la cellule en utilisant le nombre limité de couleurs prédéfinies d'Excel représentées par les indices allant de 1 à 56. Ici, si nous voulons ajouter un contrôle supplémentaire pour nous assurer que nous ne dépassons pas de ces 56, nous pouvons ajouter une deuxième condition dans notre test if. Pour cela, nous ajoutons un end et nous regardons si la valeur de couleur index est inférieure ou égale à 56. Si effectivement, dans la sélection, il y a plus de 57 valeurs différentes, à partir de la 57ème couleur, il n'y aura plus de mise en évidence. Nous nous replaçons à l'intérieur de cette boucle et donc si ces deux conditions sont remplies, nous allons pouvoir passer aux étapes suivantes qui vont permettre d'appliquer une couleur de fond sur la cellule. Pour cela, nous allons créer une nouvelle clé dans notre dictionnaire que nous allons appeler couleur suivi du numéro d'index de la couleur. Si cette clé n'existe pas encore, cela signifie que nous n'avons pas assigné de couleur à ce doublon spécifique. En revanche, si elle existe déjà, alors nous n'avons pas besoin de lui attribuer une nouvelle couleur, nous utiliserons simplement celle déjà enregistrée. Si ce passage n'a pas été très clair pour vous, ne vous inquiétez pas, nous allons maintenant voir comment le mettre en place et les choses devraient être beaucoup plus claires. Comme je vous l'expliquais ici, nous allons commencer par vérifier si, dans notre dictionnaire, nous avons une clé qui existe, qui commence par couleur et sur laquelle nous allons coller la valeur de la cellule. Pour cela, nous utilisons l'esperluette. De cette manière, nous aurons une clé qui aura pour début le terme couleur suivi de la valeur de la cellule. Si cette clé n'existe pas, alors nous allons pouvoir la créer en utilisant la méthode add de dictionnaire. Pour créer une nouvelle entrée dans le dictionnaire, nous utilisons dictionnaire.add. Ici, comme juste au-dessus, nous allons définir l'index qui est égal à couleur, esperluette et C et nous allons affecter à cette clé la couleur en cours d'analyse, c'est-à-dire couleur index qui, par défaut, sera égale à 3 et lorsque nous aurons utilisé cette couleur, nous pourrons passer à la suivante. Couleur index est égale à couleur index, donc nous récupérons sa propre valeur et nous ajoutons plus 1 pour passer à la couleur suivante. Cela étant fait, il ne reste plus qu'à appliquer la couleur à la cellule C en accédant pour cela au dictionnaire avec la clé de la couleur qui va permettre de récupérer l'indice que nous venons d'attribuer à l'instant. En assignant cet indice à C.interior.colorIndex, nous allons appliquer cette couleur de fond à la cellule. Il faut savoir qu'il existe plusieurs méthodes pour pouvoir modifier la couleur de la cellule et l'utilisation de colorIndex est une manière d'appliquer une couleur de fond spécifique à une cellule en utilisant un indice de couleur standard Excel. Nous allons modifier la couleur de fond de la cellule, donc C.interior.colorIndex est égale à couleur index. Maintenant, la boucle va se poursuivre jusqu'à ce que toutes les cellules aient été analysées et si nécessaire qu'elles aient également été colorées. A la fin de la macro, nous libérons le dictionnaire afin de pouvoir éviter tout de suite de mémoire. Nous définissons que dictionnaire est égale à nos signes. Celui-ci est vidé et purgé de la mémoire. Maintenant, notre macro est prête. Si jamais vous n'avez pas bien compris comment la mettre en place, vous pourrez quand même l'utiliser en téléchargeant tout simplement le fichier en suivant le lien disponible dans la description de la vidéo. Et ensuite, il vous suffira d'effectuer un copier-coller de cette macro. Alors, juste deux petites corrections avant de pouvoir lancer la macro. Ici, pour créer un objet, nous utilisons le createJect avec un C. Et ici, j'avais bien dit tout à l'heure scripting, il n'y a pas de O. Ah oui, et ici, nous allons utiliser la même méthode que celle que nous avons mise en place juste ici avec le dictionnaire.add. Sinon, étant donné que l'entrée n'existe pas dans le dictionnaire, nous n'allons pas pouvoir lui affecter la valeur de 1. Donc add C avec pour valeur 1. Cela étant fait, nous pouvons revenir dans notre feuille de calcul, sélectionner la liste des tâches et cliquer sur le bouton distinguer les valeurs pour qu'automatiquement tous les groupes d'une même valeur soient colorés de la même couleur. Nous allons faire pareil pour les opérateurs. Nous sélectionnons la colonne opérateur et lorsque nous cliquons sur distinguer les valeurs, nous voyons bien que en rouge, nous avons toutes les tâches affectées Comme d'habitude, vous pouvez télécharger le support de secours en suivant le lien que vous trouverez dans la description de la vidéo. Si la vidéo vous a plu, n'oubliez pas de la liker et de la partager à vos collègues et amis sur l'ensemble de vos réseaux sociaux. Et vous pouvez également me laisser vos impressions dans l'espace des commentaires. Je lirai chacun d'entre eux et y répondrai avec plaisir. Evidemment, si ce n'est pas encore fait, abonnez-vous à la chaîne pour ne manquer aucune astuce. C'est également très important pour m'aider à gagner en visibilité afin de pouvoir développer la chaîne et continuer de vous proposer des tas de nouvelles vidéos. Merci d'avoir suivi cette vidéo et à très bientôt pour un prochain tuto.