Ajouter de nouvelles fonctions à Excel

Le tableur Excel est un outil de calcul extrêmement polyvalent. Son éditeur de formule de calcul offre un choix très riche de fonctions de calcul. L’inconvénient des formules de calcul est que leur duplication favorise les erreurs (copier/coller…) et leur mise à jour peut se révéler ardue.

Plutôt que d’utiliser des formules de calcul, il est possible d’ajouter de nouvelles fonctions à Excel en les créant de toute pièce. L’avantage étant que la formule de calcul n’est rédigée qu’une seule fois, facilitant à la fois sa mise à jour et son contrôle. La démarche de création de nouvelles fonctions est assez simple. Une fois créées, ces fonctions sont utilisables dans les formules de calcul Excel et peuvent être associées à d’autres fonctions, natives ou non, pour donner lieu à des calculs très élaborés.

Calcul RFA formule de calcul

Par exemple, le calcul de primes ou de remises de fin d’année (RFA) peut être réalisé à l’aide de formules de calcul :

=C7*0,025

Dans ce cas, la prime est calculée sur la base du chiffre d’affaires (stocké en cellule C7) auquel est appliqué un taux de 2,5 %.

Pour éviter d’avoir à dupliquer la formule de calcul de la prime sur toute une feuille de calcul, il est envisageable de créer une fonction Excel qui assure la même tâche.

Ajouter des fonctions à Excel est particulièrement simple. Il suffit d’accéder à l’éditeur de programme VBA (EDI) puis de faire précéder le programme de l’instruction Function suivi d’un nom (au gré du créateur de la fonction) puis de ponctuer le tout avec End Function.

Function Nom_fonction(paramètre1, paramètre2...)


End Function

Ainsi, pour le calcul des primes liées au chiffre d’affaires (CA), la fonction équivalente à la formule de calcul exposée ci-avant se présente ainsi :

Function Prime_CA(CA As Double)
'Calcul d'une prime en fonction du CA réalisé
    Prime_CA = CA * 0.025
End Function

Le CA est transmis à la fonction par l’intermédiaire du paramètre qui suit immédiatement le nom de la fonction. Le code VBA stocke ce paramètre dans la variable CA (de type Double, c’est-à-dire un nombre décimal). Le résultat du calcul est retourné à Excel à l’aide de la commande “Prime_CA = …“.

Les fonctions VBA sont automatiquement intégrées à Excel. Ainsi, lorsque l’utilisateur du classeur Excel commence à saisir une formule de calcul, Excel lui propose les commandes qui correspondent à l’entrée en cours (saisie prédictive) y compris les fonctions créées par l’utilisateur.

Excel sasie formule de calcul

Le tableau de calcul intégrant la nouvelle fonction Prime_CA se présente ainsi :

Calcul RFA fonction

A noter : un classeur Excel qui comprend des macros VBA doit être enregistré avec une extension *.XLSM au lieu de *.XLSX, faute de quoi les macros seront supprimées lors de l’enregistrement du classeur.

Le classeur contenant les exemples est proposé en libre téléchargement en bas de cette page.

Bien entendu, les fonctions créées par l’utilisateur peuvent être intégrées dans des formules de calcul plus ou moins complexes. Par exemple :

=SI(SOMME(C7:D7)>200000;Prime_CA(SOMME(C7:D7)*2);Prime_CA(SOMME(C7:D7)))

La fonction Prime_CA est assez simple, elle se contente d’appliquer un taux sur une base. Dans la réalité, le taux de RFA varie généralement en fonction du CA réalisé ; une grille de calcul comprenant différentes tranches de CA et taux à appliquer est paramétrée. Dans ce cas, la fonction pourra prendre cette forme :

Function Prime_CA_Grille(CA As Double)
'Calcul d'une prime en fonction du CA réalisé
    'Grilles CA et pourcentage (Pct) ; exemple CA >= 100 000 € et < 200 000 €, 1.5 % de prime
    Const GrilleCA = "0;100000;200000;250000;300000"
    Const GrillePct = "0;1.5;2;2.5;2.75"
    
    Dim Table_GrilleCA As Variant
    Dim Table_GrillePct As Variant
    Dim i As Integer
    
    'Conversion des grilles en tableaux de données
    Table_GrilleCA = Split(GrilleCA, ";")
    Table_GrillePct = Split(GrillePct, ";")
    
    'Parcours de la table de CA et calcul de la prime
    For i = UBound(Table_GrilleCA) To LBound(Table_GrilleCA) Step -1
        If CA >= Table_GrilleCA(i) Then
            Prime_CA_Grille = CA * Val(Table_GrillePct(i)) / 100
            Exit For
        End If
    Next i
