Excel : suivre des règlements partiels de factures avec reste dû et statut automatique
Créez dans Excel un suivi automatique des paiements partiels par facture, avec reste dû, ventilation par client et statut via SOMME.SI.ENS, RECHERCHEX et SI.
- Difficulté
- Intermédiaire
Le plus simple est de séparer les factures et les encaissements dans deux tableaux Excel, puis de calculer automatiquement le total payé, le reste dû et le statut. Avec SOMME.SI.ENS, RECHERCHEX et SI, vous obtenez un suivi fiable des règlements partiels sans ressaisie.
Ce tutoriel montre une structure facile à maintenir : une ligne par facture dans le tableau principal, et une ligne par paiement dans le tableau des encaissements. Les formules proposées fonctionnent si chaque facture possède un identifiant unique.
Prérequis
Préparez un classeur avec deux feuilles ou deux zones distinctes : Factures et Encaissements. Convertir chaque plage en Tableau Excel (Ctrl+T) est recommandé pour recopier les formules proprement.
Les noms de fonctions ci-dessous sont donnés en français. RECHERCHEX est disponible dans les versions récentes d’Excel ; si votre version ne l’intègre pas, il faudra une autre méthode, non détaillée ici.
Matériel nécessaire
- Un tableau Excel des factures avec N° de facture, client, date et montant TTC
- Un tableau Excel des encaissements avec date, N° de facture et montant encaissé
- Excel avec SOMME.SI.ENS, SI et idéalement RECHERCHEX
Étapes
Créer le tableau des factures
Créez un premier tableau nommé par exemple Factures avec ces colonnes : No_Facture, Client, Date_Facture, Montant_Facture, Total_Encaisse, Reste_Du et Statut. Saisissez une ligne par facture, avec un numéro unique par document.
Cette table devient votre référence. Le montant de facture ne doit pas être répété ailleurs pour éviter les écarts.
Créer le tableau des encaissements
Créez un second tableau nommé Encaissements avec au minimum : Date_Encaissement, No_Facture, Montant_Encaisse et Client. Saisissez une ligne par règlement, même si plusieurs paiements concernent une seule facture.
Cette structure gère naturellement les paiements partiels. Elle convient aussi aux paiements fractionnés sur plusieurs dates.
Rapatrier le client avec RECHERCHEX
Dans la colonne Client du tableau Encaissements, utilisez RECHERCHEX pour retrouver automatiquement le client à partir du numéro de facture. La formule type est : =RECHERCHEX([@No_Facture];Factures[No_Facture];Factures[Client];"").
Chaque encaissement récupère ainsi le bon client sans ressaisie manuelle. Si le numéro de facture est introuvable, la formule renvoie ici une cellule vide.
Calculer le total encaissé par facture
Dans la colonne Total_Encaisse du tableau Factures, additionnez tous les paiements correspondant au numéro de facture. Utilisez par exemple : =SOMME.SI.ENS(Encaissements[Montant_Encaisse];Encaissements[No_Facture];[@No_Facture]).
La formule cumule automatiquement tous les règlements partiels d’une même facture. Une facture sans paiement renverra 0.
Calculer le reste dû
Dans la colonne Reste_Du, soustrayez le total encaissé au montant de facture. Utilisez une formule simple comme : =[@Montant_Facture]-[@Total_Encaisse].
Vous obtenez en un coup d’œil le solde à encaisser sur chaque facture. Si le montant devient négatif, cela signale un trop-perçu ou une erreur de saisie.
Définir le statut soldé ou partiellement payé
Dans la colonne Statut, utilisez SI pour afficher l’état de la facture selon le montant encaissé. Une formule pratique est : =SI([@Total_Encaisse]=0;"Non payé";SI([@Reste_Du]<=0;"Soldé";"Partiellement payé")).
Le statut se met à jour dès qu’un nouvel encaissement est ajouté. Vous distinguez ainsi les factures non réglées, en cours de règlement et soldées.
Ventiler les encaissements par client
Pour connaître le total encaissé par client, créez une petite synthèse avec une colonne Client et une colonne Total encaissé. Dans cette seconde colonne, utilisez : =SOMME.SI.ENS(Encaissements[Montant_Encaisse];Encaissements[Client];A2) si A2 contient le nom du client à analyser.
Vous pouvez aussi ajouter d’autres critères, par exemple une période, si votre synthèse contient des dates de début et de fin. Le principe reste le même : chaque critère s’ajoute dans SOMME.SI.ENS.
Tester le modèle avec un exemple simple
Prenez une facture F-2024-015 de 1 000 pour le client Durand. Saisissez ensuite deux encaissements sur cette facture : 300 puis 450.
Le Total_Encaisse doit afficher 750, le Reste_Du doit afficher 250 et le Statut doit devenir Partiellement payé. Après un troisième paiement de 250, le statut doit passer à Soldé.
Erreurs fréquentes & dépannage
Confondre numéro de facture et référence client fausse les rapprochements. La clé de calcul doit rester le No_Facture.
Saisir manuellement le client dans les encaissements crée des variantes d’orthographe et perturbe la ventilation. Utilisez plutôt RECHERCHEX pour harmoniser la donnée.
Écraser un ancien paiement au lieu d’ajouter une nouvelle ligne fait perdre l’historique. Un règlement partiel doit toujours être enregistré comme un nouvel encaissement.
Astuces & pour aller plus loin
Ajoutez une mise en forme conditionnelle sur la colonne Statut pour colorer Non payé, Partiellement payé et Soldé. Le suivi devient plus lisible sans changer les formules.
Si vous travaillez beaucoup par client, créez un tableau croisé dynamique à partir du tableau Encaissements. Il complète bien la formule SOMME.SI.ENS pour des vues rapides.
Questions fréquentes
Peut-on suivre plusieurs paiements pour une même facture ?
Oui. C’est précisément l’intérêt du tableau Encaissements avec une ligne par règlement et de la formule SOMME.SI.ENS qui cumule tous les montants liés au même No_Facture.
Pourquoi utiliser RECHERCHEX si le numéro de facture suffit au calcul ?
Le calcul du total par facture peut se faire sans la colonne client dans les encaissements. RECHERCHEX devient utile pour ventiler les paiements par client sans ressaisie ni divergence de libellé.
Que faire si une facture est surpayée ?
Le Reste_Du devient négatif avec la formule proposée. Utilisez ce signal pour vérifier un doublon, un avoir non pris en compte ou un trop-perçu à traiter.
Conclusion
Avec cette structure en deux tableaux, Excel calcule automatiquement les règlements partiels, le reste dû et le statut de chaque facture. Commencez par fiabiliser les numéros de facture, puis saisissez chaque encaissement sur une ligne distincte : tout le suivi en dépend.