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
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
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
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
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.
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
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
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
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
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.
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.
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.
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).
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.
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
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
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
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
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
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
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
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
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.
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.
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.
Sophie Marchand, M.Sc., CPA, CGAExperte en modélisation d’affairesSite web: lecfomasque.comCell.: 514-605-7112Courriel: [email protected]
lecfomasque.com