Excel - Les fondamentaux

Objectif
Concevoir des tableaux professionnels avec des calculs essentiels puis les présenter sous forme graphique, découvrir les principales fonctions statistiques simples d’Excel

Durée
21 à 28 heures

Prérequis
Connaissance de l’environnement Windows

Public
Toute personne souhaitant maitriser les fonctionnalités essentielles du logiciel Excel pour une utilisation courante

Programme

Connaître l'interface et les principales fonctionnalités du logiciel
- lancer et quitter l’application
- découvrir la fenêtre et le vocabulaire du logiciel
- créer et ouvrir un classeur
- enregistrer et fermer un classeur

Saisir et mettre en forme des données
- saisie des données de type nombre, texte, date, heure
- recopie des données
- gestion des lignes et des colonnes
- format des cellules : couleur de police, taille, remplissage, bordures, alignement
- format de nombre (monétaire, pourcentage...) et format personnalisé
- copier / couper / coller

Créer des calculs
- avec les opérateurs mathématiques
- fonctions de calculs : SOMME, MOYENNE, MAX, MIN, NB, SI, SI
- recopie de formules
- références relative/absolue

Mettre en page les données et imprimer
- gestion des modes d'affichage Normal, Mise en page et Aperçu des sauts de page
- insertion d'un en En-tête et Pied de page : de texte, date, image, numérotation
- paramètres d'impression : Impression simple, Orientation, Marges, Mise à l'échelle, Format de papier A4/A3, Zone d'impression, Sauts de page automatiques et manuels, Impression des titres sur chaque page

Représenter les données à l'aide d'un graphique
- choix du type et création du graphique
- modification de la taille et déplacement
- ajout d’éléments : titre, légende, valeur, pourcentage
- formatage des éléments
- impression du graphique seul ou avec le tableau
- suppression du graphique

Gérer une base de données
- créer une base de données
    =>structure d'une base de données et erreurs à éviter
    =>utilisation des fonctions Rechercher et Remplacer
- exploiter les données
    =>classement à l'aide de tris simples et multicritères
    =>affichage à l'aide de filtres automatiques sur des nombres, des dates, du texte, une couleur

Automatiser sans programmer
-mise en forme conditionnelle simple : format de police et couleur de remplissage, barres de données, jeux d'icône

Exploiter les options de Collage
- utilisation des raccourcis clavier CTRL + C, CTRL + V
- option de collage pour conserver les formules et les formats
- option de collage pour conserver la largeur des colonnes sources
- transposition des données en lignes/colonnes
- option de collage des valeurs uniquement
- option de collage avec liaison
- réalisation d’une opération

Formules et fonctions de calculs

Objectif
Être autonome dans l'écriture des fonctions : syntaxe, débogage. Connaître les fonctions de calculs essentielles

Durée
7 heures

Prérequis
Connaître Excel ou avoir suivi la formation sur les fondamentaux

Public
Utilisateur d'Excel souhaitant maîtriser la saisie des formules

Programme

Rappel sur les fonctions de base
- opérateurs mathématiques
- fonctions statistiques : SOMME, MOYENNE, MAX, MIN, NB
- recopie de formules avec des références absolues / relatives / mixtes

Écrire et comprendre la syntaxe des principales fonctions
- dénombrement NB, NBVAL, NB.VIDE, NB.SI, RANG, GRANDEVALEUR
- texte MAJUSCULE, MINUSCULE, GAUCHE, DROITE, NOMPROPRE, NBCAR, &, EPURAGE, SUPPRESPACE...
- date AUJOURDHUI, MAINTENANT, DATEDIF, ANNEE, MOIS, JOUR, DATE, JOURSEM, FINMOIS, NB.JOURS.OUVRES, NO.SEMAINE.ISO
- logique : SI, ET, OU, SIERREUR, SI.CONDITIONS
- recherche et référence : ADRESSE, RECHERCHE-V
- information : ESTERREUR, ESTNUM, ESTNONTEXTE, ESTVIDE
- financière : VPM, INTPER, PRINCPER

