Spécialiste Microsoft Office : Expert Avancé en Microsoft Excel

Aperçu

Microsoft Office Excel Formulas and Functions, Excel in Data Analysis and Data Science, Data Visualization, Automation What you'll learn:

Module 1:

Advanced Formulas and FunctionsExercise 1:

Logical FunctionsExercise 2:

Lookup and Reference FunctionsExercise 3:

Text FunctionsModule 2:

Data Analysis and VisualisationExercise 4:

Pivot TablesExercise 5:

Advanced ChartingExercise 6:

Conditional FormattingExercise 7:

Data TablesModule 3:

Data Management and CleaningExercise 8:

Data ValidationExercise 9:

Text to ColumnsExercise 10:

Removing DuplicatesModule 4:

Automation with Macros and VBAModule 5:

Advanced Data Import and ExportExercise 15:

Using Downloaded JSON Data for Data ConnectionsExercise 16:

Importing dataExercise 17:

Data Integration with SQLite and ExcelExercise 18:

Advanced Filtering and Sorting with Dynamic Arrays Welcome to Program:

Microsoft Office Specialist:

Microsoft Excel Advanced Expert by MTF InstituteCourse provided by MTF Institute of Management, Technology and FinanceMTF is the global educational and research institute with HQ at Lisbon, Portugal, focused on business & professional hybrid (on-campus and online) education at areas:

Business & Administration, Science & Technology, Banking & Finance. MTF R&D center focused on research activities at areas:

Artificial Intelligence, Machine Learning, Data Science, Big Data, WEB3, Blockchain, Cryptocurrency & Digital Assets, Metaverses, Digital Transformation, Fintech, Electronic Commerce, Internet of Things.

MTF is the official partner of:

IBM, Intel, Microsoft, member of the Portuguese Chamber of Commerce and Industry.MTF is present in 215 countries and has been chosen by more than 684 000 students.Course Author:

Dr. Alex Amoroso is a seasoned professional with a rich background in academia and industry, specializing in research methodologies, strategy formulation, and product development.

With a Doctorate Degree from the School of Social Sciences and Politics in Lisbon, Portugal, where she was awarded distinction and honour for her exemplary research, Alex Amoroso brings a wealth of knowledge and expertise to the table.In addition to her doctoral studies, Ms. Amoroso has served as an invited teacher, delivering courses on to wide range of students from undergraduate level to business students of professional and executives courses.

Currently, at EIMT in Zurich, Switzerland, she lectures for doctoral students, offering advanced instruction in research design and methodologies, and in MTFInstitute Ms. Amoroso is leading Product Development academical domain.In synergy between academical and business experience, Ms.

Amoroso achieved high results in business career, leading R&D activities, product development, strategic development, market analysis activities in wide range of companies. She implemented the best market practices in industries from Banking and Finance, to PropTech, Consulting and Research, and Innovative Startups.Alex Amoroso's extensive scientific production includes numerous published articles in reputable journals, as well as oral presentations and posters at international conferences.

Her research findings have been presented at esteemed institutions such as the School of Political and Social Sciences and the Stressed Out Conference at UCL, among others.With a passion for interdisciplinary collaboration and a commitment to driving positive change, Alex Amoroso is dedicated to empowering learners and professionals for usage of cutting edge methodologies for achieving of excellence in global business world.What is Microsoft Excel?Microsoft Excel is a powerful spreadsheet program that allows users to organize, analyze, and manipulate data. It's part of the Microsoft Office suite and is widely used by businesses, organizations, and individuals across various fields.Why is it important to know Excel at an advanced level?In data-driven world, advanced Excel skills are becoming increasingly essential.

Here's why:

Increased Efficiency:

Advanced users can automate repetitive tasks, saving time and reducing errors. They can also perform complex calculations and analyses quickly and accurately.Enhanced Data Analysis:

Advanced features like PivotTables, Power Query, and complex formulas allow users to extract meaningful insights from large datasets, identify trends, and make data-driven decisions.Improved Decision-Making:

By creating dynamic reports, dashboards, and visualizations, advanced users can present data in a clear and compelling way, facilitating better understanding and informed decision-making.Career Advancement:

Advanced Excel skills are highly sought after by employers.

They demonstrate a strong ability to handle complex data and contribute to organizational efficiency and effectiveness.Why is advanced Excel important for companies and experts?Companies:

Excel proficiency empowers employees to analyze financial data, track performance, manage projects, forecast trends, and automate various business processes. This leads to increased productivity, improved decision-making, and ultimately, greater profitability.Experts:

Professionals in fields like finance, accounting, marketing, and data analysis rely heavily on advanced Excel skills to perform their job functions effectively.

These skills enable them to analyze data, build models, generate reports, and provide valuable insights to their organizations or clients.Importance of the specified skill domains:

Advanced Formulas and Functions:

Mastering advanced formulas and functions allows users to perform complex calculations, manipulate data efficiently, and automate tasks.Data Analysis and Visualisation:

This module equips users with the skills to analyze data using tools like PivotTables, create compelling charts and graphs, and present data in a visually appealing and informative manner.Data Management and Cleaning:

This is crucial for ensuring data accuracy and reliability. Skills in this domain include importing, cleaning, transforming, and validating data to prepare it for analysis.Automation with Macros and VBA:

Automating tasks with macros and VBA significantly increases efficiency by streamlining repetitive processes and reducing manual effort.Advanced Data Import and Export:

This enables users to work with data from various sources, including databases and external files, expanding the scope and possibilities of their analyses.Advanced Filtering, Sorting, and Dynamic Arrays:

These skills allow users to effectively manage and manipulate large datasets, extract relevant information quickly, and perform dynamic calculations.By mastering these advanced Excel skills, individuals and organizations can unlock the full potential of this powerful tool and gain a significant competitive advantage in today's data-driven world.

Programme

• **Module 1 : Formules et fonctions avancées**
Fonctions logiques
Exercice 1 : Utiliser IF, AND, OR pour l'analyse conditionnelle
Fonctions de recherche et de référence
Exercice 2 : Utiliser VLOOKUP, HLOOKUP et INDEX-MATCH
Fonctions textuelles
Exercice 3 : Appliquer les fonctions CONCATENATE, TEXT et LEFT/RIGHT
• **Module 2 : Analyse et visualisation des données**
Tableaux croisés dynamiques
Exercice 4 : Créer et personnaliser des tableaux croisés dynamiques
Graphiques avancés
Exercice 5 : Concevoir des graphiques dynamiques et complexes
Mise en forme conditionnelle
Exercice 6 : Utiliser la mise en forme conditionnelle pour mettre en évidence des données
Tableaux de données
Exercice 7 : Mettre en œuvre des tableaux de données pour une analyse de scénario
• **Module 3 : Gestion et nettoyage des données**
Validation des données
Exercice 8 : Configurer et utiliser des règles de validation des données
Texte en colonnes
Exercice 9 : Diviser les données en colonnes séparées
Suppression des doublons
Exercice 10 : Identifier et supprimer les doublons dans les entrées de données
• **Module 4 : Automatisation avec macros et VBA**
Introduction aux macros
Enregistrement des macros pour automatiser les tâches répétitives
Programmation VBA de base
Écrire des scripts VBA simples pour des solutions personnalisées
• **Module 5 : Importation et exportation de données avancées**
Utilisation des données JSON téléchargées pour les connexions de données
Exercice 15 : Importer et connecter des données JSON
Importation de données
Exercice 16 : Importer des données de diverses sources externes
Intégration des données avec SQLite et Excel
Exercice 17 : Connecter et intégrer avec des bases de données SQLite
Filtrage, tri et tableaux dynamiques avancés
Exercice 18 : Travailler avec des tableaux dynamiques et utiliser des techniques de filtrage/tri avancées

---

Enseigné par

MTF Institute of Management, Technology and Finance

---

Matières

Business