{"id":2793,"date":"2013-01-12T20:58:46","date_gmt":"2013-01-12T18:58:46","guid":{"rendered":"http:\/\/www.auditsi.eu\/?p=2793"},"modified":"2014-05-06T22:03:40","modified_gmt":"2014-05-06T20:03:40","slug":"auditer-des-donnees-avec-les-fonctions-de-traitement-de-donnees-dexcel","status":"publish","type":"post","link":"https:\/\/www.auditsi.eu\/?p=2793","title":{"rendered":"Auditer des donn\u00e9es avec les fonctions de traitement de donn\u00e9es d\u2019EXCEL"},"content":{"rendered":"<p style=\"text-align: justify;\">L&#8217;audit de donn\u00e9es est grandement facilit\u00e9 par les fonctions de filtre, de tri et de sous totaux offertes par EXCEL.<\/p>\n<p style=\"text-align: justify;\">La fonction de filtrage des donn\u00e9es permet de n&#8217;afficher que les donn\u00e9es r\u00e9pondant aux crit\u00e8res d\u00e9finis par l&#8217;auditeur. Cette fonctionnalit\u00e9 est activ\u00e9e dans le menu Donn\u00e9es\/Filtrer.<\/p>\n<p style=\"text-align: justify;\"><a href=\"http:\/\/www.auditsi.eu\/?attachment_id=2794\" rel=\"attachment wp-att-2794\"><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-2794\" src=\"https:\/\/www.auditsi.eu\/wp-content\/uploads\/2013\/01\/EXCEL-FILTRES.png\" alt=\"Filtres sous Excel\" width=\"557\" height=\"598\" srcset=\"https:\/\/www.auditsi.eu\/wp-content\/uploads\/2013\/01\/EXCEL-FILTRES.png 557w, https:\/\/www.auditsi.eu\/wp-content\/uploads\/2013\/01\/EXCEL-FILTRES-279x300.png 279w\" sizes=\"auto, (max-width: 557px) 100vw, 557px\" \/><\/a>Avant de l&#8217;activer, il est n\u00e9cessaire de s\u00e9lectionner les ent\u00eates des donn\u00e9es \u00e0 traiter. Une fois les filtres actifs, il suffit de cliquer sur la fl\u00e8che situ\u00e9e en regard de chaque ent\u00eate puis de s\u00e9lectionner les crit\u00e8res \u00e0 appliquer. Parmi les options de filtrages, il est possible de s\u00e9lectionner\/d\u00e9s\u00e9lectionner les occurrences de donn\u00e9es, de filtrer par couleur de cellule, d&#8217;utiliser les op\u00e9rateur de comparaison (&gt;, &lt;)&#8230;<\/p>\n<p style=\"text-align: justify;\">Les donn\u00e9es peuvent \u00eatre tri\u00e9es \u00e0 la demande, soit \u00e0 l&#8217;aide de la fen\u00eatre de filtre (Trier du plus petit au plus grand ou l&#8217;inverse) soit en s\u00e9lectionnant la fonction Trier dans le menu Donn\u00e9es.<\/p>\n<p style=\"text-align: justify;\">Une fois les donn\u00e9es filtr\u00e9es, il est possible de totaliser les donn\u00e9es ainsi obtenues ; la fonction SOUS.TOTAL r\u00e9pond \u00e0 ce besoin (en effet la fonction SOMME totalise l&#8217;ensemble des donn\u00e9es y compris les donn\u00e9es exclues par les filtres).<\/p>\n<p style=\"text-align: justify;\"><a href=\"http:\/\/www.auditsi.eu\/?attachment_id=2796\" rel=\"attachment wp-att-2796\"><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-2796\" src=\"https:\/\/www.auditsi.eu\/wp-content\/uploads\/2013\/01\/EXCEL-SOUSTOTAL.png\" alt=\"Fonction Sous.Total() d'EXCEL\" width=\"308\" height=\"271\" srcset=\"https:\/\/www.auditsi.eu\/wp-content\/uploads\/2013\/01\/EXCEL-SOUSTOTAL.png 308w, https:\/\/www.auditsi.eu\/wp-content\/uploads\/2013\/01\/EXCEL-SOUSTOTAL-300x263.png 300w\" sizes=\"auto, (max-width: 308px) 100vw, 308px\" \/><\/a>L&#8217;usage de cette fonction est simple, elle n\u00e9cessite deux param\u00e8tres : le premier d\u00e9finit la nature de l&#8217;op\u00e9ration \u00e0 effectuer (totalisation, d\u00e9nombrement, moyenne&#8230;), la deuxi\u00e8me la plage de donn\u00e9es \u00e0 traiter. Ainsi dans notre exemple, pour totaliser la colonne D\u00e9bit filtr\u00e9e, la formule sera r\u00e9dig\u00e9e ainsi : =SOUS.TOTAL(9;H4:H54780) et fournira le r\u00e9sultat suivant : 10775085,75 (donn\u00e9es filtr\u00e9es sur le mois de mars) alors que la fonction SOMME aurait retourn\u00e9 69922094,45 pour l&#8217;ensemble des donn\u00e9es.<\/p>\n<p style=\"text-align: justify;\"><a title=\"S\u00e9rie d'articles Ma\u00eetriser Excel\" href=\"http:\/\/www.auditsi.eu\/?p=4475\">D\u00e9couvrir la s\u00e9rie d&#8217;articles Ma\u00eetriser Excel<\/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 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=\"Auditer%20des%20donn%C3%A9es%20avec%20les%20fonctions%20de%20traitement%20de%20donn%C3%A9es%20d%E2%80%99EXCEL\";<\/script><script type=\"text\/javascript\" src=\"https:\/\/static.hupso.com\/share\/js\/counters.js\"><\/script><!-- Hupso Share Buttons --><\/div>","protected":false},"excerpt":{"rendered":"<p>L&#8217;audit de donn\u00e9es est grandement facilit\u00e9 par les fonctions de filtre, de tri et de sous totaux offertes par EXCEL. La fonction de filtrage des donn\u00e9es permet de n&#8217;afficher que les donn\u00e9es r\u00e9pondant aux crit\u00e8res d\u00e9finis par l&#8217;auditeur. Cette fonctionnalit\u00e9 est activ\u00e9e dans le menu Donn\u00e9es\/Filtrer. Avant de l&#8217;activer, il est n\u00e9cessaire de s\u00e9lectionner les &#8230;<\/p>\n<p><a href=\"https:\/\/www.auditsi.eu\/?p=2793\" class=\"more-link\">Continue reading &lsquo;Auditer des donn\u00e9es avec les fonctions de traitement de donn\u00e9es d\u2019EXCEL&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=\"Auditer%20des%20donn%C3%A9es%20avec%20les%20fonctions%20de%20traitement%20de%20donn%C3%A9es%20d%E2%80%99EXCEL\";<\/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":[50,7,48,8,1326],"tags":[1634,1138,1140,1633,52,294,1141,698,1142,292,291,1139,1137],"class_list":["post-2793","post","type-post","status-publish","format-standard","hentry","category-audit-de-donnees","category-breves","category-excel","category-extractions-de-donnees","category-maitriser-excel","tag-audit-de-donnees","tag-criteres","tag-denombrement","tag-excel","tag-exploitation-des-donnees","tag-filtres","tag-fomule-de-calcul","tag-moyenne","tag-operateurs-de-comparaison","tag-somme","tag-sous-total","tag-totalisation","tag-tri"],"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\/2793","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=2793"}],"version-history":[{"count":5,"href":"https:\/\/www.auditsi.eu\/index.php?rest_route=\/wp\/v2\/posts\/2793\/revisions"}],"predecessor-version":[{"id":4815,"href":"https:\/\/www.auditsi.eu\/index.php?rest_route=\/wp\/v2\/posts\/2793\/revisions\/4815"}],"wp:attachment":[{"href":"https:\/\/www.auditsi.eu\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=2793"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.auditsi.eu\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=2793"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.auditsi.eu\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=2793"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}