Excel et VBA, version 2011

Mise à jour du 14 octobre

La seconde partie de cette humeur, signée Bigalo, devrait être publiée le 2 novembre. En outre, François m’a demandé si je pouvais en faire d’autres. J’ai accepté avec plaisir, mais quitte à rédiger des articles demandant pas mal de préparation, je préfère les publier sous mon nom, plutôt que sous un pseudonyme. Les prochains articles seront donc signés Michel Gaboly.

À bientôt !

Présentation

Un certain nombre d’habitués de Cuk me connaissent déjà, parce qu’il m’est arrivé d’intervenir dans les commentaires, y compris parfois lors de sujets polémiques 

Toutefois, c’est la première humeur que j’ai le plaisir de rédiger. Elle devrait vous faire découvrir des aspects méconnus d’Excel et en particulier la puissance que confère au produit VBA (Visual Basic for Applications), l’environnement de développement associé à la suite Office.

Cette humeur est relativement technique, avec de nombreuses copies d’écran, mais compte tenu de l’usage généralisé d’Excel dans les entreprises, j’espère qu’elle pourra intéresser pas mal de monde, d’autant plus que mon approche d’Excel est atypique : j’ai découvert le produit, dans sa version 1, fin octobre 1985, il y a donc plus de 25 ans déjà, et j’ai toujours largement exploité les possibilités de programmation d’Excel.

Je travaillais alors depuis 5 ans dans un cabinet d’expertise-comptable. J’avais acquis ma première machine, un Mac 128, environ 1 an plus tôt, et découvert avec Multiplan ce qu’était un tableur.

Multiplan existait alors sur Apple II et sur IBM PC, avec une interface texte. C’était déjà relativement puissant mais très peu convivial. La version pour Mac a été une révolution grâce à son interface graphique : on pouvait par exemple déjà élargir ou réduire la largeur des colonnes à la souris, et un clic suffisait, bien sûr, pour mettre des cellules en gras ou en italique.

C’était vraiment l’ancêtre d’Excel. Les principales limitations étaient la taille des fichiers que l’on pouvait alors construire (de mémoire 43 Ko avec le Mac 128 et 57 Ko avec un Mac 512). Mais c’était une autre époque : les Mac n’avaient alors pas encore de disque dur, et ne comportaient qu’un lecteur de disquettes, dont la capacité était de 400 Ko. On pouvait y adjoindre un second lecteur de disquettes, externe, qui coûtait une petite fortune. Les premiers disques durs apparaissaient alors, mais étaient encore très peu répandus.

Ils ont commencé à lentement se démocratiser avec le Mac Plus, sorti début 1986, qui a introduit le port SCSI.

Ce pour quoi j’attendais alors avec impatience de pouvoir tester Excel était la présence annoncée d’un langage macro. Ce langage n’avait rien à voir avec VBA, apparu avec Excel 5, mais permettait déjà énormément de choses, y compris certaines que VBA ne permet pas de réaliser directement.

Avant d’entrer dans le vif du sujet

J’ai enfin eu récemment l’occasion de me plonger sérieusement dans les nouveautés d’Excel 2011. Côté VBA, j’ai rencontré quelques progrès, mais aussi certains bugs, dont un qui est particulièrement grave à mes yeux. À plusieurs reprises, j’ai eu des velléités de rédiger une humeur pour Cuk. Cette fois j’ai franchi le pas, et envoyé un message à François pour lui demander si le sujet l’intéressait. C’était clairement le cas.

Le principal bug de VBA dans la version 2011 d’Excel concerne la fonction Evaluate. Celle-ci permet d’exploiter en particulier les valeurs associées à des noms. Pour faire prendre conscience aux lecteurs à quel point ce bug est gênant, je me suis rendu compte que j’avais besoin d’un très long préambule pour montrer à quel point l’usage des noms peut être utile, ce que l’immense majorité des utilisateurs ne soupçonne pas. 

