Module D - Excel : Notions de base

Préparé par Luc Baron
Le 10 septembre 2021

1 Introduction

1.1 Objectifs

Le module D n'est pas de niveau universitaire. Il ne fait pas partie intégrante du cours. Cependant, le module E sur la programmation VBA dans Excel nécessite des connaissances de base sur Excel. Ainsi, le module D présente les notions que les étudiants devraient déjà connaître sur Excel afin de se préparer pour le module E. Le module D comprend le présent document de référence, ainsi que deux tests Moodle avec rétroactions immédiates permettant un auto-apprentissage. Les étudiants ayant besoin d'un rappel ou d'une mise à jour des notions de base sont donc invités à lire et à faire les exercices pratiques. Les autres étudiants peuvent simplement parcourir le module D afin de valider qu'ils possèdent déjà toutes les connaissances requises.

1.2 Outil générique

Un tableur est une application capable de manipuler des feuilles de calcul. À l'origine, destinée au traitement automatisé de données commerciales et financières, les tableurs sont désormais utilisés pour effectuer des tâches plus variées tels que la gestion de bases de données simples ou la production de graphiques, en passant par des tâches de calculs techniques de complexité faible. Une feuille de calcul, appelé en anglais spreadsheet, est une grille ou un tableau d'informations structurés et configurables.

1.3 Interface graphique

Tel que montré à la figure D.1, l’interface graphique usager de Microsoft Excel 365 se compose d’une barre de titre contenant le nom du fichier Excel ouvert, d’une barre de menu, d’une barre d'outils à accès rapide (reconfigurable par l'usager) et les contrôles habituels de la fenêtre Windows. Le ruban affiche les outils associés au menu sélectionné, soit Accueil. La cellule active (colonne B et ligne 2) s'affiche avec un encadré vert. Au-dessus de la feuille de calcul, il y a la zone d'adresse et de saisie d'une donnée ou d'une formule dans la cellule active. En bas à gauche, on retrouve les outils de navigation et les onglets des feuilles, ainsi que le bouton + pour ajouter une feuille. À droite, on retrouve les barres de défilement horizontale et verticale, ainsi que les contrôles du zoom de la feuille.


Win
Fig. D.1 - Interface graphique usager de Microsoft Excel 365 sous Windows

La figure D.2 montre l'interface graphique usager de Microsoft Excel 365 sous MacOS. Celle-ci est identique à celle sous Windows, sauf pour le menu Fichier qui se trouve sur la barre de menu du Mac plutôt que dans la fenêtre de l'application Excel.


Win
Fig. D.2 - Interface graphique usager de Microsoft Excel 365 sous MacOS

Comme dans toutes les applications Windows et MacOS, la touche Ctrl (ou Command sous MacOS) du clavier peut être utilisée en combinaison avec le bouton de gauche de la souris pour sélectionner individuellement plusieurs objets sur lesquels vous désirez effectuer une même opération. Il est également possible d’utiliser la touche Shift pour ne sélectionner que le premier et le dernier objet d’une liste pour automatiquement sélectionner tous les objets intermédiaires.

2 Classeurs

L'application Excel permet de créer, configurer et sauvegarder des fichiers appelés Classeur. Au démarrage, l'application Excel permet de créer un nouveau classeur ou d'ouvrir un classeur existant. Chaque classeur peut contenir une ou plusieurs feuilles de calcul, nommé par défaut Feuil1, Feuil2, etc.. On sélectionne une feuille par un clic gauche sur l’onglet correspondant. Un double clic gauche sur l’onglet permet de renommer la feuille, alors qu’un clic droit permet d'afficher le menu contextuel de l’onglet permettant de copier, déplacer, supprimer ou insérer des feuilles.

2.1 Sauvegarde

La sauvegarde d'un classeur se fait par le menu Fichier>Enregistrer sous>Parcourir, afin d'obtenir la fenêtre de la figure D.3 qui permet de choisir le répertoire, le nom et le type de fichier à enregistrer. La table ci-dessous présente quelques un des principaux types, extensions et descriptions de format de sauvegarde de fichier Excel.


Type Extension Description
Classeur Excel .xlsx Format Excel de dernière génération.
Classeur Excel prenant en charge les macros .xlsm Sauvegarder le classeur et les macros.
Classeur Excel 97-2003 .xls Format Excel des versions 1997 à 2003.
CSV (séparateur : point-virgule) .csv Format texte avec les données-cellules séparées par un point-virgule.

Win
Fig. D.3 - Sauvegarde d'un classeur Excel de type .xlsx sous MacOS

2.2 Impression

Plusieurs information peuvent être imprimées à partir d'un classeur. Afin d'obtenir un résultat de qualité, il est préférable de définir manuellement la zone d'impression. Tel que montré à la figure D.4, il faut d'abord encadrer les cellules à imprimer, puis par le menu Mise en page>ZoneImpr>Définir définir la zone d'impression.


