Extraire sans doublon avec formule

Temps de lecture : 2 minutes

Extraire sans doublon est une opération très commune dans Excel. Mais cette manipulation peut être automatisée au moyen d'une formule.

Extraire sans doublon avec formule

Dans Excel, vous pouvez extraire une liste de données sans doublon en utilisant l'outil Données>Supprimer les doublons

Menu Extraction Sans Doublon

Seulement, si votre liste initiale de données est remise à jour régulièrement, vous devez refaire, encore et encore ce travail. Et pour cela, il y a 2 méthodes :

  • Une formule matricielle "ancienne technique".
  • Une nouvelle formule présente dans Microsoft 365 ou Excel Online.

Fonction UNIQUE

Si vous travaillez avec la version de Microsoft 365 ou la version gratuite Excel Online, vous avez la fonction UNIQUE.

Cette fonction, très simple à écrire, vous permet d'extraire dynamiquement une liste sans doublon.

Fonction UNIQUE

UNIQUE vous permet également d'extraire les valeurs présentes une seule fois dans une liste de valeurs.

Extraire les elements presents quune seule fois

Formule pour extraire sans doublon (ancienne méthode)

Maintenant, si vous n'avez pas Microsoft 365, vous n'avez pas les fonctions matricielles dynamiques 😕. Pour autant, vous pouvez extraire une liste de données pas formule mais la fonction est complexe.

La solution ici est l'œuvre du maître incontesté en matière de formule matricielle : Mike "ExcelIsFun" Girvin. La formule pour extraire toutes les valeurs uniques d'une liste est la suivante :

=INDEX(Ma_Colonne;PETITE.VALEUR(SI(FREQUENCE(SI(Ma_Colonne<>"";EQUIV(Ma_Colonne;Ma_Colonne;0)); LIGNE(Ma_Colonne)-LIGNE($B$2)+1);LIGNE(Ma_Colonne)-LIGNE($B$2)+1);LIGNES(I$2:I2)))

Pour valider une formule matricielle vous devez appuyer simultanément sur les touches Ctrl + Shift + Entrée

Extraction sans doublon avec une formule matricielle

