Préparé par Luc Baron
Édition du 11 mars 2024
Le module D porte sur les notions de base d'Excel qui seront nécessaire pour le module suivant sur la programmation VBA dans Excel. Il contient des notions peu avancées, telles que l'ouverture, l'impression et la sauvegarde de fichiers, ainsi que des notions connues, mais non maîtrisées par tous, telles que les références relative, absolues, absolues par nom ou externes. Finalement, il contient des notions plus avancées comme l'importation et la validation des données et une introduction à la création de macros VBA. 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 Worksheet, 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.
En français, Excel utilise par défaut la virgule comme symbole de séparation des décimales et l'espace blanc comme symbole de séparation du groupement des milliers (tel que 1 534 213,74). En anglais, c'est le point qui est utilisé comme séparateur des décimales et la virgule comme séparateur des milliers (tel que 1,534,213.74). Ainsi, lorsque l'on inscrit un nombre en format anglais dans un Excel en format français, le nombre sera interprété comme du texte plutôt qu'un nombre, et aucune opération arithmétique ne sera possible. Le choix de symboles de séparation des décimales peut complexifier l'importation de données de sources angloxaonnes.
| Recommandation : Choisir le point comme séparateur des décimales et l'espace blanc comme séparateur des milliers. Vos fichiers seront automatiquement convertis à leur prochaine ouverture par Excel. |
Sous MacOS, le choix du symbole décimale peut se faire dans les Réglages système... de MacOS, et aussi, dans Excel. Tel que montré à la Figure D.3, il faut:
Puisque le choix des formats de nombre est très limité, il est possible de spécifier un choix différent pour Excel. Tel que montré à la Figure D.4, il faut:
Sous Windows, le choix du symbole décimale se fait dans les Paramètres régionaux de Windows plutôt que dans Excel. Tel que montré à la Figure D.5, il faut:
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.6 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.
| 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. |
| Fichier CSV (choix de délimiteur) | .csv | Format texte avec les données séparées par un délimiteur. |
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.7, 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.8. 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 D.9, 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.10 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.11, 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.12, 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.14, 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.15, 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 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 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 |
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 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 apostrophe ('), alors elle sera interprétée comme du texte. Lorsqu'un nombre est entrée avec le mauvais symbole de décimale, il est interprété comme du texte et aucune opération arithmétique ne pourra être effectuée avec celui-ci. La Figure D.16 montre le ruban Données d'Excel. Les outils Obtenir des données à partir d'un fichier CSV, Trier des données, Valider des données et Sauvegarde des données dans un fichier CSV sont présentés dans cette section.
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.17, 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.18 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.19.
Le résultat de ce tri croissant permet de revenir à l'état initial de la Figure D.18.
Lorsqu'il existe un choix limité de données différentes, il est préférable de présenter à l'usager un choix dans une liste déroulante, afin d'uniformiser et faciliter l'entrer de ces données. L'outil Validation des données permet de valider les données entrées dans une cellule spécifique. Tel que montré à la Figure D.20, la cellule B2 peut être configuré en liste déroulante avec les 7 étapes suivantes:
La Figure D.21 montre la liste déroulante de la cellule B2. La liste source I1:I14 pourrait être déposée sur une feuille masquée pour ne pas surcharger l'affichage.
Pour exporter les données d'une feuille vers un fichier texte, il faut sauvegarder en format .csv par Fichier>Enregistrer sous (#1), choisir le répertoire (#3) et le nom (#2), le format (#4), tel que CSV UTF-8 avec virgule ou point-virgule comme délimiteur, puis Enregistrer (#5).
Dans l'application Excel, comme dans plusieurs autres, il est possible de programmer en Visual Basic de nouvelles fonctionnatlitées. Vous avez alors accès à l'ensemble des fonctions de bases déjà présentes dans cette application pour automatiser vos nouvelles opérations. L'enregistrement d'une macro VBA est la façon la plus simple de créer un programme VBA. La gestion des macros est accessibles par l'onglet Développeur.
Le ruban Développeur n'est habituellement pas présent dans le menu Excel. Il est donc nécessaire de l'ajouter par une séquence de commandes qui est différente sur Windows et MacOS.
Pour ajouter le ruban Développeur sur Windows, il faut :
L'onglet Développeur est alors ajouté à la barre de menu d'Excel. Il faut ensuite ajuster le niveau de sécurité pour l'exécution des macro et des contrôles ActiveX, c'est-à-dire :
Le ruban Développeur contient l'outil Visual basic pour accéder à la fenêtre VBA, l'outil Macros pour exécuter les macros, l'outil Enregistrer une macro pour enregistrer une séquence de commandes dans une macro, ainsi que l'outil insertion de Boutons de contrôles.
Pour ajouter le ruban Développeur sur MacOS, il faut :
Onglet Développeur est alors ajouté à la barre de menu d'Excel. Une fois sélectionné, le ruban Développeur apparait comme à la Figure D.26.
On désire enregistrer des commandes de mise en forme d'un fichier dans une macro appelée Macro1. On désire également lui associer le raccourci clavier Ctrl-Shift-A pour démarrer son exécution. Il suffit de :
La fenêtre de développement VBA de la Figure D.28 s'affiche lors que l'on sélectionne l'outil Visual basic du ruban Développeur. Pour afficher le contenu d'une macro, il faut :
Apparament, la création d'une macro est la traduction en code VBA d'une séquence de commandes souris-clavier comme le montre la fenêtre de codes de la Figure D.28. Les mots-clef du langage VBA sont affichés en bleu, tels que Sub ... End Sub ou bien With ... End With et aussi True. L'orthographe majuscule/minuscule de ces mots-clefs est automatiquement corrigé par l'éditeur VBA. Le simple apostrophe (') indique que le reste de la ligne est un commentaire qui s'affichera en vert. La sélection des cellules A1:D1 de la ligne de titre est effectuée par Range("A1:D1").Select.
Plusieurs propriétés Interior des cellules de cette sélection sont modifiées par le With. La fonte de caractères gras est activée par
Selection.Font.Bold = True. Une nouvelle sélection est effectuée sur la plage C2:D6 par Range("C2:D6").Select. La propriété NumberFormat de cette sélection est modifiée par Selection.NumberFormat = "#,##0.00 $". Finalement, la cellule A1 est sélectionnée par Range("A1").Select.
Pour sauvegarder une macro avec son classeur, il est nécessaire de choisir le format .xlsm (classeur prenant en charge les macros) plutôt que le format habituel .xlsx qui ne sauvegardera que le contenu du classeur Excel.
Une macro VBA peut être exécutée de plusieurs façons avec ou sans le ruban Développer.
Toute macro VBA peut être exécutée avec l'outil Macros de le ruban Développer. Il suffit de :
Dans la fenêtre Exécution (afficher avec Ctrl-G) de l'environnement de développement VBA, il suffit d'inscrire une commande d'exécution de la macro, telle que: Call Macro1().
Dans l'exemple de la Figure D.27, le raccourci clavier Ctrl-Shift-A est défini lors de l'enregistrement de Macro1.
Afin de faciliter l'accès à l'exécution d'une macro, nous allons ajouter une icône de Tirelire à la barre d'outils Accès rapide du classeur. Ainsi, un clic sur la Tirelire démarre l'exécution de la macro, dans cet exemple FormatVentes. Comme montré à la Fig. D.30, les opérations suivantes sont nécessaires pour personaliser la barre d'outils Accès rapide :
Une macro spécifique peut être exécutée par au moins 4 moyens différents: 1) un clic sur une icône de la barre d'outil Accès rapide, telle que la Tirelire; 2) un raccourci clavier, tel que Ctrl-Shift-A; 3) l'outil Macros du ruban Développeur et 4) une commande, telle que Call Macro1(), dans la fenêtre Exécution de l'environnement de développement VBA.
L'application Excel est devenue au cours des années une super calculatrice permettant de manipuler et traiter de grands volumes d'informations. Cependant, la grandeur maximale des feuilles de calcul ou le nombre maximum de ligne dans un fichier CSV est souvent atteint. L'utilisation de programme VBA est un moyen d'optimiser l'usage des ressources d'Excel et étendre la portée de son utilisation.