Blog ENI : Toute la veille numérique !
-25€ dès 75€ sur les livres en ligne, vidéos... avec le code FUSEE25. J'en profite !
Accès illimité 24h/24 à tous nos livres & vidéos ! 
Découvrez la Bibliothèque Numérique ENI. Cliquez ici
  1. Livres et vidéos
  2. Tableaux de synthèse et tableaux de bord
  3. Utiliser les utilitaires d'analyse et de simulation
Extrait - Tableaux de synthèse et tableaux de bord Traitez et analysez de gros volumes de données avec Excel 2016
Extraits du livre
Tableaux de synthèse et tableaux de bord Traitez et analysez de gros volumes de données avec Excel 2016
1 avis
Revenir à la page d'achat du livre

Utiliser les utilitaires d'analyse et de simulation

Introduction

Excel est non seulement un formidable outil de calcul mais aussi un puissant outil de simulation et de modélisation. Ce chapitre n’a pas pour objectif de faire de vous un statisticien chevronné mais va aborder quelques techniques de prévision et de simulation.

Plusieurs points vont être abordés.

  • Effectuer une analyse de simulation à l’aide d’une table de données.

  • Réaliser une simulation dans l’avenir à partir de données antérieures connues.

  • Construire un diagramme de Pareto.

  • Utiliser la valeur cible pour déterminer une valeur en fonction de la variation d’une autre.

  • Résoudre des problèmes à plusieurs inconnues à l’aide du solveur.

Les tables de données

Créer une table de données à une variable

Une table de données est une plage de cellules qui va indiquer comment la modification d’une ou deux variables dans vos formules peut affecter le résultat de vos formules. Les tables de données fournissent une technique simple et rapide permettant de calculer plusieurs résultats en une seule manipulation ainsi qu’un mode d’affichage et de comparaison des résultats de toutes les différentes variations.

En outre, utiliser une table de données permet de vous affranchir de la gestion des adressages (relatif, absolu et mixte) car Excel va générer une formule matricielle qui va gérer automatiquement les références de chacune des plages concernées.

Afin de vous familiariser avec cette technique, nous allons débuter par la conception d’une table de données à une variable.

 Ouvrez le classeur ExemplesAnalyse.xlsx.

La première feuille de notre classeur TD1V contient un tableau permettant de calculer différents éléments d’un placement financier. À partir de la saisie du montant du capital placé, du taux d’intérêt annuel et de la durée nous souhaitons pouvoir comparer les valeurs du capital obtenu et des intérêts acquis lorsque le taux varie.

images/CapturePage212.PNG

La mensualité...

Obtenir des prévisions

Principe

Lorsque vous disposez de données qui correspondent à des valeurs antérieures, vous pouvez utiliser Excel pour effectuer une prévision dans l’avenir. Par exemple, si vous connaissez les chiffres d’affaires obtenus sur les 6 premiers mois de l’année, vous pouvez estimer quel sera le chiffre d’affaires prévisionnel du mois 11 (bien sûr en considérant que la progression suit le même rythme).

La technique consiste à réaliser un graphique en courbe de cette évolution et, à partir de cette courbe, obtenir son équation puis utiliser cette équation pour obtenir la valeur future souhaitée.

Effectuer une simulation dans l’avenir à partir de données antérieures connues

Vous avez recensé sur un an le nombre de connexions hebdomadaires réalisées sur votre nouveau site web. Le détail a été recensé sur 52 semaines dans la feuille Previsions.

La courbe des connexions a été insérée dans la feuille.

images/Chap6-011.PNG

Vous souhaitez connaître quelles seront les connexions pour une semaine future précise, si la progression suit le même rythme.

Le graphique nous montre que notre courbe pourrait être assimilée à une droite d’équation y=ax+b

La fonction DROITEREG calcule les paramètres d’une droite par la méthode des moindres carrés afin de calculer une droite s’ajustant au plus près de vos données, puis renvoie une matrice de deux variables qui décrit cette droite.

