Download Tutoriel Excel : Maîtriser les Fonctions et Formules pour l'Analyse de Données and more Study Guides, Projects, Research Computer science in PDF only on Docsity!
Formation / tutorial / cours EXCEL
première partie
1.1. L'écran Microsoft Excel
EXCEL est un tableur. Sa principale utilisation est le calcul automatique de formules. Excel peut être vu comme une feuille de calcul. Les colonnes sont référencées par des lettres, les lignes par des chiffres. La référence d'une cellule est reprise avec la combinaison de la colonne suivie de la ligne. La référence de la cellule en haut à droite (à l'intersection de la colonne A et de la ligne 1) est donc A1. Celle à l'intersection de la ligne 8 et de la colonne D se nomme D8. Les majuscules - minuscules n'ont aucune importance. Pour sélectionner une cellule, il suffit de se trouver dans la cellule. Pour sélectionner une colonne, cliquez sur la référence de la colonne (A par exemple). Pour sélectionner plusieurs colonnes, cliquez sur une référence colonne et poursuivez en maintenant la touche gauche de la souris enfoncée. C'est de même pour les lignes. Pour sélectionner un groupe de
cellule, maintenez la touche gauche de la souris enfoncée et bougez la souris (quelque soit le sens). A retenir : les cellules Excel sont référencées par leur numéro de colonne suivi de leur numéro de ligne Un petit truc, pour sélectionner des ensembles de cellules non adjacentes, maintenez la touche du clavier enfoncée lorsque vous sélectionnez la zone suivante. Pour élargir (ou rétrécir) un colonne ou une ligne, pointez entre les références de colonnes dont vous désirez modifier la taille. Il n'est pas possible comme dans Word de modifier la taille d'une seule cellule.
1.2. Entrée des données, formules de base
Commençons tout de suite par un petit calcul. Positionnez votre curseur sur la cellule A1 et tapez un nombre (ici le chiffre 45). Faites de même dans la cellule A2. Remarquez que le chiffre se met à deux place: dans la cellule et dans la barre de formule d'Excel. Nous souhaitons faire la somme de ces cellules et obtenir le résultat dans la cellule A3. Dans un cas général, nous utiliserions le calcul mental pour taper dans la cellule le chiffre 99. Microsoft Excel va nous faciliter la vie puisque non seulement, il va faire le calcul mais va tenir également compte des modifications dans les 2 cellules pour afficher le résultat correct. Pour signaler à EXCEL que le contenu de la cellule est un calcul à exécuter, le contenu de la cellule doit commencer par = (+ est également accepté). Tapons dans A =A1+A2 (les références des cellules que nous souhaitons additionner). Lorsque nous acceptons la formule (par la touche <Entrée>), le résultat de l'addition apparaît automatiquement dans la cellule A En repassant sur cette cellule, le résultat apparaît dans la cellule, tandis que la formule est affichée dans la barre de formule. Pour modifier la formule, il vous faut donc repasser dans la barre de formule. Un petit truc, si vous appuyez sur la touche , vous pouvez directement modifier le contenu de la formule dans la cellule. Si vous modifiez le contenu de la cellule A1 ou A2, le résultat est directement affiché dans la cellule A3. A retenir : les calculs commencent toujours par =, il ne faut pas d'espaces entre les chiffres, références de cellules. La référence de cellule peut être tapée en minuscule ou majuscule
- Dans la cellule B8, tapons LUN , en recopiant les cellules, EXCEL recopie automatiquement les abréviations des jours de la semaine, de même pour le jour en toute lettre, abréviation du mois et mois complet.
- En I5 (par exemple), tapons maintenant une date et recopions la cellule, les dates se suivent automatiquement, tenant compte des mois et même des années. Nous souhaiterions faire une suite de nombre (1 2 3, ...). Par le point 1, nous savons que sélectionner un nombre et le recopier ne fait que remplir les cellules avec le même nombre. Pour faire cette suite, nous devons d'abord signaler au tableur que nous voulons effectivement cette progression arithmétique. Sélectionnons la cellule contenant le chiffre 1 et la cellule adjacente contenant le chiffre 2 et seulement ensuite, recopions les cellules. Excel va automatiquement créer la suite de nombre. Ceci marche également avec les formules de calculs (avec des modifications) comme nous le verrons dans l'exercice ci-dessous et au chapitre suivant A retenir : le coin droit en bas d'une cellule permet de recopier son contenu vers d'autres cellules.
1.4. Exercice: un début de facture.
Nous allons utiliser ces notions pour faire un petit tableau de type facture. Tous le cours Excel premier niveau se basera sur ce petit exercice. Commençons par effacer le contenu des cellules que nous avons tapé. Pour cela, sélectionnons l'ensemble des cellules où se trouvent des nombres ou du texte et appuyons sur la touche (clavier belge) ou (clavier français). Tapons maintenant les textes ci- dessous dans les bonnes cellules. Le but est de taper la quantité, la description, le prix et de retrouver automatiquement (par calcul) le montant total soit le prix multiplié par la quantité. Ceci est la base de tous les exercices de ce cours Excel puisque nous l'utiliserons jusqu'à la dernière formation, chaque fois en l'améliorant. Rentrons les données suivantes dans la ligne 2 (par exemple) Le but est de retrouver automatiquement le prix dans la cellule A4. Nous allons donc faire une formule dans la cellule D2 en multipliant la quantité et le prix unitaire dans la ligne associée (ici la ligne 2). La formule sera: =A2*c2. Si la formule est tapée correctement, le résultat est effectivement 800. Un changement dans la quantité ou dans le prix donnera automatiquement le résultat.
= pour signaler un calcul A2 pour signaler que nous souhaitons la quantité (colonne A) associée à la ligne 2 C2 pour signaler que nous souhaitons le prix unitaire (colonne A) associé à la ligne 2 Une facture, un devis, ... utilise généralement plusieurs lignes. Nous allons donc compléter notre tableau en conséquence. Commençons par positionner la souris dans le coin droit de la cellule D2 et recopions le contenu vers le bas jusqu'à la ligne 20. Les autres cellules affichent 0. En effet, si nous positionnons le curseur sur la cellule D20, le contenu est 0 mais la formule est devenue =A20*C20. Excel a automatiquement mis les formules à jour en fonction du numéro de ligne. Même si ceci n'est pas toujours intéressant, cette mise à jour permet de faire de grands tableaux comprenant une multitudes de formules sans trop se fatiguer. Nous allons compléter notre facture - devis Excel par une colonne Réduction. La première opération est d'insérer une colonne entre la colonne C (prix unitaire) et D (Total). Sélectionnons la colonne D et utilisons la touche droite de la souris pour afficher le menu contextuel. Sélectionnons la commande "Insérer" et une colonne est insérée entre C et D. La colonne D devient donc la colonne E. Remplissons les 2 cellules comme ci-dessous. A ce stade, n'utiliser le sigle des pourcentages %, tapez uniquement le montant de la réduction (ici 10). Notre montant total est faux puisqu'il ne tient pas compte de la réduction. Nous allons donc modifier la formule en E pour inclure cette réduction.
La fonction =SOMME(E2:E20) s'inscrit dans la barre de formule, dans la cellule E21, E2:E est en sur brillance et la zone E2 à 22 est entourée. Si nous sélectionnons une autre zone avec la souris, la zone entre parenthèse dans la formule est automatiquement modifiée en conséquence mais ce n'est pas notre but. Pour accepter la plage de cellule, tapez <Entrée>. Remarquez le ":" entre la référence E2 et la référence E20. Ce sera important pour la suite du cours EXCEL. L'intérêt d'utiliser cette formule à la place de =E+E3+E4+... est déjà explicite mais il y en a une autre. Sélectionnez une ligne dans cet ensemble et utilisez le menu contextuel pour insérer une ligne (touche droite de la souris)
Reprenons notre résultat (maintenant en E22) et vérifions dans la barre de formule la fonction. Elle a été modifiée automatiquement par EXCEL pour tenir compte de cette insertion. Il faudra néanmoins recopier la formule de prix de la ligne. Il nous reste deux petites formules à rajouter: le montant de TVA et le montant à payer. Si nous utilisons une TVA à 21 % (Belgique), la formule en E23 devient =E22*0,21 et la formule en E24 (montant à payer) devient =E22+E A ce stade, il reste deux petits problèmes: les chiffres derrière la virgule et la présentation. Nous les verrons plus tard. A retenir : L'insertion d'une ligne ou d'une colonne modifie automatiquement la plage des cellules d'une fonction.
taper <Entrée> Remarquez que les 2 références extrêmes de cette zone sont notées A2 : A15. Le : signale à Excel que vous souhaitez toute la plage. Faisons de même mais en regard de Nombre2. La formule (affichée au-dessus) devient =SOMME(A2:A15;C2:C10) A retenir : Les " : " signalent à Excel que la plage reprend toutes les cellules entre les 2 références désignées. Par contre, le signe " ; " découpe des zone distinctes. Le résultat ci-dessus reprendra donc la somme des cellules comprises entre A2 et A
- la somme des cellules reprises entres les cellules C2 et C10. Les fonctions Max, Min, Moyenne, Mode s'utilisent de la même méthode.
2.3. Une fonction Excel simple, ENTIER: ENT().
Dans Insertion Fonction, sélectionnons la fonction ENT qui affiche la valeur entière d'un nombre (nombre sans les chiffres derrière la virgule). La fenêtre qui apparaît est identique à celle ci-dessus. Tout au-dessus, dans la barre des formules, =ent(). En effet, une fonction nécessite un calcul et tous calculs commencent par =. Il est suivi par le nom de la fonction, sans espaces.
Nombre est le nombre que nous voulons arrondir à la décimale inférieure. Cliquons sur la flèche rouge et la fenêtre disparaît pour laisser la place à notre grille habituelle. A l'aide de la souris, cliquons sur la cellule pour laquelle nous souhaitons avoir le nombre entier (exemple A3) et appuyons sur la touche <Entrée> ou sur la flèche en rouge en haut. La fenêtre de fonction réapparaît comme ci-dessous. La formule dans la barre des formules devient =ENT(A1). Nous pouvons maintenant appuyer sur OK pour récupérer le résultat. Intéressons-nous à la manière dont Excell a écrit cette fonction: = puisque c'est un calcul ENT , la fonction (référence de cellule) , la partie à calculer est entre parenthèses. Remarquez qu'il n'y a pas d'espaces. Voyons cette formule de manière plus théorique: =ent(nombre à calculer). Ce nombre à calculer peut en fait être n'importe quoi, un chiffre, une référence de cellule ou .. le résultat d'un calcul. Dans la barre de formules, nous pouvons par exemple taper =ent(A1+A2). Ceci fera d'abord l'addition du contenu de A1 et du contenu de A2, la formule retirera la valeur entière de cette formule. Voyons cette formule avec les aides de Microsoft. Revenons à notre fonction ENT() de départ et utilisons l'icône pour faire apparaître la petite barre. Sélectionnons la cellule A2 et tapons immédiatement sur le sigle +. Si nous sélectionnons la cellule A3 avec la souris, ceci devient dans la barre blanche A2+A3, et ainsi de suite. Nous aurions également pu prendre une cellule contenant une autre fonction mais cette façon de travailler pose quelques problèmes de présentation. Nous verrons les fonctions imbriquées à la fin de ce chapitre.
L'étape suivante de notre cours excel va être de mettre des calculs (fonctions) inclus dans d'autres calculs (fonctions), par exemple l'arrondi d'une somme.
3.1. Imbrications de fonctions.
L'utilisation de fonctions Excel permet de calculer des fonctions complexes. Néanmoins, souvent l'utilisation d'une fonction est insuffisante, il faut utiliser une fonction sur un résultat d'une formule ou d'un ensemble de fonctions. La version Excel 95 permettait ceci très facilement. Depuis la version 97 (et suivante), l'utilisation est plus ardue. L'imbrication de fonctions consiste à remplacer des opérantes par d'autres fonctions. Reprenons notre exercice de facturation des chapitres précédents. J'ai déjà mentionné le problème des arrondis dans les résultats. Nous avons également déjà vu au chapitre précédent la fonction arrondi. Prenons la cellule E2=A2C2(1-D2/100). Pour ne pas avoir d'erreurs, cette fonction devrait inclure l'arrondi de ce résultat. Nous pourrions utiliser la méthode du chapitre précédent mais voyons cette possibilité d'une manière plus manuelle. Ceci nous préparera à la suite de la formation EXCEL. La fonction arrondi est de type: =ARRONDI(nombre à arrondir;nombre de chiffres) Le calcul qui donne le prix total (réduction incluse) est =A2C2(1-D2/100)
Il va nous suffire de remplacer dans la fonction arrondi le nombre à arrondir par la formule de prix de vente Le résultat donne comme formule =ARRONDI(A2C2(1-D2/100);2)**. Il ne nous reste plus qu'à recopier cette formule (coin en bas à droite) sur les 20 lignes de la facture. Nous pourrions le faire sur la somme des lignes. Ceci ne sert pas à grand chose puisque la somme de chiffres avec 2 chiffres derrière la virgule maximum donnera toujours au plus 2 chiffres derrière la virgule. Faisons-le pour le plaisir. La cellule E22 contient =SOMME(E2:E21) et la fonction arrondi est de type =ARRONDI(nombre à arrondir;nombre de chiffres) Dans notre cas, nous souhaitons arrondir la somme. La fonction extérieure est dont la fonction Arrondi qui reprendra comme argument "nombre à arrondir" le résultat de la somme. La formule devient: =ARRONDI(SOMME(E2:E21);2) Nous pourrions faire de même avec la TVA et le montant total (à payer) mais nous pouvons nous contenter de n'afficher que les 2 derniers chiffres si nous n'utilisons pas ces résultats pour d'autres calculs.
3.2. La fonction conditionnelle SI
Même si je ne vois pas beaucoup de fonctions, quelques unes s'utilisent régulièrement. La fonction SI permet d'exécuter une opération si la condition est remplie et une autre si cette condition n'est pas remplie. =SI(Condition;opération à effectuer si VRAI; Opération à effectuer si FAUX) Les conditions possibles sont Supérieur > Supérieur ou égal >= Inférieur < Inférieur ou égale <= égal = différent <> Les opérantes peuvent être de tous types. Voici quelques exemples: a1<>A2 ; A12>=20 ; G15 <= 0 mais peuvent également être de type A2<>"" : le contenu de A2 différent de rien. Attention, si le chiffre 0 est contenu dans cette cellule, le résultat est vrai. Nous allons utiliser cette possibilité dans notre facture dans quelques secondes.
=SI(A2<>"";calcul;"") soit en imbriquant les 2 formules: =SI(A2<>"";ARRONDI(A2C2(1-D2/100);2);"")** Remplaçons le contenu de la cellule E2 par cette formule et recopions jusqu'à la ligne 20 incluse et les lignes où il n'y a pas de quantités n'affichent plus de zéro. Nous pourrions encore modifier la fonction si pour que le calcul ne se fasse que si la colonne quantité et la colonne prix soient différents de rien =SI(ET(A2<>"";C2<>"");ARRONDI(A2C2(1-D2/100);2);"") A retenir** : dans la fonction SI, "" permet de faire une condition sur une case vide ou de n'afficher aucun caractère comme résultat. Notre facture EXCEL est déjà bien en route, il nous faut maintenant la personnalisée pour notre propre entreprise. Ce sera le chapitre suivant. La facture que nous utilisons ne peut pas encore être utilisée, la mise en page est plutôt sommaire. Il nous manque les en-têtes, les cases pour délimiter les cellules, les dates, ... Ce chapitre va nous permettre de mettre en forme. Le suivant sera l'impression.
4.1 Format des cellules
Même si nous le verrons par la suite avec la barre des tâches, l'ensemble des mises en forme de cellule se trouve dans la commande CELLULE du menu FORMAT Nombre reprend le format du contenu des cellules sélectionnées Alignement reprend le format "paragraphe" des cellules Police reprend la police, couleurs, ... du contenu de la cellule Bordure reprend les encadrements des cellules sélectionnées Motifs reprend les fond des cellules Protection permet d'empêcher la modification de cellules
4.2. Le format Nombre
Plusieurs possibilités existent pour la manière qu'affiche le contenu d'une cellule. STANDARD est le format standard des cellules d'EXCEL. Le texte est aligné à gauche, les nombres alignés à droite/ NOMBRE permet de modifier la présentation des nombres (texte est néanmoins accepté). Vous pouvez "formater" par ici les nombres négatifs et insérer un espace automatiquement entre les centaines et les milliers.
La fonction "Fusionner des cellules" permet de rassembler plusieurs cellules en une seule. Cette fonction est accessible par la barre d'outils. Par contre, pour défusionner, il faut repasser dans le menu (sauf Excel XP et 2003). L'option Ajuster va, dans le cas de textes , va diminuer la taille des caractères pour rentrer toute la phrase dans la cellule. Elle n'a aucune influence sur les chiffres.
4.4. Police
Rien de bien spécial pour cette commande, à part des soulignements doubles, ... La majorité des commandes est accessible via la barre d'outils. Remarque, vous ne pouvez pas utiliser 2 polices différentes dans la même cellule.
4.5. Bordures
1 permet de créer les bordures internes à la plage de cellules sélectionnées ou externe. 2 permet de sélectionner le type de bordure (y compris les
épaisseurs)
- permet d'insérer des bordures de différentes manières Vous devez à chaque fois sélectionner en premier la taille, suivie de la couleur et seulement taper le contour ou intérieur ou directement un des bord.
4.6. Motifs
Sélection de la couleur de fond de la plage de cellules sélectionnées ou même motifs suivant les couleurs en dessous. Pour une impression lisible, seules quelques couleurs claires peuvent être utilisées si la cellule contient du texte.
4.7. Protection
La protection des cellules est un peu spéciale. Elle s'utilise avec la fonction protection du menu OUTILS. Lorsque vous protégez la feuille, toutes les cellules dont la case verrouillée est cochée ne pourront être modifiées. Par contre, si une cellule verrouillée contient une formule, le résultat sera quand même affiché si vous modifiez les cellules de départ de cette formule. Nous utiliserons cette fonction pour protéger les formules dans l'exercice ci-dessous
