Informatique et programmation L2

Excel

Préparation au TOSA réalisée en TD.

CM

Créer une base de données avec Excel (Utilisation de formulaire et introduction Visual Basic), pour des informations complémentaires : www.polykromy.com. Gestion de bouton, création de macro, correction d'erreur, boucle, condition sous VB.

Fusion (join / merge) de tables de plusieurs manières : RECHERCHEV (RECHERCHE, RECHERCHEH), EQUIV + INDEX, MACRO VB, fusion avec une clé composée.

Construction de tableaux croisés dynamiques. Appréhension de la fonction SOMMEPROD.

Utilisation de la fonction tables pour étudier la sensibilité d'une formule. Utilisation du Solveur* pour trouver une solution optimale au problème de publipostage de l'entreprise de vente par correspondance de parfum. Consolidation de plusieurs feuilles Excel par l'outil de consolidation des données.

La consolidation des données en utilisant les tableaux croisés dynamiques (sources multiples). Régression de trois manière : graphique (nuage de point) avec une courbe de tendance, les fonctions comme DROITEREG, LOGREG (Schift + Alt + Entrée), outil d'analyse avec la corrélation des variables explicatives. Changement de variables (F/M transformé en 0/1, introduction de la variable (âge - 40)^2.

Importation des données de type texte (*.csv), notamment avec la fonction convertir. Le calcul matriciel sous excel, les conditionnalités, les fonctions financières, remplissage de formulaire pdf.

*sous OpenOffice/LibreOffice, le solveur nécessite l'installation de deux plug-in : http://extensions.openoffice.org/en/project/statistical-data-analyser-oo-calc et http://extensions.openoffice.org/en/project/solver-nonlinear-programming-beta

VB (Excel)

Créer une base de données avec Excel (Utilisation de formulaire et introduction Visual Basic), pour des informations complémentaires : www.polykromy.com.

Création et mise à jour automatisé de la table Noms Clients (grâce à la validation des données et à l'utilisation de VB). Utilisation de la fonction tables pour étudier la sensibilité d'une formule.

1. Editeur de macro Alt+F11
2. Conditionnalité If <> Then... Select Case
3. Messages MsgBox(prompt, type, titre),
4. Les répétitives For Next, For each ... in ...., While Wend
5. Adresse & cellule,
6. Enregistreur de macro.

TD Assurance Viticole de la cave Guillerette dans le pays d'Oc

La législation des vins français d'Indication Géographique Protégée (IGP) impose un rendement maximal de 90 hl/ha soit environ 10.000 kg/ha pour le pays-d’oc. La Cave Guillerette, pour répondre au besoin du marché, décide d'encourager la production de vin de qualité en rétribuant un prix supérieur aux vins de qualité supérieure et réduit le rendement maximal à 80 hl/ha. Ce prix dépend donc du cépage et du degré d’alcool résultant de l'opération de vinification. Pour fidéliser ses adhérents, elle souscrit 'pour compte' une assurance rendement qui prend en compte la perte de qualité, au taux avantageux de 2,5 %.

Les bonifications sont proposées avant la récolte aux adhérents. Par contre, le prix de référence de la vendange est fixé a posteriori en fonction des résultats de la coopérative. Pour simplifier notre exercice, on considère que les bonifications et les prix de référence sont connus à l'avance et sont les mêmes en 2010, 2011 et 2012. Le prix est de 96€/hl, le déclassement en vin de table donne un prix de 59€/hl. Il peut être déclassé soit par un manque de sucre (ou d'alcool) soit par une mauvaise conduite culturale (non respect du cahier des charge).

Le TD vise à construire des macros dans un fichier xlsm pour gérer la base de données de souscription.

1. Créer une base de parcelles (environ 10), avec le numéro de la parcelle 'PXXX', le numéro de l'adhérent 'AXXXX', le cépage (majuscule), sa surface (en are) et la commune de la parcelle. Bien sûr, la liste des cépages est préenregistrée.
2. Créer une base d'Adhérent (environ 6), avec le numéro de l'adhérent 'AXXXX', son nom, son prénom, sa commune de domiciliation. Vérifier maintenant dans la base parcelle que l'adhérent existe.
3. Grâce aux fonctions 'Recherchev', automatiser dans un nouvel onglet le calcul du prix et du classement en fonction du degré d’alcool et du cépage dans le cas où il n'a pas été déclassé.
4. Création d'une base de récolte (environ 20), avec l'année de récolte, le numéro de parcelle, le rendement en kg, le rendement en hl, le taux d'alcool et si elle a été déclassée pour mauvaise conduite culturale. On ajoute aussi le rendement historique de la parcelle. Un contrôle de cohérence est fait pour vérifier que le rendement en hl soit entre 100 et 150 fois plus petit que le rendement en kg. On vérifie également que le rendement obtenu est inférieur au rendement autorisé.
5. Automatiser la création d'une base détaillée avec toutes les informations :
   1. le numéro de la parcelle 'PXXX', le numéro de l'adhérent 'AXXXX', le cépage (majuscule), sa surface (en are) et la commune de la parcelle
   2. son nom, son prénom, sa commune de domiciliation,
   3. l'année de récolte, le rendement en kg, le rendement en hl, le taux d'alcool,déclassé ou non,
   4. le prix de référence, le prix obtenu, la bonification, le capital souscrit pour l'assurance (égal au prix de référence fois le rendement historique si la production n'est pas déclassé), la production en hl et en €.
6. Mettre un bouton permettant l'édition des résultats d'un adhérent sur une année (on utilise ActiveSheet.PrintPreview).
7. L'assurance récolte intervient si la perte de rendement par rapport au rendement historique est supérieure à 30%. Elle applique une franchise de 25%.
   1. Calculer les indemnités dans le cas où nous ne tenons pas compte de la qualité.
   2. La prise en compte de la qualité:
      1. L'assureur est exempté de payer l'indemnité en cas de mauvaise conduite culturale
      2. La perte de qualité est égale à (1- le prix obtenu / le prix de référence).
      3. Recalculer les indemnités dans le cas où on applique la perte de qualité.

Entrainement

Exercices proposés par M. Dubois : https://sites.google.com/site/florentduboiseco/teaching/informatique_l2

Bibliographie

Travaux pratiques avec Excel 2007 et 2010 - Fonctions avancées: Fonctions avancées : traitement des données, travail collaboratif, Windows Live SkyDrive, VBA (2011) Fabrice Lemainque

Travaux pratiques avec Excel 2007 et 2010: Saisie et mise en forme, formules et exploitation des données, courbes et graphiques (2011) Fabrice Lemainque, 128p.

http://office.microsoft.com/fr-fr/excel-help/decouverte-d-excel-2007-creer-des-formules-RZ010074593.aspx

http://office.microsoft.com/fr-fr/excel-help/decouvrir-les-boucles-avec-les-macros-excel-RZ001150634.aspx

http://www.polykromy.com/blog/excel-base-de-donnees.htm

Combien coûterait une formation équivalente par jour de formation (7h/j) ?