EXCEL - FORMULES ET FONCTIONS COMPLEXES - NIVEAU EXPERT

Informations

Ce programme est standard. (Versions 2010 à 2019)
Toutes nos actions de développement des compétences sont entièrement personnalisées.
Ce programme peut donc être personnalisé à votre demande à la suite d’un pré-diagnostic sous la forme d’un questionnaire ou d’un échange téléphonique.

 

Durée : 21 heures ou 28 heures

Rythme : Temps plein

Format : Présentiel

Public visé : Utilisateurs confirmés d’Excel désirant exploiter les fonctions avancées du tableur. 

Pré-requis : Maîtriser parfaitement les fonctions de calculs avancées d’Excel.

Délais et modalités d’accès à nos actions de formations :

  • L’accès à nos actions de formations peut être initié par l’employeur, à l’initiative du salarié, par un particulier, à la demande de Pôle Emploi …
  • Pour vos demandes, contactez-nous par téléphone au 07 81 37 31 68 ou par courriel : hello@corefor-centrevaldeloire.com, ou sur notre formulaire en ligne « obtenir un devis » :  https://corefor-centrevaldeloire.com/demande-de-devis/
  • Nous nous engageons à vous répondre dans un délai de 72 heures : Lors de cet entretien, nous vous préciserons les modalités de déroulement de l’action de formation, les objectifs, les prérequis avec les modes d’évaluation, les sources de financement, etc…
  • A l’issue de l’évaluation par entretien, questionnaire, nous vous établiront un plan d’action personnalisée qui prend en compte vos attentes/besoins, et vos contraintes, ainsi que le devis associé.
  • A réception du devis signé l’organisation logistique, technique, pédagogique et financière est établie lors des divers échanges avec vous et nous.

Le délai d’accès est de minimum 11 jours.
Délai à convenir avec le Client.

Nombre de participants : Minimum : 6 | Maximum : 8

Méthode pédagogique :
Active et personnalisée, la pédagogie est basée sur le principe de la dynamique de groupe avec alternance d’apports théoriques (30%), et les apports pratiques (70%).

Modalités d’évaluation :
Des évaluations formatives sont réalisées tout au long du parcours formation de l’apprenant sous la forme de petits exercices très courts. Elles ont pour but de permettre à l’apprenant de voir où se situent ses acquis par rapport aux exigences de réussite de la formation. Le résultat de l’évaluation formative n’est communiqué qu’à l’apprenant. Une évaluation sommative est réalisée sous forme d’un questionnaire individuel reprenant les objectifs de l’action de formation.

Accessibilité aux personnes en situation de handicap :
Nos formations peuvent être accessibles aux personnes en situation de handicap. Chaque situation étant différente, nous vous recommandons de nous contacter avant votre inscription.

Profil de l’intervenant :
Consultant-Formateur expert en Bureautique. Il bénéficie d’un suivi de compétences aussi bien au niveau technique que pédagogique assuré par nos services.

Contact :
BESSE Vanina – 07 81 37 31 68
ou courriel : hello@corefor-centrevaldeloire.com

Tarif 2021 :
Tarif Intra-Entreprise, nous consulter pour un devis personnalisé sur ce lien : https://corefor-centrevaldeloire.com/demande-de-devis/

Date de mise en ligne du Programme : 25/08/2021

EXCEL - FORMULES ET FONCTIONS COMPLEXES - NIVEAU EXPERT

Objectifs pédagogiques

  • Maîtriser les fonctions de calculs et de recherches avancées Excel
  • Savoir utiliser et imbriquer des fonctions pour réaliser des calculs complexes avec des tests, des recherches, des extractions de texte, des calculs entre les dates…
  • Savoir réaliser des calculs conditionnels
  • Maîtriser la consolidation d’informations (Multi-feuilles, Multi-classeurs)
  • Comprendre comment créer des formulaires de saisie (case à cocher, liste déroulante etc.…).
  • Pouvoir effectuer des calculs de dates et heures ou des calculs matriciels
  • Analyser plusieurs hypothèses avec les outils de simulation, Savoir tirer parti des fonctionnalités de simulations (valeur cible, solveur, gestionnaire de scénario)
  • Gérer les tableaux avec les utilitaires Excel
  • Créer des macro-commandes simples sans programmation

Programme

Formules et Fonctions de calcul : révisions rapides

  • Bref rappel sur les références de cellules : relatives (A1), absolues ($A$1), mixtes (A$1, $A1)
  • Utiliser des plages nommées dans les formules de calcul

 

Arrondir les chiffres

  • Arrondir un chiffre (Arrondi.inf, Arrondi sup)
  • Garder la partie entière d’un chiffre (Ent, Tronque)

 

