Excel : créer un suivi des acomptes sur commandes avec solde, échéances et statut

Difficulté

Intermédiaire

Le plus simple est de séparer les commandes et les règlements dans deux tableaux, puis de calculer automatiquement l’acompte encaissé, le solde à livrer et le solde à facturer. Avec SOMME.SI.ENS, RECHERCHEX et SI, vous obtenez un suivi exploitable sans saisies en double.

Ce montage fonctionne bien si chaque commande possède une référence unique. Il faut en revanche définir vous-même vos règles métier, par exemple le pourcentage d’acompte demandé ou la logique exacte du “solde à livrer”.

Prérequis

Préparez un fichier Excel avec au moins deux feuilles : Commandes et Règlements. Convertissez chaque plage en Tableau Excel avec Ctrl+T pour faciliter les formules et l’extension automatique.

Matériel nécessaire

• Un fichier Excel avec deux feuilles nommées Commandes et Règlements
• Une liste de commandes avec référence unique, client, montant et dates
• Une liste de règlements avec référence de commande, date et montant encaissé

Étapes

1. 1

   Créer la table des commandes

   

   Dans la feuille Commandes, créez un tableau avec au minimum ces colonnes : RefCommande, Client, MontantCommande, TauxAcompte, AcompteAttendu, DateCommande et DateAppelAcompte. Ajoutez aussi AcompteEncaissé, SoldeÀFacturer, SoldeÀLivrer et StatutRèglement.

   Dans AcompteAttendu, utilisez une formule simple comme =[@MontantCommande]*[@TauxAcompte]. Si vous travaillez en pourcentage, formatez TauxAcompte en % et les montants en devise.

2. 2

   Créer la table des règlements

   Dans la feuille Règlements, créez un second tableau avec RefCommande, DateRèglement, MontantRéglé et, si besoin, ModePaiement ou RéférencePièce. Chaque ligne doit représenter un encaissement réel.

   Cette structure permet de gérer les règlements partiels sans modifier la commande d’origine. Elle reste aussi plus fiable qu’une seule cellule “acompte payé”.

3. 3

   Calculer l’acompte encaissé avec SOMME.SI.ENS

   Dans la colonne AcompteEncaissé du tableau Commandes, saisissez une formule de ce type : =SOMME.SI.ENS(Règlements[MontantRéglé];Règlements[RefCommande];[@RefCommande]). Excel additionnera tous les règlements associés à la référence de la ligne.

   Si aucune ligne n’existe encore dans Règlements, le résultat reste à 0. C’est pratique pour repérer immédiatement les appels d’acompte non réglés.

4. 4

   Calculer le solde à facturer et le solde à livrer

   

   Dans SoldeÀFacturer, utilisez par exemple =[@MontantCommande]-[@AcompteEncaissé] si vous voulez suivre ce qu’il reste à facturer après encaissement de l’acompte. Si votre processus distingue acompte facturé et acompte encaissé, adaptez la formule à votre circuit comptable.

   Dans SoldeÀLivrer, la formule dépend de votre définition métier. Une base simple consiste à reprendre le montant restant après acompte, par exemple =[@MontantCommande]-[@AcompteEncaissé], mais si la livraison dépend d’autres données, il faudra une colonne supplémentaire dédiée.

5. 5

   Récupérer des informations avec RECHERCHEX

   Si vous créez une feuille de synthèse ou de relance, utilisez RECHERCHEX pour ramener les données de la commande à partir de la référence. Par exemple : =RECHERCHEX([@RefCommande];Commandes[RefCommande];Commandes[Client];"") pour afficher le client.

   Vous pouvez faire la même chose pour la date d’appel, le montant de commande ou l’acompte attendu. Le quatrième argument vide évite d’afficher une erreur si la référence est absente.

