Transcript
Page 1: Les secrets d'un bon tableau de bord excel

Sophie Marchand, M.Sc., CPA, CGAExperte en modélisation d’affairesSite web: lecfomasque.comCell.: 514-605-7112Courriel: [email protected]

Les secrets d’un bon tableau de bord dans Excel

Page 2: Les secrets d'un bon tableau de bord excel

Sophie Marchand, M.Sc., CPA, CGA, MVPExperte en modélisation d’affairesSite web: lecfomasque.comCell.: 514-605-7112Courriel: [email protected]

Sophie Marchand, M.Sc., CPA, CGA, MVP Experte en modélisation d’affaires

Site web: lecfomasque.com

Services offerts

Formations offertes

Activités

Boutique

Blogue

Forum

PRÉSENTATION

Page 3: Les secrets d'un bon tableau de bord excel

Sophie Marchand, M.Sc., CPA, CGA, MVPExperte en modélisation d’affairesSite web: lecfomasque.comCell.: 514-605-7112Courriel: [email protected]

Éléments d’un tableau de bord réussi dans Excel:

Accès automatisé à des données structurées

Manipulation et croisement de données efficaces

Production automatique de graphiques et d’autres éléments de visualisations

Options pour forer les données et les voir sous divers angles

INTRODUCTION

Page 4: Les secrets d'un bon tableau de bord excel

Sophie Marchand, M.Sc., CPA, CGA, MVPExperte en modélisation d’affairesSite web: lecfomasque.comCell.: 514-605-7112Courriel: [email protected]

Provenance des données: Systèmes comptables (ERP)

Systèmes de relations clients (CRM)

Systèmes transactionnels de vente

Systèmes de facturation

Systèmes « maisons »

Données transmises par courriels

Logiciels en ligne (Google Analytics, MailChimp, etc.)

Données web (Wikipedia, etc.)

Etc.

BASES DE DONNÉES

Page 5: Les secrets d'un bon tableau de bord excel

Sophie Marchand, M.Sc., CPA, CGA, MVPExperte en modélisation d’affairesSite web: lecfomasque.comCell.: 514-605-7112Courriel: [email protected]

Format des données: Dans certaines entreprises, les données se retrouvent

dans un cube de données bien organisé ou un entrepôt de données structuré.

Mais attention! Cet entrepôt a été conçu pour les besoins principaux des usagers mais ne couvrent pas nécessairement tous leurs besoins.

Souvent, les usagers doivent utiliser d’autressources de données ou faire des requêtesparticulières auprès de leur équipe de TI.

BASES DE DONNÉES

L’article suivant pourrait vous intéresser: Comment fonctionnent et à quoi servent les bases de données OLAP.

Page 6: Les secrets d'un bon tableau de bord excel

Sophie Marchand, M.Sc., CPA, CGA, MVPExperte en modélisation d’affairesSite web: lecfomasque.comCell.: 514-605-7112Courriel: [email protected]

Format des données: Dans d’autres entreprises, il n’existe pas de cube ou

d’entrepôt de données oganisé

Les données sont donc extraites des systèmes sources en divers formats: Fichiers: .csv, .xlsx, .txt, .xml

Bases de données: Access, MySql, Oracle, Sybase, SQL Server

Autres: SAP BO, Sharepoint List, Hadoop File, etc.

Parfois, ce sont les bases de données elles-mêmes qui peuvent être extraites, d’autres fois, ce sont seulement des rapports que l’on peut configurer (le propriétaire du logiciel ne donne pas accès aux bases de données sources)

BASES DE DONNÉES

Page 7: Les secrets d'un bon tableau de bord excel

Sophie Marchand, M.Sc., CPA, CGA, MVPExperte en modélisation d’affairesSite web: lecfomasque.comCell.: 514-605-7112Courriel: [email protected]

Volume des données: Dans Excel 2013, on a accès à un million de lignes par

feuille mais il n’est pas recommandé, côté performance, d’utiliser un million de lignes pour loger les données de son tableau de bord

