TABLEAU D‛ÉCHÉANCES – PRATIQUE FONCTION SI
Nous proposons un exercice relativement simple permettant de pratiquer les fonctions condition-nelles SI et leurs imbrications. Ici, nous travaillons en valeurs statiques pour nous concentrer sur la construction de ces formules. Dans un autre cas pratique nous combinerons l‛utilisation des fonctions SI et des références absolues.
Vous trouverez le classeur dans le dossier Sources.
Il s‛agit d‛un tableau d‛échéances des paiements de factures de clients recensés dans la première colonne. Apparaissent pour chacun d‛entre eux le montant de la facture à payer et l‛état en cours de leur versement. Trois cas se présentent : Certains clients ont réglé leur facture, d‛autres n‛ont pas tout payé et d‛autres encore ont trop payé. C‛est pourquoi après avoir calculé le reste à payer, l‛exercice propose d‛ajouter le commentaire soldé, ou avoir ou relance dans la colonne Observa-tions selon le cas. Enfin, les mauvais payeurs devront être pénalisés à hauteur de 5% du montant de la facture dans la colonne Pénalités.
CALCUL DU RESTE – SOUSTRACTION
Le reste à payer est la différence entre le montant de la facture et le montant versé :
- Sélectionnez toutes les cellules du reste (E5 à E15),
- Tapez = pour initialiser le calcul,
- Cliquez sur la première cellule de la facture (C5),
- Tapez le symbole moins (–) pour la soustraction,
- Cliquez sur la première cellule du versement (D5),
- Validez par CTRL + Entrée pour reproduire le calcul,
Vous obtenez tous les restes à payer en regard des clients. Certains sont positifs, d‛autres né-gatifs et d‛autres encore sont nuls. Si des symboles dièse (#) apparaissent, cela signifie que la largeur de colonne est insiffisante pour afficher l‛intégralité des valeurs numériques. Il ne s‛agit pas d‛une erreur.
● Placez vous à l’intersection des étiquettes de colonne E et F,
Le curseur de la souris se transforme en une double flèche noire.
● Double cliquez sur cette intersection.
Vous remarquez que la colonne est désormais suffisamment large. Le double clique à l‛intersection des étiquettes est une méthode qui per-met d‛ajuster les colonnes et les lignes à leur contenu.
Excel 2007 Tableau d‛échéances – Fonction Si et imbrication
OBSERVATION – PROBLÈME – FONCTION SI
Nous devons résoudre le problème de la colonne observation. Nous devons envisager trois cas :
- Mauvais payeur ==> Relance,
- Trop payé ==> Avoir,
- Paiement exact ==> Soldé.
Comme nous l‛avions expliqué dans le support de présentation des fonctions SI à cette adresse : http://www.assistance-en-informatique.com/fonction-si-calcul-condition_122.aspx, si n cas se présentent, nous devons utiliser n-1 fonctions si. Donc ici nous avons besoin de 3-1=2 fonctions SI. Pour savoir si le client est un bon ou mauvais payeur, la solution la plus simple consiste à regarder si le reste à payer est négatif ou positif.
- Sélectionnez toutes les cellules de la colonne Observations (F5 à F15),
- Tapez = pour lancer le calcul,
- Tapez SI et ouvrez la parenthèse,
- Sélectionnez la première cellule du reste (E5),
- Tapez >0 pour savoir si c’est un mauvais payeur,
- Tapez un point virgule (;),
Nous venons de poser le critère à vérifier si le reste à payer est positif ce qui signifierait que le client est un mauvais payeur (Relance).
- Tapez alors le texte Relance entre guillemets,
- Tapez un point virgule (;),
Nous arrivons dans la partie Sinon de la fonction SI. Comme il reste deux possibilités (Avoir et Soldé), une seule fonction Si est insuffisante, nous devons en imbriquer une autre.
- Tapez de nouveau SI et ouvrez la parenthèse,
- Sélectionnez la première cellule du reste (E5),
- Tapez <0 pour savoir si c’est un mauvais payeur,
- Tapez un point virgule (;),
Nous venons poser le deuxième critère qui, s‛il est vérifié signifie que le client a trop payé et que nous lui devons de l‛argent (Avoir).
- Tapez alors le texte Avoir entre guillemets,
- Tapez un point virgule (;),
Nous arrivons dans la partie SINON de la deuxième fonction SI. Si la lecture de la formule parvient jusqu‛à ce point, cela signifie que ni le premier critère n‛est vérifié, ni le second. Si le reste n‛est ni positif, ni négatif, par déduction, il est forcément nul (Soldé).
- Tapez le texte Soldé entre guillemets,
- Fermez les deux parenthèses des deux fonctions SI, ●Validez par CTRL + Entrée pour reproduire le calcul.
Excel 2007 Tableau d‛échéances – Fonction Si et imbrication
La formule finale est la suivante : =SI(E5>0; »Relance »;SI(E5<0; »Avoir »; »Soldé »)).
Vous pouvez noter que les observations sont cohérentes et surtout dynamiques puisqu‛il suffirait par exemple qu‛un mauvais payeur ajoute la différence pour que son statut passe à Soldé.
CALCUL DES PÉNALITÉS, SI…
Tous les clients ne doivent pas être pénalisés, mais seulement les mauvais payeurs. Les mauvais payeurs ont un reste positif. C‛est donc le critère le plus simple que nous pouvons poser pour sa-voir si nous calculons le montant de la pénalité ou non. C‛est tout naturellement que la fonction SI s‛impose à nous. Et pour une fois nous allons intégrer un calcul dans la fonction conditionnelle.
- Sélectionnez toutes les cellules de la colonne Pénalités (G5 à G15),
- Tapez = pour initialiser le calcul,
- Tapez SI et ouvrez la parenthèse,
- Cliquez sur la première cellule du reste (E5),
- Tapez >0 pour savoir si c’est un mauvais payeur,
- Tapez un point virgule (;)
- Cliquez sur la cellule de la facture (C5),
- Puis tapez *0,05,
Pas de guillemets ici car si le critère est vrai nous calculons directement le montant de la pénalité en multipliant (*) le montant de la facture (C5) par 5%(0,05).
● Tapez un point virgule (;),
Nous arrivons au sinon de la fonction SI. Cette branche doit toujours être remplie, sous peine de générer une erreur, même si a priori il n‛y a rien à faire. Ici il faut expliquer que dans le cas contraire (Bon payeur), il faut laisser la cellule vide, ce qui se traduit par deux guillements. Les guillemets permettent d‛insérer du texte. S‛il n‛y a rien entre le guillemet ouvrant et fermant, le résultat est une cellule vide.
- Ouvrez et fermer les guillemets,
- Fermez la parenthèse de la fonction SI,
- Validez le calcul par CTRL + Entrée.
Seuls les mauvais payeurs sont en effet pénalisés.
Enfin, vous pouvez éventuellement utiliser la fonction Nb.Si pour compter le nombre de mauvais payeurs en bas de la colonne Observations. L‛explication sur cet-te fonction de dénombrement est donnée sur le même support présentant la fonction SI