Analyser les erreurs sur les formules
- audit et évaluation de formules
- localisation des données non valides : repérer les antécédents / les dépendants
- interprétation des messages d’erreur : #REF!, #NOM, #DIV/0!, #NULL!, #VALEUR, #NUM, #N/A

Liaisons entre feuilles et classeurs
- création et syntaxe des formules ; mise à jour des liaisons

Graphiques

Objectif
Concevoir des graphiques pour mettre en valeur les données Excel

Durée
Entre 4 et 7 heures

Prérequis
Connaissances des bases d’Excel

Public
Tout public qui souhaite compléter ses connaissances par l'utilisation des graphiques

Programme

Concevoir des graphiques pertinents
- choix du type et création du graphique
- découverte des graphiques recommandés et des outils d'Analyse rapide

Améliorer la lecture du graphique
- modification de la taille et déplacement
- ajout d’éléments : titre, légende, valeur, pourcentage
- formatage des éléments
- impression du graphique seul ou avec le tableau
- suppression du graphique

Aller plus loin avec les graphiques
- combinaison de plusieurs types de graphiques
- affichage d'un double axe vertical
- insertion d'une courbe de tendance
- utilisation des graphiques Sparkline

Gestion avancée des données

Objectif
Traiter, mettre en forme et analyser de gros volumes de données

Durée
7 heures

Prérequis
Connaître ou avoir suivi la formation « Les fondamentaux d’Excel »

Public
Toute personne qui a besoin d'exploiter des données

Programme
Créer une base de données
- structure d'une base de données et erreurs à éviter
- utilisation des fonctions Rechercher et Remplacer
- création d'une liste déroulante
- contrôle de la saisie avec la validation des données et l'ajout de commentaire

Optimiser la base de données
- affichage de la ligne des titres (= figer les volets) pendant le défilement de la feuille
- gestion des valeurs nulles et suppression des doublons
- fractionnement du contenu des cellules avec l'Assistant Conversion et le remplissage instantané

Exploiter les données
- classement à l'aide de tris simples et multicritères, en colonne et en ligne
- affichage à l'aide de filtres automatiques : filtres textuels, numériques, chronologiques

Automatiser sans programmer
- mise en forme conditionnelle : format de police et couleur de remplissage, barres de données, jeux d'icônes, formules de calculs
- formats personnalisés

Tableaux croisés dynamiques

Objectif
Synthétiser et analyser des données

Durée
7 heures

Prérequis
Connaître ou avoir suivi la formation « Les fondamentaux d’Excel »

Public
Managers, opérationnels, assistants

Programme

Rappels sur la structure d'une base de données

Créer des tableaux croisés dynamiques
- suggestion de tableaux
- choix des champs à intégrer
- suppression d'un champ

Améliorer la présentation
- style de tableau
- format des nombres
- réécriture des titres
- disposition avec ou sans les totaux
- propriétés du tableau

Approfondir l'analyse
- modification du calcul de synthèse : moyenne, minimum, comptage, pourcentage
- application d'un tri ou d'un filtre
- mise à jour des données et modification de la source
- synthèse pour grouper des dates (mois, trimestre, année) et des tranches d'âge
- synthèse pour grouper des données non numériques
- insertion d'un champ calculé
- création d'une liste détaillée issue d'une synthèse
- insertion d'un graphique croisé dynamique

Outils d’optimisation et tableaux de bord

Objectif
Savoir exploiter les différents outils d’analyse et de simulation. Importer des données. Maîtriser la consolidation d’informations multi-feuilles et multi-classeurs.

Durée
14 heures

Prérequis
Maîtriser les fondamentaux et les fonctions avancées d'Excel

Public
Managers, gestionnaires, opérationnels qui souhaitent manipuler, analyser et présenter des données chiffrées

Programme

Importer et préparer les données au format Excel
- conversion des données au format Excel
- CSV, texte tabulé, fichier XML, page web
- correction des données non conforme
- transformer en nombre les valeurs stockées en texte
- mettre en forme les numéros de téléphone ou codes postaux
- données de type Date…

