Formation Excel Lyon

Prise en main d’Excel

• Présentation de la nouvelle interface de démarrage et du ruban
• Découverte des nouveaux modèles proposés
• Savoir mettre à jour les services office
• Personnaliser le ruban et la barre d’outils d’accès rapide
• Définition des termes : cellule, feuille, classeur
• Les différents formats de fichiers (xlsx, pdf , …)
• Les différents formats de cellule

Saisir des données

• Police et taille de caractères
• Afficher plusieurs lignes dans une cellules
• Alignement et fusion de cellules
• Mise en forme des cellules : couleur, bordure des cellules

Figer les volets, grouper les colonnes et les lignes

• Figer les volets
• Grouper les colonnes et les lignes, dissocier

Créer et modifier des tableaux

• Le remplissage instantané (Flash Fill)
• Saisie des données par incrémentation et listes personnalisées
• Copier, déplacer ou lier des données
• Filtrer les données d’une façon interactive et dynamique à l’aide des segments

Insérer des formules de calcul

• Type d’erreur EXCEL
• Fonction texte simple : gauche, droite, MAJSUCULE, NBCAR, NOMPROPE
• Fonction de calcul de base , addition, Soustraction, Multiplication, Division
• Fonctions mathématiques: pourcentages, sommes, moyennes, minimum et maximum NB et NBVAL
• Insérer des formules avec variables & constantes
• Références absolues et relatives
• Nommer des cellules
• Utilisation de l’assistant fonction

Mettre en forme les données – imprimer un document au format Excel

• Insérer un en-tête et pied de page Insérer un commentaire, une image, un logo…
• L’affichage & mise en page avant impression
• Les captures d’écran
• Définir une zone d’impression
• Options d’impression
• Exporter au format PDF

Créer des graphiques

• Outil de suggestions de graphiques adaptés aux données
• Insertion d’histogrammes, camemberts, courbes… à partir d’un tableau
• Mise en forme et personnalisation du graphique (ajout de titre, légende, étiquettes)
• Mettre les valeurs en %
• Créer des mini-graphes Sparklines
• Modifications des graphes Sparklines

Lier les données

• Ajouter supprimer des feuilles
• Liaisons entre feuilles et avec Word

Mise en application

Personnaliser l’environnement Excel

• Définir les options d’Excel
• Adapter la barre d’outils d’accès rapide
• Ajouter des onglets et des boutons supplémentaires sur le ruban
• Savoir exporter et importer ses personnalisations

Créer des formules de calcul élaborées

• Maitriser les références absolues et relatives
• Nommer les cellules et utiliser les noms dans les formules
• Les fonctions de recherche avancée
• Insérer des formules conditionnelles simples, complexes et imbriquées
• Calculer des dates, des heures
• Gérer le texte : mise en forme des caractères, alignement, nom propre, concatener
• Les nouvelles fonctions : Joindre.texte, Si.multiple, Concat.
• Créer et utiliser une liste déroulante
• Utiliser les fonctions logiques, conditionnelles, recherche V et H, Somme.si….

Exploiter des données issues de différentes feuilles de calcul

• Trier sur plusieurs critères
• Appliquer des filtres élaborés
• Créer des listes personnalisées
• Utiliser le mode plan
• Intégrer des sous-totaux
• Utiliser les fonctions de liaison :Lier des données entre feuilles Excel et avec Word
• Référence 3D
• La consolidation des données dans plusieurs feuilles de calcul

Personnaliser les graphiques

• Illustrer ses données avec des graphiques pertinents
• Formatage avancé des graphiques : titres, légendes, étiquettes…
• Mixer 2 types de graphiques (histogramme et courbe)
• Les cartes 2D, les graphiques entonnoir.
• Les courbes de tendance Sparkline
• Intégrer son graphique dans un document Word ou Powerpoint

Créer des tableaux croisés dynamiques

• Elaborer un tableau croisé à partir d’une liste de données
• Gérer les différents champs en colonnes et en lignes
• Trier, filtrer un tableau croisé dynamique
• Savoir actualiser un tableau croisé dynamique
• Associer un graphique croisé dynamique

Mise en application

Module 1 : découverte et interface d’Excel

Introduction à Microsoft Excel

• Interface de Démarrage et Ruban
• Les Nouveaux Modèles Proposés
• Mise à Jour des Services Office
• Personnalisation du Ruban et de la Barre d’Outils d’Accès Rapide

Notions Fondamentales

• Définition des Termes : Cellule, Feuille, Classeur
• Les Formats de Fichiers (xlsx, pdf, etc.)
• Les Formats de Cellules

Module 2 : saisie de données

Gestion de la Saisie de Données

• Police, Tailles de Caractères
• Affichage de Plusieurs Lignes dans une Cellule
• Alignement et Fusion de Cellules
• Mise en Forme des Cellules

Module 3 : gestion de tableaux