Si vous importez des données pour créer une base de données dans Excel et que vous n’utilisez aucune formule dans votre base de données, alors, vous pouvez utiliser plusieurs centaines de milliers de lignes

BASES DE DONNÉES

Page 8: Les secrets d'un bon tableau de bord excel

Sophie Marchand, M.Sc., CPA, CGA, MVPExperte en modélisation d’affairesSite web: lecfomasque.comCell.: 514-605-7112Courriel: [email protected]

Volume des données: Si vous devez ajouter des formules dans vos bases de

données dans Excel et que vous avez un volume de plusieurs centaines de milliers de ligne, il est recommandé d’utiliser le VBA pour affecter les formules aux nouvelles données ajoutées à la base de données au fil du temps et les copier ensuite immédiatement en valeurs

Cela améliorera largement la performance de votre fichier

BASES DE DONNÉES

Page 9: Les secrets d'un bon tableau de bord excel

Sophie Marchand, M.Sc., CPA, CGA, MVPExperte en modélisation d’affairesSite web: lecfomasque.comCell.: 514-605-7112Courriel: [email protected]

Volume des données: Si le volume de données est trop

important, on pourra simplement créer une connexion vers la base de données (si celle-ci est bien structurée)

Par exemple, on pourra connecter un tableau croisé dynamique directement sur un cube OLAP

On pourra aussi créer une connexion à l’aide de Power Query

Ou on pourra importer et loger les données dans Power Pivot

BASES DE DONNÉES

Page 10: Les secrets d'un bon tableau de bord excel

Sophie Marchand, M.Sc., CPA, CGA, MVPExperte en modélisation d’affairesSite web: lecfomasque.comCell.: 514-605-7112Courriel: [email protected]

Importation des données: Pour importer physiquement des données dans Excel, on peut

utiliser le VBA, i.e. qu’on peut écrire une macro qui, à l’activation, ira chercher tous les documents enregistrés dans un répertoire X

On peut aussi faire la même chose avec Power Query

Sinon, on peut utiliser des connecteurs, comme F9, qui lient des données directement dans le système comptable

En dernier recours, certaines données seront copiées/collées ou entrées manuellement

BASES DE DONNÉES

L’article suivant pourrait vous intéresser: Power Query: Combiner plusieurs fichiers Excel.

Page 11: Les secrets d'un bon tableau de bord excel

Sophie Marchand, M.Sc., CPA, CGA, MVPExperte en modélisation d’affairesSite web: lecfomasque.comCell.: 514-605-7112Courriel: [email protected]

Traitement des données: Malheureusement, quand on importe des données d’un autre

système dans Excel, elles ne sont pas toujours dans un format idéal…

Souvent, il faudra donc procéder à des manipulations afin de rendre les données utilisables

Exemples: transformer les points en virgules, convertir des données textes en nombres, séparer une colonne en deux

Ces étapes de transformation pourront être automatisées à l’aide d’Excel, de VBA et/ou de Power Query

MANIPULATION DE DONNÉES

L’article suivant pourrait vous intéresser: Power Query: Importer, transformer et fusionner des tables de données dans Excel.

Page 12: Les secrets d'un bon tableau de bord excel

Sophie Marchand, M.Sc., CPA, CGA, MVPExperte en modélisation d’affairesSite web: lecfomasque.comCell.: 514-605-7112Courriel: [email protected]

Croisement des données Une fois les bases de données bien ordonnées, il est

possible que vous souhaitiez croiser les données entre les différentes sources

Pour croiser les données,on pourra utiliser desformules Excel comme« vlookup » et « sumifs »ou encore Power Pivot

MANIPULATION DES DONNÉES

L’article suivant pourrait vous intéresser: Excel: Créer un modèle de données.

Page 13: Les secrets d'un bon tableau de bord excel

Sophie Marchand, M.Sc., CPA, CGA, MVPExperte en modélisation d’affairesSite web: lecfomasque.comCell.: 514-605-7112Courriel: [email protected]