Mettre sous forme de tableaux structurés
- formatage rapide du tableau
- sélection dans un tableau
- étendue rapide du tableau
- ligne automatique des Totaux
- découverte de la syntaxe des formules

Empêcher une saisie non conforme
- validation de données pour restreindre ou autoriser une saisie
- mise en évidence des données non valides
- message d’alerte ou d’information
- validation de données pour créer une liste déroulante

Nommer les plages et les tableaux
- gestion automatique ou manuelle des noms
- utilisation du Gestionnaire des noms

Calculer à l’intérieur des données avec la fonction RechercheV
- syntaxe de la fonction
- optimisation de la syntaxe avec les noms de tableaux
- gestion du message d’erreur #N/A

Consolider les données
- regroupement de données issues de tableaux identiques : par insertion d’une addition, par Collage spécial avec opération, par insertion d’une formule 3D/Multi-feuilles
- regroupement de données issues de tableaux structurés différemment : avec l’Assistant Consolidation, avec ou sans lien aux données sources

Exploiter les sous-totaux et les noms
- création d’une synthèse automatique avec l’outil Sous-total
- organisation du résultat à l’aide du mode du Plan
- création et utilisation des Noms dans les formules
- en savoir plus sur l’utilisation des noms
- utilisation du Gestionnaire de noms

Créer des filtres avancés et insérer des fonctions de base de données
- spécificité des filtres avancés par rapport aux filtres simples
- création et paramétrage d’une zone de critères pour filtrer une base de données
- insertion de fonctions de calculs propres aux bases de données

Utiliser les outils d’analyse
- table de données
- diagramme de Pareto
- diagramme de Gantt

Utiliser les outils de simulation
- la valeur cible pour ajuster automatiquement une valeur en fonction d’un résultat attendu
- le solveur pour définir un objectif en déterminant des variables et des contraintes
- création et affichage de scénarios

Tableaux de bord et indicateurs
À travers des exemples concrets, création d’un tableau qui met en évidence les éléments clés de l’entreprise à l’aide d’indicateurs chiffrés ou graphiques

Enregistrer des Macros et faire ses premiers pas en VBA

Objectif
Automatiser des tâches simples grâce aux macros, comprendre et utiliser l’enregistreur de macro-commandes, savoir dépanner une macro-commande dans l’éditeur.

Durée
7 heures

Prérequis
Maîtrise du logiciel Excel

Public
Utilisateur avancé d’Excel souhaitant automatiser des tâches simples

Programme
- notions de base :

• dans quel cas utiliser des macro-commandes ?
• affichage de l’onglet Développeur dans Excel
• organisation de son travail
• méthode de création

- création et exécution de macros automatiques
- découverte et modification du code généré dans l’Éditeur
- débogage des macros
- syntaxe du code : objet, propriétés, méthodes, procédures
- règles de codification des cellules et des plages de cellules
- écriture d’une fonction en VBA
- insertion d’un bouton d’exécution
- enregistrement et sécurité des macros
- utilisation des références relatives et absolues dans les macros
- combinaison de plusieurs macros à l’aide de la méthode CALL

Approfondir sa connaissance des Macros

Objectif
Faire le point sur les notions de base, utiliser les Variables, les Boucles, les Conditions.

Durée
7 heures

Prérequis
Maîtrise du logiciel Excel, avoir suivi la formation Enregistrer des « Macros et faire ses premiers pas en VBA. »

Public
Utilisateur avancé d’Excel souhaitant automatiser des tâches simples

Programme
- notions de base :dans quel cas utiliser des macro-commandes ?
- organisation du travail à faire
- utilisation des références relatives et absolues dans les macros
- insertion d’un bouton d’exécution
- utilisation des Variables :

• comprendre la notion de Variables,
• déclarer des Variables

- utilisation des Boucles :

• comprendre la notion de Boucles,
• créer des Boucles FOR NEXT / WHILE WEND / DO LOOP

- utilisation des Conditions :

• comprendre la notion de Conditions,
• créer des Conditions IF THEN / IF THEN ELSE

- interaction avec l’utilisateur à l’aide d’une boîte de message (INPUTBOX)
- contrôle de la saisie
- gestion des erreurs