Excel Fonction unique - moyen le plus rapide de trouver des valeurs uniques

Le tutoriel examine comment obtenir des valeurs uniques dans Excel en utilisant la fonction unique et les tableaux dynamiques. Vous apprendrez une formule simple pour trouver des valeurs uniques dans une colonne ou une ligne, dans plusieurs colonnes, en fonction des conditions, et bien plus encore.

Dans les versions précédentes d'Excel, l'extraction d'une liste de valeurs uniques était un défi difficile. Nous avons un article spécial qui montre comment trouver des uniques qui se produisent une seule fois, extraire tous les éléments distincts d'une liste, ignorer les blancs, etc. Chaque tâche nécessitait une utilisation combinée de plusieurs fonctions et une formule de tableau multi-lignes qui n'excellent que les gourous peuvent comprendre pleinement.

L'introduction de la fonction unique dans Excel 365 a tout changé! Ce qui était autrefois une science des fusées devient aussi facile qu'ABC. Maintenant, vous n'avez pas besoin d'être un expert de la formule pour obtenir des valeurs uniques d'une gamme, en fonction d'un ou de plusieurs critères, et d'organiser les résultats dans l'ordre alphabétique. Tout se fait avec des formules simples que tout le monde peut lire et ajuster pour vos propres besoins.

• Excel Fonction unique
  • Formule unique de base dans Excel
  • Fonction unique - Conseils et notes
• Comment trouver des valeurs uniques - Exemples de formule
  • Extraire des valeurs qui ne se produisent qu'une seule fois
  • Trouver des valeurs distinctes qui se produisent plus d'une fois
  • Trouver des valeurs uniques dans plusieurs colonnes (lignes uniques)
  • Excel Valeurs uniques triées alphabétiquement
  • Concaténer les valeurs uniques de plusieurs colonnes en une seule cellule
  • Obtenez une liste de valeurs uniques en fonction des critères
  • Valeurs uniques basées sur plusieurs critères
  • Valeurs uniques avec plusieurs ou critères
  • Trouver des valeurs uniques dans Excel en ignorant les cellules vierges
  • Trouver des valeurs uniques dans des colonnes non adjacentes
  • Trouver des valeurs uniques et gérer les erreurs
• Excel Fonction unique ne fonctionne pas

Excel Fonction unique

La fonction unique dans Excel renvoie une liste de valeurs uniques d'une plage ou d'un tableau. Il fonctionne avec n'importe quel type de données: texte, chiffres, dates, heures, etc.

La fonction est classée sous les fonctions de tableaux dynamiques. Le résultat est un réseau dynamique qui se déverse automatiquement dans les cellules voisines verticalement ou horizontalement.

La syntaxe de la fonction unique Excel est la suivante:

Unique (array, [by_col], [exactement_once])

Où:

Array (requis) - La plage ou le tableau à partir de laquelle renvoyer des valeurs uniques.

BY_COL (Facultatif) - Une valeur logique indiquant comment comparer les données:

• Vrai - compare les données entre les colonnes.
• Faux ou omis (par défaut) - compare les données entre les lignes.

Exactement_once (facultatif) - une valeur logique qui définit les valeurs considérées comme uniques:

• Vrai - renvoie des valeurs qui ne se produisent qu'une seule fois, qui est la notion de base de données d'unique.
• Faux ou omis (par défaut) - Renvoie toutes les valeurs distinctes (différentes) dans la plage ou le tableau.

Note. Actuellement, la fonction unique est uniquement disponible dans Excel pour Microsoft 365 et Excel 2021. Excel 2019, 2016 et plus tôt ne prennent pas en charge les formules de tableau dynamique, de sorte que la fonction unique n'est pas disponible dans ces versions.

Formule unique de base dans Excel

Vous trouverez ci-dessous une formule de valeurs uniques Excel dans sa forme la plus simple.

L'objectif est d'extraire une liste de noms uniques de la gamme B2: B10. Pour cela, nous entrons la formule suivante en D2:

=UNIQUE(B2:B10)

Veuillez noter que les 2e et 3e arguments sont omis parce que les défauts fonctionnent parfaitement dans notre cas - nous comparons les lignes les unes contre les autres et souhaitons retourner tous les différents noms de la gamme.

Lorsque vous appuyez sur la touche Entrée pour terminer la formule, Excel sortira le premier nom trouvé en D2 renversant les autres noms dans les cellules ci-dessous. En conséquence, vous avez toutes les valeurs uniques dans une colonne:

Dans le cas où vos données se trouvent à travers les colonnes de B2 à I2, définissez le 2 ^ème argument sur true pour comparer les colonnes les unes contre les autres:

=UNIQUE(B2:I2,TRUE)

Tapez la formule ci-dessus en B4, appuyez sur Entrée , et les résultats se déboucheront horizontalement dans les cellules vers la droite. Ainsi, vous obtiendrez les valeurs uniques dans la ligne:

Conseil. Pour trouver des valeurs uniques dans des tableaux multi-colonnes et les retourner dans une colonne ou une ligne, utilisez unique avec la fonction TOCOL ou TOROW comme indiqué dans les exemples ci-dessous:

• Extraire des valeurs uniques d'une plage multi-colonnes dans une colonne
• Tirez des valeurs uniques d'une plage multi-colonnes en une ligne

Excel Fonction unique - Conseils et notes

Unique est une nouvelle fonction et, comme d'autres fonctions dynamiques, a quelques spécificités que vous devez connaître:

• Si le tableau renvoyé par Unique est le résultat final (c'est-à-dire non transmis à une autre fonction), Excel crée dynamiquement une plage de taille appropriée et le remplit avec les résultats. La formule doit être entrée uniquement dans une seule cellule . Il est important que vous ayez suffisamment de cellules vides et / ou à droite de la cellule où vous entrez la formule, sinon une erreur #spill se produit.
• Les résultats se mettent à jour automatiquement lorsque les données source changent. Cependant, les nouvelles entrées ajoutées en dehors du tableau référencées ne sont pas incluses dans la formule, sauf si vous modifiez la référence du tableau . Si vous souhaitez que le tableau réponde automatiquement à la redimensionnement de la plage de source, convertissez la plage en une table Excel et utilisez des références structurées, ou créez une plage dynamique nommée.
• Les tableaux dynamiques entre différents fichiers Excel ne fonctionnent que lorsque les deux classeurs sont ouverts . Si le classeur source est fermé, une formule unique liée renverra un #Ref! erreur.
• Comme d'autres fonctions de tableau dynamique, unique ne peut être utilisé que dans une plage normale, pas une table. Lorsqu'il est placé dans des tables Excel, il renvoie un #spill! erreur.

Comment trouver des valeurs uniques dans Excel - Exemples de formule

Les exemples ci-dessous montrent certaines utilisations pratiques de la fonction unique dans Excel. L'idée principale est d'extraire des valeurs uniques ou de supprimer des doublons, selon votre point de vue, de la manière la plus simple possible.

Extraire des valeurs uniques qui ne se produisent qu'une seule fois

Pour obtenir une liste de valeurs qui apparaissent dans la plage spécifiée exactement une fois, définissez l'argument de 3 ^ec de TRUE unique.

Par exemple, pour extraire les noms qui figurent sur la liste des gagnants une fois, utilisez cette formule:

=UNIQUE(B2:B10,,TRUE)

Où B2: B10 est la plage de source et l'argument 2 ^nd ( by_col ) est faux ou omis car nos données sont organisées en lignes.

Trouver des valeurs distinctes qui se produisent plus d'une fois

Si vous poursuivez un objectif opposé, IE cherche à obtenir une liste de valeurs qui apparaissent dans une plage donnée plus d'une fois, utilisez la fonction unique avec Filtre et Countif:

Unique (filtre ( plage , counttif ( plage , gamme )> 1))

Par exemple, pour extraire différents noms qui se produisent dans B2: B10 plus d'une fois, vous pouvez utiliser cette formule:

=UNIQUE(FILTER(B2:B10, COUNTIF(B2:B10, B2:B10)>1))

Comment fonctionne cette formule:

Au cœur de la formule, la fonction de filtre filtre les entrées en double basées sur le nombre d'occurrences, renvoyées par la fonction Countif. Dans notre cas, le résultat de Countif est ce tableau de dénombrements:

{4;1;3;4;4;1;3;4;3}

L'opération de comparaison (> 1) modifie le tableau ci-dessus en valeurs vraies et fausses, où True représente les éléments qui apparaissent plus d'une fois:

{TRUE;FALSE;TRUE;TRUE;TRUE;FALSE;TRUE;TRUE;TRUE}

Ce tableau est remis au filtre comme argument inclue , indiquant à la fonction quelles valeurs incluent dans le tableau résultant:

{"Andrew";"David";"Andrew";"Andrew";"David";"Andrew";"David"}

Comme vous pouvez le remarquer, seules les valeurs correspondant à la vraie survivent.

Le tableau ci-dessus va à l'argument du tableau unique, et après avoir supprimé les doublons, il obtient le résultat final:

{"Andrew";"David"}

Conseil. De la même manière, vous pouvez filtrer des valeurs uniques qui se produisent plus de deux fois (> 2), plus de trois fois (> 3), etc. Pour cela, modifiez simplement le nombre dans la comparaison logique.

Trouver des valeurs uniques dans plusieurs colonnes (lignes uniques)

Dans la situation où vous souhaitez comparer deux colonnes ou plus et renvoyer les valeurs uniques entre elles, incluez toutes les colonnes cibles de l'argument du tableau .

Par exemple, pour retourner le prénom unique (colonne A) et le nom de famille (colonne B) des gagnants, nous entrons dans cette formule dans E2:

=UNIQUE(A2:B10)

Appuyez sur la touche Entrée donne les résultats suivants:

Pour obtenir des lignes uniques , c'est-à-dire les entrées avec la combinaison unique de valeurs dans les colonnes A, B et C, c'est la formule à utiliser:

=UNIQUE(A2:C10)

Étonnamment simple, n'est-ce pas? :)

Obtenez une liste de valeurs uniques triées dans l'ordre alphabétique

Comment alphabétisez-vous habituellement dans Excel? Droite, en utilisant la fonction de tri ou de filtre intégré. Le problème est que vous devez re-sort chaque fois que vos données source changent, car contrairement aux formules Excel qui recalculent automatiquement à chaque changement dans la feuille de calcul, les fonctionnalités doivent être réappliqués manuellement.

Avec l'introduction de fonctions Dynamic Array, ce problème a disparu! Ce que vous devez faire, c'est simplement déformer la fonction de tri autour d'une formule unique régulière, comme ceci:

Trier (unique (tableau))

Par exemple, pour extraire des valeurs uniques dans les colonnes A à C et organiser les résultats de A à Z, utilisez cette formule:

=SORT(UNIQUE(A2:C10))

Par rapport à l'exemple ci-dessus, la sortie est beaucoup plus facile à percevoir et à travailler avec. Par exemple, nous pouvons clairement voir qu'Andrew et David ont été gagnants dans deux sports différents.

Conseil. Dans cet exemple, nous avons trié les valeurs dans la 1ère colonne de A à Z. Ce sont les défaillances de la fonction de tri, donc les arguments SORT_INDEX FACTIONNELS et SORT_ORDE sont omis. Si vous souhaitez trier les résultats par une autre colonne ou dans un ordre différent (de Z à A ou du plus haut au plus petit), définissez les 2e et 3e arguments comme expliqué dans le didacticiel de la fonction de tri.

Trouver des valeurs uniques dans plusieurs colonnes et concaténer en une seule cellule

Lors de la recherche dans plusieurs colonnes, par défaut, la fonction unique Excel sortira chaque valeur dans une cellule séparée. Peut-être que vous trouverez plus pratique d'avoir les résultats dans une seule cellule?

Pour y parvenir, au lieu de référencer toute la gamme, utilisez l'ampesand (&) pour concaténer les colonnes et mettre le délimiteur souhaité entre les deux.

Par exemple, nous concatérons les prénoms dans A2: A10 et les noms de famille dans B2: B10, séparant les valeurs avec un caractère spatial (""):

=UNIQUE(A2:A10&" "&B2:B10)

En conséquence, nous avons une liste de noms complets dans une colonne:

Obtenez une liste de valeurs uniques en fonction des critères

Pour extraire des valeurs uniques avec la condition, utilisez les fonctions Excel uniques et filtres ensemble:

• La fonction de filtre limite les données uniquement aux valeurs qui répondent à la condition.
• La fonction unique supprime les doublons de la liste filtrée.

Voici la version générique de la formule des valeurs uniques filtrées:

UNIQUE (FILTER (Array, Criteria_Range = Critères ))

Pour cet exemple, obtenons une liste de gagnants dans un sport spécifique. Pour commencer, nous entrons le sport d'intérêt pour certaines cellules, disons F1. Et puis, utilisez la formule ci-dessous pour obtenir les noms uniques:

=UNIQUE(FILTER(A2:B10, C2:C10=F1))

Où A2: B10 est une plage pour rechercher des valeurs uniques et C2: C10 est la plage pour vérifier les critères.

Filtrez des valeurs uniques basées sur plusieurs critères

Pour filtrer les valeurs uniques avec deux conditions ou plus, utilisez les expressions comme indiqué ci-dessous pour construire les critères requis pour la fonction de filtre:

UNIQUE (FILTER (Array, ( Criteria_Range1 = Criteria1 ) * ( Criteria_Range2 = Criteria2 ))))

