Figer une cellule dans un calcul Excel pour reproduire la formule

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


Sujets que vous pourriez aussi aimer :


Références Absolues dans Excel - Figer une cellule dans un calcul
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é.

• Ouvrir le classeur Excel references-absolues.xlsx, en cliquant sur ce lien,

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électionner les cellules des marges à calculer (D6 à D10),
• Taper le symbole = pour initialiser le calcul,
• Cliquer sur la première cellule du prix de vente (B6),
• Enfoncer la touche - (moins) du pavé numérique,
• Cliquer sur la première cellule du prix de revient (C6),
• Valider 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électionner toutes les cellules des bénéfices à calculer (F6 à F10),
• Taper le symbole = pour initialiser le calcul,
• Cliquer sur la première cellule de la marge réalisée (D6),
• Enfoncer la touche * (Multiplier) du pavé numérique,
• Cliquer sur la première cellule de la quantité vendue (E6),
• Valider 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électionner toutes les cellules à sommer + les cellules des résultats (B6 à F11),
• Réaliser la combinaison Alt + =,

Vous obtenez tous les totaux en un clin d'oeil. Vous auriez pu cliquer sur le bouton Somme automatique ou encore passer par le petit bouton Analyse rapide qui se déclenche en bas à droite de la sélection.

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 :

