Initiation au tableur Excel

 

Initiation au tableur Excel

Introduction

Un centre de formation organise chaque année un concours d’accès au centre, qui se déroule selon les modalités suivantes :

Tout candidat doit passer un écrit portant sur trois épreuves : une épreuve de Sciences de l’Education, une épreuve de Socio-économie et une épreuve de tests psychotechniques. Ces épreuves sont affectées des coefficients respectifs 2, 3 et 1.

Tout candidat ayant une moyenne en écrit supérieure ou égale à 10 sans avoir eu une note éliminatoire (note<5) doit passer une épreuve orale. La note finale est égale à la moyenne des notes de l’écrit et de l’oral.

Les 120 premiers candidats seront admis au centre pour une formation de deux années en vue de l’obtention du diplôme de conseiller en orientation ou en planification.

Le bureau des examens utilise le logiciel « Excel » pour traiter ces données. Dans cette première séance, nous allons présenter, à l’aide de cet exemple illustratif, la fonction principale d’un tableur, en l’occurrence « Excel », à savoir : gérer interactivement un ensemble cohérent d’informations à partir de données saisies par l’utilisateur.

Objectif du cours :

L’accent est plus mis sur l’effet des commandes plutôt que sur le mode opératoire. Le but étant d’abord d’orienter les représentations des apprenants sur les possibilités d’exploitation du logiciel.

Déroulement du cours :

• Démarrer l’application
  • double clic sur l’icône
  • Menu Démarrer.
• Charger le fichier Excel « concours_saisi.xls.(Cf. fig. 1).
• Modifier quelques données (nom mal écrit, erreur sur la note d’un candidat, …)
• Sauvegarde en cours
• Ecriture de la formule calculant la moyenne du candidat n°1.
• Recopie de la formule dans le reste de la colonne.
• Ecriture d’une formule retournant un résultat logique ; (vrai pour passer l’oral)
• Extraction des candidats passibles de passer l’oral.
• Création d’un nouveau fichier (ou d’une nouvelle feuille) à partir de l’extraction.
• Saisie des notes de l’oral.
• Formule calculant la moyenne générale.
• Formule donnant le rang d’un candidat.
• Tri des données selon la clé rang
• Formule calculant les résultats finaux (reçu, non reçu).
• Mise en forme de la feuille de calcul : style des cellules, format des valeurs de type nombre.
• Mise en page de la feuille de calcul : marges, entêtes et bas de page, déplacement, insertion et suppression de lignes et de colonnes.
• Graphique illustrant le tableau des données sous forme d’histogramme, de camembert…
• Impression
• Fermeture des fichiers.
• Fermeture de l’application.

Présentation générale :

Feuille de calcul

• Démarrer l’application : Trois façons de faire
• Double-cliquez l’icône Excel sur le bureau
• Cliquez l’icône Excel dans la barre des taches
• Menu Démarrer_Programmes_Microsoft Excel
• Ouvrir un nouveau classeur : Fichier_Nouveau_Classeur.
• Description de la fenêtre de l’application :
• Barre de menu
• Barre d’outils
• Barre de formule.

Les deux premières sont comparables à celles de Word. La troisième est spécifique à Excel. Elle comporte à gauche une zone d’informations sur la référence absolue de la cellule active et à droite une zone de saisie et d’affichage du texte descriptif de cette cellule. les deux icônes entre les deux zones servent à :

• Annuler la saisie en cours (X).
• Valider la saisie sans changer de cellule active(Ö). Les deux touches ¿ et TAB permettent aussi de valider la saisie avec un décalage, de la cellule active, en bas pour la première touche et à droite pour la seconde.

Le nombre de colonnes est de 256.

Le nombre de lignes est 65536.

L’intersection d’une ligne et d’une colonne forme une cellule.

Un pointeur de cellules sous forme de rectangle indique la cellule active.

Les flèches de direction et la souris permettent le déplacement dans la plage de cellules.

Exercice :

Cf. ci-dessous Fiche de travail n° 1

