Comment compter le nombre d'occurrences dans Excel

Le tutoriel explique comment rechercher des doublons dans Excel. Vous apprendrez quelques formules pour identifier les valeurs en double avec ou sans premières occurrences. Vous apprendrez également à compter individuellement les occurrences de chaque enregistrement en double et à trouver le nombre total de doublons dans une colonne, etc.

Lorsque vous travaillez avec une grande feuille de calcul Excel ou que vous consolidez plusieurs petites feuilles de calcul, vous pouvez y trouver de nombreuses lignes en double. J'aimerais partager quelques méthodes rapides et efficaces pour identifier les doublons dans une liste unique. Ces solutions fonctionnent dans toutes les versions d'Excel 2016, Excel 2013, Excel 2010 et versions antérieures.

Table des matières

Comment trouver les doublons dans Excel

Comment trouver les enregistrements en double, y compris les 1ères occurrences

Comment rechercher des doublons dans Excel sans 1ère occurrence

Comment trouver des doublons sensibles à la casse dans Excel

Comment trouver des lignes en double dans Excel

Comment compter les doublons dans Excel

Compter les occurrences de chaque enregistrement dupliqué individuellement

Compter le nombre total de doublons dans une colonne

Comment supprimer les doublons - Éliminer les lignes répétées

Comment trouver les doublons dans Excel

Le moyen le plus simple de détecter les doublons dans Excel consiste à utiliser la fonction NB.SI. Selon que vous souhaitiez rechercher des valeurs en double avec ou sans premières occurrences, il y aura une légère variation dans la formule, comme illustré dans les exemples suivants.

Comment trouver les enregistrements en double, y compris les 1ères occurrences

Supposons que vous souhaitiez vérifier les doublons dans la colonne A. Celles-ci peuvent être des factures, des ID de produit, des noms ou toute autre donnée.

Voici une formule pour rechercher les doublons dans Excel, y compris les premières occurrences (où A2 est la cellule la plus haute) :

=NB.SI(A:A ;A2)> 1

Entrez la formule ci-dessus dans B2, puis sélectionnez B2 et faites glisser la poignée de recopie pour copier la formule dans d'autres cellules :

Comme vous pouvez le voir dans la capture d'écran ci-dessus, la formule retourne VRAI pour les valeurs en double et FAUX pour les valeurs uniques.

Remarque : Si vous avez besoin de rechercher des doublons dans une plage de cellules plutôt que dans une colonne entière, pensez à corriger cette plage en utilisant $. Par exemple, pour rechercher des doublons dans les cellules A2:A8, utilisez la formule =NB.SI($A$2:$A$8 ;A2)> 1.

Pour qu'une formule dupliquée renvoie quelque chose de plus significatif que les valeurs booléennes VRAI et FAUX, insérez-la dans la fonction SI et entrez les étiquettes de votre choix pour les valeurs dupliquées et uniques :

=SI(NB.SI($A$2:$A$8;A2)> 1;"Dupliqué";"Unique")

Si vous souhaitez qu'une formule Excel ne trouve que les doublons, remplacez "Unique" par une chaîne vide ("") comme celle-ci :

=SI(NB.SI($A$2:$A$8;A2)> 1;"Dupliqué";"")

La formule renverra "Dupliqué" pour les enregistrements en double et une cellule vide pour les enregistrements uniques :

Comment rechercher des doublons dans Excel sans 1ère occurrence

Si vous envisagez de filtrer ou de supprimer les doublons après les avoir trouvés, l'utilisation de la formule ci-dessus n'est pas sûre, car tous les enregistrements identiques sont marqués comme doublons. Et si vous souhaitez conserver les valeurs uniques dans votre liste, vous ne pouvez pas supprimer tous les enregistrements en double, vous devez uniquement supprimer la deuxième instance et toutes les instances suivantes.

Modifions donc notre formule de duplication Excel en utilisant des références de cellules absolues et relatives, le cas échéant :

=SI(NB.SI($A$2:$A2; $A2)>1; "Dupliqué";"")

Comme vous pouvez le voir dans la capture d'écran suivante, cette formule n'identifie pas la première occurrence de "Pommes" en double :

Comment trouver des doublons sensibles à la casse dans Excel

Dans les cas où vous devez identifier les doublons exacts, y compris la casse du texte, utilisez cette formule de tableau générique (entrée en appuyant sur Ctrl + Shift + Entrée) :

