Centre de formation professionnelle - 05 63 47 98 36

Centre agréé TOSA®

Maîtrise Excel

Maîtrise ExcelCoût : 2100€
Durée : 20 jours (140h)

CPFCoût : 2100€
Durée : 20 jours (140h)

Objectifs
Objectifs

Maîtiser toutes le fonctionnalités du tableur Excel

Passer la certification TOSA

Passage de la certification TOSA sur demande

Programme

Programme

Se familiariser à l’environnement Microsoft Excel et gérer ses classeurs

Ouvrir un nouveau classeur, l’enregistrer et le fermer, utiliser la barre d’état et les barres d’outils

Insérer des feuilles

Déplacer des feuilles

Renommer des feuilles

Choix du nombre de feuilles par défaut

Protection de cellules, feuilles et classeurs

Saisir, modifier, déplacer les données d’un tableau

Insérer ou supprimer une ligne ou une colonne

Choisir un format de cellule et utiliser la mise en forme automatique

Réaliser des sommes automatiques

Utilisation de la poignée de recopie

Générer des séries

Utiliser la mise en forme conditionnelle

Utiliser les fonctions statistiques usuelles, Somme(),  Moyenne (), Max (), Min (), etc.

Comprendre les notions de référence relative, absolue et mixte

Attribuer un nom à une ou plusieurs cellules

Créer un graphique à partir d’un tableau

Affiner sa présentation pour mettre en relief les résultats obtenus

Choisir des graphiques (histogrammes, barres, courbes, secteurs, etc.) , les mettre en forme

Insérer un titre et une pagination à ses feuilles de calcul

Utiliser le mode «aperçu avant impression»

Exporter ses feuilles de calcul et graphiques vers d’autres outils Microsoft Office

Impression : répétition des titres

Impression : aperçu et manipulation des sauts de page

Premières formules (les 4 opérations)

Construction d’une formule

Les Fonctions d’Excel. Assistant fonction. Aide sur les fonctions

Evaluer une formule pas à pas en cas d’erreur

Afficher/masquer les formules

Références relatives/absolues

Nommer des cellules et utilisation de noms dans les formules

Formules de test (SI, NBSI, SOMMESI)

Les dates : comment Excel les stocke. Calculs sur les dates.

Les durées : comment Excel les stocke. Calculs sur les heures, minutes, secondes.

Rechercher la correspondance d’une valeur dans un tableau (RECHERCHEV)

Calculer un age à partir d’une date de naissance

Calculer la mensualité d’un emprunt (VPM)

Calcul inversé (valeur cible)

Comment saisir et présenter ses données. L’outil « Tableau »

Trier

Filtrer

Rechercher et remplacer des données

Supprimer les doublons

Consolider (regrouper les valeurs de plusieurs plages dans une seule plage)

Sécuriser et automatiser la saisie : validation des données

Formules dans une base de données

Mise en page d’une base de données

Présentation du Tableau Croisé Dynamique (TCD)

Organisation de la base de données

Créer le TCD

Options

Plusieurs TCD sur la même feuille

Lien entre la base de données et le TCD (actualisation)

Filtrer plusieurs TCD en 1 clic

Les Segments (à partir de la version 2010)

Représenter des données en pourcentages

Grouper les résultats (par mois, trimestre, année ou par tranches de valeurs)

Créer des champs calculés (évite de rajouter des formules dans la base de données. Ce peut être utile dans le cas de données extraites)

Représentation graphique du TCD

Créer un graphique

Différents types de graphiques

Modifier la présentation d’un graphique

Masquer une partie des données

Graphique à 2 axes (quand les valeurs en ordonnées ne sont pas du même ordre de grandeur)

Ajout d’un seuil horizontal (moyenne, objectif…)

Créer une pyramide des âges

Les graphiques sparkline (mini graphique dans une cellule)

Boîte de dialogue personnalisée

Création, éléments d’une boîte de dialogue, placer un contrôle, modifier les propriétés d’un contrôle, associer du code aux contrôles, interagir avec la feuille de calcul sans code, afficher le formulaire

Les contrôles dans une feuille

Créer un contrôle, liste des contrôles, exemple d’utilisation

Protection

Protéger un fichier

Protéger des cellules

Déverrouiller les cellules, protéger la feuille, déprotéger la feuille

Protéger le classeur

Protéger les cellules selon les utilisateurs

Enregistrer un classeur en lecture seule

Sécuriser la saisie dans une base de données

Vue d’ensemble

Restreindre l’entrée des données à une date comprise dans un intervalle, restreindre l’entrée des données en limitant leur choix à l’aide d’une liste déroulante, mise en évidence des données non valides

Installer la macro Solveur

Rappels sur l’outil Valeur cible

Exercices variés de résolutions de problèmes

Réflexions sur le contexte d’utilisation d’une macro

Enregistrer une macro avec l’enregistreur de macros

Créer le bouton déclencheur de la macro

Modifier une macro : accès au code VBA

Rudiments de programmation : saisie du code VBA

L’enregistreur de Macros

Utilisation de l’enregistreur de Macros
Modification d’une Macro enregistrée
Affectation d’une Macro à un bouton, à un objet
Création d’une Macro accessible depuis tous les classeurs

Le langage VBA

Saisie du Code dans l’éditeur Visual Basic
Notion d’objets, méthodes et propriétés
Déclaration des variables
Gestion du curseur : position, déplacement, sélection dynamique
Manipulation des feuilles et des classeurs
Transfert des données d’une feuille dans une autre
Macros interactives : Inputbox, Msgbox
Tests : If… Then… Else, Select Case
Boucles pour les traitements répétitifs : Do… Loop, For… Next, For Each
Traitement des erreurs : On Error
Déclenchement automatique des macros à l’ouverture d’un classeur, à sa fermeture

VBA et les Bases de données

Organisation de la Base de données
Macros d’extraction : utiliser les filtres élaborés pour interroger et extraire des données
Macros de consolidation : consolider des données issues de plusieurs bases
Créer un formulaire personnalisé (UserForm) avec zones de saisie, listes déroulantes, boutons d’option, cases à cocher, etc
Contrôler les données par macro et les transférer dans la feuille de calcul

Le déboguage

Exécuter une Macro pas à pas
Points d’arrêt
Contrôle des valeurs contenues dans une variable
Ajout d’espions