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.
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))
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)
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 🙂
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,
Frédéric LE GUEN
13/12/2021 @ 16:32
Utilisez la fonction FILTRE
Miryam
14/12/2021 @ 09:21
Je vous remercie, cela marche parfaitement !
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
Frédéric LE GUEN
17/11/2021 @ 11:15
La fonction NB.SI.ENS (tout simplement)
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)
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
Frédéric LE GUEN
24/08/2021 @ 17:11
Il faut ajouter la fonction TRIER
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
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
Frédéric LE GUEN
03/05/2021 @ 19:07
Oui ! Avec cet article vous trouverez la réponse. Vous aimez ? Partager 😉
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
Frédéric LE GUEN
20/03/2021 @ 11:01
Tout simplement avec la fonction NB.SI.ENS
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......
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
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
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
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
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.
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 ?
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