Analyse de données et automatisation avec Excel et Access (7ème volet)

Les quatre premiers volets de cette formation ont été consacrés à Excel. Les cinq et sixième volets abordent le langage VBA et l’automatisation. Ce septième volet clôt ce sujet du VBA.

La programmation n’est pas un sujet réservé aux développeurs professionnels. Bien au contraire. Tout un chacun peut s’approprier ce sujet sans connaissance approfondie des techniques de programmation.

Volet 7 VBA cas complexe

Au programme :

  • Convertir une chaîne de caractères en une variable tableau avec la fonction Split
  • Algèbre de Boole (ou arithmétique booléenne)
  • Tests conditionnels If… Then… ElseIf… Else:… End If
  • Cas complexe : tests conditionnels If… Then… Else…, boucles compteur et variables tableaux
  • Tests conditionnels Select Case… End Select
  • Formater des cellules
  • Ajouter/supprimer des lignes et des colonnes
  • Approfondir le sujet
  • Exemples et cas pratiques
  • Découvrir la programmation avec le langage Basic

L’objectif de ce volet est d’approfondir les concepts présentés dans les deux précédents volets et d’en introduire de nouveaux.

A titre d’illustration, l’exemple qui suit mêle dans le même code source plusieurs concepts (fonction Split, variable tableau, boucle compteur, test conditionnel…) démontrant la puissance de calcul apporté par le langage VBA à Excel.

Cet exemple calcule des remises sur chiffre d’affaires selon une grille (tranches de CA et taux). Ce calcul donne le même résultat que celui de l’exemple 12 (test conditionnel If… Then… Else). Toutefois, la rédaction est ici beaucoup plus élégante notamment parce que la grille de remise est intégrée dans une constante ce qui facilite sa lisibilité et sa mise à jour.

Function Prime_CA_Grille(CA As Double)
'Calcul d'une prime en fonction du CA réalisé
    'Grilles CA et pourcentage (Pct) ; exemple CA >= 100 000 € et < 200 000 €, 1.5 % de prime
    Const GrilleCA = "0;100000;200000;250000;300000"
    Const GrillePct = "0;1.5;2;2.5;2.75"
    
    Dim Table_GrilleCA As Variant
    Dim Table_GrillePct As Variant
    Dim i As Integer
    
    'Conversion des grilles en tableaux de données
    Table_GrilleCA = Split(GrilleCA, ";")
    Table_GrillePct = Split(GrillePct, ";")
    
    'Parcours de la table de CA et calcul de la prime
    For i = UBound(Table_GrilleCA) To LBound(Table_GrilleCA) Step -1
        If CA >= Table_GrilleCA(i) Then
            Prime_CA_Grille = CA * Val(Table_GrillePct(i)) / 100
            Exit For
        End If
    Next i
End Function

Autre concept introduit dans ce volet, les tests conditionnels Select Case… End Select qui sont une forme de tests complémentaires des If.. Then… ElseIf… End If. Un test conditionnel permet de n’exécuter une instruction ou un ensemble d’instructions que si une ou plusieurs conditions sont remplies.

Test conditionnel

Dans cet exemple, les remises sont calculées en fonction de paliers. Dans le cadre d’un calcul par palier, le taux de remise appliqué varie en fonction de la décomposition du chiffre d’affaires réalisé par tranche (palier). Par exemple, l’application de la grille proposée plus haut à un CA de 220 K€ donne une remise de 0 % de jusqu’à 99 999 € de CA + 1,5 % de 100 000 € à 199 999 € + 2 % de 200 000 € à 220 000 € soit dans cet exemple une remise de 1 900 €.

Function Prime_CA_Palier(CA As Double)
'Calcul d'une prime en fonction du CA réalisé
    'Grilles CA et pourcentage (Pct) ; exemple CA = 220 000 €, 0 % de prime jusque 99 999 € + 1,5 % de prime de 100 000 € à 199 999 € + 2 % de prime de 200 000 € à 220 000 €
    'soit dans cet exemple une prime de 1 900 €
    Const GrilleCA = "0;100000;200000;250000;300000"
    Const GrillePct = "0;1.5;2;2.5;2.75"
    
    Dim Table_GrilleCA As Variant
    Dim Table_GrillePct As Variant
    Dim i As Integer
    Dim CA_Plafond As Double
    
    'Conversion des grilles en tableaux de données
    Table_GrilleCA = Split(GrilleCA, ";")
    Table_GrillePct = Split(GrillePct, ";")
    
    'Parcours de la table de CA et calcul de la prime
    For i = UBound(Table_GrilleCA) To LBound(Table_GrilleCA) Step -1
        Select Case i < UBound(Table_GrilleCA)
            'Si i = UBound(Table_GrilleCA) -> dernière tranche de la grille de CA
            Case True:
                Select Case Table_GrilleCA(i + 1) < CA
                    Case True:
                        CA_Plafond = Table_GrilleCA(i + 1)
                    Case False:
                        If CA > Table_GrilleCA(i) Then CA_Plafond = CA Else CA_Plafond = Table_GrilleCA(i)
                End Select
            Case False:
                If CA > Table_GrilleCA(i) Then CA_Plafond = CA Else CA_Plafond = Table_GrilleCA(i)
        End Select
        Prime_CA_Palier = Prime_CA_Palier + (CA_Plafond - Table_GrilleCA(i)) * Val(Table_GrillePct(i)) / 100
    Next i
