Home

NOTIONS DE BASE - Jacques Cellier

1. UNIVERSITE DE RENNES 2 Jacques CELLIER 36 ANNEXE D EXCEL 5 VERS EXCEL97 Marche arri re Excel 5 permettait d annuler via Edition Annuler ou le bouton ln d annuler la derni re action ex cut e Excel 97 plus tol rant laxiste permet de remonter plusieurs tapes en arri re Conversion vers HTML Cette conversion d une feuille de calcul vers un document au format HTML s op re tr s simplement via Fichier Enregistrer au format HTML Le r sultat de cette m tamorphose pouvant tre un tableau formant une page Web autonome ou un tableau ins r dans une page Web pr existante Cartes Excel 97 int gre un module de cartographie certes beaucoup plus modeste que les logiciels sp cialis s mais n anmoins utile pour ceux qui n ont pas acc s ces derniers Il permet de relier des donn es g ocod es un fond de carte choisi pour les repr senter LE MOINS BON Il est paradoxal qu une nouvelle version d un logiciel soit en r gression sur certains points avec la version pr c dente Mais c est malheureusement le cas Le futur manque parfois d avenir Compatibilit Tradition chez Microsoft le format de fichier Excel 97 n est pas compatible avec le format Excel 5 Bien entendu Excel 97 s av re capable d ouvrir un vieux fichier et de le convertir au go t du jour mais l inverse est faux Impossible d ouvrir le moindre fichier Excel 97 m me un classeur ne contenant que des donn es
2. deux s ries d abscisse UNIVERSITE DE RENNES 2 Jacques CELLIER ANNEXE D EXCEL 5 VERS EXCEL97 33 CE QUI RESTE STABLE Aux yeux de ceux qui pensent qu Excel cela sert d abord a organiser des donn es num riques et faire des calculs avec l volution d une version a l autre appara tra mineure les techniques de base et la biblioth que de fonctions pr d finies restant inchang s On est loin du bond en avant qu avait t le passage de la version 4 5 L environnement menus menus contextuels barre d outils ne subit pas non plus de bouleversements d routants Autrement dit pour des utilisateurs ma trisant correctement la version 5 la migration devrait s op rer sans traumatismes majeurs QUELQUES CHANGEMENTS ANODINS Un certain nombre de modifications ont t apport es dans la pr sentation des Assistants Qu elles constituent un progr s ou non est une affaire de go t personnel L assistant fonction L Assistant fonction voit l apparition des bo tes de saisie r tractables SE En cliquant sur le bouton A la bo te s efface pour ne laisser appara tre que le champ de saisie 2 71828 Apr s saisie un clique sur le bouton restituera la boite de saisie dans son int gralit Ce dispositif est destin faciliter le clique sur les cellules contenant les arguments lorsqu elles sont masqu es par la bo te de dialogue L assistant graphique Les quatre tapes de l
3. placer dans la formule source Ces questions seront trait es au d but du prochain chapitre UNIVERSITE DE RENNES 2 Jacques CELLIER Principes et m thodes de base v S lection du bloc form de la cellule source et des cellules buts et Edition Recopier vers le bas ou a droite v Etirer a la souris la cellule active contenant la formule a recopier a partir du petit carr situ en bas a droite de cette cellule Cr ation de s ries de donn es Il est tres simple d engendrer sur une ligne ou une colonne une s rie de valeurs en progression arithm tique 1 2 3 ou de dates jan f v Il suffit de placer dans deux cellules l une en dessous de l autre ou l une c t de l autre les deux premiers termes de la s rie de s lectionner les deux cellules et d tirer la souris comme pr c demment le petit carr magique Pour des s ries plus sophistiqu es comme les s ries g om triques 1 2 4 8 ou chronologiques vari es il faut apr s criture du premier terme dans une cellule et s lection du bloc devant accueillir la s rie premier terme inclus utiliser Edition Recopier S rie et pr ciser dans la bo te de dialogue le type et ventuellement le pas et la derni re valeur 1 2 7 INSERER SUPPRIMER Lignes colonnes cellules Pour ins rer une colonne enti re au sein d une feuille de calcul d j en partie labor e il suffit de s lectionner la colonne situ e l endroit o l
4. on veut effectuer l insertion puis d utiliser Insertion Colonne ou le bouton droit de la souris L insertion de la nouvelle colonne d place vers la droite la colonne s lectionn e et toutes celles situ es droite Excel a le bon go t de mettre jour les r f rences ventuellement bouscul es par cette insertion Le proc d mutatis mutandis s applique bien videmment aux lignes L insertion d un bloc de cellules apr s s lection du bloc s effectue de la m me fa on mais Excel demande si les cellules existantes doivent tre d cal es vers le bas ou vers la droite Les suppressions de lignes colonnes ou cellules s op rent de la m me fa on apr s s lection par Edition Supprimer ou via le bouton droit de la souris Mais cette suppression peut avoir des effets d sastreux lorsque des formules font r f rences des cellules supprim es A ce propos il ne faut pas confondre l effacement obtenue Edition Effacer qui vide une ou plusieurs cellules de leur contenu ou de leur format sans affecter les cellules voisines avec la suppression qui elle d place ples cellules voisines lesquelles viennent prendre la place des cellules supprim es Annotation Les annotations sont des commentaires libres que l on peut attacher a une cellule La cr ation d une annotation se fait par Insertion Annotation qui ouvre une bo te de dialogue permettant de composer une annotation pour la cellule active Par ailleurs cette m me bo t
5. requises 1 Excel ignorant les dates ant rieures les historiens peuvent se sentir frustr s UNIVERSITE DE RENNES 2 Jacques CELLIER 22 Il R f rences et fonctions Limites Effectifs O FREQUENCE DONNEES LIMITES La fonction calcule v droite de x4 le nombre de donn es lt xi v droite de x2 le nombre de donn es gt x et lt Xo etc v et dans la derni re cellule le nombre de donn es gt x Fonctions d ajustements d un nuage de points Pour calculer un ajustement lin aire Excel propose deux fonctions matricielles y DROITEREG Y X qui fournit dans deux cellules les coefficients a et b de l quation y ax b de la droite d ajustement lin aire de la s rie de donn e Y par rapport a la s rie de donn e X y TENDANCE Y X qui fournit dans un bloc de cellules les ordonn es des points de la droite d ajustement correspondant aux valeurs de la s rie X Il existe par ailleurs pour les amateurs des versions plus sophistiqu es de ces deux fonctions consulter l aide en ligne Deux fonctions jouent un r le analogue lorsque l on souhaite ajuster le nuage de points par une courbe exponentielle et non par une droite v LOGREG Y X v CROISSANCE Y X Formules matricielles Avec les m mes conventions de validation notamment l utilisateur peut crire ses propres formules matricielles Sont a consid rer et donc a traiter comme telles toutes formules dont le r sultat final ou un r su
6. thode permet en particulier de calculer des limites de suites d finies par r currence Exemple Soit calculer la limite de la suite d finie par u 1et pour n gt Ou 1 Placer u n 1 la valeur initiale dans la cellule L1C1 par exemple puis crire dans la cellule L1C2 la formule 1 1 LC 1 qui calculera u Revenir a la cellule L1C1 pour y placer la formule 1 1 LC 1 et Excel envoie le message d erreur pr visible Prendre alors Outils Options Calcul et dans la bo te de dialogue cocher It rations modifier ventuellement Nb maximum d it rations et Ecart maximum propos es par d faut et cliquer le bouton Calculer maintenant les r f rences circulaires sont r solues et une valeur approch e de la limite de la suite est calcul e dans l exemple cette limite est le nombre d or Ne pas oublier par la suite de revenir au mode de recalcul automatique 11 2 LES FONCTIONS Avec Excel 5 l ventail de fonctions disponibles en standard est d j consid rable et si de plus la macro compl mentaire Utilitaire d analyse a t install l ensemble est impressionnant puisque l amateur y trouvera m me des fonctions valeurs et arguments complexes UNIVERSITE DE RENNES 2 Jacques CELLIER 17 Il R f rences et fonctions 112 1 SYNTAXE GENERALE Une fonction retourne une valeur calcul e a partir d arguments L criture g n rale est NOM LISTE_D_ ARGUMENTS dans laquelle v NOM d signe le
7. tre plac s sur celle ci Les graphiques seront d velopp s au chapitre 3 1 3 L AFFICHAGE Parmi les moyens de contr ler l affichage l cran on trouve Le zoom Ajustable par Affichage Zoom ou le menu d roulant flou de la barre d outils Les zooms a 50 ou 25 s av rent pratiques pour op rer et visualiser une s lection tendue Les volets Fen tre Figer les volets permet d inhiber l cran le d filement des cellules situ es au dessus ou gauche de la cellule active Ce dispositif est utile pour faire d filer de longues feuilles l cran tout en ayant toujours en vue lignes et ou colonnes de titre Le fractionnement de la feuille Le fractionnement de la feuille consiste afficher dans l espace de travail deux ou quatre vues avec un d filement synchronis entre les vues La commande Fen tre Fractionner partage la feuille en quatre la commande Fen tre Supprimer le fractionnement annule ce partage Il est aussi possible de tirer la souris la barre h LA A LA noire 2 situ e en haut gauche de la fen tre du document pour cr er un partage UNIVERSITE DE RENNES 2 Jacques CELLIER 10 Principes et m thodes de base horizontal que l on annule en tirant la souris la barre de s paration en haut de la feuille Le m me dispositif s applique mutatis mutandis pour un fractionnement vertical Le multi fen trage Excel autorise louverture simultan e de plusieurs classeurs Le pass
8. Assistant Graphique ont fait l objet d une nouvelle distribution et pr sentation Les voici dans l ordre UNIVERSITE DE RENNES 2 Jacques CELLIER 34 Types shane Types pero Type de graphique 1 Choix du type de graphique ASSIS lat Graphique Etape 3 our 4 Op Ties os Quaditsge L gende tiquettes de donn es Lee da gate LS am Aa dent ardani E ar ie Le des gjori T oF LE Are th i a pert fi l i de r E 3 Documentation du graphique Jacques CELLIER Feire le Qrapheque ANNEXE D EXCEL 5 VERS EXCEL97 Assistant Ca raphe tape 4 sur d Emplacement du T ar une poele leuk raphi 4 Emplacement du graphique UNIVERSITE DE RENNES 2 ANNEXE D EXCEL 5 VERS EXCEL97 35 QUELQUES NOUVEAUTES INTERRESSANTES Mise en forme et pr sentation Des possibilit s suppl mentaires concernant la mise en forme des cellules et des feuilles ont t rajout es Certaines la limite du cosm tique mais pourquoi pas 7 exemples v Alignement du texte en diagonale Format Cellule Alignement v Image d arri re plan de la feuille Format Feuille Arri re plan d autres plus consistantes exemples v Mise en forme conditionnelle Format Mise en forme conditionnelle Celle ci autorise une mise en forme diff rente suivant que la valeur affich e dans une cellule v rifie ou non une condition sp cifi e au travers d une bo te de dialogue Utile pour attirer
9. LE CLASSEUR 1 2 LA FEUILLE DE CALCUL 1 2 1 LIGNES COLONNES CELLULES 1 2 2 CONTENU DES CELLULES 1 2 3 FORMULES DE CALCUL 1 2 4 ERREURS 1 2 5 MISE EN FORME ET PRESENTATION 1 2 6 COPIER COLLER 1 2 7 INSERER SUPPRIMER 1 3 LAFFICHAGE J O1 CD CO UD ID 1 1 4 LES IMPRESSIONS 1 5 UN PEU DE METHODE Il REFERENCES ET FONCTIONS 1 1 LES REFERENCES I 1 1 LE MECANISME DES REFERENCES 11 1 2 EXEMPLES D UTILISATION 1 1 3 REFERENCES A DES BLOCS 1 1 4 REFERENCES EXTERNES 11 1 5 NOMS DE CELLULES 11 1 6 ITERATIONS 11 2 LES FONCTIONS 11 2 1 SYNTAXE GENERALE 11 2 2 MISE EN OEUVRE 1 2 3 BREVE ANTHOLOGIE 11 2 4 FONCTIONS MATRICIELLES Ill LES GRAPHIQUES 11 1 LES TYPES DE GRAPHIQUE IIl 2 CREATION D UN GRAPHIQUE 112 1 SELECTION 11 2 2 INSERTION DU GRAPHIQUE 11 2 3 ASSISTANT GRAPHIQUE II 3 PERSONNALISATION 111 3 1 ECHELLES 111 3 2 QUADRILLAGES II1 3 3 ETIQUETTES 111 3 4 TRACE LISSE 111 3 5 COURBES DE TENDANCE 111 3 6 BARRES D ERREUR 111 3 7 DEUX SERIES D ABSCISSE ANNEXE D EXCEL 5 VERS EXCEL97 CE QUI RESTE STABLE QUELQUES CHANGEMENTS ANODINS L assistant fonction L assistant graphique QUELQUES NOUVEAUTES INTERRESSANTES Mise en forme et pr sentation Validation Graphiques Marche arri re Conversio
10. cellules de r sultats interm diaires tous ces points doivent tre soigneusement examin s avant de se UNIVERSITE DE RENNES 2 Jacques CELLIER 12 Principes et m thodes de base pr cipiter sur le clavier Un sch ma confectionn avec du papier et un crayon n est pas inutile v Tenir compte du fait qu une feuille de calcul doit pouvoir tre utilis e avec diff rents jeux de donn es v Dans les formules utiliser des r f rences plut t que des valeurs num riques Faute de quoi la feuille ne s adaptera pas aux modifications de donn es y Utilisez au maximum les copies de formule vy V rifier et re v rifier les formules v Ne se lancer dans la mise en forme et la pr sentation qu apr s avoir achev la mise au point de la feuille Si l on se satisfait de parer de couleurs chatoyantes des calculs faux il faut mieux se r orienter vers un logiciel de dessin Il en existe d excellent v Traiter simultan ment toutes les cellules devant recevoir la m me pr sentation plut t que de recommencer dix fois les m mes manipulations vy Ne pas oublier que tous les outils de mise en forme et de pr sentation doivent tre mis au service de la lisibilit des donn es et r sultats UNIVERSITE DE RENNES 2 Jacques CELLIER 13 Il R f rences et fonctions 11 1 LES REFERENCES 111 1 LE MECANISME DES REFERENCES Excel donne le choix entre deux syst mes de r f rences aux cellules Dans le syst me A1 les li
11. donner les m mes dimensions plusieurs lignes ou colonnes il faut pr alablement les s lectionner en cliquant glissant sur les num ros correspondants Il est aussi possible de recourir Format Ligne ou Format Colonne Les colonnes ou lignes peuvent tre masqu es par ces m mes m thodes Format des cellules Il faut pr alablement s lectionner les cellules concern es par la mise en forme S lection de cellules y Pour s lectionner un bloc rectangulaire de cellules on clique et glisse sur le bloc Les cellules s lectionn es l exception de la premi re sont affich es avec les couleurs compl mentaires de celles d origine Y Pour s lectionner plusieurs blocs rectangulaires il faut par s avoir s lectionn un bloc appuyer sur la touche Ctrl rel cher la souris et passer au bloc suivant y Pour s lectionner la totalit d une ligne ou d une colonne cliquer sur son num ro Y Pour s lectionner toute la feuille cliquer sur le bouton gris situ gauche des num ros de ligne v Pour d selectionner cliquer n importe o Mise en forme du texte Une fois la s lection effectu e Excel offre toute une panoplie pour mettre en forme les cellules Celle ci est accessible soit partir du menu Format Cellule soit en UNIVERSITE DE RENNES 2 Jacques CELLIER Principes et m thodes de base cliquant avec le bouton droit de la souris sur les cellules s lectionn es soit en utilisant les boutons de racc
12. en abscisse la deuxi me colonne fournira les valeurs correspondantes d une premi re s rie de donn es etc La premi re servira de textes de l gendes pour distinguer les diff rentes s ries repr sent es Le bloc n est pas n cessairement constitu de colonnes juxtapos es Le bloc s lectionn peut avoir une disposition analogue en permutant le r le des lignes et des colonnes 111 2 2 INSERTION DU GRAPHIQUE La commande Insertion Graphique propose d incorporer le graphique sur la feuille ou de le cr er dans une feuille sp cifique du classeur Dans le premier cas le pointeur de la souris se transforme en qu il faut d ployer la souris pour d finir la taille Th et la position initiales du futur graphique Le bouton incorporer le graphique sur la feuille Dans tous les cas le lien entre le graphique et les donn es qu il visualise est dynamique Une modification des donn es provoque une mise jour du graphique 11 2 3 ASSISTANT GRAPHIQUE La commande pr c dente lance simultan ment l Assistant graphique qui en 5 tapes mat rialis es par autant de bo tes de dialogue va guider l utilisateur pour op rer un certain nombre de choix Etape 1 5 L assistant graphique demande confirmation du bloc de donn es s lectionn Si celui ci ne correspond pas on peut soit corriger manuellement les r f rences soit annuler et recommencer toute la proc dure Etape 2 5 L assistant graphique propose
13. g n ral suivant Cliquer avec le bouton droit de la souris sur un objet s lectionn ouvre un menu contextuel c d d pendant de l objet cibl pour modifier les propri t s de cet objet Cette mani re de proc der s av re efficace notamment avec les graphiques ANNULATION D UNE COMMANDE Excel accorde ses utilisateurs un droit au remords La derni re commande ex cut e peut tre annul e par Edition Annuler o par le bouton Mais attention contrairement Word 6 cette lib ralit se limite la derni re commande et ne va pas au del 1 1 2 LA FENETRE DE DOCUMENT LE CLASSEUR Avec Excel 5 le document de base se nomme un classeur et peut regrouper diff rentes feuilles v des feuilles de calcul UNIVERSITE DE RENNES 2 Jacques CELLIER Principes et m thodes de base v des feuilles graphiques v des feuilles de programme en Visual Basic baptis es des modules v des feuilles de bo te de dialogue Chaque feuille du classeur est rep r e par un onglet Cliquer sur un onglet active la feuille correspondante Ces onglets portent par d faut les noms Feuil1 Feuil2 etc Cliquer avec le bouton droit de la souris sur un onglet ouvre un menu contextuel qui permet entre autres de donner un nom plus parlant la feuille L ordre de ces feuilles peut d ailleurs tre modifi en cliquant et glissant les onglets correspondants 1 2 LA FEUILLE DE CALCUL 1 2 1 LIGNES COLONNES CELLULES Une feu
14. le Visual Basic Editor qui offre une interface certes plus compl te mais inutilement complexe pour la r daction et la mise au point de petites macros fonctions personnalis es et modification de code engendr par l enregistreur Un probleme de langue apparait avec cette nouvelle version Avec Excel 5 Visual Basic tait francis avec Excel 97 les termes du langage sont anglais ce qui ne pose pas v ritablement de probl me sauf lorsqu on utilise des fonctions Excel dans le code Dans ce cas il est n cessaire de traduire les noms frangais des fonctions Excel pour pouvoir les utiliser dans le code Visual Basic Ainsi au lieu d crire par exemple application moyenne il faut crire application average Curieusement cette traduction est r alis e automatiquement lorsqu on ouvre avec Excel 97 un classeur Excel 5 contenant une feuille module mais pas lorsque l on r dige directement du code sous Excel 97 Un glossaire de conversion Listevba xls est fourni avec la nouvelle version Le Solveur Avec Excel 97 le Solveur ne fonctionne pas si lon opte pour le style de r f rence L1C1 IL semble que cela soit li un probl me de traduction le solveur codant les cellules sous la forme R1C1 version anglaise Row Ligne au lieu de L1C1 UNIVERSITE DE RENNES 2 Jacques CELLIER TABLE DES MATIERES PRINCIPES ET METHODES DE BASE 1 1 LE DECOR GENERAL 1 1 1 LA FENETRE DE L APPLICATION 1 1 2 LA FENETRE DE DOCUMENT
15. me nombre Il est donc inutile et m me nuisible de placer des coefficients 100 parasites dans les formules Le nombre de d cimales affich doit tenir compte de leur caract re significatif Afficher la distance de Rennes a New York au micron pr s trahit un manque d esprit critique facheux en g n ral et une m connaissance de la d rive des continents en particulier 1 2 6 COPIER COLLER Comme tout logiciel Windows Excel offre sous forme habituel le Copier Coller qui permet de recopier le contenu texte donn e num rique ou formule d une cellule source vers une ou d autres cellules 1 On active ou on s lectionne le ou les cellules a copier O E t 2 On utilise Edition copier ou le bouton 3 On s lectionne les cellules de destination 4 On utilise Edition coller ou le bouton A Mais il propose des variantes plus rapides mettre en oeuvre ou plus adapt es ap applications d Excel lesquelles font un usage intensif de la recopie des formules Dans nombre de feuilles Excel on trouve une formule r p t e sur toute une colonne ou ligne Il nest videmment pas n cessaire d introduire une une les formules dans chacune des cellules Il suffit d crire la formule dans la premi re cellule source puis d utiliser un des deux moyens suivants On peut utiliser Couper au lieu de Copier qui effacera le contenu de la source Dans certains cas les recopies de formules posent des probl mes quant aux r f rences
16. travers la bo te de dialogue de la figure 7 le choix du type de graphique Etape 3 5 En fonction du choix pr c dent l assistant propose diff rents sous types possibles fournit un raccourci pour UNIVERSITE DE RENNES 2 Jacques CELLIER 26 Ill Graphiques Etape 4 5 L assistant propose a travers la boite de dialogue repr sent Figure 11 de confirmer ou d infirmer certains choix concernant l interpr tation donner au bloc des donn es Ces choix sont fondamentaux A ce stade l assistant graphique donne une esquisse du futur graphique pour appr cier la pertinence de ces choix Assistant Graphique Etape 4 sur5 x Exemple de graphique S rie de donn es en Lignes poageeseosesesseossesesossse Le Sr rr eerte e Utilisez les nh premi res colonnes pour les tiquettes de l axe des cat gories X Utilisez les nh premi res lignes pour le texte de la l gende Aide _Annuter lt Pr c dent Suivant gt En Figure 10 interpr tation du bloc de donn es v S ries de donn es en colonnes ou en lignes Si les donn es sont dispos es selon la figure 8 il faudra choisir en colonnes v Interpr tation de la 1 colonne resp 1 ligne Dans une disposition en colonnes la 1 colonne peut tre interpr t comme tant les abscisses ou comme constituant la premi re s rie de donn es auquel cas Excel placera 1 2 3 etc comme abscisses Ce choix s effectue en sp
17. 5000 F En supposant que la cellule contenant le montant avant r duction soit nomm e MONTANT la formule pour appliquer la r duction s crira UNIVERSITE DE RENNES 2 Jacques CELLIER 20 Il R f rences et fonctions SI MONTANT lt 5000 MONTANT 0 95 MONTANT 0 90 Les SI peuvent s embo ter pour traduire des branchements conditionnels trois branches ou plus Exemple Pour faire appara tre dans des cellules la mention obtenue un examen en fonction de la note on crira la cellule contenant la note tant nomm e NOTE la formule SI NOTE lt 10 Aj SI NOTE lt 12 P SI NOTE lt 14 AB SI NOTE lt 16 B TB La fonction SI se combine aussi avec les autres fonctions logiques comme dans l exemple suivant Exemple Pour faire apparaitre le texte Dedans ou Dehors suivant que le contenu de la cellule voisine soit dans l intervalle 8 12 ou non on crira la formule SI ET LC 1 gt 8 lt 12 Dedans Dehors Fonctions de recherche Elles permettent de rechercher soit a partir d une valeur soit a partir d une position une donn e dans un bloc de cellules Exemples INDEX B L C retourne la valeur situ e sur la L T ligne et C colonne du bloc B L et C d signant la position par rapport au bloc et non par rapport la feuille RECHERCHE V V1 V2 recherche la valeur V dans un bloc d une seule ligne ou d une seule colonne V1 rang par ordre croissant et retourne la valeur corr
18. Format et Insertion dont le contenu change lorsque le graphique est ouvert On peut ainsi changer les couleurs le type des trac s ou des marques les polices employ es dans les zones de texte le format d affichage des nombres etc Ces mises en forme de type d coratives s op rent sans aucune difficult Voici quelques pr cisions concernant des modifications ou insertions d l ments plus sp cifiques aux graphiques 111 3 1 ECHELLES Par d faut Excel calcule automatiquement les chelles d axe adapt es aux donn es Ce choix peut tre corrig essentiellement pour les chelles num riques c est le cas des axes Y et de laxe des X dans le cas d un graphique de type XY Cette personnalisation de l chelle s effectue en cliquant avec le bouton droit de la souris sur laxe puis en choisissant Format d axe dans le menu contextuel puis en cliquant l onglet Echelle de la bo te de dialogue Il est alors possible de red finir la valeur 12 Si le graphique est sur une feuille s par e on clique sur son onglet pour l ouvrir S il est incorpor dans une feuille on clique deux fois sur le graphique pour l ouvrir il appara t alors dans un encadrement hachur ou dans une fen tre UNIVERSITE DE RENNES 2 Jacques CELLIER 29 Ill Graphiques minimum des graduations la valeur maximum l unit principale l unit secondaire On peut aussi choisir une chelle logarithmique 111 3 2 QUADRILLAGES L insertion de quadrillage s obti
19. UNIVERSITE DE RENNES 2 FASCICULE 1 AVEC ANNEXE SUR EXCEL 97 Jacques CELLIER Principes et m thodes de base 1 1 LE DECOR GENERAL Edition Affichage menion Format ane Bon w 2 ESE Rel Ee erases a ppur Figure 1 fen tres Excel 1 1 1 LA FENETRE DE L APPLICATION Outre quelques raccourcis claviers parfois encore utilis es Excel offre trois moyens d acc s ses commandes et outils LES MENUS En haut de la fen tre de l application le menu principal ouvre des sous menus d roulants On y retrouve les grandes rubriques traditionnelles des logiciels Windows Fichier Edition Affichage Insertion Format Outils Fen tre l instar de Word 6 plus une rubrique Donn es La r partition des commandes entre ces diff rents menus a t en partie modifi e par rapport la version pr c dente Excel 4 En voici une description sommaire UNIVERSITE DE RENNES 2 Jacques CELLIER Principes et m thodes de base Fichier regroupe les commandes pour ouvrir un fichier l enregistrer sous un autre nom ou autre r pertoire et toutes les commandes de mise en page et d impression Edition regroupe les commandes pour couper copier coller les commandes d effacement de suppression etc Affichage regroupe les outils pour r gler l affichage zoom ou pour personnaliser la fen tre de l application Insertion regroupe les commandes pour ins rer de nouvelles lignes de nouvelles colonnes ou de
20. age du classeur actif un autre s obtient par l interm diaire du menu fen tre qui en donne la liste ou par la combinaison de touches Tab Il est aussi possible d obtenir dans l espace de travail l affichage de deux fen tres ou plus mais la visibilit devient douteuse d une m me feuille d un m me classeur ou de deux classeurs distincts Dans les deux premiers cas il faut pr alablement cr er une nouvelle fen tre par Fen tre Nouvelle fen tre dans le dernier cas il faut s assurer que les deux classeurs sont ouverts Puis dans tous les cas on utilise Fen tre R organiser pour choisir parmi les dispositions propos es Mosa que Vertical Horizontal Cascade A signaler que contrairement au fractionnement le d filement dans les diverses fen tres n est pas synchronis 1 4 LES IMPRESSIONS Qu imprimer On peut choisir d imprimer v La feuille active v Une s lection de la feuille active v Les feuilles s lectionn ed v La totalit du classeur Ce choix s op re via Fichier Impression Sur quelle imprimante C est vers l imprimante par d faut de Windows que sera dirig e l impression La bo te de dialogue ouverte par Fichier Impression permet de rediriger l impression vers une autre imprimante install e Mise en page Elle s op re par l interm diaire de Fichier Mise en page qui ouvre une bo te de dialogue onglets permettant de choisir Onglet Page La dimension de la feuille de papier et
21. brutes sans la moindre formule avec Excel 5 Compagnon Office Toute la gamme Microsoft Office s est vue affubl d un Compagnon Thal sense venir en aide l usager gar Sous la forme d un trombone convulsif il propose inlassablement des services que l on a nullement sollicit s et rarement en rapport avec ce que l on est en train d effectuer Souhaitez vous acc der l aide d une fonction Il surgira pour vous demander si vous voulez vraiment de l aide sur cette fonction l ou sur une autre puis vous proposera un ou deux conseils avant que VOUS puissiez acc der la rubrique d aide souhait e Son pouvoir de nuisance peut tre mouss via le menu Options qu il lui est associ mais pour s en d barrasser compl tement il faut passer par le programme d installation du logiciel Mise en uvre du Visual Basic Avec Excel 5 le code Visual Basic r dig par l utilisateur ou engendr par l enregistreur de macros tait plac dans des feuilles dites feuilles modules ins r es directement dans le classeur Ce dispositif avait l avantage de la simplicit Ou sous une autre ventuellement L utilsateur peut choisir la forme de son importun compagnon La mouche du coche conviendrait merveille Peut tre qu avec la version 2000 Jacques CELLIER UNIVERSITE DE RENNES 2 ANNEXE D EXCEL 5 VERS EXCEL97 37 Avec Excel 97 il faut d sormais ouvrir a partir de Outils Macros
22. ce au prix devra tre absolue en colonne le prix est toujours sur la 1 colonne mais relative en ligne Pour le taux de r duction c est l inverse la r f rence devra tre absolue en ligne le taux de r duction est toujours sur la premiere ligne mais relatif en colonnes Avec ces r f rences la formule peut alors tre recopi e aussi bien vers le bas que vers la droite OA oo aa a a O A aJ 5 _ 6 p k 2 78 00F LC1 1 L1C a 37 2350F 42 00 ZE LC1 1 Figure 4 r f rences mixtes UNIVERSITE DE RENNES 2 Jacques CELLIER 15 Il R f rences et fonctions 111 3 REFERENCES A DES BLOCS La r f rence a un bloc ou plage rectangulaire de cellules s obtient en sp cifiant la r f rence de la cellule situ e en haut a gauche et la r f rence de la cellule en bas a droite connect es par deux points Exemple L1C2 L10C2 L 4 C 2 L 1 C 1 La r f rence a un ensemble de cellules non contigu s comme plusieurs blocs rectangulaires s obtient en connectant leurs r f rences avec un ou des points virgules symbole de la r union ensembliste pour Excel Exemple L1C1 L2C2 L3C3 L4C4 L5C5 est la r f rence de la diagonale du bloc rectangulaire L1C1 L5C5 On rappelle que pour s lectionner des blocs non contigu s il faut combiner le cliquer glisser la souris avec l appui de la touche Ctrl 11 1 4 REFERENCES EXTERNES Le cas plus courant est la r f rence a une ou plusieurs cellules d une
23. cifiant 1 ou 0 dans la case Utilisez les _ premi res colonnes pour les tiquettes de l axe des cat gories X v Interpr tation de la 1 ligne resp 1 colonne Dans une disposition en colonnes la 1 ligne peut tre interpr t comme donnant le titre des diff rentes s ries titre qui pourra servir de texte de l gende Elle peut aussi tre interpr t e comme donnant la 1 valeur de chaque s rie Ce choix s effectue en sp cifiant 1 ou 0 dans la case Utilisez la _ premi res lignes pour le texte de la l gende Exemple Pour illustrer l importance de ces choix voici a partir du bloc de donn es UNIVERSITE DE RENNES 2 Jacques CELLIER 27 Ill Graphiques Apr s avoir choisi le type de graphique dit Histogramme donc en fait des barres verticales voici ce qu on obtient en choisissant s rie de donn es en colonnes 1 colonne comme tiquettes de l axe des X 1 ligne comme texte de l gende DEUG AES 600 500 400 300 E BAES 2 w 200 100 0 1992 1993 1994 1995 Ann es Figure 11 barres juxtapos es ou DEUG AES 1000 800 600 MAES 2 400 DAES 1 Ww 200 0 1992 1993 1994 1995 Ann es Figure 12 barres superpos es et ce que l on obtient en choisissant s ries de donn es en lignes 1 ligne comme tiquettes de l axe des abscisses 1 colonne comme texte de l gende DEUG AES 600 500 Aa
24. colonnes appropri es ou utiliser la fonction SOMMEPROD qui calcule les produits termes termes de deux ou plusieurs blocs puis en effectue la somme Exemple Valeurs 1 2 3 4 5 Fr quences 20 35 25 15 5 Le bloc des cing valeurs tant nomm X le bloc des cinq fr quences tant nomm F on calcule dans une cellule qui sera nomm e M la moyenne par SOMMEPROD X F puis la variance par SOMMEPROD X X F M M A signaler aussi la fonction COEFFICIENT CORRELATION Y X et qui retourne le coefficient de corr lation lin aire d une s rie de donn es Y sur une s rie de donn es X ce coefficient not py x toujours compris entre 1 et 1 est un indicateur de la UNIVERSITE DE RENNES 2 Jacques CELLIER 19 Il R f rences et fonctions qualit de la corr lation On peut aussi utiliser la fonction COEFFICIENT DETERMINATION Y X qui calcule le carr du coefficient pr c dent Fonction textes Certaines fonctions permettent de manipuler du texte Dans les exemples T1 Julius et T2 CAESAR CONCATENER T1 T2 Met bout a bout T1 suivi de T2 On peut utiliser a la place l op rateur amp Exemple CONCATENER T1 T2 et 11 amp T2 retourneront Julius CAESAR NBCAR T1 Retourne le nombre de caract res de T1 Exemple NBCAR T1 retournera 7 Pourquoi MAJUSCULE T1 Met le texte en majuscules ou en minuscules MINUSCULE T1 Exemple MAJUSCULE Julius retournera JULIUS STXT T1 1 K Extrait K caract r
25. e de dialogue donne la liste des cellules annot es et permet de visualiser les annotations associ es chacune d entre elles Les cellules annot es sont rep rables dans la feuille par la pr sence d un petit carr dans le coin sup rieur La confusion est d autant plus tentante que la touche Suppr efface le contenu de la s lection UNIVERSITE DE RENNES 2 Jacques CELLIER Principes et m thodes de base droit de la cellule Le bouton permet aussi de visualiser les annotations ainsi que d autres informations concernant la cellule active Nouvelle feuille Pour ins rer une nouvelle feuille de calcul dans un classeur on peut utiliser Insertion Feuille de calcul ou bien cliquer avec le bouton droit de la souris sur l onglet de la feuille active Objets flottants Identifies On peut ins rer dans une feuille de calcul divers objets Ces objets qui ne sont pas riv s une cellule peuvent tre d plac s ou redimensionn s ais ment a la souris Ce sont notamment Des vignettes de texte On peut placer n importe quel endroit de la feuille du texte encadr o non l aide du bouton de la barre d outil dessin Des l ments de dessin On peut dessiner des fl ches rectangles cercles etc l aide de la barre d outil x dessin ou m me via Insertion Image importer un dessin partir d un fichier graphique Des graphiques Des graphiques visualisant des donn es de la feuille peuvent directement
26. e le nombre de valeurs num riques de la liste L SOMME L Retourne la somme de la liste L MOYENNE L Retourne la moyenne de la liste L MEDIANE L Retourne la m diane de la liste L MIN L Retourne la valeur minimum de la liste L MAX L Retourne la valeur maximum de la liste L VARP L Retourne la variance de la liste L ECARTYPEP L Retourne l cart type de la liste L L d signant une liste de valeurs num rique en g n ral sp cifi es par les r f rences du bloc qui les contient Dans ce cas les cellules vides sont ignor es et ne sont donc pas trait es comme des z ros Pour les deux derni res cit es il existe deux variantes VAR L et ECARTYPE L La diff rence est la suivante VARP L et ECARTYPEP L sont a utiliser pour calculer la variance ou l cart type lorsque L repr sente la liste des valeurs pour une population statistique complete VAR L et ECARTYPE L sont a utiliser lorsque L repr sente les valeurs pour un chantillon d une population que l on cherche estimer Lorsque le nombre de valeurs est grand la diff rence entre les deux variantes est faible Attention Les fonctions MOYENNE MEDIANE VAR VARP ECARTYPE ECARTYPEP ne permettent pas de tenir compte d ventuelles pond rations Elles sont donc a proscrire lorsqu au lieu de disposer de la liste exhaustive des valeurs on ne conna t que la distribution Dans ce cas il faut mettre en sc ne soi m me le calcul en utilisant des lignes ou
27. ent en cliquant avec le bouton doit sur la zone de tra age et choix de Insertion de quadrillage dans le menu contextuel ou par Insertion Quadrillage du menu principal Le quadrillage peut concerner laxe des X et ou l axe des Y et se limiter aux graduations principales ou non 11 3 3 ETIQUETTES Par des m thodes analogues a celles du paragraphe pr c dent on peut ins rer dans le graphique aupr s de chaque marque repr sentative la valeur ou l abscisse correspondante Il est regrettable que les tiquettes se limitent pratiquement a ces deux possibilit s Exemple Population d l amp V 750000 e E 700000 650000 600000 550000 L e 500000 AA 1950 1955 1960 1965 1970 1975 1980 Figure 15 insertion d tiquettes 111 3 4 TRACE LISSE Dans les graphiques en trac continu type Courbe ou XY principalement le trac est constitu d une suite de segments de droites En cochant l option Lissage dans la bo te de dialogue Format de s rie de donn es onglet Motifs on peut arrondir ce trac 1 5 j 0 5 y 0 0 5 1 ZA 4 Liss Exemple Non liss Figure 16 lissage du trac UNIVERSITE DE RENNES 2 Jacques CELLIER 30 Ill Graphiques 111 3 5 COURBES DE TENDANCE A partir d une s rie de valeurs repr sent es par un graphique de type XY on peut obtenir automatiquement une courbe d ajustement c est dire une courbe simple l
28. es du texte T1 partir du 1 caract re Exemple STXT T2 2 3 retournera AES ll existe par ailleurs des fonctions de conversion de texte en valeur num rique et r ciproquement Mais ces conversions peuvent tre implicites Si l on crit par exemple 4 amp amp 5 la valeur retourn e sera le texte 4 5 Fonctions financieres Pour naviguer dans les eaux glac s du calcul financier Excel offre toute une panoplie de fonctions citons simplement VPM TP NP C qui retourne le montant du remboursement p riodique pour l emprunt d un capital C aux taux p riodique de TP sur une dur e de NP p riodes Fonctions logiques Les fonctions logiques sont la traduction Excel des connecteurs logiques de base NON ET OU SI La fonction SI est en particulier utilis e pour cr er une alternative ou branchement conditionnel dans une formule Sa syntaxe est SI CONDITION VALEUR SI VRAI VALEUR_SI_ FAUX y CONDITION repr sentant un expression logique retournant la valeur vraie ou fausse form e en g n ral avec les op rateurs de comparaison lt gt lt gt lt gt v VALEUR SI _VRAIE repr sentant une expression calculer si CONDITION est vraie v VALEUR SI FAUSSE repr sentant une expression calculer si CONDITION est fausse Exemple Une remise de 5 doit tre accord e sur les commandes d un montant strictement inf rieur 5000 F et une remise de 10 sur les commandes d un montant sup rieur
29. espondante du bloc V2 Fonctions d informations Elles retournent divers renseignements sur des cellules Exemples ESTNUM C retourne vrai si le contenu de la cellule C est une valeur num rique NB B retourne le nombre de cellules du bloc B contenant valeurs num riques NBVAL B retourne le nombre de cellules non vides du bloc B Fonctions dates et heures Pour Excel dates et heures sont des valeurs num riques trait es et affich es de mani re particuli res Excel appelle num ro de s rie le temps coul depuis le 1 UNIVERSITE DE RENNES 2 Jacques CELLIER 21 Il R f rences et fonctions janvier 1908 exprim en nombre de jours suivi de d cimales pour tenir compte des heures minutes secondes Ainsi le 1 mai 1996 midi correspond au num ro de s rie 35186 50 Par Format Cellule Nombre on peut obtenir l affichage des num ros de s rie sous les diff rents formats d heure et de dates conventionnels Divers fonctions sp cifiques permettent de g rer ces heures et dates Exemples MAINTENANT retourne le num ro de s rie en cours et l affiche sous la forme jj mm aaaa hh mm AUJOURDHUI retourne la date courante sous la forme jj mm aaaa ANNEE NS retournent respectivement l ann e le mois de 1 a MOIS NS 12 le jour de 1 a 31 l heure de 0 a 23 les JOUR NS minutes de 0 59 les secondes de 0O a 59 HEURE NS correspondant au num ro de s rie NS MINUTE NS SECONDE NS DATE A M J effect
30. gnes sont rep r es par un num ro et les colonnes par une lettre A Z AA AB Dans le syst me L1C1 lignes et colonnes sont toutes deux rep r es par un num ro La bascule d un syst me l autre se fait par Outils Options G n ral Dans une formule on peut utiliser des r f rences relatives ou des r f rences absolues v Une r f rence absolue d signera toujours la m me cellule quelque soit la position dans la feuille de la cellule dans laquelle elle est plac e v Une r f rence relative indique la position d une cellule par rapport la cellule dans laquelle elle est plac e Vv Les r f rences mixtes combinent les deux types pr c dents Le tableau suivant r sume leur notation et signification lt lt L1C1 lt amp Al gt 2 colonnes avant Relative en colonne 2 colonnes avant Dans la pratique l utilisateur n crit pas directement les r f rences dans la formule mais clique sur la cellule concern e Par d faut Excel traduit alors en r f rence relative Si l on souhaite changer le type de r f rence utiliser la touche F4 qui convertit suivant le sch ma suivant Le a L601 Lec 4 101 Le a Les r f rences d une cellule d autres cellules peuvent tre visualis es par Outils Audit Rep rer les ant c dents qui indique par des fl ch s sur la feuille de calcule les cellules auxquelles la cellule active fait r f rence Ce m me menu propose aussi de Rep rer le
31. ille de calcul est structur e en lignes et en colonnes num rot es Au croisement d une ligne et d une colonne se trouve une cellule rep r e par son num ro de ligne et son num ro de colonne qui constituent sa r f rence ou adresse La r f rence de la cellule situ e l intersection de la ligne 3 et de la colonne 4 est not e ainsi L3C4 A chaque instant une cellule marqu e par un encadrement caract ristique 2 a se trouve tre la cellule active de la feuille c est elle qui recevra les informations frapp es au clavier Pour rendre une cellule active on peut user des touches de d placement usuelles T gt 4 qui d placent la cellule active ou bien cliquer sur la cellule que l on souhaite rendre active Parall lement les barres de d filement classiques horizontales et verticales permettent de naviguer dans une feuille dont les dimensions peuvent exc der de beaucoup la taille de la fen tre d affichage 1 2 2 CONTENU DES CELLULES Les cellules peuvent recevoir essentiellement trois types d information 1 Du texte servant de libell et de titre pour documenter la feuille 2 Des donn es num riques Excel sait en principe distinguer le texte du num rique mais se livre parfois des fac ties troublantes S il on place ainsi dans une cellule 0 5 il affiche sans surprise 0 5 mais si on place 1 5 il affiche de fa on inattendue 01 mai interpr tant l expression frapp e c
32. in aire exponentielle ou polynomiale qui approche au plus pr s la courbe initiale On peut de plus obtenir directement sur le graphique l quation de la courbe d ajustement et la valeur du coefficient de d termination qui indique la qualit de l ajustement Cette insertion s obtient en cliquant avec le bouton droit de la souris sur la s rie concern e puis en choisissant Insertion de courbe de tendance dans le menu contextuel Dans la bo te de dialogue on choisit le type de courbe d ajustement d sir Lin aire et Exponentiel sont les plus couramment utilis s Moyenne mobile est de nature diff rente Puis par l onglet Options on peut obtenir l affichage de l quation et du coefficient de d termination sur le graphique Exemple Corr lation Temp rature Insolation 15 14 y 0 0039x 3 7099 J 2 R 0 8468 2 13 b 2 12 z z E Courbe droite g a de tendance o 11 E a E 104 a 9 1500 1700 1900 2100 2300 2500 2700 Insolation heures an Figure 17 insertion d une droite de tendance 111 3 6 BARRES D ERREUR En utilisant une proc dure analogue on peut ajouter chaque point d une s rie de donn e une barre mat rialisant la marge d erreur sur cette valeur Exemple Barres d erreur sur Y a 10 de la valeur Figure 18 in
33. ions marges et orientation d finies Les traces de ce d coupage peuvent se mat rialiser l cran sous forme de traits pointill s via Outils Options Affichage Sauts de page automatique Mais il est possible d imposer son propre d coupage Si l on veut qu un saut de page ait lieu par exemple entre les lignes 17 et 18 on s lectionne la ligne 18 et on utilise la commande Insertion Saut de page Ces sauts de page d finies par l utilisateur apparaissent sous forme de traits discontinus Lorsque les dimensions de la zone imprimer n exc dent que de peu la taille de la page une gymnastique classique sous Excel consiste jouer sur les largeurs de colonnes hauteurs de lignes tailles de polices etc pour que tout tienne en une seule page Aper u avant impression En tout tat de cause ne pas se priver des services de Aper u avant impression accessible aussi bien partir du menu Fichier que des bo tes de dialogue Mise en page et Imprimer pour viter le g chis de papier Outre la visualisation cette option permet de redimensionner la souris marges largeur de colonnes hauteur de lignes ou de retourner aux bo tes de dialogue Mise en page et Imprimer pour rectifier 1 5 UN PEU DE METHODE Quelques conseils gratuits y R fl chir l architecture g n rale conf rer la feuille de calcul La disposition des donn es l emplacement des formules de calcul et leurs enchainements l ventuel besoin de
34. l il et l attention qui doit suivre sur des r sultats hors normes v Une visualisation plus pratique des commentaires appel s annotations dans la version 5 Sous r serve d avoir coch Commentaires Indicateur seul dans Outils Options Affichage les cellules auxquelles un commentaire a t attach via Insertion Commentaire se verront orn es d un petit triangle rouge dans le coin sup rieur droit et le commentaire s affichera d s que l on pointera sur la cellule Validation La validation consiste op rer d s la saisie la pertinence de ce qui est introduit dans des cellules de donn es Cette nouvelle fonctionnalit trouve tout son int r t dans le cadre de la gestion de donn es o elle pr vient la pollution d une table de donn es par un utilisateur inattentif et nous le sommes tous par moment On y acc de donc logiquement via Donn es validation qui ouvre la bo te de dialogue Graphiques Parmi les nouveaux types de graphique qui font leur apparition un seul est digne de retenir l int r t le type Bulle qui permet de repr senter une fonction de deux variables z f x y en tra ant autour de chaque point de coordonn es x y un disque une bulle de rayon proportionnel a z Exemple X Y Z Graphique en bulles 0 0 4 2 t 0 4 1112 o 1 au NS 11150 14 EE 2 o 1 la 0 2 x 1 A 2
35. l orientation Portrait ou Paysage Cette derni re disposition s av rant souvent mieux adapt e a certaines feuilles Onglet Marges Les tailles des marges et le centrage de la zone imprim e par rapport a cette derni re 7 Cette s lection de plusieurs feuilles s obtient en cliquant et glissant sur les onglets s ils sont contig es ou en cliquant en appuyant simultan ment sur la touche Ctrl sinon UNIVERSITE DE RENNES 2 Jacques CELLIER 11 Principes et m thodes de base Onglet En t te Pied de page Comme en traitement de texte ces zones sont destin es a recevoir un texte qui sera r p t sur chaque page imprim e Par d faut Excel propose le nom de la feuille en en t te et le num ro de la page en pied de page Mais rien interdit de les personnaliser en y pla ant par exemple la date un grand titre Onglet Feuille Diverses options concernant l impression de la feuille v Impression ou non du quadrillage des num ros de ligne et de colonne v Choix de la Zone d impression v Impression des titres Cette derni re option permet l impression sur chaque page de lignes et ou colonnes de titres Elle est l quivalent pour l impression de Figer les volets D coupage Bien videmment les dimensions d une feuille de calcul peuvent exc der celle de la page Excel se livre alors un travail de d coupage de la feuille en rectangles qui pourront chacun tenir sur une page imprim e compte tenu des dimens
36. le seul type de graphique respecter l chelle num rique selon laxe des X Pour autant il n interdit en rien obtention d un trac continu alors que le type Courbe permet tout autant de son c t une repr sentation par des points isol s Pour illustrer cette diff rence fondamentale voici partir de la s rie de donn es X 4 10 7 Y 5 17 11 ce que donnera un graphique dit en Courbes Y 20 15 10 5 4 10 7 X Figure 7 graphique type Courbe et ce que donnera un graphique dit en XY Nuages de points Y 20 15 10 o 4 5 6 7 8 9 10 X Figure 8 graphique type XY Enfin le type Combinaison autorise le m lange de deux types compatibles comme des barres et une courbe dans une m me repr sentation Mais son int r t majeur r side dans ce qu il permet facilement d obtenir deux axes des Y avec des chelles diff rentes ce qui permet de repr senter sur un m me graphique deux s ries dont les domaines de variation sont tr s loign s UNIVERSITE DE RENNES 2 Jacques CELLIER 25 Ill Graphiques 111 2 CREATION D UN GRAPHIQUE 111 2 1 SELECTION La cr ation du graphique commence par la s lection du bloc des donn es repr senter Typiquement celui ci doit avoir la structure suivante Abscisses 1 S rie Se A Ke wD reac Ke LL Figure 9 s lection du bloc de donn es La premi re colonne non n cessairement num rique figurera
37. ltat interm diaire est un bloc de cellules Avec les m mes notations que dans l exemple cit page 18 on peut calculer moyenne et variance pond r es par les formules matricielles y SOMME F X pour la moyenne y SOMME F X X M M pour la variance UNIVERSITE DE RENNES 2 Jacques CELLIER 23 Ill Graphiques 7 Une repr sentation graphique vise a donner une vision synth tique claire et de v ridique d un ensemble de donn es num riques Quelque soit l outil utilis pour le r aliser il existe en fonction de la nature des donn es et de l clairage qu on souhaite leur apporter un certain nombre de r gles simples et de bon sens pour l essentiel respecter pour satisfaire a ces exigences Avec Excel le terrain est min Non seulement il ne fournit aucune aide concernant le respect de ces r gles mais en plus il gare sous une terminologie plus que douteuse Mais il faut lui rendre justice il permet d obtenir tr s rapidement en quelques coups de souris une repr sentation graphique aussi d corative et spectaculaire que falsificatrice si l on se dispense d une r flexion pr alable sur le type de repr sentation appropri la nature des donn es dont on dispose 111 1 LES TYPES DE GRAPHIQUE Voici l ventail propos par Excel Assistant Graphique Etape 2 sur 5 x ae ES Ca Cane AO te Choisissez un type de graphique Figure 6 les principaux types de graphique On peut t
38. m 1992 m 1993 g 00 1994 200 z 11995 100 Ann es Figure 13 inversion lignes colonnes A noter que dans ce deuxi me cas la superposition des barres serait d nu de sens UNIVERSITE DE RENNES 2 Jacques CELLIER 28 Ill Graphiques Etape 5 5 Elle permet a travers la boite de dialogue de la Figure 13 de mettre en place certains textes et titres utiles et m me indispensables pour certains a la compr hension du graphique Il s agit du titre g n ral du titre de laxe des X du titre de l axe des Y et de la l gende D s que deux s ries ou plus sont repr sent es sur un graphique une l gende plus explicite que s rie 1 s rie 2 etc attribu e par d faut par Excel s av re indispensable pour distinguer les diff rentes s ries Assistant Graphique Etape 5 sur 5 x Exemple de graphique Ajouter une l gende Oui C Non Titre du graphique DEUG AES Titre de l axe Cat gories X Ann es Valeurs Y Effectifs Superpos Le Aide C Annuler lt Pr c dent Suvon QU En DEUG AES Figure 14 textes d accompagnement 111 3 PERSONNALISATION Le principe pour personnaliser le graphique consiste ouvrit puis s lectionner en cliquant avec le bouton droit de la souris l l ment concern Axe s rie de donn es l gende zone de tra age etc puis d utiliser le menu contextuel qui s ouvre ll est aussi possible d utiliser les menus
39. n vers HTML Cartes LE MOINS BON Compatibilit Compagnon Office Mise en uvre du Visual Basic Le Solveur UNIVERSITE DE RENNES 2 Jacques CELLIER
40. nce de fonction fl de parenth ses et d op randes ou arguments crits en particulier en ce qui concerne le parenth sage et les r gles de priorit des op rateurs selon les r gles habituelles L originalit r side dans ce que les op randes plac es dans la formule peuyent tre non seulement des constantes num riques mais aussi des r f rences des cellules contenant des valeurs De plus ces r f rences peuvent tre obtenues automatiquement en cliquant sur les cellules concern es Ces cellules peuvent se trouver sur la m me feuille c est le cas le plus courant sur une autre feuille du classeur ou m me sur une feuille d un autre classeur ouvert Exemple Pour calculer dans une cellule le produit du contenu des deux cellules situ es au dessus il suffira apr s avoir activ cette troisi me cellule de Taper Cliquer sur la premi re cellule Excel crira L 2 C Taper Cliquer sur la deuxi me cellule Excel crira L 1 C Valider Excel valuera et affichera le r sultat du calcul dans la troisi me cellule Dans ce contexte une cellule contenant une formule a comme ant c dents les cellules qui sont ainsi r f renc es dans la formule Lorsque le conteny d une de ces cellules ant c dents se voit modifi Excel r value automatiquement la formule et affiche le nouveau r sultat Ainsi con ue la feuille de calcul est donc vivante chaque modification des donn es pro
41. nibles class es par cat gorie pour choisir la fonction puis ouvre alors une deuxi me bo te de dialogue pour choisir selon la fonction s lectionn e les arguments Dans cette deuxi me bo te les arguments obligatoires sont indiqu s en gras les autres sont facultatifs Ces arguments peuvent tre crits directement dans la barre correspondante soit tre sp cifi s la souris dans la feuille 11 2 3 BREVE ANTHOLOGIE Compte tenu de la quantit de fonctions disponibles il est hors de question d en donner un inventaire exhaustif l assistant fonction donnant par ailleurs acc s une aide pr cise pour chaque fonction Cette anthologie se bornera donc une pr sentation de quelques une des plus courantes Fonctions Maths et Trigo Dans cette rubrique sont regroup es toutes les fonctions math matiques classiques LOG EXP COS SIN etc Parmi les moins classiques on signalera la fonction ALEA et sa cousine ALEA ENTRE BORNES MIN MAX qui retournent un nombre Certaines fonctions comme PI qui renvoie une valeur approch e de x n ont pas d arguments II faut cependant l crire PI 1 ALEA est un autre exemple de fonctions sans arguments UNIVERSITE DE RENNES 2 Jacques CELLIER 18 Il R f rences et fonctions al atoire entre 0 et 1 pour l une et entre MIN et MAX pour l autre Elles permettent de simuler des tirages au sort Fonctions statistiques Les plus couramment utilis es sont NB L Retourn
42. nom de la fonction Avec Excel ces noms sont en majuscules sans espace a l int rieur du nom ni entre la fin du nom et la parenth se ouvrante Si on tape le nom en minuscule Excel l acceptera mais le convertira en majuscule v LISTE D ARGUMENTS d signe une liste de dl 1 ou n arguments s par es par des points virgules si n cessaire Un argument peut tre v Une constante num rique ou autre bien que ceci soit en g n ral viter v Une r f rence une cellule ou bloc de cellules Cette r f rence pouvant tre absolu relative mixte v Un nom d fini dans le classeur lequel est l quivalent d une r f rence absolue v Une expression ou une autre fonction ll est bien vident que le type des arguments fournis doit tre compatible avec ceux requis par la fonction et que l on ne saurait demander la fonction MOYENNE par exemple de calculer la moyenne d une plage contenant du texte 11 2 2 MISE EN OEUVRE Pour les fonctions les plus courantes comme SOMME ou MOYENNE il est plus rapide d crire directement le nom de la fonction suivi de ses arguments Pour les fonctions d emploi plus exceptionnel il est plus s r de passer par Assistant fonction Celui ci est activ soit par Insertion Fonction partir du menu principal soit par le bouton fr s il est pr sent sur la barre d outils A partir de l l assistant fonction propose dans une premi re bo te de dialogue la liste des fonctions dispo
43. ns une cellule n emp chera pas toutes les d cimales disponibles mais non visibles d tre utilis es dans un calcul qui ref rera au contenu de cette cellule Pour une mise en forme courante les boutons suivants s av rent les plus rapides El format mon taire 5237 devient 5 237 00 F format pourcentage 0 25 devient 25 00 format avec espace entre les milliers 5237 devient 5 237 00 0 uo 2 augmente le nombre de d cimales 00 diminue le nombre de d cimales UNIVERSITE DE RENNES 2 Jacques CELLIER Principes et m thodes de base Les formats plus sophistiqu s requi rent le passage par Format Cellule Nombre qui ouvre une bo te de dialogue proposant des mod les plus labor s regroup s par cat gorie et d crits par des codes d affichage A signaler entre autres v les formats mon taires avec affichage des nombres n gatifs en rouge v les multiples formats d affichage des dates et des heures v le format scientifique v le format fraction Il est par ailleurs possible de cr er ses propres formats Le plus simple pour y parvenir est de modifier dans la case code un format d j existant Par exemple partir du mod le 0 00 on ajoute par exemple droite Km pour obtenir l affichage de l unit de mesure Deux remarques pour clore ce sujet Avec Excel le pourcentage est un format d affichage et non une multiplication par 100 de la valeur 0 5 50 ou 2 sont des variantes d criture du m
44. omme une date Pour viter ce genre d inconv nient commencer la saisie par une apostrophe ce qui force Excel consid rer ce qui va suivre comme du texte et l afficher tel quel 3 Des formules de calcul pouvant faire r f rence d autres cellules Ces formules de calcul doivent obligatoirement commencer par le signe qui est la marque pour Excel de l introduction d une formule Si on tape par exemple 5 2 Excel consid rera qu il s agit de texte et affichera dans la cellule 5 2 si l on tape 5 2 Excel interpr tera cela comme une formule qu il valuera avant d afficher le r sultat 7 dans la cellule UNIVERSITE DE RENNES 2 Jacques CELLIER Principes et m thodes de base Une fois saisie l information est valid e par la touche ou en changeant de cellule active Si l on souhaite corriger une information d j saisie on doit alors rendre la cellule la contenant active puis cliquer a la souris dans la barre de formule les touches de d placement horizontaux permettent alors de se d placer dans l expression pour proc der aux corrections Les deux boutons de la barre de formule permettent respectivement d annuler une entr e ou de la valider 1 2 3 FORMULES DE CALCUL C est bien vident cette troisi me possibilit qui fait la sp cificit des tableurs en g n ral et d Excel en particulier Une formule Excel est constitu e de symboles op ratoires usuels l vation a une puissa
45. ou plusieurs autres feuilles du m me classeur Le principe de la notation consiste faire pr c der la r f rence absolue ou relative de la cellule ou du bloc de cellules par le nom de la feuille suivi d un point d exclamation Mais comme pour les r f rences pr c dentes la saisie des formules elles peuvent tre obtenues automatiquement en cliquant et glissant ventuellement sur les cellules concern es apr s avoir cliqu sur l onglet de la feuille o elles se situent Exemples JANVIERIL 1 C La cellule situ e une ligne avant sur la m me colonne dans la feuille nomm e JANVIER JANVIER JUINIL1C2 Toutes les cellules L1C2 de toutes les feuilles depuis la feuille nomm e JANVIER jusqu celle nomm e JUIN dans l ordre des onglets JANVIERIL1C1 L4C4 Le bloc L1C1 L4C4 de la feuille nomm e JANVIER JANVIER JUIN IL1C1 L4C4 Tous les blocs L1C2 de toutes les feuilles depuis la feuille nomm e JANVIER jusqu celle nomm e JUIN dans l ordre des onglets 11 1 5 NOMS DE CELLULES Excel permet l utilisateur de nommer sa convenance cellules ou blocs de cellules Ces noms reconnus et utilisables dans tout le classeur renvoient aux r f rences absolues du bloc Ils facilitent l criture des formules et en am liorent la lisibilit Il ne faut cependant pas confondre le fait de nommer des cellules avec l insertion de titres ou libell s dans la feuille ces derniers n ayant qu un r le documentaire alor
46. ourci de la barre d outil format comme par exemple ho I EEEE dont les r les se devinent ais ment Les outils disponibles quivalent de ceux dont on dispose en traitement de texte concernent v le type police taille etc des caract res employ es A signaler que a l encontre de son pr d cesseur Excel 4 la version 5 autorise diff rents types de caract re au sein d une m me cellule v l alignement dans les cellules centr a droite gauche justifi horizontalement ou verticalement Excel permet d crire un texte sur plusieurs lignes au sein d une cellule Normalement si la longueur du texte d passe la largeur de la cellule Excel d borde sur les cellules voisines si celles ci sont vierges sinon le texte est tronqu l affichage En activant via Format Cellule Alignement l option Renvoi la ligne automatique Excel affiche le texte complet sur plusieurs lignes si n cessaire Ce renvoi la ligne peut d ailleurs tre impos lors de la saisie par la combinaison de touche Alt y La couleur Motifs et l encadrement Bordure y Le centrage du texte sur plusieurs colonnes Format nombre Excel ayant une vocation prononc e pour le calcul il est logique de trouver des outils sp cifiques de mise en forme des nombres Ces outils n affectent que l affichage des nombres l cran et a l impression mais ne modifient en rien la pr cision des calculs Afficher x avec deux d cimales da
47. r s rapidement r duire le choix r el A l exception du type Surface 3D apte a repr senter une fonction de deux variables tous les types 3D sont non seulement inutiles mais nuisibles Induisant artificiellement des effets de perspective parasites ils privil gient arbitrairement la s rie en premier plan aux d triments des autres souvent masqu es On obtient ainsi des vues d avion de Manhattan spectaculaire a souhait mais absolument aucun graphique pertinent Le type dit Histogrammes est abusivement nomm Il s agit en fait d un diagramme en batons comme son cousin Barres la diff rence r sidant en ce que l un UNIVERSITE DE RENNES 2 Jacques CELLIER 24 Ill Graphiques donne des barres verticales tandis que l autre donne des barres horizontales Mais ni l un ni l autre ne sont de v ritables histogrammes Les types Courbes et Aires sont des variantes de pr sentation de ce type en donnant un trac continu avec superposition dans le deuxi me cas Le type Secteur fournit une repr sentation classique en camembert l instar du type Anneau qui n apporte rien d autre qu un trou au milieu Le type Radar est adapt des repr sentations graphiques dont l abscisse est une grandeur angulaire comme par exemple une rose des vents Le type XY Nuages de points est lui encore bizarrement nomm Sa v ritable caract ristique est d tre en fait
48. s d pendants autrement dit les cellules qui font r f rences la cellule active UNIVERSITE DE RENNES 2 Jacques CELLIER 14 Il R f rences et fonctions 11 1 2 EXEMPLES D UTILISATION La diff rence entre ces types de r f rence ne prend sa v ritable signification que dans le cadre de recopie de la formule vers d autre cellule Exemple 1 On souhaite calculer la r partition en pourcentage des ressources de la r gion Bretagne este SE SE 1 IMPOTS mE 2 TRANSFERTS Es cen JL 7 1 1 1 fe Ig 4 C 1 yrs 3254 VL 4 C 1 Eee ss A mn Figure 2 r f rences relatives Sur la premi re ligne le pourcentage des imp ts dans les recettes sera calcul correctement mais la recopie vers le bas de cette formule provoquera l affichage du message d erreur DIV 0 Logique car dans les formules recopi es le d nominateur pointe non sur la cellule qui contient le total 20841 mais sur les cellules vides situ es au dessous Il faut donc dans la formule initiale mettre le d nominateur en r f rences absolues ors SS 1 IMP TS DE C1yL5C2 2 TRANSFERTS 4369 C 1 L5C2 3 PRODUITS 7294 ZLC 4 AUTRES 5299 Fa C 1 5 TOTAL 20841 O Figure 3 r f rences relatives et absolues Exemple 2 On souhaite calculer dans un tableau le prix r duit de diff rents articles avec diff rents taux de r duction Dans la cellule L2C2 ou sera plac e la premi re formule la r f ren
49. s objets divers et vari s au sein du document Format regroupe les outils de mise en forme Outils regroupe les outils sp ciaux Audit Solveur etc Donn es regroupe les outils sp cifiques la gestion de donn es Tri etc Fen tre regroupe les commandes pour basculer d un document l autre ouvrir plusieurs fen tres sur une m me feuille ou des feuilles diff rentes 2 ouvre l acc s l aide en ligne BARRE D OUTILS Situ e en dessous de la barre des menus la ou les barres d outils fournissent des boutons de raccourci pour activer les diff rentes commandes et sont enti rement personnalisables Un bon choix consiste via le menu Affichage Barre d outils de choisir la barre d outils standard et la barre d outils format puis en fonction du type d application que l on envisage et de ses habitudes de travail d enlever les boutons peu utilis s et d y inclure les boutons correspondant des commandes fr quemment sollicit es On peut par ailleurs faire appara tre momentan ment des barres d outils flottantes utilisables en fonction du contexte de travail graphique dessin gestion de donn es En laissant une ou deux fraction de seconde le pointeur de la souris sur un de ces boutons on provoque l apparition d une bulle explicative quant sa finalit MENUS CONTEXTUELS Par rapport la version pr c dente Excel 5 d veloppe l usage du bouton droit de la souris suivant le principe
50. s que les noms de cellules ont un v ritable r le fonctionnel dans l criture des formules Le logiciel pr te quelque peu confusion en proposant UNIVERSITE DE RENNES 2 Jacques CELLIER 16 Il R f rences et fonctions comme nom par d faut le titre du bloc que l on souhaite nommer s il en d tecte un dans la feuille mais rien oblige accepter cette suggestion Pour nommer une cellule ou un bloc on le s lectionne puis on utilise Insertion Nom D finir pour ouvrir la bo te de dialogue qui permet d attribuer le nom de son choix au bloc s lectionn Les espaces l int rieur du nom ne sont pas accept es les caract res accentu es sont d conseill s et Excel ne distingue pas majuscules et minuscules Plus rapidement on peut utiliser la liste d roulante situ e gauche dans la barre de formule pour donner le nom apr s s lection du bloc ES EE D E RE RE EH JULES Figure 5 nommer un bloc Cette m me liste d roulante permet de visualiser les noms d finis dans le classeur Elle sert de plus lors de l criture d une formule ins rer un nom en le cliquant dans la liste 11 1 6 ITERATIONS Lorsqu une cellule X fait r f rence une cellule Y qui elle m me fait r f rence X cela s appelle un cercle vicieux et Excel d livre le message d erreur R f rences circulaires non r solues Mais il est possible de transformer ce cercle vicieux en spirale vertueuse Cette m
51. sertion de barres d erreurs UNIVERSITE DE RENNES 2 Jacques CELLIER 31 Ill Graphiques 111 3 7 DEUX SERIES D ABSCISSE Dans tous les cas voqu s pr c demment la s rie des abscisses tait unique et donc la m me pour les diff rentes s ries de valeurs repr sent es en Y Il est cependant possible de repr senter sur un m me graphique deux ou plus courbes avec une s rie d abscisses diff rente pour chacune d entre elles Voici la marche suivre avec comme exemple les donn es 1 S lectionner les deux premi res lignes et proc der normalement pour obtenir une repr sentation en XY de la premi re courbe 2 Le graphique tant ouvert utiliser la commande Insertion Nouvelle donn e et dans la bo te de dialogue pour d finir la Plage cliquer et glisser sur la ligne Y2 10 Excel convertira en r f rences On obtient ainsi une deuxi me courbe mais dont les abscisses sont les valeurs X1 et non les valeurs X2 comme il le faudrait 3 Cliquer alors avec le bouton droit de la souris sur cette deuxi me courbe et choisir dans le menu contextuel Format s rie de donn es puis dans la bo te de dialogue l onglet Valeurs X Effacer le contenu de la zone de saisie Valeurs X et cliquer et glisser sur la ligne X2 de la feuille sur la zone des valeurs 2 10 cellule contenant le libell X2 exclu et valider par OK On obtient 20 15 10 o Y1 5 a Y2 0 2 4 6 8 10 Figure 19
52. uent la conversion inverse en retournant le HEURE H M S num ro de s rie correspondant l ann e A mois M jour J pour l une et l heure H minute M seconde S pour l autre 11 2 4 FONCTIONS MATRICIELLES Alors que les fonctions ordinaires retournent un r sultat unique dans une cellule les fonctions qualifi es de matricielles retournent plusieurs r sultat dans un bloc de cellules Leur mode d emploi diff re en ceci y Il faut pr alablement s lectionner le bloc de cellules devant accueillir les r sultats v La fonction une fois crite doit tre valid e par la combinaison de touches Ctrl 2 l v Une fois valid es correctement elles apparaissent entre accolades dans la barre de formule v Il n est pas possible de modifier isolement une cellule du bloc En voici quelques exemples Fonction FREQUENCE DONNEES LIMITES Cette fonction mal nomm e puisqu elle calcul des effectifs et non des fr quences s av re n anmoins fort utile Elle permet partir d un bloc de donn es de calculer la r partition par classes limit es par des valeurs x x que l on choisit sa convenance En supposant le bloc de donn es nomm DONNEES on dispose les limites de classe en colonne on les nomme LIMITES puis apr s s lection sur la colonne adjacente d un nombre de cellules gal au nombre de limites plus 1 en gris sur la figure on crit la formule et on valide avec la combinaison de touches
53. voquant une mise jour des r sultats subordonn s 1 2 4 ERREURS Des formules erron es peuvent provoquer dans la cellule o elles ont t plac es l affichage de messages d erreur au lieu du r sultat escompt Voici les principaux et leur signification O1 R D DIV 0 Division par 0 1 Les fonctions d Excel sont d velopp es au chapitre suivant Les r f rences et les subtilit s que leur maniement peut receler sont claircies au d but du prochain chapitre 3 Ce processus de recalcul automatique peut tre gel par Outils Option Calcul en cochant calcul sur ordre UNIVERSITE DE RENNES 2 Jacques CELLIER Principes et m thodes de base NOM Excel ne reconnait pas un des noms de fonction ou de bloc utilis dans la formule NOMBRE Utilisation d un argument incorrect dans une formule num rique comme par exemple 1 avec la fonction RACINE VALEUR Utilisation d un type d argument incorrect comme du texte dans une formule num rique REF R f rences incorrectes dans une formule HR Ne signale pas une erreur proprement parler mais indique qu Excel manque de place dans la cellule pour afficher un nombre avec la pr cision requise 1 2 5 MISE EN FORME ET PRESENTATION Largeur de colonnes hauteur de lignes Le moyen le plus rapide pour redimensionner lignes et colonnes consiste d placer la souris les traits s parant les num ros sur les bords de la feuille Si l on souhaite

NOTIONS DE BASE - Jacques Cellier

Contents

Download Pdf Manuals

Related Search

Related Contents