Tableau de bord de suivi des dépenses

nous allons voir comment créer un suivi des dépenses très facilement avec Excel grâce au tableau croisée dynamique et les segments. et avec un peu de vba nous allons automatiquement afficher la catégorie associer au fournisseur.

Création du tableau des entrées

Nous allons commencer à créer un tableau qui va contenir nos entrées de dépenses. Pour cela nous allons simplement créer un tableau qui contient la date, à qui, le montant et la catégorie de dépense. Ici nous allons rechercher automatiquement la catégorie associer a la colonne à qui (to who) mais avant cela nous allons devoir nommer les champs à qui.

Rechercher la catégorie

Alors pour recherche la catégorie associer à qui nous payons nous avons deux solutions soit nous allons utiliser une des fonctions de recherche ou nous allons rechercher la catégorie avec du code vba.

La première solution est la plus facile mais elle à un petit problème c’est que si une entreprise vend des aliments et de l’électronique et que nous associons l’entreprise à la catégorie alimentation, la recherche affichera donc alimentation mais si nous avons acheter de l’électronique nous allons devoir écraser la formule avec un texte. En soit cela n’est pas trop grave mais je n’aime pas trop utiliser une formule si la valeur peut être différente que le résultat, c’est pourquoi nous allons coder cela.

Pour cela nous allons créer un nouveau tableau sur une nouvelle page et associer le fournisseur avec une catégorie comme suite.

Puis nous allons créer une petite fonction VBA qui va au moment que la valeur du fournisseur change dans la base de données rechercher si le fournisseur est associé à une catégorie si c’est le cas alors il placera la catégorie dans la base de donnée

Private Sub Worksheet_Change(ByVal Target As Range)

On Error Resume Next

    If Not Intersect(Target, Range("who")) Is Nothing Then   

        Dim ownerRange As Range, cell As Range

        Set ownerRange = Sheets("Param").Range("owner")       

        For Each cell In ownerRange           

            If LCase(cell.Value) = LCase(Target.Value) Then

                Target.Offset(0, 2) = cell.Offset(0, 1).Value

                Exit For

            End If           

        Next cell       

    End If   

End Sub

Analyse mensuelle

Désormais que nous avons nos données dans notre base de données nous allons pouvoir créer notre analyse de dépense mensuelle. Pour cela nous allons simplement créer un tableau croisée dynamique de notre base de données que nous allons placer sur une nouvelle feuille et qui représente le total des dépenses par mois.

Et nous allons créer un graphique de nos dépenses et lui donner les couleurs voulut

Analyse Catégorie

Nous allons faire la même chose que l’analyse mensuelle mais cette fois si nous allons remplacer la date par la catégorie.

Ici aussi nous allons créer un graphique.

Le tableau de bord

Pour la création de notre tableau de bord nous allons créer une page que nous allons nommer Analyse et placer un tableau croisée dynamique qui va représenter les dépenses par catégorie et par mois.

Une fois notre tableau croisé dynamique créer nous allons le faire descendre de façons à ne plus le voir à l’écran, cela change cela la résolution et la taille de votre écran. Mais l’objectif ici c’est de voir notre tableau de bord quand on arrive sur al page analyse et le détaille quand on descend plus bas sur la page.

Puis nous allons créer 3 segments de notre tableau croisé dynamique qui représente nos date(mois), année, catégorie. Et placer les ou vous le voulez dans votre tableau de bord et changer la couleur si vous le souhaitez. Mais cela n’est pas terminer car vous allez devoir associer ces 3 segment avec nos autres tableaux croisés dynamique que nous avons créé précédemment afin filtré leurs données aussi. Pour cela faite un clic droit sur un segment et sélectionner connexion de rapport

Désormais nous ajouter le graphique des dépenses mensuelle à notre tableau de bord

Sur le côté nous allons placer notre graphique de catégorie

Nous allons aussi créer un graphique du type secteur qui va représenter le % de dépense par catégorie que nous allons placer au centre de notre tableau de bord.

Et pour finir nous allons placer un rectangle de couleur dans le fond de notre tableau de bord.

Et voila nous avons notre tableau de bord de suivi des dépenses.