EXCEL - FORMULES ET FONCTIONS COMPLEXES - NIVEAU EXPERT

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