Cette introduction s’est avérée plus longue que je ne l’avais pensé au départ, car le meilleur moyen que j’ai trouvé pour illustrer l’intérêt des noms est un détour par la paye

Pour cette raison, l’humeur comportera finalement 2 parties. Ce n’est que dans la seconde que nous aborderons les nouveautés d’Excel 2011, et en particulier les bugs liés à VBA.

L’usage des noms dans Excel

Le cas le plus simple consiste à associer un nom à une cellule ou à une plage de cellules. Mais on peut également associer un nom à une valeur particulière, le plafond de sécurité sociale par exemple, voire à une formule. Nous allons examiner ces différents cas.

À mon sens, le thème permettant le mieux d’illustrer l’intérêt des noms, sans avoir besoin de construire une feuille de calcul de grande taille, est la réalisation d’un bulletin de paye. C’est en effet un domaine complexe, qui est un bon exemple pour montrer comment les noms peuvent simplifier les choses.

Il s’agit cependant, d’un sujet indigeste pour certains. Plutôt qu’abandonner à ce stade la lecture de cette humeur, sautez plutôt les explications en italique qui suivent.

En résumé, du contrat de travail entre l’employeur et le salarié, découle une rémunération mensuelle, le salaire de base. Un ensemble de retenues (pour maladie, ou congés sans solde, par exemple) ou de compléments (comme les heures supplémentaires ou une prime exceptionnelle) conduit au salaire brut.

Doivent ensuite être calculées des cotisations dont les bases de calcul et les taux varient. Ces cotisations peuvent comporter une part salariale et/ou une part patronale. Dans le cadre de cette humeur, nous nous limiterons à la partie salariale des cotisations. Celles-ci sont en principe déductibles fiscalement, à l’exception notable de la CRDS et d’une part importante de la CSG.

Le salaire brut, diminué des retenues correspondant à la part déductible des cotisations, représente le net imposable. Celui-ci, moins les cotisations non déductibles, et différents éléments complémentaires, venant en plus ou en moins (part salariale des tickets restaurant, remboursement de frais, … ) conduit enfin au net à payer.

Techniquement, ce sont les bases de calcul des différentes cotisations qui représentent la difficulté essentielle. En effet les taux ne sont qu’un pourcentage à entrer dans le modèle, à appliquer aux bases concernées.

Pour calculer celles-ci, il faut prendre en compte le plafond URSSAF, défini annuellement, actuellement 2 946 € par mois. Certaines cotisations, dites non plafonnées, s’appliquent au salaire brut, d’autres aux tranches A, B et C (celle-ci ne concernant qu’un nombre infime de salariés). Enfin, la CSG et la CRDS ont pour base le salaire brut diminué d’un abattement de 3% pour frais professionnels.

