Echéances de paiement des clients dans Excel

Pour partager cette vidéo sur les réseaux sociaux ou sur un site, voici son url :


Sujets que vous pourriez aussi aimer :


Calcul des échéances de paiement clients avec Excel


Cet exercice Excel propose de mettre en pratique différentes notions abordées dans de précédents supports de formation. Il s'agit d'un tableau listant des clients ayant une facture à régler selon le montant indiqué dans la colonne Facture. La colonne Versement représente l'état en cours du règlement. Nous pouvons constater que certains clients n'ont pas encore tout payé, que d'autres ont bien réglé le montant de la facture et que d'autres encore ont trop payé.

• Télécharger le classeur tableau-echeances-excel.xlsx en cliquant sur son lien,
• L'ouvrir dans Excel,

Le reste à payer
Le calcul que propose de réaliser cette colonne est on ne peut plus basique. Il s'agit d'une soustraction pour afficher la différence entre le montant de la facture et la montant versé.

• Sélectionner toutes les cellules de la colonne Reste soit F6:F16,
• Taper = pour débuter le calcul et sélectionner le premier montant de facture soit D6,
• Taper le symbole - pour la soustraction et sélectionner le premier montant versé soit E6,
• Valider le calcul par CTRL + Entrée.

Nous allons faire plusieurs remarques. Tout d'abord, la technique que nous avons employée permet de reproduire le calcul sur l'ensemble des cellules sans tirer la poignée. Pour ce faire, nous avons présélectionné toutes les cellules concernées. Et comme dans une plage de cellule, la cellule qui est active par défaut est la première de la sélection, nous avons réalisé le calcul pour cette cellule. Le raccourci clavier CTRL + Entrée permet de valider le calcul en le répliquant sur l'ensemble des cellules présélectionnées. Chaque référence de cellule dans le calcul est ainsi automatiquement déplacée, chaque fois une ligne plus bas, pour l'adapter au résultat de la ligne du dessous. Le support sur les raccourcis clavier d'Excel propose ce raccourci et bien d'autres. Nous obtenons des résultats positifs indiquant que les clients n'ont pas totalement réglé la facture. D'autres sont négatifs indiquant que certains ont trop payé. Et d'autres enfin sont nuls, indiquant que ces clients se sont acquittés de la facture. Ces résultats synthétiques vont nous permettre par la suite de poser des critères pour enclencher certaines actions ou observations. Vous constatez enfin que les résultats négatifs sont automatiquement mis en forme et ressortent du lot. Cette mise en forme n'est pas vraiment adaptée, il serait plus logique de faire apparaître les mauvais payeurs en rouge et les trop bons en vert. Nous allons commencer par supprimer la mise en forme conditionnelle existante :

• Sélectionner de nouveau toutes les cellules du reste, F6:F16,
• En bas de la sélection, cliquer sur le petit bouton carré, il s'agit d'une balise active,
• Dans les choix proposés, cliquer sur le dernier bouton Annuler la mise en forme,

Une balise active propose des actions contextuelles par rapport à la sélection. Elle permet de gagner du temps en évitant de passer par les rubans pour retrouver la fonctionnalité appropriée. Quoiqu'il en soit, la mise enforme conditionnelle a bien été effacée. On parle de mise en forme conditionnelle car les cellules ressortent du lot lorsqu'elles correspondent à un critère. Les cellules du reste étant toujours sélectionnées :

• Cliquer sur le bouton Mise en forme conditionnelle du ruban Accueil,
• Dans la liste, choisir Nouvelle règle tout en bas,
• Dans la boîte de dialogue, choisir le dernier type Utiliser une formule pour...,

L'objectif est d'afficher en rouge et en gras tous les restes à payer positifs.

• Cliquer dans la zone de saisie pour taper la formule du critère,
• Saisir le symbole = et cliquer sur la première cellule du reste soit F6,

Vous remarquez qu'elle apparaît encadrée de dollars. Cela signifie qu'elle est figée. On parle de référence absolue. Nous ne souhaitons pas qu'elle soit figée puisque ce critère doit être vérifié pour chaque cellule du reste à payer. La formation sur les références absolues explique pourquoi cette notion est importante dans Excel.

• Enfoncer plusieurs fois la touche F4 du clavier jusqu'à ce que les dollars disparaissent,
• Taper ensuite la fin du critère >0,
• Puis, cliquer sur le bouton Format,

Dans la nouvelle boîte de dialogue choisir une couleur rouge et une police grasse,

Valider ces réglages afin de revenir sur le classeur.

Toutes les cellules dont le reste est positif apparaissent automatiquement en rouge. Il s'agit d'une très bonne méthode afin d'isoler rapidement les mauvais payeurs. De plus, la mise en forme conditionnelle est dynamique si bien que si la facture venait à être soldée, le reste passerait à 0, ne correspondrait plus au critère et perdrait sa mise en forme rouge. Les mises en forme conditionnelles peuvent se cumuler, nous allons en profiter pour faire ressortir les trop bons payeurs en vert.

• Sélectionner toutes les cellules du reste,
• Cliquer sur le bouton Mise en forme conditionnelle et choisir Nouvelle règle,
• Comme précédemment, choisir le dernier type Utiliser une formule pour...,
• Taper la formule =F6<0 et cliquer sur le bouton Format pour attribuer du vert gras,
• Valider ces réglages.

