Comment trouver des valeurs uniques dans Excel avec une formule ?

Trouver une formule Excel unique pour extraire des valeurs uniques

L'identification et l'extraction de valeurs uniques à partir d'une liste de données sont des opérations courantes dans Excel. Plusieurs méthodes existent, allant des filtres avancés aux formules complexes. Le choix de la méthode dépend de la complexité de vos données et de vos besoins spécifiques. Voici quelques approches pour trouver la formule Excel unique qui vous convient.

Méthode 1: Utiliser la fonction UNIQUE (Excel 365 et versions ultérieures)

La fonction UNIQUE est la méthode la plus simple et la plus directe pour extraire des valeurs uniques. Elle est disponible dans Excel 365 et les versions ultérieures.

Syntaxe:

=UNIQUE(tableau;[par_colonne];[apparaît_une_fois])

• tableau: La plage de cellules contenant les données dont vous voulez extraire les valeurs uniques.
• [par_colonne]: (Facultatif) Indique si la comparaison doit se faire par colonne (VRAI) ou par ligne (FAUX ou omis). Généralement, on omet cet argument pour comparer par ligne.
• [apparaît_une_fois]: (Facultatif) Indique si vous voulez extraire les valeurs qui apparaissent une seule fois (VRAI) ou toutes les valeurs uniques (FAUX ou omis).

Exemple:

Supposons que vous ayez une liste de noms dans la plage A1:A10. Pour extraire les noms uniques, entrez la formule suivante dans une cellule vide :

=UNIQUE(A1:A10)

Excel renverra une liste de toutes les valeurs uniques présentes dans la plage A1:A10.

Capture d'écran:

