Organisme de Formation aux technologies et métiers de L'informatique
excel

Excel - tableaux, graphiques, TCD et macro-commandes

Maîtriser les principales fonctions du tableur
Ce cursus métier est composé de plusieurs formations distinctes

Informations générales

MS20136
5 jours (35h)
1 695 €HT
repas inclus
70 €HT TOSA Excel
Financement CPF possible

Objectifs

  • Savoir utiliser des filtres avancés pour extraire des données ou éliminer les doublons
  • Être capable de faire des rapprochements de données via le module Query ou en recourant aux différentes fonctions de recherches (Recherche, RechercheV, Index, Equiv)
  • Comprendre comment créer des tableaux de synthèse (Tableaux Croisés Dynamiques) pour analyser rapidement et facilement des informations
  • Savoir repérer des données par l'intermédiaire de couleurs ou d'icônes (mise en forme conditionnelle)
  • Pouvoir créer des listes déroulantes pour imposer une saisie (Validation des données)
  • Pouvoir enregistrer et à exécuter des macro-commandes pour automatiser des tâches simples
  • Comprendre comment bien utiliser l'enregistreur de macro-commandes
  • Savoir dépanner une macro-commande en l'exécutant en mode "pas-a-pas" dans l'éditeur
  • Savoir construire des tableaux en soignant leur mise en forme d'une façon immédiate
  • Comprendre comment analyser vos résultats à l'aide de différents calculs
  • Gagner du temps en modifiant simultanément plusieurs tableaux
  • Savoir trier et filtrer des données dans une table
  • Pouvoir présenter vos données sous forme de graphiques simples
plus d'infos

Public

  • Toute personne souhaitant devenir autonome sur Excel et souhaitant devenir un utilisateur averti pour maitriser l'exploitation de données et les bases de données Excel.

Prérequis

  • Être à l'aise avec l'environnement Excel
  • Savoir construire des tableaux en soignant rapidement leur mise en forme
  • Savoir réaliser des calculs de base
  • Créer, modifier, sauvegarder et imprimer des documents

Programme de la formation

1ère partie

Insérer des formules de calcul

  • Insérer les fonctions statistiques (somme cumulée, max, min, Nb, moyenne...)
  • Visualiser les formules dans une feuille de calcul
  • Calculer des pourcentages dans une facture
  • Gagner du temps en recopiant une formule de calcul
  • Nommer une zone pour faciliter la lecture d'une formule
  • Utiliser l'adressage relatif, absolu et mixte

Soigner la mise en forme de vos tableaux

  • Renvoyer à la ligne automatiquement dans une cellule
  • Utiliser les formats personnalisés
  • Masquer l'affichage des zéros dans un tableau
  • Figer des colonnes ou des lignes à l'écran pour faciliter la lecture de grands tableaux
  • Afficher plusieurs feuilles et/ou classeurs en côte à côte

Améliorer la présentation d'un tableau dans une page

  • Savoir répéter automatiquement les titres d'un tableau lors d'une impression
  • Définir des zones d'impression
  • Mettre à l'échelle un tableau
  • Affichage des sauts de page

Gagner du temps en travaillant sur plusieurs feuilles de calcul

  • Recopier une feuille pour dupliquer un tableau
  • Modifier simultanément plusieurs tableaux situés sur plusieurs feuilles de calcul (notion de groupe de travail)

Saisir et trier les données d'une base

  • Réaliser des tris multicritères (64 ordres de tri)
  • Créer un format personnalisé pour trier des cellules alphanumériques
  • Trier selon un ordre personnalisé (possibilité de trier selon la couleur de la police, cellule)

Utiliser les filtres pour extraire des données

  • Extraire des données en appliquant un filtre automatique
  • Utiliser les filtres personnalisés pour consulter les données selon deux critères maximum
  • Filtrer suivant le champ qui est déclaré (numérique, texte, date)
  • Possibilité sur une même rubrique de faire des choix multiples
  • Filtrer grâce une couleur de police ou de cellule

