Préparé par Luc Baron
Le 10 septembre 2021
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.
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.
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.
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.
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.
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.
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. |
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.
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.
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.
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.
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.
À 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.
| 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. |
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.
| Astuce : Une référence absolue par nom est valide pour toutes les feuilles d'un même classeur. |
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)*B3 où B$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.
| 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. |
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.
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.
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 |
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 :
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.
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 :
Le résultat de ce tri décroissant est encerclé en bleu à la Figure D.15.
Le résultat de ce tri croissant permet de revenir à l'état initial de la Figure D.14.
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.