Cours d’informatique MICROSOFT EXCEL

Cours d’informatique MICROSOFT EXCEL, tutoriel & guide de travaux pratiques en pdf.

Références de cellules

Chaque cellule peut recevoir une formule de calcul, l’évaluation de cette formule fournissant une valeur.
Pour que le tableur comprenne que le contenu d’une cellule est une formule, on utilise la convention suivante : une formule commence toujours par le symbole « = »
Une formule est un ensemble de calculs effectués à l’aide d’opérateurs (opérateurs arithmétiques usuel « + », « * », -, … ; opérateurs de comparaison «< », « > », « = », …, etc) et de fonctions sur des données (fonctions statistiques, financières, mathématiques…).
Les données peuvent être inscrites littéralement nsda la formule ou peuvent se trouver dans d’autres cellules.
Le mécanisme permettant d’accéder à partir d’une cellule à une valeur se trouvant dans une autre cellule est appelé référence.Nous allons, dans cette partie, présenter les références, puis les fonctions et les formules.

Références relatives

La référence relative d’une cellule est l’expression de sa position par rapport à une autre cellule. A insi, une référence relative donne le décalage (en termede nombre de lignes et de colonnes) entre la cellule (dîte de référence) et une cellule pointée (dîte cellule référencée). Par convention on note négativemnt un décalage vers le haut pour l’axe vertical et undécalage vers la gauche pour l’axe horizontal.
Exemple
Considérons l’exemple suivant : on désire disposer4 multiplications sur la feuille de calcul de la façon suivante, les produits étant calculés parExcel grâce à des formules adéquates.
Une première solution consiste à écrire soi-même toutes les formules pour calculer les produits, ce qui donne :
• formule en C6 : =C4*C5
• formule en F6 : =F4*F5
• formule en C10 : =C8*C9
• formule en F10 : =F8*F9
Autrement dit, il faut taper quatre formules différentes, alors que le principe du calcul est toujours le même : effectuer le produit de la valeur se trouvan deux lignes au-dessus par la valeur de la cellule se trouvant immédiatement au-dessus.
Une autre façon de procéder est cependant possible : écrire la première formule dans la cellule C6. Ensuite on utilise le Copier-Coller. La cellule C6 est copiée puis collée en F6, C10 te F10. Que se passe-t-il lors d’une opérationColler ?
A priori on pourrait s’attendre à ce que la formule soit recopiée telle quelle : on obtiendrait alors la formule « =C4*C5 » dans la cellule F6, ce qui n’est pas précisément ce que l’on désirait. Ce n’est cependant pas ce qui se passe. La formule obtenue en F6 est bien « =F4*F5 ». Pourquoi ?
Cela vient du fait que les références utilisées lors de l’écriture de la formule dans la cellule C6 sont des références relatives.Ainsi C4 désigne davantage la cellule se trouvantdeux lignes plus haut dans la même colonne (et qui se trouve être la cellule C4) ! que la cellule C4 elle-même. Lorsque cette formule est recopiée dans le presse-papiers, c’est ce point de vue qui est conservé. Lors du collage dans la cellule F6, la référence est modifiée pour continuer à désigner la cellule se trouvant deux lignes plus haut dans la même colonne. Ainsi C4 se transforme en F4 et C5 se transforme en F5, etc…

Références absolues

