Introduction

Un peu de cours pour commencer.

Un tableur est un outil permettant de traiter des données rangées dans des tableaux. Les objectifs sont extrêmement variés : simulation, synthèse, visualisation, etc. Les domaines d’applications sont également extrêmement variés : comptabilité, gestion financière, enquêtes, etc.

Dans ce TD, nous allons illustrer les manipulations les plus fréquentes et les notions fondamentales (référencement de cellules, formule, fonction, tri, filtre, lien entre feuilles, etc.).

Exercice

  • Télécharger ce document. L'ouvrir avec OpenOffice ou Excel. Les données manipulées recensent les équipements par villes françaises.
  • Enregistrez-le sous le nom TDB3-NOM-tableau.ods.

Premiers pas

L’interface

  • L’adresse (ou référence) de la cellule sélectionnée est indiquée à gauche dans la zone de nom, ici E2.
  • Le contenu de la cellule E2 est une formule visible dans la Barre de formule, ici =C2*D2. Cela signifie que le contenu de la cellule C2 est multiplié par le contenu de la cellule D2. Le résultat de cette formule est 69 qui est visible dans la cellule.

Saisie des données et type des données

Dans un tableur, la saisie d’une donnée ou d’une formule dans une cellule se fait après avoir sélectionné la cellule. Toute saisie doit être validée en appuyant sur la touche ↵ (Entrée).

Il existe plusieurs types de données qui conditionnent les opérations que l'on peut appliquer sur ces données : nombres, textes, dates, valeurs monétaires, pourcentages, etc. Pour modifier le type d'une cellule : Format > Cellule > Nombre. Attention, dans les dernières versions d'Excel (à partir de 2013), ce menu n'est plus présent par défaut : il faut l'ajouter (cf FICHIER>Options>Personnaliser le ruban  ajouter le menu Format de cellule).

Sélection des données

Il y a toujours au moins une cellule sélectionnée. Pour sélectionner une plage (ensemble) de cellules, il y a deux méthodes :

  1. Commencer par cliquer sur la première cellule, puis étendre la sélection en maintenant le bouton de la souris enfoncé et en la déplaçant.
  2. Cliquer une première fois dans un angle de la plage puis une deuxième fois, dans l'autre angle, en maintenant la touche ⇧.

Pour sélectionner des cellules non contiguës (sélection multiple), il faut une première sélection (cellule ou plage) puis il faut maintenir la touche cmd sur un mac (respectivement ctrl sur un PC) enfoncée et sélectionner les autres plages de cellules.

Exercice

  • Combien de feuilles contient ce tableau ? Réponse..
  • Si une colonne est trop étroite pour afficher une valeur numérique, des #### sont affichés à la place de la valeur. Taper un nombre très long dans la cellule I3 et observer les ####. Jouer sur la largeur de la colonne pour refaire apparaître ce nombre.
  • Transformer ce nombre en prix Format > Cellule.
  • Effacer ce nombre.
  • Sélectionner tous les noms de communes et changer la couleur de fond en rose.

Visualiser l'ensemble du document

Exercice

  • Redimensionner la fenêtre de manière à ne voir que 9 colonnes au plus.
  • Sélectionner la cellule M182.
  • Pouvez-vous dire sans vous redéplacer dans la feuille à quoi correspond l’information contenue dans cette cellule ? quelle commune ? quelle catégorie d'équipement ? Réponse.

Le problème souligné par cet exercice survient dès que l’on manipule des tableaux de grandes dimensions. Lorsqu’on se déplace dans le tableau, les titres des lignes et des colonnes ne sont plus affichés.

Pour pallier cet inconvénient, il est possible de Fractionner (Scinder avec Open Office) ou de Figer les volets (Fixer avec Open Office) la feuille de calcul. La feuille est alors divisée en 4 volets, séparés par des traits de fractionnement.

Exercice

  • Sélectionner la cellule F9 puis appliquer le menu Fenêtre>Figer. Utiliser les barres de défilement pour vous déplacer dans le tableau et visualiser le haut et le bas du tableau.
  • Supprimer le figement puis recommencer en fractionnant cette fois. Quelle est la différence ?
  • Remettre en place le figement et le garder pour la suite du TD.

Recopie incrémentale

Les tableurs permettent de remplir des séries de nombres, de dates ou des séries personnalisées.
Il suffit de donner au tableur les deux premiers éléments de la série et il génère les éléments suivants de la série.

Exercice

  • Ajouter une nouvelle feuille et la renommer Test en double cliquant sur le titre de l'onglet.
  • Insérer la valeur 1 en A1 puis 2 en A2.
  • Sélectionner la plage de cellules A1:A2.
  • Positionner le curseur de la souris dans le coin bas-droit de la cellule A2.
  • Lorsque le curseur se transforme en une croix fine, tirer vers le bas.
  • Insérer la valeur 3 en B1, et essayer le même mécanisme de recopie incrémentale mais cette fois vers la droite.
  • Dans la cellule A50 taper lundi puis mardi dans A51 et étirer vers le bas.
  • Dans la case B50 taper etudiant1 puis dans la case B51 taper étudiant2 et étirer vers le bas.

Les formules

