Blog ENI : Toute la veille numérique !
Accès illimité 24h/24 à tous nos livres & vidéos ! 
Découvrez la Bibliothèque Numérique ENI. Cliquez ici
💥 Du 22 au 24 novembre : Accès 100% GRATUIT
à la Bibliothèque Numérique ENI. Je m'inscris !
  1. Livres et vidéos
  2. Tableaux de bord
  3. Les techniques avancées d’automatisation
Extrait - Tableaux de bord Pilotez vos informations pour optimiser la prise de décision avec Excel (versions Microsoft 365...)
Extraits du livre
Tableaux de bord Pilotez vos informations pour optimiser la prise de décision avec Excel (versions Microsoft 365...)
13 avis
Revenir à la page d'achat du livre

Les techniques avancées d’automatisation

Introduction

Dans ce dernier chapitre, nous allons nous pencher sur l’automatisation de la collecte, des traitements et des affichages. Nous nous appuierons sur les macro-commandes d’Excel et sur le langage associé VBA (Visual Basic for Applications). En complément de cet apport, nous vous conseillons d’approfondir vos connaissances à l’aide d’ouvrages dédiés au sujet tel que le livre Macros et langage VBA - Apprendre à programmer sous Excel, paru aux Éditions ENI dans la collection Solutions Business.

Nous commencerons donc par découvrir la mise en œuvre de macros à l’aide de l’Enregistreur, nous appliquerons son usage à l’ajout de données venant d’un formulaire dans une base de données complète, tel qu’évoqué à la section La saisie des données - Utiliser un formulaire de saisie du chapitre Les données.

Nous poursuivrons la découverte du langage en construisant des macros d’organisation, telles que la récupération de la liste des fichiers présents dans un répertoire, le listage des noms des onglets présents dans un classeur et le listage des noms utilisés dans un classeur. 

Nous automatiserons la conversion de données supposées récupérées d’un logiciel tiers ne respectant pas les formats de dates usuels ainsi que la conversion de données fusionnées à tort dans une cellule (nom et prénom).

Nous traiterons des tris systématisés sur une plage variable de données, enfin, nous terminerons par une macro d’envoi d’une feuille d’un classeur par mail, dès qu’un indicateur dépasse un seuil prévu.

Utiliser des macros

Nous avons précédemment activé l’accès à l’onglet Développeur, celui-ci doit toujours être opérationnel. Nous allons à présent nous concentrer sur le groupe Code.

images/C12-001N3.png

Il contient les boutons suivants :

images/C12-002N3.png

Enregistrer une macro (qui devient, lorsque l’enregistrement a démarré, Arrêter l’enregistrement).
images/C12-003N3.png

Afficher les macros : pour faire appel à une macro, tant qu’elle n’est pas liée à un bouton.
images/C12-004N3.png

Visual Basic (le raccourci est AltF11) : la fenêtre de l’éditeur VBA se décompose comme suit.
images/C12-005N3.png

En 1, la zone Explorateur de projet (affichable par Ctrl R, si elle a été fermée par erreur), le classeur, les feuilles et les modules comportant potentiellement du code.

En 2, la zone du Code, édité par double clic dans la zone 1, ou après choix de l’objet par simple clic.

En 3, dans la fenêtre Propriétés, les propriétés de l’objet sélectionné en 1.

En 4, la fenêtre Exécution.

Dans la barre d’outils Standard se trouvent les boutons classiques Couper, Copier, Coller, Rechercher, Annuler, Répéter complétés par les boutons Exécuter, Arrêt et Réinitialiser.

Les macros indispensables au tableau de bord

Historisation

 Ouvrez le classeur intitulé Ajout client.xlsm, onglet ajout.

 Copiez les données des cellules O9 à O35.

 Collez-les en D9.

Les données présentes de D9 à D35 ne sont pas modifiées. Il ne reste plus qu’à les transférer dans l’onglet liste_client. Il existe dans l’onglet ajout une « zone tampon » dans les cellules Y97 à An101, accessibles par la flèche en L3. Cette zone reprend les valeurs des cellules D9 à D35, dans le sens et l’ordre du tableau de l’onglet liste_client.

 Dans l’onglet Développeur - groupe Code, cliquez sur Enregistrer une macro. Dans la fenêtre qui apparaît, saisissez Ajout dans la zone Nom de la macro. Nous la stockerons dans ce classeur car nous n’aurons pas besoin de l’utiliser dans un autre classeur (il est au format .xlsm, le format de classeur Excel qui prend en compte les macros).

images/C12-006N3.png

 Validez.

Ne faites plus d’actions inutiles, du type défilement vers le bas ou la droite, tout ce que vous faites maintenant sera mémorisé et transformé en code VBA.

 Cliquez sur l’onglet liste_client, sélectionnez la ligne 9, insérez une ligne et cochez Format identique à celui du dessous.

images/C12-007N3.png

 Cliquez sur l’onglet ajout, sélectionnez les cellules Y100 à AM100, faites Copier.

 Cliquez sur l’onglet liste_client, en B9, faites Coller - Collage spécial - Valeurs.

 Appuyez sur la touche Echap pour désactiver la fonction de copie.

 Revenez sur l’onglet ajout, sélectionnez la cellule D9.

Arrêtons-là la macro :

 Dans l’onglet Développeur - groupe Code, cliquez sur Arrêter l’enregistrement.

images/C12-008N3.png

Le bouton d’arrêt est aussi présent...

Les macros de retraitement

Sur des dates

Imaginons ici que vous ayez importé des données à partir de votre application de gestion. Les dates présentes dans cette liste sont sous la forme AAAAMMJJ et ne peuvent pas être traitées directement par Excel comme des dates.