Le résultat de la formule est une liste des entrées uniques pour lesquelles toutes les conditions spécifiées sont vraies. En termes d'Excel, c'est ce qu'on appelle la logique.

Pour voir la formule en action, obtenons une liste de gagnants uniques pour le sport dans G1 (critères 1) et sous l'âge de G2 (critères 2).

Avec la gamme source dans A2: B10, les sports en C2: C10 (Criteria_Range 1) et âges en D2: D10 (Criteria_Range 2), la formule prend cette forme:

=UNIQUE(FILTER(A2:B10, (C2:C10=G1) * (D2:D10<g2></g2>

Et renvoie exactement les résultats que nous recherchons:

Comment fonctionne cette formule:

Voici une explication de haut niveau de la logique de la formule:

Dans l'argument inclué de la fonction de filtre, vous fournissez deux ou plusieurs paires de plages / critères. Le résultat de chaque expression logique est un tableau de valeurs vraies et fausses. La multiplication des tableaux contraint les valeurs logiques aux nombres et produit un tableau de 1 et 0. Étant donné que la multiplication par zéro donne toujours zéro, seules les entrées qui remplissent toutes les conditions ont 1 dans le tableau final. La fonction de filtre filtre les éléments correspondant à 0 et remet les résultats à Unique.

Pour plus d'informations, veuillez consulter le filtre avec plusieurs critères à l'aide et la logique.

Filtrez des valeurs uniques avec plusieurs ou critères

Pour obtenir une liste de valeurs uniques basées sur plusieurs ou critères, c'est-à-dire que lorsque ce critère ou ce critère est vrai, ajoutez les expressions logiques au lieu de les multiplier:

UNIQUE (FILTER (Array, ( Criteria_Range1 = Criteria1 ) ( Criteria_Range2 = Criteria2 ))))

Par exemple, pour montrer les gagnants dans le football ou le hockey , vous pouvez utiliser cette formule:

=UNIQUE(FILTER(A2:B10, (C2:C10="Soccer") (C2:C10="Hockey")))

Si nécessaire, vous pouvez bien sûr entrer dans les critères dans les cellules séparées et vous référer aux cellules comme indiqué ci-dessous:

=UNIQUE(FILTER(A2:B10, (C2:C10=G1) (C2:C10=G2)))

Comment fonctionne cette formule:

Tout comme lorsque vous testez plusieurs critères, vous placez plusieurs expressions logiques dans l'argument inclue de la fonction de filtre, qui renvoie un tableau de valeurs vraies et fausses. Lorsque ces tableaux sont additionnés, les éléments pour lesquels un ou plusieurs critères sont vrais auront 1, et les éléments pour lesquels tous les critères sont faux auront 0. En conséquence, toute entrée qui répond à une seule condition fait de la matrice qui est remise à unique.

Pour plus d'informations, veuillez consulter le filtre avec plusieurs critères en utilisant ou logique.

Obtenez des valeurs uniques dans Excel en ignorant les blancs

Si vous travaillez avec un ensemble de données qui contient quelques lacunes, une liste des uniques obtenue avec une formule régulière est susceptible d'avoir une cellule vide et / ou une valeur nulle. Cela se produit parce que la fonction unique Excel est conçue pour renvoyer toutes les valeurs distinctes dans une plage, y compris les blancs. Donc, si votre gamme de source a à la fois des zéros et des cellules vierges, la liste unique contiendra 2 zéros, l'un représentant une cellule vierge et l'autre - une valeur zéro elle-même. De plus, si les données source contiennent des chaînes vides renvoyées par une formule, la liste UIQUE comprendra également une chaîne vide ("") qui ressemble visuellement à une cellule vierge:

Pour obtenir une liste de valeurs uniques sans blanc, c'est ce que vous devez faire:

• Filtrez les cellules vierges et les chaînes vides en utilisant la fonction de filtre.
• Utilisez la fonction unique pour limiter les résultats aux valeurs uniques uniquement.

Sous une forme générique, la formule ressemble comme suit:

Unique (filtre ( plage , plage ""))

Dans cet exemple, la formule en D2 est:

=UNIQUE(FILTER(B2:B12, B2:B12""))

En conséquence, Excel renvoie une liste de noms uniques sans cellules vides:

Note. Dans le cas où les données d'origine contiennent des zéros , une valeur zéro sera incluse dans la liste unique.

Trouver des valeurs uniques dans des colonnes spécifiques

Parfois, vous pouvez extraire des valeurs uniques de deux colonnes ou plus qui ne sont pas adjacentes les unes aux autres. Parfois, vous pouvez également réorganiser les colonnes dans la liste résultante. Les deux tâches peuvent être remplies à l'aide de la fonction de choix.

UNIQUE (Choisissez ({1,2,…}, Range1 , Range2 ))

À partir de notre échantillon de table, supposons que vous souhaitiez obtenir une liste de gagnants en fonction des valeurs des colonnes A et C et organiser les résultats de cette commande: d'abord un sport (colonne C), puis un nom sportif (colonne A). Pour le faire, nous construisons cette formule:

=UNIQUE(CHOOSE({1,2}, C2:C10, A2:A10))

Et obtenez le résultat suivant:

Comment fonctionne cette formule:

La fonction Choison renvoie un tableau à 2 dimensions de valeurs dans les colonnes spécifiées. Dans notre cas, il échange également l'ordre des colonnes.

{"Basketball", "Andrew"; "Basketball", "Betty"; "Volleyball", "David"; "Basketball", "Andrew"; "Hockey", "Andrew"; "Soccer", "Robert"; "Volleyball", "David"; "Hockey", "Andrew"; "Basketball", "David"}

À partir du tableau ci-dessus, la fonction unique renvoie une liste d'enregistrements uniques.

Trouver des valeurs uniques et gérer les erreurs

Les formules uniques dont nous avons discuté dans ce travail de tutoriel sont parfaitement parfaites… à condition qu'il y ait au moins une valeur qui répond aux critères spécifiés. Si la formule ne trouve rien, un #calc! Une erreur se produit:

Pour éviter que cela ne se produise, enveloppez simplement votre formule dans la fonction IFERROR.

Par exemple, si aucune valeur unique ne répondant aux critères, vous ne pouvez rien afficher, c'est-à-dire une chaîne vide (""):

=IFERROR(UNIQUE(FILTER(A2:B10, (C2:C10=G1) * (D2:D10<g2></g2>

Ou vous pouvez clairement informer vos utilisateurs qu'aucun résultat n'est trouvé:

=IFERROR(UNIQUE(FILTER(A2:B10, (C2:C10=G1) * (D2:D10<g2 results><p><img src="/static/imghwm/default1.png" data-src="https://img.php.cn/upload/article/000/000/000/174347193269059.png?x-oss-process=image/resize,p_40" class="lazy" alt="Excel Fonction unique - moyen le plus rapide de trouver des valeurs uniques" ></p> <h2 id="Excel-Fonction-unique-ne-fonctionne-pas"> Excel Fonction unique ne fonctionne pas</h2> <p> Comme vous l'avez vu, l'émergence de la fonction unique a rendu les valeurs uniques dans Excel incroyablement faciles. Si tout d'un coup, votre formule entraîne une erreur, il est très susceptible d'être l'un des éléments suivants.</p> <h3 id="NOM-erreur"> #NOM? erreur</h3> <p> Se produit si vous utilisez une formule unique dans une version Excel où cette fonction n'est pas prise en charge.</p> <p> Actuellement, la fonction unique n'est disponible que dans Excel 365 et 2021. Si vous avez une version différente, vous pouvez trouver une solution appropriée dans ce tutoriel: comment obtenir des valeurs uniques dans Excel 2019, Excel 2016 et plus tôt.</p> <p> Le #Name? L'erreur dans les versions prises en charge indique que le nom de la fonction est mal orthographié. Pour plus de détails, voyez comment corriger l'erreur #Name dans Excel.</p> <h3 id="Erreur-spill"> Erreur #spill</h3> <p> Se produit si une ou plusieurs cellules de la plage de déversement ne sont pas complètement vides.</p> <p><img src="/static/imghwm/default1.png" data-src="https://img.php.cn/upload/article/000/000/000/174347193419432.png?x-oss-process=image/resize,p_40" class="lazy" alt="Excel Fonction unique - moyen le plus rapide de trouver des valeurs uniques" ></p></g2>

Pour corriger l'erreur, effacez ou supprimez simplement les cellules non vides. Pour voir exactement quelles cellules entravent le chemin, cliquez sur l'indicateur d'erreur, puis cliquez sur Sélectionner des cellules obstruées . Pour plus d'informations, veuillez consulter #Spill! Erreur dans Excel - Causes et correctifs.

C'est comment trouver des valeurs uniques dans Excel. Je vous remercie d'avoir lu et j'espère vous voir sur notre blog la semaine prochaine!

Classeur de pratique pour télécharger

Excel Exemples de formule de valeurs uniques (fichier .xlsx)

Ce qui précède est le contenu détaillé de. pour plus d'informations, suivez d'autres articles connexes sur le site Web de PHP en chinois!