End Function

Les grilles de CA et taux sont définies respectivement à l’aide des constantes GrilleCA et GrillePct avant d’être converties en variables tableaux (Table_GrilleCA et Table_GrillePct) à l’aide de la fonction VBA Split.

Les deux lignes de code :

Const GrilleCA = "0;100000;200000;250000;300000"
Const GrillePct = "0;1.5;2;2.5;2.75"

Définissent la grille de remises suivante :

Excel grille de remises

Ainsi, un CA compris entre 100 et 200 K€ donnera une RFA de 1,5 %. La boucle compteur For… To… Step… permet de parcourir les tranches de CA de la grille de RFA (des tranches supérieures (Ubound) vers les tranches inférieures (Lbound) pour déterminer le taux de remise (Table_GrillePct(i)) et calculer la remise (Prime_CA_Grille = CA * Val(Table_GrillePct(i)) / 100).)

Calcul RFA grille fonction

Une autre méthode de calcul moins élégante mais tout aussi fonctionnelle aurait pu être utilisée en imbriquant des tests conditionnels (If… Then… Else…) à la manière des SI(…) des formules de calcul Excel. La fonction rédigée de la sorte donnerait le code source suivant :

Function Prime_CA_Grille_IF(CA As Double)
'Calcul d'une prime en fonction du CA réalisé avec des tests conditionnels If... Then... Else...
    Dim Taux_Remise As Double
    
    If CA >= 300000 Then
        Taux_Remise = 2.75
    ElseIf CA >= 250000 Then
        Taux_Remise = 2.5
    ElseIf CA >= 200000 Then
        Taux_Remise = 2
    ElseIf CA >= 100000 Then
        Taux_Remise = 1.5
    Else:
        Taux_Remise = 0
    End If
        
    Prime_CA_Grille_IF = CA * Taux_Remise / 100
End Function

Si cette forme de rédaction semble plus lisible, elle est plus difficile à mettre à jour en cas de modification de la grille de remise (notamment ajout de tranches).

Son équivalent en formule de calcul Excel serait :