Les formules commencent toujours par le signe =.
Une formule utilise la plupart du temps le contenu d’une autre cellule. Pour cela, il faut soit saisir l’adresse de la cellule au clavier, soit cliquer dessus et son adresse s’inscrit automatiquement.

Exercice

  • Revenir sur la feuille COM.
  • Insérer une colonne vide entre les colonnes G et H. Cette colonne est maintenant la colonne H. Placer en H5 le texte Total grands magasins.
  • Nous allons à présent compléter les cellules de cette colonne pour y faire apparaître le nombre de grands magasins de chaque commune (Hyper et Super marché).
  • La formule en H7 est =F7+G7. Utiliser la souris pour sélectionner les cellules à additionner. Pour cela, cliquer sur chaque cellule et saisir le + au clavier. Vérifier que l’ordre de grandeur du résultat correspond à ce que l’on attend.

Une formule est un contenu de cellule. Elle peut être recopiée au même titre et de la même manière qu’une donnée.

Exercice

  • Positionner le curseur de la souris dans le coin bas-droit de la cellule H7.
  • Lorsque le curseur se transforme en une croix fine, tirer vers le bas.
  • Attention, penser à figer au préalable votre tableur.

Les fonctions

Nous désirons à présent calculer le nombre total de grands magasins en H36665.
Les fonctions sont des programmes écrits dans le but de faciliter la création de formules longues ou complexes.
Pour insérer une fonction dans une formule, il faut lancer la commande Insertion > Fonction. La fenêtre Assistant fonction s’ouvre alors et il faut choisir la fonction dans une catégorie.
Une explication succincte apparaît et il est toujours possible d’aller consulter l’aide pour plus de précision.
Elles ont une syntaxe précise qu’il est nécessaire de respecter et un nombre d’arguments (de paramètres) éventuellement prédéfini.

Exercice

  • Insérer la formule =SOMME(H7:H36664) en H36665.
  • Calculer les totaux pour toutes les catégories d'équipements.
  • Sur la ligne suivante 36666, calculer les moyennes et utiliser pour cela la fonction MOYENNE.
  • Pour chaque ville, calculer le nombre total d'équipements dans la colonne AD.

Adressage absolu

Nous désirons calculer la proportion d'hypermarchés et de supermarchés par rapport au nombre total de grands magasins (donc les ratios : nombre d'hypermarchés / nombre total de grands magasins et nombre de supermarchés / nombre total de grands magasins).

Exercice

  • Insérer la formule adéquate pour exprimer cette proportion pour les hypermarchés en F36667.
  • Présenter cette proportion sous forme d’un pourcentage grâce au menu Format>Cellule>Nombre.

Nous désirons à présent calculer la même proportion pour les supermarchés. Cette étape est difficile. N'hésitez pas à solliciter votre chargé de TD.
Il suffit apparemment de recopier le contenu de la cellule F36667 dans la cellule G36667. Faites-le. Que constatez-vous ? Réponse.
Examiner l’erreur qui se produit : G36667 contient quelle formule ? Analyser cette erreur en cliquant sur la barre de formule après avoir sélectionné la cellule.
Il faudrait donc que dans la formule copiée de F36667 à G36667, la cellule qui contient le nombre total de grands magasins, ne soit pas transposée.
Comme la copie ici se fait de gauche à droite, il suffit de bloquer cette cellule en ajoutant un dollar.
Le dollar peut se placer devant la numéro de ligne quand on étire vers le bas ou devant la colonne quand on étire vers la droite ? Ici où devons-nous le placer ? Réponse.

Exercice

  • Modifier la formule en F36667 de manière à ce que la recopie dans les cellules F36667 à G36667 donne le résultat attendu.

Exercice d'application : le seuil de rentabilité

Nous allons maintenant mettre en œuvre sur un premier exemple, les notions de tableur que nous venons d'explorer.

L'association des anciens étudiants de ton université projette de vendre deux ouvrages : "Comment réussir tes études" et "Les sorties dans ta ville". Le premier sera vendu en septembre, le second en octobre.
Il s'agit d'étudier les résultats prévisionnels pour ces deux ouvrages.
Le comité de rédaction a commencé par évaluer quelques charges :

Exercice 1

  • Télécharger ce document.
  • Enregistrez-le sous le nom TDB3-NOM-rentabilite1.ods.
  • Les cases à compléter sont en vert.
  • Dans la feuille "CahierDesCharges", distinguez charges fixes et charges variables. Pour cela indiquer dans la colonne D, les valeurs CF ou CV. Quelle particularité faut-il remarquer ? Certaines charges sont communes aux deux produits.

Exercice 2

Faire les comptes de résultat différentiel pour les produits, sachant que les quantités prévisionnelles de ventes sont de 1000 pour le livre "Comment réussir tes études" et de 1100 pour "Les sorties dans ta ville". Les prix de vente sont fixés à 3 € pour le premier et 4 € pour le second.

  • Remplissez le tableau présent sur la feuille "Comptes de résultat différentiel" en vous aidant des consignes présentes dans le tableau.
  • Remplissez le tableau présent sur la feuille "Graphique" en vous aidant des consignes présentes dans le tableau.
  • Tracer les diagrammes ci-dessous (Menu Insertion > Diagramme > XY (dispersion) > Points et Lignes).