Excel : créer un registre automatique des notes de frais avec plafonds et alertes

Difficulté

Intermédiaire

Le plus simple pour automatiser une note de frais dans Excel est de séparer les dépenses et les règles de remboursement. Vous allez créer un registre avec catégories, plafonds par type de dépense, alerte visuelle en cas de dépassement et calcul du montant remboursable avec RECHERCHEX, SOMME.SI.ENS et la mise en forme conditionnelle.

Ce guide fonctionne si votre version d’Excel prend en charge RECHERCHEX. Si cette fonction n’existe pas dans votre version, il faudra la remplacer par une autre méthode, non détaillée ici.

Prérequis

Préparez une feuille pour le registre et une feuille pour les plafonds. Transformez chaque zone en tableau Excel avec Insertion > Tableau : les formules seront plus lisibles et se recopieront automatiquement.

Matériel nécessaire

• Excel avec prise en charge de RECHERCHEX
• Une feuille "Frais"
• Une feuille "Plafonds"

Étapes

1. 1

   Créer la table des plafonds

   

   Dans la feuille Plafonds, créez un tableau avec au moins deux colonnes : Catégorie et Plafond. Saisissez une ligne par type de dépense, par exemple repas, transport ou hébergement.

   Nommez ce tableau, par exemple tbPlafonds. Les en-têtes doivent rester stables, car les formules s’appuieront dessus.

2. 2

   Créer le registre des notes de frais

   Dans la feuille Frais, créez un tableau avec ces colonnes : Date, Collaborateur, Catégorie, Montant, Plafond, Total catégorie, Dépassement et Remboursable.

   Nommez ce tableau tbFrais. Saisissez ensuite vos premières lignes de dépenses.

3. 3

   Limiter la saisie des catégories

   Sélectionnez la colonne Catégorie du tableau des frais, puis appliquez une validation des données de type liste. Utilisez comme source la colonne des catégories de votre table de plafonds.

   Ainsi, les libellés restent cohérents et RECHERCHEX retrouve bien le plafond correspondant.

4. 4

   Récupérer le plafond avec RECHERCHEX

   Dans la colonne Plafond de tbFrais, entrez une formule qui cherche le plafond à partir de la catégorie. Par exemple : =RECHERCHEX([@Catégorie];tbPlafonds[Catégorie];tbPlafonds[Plafond];"").

   Excel remplit automatiquement la colonne si vous êtes dans un tableau. Si aucune catégorie n’est trouvée, la cellule reste vide grâce au quatrième argument.

5. 5

   Calculer le total par catégorie avec SOMME.SI.ENS

   Dans la colonne Total catégorie, calculez le cumul des montants pour la catégorie de la ligne. Utilisez par exemple : =SOMME.SI.ENS(tbFrais[Montant];tbFrais[Catégorie];[@Catégorie]).

   Chaque ligne affiche alors le total de sa catégorie dans le registre entier. Vous pouvez comparer ce total au plafond récupéré juste avant.

6. 6

   Détecter le dépassement

   

   Dans la colonne Dépassement, comparez le total de catégorie au plafond. Par exemple : =SI(ET([@[Plafond]]<>"";[@[Total catégorie]]>[@Plafond]);[@[Total catégorie]]-[@Plafond];0).

   La cellule retourne 0 si le plafond n’est pas dépassé, sinon elle affiche l’excédent. Vous pouvez ensuite filtrer facilement les dépassements.

7. 7

   Calculer le montant remboursable

   Dans la colonne Remboursable, affichez le montant pris en charge. Pour limiter le remboursement au plafond global de la catégorie, vous pouvez utiliser : =SI([@[Plafond]]="";0;MIN([@[Total catégorie]];[@Plafond])).

   Cette formule renvoie le plus petit montant entre le total de la catégorie et le plafond. Elle indique donc le maximum remboursable pour cette catégorie.

8. 8

   Appliquer une mise en forme conditionnelle

   

   Sélectionnez les lignes du tableau des frais, puis créez une règle de mise en forme conditionnelle basée sur une formule. Utilisez par exemple une formule équivalente à la colonne Dépassement, comme =[@Dépassement]>0 si Excel l’accepte dans votre contexte.

   Choisissez un remplissage rouge ou orange pour repérer immédiatement les catégories qui dépassent leur plafond. Testez la règle avec une dépense volontairement élevée.

9. 9

   Tester le fichier avec un exemple simple

   Ajoutez dans tbPlafonds la catégorie Repas avec un plafond de 50. Saisissez ensuite deux dépenses dans tbFrais : 20 puis 40 pour la catégorie Repas.

   Le Total catégorie doit afficher 60, le Dépassement doit afficher 10 et le Remboursable doit afficher 50. Si ce test échoue, vérifiez d’abord les libellés de catégorie et les plages du tableau.

Erreurs fréquentes & dépannage

La confusion la plus fréquente consiste à mélanger plafond par catégorie et plafond par ligne. Le modèle ci-dessus contrôle un total cumulé par catégorie, pas chaque dépense isolée.

Autre piège courant : une catégorie absente de la table des plafonds laisse le plafond vide. Dans ce cas, le registre ne peut pas décider correctement du dépassement ou du remboursement.

Enfin, la mise en forme conditionnelle peut se comporter différemment selon la référence utilisée. Si la règle ne colore rien, recréez-la avec des références de cellules classiques sur la première ligne du tableau.

Astuces & pour aller plus loin

Ajoutez un filtre sur la colonne Dépassement pour n’afficher que les anomalies. C’est le moyen le plus rapide de contrôler un grand volume de notes de frais.

Si vous devez suivre les dépenses par salarié, ajoutez ce critère dans SOMME.SI.ENS avec la colonne Collaborateur. Vous obtiendrez alors un plafond contrôlé par catégorie et par personne, à condition que cette règle corresponde à votre politique interne.

Questions fréquentes

Que faire si RECHERCHEX n’existe pas dans mon Excel ?

Votre version d’Excel ne prend probablement pas en charge cette fonction. Ce tutoriel ne détaille pas l’alternative, car elle dépend de la version exacte utilisée.

Puis-je calculer le dépassement par collaborateur et par catégorie ?

Oui, en ajoutant un critère supplémentaire dans SOMME.SI.ENS sur la colonne Collaborateur. Il faut alors que le plafond soit bien défini selon cette même règle de gestion.

Pourquoi le montant remboursable est-il identique sur plusieurs lignes ?

Parce que la formule proposée calcule un remboursable au niveau de la catégorie entière. Pour répartir un remboursement ligne par ligne, il faut ajouter une règle d’allocation précise.

Conclusion

Avec deux tableaux et trois outils d’Excel, vous obtenez un registre de frais lisible et semi-automatique. Commencez par valider votre logique de plafond sur un petit jeu d’essai, puis dupliquez le modèle pour votre suivi réel.