Tableaux croisés dynamiques intermédiaires Avant de produire des graphiques ou d’autres informations

dans le tableau de bord, il est souvent préférable de créer des tableaux croisés dynamiques intermédiaires

Par la suite, on pourra ajouter des formules dans ce tableau croisé dynamique et s’y connecter pour créer des graphiques ou des formules de type «getpivotdata»

Dans le cas où les données sont dans un cube OLAP ou un modèle Power Pivot, on pourra aussi utiliser des formules cubes

MANIPULATION DES DONNÉES

L’article suivant pourrait vous intéresser:Excel: Comment se servir adéquatement de la fonction Liredonneestabcroidynamique (getpivotdata).

Page 14: Les secrets d'un bon tableau de bord excel

Sophie Marchand, M.Sc., CPA, CGA, MVPExperte en modélisation d’affairesSite web: lecfomasque.comCell.: 514-605-7112Courriel: [email protected]

Power Pivot et le DAX Si on utilise Power Pivot, on

peut aussi utiliser le langage DAX, pour ajouter des colonnes de calculs à nos bases de données ou créer des mesures supplémentaires, qui ne seraient pas supportées directement dans le TCD

On peut même y définir ses KPI

MANIPULATION DES DONNÉES

L’article suivant pourrait vous intéresser: PowerPivot: Colonnes et champs calculés en DAX.

Page 15: Les secrets d'un bon tableau de bord excel

Sophie Marchand, M.Sc., CPA, CGA, MVPExperte en modélisation d’affairesSite web: lecfomasque.comCell.: 514-605-7112Courriel: [email protected]

Choix des graphiques

ÉLÉMENTS DE VISUALISATION

Il faut savoir choisir les bons graphiques pour

exprimer les bons messages

Page 16: Les secrets d'un bon tableau de bord excel

Sophie Marchand, M.Sc., CPA, CGA, MVPExperte en modélisation d’affairesSite web: lecfomasque.comCell.: 514-605-7112Courriel: [email protected]

Utilisation de slicers (segments)

ÉLÉMENTS DE VISUALISATION

L’article suivant pourrait vous intéresser: Excel: Utiliser les slicers pour des tableaux de bord performants.

On peut utiliser les slicersSegments pour

permettre le forage et présenter les donnes

sous différentes dimensions

Page 17: Les secrets d'un bon tableau de bord excel

Sophie Marchand, M.Sc., CPA, CGA, MVPExperte en modélisation d’affairesSite web: lecfomasque.comCell.: 514-605-7112Courriel: [email protected]

Utilisation de sparklines

ÉLÉMENTS DE VISUALISATION

L’article suivant pourrait vous intéresser: Excel: Utiliser les sparklines pour des tableaux de bord performants.

On peut utiliser les sparklines pour

montrer l’évolution des données

Page 18: Les secrets d'un bon tableau de bord excel

Sophie Marchand, M.Sc., CPA, CGA, MVPExperte en modélisation d’affairesSite web: lecfomasque.comCell.: 514-605-7112Courriel: [email protected]

Utilisation de format conditionnel

ÉLÉMENTS DE VISUALISATION

L’article suivant pourrait vous intéresser: Excel: Du format conditionnel dans vos tableaux de bord.

On peut utiliser une panoplie d’indicateurs visuels intéressants à

partir du menu de format conditionnel

Page 19: Les secrets d'un bon tableau de bord excel

Sophie Marchand, M.Sc., CPA, CGA, MVPExperte en modélisation d’affairesSite web: lecfomasque.comCell.: 514-605-7112Courriel: [email protected]

Utilisation de contrôles de formulaires

ÉLÉMENTS DE VISUALISATION

L’article suivant pourrait vous intéresser: Excel: Améliorer vos présentations grâce aux contrôles de formulaires.

On peut utiliser les contrôles de formulaire pour faciliter l’utilisation et la navigation dans le

tableau de bord

Page 20: Les secrets d'un bon tableau de bord excel