• Sélectionner la cellule de la marge moyenne (D13),
• Taper le signe = pour initialiser le calcul,
• Saisir la formule =moyenne(,
• Sélectionner les marges (D6 à D10),
• Fermer la parenthèse et valider le calcul par la touche 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. Il faut remplacer la fonction moyenne() par la fonction max() à appliquer sur la plage  F6 à F10, et par la fonction min() à appliquer sur la plage G6 à G10, même si ces dernières sont encore vides à ce stade. La part minimum se mettra à jour automatiquement dès lors que nous aurons calculé les parts de bénéfices.


La part de bénéfice - Division
C'est ici que la situation se corse ! Pour connaître la part de chaque bénéfice par rapport à l'ensemble des bénéfices, 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électionner toutes les cellules des parts à calculer (G6 à G10),
• Taper le symbole = pour initialiser le calcul,
• Cliquer sur la première cellule du bénéfice réalisé (F6),
• Enfoncer la touche / (Diviser) du pavé numérique,
• Cliquer sur la cellule de la somme des bénéfices (F11),
• Valider 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électionnant tour à tour et en consultant la formule dans la barre de formules. 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 effet, 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. Or la cellule F12 est vide et une division par 0 est impossible. C'est ce qu'indique le message d'erreur Excel #DIV/0!. 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électionner de nouveau les parts de bénéfice (G6 à G10),
• Enfoncer la touche = pour recommencer les calculs,
• Cliquer sur la première cellule du bénéfice réalisé (F6),
• Enfoncer la touche / (Diviser) du pavé numérique,
• Cliquer sur la cellule de la somme des bénéfices (F11),
• Pour figer cette dernière, enfoncer la touche F4 du clavier,
• Valider le calcul en enfonçant ensemble CTRL et ENTREE(Ctrl + Entrée).

Comme vous le constatez (Cf. figure) des dollars encadrent désormais la cellule F11 l'empêchant de bouger au moment de reproduire le calcul. Si vous testez chaque cellule, vous remarquez que le bénéfice réalisé suit bien le mouvement du calcul (chaque fois une ligne plus bas). On parle de référence relative. En revanche, malgré le déplacement, la cellule utilisée pour la division reste la cellule F11. Elle est figée, on parle de référence absolue. Vous noterez enfin que le calcul de la part minimum que nous avions pré-établi s'est mis à jour automatiquement.

Mise en pratique des références absolues
Calculs de TVA et pourcentage
Nous proposons ici un cas pratique supplémentaire sur les références absolues qui représentent un point épineux d'Excel, au travers de calculs de montants TTC basés sur des valeurs hors taxes et un taux de TVA proposé dans une cellule unique.

• Télécharger le fichier source en cliquant sur ce lien,
• L'ouvrir dans Excel.

Il s'agit d'une société basée à Grenoble. Dans ce tableau sont listés ses frais principaux sur les trois derniers mois. Nous allons devoir calculer les postes qui lui coûtent le plus cher ainsi que la somme des charges par mois. Ces montants s'entendent hors taxes. Nous en profiterons pour pratiquer le calcul du TTC basé sur la TVA.


Calcul des charges - Somme
Il s'agit de calculer la somme des charges par mois et par poste, soit en lignes et en colonnes, rien de plus simple avec la technique de la somme automatique.

• Sélectionner toutes les valeurs à sommer plus les cellules des résultats (C9 à F14),
• Réaliser la combinaison Alt + =,

Tous les résultats sont instantanément calculés en lignes et colonnes. Vous auriez aussi pu cliquer sur le bouton Somme automatique de la section Edition du ruban Accueil.

Part de pourcentage - Division
Commençons par nous intéresser aux parts de pourcentages de la colonne située avant le TTC. Ici nous devons trouver le rapport que représente la somme des charges pour un poste par rapport à l'ensemble des charges. Le rapport des salaires s'obtient donc en divisant la somme des charges due aux salaires par la somme de toutes les charges. Comme ce calcul doit être reproduit sur toute la colonne et que chaque somme sera divisée par la même cellule, nous devons appliquer la technique des références absolues sur la cellule du total de toutes les charges.

• Sélectionner les cellules des parts de pourcentage (G9 à G13),
• Taper le symbole = pour initialiser le calcul,
• Cliquer sur la somme des charges des salaires (F9),
• Taper le symbole de fraction (/), en haut du pavé numérique,
• Cliquer sur la cellule de la somme de toutes les charges (F14),
• Enfoncer la touche F4 du clavier pour figer cette dernière,
• Valider le calcul par Ctrl + Entrée.

Pour rappel, ce raccourci clavier permet, lorsque toutes les cellules sont présélectionnées, de répercuter le calcul sur toute la plage. Biensûr, si nous n'avions pas figé la cellule de division à l'aide de la touche F4 nous aurions obtenu des erreurs de calcul. Selon la même technique soucions nous des parts de pourcentage de la ligne inférieure du tableau.

• Sélectionner toutes les parts de pourcentage à calculer (C15 à E15),
• Taper le symbole = pour initialiser le calcul,
• Cliquer sur la somme des charges du mois de Juillet (C14),

Lorsque vous n'arrivez pas à cliquer sur une cellule pendant le calcul, vous pouvez l'atteindre à l'aide des flèches du pavé directionnel ou tout simplement saisir ses coordonnées au clavier.

• Taper le symbole de fraction (/), en haut du pavé numérique,
• Cliquer sur la cellule de la somme de toutes les charges (F14),
• Enfoncer la touche F4 du clavier pour figer cette dernière,
• Valider le calcul par Ctrl + Entrée.

Calcul du TTC - Multiplication
Il nous reste à calculer les montants TTC sachant que les valeurs de la colonne F sont des montants hors Taxe. Sachant de même qu'il n'y pas de charge sur les salaires, nous ignorerons le caclul de la ligne H9. Un montant TTC est égal au montant HT auquel on ajoute les charges dues à la TVA sur ce montant HT, soit HT*TVA. Ce qui donne TTC = HT + HT*TVA. Nous pourrions aussi factoriser ce calcul (TTC = HT*(1 + TVA)) mais ce n'est pas l'intérêt à ce stade.

• Sélectionner les cellules du TTC à calculer (H10 à H13),
• Enfoncer la touche = pour débuter le calcul,
• Cliquer sur la première cellule du HT (F10),
• Taper le symbole + du pavé numérique,
• Cliquer de nouveau sur le HT (F10),
• Taper le symbole de multiplication (*),
• Cliquer la cellule de la TVA (E6),
• Enfoncer la touche F4 du clavier pour figer cette TVA,
• Valider le calcul par CTRL + Entrée.

Une fois de plus nous utilisons la touche F4 du clavier, ici pour figer la TVA, tout simplement parce que cette dernière intervient pour le calcul de chaque ligne de TTC.