Sachez qu'à tout moment il est possible d'afficher les règles de mise en forme conditionnelle en vigueur sur une plage de cellules. Pour cela, après avoir préalablement sélectionné les cellules, vous cliquez sur le bouton Mise en forme conditionnelle du ruban et vous choisissez Gérer les règles tout en bas de la liste.

Observations sur l'état en cours du paiement
Dans cette colonne nous devons inscrire des informations différentes selon le cas rencontré. C'est d'ailleurs ce qu'indique l'encadré en bas du tableau. Dans la colonne Observations doit apparaître le texte Relance si le client n'a pas tout payé, Avoir s'il a trop payé et Soldé s'il a tout payé. C'est la fonction Si() d'Excel qui permet de poser des critères pour envisager des actions différentes selon que la condition est vérifiée ou non. =Si(Critere_verifie ;Alors_action1 ;Sinon_action2). Elle requiert trois arguments. Le premier est le critère à vérifier (Reste>0 par exemple). Le deuxième est l'action lorsque le critère est vérifié (Ecrire le texte Relance par exemple). Le troisième est l'action à envisager lorsque la condition n'est pas satisfaite (Ecrire le texte Avoir par exemple). La formation Excel sur les fonctions Si explique en détail leur syntaxe et fonctionnement. Comme ici, nous devons envisager trois cas, une seule fonction Si n'est pas suffisante. Nous allons imbriquer deux fonctions Si pour envisager chaque observation.

• Sélectionner toutes les cellules de la colonne Observations,
• Saisir la formule suivante : =SI(F6>0;'Relance';SI(F6<0;'Avoir';'Soldé')),

Traduction : (=SI(F6>0;) : si les reste à payer est positif, (;'Relance') alors nous inscrivons le texte Relance dans la cellule. Comme il s'agit d'un texte, nous le saisissons entre guillemets. (;SI(F6<0;) Sinon si le reste à payer est négatif, (;'Avoir') alors nous inscrivons le texte Avoir car le client a trop payé, (;'Soldé') sinon nous inscrivons le texte Soldé. Ce dernier sinon sous-entend que les deux premiers critères n'ont pas été vérifiés. Si le reste n'est ni positif ni négatif, c'est qu'il est forcément nul. Le dernier critère ne se pose donc pas, il se déduit des précédents.


Vous obtenez les observations attendues dans la colonne. Ce sont elles qui indiquent quelle mesure entreprendre en cas de mauvais paiement ou de paiement trop important. Pour que ces indications ressortent, il convient de leur appliquer comme précédemment une mise en forme conditionnelle.

• Avec la mise en forme conditionnelle, faire ressortir les textes Relance en rouge et Avoir en vert,

Attention, cette fois-ci, le critère n'est pas numérique, il doit vérifier que la cellule est égale au texte qui précisément justifie la mise en forme.


Les pénalités en cas de retard de paiement
Dans cette dernière colonne, nous devons appliquer une pénalité de 5% sur le montant total de la facture, si le client est un mauvais payeur. Le calcul doit se baser sur la valeur en pourcentage située en H5. Comme cette cellule est une cellule de référence pour l'ensemble de la plage Pénalités, elle doit être figée. Le calcul est donc le suivant, sur la base de la première cellule : =D6*$H$5. Mais cette pénalité ne doit pas être calculée pour les clients en règle. Cela signifie qu'elle doit être intégrée dans une fonction Si pour s'assurer que le client est un mauvais payeur. Si(Reste>0 ;Pénalité ;Ne_rien_faire). Ne rien faire peut se traduire pas deux guillemets (''). On écrit un texte vide dans la cellule.

• Sélectionner toutes les cellules de la colonne Pénalités,
• Poser le calcul dans la fonction Si appropriée et valider par CTRL +Entrée,

Une fois encore, grâce à la fonction Si(), nous sommes en mesure d'adapter les résultats aux conditions rencontrées. Il ne reste plus qu'à calculer les sommes pour les Restes à payer et les pénalités. Voyons deux méthodes différentes mais aussi efficaces.

• Sélectionner la cellule F17,
• Au clavier, réaliser le raccourci Alt + = pour déclencher la somme,
• Valider la proposition d'Excel par la touche Entrée.

Comme toutes les cellules de la colonne du reste ont une valeur, la somme ainsi déclenchée propose de toutes les intégrer dans le calcul. A validation, vous obtenez le résultat de l'addition dans la cellule. Le contexte est différent pour la colonne Pénalités qui dispose de cellules vides. Le Alt + = fonctionnerait mais nécessiterait de réajuster la plage de cellules à sommer, soit une action supplémentaire. Pour pallier le problème :

• Sélectionner toutes les cellules de la colonne Pénalités avec en plus, la cellule du total soit H17,
• Réaliser la combinaison Alt + = ou cliquer sur le bouton Somme automatique du ruban,

Vous obtenez bien la somme des pénalités dans la cellule du total. Le fait d'avoir présélectionné toutes les cellules à additionner avec la cellule du total, lève l'ambiguïté sur les valeurs à intégrer dans le calcul. Et la somme livre naturellement le résultat dans la dernière cellule de la plage ainsi volontairement présélectionnée.