Ce cours traite (1) de l'utilisation du tableur EXCEL en tant qu'outil d'analyse et de simulation ; (2) de la programmation VBA (visual basic pour applications).
Après une première partie destinée avant tout à familiariser les étudiants avec les outils d'édition avancées d'Excel (manipulations sur plusieurs feuilles, mise en forme et verrouillage des cellules, etc.), l'accent est mis sur les différentes manières de transcrire une réalité économique sous un tableur et la recherche des meilleures solutions afférentes (sous la forme d'une optimisation par exemple, ou encore sous la forme d'une simulation, etc.). La programmation VBA (macro-instructions) est un instrument indispensable dans ce cadre. Deux aspects de la programmation sous Excel seront détaillés : les fonctions personnalisées et les macros instructions (macros).
L'étudiant doit déjà bien connaître les bases de l'utilisation d'un tableur (cellules, feuille de calcul, classeur, formules et fonctions simples, formattages et graphiques).
Voici la liste détaillée des thèmes qui seront étudiés :
(1) la résolution de problèmes et
l'analyse à l'aide de l'outil valeur cible et du solveur ;
(2) la simulation
avec les tables et le gestionnaire de scénarios ;
(3) l'analyse statistique
des tableaux de données, avec notamment la régression et le calcul matriciel ;
(4) l'analyse des listes de données, tableaux croisés dynamiques, filtres
simples et élaborés, régression ;
(5) programmation VBA : les fonctions
personnalisées ;
(6) programmation VBA : les macros-instructions ;
(7)
programmation VBA : les boîtes de dialogue.
Ce cours est principalement dispensé en licence (L3), plutôt dans les facultés de sciences humaines et sociales.
Ressources pour le cours - Outils d'analyse et Programmation
| Thème | Cours | Exercices | Fichier Excel |
|---|---|---|---|
| Programmation VBA sous Excel. Bases de l'algorithmie et du langage Visual Basic. Programmation des fonctions personnalisées et des macros. Manipulation des objets Excel, en articulier le type Range (plage de cellules) |  |
- |  |
| TD n°1. Construction d’une feuille de calcul. Références absolues et relatives. Elaboration de graphiques. |  |
|
|
| TD n°2. Introduction à la programmation VBA. Branchements conditionnels. Ecriture et utilisation des fonctions personnalisées. | |
|
|
| TD n°3. Simulations et scénarios. Table de simulation à 1 et 2 entrées. Gestionnaire de scénarios. Programmation VBA des fonctions personnalisées avec branchement conditionnels et branchements multiples. | |
|
|
| TD n°4. Les boucles en programmation VBA. Exploitation du type Range (plage de cellules) dans les fonctions personnalisées. | |
|
|
| TD n°5. Résolution des problèmes et optimisation. Valeur cible et solveur. | |
|
|
| TD n°6. Programmation VBA des fonctions personnalisées. Les boucles while et for each. Le type Variant. | |
|
|
| TD n°7. Programmation des macros VBA. Exploitation directe des classeurs et feuilles. Programmation des simulations. | |
|
|
| TD n°8. Programmation des macros VBA sur les sélections. Sélection simples (une seule zone) et multiple (plusieurs zones). | |
|
|
| Exercices - Problèmes | Enoncé | Fichier Excel | |
| Publipostage. Simulation à 1 et 2 entrées, gestionnaire de scénarios. | |
|
|
| Exportation de rasoirs en Syldavie. Fonctions VBA, gestion du type Range (plage de cellules), table de simulation à 1 entrée, valeur cible. | |
|
|
| Simulation de gestion de portefeuille sans programmation VBA. Construction de feuille de calcul Excel. Fonction ALEA(), table de simulation, fonction NB.SI. | |
|
|
| Production de jouets à l'aide de plusieurs matières premières. Matrice de production. Optimisation (solveur) et simulation. Programmation VBA, fonction personnalisée et macro. | |
|
|
| Calcul simplifié de l'impôts à partir des salaires des conjoints et du nombre d'enfants. Table de simulation et simulation avec macro VBA. Programmation de macro pour le traitement des sélections multiples. | |
|
|
| Machines et production. Optimisation de la production en fonction des capacités des machines. Solveur, programmation VBA des simulations. | |
|
|
Autres ressources - Excel Avancé
| Ressource | Accès |
| Portails et supports en ligne | |
|---|---|
| Le compagnon Info. Particulièrement complet, avec des vidéos. Excellent pour une initiation. |
|
| Cours VBA gratuit. Aborde tous les thèmes (et plus même) de notre cours de programmation VBA avec pédagogie. |
|
| La page VBA de developpez.com |
|
| Cours Excel - Jacques Chaussard |
|
| Site de Christian Herbé - XL avancé |
|
| Un autre portail, avec une section sur EXCEL. Les cours sont bien expliqués et illustrés. Une section complète est consacrée à la programmation VBA (Michel Defawes) |
|
| VBAXL -- Un site consacré à la programmation VBA |
|
| Open Office Calc (OOCalc) | |
| Le tableur libre Open Office Calc (idem pour LibreOffice) propose les mêmes outils qu'Excel, notamment les outils d'analyse (Valeur cible, solveur, simulation, tableaux croisés dynamiques, etc.). A la différence qu'OOCalc est totalement gratuit. |
|
| Il est possible de programmer en VBA sous OOCalc. Il suffit d'introduire préalablement l'instruction magique "Option VBASupport 1". Ces deux classeurs en témoignent (attention, réduire le niveau de sécurité pour pouvoir exécuter les macros). |
|
Ricco Rakotomalala