SI(SOMME ((--EXACT (plage ; cellule_supérieure)))

Au cœur de la formule, vous utilisez la fonction EXACT pour comparer exactement la cellule cible à chaque cellule de la plage spécifiée. Le résultat de cette opération est un tableau de VRAI (correspondance) et FAUX (non de correspondance), qui est forcé à un tableau de 1 et de 0 par l'opérateur unaire (-). Après cela, la fonction SOMME additionne les nombres et si la somme est supérieure à 1, la fonction SI signale un "dupliqué".

Pour notre échantillon de données, la formule est la suivante :

=SI(SOMME((--EXACT($A$2:$A$8 ;A2)))

Comme le montre la capture d'écran ci-dessous, les lettres minuscules et majuscules sont traitées différemment (POMMES n'est pas identifié comme un doublon) :

Comment trouver des lignes en double dans Excel

Si votre objectif est de dédupliquer une table composée de plusieurs colonnes, vous avez besoin d’une formule qui puisse vérifier chaque colonne et identifier uniquement les lignes en double absolues, c’est-à-dire les lignes dont les valeurs sont complètement égales dans toutes les colonnes.

Considérons l'exemple suivant. Supposons que vous ayez des numéros d'ordre dans la colonne A, des dates dans la colonne B et des articles commandés dans la colonne C, et que vous souhaitez rechercher des lignes en double avec les mêmes numéros d'ordre, date et article. Pour cela, nous allons créer une formule dupliquée basée sur la fonction NB.SI.ENS qui permet de vérifier plusieurs critères à la fois.

Pour rechercher des lignes en double avec une 1ère occurrence, utilisez cette formule :

=SI(NB.SI.ENS($A$2:$A$8;A2; $B$2:$B$8;B2;$C$2:$C$8;C2)> 1; "Ligne dupliqué";"")

La capture d'écran suivante montre que la formule localise uniquement les lignes ayant des valeurs identiques dans les 3 colonnes. Par exemple, la ligne 8 a le même numéro d'ordre et la même date que les lignes 2 et 5, mais un élément différent dans la colonne C et, par conséquent, elle n'est pas marquée en tant que ligne en double :

Pour afficher les lignes en double sans 1ère occurrence, apportez quelques modifications à la formule ci-dessus :

=SI(NB.SI.ENS($A$2:$A2;A2; $B$2:$B2;B2;$C$2:$C2;C2)> 1; "Ligne dupliqué";"")

Comment compter les doublons dans Excel

Si vous souhaitez connaître le nombre exact d'enregistrements identiques dans votre feuille Excel, utilisez l'une des formules suivantes pour compter les doublons.

Compter les occurrences de chaque enregistrement dupliqué individuellement

Lorsque vous avez une colonne avec des valeurs dupliquées, vous pouvez souvent avoir besoin de savoir combien il y a de doublons pour chacune de ces valeurs.

Pour savoir combien de fois telle ou telle entrée se produit dans votre feuille de calcul Excel, utilisez une formule NB.SI simple, où A2 est le premier et A8 le dernier élément de la liste :

=NB.SI($A$2:$A$8; A2)

Comme le montre la capture d'écran suivante, la formule compte l'occurrence de chaque produit : "Pommes" apparaît 3 fois, "Bananes vertes" - 2 fois, "Bananes" et "Oranges" une seule fois.

Si vous souhaitez identifier les 1ère, 2ème, 3ème, etc. occurrences de chaque élément, utilisez la formule suivante :

=NB.SI($A$2:$A2 ;$A2)

De la même manière, vous pouvez compter le nombre de lignes dupliquées. La seule différence est que vous devrez utiliser la fonction NB.SI.ENS au lieu de NB.SI. Par exemple :

=NB.SI.ENS($A$2:$A$8 ; A2 ; $B$2:$B$8; B2)

Une fois que les valeurs en double sont comptées, vous pouvez masquer des valeurs uniques et ne visualiser que les doublons, ou inversement.

Compter le nombre total de doublons dans une colonne

Le moyen le plus simple de compter les doublons dans une colonne consiste à utiliser l'une des formules utilisées pour identifier les doublons dans Excel (avec ou sans premières occurrences). Et vous pouvez ensuite compter les valeurs en double en utilisant la formule NB.SI suivante :

=NB.SI(plage ; "dupliqué")

Où "dupliqué" est l'étiquette que vous avez utilisée dans la formule pour localiser les doublons.

Dans cet exemple, notre formule de doublons prend la forme suivante :

=NB.SI(B2:B8 ; "dupliqué")

Une autre façon de compter les valeurs en double dans Excel en utilisant une formule matricielle plus complexe. Un avantage de cette approche est qu’elle ne nécessite pas de colonne auxiliaire :

=LIGNES($A$2:$A$8) -SOMME(SI(NB.SI($A$2:$A$8 ; $A$2:$A$8)) = 1 ;1 ;0))