La référence absolue représente le moyen de désigner une cellule de façon unique dans une feuille de calcul.
Considérons maintenant l’exemple suivant : on désire écrire la table de multiplication du nombre se trouvant dans la cellule E2.
De nouveau, les formules que l’on veut copier dans les cellules B5 à K5 sont toutes du même type : faire le produit du contenu de la cellule E2 par le contenu de la cellule immédiatement au-dessus. On va donc écrire cette formule une seule fois dans la cellule B5, puis la recopier dans les cellules suivantes à l’aide du Copier-Coller.
Si l’on s’en tient à ce que l’on connaît pour l’instant, on est tenté d’écrire simplement la formule « =E2*B4 » dans la cellule B5.
Que se passe-t-il si on recopie cette formule dans la cellule voisine ?
Les références utilisées sont des références relatives, donc lors de la recopie, ces références sont
modifiées pour désigner toujours la même positionelativer de cellule.
Ainsi, en B5, B4 désigne en réalité la cellule serouvant à la ligne précédente dans la même colonne. Après Copier-Coller dans la cellule voisine, la référence est modifiée pour désigner à nouveau la cellule de la ligne précédente et de la même colonne, ce qui donne C4. C’est bien ce que l’on voulait obtenir.
Examinons maintenant le cas de la référence B2 utiséel dans la formule en B5. Elle désigne en réalité la cellule se trouvant trois lignes plus haut et trois colonnes à droite. Après Copier-Coller de la formule en C5, cette référence est modifiée pour désigner la cellule se trouvant dans le même position relative, à savoir la cellule F2 !
Catastrophe ! Ce n’est pas du tout ce que l’on voulait obtenir ! Comment s’en sortir ?
Dans la cellule B5, il faut utiliser une référence absolue sur la cellule E2. Une telle référence s’obtient en faisant précédera lettre de la colonne et le numéro de la ligne par un signe « $ » : cela donne l’écriture $E$2. Cette écriture désigne réellement la cellule E2, et non pas la cellule se trouvant trois lignes plus haut et trois colonnes à droite. Lors d’une opération de Copier-Coller, la référence n’est pas modifiée.
Finalement, la bonne formule à écrire dans la cellule en B5 est « =$E$2*B4 ». Après recopie en C5, on obtient la formule « =$E$2*C4 » (la référence absolue n’est pas modifiée, la référence relative est mise à jour).

Références mixtes

Une référence absolue désigne de manière absolue la ligne et la colonne de la cellule référencée, ceci se fait en faisant précéder les références aux ligne et colonne du symbole « $ » (exemple : $E$2). Une référence relative, quant à elle, désigne de manière relative la ligne et la colonne de la cellule référencée (exemple : E2).
La référence mixte est un mélange des deux modes de référence vus précédemment. La référence mixte permet de désigner la ligne de manière absolue et la colonne de manière relative (ou l’inverse) : ainsi écrire « =$C4 » dans la cellule D5 référence la cellule située dans la colonne C (référence absolue pour la colonne par l’utilisation d’un signe « $) » et dans la ligne précédente (référence relative pour la ligne, pas de signe « $» devant le numéro de ligne).

Référence absolue et relative à des plages de cellules

On peut avoir besoin de référencer plusieurs cellules adjacentes pour pouvoir effectuer certains calculs : pensons par exemple au calcul des notes moyennes d’un élève dans différentes matières.
Le calcul de la moyenne en français se fait sur les notes contenues dans les cellules B3, B4 et B5. Une première solution serait d’écrire en B7 la formule « =MOYENNE(B3;B4;B5) ». On énumère toutes les cellules en séparant chaque référence par le signe « ; ». Cependant, puisque les cellules référencées sont adjacentes, no peut écrire plus simplement « =MOYENNE(B3:B5) ».
Dans le cas général, pour désigner un ensemble de cellules adjacentes les unes aux autres et formant une plage rectangulaire, on se contente de désigner les deux cellules se trouvant aux extrémités haut-gauche et bas-droite de cette plage, en séparant toutefois la référence à chacune de ces cellules parle signe « : ».
En mode édition de formule, la désignation d’une plage peut être faite à la souris en sélectionnant la plage.
Si l’on a besoin de référencer des cellules se trouvant dans plusieurs plages (par exemple ici pour calculer la moyenne générale), il suffit de désigner chaque plage comme précédemment, et de séparer les références à chaque plage par le signe « ; ».
Ainsi la formule « =MOYENNE(B3:B5;D3:D5) » signifie : calculer la moyenne des valeurs se
trouvant dans la plage délimitée par les cellules B3 et B5 (utilisation de « : » entre B3 et B5) et (utilisation de « ; ») dans la plage délimitée parles cellules D3 et D5 (utilisation de « : » entre D3 et D5)Avec la souris, en mode édition de formule, il suffit de sélectionner les différentes plages tout ne appuyant sur la touche Ctrl.

Références de cellules : bilan

Lorsque des calculs similaires ont lieu en plusieurs endroits du tableau, il faut se poser la question de l’utilisation des références absolues, relatives oumixtes.
Si je copie la formule sur une même ligne, est-ce-que la cellule référencée doit se déplacer de la même façon ?
• Si oui, la référence sur la colonne doit être relative.
• Si non, la référence sur la colonne doit être absolue.
Si je copie la formule sur une même colonne, est-ce-que la cellule référencée doit se déplacer de la même façon ?
• Si oui, la référence sur la ligne doit être relativ.
• Si non, la référence sur la ligne doit être absolue.

Références par nom

