Excel Expert : Power Query, Power Pivot + Graphiques, Formulaires et Fonctions complexes

- Tableaux croisés dynamiques (TCD) avancés Rappel des fonctionnalités de base des TCD Utiliser un tableau croisé dynamique sous l’outil tableau Gérer les dispositions d’un TCD Les différentes options du tableau croisé dynamique Choisir des options d’affichages des valeurs Les champs de calculs et leurs limites Insérer et paramétrer des segments et des chronologies Utiliser la mise en forme conditionnelle dans un TCD Gérer l’actualisation des données Exploiter « élément calculé » et « groupement des données » en même temps Créer des TCD avec des sources externes - Découvrir Power Pivot Présentation de Power Pivot en tant qu’outil Avantages et fonctionnalités Les données à utiliser avec Power Pivot Description de l’onglet Power Pivot dans Excel Utilisation du gestionnaire Power Pivot Configuration de l’importation des données externes Gestion des données importées à partir de l’interface Power Pivot Manipuler des données avec Power Pivot - Paramétrer et gérer des données multi-tables Chargement des tables Power Pivot depuis Excel Accès aux bases de données relationnelles : Access, ODBC etc. Gestion des connexions et des mises à jour Utilisation de la vue Diagramme Création des relations entre les tables Gestion des paramètres de Power Pivot sur les multi table Aménagement de l’affichage et création des perspectives Gestion des données multi table depuis Power Pivot Atelier : Importer des données et créer des relations dans Power pivot puis générer des calculs - Calculer dans le cube Fonctions DAX, fonctions Excel et calcul dans le tableau croisé dynamique Concevoir des champs calculés Mettre en place des mesures (fonctions, ratio, écarts, progression…) Mettre en œuvre une gestion des dates intelligente Formatage les données - Création de tableaux croisés dynamiques connecté au Power Pivot Manipulation d’un tableau croisé dynamique en multi-tables Utiliser des champs de calculs dans le TCD fait à partir de Power Pivot Créer des jeux de données Créer des indicateurs de performance (KPI) Utiliser des regroupements et définir des tranches de valeurs Atelier : Générer des mesures et faire un tableaux croisés dynamiques avec Power Pivot à partir de sources externes - Découvrir Power Query Le rôle de Power Query Les outils complémentaires au Power Query - Charger une source de données Sources Excel, csv et texte Choix des paramètres d’importation Importation d’une base Access - Exploiter l'éditeur de requêtes Power Query Transformation des données Fractionnement d’une colonne modification des types de données et transposer Actualisation des données par rapport à la source Insertion ou suppression de lignes et/ou de colonnes Agrégation des données d'une colonne Utilisation du Filtre, du tri et du regroupement des lignes Fusionner et ajouter des requêtes Pivoter ou Dépivoter des données Lier des requêtes à un modèle de données (Tables Power pivot) Atelier : importation, de transformation et de combinaison de données à partir de sources externes - Présentation de 3D Maps Modification l'apparence de la carte Ajout d’une scène Exploitation de données géographiques et temporelles Exportation de la carte 3D Maps vers une vidéo ou vers une autre application Atelier : Présentation de données géographiquement - Personnaliser la mise en forme des données Utiliser des formats de nombres personnalisés Gérer les heures et leurs formats Créer et exploiter des listes personnalisées - Construire des graphiques complexes Combiner dans un Histogramme l’affichage groupé et empilé Créer et configurer un graphique en cascade Enrichir un graphique avec des boutons de contrôle pour le dynamiser - Ecrire des fonctions complexes Imbriquer des fonctions (fonctions index et Equiv, fonction Décaler, Indirect…) Les formules matricielles (Transpose, Sommeprod, Si en matricielle…) Gérer des Imbrications complexes de fonctions de date Autres exemples d’imbrications de fonctions pour des besoins complexes Atelier : Faire des calculs matriciels à partir des données d’un tableau - Mettre en place des tris et des filtres personnalisés Tri avec de la mise en forme conditionnelle Tri avec un ordre spécifique Filtre avancé avec des critères spécifiques - Gérer la validation des données Menu déroulant en cascade Règles de validations avec formules (sans doublon, Majuscule…) Les options spécifiques de validations Dynamiser l’affichage d’images avec un menu déroulant - Mettre en forme des données sous conditions Personnalisation des jeux d’icônes, nuances de couleurs… Utilisation de fonctions dans les conditions Atelier : Mettre en place de la mise en forme conditionnelle avec des formules - Utiliser des outils de simulation Valeur cible Solveur Gestion des scénarios - Les contrôles de formulaire Bouton, Menu déroulant, case à cocher… Les paramètres spécifiques aux contrôles Liaisons des contrôles avec les formules de calculs - Gérer des données avec INQUIRE Comparer des classeurs Analyser un classeur (Structure, formule, cellules, plages, avertissement) Afficher les liens entre classeurs, feuilles de calcul et cellules Nettoyer l'excès de mise en forme Gestion des mots de passe Atelier : Comparer des fichiers et identifier certaines données