Actualités et agenda
Nos formations
Établissement
Langue d'enseignement
English
Matières
MANAGEMENT OF INFORMATION SYSTEMS
Ce cours apparaît dans les formation(s) suivante(s)
Responsable(s)
G.MOURMANT
Intervenant(s)
G.MOURMANT
Présentation
Prérequis
- Participants should have an intermediate level in Excel including
* named range
* basic knowledge of Index, Match, SUM, IF formula
* pivot tables
- Participants should have a basic level of VBA (recording a macro, variables, loop, conditions)
* named range
* basic knowledge of Index, Match, SUM, IF formula
* pivot tables
- Participants should have a basic level of VBA (recording a macro, variables, loop, conditions)
Objectifs
At the end of the course, the student should be able to:
- use Excel for advanced analysis using following tools:
* advanced use of spreadsheet functions (sumifs, index/match,sommeprod, matricial functions)
* analytical tools in Excel (Solver, Pivot table, Powerpivot, DAX)
* intermediate and advanced use of Visual Basic for Applications
These competencies and/or skills contribute to following learning objectives
- 3.B Propose creative solutions within an organization
- 3.C Organize change management processes
- 4.A. Appraise the performance of a team
- 4.B Compose constructive personal feedback and guidance
- 4.C. Convey powerful messages using contemporary presentation techniques
- 5.D. Make effectual organizational decisions
- use Excel for advanced analysis using following tools:
* advanced use of spreadsheet functions (sumifs, index/match,sommeprod, matricial functions)
* analytical tools in Excel (Solver, Pivot table, Powerpivot, DAX)
* intermediate and advanced use of Visual Basic for Applications
These competencies and/or skills contribute to following learning objectives
- 3.B Propose creative solutions within an organization
- 3.C Organize change management processes
- 4.A. Appraise the performance of a team
- 4.B Compose constructive personal feedback and guidance
- 4.C. Convey powerful messages using contemporary presentation techniques
- 5.D. Make effectual organizational decisions
Présentation
- Advanced use of excel functions
* Quick overview of the basics : Sumifs, Index/Match,Sumproduct, matricial functions, conditional formatting
* Combination of functions in power-formulas
* Reporting & Dashboards
- Analytical tools in Excel
* Pivot Tables
* PowerQuery
* Powerpivot and DAX
- Intermediate and advanced use of VBA
* Quick overview of the basics : variables, loops and conditions.
* Intermediate : arrays, user-defined functions, userforms.
* Advanced : add-ins, class modules, events procedures, ribbon.
- During the course, we will also cover best practices for designing robust applications using spreadsheets.
* Quick overview of the basics : Sumifs, Index/Match,Sumproduct, matricial functions, conditional formatting
* Combination of functions in power-formulas
* Reporting & Dashboards
- Analytical tools in Excel
* Pivot Tables
* PowerQuery
* Powerpivot and DAX
- Intermediate and advanced use of VBA
* Quick overview of the basics : variables, loops and conditions.
* Intermediate : arrays, user-defined functions, userforms.
* Advanced : add-ins, class modules, events procedures, ribbon.
- During the course, we will also cover best practices for designing robust applications using spreadsheets.
Modalités
Organisation
| Type | Amount of time | Comment | |
|---|---|---|---|
| Présentiel | |||
| Cours interactif | 32,00 | ||
| Autoformation | |||
| E-Learning | 10,00 | ||
| Lecture du manuel de référence | 10,00 | ||
| Travail personnel | |||
| Group Project | 48,00 | ||
| Overall student workload | 100,00 | ||
Évaluation
- MCQ
- Students will be asked to develop a fully functional data-analysis application to solve an existing problem
- Students will be asked to develop a fully functional data-analysis application to solve an existing problem
| Control type | Duration | Amount | Weighting |
|---|---|---|---|
| Autres | |||
| Projet Collectif | 13,00 | 1 | 50,00 |
| Contrôle continu | |||
| QCM | 1,00 | 1 | 50,00 |
| TOTAL | 100,00 | ||
Ressources
Bibliographie
Professional Excel Development: The Definitive Guide to Developing Applications Using Microsoft Excel, VBA, and .NET, 2009, by Rob Bovey (Author), Dennis Wallentin (Author), Stephen Bullen (Author), John Green (Author) -
Alexander, Michael, and Richard Kusleika. Excel 2016 Power Programming with VBA. John Wiley & Sons, 2016. -
Alexander, Michael, and Richard Kusleika. Excel 2016 Power Programming with VBA. John Wiley & Sons, 2016. -
Ressources Internet