Création et Modification de Tableaux

• Le Remplissage Instantané
• Saisie de Données par Incrémentation et Listes Personnalisées
• Copie, Déplacement ou Liaison de Données
• Filtrage Interactif des Données avec les Segments

Module 4 : formules et calculs

Insertion de Formules de Calcul

• Types d’Erreurs Excel
• Fonctions Texte Simple : Gauche, Droite, MAJUSCULE, NBCAR, NOMPROPRE
• Fonctions de Calcul de Base : Addition, Soustraction, Multiplication, Division
• Fonctions Mathématiques : Pourcentages, Sommes, Moyennes, Minimum, Maximum, NB, NBVAL
• Utilisation de Variables et Constantes dans les Formules
• Références Absolues et Relatives
• Nommer des Cellules
• Utilisation de l’Assistant Fonction

Module 5 : mise en forme et impression

Mise en Forme des Données et Impression

• Insertion d’En-Tête et de Pied de Page
• Insérer Commentaires, Images, Logos
• Affichage et Mise en Page Avant Impression
• Utilisation des Captures d’Écran
• Définir une Zone d’Impression
• Options d’Impression
• Exporter au Format PDF

Module 6 : création de graphiques

Création et Personnalisation de Graphiques

• Utilisation de l’Outil de Suggestions de Graphiques Adaptés aux Données
• Insertion d’Histogrammes, Camemberts, Courbes à Partir de Données
• Personnalisation des Graphiques (Titres, Légendes, Étiquettes)
• Conversion des Valeurs en Pourcentage
• Création de Mini-Graphes Sparklines
• Modification des Graphiques Sparklines

Module 7 : liaison de données

Liaison de Données

• Ajout et Suppression de Feuilles
• Liaisons entre Feuilles et avec Word

Module 8 : personnalisation d’Excel

Personnalisation de l’Environnement Excel

• Configuration des Options d’Excel
• Adaptation de la Barre d’Outils d’Accès Rapide
• Ajout d’Onglets et de Boutons Supplémentaires dans le Ruban
• Exportation et Importation des Personnalisations

Module 9 : formules de calcul élaborées

Utilisation Avancée des Formules

• Maîtrise des Références Absolues et Relatives
• Utilisation des Noms de Cellules dans les Formules
• Fonctions de Recherche Avancée
• Formules Conditionnelles Simples, Complexes et Imbriquées
• Calcul de Dates et d’Heures
• Gestion du Texte (Mise en Forme, Alignement, Concaténation)
• Nouvelles Fonctions : Joindre.Texte, Si.Multiple, Concat.
• Création et Utilisation de Listes Déroulantes
• Utilisation des Fonctions Logiques, Conditionnelles, Recherche V et H, Somme.Si, etc.

Module 10 : exploitation de données

Exploitation de Données Issues de Différentes Feuilles de Calcul

• Trier sur Plusieurs Critères
• Appliquer des Filtres Élaborés
• Créer des Listes Personnalisées
• Utiliser le Mode Plan
• Intégrer des Sous-Totaux
• Utiliser les Fonctions de Liaison entre Feuilles Excel et avec Word
• Référence 3D
• Consolidation des Données dans Plusieurs Feuilles de Calcul

Module 11 : personnalisation des graphiques

Personnalisation Avancée des Graphiques

• Illustration des Données avec des Graphiques Pertinents
• Formatage Avancé des Graphiques (Titres, Légendes, Étiquettes, etc.)
• Combinaison de Deux Types de Graphiques (Histogramme et Courbe)
• Utilisation des Cartes 2D, des Graphiques Entonnoir
• Courbes de Tendance Sparkline
• Intégration des Graphiques dans un Document Word ou PowerPoint

Module 12 : tableaux croisés dynamiques

Création et Gestion de Tableaux Croisés Dynamiques

• Élaboration d’un Tableau Croisé Dynamique à Partir de Données
• Gestion des Champs en Colonnes et en Lignes
• Triage, Filtrage et Actualisation d’un Tableau Croisé Dynamique
• Association de Graphiques Croisés Dynamiques

Mise en application

Gérer les listes et les bases de données

• Le remplissage instantané (Flash Fill)
• Afficher des plans et des sous totaux
• Consolidation des tableaux

Les outils d’analyse et de représentation des données

• Analyse de scénarios
• Outil de suggestion de tableaux croisés dynamiques (TCD)
• Utilisation des filtres dynamiques (Slicers) et des filtres sur les dates (Timeline Slicer) Segments
• Interprétation des données TCD
• Créer des relations entre tableaux
• Formats conditionnels avec barres de données, nuances de couleurs, jeux d’icônes
• Création de graphiques combinés.

Les formules avancées

• Utiliser les fonctions BDLIRE, INDEX ET EQUIV, …
• Créer des fonctions imbriquées : calculs avec des dates
• Groupes de travail et formules 3D : auditer et évaluer des formules
• Utiliser le volet espion