Explication des paramètres de la fonction

  • Ma_Colonne par la plage de cellule qui contient les données que vous voulez extraire.
  • B2 par la première cellule de votre colonne où il y a les doublons.
  • I2 par la cellule dans laquelle vous voulez voir le résultat (normalement écrire la référence de la cellule  du résultat dans la formule de calcul de cette même formule entraîne une référence circulaire mais pas dans le cas d'une formule matricielle).

Tout le cœur de la formule, c'est la partie PETITE.VALEUR.

Cette partie récupère la position de la prochaine valeur différente des précédentes. Et comme cette formule est incluse dans la fonction INDEX, nous ramenons toutes les valeurs uniques de notre colonne.

Vous trouverez des informations complémentaires sur filtrer des valeurs uniques ou supprimer des doublons sur le site de Microsoft.

28 Comments

  1. MARY
    07/07/2022 @ 12:39

    Bonjour,
    Beau tuto,

    La fonction matricielle ne marche pas dans mon exemple.
    Notamment LIGNES(C$25;C25) ne rend que des 1, et en conséquence je n'ai que le premier unique, non vide; le reste de la fonction fonctionnant à peu près comme une matrice NB.SI.ENS.

    Après pas mal de recherche pour comprendre le fonctionnement de la formule, j'ai corrigé par :
    =INDEX(Ma_Colonne;PETITE.VALEUR(SI(FREQUENCE(SI(Ma_Colonne"";EQUIV(Ma_Colonne;Ma_Colonne;0));LIGNE(Ma_Colonne)-LIGNE($D$25)+1);LIGNE(Ma_Colonne)-LIGNE($D$25)+1);LIGNE(Ma_Colonne)-LIGNE($D25)+1))

    Reply

  2. MARY
    07/07/2022 @ 10:59

    Bonjour,
    Très joli tuto.
    En l'absence d'excel 365, j'ai essayé la formule matricielle.
    =INDEX(Ma_Colonne;PETITE.VALEUR(SI(FREQUENCE(SI(Ma_Colonne"";EQUIV(Ma_Colonne;Ma_Colonne;0)); LIGNE(Ma_Colonne)-LIGNE($D$10)+1);LIGNE(Ma_Colonne)-LIGNE($D$10)+1);LIGNES(C$10:C10)))
    C10 est le début de la matrice.
    D10 le début de la colonne à tester.
    Ma_Colonne définit comme $D$10:$D$25

    Le résultat est surprenant. C10:C25 prend comme valeur la 1ere valeur non vide présente en D10:D25, sans passer à la suivante.

    (pour les autres utilisateurs, le message de manque de ressources arrive lorsqu' on indique une colonne complète au lieu d'une plage sur la Ma_Colonne)

    Reply

  3. TeDy
    16/04/2022 @ 17:41

    Bonjour
    Je cherche à utiliser les fonctions petite.valeur et grande.valeur comme mise en forme conditionnelle dans un TCD avec des nombre pour mettre en valeur le plus grand nombre, puis le 2ème plus grand nombre, et enfin le plus petit nombre ...

    Le problème c'est les doublons ... et comme le tableau évolue souvent je ne peux mettre en place des rang ...

    Avez-vous une solution svp ?

    D'avance, merci :)

    Reply

  4. Miryam
    13/12/2021 @ 15:42

    Bonjour,

    Je vous remercie pour votre article, il est très intéressant.

    Cependant, je voulais savoir s'il y a une possibilité de trouver une valeur en se basant sur un seul critère. En gros chopper la même information sans avoir un doublant.

    Dans mon exemple j'ai des commandes qui vont partir au même heure, et je veux chercher les commandes selon l'heure sans que l'Excel me redouble la commande.

    Commandes Heure
    A 13h
    B 13h
    C 13h
    D 13h

    Merci d'avance,
    Cordialement,

    Reply

    • Frédéric LE GUEN
      13/12/2021 @ 16:32

      Utilisez la fonction FILTRE

      Reply

      • Miryam
        14/12/2021 @ 09:21

        Je vous remercie, cela marche parfaitement !

  5. Deprez
    17/11/2021 @ 10:31

    Bonjour,
    J'ai une liste de produits différents qui apparaissent plusieurs fois et j'aimerais pouvoir obtenir le nombre de produits différents qui sont référencés.

    Par exemple :
    - pomme
    - poire
    - pomme
    - pomme
    - ananas

    Quelle est la formule qui me permet de savoir combien de référence fruits il y a (en l'occurence 3) ?

    Merci par avance !

    Caroline

    Reply

    • Frédéric LE GUEN
      17/11/2021 @ 11:15

      La fonction NB.SI.ENS (tout simplement)

      Reply

      • Deprez
        17/11/2021 @ 12:44

        Une découverte pour moi ! Merci beaucoup, je teste cela tout de suite !

      • Deprez
        17/11/2021 @ 13:04

        Je pense qu'il y a une chose qui m'échappe, ce que je cherche, c'est à faire apparaître le nom de fruits différents (3 = pomme / ananas / poire) indépendamment du nombre de fois où ils apparaissent (pommes = 3, poire = 1 ; ananas = 1).
        Est-ce qu'une telle chose est possible ?

      • Frédéric LE GUEN
        20/11/2021 @ 14:25

        C'est possible mais pas avec une fonction. Par macro ou Power Query c'est possible

    • kevin
      12/08/2022 @ 13:54

      Il faut utiliser la fonction UNIQUE ("tableau";Faux;Faux)

      Reply

  6. Alan
    24/08/2021 @ 16:29

    Bonjour,
    Est il possible d'avoir les résultats triés alphabétiquement (ou au moins dans l'ordre croissant) avec la méthode matricielle ?
    Merci d'avance

    Reply

    • Frédéric LE GUEN
      24/08/2021 @ 17:11

      Il faut ajouter la fonction TRIER

      Reply

      • Alan
        25/08/2021 @ 08:02

        Apres recherche sur votre site, j'y avais pensé, mais je n'ai pas cette fonction sur ma version d'excel... (Office Pro Plus 2016)
        j'ai réussi a contourner le problème en passant par un set de données déjà triées, mais je serais tout de même intéressé ( pour le futur) de savoir si cela est possible sans les fonctions apportées par Office 365.
        Merci encore.

      • Frédéric LE GUEN
        25/08/2021 @ 10:26

        Tous les développements sont fait pour Microsoft 365. Sans vouloir changer vos versions actuelles, le plus simple serait de faire une migration de version. Sinon, avec Excel Online, vous avez toutes les dernières fonctions

  7. Nicolas
    03/05/2021 @ 18:14

    Bonjour,
    Avez-vous une solution pour trouver les valeurs uniques, dans les cellules, mais pour un tableau de plusieurs lignes et plusieurs colonnes ?
    Merci

    Reply

    • Frédéric LE GUEN
      03/05/2021 @ 19:07

      Oui ! Avec cet article vous trouverez la réponse. Vous aimez ? Partager ;)

      Reply

  8. Meca
    20/03/2021 @ 10:30

    Bonjour,
    Quelle formule pour trouver, combien de fois un nombre ou un chiffre (doublons) est utilisé dans un tableau numérique sous Excel?
    je vous remercie d'avance pour votre réponse

    Reply

  9. Gilliand
    10/09/2020 @ 17:46

    Bonsoir,
    j'ai un fichier excel contenant deux feuilles de données client. Je voudrais savoir si les données de ma feuille 1 se retrouvent dans ma feuille 2. Tout en sachant que les données inscrites ne sont pas toujours les mêmes.
    Ex. : feuille 1 -> café, restaurant Le Bijou, Pampelune
    feuille 2 -> resto Bijou, La Pampelune Nord
    Comment faire dès lors pour ne pas devoir faire une recherche manuelle ligne par ligne. Avec la fonction recherchev et estan et si, je n'y arrive pas.....Un petit coup de pouce......

    Reply

    • Frédéric LE GUEN
      10/09/2020 @ 18:20

      Des données propres, c'est la clé de la réussite dans Excel.
      Il faudrait commencer par "nettoyer" les cellules en utilisant le remplissage instantané ou utiliser l'outil de recherche approchante de Power Query

      Reply

  10. Claire
    12/10/2018 @ 08:41

    Merci beaucoup pour cette formule matricielle. Vos instructions sont top. J'adore et pourtant je ne sais pas faire ce type de fonction. Merci

    Reply

  11. yassine
    07/03/2018 @ 21:36

    bonjour,
    est ce que cette fonction est utile seulement pour les caractères ou même les numéros.
    pi: j'ai testé cette fonction mais malheureusement pas de succès

    Merci

    Reply

  12. Lucas
    14/02/2018 @ 22:44

    Bonjour,
    J’ai appliqué la formule comme expliqué mais sans succès. Excel me met une alerte: Excel à manqué de ressources lors de l’an tentative de calcul d’une ou plusieurs formules. Ces formules n’ont pas peu être évaluées.

    Je travaille avec Excel version 15.32 sur Mac

    Étant la dernière formule que j’ai ajouté, ce ne peut être que celle là.

    Avez-vous une explication ?

    Merci d’avance

    Reply

    • TAHIRY
      30/06/2021 @ 15:08

      Bonjour,
      J'ai essayé aussi la formule mais ça ne fonctionne pas et j'ai eu le même message. je ne sais pas s'il y a des manipulations que j'ai raté ou des choses comme ca.

      Reply

      • Frédéric LE GUEN
        30/06/2021 @ 15:53

        Heeeuuuu, c'est impossible de vous apporter une réponse sans voir le contexte (en clair, la construction du classeur)
        Mais la formule UNIQUE ne fonctionne pas sur votre version ?

  13. bimbo
    04/03/2016 @ 17:52

    Bonjour, svp y a t-il une fonction qui nous permet de visualisé les doublons et de les transféré dans une nouvel feuille
    Merci

    Reply

Laisser un commentaire

Votre adresse e-mail ne sera pas publiée. Les champs obligatoires sont indiqués avec *

Ce site utilise Akismet pour réduire les indésirables. En savoir plus sur comment les données de vos commentaires sont utilisées.

MICROSOFT MVP

RECHERCHE D’EMPLOI AVEC