Remerciez-le!

Remerciez @Admin pour avoir partagé cet document gratuitement, de la manière la plus simple, en partageant sur les réseaux sociaux.

Master Génie Industriel Production et maintenance Réalisé par : Encadré par : 

Master Génie Industriel Production et maintenance Réalisé par : Encadré par :  Jabli Badr Mr. Olivier Losson  Mohamed Qaztabi 2015/2016 2015/2016 Sommaire Sommaire I. I. Objectif du TP Objectif du TP II. II. Exercice1 : Formules Exercice1 : Formules III. III. Exercice2 : Graphes et éléments de formulaires Exercice2 : Graphes et éléments de formulaires IV. IV. Exercice3 : Solveur Exercice3 : Solveur V. V. Exercice4 : Traitement de données Exercice4 : Traitement de données I-Objectif du TP : I-Objectif du TP : Apprendre à manipuler sur Excel : le traitement des formules, les graphes et les éléments de formulaires, le Solveur et le traitement de données. II-Exercice1 : Formules II-Exercice1 : Formules Nous suivons l’évolution d’un stock des pièces par rapport au temps, connaissant le stock initial et le nombre de pièces consommées chaque jour. A partir des simples formules de départ, en figeant bien évidemment les valeurs constantes (telle que la commande journalière dans notre cas), on peut par la suite, après obtention du premier résultat, trouver automatiquement les autres résultats par poignée de recopie. 1) 1) Pour créer la colonne Evolution du Stock il faut cliquer sur le bouton Fusionner (voir Fig.1-1) puis on remplit les autres colonnes (voir Fig. 1-2) 2) 2)  Le stock initial, les consommations quotidiennes et le nombre de pièces commandées chaque jour étant connus  La formule de calcul du stock final est définie sur le cercle bleu. La cellule F3 est une constante et donc nous l’avons figé en ligne et colonne par le symbole $ : $F$3. Le premier résultat donne 19 après avoir tapé sur « Entrée ».  Le stock initial du jour suivant est donc le stock final du jour précédent (B4=D3) dans les cercles bleus sur Excel utilise l’instruction sur la barre de formule et place le résultat de D3 dans la cellule B4  En utilisant la poignée de recopie, nous obtenons automatiquement le stock initial et final pour le reste des cellules. Pour le calcul du reste de stock final, Excel utilise la première formule et l’exécute par ligne suivante. De même pour le stock initial, Excel place dans la cellule suivante du stock initial le résultat de calcul du stock final précédent. 3) 3)  Pour le calcul de la somme de sortie de stock, nous avons sélectionné la cellule C9 puis nous avons entré la formule (barre de formule). Après validation, Excel somme toutes les valeurs de la colonne C allant de de C3 à C8 et place le résultat dans la cellule C9.  Pour le calcul de la moyenne de stock final, nous avons sélectionné la cellule D9 puis nous avons entré la formule (barre de formule). Après validation, Excel calcule la moyenne de toutes les valeurs de la colonne D allant de de D3 à D8 et place le résultat dans la cellule D9. Pour retrouver ces informations sans saisir aucune formule, nous pouvons utiliser les opérateurs somme et moyenne de Excel ensuite sélectionner la colonne sur laquelle se portera l’opération et le résultat est donné automatiquement dans la cellule suivante. III-Exercice2 : Graphes et éléments de formulaires III-Exercice2 : Graphes et éléments de formulaires Dans cet exercice, il est au but de tracer la courbe de la fonction définie par f(x)=x²+4x+5 sur l’intervalle [-10, 6] avec type de graphique « Nuage de points reliés par une courbe ». Nous avons créé un bruit b(x) compris entre ±k% de f(x) en utilisant la fonction ALEA (). K étant un paramètre réglable que nous avons lié au compteur Excel. Le compteur nous a permis de fixer automatiquement la valeur de k par incrémentation ou décrémentation à pas fixe (pas = 10 dans notre cas). Sur le même graphe, nous avons ensuite tracé la courbe de la fonction bruitée f(x)+b(x) sur le même intervalle et tracé enfin sa courbe de tendance polynomiale d’ordre 2 (courbe en noir). 1) 1) X étant les valeurs de l’intervalle [-10, 6] : Colonne B4 : B20 La fonction f(x)=x²+4x+5 : Colonne C4 : C20 Dans la cellule C4 nous insérons la formule : =B4*B4+4*B4+5. Le résultat (cellule C4) est obtenu après validation. Pour le reste, le résultat est obtenu automatiquement avec la poignée de recopie (C5 : C20). Pour tracer la courbe de la fonction f(x) en cliquant sur insertion dans la barre de menu puis graphe puis en sélectionne le type de courbe ici en veut <Nuage de point reliés par une courbe>. 2) 2)  Bruit aléatoire b(x) : Le bruit b(x)= ALEA ()*(b- a) + a Nous avons défini un intervalle [a, b] avec a=-k*f(x)/100 et b=k*f(x)/100 3) 3)  Fonction bruité f(x)+b(x) : Dans la colonne E5 : E21, la fonction bruitée est la somme de f(x) et de b(x). Formule : En E5 : =C5+D5. Pour le reste, le résultat est obtenu automatiquement avec la poignée de recopie (E6 : E21).  Courbes : Les courbes sont obtenues en sélectionnant les colonnes concernées (Dans notre cas toutes les colonnes) puis insertion – nuages de points avec courbe lissée et marqueur. Les courbes sont tracées automatiquement avec légende. Pour la courbe de tendance, nous avons sélectionné la courbe de la fonction f(x)+b(x) puis clic droit \ajouter une courbe de tendance \ nous cochons la courbe de tendance polynomiale, ordre 2 et afficher l’équation sur le graphique. Après la fermeture de la fenêtre, nous obtenons la courbe de tendance (en violet). 4) 4) Le compteur se trouve dans le formulaire. Pour l’afficher, nous sommes allés dans Affichage \Barre d’outils \ Formulaire. Du formulaire, nous avons choisi Compteur. Paramétrage : Valeur mini : 0 \ Valeur maxi : 30000 \ Changement de pas : 10 \ Cellule liée : H5. En jouant sur le compteur, on fait varier la valeur de k. Ce qui a pour influence la variation du bruit b(x) et par conséquent, la courbe de la fonction bruitée se déforme. A k=0, l’équation y de la courbe de tendance est égale à f(x), la courbe de tendance est confondue à la courbe de f(x). Lorsqu’on augmente la valeur de k, les coefficients de l’équation de la courbe de tendance varient de manière aléatoire mais sa courbe fluctue autour de la courbe de f(x) (fluctuation asymptotique vers x et vers y). IV-Exercice n°3 : Solveur IV-Exercice n°3 : Solveur Dans cet exercice, nous avons utilisé l’outil solveur pour trouver les valeurs de a et b fournissant une meilleure adéquation (somme des erreurs quadratiques minimale) pour la fonction g(x)=sin (αx)*e^-b*x. 1) 1) Nous avons donc grâce au menu Données/Données externes/importer des données…, importer en A1 les données du fichier http://lagis-vi.univ-lille1.fr/~lo/ens/gi/data.txt. 2) 2) En colonne A : (A2 : A12), les valeurs de x En colonne B : (B2 : B12), les données associées à x En colonne C : (C2 : C12), les résultats de calcul de la fonction g(x)=sin (αx)*e^-b*x. Pour le calcul de g(x), on s’est placé dans la cellule C2 puis nous avons entré la formule : =SIN ($B$14*A2)*EXP (-$B$15*A2). Notons que a et b étant des valeurs données arbitrairement au départ. Nous avons fixé les cellules contenant a et b ($B$14 et $B$15) car Excel utilise à chaque passage à la ligne suivante de C les mêmes valeurs de alpha et beta. Le résultat du reste de lignes (C3 : C12) est obtenu avec la poignée de recopie. En colonne D (D2 : D12), le résultat de calcul de l’erreur quadratique E². E²= (g(x)- données) ² Formule de l’erreur quadratique : En se plaçant dans la colonne D2, nous avons entré la formule : = (C2-B2)*(C2-B2). Le résultat du reste de lignes (D3 : D12) est obtenu avec la poignée de recopie. La somme des erreurs (ΣE²(x)) est calculée en colonne D13 par la formule =SOMME (D2 : D12). 3) 3) L’outil solveur nous a permis de trouver a et b qui donnent la somme des erreurs (ΣE²(x)) la plus minimale possible. Nous avons défini dans les paramètres solveur l’objectif à atteindre : La somme des erreurs (ΣE²(x)) (cellule $D$13). Cette somme doit être minimale (cocher min). Puis nous avons sélectionné les cellules variables (alpha et beta) c’est-à-dire ($B$14:$B$15). Après avoir cliqué sur résoudre, nous avons obtenu les valeurs de alpha et beta donnant la somme des erreurs (ΣE²(x)) minimale. V-Exercice 4 : Traitement de données V-Exercice 4 : Traitement de données 1) Dans cet exercice, nous avons au départ, importer les données du fichier http://lagis- vi.univ-lille1.fr/~lo/ens/gi/etudiants.txt. 2) 2) En utilisant le menu Données/Trier, Nous avons spécifié le tri par Pays en Ordre décroissant et en CP par Ordre croissant. Après validation, nous avons obtenu le résultat suivant : Excel range les noms des Pays par ordre alphabétique décroissant. Puis dans chaque plage de Pays, Excel range les valeurs de CP par ordre croissant. 3) 3) Avec le menu Données/Formulaire…, nous avons trouvé le nombre d’étudiants se prénommant Philippe, puis les étudiants qui ont un nom commençant par E et un prénom par P. Cette opération permet de spécifier au départ dans la fenêtre formulaire, uploads/Sante/ corrige-jabli-badr-qaztabi-mohamed-2.pdf