Comme il s’agit d’une formule matricielle, n’oubliez pas d’appuyer sur Ctrl + Shift + Entrée pour la compléter. Notez également que cette formule compte tous les enregistrements en double, y compris les premières occurrences :

Pour rechercher le nombre total de lignes en double, intégrez la fonction NB.SI.ENS à la place de NB.SI dans la formule ci-dessus et spécifiez toutes les colonnes pour lesquelles vous souhaitez rechercher des doublons. Par exemple, pour compter les lignes en double basées sur les colonnes A et B, entrez la formule suivante dans votre feuille Excel :

=LIGNES($A$2:$A$8) -SOMME(SI(NB.SI.ENS($A$2:$A$8; $A$2:$A$8; $B$2:$B$8;$B$2:$B$8) = 1;1;0))

Comment supprimer les doublons - Éliminer les lignes répétées

Bien que Microsoft Excel soit avant tout un outil de calcul, ses feuilles sont souvent utilisées comme bases de données pour suivre les stocks, établir des rapports de vente ou gérer des listes de diffusion.

Un problème courant qui se produit lorsque la base de données augmente en taille est que de nombreuses lignes en double y apparaissent. Et même si votre énorme base de données ne contient qu'une poignée d'enregistrements identiques, ces quelques doublons peuvent poser de nombreux problèmes, par exemple envoyer plusieurs copies du même document à la même personne, ou calculer plusieurs fois les mêmes nombres dans un résumé. rapport. Par conséquent, avant d’utiliser une base de données, il est judicieux de vérifier qu’elle ne contient pas de doublons, afin de ne pas perdre votre temps à répéter vos efforts.

Si vous utilisez la version récente de Microsoft Excel 2007, Excel 2010, Excel 2013 ou Excel 2016, vous avez un petit avantage, car ces versions contiennent une fonctionnalité intégrée pour rechercher et supprimer les doublons qui est appelée, sans surprise, Supprimer les doublons.

Cet outil vous permet de rechercher et de supprimer les doublons absolus (cellules ou lignes entières) ainsi que les enregistrements partiellement correspondants (lignes ayant des valeurs identiques dans une colonne ou des colonnes spécifiées). Pour ce faire, suivez les étapes ci-dessous.

Remarque : Étant donné que l'outil Supprimer les doublons supprime définitivement les enregistrements identiques, il est conseillé de copier les données d'origine avant de supprimer les lignes en double.

1. Pour commencer, sélectionnez la plage dans laquelle vous souhaitez supprimer les valeurs dupliquées. Pour sélectionner la table entière, appuyez sur Ctrl + A.
2. Avec la plage sélectionnée, allez dans l'onglet Données > groupe Outils de données, puis cliquez sur le bouton Supprimer les doublons.
3. La boîte de dialogue Supprimer les doublons s'ouvre, vous sélectionnez les colonnes pour vérifier les doublons, puis cliquez sur OK.

• Pour supprimer les lignes en double ayant des valeurs complètement égales dans toutes les colonnes, laissez les coches en regard de toutes les colonnes, comme dans la capture d'écran ci-dessous.
• Pour supprimer les doublons partiels basés sur une ou plusieurs colonnes clés, sélectionnez uniquement les colonnes pertinentes. Si votre table comporte plusieurs colonnes, le moyen le plus rapide est de cliquer sur le bouton Désélectionner tout, puis de sélectionner les colonnes pour lesquelles vous souhaitez rechercher des doublons.
• Si votre table n'a pas d'en-tête, décochez la case « Mes données ont des en-têtes » dans le coin supérieur droit de la fenêtre de dialogue, qui est généralement sélectionnée par défaut.

Terminé! Toutes les lignes en double de la plage sélectionnée sont supprimées et un message s'affiche indiquant le nombre d'entrées en double supprimées et le nombre de valeurs uniques restantes.

Remarque : La fonction Supprimer les doublons d'Excel supprime la deuxième instance et toutes les instances dupliquées suivantes, en laissant toutes les lignes uniques et les premières instances d'enregistrements identiques.