DROITEREG

Syntaxe :...

Concevoir un diagramme de Pareto à partir de vos données

Construire un diagramme de Pareto, dans quel but ?

Un diagramme de Pareto est un graphique qui représente l’importance de différents facteurs sur un phénomène. Les informations sont présentées sous la forme d’un histogramme dont les données représentées en abscisses sont classées par valeurs décroissantes. Généralement, une courbe représentant les pourcentages cumulés est ajoutée au graphique.

Ce type de représentation peut être utilisé dans de nombreux domaines, quelques exemples vous sont présentés ci-dessous :

  • En gestion de qualité, visualiser quels sont les 20 % de causes qui sont à l’origine de 80 % des défauts.

  • En gestion des ressources humaines, connaître quels sont les motifs les plus fréquents d’absences des employés.

  • Peut-on affirmer, dans notre entreprise, que les quatre plus gros clients réalisent près de trois quarts de notre chiffre d’affaires ?

Les cas sont nombreux où construire un diagramme de Pareto pourra vous rendre service dans l’analyse de vos données. Vous pourrez ainsi axer vos prises de décisions sur les éléments majeurs.

Présentation

Vous dirigez une petite société qui commercialise des sorbets aux fruits sur Internet. La liste des ventes est stockée dans la feuille Sorbets.

images/CapturePage223.PNG

À partir de cette liste, vous souhaitez pouvoir visualiser quels sont les produits qui réalisent la plus grosse part de chiffre d’affaires.

Avant de construire notre graphique, il va falloir réaliser notre tableau de synthèse. Les familles de produits devront être automatiquement classées par valeurs décroissantes afin que notre histogramme soit conforme...

Effectuer des simulations

À l’aide de la valeur cible

Vous aurez parfois besoin, dans vos tableaux de calcul, d’effectuer des modifications successives d’une donnée jusqu’à ce que vous arriviez au résultat escompté. Vous pouvez procéder par tâtonnement en saisissant à la main différentes valeurs de plus en plus précises (par dichotomie) pour vous rapprocher de plus en plus de la valeur souhaitée. Cette procédure peut être longue et fastidieuse si elle est effectuée manuellement.

Excel dispose d’une fonctionnalité qui permet de faire ce travail à votre place : la valeur cible. Cette technique permet de déterminer, lorsque vous connaissez la valeur du résultat escompté, la valeur d’un paramètre. Excel va faire varier ce paramètre jusqu’à ce que le résultat de votre formule soit pratiquement égal à la valeur requise.

Nous allons utiliser quatre petits tableaux qui vont vous permettre d’assimiler rapidement cette technique.

Calcul d’un taux de marge

Le tableau de la feuille VC1 calcule le prix de vente TTC d’un produit.

images/Chap6-027.PNG

Les formules :

B4 : =B2*B3

B6 : =B4*(1+B5)

La concurrence vend un produit équivalent à 3200,00 € TTC, nous souhaitons le proposer à 3150,00 € TTC. Nous ne pouvons pas fabriquer cette machine à un prix inférieur. Le taux de TVA est aussi imposé.

La question à poser pour solutionner notre problème est donc : Quel doit être le coefficient de marge à appliquer pour obtenir un prix de vente TTC de 3150 € ?

Utilisons la technique de la valeur cible.

 Positionnez votre curseur sur la cellule contenant la valeur à atteindre (B6).

 Onglet Données - groupe Prévision, déroulez le menu Analyse scénarios puis sélectionnez Valeur cible.

 Dans la boîte de dialogue Valeur cible, cliquez dans la zone Valeur à atteindre puis saisissez 3150.

 Cliquez dans la zone Cellule à modifier puis positionnez le curseur sur le coefficient de marge (B3).

images/Chap6-028.PNG

 Validez enfin par OK.

Excel a trouvé une solution :

images/Chap6-029.PNG

Votre tableau affiche maintenant un coefficient de marge de 1.32. Si vous cliquez en B3, la valeur calculée réelle est visible dans...