Excel pour Mac 2016 : Astuces et techniques

Manipuler les tables de données

TESTEZ LINKEDIN LEARNING GRATUITEMENT ET SANS ENGAGEMENT

Tester maintenant Afficher tous les abonnements
Vous allez apprendre à manipuler les tables de données afin d'effectuer des simulations sur un tableau.
08:30

Transcription

Excel possède un très grand nombre d'outils, chacun dédié à une activité bien précise. Par exemple, si je prends le Tableau croisé dynamique, c'est un outil qui permet de générer des tables de synthèse, des résumés, donc des condensés d'informations. Si je prends par exemple le Solveur, eh bien, c'est un outil qui permet de faire une optimisation de tableaux. Je trouve un résultat, le résultat ne me convient pas, je veux l'optimiser, j'utilise le Solveur. Ou bien encore, si je veux optimiser un tableau, je peux utiliser la valeur cible. Là, je vais vous montrer dans ce tutoriel un outil qui est fait pour faire des simulations. Je vais vous demander de vous rapprocher des fichiers d'exercices, et d'aller dans le chapitre 06, où vous allez trouver un classeur qu s'appelle « TableDonnees.xlsx ». Dedans, il y a une seule feuille qui est composée d'un emprunt, donc on a un emprunt de 100 000 € avec un taux à 2,50 %, une durée de dix, un nombre de remboursement de douze, et on doit rembourser 942,70 € par mois. Maintenant, moi, je voudrais faire une simulation, parce qu'on m'a fait d'autres propositions, et notamment, on m'a fait plusieurs propositions avec des durées différentes. Alors, je voudrais voir qu'est-ce que ça donne si je change la durée, qu'est-ce que ça donne sur mes 942,70 € par mois. Pour ça, je vais utiliser des tables de données. Je vais faire une simulation. Alors les données, ce sont des hypothèses dans le langage Excel, et donc, je vais déposer mes hypothèses de deux ou trois manière différentes. Je peux déposer mes hypothèses verticalement, je peux déposer mes hypothèses horizontalement, et je peux déposer mes hypothèses verticalement et horizontalement. Alors, on va déjà commencer verticalement. Moi, je vais mettre comme hypothèse que par exemple, j'ai une durée de 8 ans, on m'a proposé 10 ans, on m'a proposé 12 ans, on m'a proposé 15 ans, et on m'a proposé 20 ans. Si je mets mes hypothèses verticalement, eh bien, je suis obligé de mettre une formule. Cette formule je ne peux pas la mettre ni ici, ni là, ni ailleurs, je dois la mettre obligatoirement ici. C'est-à-dire que quand vous mettez vos hypothèses à la verticale, eh bien, votre formule doit être positionnée immédiatement à droite de la première hypothèse, au dessus. Ici, je vais mettre la formule, je vais faire =, et je vais cliquer sur le montant remboursé. Je vais pas refaire la formule qu'il y a dans cette cellule, d'autant que c'est une formule quand même assez longue, et il y a le VPM, etc. Là, je marque tout simplement = le contenu de cette cellule. J'ai mes hypothèses qui sont posées verticalement, et j'ai ici ce que je cherche. Je cherche à trouver combien je veux rembourser. Je vais maintenant sélectionner l'ensemble, c'est-à-dire la cellule vide qui est ici, la formule que je viens d'écrire, et toutes les hypothèses que j'ai saisies à la verticale. Maintenant, je viens dans l'onglet Données, dans l'onglet Données, je viens sur Analyse de scénarios, dans Analyse de scénarios, je clique sur Table de données. Une boite de dialogue fait son apparition. Là, on me dit, Cellule d'entrée en ligne, Cellule d'entrée en colonne. Ça veut dire quoi ? Ça veut dire, dans quel sens est-ce que je dois lire ces hypothèses ? Moi, mes hypothèses, ici je les ai mis dans une colonne. Donc, je vais cliquer sur cette case-là, pour dire que mes hypothèses sont disposées en colonne, et que donc toi, Excel, tu dois lire les éléments les uns derrière les autres, à la verticale. Si j'avais déposé mes hypothèses à l'horizontale, j'aurais pris donc la première case. Je me mets dans cette case-là, et là, qu'est-ce que je dois indiquer ? Je dois indiquer où se trouve la cellule qui doit recevoir justement l'hypothèse. C'est-à-dire en deux mots, comme en trois, que représentent les valeurs que j'ai mis ici ? 8, 10, 12, 15, 20, ça représente quoi ? Ça représente des durées. Où se trouve dans mon tableau initial la durée ? La durée, elle est dans la cellule B3. Donc ici, je clique et je viens sur B3, et je clique. Là, qu'est-ce que je lui dis ? Je lui dis écoute Excel, tu mets dans ton petit sac-à-dos la première valeur d'hypothèse. Tu viens l'écrire dans la cellule B3. Tu prends le résultat que tu vas trouver dans B6, et tu viens l'écrire à cet emplacement-là. Puis, puisqu'on descends verticalement, tu prends l'hypothèse qui est située dans cette cellule, tu la mets dans ton petit sac-à-dos, tu repars au niveau de la cellule B3, tu déposes cette hypothèse, tu prends le résultat, et tu viens l'écrire à côté, etc. Je fais OK. Tada ! Ça y est, j'ai fini. On va remettre les choses un petit peu d'aplomb. Je sélectionne toutes ces cellules, je vais dire dans l'Accueil que je veux mettre de milliers, ça sera plus visible, on va déjà vérifier que ça marche. Vous voyez que pour 10 ans, je trouve bien 942,70 €, c'est bien la valeur de départ. Donc, là maintenant, dans l'hypothèse où j'emprunte que pour 8 ans, je vais rembourser par mois 1150,38. Dans l'hypothèse où j'emprunte sur 12 ans, je rembourserai 804,53, etc. Alors maintenant, on peut faire la même chose dans l'autre sens. On va dire que par exemple, on veut travailler sur les montants empruntés. Alors, si j'emprunte 90 000, qu'est-ce qui se passe ? Si j'emprunte 100 000, qu'est-ce qui se passe ? Si j'emprunte 110 000, qu'est-ce qui se passe ? Si j'emprunte 120 000, qu'est-ce qui se passe ? Je peux en mettre comme ça autant que j'en veux. Quand vous déposez vos hypothèses à l'horizontale, eh bien, la formule, vous devez impérativement la mettre ici, c'est-à-dire que votre formule doit être posée devant la première valeur d'hypothèse, sur la ligne immédiatement en dessous. Donc ici, je mets ma formule, =VPM. Non, je vais pas retaper ma formule, je vais tout simplement cliquer sur cette cellule-là qui contient la valeur que je veux retrouver finalement, je valide. Maintenant, je sélectionne toutes les cellules. Comme tout à l'heure, je prends la cellule vide, toutes mes valeurs d'hypothèse, et la formule. Et je repars sur Données, puis je vais dans Analyse de scénarios, je viens sur Table de données, et là, je vais remplir la Cellule d'entrée en ligne, j'ai déposés mes hypothèses sur une ligne. Ces hypothèses là ici, 90 000, 100 000, 110 000, 120 000, elles représentent quoi ? Elles représentent un emprunt. Où se trouve l'emprunt dans mon tableau initial ? Dans la cellule B1. Donc je clique sur B1, je fais OK, Boom, j'ai le résultat qui apparaît. On va carrosser tout ça en milliers, je viens sur Accueil, je viens sur Milliers, voilà. Donc dans l'hypothèse où j'emprunte 90 000, je rembourserai 848,43 € chaque mois, etc. Enfin, et pour conclure, nous pouvons faire la même chose dans les deux sens, c'est-à-dire avec deux séries d'hypothèses. Donc, on a la possibilité par exemple de dire, imaginons qu'on nous propose un taux de 1,85 %, un taux de 1,95 %, un taux de 2 %, un taux de 2,5 %, et un taux de 2,78 %. Ça, c'est les taux qu'on m'a proposé. Mais en plus, on me propose un emprunt de 90 000, ou de 95 000, ou encore de 100 000, ou de 110 000. Donc, je mets ici mes valeurs d'hypothèse. On va carrosser tout ça tout de suite en milliers, voilà. Donc 90 000. 950 000... je me disais qu'il y a quelque chose qui allait pas. Donc 95 000, 100 000 et 110 000. OK, donc là, j'ai déposé une série d'hypothèses à l'horizontale, une série d'hypothèses à la verticale, je sélectionne la cellule vide ici, et je vais mettre ma formule à cet emplacement. Je fais =, et je clique sur la valeur que je recherche. Je valide. Alors, je récapitule. Quand on met les hypothèses à la verticale, la formule, elle est ici. Quand on les met à l'horizontale, elle est là. Quand on les met dans les deux sens, elle est à cet emplacement-là, et vous n'avez pas le droit de faire autre chose. Donc maintenant, je n'ai plus qu'à sélectionner tout ça, et à aller bien évidemment dans Données, nous l'avons vu, aller dans l'outil d'analyse Table de données, et là, on va dire ben, à l'horizontale qu'est-ce que nous avons ? Ça, ça représente des emprunts. Qu'est-ce que nous avons à la verticale ? Nous avons ici les taux, et il n'y a plus qu'à faire OK, et là, Boom, on a le résultat. Par exemple, je peux vous dire que si j'emprunte 95 000 à 2 %, eh bien, je rembourserai 874,13 € par mois. Voilà donc ce que sont les tables de données. on peut déposer des hypothèses et voir suivant le choix qu'on va faire, quel est l'impact sur le résultat final.

Excel pour Mac 2016 : Astuces et techniques

Optimisez votre productivité et améliorez votre flux de travail avec Excel pour Mac 2016. Découvrez des astuces sur la saisie, l’exploitation de formules et la mise en forme.

3h13 (44 vidéos)
Aucun commentaire n´est disponible actuellement
Spécial abonnés
Date de parution :30 juin 2017

Votre formation est disponible en ligne avec option de téléchargement. Bonne nouvelle : vous ne devez pas choisir entre les deux. Dès que vous achetez une formation, vous disposez des deux options de consultation !

Le téléchargement vous permet de consulter la formation hors ligne et offre une interface plus conviviale. Si vous travaillez sur différents ordinateurs ou que vous ne voulez pas regarder la formation en une seule fois, connectez-vous sur cette page pour consulter en ligne les vidéos de la formation. Nous vous souhaitons un excellent apprentissage avec cette formation vidéo.

N'hésitez pas à nous contacter si vous avez des questions !