Fiche de travail N°1

• Démarrer l’application Excel.
• Saisir les 12 premières fiches de la figure ci dessous (fig. 1).
• Sauvegarder sous le nom « f_saisi_1 »
• Menu fichier + Enregistrer + Saisir le nom « F_saisi_1 » + Valider
• Fermer ce fichier.
• Menu fichier + Fermer
• Quitter l’application.
• Menu fichier + Quitter

Cellule.

Contenu d’une cellule.

Le contenu d’une cellule non vide est constitué de deux éléments d’information : le texte descriptif entré par l’utilisateur et la valeur calculée par le logiciel. Le texte descriptif est soit une constante (nombre, texte ou booléen) soit une formule (expression à évaluer préfixée par le signe égal « = »). L’expression d’une formule est formée de constantes et/ou de valeurs de cellules désignées par leur référence ou leur nom.

Pour saisir le texte descriptif d’une formule dans une cellule :

• Sélectionner la cellule.
• Activer la barre de formule (clic dans la zone d’édition)
• Taper le texte descriptif au clavier en commençant par le signe « = ».
• Valider en cliquant sur l’icône « Ö ».

Désignation d’une cellule.

Il existe trois façons de désigner une cellule : la référence absolue, la référence relative et la nomination.

Le référence relative de la cellule située à l’intersection d’une colonne et d’une ligne est désignée par le chaîne alphanumérique « Nom de la colonne et numéro de la ligne ».

Dans une formule on s’intéresse souvent à la position d’une cellule par rapport à la cellule active, celle où l’on saisit la formule. La référence absolue d’une cellule est obtenue en inscrivant le symbole « $ » devant la colonne et la ligne.

Exemple :

Remarque.

Une désignation peut aussi être mixte, c-a-d absolue pour la ligne et relative pour la colonne et inversement.

On peut aussi utiliser le nom d’une cellule à la place de référence. Pour nommer une cellule ou une plage de cellules :

• On sélectionne la cellule ou la plage de cellules à nommer
• Menu Insertion/Nom/Définir/¿

Exemples d’application :

Un petit commerçant fait le bilan d’une année d’exercice avec les données suivantes :

• chargement du fichier « f_bilan » (fig. ci-dessous)
• Menu fichier + Ouvrir + Double-cliquer « F_bilan » dans le dossier « Mes documents »
  

Calcul du bilan mensuel :

• Sélectionner la cellule D4.
• Taper le signe « = »
• Cliquer dans la cellule B4.
• Taper le signe « - »
• Cliquer dans la cellule C4.
• Cliquer sur l’icône « Ö » pour valider la saisie.

On peut procéder de la même façon pour chaque cellule de la plage D4 : D15. Mais il y a plus simple, on peut utiliser le copier/coller (déjà vu sous Word) ou la poignée de recopie.

N.B.

Vérifions que l’application de cette procédure de recopie à la même formule, mais en référence absolue, ne donne pas le résultat escompté.

Utilisation des noms pour faire le même calcul :

• Nommer la plage de cellules B4 : B15C2 « recettes » (sélectionner cette plage, cliquer dans la zone gauche de la barre de formule, taper recettes + ¿)
• Nommer la plage de cellules C4 : C15 « depenses » (idem).
• Cliquer dans la cellule D4 écrire l’expression « =recettes – depenses » + ¿
• Répliquer D4 dans la plage D5 : D15.
  

Exercice :

(CF. ci-dessous fiche de travail n°2).Fiche de travail N°2

• Démarrer l’application Excel.
• Charger le fichier « f_saisie ».
• Ecrire une formule à placer dans la cellule G5 recopiable dans le reste de la colonne G et qui calcule la moyenne à l’écrit de chaque candidat.
• Sauvegarder sous le nom « f_ecrit_1 »

Votre feuille de calcul devrait ressembler à la figure (Fig.2)

Fig. 2

Mise en forme d’une feuille de calcul :

Style d’une cellule :

