Cours Microsoft Excel Niveau 1 par la pratique
Qu'est ce qu'un tableur ?
Définition (c) Larousse : programme de création et de manipulation interactives de tableaux numériques visualisés.
Un tableur est un programme d'application (ou logiciel) utilisé pour l'établissement de budgets, de planifications ou d'autres tâches comptables. Dans un tableur, les données et les formules se présentent sous forme de tableaux (appelés feuilles de calcul) et permettent l'analyse, le suivi de données...
Les tableurs utilisent des lignes et des colonnes de cellules. Chaque cellule peut contenir du texte, des données numériques ou des formules utilisant des valeurs d'autres cellules pour calculer un résultat attendu. Pour faciliter le calcul, les tableurs disposent de fonctions intégrées qui exécutent des calculs standards.
Un tableur comme Excel peut également lier plusieurs feuilles de calcul entre elles et mettre automatiquement à jour les données des feuilles de calcul ainsi liées. Cette automatisation des traitements en fait un excellent outil de modélisation.
Excel peut être utilisé pour la création et le tri de bases de données.
Il dispose également de représentations des données sous forme de graphiques ou diagrammes.
En privé, Excel peut servir, par exemple à faire un budget, gérer des comptes en banque, calculer un emprunt ...
Dans les entreprises, il sert souvent à l'analyse de chiffres d'affaire, analyse des coûts, détermination des prix... bien d'autres applications sont envisageables, que ce soit dans les domaines scientifiques , statistiques ou autres...
Le principal intérêt de ce type de logiciel est la modélisation, c'est à dire la possibilité d'actualiser en instantané des données et leur traitement, et la possibilité d'envisager plusieurs scénarios ou hypothèses.
Aujourd'hui Excel est le tableur le plus répandu.
Présentation de l'environnement
Lorsque Excel est lancé, un nouveau classeur s'ouvre par défaut ( les fichiers d'Excel, avec l' extension .xls, s'appellent des "classeurs" ).
Voici (à peu près) la fenêtre qui s'affiche.
Ecran Excel
L'écran d'Excel présente comme pour toutes les applications de la suite Office, les caractéristiques classiques des fenêtres :
La barre de formule et la zone Nom.
La barre de formule peut être affichée ou masquée par le menu Affichage / Barre de formule.
A gauche de la barre de formule se trouve la zone nom, qui indique l'adresse de la cellule active. Par défaut, la cellule est indiquée par la colonne, lettre, et la ligne, numéro.
La barre d'état
La barre d'état, au bas de le fenêtre, informe sur les actions attendues.
Par exemple en double cliquant dans la cellule, la barre d'état nous invite à modifier
A la copie de cellule, elle nous invite à choisir une destination...
Ou encore lors des manipulations avec la souris
La barre d'état peut être affichée ou masquée par le menu Affichage / Barre d'Etat
La feuille de calcul
L'espace de travail, qui nous apparaît comme un tableau est une feuille de calcul composée de colonnes et de lignes de cellules.
La feuille de calcul occupe la totalité de l'espace de travail. Elle dispose de 65 536 lignes (numérotées) et de 256 colonnes (référencées par des lettres de A à IV).
Les informations sont saisies dans une seule cellule à la fois, la cellule active. Visible par son contour épais, sa position est affichée dans la zone nom, à gauche de la barre des formules (par défault A1)
Se déplacer dans le feuille de calcul
M Au clavier
- les flèches de direction permettent un déplacement de ligne à ligne ou colonne à colonne
- Page Suiv ou Page Préc. déplace la cellule active du nombre de lignes visibles vers le bas ou vers le haut.
- ALT+ Page Suiv ou Page Préc déplace la cellule active du nombre
de colonnes visibles vers la droite ou vers la gauche
- CTRL + ORIGINE amène le curseur sur la cellule A1
- CTRL + FIN amène le curseur à la denière cellule modifée en bas à droite de la feuille.
- SHIFT + déplacement étend la sélection.
M Avec la souris
- les barres de défilement. La cellule active peut alors n'être plus visible.
- maintenir le click étend la sélection, si le pointeur de souris glisse vers un bord de fenêtre, la feuille de calcul défile plus ou moins vite selon la position du pointeur.
M Dans la zone Nom, saisir l'adresse soit de la cellule à atteindre, soit d'une plage de cellule à sélectionner.
Se déplacer dans le classeur
Au bas à gauche de la feuille de calcul se trouvent les quatre flèches de déplacement dans le classeur (début, précédent, suivant, fin) et les onglets des feuilles qui constituent le classeur. Sélectionnés, ils sont blancs, sinon grisés. Ils contiennent le nom de feuille.
M Au clavier
o CTRL + Page Suiv ou Page Préc affiche la feuille suivante ou précédente
o CTRL + SHIFT + Page Suiv ou Page Préc sélectionne plusieurs feuilles
M Avec la souris
o Click sur l'onglet sélectionne la feuille
o Double click permet de modifier le nom de la feuille
o Click + SHIFT étend la sélection.
o Click + CTRL sélectionne les feuilles de manière dissocièe
o Click droit sur les flèches affiche la liste des feuilles
o Click droit sur les onglets sélectionnés, affiche le menu : Insérer.. , Supprimer, Renommer, Déplacer ou Copier, Sélectionner toutes les feuilles.
M Par les menus
o Edition / Supprimer une feuille
o Edition / Déplacer ou Copier une feuille...
o Insertion / Feuille
o Format / Feuille / Renommer
o Format / Feuille / Masquer ou Afficher; les feuilles de calcul peuvent être masquer.
o Outils / Protection / Protéger la feuille... , ce qui peut être utile pour sauvegarder les formules
Les modifications de cellules s'effectuent sur toutes les feuilles sélectionnées.
Le contenu
Une feuille est constituée de cellules, mais peut contenir toutes sortes d'objets
n Les objets de la barre dessin, formes automatiques, images, WordArt, n Des objets liés d'autres applications word , ou PowerPoint n Des graphiques
n Des contrôles, tels des listes déroulantes, cases à cocher...
Les options de la feuille
Par le menu Outils / Options... / onglet Affichage, il est possible de modifier l'apparence, ne pas afficher le quadrillage, ou les entêtes de lignes et de colonnes, les barres de défilement, ou les onglets du classeur.
La cellule
Le contenu d'une cellule
Elle est l'élément de base du tableur, à la croisée des colonnes et des lignes
On la sélectionne dans le classeur soit en se déplaçant avec les flèches de directio clavier, soit en pointant et cliquant avec la souris. On peut y saisir au clavier :
Du texte :
Des valeurs :
Des formules :
Des fonctions :
La modification de cellule
Pour modifier le contenu d'une cellule, il faut :
On peut activer ou désactiver la modification directe dans les cellules, par le menu O / Options; onglet Modifier, case à cocher Modification directe. Le contenu sera alors modifiable dans la barre de formule
Pour ajouter rapidement du contenu de la cellule, sélectionnez la, puis appuyez sur F Excel affiche en couleurs les cellules auxquelles le calcul ou la fonction fait référenc
Sélectionner des cellules
Sélection étendue, plage de cellules
Plusieurs cellules peuvent être sélectionnées en même temps, mais une seule reste active, ici A2
Pour sélectionner (comme l'image ci-dessus), il y a plusieurs façons de procéder :
o pointer sur A2 et maintenir le click en glissant jusqu'à B4
o se positionner sur A2 avec les flèches de direction, maintenir la touche SHIFT, puis se déplacer jusqu'à B4 toujours avec les flèches de direction
o la cellule A2 sélectionnée, maintenir la touche SHIFT, et cliquer sur la cellule de destination B4
La sélection est dite étendue et constitue une plage de cellule, dont l'adresse ou le nom est A2:B4, une cellule de départ et une cellule d'arrivée séparées par deux points.
La cellule active dans la sélection peut être déplacée par la touche Tabulation
Sélection discontinue
La sélection peut également être discontinue, ou dissociée.
Maintenir la touche CTRL, pendant les sélections successives,
Dans une sélection discontinue, la poignée a disparue.
Pour Excel, la plage est ici, A1:C4;A6:C6, le séparateur de plages discontinues est alors le point virgule
Sélectionner des lignes et/ou des colonnes
Cliquez sur les entêtes.
Elles peuvent être adjacentes.
Elles peuvent être discontinues par la touche CTRL.
Sélection par la saisie
En cliquant dans la zone nom, on peut saisir la plage de cellules à atteindre ou à sélectionner
Valider avec la touche ENTREE.
Sélection dans le contenu
Pour sélectionner dans le contenu d'une cellule, quand la cellule est en état de modification, on peut sélectionner ou déplacer le curseur dans le contenu de la cellule.
Manipuler les cellules
La cellule active ou la plage de cellules sélectionnée, est encadrée par un trait plus gras. A l'angle inférieur droit, il y a un carré noir qui se distingue de la bordure, la poignée.
Avec la souris
Quand le pointeur est sur la bordure de la sélection, il a le forme (familière) de la flèche. A ce moment, en maintenant le click, la sélection est déplacée, + CTRL elle est copiée.
Quand le pointeur est sur la poignée, il a la forme du croix fine. A ce moment, en maintenant le click, la sélection est recopiée.
Dans les deux cas, si la manipulation est faite avec le click droit de la souris, un menu (contextuel) s'affiche. Le menu Edition, permet de déplacer par copier coller.
Il faut faire la distinction entre Effacer (et ses options) et Supprimer. Lorqu'une sélection est supprimée, Excel demande comment doivent être décalées les autres cellules. Les raccourcis clavier sont très pratiques. Quand une sélection est copiée, la cellule s'entoure d'une bordure de tirets défilants.
La barre d'état invite à choisir une destination La touche ECHAP annule cette opération.
Mettre en forme des cellules
Il faut d'abord sélectionner les cellules ou la plage que l'on veut mettre en forme. Toutes les possibilités sont accessibles par le menu Format / Cellule... La boite de dialogue possède six onglets :
Excel propose un large catalogue (classé en catégories et types) de formats nombre. Certains formats sont même personnalisables.
Permet d'aligner, d'orienter le texte... A remarquer les contrôles, renvois à la ligne, ajustement, fusion...
Permet de choisir une mise en forme de police de caractère...
Permet de choisir des styles de traits, leurs couleurs et places...
Concerne le remplissage, couleurs et trames...
Les options ne sont valides que si la feuille est protégée.
Verrouillées, les cellules sont protégées, masquées, la formule est cachée.
La barre d'outils Mise en forme, permet de
A la cellule peut être attaché un commentaire. Il se présente sous la forme d'une info bulle, signalée en haut à droite de la cellule par un triangle rouge.
Dans le menu Outils/Options/Affichage - Commentaires, ils peuvent être masqués, indiqués, affichés. Simplement indiqués, ils s'afficheront au passage de la souris.
Copier ou recopier une mise en forme de cellule
Lorsque une cellule est copiée, le texte, la valeur, la formule, les bordures et les motifs, les formats de police, les commentaires, tout par défaut est copié.
Le menu édition collage spécial nous permet de caler soit uniquement la mise en forme, soit sans la mise en forme.
La recopie par la poignée, angle droit inférieur de la cellule, en maintenant le click droit, affiche le menu contextuel avec la commande recopier le format.
Les opérations de base
Les opérations.
Pour indiquer à Excel que la cellule contient un calcul, commencez la saisie par la signe =
Le curseur du clavier clignote, soit dans la cellule (modification directe), soit dans la barre de formule.
Pointez sur la première référence de calcul, soit par un click de souris, soit en se déplaçant avec les flèches de direction du clavier.
Il est également possible de taper directement la référence au clavier : par exemple A1 (majuscule ou minuscule, peu importe)
Tapez, au clavier, pavé numérique, l'opérateur arithmétique choisi.
Puis pointez sur la seconde référence de calcul, comme pour la première.
et ainsi de suite...
Validez avec la touche du clavier
La barre de formule contient le calcul, la cellule renvoie le résultat.
Utilisation des parenthèses
Lorsque le calcul contient à la fois, addition multiplication soustraction et division, Excel donne la priorité à la multiplication et la division.
Ici par exemple, il multiplie 4x2, puis additionne
Peu importe l'ordre
Le calcul peut être "sérié" en utilisant les parenthèses
Ici par exemple, nous voyons bien la différence de calcul
Quand les calculs sont-ils exécutés ?
Le calcul est la procédure utilisée pour résoudre les formules et en afficher les résultats sous forme de valeurs dans les cellules qui contiennent ces formules.
Excel calcule automatiquement les classeurs ouverts. Cependant, vous pouvez décider du moment où le calcul se produit par le menu :Outils/Options/Onglet Calcul
Par défaut, le calcul est automatique. Il se fait en arrière plan.
Excel recalcule les cellules dépendant d'autres cellules contenant les valeurs modifiées. Excel calcule également les classeurs à chaque fois qu'ils sont ouverts ou enregistrés.
La division et les formats d'affichage
Excel peut afficher le résultat du calcul de façons différentes, et en particulier la division (ou fraction). Prenons l'exemple suivant
Par le menu : Format/Cellule/Onglet Nombre,
Par défaut le format est standard, en fait il n'y a pas de format. A noter l'aperçu
Le format pourcentage et la possibilité de choisir le nombre de décimales
Le format fraction, avec la possibilité de choisir entre plusieurs types.
A noter, certains formats sont accessibles rapidement par la barre d'outils mise en forme.
La recopie des calculs
Plaçons nous dans cette hypothèse :
Le calcul est saisi.
Par la poignée en bas à droite, la recopie est étendue.
La formule est recopiée en ligne, les références de calcul s'incrémentent sur les colonnes. =B2-B3 devient ici =C2-C3
La référence est relative.
C'est aussi vrai pour les recopie en colonnes où le numéro de ligne est incrémenté.
=B2+C2 devient ici =B3+C3
La recopie des calculs
Pour calculer le rapport de Janvier dans le total, voici les calculs (le résultat sera en format pourcentage).
A la recopie en ligne pour Février, le diviseur deviendrait E4. Excel permet de bloquer la référence avec la touche du clavier , avec des pressions successives, s'intercale des $.
Dans le cas présent, il suffit de bloquer la colonne, puisque la recopie est en ligne.
La référence est dite absolue.
Rendre une référence absolue en la nommant
En Cliquant dans la zone nom, on peut renommer une cellule
Le nom ne peut contenir que des lettres, aucun signe de ponctuation ou d'espace. Choisi avec discernement, il facilitera la lecture des formules
Attention : l'entrée doit être validée.
A la saisie du calcul, quand une référence nommée est pointée, Excel utilise son nom et la rend absolue.
Calculer en utilisant les noms
Par le menu Insertion / Nom /Définir, il est possible d'affecter des noms.
Quand la boite de dialogue s'ouvre, Excel prend la (ou les) cellule(s) sélectionnée(s) comme référence.
Il propose un nom, s'il trouve une étiquette de donnée.
Les noms doivent être ajoutés à la liste. Il peuvent ne pas faire référence à une cellule mais à une valeur.
Ici par exemple : taxe = 10%
Rappel, les noms sont accessibles par le menu Insertion / Nom /Définir
Dans la liste déroulante de la zone nom, seuls apparaissent les noms qui font référence à une cellule ou une plage de cellules. En sélectionnant le nom, la cellule ou la plage sont sélectionnées.
Les fonctions, les formules de calcul
Excel possède de nombreuses formules de calcul prédéfinies. Elles sont toutes accessibles par la commande Insertion / Fonctions, ou le bouton de la barre de formule <<Insérer une fonction>>
La boite de dialogue <<Insérer une fonction >> s'affiche.
Rechercher une fonction :
Il s'agit de faire des recherches à l'aide de mots clés. Par exemple, si vous saisissez le mot "amortissement", Excel vous propose une liste de fonctions de calcul d'amortissement.
Les fonctions sont classées par catégories :
La syntaxe, c'est à dire la façon dont la fonction s'écrit et les arguments sont expliqués au bas de la boite de dialogue.
L'aide plus avancée est disponible, par exemple ici, l'aide sur la fonction AMORLIN
Les fonctions, les formules de calcul
Les fonctions les plus utilisées
M SOMME(nombre1;nombre2;...)
M MOYENNE(nombre1;nombre2;...)
M NBVAL(valeur1;valeur2;...)
M MAX(nombre1;nombre2;...)
M MIN(nombre1;nombre2;...)
M SI(test_logique;valeur_si_vrai;valeur_si_faux)
M ET(valeur_logique1;valeur_logique2;...)
M OU(valeur_logique1;valeur_logique2,...)
SOMME(nombre1;nombre2;...) nombre1, nombre2, ... représentent les 1 à 30 arguments dont vous voulez connaître la valeur totale ou somme.
Cela additionne tous les nombres contenus dans une plage de cellules. Notes
MOYENNE(nombre1;nombre2;...) nombre1,nombre2, ... représentent les 1 à 30 arguments numériques dont vous voulez obtenir la moyenne.
Cela renvoie la moyenne (arithmétique) des arguments. Notes
NBVAL(valeur1;valeur2;...) compte le nombre de cellules qui ne sont pas vides et les valeurs comprises dans la liste des arguments. Utilisez NBVAL pour compter le nombre de cellules contenant des données dans une plage ou une matrice.
valeur1,valeur2, ... représentent les 1 à 30 arguments correspondant aux valeurs à compter. Dans ce cas, une valeur correspond à tout type d'information, y compris du texte vide (""), la seule exception étant les cellules vides. Si un argument correspond à une matrice ou à une référence, les cellules vides à l'intérieur de cette matrice ou référence ne sont pas prises en compte. Si vous n'avez pas besoin de compter des valeurs logiques, du texte ou des valeurs d'erreur, utilisez la fonction NB.
MAX(nombre1;nombre2;...) renvoie le plus grand nombre de la série de valeurs.
nombre1, nombre2, ... représentent les 1 à 30 nombres parmi lesquels vous souhaitez trouver la valeur la plus grande.
Notes
M Les arguments peuvent être des nombres, des cellules vides, des valeurs logiques ou des nombres représentés sous forme de texte. Les arguments qui sont des valeurs d'erreur ou du texte ne pouvant pas être converti en nombres génèrent des erreurs.
M Si un argument est une matrice ou une référence, seuls les nombres et valeurs d'erreur de cette matrice ou de cette référence sont considérés. La fonction MAX renvoie la première valeur d'erreur rencontrée dans la matrice ou la référence.
M Les cellules vides, les valeurs logiques ou le texte contenus dans la matrice ou la référence ne sont pas pris en compte. Si les valeurs logiques et le texte doivent être pris en compte, utilisez la fonction MAXA au lieu de la fonction MAX.
M Si les arguments ne contiennent pas de nombre, la fonction MAX renvoie 0 (zéro).
MIN(nombre1;nombre2;...) renvoie le plus petit nombre de la série de valeurs.
nombre1, nombre2, ... représentent les 1 à 30 nombres parmi lesquels vous souhaitez trouver la valeur minimale.
Notes
M Les arguments peuvent être des nombres, des cellules vides, des valeurs logiques ou des nombres représentés sous forme de texte. Les arguments qui sont des valeurs d'erreur ou du texte ne pouvant pas être converti en nombres génèrent des erreurs.
M Si un argument est une matrice ou une référence, seuls les nombres et valeurs d'erreur de cette matrice ou de cette référence sont considérés. Les cellules vides, les valeurs logiques ou le texte contenus dans la matrice ou la référence ne sont pas pris en compte. Si les valeurs logiques et le texte doivent être pris en compte, utilisez la fonction MINA au lieu de la fonction MIN.
M Si les arguments ne contiennent aucun nombre, MIN renvoie la valeur 0 (zéro).
SI(testlogique;valeursivrai;valeursifaux) renvoie une valeur si la condition que vous spécifiez est VRAI et une autre valeur si cette valeur est FAUX.
Utilisez la fonction SI pour effectuer un test conditionnel sur des valeurs et des formules.
test_logique représente toute valeur ou expression qui peut prendre la valeur VRAI ou FAUX. Par exemple, A10=100 est une expression logique ; si la valeur contenue dans la cellule A10 est égale à 100, le résultat de l'expression est VRAI. Dans le cas contraire, le résultat est FAUX. Cet argument peut utiliser n'importe quel opérateur de calcul par comparaison.
valeur_si_vrai est la valeur qui est renvoyée si le test logique est VRAI. Par exemple, si cet argument est la chaîne de texte « Valeur acceptée» et si le résultat de l'argument du test logique est VRAI, la fonction SI affiche le texte « Valeur acceptée».
Si le résultat de l'argument test_logique est VRAI et si l'argument
valeur_si_vrai est vide, il renvoie 0 (zéro). Pour afficher le mot VRAI, utilisez la valeur logique VRAI pour cet argument.
L'argument valeur_si_vrai peut aussi être une autre formule.
valeur_si_faux est la valeur qui est renvoyée si le test logique est FAUX. Par exemple, si cet argument est la chaîne de texte « Valeur refusée» et si le résultat de l'argument du test logique est FAUX, la fonction SI affiche le texte « Valeur refusée». Si le résultat de l'argument test_logique est FAUX et si l'argument valeur_si_faux est omis (à savoir s'il n' a pas de virgule derrière valeur_si_faux), la valeur logique FAUX est renvoyée. Si l'argument
test_logique est FAUX et si l'argument valeur_si_faux est vide (à savoir s'il y a une virgule suivie de la parenthèse fermante derrière valeur_si_faux), alors la valeur 0 (zéro) est renvoyée. L'argument valeur_si_faux peut aussi être une autre formule.
Notes
o Pour compter le nombre d'occurrences d'une chaîne de texte ou d'un nombre à l'intérieur d'une plage de cellules, utilisez la fonction de feuille de calcul NB.SI
o Pour calculer une somme basée sur une chaîne de texte ou un nombre à l'intérieur d'une plage, utilisez la fonction de feuille de calcul SOMME.SI.
ET(valeur_logique1;valeur_logique2;...) valeur_logique1,valeur_logique2, ... représentent les 1 à 30 conditions que vous souhaitez tester et qui peuvent être soit VRAI, soit FAUX.
Renvoie VRAI si tous les arguments sont VRAIS ; renvoie FAUX si au moins l'un des arguments est FAUX.
Notes :
Les arguments doivent être évalués à des valeurs logiques, telles que VRAI ou FAUX, ou doivent être des matrices ou des références contenant des valeurs logiques.
Microsoft Excel Niveau 1
OU(valeur_logique1;valeur_logique2,...) Renvoie la valeur VRAI si un argument est VRAI et FAUX si tous les arguments sont FAUX.
valeur_logique1, valeur_logique2, ... sont de 1 à 30 conditions que vous souhaitez tester, et qui peuvent être soit VRAI, soit FAUX.
Notes
SOMME(nombre1;nombre2;...)
nombre1, nombre2, ... représentent les 1 à 30 arguments dont vous voulez calculer la valeur totale ou somme.
Cela additionne tous les nombres contenus dans une plage de cellules.
Notes
