Reproduire des calculs Excel partout avec une seule formule

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


Sujets que vous pourriez aussi aimer :


Techniques avancées de calculs dans Excel

Cette formation est destinée à mettre en pratique des notions importantes de calculs dans différents contextes. Ainsi au travers de trois exercices, nous apprendrons à déceler quand et comment doivent intervenir les références absolues de manière à pouvoir reproduire les calculs, en toutes circonstances. Nous en profiterons de même pour appliquer des fonctions communes et en découvrir d'autres, notamment pour fournir des résultats statistiques.

Le classeur des exercices

• Télécharger le classeur techniques-de-calculs-avances-excel.xlsx en cliquant sur son lien,
• L'ouvrir dans Excel,

Ce classeur est constitué de trois feuilles : Collège Pablo Picasso, Chiffre d'affaire et Remises clients. Chaque feuille correspond à un exercice indépendant où l'objectif consiste notamment, à appliquer les références absolues dans des cas de figure bien différents. Par défaut, la feuille Collège Pablo Picasso doit être active.

Relevé de notes du collège
Ce tableau présente les notes d'élèves d'une classe de 3^ème. Il propose de réaliser des calculs statistiques et de synthèse dans ses cinq dernières colonnes. Nous allons débuter par la calcul de la moyenne des notes par élève avec la fonction Excel Moyenne(). Certains élèves n'ont pas de note dans certaines matières. Or la moyenne est le calcul de la somme des notes divisé par le nombre de ces notes. La fonction Moyenne() est capable d'ignorer les cellules vides pour ne pas les intégrer dans le calcul. Donc nous pouvons désigner la plage de cellules complète à moyenner sans nous soucier du nombre de notes renseignées. Parmi les différentes méthodes qui s'offrent à nous pour réaliser le calcul, nous choisissons celle qui consiste à présélectionner l'ensemble des cellules des résultats :

• Sélectionner les cellules de la colonne Moyenne soit J7:J14,
• Taper le symbole = pour débuter le calcul,

Par défaut une saisie s'inscrit dans la première des cellules d'une plage sélectionnée.