Les utilitaires de simulation et synthèse

• Les outils d’audit et de vérification
• Créer et utiliser des scénarios
• La valeur cible
• Le solveur : cellules variables et cibles, contraintes, résultats.

Les macros

• Création et enregistrement d’une macro simple
• Affecter une macro à un bouton de commande
• Exécuter des macros : raccourcis clavier, ruban.

Les protections et les modèles

• Protéger des cellules, des feuilles.
• Protéger un classeur
• Créer des modèles de documents

Importer et exporter des données

• Importer des fichiers texte, des données du Web
• Partager un classeur
• Exporter un tableau dans Word

Mise en application

I – Excel : maîtriser les fondamentaux (niveau 1)

• Introduction à Excel : Prise en Main
• Saisie de Données
• Création et Modification de Tableaux
• Insertion de Formules de Calcul
• Mise en Forme des Données
• Impression de Documents au Format Excel
• Création de Graphiques
• Liaison de Données

II – Renforcer ses compétences sur Excel (niveau 2)

• Personnalisation de l’Environnement Excel
• Création de Formules de Calcul Élaborées
• Exploitation de Données à Partir de Différentes Feuilles de Calcul
• Personnalisation Avancée des Graphiques
• Création de Tableaux Croisés Dynamiques

III – Les fonctions avancées sur Excel (niveau 3)

• Gestion de Listes et de Bases de Données
• Outils d’Analyse et de Représentation des Données
• Formules Avancées
• Utilitaires de Simulation et Synthèse
• Macros
• Protections et Modèles de Feuilles
• Importation et Exportation de Données

Mise en application

Examen TOSA (Certification)

Les bases de données

• Les principes d’une base de données Excel
• Instructions pour la création d’une liste et pour la saisie
• Les précautions à prendre

Les tableaux

• Utiliser les noms de cellules
• Contrôler l’affichage (valeur zéro, problèmes d’arrondi)
• Chercher une valeur en fonction d’un résultat voulu
• Cumuler plusieurs tableaux avec la consolidation de données

Les listes

• Fonctionnement du tri et précautions
• Annuler un tri
• Trier les colonnes
• Ordre de tri personnalisé (option de tri)

Les calculs

• Les opérations de base
• Utilisation de Somme Automatique
• L’assistant fonction
• Les fonctions statistiques
• Utilisation des références relatives et absolues dans un calcul OPTIMISER

L’utilisation des listes

• Les fonctions de recherche dans une liste
• Filtre automatique sur la liste
• Principe des critères simples et composés
• Critères personnalisés
• Filtre élaboré (zone de critères)
• Copie de données filtrées dans une autre feuille
• Somme automatique dans une liste filtrée

Les sous-totaux

• Le facteur de regroupement
• Le choix des statistiques
• La combinaison des statistiques
• Imprimer des résultats en fonction du plan

Les tableaux croisés dynamiques

• Présentation des tableaux croisés dynamiques
• Mettre en place un tableau croisé dynamique à une, deux ou trois dimensions
• Filtrer pour conserver les données utiles
• Filtrer avec les segments
• Le choix des fonctions de calcul (moyenne, pourcentage…)
• Les options des tableaux croisés dynamiques
• La répartition des champs sur plusieurs niveaux
• Regrouper les éléments d’un tableau croisé (Texte, Dates, Valeurs numériques)
• Créer un champ calculé ou un élément calculé
• Utiliser les pourcentages dans la zone de données
• Afficher/masquer certaines informations

La présentation d’un tableau croisé dynamique

• Appliquer une mise en forme conditionnelle
• Personnaliser la disposition par défaut des tableaux croisés dynamiques
• Création, modification et suppression des mesures personnalisées
• Détection automatique de relations

Les graphiques croisés dynamiques

• Créer et mettre en forme un graphique
• Appliquer un filtre à un graphique
• Les limites des graphiques croisés dynamiques

Mise en application

L’enregistreur de macros

• Exemples de macros avec l’enregistreur
• Les macros enregistrés comme base de la programmation VBA

Les bases du VBA

• Les bases de la programmation en VBA
• Prise en main de l’éditeur
• La notion d’objet
• Les propriétés et les méthodes
• Manipuler les objets cellules, feuilles, classeurs : différents exemples

Les instructions VBA

• Utiliser des variables
• Instruction de condition: if/ end if
• Instructions de boucle: For next – Do loop while
• Utilisation des fonctions

Création de formulaire

• Créer un formulaire (boîte de dialogue)
• Insérer des objets (contrôles) dans un formulaire
• Récupérer la saisie des utilisateurs
• Liste de choix, case à cocher, bouton
• Procédure événementielle

Exemples de macros et exercices

• Modification du code existant
• Les grandes lignes d’une nouvelle macro

Mise en application