LE MANUEL IL A ÉTÉ CONÇU POUR RÉPONDRE À PLUSIEURS OBJECTIFS :
situer Excel dans le contexte plus général de la micro-informatique accompagner votre progression dans la connaissance de Excel vous indiquer pour chaque manipulation la procédure à suivre vous aider dans tous les travaux que vous pourrez être amenés à effectuer en tant que guide et conseil CE SUPPORT PRÉSENTE DIFFÉRENTES RUBRIQUES REPÉRÉES PAR UNE ICONE
DES PROCÉDURES DÉCRIVENT LA MARCHE À SUIVRE POUR EFFECTUER UNE ACTION PROCÉDURE
Des informations viennent compléter ces procédures INFORMATION
des exemples viennent illustrer ces procédures EXEMPLE
Des conseils vous aident à mettre en pratique vos connaissances CONSEIL
Des mises en garde vous permettent d'éviter les pièges ou d'en sortir ATTENTION
DES EXERCICES PERMETTENT DE TESTER LES PROCÉDURES ET DE METTRE VOS CONNAISSANCES À L'ÉPREUVE EXERCICE
Des procédures avancées décrivent des actions plus complexes AVANCÉ
des emplacements vous permettent de prendre des notes directement sur le support en bas de chaque page. NOTES
Éditions I O S
I. LE NOM DANS LE CLASSEUR Une plage, une constante, une formule peuvent recevoir un nom ; ce nom est très utile pour la manipulation de blocs de données et la bonne compréhension des tableaux.
A A.. C CR RÉ ÉE ER RU UN NN NO OM M Le plus souvent, le nom correspond aux titres de lignes ou de colonnes. NOMMER UNE PLAGE : SÉLECTIONNER L'ENSEMBLE DU TABLEAU (HORS TOTAUX)
PROCÉDURE
MENU INSERTION NOM
<OK> POUR VALIDER
B B.. A AP PP PLLIIQ QU UE ER RU UN NN NO OM MA AU UN NE E FFO OR RM MU ULLE E Les noms peuvent dés lors être utilisés dans des formules nouvellement créées (à partir de la barre de formule). Pour les formules existantes, il faut indiquer à Excel de les modifier. APPLIQUER : SÉLECTIONNER UNE CELLULE CONTENANT UNE
PROCÉDURE
FORMULE MENU INSERTION NOM
(LES NOMS SÉLECTIONNÉS) <OK> POUR VALIDER
OUVRIR LE CLASSEUR NOM NOMMER LES DONNÉES EN FONCTION DES TITRES DE LIGNES ET DE COLONNES APPLIQUER LES NOMS AUX FORMULES EXERCICE
NOTES
Éditions I O S
page 1
C C.. D DÉ ÉFFIIN NIIR RU UN NN NO OM MD DE EP PLLA AG GE E Un nom peut être donné à une plage directement par l'utilisateur et indépendamment du contexte. DÉFINIR LES NOMS : PROCÉDURE
SÉLECTIONNER LA PLAGE DÉROULER LA ZONE NOMS DE LA BARRE DE FORMULE SAISIR LE NOM
<ENTRÉE> POUR VALIDER
D D.. D DO ON NN NE ER RU UN NN NO OM MA AU UN NE EC CO ON NS STTA AN NTTE E Un nom et une valeur peuvent être donnés à une constante indépendamment des cellules de la feuille. Les constantes peuvent être utilisées dans des formules. Modifier la valeur de la constante met à jour le résultat de la formule. DONNER UN NOM A UNE CONSTANTE : PROCÉDURE
MENU INSERTION NOM DÉFINIR SAISIR LE NOM DE LA CONSTANTE SAISIR LA VALEUR <OK> POUR VALIDER
NOTES
page 2
reproduction réservée
Les noms de constante ne sont pas proposés dans la zone "nom" de la barre de formule INFORMATION
DANS LE CLASSEUR "NOMS", REGION "EST", CRÉER UN NOM "COEFF" (VALEUR :8%) CORRESPONDANT À UN COEFFICIENT PERMETTANT D'OBTENIR LES PRÉVISIONS 2006 ET CALCULER CES PRÉVISIONS
EXERCICE
E E.. D DO ON NN NE ER RU UN NN NO OM MA AU UN NE E FFO OR RM MU ULLE E Un nom peut être donné à une formule, ce qui peut être très utile pour les calculs de pourcentage par exemple. DONNER UN NOM A UNE FORMULE : PROCÉDURE
MENU INSERTION NOM DÉFINIR SAISIR LA FORMULE S'AIDER DE L'OUTIL <OK> POUR VALIDER
POUR DÉSIGNER LES PLAGES
le nom de la formule peut être utilisé dans d'autres formules INFORMATION
EXERCICE
DANS LE CLASSEUR "NOMS", ENREGISTRER SOUS LE NOM "SIGMA" LA SOMME DES DONNÉES DE TOUTES LES FEUILLES (3636) CALCULER EN A8 DE CHAQUE FEUILLE, LE POURCENTAGE REPRÉSENTÉ PAR CHAQUE RÉGION PAR RAPPORT À CE TOTAL
NOTES
Éditions I O S
page 3
FF.. D DO ON NN NE ER RU UN NN NO OM MS SP PÉ ÉC CIIFFIIQ QU UE EA AU UN NE E FFE EU UIILLLLE ED DE E TTR RA AV VA AIILL Les noms sont valables pour l'ensemble du classeur. S'ils sont créés à partir d'une plage 3 D, Excel sait affecter à chaque feuille les noms qui lui sont propres. Pour définir un nom de plage propre à une feuille, il suffit de le formuler de manière spécifique. DONNER UN NOM SPECIFIQUE A UNE FEUILLE : PROCÉDURE
MENU INSERTION NOM DÉFINIR SAISIR LE NOM
INDIQUER LES RÉFÉRENCES <OK> POUR VALIDER
INFORMATION
le nom doit être de la forme : nom de la feuille de travail entre guillemets simples : 'RÉGION EST' point d'exclamation : ! nom de la plage ou de la constante : COEFF
DANS LA FEUILLE "NOMS" SUPPRIMER LE "COEFF" CRÉÉ EN RÉGION EST CRÉER UN NOM UNIQUE : "COEFF" MAIS AVEC DES VALEURS DIFFÉRENTES POUR CHACUNE DES FEUILLES EXERCICE CALCULER LES PRÉVISIONS DE CHACUNE DES FEUILLES AVEC CE COEFFICIENT
G G.. LLE ES SR RÉ ÉFFÉ ÉR RE EN NC CE ES SE ETT LLE EN NO OM M Un nom est défini par défaut comme faisant référence à une plage absolue. Ainsi, lors de copie ou de déplacement, les données auxquelles il est fait référence sont à priori correctes. Dans certains cas, il peut être utile que le nom fasse référence à une plage relative. NOM DE PLAGE EN REFERENCE RELATIVE : PROCÉDURE
MENU INSERTION NOM DÉFINIR SAISIR LE NOM MODIFIER LES RÉFÉRENCES PROPOSÉES AVEC <F4> (après
sélection si c'est une plage)
<OK> POUR VALIDER
NOTES
page 4
reproduction réservée
II. LE PLAN Les tableaux présentant une certaine hiérarchisation des données avec des totaux intermédiaires de lignes ou de colonnes peuvent être avantageusement utilisés avec un mode plan. Ce mode issu du traitement de texte permet d'afficher ou/et d'imprimer des niveaux de structure intermédiaires afin de présenter des éléments détaillés ou au contraire plus généraux. La barre d'outils TABLEAUX CROISÉS DYNAMIQUES est particulièrement adaptée à l'utilisation du plan ; il est conseillé de l'afficher.
A A..
LLA AB BA AR RR RE ED D’’O OU UTTIILLS S TTA AB BLLE EA AU UX XC CR RO OIIS SE ES S
Cette barre d’outils est directement accessible par le menu contextuel des barres d’outils. BARRE D’OUTILS TABLEAUX CROISES : MENU CONTEXTUEL BARRE D’OUTILS PROCÉDURE
<CLIC G> SUR
B B.. C CR RÉ ÉE ER RU UN NP PLLA AN NA AU UTTO OM MA ATTIIQ QU UE E La création du plan est automatiquement effectuée par EXCEL en fonction de la disposition des données et des formules.
PROCÉDURE
CRÉER UN PLAN : <CLIC G> DANS LE TABLEAU MENU DONNÉES GROUPER ET CRÉER UN PLAN les symboles de plan apparaissent de part et d'autre du tableau PLAN.XLS
NOTES
Éditions I O S
page 5
INFORMATION
Si le tableau possède une structure simple, le plan automatique est le plus souvent correct. Si la structure du tableau est complexe, il faudra le plus souvent s’aider des outils de la barre TABLEAU CROISÉ DYNAMIQUE pour corriger le plan ; enfin, il est peut être dans certains cas plus rapide de procéder manuellement OUVRIR LE CLASSEUR "PLAN.XLS" CRÉER UN PLAN PERMETTANT DE MASQUER LES NIVEAUX INFÉRIEURS
EXERCICE
C C.. U UTTIILLIIS SE ER R LLE EP PLLA AN N Le plan va permettre de choisir le niveau d’agrégation des données à afficher, à représenter graphiquement ou à imprimer. OPTIONS : DÉVELOPPE (AFFICHE) LES NIVEAUX DE PLAN INFÉRIEURS RÉDUIT (MASQUE) LES NIVEAUX DE PLAN INFÉRIEURS AFFICHE LES 3 PREMIERS NIVEAUX DE PLAN AFFICHE LES 2 PREMIERS NIVEAUX DE PLAN AFFICHE LE PREMIER NIVEAU DE PLAN
NOTES
page 6
reproduction réservée
D D.. LLA AM MO OD DIIFFIIC CA ATTIIO ON ND DU UP PLLA AN N Si le plan proposé par EXCEL n'est pas parfaitement adapté aux exigences de l'utilisateur ; celui doit alors le remanier. BILAN1.XLS
MENU DONNÉES - GROUPER ET CRÉER UN PLAN RÉDUIT LE NIVEAU DE PLAN DES LIGNES OU COLONNES SÉLECTIONNÉES DÉVELOPPE LE NIVEAU DE PLAN DES LIGNES OU COLONNES SÉLECTIONNÉES GROUPE LES LIGNES OU LES COLONNES SÉLECTIONNÉES DISSOCIE LES LIGNES OU LES COLONNES SÉLECTIONNÉES
SYMBOLES DE PLAN REPRÉSENTATION DE LA STRUCTURE DU PLAN EN LIGNES
REPRÉSENTATION DE LA STRUCTURE DU PLAN EN COLONNES
POUR DÉVELOPPER POUR RÉDUIRE BOUTONS NIVEAUX DE LIGNE ET NIVEAUX DE COLONNE
<CLIC G> POUR AFFICHER LE NIVEAU DÉSIRÉ
Éditions I O S
page 7
E E.. LLE ES SS STTY YLLE ES SD DE EP PLLA AN N Le style affecté aux différents niveaux de plan peut être remanié dans la boîte de dialogue FORMAT STYLES. MODIFIER LES STYLE DE PLAN : PROCÉDURE
MENU FORMAT STYLE COCHER LES CASES DES PARAMÈTRES À PRENDRE EN COMPTE PAR LE STYLE
<CLIC G> SUR les styles de plan peuvent être comme tout autre style redéfinis à ce niveau
DANS LE CLASSEUR "PLAN.XLS", FEUILLE "EXERCICE", MODIFIER LES STYLES DES TOTAUX DE LIGNE ET DE COLONNE DU PLAN
EXERCICE
FF.. LLA AS SU UP PP PR RE ES SS SIIO ON ND DU UP PLLA AN N Si le plan n'est pas utile temporairement, il est possible de travailler sans afficher les symboles de plan. S'il n'apporte aucun avantage, autant le supprimer . La suppression ne consiste alors qu'à rehausser tous les niveaux de plan au niveau le plus haut, ce qui entraîne automatiquement la disparition de la structure. MASQUER UN PLAN : PROCÉDURE
SUPPRIMER UN PLAN :
MENU OUTILS OPTIONS AFFICHAGE GROUPE "FENÊTRE"
SÉLECTIONNER LES LIGNES DU TABLEAU MENU DONNÉES GROUPER ET CRÉER UN PLAN
DÉCOCHER <OK> POUR VALIDER
<CLIC G> SUR
les symboles de plan ne sont plus affichés
le plan est supprimé
IDEM POUR LES COLONNES
OUVRIR LE CLASSEUR "BILAN1.XLS" FAIRE LES SOUS-TOTAUX, TOTAUX ET CALCULS DE CHACUNE DES FEUILLES CRÉER UN PLAN DANS CHACUNE DES FEUILLES POUR N'AFFICHER QUE LES LIGNES DE SYNTHÈSE RENOMMER CHACUNE DES FEUILLES EN FONCTION DU CONTENU DE LA CELLULE F1 EXERCICE FAIRE SUR CHACUNE DES FEUILLES UN GRAPHIQUE REPRÉSENTANT LA STRUCTURE SOUS FORME D'HISTOGRAMME CUMULÉ REPRÉSENTER L'ÉVOLUTION (CA, RÉSULTAT BRUT ET NET) SUR UNE FEUILLE GRAPHIQUE AVEC UN GRAPHIQUE À CÔNE 3D
page 8
reproduction réservée
III. LA CONSOLIDATION La consolidation des feuilles de travail permet de synthétiser les données de plusieurs tableaux en un seul. Un lien peut être conservé entre les zones sources contenant les données et la zone de destination. Le mode plan permet alors de conserver le détail de chacun des chiffres consolidés. La consolidation peut s'effectuer de deux manières différentes : PAR POSITION :
la consolidation est basée sur les références des zones sources
PAR CATÉGORIE : La consolidation est basée sur les étiquettes de lignes et/ou de colonnes (les libellés de la colonne de gauche et de la ligne du haut) Elle peut utiliser diverses fonctions bien que la fonction SOMME soit la plus courante : somme
moyenne
ecartypep
nbval
produit
ecartype
nb
max
min
une consolidation ne peut pas être annulée
ATTENTION
A A.. LLA AC CO ON NS SO OLLIID DA ATTIIO ON N // P PO OS SIITTIIO ON N Ce mode convient à la consolidation de tableaux présentant une structure identique. Ainsi, il suffit de donner les références des zones à consolider pour que la consolidation puisse s'effectuer. Seules les zones où figureront des valeurs sont à indiquer dans la zone de destination. Les libellés de lignes et de colonnes ne sont pas à inclure dans les zones (que ce soient les zones sources ou la zone de destination). CONSOLIDATION/POSITION : PROCÉDURE
OUVRIR LE OU LES CLASSEURS NÉCESSAIRES CRÉER PAR COPIE UNE STRUCTURE D'ACCUEIL DES DONNÉES CONSOLIDÉES SÉLECTIONNER LA ZONE DE DESTINATION (EX : B2.E5) MENU DONNÉES CONSOLIDER
éventuellement cocher pour créer un lien entre données sources et données résultantes <CLIC G> SUR RÉFÉRENCES <FAIRE GLISSER> LE POINTEUR SUR LA ZONE À CONSOLIDER DU PREMIER TABLEAU SOURCE (EX : CONSEST.XLS!B2:E5) <AJOUTER> FAIT PASSER LES RÉFÉRENCES DE LA ZONE DÉSIGNÉE DANS "RÉFÉRENCES SOURCE" RECOMMENCER POUR CHACUN DES TABLEAUX À CONSOLIDER <OK> POUR EFFECTUER LA CONSOLIDATION
NOTES
Éditions I O S
page 9
Si les fichiers à consolider ne sont pas ouverts, le bouton <parcourir>permet de les désigner. La zone à consolider doit alors être indiquée à la suite par ses références INFORMATION
Vous pouvez directement sélectionner un autre classeur ouvert sans passer par l'outil CONSEIL
OPTIONS FAIT PASSER LA RÉFÉRENCE DÉFINIE DE LA ZONE RÉFÉRENCES À LA ZONE RÉFÉRENCES SOURCE PERMET DE SUPPRIMER DE LA ZONE RÉFÉRENCES SOURCES UNE RÉFÉRENCE ERRONÉE N'EST UTILE QUE LORSQUE LES FICHIERS SOURCES NE SONT PAS OUVERTS
INFORMATION
Si les feuilles de travail à consolider sont ouvertes, il suffit de passer de la zone références de la boîte de dialogue à la feuille de travail; de sélectionner la zone à consolider, puis de revenir à la zone références de la boîte de dialogue ; les références complètes de la zone s'inscrivent et le bouton <ajouter> permet de l'inclure dans le périmètre de consolidation ; réitérer cette opération pour chacune des feuilles de travail à consolider CRÉER AUTOMATIQUEMENT UN PLAN EN CONSERVANT UN LIEN ENTRE DONNÉES SOURCE ET DONNÉES RÉSULTANTE EN COCHANT LA CASE À COCHER
CONSEIL
INFORMATION
Si la zone est activée, la feuille de travail consolidée est créée en mode plan ; elle ne peut contenir qu'une et une seule consolidation dont les références peuvent cependant être aisément modifiées ou complétées UNE ZONE DE LA FEUILLE DE CONSOLIDATION PEUT ÊTRE NOMMÉE ZONE_DE_CONSOLIDATION ; ELLE DEVIENT ALORS ZONE DE CONSOLIDATION PERMANENTE
INFORMATION
NOTES
page 10
reproduction réservée
CONSPOS.XLS
OUVRIR LE CLASSEUR "CONSPOS1.XLS" (DOSSIER CONSOPOS) EFFECTUER LA CONSOLIDATION DES FEUILLES DU CLASSEUR
EXERCICE
OUVRIR LE CLASSEUR "CONSPOS2.XLS" (DOSSIER CONSOPOS) EFFECTUER LA CONSOLIDATION DES CLASSEURS "CONSEST", "CONSNORD", "CONSUD" ET "CONSOUEST" EXERCICE
B B.. LLA AC CO ON NS SO OLLIID DA ATTIIO ON N // C CA ATTE EG GO OR RIIE E Ce mode convient à la consolidation de données disparates, situées dans des endroits différents de feuilles de travail à priori différentes mais comportant des titres de lignes et/ou de colonnes (étiquettes) identiques. Excel sait alors retrouver les données correspondant à ces étiquettes et les consolider dans une feuille de travail de destination, tout en gardant le cas échéant un lien entre données sources et zone de destination.
INFORMATION
CONSEIL
La procédure est quasiment identique à la précédente hormis le fait qu'il est nécessaire d'inclure dans les références des zones sources et de la zone de destination les libellés des lignes et/ou des colonnes afférentes aux valeurs. La consolidation s'effectuera dans l'ordre des étiquettes des lignes de la zone de destination Il est presque indispensable dans ce type de consolidation que les feuilles de travail sources soient ouvertes dans la mesure où les données peuvent être à des endroits différents et dans un ordre différent d'une feuille à l'autre CONSOLIDATION/CATEGORIE :
PROCÉDURE
OUVRIR LE CLASSEUR OU LES TABLEAUX NÉCESSAIRES CRÉER PAR COPIE UNE STRUCTURE D'ACCUEIL DES DONNÉES CONSOLIDÉES SÉLECTIONNER LA ZONE DE DESTINATION (EX : A1.E5) MENU DONNÉES CONSOLIDER <CLIC G> SUR "LIER AUX DONNÉES SOURCES" POUR CRÉER UN LIEN ENTRE DONNÉES SOURCES ET DONNÉES RÉSULTANTES <ACTIVER> LES CASES À COCHER D’ÉTIQUETTES <CLIC G> SUR RÉFÉRENCES <FAIRE GLISSER> LE POINTEUR SUR LA ZONE À CONSOLIDER DU PREMIER TABLEAU SOURCE (EX : CONSEST.XLS!A1:E5) <AJOUTER> COPIE LES RÉFÉRENCES DE LA ZONE DÉSIGNÉE DANS LA ZONE RÉFÉRENCES SOURCE RECOMMENCER POUR CHACUN DES TABLEAUX À CONSOLIDER <OK> POUR EFFECTUER LA CONSOLIDATION
Éditions I O S
page 11
CONSCAT1.XLS
page 12
reproduction réservée
OUVRIR LE CLASSEUR "CONSCAT1.XLS" (DOSSIER CONSOCAT1) CONSOLIDER LES DONNÉES DES FICHIERS CONSSUD, CONSOUEST, CONSNORD ET CONSEST EN CONSERVANT UNE LIAISON AVEC LES FICHIERS D'ORIGINE. REMARQUE : LES TABLEAUX NE SONT PAS SEMBLABLES CAR LES CATÉGORIES D'OUTILS SONT SUR DES EXERCICE LIGNES DIFFÉRENTES
EXERCICE
EXERCICE
OUVRIR LE CLASSEUR "CONSCAT2.XLS" (DOSSIER CONSOCAT2) CONSOLIDER LES DONNÉES DES FICHIERS CONSSUD, CONSOUEST, CONSNORD ET CONSEST EN CONSERVANT UNE LIAISON AVEC LES FICHIERS D'ORIGINE. REMARQUE : LES TABLEAUX SONT À DES ENDROITS DIFFÉRENTS DE LA FEUILLE
OUVRIR LE CLASSEUR "CONSCAT3.XLS" (DOSSIER CONSOCAT3) CONSOLIDER LES DONNÉES DES FEUILLES SUD, OUEST, NORD ET EST. REMARQUE : LES TABLEAUX NE SONT PAS SEMBLABLES CAR LES CATÉGORIES D'OUTILS SONT SUR DES LIGNES DIFFÉRENTES.
C C.. LLA AM MIIS SE EA A JJO OU UR RE ETT LLA AM MO OD DIIFFIIC CA ATTIIO ON N La mise à jour des données consolidées s'effectue automatiquement au sein d’un classeur. Cette mise à jour est aussi automatique entre des fichiers séparés si à la fois les fichiers sources et le fichier cible sont ouverts. Si les fichiers source ne sont pas ouverts, les procédures standards de mise à jour des liaisons régissent les rapports entre sources et destination dès lors que des liaisons ont été créées.
PROCÉDURE
MISE A JOUR : <ACTIVER> LA FEUILLE DE CONSOLIDATION MENU EDITION LIAISONS SÉLECTIONNER LA OU LES LIAISONS FERMER LA BOÎTE DE DIALOGUE
NOTES
Éditions I O S
page 13
PROCÉDURE
MODIFIER LES FICHIERS LIES : <ACTIVER> LA FEUILLE DE CONSOLIDATION MENU EDITION LIAISONS SÉLECTIONNER LA OU LES LIAISONS À MODIFIER INDIQUER LE NOUVEAU FICHIER LIÉ RÉITÉRER L'OPÉRATION SI NÉCESSAIRE FERMER LA BOÎTE DE DIALOGUE
MODIFIER FICHIERS ET ZONES : <ACTIVER> LA FEUILLE DE CONSOLIDATION PROCÉDURE
SÉLECTIONNER LA ZONE DE DESTINATION MENU DONNÉES CONSOLIDER <CLIC G> SUR LA RÉFÉRENCE SOURCE À MODIFIER ELLE S'INSCRIT DANS LA ZONE RÉFÉRENCES MODIFIER LA RÉFÉRENCES PAR <PARCOURIR> PAR SÉLECTION DE LA SOURCE OU PAR RÉÉCRITURE DES RÉFÉRENCES <AJOUTER> <CLIC G> SUR L'ANCIENNE RÉFÉRENCE SOURCE <SUPPRIMER> RECOMMENCER AUTANT DE FOIS QUE NÉCESSAIRE <OK> POUR METTRE À JOUR
CRÉER UN NOUVEAU DOSSIER NOMMÉ "EXERCICE CONSO" Y COPIER TOUS LES FICHIERS DU DOSSIER "CONSOCAT2" SAUF "CONSCAT2.XLS" MODIFIER LES VALEURS DE L'UN OU DE PLUSIEURS FICHIERS DU DOSSIER "EXERCICE CONSO" OUVRIR LE CLASSEUR DOSSIER "CONSCAT2.XLS" DU DOSSIER "CONSOCAT2" EXERCICE CHANGER LA SOURCE POUR QU'IL POINTE SUR LES CLASSEURS DU DOSSIER "EXOCONSO"
Un avertissement peut être généré à l'ouverture d'un classeur possédant des liaisons <CLIC G> sur
de la boite de dialogue "liaisons" pour gérer ce message
AVANCÉ
NOTES
page 14
reproduction réservée
IV. LES LIAISONS Il est préférable d'organiser les données dans des feuilles de travail séparées et/ou dans des classeurs séparés, bien qu'il soit souvent nécessaire de reprendre des résultats d'une feuille de travail ou d'un classeur dans une autre. Dans le même ordre d'idée, plusieurs feuilles de travail d'un même classeur ou de classeurs différents peuvent traiter des étapes successives d'un même processus. Excel permet de résoudre de manière optimale la plupart de ces cas de figure. les classeurs d'ou proviennent les données sont les classeurs auxiliaires le classeur contenant une copie mise à jour des données est le classeur principal les références externes sont des références à une plage de cellules d'un autre classeur INFORMATION
A A.. LLE ES S LLIIA AIIS SO ON NS SE EN NTTR RE EC CLLA AS SS SE EU UR RS S Si des informations nécessaires à une feuille de travail sont disponibles dans une feuille de travail d'un autre classeur, il est possible de créer des liaisons entre les deux fichiers. (Si les feuilles de travail appartiennent au même classeur, une formule normale suffit) (VOIR LIAISON.XLS - RECAP). Deux possibilités s'offrent alors : copier avec liaison saisir une formule de liaison
1 COPIE AVEC LIAISON La copie avec liaison permet de lier les données de feuilles différentes. COPIER AVEC LIAISON : PROCÉDURE
FENÊTRE DU DOCUMENT SOURCE ACTIVÉE SÉLECTIONNER LA PLAGE LA COPIER DANS LE PRESSE-PAPIERS FENÊTRE DU DOCUMENT CIBLE ACTIVE SÉLECTIONNER LA CELLULE DE COPIE <CLIC D> - MENU CONTEXTUEL
Éditions I O S
page 15
2 LA FORMULE DE LIAISON Une formule du type suivant s'inscrit dans la zone cible
DANS LAQUELLE
=
INDIQUE QU'UNE FORMULE OU UNE VALEUR NUMÉRIQUE SUIT
' '
ENCADRENT CHEMIN D'ACCÈS, NOM DU CLASSEUR ET NOM DE LA FEUILLE
C:\EXCEL\DONNEES\
CHEMIN D'ACCÈS DU FICHIER (UNITÉ DE DISQUE - RÉPERTOIRE - SOUS-DOSSIER)
VENTES.XLS
NOM DU FICHIER LIÉ (CELUI OU SE TROUVENT LES DONNÉES)
[ ]
ENCADRENT LE NOM DU FICHIER
REGION EST
NOM DE LA FEUILLE DE TRAVAIL DU CLASSEUR
!
SÉPARE LA FEUILLE DE TRAVAIL DE LA PLAGE
$F$2
RÉFÉRENCE DE LA PLAGE D'ORIGINE (CELLE OU SE TROUVENT LES DONNÉES)
3 SAISIE DE LA FORMULE DE LIAISON L'utilisateur peut lui même inscrire cette formule et l'associer à des opérateurs arithmétiques ou des fonctions de EXCEL. Une formule du type suivant permet de faire la consolidation de plusieurs tableaux :
4 MISE A JOUR DES LIAISONS La mise à jour s'effectue automatiquement à l'ouverture et en cours d'utilisation du fichier. Cependant, les différentes liaisons d'un document peuvent être actualisées si les options mise à jour manuelle et/ou recalcul sur ordre sont activées (voir page 13).
5 NEUTRALISATION DE LA MISE A JOUR Les liaisons entre les différent classeurs sont mises à jour lors de leur ouverture. Il est cependant possible de neutraliser cette mise à jour. NEUTRALISER UNE LIAISON : PROCÉDURE
CLASSEUR PRINCIPAL ACTIVE MENU OUTILS OPTIONS ONGLET ACTIVER LA CASE DÉSACTIVER LA CASE ONGLET DÉSACTIVER LA CASE POUR VALIDER
la dernière valeur des liaisons sera prise en compte
NOTES
page 16
reproduction réservée
OUVRIR LE CLASSEUR "LIAISONS.XLS" (DOSSIER LIAISONS) FAIRE UN TABLEAU DE SYNTHÈSE À PARTIR DU FICHIER VENTES.XLS (DOSSIER LIAISONS) INDIQUER EN LIGNE CHACUNE DES RÉGIONS INDIQUER EN COLONNE CHACUNE DES ACTIVITÉS EXERCICE POUR CHAQUE CELLULE DE VALEUR, FAIRE LA SOMME DE CHAQUE CELLULE DE CHACUN DES TABLEAUX RÉGIONAUX CONSERVER DES LIAISONS AVEC CHACUN DE CES TABLEAUX AFIN QUE LE TABLEAU DE SYNTHÈSE SOIT AUTOMATIQUEMENT MIS À JOUR MODIFIER LES TABLEAUX D'ORIGINE VÉRIFIER LA MISE À JOUR DANS LE TABLEAU DE SYNTHÈSE NEUTRALISER LA MISE À JOUR MODIFIER LES TABLEAUX D'ORIGINE METTRE À JOUR ET ANNULER LA NEUTRALISATION REPRÉSENTER LE TABLEAU DE SYNTHÈSE PAR UN GRAPHE EN AIRE 3D
B B..
LLE ES S LLIIA AIIS SO ON NS SA AV VE EC CD D''A AU UTTR RE ES S FFIIC CH HIIE ER RS S
Il peut être nécessaire de faire communiquer des applications différentes entre elles (Mettre un tableau ou/et un graphique EXCEL dans un document Word par exemple). Deux possibilités s'offrent alors : créer une liaison DDE un lien est établi entre l'application serveur (celle qui possède l'information) et l'application client (celle qui a besoin de l'information) - les deux fichiers mis en jeu existent chacun individuellement) copier les données dans le presse-papiers - coller les données par collage spécial avec liaison créer une liaison OLE un objet est crée au sein du fichier, cet objet contenant à la fois les données et leurs programmes et ce au sein de l'application gérant le document principal. le fichier ainsi crée peut n'avoir aucune existence individuelle ou être créé à partir d'une copie d'un fichier existant barre d'outils ou menu insertion objet.
NOTES
Éditions I O S
page 17
1 LA LIAISON DDE Le menu et le presse-papiers sont les moyens privilégiés pour créer une liaison DDE. LIAISON DDE : 1 -APPLICATION ET DOCUMENT SOURCE ACTIFS (EXCEL) PROCÉDURE
SÉLECTIONNER LA PLAGE DANS LE PRESSE-PAPIERS 2 -DOCUMENT CIBLE ACTIVE (WORD) POSITIONNER LE POINT D'INSERTION MENU ÉDITION
POUR VALIDER
<double clic> dans les données importées ouvre l'application d'origine permettant de les modifier INFORMATION
EXERCICE
OUVRIR WORD OUVRIR DANS EXCEL LE DOCUMENT "VENTES.XLS" (DOSSIER LIAISONS) COPIER LA SYNTHÈSE "TOTAL FRANCE" DANS LE DOCUMENT W ORD EN MAINTENANT UNE LIAISON MODIFIER LE DOCUMENT "VENTES" À PARTIR DE W ORD
NOTES
page 18
reproduction réservée
2 LA LIAISON OLE L'outil adéquat de la barre d'outil standard est souvent le meilleur moyen de créer un objet incorporé (exemple : outil pour créer un tableau Excel dans Word) ; Le menu permet lui de créer tout type d'objet. TABLEAU EXCEL DANS WORD (création de AUTRE OBJET (création de données données propres): propres): PROCÉDURE
POSITIONNER LE POINT D'INSERTION
<CLIC G> SUR RENSEIGNER LE TABLEAU POUR VALIDER RENSEIGNER L'OBJET <CLIC G> EN DEHORS DE L'OBJET TERMINÉ
POSITIONNER LE POINT D'INSERTION MENU INSERTION OBJET SÉLECTIONNER LE TYPE D'OBJET POUR VALIDER RENSEIGNER L'OBJET
NOTES
Éditions I O S
page 19
Une liaison OLE peut aussi être créée à partir d'un fichier existant mais plus rarement. LIAISON OLE (fichier de données existant): PROCÉDURE
FAIRE GLISSER LA PLAGE SÉLECTIONNÉE DU CLASSEUR EXCEL AU DOCUMENT WORD OU APPLICATION ET DOCUMENT SOURCE ACTIFS (EXCEL) SÉLECTIONNER LA PLAGE DANS LE PRESSE-PAPIERS DOCUMENT CIBLE ACTIVE (WORD) POSITIONNER LE POINT D'INSERTION MENU ÉDITION
POUR VALIDER
INFORMATION
l'objet peut être symbolisé par une icône et non affiché en activant la case ; un fichier existant peut être utilisé en choisissant l'onglet <objet existant> <DOUBLE CLIC> sur l'objet appelle le menu de l'application le gérant et permet de le modifier OUVRIR W ORD ET CRÉER DANS W ORD LE TABLEAU SUIVANT SOUS FORME D'OBJET INCORPORÉ EN UTILISANT L'OUTIL
EXERCICE
NOTES
page 20
reproduction réservée
V. LA RECHERCHE DE SOLUTIONS Un certain nombre d'outils vont permettre de rechercher des solutions plus ou moins complexes à un problème donné et de les enregistrer.
A A.. LLA AV VA ALLE EU UR RC CIIB BLLE E La valeur cible prend en compte trois éléments : une cellule à définir, une valeur à atteindre et une cellule à modifier ; bien évidemment, il doit y avoir une relation (directe ou indirecte) entre la cellule à définir (qui contient une formule) et la cellule à modifier (qui contient une constante). C'est en quelque sorte l'équivalent d'un solveur simplifié (VOIR VALCIB.XLS)
PROCÉDURE
LA VALEUR CIBLE : <CLIC G> SUR LA CELLULE À DÉFINIR MENU OUTILS VALEUR CIBLE INDIQUER LA VALEUR À ATTEINDRE DÉSIGNER LA CELLULE À MODIFIER <OK> POUR VALIDER
OUVRIR VALCIB.XLS (DOSSIER SOLVEUR) SACHANT QUE LE SALAIRE NET EST ÉGAL AU SALAIRE BRUT MOINS LES CHARGES SOCIALES SALARIALES DÉTERMINER LE SALAIRE BRUT PERMETTANT D'OBTENIR UN SALAIRE NET DE 1500 € POUR 150 H EXERCICE DÉTERMINER LE NOMBRE D'HEURES DE TRAVAIL NÉCESSAIRE POUR OBTENIR UN SALAIRE NET DE 1500 € POUR À PARTIR D'UN SALAIRE BRUT DE 2500 €
NOTES
Éditions I O S
page 21
B B.. LLE EG GE ES STTIIO ON NN NA AIIR RE ED DE ES SC CE EN NA AR RIIO OS S Le gestionnaire de scénarios permet d'enregistrer des combinaisons de valeurs de cellules constantes d'une feuille de travail et de donner un nom à cet enregistrement : C'est le scénario. Les solutions proposées par la recherche d'une valeur cible ou par le solveur en fonction des hypothèses et des contraintes données vont pouvoir être conservées de cette manière.
PROCÉDURE
LANCER LE GESTIONNAIRE DE SCENARIOS : MENU OUTILS GESTIONNAIRE DE SCÉNARIOS
PARAMÈTRES Joue le scénario sélectionné en remplaçant les valeurs des cellules variables Ajoute un scénario en proposant les valeurs actuelles des cellules variables Supprime le scénario sélectionné Modifie le scénario sélectionné Fusionne les scénarios de feuilles de travail ouvertes Propose une synthèse des scénarios existants sur une feuille de travail
NOTES
page 22
reproduction réservée
A)
ENREGISTRER UN SCENARIO
Pour enregistrer un scénario, il faut auparavant afficher dans la feuille les valeurs voulues, soit directement, soit par l'intermédiaire de la valeur cible ou par l'intermédiaire du solveur. AJOUTER UN SCENARIO : PROCÉDURE
AFFICHER DANS LA FEUILLE LES VALEURS VOULUES MENU PRINCIPAL OUTILS GESTIONNAIRE DE SCÉNARIOS. NOMMER LE SCÉNARIO ACCEPTER OU MODIFIER LES CELLULES VARIABLES <OK> POUR VALIDER
pour indiquer plusieurs cellules variables, utiliser <CTRL>
La boîte de dialogue "modifier un scénario" est quasiment identique ; il faut simplement, comme ici afficher préalablement les valeurs voulues dans les cellules INFORMATION
Éditions I O S
page 23
Enregistrer directement le scénario correspondant aux solutions du solveur à partir de ce dernier à l'issue de la résolution du problème en choisissant
.
CONSEIL
B)
AFFICHER UN SCENARIO
Un scénario, une fois enregistré, peut être affiché à volonté ; Pour conserver les scénarios d'une session à l'autre, il faut enregistrer la feuille de travail. AFFICHER UN SCENARIO : MENU OUTILS - GESTIONNAIRE DE SCÉNARIOS. PROCÉDURE
SÉLECTIONNER LE SCÉNARIO
AFFICHER VALCIB.XLS ENREGISTRER LES SOLUTIONS AUX 2 PROBLÈMES POSÉS CI-DESSUS EN TANT QUE SCÉNARIOS SOIT : 1 -DÉTERMINER LE SALAIRE BRUT PERMETTANT D'OBTENIR UN SALAIRE NET DE 1500 € POUR 150 H 2 - DÉTERMINER LE NOMBRE D'HEURES DE TRAVAIL NÉCESSAIRE POUR OBTENIR UN SALAIRE NET DE EXERCICE 1500 € POUR À PARTIR D'UN SALAIRE BRUT DE 2500 €
C)
LA SYNTHÈSE DES SCENARIOS
La synthèse des scénarios est un rapport ou un tableau croisé synthétisant toutes les options. AFFICHER LA SYNTHÈSE DES SCENARIOS : MENU OUTILS - GESTIONNAIRE DE SCÉNARIOS. PROCÉDURE
INDIQUER LA CELLULE RÉSULTANTE POUR VALIDER
DANS LE CLASSEUR VALCIB.XLS, AFFICHER LA SYNTHÉSE DES SCÉNARIOS
EXERCICE
page 24
reproduction réservée
C C.. LLE ES SO OLLV VE EU UR R L'application solveur, intégrée à Microsoft Excel, permet d'effectuer des simulations. Il faut cependant modéliser préalablement le problème sur une feuille de calcul. (VOIR PUB.XLS).
CONSEIL
La modélisation du problème dans un classeur Excel est la partie la plus ardue ; si le problème est bien posé, l'utilisation du solveur est très simple. Consacrer le temps nécessaire à une analyse approfondie du problème avant de commencer à travailler
1 LANCER LE SOLVEUR Le solveur est une application complémentaire accessible par le menu outils.
PROCÉDURE
POUR LANCER LE SOLVEUR : MENU OUTILS SOLVEUR PARAMÉTRER LES OPTIONS DU SOLVEUR RÉSOUDRE
Si la commande "solveur" n'est pas affichée dans le menu "outils", activer la macro correspondante dans outils macros complémentaires ATTENTION
EXERCICE
LE CLASSEUR PUB.XLS EST UN TABLEAU DE GESTION D'ENTREPRISE. DANS CE TABLEAU, LE MONTANT DES INVESTISSEMENTS PUBLICITAIRES DÉTERMINE, PAR LE BIAIS D'UNE FORMULE, LES UNITÉS VENDUES. LE PROBLÈME EST DONC DE CALCULER LE BUDGET PUBLICITAIRE QUI PERMET DE MAXIMISER LE RÉSULTAT DE L'ENTREPRISE
PUB.XLS - TABLEAU
NOTES
Éditions I O S
page 25
2 PARAMÉTRER LE SOLVEUR Les paramètres de fonctionnement du solveur sont indiqués dans la boîte de dialogue principale.
PARAMÈTRES Sa valeur peut être maximisée, minimisée ou son niveau déterminé. Cellules variables que le solveur modifie pour trouver une solution. Ajouter, modifier ou supprimer des contraintes Propose des cellules définissant directement ou indirectement le résultat Rétablit les paramètres par défaut Lance le solveur Voir "Contrôle de la recherche d'une solution"
A)
LA CELLULE CIBLE
C'est la cellule que l'on veut optimiser. Dans notre exemple, c'est la cellule F14, le bénéfice total. Cette cellule doit atteindre un maximum, un minimum ou une valeur donnée. Cellule cible FEUILLE DE TRAVAIL
SOLVEUR
NOTES
page 26
reproduction réservée
B)
LES CELLULES VARIABLES
Ce sont les cellules dont la valeur va être modifiée par le solveur afin d'optimiser la cellule cible. Cellules variables FEUILLE DE TRAVAIL
SOLVEUR
C)
LES CONTRAINTES
Une contrainte est l'affectation à une cellule ou une plage de cellules contenant une formule(et dont le résultat dépend des valeurs prises par une ou plusieurs cellules variables) de valeurs ou de normes définies. Des contraintes peuvent être ajoutées à celles d'origine ; de même, elles peuvent être modifiées. CONTRAINTES FEUILLE DE TRAVAIL
SOLVEUR
OPTIONS : Ajoute une contrainte supplémentaire Modifie la contrainte sélectionnée Supprime la contrainte sélectionnée AJOUTER UNE CONTRAINTE : PROCÉDURE
<CLIC G> SUR <CLIC G> SUR LA CELLULE <CLIC G> SUR L'OPÉRATEUR LOGIQUE SAISIR LA VALEUR DANS CONTRAINTE <OK> POUR VALIDER
NOTES
Éditions I O S
page 27
D)
LES RAPPORTS DE RESULTATS
Les rapports résument les résultats de la recherche d'une solution. RAPPORTS : <RÉSOUDRE> LANCE LE SOLVEUR PROCÉDURE
SÉLECTIONNER LES RAPPORTS À CRÉER <CLIC G> SUR CHACUN DES RAPPORTS "GARDER LA SOLUTION DU SOLVEUR" ACTIVÉ <OK> POUR VALIDER
les valeurs trouvées par le solveur peuvent ici être enregistrées sous forme de scénario
RAPPORT DES REPONSES
NOTES
page 28
reproduction réservée
RAPPORT DE LA SENSIBILITÉ
RAPPORT DES LIMITES
NOTES
Éditions I O S
page 29
E) Le bouton
EXERCICE
CONTRÔLE DE LA RECHERCHE D'UNE SOLUTION de la boîte de dialogue principale gère le processus de recherche.
UN GROUPE POSSÈDE 3 USINES DE FABRICATION DE PIÈCES DÉTACHÉES (USINE1, USINE2, USINE3) CHACUNE DE CES USINES EST À MÊME DE PRODUIRE DES CHÂSSIS, DES TUBES VIDÉO, DES CÔNES DE HAUT PARLEUR, DES ALIMENTATIONS ET DE L'ÉLECTRONIQUE UN COEFFICIENT DE PRODUCTION DÉTERMINE LA CAPACITÉ DE PRODUCTION DE L'USINE (400, 500, 600) LES PRÉVISIONS DE PRODUCTION SONT CALCULÉES EN % DU COEFFICIENT DE PRODUCTION (43 % POUR LES CHÂSSIS, 22% POUR LES TUBES, 85% POUR LES CÔNES HP, 43 % POUR LES ALIMENTATIONS ET 64% POUR L'ÉLECTRONIQUE) CELA QU'ELLE QUE SOIT L'USINE LA FABRICATION RÉELLE EST UN % DE LA FABRICATION PRÉVUE (95 % POUR CHACUNE DES USINES) LES REJETS SONT DE 1% DE LA FABRICATION UNE USINE DE MONTAGE UTILISE CES PIÈCES DÉTACHÉES POUR FABRIQUER 3 TYPES DE PRODUIT FINI : DES TÉLÉVISIONS, DES CHAINES STÉRÉO ET DES HAUTS PARLEURS LE NOMBRE DE PIÈCES DÉTACHÉES NÉCESSAIRE POUR FABRIQUER UN TYPE DE PRODUIT EST DIFFÉRENT SELON LES PRODUITS : TÉLÉVISION, CHAÎNE STÉRÉO, HAUT-PARLEURS
LE NOMBRE DE PRODUITS EST LIMITÉ PAR LA QUANTITÉ DE PIÈCES DÉTACHÉES FABRIQUÉES DANS LES 3 USINES LE PRIX DE VENTE DE CHACUN DES PRODUITS EST LE SUIVANT : TÉLÉ : 5000 €, CHAINE : 3000 €, HP : 1200 € LE PROFIT EST CALCULÉ EN MULTIPLIANT CE PRIX PAR UN COEFFICIENT DE MARGE ET PAR LE NOMBRE DE PRODUITS FABRIQUÉS LES COEFFICIENTS DE MARGES SONT DIFFÉRENTS SELON LES PRODUITS : TÉLÉ : 60%, CHAINE : 40%,
HP : 30% LE BUT DU JEU EST DE MAXIMISER LE PROFIT GLOBAL EN FONCTION DES COEFFICIENTS DE MARGE PAR PRODUIT EN FONCTION DU NOMBRE DE CHACUN DES PRODUITS FABRIQUÉS DANS LA LIMITE DES CAPACITÉS DE PRODUCTION DE CHACUNE DES USINES ET EN OPTIMISANT LA RÉPARTITION DES PIÈCES DÉTACHÉES ENTRE LES PRODUITS FAIRE UN GRAPHIQUE SUR SA PROPRE FEUILLE COMPARANT L'INVENTAIRE ET L'UTILISATION DE CHAQUE ARTICLE DE BASE ENREGISTRER LES DIFFÉRENTS CAS DE FIGURE (ORIGINE, SOLVEUR…) COMME SCÉNARIOS FAIRE LA SYNTHÉSE DES SCÉNARIOS ET LA REPRÉSENTER GRAPHIQUEMENT SUR UNE FEUILLE GRAPHIQUE
page 30
reproduction réservée
VI. LE GRAPHIQUE Les graphiques sont un complément indispensable aux tableaux. Ils permettent de matérialiser les données en les présentant d'une manière concise et claire. Les graphiques sont insérés au sein d'une feuille de travail ou sont affichés dans leur propre feuille graphique. Ils sont automatiquement mis à jour en fonction de l'évolution des données.
A A.. LLA AC CR RE EA ATTIIO ON ND DU UG GR RA AP PH HIIQ QU UE E Les graphiques peuvent être incorporés dans une feuille de travail (avec un tableau) ou indépendants dans leur propre feuille : Un graphique incorporé est un objet qui va venir compléter et illustrer un tableau et sera imprimé avec lui Un graphique indépendant est seul sur sa propre feuille ; il se suffit à lui-même ; il est plus destiné à compléter un classeur ou à être projeté sur un écran. SELECTION : PROCÉDURE
seule la zone foncée est à prendre en compte dans un histogramme: CREATION D'UN GRAPHIQUE INCORPORÉ : AFFICHER LA BARRE D'OUTILS GRAPHIQUES (CLIC DROIT) PROCÉDURE
SÉLECTIONNER LES DONNÉES DU TABLEAU À REPRÉSENTER Y COMPRIS LIBELLÉS et HORS TOTAUX
<CLIC G> SUR DE POUR DÉROULER LES TYPES DE GRAPHE <CLIC G> SUR LE TYPE DE GRAPHE RETENU le graphe est créé en milieu d'écran POINTER SUR UNE DES LIMITES INTÉRIEURES DU GRAPHIQUE ET FAIRE GLISSER LA SOURIS POUR REPOSITIONNER LE GRAPHIQUE
le pointeur de souris change alors de forme CREATION D'UNE FEUILLE GRAPHIQUE : PROCÉDURE
SÉLECTIONNER LES DONNÉES DU TABLEAU À REPRÉSENTER Y COMPRIS LIBELLÉS HORS TOTAUX <F11>
le graphe par défaut (histogramme simple) est créé sur sa propre feuille graphique DÉROULER LES TYPES DE GRAPHE <CLIC G> SUR LE TYPE DE GRAPHE RETENU RENOMMER ÉVENTUELLEMENT LA FEUILLE GRAPHIQUE
NOTES
Éditions I O S
page 31
CONSEIL
Préférer autant que possible la feuille graphique, plus simple à mettre en œuvre (simplement F11) et toujours bien proportionnée, plutôt que le graphe incorporé, difficile à équilibrer par rapport aux données et dont la proportion des différents éléments est souvent à reprendre - le cas échéant, adjoindre un tableau de données à la feuille graphique AFFICHER LA FEUILLE "RÉSULTAT" DU CLASSEUR "BILAN2" SÉLECTIONNER LES DEUX PREMIÈRES LIGNES DE DONNÉES ET LEURS LIBELLES
EXERCICE
CRÉER UN OBJET GRAPHIQUE HISTOGRAMME 3D DANS LA FEUILLE DE TRAVAIL- LE POSITIONNER SUPPRIMER LE GRAPHIQUE INCORPORÉ SÉLECTIONNER LES LIGNES CHIFFRE 'AFFAIRES ,CHARGES, AMORTISSEMENT, FRAIS FINANCIERS
CRÉER UNE FEUILLE GRAPHIQUE AVEC UN GRAPHIQUE À PYRAMIDE RENOMMER LA FEUILLE "STRUCTURE"
3D
B B.. LL''A AS SS SIIS STTA AN NTT G GR RA AP PH HIIQ QU UE E L'assistant va aider l'utilisateur dans chacune des 4 étapes de la conception. Il est plus long que les 2 méthodes précédemment décrites mais très complet. GRAPHIQUE DANS UNE FEUILLE : SÉLECTIONNER LES DONNÉES PROCÉDURE
<CLIC G> SUR DANS LA BARRE D'OUTILS SUR L'ASSISTANT
FEUILLE GRAPHIQUE : SÉLECTIONNER LES DONNÉES <CLIC D> SUR ONGLET DE FEUILLE INSÉRER -
Dans la plupart des graphiques (hors secteurs), il faut sélectionner plusieurs séries de données mais pas les totaux ; ces derniers peuvent être éventuellement réalisés au sein d'un graphique type histogramme cumulé ATTENTION
NOTES
page 32
reproduction réservée
1 PREMIÈRE ÉTAPE La première étape consiste à choisir le type de graphique.
A ce stade choisir le type de graphique puis <CLIC G> sur le bouton
.
NOTES
Éditions I O S
page 33
2 DEUXIÈME ÉTAPE La deuxième étape permet de modifier la plage de données sélectionnée ainsi que leur ordre.
INFORMATION
Pour accéder aux données du tableau pour modifier la sélection avec la souris, <CLIC G> sur INFORMATION
A ce stade confirmer ou modifier les choix puis <CLIC G> sur le bouton
.
NOTES
page 34
reproduction réservée
3 TROISIÈME ÉTAPE La troisième étape permet de choisir les options du graphique.
A ce stade confirmer ou modifier les choix puis <CLIC G> sur le bouton
.
NOTES
Éditions I O S
page 35
4 QUATRIÈME ÉTAPE La quatrième et dernière étape permet de confirmer l'insertion du graphique dans la feuille de travail ou au contraire de choisir la création d'une feuille graphique.
A ce stade confirmer ou modifier les choix puis <CLIC G> sur le bouton BOUTONS : permet de passer à l'étape suivante permet de revenir à l'étape précédente termine l'assistant CRÉER AVEC L'ASSISTANT GRAPHIQUE UN HISTOGRAMME 3D EN TANT QUE GRAPHIQUE INCORPORÉ ET REPRÉSENTANT LES VENTES (FEUILLE "VENTES") DU CLASSEUR "BILAN"
EXERCICE
C C.. S SÉ ÉLLE EC CTTIIO ON N,, TTA AIILLLLE EE ETT D DIIM ME EN NS SIIO ON N Le graphique incorporé va pouvoir être sélectionné, déplacé ou redimensionné.
1 SÉLECTION Avant de pouvoir changer la taille d'un graphique incorporé, il faut le sélectionner. SÉLECTIONNER LE GRAPHE : PROCÉDURE
POINTEUR SUR LES LIMITES DU GRAPHIQUE <CLIC G>
(flèche tournée vers la gauche)
le graphe est encadré de petits carrés noirs Bien prendre le graphique entier ; la bulle d'aide doit indiquer "zone de graphique" et non "zone de traçage". ATTENTION
NOTES
page 36
reproduction réservée
2 LA DIMENSION La dimension du graphique dans la feuille doit souvent être adaptée à celle des données. DIMENSIONNER LE GRAPHE : LE GRAPHIQUE ÉTANT SÉLECTIONNÉ PROCÉDURE
POINTEUR SUR UN DES CARRÉS NOIRS DE SÉLECTION DU GRAPHIQUE : <FAIRE GLISSER> POUR MODIFIER LA TAILLE DU GRAPHIQUE
,
,
,
les polices du graphique sont proportionnellement adaptées à la nouvelle taille du graphique ; si le graphique n'est pas complet (libellés non affichés), réduire la taille de police des axes ATTENTION
AFFICHER LA FEUILLE "VENTES" DU CLASSEUR "BILAN2 AGRANDIR LE GRAPHE PRÉCÉDEMMENT CRÉÉ
EXERCICE
3 LA POSITION Le graphique peut être positionné n'importe où dans la feuille de travail. POSITIONNER LE GRAPHE : PROCÉDURE
POINTER SUR UNE DES LIMITES DU GRAPHIQUE - LE POINTEUR DE SOURIS CHANGE DE FORME FAIRE GLISSER LA SOURIS POUR REPOSITIONNER LE GRAPHIQUE
pointer n'importe où sur les limites du graphe sauf les cases de sélection
ATTENTION
AFFICHER LA FEUILLE "VENTES" DU CLASSEUR "BILAN2" REPOSITIONNER LE GRAPHE PRÉCÉDEMMENT CRÉÉ
EXERCICE
NOTES
Éditions I O S
page 37
D D.. LLE ES SD DIIFFFFÉ ÉR RE EN NTTS SM MO OD DÈ ÈLLE ES S Des modèles de graphiques perfectionnés sont disponibles dans EXCEL ; ils répondent chacun à un type de problème particulier. Certains existent en deux et en trois dimensions.
1 LA MODIFICATION DU TYPE DE GRAPHIQUE La modification du type de graphique est immédiate avec la barre d'outils graphique ou avec le menu contextuel. MODIFICATION DU TYPE / BARRE GRAPHIQUE : LE GRAPHIQUE ÉTANT SÉLECTIONNÉ PROCÉDURE
PROCÉDURE
<CLIC G> SUR DE POUR DÉROULER LES TYPES DE GRAPHE <CLIC G> SUR LE TYPE DE GRAPHE RETENU MODIFICATION DU TYPE / MENU CONTEXTUEL : <SÉLECTIONNER> LA ZONE DE TRAÇAGE (lire la bulle d'aide) <CLIC D> - TYPE DE GRAPHIQUE afficher la feuille "ventes" du classeur "bilan changer le type du graphe précédemment créé en "cônes 3D"
INFORMATION
Si vous avez modifié la taille de la police par défaut de Excel, vous pouvez obtenir des graphiques dont les légendes sont de taille trop importante - réduire alors la taille de la police de ces légendes ATTENTION
INFORMATION
type de graphique détermine le type puis les sous-types de graphiques disponibles. format automatique correspond à l'enregistrement de modèles de graphiques incluant type de graphique et mise en forme (idem format automatique de plages de cellules
MODIFIER LE TYPE DE GRAPHIQUE PAR DÉFAUT : PROCÉDURE
METTRE EN FORME COMPLÈTEMENT LE GRAPHIQUE <CLIC D> DANS LA ZONE DE TRAÇAGE TYPE DE GRAPHIQUE MODIFIER ÉVENTUELLEMENT LES CARACTÉRISTIQUES
<CLIC G> SUR
POUR UTILISER A PARTIR DE MAINTENANT CE GRAPHIQUE COMME TYPE
PAR DÉFAUT <OK> <FERMER> OUVRIR EXERCICE GRAPHIQUE SUR LA FEUILLE "RÉGION EST", FAIRE UN GRAPHE INCORPORÉ "HISTOGRAMME 3D" L'ENRICHIR (POLICES, COULEURS, MOTIFS…) LE DÉFINIR COMME GRAPHIQUE PAR DÉFAUT EXERCICE LE TESTER SUR LA FEUILLE RÉGION NORD SUPPRIMER LES 2 GRAPHIQUES CRÉÉS
NOTES
page 38
reproduction réservée
AJOUTER UN FORMAT PERSONNALISE : PROCÉDURE
CRÉER UN FORMAT PERSONNALISE À PARTIR D'UN GRAPHIQUE STANDARD METTRE EN FORME CE GRAPHIQUE <CLIC D> DANS LA ZONE DE TRAÇAGE MENU CONTEXTUEL TYPE DE GRAPHIQUE ACTIVER L'ONGLET
(EN HAUT ET A DROITE)
SÉLECTIONNER (EN BAS ET A GAUCHE) <AJOUTER> PUIS DONNER LE NOM DU MODÈLE ET SA DESCRIPTION <OK> - <FERMER>
AFFICHER LA FEUILLE "RESULTAT" DU CLASSEUR "BILAN2" SÉLECTIONNER LES LIGNES DE DONNÉES "CHIFFRE D'AFFAIRES", "RESULTAT BRUT" ET "RESULTAT NET" ET LEURS LIBELLES EXERCICE
CREER UNE FEUILLE GRAPHIQUE AVEC UN GRAPHE EN CYLINDRE 3D PERSONNALISER LE GRAPHIQUE ET L'AJOUTER AUX TYPES PERSONNALISES FERMER LE CLASSEUR
NOTES
Éditions I O S
page 39
2 LES HISTOGRAMMES 2D/3D A)
MODÈLES
Plusieurs modèles d'histogrammes sont proposés par EXCEL.
B)
L'HISTOGRAMME SIMPLE (1) FINALITÉ
L'histogramme simple est adapté à la présentation de données par produit, par région ou autre. C'est le modèle automatiquement proposé lors de la création d'un graphe. Il se présente sous forme de barres verticales individuelles (un seul champ de données) ou groupées (plusieurs champs de données). Certains modèles d'histogrammes 3D permettent de visualiser des données en fonction de deux paramètres différents. (2) LES AXES L'axe des Y indique les valeurs atteintes pour chaque donnée. L'axe des X indique le type de chaque donnée ou groupe de données. L'axe des Z prend éventuellement en compte une troisième variable. (3) LES SÉRIES Chaque valeur de chaque série correspond à une barre verticale (ou horizontale). La marque de chaque valeur de chaque série peut être en 2D ou en 3D. (4) REPRÉSENTATION Ce premier modèle est un modèle 3D dont les marques sont 3D mais dont la représentation ne prend en compte que 2 paramètres.
OUVRIR LE CLASSEUR "EXERCICE GRAPHIQUE" FAIRE UN HISTOGRAMME 2D SUR UNE NOUVELLE FEUILLE GRAPHIQUE ENREGISTRER EN TYPE PERSONNALISÉ EXERCICE SUPPRIMER LA FEUILLE GRAPHIQUE CRÉÉE
NOTES
page 40
reproduction réservée
Le modèle suivant est un vrai modèle 3D avec 3 axes.
Les graphes à cônes, à pyramides où à cylindres ne sont que des variantes des graphes à barres (histo 3d) INFORMATION
NOTES
Éditions I O S
page 41
AFFICHER LE CLASSEUR "EXERCICE GRAPHIQUE" FAIRE UN GRAPHE EN PYRAMIDES INCORPORÉ SUR LA FEUILLE "TOTAL FRANCE" LE TRANSFORMER EN HISTOGRAMME 3D EXERCICE
Vous pouvez faire pivoter votre graphique pour présenter les séries au mieux. FAIRE PIVOTER LE GRAPHIQUE : PROCÉDURE
SÉLECTIONNER LA ZONE DE TRAÇAGE POINTER SUR LE COIN LE PLUS ÉLOIGNÉ <CLIC G> POUR LE SÉLECTIONNER <CLIC G> ET FAIRE GLISSER POUR FAIRE PIVOTER LE GRAPHE
Faire glisser ce point
AFFICHER LE CLASSEUR "EXERCICE GRAPHIQUE" FEUILLE "TOTAL FRANCE" LE FAIRE PIVOTER
EXERCICE
C)
L'HISTOGRAMME CUMULE (1) FINALITÉ
L'histogramme cumulé est adapté à la comparaison de la composition de plusieurs ensembles de données ou à la représentation de l'évolution dans le temps de la composition d'un ensemble unique dont Il superpose dans une même barre les différentes composantes.
NOTES
page 42
reproduction réservée
(2) LES AXES L'axe des X indique chacun des ensembles ou chacune des périodes successives. L'axe des Y informe sur la valeur de chaque composante et de l'ensemble. Éventuellement, l'axe des Z prend en compte une troisième variable. (3) LES SÉRIES Les valeurs de chaque série se cumulent en une barre verticale (ou horizontale) dont la valeur est la somme des valeurs de la série. Cette barre peut être en 2 D ou en 3D. (4) REPRÉSENTATION
AFFICHER LE CLASSEUR "EXERCICE GRAPHIQUE" FEUILLE "OUEST FAIRE UN HISTOGRAMME INCORPORÉ CUMULÉ 3D EN RELIEF SUR DE LA FEUILLE
EXERCICE
NOTES
Éditions I O S
page 43
3 LES GRAPHES LINÉAIRES 2D/3D Le graphe en courbes 2D se présente sous la forme de lignes représentant les séries de données et en reliant chaque point. En 3 D, seules les marques sont en volume afin d'améliorer la lisibilité du graphe. Des rubans prennent alors la place des lignes pour représenter les données.
A)
FINALITÉ
Le graphique en courbes ou linéaire est adapté à la représentation de l'évolution de données dans le temps. Il se présente sous forme de points reliés par une ligne ou un ruban. A chaque série de données correspond une série de points et donc une ligne différente. Selon l'icône choisie, les données du graphe peuvent être cumulées ou non
B)
SÉLECTION DES AXES
L'axe des X est le plus souvent utilisé pour indiquer les périodes. L'axe des Y indique la valeur de chacune des données pour chacune des périodes référencée.
C)
LES SÉRIES
Chaque série associe pour une donnée une valeur à une période.
D)
REPRÉSENTATION
AFFICHER LE CLASSEUR "EXERCICE GRAPHIQUE" FEUILLE "SUD" FAIRE UN GRAPHE INCORPORÉ EN COURBE 3D
EXERCICE
AFFICHER LE CLASSEUR "CROISSANCE" REPRÉSENTER GRAPHIQUEMENT LES DONNÉES DU TABLEAU (VENTES /TEMPÉRATURE EXERCICE
page 44
reproduction réservée
4 LES GRAPHES EN SURFACE 2D/3D Le graphe en surface est une forme particulière du graphe en courbes. Il représente les données en surface et met en évidence l'ampleur d'une évolution. Il est adapté à la représentation de données géographiques ,thermiques…Il existe en 2D et en 3D.
EXERCICE
AFFICHER LE CLASSEUR "EXERCICE GRAPHIQUE" FAIRE UN GRAPHE EN SURFACE 3D SUR UNE NOUVELLE FEUILLE GRAPHIQUE À PARTIR DE LA FEUILLE "FRANCE" SUPPRIMER LA FEUILLE GRAPHIQUE
NOTES
Éditions I O S
page 45
5 LES GRAPHES SECTORIELS 2D/3D Le graphe par secteur se présente sous la forme d'un disque. On l'appelle aussi camembert. Ce disque peut être entier ou éclaté ; les différents secteurs sont alors séparés les uns des autres.
A)
FINALITÉ
Ce graphe permet de mettre en évidence à un instant donné les différentes composantes d'un ensemble sous forme de pourcentages.
B)
LES AXES
Il n'y a plus réellement d'axe dans ce type de graphe. L'axe des abscisses est cependant utilisé pour indiquer les intitulés de chacun des secteurs.
C)
LES SÉRIES
Les données à partir desquelles sont automatiquement calculés les différents pourcentages doivent être indiquées dans le 1er champ d'ordonnées. Les autres champs ne sont pas utilisés. Il est possible de mettre en valeur un secteur particulier en le faisant glisser pour l'éclater.
D)
REPRÉSENTATION
AFFICHER LE CLASSEUR "EXERCICE GRAPHIQUE" FEUILLE "TOTAL FRANCE" FAIRE UN GRAPHE EN SECTEUR 3D REPRÉSENTANT LE TOTAL SUR UNE NOUVELLE FEUILLE GRAPHIQUE NOMMER LA FEUILLE "GRAPHE FRANCE RÉPARTITION" EXERCICE
NOTES
page 46
reproduction réservée
6 LE GRAPHE EN NUAGES DE POINTS Il est identique au graphe en courbes hormis le fait que l'axe des X est ici un axe numérique. Chaque point est alors représenté à l'intersection de ses coordonnées.
A)
FINALITÉ
Le graphique XY permet de mettre en évidence la corrélation existant entre des données.
B)
LES AXES
Chacun des axes correspond à un paramètre déterminé.
C)
LES SÉRIES
A chaque valeur du premier paramètre correspond une valeur du second paramètre. Chaque point du graphe correspond au point d'intersection des valeurs de chacun des deux paramètres. L'axe des abscisses va être utilisé pour les valeurs du premier paramètre. La première série d'ordonnées pour les valeurs du second paramètre (le cas échéant, une autre série pour un autre paramètre).
D)
REPRÉSENTATION
REPARTITION.XLS
Éditions I O S
page 47
AFFICHER LE CLASSEUR "REPARTITION" REPRÉSENTER GRAPHIQUEMENT LES DONNÉES DU TABLEAU EXERCICE
7 LES GRAPHES EN RADAR 2D/3D Le graphe en radar montre l'évolution ou la fréquence des données par rapport à un point central et entre elles. Il est souvent utilisé pour comparer des produits en fonction d'un certain nombre de critères. RADAR.XLS
AFFICHER LE CLASSEUR "RADAR" FAIRE UN GRAPHIQUE METTANT EN ÉVIDENCE LES CARACTÉRISTIQUES DES PRODUITS SUR UNE FEUILLE GRAPHIQUE EXERCICE
page 48
reproduction réservée
8 LES COMBINAISONS DE GRAPHES Plusieurs types de graphes peuvent être combinés. Un axe droit des ordonnées peut être ajouté. Le graphique boursier en est un exemple. BOURSIER.XLS
AFFICHER LE CLASSEUR "BOURSIER" REPRÉSENTER GRAPHIQUEMENT SUR UNE FEUILLE GRAPHIQUE LES VOLUMES ÉCHANGÉS AINSI QUE LES COURS LES PLUS SIGNIFICATIFS EXERCICE
NOTES
Éditions I O S
page 49
E E.. LLA AS STTR RU UC CTTU UR RE ED DU UG GR RA AP PH HIIQ QU UE E Le menu GRAPHIQUE de la barre des menus gère l'intégration de nouveaux éléments et leur mise en forme. PRINCIPES : SÉLECTIONNER L'ÉLÉMENT
<CLIC G> SUR L'ÉLÉMENT
il s'entoure de poignées de sélection - un second <clic g> sélectionne un élément dans un groupe le nom de l'élément est affiché dans la zone de formule. La souris permet de le manipuler ACTIVER LE MENU CONTEXTUEL DE L'ÉLÉMENT
titre
<CLIC D> SUR L'ÉLÉMENT
VENTES FRANCE 2000 panneaux axe des z
marques de données quadrillage
350 300 250 4 éme série de données
200 150
3 éme série de données
100
VENTES
50 2 éme série de données
0 titre
1 ére série de données
axe des X
axe des z
Express
TRIM.1 Messagerie
TRIM.2 TRIM.3 titre axe des X
périodes
Course
TRIM.4
ACTIVITE titre axe des Y
International axe des Y
Un graphique est composé d'un certain nombre d'éléments : LA ZONE DE TRAÇAGE, les panneaux et le plancher qui délimitent un graphique 3D LES SÉRIES DE DONNÉES,
issues d'un tableau, représentées par des marques enrichies
d'étiquettes (1, 2, 3 axes selon le type de graphe), précisés par des titres dont le rapport est défini par une échelle symbolisée par un quadrillage qui permet de situer chacun des points par rapport aux autres et par rapport à l'échelle LES AXES
LES OBJETS GRAPHIQUES
ou texte qui complètent le graphe
NOTES
page 50
reproduction réservée
1 LES TITRES DES AXES ET DU GRAPHIQUE Le graphique peut recevoir un titre, un sous titre et le cas échéant, être accompagné de notes. Il est créé automatiquement dans le graphique original avec le contenu de la cellule située à l'angle haut et gauche de la sélection. Pour le modifier, il suffit d'appeler le menu contextuel. INSERTION DE TITRE : <CLIC D> DANS LA ZONE DE TRAÇAGE (OU MENU PRINCIPAL GRAPHIQUE) PROCÉDURE
SAISIR LES DIFFÉRENTS TITRES
Vous pouvez aussi saisir directement du texte dans la barre de formule et sans sélection préalable CONSEIL
Pour supprimer un titre, le sélectionner puis touche <SUPP> du clavier INFORMATION
NOTES
Éditions I O S
page 51
FORMAT DES TITRES : <CLIC G> SUR LE TITRE DE GRAPHIQUE OU D'AXE PROCÉDURE
<CLIC D> SÉLECTIONNER L'ONGLET ONGLET MOTIFS : BORDURE ET COULEURS ONGLET POLICE : FONTE, TAILLE ET ATTRIBUTS ONGLET ALIGNEMENT : HORIZONTAL, VERTICAL ET ORIENTATION RENSEIGNER LA ZONE DE DIALOGUE POUR VALIDER
La mise en forme de ces titres s'effectue pour chaque titre isolément INFORMATION
AFFICHER LE CLASSEUR "EXERCICE GRAPHIQUES" FEUILLE "TOTAL FRANCE" SUR UNE FEUILLE GRAPHIQUE NOMMÉE "GRAPHIQUE DE SYNTHÈSE", CRÉER UN GRAPHE HISTOGRAMME 3D (OU CÔNES, PYRAMIDES OU CYLINDRES) CRÉER UN TITRE DE GRAPHIQUE ET DES TITRES D'AXE EXERCICE LES METTRE EN FORME
NOTES
page 52
reproduction réservée
2 LES SÉRIES DE DONNÉES Il est aisé de modifier les séries de données, d'en rajouter ou d'en supprimer. MODIFIER UNE SÉRIE : <CLIC G> DANS LA ZONE DE TRAÇAGE OU SUR LES MARQUES D'UNE SÉRIE PROCÉDURE
<CLIC D> ONGLET PLAGE DE DONNÉES POUR MODIFIER LA DÉSIGNATION DES DONNÉES DANS LE TABLEAU ONGLET SÉRIE POUR MODIFIER LES CARACTÉRISTIQUES DES SÉRIES UNE À UNE SÉLECTIONNER LA NOUVELLE ZONE AVEC LA SOURIS POUR VALIDER
la modification est prise en compte par le graphe
Il est aisé de modifier la disposition lignes/colonnes dans l'onglet "plage de données" INFORMATION
NOTES
Éditions I O S
page 53
La présentation de ces mêmes séries peut être modifiée. MODIFIER LE FORMAT D'UNE SÉRIE : <CLIC G> SUR LES MARQUES DE LA SÉRIE À MODIFIER PROCÉDURE
<CLIC D> SÉLECTIONNER L'ONGLET ONGLET MOTIFS : BORDURE ET COULEURS DES MARQUES ONGLET FORME : CÔNES, BARRES, PYRAMIDES… ONGLET ÉTIQUETTES DE DONNÉES : POUR L'AFFICHAGE DE VALEURS OU TEXTE SUR LES MARQUES ONGLET ORDRE DES SÉRIES : POUR MODIFIER L'ORDRE DE PRÉSENTATION DES MARQUES ONGLET OPTIONS : POUR MODIFIER LES CARACTÉRISTIQUES D'INTERVALLE RENSEIGNER LA ZONE DE DIALOGUE POUR VALIDER
les modifications sont prises en compte par le graphe
NOTES
page 54
reproduction réservée
L'ordre d'affichage des marques, représentant les séries dans le graphe, peut être modifié.
PROCÉDURE
MODIFIER L'ORDRE DES SÉRIES : DANS LA MÊME BOITE DE DIALOGUE "FORMAT DE SÉRIE DE DONNÉES" CHOISIR L'ONGLET "ORDRE DES SÉRIES" <DÉPLACER VERS LE HAUT> REMONTE LA SÉRIE DANS L'ORDRE D'AFFICHAGE DES MARQUES <DÉPLACER VERS LE BAS> DESCEND LA SÉRIE DANS L'ORDRE D'AFFICHAGE DES MARQUES POUR VALIDER
DANS LE CLASSEUR "EXERCICE GRAPHIQUES" FEUILLE "GRAPHIQUE DE SYNTHÈSE", MODIFIER LES CARACTÉRISTIQUES DES DONNÉES (PLAGES, ORDRE…) ET LA PRÉSENTATION DES MARQUES (COULEURS, MOTIFS…) EXERCICE
Une série de données peut être ajoutée à un graphe existant. Une série de données, représentée dans un graphique, peut être supprimée de ce dernier. AJOUTER UNE SÉRIE : PROCÉDURE
SÉLECTIONNER LA SÉRIE DANS LE TABLEAU <LA FAIRE GLISSER> DU TABLEAU AU GRAPHE DANS UN GRAPHIQUE INCORPORÉ
ou <LA COPIER> DANS LE PRESSE-PAPIERS <LA COLLER> DANS LE GRAPHE POUR UNE -
EFFACER UNE SÉRIE : <CLIC G> SUR LES MARQUES DE LA SÉRIE À EFFACER <CLIC D> - MENU CONTEXTUEL <EFFACER>
ou <SUPPR>
FEUILLE GRAPHIQUE
NOTES
Éditions I O S
page 55
INFORMATION
Dans un histogramme simple, il est possible de modifier les valeurs d'une donnée en modifiant la marque dans le graphique. Pour ce faire : <CLIC G> sur les marques de la série de données <CLIC G> sur la poignée de sélection au milieu de la marque à modifier <FAIRE GLISSER> la poignée de sélection située en haut de la barre vers le haut ou le bas DANS LE CLASSEUR "EXERCICE GRAPHIQUE" FEUILLE "GRAPHIQUE DE SYNTHÈSE", SUPPRIMER LA SÉRIE "INTERNATIONAL" LA RAJOUTER (ne pas annuler l'opération précédente)
EXERCICE
3 LES ÉTIQUETTES DE DONNÉES Il est possible d'afficher des étiquettes venant préciser les marques. AJOUTER DES ÉTIQUETTES : PROCÉDURE
DANS LA MÊME BOITE DE DIALOGUE "FORMAT DE SÉRIE DE DONNÉES" CHOISIR L'ONGLET "ORDRE DES SÉRIES" ONGLET ÉTIQUETTES DE DONNÉES NOM DE SÉRIE : AFFICHE PRÉS DE LA MARQUE LE TITRE DE LIGNE CORRESPONDANT DU TABLEAU NOM DE CATÉGORIE: AFFICHE PRÉS DE LA MARQUE LE TITRE DE COLONNE CORRESPONDANT DU TABLEAU VALEURS : AFFICHE PRÉS DE LA MARQUE LE CHIFFRE CORRESPONDANT DU TABLEAU POUR VALIDER
NOTES
page 56
reproduction réservée
<CLIC G> permet de sélectionner l'étiquette - un second <CLIC G> permet d'en modifier le contenu INFORMATION
Pour mettre en forme les étiquettes de données METTRE EN FORME DES ÉTIQUETTES : <CLIC G> SUR UNE ÉTIQUETTE PROCÉDURE
<CLIC D> SÉLECTIONNER L'ONGLET MOTIFS : BORDURE ET COULEURS POLICE : FONTE, TAILLE ET ATTRIBUTS NOMBRE : FORMAT DES NOMBRES ALIGNEMENT : HORIZONTAL, VERTICAL ET ORIENTATION POUR VALIDER
pour déplacer une étiquette la <FAIRE GLISSER>
DANS LE CLASSEUR "EXERCICE GRAPHIQUES" FEUILLE "GRAPHIQUE DE SYNTHÈSE", AFFICHER DES ÉTIQUETTES DONNANT LES DIFFÉRENTES VALEURS DE CHAQUE MARQUE DE SÉRIE METTRE EN FORME CES ÉTIQUETTES EXERCICE
NOTES
Éditions I O S
page 57
4 LES AXES Les axes des graphiques représentent chacun un aspect différent des données de la série et le cas échéant l'échelle de valeurs liée. LES GRAPHES 3D (HISTO 3D) ONT TROIS AXES : L'AXE DES X, L'AXE DES Y ET L'AXE DES Z les séries de données sont indiquées le long de l'axe Y les abscisses le long de l'axe des X les ordonnées le long de l'axe des Z vertical LES GRAPHES 2D ( Y COMPRIS À MARQUES 3D) ONT DEUX AXES : L'AXE DES X ET L'AXE DES Y
(hormis le graphe sectoriel et le mixte) les ordonnées sont tracées le long de l'axe Y vertical les abscisses le long d'un axe des X horizontal (hors sectoriel et barres)
INFORMATION
dans les graphes à barres, l'axe des X est vertical et l'axe des Y horizontal dans les graphes à nuage de points, l'axe des X est numérique (2ème coord des points) un deuxième axe des ordonnées peut être rajouté dans les graphiques mixtes (boursier)
FORMAT DES AXES : <CLIC G> SUR L'AXE son nom s'inscrit dans la barre de formule PROCÉDURE
<CLIC D> SÉLECTIONNER L'ONGLET MOTIFS : BORDURE ET COULEURS ÉCHELLE : MARQUES NUMÉROTÉES LE LONG DE L'AXE POLICE : FONTE, TAILLE ET ATTRIBUTS NOMBRES : FORMAT DES NOMBRES ALIGNEMENT : HORIZONTAL, VERTICAL ET ORIENTATION POUR VALIDER
NOTES
page 58
reproduction réservée
L'échelle est un ensemble de marques numérotées mesurant les valeurs prises le long d'un axe. Elle est déterminée automatiquement par EXCEL. Néanmoins, il est possible de déterminer manuellement les échelles des axes X et Y ; il est alors nécessaire d'en indiquer les valeurs minimales et maximales ainsi que l'intervalle (unité principale). ÉCHELLE DES AXES : PROCÉDURE
DANS LA MÊME BOITE DE DIALOGUE "FORMAT D'AXE" CHOISIR L'ONGLET "ÉCHELLE" DÉSACTIVER LA CASE À COCHER "AUTOMATIQUE" POUR LES VALEURS À MODIFIER SAISIR LA NOUVELLE VALEUR POUR VALIDER
OPTIONS MINIMUM :
plus petite valeur de l'échelle prise en compte dans le graphe
MAXIMUM:
plus grande valeur de l'échelle prise en compte dans le graphe
UNITÉ PRINCIPALE :
intervalle entre deux graduations principales intervalle entre graduation principale et secondaire
UNITÉ SECONDAIRE : PLANCHER
position verticale du plancher du graphique (pas obligatoirement en bas)
la valeur de l'unité secondaire n'est pas affichée - vérifier l'activation des marques et des étiquettes de graduation INFORMATION
DANS LE CLASSEUR "EXERCICE GRAPHIQUE" FEUILLE "GRAPHIQUE DE SYNTHÈSE", MODIFIER LES CARACTÉRISTIQUES DES AXES
EXERCICE
NOTES
Éditions I O S
page 59
L'affichage d'un quadrillage peut faciliter la lecture du graphe. QUADRILLAGE DES AXES : <CLIC D> DANS LA ZONE DE TRAÇAGE (OU MENU PRINCIPAL GRAPHIQUE) PROCÉDURE
CHOISIR L'ONGLET QUADRILLAGE ACTIVER LES CASES À COCHER CORRESPONDANT AUX QUADRILLAGES À AFFICHER <OK> POUR VALIDER
DANS LE CLASSEUR "EXERCICE GRAPHIQUE" FEUILLE "GRAPHIQUE DE SYNTHÈSE", AFFICHER LES QUADRILLAGES PRINCIPAUX DES AXES
EXERCICE
5 LA LÉGENDE Les légendes reproduisent le symbole (motifs, lignes, couleurs) affecté à une série de données ainsi qu'un court texte la précisant. Elles peuvent être affichées n' importe où dans le graphique.
PROCÉDURE
CLIC G> SUR DANS LA BARRE GRAPHIQUE POUR AFFICHER IDEM POUR EFFACER <FAIRE GLISSER> LA LÉGENDE AVEC LA SOURIS POUR LA POSITIONNER <FAIRE GLISSER> UN ANGLE OU UN BORD POUR MODIFIER LA FORME DANS LE CLASSEUR "EXERCICE GRAPHIQUE" FEUILLE "GRAPHIQUE DE SYNTHÈSE", AFFICHER PUIS ENLEVER LA LÉGENDE, LA DÉPLACER ET MODIFIER SA FORME
EXERCICE
NOTES
page 60
reproduction réservée
6 L'ORIENTATION Les graphiques en trois dimensions peuvent être présentés sous l'angle désiré afin de mettre en valeur les éléments les plus significatifs. ORIENTATION : <CLIC G> DANS LA ZONE DE TRAÇAGE PROCÉDURE
<CLIC D> RENSEIGNER LA BOÎTE DE DIALOGUE <ATTRIBUER> ATTRIBUE AU FUR ET À MESURE AU GRAPHE LES PARAMÈTRES CHOISIS <OK> POUR VALIDER
il est possible d'effectuer des rotations de la vue du graphique dans toutes les directions
Prendre le coin le plus éloigné de la zone de traçage et le faire glisser pour faire effectuer une rotation au graphique – il est nécessaire de s'y prendre en deux fois CONSEIL
DANS LE CLASSEUR "EXERCICE GRAPHIQUE" FEUILLE "GRAPHIQUE DE SYNTHÈSE", MODIFIER L'ORIENTATION 3D AVEC LA SOURIS EXERCICE
7 LES OBJETS GRAPHIQUES Des objets graphiques, c'est à dire les traits, courbes, rectangles, zone texte ..., peuvent être créés avec la barre d'outils et insérés dans la feuille de travail ou le graphique.
PROCÉDURE
BARRE D'OUTILS DESSIN : <CLIC D> DANS UNE BARRE D'OUTILS <CLIC G> SUR DESSIN PRINCIPES : <CLIC G> sur l'objet pour le sélectionner <FAIRE GLISSER> l'objet pour le déplacer <FAIRE GLISSER> les poignées de sélection pour modifier la forme <DOUBLE CLIC> sur l'objet pour le modifier <CLIC D> sur l'objet pour appeler son menu contextuel <MAJ> pour sélectionner plusieurs objets (et non <CTRL>)
Éditions I O S
page 61
QUELQUES OUTILS : sélectionne un objet dessine un trait (<double clic> sur le trait pour en modifier la forme) dessine une flèche (<double clic> sur la flèche pour en modifier la forme) dessine un rectangle vide ou plein (<maj> enfoncé dessine un carré) dessine une ellipse vide ou pleine (<maj> enfoncé dessine un cercle) insère une zone de texte insère un objet Wordart Pour sélectionner un objet, pointer dessus et <CLIC G> lorsque le pointeur change de forme. Pour le mettre en forme (hors bloc de texte), <DOUBLE CLIC> sur l'objet. INFORMATION
8 LA BARRE D'OUTILS GRAPHIQUE La barre d'outils graphique permet d'exécuter simplement la plupart des opérations. Sélectionne et Indique le nom de la zone sélectionnée
Affiche ou masque la légende
Modifie le format de la zone sélectionnée
Modifie la disposition lignes/colonnes
Modifie le type de graphique
Pour agrémenter la présentation, il est possible d'insérer une image comme fond de graphique ou comme marque de série de données (selon la sélection) CONSEIL
OUVRIR LE CLASSEUR "GRAPHES.XLS" FAIRE LES DIFFÉRENTS GRAPHIQUES DEMANDÉS DANS LES ZONES DE COMMENTAIRES : REPRÉSENTER LES DONNÉES PAR UN GRAPHE EN CYLINDRE 3D AVEC TITRES...POUR LA RÉGION EST REPRÉSENTER LES DONNÉES PAR UN GRAPHE EN PYRAMIDE AVEC TITRES...POUR LA RÉGION NORD EXERCICE REPRÉSENTER LES DONNÉES PAR UN GRAPHE EN CÔNES 3D AVEC TITRES...POUR LA RÉGION OUEST REPRÉSENTER LES DONNÉES PAR UN GRAPHE EN AIRES 3D AVEC TITRES...POUR LA RÉGION SUD REPRÉSENTER LES TOTAUX PAR UN GRAPHE EN SECTEUR 3D AVEC TITRES ET % POUR LE TOTAL LES ENRICHIR D'ÉTIQUETTES, DE LÉGENDES, DE TITRES… MODIFIER LES POLICES DES TEXTES, LEURS COULEURS, LEURS CARACTÉRISTIQUES MODIFIER LES CARACTÉRISTIQUES DES MARQUES PUIS MODIFIER LES CARACTÉRISTIQUES DES AXES INSÉRER DES OBJETS GRAPHIQUES
OUVRIR LE CLASSEUR CONGÉS REPRÉSENTER GRAPHIQUEMENT LES CONGÉS PRIS PAR LES SALARIÉS EN JUIN COMPARER AVEC LE CLASSEUR "CONGES FINI.XLS" EXERCICE
NOTES
page 62
reproduction réservée
VII. LES FONCTIONS Les fonctions sont des formules intégrées qui permettent de réaliser des calculs mathématiques, financiers, logiques...etc. sur les données saisies dans EXCEL (nombres, caractères...etc.). L'intégration de la formule évite d'avoir à détailler sa formulation puisqu'il suffit alors de n'indiquer que ses éléments variables. Les formules les plus couramment utilisées dans leur domaine existent sous forme de fonction.
A A.. G GÉ ÉN NÉ ÉR RA ALLIITTÉ ÉS S Ne seront étudiées dans le détail que les fonctions à même d'être souvent utilisées dans une organisation ; les fonctions plus spécifiques (trigonométriques, de chaîne...) ne seront qu'évoquées.
1 LA SYNTAXE Une fonction s'exprime toujours sous la forme :
=FONCTION(ARG1;ARG2;ARGN) Dans lequel : =
ÉGAL
indique à EXCEL qu'une fonction suit ;
(...) PARENTHÈSES fonction ;
les parenthèses ouvrantes et fermées encadrent les arguments de la
ARG1 ARG2...ARGN
figurent les différentes variables dont la valeur est à renseigner pour que la fonction puisse s'exécuter ; ;
POINT VIRGULE
caractères séparateurs indispensables.
Une fonction peut être écrite telle que ci-dessus ou insérée à partir de la barre de formule ou du menu INFORMATION
INSÉRER UNE FONCTION : <CLIC G> SUR DANS LA BARRE DE FORMULE PROCÉDURE
OU MENU INSERTION SÉLECTIONNER UNE CATÉGORIE DE FONCTION SÉLECTIONNER LA FONCTION SAISIR OU DÉSIGNER LES DIVERS ARGUMENTS"
NOTES
Éditions I O S
page 63
2 LES TYPES DE FONCTION Les fonctions sont différentes selon les domaines qu'elles recouvrent. FINANCIER
(investissements, amortissements, emprunts) ;
MATHÉMATIQUE STATISTIQUE
(général ou trigonométrique) ;
(général ou base de données) ;
LOGIQUE.
Elles peuvent être directement liées au contenu des cellules, leur champ d'action est alors : L'INFORMATION,
la consultation...
LA RECHERCHE,
le remplacement... LA DATE, l'heure...
3 LES ARGUMENTS Les arguments donnent à la fonction les informations nécessaires à la détermination et à l'exécution des calculs. Ils sont entre parenthèses et séparés par un point-virgule. Ils peuvent être de quatre catégories selon les fonctions: (nombre, formule ) TEXTE (suite de caractères alphanumériques entre guillemets) NOMBRE
VALEURS LOGIQUES
(utilisant les opérateurs logiques)
VALEURS D'ERREUR
Ces catégories d'argument peuvent être indirectement induites au moyen de : références (à une cellule, une plage, des plages) absolues, relatives ou mixtes noms (d'une plage de cellules) formules Matrices
NOTES
page 64
reproduction réservée
4 LES RÈGLES D'UTILISATION débuter par le signe = (égal) ; mettre les arguments entre parenthèses ; ne pas laisser d'espaces ; utiliser le point-virgule comme séparateur. Si des fonctions ne sont pas disponibles dans Excel ou si leur résultat est "erreur", aller dans le menu outils, macros complémentaires et activer les options nécessaires (en particulier ) et les fonctions seront disponibles ATTENTION
B B.. LLE ES S FFO ON NC CTTIIO ON NS S FFIIN NA AN NC CIIÈ ÈR RE ES S Les fonctions financières sont très utiles dans les services administratifs, comptables ou financiers des entreprises.
1 LES INVESTISSEMENTS Des fonctions spécifiques permettent de calculer des ratios très précieux dans l'évaluation de la rentabilité prévisible d'investissements. (VOIR FONCFIN.XLS-TAUX-LN-NPM)) FONCFIN.XLS - TAUX-LN-NPM
NOTES
Éditions I O S
page 65
A)
=NPM(TAUX;VERSEMENT;VAL._ACTUELLE;VAL._CAPITALISÉE;TYPE)
Cette fonction détermine le nombre de périodes nécessaires (années, trimestres, mois...) pour qu'une série de versements constants atteigne, en fonction d'un taux d'intérêt donné, une valeur capitalisée spécifiée. TYPE PREND LA VALEUR 0 POUR DES VERSEMENTS FIN DE PÉRIODE ET 1 POUR DES VERSEMENTS DÉBUT DE PÉRIODE
INFORMATION
B) =TAUX(NB_PÉRIODES;VERSEMENT;VAL.ACTUALISÉE;VAL.CAPITALISÉE;T YPE) Cette fonction calcule le taux d'intérêt qui permet à une somme investie (valeur actualisée) d'atteindre une valeur donnée (valeur capitalisée) en un nombre de périodes définis.
C)
=TRI(RESSOURCES;TAUX ESTIMÉ)
Cette fonction calcule le T.R.I (Taux de Rendement Interne) d'un investissement à partir des ressources nettes qu'il génère. Le T.R.I. est la valeur prise par le taux d'actualisation (ARG2 : TAUX ESTIME) qui rend le total des ressources nettes générées pendant la période de référence (ARG1 : RESSOURCES) égal au montant de l'investissement net initial. Il présente l'avantage d'être un ratio indépendant de l'environnement qui ne prend en compte que des données propres à l'investissement considéré ; il permet d'avoir un classement comparatif des investissements les uns par rapport aux autres et ce sans facteur exogène. Il présente l'inconvénient de ne pas permettre de faire de distinction en fonction de l'importance des capitaux investis ni en fonction des périodes de vie des investissements. Plus le T.R.I. est élevé, plus l'investissement est rentable. (VOIR FONCFIN.XLS-TRI-VAN)
NOTES
page 66
reproduction réservée
FONCFIN.XLS - TRI-VAN
D)
=TRIM(VALEURS;TAUX FINANCEMT CF;TAUX PLACEMENT CF)
Cette fonction calcule le Taux de Rendement Interne Modifié d'une série de Cash-flows périodiques. Il prend en compte le coût du financement des cash-flows et l'intérêt perçu sur leur placement.
E)
=VA(TAUX;NBRE_PÉRIODES;VERSEMENT;TYPE)
Cette fonction permet de connaître la valeur, en francs actuels, d'une série de versements constants placés à un taux d'intérêt défini. Elle ramène une somme en francs futurs à sa valeur actuelle à des fins de comparaison. Elle peut ainsi permettre de faire la comparaison entre une rente périodique et une somme cash. (VOIR FONCFIN.XLS-VA) FONCFIN.XLS - VA
NOTES
Éditions I O S
page 67
F)
=VAN(TAUX;RESSOURCES)
Cette fonction permet de calculer la valeur actualisée nette des ressources générées par un investissement, ceci à un taux d'intérêt constant (ou encore la valeur actualisée de l'investissement lui-même en incluant la dépense initiale). (VOIR FONCFIN.XLS-TRI-VAN)
G)
=VC(TAUX;NBRE_PÉRIODES;VERSEMENT;VALEUR_ACTUELLE;TYPE)
Cette fonction calcule la valeur capitalisée d'une série de versements égaux effectués à un taux d'intérêt constant pendant n périodes. Type prend les mêmes valeurs que précédemment. UNE SOCIÉTÉ ÉPARGNE TOUS LES ANS 5000 € VERSÉS EN UNE SEULE FOIS EN DÉBUT DE PÉRIODE ET PLACÉS À UN TAUX FIXE DE 4,3 %. CALCULER LA VALEUR CAPITALISÉE DE CES VERSEMENTS AU BOUT DE 12 ANS IDEM MAIS AVEC DES VERSEMENTS FIN DE PÉRIODE. EXERCICE CALCULER LA VALEUR ACTUALISÉE SI AU LIEU DE VERSEMENTS CONSTANTS, L’ENSEMBLE DU CAPITAL ÉTAIT INVESTI EN UNE SEULE FOIS
NOTES
page 68
reproduction réservée
2 LES EMPRUNTS Les emprunts, de la même façon que les investissements, peuvent être calculés avec les fonctions EXCEL. (VOIR FONCFIN.XLS - VPM-INTPER-PRINCPER) FONCFIN.XLS - VPM-INTPER-PRINCPER
A)
=INTPER(TAUX;NOPÉRIODE;PÉRIODES;VAL.ACTUALISÉE;VAL.CAPITALI SÉE;TYPE)
Cette fonction permet de calculer le montant des intérêts payés sur une période donnée dans un plan de remboursement d'emprunt.
B)
=PRINCPER(TAUX;NO_PÉRIODE;PÉRIODES;VALEUR_ACTUALISÉE;VALE UR_CAPITALISÉE;TYPE)
Cette fonction permet de calculer le montant du capital remboursé sur une période donnée dans un plan de remboursement d'emprunt.
C)
=VPM(TAUX;PÉRIODES;VAL.ACTUALISÉE;VAL.CAPITALISÉE;TYPE)
Cette fonction permet de calculer les versements nécessaires au remboursement d'un emprunt en fonction du nombre de périodes de remboursement et du taux.
NOTES
Éditions I O S
page 69
VOUS EMPRUNTEZ POUR ACHETER UNE MAISON LA SOMME DE 150 000 € AU TAUX DE 4,85 % PAR AN, ASSURANCES COMPRISES ET CE SUR 18 ANS. SACHANT QUE LES INTÉRÊTS D’UNE MENSUALITÉ S’EXERCENT SUR LE CAPITAL RESTANT DU (MOINS CELUI DÉJÀ REMBOURSÉ), FAIRE UN TABLEAU DE REMBOURSEMENT MENSUEL DÉCOMPOSANT EXERCICE INTÉRÊTS ET CAPITAL CALCULER LA MENSUALITÉ DE REMBOURSEMENT AVEC LA FONCTION EXCEL APPROPRIÉE
3 LES AMORTISSEMENTS La notion d'amortissement de EXCEL est une notion économique qui prend en compte la valeur de revente des immobilisations. Cependant, leur utilisation selon les préceptes du plan comptable général, amène à des calculs d'amortissement comptable.
INFORMATION
L'amortissement comptable doit, en FRANCE, se pratiquer sur la totalité de la valeur d'achat, indépendamment d'une éventuelle valeur de revente et en prenant en compte, non pas la durée d'utilisation mais la durée légale d'amortissement. Les fonctions suivantes vont donc surtout trouver leur utilité dans les services de gestion pour effectuer des simulations, des prévisions ou encore pour obtenir une comptabilité analytique plus proche de la réalité économique que la comptabilité générale AMOS.XLS
A)
=AMORLIN(COÛT;VALEUR_RÉSIDUELLE_DURÉE)
Cette fonction calcule la charge d'amortissement d'une immobilisation selon le mode linéaire.
B)
=DB(COÛT;VALEUR_RÉCUPÉRATION;DURÉE;PÉRIODE;MOIS)
Cette fonction calcule, pour une période donnée, la dépréciation d'un amortissement selon la méthode américaine de l'amortissement décroissant.
NOTES
page 70
reproduction réservée
C)
=DDB(COÛT;VALEUR_RÉCUPÉRATION;DURÉE;PÉRIODE;FACTEUR)
Cette fonction calcule, pour une période donnée, l'annuité d'amortissement dégressif d'une immobilisation en fonction de sa valeur d'achat, de son éventuelle valeur de revente et de sa durée d'amortissement. FACTEUR EST LE TAUX AUQUEL LE SOLDE À AMORTIR DÉCROÎT. S'IL N'EST PAS INDIQUÉ, 2 EST PRIS PAR DÉFAUT INFORMATION
D)
=SYD(COÛT;VALEUR-RECUPÉRATION;DURÉE;PÉRIODE)
Cette fonction calcule, pour une période donnée, la charge d'amortissement d'une immobilisation selon le mode "Sum of years digit américain".
E)
=VDB(COÛT;VAL.RÉCUPÉRATION;DURÉE;PÉR_DÉBUT; PÉR_FIN)
Cette fonction calcule l'amortissement d'un bien selon la méthode Variable Declining Balance.
UNE ENTREPRISE ACHÈTE LES BIENS SUIVANTS : UN TERRAIN LE 07 MAI 1990 POUR 600 000 € ; ELLE Y FAIT CONSTRUIRE DES LOCAUX LE 11 AOUT 1995 POUR 500 000 € UNE MACHINE OUTIL LE 22 DÉCEMBRE 1998 POUR 450 000 € EXERCICE UNE SECONDE MACHINE OUTIL LE 19 OCTOBRE 1999 POUR 550 000 € DU MOBILIER LE 22 FÉVRIER 2002 POUR 15 000€ DES MICRO-ORDINATEURS LE 05 AVRIL 2004 POUR 75 000 € DES LOGICIELS LE 29 DÉCEMBRE 2005 POUR 15 000 € CALCULER LA VALEUR ANNUELLE DES AMORTISSEMENTS DE CHAQUE BIEN
NOTES
Éditions I O S
page 71
NOTES
page 72
reproduction réservée
VIII. DOSSIER PÉDAGOGIQUE A A.. N NO OM M
OUVRIR LE CLASSEUR NOM NOMMER LES DONNÉES EN FONCTION DES TITRES DE LIGNES ET DE COLONNES APPLIQUER LES NOMS AUX FORMULES DANS REGION "EST", CRÉER UN NOM "COEFF" (VALEUR :8%) CORRESPONDANT A UN COEFFICIENT EXERCICE PERMETTANT D'OBTENIR LES PRÉVISIONS 2006 ET CALCULER CES PRÉVISIONS ENREGISTRER SOUS LE NOM "SIGMA" LA SOMME DES DONNÉES DE TOUTES LES FEUILLES (3636) CALCULER EN A8 DE CHAQUE FEUILLE, LE POURCENTAGE REPRÉSENTE PAR CHAQUE RÉGION PAR RAPPORT A CE TOTAL SUPPRIMER LE "COEFF" CREE EN REGION EST CRÉER UN NOM UNIQUE : "COEFF" MAIS AVEC DES VALEURS DIFFÉRENTES POUR CHACUNE DES FEUILLES CALCULER LES PRÉVISIONS DE CHACUNE DES FEUILLES AVEC CE COEFFICIENT FAIRE LA CONSOLIDATION DANS TOTAL FRANCE DES VALEURS ET DES PRÉVISIONS
B B.. P PLLA AN N..X XLLS S
CRÉER UN PLAN AUTOMATIQUE DANS CE TABLEAU PERMETTANT D'AFFICHER AU CHOIX LE DÉTAIL ET/OU LES TOTAUX DANS LA FEUILLE "EXERCICE", MODIFIER LES STYLES DES TOTAUX DE LIGNE ET DE COLONNE DU PLAN EXERCICE
NOTES
Éditions I O S
page 73
C C.. N NO OB BIILLA AN N11..X XLLS S
EXERCICE
FAIRE LES SOUS-TOTAUX, TOTAUX ET CALCULS DE CHACUNE DES FEUILLES CRÉER UN PLAN DANS CHACUNE DES FEUILLES POUR N'AFFICHER QUE LES LIGNES DE SYNTHÈSE RENOMMER CHACUNE DES FEUILLES EN FONCTION DU CONTENU DE LA CELLULE F1 FAIRE SUR CHACUNE DES FEUILLES UN GRAPHIQUE REPRÉSENTANT LA STRUCTURE SOUS FORME D'HISTOGRAMME CUMULÉ REPRÉSENTER L'ÉVOLUTION (CA, RÉSULTAT BRUT ET NET) SUR UNE FEUILLE GRAPHIQUE AVEC UN GRAPHIQUE À CÔNE 3D
NOTES
page 74
reproduction réservée
D D.. C CO ON NP PO OS S11..X XLLS S
EFFECTUER LA CONSOLIDATION DES FEUILLES DU CLASSEUR
EXERCICE
E E.. C CO ON NS SP PO OS S22..X XLLS S CONSOLIDER LES CLASSEURS "CONSEST", "CONSNORD", "CONSUD" ET "CONSOUEST
EXERCICE
FF.. C CO ON NS SC CA ATT11..X XLLS S CONSOLIDER LES DONNÉES DES FICHIERS CONSSUD, CONSOUEST, CONSNORD ET CONSEST EN CONSERVANT UNE LIAISON AVEC LES FICHIERS D'ORIGINE. REMARQUE : LES CATÉGORIES D'OUTILS SONT SUR DES LIGNES DIFFÉRENTES EXERCICE
G G.. C CO ON NS SC CA ATT22..X XLLS S CONSOLIDER LES DONNÉES DES FICHIERS CONSSUD, CONSOUEST, CONSNORD ET CONSEST EN CONSERVANT UNE LIAISON AVEC LES FICHIERS D'ORIGINE. REMARQUE : LES TABLEAUX SONT À DES ENDROITS DIFFÉRENTS DE LA FEUILLE EXERCICE
H H.. C CO ON NS SC CA ATT33..X XLLS S CONSOLIDER LES DONNÉES DES FEUILLES SUD, OUEST, NORD ET EST. REMARQUE : LES CATÉGORIES D'OUTILS SONT SUR DES LIGNES DIFFÉRENTES ET LES TABLEAUX SONT À DES ENDROITS DIFFÉRENTS DE LA FEUILLE EXERCICE
NOTES
Éditions I O S
page 75
II.. LLIIA AIIS SO ON NS S..X XLLS S
EXERCICE
FAIRE UN TABLEAU DE SYNTHÈSE À PARTIR DU FICHIER VENTES.XLS (DOSSIER LIAISONS) INDIQUER EN LIGNE CHACUNE DES RÉGIONS INDIQUER EN COLONNE CHACUNE DES ACTIVITÉS POUR CHAQUE CELLULE DE VALEUR, FAIRE LA SOMME DE CHAQUE CELLULE DE CHACUN DES TABLEAUX RÉGIONAUX CONSERVER DES LIAISONS AVEC CHACUN DE CES TABLEAUX AFIN QUE LE TABLEAU DE SYNTHÈSE SOIT AUTOMATIQUEMENT MIS À JOUR MODIFIER LES TABLEAUX D'ORIGINE VÉRIFIER LA MISE À JOUR DANS LE TABLEAU DE SYNTHÈSE NEUTRALISER LA MISE À JOUR MODIFIER LES TABLEAUX D'ORIGINE METTRE À JOUR ET ANNULER LA NEUTRALISATION REPRÉSENTER LE TABLEAU DE SYNTHÈSE PAR UN GRAPHE EN AIRE 3D
NOTES
page 76
reproduction réservée
JJ.. V VE EN NTTE ES S..X XLLS S
OUVRIR WORD OUVRIR DANS EXCEL LE DOCUMENT "VENTES.XLS" (DOSSIER LIAISONS) COPIER LA SYNTHÈSE "TOTAL FRANCE" DANS LE DOCUMENT W ORD EN MAINTENANT UNE LIAISON EXERCICE
K K.. V VA ALLC CIIB B..X XLLS S
SACHANT QUE LE SALAIRE NET EST ÉGAL AU SALAIRE BRUT MOINS LES CHARGES SOCIALES SALARIALES DÉTERMINER LE SALAIRE BRUT PERMETTANT D'OBTENIR UN SALAIRE NET DE 1500 € POUR 150 H DÉTERMINER LE NOMBRE D'HEURES DE TRAVAIL NÉCESSAIRE POUR OBTENIR UN SALAIRE NET DE 1500 € POUR A PARTIR D'UN SALAIRE BRUT DE 2500 € EXERCICE ENREGISTRER LES SOLUTIONS AUX 2 PROBLÈMES POSES CI-DESSUS EN TANT QUE SCENARIOS SOIT : 1 -DÉTERMINER LE SALAIRE BRUT PERMETTANT D'OBTENIR UN SALAIRE NET DE 1500 € POUR 150 H 2 - DÉTERMINER LE NOMBRE D'HEURES DE TRAVAIL NÉCESSAIRE POUR OBTENIR UN SALAIRE NET DE 1500 € POUR A PARTIR D'UN SALAIRE BRUT DE 2500 € AFFICHER LA SYNTHÈSE DES SCENARIOS
Éditions I O S
page 77
LL.. P PU UB B..X XLLS S PUB.XLS - TABLEAU
EXERCICE
LE CLASSEUR PUB.XLS EST UN TABLEAU DE GESTION D'ENTREPRISE. DANS CE TABLEAU, LE MONTANT DES INVESTISSEMENTS PUBLICITAIRES DÉTERMINE, PAR LE BIAIS D'UNE FORMULE, LES UNITÉS VENDUES. LE PROBLÈME EST DONC DE CALCULER LE BUDGET PUBLICITAIRE QUI PERMET DE MAXIMISER LE RÉSULTAT DE L'ENTREPRISE
NOTES
page 78
reproduction réservée
M M.. E ELLE EC CTTR RO ON N..X XLLS S
EXERCICE
UN GROUPE POSSÈDE 3 USINES DE FABRICATION DE PIÈCES DÉTACHÉES (USINE1, USINE2, USINE3) CHACUNE DE CES USINES EST À MÊME DE PRODUIRE DES CHÂSSIS, DES TUBES VIDÉO, DES CÔNES DE HAUT PARLEUR, DES ALIMENTATIONS ET DE L'ÉLECTRONIQUE UN COEFFICIENT DE PRODUCTION DÉTERMINE LA CAPACITÉ DE PRODUCTION DE L'USINE (400, 500, 600) LES PRÉVISIONS DE PRODUCTION SONT CALCULÉES EN % DU COEFFICIENT DE PRODUCTION (43 % POUR LES CHÂSSIS, 22% POUR LES TUBES, 85% POUR LES CÔNES HP, 43 % POUR LES ALIMENTATIONS ET 64% POUR L'ÉLECTRONIQUE) CELA QU'ELLE QUE SOIT L'USINE LA FABRICATION RÉELLE EST UN % DE LA FABRICATION PRÉVUE (95 % POUR CHACUNE DES USINES) LES REJETS SONT DE 1% DE LA FABRICATION UNE USINE DE MONTAGE UTILISE CES PIÈCES DÉTACHÉES POUR FABRIQUER 3 TYPES DE PRODUIT FINI : DES TÉLÉVISIONS, DES CHAINES STÉRÉO ET DES HAUTS PARLEURS LE NOMBRE DE PIÈCES DÉTACHÉES NÉCESSAIRE POUR FABRIQUER UN TYPE DE PRODUIT EST DIFFÉRENT SELON LES PRODUITS : TÉLÉVISION, CHAÎNE STÉRÉO, HAUT-PARLEURS
LE NOMBRE DE PRODUITS EST LIMITÉ PAR LA QUANTITÉ DE PIÈCES FABRIQUÉES DANS LES 3 USINES LE PRIX DE VENTE DE CHACUN DES PRODUITS EST LE SUIVANT : TÉLÉ : 5000 €, CHAINE : 3000 €, HP : 1200 € LE PROFIT EST CALCULÉ EN MULTIPLIANT CE PRIX PAR UN COEFFICIENT DE MARGE ET PAR LE NOMBRE DE PRODUITS FABRIQUÉS LES COEFFICIENTS DE MARGES SONT SELON LES PRODUITS : TÉLÉ : 60%, CHAINE : 40%, HP : 30% LE BUT DU JEU EST DE MAXIMISER LE PROFIT GLOBAL EN FONCTION DES COEFFICIENTS DE MARGE PAR PRODUIT EN FONCTION DU NOMBRE DE CHACUN DES PRODUITS FABRIQUÉS DANS LA LIMITE DES CAPACITÉS DE PRODUCTION DE CHACUNE DES USINES ET EN OPTIMISANT LA RÉPARTITION DES PIÈCES DÉTACHÉES ENTRE LES PRODUITS FAIRE UN GRAPHIQUE SUR SA PROPRE FEUILLE COMPARANT L'INVENTAIRE ET L'UTILISATION DE CHAQUE ARTICLE DE BASE ENREGISTRER LES DIFFÉRENTS CAS DE FIGURE (ORIGINE, SOLVEUR…) COMME SCÉNARIOS FAIRE LA SYNTHÈSE DES SCÉNARIOS ET LA REPRÉSENTER SUR UNE FEUILLE GRAPHIQUE
Éditions I O S
page 79
N N.. B BIILLA AN N22..X XLLS S
AFFICHER LA FEUILLE "RÉSULTAT" SÉLECTIONNER LES DEUX PREMIÈRES LIGNES DE DONNÉES ET LEURS LIBELLES CRÉER UN OBJET GRAPHIQUE HISTOGRAMME 3D DANS LA FEUILLE DE TRAVAIL- LE POSITIONNER SUPPRIMER LE GRAPHIQUE INCORPORÉ EXERCICE SÉLECTIONNER LES LIGNES CHIFFRE 'AFFAIRES ,CHARGES, AMORTISSEMENT, FRAIS FINANCIERS CRÉER UNE FEUILLE GRAPHIQUE AVEC UN GRAPHIQUE À PYRAMIDE 3D RENOMMER LA FEUILLE "STRUCTURE" AFFICHER LA FEUILLE "RESULTAT" DU CLASSEUR "BILAN2" SÉLECTIONNER LES LIGNES DE DONNÉES "CHIFFRE D'AFFAIRES", "RESULTAT BRUT" ET "RESULTAT NET" ET LEURS LIBELLES CRÉER UNE FEUILLE GRAPHIQUE AVEC UN GRAPHE EN CYLINDRE 3D PERSONNALISER LE GRAPHIQUE ET L'AJOUTER AUX TYPES PERSONNALISES FERMER LE CLASSEUR
page 80
reproduction réservée
O O.. E EX XE ER RC CIIC CE EG GR RA AP PH HIIQ QU UE E
AFFICHER LE CLASSEUR "EXERCICE GRAPHIQUES" FEUILLE "TOTAL FRANCE" SUR UNE FEUILLE GRAPHIQUE QUE VOUS NOMMEREZ "GRAPHIQUE DE SYNTHÈSE", CRÉER UN GRAPHE HISTOGRAMME 3D (OU CÔNES, PYRAMIDES OU CYLINDRES) CRÉER UN TITRE DE GRAPHIQUE ET DES TITRES D'AXE PUIS LES METTRE EN FORME EXERCICE MODIFIER LES CARACTÉRISTIQUES DES DONNÉES (PLAGES, ORDRE…) ET LA PRÉSENTATION DES MARQUES (COULEURS, MOTIFS…) SUPPRIMER LA SÉRIE "INTERNATIONAL" LA RAJOUTER (NE PAS ANNULER L'OPÉRATION PRÉCÉDENTE AFFICHER DES ÉTIQUETTES DONNANT LES DIFFÉRENTES VALEURS DE CHAQUE MARQUE DE SÉRIE METTRE EN FORME CES ÉTIQUETTES MODIFIER LES CARACTÉRISTIQUES DES AXES AFFICHER LES QUADRILLAGES PRINCIPAUX DES AXES AFFICHER PUIS ENLEVER LA LÉGENDE, LA DÉPLACER ET MODIFIER SA FORME MODIFIER L'ORIENTATION 3D AVEC LA SOURIS
NOTES
Éditions I O S
page 81
P P.. C CR RO OIIS SS SA AN NC CE E..X XLLS S
REPRÉSENTER GRAPHIQUEMENT LES DONNÉES DU TABLEAU (VENTES /TEMPÉRATURE)
EXERCICE
page 82
reproduction réservée
Q Q.. R RÉ ÉP PA AR RTTIITTIIO ON N
REPRÉSENTER GRAPHIQUEMENT LES DONNÉES DU TABLEAU DES OBSERVATIONS DE L'ACTIVITÉ D'UN PARC D'ATTRACTION EN FONCTION DE LA TEMPÉRATURE AMBIANTE EXERCICE
NOTES
Éditions I O S
page 83
R R.. R RA AD DA AR R..X XLLS S
FAIRE UN GRAPHIQUE METTANT EN ÉVIDENCE LE MEILLEUR PRODUIT SUR UNE FEUILLE GRAPHIQUE
EXERCICE
NOTES
page 84
reproduction réservée
S S.. B BO OU UR RS SIIE ER R..X XLLS S
REPRÉSENTER GRAPHIQUEMENT SUR UNE FEUILLE GRAPHIQUE LES VOLUMES ÉCHANGÉS AINSI QUE LES COURS LES PLUS SIGNIFICATIFS
EXERCICE
NOTES
Éditions I O S
page 85
TT.. C CO ON NG GE ES S..X XLLS S
REPRÉSENTER GRAPHIQUEMENT LES CONGÉS PRIS PAR LES SALARIÉS EN JUILLET
EXERCICE
NOTES
page 86
reproduction réservée
U U.. G GR RA AP PH HE ES S
EXERCICE
REPRÉSENTER LES DONNÉES EST PAR UN GRAPHE EN CYLINDRE 3D AVEC TITRES... REPRÉSENTER LES DONNÉES NORD PAR UN GRAPHE EN PYRAMIDE AVEC TITRES... REPRÉSENTER LES DONNÉES OUEST PAR UN GRAPHE EN CÔNES 3D AVEC TITRES... REPRÉSENTER LES DONNÉES SUD PAR UN GRAPHE EN AIRES 3D AVEC TITRES... REPRÉSENTER LES TOTAUX PAR UN GRAPHE EN SECTEUR 3D AVEC TITRES ET % REPRÉSENTER LES TOTAUX PAR UN HISTOGRAMME 3D AVEC TITRES ET % SUR UNE FEUILLE GRAPHIQUE
NOTES
Éditions I O S
page 87
V V.. FFO ON NC CFFIIN N..X XLLS S!!N NP PM M--V VC CTT
NOTES
page 88
reproduction réservée
W W.. FFO ON NC CFFIIN N..X XLLS S!!TTA AU UX X--LLN N--N NP PM M
NOTES
Éditions I O S
page 89
X X.. FFO ON NC CFFIIN N..X XLLS S!!TTR RII--V VA AN N
NOTES
page 90
reproduction réservée
Y Y.. FFO ON NC CFFIIN N..X XLLS S!!V VA A
NOTES
Éditions I O S
page 91
ZZ.. FFO ON NC CFFIIN N..X XLLS S!! V VP PN N--IIN NTTP PE ER R-P PR RIIN NC CP PE ER R
NOTES
page 92
reproduction réservée
A AA A.. FFO ON NC CA AM MO OS S..X XLLS S
UNE ENTREPRISE ACHÈTE LES BIENS SUIVANTS : UN TERRAIN LE 07 MAI 1990 POUR 600 000 € ; ELLE Y FAIT CONSTRUIRE DES LOCAUX LE 11 AOUT 1995 POUR 500 000 € UNE MACHINE OUTIL LE 22 DÉCEMBRE 1998 POUR 450 000 € EXERCICE UNE SECONDE MACHINE OUTIL LE 19 OCTOBRE 1999 POUR 550 000 € DU MOBILIER LE 22 FÉVRIER 2002 POUR 15 000€ DES MICRO-ORDINATEURS LE 05 AVRIL 2004 POUR 75 000 € DES LOGICIELS LE 29 DÉCEMBRE 2005 POUR 15 000 € CALCULER LA VALEUR ANNUELLE DES AMORTISSEMENTS DE CHAQUE BIEN CALCULER LA VALEUR DES AMORTISSEMENTS 2005 (PRORATA TEMPORIS)
NOTES
Éditions I O S
page 93
page 94
reproduction réservée
TABLE DES MATIÈRES I.
LE NOM DANS LE CLASSEUR
1
A.
CRÉER UN NOM
1
B.
APPLIQUER UN NOM A UNE FORMULE
1
C.
DÉFINIR UN NOM DE PLAGE
2
D.
DONNER UN NOM A UNE CONSTANTE
2
E.
DONNER UN NOM A UNE FORMULE
3
F.
DONNER UN NOM SPÉCIFIQUE A UNE FEUILLE DE TRAVAIL
4
G.
LES RÉFÉRENCES ET LE NOM
4
II.
LE PLAN
5
A.
LA BARRE D’OUTILS TABLEAUX CROISES
5
B.
CRÉER UN PLAN AUTOMATIQUE
5
C.
UTILISER LE PLAN
6
D.
LA MODIFICATION DU PLAN
7
E.
LES STYLES DE PLAN
8
F.
LA SUPPRESSION DU PLAN
8
III.
LA CONSOLIDATION
9
A. B.
LA CONSOLIDATION / POSITION LA CONSOLIDATION / CATEGORIE
9 11
C.
LA MISE A JOUR ET LA MODIFICATION
13
IV.
LES LIAISONS A.
LES LIAISONS ENTRE CLASSEURS 1 2 3 4 5
B.
COPIE AVEC LIAISON LA FORMULE DE LIAISON SAISIE DE LA FORMULE DE LIAISON MISE A JOUR DES LIAISONS NEUTRALISATION DE LA MISE A JOUR
LES LIAISONS AVEC D'AUTRES FICHIERS 1 2
V.
15 15 15 16 16 16 16
17
LA LIAISON DDE LA LIAISON OLE
18 19
LA RECHERCHE DE SOLUTIONS
21
A.
LA VALEUR CIBLE
21
B.
LE GESTIONNAIRE DE SCENARIOS
22
a) b) c)
C.
LE SOLVEUR 1 2
LANCER LE SOLVEUR PARAMÉTRER LE SOLVEUR a) b) c) d) e)
VI.
ENREGISTRER UN SCENARIO AFFICHER UN SCENARIO LA SYNTHÈSE DES SCENARIOS
LA CELLULE CIBLE LES CELLULES VARIABLES LES CONTRAINTES LES RAPPORTS DE RESULTATS CONTRÔLE DE LA RECHERCHE D'UNE SOLUTION
LE GRAPHIQUE A.
LA CREATION DU GRAPHIQUE
Éditions I O S
23 24 24
25 25 26 26 27 27 28 30
31 31 page 1
B.
L'ASSISTANT GRAPHIQUE 1 2 3 4
C.
PREMIÈRE ÉTAPE DEUXIÈME ÉTAPE TROISIÈME ÉTAPE QUATRIÈME ÉTAPE
33 34 35 36
SÉLECTION, TAILLE ET DIMENSION 1 2 3
D.
36
SÉLECTION LA DIMENSION LA POSITION
36 37 37
LES DIFFÉRENTS MODÈLES 1 2
3 a) b) c) d)
4 5
38 40
MODÈLES L'HISTOGRAMME SIMPLE L'HISTOGRAMME CUMULE
40 40 42
LES GRAPHES LINÉAIRES 2D/3D
44
FINALITÉ SÉLECTION DES AXES LES SÉRIES REPRÉSENTATION
44 44 44 44
LES GRAPHES EN SURFACE 2D/3D LES GRAPHES SECTORIELS 2D/3D a) b) c) d)
6
45 46
FINALITÉ LES AXES LES SÉRIES REPRÉSENTATION
46 46 46 46
LE GRAPHE EN NUAGES DE POINTS a) b) c) d)
7 8
E.
38
LA MODIFICATION DU TYPE DE GRAPHIQUE LES HISTOGRAMMES 2D/3D a) b) c)
47
FINALITÉ LES AXES LES SÉRIES REPRÉSENTATION
47 47 47 47
LES GRAPHES EN RADAR 2D/3D LES COMBINAISONS DE GRAPHES
48 49
LA STRUCTURE DU GRAPHIQUE 1 2 3 4 5 6 7 8
VII.
32
50
LES TITRES DES AXES ET DU GRAPHIQUE LES SÉRIES DE DONNÉES LES ÉTIQUETTES DE DONNÉES LES AXES LA LÉGENDE L'ORIENTATION LES OBJETS GRAPHIQUES LA BARRE D'OUTILS GRAPHIQUE
51 53 56 58 60 61 61 62
LES FONCTIONS A.
GÉNÉRALITÉS 1 2 3 4
B.
63
LA SYNTAXE LES TYPES DE FONCTION LES ARGUMENTS LES RÈGLES D'UTILISATION
63 64 64 65
LES FONCTIONS FINANCIÈRES
65
1
LES INVESTISSEMENTS a) b) c) d) e)
page 2
63
65
=NPM(taux;versement;val._actuelle;val._capitalisée;type) =TAUX(nb_périodes;versement;val.actualisée;val.capitalisée;type) =TRI(ressources;taux estimé) =TRIM(Valeurs;taux financemt CF;taux placement CF) =VA(taux;nbre_périodes;versement;type)
66 66 66 67 67
reproduction réservée
f) g)
2
=VAN(taux;ressources) =VC(taux;nbre_périodes;versement;valeur_actuelle;type)
LES EMPRUNTS a) b) c)
3
69
=INTPER(taux;Nopériode;périodes;val.actualisée;val.capitalisée;type) =PRINCPER(taux;No_période;périodes;valeur_actualisée;valeur_capitalisée;type) =VPM(taux;périodes;val.actualisée;val.capitalisée;type)
LES AMORTISSEMENTS a) b) c) d) e)
VIII.
68 68 69 69 69
70
=AMORLIN(coût;valeur_résiduelle_durée) =DB(coût;valeur_récupération;durée;période;mois) =DDB(coût;valeur_récupération;durée;période;facteur) =SYD(coût;valeur-recupération;durée;période) =VDB(coût;val.récupération;durée;pér_début; pér_fin)
DOSSIER PÉDAGOGIQUE
70 70 71 71 71
73
A.
NOM
73
B.
PLAN.XLS
73
C.
NO BILAN1.XLS
74
D.
CONPOS1.XLS
75
E.
CONSPOS2.XLS
75
F.
CONSCAT1.XLS
75
G.
CONSCAT2.XLS
75
H.
CONSCAT3.XLS
75
I.
LIAISONS.XLS
76
J.
VENTES.XLS
77
K. L.
VALCIB.XLS PUB.XLS
77 78
M.
ELECTRON.XLS
79
N.
BILAN2.XLS
80
O. P.
EXERCICE GRAPHIQUE CROISSANCE.XLS
81 82
Q.
RÉPARTITION
83
R.
RADAR.XLS
84
S. T.
BOURSIER.XLS CONGES.XLS
85 86
U.
GRAPHES
87
V.
FONCFIN.XLS!NPM-VCT
88
W.
FONCFIN.XLS!TAUX-LN-NPM
89
X.
FONCFIN.XLS!TRI-VAN
90
Y.
FONCFIN.XLS!VA
91
Z.
FONCFIN.XLS! VPN-INTPER-PRINCPER
92
AA.
FONCAMOS.XLS
93
Éditions I O S
page 3