Extraire sans doublon avec formule

Dernière mise à jour le 24/03/2024
Temps de lecture : 3 minutes

Extraire sans doublon est une opération très commune dans Excel. Cet article va vous présenter deux solutions pour obtenir une liste sans doublon à partir de 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. Pour vous éviter cela, il y a deux formules :

  • La nouvelle fonction UNIQUE présente dans Microsoft 365, Excel 2019 et Excel Online.
  • Une formule matricielle très complexe pour les versions autres d'Excel.

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.

Articles liés

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 2024

Newsletter

1 fois par mois :
Astuces et quiz

    Nous ne vous enverrons pas de spam. Vous pouvez vous désabonner à tout moment.

    Extraire sans doublon avec formule

    Reading time: 3 minutes
    Dernière mise à jour le 24/03/2024

    Extraire sans doublon est une opération très commune dans Excel. Cet article va vous présenter deux solutions pour obtenir une liste sans doublon à partir de 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. Pour vous éviter cela, il y a deux formules :

    • La nouvelle fonction UNIQUE présente dans Microsoft 365, Excel 2019 et Excel Online.
    • Une formule matricielle très complexe pour les versions autres d'Excel.

    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.

    Articles liés

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

    Newsletter

    1 fois par mois :
    Astuces et quiz

      Nous ne vous enverrons pas de spam. Vous pouvez vous désabonner à tout moment.

      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.