Créer des graphiques adaptés aux données

  • Suggestion des meilleurs graphiques par rapport à la structure des tableaux
  • Comparer des résultats en créant un histogramme
  • Visualiser une répartition avec un graphique en secteur (camembert)

Améliorer la mise en forme d'un graphique

  • Modifier les couleurs
  • Insérer une image dans le graphique

Améliorer la lisibilité des données d'un graphique

  • Insérer des titres
  • Afficher les données chiffrées dans un graphique
  • Expliciter les résultats grâce à des zones de texte

2ème partie

Analyse rapide d'un tableau dans Excel

  • Mise en forme conditionnelle
  • Graphiques
  • Totaux (Somme, Moyenne, Nbval, % du total, résultat cumulé)
  • Tableaux ou tableaux croisé dynamiques
  • Graphique Sparklines

Utiliser les filtres pour extraire des données

  • Appliquer un filtre élaboré pour extraire des données qui répondent à de nombreux critères
  • Possibilité de mettre le résultat sur une autre feuille et aussi d'éliminer les doublons dans une liste de données

Réaliser des calculs statistiques sur une base de données

  • Nommer une base de données
  • Réaliser des calculs statistiques sur des données groupées (moyenne CA;an)

Exploiter des bases de données provenant d'autres applications avec Query

  • Ouvrir Query à partir d'Excel
  • Trier et filtrer des fichiers provenant de l'application Access
  • Renvoyer les données dans Excel

Créer des tableaux de synthèse (tableau croisé dynamique)

  • Suggestion pour aider à la création de TCD
  • Créer un tableau de synthèse pour analyser les données d'une base
  • Modifier l'organisation des données dans le tableau
  • Grouper des informations (texte, date, numérique)
  • Créer des segments chronologiques
  • Calculer une répartition
  • Modifier le calcul (moyenne, pourcentage)
  • Créer une formule personnalisée (champ calculé, élément calculé)
  • Actualiser les données
  • Trier des données dans un tableau croisé dynamique
  • Mise en forme conditionnelle
  • Mettre deux tables en relation sous forme d'un tableau croisé
  • Transformer un tableau croisé dynamique en graphique

Remanier les données d'une base provenant d'une autre application

  • Remplissage instantané
  • Répartir les données d'une cellule dans plusieurs colonnes
  • Afficher le contenu d'une cellule en majuscule ou en minuscule
  • Faire une distinction entre majuscule et minuscule (fonction Exact)
  • Extraire certains caractères du contenu d'une cellule
  • Assemblage de plusieurs cellules en une seule (concaténer)
  • Supprimer les espaces dans une cellule (supprespace)
  • Convertir une valeur en format texte, en nombre (Cnum)
  • Compter le nombre de caractères d'une chaine (NbCar)
  • Afficher une valeur numérique en respectant un format de texte (Texte)
  • Effectuer une recherche sur du texte (Trouve, Cherche)

Mettre en relief les données d'une base

  • Modifier la mise en forme selon le contenu des cellules
  • Possibilités de placer plusieurs règles pour la même zone
  • Appliquer une mise en forme à des cellules en fonction du résultat d'un calcul (présenter toute une ligne en couleur, mettre certains jours en couleur)

Faciliter la saisie dans une base de données et limiter le nombre d'erreurs

  • Créer une liste déroulante pour saisir des informations redondantes
  • Définir des critères de validation pour limiter les erreurs de saisie
  • Afficher dans une zone les valeurs non valides
  • Empêcher la saisie de doublons dans une zone (fonction NB.Si)

Réaliser un calcul exact entre deux dates

  • Fonction datedif

Optimiser la recherche d'informations

  • Utiliser la fonction Recherche
  • (avec une valeur exacte ou proche)
  • Gérer l'affichage des erreurs dans une cellule (Estna, Esterreur)
  • Création d'une liste déroulante combinée avec la fonction de RechercheV
  • Obtenir la position d'un élément dans une liste de donnée (fonction Equiv)
  • Renvoyer une référence ou une valeur de cellule à l'intersection d'une ligne ou d'une colonne dans une plage de données (Index)

3ème partie

