séance 4 modéliser à l’aide d’un tableur modéliser à l'aide d'un tableur (4) 1

Click here to load reader

Upload: ozanne-andrieu

Post on 04-Apr-2015

115 views

Category:

Documents


1 download

TRANSCRIPT

  • Page 1
  • Sance 4 Modliser laide dun tableur Modliser l'aide d'un tableur (4) 1
  • Page 2
  • Sance 4 Quelques fonctions Gnralistes (dates, textes) et mtier Outils manipulation de listes : classer, filtrer, sous-totaux, plan mise en forme : manuelle, conditionnelle, styles synthses : consolidation, analyse croise diagrammes simulations : valeur cible, scnario, tables de donnes, solveur Importation/Exportation Sources de donnes externes Publication Audit des feuilles de calcul Macros Modliser l'aide d'un tableur (4) 2
  • Page 3
  • quelques fonctions Modliser l'aide d'un tableur (4) 3
  • Page 4
  • quelques fonctions Dates ANNEE, MOIS, JOUR : anne, mois, jour du mois dune date JOURSEM : jour de la semaine dune date AUJOURDHUI : date du jour MAINTENANT : date et heure du jour DATE : construire une date avec une anne, un mois et un jour FIN.MOIS : date de fin de mois partir dune date et un nombre de mois de dcalage MOIS.DECALER : date partir dune date plus un nombre de mois de dcalage NO.SEMAINE : numro de la semaine dune date (2 modes) JOURS.OUVRES : entre 2 dates, en tenant compte de jours fris Modliser l'aide d'un tableur (4) 4
  • Page 5
  • quelques fonctions Textes MINUSCULE, MAJUSCULE, NOMPROPRE : conversions minuscule/majuscules ou 1 re lettres en majuscules CHERCHE, TROUVE: position dune chane dans une sous-chane (le 1 er insensible la casse, le 2 nd sensible la casse) GAUCHE, DROITE, STXT : rcuprer un certain nombre de caractres partir de la gauche, de la droite ou dune position prcise dans une chane REPT : rpter un texte un certain nombre de fois CONCATENER : mettre plusieurs chaines bout bout (comme oprateur &) CNUM : convertir une chaine en nombre CTXT : convertir un texte en nombre (arrondi, mise en forme) Modliser l'aide d'un tableur (4) 5
  • Page 6
  • quelques fonctions Fonctions mtier Comptabilit, Finance (cf.) AMORLIN : calcul damortissement linaire VPM : ex. calculer le montant de chaque mensualit dun emprunt VC : ex. calculer la valeur capitalise dun placement Statistiques FREQUENCE : frquence de valeurs dans des plages de valeurs ECARTYPE, VAR :cart-type et variance RANG : rang dune valeur dans une liste de valeurs Mathmatiques, en vrac SIN, COS, LOG, RACINE, ABS, ENT, FACT, PLAFOND, PLANCHER, SIGNE, ROMAIN... Ingnierie : COMPLEXE, BESSELI... Modliser l'aide d'un tableur (4) 6
  • Page 7
  • quelques fonctions autres HYPERLIEN : crer un hyperlien vers un autre endroit dun classeur ou vers lextrieur (autre fichier, internet) ARRONDI, ARRONDI.INF, ARRONDI.SUP : arrondi commercial, arrondi infrieur, arrondi suprieur CONVERT : conversion entre units de mesure SOMME.SI.ENS, MOYENNE.SI.ENS : somme et moyenne dune plage avec possibilit de dfinir plusieurs critres Laide intgre au logiciel ou aux sites Internet des diteurs fournit des informations sur toutes les fonctions Modliser l'aide d'un tableur (4) 7
  • Page 8
  • Configuration de laffichage les volets : figer et librer Modliser l'aide d'un tableur (4) 8
  • Page 9
  • Options daffichage dune feuille les Volets : figer / librer Modliser l'aide d'un tableur (4) 9 Objectif : se dplacer horizontalement et verticalement dans une feuille tout en conservant affiches certaines colonnes et/ou lignes Menu : Affichage > Figer les volets
  • Page 10
  • Options daffichage dune feuille les Volets : figer / librer Modliser l'aide d'un tableur (4) 10 Figer 2 colonnes Figer 2 lignes slectionner une colonne, puis figer les volets Figer 2 lignes et 2 colonnes slectionner une ligne, puis figer les volets slectionner une cellule, puis figer les volets
  • Page 11
  • Options daffichage dune feuille les Volets : figer / librer Modliser l'aide d'un tableur (4) 11 barre de dfilement vertical barre de dfilement horizontal { restent affiches }
  • Page 12
  • Options daffichage dune feuille les Volets : figer / librer Modliser l'aide d'un tableur (4) 12 Menu : Affichage > Figer les volets Pour annuler les volets figs dans la feuille
  • Page 13
  • Outils de manipulation de listes Modliser l'aide d'un tableur (4) 13
  • Page 14
  • Outils de manipulation de listes Les listes de donnes sont formes par des tableaux rectangulaires dont la premire ligne comporte des enttes de colonnes (elles sont utilisables en tant que bases de donnes). La liste est gnralement utilise dans sa totalit (toutes les lignes et toutes les colonnes) pour tre trie, filtre, ou se voir ajouter des sous- totaux. Des regroupements de lignes/colonnes permettent de construire des plans quon peut rduire ou dvelopper pour masquer ou afficher le dtail. ATTENTION : la manipulation dune partie dune liste peut avoir de graves consquences sur lagencement du contenu des donnes et crer des incohrences non rcuprables. Modliser l'aide d'un tableur (4) 14
  • Page 15
  • Outils de manipulation de listes Trier 1/2 Le tri (ou classement) permet dafficher les lignes dune liste dans un ordre diffrent Le tri peut utiliser la valeur dune seule colonne ou bien associer plusieurs colonnes Les critres de tri sont Le tri croissant (A Z, du plus petit au plus grand) Le tri dcroissant (Z A) du plus grand au plus petit) Dans le cas dun tri mettant en jeu les valeurs de plusieurs colonnes, le tri sapplique dabord avec la premire colonne spcifie, puis la suivante, etc. Modliser l'aide d'un tableur (4) 15
  • Page 16
  • Outils de manipulation de listes Trier 2/2 Sur Excel : Donnes > Trier Modliser l'aide d'un tableur (4) 16
  • Page 17
  • Outils de manipulation de listes Filtrer 1/5 Le filtre (ou slection) permet de restreindre le nombre de lignes affiches en appliquant des critres de slection aux valeurs de colonnes (les lignes ne rpondant pas aux critres sont simplement masques) Les critres de filtre utilisent des oprateurs de comparaison classique appliqus essentiellement aux valeurs numriques: gal, diffrent, infrieur, infrieur ou gal, suprieur, suprieur ou gal, dans un intervalle de valeurs Dans les 10 premires valeurs Au dessus, au dessous de la moyenne Et des filtres qui sappliquent plutt aux textes : Dbute par, contient, se termine par, gal, diffrent Modliser l'aide d'un tableur (4) 17
  • Page 18
  • Outils de manipulation de listes Filtrer automatique 2/5 Sur Excel : Donnes > Trier et filtrer > Filtrer : un critre de slection peut tre prcis pour chaque colonne Modliser l'aide d'un tableur (4) 18 Chaque critre ajout vient restreindre davantage le nombre de lignes affiches
  • Page 19
  • Outils de manipulation de listes Filtrer automatique 3/5 La liste est filtre ( sur place ) : les lignes qui ne rpondent pas aux critres sont masques Pour revenir la liste complte : Modliser l'aide d'un tableur (4) 19
  • Page 20
  • Outils de manipulation de listes Filtrer avanc 4/5 Sur Excel : Donnes > Trier et filtrer > Avanc : davantages de possibilits grce une zone de critres dfinissant des oprateurs de comparaison et des oprateurs logiques et et ou Modliser l'aide d'un tableur (4) 20 Liste des critres ET OU
  • Page 21
  • Outils de manipulation de listes Filtrer avanc 5/5 La liste est filtre ( sur place ) : les lignes qui ne rpondent pas aux critres sont masques Pour revenir la liste complte : Modliser l'aide d'un tableur (4) 21
  • Page 22
  • Outils de manipulation de listes Ajouter des sous-totaux 1/3 Les sous-totaux sont des lignes de synthse calcules par regroupement de valeurs et ajoutes chaque changement de valeur des colonnes de regroupement Modliser l'aide d'un tableur (4) 22 La liste est trie par rfrence chaque changement de valeur de rfrence, on souhaite ajouter un sous-total somme de montant
  • Page 23
  • Outils de manipulation de listes Ajouter des sous-totaux 2/3 Sur Excel : Donnes > Plan > Sous-total Modliser l'aide d'un tableur (4) 23 chaque changement de rfrence, on ajoute une somme des montants, sous les donnes, en remplacer les sous- totaux existants chaque changement de rfrence produit, on ajoute une somme de montant, sous les donnes, en remplacer les sous- totaux existants
  • Page 24
  • Outils de manipulation de listes Ajouter des sous-totaux 3/3 Les sous-totaux sont ajouts, ainsi que niveau de plan Modliser l'aide d'un tableur (4) 24 sous-totaux Niveaux de plan sous-totaux Niveaux de plan
  • Page 25
  • Outils de manipulation de listes Regrouper (plans) Les plans offrent la possibilit de dfinir des groupes de lignes/colonnes rduire (-) ou dvelopper (+)) Les sous-totaux ajoutent automatiquement des regroupements Modliser l'aide d'un tableur (4) 25
  • Page 26
  • Outils de manipulation de listes Regrouper (plans) Sur Excel : slectionner les lignes ou colonnes, puis Donnes > Plan > Grouper ou Dissocier ou Supprimer le plan Modliser l'aide d'un tableur (4) 26 rduire dvelopper Niveaux de plan (regroupements imbriqus)
  • Page 27
  • Outils de mise en forme Modliser l'aide d'un tableur (4) 27
  • Page 28
  • Outils de mise en forme Les tableurs proposent toute une panoplie de paramtres permettant la configuration manuelle de laspect dun tableau dans une feuille de calcul (cf. sance 1) Afin dassurer une cohrence globale de laspect des tableaux dun classeur, la notion de style offre la possibilit dattribuer un nom un groupe de proprits de mise en forme, et dappliquer ce style un ensemble de cellules Un outil de mise en forme conditionnelle permet la mise en forme automatique de cellules en fonction de leur valeur Modliser l'aide d'un tableur (4) 28
  • Page 29
  • Outils de mise en forme manuelle La mise en forme manuelle est ralise en utilisant les icnes et options des fentres de mise en forme de cellules. Deux outils permettent la reproduction dune mise en forme dj ralise sur une cellule : Reproduire la mise en forme : Slectionner une cellule, cliquer sur licne (le pointeur de la souris devient un pot de peinture), appliquer en faisant glisser sur les cellules cibles Copier/Collage spcial : Coller le format Slectionner une cellule, Copier, Coller > Collage spcial, slectionner Format Modliser l'aide d'un tableur (4) 29
  • Page 30
  • Outils de mise en forme Styles de cellules 1/2 Un style porte un nom et regroupe un ensemble de proprits de mise en forme de cellule. Certains styles sont prdfinis mais leurs proprits peuvent tre modifies; des styles personnaliss peuvent tre crs pour rpondre des besoins mise en forme spcifique Un style est appliqu un ensemble de cellules ; la modification de ses proprits est propage aux cellules auxquelles il est appliqu Il permet dassurer une cohrence de mise en forme sur lensemble des feuilles dun classeur De plus, le nom de style peut tre un lment de documentation dans un classeur Modliser l'aide d'un tableur (4) 30
  • Page 31
  • Outils de mise en forme Styles de cellules 2/2 Sous Excel : Accueil > Style (slection et clic-droit modifier) Modliser l'aide d'un tableur (4) 31 Accs au dtail de la mise en forme Slection des lments intgrs dans le style (dautres proprits pourront tre dfinies spcifiquement pour certaines cellules)
  • Page 32
  • Outils de mise en forme Mise en forme conditionnelle La mise en forme conditionnelle met en forme des cellules automatiquement en fonction de critres appliqus leur contenu Sous Excel : Accueil > Style > Mise en forme conditionnelle Modliser l'aide d'un tableur (4) 32
  • Page 33
  • Outils de synthse Modliser l'aide d'un tableur (4) 33
  • Page 34
  • Outils de synthse La gestion des volumes importants de donnes sous forme de listes ou tableaux, ou bien rpartis sur plusieurs feuilles (ou mme plusieurs classeurs), ne permet pas davoir une vue synthtique sur cet ensemble de donnes Deux outils sont offerts par les tableurs Un outil de consolidation de donnes provenant de plusieurs feuilles Un outil de construction danalyse croise (rapport de tableau croise dynamique, analyse multidimensionnelle) Modliser l'aide d'un tableur (4) 34
  • Page 35
  • Outils de synthse Consolider 1/3 La consolidation permet la cration dune feuille de synthse de rsultats rpartis sur plusieurs autres feuilles. Par exemple : 12 feuilles de CA mensuel Les consolider pour obtenir le CA annuel Plusieurs types de consolidation sont disponibles Par position : les feuilles sont toutes organises de la mme manire Par catgories : des tiquettes de lignes et colonnes vont permettre la consolidation des donnes La saisie de formules sappliquant une plage de feuilles Modliser l'aide d'un tableur (4) 35
  • Page 36
  • Outils de synthse Consolider - 2/3 Sur Excel : Donnes > Consolider Modliser l'aide d'un tableur (4) 36
  • Page 37
  • Outils de synthse Consolider - 3/3 Exemple sur 3 feuilles : Modliser l'aide d'un tableur (4) 37 janvierfvriermars consolidation
  • Page 38
  • Outils de synthse Analyse croise 1/5 Un rapport de tableau crois dynamique offre un PUISSANT moyen de synthtiser rapidement de grandes quantits de donnes, selon plusieurs axes danalyse, sous forme dun tableau double entre (lignes et colonnes) Il est utilis pour analyser en profondeur des donnes numriques et rpondre des questions imprvues Trois dimensions danalyse sont gnralement dfinies : Axe temporel : mois, anne, trimestre, etc. Axe produit : catgorie de produits, rfrence de produits Axe client : marchs, rgion, pays, etc. Lanalyse porte sur des mesures : Nombre, quantits, valeurs, etc. Modliser l'aide d'un tableur (4) 38
  • Page 39
  • Outils de synthse Analyse croise 2/5 Sur Excel : Insertion > Tableau > Tableau crois dynamique Modliser l'aide d'un tableur (4) 39 Plage de cellules analyse
  • Page 40
  • Outils de synthse Analyse croise 3/5 Dfinir les diffrents axes danalyse et les mesures analyser Modliser l'aide d'un tableur (4) 40 Filtre global par pays Les mois en colonnes Les catgories en lignes Calcul de la somme des montants
  • Page 41
  • Outils de synthse Analyse croise 4/5 La construction du tableau est immdiate : les axes peuvent tre interchangs par simple glisser/dposer Modliser l'aide d'un tableur (4) 41
  • Page 42
  • Outils de synthse Analyse croise 5/5 Pour analyser en profondeur une donne (ici CA faible, quels clients sont concerns ?), double-cliquer sur la cellule ouvre une nouvelle feuille avec le dtail des donnes sources correspondant cette valeur : Modliser l'aide d'un tableur (4) 42
  • Page 43
  • Diagrammes Modliser l'aide d'un tableur (4) 43
  • Page 44
  • Diagrammes Les diagrammes illustrent de manire visuelle des ensembles de donnes De nombreuses formes de diagrammes sont proposes, certaines formes tant plus adaptes reprsenter une volution ou une rpartition Des courbes de tendances peuvent tre calcules et ajoutes automatiquement aux diagrammes (et leurs fonctions mathmatiques affiches) Modliser l'aide d'un tableur (4) 44 un graphique vaut mille mots on peut faire dire nimporte quoi des chiffres !
  • Page 45
  • Diagrammes Types de diagrammes Les histogrammes illustrent une volution dans le temps (volution du CA mensuel, volution du CA annuel) ou une comparaison de diffrents lments (les clients avec leur CA, les produit avec le CA associ) Les courbes illustrent : Une volution comparative de diffrents lments (volution du CA annuel France et Export) Diagrammes en secteur Une comparaison en proportion de diffrents lments (parts en pourcentage du CA France et Export) Modliser l'aide d'un tableur (4) 45
  • Page 46
  • Diagrammes Types de diagrammes Les diagrammes en barres illustrent une comparaison de diffrents lments (les clients avec leur CA, les produit avec le CA associ) avec les tiquettes longues Les diagrammes en aire illustrent : Une amplitude de variation de diffrents lments sur une priode (volution des CA annuels France et Export cumuls) Les nuages de points illustrent Une comparaison des distributions de valeurs entre diffrents lments (parts en pourcentage du CA France et Export) Autres : radar, bulles, anneaux, surface, boursiers (cf. aide) Modliser l'aide d'un tableur (4) 46
  • Page 47
  • Diagrammes Sous Excel : Insertion > Graphiques Pour un type de diagramme : Dfinir la plage de donnes (sries de donnes) Dfinir les noms des sries Dfinir les tiquettes associes aux donnes Modliser l'aide d'un tableur (4) 47
  • Page 48
  • Outils de simulation Modliser l'aide d'un tableur (4) 48
  • Page 49
  • Outils de simulation Les donnes utilises dans les formules de calcul sont gnralement saisies ou obtenues dune source extrieure de donnes : des formules de calcul utilisent ces donnes pour produire les rsultats Les outils de simulation permettent De connatre lincidence de diffrentes valeurs de cellules sur le rsultat dun calcul ( what if ? ) Ou au contraire la dcouverte des valeurs qui permettent lobtention dun rsultat cibl (maximiser, minimiser, atteindre un certain rsultat) Modliser l'aide d'un tableur (4) 49
  • Page 50
  • Outils de simulation Valeur cible 1/2 Loutil valeur cible permet la recherche dun rsultat dtermin (valeur cible) dans une cellule calcule en ajustant la valeur dune des cellules utilises (directement ou pas) dans la formule de calcul. Modliser l'aide d'un tableur (4) 50 Quelle est la valeur de la moyenne dexpression franaise pour atteindre 10 en moyenne gnrale ?
  • Page 51
  • Outils de simulation Valeur cible 2/2 Avec Excel : Donnes > Analyse de scnarios > Valeur cible Modliser l'aide d'un tableur (4) 51 Une moyenne de 9 en expression franaise permet dobtenir une moyenne de 10 La moyenne de 9 en Exp.Fr. nous permet de cibler une moyenne gnrale de 10
  • Page 52
  • Outils de simulation Scnario 1/4 Loutil scnario permet dtablir des jeux de valeurs diffrentes pour des cellules utilises dans des formules (chaque jeu constitue un scnario), de tester ces jeux afin den valuer limpact Modliser l'aide d'un tableur (4) 52 Lobjectif est le test de plusieurs jeux de notes afin dvaluer leffet sur la moyenne
  • Page 53
  • Outils de simulation Scnario 2/4 Avec Excel : Donnes > Analyse de scnarios > Gestionnaire de scnarios Ajouter un scnario Optimiste pour les notes dExpression franaise, dAnglais et dEconomie Puis un scnario Pessimiste pour ces mmes matires avec les notes 6, 9 et 6. Modliser l'aide d'un tableur (4) 53
  • Page 54
  • Outils de simulation Scnario 3/4 Il est maintenant possible dAfficher limpact dun scnario Modliser l'aide d'un tableur (4) 54 Impact sur la moyenne Fermer et annuler pour revenir aux valeurs initiales
  • Page 55
  • Outils de simulation Scnario 4/4 Il est galement possible dobtenir une synthse des impacts des diffrents scnarios, Sous une forme fige (non actualisable) Ou sous forme dun tableau crois dynamique Modliser l'aide d'un tableur (4) 55
  • Page 56
  • Outils de simulation Table de donnes 1/4 Loutil Table de donnes permet de lister les diffrents rsultats dune formule en fonction des valeurs dune ou deux variables de la formule Modliser l'aide d'un tableur (4) 56 Quel seraient les diffrentes mensualits avec les taux 4%, 4.25%, 4.5%, 4.75% et 5% ?
  • Page 57
  • Outils de simulation Table de donnes 2/4 Avec Excel : slection de la plage de simulation constituer, puis Donnes > Analyse de scnarios > Tables de donnes Modliser l'aide d'un tableur (4) 57 Valeur laquelle se substituent les diffrentes valeurs tester en colonne (ici le taux dintrt) Rfrence la formule =E5
  • Page 58
  • Outils de simulation Table de donnes 3/4 Une 2 me variable peut-tre dfinie pour constituer une matrice rsultat : Modliser l'aide d'un tableur (4) 58 Valeur laquelle se substituent les diffrentes valeurs tester en colonne (ici le taux dintrt) Valeur laquelle se substituent les diffrentes valeurs tester en ligne (ici la dure)
  • Page 59
  • Outils de simulation Table de donnes 4/4 Dans chacun des cas, les tables de donnes ont une structure dfinie : Modliser l'aide d'un tableur (4) 59 Les valeurs tester en colonne (ou bien ligne) La formule ou une rfrence la formule Les rsultats La formule ou une rfrence la formule Les valeurs tester en colonne Les valeurs tester en ligne Les rsultats Une liste de valeur s en ligne o en colonne Deux listes de valeur s en ligne et en colonne
  • Page 60
  • Outils de simulation Solveur 1/9 Loutil solveur permet de faire converger le rsultat dun calcul vers une valeur maximale, minimale ou fixe, en modifiant automatiquement certaines valeurs fixes et en dfinissant des contraintes encadrant certaines valeurs calcules du modle Loutil Solveur utilise les lments suivants : La rfrence de la cellule contenant la valeur maximiser ou minimiser ou rapprocher dune valeur cible (cette cellule contient une formule) Les cellules modifier (contiennent des valeurs fixes) Des contraintes qui sont imposes certaines cellules (contiennent des formules) Des options lies la recherche du rsultat : temps maximal, prcision du calcul, etc. Modliser l'aide d'un tableur (4) 60
  • Page 61
  • Outils de simulation Solveur 2/9 Avec Excel : slection de la plage de simulation constituer, puis Donnes > Analyse de scnarios > Tables de donnes Si loutil Solveur nest pas disponible, vous lactiver Bouton Office > Options dExcel > Complments > Complment Solver Modliser l'aide d'un tableur (4) 61
  • Page 62
  • Outils de simulation Solveur 3/9 Exemple simple : cibler une valeur de marge Modliser l'aide d'un tableur (4) 62 Objectif : atteindre une marge de 800 000 Cellule variable : - la quantit, Les contraintes : -quantit