=C7*SI(C7>= 300000;2.75; SI(C7>= 250000;2.5; SI(C7>= 200000;2; SI(C7>= 100000;1.5;0)/100

Plus complexes encore sont les remises calculées en fonction de paliers. Dans le cadre d’un calcul par palier, le taux de remise appliqué varie en fonction de la décomposition du CA réalisé par tranche (palier). Par exemple, l’application de la grille proposée plus haut à un CA de 220 K€ donne une remise de 0 % de jusqu’à 99 999 € de CA + 1,5 % de 100 000 € à 199 999 € + 2 % de 200 000 € à 220 000 € ‘soit dans cet exemple une remise de 1 900 €.

Function Prime_CA_Palier(CA As Double)
'Calcul d'une prime en fonction du CA réalisé
    'Grilles CA et pourcentage (Pct) ; exemple CA = 220 000 €, 0 % de prime jusque 99 999 € + 1,5 % de prime de 100 000 € à 199 999 € + 2 % de prime de 200 000 € à 220 000 €
    'soit dans cet exemple une prime de 1 900 €
    Const GrilleCA = "0;100000;200000;250000;300000"
    Const GrillePct = "0;1.5;2;2.5;2.75"
    
    Dim Table_GrilleCA As Variant
    Dim Table_GrillePct As Variant
    Dim i As Integer
    Dim CA_Plafond As Double
    
    'Conversion des grilles en tableaux de données
    Table_GrilleCA = Split(GrilleCA, ";")
    Table_GrillePct = Split(GrillePct, ";")
    
    'Parcours de la table de CA et calcul de la prime
    For i = UBound(Table_GrilleCA) To LBound(Table_GrilleCA) Step -1
        Select Case i < UBound(Table_GrilleCA)
            'Si i = UBound(Table_GrilleCA) -> dernière tranche de la grille de CA
            Case True:
                Select Case Table_GrilleCA(i + 1) < CA
                    Case True:
                        CA_Plafond = Table_GrilleCA(i + 1)
                    Case False:
                        If CA > Table_GrilleCA(i) Then CA_Plafond = CA Else CA_Plafond = Table_GrilleCA(i)
                End Select
            Case False:
                If CA > Table_GrilleCA(i) Then CA_Plafond = CA Else CA_Plafond = Table_GrilleCA(i)
        End Select
        Prime_CA_Palier = Prime_CA_Palier + (CA_Plafond - Table_GrilleCA(i)) * Val(Table_GrillePct(i)) / 100
    Next i
End Function

Exemple de tableau de calcul de remises par palier :

Calcul RFA palier fonction

Enfin, le calcul des primes ou remises peut être déterminé en fonction d’objectifs fixés individuellement.

Par exemple, la grille de RFA ci-après fixe le pourcentage de remise en fonction de l’atteinte de l’objectif de CA (soit CA réalisé / objectif de CA) :

Calcul RFA objectif fonction

Cette grille est paramétrée ainsi dans le code VBA :

Const GrillePctObjectif = "0;95;100;105;110"
Const GrillePctPrime = "0;1.5;2;2.5;2.75"

Le calcul d’une prime fixée en fonction d’objectifs individuels nécessite deux paramètres : le CA réalisé (ici : CA) et l’objectif de CA (ici : CA_Objectif). Les paramètres transmis par une fonction sont séparés les uns des autres par des virgules.

Function Prime_Objectif(CA As Double, CA_Objectif As Double)
'Calcul d'une prime en fonction de la réalisation d'un objectif
    'Grilles CA et pourcentage (Pct) ; exemple % réalisation > 110 % , 2.75 % de prime
    Const GrillePctObjectif = "0;95;100;105;110"
    Const GrillePctPrime = "0;1.5;2;2.5;2.75"
    
    Dim Table_GrillePctObjectif As Variant
    Dim Table_GrillePctPrime As Variant
    Dim i As Integer
    Dim PctRéalisé As Double
    
    'Conversion des grilles en tableaux de données
    Table_GrillePctObjectif = Split(GrillePctObjectif, ";")
    Table_GrillePctPrime = Split(GrillePctPrime, ";")
    
    PctRéalisé = CA / CA_Objectif
    
    'Parcours de la table des pourcentages d'objectif et calcul de la prime
    For i = UBound(Table_GrillePctObjectif) To LBound(Table_GrillePctObjectif) Step -1
        If PctRéalisé >= Table_GrillePctObjectif(i) / 100 Then
            Prime_Objectif = CA * Val(Table_GrillePctPrime(i)) / 100
            Exit For
        End If
    Next i
End Function

Le calcul de remises en fonction d’objectifs individuels se résume ainsi :

Calcul RFA objectif fonction2

Code source de l’ensemble des fonctions du classeur :

'Créer de nouvelles fonctions Excel en VBA
'Benoît RIVIERE, 04/2021
'www.auditsi.eu


Option Explicit


Function Prime_CA(CA As Double)
'Calcul d'une prime en fonction du CA réalisé
    Prime_CA = CA * 0.025
End Function


Function Prime_CA_Grille(CA As Double)
'Calcul d'une prime en fonction du CA réalisé
    'Grilles CA et pourcentage (Pct) ; exemple CA >= 100 000 € et < 200 000 €, 1.5 % de prime
    Const GrilleCA = "0;100000;200000;250000;300000"
    Const GrillePct = "0;1.5;2;2.5;2.75"
    
    Dim Table_GrilleCA As Variant
    Dim Table_GrillePct As Variant
    Dim i As Integer
    
    'Conversion des grilles en tableaux de données
    Table_GrilleCA = Split(GrilleCA, ";")
    Table_GrillePct = Split(GrillePct, ";")
    
    'Parcours de la table de CA et calcul de la prime
    For i = UBound(Table_GrilleCA) To LBound(Table_GrilleCA) Step -1
        If CA >= Table_GrilleCA(i) Then
            Prime_CA_Grille = CA * Val(Table_GrillePct(i)) / 100
            Exit For
        End If
    Next i
End Function


Function Prime_CA_Palier(CA As Double)
'Calcul d'une prime en fonction du CA réalisé
    'Grilles CA et pourcentage (Pct) ; exemple CA = 220 000 €, 0 % de prime jusque 99 999 € + 1,5 % de prime de 100 000 € à 199 999 € + 2 % de prime de 200 000 € à 220 000 €
    'soit dans cet exemple une prime de 1 900 €
    Const GrilleCA = "0;100000;200000;250000;300000"
    Const GrillePct = "0;1.5;2;2.5;2.75"
    
    Dim Table_GrilleCA As Variant
    Dim Table_GrillePct As Variant
    Dim i As Integer
    Dim CA_Plafond As Double
    
    'Conversion des grilles en tableaux de données
    Table_GrilleCA = Split(GrilleCA, ";")
    Table_GrillePct = Split(GrillePct, ";")
    
    'Parcours de la table de CA et calcul de la prime
    For i = UBound(Table_GrilleCA) To LBound(Table_GrilleCA) Step -1
        Select Case i < UBound(Table_GrilleCA)
            'Si i = UBound(Table_GrilleCA) -> dernière tranche de la grille de CA
            Case True:
                Select Case Table_GrilleCA(i + 1) < CA
                    Case True:
                        CA_Plafond = Table_GrilleCA(i + 1)
                    Case False:
                        If CA > Table_GrilleCA(i) Then CA_Plafond = CA Else CA_Plafond = Table_GrilleCA(i)
                End Select
            Case False:
                If CA > Table_GrilleCA(i) Then CA_Plafond = CA Else CA_Plafond = Table_GrilleCA(i)
        End Select
        Prime_CA_Palier = Prime_CA_Palier + (CA_Plafond - Table_GrilleCA(i)) * Val(Table_GrillePct(i)) / 100
    Next i
End Function


Function Prime_Objectif(CA As Double, CA_Objectif As Double)
'Calcul d'une prime en fonction de la réalisation d'un objectif
    'Grilles CA et pourcentage (Pct) ; exemple % réalisation > 110 % , 2.75 % de prime
    Const GrillePctObjectif = "0;95;100;105;110"
    Const GrillePctPrime = "0;1.5;2;2.5;2.75"
    
    Dim Table_GrillePctObjectif As Variant
    Dim Table_GrillePctPrime As Variant
    Dim i As Integer
    Dim PctRéalisé As Double
    
    'Conversion des grilles en tableaux de données
    Table_GrillePctObjectif = Split(GrillePctObjectif, ";")
    Table_GrillePctPrime = Split(GrillePctPrime, ";")
    
    PctRéalisé = CA / CA_Objectif
    
    'Parcours de la table des pourcentages d'objectif et calcul de la prime
    For i = UBound(Table_GrillePctObjectif) To LBound(Table_GrillePctObjectif) Step -1
        If PctRéalisé >= Table_GrillePctObjectif(i) / 100 Then
            Prime_Objectif = CA * Val(Table_GrillePctPrime(i)) / 100
            Exit For
        End If
    Next i
End Function

Autre exemple de création de fonction appliqué au barème d’heures des commissaires aux comptes.

___

Pour aller plus loin : programmer en VBA

Share Button
CALCUL PRIMES OU RFA
CALCUL PRIMES OU RFA
CALCUL-PRIMES-OU-RFA.xlsm
20 KiB
291 téléchargements
Détails...
The following two tabs change content below.
Après seize années passées en cabinet d’expertise-comptable et de commissariat aux comptes (où j’ai exercé comme expert-comptable et chef de mission audit), j’ai pris le poste de directeur comptable d’un groupe de distribution automobile en novembre 2014. Au cours de ma carrière, j’ai acquis une expérience significative en audit et en exploitation des systèmes d’information (analyse de données, automatisation des tâches, programmation informatique) au service de la production des comptes annuels et consolidés. C’est cette expérience personnelle et ma passion pour l’informatique que je partage sur ce blog. Mon CV / Réalisations personnelles et projets informatiques / Ma collection / Me contacter

2 commentaires

  1. Benoît,
    Merci pour ton article sur la création de fonction qui est très inspirant.
    En analysant de plus près le code VBA, il est plus facile de comprendre pourquoi tu as opté par la déclaration de constantes puis de la conversion de la grille en tableau (avec split). Cela paraît plus compliquer de définir comme variables (variant) les données contenues dans les cellules d’une feuille (Range(“A1:A5”).value). Dans ton exemple sur la grille de RFA, permettre à l’utilisateur décisionnaire de modifier la grille aurait permis une souplesse d’utilisation à condition que le contenu des cellules soit considéré comme du texte (String) si j’ai bien compris.

  2. Bonjour Claude,
    Merci pour ton message.
    J’ai pris le parti de coder la grille de remise en dur dans le code pour des raisons pratiques. Mais il est tout à fait possible de la stocker dans une feuille Excel.
    Si le sujet des échanges (lecture / écriture) de données Excel à partir d’un programme VBA, je l’ai traité notamment ici : https://www.auditsi.eu/?p=5678.
    Bonne soirée,
    Benoît

Laisser un commentaire

Votre adresse e-mail ne sera pas publiée. Les champs obligatoires sont indiqués avec *

Ce site utilise Akismet pour réduire les indésirables. En savoir plus sur comment les données de vos commentaires sont utilisées.