Au lieu de désigner une cellule par des coordonnées, on peut utiliser un nom, à condition que l’on ait défini auparavant à quelle cellule se rapporte ce nom.
Dans le second exemple, on aurait pu référencer lacellule E2 par le nom MULTIPLICATEUR.
La formule à écrire dans la cellule B5 aurait alors été « =MULTIPLICATEUR*B4 ».
Pour pouvoir utiliser une référence par nom, deux tapesé sont nécessaires :
1. Il faut d’abord affecter un nom à une cellule. Cela se fait en tapant le nom dans la fenêtre d’édition des noms ou par l’intermédiaire du menuInsertion/Nom/Définir.
2. On peut ensuite utiliser ce nom pour référencer cette cellule dans une formule. La fenêtre d’édition des noms permet d’introduire facilement esd noms dans les formules.
L’utilisation de la référence par nom procure deuxavantages :
• Les formules deviennent plus lisibles : une formule du type « =MONTANT_HT*(1+TVA) » est bien plus explicite qu’une formule du type =C2*(1+$D$2) (en supposant que le nom de la cellule C2 est MONTANT_HT et celui de la cellule D2 est TVA).
• En utilisant une référence par nom, la référence solueab de la cellule devient transparente. Si pour une raison ou pour une autre on est amené à indiquer la TVA dans la cellule B2 (au lieu de la cellule D2), il suffit de faire porter le nom TVA sur cette nouvelle cellule. Ainsi les cellules utilisant la référence par nom en seront pas à modifier au contraire des cellules qui utilisaient la référence absolue $D$2,qu’il faudrait aller modifier à la main.

Portée des noms

Les noms définis peuvent être utilisés dans tout le classeur ce qui signifie qu’un nom est défini pour tout le classeur. Ceci implique qu’il ne peut exister qu’une seule cellule ou plage de cellules associée à un nom. Par exemple, le nom taux_de_tva désignera une cellule unique d’une feuille du classeur.
Si nous reprenons l’exemple de l’étude précédente, les feuilles pour les différents représentants sont identiques. Pour les calculs dans la feuille d’un représentant, on souhaite désigner la plage B6:E6 par le nom ventes. Ce nom doit être local à la feuille.
On peut commencer par nommer les feuilles des représentants. Par défaut, les feuilles s’appellent
Feuil1, …
Pour renommer une feuille, double cliquer sur l’onglet avec son nom, vous pouvez alors la renommer.
Pour définir un nom de cellule local à la feuille, sélectionnez la cellule, dans la fenêtre de saisie des noms, taper le nom de la feuille suivi d’un « ! » suivi du nom de la cellule. Par exemple la feuille du représentantDupont a pour nom feuildupont, je sélectionne la zone B6:E6, je la nomme feuildupont! ventes. Pour utiliser cette plage dans la feuille, il suffit alors de la désigner par le nom ventes, pour utiliser cette plage dans une autre feuille, il suffit alors de la désigner par le nom feuile du pont ventes.
Une deuxième solution, que nous vous recommandons, consiste à créer la feuille pour un représentant : créer la maquette, nommer les cellules et plage de cellules, entrer les formules, faire les mises en forme. Puis, dupliquez cette feuille autant de fois qu’il y de représentants et enfin renomme les feuilles. Lors de la duplication, les noms seront alors considérés comme locaux à chacune des feuilles.

Organisation et choix du type de référence à utiliser

Comment choisir tel ou tel mode de référence ? Le hoixc d’un certain type de référence ne doit pas se faire au hasard, mais doit résulter d’un minimum deréflexion afin de construire efficacement sa feuile de calcul. Il faut avoir en tête les principes suivants :
• Les cellules isolées qui contiennent des données fixées doivent être référencées par nom, ce nom sera choisi pour qu’il décrive le contenu de lacellule (tva, tauxchange, réduction, …).
• Pour garantir la cohérence de la feuille de calcul, il faut que chaque donnée n’apparaisse qu’une seule fois. Par exemple, il est hors de question que le montant de la TVA apparaisse dans deux cellules D2 et F6. Envisagez par exemple le jour où la TVA passe de 18,6% à 20,6% : que se passera-t-il si vous modifiez la cellule D2 et pas la cellule F6 ? Réponse : la feuille de calcul risque fort de comporter des résultats incohérents !
• Attribuer des noms aux plages de cellules à chaque fois que cela est nécessaire et possible. Les formules sont plus faciles à élaborer, à relire ou à corriger. Les erreurs de références sont moins courantes avec l’utilisation de noms.