(Description textuelle: Une capture d'écran montrant une colonne A contenant une liste de noms avec des doublons. La formule =UNIQUE(A1:A10) est entrée dans la cellule C1 et affiche une liste des noms uniques dans les cellules C1:C5.)

Méthode 2: Utiliser la combinaison INDEX, AGGREGATE, SI, et COUNTIF (versions antérieures à Excel 365)

Si vous utilisez une version d'Excel antérieure à Excel 365, vous pouvez utiliser une combinaison de fonctions pour obtenir le même résultat. Cette méthode est plus complexe, mais elle est compatible avec les anciennes versions d'Excel.

Étapes:

1. Déterminer la plage de données: Identifiez la plage de cellules contenant les données dont vous voulez extraire les valeurs uniques (par exemple, A1:A10).

2. Créer une colonne auxiliaire (facultative): Cette étape est facultative, mais elle peut simplifier la formule. Dans une colonne vide (par exemple, B1:B10), entrez la formule suivante dans la cellule B1 et étirez-la vers le bas :

   =COUNTIF($A$1:A1,A1)

   Cette formule compte le nombre d'occurrences de chaque valeur dans la plage A1:A10 jusqu'à la ligne actuelle. La première occurrence aura une valeur de 1, la deuxième de 2, etc.

3. Utiliser la formule complexe: Dans une cellule vide où vous voulez que la liste des valeurs uniques commence (par exemple, C1), entrez la formule suivante :

   =IFERROR(INDEX($A$1:$A$10,AGGREGATE(15,6,ROW($A$1:$A$10)/(B$1:B$10=1),ROW(A1))),"")

   • Si vous n'avez pas utilisé la colonne auxiliaire, remplacez B$1:B$10=1 par COUNTIF($A$1:$A$10,$A$1:$A$10)=1.
   • AGGREGATE(15,6,...) renvoie le k-ième plus petit nombre d'une plage, en ignorant les erreurs.
   • ROW($A$1:$A$10)/(B$1:B$10=1) crée un tableau de nombres de ligne où seules les lignes contenant la première occurrence de chaque valeur sont conservées. Les autres lignes renvoient une erreur.
   • INDEX($A$1:$A$10,...) renvoie la valeur de la cellule à la ligne spécifiée par AGGREGATE.
   • IFERROR(...,"") gère les erreurs qui se produisent lorsque toutes les valeurs uniques ont été extraites, en affichant une cellule vide.

4. Étirer la formule vers le bas: Étirez la formule de la cellule C1 vers le bas pour afficher toutes les valeurs uniques. Le nombre de lignes que vous étirez doit être supérieur au nombre de valeurs uniques attendues.

Capture d'écran:

(Description textuelle: Une capture d'écran montrant une colonne A avec une liste de noms avec des doublons, une colonne B avec la formule =COUNTIF($A$1:A1,A1) calculant le nombre d'occurrences, et une colonne C avec la formule =IFERROR(INDEX($A$1:$A$10,AGGREGATE(15,6,ROW($A$1:$A$10)/(B$1:B$10=1),ROW(A1))),"") affichant la liste des noms uniques.)

Méthode 3: Utiliser les filtres avancés

Les filtres avancés sont une fonctionnalité intégrée d'Excel qui permet de filtrer une liste de données en fonction de critères spécifiques, y compris l'extraction de valeurs uniques.

Étapes:

1. Sélectionner la plage de données: Sélectionnez la plage de cellules contenant les données dont vous voulez extraire les valeurs uniques (par exemple, A1:A10).
2. Ouvrir la boîte de dialogue Filtre avancé: Allez dans l'onglet Données et cliquez sur Avancé dans le groupe Trier et filtrer.
3. Configurer le filtre avancé:
   • Action: Choisissez "Copier vers un autre emplacement".
   • Plage de la liste: Vérifiez que la plage de données sélectionnée est correcte (par exemple, $A$1:$A$10).
   • Plage de critères: Laissez ce champ vide (nous ne filtrons pas en fonction de critères spécifiques, mais uniquement pour les valeurs uniques).
   • Copier vers: Sélectionnez une cellule vide où vous voulez que la liste des valeurs uniques commence (par exemple, $C$1).
   • Cochez la case: "Extraction sans doublons".
4. Cliquez sur OK: Excel copiera les valeurs uniques de la plage de données vers l'emplacement spécifié.

Capture d'écran:

(Description textuelle: Une capture d'écran de la boîte de dialogue Filtre avancé avec les paramètres configurés pour copier les valeurs uniques de la colonne A vers la colonne C.)

Méthode 4: Utiliser Power Query (Get & Transform Data)

Power Query est un outil puissant intégré à Excel qui permet de transformer et de charger des données à partir de diverses sources. Il peut également être utilisé pour extraire des valeurs uniques.

Étapes:

1. Sélectionner la plage de données: Sélectionnez la plage de cellules contenant les données dont vous voulez extraire les valeurs uniques (par exemple, A1:A10).
2. Créer une requête Power Query: Allez dans l'onglet Données et cliquez sur Du tableau/plage dans le groupe Obtenir et transformer des données.
3. Ouvrir l'éditeur Power Query: L'éditeur Power Query s'ouvrira.
4. Supprimer les doublons: Sélectionnez la colonne contenant les données et cliquez sur Supprimer les doublons dans l'onglet Accueil.
5. Charger les données dans Excel: Cliquez sur Fermer et charger dans l'onglet Accueil pour charger les valeurs uniques dans une nouvelle feuille de calcul Excel.

Capture d'écran:

(Description textuelle: Une capture d'écran de l'éditeur Power Query montrant une colonne de données sélectionnée et le bouton "Supprimer les doublons" mis en évidence.)

Conseils et astuces pour utiliser les formules Excel pour les valeurs uniques

• Comprendre vos données: Avant de choisir une méthode, analysez vos données. Si vous utilisez Excel 365, la fonction UNIQUE est la solution la plus simple. Sinon, les filtres avancés ou la combinaison de formules peuvent être utilisés.
• Utiliser des noms de plage: Pour faciliter la lecture et la maintenance de vos formules, utilisez des noms de plage au lieu de références de cellules directes. Par exemple, au lieu d'utiliser A1:A10, vous pouvez définir un nom de plage appelé "ListeNoms" et utiliser UNIQUE(ListeNoms).
• Gérer les erreurs: Les formules complexes peuvent générer des erreurs. Utilisez la fonction IFERROR pour gérer ces erreurs et afficher des messages plus conviviaux.
• Tester vos formules: Avant d'appliquer une formule à un ensemble de données volumineux, testez-la sur un petit échantillon pour vous assurer qu'elle fonctionne correctement.
• Documenter vos formules: Ajoutez des commentaires à vos formules pour expliquer leur fonctionnement. Cela facilitera la compréhension et la maintenance future.

Erreurs courantes à éviter

• Références de cellules incorrectes: Vérifiez attentivement les références de cellules dans vos formules. Une erreur de référence peut entraîner des résultats incorrects.
• Oublier de figer les références: Lorsque vous étirez une formule vers le bas, assurez-vous de figer les références de cellules appropriées en utilisant le signe $ (par exemple, $A$1).
• Utiliser la mauvaise méthode: Choisissez la méthode appropriée en fonction de votre version d'Excel et de la complexité de vos données.
• Ignorer les erreurs: Ne pas gérer les erreurs peut entraîner des résultats inattendus. Utilisez IFERROR pour les gérer.

Conclusion

Trouver une formule Excel unique pour extraire des valeurs uniques est une compétence essentielle pour tout utilisateur d'Excel. Que vous utilisiez la fonction UNIQUE (si vous avez Excel 365 ou une version ultérieure), la combinaison de fonctions INDEX, AGGREGATE, SI, et COUNTIF, les filtres avancés ou Power Query, vous avez maintenant les outils nécessaires pour manipuler vos données avec efficacité. N'oubliez pas de comprendre vos données, de tester vos formules et de gérer les erreurs pour obtenir les meilleurs résultats. En maîtrisant ces techniques, vous gagnerez du temps et améliorerez la qualité de vos analyses.