La tranche A est la partie du salaire brut inférieure ou égale au plafond. La tranche B est l’éventuelle fraction du salaire dépassant le plafond. Cependant, la tranche B comporte elle-même un plafond, égal à 4 fois le plafond de la tranche A, soit 11 784 € par mois. La tranche B est donc au maximum égale à 3 fois le plafond URSSAF (éventuelle part du salaire comprise entre 1 et 4 fois ce plafond. Enfin Il existe une tranche C, au-delà de la tranche B, avec un plafond égal à 8 fois le plafond URSSAF, soit 23 568 € par mois. Très peu de salariés sont concernés et nous négligerons les cotisations assises sur cette tranche. Elle a également un autre effet : depuis le premier janvier 2011, pour les salariés atteignant cette tranche, l’abattement de 3% appliqué sur le salaire brut pour le calcul de la base de la CSG et de la CRDS est plafonné et ne s’applique pas au-delà du plafond de la tranche B. Nous négligerons également cet aspect.

En principe le calcul des tranches ne se fait pas mois par mois. Il faut raisonner sur l’année civile ou à défaut sur la période de présence du salarié au cours de l’année. Il faut donc déterminer les tranches sur la base des cumuls et non sur les seuls chiffres d’un mois particulier. Compte tenu de notre sujet, nous ferons également l’impasse sur cet aspect.

Ce que je viens de décrire correspond à la règlementation française et peut désorienter nos amis suisses, mais rappelons qu’il ne s’agit ici que d’illustrer une fonctionnalité d’Excel.

Pour réaliser un bulletin de salaire avec Excel, on a donc essentiellement besoin de connaître le salaire brut, la liste des cotisations salariales, et pour chacune d’elles sa base et son taux.

Les taux et le plafond de la tranche A sont communiqués par les différentes administrations (URSSAF, et caisses de retraite principalement) au début de chaque année, avec parfois des mises à jour des taux en cours d’année. Nous allons voir comment calculer les bases de calcul.

Nous allons successivement réaliser un bulletin de salaire simplifié pour 4 cadres, Moreau, Martin, Blanchard et Dupont, ayant respectivement une rémunération mensuelle de 2 800, 5 000, 12 000 et 15 000 €. Pour cela nous allons déterminer les formules nécessaires au calcul des bases des cotisations, puis simplifier la lecture du bulletin en définissant des noms.

Fig. 1 — Modèle simplifié du bulletin de Moreau, avec la liste des cotisations et leur taux, mais avec des bases sous forme de texte (cellules B11 à B24), qu’il va falloir remplacer par les valeurs correspondantes.

Pour calculer les cotisations, nous allons devoir créer les formules des cellules B11 à B24. Pour commencer, le plus simple est d’entrer en B11 la formule « =E6 ». La base de calcul est le salaire brut, comme indiqué dans l’illustration ci-dessus, et celui-ci (2 800 €) figure en cellule E6. La formule est simple, mais ce serait encore plus clair, si on pouvait écrire « =Brut ». C’est heureusement possible, o condition, bien sûr d’avoir défini le nom !

Comment créer un nom

La méthode classique consiste à sélectionner la cellule (ou la plage de cellules) que l’on veut nommer, puis à lancer une commande du menu Insérer. Bien que cela ne soit plus indiqué dans le menu, cette commande est heureusement accessible par raccourci clavier. Pendant longtemps, le raccourci a été ⌘ + L. Il faut maintenant utiliser à la place, Ctrl + L ou  ⌘ + F3.

Fig. 2 — L’accès à la zone de dialogue Définir un nom

Apparaît alors une zone permettant d’entrer le nom désiré, et de modifier si nécessaire, ce à quoi le nom fait référence. Si la cellule située immédiatement à gauche, ou à défaut, immédiatement au-dessus de la cellule active contient du texte, celui-ci est exploité pour suggérer un nom à donner la cellule, en l’occurrence « Salaire_Brut ».

Fig. 3 — Le nom suggéré, et la référence proposée en association au nom, E6 étant la cellule active, et D6 (située immédiatement à gauche de E6) contenant « Salaire Brut »  

J’ai personnellement le caractère « _ » (underscore) en horreur. À ma connaissance, il est étranger à la typographie française, et en outre, je le trouve hideux ! Le plus souvent, je me contente de le supprimer, et de mettre une majuscule à chaque mot comme « SalaireBrut ». Toutefois, pour un bulletin de salaire, « Brut » suffit ; le terme est d’usage courant et ne laisse place à aucune ambiguïté.

Fig. 4 — Le nom modifié. La référence est correcte.  

Dans la zone Fait référence à, on trouve « =Moreau!$E$6 ». 

Moreau est le nom du salarié concerné, mais surtout celui de l’onglet associé à la feuille, comme on peut le voir, en rouge et blanc, en bas de la figure 1.
Le point d’exclamation est dans Excel le symbole d’appartenance.
Quant à $E$6, cela désigne la cellule E6, en précisant qu’il s’agit d’une référence absolue, c’est-à-dire que le nom en train d’être défini est indépendant de la cellule active.

 « =Moreau!$E$6 » signifie donc la cellule E6 de la feuille Moreau, définie de manière absolue (par opposition à relative, E6 ou, à mixte, $E6 ou E$6).

Maintenant qu’on a défini un premier nom, on va l’utiliser en entrant en B11 la formule « =Brut », pour faire apparaître notre première base.

Fig. 5 — La formule active est B11, ce que l’on peut vérifier tout en haut à gauche de l’illustration, et la formule est « =Brut », comme le montre la barre de formule (au-dessus de l’en-tête de la colonne B)

On a maintenant besoin de déterminer la base CSG. On pourrait entrer dans les cellules concernées « =Brut * 97% ». Je rappelle que les 97% résultent d’un abattement de 3% pour frais professionnel évoqué plus haut. J’ai vu une formule de ce type utilisée dans un exemple de bulletin de salaire au format Excel téléchargeable sur Internet. Une telle formule, contenant un nombre est une aberration pour de multiples raisons. 

Le principe même d’un tableur est d’avoir des cellules avec des valeurs, et d’autres avec des formules, mais on ne mélange pas les 2. En voici les principales raisons :

- Si une valeur, qu’il s’agisse du cours d’une devise, du taux d’une cotisation ou de tout autre chose doit être mise à jour, il faut pouvoir le faire aisément. Pour cela il faut qu’elle soit stockée dans une cellule indépendante, afin de pouvoir la repérer visuellement sans difficulté. On met alors à jour la cellule concernée, et toutes les cellules qui sont liées à l’élément qui vient d’être mis à jour seront recalculées. 

- Si au contraire on entre une valeur au sein d’une formule, comme dans « =Brut * 97% », le jour ou l’abattement sera supprimé ou son taux modifié, il faudra pour corriger le modèle de bulletin de salaire repérer toutes les cellules contenant la formule à adapter, ici 3 cellules, B12, B23 et B24. Dans le cas d’un bulletin, pour peu qu’on ait créé un modèle de classeur avec 12 onglets, (1 par mois), ce ne sont plus 3 mais 36 formules qui devront être corrigées. Pour peu qu’on en oublie 1, voire plus, le modèle ne sera plus fiable, et ce qui accentue le problème, cette perte de fiabilité ne sera pas flagrante.

- Enfin, si en saisissant une valeur dans une formule, on fait une faute de frappe, là encore, on aura un résultat erroné sans même en être conscient, tandis qu’en ayant utilisé une cellule spécifique pour entrer la valeur, l’effet d’une faute de frappe est clairement repérable. 

À mes yeux, une valeur intégrée à une formule est une faute professionnelle. Une telle formule doit être taboue. J’ai décrit ici un exemple détaillé de résultat aberrant résultant d’une formule de ce type.

Le coefficient de 97% permettant de déterminer la base CSG à partir du brut doit donc être stocké explicitement dans une cellule. Ni cette valeur ni le plafond n’ont à apparaître sur le bulletin. On pourrait définir une zone d’impression et les stocker à l’extérieur de celle-ci, mais il est souvent plus simple de grouper les éléments de cette nature dans une feuille spécifique, appelée ici Param (diminutif de paramètres).

Fig. 6 — Le taux permettant de déterminer à partir du salaire brut, la base de calcul de la CSG

Une fois entré le taux de 97% en cellule C6, il faut créer le nom correspondant. Pour allons voir comment le faire sans passer par le dialogue Définir un nom…, de la figure 3. La figure 7 illustre la barre de formules, telle qu’elle apparaît en figure 6, c’est-à-dire « Param » la feuille active, et C6 la cellule active. La barre comporte à son extrémité gauche, une zone allongée avec en son centre « C6 », l’adresse de la cellule. Cette zone se nomme la zone de noms, et permet de faire pas mal de choses.

Fig. 7 — La barre de formules, telle qu’elle se présente quand la cellule C6 de la feuille « Param » est active. Il s’agit d’un détail (la partie supérieure), de la figure 6.

Tout d’abord, si on clique dans la zone de noms, elle se transforme ainsi : 

Fig. 8 — La barre de formules, après qu’on ait cliqué dans la zone de noms (à son extrémité gauche), C6 étant toujours la cellule active.

On se rend compte que la zone de noms est une zone éditable. L’adresse de la cellule est sélectionnée et sera écrasée par le texte que l’on va saisir. Il suffit alors d’entrer « TxBaseCSG », et de valider avec l’une des touches Entrée ou Retour. La zone de noms se présente dorénavant ainsi :

Fig. 9 — La barre de formules, après qu’on ait entré « TxBaseCSG » dans la zone de noms, C6 étant toujours la cellule active.

Le texte entré a remplacé l’adresse de la cellule, et un nom a été créé et associé à la cellule C6, comme on peut aisément le vérifier en passant par Définir un nom… :

Fig. 10 — Contrôle que le nom défini en passant par la zone de noms, a bien été pris en compte.

La zone de noms permet donc de nommer extrêmement facilement une plage de cellules (une unique cellule n’en étant qu’un cas particulier). Il suffit de sélectionner la plage concernée, puis d’entrer le nom souhaité. Cela ne fonctionne toutefois que si le nom n’existe pas déjà. Alors que en passant par le dialogue Définir un nom…, il n’y a pas cette contrainte. Si on recrée un nom déjà utilisé, l’occurrence antérieure sera détruite, seule la dernière référence associée au nom sera valable.

Nous allons utiliser cette facilité qu’offre la zone de noms, pour créer 3 noms supplémentaires associés respectivement aux cellules C2, C3 et C4 de la feuille « Param », pour le plafond des tranches A, B et C. Il restera ensuite, pour compléter la feuille Param, à calculer les plafonds des tranches B et C, en multipliant le celui de la tranche A, par le coefficient correspondant, en colonne B. Pour ce faire, la technique la + rapide consiste à sélectionner les cellules C3 et C4, à entrer la formule, et à valider en une seule fois, grâce à l’emploi conjoint des touches Ctrl et Retour :

Fig. 11 — La formule permettant de calculer le plafond de la tranche B. En ayant préalablement sélectionné les 2 cellules C3 et C4, et en validant avec Ctrl + Retour, on obtient les 2 plafonds en une seule manip. La formule présente en C4 fait bien évidemment référence à B4 et non à B3 (référence relative). 

Après avoir validé la formule, cliquer dans la zone avec 2 flèches (au-dessus du curseur, sur la figure 12), permet d’illustrer encore une autre fonctionnalité de la zone de noms : elle permet de lister les plages qui ont été nommées de manière explicite. En outre, si on clique sur l’un des noms de la liste, la plage correspondante est automatiquement sélectionnée. C’est extrêmement pratique pour les tableaux de grande taille (plusieurs centaines ou milliers de lignes). Avoir nommé les principales zones permet alors de se déplacer aisément malgré la taille du tableau.

Fig. 12 — La zone de noms permet également de lister des zones nommées, et d’y accéder en les choisissant simplement dans la liste déroulante. 

Le déplacement n’est pas limité à la feuille active. Si on clique sur « Brut », on passe de la feuille « Param » à la feuille « Moreau » :

Fig. 13 — La cellule nommée « Brut », sur la feuille « Moreau ». On y a accédé en la choisissant dans la liste de la figure 12.

La zone de noms offre encore d’autres possibilités : si on sélectionne à la souris une plage comportant au moins 2 cellules contigües, pendant toute la phase de sélection, la zone de noms indique le nombre de lignes et de colonnes sélectionnées à tout instant. L’information, éphémère, cesse d’être affichée dès qu’on lâche le bouton de la souris. C’est une aide précieuse lorsqu’on a besoin de sélectionner un nombre précis de lignes et/ou de colonnes :

Fig. 14 — Lors de la sélection d’une plage comportant au moins 2 cellules contigües, la zone de noms indique précisément le nombre de lignes et de colonnes sélectionnées, ici « 6L x 2C », pour désigner un rectangle de 6 lignes par 2 colonnes.

Enfin, dans la figure 11, la zone de noms affiche la fonction SOMME. Dès lors qu’on est en train de saisir une formule (commençant par le signe égale, ce ne sont plus les noms, mais les 10 dernières fonctions utilisées qui sont listées si on clique sur les flèches indiquées par le curseur sur la figure 12.

Après cette longue digression sur la zone de noms, revenons — enfin diront certains — au calcul de nos base de cotisations. Pour la base CSG en cellule B12 du bulletin, il suffit de multiplier le brut par le taux correspondant, ce que fait la formule de la figure 15 :

Fig. 15 — Formule permettant de calculer la base CSG.

Jusqu’à présent, on s’est contenté d’utiliser des noms correspondant chacun à une cellule. L’étape suivante va associer des formules aux noms : Pour la tranche A, il s’agit de la partie du Brut inférieure ou égale au plafond. Chacune de ces valeurs étant associée à un nom, la solution pour débutant consiste à utiliser la fonction SI(). Voici sa syntaxe, pour ceux qui ne la connaissent pas :

=SI(Condition;Résultat si condition remplie;Résultat dans le cas contraire)

Cela signifie qu’il faut lui fournir 3 arguments :

- La condition à tester
- Le résultat à afficher si la condition est remplie
- Le résultat à afficher si la condition n’est pas remplie

Cela donnerait ici :

=SI(Brut < PlafondTrA;Brut;PlafondTrA)

Cela fonctionne mais risque de devenir compliqué quand il s’agira de déterminer les tranches B et C. En outre, il existe une fonction bien mieux adaptée, MIN() pour minimum. La tranche A sera ainsi définie :

 

Fig. 16 — Cette fois, le nom défini n’est pas associé à une cellule, mais à une formule.

Quant à la tranche B, c’est la fraction du Brut qui dépasse le plafond de la tranche A. En première approximation, ce serait :

= Brut - PlafondTrA

Toutefois, si le salaire Brut est inférieur au plafond de la tranche A (2 946 €), ce qui est le cas ici, il n’y a pas lieu d’aboutir à une tranche B négative. Nous avons précisé plus haut, que nous nous placions dans un cas simplifié où on raisonne sur les seules données du mois en cours, alors qu’il peut en réalité y avoir des régularisations, car il faut en réalité raisonner sur les cumuls depuis le 1er janvier (ou l’entrée du salarié dans l’entreprise s’il est arrivé en cours d’année). On considérera ici qu’il s’agit d’un bulletin de janvier, et que donc aucune régularisation n’est nécessaire. La fonction MAX() va nous permettre d’éviter les valeurs négatives, en prenant zéro si la différence est négative :

=MAX(0;Brut - TrancheA)

S’il y a effectivement une tranche B, il faut également prendre en compte son plafond ; elle ne peut être supérieure à la différence entre les plafonds des tranches A et B, soit 3 fois le plafond de la tranche A. Voici la formule complète pour la tranche B :

=MIN(PlafondTrB - PlafondTrA;MAX(0;Brut - TrancheA))

Ce plafonnement avec la fonction MIN() ne concerne qu’une infime minorité de salariés, mais il faut tout de même en tenir compte.

Fig. 17 — Formule permettant de déterminer la tranche B. Malheureusement dans les versions Mac d’Excel, la fenêtre n’est pas redimensionnable, et ne permet de voir qu’une partie de la formule.

La formule est trop longue pour être intégralement visible dans la fenêtre Définir un nom… C’est une des déceptions d’Excel 2011 : depuis la version 2007, les versions Windows d’Excel permettent d’agrandir sans limitation cette fenêtre, qui par défaut est ridiculement petite. Une autre fonctionnalité, très utile a également été introduite avec Excel 2007, la possibilité d’associer à chaque nom un commentaire. Malheureusement ces 2 éléments sont toujours absents de la version 2011 pour Mac ;-((

Le calcul de la tranche C est comparable à celui de la tranche B :

=MIN(PlafondTrC - PlafondTrB;MAX(0;Brut - TrancheA - TrancheB))

A ce stade il reste à remplacer le texte indicatif entré en colonne B par des formules faisant appel aux noms, selon une substitution très simple :

=Brut * TxBaseCSG au lieu de « Base CSG ». On aurait d’ailleurs pu définir le nom « Base CSG » associé à cette formule. Pour être tout à fait correct, il faut tenir compte que l’abattement de 3% est supprimé au-delà de la tranche B. En négligeant les très rares salariés dépassant la tranche C (près de 300 000 € par an), la formule correcte est (TrancheA + TrancheB) * TxBaseCSG + TrancheC, et cela vaut la peine de lui associer le nom « BaseCSG »

=TrancheA au lieu de « Tranche A ».

=TrancheB au lieu de « Tranche B ».

=TrancheA + TrancheB au lieu de « Tranche A + Tranche B ».

À part la première formule, il suffit d’ajouter le signe égale et de supprimer les espaces au sein de « Tranche A » et de « Tranche B », car ceux-ci ne sont pas autorisés dans les noms.

Maintenant que les bases sont entrées, on peut mettre en place le calcul des cotisations. Si nous l’avions fait trop tôt, nous aurions obtenu des valeurs d’erreur, car les noms n’étaient pas encore définis. En D11 par exemple, pour avoir le montant de la cotisation correspondante, on aurait pu entrer simplement « =B11 * C11 ». Les espaces au sein de la formule sont facultatifs, mais améliorent la lisibilité.  Cette formule aurait pu ensuite être recopiée vers le bas jusqu’en D17 pour les cotisations fiscalement déductibles, et reprise en C23 et C24 pour les cotisations non déductibles. Ici encore, définir 2 noms va rendre les formules bien + lisibles : sélectionnons la plage B11:B24 et associons-lui le nom « Base », grâce à la zone de noms.

Fig. 18 — Définition de la plage Base

Répétons l’opération avec la plage C11:C24 et le nom « Tx ». Maintenant que tous les noms nécessaires ont été définis, il suffit d’entrer dans les cellules D11:D17 et D23:D24, la simple formule « = Base * Tx », et les cotisations sont calculées, en utilisant pour chacune, la base et le taux situés sur la même ligne, sans indication supplémentaire : 

Fig. 19 — Un premier exemple de bulletin simplifié mais finalisé !

Le premier bulletin faisait apparaître une base nulle pour la retraite complémentaire tranche B, et aucune des cotisations n’était plafonnée. C’est normal, car le salaire brut (2 800 €) était inférieur au plafond de la tranche A (2 946 €) pour 2011. Si on fait passer le salaire brut à 5 000 €, les cotisations assises sur la tranche A sont plafonnées, et une tranche B apparaît, égale à 2 054 (cellule B17).

Fig. 20 — Dans ce deuxième exemple, de la tranche B apparaît, car le salaire brut dépasse le plafond de la tranche A.

En portant le brut à 12 000 €, on dépasse le plafond de la tranche B. Le total des tranches A et B est égal à ce plafond (cf. figure 12). Les tranches semblent donc correctement calculées. 

Fig. 21 — Avec ce troisième bulletin, le brut dépasse 4 fois le plafond de la tranche A. Cela a pour conséquence que la tranche A atteint son plafond et la tranche B est égale à 3 fois ce plafond. Le total des 2, qui sert de base de cotisation pour le chômage est bien égal au plafond de la tranche B, tel qu’il apparaît sur la feuille « Param », en figure 12.

Enfin, si on fait passer la rémunération totale à 15 000 €, soit 3 000 de plus, on constate que la base augmente elle aussi du même montant, ce qui prouve que la suppression de l’abattement de 3% pour le calcul de la base CSG, au-delà du plafond de la tranche B fonctionne également.

Fig. 22 — Un dernier bulletin permet de vérifier le bon calcul de la base CSG.

La suite au prochain épisode …