Tableau de bord Excel basé sur Microsoft Exchange

Il est possible de créer un tableau de bord dans Excel à partir de données enregistrées dans Microsoft Exchange. Il existe plusieurs façons pour récupérer les données de Microsoft Exchange. L’une des façons les plus récentes, est certainement celle proposée par Power Query. Cet article vise à vous montrer comment utiliser Power Query pour créer un tableau de bord dans Excel, basé sur des données Microsoft Exchange et à vous donner quelques conseils en la matière.

Exemple de données récupérées via Microsoft Exchange

Pour chaque formation que vous désirez suivre avec le CFO masqué, on vous recommande de passer un test en ligne. Par la suite, vous recevez un courriel avec le résultat obtenu sur le test en question et une recommandation quant à la formation ou aux formations que vous devriez suivre. Ce courriel ressemble à ceci:

Exemple de tableau de bord Excel

Les courriels ci-haut sont générés automatiquement par notre système maison (du bon vieux VBA!). Nous pouvons donc récupérer ces courriels, plus particulièrement la note à l’intérieur de chacun des courriels, afin de bâtir le tableau de bord suivant. Par exemple, au mois de janvier 2016, à ce jour, 27 personnes ont effectué le test en ligne de la formation Excel – Mise à niveau. Parmi elles, 3 ont obtenu 30%, 4 ont obtenu 40%, etc.

Se connecter à Microsoft Exchange

Pour se connecter à Microsoft Exchange à partir de Power Query, il faut se rendre dans la section From Other Sources/À partir d’autres sources, tel qu’illustré ci-bas:

Ce qui peut être extrait de Microsoft Exchange

Tel qu’illustré ci-dessous, les données accesssibles dans Microsoft Exchange sont les courriels, les calendriers, les personnes (contacts), les tâches et les invitations à des réunions.

Notre exemple est basé sur des courriels envoyés. Donc, une fois connecté à Microsoft Exchange, via Power Query, nous devons choisir Mail. Mais attention de ne pas choisir l’option Load (ou Charger), car dans ce cas, tous les courriels seront chargés, ce qui peut prendre une éternité. Il est largement préférable de cliquer immédiatement sur Edit (ou Modifier).

Transformations dans Power Query

Une fois l’option Mail sélectionnée, nous avons obtenu une table avec les colonnes suivantes:

• Folder Path
• Subject
• Sender
• DisplayTo
• DisplayCc
• ToRecipients
• CcRecipients
• BccRecipients
• DateTimeSent
• DateTimeReceived
• Importance
• Categories
• IsRead
• HasAttachments
• Attachments
• Preview
• Attributes
• Body
• Id

Si vous avez bien examiné la liste des items énumérés ci-dessus, vous avez sans doute aperçu la mention de Attachments. Cela est plutôt intéressant et pratique. Par exemple, penser à un projet d’entreprise où tout le monde vous envoie par courriel des données dans un document Excel standard, (données de budget, feuilles de temps, compte de dépenses, etc.). Imaginez maintenant que vous utilisiez tout simplement Power Query pour récupérer automatiquement toutes les données de ces fichiers-joints… Quand même pas mal, non?

Voici ensuite la série de transformations que nous avons enregistrées dans Power Query.

Nous avons filtré nos sujets de courriels pour ne retenir que ceux commençant par Test en ligne.

Nous avons ensuite choisi de conserver les colonnes suivantes:

Nous avons deux options pour récupérer les données de la colonne Body, soit le format texte ou le format html. Nous avons choisi le format texte.

Nous avons ensuite remplacé certaines valeurs par des espaces vides, soit les valeurs avant la mention du résultat au test.

Nous avons obtenu une colonne avec ce qui semblait, à prime abord, être des cellules vides, mais en cliquant sur l’une de ces cellules, on a pu voir un aperçu du contenu au bas de notre écran. Comme nous avions besoin de récupérer la note obtenu au test, nous avons d’abord supprimé tout l’espace vide devant cette note.

Pour supprimer l’espace vide, nous avons utilisé la fonction Trim, ce qui nous a permis d’obtenir ce qui suit:

Pour nous débarrasser de tout le reste, nous avons utilisé la fonctionnalité Split Column By et nous avons ensuite renommer nos colonnes et changer les types de données.

Nous avons ensuite voulu convertir les nombres entiers en %. Pour ce faire, nous avons choisi d’ajouter une colonne personnalisée:

Finalement, nous avons obtenu une table avec les courriels des utilisateurs, les dates et les notes des résultats de tests. C’est ce qui nous a permis de créer le tableau de bord présenté en début d’article.

À prendre en considération

Une solution comme celle présentée ci-haut peut être intéressante mais il faut être vigileant en milieu corporatif. En effet, cette solution s’alimente de données contenues dans un compte personnel, dans ce cas-ci, mon compte personnel de courriels. Si un individu développe une telle solution en entreprise, la solution ne peut pas être transféfée à un autre individu puisque la requête se fera alors à partir du compte de cet individu (qui n’aura certainement pas le même contenu que le premier). Donc, si vous songez à développer une solution corporative, basée sur Microsoft Exchange, vous devez prendre en considération cet élément. Vous pourriez songer à transférer les archives courriels d’un employé vers un autre (dans un cas extrême) ou vous pourriez songer à exporter l’info d’abord sur une plate-forme indépendante et à faire pointer vos requêtes vers cette plate-forme. Dans tous les cas, c’est un pensez-y bien.

---

NOTRE OFFRE DE FORMATIONS

---

Le CFO masqué vous offre 13 formations, réparties dans 4 catégories: Tableaux de bord, Modélisation financière, Finance corporative et Analyse et modélisation de données. Ces formations sont offertes en classe, en entreprise et en ligne. Nos formateurs sont des experts dans leur domaine et sont accrédités par Emploi-Québec et vous remettent un certificat, à la fin de chaque formation, que vous pouvez notamment utiliser pour faire reconnaître des heures de formation continue auprès de votre ordre professionnel.

Consulter la liste des formations offertes

Pour info: 514-605-7112 ou info@lecfomasque.com