RÉFÉRENCES ABSOLUES DANS LES CALCULS – EXCEL 2007
Le tableur Excel est un fabuleux calculateur qui interprète souvent très bien le contexte et qui permet, à partir d‛un calcul donné, de le reproduire sur l‛ensemble des autres cellules en tirant simplement la poignée de la cellule. Nous allons voir ici que cette méthode peut avoir des limites et nous allons comprendre pourquoi. Puis nous verrons quelle solution offre Excel pour palier le problème.
Nous vous proposons l‛apprentissage au travers d‛un cas pratique guidé.
– Ouvrez le classeur Excel situé dans le dossier Sources,
Un seul tableau ici, Analyses des bénéfices. Ce tableau représente les articles vendus par un magasin. Les colonnes Prix de revient et Prix de ventes indiquent respectivement à quel prix le gérant achète l‛unité et la revend. La colonne quantités vendues liste bien sûr, le nombre vendu par article.
CALCUL DE LA MARGE UNITAIRE – SOUSTRAIRE
Le premier calcul proposé est celui de la marge unitaire, soit la différence entre le prix de vente et le prix de revient.
- Sélectionnez les cellules des marges à calculer (D6 à D10),
- Tapez le symbole = pour initialiser le calcul,
- Cliquez sur la première cellule du prix de vente (B6),
- Enfoncez la touche – (moins) du pavé numérique,
- Cliquez sur la première cellule du prix de revient (C6),
- Validez le calcul en enfonçant ensemble CTRL et ENTREE (Ctrl + Entrée).
Avec cette combinaison de touches, vous reproduisez instantanément les calculs sur l‛ensemble des cellules pré-sélectionnées. Ici on parle de références relatives. Pas de souci lié au contexte. Le calcul se reproduit naturellement car sur chaque ligne la logique est respectée, puisqu‛il faut soustraire le prix de vente de la ligne par le prix de revient de la ligne.
CALCUL DU BÉNÉFICE – MULTIPLIER
Le bénéfice s‛obtient en multipliant la marge unitaire par la quantité d‛articles vendus.
● Sélectionnez toutes les cellules des bénéfices à calculer (F6 à F10),
Excel 2007 Références absolues dans les calculs – Figer une cellule
- Tapez le symbole = pour initialiser le calcul,
- Cliquez sur la première cellule de la marge réalisée (D6),
- Enfoncez la touche * (Multiplier) du pavé numérique,
- Cliquez sur la première cellule de la quantité vendue (E6),
- Validez le calcul en enfonçant ensemble CTRL et ENTREE (Ctrl + Entrée).
Comme précédemment et pour les mêmes raisons, vous reproduisez naturellement les calculs de bénéfices sur toutes les cellules pré-sélectionnées.
LES TOTAUX – SOMME AUTOMATIQUE
Nous laissons pour l‛instant le calcul de la part de bé-néfice de côté car il s‛agit du noeud du problème. Pour calculer toutes les sommes, même si certaines ne sont pas pertinentes, utilisons la méthode la plus rapide :
- Sélectionnez toutes les cellules à sommer plus les cellules du résultat (B6 à F11),
- Réalisez la combinaison Alt + =,
Vous obtenez tous les totaux en un clin d‛oeil. Vous auriez pu cliquer sur le bouton Somme auto-matique mais autant profiter d‛une des nouvelles astuces d‛Excel 2007.
LES CALCULS STATISTIQUES
Occupons nous maintenant de calculs simples de la marge moyenne, du meilleur bénéfice et de la part minimum. Les fonctions Excel vont tout faire pour nous ou presque :
- Placez vous dans la cellule de la marge moyenne (D13),
- Dans la barre d’outils Excel, cliquez sur la rubrique (Onglet) Formules,
- Cliquez sur la flèche associée au bouton somme automatique pour choisir le calcul,
- Puis cliquez sur Moyenne,
Avant de valider le calcul, il convient de redéfinir la plage du cellules pour ne pas inclure le total qui fausserait le résultat.
- Sélectionnez les marges (D6 à D10),
- Validez le calcul par Entrée.
Vous obtenez la moyenne des marges soit 64 €. Il ne vous reste plus qu‛à reproduire exactement le même schéma pour le calcul du meilleur bénéfice et de la part minimum. Pour le premier, vous utilisez la fonction Max sur les cellules F6 à F10, pour le second la fonction Min sur les cellules G6 à G10 même si ces dernières sont encore vides à ce stade.
Excel 2007 Références absolues dans les calculs – Figer une cellule
LA PART DE BÉNÉFICE – DIVISION
ça se corse ! Ici pour connaître la part de chaque bénéfice par rapport à l‛ensemble des bénfices, le calcul consiste à diviser le bénéfice effectué sur un article par la somme des bénéfices. Nous allons procéder en deux temps pour bien comprendre.
- Sélectionnez toutes les cellules des parts à calculer (G6 à G10),
- Tapez le symbole = pour initialiser le calcul,
- Cliquez sur la première cellule du bénéfice réalisé (F6),
- Enfoncez la touche / (Diviser) du pavé numérique,
- Cliquez sur la cellule de la somme des bénéfices (F11),
- Validez le calcul en enfonçant ensemble CTRL et ENTREE (Ctrl + Entrée).
Le calcul est bien reproduit sur l‛ensemble des cellules mais cette fois il pose problème car il ne s‛agit plus de références relatives. Vous pouvez scruter le calcul de chaque cellule en les sélec-tionnant tour à tour et en consultant la formule dans la barre de formule.
Dans la première cellule en G6, nous divisons la ligne 6 par la ligne 11 (F6/F11), tout va bien. Sur la cellule suivante G7, nous divisons bien la 7 mais non pas par la ligne 11 mais la 12 (F7/F12). En ef-fet, Excel interprète et suit le mouvement. Vos calculs sont reportés vers le bas, chaque fois une ligne plus bas, il adapte donc les calculs sur les lignes du dessous. Le bénéfice est bien le suivant (ligne 7) mais chacun d‛entre eux doit être divisé par le même total, toujours situé en ligne 11 (F11). Il faut donc indiquer à Excel dans le calcul que cette cellule est figée pour la division. Comment ? C‛est le fameux principe des références absolues !
- Sélectionnez de nouveau les parts de bénéfice (G6 à G10),
- Enfoncez la touche Suppr du clavier pour supprimer les calculs,
- Tapez le symbole = pour initialiser le calcul,
- Cliquez sur la première cellule du bénéfice réalisé (F6),
- Enfoncez la touche / (Diviser) du pavé numérique,
- Cliquez sur la cellule de la somme des bénéfices (F11),
- Pour figer cette dernière, enfoncer la touche F4 du clavier,
Comme vous le constatez (Cf. figure) des dollars encadrent dé-sormais la cellule F11 dans le calcul l‛empêchant de bouger au moment de reproduire le calcul. Testons :
- Validez le calcul en enfonçant ensemble CTRL et ENTREE (Ctrl + Entrée).
Cette fois tout est correct. Si vous passez en revue tour à tour la calcul de chaque cellule, vous noterez que chaque ligne est bien divisée par la ligne 11 (F11).