[PDF] fonction solveur excel
[PDF] solutions harcèlement scolaire
[PDF] résolution système d'équation non linéaire excel
[PDF] qu'est ce que le harcèlement scolaire ?
[PDF] prévention harcèlement scolaire
[PDF] agir contre le harcèlement ? l'école pdf
[PDF] harcèlement sévère définition
[PDF] le harcèlement entre élèves le reconnaître le prévenir le traiter
[PDF] introduction ? l'informatique cours pdf
[PDF] marché de la confiserie 2015
[PDF] harlan coben innocent pdf
[PDF] harlan coben pdf francais
[PDF] harlan coben livres pdf gratuit
[PDF] harlan coben une chance de trop pdf
[PDF] sans un adieu pdf
17/02/2013
1
Excel Avancé
Plan
Outils de résolution
La valeur cible
Le solveur
Interactivité dans les feuilles
Fonctions de recherche (ex: RechercheV)
Formulaires »
Outils de simulation
Table
Scénario
17/02/2013
2
La valeur cible
Cette cellule devant contenir une formule
Cet outil a besoin de 3 paramètres
La référence de la cellule à laquelle on veut affecter la valeur particulière atteindre la valeur cible
La valeur cible : Exemple
A B C D E F G
1 Matière Eco Finan Info Math-Fi Audit Moy
2 Note 10 12 14 16 8,5
3 Coef 2 3 1 2 4
Audit pour que la moyenne générale soit égale à 15. Le cellule à définir est G2 (celle qui contient la moyenne)
La valeur à atteindre est 15 (la valeur cible)
La cellule à modifier est F2
17/02/2013
3
La valeur cible : Exemple
Sous 2007 : Données/Analyse de
scénarios/Valeur cible
La note doit être égale à 19,5
Valeur cible : fonctionnement
Pour atteindre la valeur cible, Excel ajoute ou retranche de
Exemple : On veut calculer la racine de 4.
Dans B1, on saisit la formule = A1*A1 4
La cellule à définir est B1
La valeur à atteindre est 0
La cellule à modifier est A1
Si au départ, on met --2
17/02/2013
4
Valeur cible : application
Concevoir une feuille de calcul qui permet :
M, T, S Tester votre feuille de calcul avec les données
M = 200.000
T = 4,5 %
S = 1500
En utilisant la valeur cible, trouver
La valeur Min de S pour finir de rembourser au bout de 20 ans La valeur de T pour finir de rembourser au bout de 10 ans avec
M=200.000 et S=1500 ? Expliquer le résultat
de rembourser au bout de 10 ans avec S=1500 et T=4,5% ?
Le solveur
Outil plus puissant que la valeur cible
Il peut maximiser, minimiser, ou atteindre une valeur
En modifiant plusieurs cellules
En tenant compte de contraintes
A B C D E F G
1 Matière Eco Finan Info Math-Fi Audit Moy
2 Note 10 12 14 5,83
3 Coef 2 3 1 2 4
Quelles notes doit-on avoir en Math-fi et en Audit pour que la moyenne soit égale à 15 ?
17/02/2013
5
Le solveur
Le solveur : les contraintes
17/02/2013
6
Le solveur : fonctionnement
Tout comme pour la valeur cible, Excel
essaye de modifier le moins possible les cellules modifiables, tout en respectant les contraintes imposées, afin (max, min ou =) La difficulté consiste à bien organiser les données dans la feuille de calcul afin de les exploiter facilement A B C
1 Montants Taux
2 Quantité vendue 1 000
3 Prix de Vente unitaire 5 000,00 ¼
4 Prix d'achat unitaire 3 000,00 ¼
5 Chiffre d'affaires 5 000 000,00 ¼ 100%
6 Prix d'achat total 3 000 000,00 ¼ 60%
7 Marge Commerciale 2 000 000,00 ¼ 40%
8 Frais de personnel 1 000 000,00 ¼
9 Loyer 100 000,00 ¼
10 RÉSULTAT 900 000,00 ¼ 18%
peut pas réduire les frais de personnel en dessous de 800000.
17/02/2013
7
Le solveur
Le recensement de toutes les contraintes
La conception de la feuille de calcul afin de prendre en compte toutes les contraintes Sachant que chaque contrainte utilise une cellule, il faut faire en sorte à ce que pour les contraintes de la forme
Expression_1 Expression_2
expressions
Solveur Exemple (énoncé) -
Trois machines M1, M2 et M3 peuvent produire chacune deux types de pièces P1 et P2
Pi sur la machine Mj est donné dans le tableau
suivant (temps en heures): On veut fabriquer à moindre coût 6 pièces de type P1 et 8 pièces de type P2. La machine M1 est disponible 14 heures, les machines M2 et M3 sont disponibles chacune 24 heures. Le coût horaire de M1 (respectivement M2 et M3) vaut 7 (respectivement 5 et 6).
M1 M2 M3
P1 3 4 4
P2 4 6 5
17/02/2013
8
Solveur Exemple (modélisation) -
Notons par xij le nombre de pièces Pi fabriquées par la machine Mj (i=1 ou
2 et j = 1,2 ou 3).
Le nombre de pièces fabriquées est positif : xij > 0 pour tous i,j
Nombre de pièces P1 fabriquées :
X11 +X12 + X13 = 6
Nombre de pièces P2 fabriquées :
X21 +X22 + X23 = 8
La machine M1 est disponible 14h. Le temps nécessaire pour fabriquer P1 (resp. P2) est de 3h (resp. 4h) :
3X11 +4X21 <= 14
Idem pour les deux autres machines :
4X12 +6X22 <= 24
4X13 +5X23 <= 24
Le coût total de fabrication est donné par :
7(3X11 +4X21 )+5(4X12 +6X22 )+6(4X13 + 5X23 )
Solveur Exemple (Résolution) -
Feuille Excel :
17/02/2013
9
Solveur Exemple (Résolution) -
Solveur :
Solveur Exemple (Résolution) -
Feuille Excel (solution) :
17/02/2013
10
La fonction RECHERCHEV
RECHERCHEV a besoin de 3 (ou 4) paramètres
La valeur recherchée
La plage où la rechercher
Le numéro de colonne dans la plage contenant la valeur
à afficher
Exemple :
RechercheV(A1; C2:F6; 3)
va chercher la valeur de A1 dans la plage C2:F6 " en fait, seulement dans C2:C6 » Une fois trouvée, la valeur située dans la même ligne que A1 et dans la colonne 3 dans C2:F6, i.e colonne E, sera affichée
RechercheV
La recherche se fait toujours et seulement dans la première La première colonne doit être triée par ordre croissant proche qui sera utilisée RechercheV(val;plage;col; FAUX) évite de retourner une valeur proche. Retourne #N/A ESTNA(RechercheV(val;plage;col; FAUX)) est VRAIE si la recherche est infructueuse (i.e retourne #N/A)
17/02/2013
11
Application
Utile pour éviter de saisir des valeurs
Zone de liste déroulante
Permet de choisir dans une liste de valeurs
Évite les erreurs de frappe en limitant les choix La valeur sélectionnée dans cette liste doit être associée à une cellule
17/02/2013
12
Application
Modification de la feuille " Facture »
La barre de défilement
Permet, en déplaçant le curseur, de sélectionner une valeur numérique dans un intervalle. La valeur sélectionnée est affectée à une cellule.
Les paramètres sont :
La cellule à laquelle sera affectée la valeur sélectionnée
17/02/2013
13
Application
Reprendre le classeur pour la gestion des
prêts.
Ajouter une barre de défilement permettant de
et 5%. Le pas étant de 0,05%
Application 2
Approximation de la loi binomiale de
paramètres n et p par la loi normale de paramètres np et racine(np(1-p)).
On fait la simulation pour n=10, 40 et100
p est tel que 0 < p < 1.
17/02/2013
14
Gestionnaire de scénario
Permet de faire varier des cellules pour voir
comment ces variations influent sur le résultat des calculs.
Le gestionnaire de scénario agit dans le sens
contraire de la valeur cible (ou du solveur) qui part du résultat pour trouver les données.
Gestionnaire de scénario
Soit le tableau
On aimerait construire un tableau faisant apparaître les et de marge
17/02/2013
15
Gestionnaire de scénario
Les cellules variables sont B1 et B2
On peut créer plusieurs scénarii en modifiant
à chaque fois les valeurs de B1 et B2
Enfin, on peut faire la synthèse de ces
derniers
Gestionnaire de scénario
17/02/2013
16
Gestionnaire de scénario
Gestionnaire de scénario
La synthèse
17/02/2013
17
Gestionnaire de scénario
Scénario : application
VPM(T ; nb ; val)
emprunter un montant val
à un taux T
et on veut étaler nos remboursements sur nb échéances Par défaut, VPM retourne une valeur négative. il faut transformer T en un taux mensuel Construire un tableau synthétisant la valeur de la mensualité pour des taux de 4 et 5%
Remboursements sur 10, 15 et 20 ans
17/02/2013
18
Les tables
Des fois, les scénarios sont lourds à
manipuler
On peut utiliser à la place des tables
Table » permet de générer un tableau
fonction des variations
De 2 autres cellules (table à 2 dimensions)
Les tables à 1 dimension
On veut construire un tableau affichant les
taux variant de 4 à 5% par pas de 0,05%
17/02/2013
19
Les tables à 2 dimensions
On veut construire un tableau affichant les
200000 échelonné sur 20 ans en fonction
de 1
Voir illustration
Les tableaux croisés dynamiques (TCD)
Permettent de composer rapidement un tableau
synthèse provenant d'une masse de données (base de données sous Excel). Ils sont dynamiques : il est possible d'ajouter, de retirer et de modifier la présentation du tableau.
Terminologie :
Champ : Caractéristique sur une personne, une chose ou un événement qui doit être conservé dans une base de données. Enregistrement : Série de champs qui décrivent une personne, une chose ou un événement.
17/02/2013
20
TDC : Exemple
Voir la démo
Comment faire
base de données
Insertion AE TblCroiséDynnamique
17/02/2013
21
Illustration (comment faire)
17/02/2013
22
17/02/2013
23
Bases de données sous Excel
On considère le fichier commandes.xls.
que la société ABCD a passées à ses fournisseurs. Les informations portent sur 3 années (2000, 2001 et 2002). Chaque ligne de cette liste décrit une ligne-commande qui elle même est décrite par un ensemble de champs : Société ; le nom de la société à qui on a envoyé la commande
N°Employé
Ville : le nom de la ville où se trouve la société
Pays : le pays de la société
Date : la date à laquelle on a émis la commande
N°Commande
Nom du produit : nom du produit commandé.
Prix total
Questions :
Quelles sont les commandes passées en
Allemagne
Solution: utiliser les filtres
Extraire les commandes passées en Allemagne et
le détail des différentes villes
Solution : extraction + sous-totaux
Analyser le montant global des commandes,
réalisé avec chaque pays
Solution : utiliser les TCD
17/02/2013
24
Observations :
Chaque pays présent dans la base est répété dans ce pays Les informations sur les sociétés sont également répétées
Solution :
Fractionner la feuille en plusieurs feuilles
UTILISER UN SGBD
quotesdbs_dbs12.pdfusesText_18
[PDF] Solver Tutorial: Excel 2013