Cet exercice consiste à mettre en forme et manipuler les données concernant les dépenses mensuel d’un foyer. Il s’agira d’utiliser quelques fonctions dont une conditionnelle.
Téléchargez le fichier de travail en cliquant sur le bouton suivant:
Pour télécharger un fichier, faites un clic droit sur le fichier désiré et choisissez l’option Télécharger.
Pour télécharger plusieurs fichiers, sélectionnez les fichiers désirés puis cliquez sur Télécharger les sélectionnés. Les fichiers seront téléchargés sous la forme d’une archive .zip. Veillez à bien extraire le contenu de l’archive avant de commencer à travailler.
Insérez une nouvelle colonne avant la colonne Loyer.
Sélectionnez la colonne A, faites un clic droit sur la sélection et choisissez l’option Insérer.
Créer la colonne mois et remplissez-la avec les mois de l’année (sans les saisir un par un).
Saisissez les mots « Mois » et « Janvier ». Sélectionnez la cellule contenant le mot « Janvier » et double-cliquez sur la poignée de recopie.
Utilisez les formules adaptées pour créer les totaux des dépenses par mois et les totaux des dépenses annuelles pour chaque catégorie.
Il suffit d’utiliser la fonction =SOMME().
Pour les totaux mensuels, placez-vous dans la cellule F5, saisissez la formule =SOMME(B5:E5) et validez avec la touche Entrée. Reproduisez la formule pour tous les mois en double cliquant sur la poignée de recopie.
Pour les totaux annuels, placez-vous dans la cellule B17, saisissez la formule =SOMME(B5:B16) et validez avec la touche Entrée. Reproduisez la formule pour toutes les catégories en saisissante la formule et en la faisant glisser horizontalement.
Formatez les dépenses pour les afficher avec le symbole €, un séparateur de milliers et aucun chiffre après la virgule.
Sélectionnez la plage de cellules contenant les valeurs numériques, faites un clic droit sur la sélection et choisissez l’option Format de cellule….
Vous vous apercevez qu’il y a toujours une différence entre ce que vous calculez et la réalité. Vous souhaitez donc intégrer un % d’ajustement aux totaux.
Complétez le tableau pour pouvoir saisir un taux d’ajustement de votre choix et l’utiliser pour recalculer le total par mois comme dans l’illustration ci-dessous.
Le calcul à effectuer pour le mois de janvier est :
1200 + 1200 * 5% qui équivaut à 1200 + 1200 * 0,05
C’est-à-dire la dépense mensuelle à laquelle nous ajoutons la valeur d’ajustement.
Une fois que vous savez ce que vous devez calculer, la difficulté de cette question réside dans l’utilisation d’une référence absolue. Une référence absolue ne se met pas à jour lors d’une phase de recopie.
La formule à saisir dans la cellule G5 est la suivante : =F5+F5*$G$3
Pour pouvoir recopier cette formule tout en conservant la référence à la cellule G3, il est donc impératif d’utiliser une référence absolue $G$3.
En dessous de la ligne 17, utilisez les formules adaptées pour afficher les valeurs maximales et minimales de l’année pour chaque colonne.
Les fonctions MAX() et MIN() renvoient respectivement la valeur maximales et minimales d’un ensemble de valeurs.
La formule à saisir dans la cellule B18 est =MAX(B5:B16).
La formule à saisir dans la cellule B19 est =MIN(B5:B16).
Ajoutez votre salaire mensuel comme dans l’illustration ci-dessous.
Puis pour chaque mois utilisez une formule pour afficher le mot « Gain » si vous avez dépensé moins ou l’équivalent de votre revenu et le mot « Perte » si vous avez dépensé plus que votre revenu.
La fonction conditionnelle est la fonction SI().
Il s’agit pour cette question d’utiliser la fonction conditionnelle SI().
Cette fonction contient jusqu’à trois arguments SI(Test à effectuer ; si résultat est vrai ; si le résultat est faux ) .
La formule à saisir pour la cellule H5 est donc la suivante : =SI($H$3-G5<0;"Perte";"Gain")
Cette formule peut être décrite de la manière suivante :
- Si le test (ou la condition) : le salaire
$H$3moins le total ajustéG5est inférieur à 0 ? - Est vrai : on affiche le texte « Perte » ;
- Est faux : on affiche le texte « Gain ».
Vous noterez que pour afficher du texte, il est nécessaire de le placer entre guillemets !
Modifiez la formule précédente afin d’ajouter le montant du gain ou de la perte.
Pour mettre bout à bout deux éléments formules, références ou texte, il faut utiliser l’opérateur &. Cette opération s’appelle une concaténation.
Exemple de concaténation d’une référence, d’un texte et d’une fonction :
=B20&" est supérieur à "&SOMME(A1:A3)
La formule à saisir dans la cellule H5 peut s’écrire de plusieurs manières :
=SI($H$3-G5<0 ; "Perte : "&$h$3-G5 ; "Gain : "&$H$3-G5)
Ou de manière plus lisible, la conditionnelle sert uniquement à définir le texte, le montant est affiché en dehors :
=SI($H$3-G5<0 ; "Perte" ; "Gain")&" : "&$H$3-G5
Effectuez une dernière modification de la formule précédente afin de n’afficher que des valeurs positives. Une perte de -12,5 correspondrait finalement à un gain. Profitez-en pour ajouter le symbole €.
Pour transformer un nombre négatif en nombre positif, il suffit de le multiplier par -1. Par exemple : -12 * -1 = 12
Vous pouvez donc utiliser une conditionnelle sous la forme =SI(A1<0;A1*-1;A1).
Un moyen quand même bien plus simple est d’utiliser la fonction =ABS() qui est justement conçue pour cet usage.
La formule à saisir dans la cellule H5 est donc :
=SI($H$3-G5<0; "Perte"; "Gain")&" : "&ABS($H$3-G5)&" €"
Attention à ne pas utiliser la fonction ABS() pour le test sinon le test renverra toujours FAUX.
L’ajout du symbole € s’effectue avec une simple concaténation.
Complétez la cellule H17 pour avoir la même chose qu’au-dessus mais pour l’année.
Il suffit pour cette cellule de multiplier le salaire par 12.
La formule à saisir dans la cellule H17 est donc : =SI($H$3*12-G17<0; "Perte"; "Gain")&" : "&ABS($H$3*12-G17)&" €"
Sauf mention contraire*, l'article Gestion des dépenses : fonctions simples et conditionnelles [TAB07] et son contenu par Julien Crego sont mis à disposition selon les termes de la licence Creative Commons
Attribution - Pas d’Utilisation Commerciale - Partage dans les Mêmes Conditions 4.0 International
Cette licence vous permet de remixer, arranger, et adapter cette œuvre à des fins non commerciales tant que vous créditez la source en citant le nom des auteurs et que les nouvelles œuvres sont diffusées selon les mêmes conditions.
* Cette notice est intégrée automatiquement à la fin de chaque article de ce site.