6. 6

   Définir l’échéance d’appel d’acompte

   Si l’acompte doit être demandé à une date fixe après commande, calculez DateAppelAcompte avec une formule comme =[@DateCommande]+7. Remplacez 7 par votre délai réel en jours.

   Si l’appel d’acompte dépend d’une date contractuelle ou d’un jalon, saisissez cette date manuellement. Excel ne peut pas déduire seul une règle absente de vos données.

7. 7

   Attribuer un statut de règlement avec SI

   Dans StatutRèglement, utilisez une formule imbriquée pour classer chaque commande. Un exemple courant est : =SI([@AcompteEncaissé]=0;"À appeler";SI([@AcompteEncaissé]<[@AcompteAttendu];"Partiel";"Réglé")).

   Vous pouvez aller plus loin avec la date du jour pour identifier les retards. Par exemple : =SI(ET([@AcompteEncaissé]=0;AUJOURDHUI()>[@DateAppelAcompte]);"En retard";SI([@AcompteEncaissé]=0;"À appeler";SI([@AcompteEncaissé]<[@AcompteAttendu];"Partiel";"Réglé"))).

8. 8

   Créer une feuille de suivi opérationnel

   

   Ajoutez une feuille Suivi si vous voulez une vue plus lisible pour la facturation ou les relances. Reprenez la référence de commande, le client, l’acompte attendu, l’acompte encaissé, la date d’appel et le statut.

   Dans cette feuille, utilisez RECHERCHEX pour récupérer les données depuis Commandes. Vous évitez ainsi les doubles saisies et les écarts entre tableaux.

9. 9

   Tester le fichier avec un exemple simple

   Prenez une commande C-2024-015 de 10 000 avec un acompte de 30 %. L’acompte attendu vaut alors 3 000.

   Si la table Règlements contient deux lignes de 1 000 et 500 pour cette même référence, SOMME.SI.ENS renverra 1 500. Le statut sera Partiel et le solde calculé selon votre formule restera de 8 500.

Erreurs fréquentes & dépannage

L’erreur la plus fréquente est d’utiliser des références de commande non homogènes, par exemple avec des espaces ou des variantes de format. Dans ce cas, SOMME.SI.ENS et RECHERCHEX ne retrouvent pas les bonnes lignes.

Un autre piège est de mélanger acompte attendu, acompte facturé et acompte encaissé. Si ces notions sont distinctes chez vous, créez une colonne pour chacune au lieu de forcer un seul calcul.

Évitez enfin de définir le solde à livrer sans règle claire. Si la livraison dépend de quantités, de jalons ou de BL, le suivi doit inclure ces données.

Astuces & pour aller plus loin

Ajoutez une mise en forme conditionnelle sur la colonne StatutRèglement pour colorer En retard en rouge et Partiel en orange. La lecture devient beaucoup plus rapide.

Vous pouvez aussi figer les volets et ajouter des filtres sur Client, DateAppelAcompte et StatutRèglement. Ces réglages suffisent souvent avant de passer à un tableau croisé ou à Power Query.

Questions fréquentes

Peut-on faire ce suivi dans un seul tableau ?

Oui, mais ce sera moins robuste dès qu’une commande reçoit plusieurs règlements. Séparer Commandes et Règlements simplifie les cumuls et limite les erreurs de saisie.

Que faire si RECHERCHEX n’est pas disponible dans ma version d’Excel ?

Il faut utiliser une alternative comme RECHERCHEV ou INDEX avec EQUIV. Le principe du tutoriel reste le même, mais les formules changent.

Comment suivre le solde à livrer en quantité plutôt qu’en montant ?

Ajoutez des colonnes dédiées aux quantités commandées et livrées, ou une table de livraisons séparée. Le montant encaissé ne suffit pas à mesurer l’avancement logistique.

Conclusion

Pour un suivi fiable, partez d’une référence de commande unique, enregistrez chaque encaissement dans une table dédiée, puis laissez SOMME.SI.ENS, RECHERCHEX et SI alimenter votre synthèse. Si votre entreprise distingue facturation, encaissement et livraison, formalisez d’abord ces règles avant de figer les formules.