Ce cours apparaît dans les formations suivantes :
• IÉSEG > MSc in Big Data Analytics for Business > Semester 1 > 4,00 ECTS

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)

Objectifs du cours

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

Contenu du cours

- 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.

Modalités d'enseignement

Organisation du cours

Méthodes pédagogiques

• E-learning
• Interactive class
• Research
• Visits/fields trips

Évaluation

- MCQ
- Students will be asked to develop a fully functional data-analysis application to solve an existing problem

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. -

Ressources internet

• Another set of online ressources will be provided to the students.