Maîtriser les fonctions Logiques : calculer selon des conditions

  • Rappels sur la fonction SI() simple
  • Utiliser la fonction « Si » pour réaliser des calculs conditionnels
  • Combiner les fonctions SI(), ET(), OU(), SIERREUR()
  • Définir plusieurs conditions en imbriquant des fonctions SI()
  • Faire des statistiques en fonction de certains critères :
  • SI(), SOMME.SI.ENS() : Faire une somme en fonction d’une ou plusieurs conditions
  • SI(), MOYENNE.SI.ENS() : Faire une moyenne en fonction de certaines conditions 
  • NBVAL(), NB.SI(), NB.SI.ENS() : Faire un comptage de cellules textes ou numériques en fonction
    de certaines conditions
  • Le nouvelle fonction : CONDITIONS() (2016/2019)

 

Maîtriser les fonctions Statistiques

  • NB, NBVAL, NB.VIDE, ECARTYPE, ECART.MOYEN, FREQUENCE, MIN, MAX, MODE, MEDIANE, RANG, VAR, GRANDE.VALEUR, PETITE.VALEUR, QUARTILE, CENTILE

 

Maîtriser les fonctions Mathématiques

  • ENT – SOUS.TOTAL – ABS – ARRONDI – MOD – PLANCHER -PLAFOND – SOMME.SI – SOMME.SI.ENS

 

Maîtriser les fonctions Chronologiques

  • DATE, JOUR, MOIS, ANNEE, AUJOURDHUI, DATEDIF
  • SEMAINE, JOURSEM, MOIS.DECALER
  • JOURS.OUVRES, FIN.MOIS

 

Maîtriser et appliquer des calculs sur des Dates et des Heures

  • Les dates
  • Comprendre les différents formats de dates
  • Calculer le nombre de jours entre deux dates
  • Calculer le nombre de jours ouvrés entre deux dates
  • Calculer le nombre d’années ou de mois entre deux dates
  • Faire des calculs en temps réel par rapport à la date du jour
  • Les heures
  • Les heures au format Excel
  • Les centièmes d’heures
  • Faire des calculs sur les heures
  • Calculer avec des taux horaires
  • Extraire une partie d’une date avec DATE(), JOUR(), MOIS(), ANNEE()
  • Afficher le jour de la semaine avec JOURSEM()
  • Connaitre un âge précis avec DATEDIF()
  • Afficher la date de fin du mois avec FIN.MOIS()
  • Calculer des dates d’échéance avec MOIS.DECALER()
  • Extraire des heures ou des minutes avec HEURE() et  MINUTE()
  • Additionner des heures
  • Calculer le nombre de jours ouvrés entre deux dates

 

Maîtriser les fonctions Texte

  • GAUCHE – DROITE – STXT – NBCAR – MAJUSCULE – MINUSCULE – NOMPROPRE – CONCATENER – SUPPRESPACE – SUBSTITUTE
  • Manipuler du texte
  • Concaténer une chaîne de caractères
  • Combiner une chaîne de caractère et un calcul
  • Extraction d’une partie d’une chaîne de caractères
  • Mettre un texte en majuscule / minuscule
  • Saisir du texte en intégrant des données provenant de différents tableaux
  • Saisir du texte en intégrant des calculs dont les données proviennent d’un calcul
  • Supprimer les espaces en trop d’une chaîne de caractères

 

Maîtriser les fonctions de Recherche

  • RECHERCHEV – RECHERCHEH – INDEX -EQUIV – DECALER – CHOISIR
  • COLONNE, COLONNES, LIGNE, LIGNES
  • La fonction RECHERCHEV
  • Syntaxe de la fonction RECHERCHEV
  • Récupérer et mettre en relation des informations qui se trouvent sur une autre feuille
    ou dans un autre classeur
  • RECHERCHEV pour comparer deux bases de données
  • Les fonctions EQUIV et INDEX
  • Syntaxe de la fonction EQUIV
  • Syntaxe de la fonction INDEX
  • Combiner INDEX et EQUIV

 

Utilisation de formules de Recherche et Informations dans la feuille

  • Fonctions Matricielles (INDEX – EQUIV – DECALER…)
  • Fonctions d’Informations (ESTNA – ESTNUM – ESTVIDE…)
  • Calculs matriciels

 

Maîtriser les fonctions Base de Données

  • BDECARTYPE, BDECARTYPEP, BDLIRE, BDMAX, BDMIN, BDMOYENNE, BDNB, BDNBVAL, BDPRODUIT, BDSOMME, BDVAR, BDVARP

 

