Excel Perfectionnement
Programme de formation
Appliquer des fonctions avancées d’Excel dans des situations variées, notamment les fonctions conditionnelles et de recherche. Élaborer des formules de calcul complexes en utilisant des fonctions avancées imbriquées. Organiser et structurer efficacement leurs données sous forme de tableaux. Choisir et mettre en œuvre différentes techniques avancées et d’analyse : tris, filtres, calculs statistiques, fonctions complexes. Extraire les données de différents format et sources. Évaluer la qualité, la cohérence et la fiabilité des données avant exploitation et savoir les transformer/convertir à l'aide d'outils dédiés et de fonctions spécifiques. Analyser de grands volumes de données avec les tableaux et graphiques croisés dynamiques Construire des tableaux de bord grâce aux tableaux croisés dynamiques et ajouter des indicateurs à l'aide de mise en forme conditionnelles. • Découvrir les notions de BI fondamentales dans Excel, dont le modèle relationnel de données à l'aide de Power Pivot et automatiser l’extraction, la préparation et la transformation des données avec Power Query.
Toute personne ayant suivi le cours Excel Fondamental ou ayant une bonne pratique des fonctions de base d’Excel.
N’hésitez pas à nous contacter pour préparer et adapter au mieux cette formation accessible aux personnes en situation de handicap.
Excel Perfectionnement
Contenu
Maîtriser les calculs, les fonctions et les outils avancées Analyser les données avec les Tableaux et Graphiques Croisés Dynamiques Présentation de la gestion des données à l'aide des outils de Business Intelligence
1. Rappels essentiels : formules, fonctions et références
- Saisir des formules simples avec opérateurs et parenthèses
- Comprendre les références relatives, absolues et mixtes
- Utiliser les fonctions standards : SOMME, MOYENNE, MIN, MAX, NB…
- Construire progressivement des formules imbriquées
- Gérer l'utilisation du $ dans les formules (références absolues ou mixtes)
- Exemple d'exercice pratique : Calculer la somme, la moyenne des ventes pour plusieurs tableaux de différents commerciaux, puis créer un tableau de synthèse permettant de déterminer la part des ventes en pourcentage de chaque vendeur
2. Gestion des données et tableaux structurés
- Convertir une plage en tableau de données et exploiter l'onglet Outils de tableau
- Redimensionner, styliser, protéger et structurer un tableau
- Ajouter des colonnes calculées en utilisant la syntaxe tableau plutôt que des références de cellules
- Comparer des listes et supprimer les doublons
- Exemple d'exercice pratique : Convertir une plage de données RH en tableau structuré, créer une colonne pour calculer l'ancienneté puis une autre colonne pour évaluer le montant de la prime en fonction de l'ancienneté et enfin comparer les matricules des employés à une autre liste d'employés puis supprimer les doublons
3. Méthodes d'analyse : tris, filtres et sous-totaux
- Trier sur plusieurs critères valeur, couleur, icône, liste personnalisée
- Filtrer selon texte, dates, valeurs numériques ou couleurs
- Filtrer à partir d'un segment
- Exploiter le filtre avancé
- Utiliser la ligne des totaux et la fonction Sous-total
- Exemple d'exercice pratique : Trier une base de données ventes de produits selon plusieurs critères, filtrer les résultats par dates et montants grâce aux filtres classiques et aux segments, utiliser le filtre avancé pour extraire une cible croisée catégorie, origine du produit, puis activer la ligne des totaux et la fonction Sous-total pour obtenir une synthèse dynamique des stocks correspondant au filtre
4. Validation, cohérence et qualité des données
- Définir des critères de validation
- Créer des listes déroulantes
- Entourer les valeurs non valides
- Contrôler et fiabiliser les entrées
- Nettoyer et normaliser les données à l'aide de fonctions texte, dates, heures…
- Convertir les données de type date ou numérique normalisées à votre pays
- Exemple d'exercice pratique : Mettre en place des listes déroulantes pour contrôler la saisie du type de chambre et du statut d'occupation d'un hôtel, définir des règles de validation pour éviter les dates incohérentes de réservation, entourer automatiquement les valeurs non conformes, puis nettoyer et normaliser les informations (noms, dates, horaires) afin de fiabiliser la gestion des chambres de l'hôtel
5. Tableaux croisés dynamiques (TCD)
- Comprendre la structure et la logique d'un TCD
- Organiser les données sources pour un TCD fiable
- Créer, modifier et personnaliser un TCD – Trier, filtrer, grouper (mois, trimestres, valeurs…)
- Ajouter/supprimer des champs, modifier les synthèses (Somme, Moyenne, %...)
- Actualiser les données sources – Insérer segments et chronologies pour filtrer
- Construire des mini-tableaux de bord interactifs en liaison dynamique avec un TCD
- Exemple d'exercice pratique : Analyser le portefeuille des contrats d'une compagnie d'assurance en structurant les données sources puis en créant et personnalisant un tableau croisé dynamique permettant de trier, filtrer et regrouper les indemnisations par mois et type de sinistre, ajouter des champs pour obtenir des synthèses pertinentes sur les volumes d'indemnisation, construire un mini tableau de bord interactif grâce aux segments et chronologies puis paramétrer une actualisation automatique des données
6. Graphiques croisés dynamiques et tableaux de bord
- Créer et personnaliser un graphique croisé dynamique
- Connecter un segment à plusieurs TCD
- Modifier l'affichage : % du total, écarts, variations…
- Ajouter un champ calculé ou élément calculé
- Exemple d'exercice pratique : Créer et personnaliser un graphique croisé dynamique pour visualiser l'évolution des sinistres ou des primes d'une compagnie d'assurance sur une période, connecter un même segment à plusieurs graphiques croisés pour synchroniser l'analyse par région ou type de contrat, modifier l'affichage des résultats en pourcentages, écarts ou variations, et enrichir le reporting en ajoutant des champs ou éléments calculés afin de produire un tableau de bord clair et exploitable par les équipes métier
7. Calculs et fonctions avancées
- Fonctions conditionnelles : SI, ET, OU, SI.ERREUR
- Fonctions d'analyse : SOMME.SI.ENS, NB.SI.ENS…
- Fonctions de recherche : RECHERCHEV, RECHERCHEX
- Création de formules complexes et imbriquées
- Gestion des erreurs dans les formules
- Exemple d'exercice pratique : Identifier les tarifs de fret aérien les plus rentables en tenant compte du coût du carburant, des distances et des destinations, puis analyser la rentabilité en fonction des volumes expédiés et des spécificités de chaque compagnie aérienne
8. Mise en forme conditionnelle avancée
- Appliquer des règles basées sur le contenu, les dates, les pourcentages
- Appliquer barres de données, nuances de couleurs, icônes
- Créer des MFC avec formules
- MFC sur ligne entière selon plusieurs conditions
- Gérer et modifier les règles
- Appliquer de la MFC à un TCD
- Exemple d'exercice pratique : Utiliser la mise en forme conditionnelle avancée pour repérer rapidement les retards de livraison d'un transporteur routier, les écarts de coûts ou de kilométrage, visualiser la rentabilité des secteurs grâce aux barres de données, appliquer des règles avec formules pour mettre en évidence les lignes selon la catégorie du camion, sa consommation et l'itinéraire, et ajouter des indicateurs clés via des jeux d'icônes pour analyser la performance des tournées
9. Travail multi-feuilles et multi-classeur
- Créer un groupe de travail
- Faire des calculs inter-feuilles ou inter-classeurs
- Récupérer, lier et mettre à jour des données entre fichiers
- Corriger les liaisons
- Exemple d'exercice pratique : Créer un groupe de travail pour analyser simultanément plusieurs feuilles, effectuer des calculs inter-feuilles ou inter-classeurs afin de comparer les prix issus de différents fournisseurs, récupérer et lier les données entre plusieurs fichiers pour actualiser automatiquement les écarts de tarifs, puis corriger, ajouter ou supprimer des liaisons afin de garantir une analyse fiable et à jour
10. Présentation des BI d'Excel
- Introduction à la Business Intelligence dans Excel, en s'appuyant sur Power Query et Power Pivot pour analyser plus facilement des données, automatiser leur traitement et obtenir des informations fiables pour la prise de décision
- Exemple d'exercice pratique : Extraire les ventes de plusieurs fichiers provenant de différents points de vente, corriger les erreurs de format, de date et de valeur numériques et vérifier les colonnes incohérentes dues à des erreurs de saisie ou des données manquantes, compiler toutes les ventes dans une table unique, relier les ventes et les stocks pour mettre ces derniers à jour, puis actualiser automatiquement les réapprovisionnements en fonction des seuils minimums de chaque magasin
Modalités pédagogiques
Form’Agasc vous propose plusieurs dispositifs pédagogiques :
- Formation en présentiel ou en distanciel.
- Dans vos locaux ou dans les locaux proposés par Form’Agasc.
Le formateur, expert dans son domaine, s’appuie également sur la participation et l’expérience des stagiaires pour compléter et développer leurs compétences et pratiques professionnelles.
Moyens techniques et supports pédagogiques
- Participation active où l’expérimentation des participants est privilégiée.
- Mises en situation de réflexion, cas concrets.
- Méthodologie d’apprentissage attractive, interactive et participative.
- Équilibre théorie / pratique.
- Supports de cours fournis au format papier et/ou numérique.
- Ressources documentaires en ligne et références mises à disposition par le formateur.
- Pour les formations en présentiel, la salle de cours sera équipée d’un réseau WiFi, d’un tableau blanc ou paperboard.
- Pour les formations en distanciel, un outil de visioconférence simple d’utilisation permettra également aux participants de partager leur écran.
Moyens et modalités d'évaluation et de suivi
En amont de la formation, un recueil des besoins permet de récolter des informations sur le stagiaire et ses attentes. Il réalise ensuite un auto-positionnement qui permet de mesurer son niveau de départ.
Tout au long de la formation, le formateur évalue les acquis par des questions orales, exercices pratiques, QCM, et mises en situation.
À la fin de la formation, les stagiaires s’auto-positionnent pour mesurer l’acquisition de leurs compétences. Ils évaluent également la formation « à chaud » et « à froid » pour évaluer les apports réels de la formation et leur mise en application dans leur quotidien.
Le formateur évalue chaque stagiaire et les compétences acquises pendant la formation. Il évalue également l’action de formation dans son ensemble.
Le stagiaire émargera par demi-journée et recevra une attestation de formation.
Contactez-nous au 04 93 31 50 18 ou à contact@formagasc.fr (réponse sous 48h)
Selon votre besoin et vos attentes, nous calerons un échange téléphonique avec le formateur pressenti afin d’adapter la formation et ses modules puis nous vous adresserons un devis.
Les dates de la formation seront à convenir sous 30 jours maximum.
Date des programmes : 17/03/2026