A chaque cellule est attaché un ensemble de caractéristiques de mise en forme appelé style. Ces caractéristiques portent sur la position du texte dans la cellule (horizontal, vertical, cadrage, …), le choix du type et de la taille de la police, l’encadrement de la cellule (fond, bordure, …).

Toute cellule possède un style par défaut. Pour modifier ce style il faut appeler la commande : Format/Cellule. Les modifications apportées s’appliqueront à la cellule ou la plage de cellules sélectionnée.

Exemple : (centrer les éléments d’une colonne, renvoi à la ligne automatique,…)

Format d’une cellule :

A chaque cellule est appliquée un format numérique. Ce format permet d’afficher un nombre selon le contexte (numérique, monétaire, scientifique, date, pourcentage,…).

N.B.

L’utilisateur ne modifie que l’affichage (représentation externe) et non la représentation interne de la valeur.

Toute valeur calculée, à partir du texte descriptif entré par l’utilisateur, est stockée par Excel dans une représentation interne, liée à son type, et lors de l’affichage, il y a conversion du codage interne en une chaîne de caractères choisie pour l’interprétation humaine de cette valeur (une date, une somme en francs, une note en décimales, un pourcentage,…).

Exemple :

Nombre formaté : pourcentage, date, sommation erronée…

Noter bien la différence entre le format et la valeur réelle affichée dans la zone de formule.

Application

• Chargement du fichier « f_bilan ».
• Mettre en gras la ligne 1 avec une police Times de taille 14.
• Sélectionner la ligne en cliquant sur le numéro de la ligne.
  
• Barre d’outils « mise en forme »

• Cliquer l’icône « G »
• Cliquer la zone « Police » + choisir « Times »
• Cliquer la zone « Taille » Choisir « 14 »
• Mettre en gras la ligne 3 avec renvoi à la ligne automatique.
• Sélectionner la ligne en cliquant sur le numéro de la ligne.
• Barre d’outils « mise en forme » + Cliquer l’icône « G »
  
• Menu Format + Cellule + Onglet « Alignement » + Cocher la case « Renvoi à la ligne automatiquement » + ¿

• Appliquer un format monétaire à la plage B4 : D15
• 
  
• Sélectionner la plage B4 : D15+ Menu Format + Cellule + Onglet « Nombre » + Choisir « Monétaire » + « 2 décimales » + ¿

• La feuille de calcul devrait ressembler au résultat de la fig. suivante :
  

Exercice :

(CF. ci-dessous fiche de travail n°3).

Fiche de travail N°3

• Démarrer l’application Excel.
• Charger le fichier « f_ecrit ».
• Appliquer à la cellule A1 le style suivant : Police Times + Taille 14 + Gras.
• Appliquer aux lignes 2 et 4 le style : Renvoi automatique à la ligne + Gras.
• Appliquer un format numérique (2 déc.) à la plage des cellules notes et moyenne.
• Sauvegarder sous le nom « f_mis_form »

Votre feuille de calcul devrait ressembler à la figure (Fig.3)

Fig. 3

TRI

C’est la réorganisation d’une plage de cellules selon un certain ordre. Excel trie les données selon les lignes ou les colonnes. La commande trier nécessite des informations que l’utilisateur doit fournir :

• Plage de données à trier
• Clés de tri (principale et secondaires)
• Ordre de tri (croissant ou décroissant).

Remarque :

Toutes les cellules d’une même ligne (même fiche) constituent un seul individu ou une seule entité.

Les erreurs éventuelles dans une opération de tri peuvent provenir de la mauvaise sélection de données ou d’un choix erroné dans les options de la boite de dialogue comme, par exemple, le fait d’inclure la ligne des titres dans le tri.

N.B.

Pour un tri par lignes (respectivement par colonnes) veiller à ce que toute cellule ne contienne qu’une constante ou une formule ne dépendant que des cellules de la même ligne (respectivement de la même colonne).

Application sur le fichier « f_bilan »