Win
Fig. D.4 - Définition de la zone d'impression

Ensuite, il suffit d'utiliser le menu Fichier>Imprimer, afin d'obtenir la fenêtre de contrôle de la mise en page avec l'aperçu de l'impression, tel que montré à la figure D.5. Dans cet fenêtre, l'impression ne se fait pas sur une imprimante physique, mais plutôt dans un fichier PDF avec le service d'imprimante virtuelle PDFCreator.


Win
Fig. D.5 - Mise en page et aperçu de l'impression sous MacOS

3 Plages et cellules

Chaque feuille comporte un maximum de 65 536 lignes (numérotée de 1 à 65 536) et un maximum de 256 colonnes (numérotée alphabétiquement de A à IV). L’intersection d’une colonne et d’une ligne est appelée cellule. Chaque cellule peut contenir soit : rien (initialisé par défaut à NULL), du texte, des nombres ou une formule. Chaque cellule possède une adresse identifiée par les coordonnées de la colonne et de la ligne. Tel que montré à la Figure 6, la cellule active (encadré en vert) est située à la colonne D et la ligne 2. Les étiquettes de colonne et étiquettes de ligne de la cellule active sont plus foncées afin de faciliter son identification. La zone de nom affiche l'adresse D2 et la zone de saisie affiche le contenu de la cellule active, soit le nombre 1551.


Win
Fig. D.6 - La cellule active encadrée en vert ayant l'adresse D2 et le contenu 1551

3.1 Sélections

Un groupe de cellules juxtaposées, ou même disjointes, forme une plage de cellules. Pour sélectionner toutes les cellules d'une colonne ou d'une ligne, il suffit de sélectionner l’étiquette de la colonne ou de la ligne. Pour sélectionner toutes les cellules d'une feuille, vous pouvez sélectionner l’étiquette feuille entière située à l'intersection des étiquettes de colonnes et de lignes. cellules situées entre deux lignes et deux colonnes est appelées plage. La touche Shift peut être utilisée pour sélectionner un bloc de cellule ou la touche Ctrl/Command pour ajouter des cellules disjointes. Le bloc de cellules de la colonne C à E et de la ligne 2 à 4 peut être désigné par l'adresse de plage de cellules C2:E4. La Figure D.7 illustre le résultat de la sélection d’un groupe disjoint dont la plage de cellules est C2:E4,C6:E7,A1,A5. La cellule active A5 est la dernière sélectionnée et fait partie de la sélection. Cette plage peut alors être utilisée pour effectuer une même opération sur toutes ces cellules.


Win
Fig. D.7 - Sélection d'un groupe de cellules disjointes C2:E4,C6:E7,A1,A5

3.2 Références relative

Les feuilles contiennent souvent des formules mathématiques afin de traiter des données. Ces formules doivent faire référence à une ou plusieurs autres cellules (ou même des plages de cellules) par leurs adresses. À la Figure D.8, la cellule B7 contient la formule =SOMME(B3:B6). La plage B3:B6 utilise une adresse relative à la cellule B7, soit la somme des contenus des 4 cellules au-dessus de B7. Une référence relative permet de copier la fonctionnalité additionne le contenu des 4 cellules juste au-dessus dans d'autres cellules sans avoir à modifier la plage de la formule. Ainsi, la copie de =SOMME(B3:B6) de B7 vers C7, puis D7, permet d’obtenir =SOMME(C3:C6) et =SOMME(D3:D6), respectivement.


Win
Fig. D.8 - Référence relative

3.3 Références absolues

À la Figure D.9, la cellule B9 contient la formule =(1+$B$1)*B7 permettant d’appliquer le pourcentage d’accroissement (contenu à la cellule B1) sur le total des dépenses de janvier 2006 (contenu à la cellule B7), où B7 est une référence relative à B9, alors que $B$1 est une référence absolue à la cellule B1. Ainsi, la formule =(1+$B$1)*B7 demande donc d’effectuer la multiplication entre 1 plus le contenu de la cellule B1 par la 2e cellule au-dessus de la cellule où déposer le résultat. L’usage de la référence absolue $B$1 dans la formule empêche le tableur de modifier cette adresse lors de la copie vers d’autres cellules. Ainsi, la copie de =(1+$B$1)*B7 de B9 vers C9, puis D9, permet d’obtenir =(1+$B$1)*C7 et =(1+$B$1)*D7, respectivement.


Win
Fig. D.9 - Référence absolue et relative

Astuce : Un bon usage des références relatives et absolues permet d'éviter d'entrer un grand
nombre de formules. Une simple copie permet de les obtenir directement.

3.4 Références absolues par nom

