{"id":10478,"date":"2022-11-21T06:25:46","date_gmt":"2022-11-21T05:25:46","guid":{"rendered":"https:\/\/www.auditsi.eu\/?p=10478"},"modified":"2022-11-12T21:53:23","modified_gmt":"2022-11-12T20:53:23","slug":"excel-filtres-tris-et-sous-totaux","status":"publish","type":"post","link":"https:\/\/www.auditsi.eu\/?p=10478","title":{"rendered":"Excel : filtres, tris et sous-totaux"},"content":{"rendered":"<p style=\"text-align: justify;\">Filtres et totalisation des donn\u00e9es filtr\u00e9es (avec la fonction SOUS.TOTAL()) permettent de <strong>mettre en avant les donn\u00e9es r\u00e9pondant aux crit\u00e8res<\/strong> d\u00e9finis par l\u2019utilisateur.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-10479\" src=\"https:\/\/www.auditsi.eu\/wp-content\/uploads\/2022\/11\/XL-FILTRES1.png\" alt=\"XL FILTRES1\" width=\"235\" height=\"130\"><\/p>\n<p style=\"text-align: justify;\"><strong>Mise en place des filtres<\/strong> :<\/p>\n<ul>\n<li style=\"text-align: justify;\">D\u00e9finir la zone de filtres :\n<ul>\n<li style=\"text-align: justify;\">S\u00e9lectionner la plage de donn\u00e9es<\/li>\n<li style=\"text-align: justify;\">Puis aller dans le menu Donn\u00e9es \/ Filtrer ; il ne peut y avoir qu&#8217;une seule zone de filtres par onglet (feuille de travail).<\/li>\n<\/ul>\n<\/li>\n<\/ul>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-10480\" src=\"https:\/\/www.auditsi.eu\/wp-content\/uploads\/2022\/11\/XL-FILTRES2.png\" alt=\"XL FILTRES2\" width=\"645\" height=\"121\" srcset=\"https:\/\/www.auditsi.eu\/wp-content\/uploads\/2022\/11\/XL-FILTRES2.png 645w, https:\/\/www.auditsi.eu\/wp-content\/uploads\/2022\/11\/XL-FILTRES2-300x56.png 300w\" sizes=\"auto, (max-width: 645px) 100vw, 645px\" \/><\/p>\n<p style=\"text-align: justify;\">A partir de cette m\u00eame zone, il est possible de cr\u00e9er un <a href=\"https:\/\/www.auditsi.eu\/?tag=tcd\">tableau crois\u00e9 dynamique<\/a> (TCD) pour compl\u00e9ter l\u2019analyse.<\/p>\n<p style=\"text-align: justify;\"><strong>Utilisation des filtres<\/strong> :<\/p>\n<ul>\n<li style=\"text-align: justify;\">Cliquer sur un filtre<\/li>\n<li style=\"text-align: justify;\">Cocher\/d\u00e9cocher les items et\/ou saisir des termes dans la zone de recherche (utilisation possible des <a href=\"https:\/\/www.auditsi.eu\/?p=10420\">caract\u00e8res g\u00e9n\u00e9riques<\/a>)<\/li>\n<li style=\"text-align: justify;\">Cocher Ajouter \u00e0 la s\u00e9lection le cas \u00e9ch\u00e9ant<\/li>\n<\/ul>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-10481\" src=\"https:\/\/www.auditsi.eu\/wp-content\/uploads\/2022\/11\/XL-FILTRES3.png\" alt=\"XL FILTRES3\" width=\"329\" height=\"560\" srcset=\"https:\/\/www.auditsi.eu\/wp-content\/uploads\/2022\/11\/XL-FILTRES3.png 329w, https:\/\/www.auditsi.eu\/wp-content\/uploads\/2022\/11\/XL-FILTRES3-176x300.png 176w\" sizes=\"auto, (max-width: 329px) 100vw, 329px\" \/><\/p>\n<p style=\"text-align: justify;\">Les filtres permettent \u00e9galement de <strong>trier les donn\u00e9es<\/strong> (\u00e0 partir des donn\u00e9es filtr\u00e9es ou \u00e0 l\u2019aide du menu Donn\u00e9es\/Trier).<\/p>\n<p style=\"text-align: justify;\">L\u2019utilisateur perd vite de vue les ent\u00eates de colonnes et de lignes d\u00e8s lors qu\u2019il descend dans les profondeurs de tableaux de donn\u00e9es tr\u00e8s \u00e9tendus. Afin de faciliter la navigation dans de tels fichiers, Excel propose de <a href=\"https:\/\/www.auditsi.eu\/?p=10470\"><strong>figer les volets<\/strong><\/a>.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-10471\" src=\"https:\/\/www.auditsi.eu\/wp-content\/uploads\/2022\/11\/XL-VOLETS1.png\" alt=\"XL VOLETS1\" width=\"280\" height=\"153\"><\/p>\n<p style=\"text-align: justify;\">Calculer des <strong>sous-totaux<\/strong> avec la <a href=\"https:\/\/www.auditsi.eu\/?p=6539\">fonction SOUS.TOTAL(fonction ; plage de donn\u00e9es)<\/a>. Cette fonction effectue le calcul selon la fonction indiqu\u00e9e (par exemple : 9 = SOMME) sur une plage filtr\u00e9e (les lignes exclues du filtre ne sont pas prises en compte dans le sous-total). Ainsi, au gr\u00e9 de la modification des filtres par l\u2019utilisateur, les calculs sont mis \u00e0 jour.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-10482\" src=\"https:\/\/www.auditsi.eu\/wp-content\/uploads\/2022\/11\/SOUS.TOTAUX-fonctions.png\" alt=\"SOUS.TOTAUX fonctions\" width=\"456\" height=\"405\" srcset=\"https:\/\/www.auditsi.eu\/wp-content\/uploads\/2022\/11\/SOUS.TOTAUX-fonctions.png 456w, https:\/\/www.auditsi.eu\/wp-content\/uploads\/2022\/11\/SOUS.TOTAUX-fonctions-300x266.png 300w\" sizes=\"auto, (max-width: 456px) 100vw, 456px\" \/><\/p>\n<p style=\"text-align: justify;\">Pour <strong>mesurer la repr\u00e9sentativit\u00e9 des donn\u00e9es filtr\u00e9es<\/strong> par rapport \u00e0 l\u2019ensemble des donn\u00e9es : Sous.Total \/ Somme -&gt; %<\/p>\n<p style=\"text-align: justify;\">Exemple :<\/p>\n<ul>\n<li style=\"text-align: justify;\">L1 : =SOMME(L$5:L$317019)<\/li>\n<li style=\"text-align: justify;\">L2 : =L3\/L1<\/li>\n<li style=\"text-align: justify;\">L3 : =SOUS.TOTAL(9;L$5:L$317019)<\/li>\n<\/ul>\n<p style=\"text-align: justify;\"><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-10483\" src=\"https:\/\/www.auditsi.eu\/wp-content\/uploads\/2022\/11\/XL-FILTRES4.png\" alt=\"XL FILTRES4\" width=\"921\" height=\"363\" srcset=\"https:\/\/www.auditsi.eu\/wp-content\/uploads\/2022\/11\/XL-FILTRES4.png 921w, https:\/\/www.auditsi.eu\/wp-content\/uploads\/2022\/11\/XL-FILTRES4-300x118.png 300w, https:\/\/www.auditsi.eu\/wp-content\/uploads\/2022\/11\/XL-FILTRES4-768x303.png 768w, https:\/\/www.auditsi.eu\/wp-content\/uploads\/2022\/11\/XL-FILTRES4-730x288.png 730w\" sizes=\"auto, (max-width: 921px) 100vw, 921px\" \/><\/p>\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\">Analyse de donn\u00e9es et automatisation<\/a>&nbsp;\/&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 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%20%3A%20filtres%2C%20tris%20et%20sous-totaux\";<\/script><script type=\"text\/javascript\" src=\"https:\/\/static.hupso.com\/share\/js\/counters.js\"><\/script><!-- Hupso Share Buttons --><\/div>","protected":false},"excerpt":{"rendered":"<p>Filtres et totalisation des donn\u00e9es filtr\u00e9es (avec la fonction SOUS.TOTAL()) permettent de mettre en avant les donn\u00e9es r\u00e9pondant aux crit\u00e8res d\u00e9finis par l\u2019utilisateur. Mise en place des filtres : D\u00e9finir la zone de filtres : S\u00e9lectionner la plage de donn\u00e9es Puis aller dans le menu Donn\u00e9es \/ Filtrer ; il ne peut y avoir qu&#8217;une &#8230;<\/p>\n<p><a href=\"https:\/\/www.auditsi.eu\/?p=10478\" class=\"more-link\">Continue reading &lsquo;Excel : filtres, tris et sous-totaux&rsquo; &raquo;<\/a><\/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%20%3A%20filtres%2C%20tris%20et%20sous-totaux\";<\/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":[166,294,2776,291,1137,2775],"class_list":["post-10478","post","type-post","status-publish","format-standard","hentry","category-maitriser-excel","tag-analyse-de-donnees","tag-filtres","tag-representativite","tag-sous-total","tag-tri","tag-volets"],"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\/10478","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=10478"}],"version-history":[{"count":3,"href":"https:\/\/www.auditsi.eu\/index.php?rest_route=\/wp\/v2\/posts\/10478\/revisions"}],"predecessor-version":[{"id":10485,"href":"https:\/\/www.auditsi.eu\/index.php?rest_route=\/wp\/v2\/posts\/10478\/revisions\/10485"}],"wp:attachment":[{"href":"https:\/\/www.auditsi.eu\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=10478"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.auditsi.eu\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=10478"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.auditsi.eu\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=10478"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}