Nous allons donc créer une fonction personnalisée permettant de transformer ces dates au format JJ/MM/AAAA. Cette fonction pouvant vous être utile dans d’autres cas, elle est à placer dans votre classeur de macros personnelles.

 Ouvrez le classeur CorrectionDates.xlsm.

 Ouvrez l’éditeur Visual Basic VBA dans le module GestionDates (vous pouvez aussi copier le code du fichier Sub Dates.txt)  :

Function TDate(VDate As String) As Variant 
   If Len(VDate) <> 0 Then 
       TDate = CDate(Right(VDate, 2) & "/" & Mid(VDate, 5, 2) 
& "/" & Left(VDate, 4)) 
       Else 
           TDate = "" 
   End If 
End Function

Une fois la fonction créée, elle est directement utilisable dans les calculs :

 Saisissez en B2=tdate(A2).

images/C12-017N3.png

Sur des noms/prénoms

Parmi les importations effectuées, vous aurez parfois à corriger certaines informations. Dans l’exemple ci-dessous, les noms et prénoms de nos salariés ont été saisis dans une même colonne. De plus, les règles de saisies pour les minuscules/majuscules n’ont pas été respectées.

Excel dispose d’une fonction de remplissage instantané qui permet d’automatiser ces tâches. Cependant cette fonctionnalité ne renvoie pas toujours les résultats escomptés, notamment pour les prénoms composés et le respect de certaines règles relatives...

Classer vos données

Lors de vos traitements de données, vous aurez souvent à effectuer des tris. Il peut être intéressant d’utiliser une macro pour effectuer rapidement des classements.

Afin de nous simplifier les futurs tris, notre objectif est d’avoir uniquement à effectuer les actions suivantes :

  • Cliquer dans la colonne à trier.

  • Exécuter un raccourci-clavier (CtrlShift J par exemple).

Notre macro devra donc sélectionner la plage, reconnaître sur quelle colonne trier puis effectuer le tri.

 Ouvrez le classeur ListeEmployes.xlsx.

 Ouvrez l’éditeur VBA.

 Affichez le classeur de macros personnelles : dans le volet Projet, déroulez VBAProject (PERSONAL.XLSB) et double cliquez sur le module.

 Insérez le code ci-après dans un module du classeur de macros personnelles (il se trouve dans le fichier Sub Tri.txt).

Sub TriCol()  
Dim NumCol As Integer, PlageActive As Range  
Set PlageActive = ActiveCell.CurrentRegion  
NumCol = ActiveCell.Column  
With ActiveWorkbook.ActiveSheet.Sort  
   .SortFields.Clear  
   .SetRange PlageActive  
   .Header = xlYes  
   .MatchCase = False  
   .SortFields.Add Key:=Cells(, NumCol), SortOn:=xlSortOnValues,  
Order:=xlAscending  
   .Orientation = xlTopToBottom  
   .Apply  
End With  
End Sub

 Quittez l’éditeur VBA puis sauvegardez le classeur de macros personnelles.

Si le classeur de macros personnelles n’est pas visible : dans l’onglet Affichage - groupe Fenêtre, cliquez sur Afficher puis sur Afficher le classeur : PERSONAL.XLSB. Enregistrez ensuite le classeur par Ctrl S.

La dernière étape consiste à attribuer...

Envoi d’une feuille du tableau de bord par Outlook

Il peut être parfois intéressant de faire parvenir automatiquement par e-mail à un destinataire précis une feuille représentative de votre tableau de bord en fonction de la valeur d’un indicateur particulier.

 Ouvrez le classeur MiniTab.xlsm.

La feuille que vous allez envoyer comporte probablement des formules qui dépendent d’autre feuilles, voire même d’autres classeurs. Si cette feuille de calcul est envoyée seule, les formules ne fonctionneront plus. Il faudra donc envoyer cette feuille avec les résultats des calculs et non les formules.

Nous allons considérer ici que si notre indicateur du taux de remise, situé dans la cellule B1 de notre feuille tableau de bord, devient supérieur à 10 %, une alerte devra être envoyée par e-mail au destinataire pré-saisi.

Afin que cet envoi soit automatisé, il doit être associé à un événement qui se déclenche en fonction de la valeur de notre cellule référence (B1). Outlook doit être ouvert et correctement paramétré.

 Effectuez un clic droit sur l’onglet TabBord qui contient la valeur référence.

 Dans le menu contextuel affiché, sélectionnez l’option Visualiser le code.

 Saisissez directement le code ci-dessous dans le module ou utilisez le fichier Sub EnvoiMel.txt :

Private Sub Worksheet_Change(ByVal Target As Range)  
Dim Destinataire As String, Objet As String  
Destinataire = "prenom.nom@url.com" 'à remplacer par votre e-mail 
Objet = "Attention Taux remise > 10%"  
If Range("B1").Value > 0.1 Then  
  Application.ScreenUpdating = False  
 ...

En conclusion

Pour clore cet ouvrage, ci-après les derniers conseils à appliquer pour vos tableaux de bord :

  • ne pas saisir de données inutiles,

  • éviter la redondance d’informations,

  • ne pas mélanger les contenus dans les cellules,

  • placer les calculs de synthèse au-dessus de vos listes de données,

  • utiliser les liaisons entre les feuilles et classeurs,

  • utiliser les noms des cellules,

  • appliquer les mises en forme conditionnelles mais sans trop surcharger les tableaux de multiples couleurs,

  • et bien sûr, toujours vérifier vos calculs.