• Chargement du fichier « f_bilan »
• Sélection de la zone de tri A3 :D15
• Menu Données/Trier/Clé : Bilan mensuel, Ordre croissant, Ligne de titre : oui
  

Le résultat devrait ressembler à la figure suivante :

Fiche de travail N°4

• Démarrer l’application Excel.
• Charger le fichier « f_mis_forme ».
• Ecrire une formule à placer dans la cellule H6 recopiable dans le reste de la colonne H et qui affiche la valeur « VRAI » pour tout candidat passible de passer l’oral. (rappel :est admis à passer l’épreuve orale tout candidat ayant une moyenne à l’écrit supérieur ou égale à 10 et aucune note éliminatoire.
• Sélectionner la cellule H6
• Procéder comme ci-dessus pour saisir la formule suivante :
• =ET(D6>=5 ; E6>=5+ F6>=5 ; G6>=10)
• Insérer une ligne après la ligne des coefficients
• Sélectionner la cellule L4C1
• Menu « Insertion » + « Lignes »
• Trier les données selon la clé « Admis à l’oral » dans un ordre décroissant.
• Sauvegarder sous le nom « f_A_oral »

Votre feuille de calcul devrait ressembler à la figure (Fig.4)

Fig. 4

Fonctions :

Excel possède un très grand nombre de fonctions pour faire différents types de calcul.

Une fonction est caractérisée par le type et le nombre de ses paramètres mais aussi par le type de résultat qu’elle retourne. Nous ne pourrons épuiser toutes les fonctions disponibles, toutefois, nous présenterons quelques exemples que nous testerons sur la feuille de calcul du fichier « f_bilan » :

• Chargement du fichier
• Calcul de la somme des dépenses et des recettes dans les cellules B17 et C17 :
• Sélectionner la cellule B17
• Saisir « Totaux : »
• Sélectionner la cellule B17
• « =somme(B4 : B15) »
• Sélectionner la cellule C17
• « =somme(C4 : C15) »

• Calcul du bénéfice annuel dans la cellule D18.
• Sélectionner la cellule A18
• Saisir « Bénéfice annuel »
• Sélectionner la cellule D18
• « =somme(B4 : B15) - somme(C4 : C15) »
• - Calcul du bénéfice mensuel maximum dans la cellule D19.
• Sélectionner la cellule A19
• Saisir « Bénéfice mensuel maximum »
• Sélectionner la cellule D19
• « =Max(D4 : D15) »
• - Calcul du bénéfice mensuel minimum dans la cellule D20.
• Sélectionner la cellule A20
• Saisir « Bénéfice mensuel minimum »
• Sélectionner la cellule D20
• « =Min(D4 : D15) »

• - Calcul de la recette mensuelle moyenne dans la cellule D21.
• Sélectionner la cellule A21
• Saisir « Recette mensuelle moyenne »
• Sélectionner la cellule D21
• « =Moyenne(B4 : B15) »

• - Calcul de la dépense mensuelle moyenne dans la cellule D22.
• Sélectionner la cellule A22
• Saisir « Dépense mensuelle moyenne »
• Sélectionner la cellule D22
• « =Moyenne(C4 : C15) »

Exercice (Cf. ci-dessous fiche de travail N° 5)

Fiche de travail N°5

• Démarrer l’application Excel.
• Charger le fichier « f_A_oral ».
• Saisir les notes de l’oral dans la colonne 9
• Ecrire une formule à placer dans la cellule J5 recopiable dans le reste de la colonne J et qui calcule la moyenne générale pour chaque candidat.(rappel : moy gén = (moy à l’écrit + note orale)/2).
• Sélectionner la cellule J6
• Procéder comme précédemment pour saisir la formule suivante :
• =(G6+ I6)/2
• Appliquer le format arrondi :
• Sélectionner la cellule I6
• Réécrire la formule comme suit :
• =ARRONDI((G6+ I6)/2) ;2)
• Sauvegarder sous le nom « f_N_oral »

