Exercice générique sur les bonnes pratiques de l’outil tableur : suivi de l’évolution d’un budget annuel.
Construire un tableau simple d’étude d’un budget annuel
Placez-vous en B2,
tapez l’en-tête de colonne : mois,
tapez le premier mois en B3 : janvier :-),
incrémentez la cellule B3 jusqu’au mois de décembre, cellule B14,
placez-vous en C2,
tapez l’en-tête (ou étiquette) de colonne : budgets,
entrez les sommes allouées pour chaque mois de l’année, 5000 par mois,
sélectionnez de C3 à C14 et cliquez sur l’icône des “pièces d’or”, Format numérique : monnaie, dans la barre de mise en forme. Vos "5000" deviennent "5 000,00 €" !,
placez-vous en D2,
tapez l’en-tête de colonne : dépenses,
entrez, dans les cellules, les chiffres suivants :
3665
3789
4567
7994
5673
4667
7990
3666
3050
8334
3245
3256
sélectionnez de D3 à D14 et appliquez le format monnaie,
faites un Ctrl+S pour enregistrer votre travail.
Calculer les sommes
Placez-vous en B16,
tapez l’en-tête de ligne : total,
sélectionnez les cellules de la C3 à la C16,
observez le calcul réalisé dans la barre d’état placée tout en bas du tableur ; vous avez votre aperçu de somme rapide,
cliquez sur le signe Somme (E grec) qui se trouve dans la barre de formule (à peu près au-dessus de la colonne B) ; votre somme se calcule automatiquement dans la dernière cellule vide sélectionnée, la cellule C16, "60 000 €", le format numérique est déduit des cellules additionnées,
incrémentez la cellule C16 sur la droite pour demander l’addition de la colonne "dépenses",
faites un Ctrl+S pour enregistrer (ne jamais faire confiance à l’informatique !).
Vous obtenez ce début de tableau budget-1 :
- budget-1
Calculer l’évolution des dépenses
Placez-vous en E2,
tapez l’en-tête de colonne : cumul,
entrez la formule =D2,
validez. La sélection passe en E4 et le résultat donne le chiffre contenu en D2,
entrez en E4 la formule =E3+D4 (les dépenses du mois de février + les dépenses comptabilisées jusqu’à présent, la cellule au-dessus),
validez cette entrée de formule, observez le résultat,
incrémentez la cellule E4 en double-cliquant avec la souris lorsque vous avez fait apparaître le petit "+" ; les calculs se font jusqu’à la cellule E14, c’est-à-dire jusqu’en décembre, dernier mois de l’année,
remarquez, pour vérification, que le dernier cumul de l’année doit être égal au total des dépenses de l’année.
Calculer les écarts entre les budgets
Placez-vous en F2,
tapez l’en-tête de colonne : écarts,
entrez en F3 la formule =C3-D3 (le budget du mois - les dépenses du mois),
validez. Le mois de janvier est un mois positif,
placez-vous à nouveau sur la cellule F3 et incrémentez cette cellule ; la formule fera le calcul sur chaque ligne, pour chaque mois,
remarquez que le tableur fait apparaître en rouge les chiffres négatifs,
sélectionnez la plage de données de F3 à F14, lisez la somme dans la barre d’état située dans le bas du tableur, vous obtenez un résultat sur l’année,
placez-vous en F16, cliquez sur le signe Somme ; le tableur vous propose une plage de données pour cette somme, la plage de F3 à F15, et inscrit la formule =SOMME(F3:F15) dans la cellule. Vous pouvez valider en pressant la touche Entrée (du pavé numérique ou du clavier alphanumérique). La somme est alors calculée dans la cellule F16, la sélection passe dessous en F17.
Calculer l’évolution des dépenses en pourcentages cumulés
Placez-vous en G2,
tapez l’en-tête de colonne : % cumulés,
entrez en G3 la formule =E3/E$14 pour le pourcentage des dépenses du mois de janvier. Notez que nous ne multiplions pas par 100 et que le diviseur se voit ajouter un $ devant sa ligne,
validez la formule avec la touche Entrée,
revenez sur la cellule G3 et appliquez le Format numérique : pourcentage, l’icône % située juste à côté de celle des “pièces d’or”, le Format numérique : monnaie,
remarquez que vous obtenez la multiplication par 100 en même temps que le signe pourcentage,
incrémentez cette cellule G3 ; l’évolution du cumul doit logiquement vous donner un 100% dans la cellule G14 du dernier mois de l’année,
notez que ce calcul vous permet de poser le mois médian, c’est-à-dire le mois de juin, ainsi que les quartiles : nous passons les 25% du budget de l’année en avril, les 50% en juin, et les 75% en septembre.
Calculer les dépenses en pourcentage par mois
Placez-vous en H2,
tapez l’en-tête de colonne : % par mois,
entrez en H3 la formule =D3/D$16 pour le pourcentage des dépenses du mois de janvier ; D3, D16 étant la cellule contenant la somme des dépenses de l’année. Notez que nous ne multiplions toujours pas par 100 :-),
validez la formule avec la touche Entrée,
revenez sur la cellule H3 et appliquez le Format numérique : pourcentage,
incrémentez la cellule H3 en double-cliquant avec la souris lorsque vous avez fait apparaître le petit “+”,
placez-vous dans la cellule H16, cliquez sur le signe Somme, faites entrée sur la clavier, le résultat de l’addition des pourcentages sur les douze mois de l’année doit être égal à 100%.
Vous devriez obtenir ce tableau budget-2 :
- budget-2
Optimiser la largeur des colonnes en fonction des contenus
Cliquez sur la lettre B de la colonne B ; vous sélectionnez alors toute la colonne B,
restez appuyé sur le bouton gauche de la souris depuis la lettre B et glissez jusqu’à la colonne H ; vous venez de sélectionner les colonnes B C D E F G H,
placez votre souris entre deux lettres de colonnes ; la flèche de la souris change alors en une double flèche de directions opposées, cela signifie que vous vous apprêtez à jouer sur la largeur de toutes les colonnes sélectionnées,
faites un double-clic, toutes les colonnes s’ajustent en largeur en fonction de leurs contenus.
Changer l’ordre des colonnes du tableau
Déplacez la colonne G des "% cumulés" après la colonne E du "cumul". Pour cela :
sélectionnez la colonne G en cliquant sur le G de la colonne G :-),
saisissez, avec la souris, la sélection bleue claire de la colonne, n’importe où dans la colonne, mais en appuyant constamment sur le clic de la souris. Déplacez-la, ainsi vous pouvez la voir bouger avec un petit rectangle à sa base. Vous devez lui indiquer où déplacer la colonne,
appuyez, avec l’index :-) de la main gauche, sur la touche Alt située à gauche de la barre d’espace,
déplacez votre sélection bleue,
placez la souris sur la colonne F contenant les calculs des écarts,
lâchez la sélection en relâchant le bouton gauche de la souris. Vous venez de faire un glisser-insérer de la colonne des "% cumulés" entre la colonne du "cumul" et la colonne des "écarts",
placez maintenant les "% par mois" juste après la colonne des dépenses par mois. Il s’agit de la même démarche :
cliquez sur le H de la colonne H, toute la colonne est sélectionnée,
saisissez cette sélection bleue,
déplacez vers la gauche la sélection avec la souris,
appuyez sur la touche Alt,
placez-vous sur la colonne E des cumuls,
maintenez toujours la touche Alt,
lâchez la souris, bravo !
Mettre en forme rapidement le tableau
Sélectionnez le tableau entièrement depuis la cellule B2 jusqu’à la cellule H16,
cliquez sur le menu Format, puis AutoFormat...,
choisissez le type de présentation “Lavande”,
appuyez sur Options (le bouton qui se transforme en Moins), dans le bas à droite, puis décochez Format numérique pour ne pas l’utiliser, ainsi que Ajuster la largeur / hauteur pour ne pas avoir à changer les largeurs de vos colonnes,
validez en faisant OK,
optimiser les largeurs des colonnes si vous obtenez des dièses ### dans les cellules, car cela signifie que la colonne n’est pas assez large pour le contenu,
pensez toujours à prendre des couleurs claires (voire aucune couleur) pour limiter l’utilisation des toners d’imprimantes.
Vous devriez obtenir ce tableau budget-3 :
- budget-3
Calculer la moyenne des colonnes
Placez-vous sur la cellule B17, sous l’étiquette "total",
entrez l’étiquette de ligne : moyenne,
placez-vous sur la cellule C17,
tapez directement la formule =moyenne(, pui, sans refermer la parenthèse, avec la souris, sélectionnez la plage de données de la cellule C14 à la cellule C3,
validez en faisant Entrée,
observez le calcul, vous obtenez le résultat qui va bien,
replacez-vous sur la cellule C17,
incrémentez vers la droite pour un calcul automatique des deux colonnes qui suivent,
c’est terminé.