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
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.
UNIQUE vous permet également d'extraire les valeurs présentes une seule fois dans une liste de valeurs.
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
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.
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))
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)
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 🙂
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,
13/12/2021 @ 16:32
Utilisez la fonction FILTRE
14/12/2021 @ 09:21
Je vous remercie, cela marche parfaitement !
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
17/11/2021 @ 11:15
La fonction NB.SI.ENS (tout simplement)
17/11/2021 @ 12:44
Une découverte pour moi ! Merci beaucoup, je teste cela tout de suite !
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 ?
20/11/2021 @ 14:25
C'est possible mais pas avec une fonction. Par macro ou Power Query c'est possible
12/08/2022 @ 13:54
Il faut utiliser la fonction UNIQUE ("tableau";Faux;Faux)
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
24/08/2021 @ 17:11
Il faut ajouter la fonction TRIER
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.
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
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
03/05/2021 @ 19:07
Oui ! Avec cet article vous trouverez la réponse. Vous aimez ? Partager 😉
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
20/03/2021 @ 11:01
Tout simplement avec la fonction NB.SI.ENS
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......
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
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
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
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
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.
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 ?
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