Sophie Marchand, M.Sc., CPA, CGA, MVPExperte en modélisation d’affairesSite web: lecfomasque.comCell.: 514-605-7112Courriel: [email protected]

Utilisation de zones de textes

ÉLÉMENTS DE VISUALISATION

L’article suivant pourrait vous intéresser: Les zones de texte, une bénédiction pour vos tableaux de bord.

On peut utiliser les zones de texte

automatisées et les images pour une plus

grande personnalisation

Page 21: Les secrets d'un bon tableau de bord excel

Sophie Marchand, M.Sc., CPA, CGA, MVPExperte en modélisation d’affairesSite web: lecfomasque.comCell.: 514-605-7112Courriel: [email protected]

Power View

ÉLÉMENTS DE VISUALISATION

Les articles suivants pourraient vous intéresser:

Power View: Visualiser vos données sous forme de vidéo

Power View: 3 façons de travailler avec les images

4 façons de forer les données dans vos graphiques

Page 22: Les secrets d'un bon tableau de bord excel

Sophie Marchand, M.Sc., CPA, CGA, MVPExperte en modélisation d’affairesSite web: lecfomasque.comCell.: 514-605-7112Courriel: [email protected]

Power Map

ÉLÉMENTS DE VISUALISATION

Page 23: Les secrets d'un bon tableau de bord excel

Sophie Marchand, M.Sc., CPA, CGA, MVPExperte en modélisation d’affairesSite web: lecfomasque.comCell.: 514-605-7112Courriel: [email protected]

Options pour automatiser le tableau de bord Mettre toutes les données sources « sous forme de

tableau » (format as table)

Ajouter des contrôles de formulaires, des slicers (segments) et des boutons

Utiliser le VBA

Utiliser Power Query

Utiliser des formules Excel avancées

AUTOMATISATION

L’article suivant pourrait vous intéresser: Découvrez la magie des tableaux Excel.

Page 24: Les secrets d'un bon tableau de bord excel

Sophie Marchand, M.Sc., CPA, CGA, MVPExperte en modélisation d’affairesSite web: lecfomasque.comCell.: 514-605-7112Courriel: [email protected]

Découvrez nos formations liées aux tableaux de bord dans Excel:

Excel – Tableaux de bord (niveau 1)

Excel – Tableaux de bord (niveau 2)

Excel – Tableaux de bord (niveau 3)

Excel – Analyse et modélisation de données (niveau 1)

Excel – Analyse et modélisation de données (niveau 2): Power BI

Excel – Analyse et modélisation de données (niveau 3): VBA

CONCLUSION

Lien: http://www.lecfomasque.com/formations/

Note: Toutes nos formations sont offertes en classe et en entreprise à des groupes de professionnels et certaines d’entre elles sont offertes en ligne. Rendez-vous sur notre site pour en savoir davantage.

Page 25: Les secrets d'un bon tableau de bord excel

Sophie Marchand, M.Sc., CPA, CGA, MVPExperte en modélisation d’affairesSite web: lecfomasque.comCell.: 514-605-7112Courriel: [email protected]

Découvrez nos autres formations

Excel – Mise à niveau

Excel – Modélisation financière (niveau 1)

Excel – Modélisation financière (niveau 2)

Excel – Modélisation financière (niveau 3)

Finance corporative – Évaluation d’entreprise

Finance corporative – Analyse de projets d’investissement

Finance corporative – Restructuration et redressement d’entreprise

CONCLUSION

Lien: http://www.lecfomasque.com/formations/

Note: Toutes nos formations sont offertes en classe et en entreprise à des groupes de professionnels et certaines d’entre elles sont offertes en ligne. Rendez-vous sur notre site pour en savoir davantage.

Page 26: Les secrets d'un bon tableau de bord excel

Sophie Marchand, M.Sc., CPA, CGAExperte en modélisation d’affairesSite web: lecfomasque.comCell.: 514-605-7112Courriel: [email protected]

lecfomasque.com


Recommended