Introduction à la macro-commande

  • Quand et pourquoi utiliser une macro-commande
  • Préparer son travail
  • Méthodologie de création d'une macro

Utiliser l'enregistreur de macros

  • Accéder à l'onglet Développeur
  • Utiliser l'enregistreur de macros
  • Créer une macro globale ou personnelle
  • Enregistrer des macros en références relatives

Exécuter une macro

  • Exécuter une macro à partir d'un raccourci-clavier
  • Insérer une icône dans la barre d'accès rapide ou le ruban
  • Exécuter une macro à partir d'un bouton de commande
  • Lancer une macro en cliquant sur une image, une forme

Gérer un classeur contenant des macros

  • Enregistrer son classeur au bon format
  • Définir son niveau de sécurité
  • Indiquer des emplacements approuvés pour éviter la désactivation de macros

Travailler dans Visual Basic Editor

  • S'approprier l'interface de Visual Basic Editor : les fenêtres Explorateur d'objets, propriétés, code
  • Visualiser le code généré dans la fenêtre Visual Basic
  • Comprendre le code enregistré
  • Copier et renommer un module

Corriger une macro déjà enregistré

  • S'approprier les outils de débogage pour repérer les erreurs et comprendre une macro
  • Utiliser le pas à pas détaillé et les points d'arrêt
  • Afficher des espions pour suivre les variables
  • L'option Debug.Print

Modifier une macro déjà enregistrée

  • Modifier une macro dans la fenêtre Visual Basic
  • Adapter la zone d'exécution d'une macro
  • Ajouter des boites de dialogues (MsgBox, InputBox)
  • Lancer une macro dès l'ouverture d'un fichier
  • Commenter une macro
  • Continuer une macro existante
  • Exécuter des macro à la suite des uns des autres (Call)
plus d'infos

Méthode pédagogique

Chaque participant travaille sur un poste informatique qui lui est dédié. Un support de cours lui est remis soit en début soit en fin de cours. La théorie est complétée par des cas pratiques ou exercices corrigés et discutés avec le formateur. Le formateur projette une présentation pour animer la formation et reste disponible pour répondre à toutes les questions.

Méthode d'évaluation

Tout au long de la formation, les exercices et mises en situation permettent de valider et contrôler les acquis du stagiaire. En fin de formation, le stagiaire complète un QCM d'auto-évaluation.

Certification

Cette formation prépare au passage de la certification suivante et peut être financée par votre crédit CPF.
N'hésitez pas à nous contacter pour toute information complémentaire.

TOSA Excel (70 €HT)

L'examen TOSA se passe en centre agréé ou en ligne. Votre score (sur 1000) vous permettra de communiquer sur votre niveau de connaissances du logiciel.

  • Durée : 1h
  • 35 questions QCM et cas pratiques
  • Score minimum : 351

Suivre cette formation à distance

  • Un ordinateur avec webcam, micro, haut-parleur et un navigateur (de préférence Chrome ou Firefox). Un casque n'est pas nécessaire suivant l'environnement.
  • Une connexion Internet de type ADSL ou supérieure. Attention, une connexion Internet ne permettant pas, par exemple, de recevoir la télévision par Internet, ne sera pas suffisante, cela engendrera des déconnexions intempestives du stagiaire et dérangera toute la classe.
  • Privilégier une connexion filaire plutôt que le Wifi.
  • Avoir accès au poste depuis lequel vous suivrez le cours à distance au moins 2 jours avant la formation pour effectuer les tests de connexion préalables.
  • Votre numéro de téléphone portable (pour l'envoi du mot de passe d'accès aux supports de cours et pour une messagerie instantanée autre que celle intégrée à la classe virtuelle).
  • Selon la formation, une configuration spécifique de votre machine peut être attendue, merci de nous contacter.
  • Pour les formations incluant le passage d'une certification la dernière journée, un voucher vous est fourni pour passer l'examen en ligne.
  • Pour les formations logiciel (Adobe, Microsoft Office...), il est nécessaire d'avoir le logiciel installé sur votre machine, nous ne fournissons pas de licence ou de version test.
  • Horaires identiques au présentiel.

Mis à jour le 12/12/2022