End Function

Pour de plus amples explications : consulter le support de formation téléchargeable en bas de cet article.

Différents exemples et cas pratiques sont proposés à l’usage du lecteur afin d’approfondir le sujet : contrôle des comptes, analyse du fichier des écritures comptables (FEC), calcul des pourcentages d’intérêts (comptes consolidés) y compris dans les cas les plus complexes (participations croisées, autocontrôle…) grâce au calcul matriciel… Plus insolite : comment savoir si votre ordinateur calcule faux… ou non.

Afin d’élargir le sujet, une introduction à la programmation en langage Basic (dont le VBA est une déclinaison) est proposée. Les langages VBA et Basic partagent les mêmes instructions de programmation et la même syntaxe ; l’apprentissage du Basic peut être une bonne introduction au VBA. La découverte de la programmation par le jeu vidéo s’avère plus ludique.

DONKEY KONG in the Small Basic

Plus classique, voici le tutoriel complet de programmation d’un calcul d’échéancier d’emprunt en langage Basic :

Le support de formation ainsi que les classeurs Excel comprenant les macros VBA sont téléchargeables ci-après.

A titre subsidiaire, ENI vient de sortir la deuxième édition du livre Business Intelligence avec Excel écrit par Boris NORO. Cet ouvrage propose à ses lecteurs de découvrir l’analyse de données à l’aide d’Excel et plus précisément de Power Query, de Power Pivot, du Langage DAX…

Il n’aura échappé à personne, que l’intelligence artificielle s’immisce de plus en plus dans nos vies. Tout récemment ChatGPT a révolutionné la génération de texte automatisée. Cet outil est non seulement capable de “créer” du texte mais également de rédiger du code informatique. Cet outil est actuellement en version beta mais il faut le tester pour en comprendre le fonctionnement et voir comment chacun peut se l’approprier. Voici, à titre d’illustration, une demande formulée par mes soins visant à obtenir de ChatGPT une fonction VBA calculant un montant TTC à partir d’un montant HT et d’un taux de TVA (sujet évoqué dans le précédent volet de cette formation) :

ChatGPT Fonction VBA

Finalement, il n’y aura peut-être bientôt plus besoin d’apprendre à programmer pour commander une machine ;-).

Le prochain volet s’attachera à présenter le système de gestion de bases de données Access.

___

Retrouver tous les volets de cette série dédiée à l’analyse de données et à l’automatisation avec Excel et Access

Share Button
Formation Analyse De Données Automatisation Excel & Access 10-2022 Volet 7
Formation Analyse De Données Automatisation Excel & Access 10-2022 Volet 7
Formation-Analyse-de-donnees-Automatisation-Excel-Access-10-2022-Volet-7.pdf
7.5 MiB
13 téléchargements
Détails...
EXCEL - VBA - Exemple 12
18 KiB
7 téléchargements
Détails...
EXCEL - VBA - Exemple 13
19 KiB
7 téléchargements
Détails...
EXCEL - VBA - Exemple 14
19 KiB
9 téléchargements
Détails...
The following two tabs change content below.
Après seize années passées en cabinet d’expertise-comptable et de commissariat aux comptes (où j’ai exercé comme expert-comptable et chef de mission audit), j’ai pris le poste de directeur comptable d’un groupe de distribution automobile en novembre 2014. Au cours de ma carrière, j’ai acquis une expérience significative en audit et en exploitation des systèmes d’information (analyse de données, automatisation des tâches, programmation informatique) au service de la production des comptes annuels et consolidés. C’est cette expérience personnelle et ma passion pour l’informatique que je partage sur ce blog. Mon CV / Réalisations personnelles et projets informatiques / Ma collection / Me contacter

Un commentaire

  1. Pingback: ChatGPT et consorts : pour quoi faire ? - Audit & Systèmes d'Information

Laisser un commentaire

Votre adresse e-mail ne sera pas publiée. Les champs obligatoires sont indiqués avec *

Ce site utilise Akismet pour réduire les indésirables. En savoir plus sur comment les données de vos commentaires sont utilisées.