Votre feuille de calcul devrait ressembler à la figure (Fig.5), où les deux colonnes 2 et 3ont été masquées (Les volets figés au niveau de la cellule D6).

Fig. 5

Fiche de travail N°6

• Démarrer l’application Excel.
• Charger le fichier « f_N_oral ».
• Insérer une colonne avant la colonne « Résultat final »
• Sélectionner la cellule « Résultat final »
• Menu « Insertion » + « Colonnes »
• Ecrire une formule à placer dans la cellule K6, recopiable dans le reste de la colonne K, et qui calculerait le rang de chaque candidat.
• Sélectionner la cellule K6
• Procéder comme précédemment pour saisir la formule suivante :
• =Rang(J6 ; $K$6 : $K$121)
• Ecrire une formule à placer dans la cellule L6, recopiable dans le reste de la colonne 12, et qui afficherait :
• « Admis » pour tout candidat dont le rang est inférieur ou égal à 30, et qui n’aurait pas obtenu une note éliminatoire (< 5) à l’oral.
• « Eliminé » pour tout candidat ayant obtenu une note éliminatoire (< 5) à l’oral
• « Echec » pour tout candidat n’ayant pas de note éliminatoire à l’oral et dont le rang est supérieur à 30.
• Sélectionner la cellule L6
• Procéder comme précédemment pour saisir la formule suivante :
• =Si(I6<5 ; "Eliminé" ; Si( K6>30 ; "Echec" ; "Admis"))
• Sauvegarder sous le nom « f_final »

Votre feuille de calcul devrait ressembler à la figure (Fig.6), (Les volets figés au niveau de la cellule D6).

Fiche de travail N°7

Graphique

Les dépenses annuelles d’un foyer sont représentées dans le tableau ci-dessous.

Dépenses annuelles

Désignation	Trim 1	Trim 2	Trim 3	Total
Nourriture	6 500,00 F	5 800,00 F	6 000,00 F	18 300,00 F
Loyer	4 500,00 F	4 500,00 F	6 000,00 F	15 000,00 F
Gasoil	1 200,00 F	1 500,00 F	2 100,00 F	4 800,00 F
Voiture	1 400,00 F	1 600,00 F	2 500,00 F	5 500,00 F
Habits	1 500,00 F	1 700,00 F	800,00 F	4 000,00 F
Formation	500,00 F	500,00 F	900,00 F	1 900,00 F
Loisirs	900,00 F	1 200,00 F	2 000,00 F	4 100,00 F
Divers	1 000,00 F	2 000,00 F	4 000,00 F	7 000,00 F
Total	17 500,00 F	18 800,00 F	24 300,00 F	60 600,00 F

Application 1 :

Représenter les données du tableau ci-dessus par un graphique en Histogramme

Procédure

• Sélectionner tout le tableau à l’exception des totaux
  
• Cliquer sur le bouton Assistant graphique (au niveau de la barre d’outils)
• Choisir le type de graphique (dans cet exemple : Histogramme) + Suivant
• Choisir la représentation en ligne + Suivant.
  • 
• 
  

• Renseigner la boite de dialogue qui se présente à l’écran (facultatif) + Suivant

• Choisir l’emplacement du graphique (dans cet exemple : incorporé dans la feuille en tant qu’objet.
• Cliquer sur Fin.

Application 2 :

Utiliser le graphique sectoriel pour représenter la contribution de la rubrique « Divers » par rapport au total des dépenses.

N.B. la sélection ne doit porter que sur les deux dernières lignes

Application 3 :

Représenter les dépenses trimestrielles toutes catégories confondues

(la sélection porte sur la première et la dernière ligne sans inclure le total colonne, pour cela sélectionner d’abord la première ligne, maintenir la touche CTRL appuyée puis sélectionner la deuxième ligne)

 

Application 4 :

Représenter les dépenses annuelles par rubrique.

(la sélection porte sur la première et la dernière colonnes sans inclure le total ligne, pour cela sélectionner d’abord la première colonne, maintenir la touche CTRL appuyée puis sélectionner la deuxième colonne)