Maîtriser les fonctions Financières

  • VPM, VA, AMORLIN, NPM, VC, TAUX, PRINCPER, ISPMT, INTPER …
  • VPM Calcule le montant des paiements périodiques
  • VC Calcule la valeur future d’une série de dépôts périodiques
  • TAUX Calcule le taux d’intérêts payé.
  • PRINCPER Calcule le montant de principal payé par versement.
  • NPM Calcule le nombre de paiements
  • ISPMT Calcule l’intérêt payé
  • INTPER Calcule l’intérêt payé

 

Utiliser la fonction financière VPM

  • Calculer le montant des remboursements d’un prêt grâce à la fonction VPM
  • Créer une table d’hypothèses pour connaître le montant des mensualités en fonction des variations du taux
    et du nombre de mensualités
  • Données de l’amortissement
  • Montant des intérêts
  • Nombre de paiements
  • Tableau d’amortissement
  • Détermination du montant d’un emprunt

 

Maîtriser les fonctions Matricielles

Matrice : Une matrice est une plage de valeurs, soit une colonne, une ligne ou un ensemble de lignes et de colonnes.

Formule matricielle : Une formule matricielle est une formule qui a la capacité d’effectuer plusieurs calculs sur des ensembles de données d’une matrice.

Syntaxe des formules matricielles : Les formules matricielles dans Microsoft Excel sont entre accolades {  }. Il s’agit de taper des formules standards en débutant par le signe = et de valider vos formules en appuyant CTRL + MAJ + ENTRÉE, ce qui ajoute une paire d’accolades après avoir validé la formule. Si nous tapons directement les accolades, nos formules sont interprétées comme une chaîne de caractères plutôt que comme une formule matricielle.

 

  • Les avantages et inconvénients des fonctions matricielles
  • Les principes d’utilisation
  • Le cas particulier de la fonction SOMMEPROD
  • Les combinaisons des touches CTRL + ALT + MAJ

 

Les «PLUS»

  • Liste des nouvelles Fonctions : Ingénierie, Cube, Comptabilité, Web
    (quelques exemples de démonstration pour chacune de ces nouvelles Fonctions)
  • Imbriquer des données
  • Utiliser la fonction de remplissage instantané

 

Utiliser des outils de simulation : les Outils d’Analyses et de Décision

  • Définir une valeur cible
  • Utiliser le solveur
  • Garder les solutions du solveur grâce au gestionnaire de scénario

 

Analyser plusieurs hypothèses avec les outils de simulation

  • Concevoir des Tableaux de simulation (ex. emprunt, ventes) à 1 ou 2 variables
  • Utiliser le Gestionnaire de scénario, ses variables, l’affichage, synthèse, fusion pour vos simulations
  • Combiner vues et scénarios avec le gestionnaire de rapports
  • Exploiter des vues personnalisées (paramètres d’impression, rapport)
  • Analyser et résoudre des problématiques à plusieurs variables avec le solveur : cellules variables et cibles, contraintes, résultats

 

Modifier le recalcul d’une cellule, l’itération ou la précision

  • Calcul : contrôler quand et comment Excel recalcule les formules
  • L’itération est le recalcul répété d’une feuille de calcul, qui se produit jusqu’à ce qu’une condition numérique particulière soit remplie
  • Précision est une mesure du degré de précision nécessaire pour un calcul

 

Gérer ses tableaux avec les utilitaires Excel : analyse et suivi des formules

  • Utiliser et paramétrer l’impression des vues personnalisées
  • Comprendre et simplifier les formules complexes
  • Affichage des formules ou des valeurs
  • Maîtriser les outils d’audit
    (suivi des dépendances de formules, affichage d’une plage par « photo », fenêtre espion)
  • Les fonctions d’erreurs (ESTERR – ESTERREUR – ESTNA…)
  • Accéder à des données externes à partir d’Excel
  • Récupérer et analyser des données provenant d’une base Access, d’un fichier texte, d’un tableau internet
  • Créer des requêtes avec l’outil MSQuery

 

Faciliter l’utilisation de classeur avec des formulaires

  • Transformer des tableaux en formulaires
  • Intégrer des contrôles formulaires : listes, cases à cocher, groupes d’options… pour saisir ou modifier vos données
  • Contrôler le type de données des cellules pour limiter et restreindre leurs valeurs d’entrées

 

Utilisation de Formulaires

  • Découvrir le concept des formulaires
  • Accéder à l’onglet Développeur
  • Créer et modifier un formulaire
  • Insérer et personnaliser des contrôles
  • Protéger un formulaire
  • Sauvegarder, diffuser et utiliser un formulaire

 

Créer des macros simples sans programmation

  • Accéder à l’onglet développeur
  • Créer, exécuter, modifier et supprimer une macro
  • Créer des classeurs de macros personnelles, macros de classeur
  • Personnalisation de la barre d’outils d’accès rapide
  • Ajouter, supprimer et modifier des commandes
  • Créer une barre d’outils, un groupe, une commande
  • Personnaliser le ruban