{"id":10693,"date":"2023-01-27T06:27:16","date_gmt":"2023-01-27T05:27:16","guid":{"rendered":"https:\/\/www.auditsi.eu\/?p=10693"},"modified":"2022-12-26T01:43:24","modified_gmt":"2022-12-26T00:43:24","slug":"excel-2021-supprimer-des-doublons-avec-la-fonction-unique","status":"publish","type":"post","link":"https:\/\/www.auditsi.eu\/?p=10693","title":{"rendered":"Excel 2021 : supprimer des doublons avec la fonction UNIQUE"},"content":{"rendered":"<p style=\"text-align: justify;\">La <strong>suppression des doublons<\/strong> est une des \u00e9tapes incontournables en mati\u00e8re de <strong>nettoyage de donn\u00e9es<\/strong>. C&#8217;est aussi une mani\u00e8re de regrouper des donn\u00e9es pour obtenir une synth\u00e8se \u00e0 partir d&#8217;une masse de donn\u00e9es (par exemple obtenir une balance comptable \u00e0 partir des \u00e9critures comptables). C&#8217;est ce que pratique un tableau crois\u00e9 dynamique ou encore une <a href=\"https:\/\/www.auditsi.eu\/?p=6913\">requ\u00eate SQL regroupement (GROUP BY)<\/a>.<\/p>\n<p style=\"text-align: justify;\">Excel propose dor\u00e9navant une fonction qui r\u00e9alise cette t\u00e2che : la <strong>fonction UNIQUE<\/strong>. Cette fonction est dot\u00e9e de capacit\u00e9s de calcul matriciel dynamique.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-10733\" src=\"https:\/\/www.auditsi.eu\/wp-content\/uploads\/2022\/12\/Fonction-UNIQUE.png\" alt=\"Fonction UNIQUE\" width=\"376\" height=\"288\" srcset=\"https:\/\/www.auditsi.eu\/wp-content\/uploads\/2022\/12\/Fonction-UNIQUE.png 376w, https:\/\/www.auditsi.eu\/wp-content\/uploads\/2022\/12\/Fonction-UNIQUE-300x230.png 300w\" sizes=\"auto, (max-width: 376px) 100vw, 376px\" \/><\/p>\n<p style=\"text-align: justify;\">Les&nbsp;<strong>formules matricielles dynamiques<\/strong>&nbsp;permettent \u00e0 des fonctions d\u2019<strong>interagir avec les cellules adjacentes<\/strong>&nbsp;pour y loger le r\u00e9sultat de calculs alors que traditionnellement une formule de calcul ne produit un r\u00e9sultat que dans la cellule o\u00f9 elle est log\u00e9e. Le nombre de cellules concern\u00e9es&nbsp;<strong>d\u00e9pend de la taille du r\u00e9sultat<\/strong>&nbsp;; d\u2019o\u00f9 la notion de formules matricielles dynamiques.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-10734\" src=\"https:\/\/www.auditsi.eu\/wp-content\/uploads\/2022\/12\/Fonction-UNIQUE-Donnees.png\" alt=\"Fonction UNIQUE Donn\u00e9es\" width=\"753\" height=\"590\" srcset=\"https:\/\/www.auditsi.eu\/wp-content\/uploads\/2022\/12\/Fonction-UNIQUE-Donnees.png 753w, https:\/\/www.auditsi.eu\/wp-content\/uploads\/2022\/12\/Fonction-UNIQUE-Donnees-300x235.png 300w, https:\/\/www.auditsi.eu\/wp-content\/uploads\/2022\/12\/Fonction-UNIQUE-Donnees-730x572.png 730w\" sizes=\"auto, (max-width: 753px) 100vw, 753px\" \/><\/p>\n<p style=\"text-align: justify;\">Cette fonction ne requi\u00e8re qu&#8217;un seul param\u00e8tre : la table de donn\u00e9es \u00e0 traiter.<\/p>\n<p style=\"text-align: justify;\">Exemple :<\/p>\n<p style=\"text-align: center;\">=UNIQUE(A4:B28)<\/p>\n<p style=\"text-align: justify;\">La fonction UNIQUE reprend les donn\u00e9es de la plage A4:B28 en supprimant les donn\u00e9es en doublons.<\/p>\n<p>Cette fonction peut \u00eatre utilis\u00e9e conjointement aux fonctions <a href=\"https:\/\/www.auditsi.eu\/?p=10692\">ASSEMB.V<\/a>, <a href=\"https:\/\/www.auditsi.eu\/?p=10516\">FILTRE<\/a>,&nbsp;<a href=\"https:\/\/www.auditsi.eu\/?p=10516\">TRIER<\/a>&nbsp;et&nbsp;<a href=\"https:\/\/www.auditsi.eu\/?p=10426\">TRANSPOSE<\/a>&#8230;<\/p>\n<p style=\"text-align: justify;\">Exemple :<\/p>\n<p style=\"text-align: center;\">=TRIER(UNIQUE(A4:B28))<\/p>\n<p style=\"text-align: justify;\">Cette formule trie les donn\u00e9es \u00e0 l&#8217;aide de la fonction TRIER.<\/p>\n<p style=\"text-align: justify;\">Le classeur d&#8217;exemples est t\u00e9l\u00e9chargeable ci-apr\u00e8s.<\/p>\n<p style=\"text-align: justify;\">Pour en savoir plus sur les fonctions matricielles dynamiques : <a href=\"https:\/\/www.auditsi.eu\/?p=10653\">cf formation acc\u00e9l\u00e9r\u00e9e Excel<\/a><\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-10735\" src=\"https:\/\/www.auditsi.eu\/wp-content\/uploads\/2022\/12\/FORMATION-XLSOMME.SI_.ENS-MATRICIEL.png\" alt=\"FORMATION XLSOMME.SI.ENS MATRICIEL\" width=\"1096\" height=\"799\" srcset=\"https:\/\/www.auditsi.eu\/wp-content\/uploads\/2022\/12\/FORMATION-XLSOMME.SI_.ENS-MATRICIEL.png 1096w, https:\/\/www.auditsi.eu\/wp-content\/uploads\/2022\/12\/FORMATION-XLSOMME.SI_.ENS-MATRICIEL-300x219.png 300w, https:\/\/www.auditsi.eu\/wp-content\/uploads\/2022\/12\/FORMATION-XLSOMME.SI_.ENS-MATRICIEL-1024x747.png 1024w, https:\/\/www.auditsi.eu\/wp-content\/uploads\/2022\/12\/FORMATION-XLSOMME.SI_.ENS-MATRICIEL-768x560.png 768w, https:\/\/www.auditsi.eu\/wp-content\/uploads\/2022\/12\/FORMATION-XLSOMME.SI_.ENS-MATRICIEL-730x532.png 730w\" sizes=\"auto, (max-width: 1096px) 100vw, 1096px\" \/><\/p>\n<p style=\"text-align: justify;\">Autres articles en rapport avec la d\u00e9tection et la suppression des doublons :<\/p>\n<ul style=\"text-align: justify;\">\n<li><a href=\"https:\/\/www.auditsi.eu\/?p=8383\">EXCEL : mettre en \u00e9vidence les doublons avec les formats conditionnels<\/a><\/li>\n<li><a href=\"https:\/\/www.auditsi.eu\/?p=6915\">Les requ\u00eates SQL UNION<\/a><\/li>\n<li><a href=\"https:\/\/www.auditsi.eu\/?p=8313\">SQL : d\u00e9tecter les achats comptabilis\u00e9s en double<\/a><\/li>\n<li><a href=\"https:\/\/www.auditsi.eu\/?p=10684\">EXCEL : supprimer les doublons (menu Donn\u00e9es)<\/a><\/li>\n<li><a href=\"https:\/\/www.auditsi.eu\/?p=710\">EXCEL, VBA : Supprimer les doublons d\u2019une liste<\/a><\/li>\n<\/ul>\n<p style=\"text-align: justify;\">___<\/p>\n<p style=\"text-align: justify;\">Approfondir le sujet :&nbsp;<a href=\"https:\/\/www.auditsi.eu\/?cat=2765\">Formation gratuite Analyse de donn\u00e9es et automatisation avec Excel et Access<\/a> \/&nbsp;<a href=\"https:\/\/www.auditsi.eu\/?glossary=excel\">Tout savoir sur Excel<\/a>&nbsp;\/&nbsp;<a title=\"S\u00e9rie d'articles Ma\u00eetriser Excel\" href=\"https:\/\/www.auditsi.eu\/?p=4475\">D\u00e9couvrir la s\u00e9rie d\u2019articles Ma\u00eetriser Excel<\/a><\/p>\n<div id=\"sconnect-is-installed\" style=\"display: none; text-align: justify;\">2.13.0.0<\/div>\n<div id=\"sconnect-is-installed\" style=\"display: none;\">2.13.0.0<\/div>\n<div id=\"sconnect-is-installed\" style=\"display: none;\">2.13.0.0<\/div>\n<div id=\"sconnect-is-installed\" style=\"display: none;\">2.13.0.0<\/div>\n<div id=\"sconnect-is-installed\" style=\"display: none;\">2.13.0.0<\/div>\n<div id=\"sconnect-is-installed\" style=\"display: none;\">2.13.0.0<\/div>\n<div style=\"padding-bottom:20px; padding-top:10px;\" class=\"hupso-share-buttons\"><!-- Hupso Share Buttons - https:\/\/www.hupso.com\/share\/ --><a class=\"hupso_counters\" href=\"https:\/\/www.hupso.com\/share\/\"><img decoding=\"async\" src=\"https:\/\/static.hupso.com\/share\/buttons\/lang\/fr\/share-small.png\" style=\"border:0px; padding-top:2px; float:left;\" alt=\"Share Button\"\/><\/a><script type=\"text\/javascript\">var hupso_services_c=new Array(\"twitter\",\"facebook_like\",\"facebook_send\",\"email\",\"print\",\"linkedin\");var hupso_counters_lang = \"fr_FR\";var hupso_image_folder_url = \"\";var hupso_twitter_via=\"BenoitRiviere14\";var hupso_url_c=\"\";var hupso_title_c=\"Excel%202021%20%3A%20supprimer%20des%20doublons%20avec%20la%20fonction%20UNIQUE\";<\/script><script type=\"text\/javascript\" src=\"https:\/\/static.hupso.com\/share\/js\/counters.js\"><\/script><!-- Hupso Share Buttons --><\/div>","protected":false},"excerpt":{"rendered":"<p>La suppression des doublons est une des \u00e9tapes incontournables en mati\u00e8re de nettoyage de donn\u00e9es. C&#8217;est aussi une mani\u00e8re de regrouper des donn\u00e9es pour obtenir une synth\u00e8se \u00e0 partir d&#8217;une masse de donn\u00e9es (par exemple obtenir une balance comptable \u00e0 partir des \u00e9critures comptables). C&#8217;est ce que pratique un tableau crois\u00e9 dynamique ou encore une requ\u00eate SQL regroupement (GROUP BY).<\/p>\n<p>Excel propose dor\u00e9navant une fonction qui r\u00e9alise cette t\u00e2che : la fonction UNIQUE.<\/p>\n<div style=\"padding-bottom:20px; padding-top:10px;\" class=\"hupso-share-buttons\"><!-- Hupso Share Buttons - https:\/\/www.hupso.com\/share\/ --><a class=\"hupso_counters\" href=\"https:\/\/www.hupso.com\/share\/\"><img src=\"https:\/\/static.hupso.com\/share\/buttons\/lang\/fr\/share-small.png\" style=\"border:0px; padding-top:2px; float:left;\" alt=\"Share Button\"\/><\/a><script type=\"text\/javascript\">var hupso_services_c=new Array(\"twitter\",\"facebook_like\",\"facebook_send\",\"email\",\"print\",\"linkedin\");var hupso_counters_lang = \"fr_FR\";var hupso_image_folder_url = \"\";var hupso_twitter_via=\"BenoitRiviere14\";var hupso_url_c=\"\";var hupso_title_c=\"Excel%202021%20%3A%20supprimer%20des%20doublons%20avec%20la%20fonction%20UNIQUE\";<\/script><script type=\"text\/javascript\" src=\"https:\/\/static.hupso.com\/share\/js\/counters.js\"><\/script><!-- Hupso Share Buttons --><\/div>","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"advanced_seo_description":"","jetpack_seo_html_title":"","jetpack_seo_noindex":false,"ngg_post_thumbnail":0,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","_links_to":"","_links_to_target":""},"categories":[1326],"tags":[1633,2820,290,2339,2821,2076],"class_list":["post-10693","post","type-post","status-publish","format-standard","hentry","category-maitriser-excel","tag-excel","tag-formule-matricielle-dynamique","tag-group-by","tag-nettoyage-des-donnees","tag-unique","tag-valeurs-uniques"],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"jetpack_likes_enabled":false,"_links":{"self":[{"href":"https:\/\/www.auditsi.eu\/index.php?rest_route=\/wp\/v2\/posts\/10693","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.auditsi.eu\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.auditsi.eu\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.auditsi.eu\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.auditsi.eu\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=10693"}],"version-history":[{"count":9,"href":"https:\/\/www.auditsi.eu\/index.php?rest_route=\/wp\/v2\/posts\/10693\/revisions"}],"predecessor-version":[{"id":10737,"href":"https:\/\/www.auditsi.eu\/index.php?rest_route=\/wp\/v2\/posts\/10693\/revisions\/10737"}],"wp:attachment":[{"href":"https:\/\/www.auditsi.eu\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=10693"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.auditsi.eu\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=10693"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.auditsi.eu\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=10693"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}