Hier soir, à l'émission de radio Computer America de Craig Crossman, Joe de Boston avait une question:
J'ai une colonne de numéros de facture. Comment puis-je utiliser Excel pour marquer les doublons?
J'ai suggéré d'utiliser des formats conditionnels et la formule COUNTIF. Voici les détails sur la façon de faire fonctionner cela.
Nous souhaitons configurer un formatage conditionnel pour toute la plage, mais il est plus facile de configurer un format conditionnel pour la première cellule de la plage, puis de copier ce format conditionnel. Dans notre cas, la cellule A1 a un en-tête de numéro de facture, je vais donc sélectionner la cellule A2 et dans le menu, sélectionnez Format> Mise en forme conditionnelle. La boîte de dialogue Mise en forme conditionnelle commence par la liste déroulante initiale indiquant «La valeur de la cellule est». Si vous touchez la flèche à côté de cela, vous pouvez choisir "La formule est".
Après avoir sélectionné "La formule est", la boîte de dialogue change d'apparence. Au lieu de boîtes pour "Entre x et y", il y a maintenant une seule boîte de formule. Cette boîte de formule est incroyablement puissante. Vous pouvez taper n'importe quelle formule que vous pouvez imaginer, à condition que cette formule soit évaluée à VRAI ou FAUX.
Dans notre cas, nous devons utiliser une formule COUNTIF. La formule à saisir dans la case est
=COUNTIF(A:A,A2)>1
En anglais, cela dit: "parcourez toute la plage de la colonne A. Comptez le nombre de cellules de cette plage qui ont la même valeur que celle de A2. (Il est vraiment important que le" A2 "de la formule pointe vers le cellule actuelle - la cellule dans laquelle vous définissez la mise en forme conditionnelle. Donc, si vos données sont dans la colonne E et que vous définissez la première mise en forme conditionnelle dans E5, la formule serait =COUNTIF(E:E,E5)>0). Ensuite, nous comparons pour voir si cela compte est> 1. Idéalement, sans doublons, le nombre sera toujours de 1 - car la cellule A2 est dans la plage - nous devrions trouver exactement une cellule dans la colonne A qui contient la même valeur que A2.
Cliquez sur le bouton Format…
Il est maintenant temps de sélectionner un format désagréable. Il y a trois onglets en haut de cette boîte de dialogue Format de cellule. L'onglet Police est généralement le premier, vous pouvez donc sélectionner une police Gras et rouge, mais j'aime quelque chose de plus odieux. Je clique généralement sur l'onglet Motifs et je choisis soit le rouge vif soit le jaune vif. Choisissez la couleur, puis cliquez sur OK pour fermer la boîte de dialogue Format de cellule.
Vous verrez le format sélectionné dans la case "Aperçu du format à utiliser". Cliquez sur OK pour fermer la boîte de dialogue Mise en forme conditionnelle…
… Et rien ne se passe. Sensationnel. Si c'est la première fois que vous configurez une mise en forme conditionnelle, ce serait vraiment bien d'avoir des commentaires ici sur le fait que cela a fonctionné. Mais, à moins que vous n'ayez la chance que le 1098 dans la cellule A2 soit un duplicata d'une autre cellule, la condition n'est pas vraie et il semble que rien ne s'est passé.
Vous devez copier la mise en forme conditionnelle de A2 vers les autres cellules de votre plage. Avec l'appui du curseur dans A2, faites Édition> Copier. Appuyez sur Ctrl + barre d'espace pour sélectionner la colonne entière. Faites Edition> Collage spécial. Dans la boîte de dialogue Collage spécial, cliquez sur Formats. Cliquez sur OK.
Cela copiera la mise en forme conditionnelle dans toutes les cellules de la colonne. Maintenant - enfin - vous voyez des cellules avec le formatage rouge, indiquant que vous avez un doublon.
Il est informatif d'aller à la cellule A3 et de regarder le format conditionnel après la copie. Sélectionnez A3, appuyez sur od pour faire apparaître le formatage conditionnel. La formule de la zone Formule est modifiée pour compter le nombre de fois où A3 apparaît dans la colonne A: A.
Remarques
Dans la question de Joe, il n'avait que 1700 factures dans la fourchette. J'ai configuré 65536 cellules avec une mise en forme conditionnelle et chaque cellule compare la cellule actuelle à 65536 autres cellules. Dans Excel 2005 - avec plus de lignes - le problème sera encore pire. Techniquement, la formule de la première étape aurait pu être:=COUNTIF($A$2:$A$1751,A2)>1
De plus, lors de la copie du format conditionnel dans la colonne entière, vous auriez pu sélectionner uniquement les lignes contenant des données avant d'effectuer le collage des formats spéciaux.
Plus
L'autre problème que j'ai décrit après la question est que vous ne pouvez vraiment pas trier une colonne sur la base d'un format conditionnel. Si vous devez trier ces données afin que les doublons se trouvent dans une zone, procédez comme suit. Tout d'abord, ajoutez un titre à B1 appelé "Dupliquer?". Tapez cette formule dans B2: =COUNTIF(A:A,A2)>1.
Avec le pointeur de cellule en B2, cliquez sur la poignée de remplissage automatique (le petit carré dans le coin inférieur droit de la cellule) pour copier la formule tout le long de la plage.
Vous pouvez maintenant trier par colonne B par ordre décroissant et A par ordre croissant pour avoir les factures problématiques en haut de la fourchette.
Cette solution suppose que vous souhaitiez mettre en surbrillance LES DEUX factures en double afin que vous puissiez déterminer manuellement lesquelles supprimer ou corriger. Si vous ne voulez pas marquer la première apparition du double, vous pouvez ajuster la formule à: =COUNTIF($A$2:$A2,A2)>1. Il est important de saisir les signes dollar exactement comme indiqué. Cela examinera toutes les cellules de la cellule actuelle uniquement, à la recherche d'entrées en double.
Merci à Joe de Boston pour la question!
