{"id":10892,"date":"2023-01-18T01:49:29","date_gmt":"2023-01-18T00:49:29","guid":{"rendered":"https:\/\/www.auditsi.eu\/?p=10892"},"modified":"2023-11-01T19:40:08","modified_gmt":"2023-11-01T18:40:08","slug":"analyse-de-donnees-et-automatisation-avec-excel-et-access-6eme-volet","status":"publish","type":"post","link":"https:\/\/www.auditsi.eu\/?p=10892","title":{"rendered":"Analyse de donn\u00e9es et automatisation avec Excel et Access (6\u00e8me volet)"},"content":{"rendered":"<p style=\"text-align: justify;\">Les quatre premiers volets de cette formation ont \u00e9t\u00e9 consacr\u00e9s \u00e0 Excel. Le cinqui\u00e8me et ce sixi\u00e8me volet abordent le langage VBA et l\u2019automatisation.<\/p>\n<p style=\"text-align: justify;\">La programmation n\u2019est pas un sujet r\u00e9serv\u00e9 aux d\u00e9veloppeurs professionnels. Bien au contraire.&nbsp;Tout un chacun peut s\u2019approprier ce sujet&nbsp;sans connaissance approfondie des techniques de programmation.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-10922\" src=\"https:\/\/www.auditsi.eu\/wp-content\/uploads\/2023\/01\/Volet-6-VBA-fonction.png\" alt=\"Volet 6 VBA fonction\" width=\"1055\" height=\"775\" srcset=\"https:\/\/www.auditsi.eu\/wp-content\/uploads\/2023\/01\/Volet-6-VBA-fonction.png 1055w, https:\/\/www.auditsi.eu\/wp-content\/uploads\/2023\/01\/Volet-6-VBA-fonction-300x220.png 300w, https:\/\/www.auditsi.eu\/wp-content\/uploads\/2023\/01\/Volet-6-VBA-fonction-1024x752.png 1024w, https:\/\/www.auditsi.eu\/wp-content\/uploads\/2023\/01\/Volet-6-VBA-fonction-768x564.png 768w, https:\/\/www.auditsi.eu\/wp-content\/uploads\/2023\/01\/Volet-6-VBA-fonction-730x536.png 730w\" sizes=\"auto, (max-width: 1055px) 100vw, 1055px\" \/><\/p>\n<p style=\"text-align: justify;\">Au programme :<\/p>\n<ul>\n<li style=\"text-align: justify;\">Afficher un message (bo\u00eete MsgBox)<\/li>\n<li style=\"text-align: justify;\">Entrer des donn\u00e9es avec une bo\u00eete de dialogue InputBox<\/li>\n<li style=\"text-align: justify;\">Calculs<\/li>\n<li style=\"text-align: justify;\">Cr\u00e9er une fonction<\/li>\n<li style=\"text-align: justify;\">Copier\/collage sp\u00e9cial<\/li>\n<li style=\"text-align: justify;\">Boucles compteur For\u2026 To\u2026 Step\u2026 Next<\/li>\n<li style=\"text-align: justify;\">Manipuler des plages de cellules dans des variables tableaux<\/li>\n<\/ul>\n<p style=\"text-align: justify;\">Parmi les sujets \u00e9voqu\u00e9s, la cr\u00e9ation de fonctions Excel. Il est tr\u00e8s facile d&#8217;ajouter de nouvelles fonctions \u00e0 Excel. Le code source ci-apr\u00e8s en est la parfaite d\u00e9monstration.<\/p>\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"visualbasic\">Function Calcule_MtTTC(MtHT As Double, TxTVA As Double) As Double\r\n    Dim MtTTC As Double\r\n\r\n    'Calcul du montant TTC\r\n    MtTTC = MtHT * (1 + TxTVA)\r\n    \r\n    'Restitution du montant TTC\r\n    Calcule_MtTTC = MtTTC\r\nEnd Function\r\n<\/pre>\n<p style=\"text-align: justify;\">Il ajoute la fonction Calcule_MtTTC(Mt HT;Taux TVA) qui calcule le montant TTC \u00e0 partir du montant HT et du taux de TVA. Simple et rudement efficace.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-10925\" src=\"https:\/\/www.auditsi.eu\/wp-content\/uploads\/2023\/01\/Fonction-Calcule_MtTTC.png\" alt=\"Fonction Calcule_MtTTC\" width=\"352\" height=\"177\" srcset=\"https:\/\/www.auditsi.eu\/wp-content\/uploads\/2023\/01\/Fonction-Calcule_MtTTC.png 352w, https:\/\/www.auditsi.eu\/wp-content\/uploads\/2023\/01\/Fonction-Calcule_MtTTC-300x151.png 300w\" sizes=\"auto, (max-width: 352px) 100vw, 352px\" \/><\/p>\n<p style=\"text-align: justify;\">Exemple de tableau Excel :<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-10926\" src=\"https:\/\/www.auditsi.eu\/wp-content\/uploads\/2023\/01\/Fonction-Calcule_MtTTC-Formule.png\" alt=\"Fonction Calcule_MtTTC Formule\" width=\"673\" height=\"263\" srcset=\"https:\/\/www.auditsi.eu\/wp-content\/uploads\/2023\/01\/Fonction-Calcule_MtTTC-Formule.png 673w, https:\/\/www.auditsi.eu\/wp-content\/uploads\/2023\/01\/Fonction-Calcule_MtTTC-Formule-300x117.png 300w\" sizes=\"auto, (max-width: 673px) 100vw, 673px\" \/><\/p>\n<p style=\"text-align: justify;\">La fonction nouvellement cr\u00e9\u00e9e peut \u00eatre int\u00e9gr\u00e9e dans une formule de calcul plus complexe :<\/p>\n<p style=\"text-align: center;\">=SI(S3&gt;1;CalculeMtTTC(B4;B5)*S3;0)<\/p>\n<p style=\"text-align: justify;\">Plus \u00e9volu\u00e9e mais gu\u00e8re plus complexe, cette fonction calcule la <strong>clef d&#8217;un num\u00e9ro de s\u00e9curit\u00e9 sociale<\/strong>. Tr\u00e8s utile pour auditer des listes de salari\u00e9s.<\/p>\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"visualbasic\">Function ClefNumSS(num\u00e9roSS As String)\r\n  Dim num13 As Currency\r\n  Dim s As String\r\n  Dim soustrait As Currency\r\n \r\n  num\u00e9roSS = Left(Replace(num\u00e9roSS, \" \", \"\"), 13)\r\n \r\n  'Retraitement des d\u00e9partements corses (2A et 2B)\r\n  soustrait = 0\r\n  s = Mid(num\u00e9roSS, 7, 1)\r\n  Select Case s\r\n    Case \"A\"\r\n      num\u00e9roSS = Replace(num\u00e9roSS, \"A\", \"0\")\r\n      soustrait = 1000000\r\n    Case \"B\"\r\n      num\u00e9roSS = Replace(num\u00e9roSS, \"B\", \"0\")\r\n      soustrait = 2000000\r\n  End Select\r\n \r\n  num13 = CCur(num\u00e9roSS) - soustrait\r\n \r\n  'Calcul de la clef\r\n  ClefNumSS = Format(97 - (num13 - Int(num13 \/ 97) * 97), \"00\")\r\nEnd Function<\/pre>\n<p style=\"text-align: justify;\">Le <strong>copier\/coller<\/strong>, op\u00e9ration simple au demeurant, permet de <strong>transposer des donn\u00e9es<\/strong> voire d&#8217;effectuer des <strong>calculs<\/strong>.<\/p>\n<p style=\"text-align: justify;\">Les boucles compteur For\u2026 To\u2026 Step\u2026 Next permettent de r\u00e9p\u00e9ter un ensemble d\u2019instructions un certain nombre de fois.<\/p>\n<p style=\"text-align: justify;\">Enfin, la <strong>manipulation de plages de cellules<\/strong> \u00e0 l&#8217;aide de variables tableaux est une technique plus complexe d&#8217;usage mais qui assure des <strong>gains de temps<\/strong> dans l&#8217;ex\u00e9cution de codes VBA n\u00e9cessitant de nombreuses lectures\/\u00e9critures de donn\u00e9es Excel.<\/p>\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"visualbasic\">Sub BoucleCompteur_VarTab()\r\n    'D\u00e9claration des variables\r\n    Dim TauxTVA As Double\r\n    Dim TableauExcel As Variant\r\n    Dim MtHT As Double\r\n    Dim MtTTC As Double\r\n    Dim TotalTTC As Double\r\n    Dim i As Integer\r\n    \r\n    'Lecture du taux de TVA\r\n    TauxTVA = ActiveWorkbook.ActiveSheet.Range(\"B2\").Value\r\n    \r\n    'Lecture du tableau Excel\r\n    TableauExcel = ActiveWorkbook.ActiveSheet.Range(\"B5:C10\").Value\r\n    \r\n    TotalTTC = 0\r\n    \r\n    'Boucle de calculs\r\n    For i = 1 To UBound(TableauExcel, 1)\r\n        'Lecture du montant HT\r\n        MtHT = TableauExcel(i, 1)\r\n        'Calcul du montant TTC de la lgne en cours\r\n        MtTTC = MtHT * (1 + TauxTVA)\r\n        'Restitution du montant TTC de la ligne en cours\r\n        TableauExcel(i, 2) = MtTTC\r\n        'Calcul du montant TTC total\r\n        TotalTTC = TotalTTC + MtTTC\r\n    Next i\r\n    \r\n    'Restitution des r\u00e9sultats sous Excel\r\n    ActiveWorkbook.ActiveSheet.Range(\"B5:C10\").Value = TableauExcel\r\n    \r\n    'Efface le contenu de la variable\r\n    Erase TableauExcel\r\n    \r\n    'Restitution du total TTC\r\n    ActiveWorkbook.ActiveSheet.Range(\"C12\").Value = TotalTTC\r\nEnd Sub\r\n<\/pre>\n<p style=\"text-align: justify;\">Le prochain volet continuera de d\u00e9velopper le sujet de l\u2019automatisation et du VBA.<\/p>\n<p style=\"text-align: justify;\">Le support de formation ainsi que les classeurs Excel comprenant les macros VBA&nbsp;sont t\u00e9l\u00e9chargeables ci-apr\u00e8s.<\/p>\n<p style=\"text-align: justify;\">___<\/p>\n<p style=\"text-align: justify;\">Retrouver&nbsp;<a href=\"https:\/\/www.auditsi.eu\/?cat=2765\">tous les volets de cette s\u00e9rie<\/a> d\u00e9di\u00e9e \u00e0 l\u2019analyse de donn\u00e9es et \u00e0 l\u2019automatisation avec Excel et Access<\/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; text-align: justify;\">2.13.0.0<\/div>\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 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=\"Analyse%20de%20donn%C3%A9es%20et%20automatisation%20avec%20Excel%20et%20Access%20%286%C3%A8me%20volet%29\";<\/script><script type=\"text\/javascript\" src=\"https:\/\/static.hupso.com\/share\/js\/counters.js\"><\/script><!-- Hupso Share Buttons --><\/div>","protected":false},"excerpt":{"rendered":"<p>Les quatre premiers volets de cette formation ont \u00e9t\u00e9 consacr\u00e9s \u00e0 Excel. Le cinqui\u00e8me et ce sixi\u00e8me volet abordent le langage VBA et l\u2019automatisation. La programmation n\u2019est pas un sujet r\u00e9serv\u00e9 aux d\u00e9veloppeurs professionnels. Bien au contraire.&nbsp;Tout un chacun peut s\u2019approprier ce sujet&nbsp;sans connaissance approfondie des techniques de programmation. Au programme : Afficher un message &#8230;<\/p>\n<p><a href=\"https:\/\/www.auditsi.eu\/?p=10892\" class=\"more-link\">Continue reading &lsquo;Analyse de donn\u00e9es et automatisation avec Excel et Access (6\u00e8me volet)&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=\"Analyse%20de%20donn%C3%A9es%20et%20automatisation%20avec%20Excel%20et%20Access%20%286%C3%A8me%20volet%29\";<\/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":[2765,641,215],"tags":[815,1965,839,522,1596,2456,387,2865,1709,518,1561,723,2771,1133,1641],"class_list":["post-10892","post","type-post","status-publish","format-standard","hentry","category-analyse-de-donnees-et-automatisation-avec-excel-et-access","category-formation-professionnelle","category-vba","tag-automatisation","tag-boucle-compteur","tag-calculs","tag-clef-de-controle","tag-copier-coller","tag-fonction","tag-for-to-next","tag-inputbox","tag-msgbox","tag-numero-de-securite-sociale","tag-plage-de-cellules","tag-productivite","tag-transposer","tag-variable-tableau","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\/10892","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=10892"}],"version-history":[{"count":6,"href":"https:\/\/www.auditsi.eu\/index.php?rest_route=\/wp\/v2\/posts\/10892\/revisions"}],"predecessor-version":[{"id":10929,"href":"https:\/\/www.auditsi.eu\/index.php?rest_route=\/wp\/v2\/posts\/10892\/revisions\/10929"}],"wp:attachment":[{"href":"https:\/\/www.auditsi.eu\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=10892"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.auditsi.eu\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=10892"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.auditsi.eu\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=10892"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}