Excel 2013 Avancé
Contents
1. re MOV tiquettes de lignes Somme de Montant Lyon Sakai d c Lyon 21106 Marseille 524936 23 12 2013 Marseille 19932 Paris 245430 Paris 22776 Reims 219778 _ S lectionner plusieurs l ments Reims 81731 Rouen 28310 Rouen 30106 Toulouse 24733 Annuler Toulouse 24145 Total general 756148 Total general 259796 m Pour reafficher le total toutes dates confondues actionnez la fleche de la cellule de filtre et s lectionnez Tous 4 TRANSFORMEZ LE TABLEAU POUR AVOIR DES POURCENTAGES EN COLONNE m S lectionnez une cellule du tableau crois puis dans le volet Champs de tableau crois dynamique Sous VALEURS actionnez la fl che de Somme de montant puis l option Param tres des champs de valeurs Le dialogue Param tres des champs de valeurs s affiche m Sous l onglet Afficher les valeurs dans la zone lt Afficher les valeurs gt s lectionnez du total de la colonne validez par OK Nom de la source Montant Somme de Montant Mom personnalis Synth se des valeurs par Afficher les valeurs Afficher les valeurs du total de la colonne Date oct F Aucun calcul du total g n ral 7 du total de la colonne Etiquettes de lignes Somme de Montant Somme de Commission i ra total de la ligne Lyon 8 12 422 12 du total de la ligne parente Marseille 1 67 398 64 2 Paris 31 86 1655 52 Commission Reims 31 46 1634 62 Rouen 11 59 602 12 Eormat de nombre Annuler Toulouse 9 23 _ 4829 Total g2. Max Min valeur 0 contraintes et les conditions d optimisation Rapports Cellules variables D AE EAEE R ponses Conserver la solution du solveur Sensibilit O R tablir les valeurs d origine Contraintes B 6 5B 8 lt B 13 5B 15 SCS6 SES8 gt 0 poutas SCS9 SES9 gt SCS3 SES3 CO Retourner dans la bo te de dialogue Param tres Rapports de plan Annuler Enregistrer le sc nario Le Solveur a trouv une solution satisfaisant toutes les contraintes et les conditions d optimisation Modifier Supprimer Lorsque le moteur GRG est utilis le Solveur a trouv au moins une solution optimale locale Lorsque Simplex PL est utilis cela signifie que le Solveur a trouv une solution optimale globale R tablir tout Charger enregistre C Rendre les variables sans contrainte non n gatives S lect une r solution Simplex PL y Options Demande des magasins Mantes Toulon Bastia 250 240 5340 Quantit s exp di es de l usine x l entrep t y M thode de r solution S lectionnez le moteur GRG non lin aire pour des probl mes non lin aires simples de solveur S lectionnez le moteur Simplex PL pour les probl mes lin aires et le moteur Evolutionnaire pour les probl mes complexes Exp ditions Mantes Toulon Bastia 500 me p 0 9 Total exp di 850 250
3. r aliser sont pr c d es d une puce Dans cet ouvrage le terme dialogue d signe une boite de dialogue le terme actionner un l ment signifie cliquer ou appuyer sur un l ment CALCULS ET SIMULATIONS CALCULER SUR DES DATES PARAM TRER LE CHANGEMENT DE SI CLE Lorsque vous saisissez une date avec la partie ann e sur deux chiffres Excel interpr te 00 29 comme 2000 a 2029 et 30 99 comme 1930 1999 Ce seuil de 29 est d fini dans les options R gion du panneau de configuration de Windows m Pour modifier ce seuil acc dez au panneau de configuration Windows sous Horloge langue et r gion actionnez Modifier les formats de date d heure ou de nombre ou cat gorie R gion actionnez le bouton Param tres suppl mentaires puis sous l onglet Date modifiez la zone contenant le seuil Calendrier Si une ann e est entr e avec deux chiffres elle est comprise entre 1930 et 2029 Z a Ter jour de la semaine lundi Le SAISIR DES DATES OU DES HEURES m Pour saisir une date s parez les jour mois et ann e par ou par par exemple 24 12 69 24 12 69 Si le mois est saisi en lettres vous pouvez le s parer par des espaces 24 d c 69 m Pour saisir une heure utilisez le s parateur deux points par exemple 22 5 10 correspond 22H 05 minutes et 10 secondes UNE DATE EST UN NUM RO DE S RIE UN HEURE UNE FRACTION DE JOUR Excel enregistre la date comme un num
4. 257985 238367 Total g n ral 136133 35654 110004 32676 26333 112297 166175 116896 756148 Oct Automatique D but 06 10 2013 Fin Par 23 12 2013 Secondes Minutes Heures Jours Mois Trimestres Ann es Nombre de jours 1 Annuler T Etiquettes de lignes Somme de Montant Somme de Commission qe SER Lyon 8 12 422 12 Marseille CRAD E Marseille Fj Er 238 54 Paris 248420 Paris 31 86 1655 52 Reims 219778 Reims 31 46 1634 62 Rouen 28310 Rouen 11 59 602 12 Toulouse 4733 Toulouse 9 29 482 9 Total g n ral 756148 Total g n ral 100 00 5195 92 Nombre de factures par ville par mois et par vendeur B C D E F G H l K L M N 2 3 Nombre de Montant tiquettes de colonnes 7 4 Durand Total Durand Martin Total Martin Morel Total Morel Total g n ral 5 tiquettes de lignes 7 oct now d c oct now d c oct now dec 6 Lyon 4 3 4 11 11 7 Marseille 4 4 4 12 12 8 Paris 12 10 13 35 35 9 Reims 8 B 7 23 23 10 Rouen 8 7 8 23 23 11 Toulouse 2 3 2 7 7 12 Total g n ral 12 10 13 35 14 14 14 42 12 11 11 34 111 Commissions par ville et par vendeur A B C D E F G H I K L M N 2 3 Commissions par vendeur 4 Morel Total Morel Martin Total Martin Durand Total Durand Total g n ral 5 oct now d c oct now d c oct now d c 6 Lyon 4232 282 424 1 125 1125 7 Marseille 399 342 429 1170 1170 B Faris 1656 1472 1841 4969 4 969 9 Reims 1635 1691 1070 4396 4396 10 Rouen 602 626 538
5. se constitue partir de la cellule H3 m Dans le volet Champs de tableau crois dynamique Faites glisser le champ Ville jusque sous LIGNES Faites glisser le champ Montant jusque sous VALEURS de m me pour le champ Commissions Eyrolles Tsoft Excel 2013 Avanc CAS 17 TABLEAU CROIS DYNAMIQUE E B G D E F G H J Ija 1 Liste des ventes 4 me trimestre Champs de tableau crois d 3 Commission Etiquettes de lignes _ Jsomme de Montant Somme de Commission Le LA Saind n m 4 20 10 2013 AMT Paris Durand 6967 00 139 34 Lyon 56401 1128 02 A TR AE 5 27 10 2013 Marval Paris Durand 7 005 00 140 10 Marseille 58496 1169 92 6 27 10 2013 AMT Paris Durand 4538 00 90 76 Paris 248430 4968 6 Date 7 13 10 2013 AMT Paris Durand 13 009 00 260 18 Reims 219778 4395 56 Client 8 20 10 2013 Marval Paris Durand 6155 00 123 10 Rouen 83310 1766 2 N 9 20 10 2013 AMT Paris Durand 3358 00 67 16 Toulouse 84733 1694 66 v Ville 10 27 10 2013 AMT Paris Durand 7156 00 143 12 Total g n ral 756148 15122 96 Vendeur 11 13 10 2013 Marval Para Durand 11 252 00 225 04 J Montant 12 13 10 2013 AMT Paris Durand 6 834 00 137 88 Ego 13 13 10 2013 Marval Paris Durand 2358 00 47 16 V Commission 14 06 10 2013 AMT Paris Durand 3366 00 67 32 15 06 10 2013 Marval Paris Durand 10718 00 214 36 PLUS DE TABLES 16 03 11 2013 AMT Paris Dura
6. D6 D8 50MME E6 E8 Cout unitaire d expedition i Usines capacite Mantes Toulon Bastia Marseille Paris Brest Cout exp dition SOMME C16 E16 SOMME CGCE C13 C15 SOMME D6 D8 D13 D15 SOMME EG E8 E13 E15 A B C D E F G H Demande des magasins CR a l Mantes Toulon Bastia LE ARRORO CZRAGEREOZNIDZ 250 240 540 Quantit s exp di es de l usine x l entrep t y Usines Exp ditions Mantes Toulon Bastia Marseille 15 5 5 5 Paris 15 5 5 3 Brest 15 5 5 5 Total exp di 45 15 15 15 Cout unitaire d exp dition usine x entrep t y Usines capacit Mantes Toulon Bastia Marseille Paris Brest Cout exp dition 255 95 65 95 Eyrolles Tsoft Excel 2013 Avanc 17 SOLVEUR R SOUDRE PAR LE SOLVEUR m Sous l onglet Donn es gt groupe Analyse actionnez le bouton Solveur le dialogue Param tres du solveur s affiche Sp cifiez la cellule cible B16 d finir en cherchant a la minimiser lt Min gt Dans la zone lt Cellules variables gt sp cifiez la plage C6 E8 Dans la zone lt Contraintes gt sp cifiez les contraintes en actionnant le bouton Ajouter Actionnez R soudre pour essayer de trouver une solution optimale Le dialogue R sultat du solveur s affiche dans lequel Excel propose de choisir entre garder la solution ou r tablir les valeurs d origine indiquez votre choix et actionnez OK Objectif d finir Le Solveur a trouv une solution satisfaisant toutes les
7. dynamique pr c dent pour qu il repr sente le nombre de factures m S lectionnez une cellule contenant une totalisation puis sous l onglet Analyse gt groupe Champ actif actionnez le bouton Param tres de champ ou dans le menu contextuel de la cellule clic droit ou appui long actionnez l option Param tres des champs de valeur Dans le dialogue sous l onglet Synth se des valeurs par s lectionnez Nombre validez par OK A B C D E F G H I J K L M N 2 3 Nombre de Montant tiquettes de colonnes 7 4 Durand Total Durand Martin Total Martin Morel Total Morel Total general 5 Etiquettes de lignes 7 oct now dec oct now dec oct now dec 6 Lyon 4 3 4 11 11 7 Marseille 4 4 4 12 12 B Paris 12 10 13 35 35 9 Reims 8 B 7 23 23 10 Rouen 8 7 B 23 23 11 Toulouse 2 3 2 7 7 12 Total g n ral 12 10 13 35 14 14 14 42 1 11 11 34 111 Eyrolles Tsoft Excel 2013 Avanc CAS 17 TABLEAU CROIS DYNAMIQUE 8 MASQUEZ LES BOUTONS ET LES FL CHES D ROULANTES DU TABLEAU En masquant les boutons des tiquettes et les en t tes de champs avec leur fl che d roulante le tableau crois dynamique ne se distingue plus visuellement d une plage de cellules m Sous l onglet contextuel Outils de tableau crois dynamique Options gt groupe Afficher actionnez les boutons Boutons et En t tes de champs Fi B C D E F G H l K L M M 2 3 Nombre de Montant 4 Durand Total Durand Martin Total Martin Morel Total Morel Total g n ra
8. ro de s rie Le 1 janvier 1900 correspond au num ro 1 chaque date saisie correspond au nombre de jours coul s depuis le 1 janvier 1900 Excel enregistre une heure comme une fraction d cimale de jour par exemple 0 5 correspond 12H 0 75 correspond 18H a 0 760416666666667 correspond 18 H 15 Il existe un autre syst me de num rotation des dates partir du 1 janvier 1904 utilis par Excel pour Mac Il est possible d utiliser ce syst me de date dans les options d Excel rubrique Options avanc es sous Lors du calcul de ce classeur cochez la case lt M Utiliser le calendrier depuis 1904 gt EXEMPLE DE CALCULS SUR DES DATES A1 A2 renvoie le nombre de jours coul s entre les deux dates contenues dans A1 et A2 AUJOURDHUI renvoie la date du jour actuel PLAFOND MOIS A1 3 1 calcule le num ro du trimestre n du mois 3 arrondi sup rieur FIN MOIS A1 0 cette fonction renvoie la date du dernier jour du mois de la date FIN MOIS Al 1 1 renvoie la date du 1 jour du mois dernier jour du mois pr c dent 1 NO SEMAINE A1 renvoie le num ro de semaine de la date JOUR FIN MOIS A1 renvoie le nombre de jour du mois n du dernier jour du mois 5Kch ance sTEXTE A1 jjjj mmmm aaaa affiche la date en toutes lettres dans un texte TEXTE A1 jjjj renvoie le jour de la semaine de la date en toutes lettres TEXTE Al mmmm renvoie le mois de la date en toutes lettres SI AUJ
9. 12 Word 2007 Memento PK0293 13 13 Word 2007 Exercices PK0292 15 Devis Tarif Feuil3 4 Devis Tarif Feuil3 H Liste d roulante pour s lectionner les titres de livre Les cellules B5 B10 sont prevues pour entrer les titres une liste d roulante d entr es provenant de Options Message de saisie Alerte d erreur la plage nomm e Titre A 2 A 13 dans la feuille D Cie Tarif peut tre d finie pour permettre de a DG F ionorer siva s lectionner le titre Dane Liste d roulante dans la cellule m S lectionnez les cellules B5 B10 puis sous MERE l onglet Donn es gt groupe Outils de donn es a actionnez la fl che du bouton Validation de donn es Puis s lectionnez Liste s lectionnez le nom de plage Titre OK Lorsque vous s lectionnez une des cellules de la Een tout es plage B5 B10 de la feuille Devis un bouton fl che apparait droite de la cellule actionnez ce bouton fl che et s lectionnez le titre LJ Appliquer ces modifications aux cellules de parametres identiques Formules de la premiere ligne de livre du devis La table des livres est plac e dans la feuille Tarif dans les colonnes A a C dans notre exemple la table des livres est nomm e Catalogue la plage est A2 C13 dans la feuille Tarif m Une formule dans la cellule A5 va chercher dans la table des livres la r f rence correspondant au titre entr en B5 SI B5 0 0 RECHERCHEV B5 Catalogue 2 FAUX
10. 2 13 888875 2 777775 Solution X 13 888875 et Y 2 777775 arrondi a 6 d cimales Exemple 2 Calculons la commission d un commercial d finie a 5 25 du b n fice net qui lui m me d pend du montant de la commission B n fice net B n fice brut Commission les formules en B3 et B4 contiennent une r f rence circulaire Bosososssssoseoseesessessesssesesessessesessessesseseesssssssessessesesssssesees A B A B 1 Montant en 1 Montant en 2 B n fice brut 1000 2 B n fice brut 1000 3 B n fice net B2 B4 3 B n fice net 950 12 A Commission 5 25 B3 4 Commission 49 88 Solution Prime 49 88 KE arrondi a 2 d cimales pour un b n fice brut de 1 000 ACTIVER LE CALCUL ITERATIF m Actionnez l onglet Fichier puis Options s lectionnez la rubrique Formules et dans le panneau de droite de la fen tre sous Mode de calcul cochez la case lt M Activer Ne maximal d it rations 1100 le calcul it ratif gt D ooo con o Saisissez le nombre d it rations au terme desquelles EA Excel cessera le calcul saisissez la valeur d cart entre deux r sultats successifs au dessous de laquelle l it ration doit s arr ter m Actionnez OK Activer le calcul it ratif Un calcul it ratif peut tre soit divergent il n am ne aucun r sultat significatif soit convergent il converge vers une valeur S il diverge le calcul s arr te au bout de N it rations et chaque fois que vous tapez FA Reca cu Exce
11. 240 340 10 11 Cout unitaire d exp dition usine x entrep t y 12 Usines capacit Mantes Toulon Bastia 13 Marseille 14 Paris 15 Brest 16 Cout exp dition 2930 450 740 1440 OPTIONS DE R SOLUTION Le bouton Options permet de contr ler la r solution tan eae si une solution optimale n est pas trouv e au bout du RETE 0 000001 temps de r solution ou du nombre d it rations Excel C chelle automatique propose de continuer ou de se contenter de la solution Afficher le r sultat des it rations 4 R solution avec des contraintes de nombre entier approch e Ignorer les contraintes de nombre entier Optimalit des nombres entiers 5 R solution des limites Temps max secondes 100 It rations 100 volutionnaire et contraintes de nombre entier Sous probl mes max Solutions r alisables max Eyrolles Tsoft Excel 2013 Avanc CAS 17 TABLEAU CROIS DYNAMIQUE Ventes par client et par mois A B 1 2 3 Somme de Montant tiquettes de colonnes 7 4 tiquettes de lignes oct 5 AMT 45288 6 Bolor Sarl 18554 7 Champagnes Fols 41042 8 JBM Consultant 11552 9 Keops 9506 10 Marval 37488 11 SysLog 51735 12 Valeor 34571 13 Total general 259796 14 33086 19122 38295 12175 7892 40494 560369 45552 Ventes par ville commissions par ville H l ous l tiquettes de lignes Somme de Montant 5 Date Date 57759 17958 30667 8949 8935 34315 44011 35773
12. 5 Utiliser des formules conditionnelleS e u a aa aaa aaa aaa aaa aan aa aaa aan anna 147 Cas 6 Utiliser des formules matricielleS o ouuaaoa awa anawa aa aaoaaaaozaczaneciEkEc 151 Cas 7 Recherche dans une table ss 157 Caso e Utlliser le mode PIO wezwania aaa w dka 161 Cas 9 Table d hypotheSeS uu aaa aan aan aaa ana aaa aan ana waka nana a aka ana aka Aaaa ELLI 165 Cas 10 Valeur CIDIE ata asd e za by a aa AA ns nus le 169 Cas 1I Scena OS ida GaGa bkdi a 175 Cas 12 Liaisons entre THIS MN GE AE EE R 179 Cas 15 CONSONOALIONES EO Rd cet lune 183 Cas 14 G rer un tableau de donn es 189 Cas 15 Filtrer des donn es sin 197 Cas 10 SOUS LOLAUX sn Arai ad R Ecka 203 Cas 17 5 Tableau crois dynamique wad dA GG a a enr 209 Cas 18 Images pages Web et e mail uaa anawa naw anawa n aan ana na nawa n aka a R ARIAL 217 Cas 19 Acc der a des donn es externes RL 227 Cas 20 Utiliser Excel comme base de donn es 233 Cas 21 Maitriser les Graphiques 25m inst reste nes haine essais ae 239 Cas 22 Contr le et protection des donn es ss srsnsrsnesnsnnss 247 INDE Gucniiaka iii GaGa nid si 255 Eyrolles Tsoft Excel 2013 Avanc 3 Avant propos Con u par des p dagogues exp riment s l originalit de cet ouvrage est d tre a la fois un manuel de formation et un manuel de r f rence Il traite les fonctions avanc es du tableur Office Excel 2013 et il fait su
13. 88310 11 Toulouse 24145 37324 23264 84733 84733 12 Total gen ral 82776 73580 92074 248430 74183 85741 71615 231539 102837 98664 74678 276179 756148 6 MODIFIEZ LE FORMAT DES VALEURS m S lectionnez une cellule contenant un montant puis sous l onglet Analyse gt groupe Filtrer actionnez le bouton Champ actif puis l option Param tres de champ ou dans le menu contextuel de la cellule actionnez Param tres des champs de valeur Dans le dialogue actionnez le bouton Format de nombre puis sp cifiez le format Nombre avec 0 d cimales et s parateur d cimal validez par OK deux fois A B C D E E G H J K L M N 2 3 Somme de Montant tique 4 Durand Total Durand Martin Total Martin Morel Total Morel Total g n ral 5 tiquettes de lignes oct nov d c oct nov d c oct nov d c 6 Lyon 21 106 14 109 21 186 56 401 56 401 7T Marseille 19 932 17 120 21 444 58 496 58 496 8 Paris 82 776 73 580 92 074 248 430 248 430 9 Reims 81 731 84 555 53 492 219 778 219 778 10 Rouen 30 106 31 297 26 907 88 310 88 310 11 Toulouse 24 145 37 324 23 264 64 733 64 733 82 776 73 580 92 074 248 430 74183 85 741 71615 231 539 102 837 98 664 74 678 276 179 756 148 12 Total g n ral 13 7 CHANGEZ LA FONCTION DE TOTALISATION Les valeurs produites par le tableau crois dynamique sont par d faut des sommes mais vous pouvez changer cette fonction et choisir la moyenne le nombre le max le min Transformez le tableau crois
14. Mensualite de remboursement d un emprunt 2 3 Montant emprunt 275000 4 5 Dur e en ann es 3 Cellule d finir 5659 5 Dur e en mois gt Valeur atteindre 2000 7 Taux d int r t annuel 56 sa g Cellule a modifier 5653 3 calcul de la mensualit 3301 58 Annuler 10 Le r sultat s affiche dans la cellule a modifier et une fen tre message vous propose de confirmer m Actionnez OK pour valider le changement des valeurs dans la feuille A B C D E F 1 Mensualit de remboursement d un emprunt 2 3 Montant emprunte 166 587 gt Recherche sur la cellule B9 3 Dur e en ann es 3 a trouv une solution 6 Dur e en mois 108 Vu le 2000 7 Taux d int r t annuel 6 Valeur actuelle 2 000 00 5 9 calcul de la mensualit 2 000 00 La capacit d emprunt est de 166 587 Eyrolles Tsoft Excel 2013 Avanc SC NARIOS Un sc nario est un ensemble de valeurs que vous pouvez appliquer un ensemble de cellules en une seule action afin de visualiser les r sultats des formules d pendantes de ces cellules Dans l exemple ci contre les cellules variables sont B1 et B2 On veut calculer la marge B6 les charges B8 et le r sultat B10 pour diff rentes valeurs de Ventes 1 et Ventes 2 Hypothese 1 200 140 Hypoth se 2 250 170 Hypoth se 3 280 200 A B A B A B B 1 ventes 1 210 1 ventes 1 200 1 ventes1 250 1 ventes 1 260 2 Ventes 2 1
15. aaa aaa aaa wana zana aaaaaaaaaa anka nakaz zaaka anka RAA 79 Graphique crois dynamique uwaa aa aaa a aan a aaa wana aa aaa waka waka waka waka rza ani EK EL 11H 80 Se connecter a une source de donn es externe 81 uuliser Microsoft QUEFV wac da a GAGA 83 6 CONTR LE CHANGE ET COLLABORATION 85 Contr ler la validit des donn es la saisie 86 Contr ler l acc s fichier par mot de passe 87 Verrouillage et protection des cellules Re 88 Travailler plusieurs sur un classeur aan aaa aan ana aaa aan aaa ana ana aka ana AAAA K LAI 90 Importer et exporter des fichiers texte ss 93 changer des donn es entre Excel et ACCESS ere 96 Ins rer un tableau Excel dans un document Word 98 Envoyer un classeur par messagerie nier 100 P DII r AU format TE EMILESSS 2 ON TONA NC 101 Correspondances Excel 2003 Excel 2013 annees 104 Param trage des options Excel 2013 ss ssnsssnnnnss 111 N uvegites Excel 2015 risiste anneni dneni aaaeaii nienia 117 Fonctionnalit s abandonn es ou modifi es dans Excel 2013 44244414441111 119 2 Eyrolles Tsoft Excel 2013 Avanc PARTIE 2 CAS PRATIQUES Cas 1 Techniques labor es de mise en forme aan aaa aan aan aaa ania i AAA 123 Cas 2 Relance de factures impay es ss 131 Cas 3 Statistiques comparatives de prix ss 137 Cas 4 Utiliser des fonctions financi res s 4ss aaa aaa aa aaa aaa aaa aa aaa zaa A 141 Cas
16. dans la table ou la matrice fonction transpos e de RECHERCHEV 10 Eyrolles Tsoft Excel 2013 Avanc R F RENCES CIRCULAIRES ET IT RATIONS NOTION D ITERATION POUR R SOUDRE LES REFERENCES CIRCULAIRES Dans certains calculs une formule peut renvoyer indirectement elle m me on parle alors de r f rence circulaire Si l option lt M Activer le calcul it ratif gt est activ e Excel peut r soudre ce type de probl me par it ration en tenant compte chaque pas du r sultat du calcul pr c dent Exemple 1 quations deux inconnues X Y 25 2 et Y X 5 D m Saisissez la formule de la cellule E2 puis saisissez celle de la s ZAW a s z CZ ZE 251 cellule E3 qui introduit une reference circulaire gt l Excel affiche un message d avertissement Microsoft Excel Attention votre classeur contient une ou plusieurs r f rences circulaires qui risquent d entra ner des erreurs de calcul dans vos formules Remarque une r f rence circulaire peut tre une formule qui fait r f rence sa propre valeur de cellule ou une cellule d pendant de sa propre valeur de cellule m Actionnez OK pour accepter la r f rence circulaire le r sultat 0 apparait dans la cellule E3 car Excel ne peut effectuer le calcul m Si vous activez le calcul it ratif voir ci dessous Excel calcule et affiche le r sultat Sans calcul it ratif Avec calcul it ratif m M at r rne 1 x Y 1 x Y 2 12 5 0
17. les bouton onglet Analyse gt groupe Afficher A B C D E F G a Champs de tableau crois d 3 Commissions par vendeur 4 Durand Martin Morel Total g n ral Choisissez les champs inclure dans le rapport 7 5 6 Lyon 1128 1128 Date 7 Marseille 1170 1170 Client 8 Paris 4969 4969 Ville 9 Reims 4 396 4 396 Vendeur 10 Rouen 1766 1766 Montant 11 Toulouse 1 695 1 695 M 12 Total gen ral 4 969 4631 5524 15 123 13 Faites glisser les champs dans les zones voulues 14 ci dessous 15 m s 16 FILTRES COLONNES 17 Vendeur vy 18 m LIGNES VALEURS 20 21 Ville Commissions par ve Y Eyrolles Tsoft Excel 2013 Avanc CAS 17 TABLEAU CROIS DYNAMIQUE 11 TRIEZ LE TABLEAU PAR ORDRE ALPHAB TIQUE DES VILLES m Actionnez une cellule contenant une tiquette de ville puis sous l onglet Donn es gt ongJlet Trier et filtrer actionnez le bouton Trier de Z A Triez aussi de la m me fa on les noms des vendeurs par ordre de Z A Faites r apparaitre les mois actionnez une cellule contenant un nom de vendeur puis sous l onglet Analyse gt groupe Champ actif actionnez D velopper le champ m Vous pouvez faire afficher les dates du jour pour cela dissociez les valeurs de champ date que vous avez group es pr c demment en mois actionnez une cellule contenant un mois puis actionnez Dissocier partir du Ruban onglet Analyse gt groupe Groupe m Regroupez en trimestre a
18. regroupement par mois c est possible m S lectionnez une des cellules contenant une date puis actionnez le bouton Groupe onglet Analyse gt groupe Filtrer puis l option Grouper le champ utilisez l option Grouper du menu contextuel de la cellule Dans le dialogue Grouper s lectionnez Mois validez par OK Grouper Automatique B C D 1 D but 06 10 2013 R i AAAA 3 Somme de Montant tiquettes de colonnes 7 4 Etiquettes de lignes 7 oct nov ld c Total g n ral 5 AMT 45288 33086 57759 136133 6 Bolor Sarl 18554 19122 17958 55634 7 Champagnes Fols 41042 38295 30667 110004 8 IBM Consultant 11552 12175 8949 32676 9 Keops 9506 7892 8935 256333 10 Marval 37488 40494 34315 112297 Mombre de jours 1 11 SysLog 61 95 60369 44011 166175 12 Valeor 34571 460552 35773 116896 Annuler 13 Total g n ral 259796 257985 238367 756148 14 m Copiez les valeurs du tableau crois dynamique dans une nouvelle feuille s lectionnez la plage A4 E13 puis effectuez un copier coller dans la feuille Feui 2 2 CR EZ UN TABLEAU DES VENTES PAR VILLE m S lectionnez une cellule du tableau de donn es de la feuille Fichier puis sous l onglet contextuel Outils de tableau Creation gt onglet Outils actionnez le bouton Tableau crois dynamique m Choisissez l emplacement lt Feuille de calcul existante gt puis actionnez la zone lt Emplacement gt actionnez la cellule H3 validez par OK La zone mat rialisant le tableau crois
19. 1 766 1 766 11 Toulouse 483 746 465 1695 1695 12 Total g n ral 2057 1373 14594 5 524 1484 1715 1432 4631 1656 1472 1841 4 969 15 123 Eyrolles Tsoft Excel 2013 Avanc CAS 17 TABLEAU CROIS DYNAMIQUE Fonctions utilis es Cr er un tableau crois dynamique Modifier les options de ce type de tableau Les tableaux crois s dynamiques sont des tableaux de synth se sur le contenu d un tableau de donn es Ils sont dynamiques au sens o ils sont actualis s chaque modification apport e dans le tableau de donn es Les donn es de ce cas pratique sont dans le fichier CasA17 x1sx enregistr dans le dossier C Exercices Excel 2013 Ouvrez le fichier et enregistrez le sous le nom CasA17 R xlsx 1 CR EZ LE TABLEAU CROIS DYNAMIQUE DES VENTES PAR CLIENT PAR MOIS m Transformez plage de donn es qui s tend sur A3 F114 en tableau de donn es proc dez comme au cas N 14 Le tableau est nomm Tableaul son nom appara t sur le Ruban sous l onglet contextuel Outils de tableau Cr ation gt onglet Propri t s m Sous l onglet contextuel Outils de tableau Cr ation gt onglet Outils actionnez le bouton Tableau crois dynamique Cr er un tableau crois dynamique BE z a Le dialogue Cr er un tableau crois dynamique sert Choisissez les donn es analyser Fo opi r T UE ARR sp cifier la source de donn es ici le tableau s plage Tableau Plage Tableauf z Tableau1 et l emplacement d
20. 50 2 Ventes 2 140 2 Ventes 2 170 2 Ventes 2 200 3 Achat 1 70 3 Achat1 67 3 Achat1 83 3 Achat 1 93 4 Achat 2 30 4 Achat 2 28 4 Achat 2 34 4 Achat 2 40 5 Marge 260 5 Marge 245 5 Marge 303 5 Marge 347 6 Salaires 165 6 Salaires 165 6 Salaires 165 6 Salaires 165 I Loyers 40 7 Loyers 40 7 Loyers 40 7 Loyers 40 8 Charges 205 8 Charges 205 8 Charges 205 8 Charges 205 3 9 g 3 10 R sultat 55 10 R sultat 40 10 R sultat 98 10 R sultat 142 mh s 14 mam Cr er les sc narios m Commencez par s lectionner les cellules d entr e dans l exemple la plage B1 B2 Si les cellules taient dispers es vous utiliseriez la s lection multiple souris seulement appuyez sur la touche tout en s lectionnant les cellules concern es m Onglet Donn es gt groupe Outils de donn es actionnez la fl che du bouton Analyse de sc nario puis l option Gestionnaire de sc narios m Dans le dialogue Gestionnaire de sc narios actionnez Ajouter saisissez le nom du sc nario Hypoth se 1 sp cifiez les r f rences des cellules variables cellules plages multiples s par es par le caract re et deux options de protection n ayant effet que lorsque vous aurez prot g la feuille lt M Changements interdits gt pour que d autres utilisateurs ne puissent pas modifier le sc nario lt MMasquer gt pour que son nom ne soit pas visible dans la liste des scenarios Actionnez OK Nom du sc nario Hypothese 1 Cel
21. 5B51 5B52 Commentaire Cr par JeanL le 13 05 2013 Pour pouvoir restaurer les valeurs d origine des cellules variables cr ez un sc nario qui utilise les valeurs d origine des cellules avant d afficher les sc narios qui les modifient Supprimer modifier un sc nario et fusionner des sc narios m Onglet Donn es gt groupe Outils de donn es actionnez la fl che du bouton Analyse de sc nario puis l option Gestionnaire de sc narios dans le dialogue Gestionnaire de sc narios s lectionnez le sc nario et utilisez les boutons Modifier ou Supprimer Fusionner permet de fusionner les sc narios cr s dans d autres feuilles construites sur le m me mod le dans d autres classeurs Ceci permet de fusionner des sc narios provenant de plusieurs personnes mais il faut que les autres feuilles soient construites sur le m me modele Rapport de synth se m Nommez sans utiliser d espaces dans les noms les cellules contenant les variables ainsi que les cellules contenant le r sultat m Onglet Donn es gt groupe Outils de donn es actionnez la fl che du bouton Analyse de sc nario puis l option Type de rapport Gestionnaire de sc narios enfin actionnez le bouton HOTTE S th _ Sc nario du rapport de tableau crois dynamique yn ese Cellules r sultantes Choisissez entre une Feuille de synthese et un B5 B10 tableau crois dynamique sp cifiez les cellules r sultantes que vous voulez visuali
22. Excel 2013 E ET af gt Pad a 4 f P E mAlllie e A Moreau Z di r a v z i aJi AN k i p ra i 7 zh fe z N gt i a k zo k TR TM Al 2 A EYROLLES NN nn TSoft et Groupe Eyrolles 2013 ISBN 978 2 212 13812 2 TABLE DES MATI RES PARTIE 1 MANUEL UTILISATEUR 1 CALCULS ET SIMULATIONS ssssssssssazsssazazsszawaszazanis A CalcUIEFSUNIESJALES Een tease es ananas eee 8 Utiliser les fonctions de recherche ss 9 R f rences circulaires et it rations ueoaa aaa aaa aaa aaa aaa aaa aa anawa aaaa naka aaakanaEG0 11 MGIEUMCIDIE pe NE Ea RE nici cO a a 12 SceNANOS EGAN GEE GEORGA GEORG GG 13 Tables de donn es d hypotheseS u as aaa aan a aan a aan aaa aaa ana apa akara ka rzi LEE LL 15 SOINSU E Sok okaG d ada o a Gai od G Kok kia idei 17 2 POUR AM LIORER VOTRE EFFICACITE u u 111211112111 LO COMMENCES adw Ao dick E E E EEEE 20 Rechercher et remplacer n ssssesssnsnurnrnsenrnensnurnennnurnennnururnsnnrnennnnrnennunrnennnnenene 21 V rifier l orthographe sssesessesssssrnunnrnnnnunnnnnnnnnrnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnan 23 Generer UNE Selena WAGA OO aa nn ei ti ROA 24 MSA OE E EE E E E E AE AE 25 Mise en forme conditionnelle sasssnnnnananannnnnnnnnnnannnnanannnnannnnnnnnsnnanannnnennnnnnana 26 Utiliser des liens hypertexteS snsnnsssnsnnsnsnnrnrnnnnenrnurnensnurne
23. OURDHUI gt Al date d pass e affiche le texte si la date dans A1 est d pass e 110 24 60 calcule 110 mn en fraction de jour 110 60 convertit 110 mn en nombre d heures diviser par 24 heures soit 0 07777778 nombre qui s affiche 01 50 en format hh mm 9 75 24 calcule 9 75 heures en fraction de jour soit 0 40625 nombre qui s affiche 9 45 au format hh mm 8 Eyrolles Tsoft Excel 2013 Avanc UTILISER LES FONCTIONS DE RECHERCHE Par exemple vous avez une liste d articles ici des livres et vous voulez cr er un devis dans lequel vous voulez s lectionner les livres par leur titre Les informations concernant les livres r f rence et prix doivent s afficher automatiquement dans le devis lorsqu un titre de livre est s lectionn H S s QE Deissoftix T est plus pratique de s lectionner un titre de livre ACCUEIL INSERTION MISEENPAGE FORM plut t que d avoir conna tre le code r f rence du livre A B D 1 Titre Ref prix 2 Access 2003 M mento _ PK0264 11 H S gt 4 Q DevisTsoftxlsx Excel x 3 Access 2003 Exercices PKO262 14 ACCUEIL INSERTION MISE EN PAGE FORMULES DONN ES R VISION AFFICHAGE 4 Access 2007 Memento PKO301 13 s Access 2007 Exercices PKO302 15 6 Excel 2003 Memento PKO258 11 7 Excel 2003 Exercices PKO252 15 B Excel 2007 Memento PKO295 13 3 Excel 2007 Exercices PKO296 15 10 Word 2003 Memento PKO250 11 11 Word 2003 Exercices PKO244 14
24. Tant qu aucune valeur n a t entr e dans la cellule B5 B5 0 le r sultat de la formule est 0 sinon la fonction RECHERCHEV cherche le titre entr en B5 dans la premi re colonne de la plage Catalogue et renvoie la valeur qui est contenue dans la colonne 2 m Une formule dans la cellule E5 va chercher dans la liste d articles le prix correspondant au livre entr en B5 SI B5 0 0 RECHERCHEV B5 Catalogue 3 FAUX Tant qu aucune valeur n a t entr e dans la cellule B5 B5 0 le r sultat de la formule est 0 sinon la fonction RECHERCHEV cherche le titre entr en B5 dans la premi re colonne de la plage Catalogue et renvoie la valeur qui est contenue dans la colonne 3 m Les autres formules de la ligne en F5 D5 E5 en H5 F5 G5 et en 15 F5 H5 affichent leur r sultat sup rieur a 0 des que la quantit est entr e en D5 Formules des autres lignes articles du devis Les autres lignes sont obtenues par copie des formules de la premi re ligne du devis Eyrolles Tsoft Excel 2013 Avanc 9 UTILISER LES FONCTIONS DE RECHERCHE RECHERCHEV Cherche une valeur dans la premi re colonne de la matrice d un tableau et renvoie la valeur se trouvant sur la m me ligne mais dans une autre colonne de la matrice du tableau Le V de RECHERCHEYV signifie Vertical en colonne Syntaxe RECHERCHEV valeur cherchsSe table matrice no COl vateur logigne valeur cherch e la Valeur chercher dans la premi
25. ableau crois dynamique Analyse appara t sur le Ruban Une zone encadr e repr sente le tableau crois et le volet Champs de tableau crois dynamique est ouvert droite de la fen tre Notez que si vous actionnez une cellule hors du tableau crois le volet est masqu il ne s affiche que lorsque vous actionnez le tableau crois m Faites glisser champ Cent jusque sous LIGNES le champ Date jusque sous COLONNES le champ Montant jusque sous VALEURS pour les inclure dans le tableau crois A B C D E F 1 Champs de tableau crois d 2 3 Somme de Montant tiquettes de colonnes 7 Choisissez les champs inclure dans le rapport 7 4 tiquettes de lignes 7 06 10 2013 13 10 2013 20 10 2013 27 10 2013 03 11 2013 1 5 AMT 3366 19903 10325 11694 11572 Client z 6 Bolor Sarl 3345 7745 3709 3755 4025 Ville 7 Champagnes Fols 5418 19141 7826 8657 6074 TEDRE ARE 8 JBM Consultant 11552 12175 9 Keops 9506 7892 z M 10 Marval 10718 13610 6155 7005 8569 11 SysLog 15780 28735 8884 8396 11061 Faites glisser les champs dans les zones voulues 12 Valeor 6395 4211 20518 3447 5164 ci dessous 13 Total g n ral 66080 93345 57417 42954 66532 s f 14 FILTRES COLONNES 15 Date v 16 17 LIGNES VALEURS 18 Client w Somme de Montant v 19 R Feuil1 BE m i Diff rer la mise jour de la disp Le tableau se constitue au fur et mesure dans la feuille Les tiquettes de colonnes sont les jours Date mais nous voudrions avoir un
26. ctionnez une cellule contenant une date puis sous l onglet Analyse gt groupe Groupe actionnez Grouper la s lection s lectionnez 7rimestres Validez par OK A B C D E F G H l J K L M N 2 3 Commissions par vendeur 4 Morel Total Morel Martin Total Martin Durand Total Durand Total gen ral 5 Trimestre4 Trimestre4 Trimestre4 6 oct nov d c oct nov d c oct nov d c 7 Lyon 422 282 424 1128 1128 8 Marseille 399 342 429 1170 1170 9 Paris 1656 1472 1841 4 969 4969 10 Reims 1635 1691 1070 4 396 4 396 11 Rouen 602 626 538 1766 1766 12 Toulouse 483 746 465 1 695 1 695 13 Total g n ral 2057 1973 1494 5524 1484 1715 1432 4631 1656 1472 1841 4 969 15 123 m Masquez les mois pour cela actionnez une tiquette contenant un mois puis sous l onglet Analyse gt groupe Champ actif actionnez R duire le champ B c D E F G H 2 3 Commissions par vendeur 4 Morel Total Morel Martin Total Martin Durand Total Durand Total g n ral 5 Trimestre4 Trimestre4 Trimestre4 56 F Lyon 1128 1128 1128 8 Marseille 1 170 1 170 1 170 9 Paris 4 969 4 969 4 969 10 Reims 4 396 4 396 4 396 11 Rouen 1766 1766 1766 12 Toulouse 1 695 1 695 1 695 13 Total g n ral 5 534 5 534 4631 4631 4 969 4 969 15 123 14 Ainsi vous pouvez regrouper les dates en mois en trimestres et galement en ann es m Actionnez le bouton Li de la barre d outils Acc s rapide puis fermez le classeur Eyrolles Tsoft Excel 2013 A
27. eneral 100 00 5195 92 Eyrolles Tsoft Excel 2013 Avanc CAS 17 TABLEAU CROIS DYNAMIQUE 5 CREEZ UN TABLEAU DES VENTES PAR VILLE PAR MOIS ET PAR VENDEUR m S lectionnez une cellule du tableau de donn es de la feuille Fichier puis sous l onglet contextuel Outils de tableau Cr ation gt onglet Outils actionnez le bouton Tableau crois dynamique Dans le dialogue choisissez l emplacement lt Nouvelle feuille de calcul gt puis validez sur OK Dans le volet Champs de tableau crois dynamique Faites glisser le champ Ville jusque sous LIGNES Faites glisser le champ Vendeur jusque sous COLONNES Faites glisser champ Date galement sous COLONNES Faites glisser le champ Montant jusque sous VALEURS Champs de tableau crois d 7 Choisissez les champs inclure dans le rapport Date Client Ville Vendeur Montant Commission PLUS DE TABLES Faites glisser les champs dans les zones voulues ci dessous FILTRES COLONNES Vendeur r Date T A B C D E F G H l J 1 2 3 Somme de Montant tiquettes de colonn 7 LIGNES VALEURS 4 Durand Total Durand Martin Total Martin Mot E ZA 3 5 ville Y Somme de Montant 7 5 Etiquettes de lignes oct nov d c oct nov d c 6 Lyon 21106 14109 21186 56401 56401 7 Marseille 19932 17120 21444 58496 58496 8 Paris 82776 73580 92074 248430 248430 9 Reims 81731 84555 53492 219778 219778 10 Rouen 30106 31297 26907 88310
28. ite a un manuel d initiation paru chez le m me diteur Ce manuel s adresse donc a des utilisateurs ayant d j assimil et mis en pratique les fonctions de base d Office Excel 2013 FICHES PRATIQUES La premi re partie Manuel utilisateur pr sente sous forme de fiches pratiques l utilisation des fonctions avanc es d Excel 2013 et leur mode d emploi Ces fiches peuvent tre utilis es soit dans une d marche d apprentissage pas a pas soit au fur et mesure de vos besoins lors de la r alisation de vos propres documents Une fois ces fonctions ma tris es vous pourrez galement continuer a vous y r f rer en tant qu aide memoire Si vous vous tes d j aguerri sur une version pr c dente d Excel ou sur un autre logiciel tableur ces fiches vous aideront a vous approprier rapidement les fonctions avanc es d Office Excel 2013 CAS PRATIQUES La seconde partie Cas pratiques consiste r aliser de petites applications en se servant des commandes et des fonctions d Office Excel 2013 Cette partie vous propose vingt deux cas pratiques qui vous permettront de mettre en uvre la plupart des fonctions tudi es dans la partie pr c dente tout en vous pr parant concevoir vos propres applications de mani re autonome Ils ont t con us pour vous faire progresser vers une bonne ma trise des fonctionnalit s avanc es d Office Excel 2013 Ces cas pratiques constituent un parcours de formation la r alisation du parcour
29. l 5 oct nov d c oct now d c oct nov d c 6 Lyon 4 3 4 11 11 7 Marseille 4 4 4 12 12 8 Paris 12 10 13 35 35 9 Reims 8 6 7 23 23 10 Rouen 8 7 8 23 23 11 Toulouse 2 3 2 7 7 12 Total g n ral 12 10 13 35 14 14 14 42 17 11 11 34 111 9 MASQUEZ LES DETAILS DES DATES POUR LES VENDEURS m S lectionnez une cellule contenant un nom de vendeur puis sous l onglet Analyse gt groupe Champ actif actionnez le bouton R duire le champ ou dans le menu contextuel de la cellule clic droit ou appui long actionnez l option D velopper R duire puis R duire le champ entier A B C D E F G H l K L 2 3 Nombre de Montant Durand Martin Morel Total g n ral 5 6 Lyon 11 11 7 Marseille 12 12 8 Paris 35 35 9 Reims 23 23 10 Rouen 23 23 11 Toulouse 7 7 12 Total g n ral 35 42 34 111 10 AFFICHEZ LES COMMISSIONS LA PLACE DES NOMBRES DE FACTURES m S lectionnez une cellule du tableau crois dynamique puis dans le volet Champs de tableau crois dynamique faites glisser le champ Commissions jusque sous VALEURS s il n y est pas m Enlevez Somme de Montant sous VALEURS en actionnant la fl che droite de Somme de Montant puis Supprimer le champ Formatez les valeurs de champs en Nombre avec 0 d cimale et s parateur des milliers La modification du nom des valeurs totalis es peut tre faite directement dans la cellule qui contient le nom la cellule A3 Modifiez le nom en Commissions par vendeurs m R affichez
30. l effectue nouveau N it rations et les valeurs changent S il converge le calcul s arr te lorsque deux r sultats successifs diff rent de moins de l cart maximal Eyrolles Tsoft Excel 2013 Avanc 11 VALEUR CIBLE On cr e une formule contenant une variable et l on veut connaitre la valeur de la variable pour que la formule renvoie un r sultat que l on se fixe Exemple calcul du montant maximum empruntable La formule de calcul du remboursement mensuel d un emprunt est fonction du montant emprunt de la dur e et du taux d int r t La recherche d une valeur cible r pond la question sachant que ma capacit de remboursement est de 2 000 par mois que le taux est de 6 annuel et la dur e de 9 ans combien puis je emprunter Saisissez les donn es et la formule de calcul m Saisissez les donn es utilis es par la formule ici cellules B3 B7 m Saisissez la formule VPM B7 12 B6 B3 cellule B9 m S lectionnez la cellule contenant la formule ici B9 m Onglet Donn es gt groupe Outils de donn es actionnez la fl che du bouton Analyse de sc nario puis l option Valeur cible Dans la zone lt Cellule d finir gt la r f rence B9 de la cellule qui contient la formule Dans la zone lt Valeur atteindre gt saisissez la valeur cible 2000 Dans la zone lt Cellule modifier gt actionnez la cellule B3 pour ins rer la r f rence B3 m Actionnez OK A B C D E F 1
31. le de donn es dans la cellule E2 au dessus des cellules de r sultats VPM B5 12 B4 12 B3 vous pouvez aussi utiliser la formule B7 puisque cette formule a d j t saisie en B7 m S lectionnez la plage de cellules D2 E8 contenant les valeurs d hypothese en colonne et dans la colonne droite les cellules r sultats avec la formule au dessus des cellules r sultats m Onglet Donn es gt groupe Outils de donn es actionnez la fl che du bouton Analyse de sc narios puis l option Table de donn es m Dans le dialogue 7ab e de donn es actionnez la zone lt Cellules d entr e en colonne gt puis collectez la cellule B5 qui contient la variable dont les valeurs d hypotheses ont t saisies _ fe VPM B5 12 B4 12 B3 Formule B7 A B c D 8 Montant du remboursement d un emprunt Mensualit Mensualit Montant de l emprunt Dur e du remboursement ann e Taux d int r t annuel Mensualit de remboursement O CO s LL E W NM M ET 10 Cellule d entr e en ligne 11 Cellule d entr e en colonne 5BS5 FRE Annuler m Actionnez OK Vous pouvez obtenir les r sultats de plusieurs formules utilisant les m mes valeurs d hypotheses s lectionnez une plage ici D2 G8 couvrant plusieurs colonnes la premi re colonne contenant les valeurs d hypotheses chaque colonne suivante contenant une formule et ses cellules r sultats F2 x B9 A B C D E F G 1 Montant du remboursement d u
32. lules variables B1 B2 Fi f Pour ajouter des cellules non adjacentes la zone de cellules variables cliquez tout en appuyant sur la touche Ctrl Valeurs de scenar Commentaire T Tapez des valeurs pour chacune des cellules modifier Cree par JeanL le 13 05 2013 4B 1 200 1 Z Ventes 2 140 Protection v Changements interdits _ Masquer Ajouter Annuler Annuler m Dans le dialogue Valeurs de sc narios saisissez les valeurs du sc nario 200 140 puis Ajouter pour cr er le sc nario et en ajouter un autre dans le dialogue Ajouter un sc nario saisissez le nom du sc nario actionnez OK saisissez les valeurs du sc nario Actionnez OK pour cr er le sc nario et revenir au dialogue Gestionnaire de sc narios Actionnez Annuler pour annuler la cr ation du sc nario et revenir au dialogue Gestionnaire de sc narios m Actionnez Fermer pour terminer Afficher un sc nario m Onglet Donn es gt groupe Outils de donn es actionnez la fl che du bouton Analyse de sc nario puis l option Gestionnaire de sc narios Eyrolles Tsoft Excel 2013 Avanc 13 SC NARIOS m S lectionnez le sc nario que vous voulez afficher puis actionnez Afficher Les valeurs du sc nario remplacent alors les valeurs existantes dans les cellules variables Sc narios Ajouter pa supprimer Hypothese 3 supp Modifier Fusionner Synth se Cellules variables
33. n emprunt Mensualit Total inter ts Total remb 2 198 047 78 3 Montant de l emprunt 275 000 00 8 198 047 78 473 047 78 4 Dur e du remboursement ann e 15 o 227 061 96 502 061 96 5 Taux d int r t annuel 8 a 256 929 53 531929 53 6 EEEE 287 615 48 562 615 48 7 Mensualit de remboursement 2 319083 19 594 083 19 8 Total remboursement 39 351 294 87 626 294 87 9 Total int r ts 5 Eyrolles Tsoft Excel 2013 Avanc 15 TABLE DE DONN ES D HYPOTH SES TABLE DE DONN ES DOUBLE ENTR E FORMULE DEUX VARIABLES L exemple suivant est un tableau qui calcule la mensualit de remboursement d un emprunt fonction VPM en faisant varier la dur e et le taux d int r t deux s ries de valeurs d hypoth ses Saisissez le modele m Saisissez les donn es utilis es par la formule ici cellules B3 B4 et B5 m Saisissez la formule VPM B5 12 B4 12 B3 cellule B7 Saisissez les donn es dans la table de donn es m Les valeurs d hypoth se de la premi re colonne plage D3 D7 les dur es en ann es m Les valeurs d hypoth se de la premiere ligne plage E2 12 les taux d int r t annuel m La formule dans la premi re cellule de la table cellule D2 B7 nous aurions pu aussi saisir la formule VPM B5 12 B4 12 B3 mais puisqu elle a d j t saisie dans la cellule B7 nous pr f rons ici utiliser la formule B7 Effectuez le calcul de la table de donnees m S lectio
34. n respectant des contraintes Il faut avoir install le solveur Pour cela actionnez l onglet Fichier puis Options s lectionnez la rubrique Compl ments Dans le panneau de droite dans la zone lt G rer gt s lectionnez Compl ments actionnez le bouton Atteindre puis dans le dialogue Macros compl mentaires cochez lt M Compl ment solveur gt actionnez OK pour installer ce compl ment EXEMPLE DE PROBL ME Un produit fabriqu dans trois usines est envoy dans trois magasins r gionaux et le co t d exp dition est fonction de la distance Il s agit de minimiser le co t d exp dition total B16 en respectant les exigences d approvisionnement des magasins la quantit livr e est sup rieure ou gale la quantit demand e C3 E3 lt C9 E9 les contraintes de capacit des usines la quantit fabriqu e est inf rieure la capacit B6 B8 lt B13 B15 les variables calculer sont les quantit s exp di es qui doivent tre positives C6 E820 m Construisez le mod le comme ci dessous entrez des valeurs initiales quelconques dans la plage C6 E8 des variables calculer B C D E PR ENEE PENINE EEE ENEA ER Demande des magasins Affichage des formules Mantes Toulon Bastia 250 240 340 Quantit s exp di es de lu Usines Exp ditions Mantes Toulon Bastia Marseille SOMME CE E6 Paris 50MME C7 E7 Brest S0NIME CB EB Total exp di SOMME BG BB 50MME C6 C8 50MME
35. nd 9095 00 181 90 17 17 11 2013 AMT Paris Durand 3918 00 78 36 18 10 11 2013 AMT Paris Durand 11 093 00 221 38 Faites glisser les champs dans les zones voulues 13 10 11 2013 Marval Paris Durand 11071 00 221 42 ci dessous 20 17 11 2013 AMT Paris Durand 6497 00 129 94 21 24 11 2013 Marval Paris Durand 5977 00 119 54 22 03 11 2013 Marval Paris Durand 8569 00 171 38 FILTRES COLONNES 23 17 11 2013 Marval Paris Durand 1452 00 23 04 Valeurs 24 24 11 2013 Marval Paris Durand 13 425 00 268 50 25 03 11 2013 AMT Paris Durand 2477 00 49 54 26 22 12 2013 Marval Paris Durand 9923 00 198 46 27 15 12 2013 AMT Paris Durand 9171 00 183 42 29 22 12 2013 AMT Paris Durand 39 160 00 183 20 30 22 12 2013 AMT Paris Durand 5 775 00 115 50 Ville w Somme de Montant v 31 01 12 2013 AMT Paris Durand 3161 00 63 22 z 32 08 12 2013 AMT Paris Durand 8697 00 173 94 Somme de Commiss 7 3 FILTREZ LES R SULTATS m Dans le volet Champs de tableau crois dynamique faites glisser le champ Date jusque sous FILTRES Dans la feuille au dessus du tableau crois appara t une zone de filtre nomm e Date Actionnez la fl che droite de la cellule de filtre I1 et s lectionnez le mois d octobre Date Tous z l H l Rechercher P H l Date Tous 4 a are Fous m p sept Date oct T tiquettes de lignes Somme de Montant 5
36. nnez la plage de la table de donn es D2 17 puis sous l onglet Donn es gt groupe Outils de donn es actionnez le bouton Analyse de sc nario puis l option Table de donn es m Dans le dialogue 7ab e de donn es Actionnez la zone lt Cellules d entr e en ligne gt puis collectez la cellule B5 variable dont les valeurs d hypotheses ont t saisies en ligne Actionnez la zone lt Cellule d entr e en colonne gt puis collectez la cellule B4 variable dont les valeurs ont t saisies en colonne dans la table de donn es D2 bd fx B 7 A B C D E F G H l Montant du remboursement d un emprunt Montant de l emprunt Dur e du remboursement ann e Taux d int r t annuel Mensualit de remboursement 60 OM h w MON He BF mi Cellule d entr e en ligne 5655 as MI Cellule d entr e en colonne S E54 FRE FE W 15 m Actionnez OK A B c D E F G H l Montant du remboursement d un emprunt Montant de l emprunt 2407 70 2475 02 2543 45 2612 97 Dur e du remboursement ann e 2263 57 2331 65 2400 91 2471 37 Taux d int r t annuel 5 2140 45 2209 28 2279 39 2350 77 2034 14 2103 73 2174 68 2246 98 Mensualit de remboursement 2279 39 1941 49 2011 83 2083 62 2156 84 16 Eyrolles Tsoft Excel 2013 Avanc SOLVEUR Le solveur permet de trouver les valeurs de plusieurs cellules variables permettant une formule d atteindre une valeur d finie maximale ou minimale e
37. nnnurnennnurnennnnrnennnnne 29 Automatiser avec les MAaCTOS ssssssssssssssrsnensnsnsrnrnrnrnnnnnnnnnnnnnnrnrnrnnnnnnnnnnnnnnnnnni 32 3 DESSINS IMAGES ET OBJETS GRAPHIQUES 35 Ins rer et mettre en forme une image ss ssssssssrssrnurnnrnnrnnrnnnnnnnnnnnnnnnnnnnnennnnnnnnnn 36 Creerune zone d texte aaa EEE 38 Inserer Un Smar AN reaa E AAA 39 Ins r r un 0rganigramMmMe sis anini 41 I seref des TONO AO WWO aide ee 42 Positionner et redimensionner les objets 45 Eyrolles Tsoft Excel 2013 Avanc 1 4 REPRESENTATION GRAPHIQUE DES DONNEES 49 CTCEFUMAEPNIAUE iR OO dac 50 Modiner les donnecs SOUNGE wawa ka sn den nee 51 Disposer les l ments sur le graphique ns 52 Mettre en forme les l ments du graphique 53 Modifier le type de graphique aaa aan aaa an ana nan anawa snannncncenenennnneneenenenunun 56 Mod les de graphique copier le graphique 59 5 GESTION ET ANALYSE DE DONNEES 11111111111111111 61 CONS lAAUOM Gs A EOG CCC a 62 Trier transposer une plage de cellules 64 Cr er et mettre en forme un tableau de donn es 65 Filtrer un tableau de donn es ins 67 Fonctions sur base de donn es as aaa ozna aaa aaa napa ana kanapa daioni idnina 70 S0OUS TOAWGG Gio EEE EC 71 POCE PION o eee E E ai ie ice 72 Tableaux crois s dynamiques aan aan aaa aaa aaa wana waka waka n aka zaa k kaka a E AE 0Y 74 Modele de donn es Excel aaa aaa
38. re colonne de la matrice elle peut tre une valeur ou une r f rence ou m me une formule table matrice au moins deux colonnes de donn es Utilisez une r f rence a une plage ou un nom de plage no col num ro de la colonne de l argument table matrice dont la valeur correspondante doit tre renvoy e Si no col est gal 1 la fonction renvoie la valeur de la premi re colonne de l argument table matrice Sino col est gal 2 la valeur est renvoy e de la deuxi me colonne de l argument table matrice et ainsi de suite Si l argument no col est inf rieur 1 RECHERCHE renvoie VALUE s il est sup rieur au nombre de colonnes dans table matrice RECHERCHEV renvoie REF valeur logique VRAI indique que vous voulez que la fonction RECHERCHEV recherche dans la premi re colonne une valeur exacte FAUX que la fonction RECHERCHV recherche la valeur la plus proche de celle que vous avez sp cifi e Si VRAI est omis la fonction recherche en premi re colonne la valeur gale ou imm diatement inf rieure Les valeurs de la premi re colonne de table matrice doivent tre class es en ordre croissant sans cela RECHERCHEV ne renvoie pas forc ment la bonne valeur Si FAUX la fonction recherche exclusivement une correspondance exacte Dans ce cas il nest pas indispensable que les valeurs de la premi re colonne de table matrice soient tri es Si plusieurs valeurs de la premi re colonne de table matrice corresponden
39. s complet permet de s initier seul en autoformation Un formateur pourra aussi utiliser cette partie pour animer une formation l utilisation avanc e d Office Excel 2013 Mis disposition des apprenants ce parcours permet chaque l ve de progresser sa vitesse et de poser ses questions au formateur sans ralentir la cadence des autres l ves Les fichiers n cessaires la r alisation de ces cas pratiques peuvent tre t l charg s depuis le site Web www editions eyrolles com Pour cela tapez le code 13812 dans le champ lt RECHERCHE gt de la page d accueil du site puis appuyez sur Vous acc derez ainsi la fiche de l ouvrage sur laquelle se trouve un lien vers le fichier t l charger Une fois ce fichier t l charg sur votre poste de travail il vous suffit de le d compresser vers le dossier C Exercices Excel 2010 ou un autre dossier de votre choix T l chargez les fichiers des cas pratiques depuis www editions eyrolles com Conventions typographiques Pour faciliter la compr hension visuelle par le lecteur de l utilisation pratique du logiciel nous avons adopt les conventions typographiques suivantes Ruban noms des onglets groupes et boutons ou zones qui sont sur le Ruban ltalique noms des commandes dans les menus et nom des dialogues Saisie noms de dossiers noms de fichiers texte saisir XXXXX boutons qui sont dans les boites de dialogue m Actions les actions
40. ser dans la synth se actionnez OK Excel cr e une feuille Synth se de sc narios et y place la synth se de vos divers sc narios 1 2 Synth se de sc narios 3 Valeurs actuelles Origine Hypothese 1 Hypothese 2 Hypothese 3 5 Cellules variables 6 Ventes _1 210 210 200 250 280 1 Ventes 2 150 150 140 170 200 8 Cellules r sultantes 9 Marge 260 260 245 303 347 10 R sultat 33 33 40 98 142 11 La colonne Valeurs actuelles affiche les valeurs des cellules variables Eyrolles Tsoft Excel 2013 Avanc TABLES DE DONN ES D HYPOTH SES Cette fonction appel e table d hypotheses dans les versions 2007 et ant rieures vous permet de repr senter dans un tableau les r sultats d une formule selon diff rentes valeurs d une ou deux variables de la formule TABLE DE DONN ES SIMPLE ENTR E FORMULE UNE VARIABLE L exemple suivant est un tableau qui calcule la mensualit de remboursement mensuel d un emprunt fonction VPM valeur des paiements en faisant varier les taux d int r t Saisissez les donn es et la formule de calcul puis les valeurs d hypothese m Saisissez les donn es utilis es par la formule ici cellules B3 B5 m Saisissez la formule VPM B5 12 B4 12 B3 dans la cellule B7 m Saisissez les hypoth ses dans les cellules en colonne D2 D7 Nous faisons varier ici le taux d int r t les hypoth ses sont des valeurs de taux d int r t m Saisissez la formule de la tab
41. t valeur cherch e c est la premi re valeur trouv e qui est utilis e Si aucune valeur ne correspond la valeur d erreur N A est renvoy e LES AUTRES FONCTIONS DE RECHERCHE PECATER TET N Lignes p Colonnes nauteu r Largeur Renvoie une r f rence une cellule ou une plage de cellules d cal e de n lignes et p colonnes par rapport la r f rence r f Vous pouvez sp cifier la hauteur et la largeur de la plage renvoyer La fonction DECALER peut tre utilis e avec les fonctions exigeant une reference comme argument par exemple la formule SOMME DECALER C2 1 2 3 1 FOUTV valeur cherchee matrice recherche type Renvoie la position relative dune valeur cherch e dans une matrice Utilisez la fonction EQUIV plut t qu une des fonctions RECHERCHE lorsque vous avez besoin de la position d un l ment dans une plage et non de l l ment en tant que tel INDEX tablea no LTJNEPNO gt 601 0U TNDEX rt no lig no CodleNnO zore Renvoie une valeur ou une r f rence une valeur provenant d un tableau ou d une plage l intersection du no ligne et du no colonne no zone sert indiquer le num ro de zone dans le cas ou r f est constitu de plusieurs zones RECHERCHEH valeur cherch e table matrice no lig valeur logique Recherche une valeur dans la ligne sup rieure horizontalement d une table ou d une matrice de valeurs puis renvoie une valeur dans la m me colonne partir d une ligne que vous sp cifiez
42. u futur tableau crois O Utiliser une source de donn es externes dynamique ici une nouvelle feuille de calcul Nom de la connexion Choisissez l emplacement de votre rapport de tableau crois dynamique Nouvelle feuille de calcul C Feuille de calcul existante Emplacement Indiquez si vous souhaitez analyser plusieurs tables _ Ajouter ces donn es au mod le de donn es m Validez par OK EH ACCUEIL INSERTION MISEENPAGE FORMULES DONNEES REVISION AFFICHAGE DEVELOPPEUR ANALYSE CREATION Philippe 4 z 3 Ins rer un segment A LE to ki i la z B lt Eg Ins rer une chronologie n gt Options du tableau Champ Groupe Actualiser Changerlasource Actions Calculs Outils Afficher crois dynamique actif y X de donn es r M Filtrer Donn es A A3 gt fx v B C D E F G a 1 Champs de tableau crois d 3 Choisissez les champs inclure dans le rapport yd 5 z Date 6 Pour g n rer un rapport choisissez des Client z champs dans la liste des champs de Ville S tableau crois dynamique Vendeur S v 10 11 me Faites glisser les champs dans les zones voulues 12 ci dessous 14 v F FILTRES COLONNES 15 16 LIGNES E VALEURS 20 19 20 x Feuil1 Fichier gt r Diff rer la mise jour de la disp Eyrolles Tsoft Excel 2013 Avanc CAS 17 TABLEAU CROIS DYNAMIQUE La nouvelle feuille est cr e l onglet contextuel Outils de t
43. vanc
Download Pdf Manuals
Related Search