Bureautique

Excel Perfectionnement : maîtriser les fonctions avancées, les TCD et Power Query pour gagner en efficacité

3 jour(s) • 21h

Description

Cette formation vous permet de franchir un cap dans votre maîtrise d'Excel. En trois jours, vous apprendrez à construire des formules avancées, exploiter les tableaux croisés dynamiques, automatiser la préparation de vos données avec Power Query et créer des visualisations percutantes. Vous verrez également comment sécuriser vos fichiers et réaliser des simulations d'aide à la décision. La formation est compatible avec Excel 2021 et Microsoft 365.

Objectifs pédagogiques

Construire des formules avancées pour fiabiliser l'exploitation des données
Analyser des données avec les tableaux croisés dynamiques
Créer des restitutions visuelles adaptées à un besoin professionnel
Préparer et transformer des données avec Power Query
Mettre en place des contrôles, des validations et des protections adaptés au contexte professionnel
Réaliser des simulations simples d'aide à la décision

Public concerné

Collaborateurs des directions financières, juridiques, RH ou commerciales, contrôleurs de gestion, chefs de projet et assistants de direction souhaitant exploiter les fonctionnalités avancées d'Excel dans un contexte professionnel.

Prérequis

Bonnes connaissances des fonctionnalités de base d'Excel : saisie, formules simples, mise en forme, création de graphiques de base.

Connaissances équivalentes à celles apportées par une formation Excel initiation.

Déroulé du programme

Séquence 1 : Rappels et perfectionnement sur les bases (1h30)

Références relatives, absolues ($) et mixtes : maîtriser les ancres dans les formules -- approfondi en TP
Gestion des onglets : nommer, déplacer, grouper, lier des feuilles -- approfondi en TP
Liaisons entre feuilles et entre classeurs : formules de liaison, mise à jour, gestion des sources -- approfondi en TP
Révision des formules de base et priorité des opérateurs -- rappel en démonstration

Séquence 2 : Fonctions avancées et gestion des données (3h)

Fonctions conditionnelles et statistiques conditionnelles : SI, SI.ENS, NB.SI, NB.SI.ENS, SOMME.SI, SOMME.SI.ENS -- approfondis en TP
Fonctions de recherche : RECHERCHEX (Excel 2021/365), RECHERCHEV, INDEX/EQUIV -- approfondis en TP, avec présentation comparative des cas d'usage de chaque fonction
Validation des données : listes déroulantes, contrôles de saisie, messages d'alerte -- approfondi en TP
Suppression des doublons et nettoyage des données -- démonstration
Tableaux structurés : avantages, références structurées dans les formules -- approfondi en TP
Fonctions de texte et de date utiles en contexte professionnel : CONCATENER, TEXTE, GAUCHE, DROITE, STXT, AUJOURD'HUI, DATEDIF -- introduites, non approfondies

Séquence 3 : Contrôle, validation et protection des données (2h30)

Protection des cellules et des feuilles : verrouiller des plages spécifiques, protéger avec mot de passe -- approfondi en TP
Protection de la structure du classeur : empêcher l'ajout, la suppression ou le déplacement de feuilles -- approfondi en TP
Mot de passe d'ouverture du fichier : chiffrement basique du classeur -- démonstration
Suivi des modifications et partage sécurisé -- démonstration
Mise en forme conditionnelle avancée : règles personnalisées, formules dans les règles, gestion des priorités -- approfondi en TP

Séquence 4 : Tableaux croisés dynamiques (3h30)

Création d'un TCD à partir d'une base de données : sources, structure, bonnes pratiques -- approfondi en TP
Organisation des champs : lignes, colonnes, filtres, valeurs -- approfondi en TP
Regroupement des données : par dates, par tranches numériques, personnalisé -- approfondi en TP
Filtrage dynamique : segments et chronologies, connexion de plusieurs TCD à un même segment -- approfondi en TP
Calculs dans les TCD : champs calculés, pourcentages, cumuls, écarts -- approfondi en TP
Graphiques croisés dynamiques -- démonstration
Relations entre tables pour TCD multi-sources -- introduit

Séquence 5 : Communication graphique avancée (3h30)

Choix du type de graphique selon le message à transmettre -- rappel en démonstration
Graphiques combinés : associer barres et courbes, gestion des axes secondaires -- approfondi en TP
Graphiques Sparkline : indicateurs visuels intégrés dans les cellules -- approfondi en TP
Personnalisation avancée : couleurs, étiquettes dynamiques, titres liés à des cellules -- approfondi en TP
Mise en forme conditionnelle comme outil de visualisation : barres de données, jeux d'icônes, nuances de couleurs -- démonstration
Segments et chronologies comme outils d'interaction visuelle -- démonstration

Séquence 6 : Power Query (3h)

Présentation de Power Query : rôle, positionnement par rapport aux formules Excel classiques, cas d'usage -- démonstration introductive
Importer des données : fichiers CSV, Excel, dossiers -- approfondi en TP
Transformer et nettoyer les données : supprimer les colonnes inutiles, renommer, changer les types, supprimer les doublons, fractionner des colonnes -- approfondi en TP
Fusionner et consolider des sources : jointures entre tables, ajout de requêtes -- approfondi en TP
Actualiser la requête : mise à jour automatique des données sources -- démonstration
Créer un TCD à partir d'une requête Power Query -- approfondi en TP

Séquence 7 : Simulations et aide à la décision (2h)

Gestionnaire de scénarios : créer, nommer et comparer des scénarios (optimiste, pessimiste, réaliste) -- approfondi en TP
Tableau de synthèse des scénarios -- approfondi en TP
Application concrète : simulations budgétaires, projections de chiffre d'affaires -- approfondi en TP
Valeur cible : trouver la valeur d'entrée permettant d'atteindre un objectif -- démonstration avec exercice guidé
Solveur : optimiser un résultat sous contraintes -- introduit en démonstration uniquement

Séquence 8 : Révision et ancrage des acquis (2h)

Révision synthétique des fonctions et outils clés de la formation
Cas pratique intégrateur mobilisant les compétences des 3 jours : formules avancées, TCD, graphique, Power Query
Questions / réponses sur les cas terrain des participants