Pour une référence absolue, il est souvent plus facile d’attribuer un nom significatif à la cellule afin d’utiliser directement ce nom dans les formules. Par exemple, on peut attribuer le nom Taux à la cellule B1, la formule B9 devient alors =(1+Taux)*B7 qui une fois copiée dans C9 et D9 devient =(1+Taux)*C7 et =(1+Taux)*D7. Comme montrée à la Figure D.10, la zone de nom peut être utilisée pour attribuer un nom absolu à une cellule ou une plage de cellules. Alternativement, il est plus flexible d'utiliser l'outil de gestion des noms de plage. Sous Windows, il suffit de sélectionner la plage, puis de faire un clic droit pour obtenir le menu contextuel, puis choisir Définir un nom. Sous MacOS, il suffit de sélectionner la plage, puis choisir dans la barre de menu MacOS Insérer>Nom>Définir un nom.


Win
Fig. D.10 - Références absolue par nom
Astuce : Une référence absolue par nom est valide pour toutes les feuilles d'un même classeur.

3.5 Références mixtes

Une référence mixte permet d’adresser de façon différente la colonne et la ligne d’une même cellule. À la Figure D.11, un taux de croissance différent par mois est utilisé pour les quatre postes de dépenses. La cellule B8 contient la formule =(1+B$1)*B3B$1 est une référence mixte au taux de croissance du mois, c’est-à-dire une référence relative à la colonne B et une référence absolue à la ligne 1. Ainsi, une copie de la formule de la cellule B8 vers C8 et D8 produit les formules =(1+C$1)*C3 et =(1+D$1)*D3. De même, la copie de la formule B8 vers les cellules B9, B10 et B11 produit les formules =(1+B$1)*B4, =(1+B$1)*B5 et =(1+B$1)*B6. Clairement, l’utilisation d’une référence absolue à la cellule B1 n’aurait pas pu produire les formules des trois colonnes où la lettre de la colonne a été automatiquement modifiée lors de la copie.


Win
Fig. D.11 - Références mixte à une cellule
Astuce : Une référence mixte est utile lorsque l'on désire modifier automatiquement, soit la colonne, soit la ligne,
de l'adresse d'une cellule lors de la copie vers d'autres cellules.

3.6 Références externes

Une référence externe à une feuille permet de faire appel à des cellules se trouvant dans d’autres feuilles d’un même classeur sans devoir copier manuellement ces données d'une feuille à l'autre. Le point d’exclamation ! sert de séparateur entre le nom de feuille et l’adresse d’une cellule.


À gauche de la Figure D.12, la cellule A1 de la Feuil1 contient la formule =Feuil2!A1 qui lui demande de récupérer le contenu 1 de la cellule A1 de la feuille Feuil2 et d’afficher le résultat en Feuil1!A1. Au centre de la Figure D.12, La cellule C1 de la Feuil2 contient la formule =SOMME(Feuil2!B1:B3;A3) qui lui demandant d'additionner le contenu de la plage B1:B3 de la Feuil1 et le contenu de la cellule A3 de la feuille active Feuil2. Le point virgule sert de séparateur entre les éléments dans la fonction SOMME( ... ; ... ). À droite de la Figure D.12, la cellule A1 de la Feuil3 contient la formule =SOMME(Feuil1:Feuil2!A1:A3) qui lui demandant d'additionner le contenu de la plage A1:A3 de la plage de feuilles Feuil1:Feuil2. Le : peut aussi servir à définir une plage de feuilles.


Win Win Win
Fig. D.12 - Références externe à d'autres feuilles

Une référence externe à un classeur permet de faire appel à des cellules se trouvant dans un autre classeur. Les crochets [ ... ] servent à délimiter le nom du classeur devant le nom de la feuille et de l'adresse de la cellule.


Par exemple, la cellule A1 de la Feuil1 du classeur Classeur1.xlsx contient la formule =[Classeur2.xlsx]Feuil2!F12 qui demande de récupérer le contenu de la cellule F12 de la Feuil2 du classeur Classeur2.xlsx. Il est possible d’utiliser les cartes blanches dans les noms de classeurs afin de désigner, par exemple, tous les classeurs Figure??.xlsx (où ?? désigne deux caractères quelconques) ou Figure*.xlsx (où * désigne plusieurs caractères quelconques). Lorsque le nom du classeur contient des espaces, alors il est nécessaire d'indiquer celui-ci entre simples guillemets, tel que =’[Classeur resultats.xlsx]Feuil2’!F12.

4 Formules et données