• Taper le nom de la fonction et ouvrir la parenthèse soit moyenne(,

La casse est indifférente dans les noms de fonctions utilisés. Vous pouvez aussi bien les écrire en majuscules qu'en minuscules tant qu'il n'y a pas d'erreur dans l'orthographe du nom.

• Sélectionner la première plage contenant les notes à moyenner, soit D7:D17,
• Puis fermer la parenthèse et valider le calcul par CTRL + Entrée.

Le raccourci CTRL + Entrée permet en même temps de valider le calcul et de le reproduire sur l'ensemble de la plage sélectionnée, en adaptant bien sûr les références des cellules, comme si nous avions tiré la poignée du calcul. Le support de formation sur les raccourcis clavier Excel enseigne beaucoup d'astuces liées aux combinaisons de touches.


Quoiqu'il en soit, tous les calculs sont bien réalisés en un clin d'oeil grâce à cette méthode. Les décimales pour certains résultats sont cependant trop nombreuses. Nous pourrions choisir de réduire leur affichage grâce au bouton de la section Nombre du ruban Accueil ou encore plus simplement d'arrondir ces valeurs. Pour cela Excel propose deux fonctions : ARRONDI.INF() et ARRONDI.SUP(). L'une comme l'autre requiert deux arguments : Le nombre à arrondir (La cellule) et le nombre de décimales à conserver. Ainsi pour la valeur 12,66 par exemple, si nous souhaitons conserver seulement une décimale, la première des fonctions renvoie 12,6 tandis que la seconde retourne 12,7. Nous devons donc modifier la formule de la colonne Moyenne. Pour cela nous allons utiliser la fonction Arrondi.inf() à laquelle nous allons passer en premier paramètre, la fonction moyenne(). En effet, cette dernière retourne la valeur à arrondir. Puis nous saisirons le chiffre 1 en second paramètre puisque nous souhaitons conserver seulement une décimale. Et nous allons en profiter pour découvrir une méthode qui permet de mettre à jour tous les calculs de la plage en modifiant seulement l'un d'entre eux.

• Sélectionner tous les résultats de la colonne Moyenne soit J7:J14,
• Enfoncer la touche F2 du clavier,

La touche F2 permet d'activer la saisie d'une cellule, en l'occurrence ici, la saisie de la première cellule de la plage.

• Avec la souris, cliquer juste après le symbole = pour y placer le point d'insertion,
• Taper le nom de la fonction et ouvrir la parenthèse, arrondi.inf(,

Ainsi la fonction moyenne() qui retourne le résultat à arrondir figure déjà dans les parenthèses de la fonction arrondi.inf() en tant que premier argument.

• Cliquer avec la souris après la parenthèse fermante de la fonction moyenne(),
• Saisir un point-virgule (;) pour appeler le second argument de la fonction arrondi.inf(),
• Taper le chiffre 1 et fermer la parenthèse de la fonction arrondi.inf(),
• Valider la modification de la formule par CTRL + Entrée,

Comme précédemment, cette technique permet de répercuter la logique du calcul sur l'ensemble de la plage de cellules présélectionnées. Et comme vous le constatez, les décimales inutiles et disgracieuses ont disparupuisque la fonction arrondi.inf() arrondit le résultat de la moyenne à une décimale sur le nombre directement inférieur.


Le classement - Fonction Rang()
La colonne suivante, Rang, se propose d'afficher le classement de chaque élève par rapport à l'ensemble de la classe en fonction de sa note. La fonction Excel qui permet d'afficher ce genre de résultat statistique est la fonction Rang(). Elle demande trois arguments. Tout d'abord, il faut indiquer la valeur à classer (La cellule de la moyenne de l'élève). Ensuite il faut lui indiquer la table de valeurs par rapport à laquelle effectuer le classement (La plage de cellules des moyennes). Et enfin, il faut lui préciser si l'on souhaite un ordre croissant (1) ou décroissant (0). Comme nous souhaitons un classement par rapport à la moyenne la plus élevée, nous choisirons un ordre décroissant. Ce qui donne =Rang(moyenne_eleve ;plage_moyenne ;0). Comme ce calcul n'est pas si anodin qu'il ne paraît, nous allons utiliser deux méthodes différentes. Tout d'abord, nous l'effectuerons d'une façon classique puis, dans un deuxième temps, nous utiliserons les noms attribués aux plages de cellules pour les intégrer dans les formules.

• Sélectionner toutes les cellules de la colonne Rang, K7:K14,
• Taper le symbole = pour débuter le calcul,
• Saisir le nom de la fonction et ouvrir sa parenthèse, soit rang(,
• Sélectionner tout d'abord la cellule à évaluer soit J7,
• Taper un point-virgule (;) pour appeler l'argument suivant,
• Sélectionner la plage permettant la comparaison, soit J7:J14,
• Taper un point-virgule (;) pour appeler le dernier argument,
• Saisir la valeur 0 pour demander de réaliser un classement en partant des valeurs les plus hautes,
• Valider la formule par CTRL + Entrée pour la reproduire sur l'ensemble de la plage,

Les résultats obtenus ne sont pas cohérents. En effet, plusieurs élèves sont classés premiers alors qu'ils n'ont pas la meilleure moyenne. C'est ce qu'illustre la capture ci-dessous.


En effet, nous avons initialement demandé à Excel de faire la comparaison sur la plage J7:J14 pour le classement de la cellule J7. Ainsi, fort logiquement, en reproduisant la logique de calcul, Excel déplace les références des cellules pour les adapter. On parle de références relatives. C'est pourquoi, par exemple en K13, la formule fait référence à J13 et non plus J7 pour évaluer son classement. Jusque-là, tout est parfait. Mais du coup, il ne compare plus cette valeur au tableau J7:J14 mais à J13:J20. Et c'est là que le problème survient. Bien que toutes les moyennes soient passées en revue tour à tour, elles doivent toujours être comparées aux valeurs du tableau J7:J14 dont les bornes doivent rester fixes. On parle de références absolues qui sont enseignées par le support de formation Excel pour apprendre à figer des cellules dans un calcul. Nous allons donc fixer cette plage dans la formule pour corriger le problème :

• Sélectionner de nouveau toutes les cellules de la colonne Rang soit K7:K14,
• Enfoncer la touche F2 pour activer la saisie de la première cellule de la plage,
• Sélectionner les deux points (:) de la plage de cellules J7:J14 dans la fonction Rang(),
• Enfoncer la touche F4 du clavier,

Des dollars apparaissent et encadrent chaque cellule de la plage dans la formule. Ces dollars indiquent que ces cellules sont désormais figées. Excel ne déplacera plus les bornes de cette plage en répliquant la logique du calcul sur les cellules du dessous. C'est donc la touche F4 du clavier qui permet d'enclencher ces références absolues pendant la saisie de la formule. Si nous n'avions pas sélectionné préalablement les deux points de la plage, Excel n'aurait figé que l'une des deux cellules. Il aurait fallu ensuite s'occuper de l'autre.

• Valider le calcul par CTRL + Entrée,

Cette fois les résultats sont cohérents. Si vous affichez la formule de la cellule K13, comme l'illustre la capture ci-dessous, vous constatez que la cellule J13 est bien comparée aux valeurs du tableau J7:J14 figé et non plus J13:J20.


Les plages nommées
Le fait de nommer une plage de cellules permet d'éviter de passer par les références absolues. Une fois qu'un nom est attribué à une plage, Excel y fait référence dans le calcul par ce nom qui est défini sur des bornes figées. Cependant, il faut bien comprendre ce principe des références absolues, car il existe bons nombres de cas pour lesquels les plages nommées ne pourront pas venir à la rescousse.

• Sélectionner toutes les cellules de la colonne Rang soit K7:K14,
• Enfoncer la touche Suppr du clavier pour supprimer tous les résultats de calcul,
• Sélectionner la plage de cellules des moyennes soit J7:J14,
• Dans la zone Nom, en haut à gauche de la fenêtre Excel, taper le nom moyennes,
• Valider cette saisie par la touche Entrée du clavier,

Maintenant que cette plage est nommée, nous allons refaire le calcul du rang en y faisant référence par son nom. Du coup, nous verrons qu'il n'est plus nécessaire de figer ses bornes avec la touche F4 du clavier.

• Sélectionner toutes les cellules de la colonne Rang soit K7:K14,
• Saisir le début de la fonction =Rang(,
• Cliquer la première cellule à classer soit J7 puis taper un point-virgule (;),
• A la place de la plage de cellules, saisir le nom moyennes,

Vous remarquez qu'il apparaît en rouge à l'issue, indiquant qu'il est bien reconnu par Excel comme le nom d'une plage de cellules.

• Finaliser l'écriture de la formule par ;0),
• Valider ce calcul par CTRL + Entrée pour le reproduire,

Nous obtenons exactement les mêmes résultats que précédemment. Si vous affichez la formule de la cellule K13, vous remarquez que la plage de cellules ne peut être déplacée par le calcul, puisqu'elle est toujours appelée par son nom. La plage est donc figée par son nom.

Compter le nombre de valeurs - Nbval()
La colonne suivante propose d'afficher le nombre de notes sur lesquelles la moyenne de l'élève a été bâtie. Il existe une fonction Excel qui permet de comptabiliser le nombre de cellules non vides dans une plage désignée. Cette fonction se nomme Nbval(). Elle ne requiert qu'un seul paramètre, la plage de cellules sur laquelle effectuer le décompte.

• Sélectionner les cellules de la colonne Nombre de notes, soit L7:L14,
• Taper le symbole = pour débuter le calcul,
• Saisir le nom de la fonction et ouvrir sa parenthèse, nbval(,
• Sélectionner la plage de cellules sur laquelle compter soit D7:I7,
• Fermer la parenthèse de la fonction,
• Valider la formule par CTRL + Entrée pour la répercuter sur toute la plage,

Tous les résultats s'inscrivent instantanément dans la colonne. Ils fournissent une indication précieuse pour comparer les moyennes, à savoir si l'élève a participé ou non à toutes les épreuves. Comme vous l'avez compris, cette fois les références absolues ne devaient pas intervenir. La plage de cellules du décompte doit en effet changer de ligne, donc de bornes, en même temps que la formule est reproduite sur les cellules des lignes du dessous. On dit que les références de la plage de cellules suivent le déplacement du calcul et on parle cette fois de références relatives.


Les valeurs seuils - Max() et Min()
Les calculs des deux dernières colonnes sont triviaux. Il s'agit de faire ressortir pour chaque élève sa meilleure moyenne (max) et sa pire note (min). Ces deux fonctions tout comme la précédente, Nbval(), ne requièrent qu'un seul paramètre, la plage de cellules sur laquelle faire ressortir la valeur seuil. Pour la même raison aussi, nous ne faisons pas intervenir les références absolues ici.

• Sélectionner toutes les cellules de la colonne Note la + basse, soit M7:M14,
• Taper la formule suivante =MIN(D7:I7),
• Valider le calcul par CTRL + Entrée,
• Sélectionner toutes les cellules de la colonne Note la + haute, soit N7:N14,
• Taper la formule suivante =MAX(D7:I7),
• Valider le calcul par CTRL + Entrée,

Toutes les valeurs remarquables s'inscrivent dans les deux colonnes. Justement, nous allons exploiter ces deux dernières fonctions, max et min, afin de faire ressortir dynamiquement, la meilleure moyenne de la classe ainsi que la pire. Pour cela, nous allons exploiter la mise en forme conditionnelle sur la base d'une formule exploitant ces fonctions. La capacité d'Excel à formater des cellules en fonction de leur valeur est traitée par la formation sur les techniques avancées de mise en forme. Ce format dynamique doit donc s'appliquer sur la colonne des moyennes et va prouver que le champ d'application de certaines fonctions s'étend au-delà des calculs dans les feuilles. Pour appliquer un format qui varie en fonction des valeurs contenues dans les cellules, il faut commencer par les sélectionner.

• Sélectionner toutes les cellules de la moyenne soit J7:J14,
• Cliquer sur le bouton Mise en forme conditionnelle du groupe Styles du ruban Accueil,
• Dans la liste, choisir Nouvelle règle,
• Dans la boîte de dialogue qui suit, sélectionner le dernier type : Utiliser une formule...,
• Cliquer ensuite dans la zone de saisie de la formule pour y placer le point d'insertion,
• Cliquer sur la première cellule de la moyenne soit J7,

Excel inscrit alors le début de la formule comme suit : =$J$7. Il fige instantanément la cellule J7. Or pour appliquer un format conditionnel sur cette plage de cellules, c'est l'ensemble des cellules qui doivent être parcourues et analysées. La cellule doit donc bouger et ne pas être figée.

• Enfoncer plusieurs fois la touche F4 du clavier jusqu'à ce que tous les dollars disparaissent,
• A la suite, taper =max(,
• Sélectionner la plage complète, soit J7:J14 et fermer la parenthèse,

Nous obtenons la formule =J7=max($J$7:$J$14). La plage J7:J14 est cette fois figée à juste titre. Au fur et à mesure qu'Excel parcourt les cellules de la plage, il doit comparer chaque valeur avec toutes les valeurs des cellules contenues entre ces deux bornes fixes. Cette formule traduit donc bien l'égalité que nous cherchons à faire ressortir : Si la valeur de la cellule est la plus grande de la plage. Lorsque ce critère est vérifié, nous souhaitons que la valeur apparaisse en vert sur fond jaune.

• Cliquer sur le bouton Format de la boîte de dialogue,
• Dans l'onglet Police de la boîte de dialogue qui suit, choisir une couleur verte et un style gras,
• Dans l'onglet Remplissage, choisir un fond jaune orangé et cliquer sur Ok,

Lorsque vous validez ces réglages, vous constatez que la moyenne la plus grande se met directement en forme avec les attributs demandés. Si les notes venaient à évoluer et que la meilleure moyenne venait à changer, cette cellule reprendrait son format d'origine tandis que la nouvelle meilleure moyenne se mettrait en vert sur fond jaune. C'est l'intérêt de la mise en forme conditionnelle qui permet d'appliquer un format dynamique afin de faire ressortir automatiquement les valeurs remarquables et faciliter l'interprétation des tableaux de calculs.

• En suivant la même procédure que précédemment, créer une nouvelle règle, sur cette même plage de cellules afin de faire ressortir la plus mauvaise moyenne en rouge sur fond jaune, grâce à la fonction min(),

Vous pouvez cumuler autant de règles de mise en forme conditionnelle que vous souhaitez sur une même plage de cellules. Leur imbrication permet de traiter autant de critères que vous le souhaitez. A l'issue, vous êtes en mesure de créer des tableaux faciles à interpréter grâce à des jeux de couleurs dynamiques. Il est possible de modifier ou de supprimer un format conditionnel. Pour cela, il faut premièrement re-sélectionner la plage de cellules sur laquelle il est posé. Ensuite il faut de nouveau cliquer sur le bouton Mise en forme conditionnelle du ruban. Puis, il faut choisir Gérer les règles tout en bas de la liste. Dans la boîte de dialogue qui suit, les règles en vigueur pour la plage sélectionnée, sont listées. Il suffit de sélectionner l'une d'entre elles pour ensuite la modifier ou la supprimer.

Chiffre d'affaire et part de pourcentage
L'exercice suivant propose de réaliser les calculs de synthèse sur les chiffres d'affaires d'une parapharmacie.

• Cliquer sur l'onglet de la feuille Chiffre d'affaire pour l'activer,

Pour chaque article listé dans la colonne Produits, le tableau fournit le chiffre d'affaire réalisé pour chaque mois, sur les six premiers mois de l'année. Il s'agit dans un premier temps de calculer la somme de ces chiffres d'affaire par produit, dans la colonne Total et par mois, dans la ligne Total. Une simple Somme Automatique fera l'affaire. Puis il s'agira de calculer la part de pourcentage de ces sommes. Le calcul consiste à diviser chaque somme obtenue par le total de la somme. Le résultat obtenu livre alors une représentation des proportions de chiffres d'affaire réalisés. Commençons par effectuer les sommes.

• Sélectionner toutes les cellules de la colonne Total soit I5:I9,
• Réaliser le raccourci clavier ALT + =,

En instantané, toutes les sommes des chiffres d'affaire par produit, s'affichent dans la colonne Total. Cette combinaison de touches est le raccourci de la somme automatique. La formation Excel sur les raccourcis clavier fournit beaucoup d'astuces à ce sujet. Comme la plage de cellules dans lesquelles les résultats devaient être calculés, était présélectionnée, cette combinaison a livré les résultats sans étapes intermédiaires. La formation sur les trucs et astuces dans Excel vous livre de nombreuses techniques pour réaliser efficacement des calculs notamment. Si nous appliquons la même méthode dans la ligne Total, c'est-à-dire en présélectionnant toutes les cellules C11:J11, et que nous réalisons le raccourci ALT + =, nous obtenons bien toutes les sommes pour chaque colonne. Néanmoins, si vous consultez la barre de formule pour l'une des cellules après calcul, vous remarquez que la fonction inclut la ligne 10 qui est vide. Cela ne change pas les résultats qui sont corrects, mais dans la méthode ce n'est pas satisfaisant.


Nous souhaitons réaliser les sommes précisément sur les lignes 5 à 9. Nous pourrions sélectionner la première cellule du calcul, soit C11, cliquer sur le bouton Somme automatique, redéfinir à la souris la plage précise à sommer, valider le calcul par CTRL + Entrée et le reproduire sur la droite en tirant la poignée. Mais nous allons utiliser une méthode qui permet de réaliser le calcul en présélectionnant toute la plage et en le répliquant sans la poignée.

• Sélectionner toutes les cellules de la ligne Total soit C11:J11,
• Taper = pour débuter le calcul,
• Saisir le nom de la fonction et ouvrir sa parenthèse, =somme(,
• Sélectionner la première plage à sommer soit C5:C9,

• Fermer la parenthèse de la fonction et valider le calcul par CTRL + Entrée,

La somme est ainsi répliquée sur l'ensemble de la ligne. Les résultats ne changent pas mais le calcul est désormais correct puisqu'il n'inclut plus la ligne vide.

Les parts de pourcentage
Pour connaître la proportion d'un chiffre d'affaire par rapport à l'ensemble des chiffres d'affaire, il faut diviser le chiffre d'affaire par la somme de tous les chiffres d'affaire. En J5 par exemple, le calcul donnerait : I5/I11. Chaque chiffre d'affaire (I5, I6, I7 ...) doit être divisé par ce même total (I11). Donc la cellule I11 ne doit pas bouger lorsque nous répercutons le calcul. Pour cela, nous devons la figer avec la touche F4 du clavier ou utiliser un nom de cellule comme nous l'avons fait précédemment.

• Sélectionner toutes les cellules de la colonne Part de % du total soit J5:J9,
• Taper le symbole = pour débuter le calcul,
• Cliquer sur la cellule du premier total de chiffre d'affaire soit I5,
• Taper le symbole slash (/) du pavé numérique pour la division,
• Cliquer sur la cellule de la somme des chiffres d'affaire soit I11,
• Enfoncer la touche F4 du clavier pour la figer,
• Valider le calcul par CTRL + Entrée pour le reproduire sur toute la colonne,
• Cliquer sur le bouton Style de pourcentage dans le ruban Accueil,

Tous les pourcentages s'affichent et offrent des résultats parlant pour savoir rapidement quels sont les produits qui développent les chiffres les plus importants. Lorsque nous avons enfoncé la touche F4 du clavier après avoir sélectionné la cellule I11, des dollars sont venus encadrer cette dernière. Ils permettent comme vous le savez de figer une cellule dans un calcul. Un dollar ($) se place devant l'indice de colonne ($I) et un autre se place devant l'indice ligne ($11). Le premier interdit à la cellule de changer d'indice de colonne si le calcul est répercuté en colonnes. Le second interdit à la cellule de changer d'indice de ligne lorsque le calcul est répercuté sur les lignes du dessous comme c'est le cas ici. Cela signifie, en jouant sur les dollars, qu'il est tout à fait possible d'interdire à une cellule de bouger dans une direction mais de l'autoriser à bouger dans l'autre direction. Et parfois c'est même la seule façon pour reproduire intégralement un calcul sur l'ensemble d'un tableau. C'est ce que nous verrons avec le prochain exercice. Ici donc, il n'est pas nécessaire de figer la colonne de la cellule bien que cela ne change pas le résultat. Mais il est important d'apprendre :

• Sélectionner de nouveau toutes les cellules de la colonne soit J5:J9,
• Enfoncer la touche F2 du clavier pour activer la saisie de la formule,
• Puis, enfoncer la touche F4 du clavier,

Comme vous le constatez, le dollar ($) devant l'indice de colonne (I) disparaît. Seul celui devant l'indice de ligne est conservé. Le fait d'enfoncer successivement la touche F4 du clavier permet de déplacer les dollars et donc d'influer sur les degrés de liberté de la cellule. Ici elle peut changer de colonne, mais pas de ligne, ce que nous souhaitions.

• Valider ce calcul par CTRL + Entrée pour le répercuter sur toute la plage,

Les résultats ne changent pas, mais dans la méthode nous sommes plus justes. Comme vous le savez, pour éviter les références absolues, nous pouvons utiliser les noms de plages afin de faire référence à une cellule unique dans un calcul. Nous allons exploiter cette astuce pour le calcul des parts de pourcentage sur la ligne 12. Mais gardez présent à l'esprit que cette méthode à des limites dans certains cas bien spécifiques.

• Sélectionner la cellule I11,
• Dans la zone Nom en haut à gauche de la fenêtre, taper total_ca,
• Sélectionner toutes les cellules des parts de pourcentage à calculer soit C12:I12,
• Taper le symbole = pour débuter le calcul,
• Cliquer sur la première somme des chiffres d'affaire soit C11,
• Taper le symbole de la division (/),
• Saisir le nom de la cellule pour la division soit total_ca,
• Valider le calcul par CTRL + Entrée,
• Puis, cliquer sur le bouton Style de pourcentage du ruban afin de formater ces résultats,

Comme précédemment, mais sans les références absolues, nous obtenons toutes les parts de pourcentage avec un seul calcul et une cellule figée par son nom cette fois.

Remises clients - Prix d'achat
Dans ce dernier exercice, nous allons comprendre qu'il est indispensable de maîtriser les références absolues dans Excel. En effet ici, les plages nommées ne nous seront d'aucune utilité.

• Cliquer sur l'onglet Remises clients pour activer sa feuille,

Il s'agit d'un tableau listant des articles fournit à différents distributeurs. Selon le distributeur, la remise accordée n'est pas la même. Elle est indiquée sur la ligne Taux de remise. Le principe de cet exercice consiste bien sûr à calculer les prix de revient par article pour chaque distributeur, en fonction des remises respectives. Les prix sont en colonne C, les remises sont sur la ligne 4. Mais ce calcul doit être réalisé une seule fois et répercuté sur l'ensemble du tableau. Nous allons voir qu'il est nécessaire d'exploiter les références absolues en figeant tantôt un indice de ligne seulement, tantôt uniquement l'indice de colonne.

• Sélectionner toutes les cellules du calcul soit D7:H16,
• Taper le symbole = pour débuter la formule,
• Cliquer sur la première cellule du prix C7,

Lorsque nous allons reproduire le calcul vers le bas, ce sont les prix des lignes du dessous qui doivent être considérés. Donc cette cellule C7 doit se déplacer de ligne en ligne pour devenir C8 puis C9 etc... En d'autres termes, le dollar des références absolues ne doit pas figurer devant l'indice de ligne de cette cellule. En revanche, lorsque le calcul sera répercuté sur la droite, c'est toujours le même prix, C7, qui doit être intégré dans le calcul avec le taux de remise. Cette cellule ne doit donc pas se déplacer en colonne. En d'autres termes, le dollar des références absolues doit figurer devant l'indice de colonne de cette cellule.

• Enfoncer trois fois de suite la touche F4 du clavier de manière à placer un dollar unique devant le C de C7,

Le calcul du prix remisé consiste à soustraire la part de pourcentage du prix au prix lui-même. Le calcul est donc : Prix public - Prix public x Taux de remise. Ce qui donne avec les références de cellules : C7 - C7*D4. Et une fois factorisé : C7*(1-D4).

• Taper l'étoile du pavé numérique (*) pour enclencher la multiplication,
• Ouvrir la parenthèse pour factoriser le calcul,
• Taper la suite de la formule 1-D4,
• Enfoncer deux fois de suite la touche F4 du clavier de manière à placer le dollar unique devant l'indice 4 de la cellule D4,
• Fermer la parenthèse,
• Valider le calcul par CTRL + Entrée afin de le reproduire sur tout le tableau,

Tous les résultats s'affichent en lignes et colonnes avec une seule formule grâce aux références absolues. Tantôt nous avons figé la colonne de la cellule du calcul, tantôt nous avons figé sa ligne.


Nous aurions aussi pu utiliser la poignée de la cellule afin de reproduire le calcul sur l'ensemble du tableau à partir du premier résultat en D7. Mais nous aurions dû réaliser cette action en deux temps. Il est en effet impossible de tirer une poignée en biais. Nous aurions dû tout d'abord tirer la poignée du résultat sur la droite afin de reproduire le calcul sur les colonnes. Puis pour la ligne encore sélectionnée, nous aurions dû tirer la poignée vers le bas afin de reproduire ensuite le calcul sur les lignes du dessous. Les résultats possèdent trop de décimales. Nous allons modifier le calcul pour les arrondir avec la fonction arrondi.sup() cette fois et en conservant deux décimales.

• Sélectionner toutes les cellules de calcul du tableau, soit D7:H16,
• Enfoncer la touche F2 du clavier pour activer la saisie de la première cellule de la plage,
• Modifier la formule de manière à insérer le calcul précédent dans la fonction arrondi.sup() comme suit : =ARRONDI.SUP($C7*(1-D$4);2),
• Valider et reproduire le calcul par CTRL + Entrée,
• Dans la foulée, les cellules étant encore sélectionnées, réaliser le raccourci clavier CTRL + M,

Comme vous le constatez, ce raccourci clavier permet de formater l'ensemble des données numériques en Euro, le format monétaire par défaut des versions européennes d'Excel. Les résultats sont limités à deux décimales. Ils sont ainsi plus présentables.