{"id":6539,"date":"2016-02-23T00:47:58","date_gmt":"2016-02-22T23:47:58","guid":{"rendered":"http:\/\/www.auditsi.eu\/?p=6539"},"modified":"2016-02-23T00:47:58","modified_gmt":"2016-02-22T23:47:58","slug":"excel-calculer-des-sous-totaux","status":"publish","type":"post","link":"https:\/\/www.auditsi.eu\/?p=6539","title":{"rendered":"EXCEL : calculer des sous-totaux"},"content":{"rendered":"<p style=\"text-align: justify;\">Le calcul de sous-totaux sur des tableaux de donn\u00e9es filtr\u00e9s (Donn\u00e9es \/ Filtrer) s&#8217;obtient avec :<\/p>\n<ul>\n<li style=\"text-align: justify;\">la fonction <em>SOUS.TOTAL(fonction ; plage de donn\u00e9es)<\/em> sous Excel,<\/li>\n<li style=\"text-align: justify;\">la fonction <em>SUBTOTAL(fonction, plage de donn\u00e9es)<\/em> sous VBA d&#8217;Excel.<\/li>\n<\/ul>\n<table id=\"tblID0EBBDAAA\" class=\"banded\">\n<thead>\n<tr>\n<td><b class=\"ocpLegacyBold\">no_fonction<\/b> <b class=\"ocpLegacyBold\"><br \/>\n(comprend les valeurs masqu\u00e9es)<\/b><\/td>\n<td><b class=\"ocpLegacyBold\">no_fonction<\/b> <b class=\"ocpLegacyBold\"><br \/>\n(ignore les valeurs masqu\u00e9es)<\/b><\/td>\n<td><b class=\"ocpLegacyBold\">Fonction<\/b><\/td>\n<\/tr>\n<\/thead>\n<tbody>\n<tr>\n<td>1<\/td>\n<td>101<\/td>\n<td>MOYENNE<\/td>\n<\/tr>\n<tr>\n<td>2<\/td>\n<td>102<\/td>\n<td>NB<\/td>\n<\/tr>\n<tr>\n<td>3<\/td>\n<td>103<\/td>\n<td>NBVAL<\/td>\n<\/tr>\n<tr>\n<td>4<\/td>\n<td>104<\/td>\n<td>MAX<\/td>\n<\/tr>\n<tr>\n<td>5<\/td>\n<td>105<\/td>\n<td>MIN<\/td>\n<\/tr>\n<tr>\n<td>6<\/td>\n<td>106<\/td>\n<td>PRODUIT<\/td>\n<\/tr>\n<tr>\n<td>7<\/td>\n<td>107<\/td>\n<td>ECARTYPE<\/td>\n<\/tr>\n<tr>\n<td>8<\/td>\n<td>108<\/td>\n<td>ECARTYPEP<\/td>\n<\/tr>\n<tr>\n<td>9<\/td>\n<td>109<\/td>\n<td>SOMME<\/td>\n<\/tr>\n<tr>\n<td>10<\/td>\n<td>110<\/td>\n<td>VAR<\/td>\n<\/tr>\n<tr>\n<td>11<\/td>\n<td>111<\/td>\n<td>VAR.P<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<p style=\"text-align: justify;\">Par exemple, l&#8217;expression <em>SOUS.TOTAL(9;&#8221;A3;A200&#8243;)<\/em> calcule la somme (fonction n\u00b0 9) de tous les montants non filtr\u00e9s compris dans la plage A3 \u00e0 A200.<\/p>\n<p style=\"text-align: justify;\">Approfondir le sujet : <a title=\"Programmer en VBA\" href=\"http:\/\/www.auditsi.eu\/?cat=1569\">Programmer en VBA<\/a> \/ <a title=\"S\u00e9rie d'articles Ma\u00eetriser Excel\" href=\"http:\/\/www.auditsi.eu\/?p=4475\">D\u00e9couvrir la s\u00e9rie d\u2019articles Ma\u00eetriser Excel<\/a><\/p>\n<p>&nbsp;<\/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=\"EXCEL%20%3A%20calculer%20des%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>Le calcul de sous-totaux sur des tableaux de donn\u00e9es filtr\u00e9s (Donn\u00e9es \/ Filtrer) s&#8217;obtient avec : la fonction SOUS.TOTAL(fonction ; plage de donn\u00e9es) sous Excel, la fonction SUBTOTAL(fonction, plage de donn\u00e9es) sous VBA d&#8217;Excel. no_fonction (comprend les valeurs masqu\u00e9es) no_fonction (ignore les valeurs masqu\u00e9es) Fonction 1 101 MOYENNE 2 102 NB 3 103 NBVAL 4 &#8230;<\/p>\n<p><a href=\"https:\/\/www.auditsi.eu\/?p=6539\" class=\"more-link\">Continue reading &lsquo;EXCEL : calculer des 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%20calculer%20des%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":[50,7,48,1326,1569],"tags":[166,839,1633,294,291,1723,1641],"class_list":["post-6539","post","type-post","status-publish","format-standard","hentry","category-audit-de-donnees","category-breves","category-excel","category-maitriser-excel","category-programmer-en-vba","tag-analyse-de-donnees","tag-calculs","tag-excel","tag-filtres","tag-sous-total","tag-subtotal","tag-vba"],"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\/6539","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=6539"}],"version-history":[{"count":1,"href":"https:\/\/www.auditsi.eu\/index.php?rest_route=\/wp\/v2\/posts\/6539\/revisions"}],"predecessor-version":[{"id":6543,"href":"https:\/\/www.auditsi.eu\/index.php?rest_route=\/wp\/v2\/posts\/6539\/revisions\/6543"}],"wp:attachment":[{"href":"https:\/\/www.auditsi.eu\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=6539"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.auditsi.eu\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=6539"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.auditsi.eu\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=6539"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}