Une cellule peut contenir soit : 1) rien (initialisée à NULL par défaut), 2) une formule ou 3) une donnée. Si une donnée débute par un chiffre (0, 1, 2, 3, 4, 5. 6. 7, 8, 9) ou un symbole mathématique (+, -, *, /, ^), alors la celle-ci est interprétée comme un nombre, sinon elle sera interprétée comme du texte. Si une donnée débute par ', alors elle sera interprétée comme du texte. Lorsqu'un nombre est interprété comme du texte, aucune opération arithmétique ne peut être effectuée avec celle-ci. Une formule doit débuter par le symbole =. Le tableau ci-dessous présente un résumé des principales formules utiles dans ce cours.


Syntaxe Exemple Description
= adresse_cellule =B3 Copie le résultat de la cellule B3
= operation_arithmetique =A1*(D1+D2-D3)^2/C7 Calcul l'opération arithmétique
= NB( plages ) =NB(B2:B6) Compte le nombre de cellule ayant une valeur numérique
= SOMME( plages ) =SOMME(A1;A5:A9) Additionne les valeurs des cellules A1 et de A5 à A9
= MOYENNE( plages ) =MOYENNE(B1:B4;B6:B9) Calcul la moyenne des cellules B1 à B4 et B6 à B9
= MAXIMUM( plages ) =MAXIMUM(A1:B9) Recherche le maximum du bloc A1 à B9
= MINIMUM( plages ) =MINIMUM(A1;A5:C9) Recherche le minimum de A1 et du bloc A5 à C9
= SI( critère; vrai; faux ) =SI(A1>100;"Oui";"Non") Affiche Oui si A1 est plus grand que 100
= NB.SI( plage; critère ) =NB.SI(A1:B5;4) Compte le nombre cellule égale à 4
= SOMME.SI( plage; critère ) =SOMME.SI(B4:B9;">=85") Additionne les cellules plus grandes ou égale à 85

4.1 Importer

Il est possible d'importer dans une feuille les données se trouvant dans un fichier texte externe avec le menu Données>Obtenir les données>Fichier texte. Vous devez alors choisir le fichier texte à importer, puis faire Obtenir les données. L'assistant d'importation des données vous présente un aperçu et permet de :

  1. spécifier le numéro de la première ligne de données, ainsi que la méthode de séparation des colonnes (largeur fixe ou délimiteur);
  2. choisir le délimiteur à utiliser (tabulation, virgule, ...);
  3. choisir le format des données de chaque colonne.

Finalement, il faut spécifier la cellule de début ou la zone d'importation des données.spécifier dans la feuille la zone d'importation des données. À la Figure D.12, on demande d'importer les données à partir de la ligne 2, c'est-à-dire sans la ligne de titre. La séparation des données se fait par le délimiteur tabulation et le format numérique a été automatiquement détecté par l'assistant. Dans la feuille, les valeurs numériques sont normalement justifiées à droite, alors que le texte est justifiées à gauche.


Win Win
Win Win
Win
Fig. D.13 - Assistant d'importation des données

4.2 Ordonner

Avec un tableur, il est facile d'ordonner selon un ou plusieurs critères une plage de données sans modifier le reste de la feuille. La Figure D.14 montre l’utilisation de l'outil Trier du plus grand au plus petit (#3) de l'onglet Données (#1). Afin d'ordonner du plus grand au plus petit les lignes de la plage A2:D6 selon la valeur de la colonne D, il suffit de :

  1. sélectionner l'onglet Données;
  2. sélectionner l'étiquette de la colonne D;
  3. sélectionner l'outil de tri décroissant;
  4. sélectionner l'option Étendre la sélection aux autres colonnes;
  5. sélectionner Tri pour effectuer l'opération.

Le résultat de ce tri décroissant est encerclé en bleu à la Figure D.15.


Win
Fig. D.14 - Utilisation de l'outil Trier du plus grand au plus petit

La Figure D.15 montre l’utilisation de l'outil général Trier (#1). Cette fois, on désire ordonner les lignes de la plage A2:D6 par ordre croissant selon la valeur de la colonne A. Ainsi, il suffit de :

  1. sélectionner l'outil général de tri;
  2. sélectionner l'option Ma liste contient des en-têtes afin de ne pas déplacer cette ligne;
  3. sélectionner colonne A (ayant l'en-tête No) comme critère de tri;
  4. sélectionner l'ordre Du plus petit au plus grand;
  5. sélectionner OK pour effectuer l'opération.

Le résultat de ce tri croissant permet de revenir à l'état initial de la Figure D.14.


Win
Fig. D.15 - Utilisation de l'outil général Trier

4.3 Exporter

Pour exporter les données d'une feuille vers un fichier texte, il faut simplement faire Fichier>Enregistrer sous>Parcourir, choisir le répertoire et le nom du fichier texte, puis le format de fichier (appelé type sous Windows) .csv avec la virgule comme délimiteur, par exemple.


Copyright 2021 Polytechnique Montréal par Luc Baron