Utiliser des références relatives, absolues et mixtes
UTILISER DES REFERENCES MIXTES
Dans un premier temps, vous allez mettre en pratique les références absolues et relatives. En ce sens, vous allez créer une feuille de calcul pour déterminer les tarifs de différents produits, compte tenu d’un taux de remise et d’un taux de TVA.
1. En A1, saisissez TARIF.
2. En E3, saisissez Taux TVA.
3. En F3, saisissez 19,6%.
4. En A5, B5, C5, D5, E5, F5, saisissez respectivement Référence, Libellé, P.U. H.T., Remise, P.U. net H.T., P.U. T.T.C..
6. Sélectionnez A1:F1 et cliquez sur Fusionner et centrer (onglet Accueil, groupe Alignement). Appliquez une taille de police de 16 et mettez le texte en gras.
7. Sélectionnez E3:F3 et appliquez un contour de type quadrillage. Mettez E3 en gras.
8. Sélectionnez A5:F5, centrez le texte et mettez-le en gras.
9. Sélectionnez A5:F13 et appliquez un contour de type quadrillage.
10. Sélectionnez C6:C13, ainsi que E6:F13 et appliquez le format Monétaire.
Cette feuille présente plusieurs articles avec, pour chacun d’eux, son prix HT ainsi qu’un taux de remise. Le but est de calculer, pour chaque article, le prix net HT (c’est-à-dire compte tenu de la remise) et le prix TTC.
Pour calculer le prix net, il faut à chaque ligne appliquer le taux de remise :
1. En E6, saisissez =C6*(1−D6).
2. Étendez le contenu, à l’aide de la poignée de recopie, jusqu’en E13. Pour cela, sélectionnez la cellule E6 et approchez le pointeur de la souris de la poignée de recopie de la cellule. La poignée de recopie est le petit carré noir situé dans l’angle inférieur droit. Le pointeur change d’apparence : il se transforme en une petite croix noire.
3. Cliquez à présent sur la poignée de recopie et déplacez le pointeur jusqu’en E13. La plage E6:E13 est entouré d’un contour gris. Relâchez le bouton de la souris. La formule de calcul de la cellule E6 a été « étendue » aux autres cellules de la plage.
Vous avez ainsi mis à profit les propriétés des références relatives : elles localisent les cellules par rapport à la cellule active. En étendant le contenu de la cellule active à celles situées dans la même colonne, vous avez « fait suivre » les références relatives de la formule.
Il reste maintenant à calculer les prix TTC. Pour cela, il faut utiliser le taux de TVA situé dans la cellule F3 :
1. En F6, saisissez =E6*(1+F3).
2. Étendez le contenu, à l’aide de la poignée de recopie, jusqu’en F13.
Le résultat n’est pas très probant. Vous avez sans doute déjà identifié les causes du problème : la référence à F3 doit être absolue, puisque ce taux doit être utilisé de la même façon à chaque ligne.
1. Double-cliquez sur F6.
2. Positionnez le curseur à côté de la référence F3 (après le 3, par exemple).
3. Appuyez sur [F4]. La référence devient $F$3.
4. Validez par “Entrée”.
5. Étendez le contenu, à l’aide de la poignée de recopie, jusqu’en F13.
L’emploi de références relatives ou absolues est principalement conditionné par le comportement attendu de la formule lorsqu’elle sera copiée. En effet, une formule est rarement « isolée », elle fait souvent partie de lignes ou de colonnes présentant des formules semblables, obtenues par recopie d’une formule initiale.
UTILISER DES REFERENCES MIXTES
Pour mettre en pratique les références mixtes, vous allez construire une feuille de calcul qui permet de déterminer la capacité de production d’un atelier, en fonction de la capacité horaire de chaque machine et de la durée d’ouverture journalière de l’atelier.
1. En A1, saisissez Capacités de production – Atelier XXXX.
2. En C3, saisissez Lundi et étendez le contenu de la cellule jusqu’en I3.
3. En B4, saisissez Durée du travail.
4. En C4, D4, E4, F4, G4, H4, I4, saisissez respectivement 8, 10, 10, 10, 8, 6, 0.
5. En A6, saisissez Machine.
6. En B6, saisissez Capacité.
7. En C6, saisissez Lundi et étendez le contenu de la cellule jusqu’en I6.
8. En J6, saisissez Total.
9. En A7, saisissez Machine 1 et étendez le contenu de la cellule jusqu’en A12.
10. En A13, saisissez Total / jour.
11. En B7, B8, B9, B10, B11, B12, saisissez respectivement 100, 150, 75, 98, 102, 123.
12. Sélectionnez A1:J1 et cliquez sur Fusionner et centrer (onglet Accueil, groupe Alignement). Appliquez une taille de police de 16 et mettez le texte en gras.
13. Sélectionnez C3:I3 et appliquez un contour de type quadrillage. Mettez le texte en gras.
14. Sélectionnez B4:I4 et appliquez un contour de type quadrillage. Mettez B4 en gras.
15. Sélectionnez A6:J6, centrez le texte et mettez-le en gras.
16. Sélectionnez A6:J13 et appliquez un contour de type quadrillage.
17. A l’aide du bouton Somme automatique (onglet Formules) positionnez les totaux de lignes et de colonnes.
18. Sélectionnez C13:J13 et mettez le texte en gras.
19. Sélectionnez J7:J12 et mettez le texte en gras.
Ainsi, le lundi, la capacité de production de la machine 1 est de100 × 8, soit 800 pièces. Vous généraliserez ce calcul à l’ensemble des machines, pour chacun des jours de la semaine.
1. En C7, saisissez =$B7*C$4.
2. Étendez le contenu, à l’aide de la poignée de recopie, jusqu’en C12.
3. Étendez le contenu, à l’aide de la poignée de recopie, jusqu’à la colonne I.
En saisissant une formule, vous avez pu en créer quarante-deux (6 × 7) par simple copie. Il est important de réfléchir, lors de la conception des formules, à l’intérêt de figer ou non la référence à la ligne ou à la colonne. En figeant la référence à la colonne B et en laissant la ligne libre, vous demandez à la formule d’aller chercher la valeur de la capacité horaire de chaque machine, quel que soit le jour de la semaine. De même, en figeant la référence à la ligne 4 et en laissant la colonne libre, vous autorisez la formule à aller chercher la durée d’ouverture de l’atelier pour chacun des jours, quelle que soit la machine considérée.