utilisation AVANCéE des FORMULES (PCIE bASE DE DONNEES)

1.      Plage de cellules dans une fonction

Utiliser plusieurs plage de cellules dans une fonction

Saisir la fonction

Sélectionner la première plage

Saisir un point-virgule

Sélectionner la plage ou la cellule suivante

Terminer la saisie de la fonction puis valider la saisie

NB : Il est possible d’utiliser la touche Ctrl pour sélectionner des plages non-contigües plutôt que de saisir un point-virgule entre chaque plage.

Réaliser un calcul dans une fonction

Saisir la fonction

Saisir le calcul à réaliser entre 2 points-virgules

Terminer la saisie de la fonction puis valider la saisie

2.      Calculs d’heures

Extraire l’heure

Saisir dans la cellule de résultat =heure(référence de la cellule contenant l’heure à extraire

Valider avec la touche Entrée

Extraire les minutes

Saisir dans la cellule de résultat =minute(référence de la cellule contenant l’heure à extraire

Valider avec la touche Entrée

La fonction TEMPS

Convertit des minutes en heures et minutes

Saisir dans la cellule de résultat =temps(cellule contenant les heures ;la cellule contenant les minutes ;cellule contenant les secondes

Valider avec la touche Entrée

Convertir des heures en centièmes

Il faut multiplier la valeur des heures par 24 (diviser par 24 pour repasser en heure)

Additionner des heures

Utiliser une somme classique

Appliquer le format [h] :mm :ss pour qu’Excel puisse aller au-delà de 24 heures

Calculer la différence entre deux heures

Réaliser une simple soustraction

Calculer un prix horaire

Diviser le nombre d’heures effectuées par '1:00' puis multiplier par le prix horaire

3.      Calculs de dates

Ajouter des jours a une date

Réaliser une simple addition

La fonction MOIS .DECALER

Ajoute des mois à une date précise

Saisir =mois.decaler(date de départ; nombre de mois)

Valider avec la touche Entrée

La fonction FIN.MOIS

Calcule la date de fin de mois d'après un intervalle

Saisir =fin.mois (date de départ; nombre de mois

Valider avec la touche Entrée

Calculer le premier jour du mois suivant

Saisir =fin.mois (date de départ;0)+1

Valider avec la touche Entrée

Ajouter des années, des mois et des jours à une date

Saisir =date(annee(cellule contenant l'année)+nombre d'années à ajouter; mois(cellule contenant l'année)+nombre de mois à ajouter; jour(cellule contenant l'année)+nombre de jours à ajouter;

La fonction DATEDIF

Saisir =datedif(date de départ; date de fin; y pour afficher les années m pour les mois d pour les jours

4.      Fonction texte

La fonction GAUCHE

Extrait des caractères à partir de la gauche

Saisir =gauche( dans la cellule du résultat

Préciser le texte ou la cellule contenant le texte sur lequel on veut extraire des caractères depuis la gauche

Saisir un 

Saisir le nombre de caractères à extraire

Fermer la parenthèse

Taper sur la touche Entrée pour valider la formule

Concaténation de textes

Utiliser le caractère & entre caractères à concaténer

La fonction DROITE

Extrait des caractères à partir de la droite

Saisir =droite( dans la cellule du résultat

Préciser le texte ou la cellule contenant le texte sur lequel on veut extraire des caractères depuis la droite

Saisir un 

Saisir le nombre de caractères à extraire

Fermer la parenthèse

Taper sur la touche Entrée pour valider la formule

La fonction MAJUSCULE

Passe le texte en majucule

Saisir =majuscule( dans la cellule du résultat

Indiquer le texte à passer en majuscule ou la référence de cellule contenant le texte (ou une fonction de texte)

Fermer la parenthèse

Taper sur la touche Entrée pour valider la formule

La fonction SUBSTITUE

Remplace un texte par un autre

Saisir =substitue( dans la cellule du résultat

Saisir les différents arguments

Fermer la parenthèse

Taper sur la touche Entrée pour valider la formule

5.      Calculs lors des copies

Additionner en copiant des données

Sélectionner les cellules concernées par la copie

Utiliser l'option  Copier du menu contextuel

Utiliser l'option Collage spécial du menu contextuel sur la cellule de destination

Dans le cadre Opération, sélectionner l'option Addition

Cliquer sur le bouton

Soustraire en copiant des donnés

Sélectionner les cellules concernées par la copie

Utiliser l'option  Copier du menu contextuel

Utiliser l'option Collage spécial du menu contextuel sur la cellule de destination

Dans le cadre Opération, sélectionner l'option Soustraction

Cliquer sur le bouton

6.      Audit des formules

Afficher les formules à la place de leurs résultats

Pour afficher les formules :

o      Cliquer sur l’onglet Formules

o      Cliquer sur l’outil  Afficher les formules dans le groupe Audit de formules

Pour masquer les formules :

o      Cliquer à nouveau sur l’outil  Afficher les formules dans le groupe Audit de formules

Afficher les relations entre les cellules et une formule

Pour afficher les cellules qui interviennent dans la formule :

o      Activer la cellule contenant la formule

o      Cliquer sur l’onglet Formules

o       Cliquer sur le bouton  Repérer les antécédents dans le groupe Audit de formules

Pour afficher les cellules contenant une formule qui fait référence à la cellule sélectionnée :

o       Cliquer sur le bouton  Repérer les dépendants dans le groupe Audit de formules

Pour retirer les flèches :

o       Cliquer sur le bouton  Supprimer les flèches dans le groupe Audit de formules

Repérer les erreurs

Activer la cellule qui génère une erreur

Pour obtenir des informations sur l’erreur :

o      Pointer l’indicateur d’erreur

Pour vérifier l’erreur :

o      Ouvrir la liste de l’outil  Vérification des erreurs

o      Sélectionner l’option Repérer une erreur

Pour retirer les flèches :

o       Cliquer sur le bouton  Supprimer les flèches dans le groupe Audit de formules

Analyser l’erreur d’une formule

Activer la cellule contenant l’erreur

Ouvrir la liste de l’outil  Vérification des erreurs

Pour masquer l’erreur :

o      Sélectionner l’option Ignorer l’erreur

Pour corriger la formule :

o      Sélectionner l’option Modifier dans la barre de formule

Analyser les erreurs de toutes les formules

Activer la feuille contenant les formules

Cliquer sur l’onglet Formules

Cliquer sur l’outil  Vérification des erreurs dans le groupe Audit de formules

Utiliser les différentes options de la boîte de dialogue de vérifications des erreurs

Cliquer sur le bouton  lorsque la vérification est terminée

Règles de vérification des erreurs

Cliquer sur le bouton Office

Cliquer sur le bouton Options Excel

Sélectionner l’option Formules dans le volet gauche

Dans le cadre Règles de vérification des erreurs, activer ou désactiver les options souhaitées

7.      Évaluation de formules

Évaluer les formules

Sélectionner la cellule qui contient la formule à évaluer

Cliquer sur l’onglet Formules

Cliquer sur l’outil  Évaluation de formule

Pour afficher le détail d’une formule qui fait référence à une ou plusieurs autre(s) formule(s) :

o      Cliquer sur le bouton Pas à pas détaillé

Pour revenir à la formule initiale :

o      Cliquer sur le bouton Pas à pas sortant

Pour afficher le résultat de l’expression soulignée :

o      Cliquer sur le bouton Évaluer

Pour afficher le résultat de la partie suivante :

o      Cliquer à nouveau sur Évaluer

Pour recommencer l’analyse :

o      Cliquer sur le bouton Redémarrer

Pour quitter l’évaluation :

o      Cliquer sur le bouton

8.      Fenêtre Espion

Utiliser la fenêtre Espion

Sélectionner les cellules à observer

Cliquer sur l’onglet Formules

Cliquer sur l’outil  Fenêtre Espion

Pour ancrer la fenêtre sous le ruban :

o      Double-cliquer dans la barre de titre de la Fenêtre Espion

Pour libérer la Fenêtre Espion :

o      Glisser la barre de titre vers la zone de travail

Pour créer un espion :

o      Cliquer sur l’outil  Ajouter un espion

Si nécessaire préciser la sélection de cellules

o      Cliquer sur le bouton Ajouter

Ajouter des cellules à la liste

Sélectionner les cellules à ajouter

Cliquer sur l’outil  Ajouter un espion

Cliquer sur le bouton Ajouter

Modifier la largeur des colonnes de la fenêtre Espion

o      Réaliser un cliquer-glisser au niveau de l’intersection des intitulés de colonnes

ou

o      Double-cliquer sur l’intersection pour ajuster la colonne au contenu

Utiliser la Fenêtre Espion

Pour atteindre rapidement une sélection de la Fenêtre Espion :

Double-cliquer sur la ligne correspondante

Supprimer un espion

Sélection la ou les références à supprimer

Appuyer sur la touche Suppr

Fermer la fenêtre

Cliquer sur le bouton de fermeture  de la Fenêtre Espion

9.      Formule matricielle

Construire une formule matricielle

Sélectionner les cellules de la matrice du résultat

Saisir le signe

Construire la formule en utilisant les références de la matrice

Pour valider la formule matricielle, taper Ctrl+Shift+Entrée

NB : Une formule matricielle est encadrée d'accolades et les cellules de la matrice de résultats sont protégées contre les modifications.

Modifier une formule matricielle

Cliquer dans la matrice de résultat

Utiliser le raccourci clavier Ctrl + /

Effectuer les modifications dans la barre de formule

taper Ctrl+Shift+Entrée pour valider les modifications

Utiliser une formule matricielle pour poser des conditions

Pour calculer une statistique contenant plusieurs conditions avec une formule matricielle:

o      Saisir la formule (par exemple Somme

o      Saisir les différentes conditions, chacune entre parenthèses, en les séparant par le signe

o      Saisir le signe et sélectionner la plage sur laquelle le calcul doit être effectué

taper Ctrl+Shift+Entrée pour valider les modifications

10. Consolidation

Effectuer une consolidation simple

Activer la 1^ère cellule d'affichage du résultat

Cliquer sur l'onglet Données

Cliquer sur l'outil  Consolider dans le groupe Outils de données

Sélectionner un type de calcul dans la liste Fonction

Préciser la 1^ère plage dans la zone Référence

Cliquer sur le bouton Ajouter

Renouveler l'ajout de plage autant de fois que nécessaire

Cliquer sur le bouton

Consolider en intégrant les titres

Activer la 1^ère cellule d'affichage du résultat

Cliquer sur l'onglet Données

Cliquer sur l'outil  Consolider dans le groupe Outils de données

Sélectionner un type de calcul dans la liste Fonction

Préciser la 1^ère plage dans la zone Référence

Cliquer sur le bouton Ajouter

Renouveler l'ajout de plage autant de fois que nécessaire

Activer les options qui conviennent dans la zone Étiquettes dans pour intégrer les titres des lignes et/ou des colonnes

Cliquer sur le bouton

Effectuer une consolidation avec liaison

Activer la 1^ère cellule d'affichage du résultat

Cliquer sur l'onglet Données

Cliquer sur l'outil  Consolider dans le groupe Outils de données

Sélectionner un type de calcul dans la liste Fonction

Préciser la 1^ère plage dans la zone Référence

Cliquer sur le bouton Ajouter

Renouveler l'ajout de plage autant de fois que nécessaire

Activer l'option Lier aux données sources

Cliquer sur le bouton

NB : Si les plages qui contiennent les sources de la consolidation sont modifiées, la consolidation est automatiquement modifiée. Le contenu des cellules est sous forme de fonctions au lieu de valeurs.

11. Fonctions financières

La fonction VPM

Calcule la mensualité en fonction du taux d'emprunt, du capital emprunté et du nombre de mensualités

Saisir =VPM(

1^er argument : taux d'intérêt

2^ème argument : nombre de mensualités

3^ème argument : montant emprunté

Fermer la parenthèse

Valider la fonction

NB : Si les si les arguments sont exprimés pour une année, diviser par 12. Si le résultat est négatif, encadrer la fonction VPM par la fonction ABS pour obtenir la valeur absolue.

La fonction INTPER

Calcule les intérêts payés pour une mensualité

Saisir =INTPER(

1^er argument : taux d'intérêt

2^ème argument : numéro de la mensualité

3^ème argument : nombre total de mensualités

Fermer la parenthèse

Valider la fonction

NB : Si les si les arguments sont exprimés pour une année, diviser par 12. Si le résultat est négatif, encadrer la fonction INTPER par la fonction ABS pour obtenir la valeur absolue.

12. Table à double entrée

Préparer une table à double entrée

Saisir sur une ligne les valeurs de la 1^ère variable

Saisir sur une colonne les valeurs de la 2^ème variable, 1 colonne plus à gauche et 1 ligne en dessous de la 1^ère variable

Saisir la formule qui sera utilisée à l'intersection des lignes et des colonnes

Créer une table à double entrée

Sélectionner la plage prévue pour la table à double entrée

Cliquer sur l'onglet Données

Cliquer sur la liste de l'outil  Analyse de scénarios

Sélectionner l'option Table de données

Dans la zone Cellule d'entrée en ligne, préciser la référence de la cellule qui contient la valeur utilisée dans la formule des données en lignes

Dans la zone Cellule d'entrée en colonne, préciser la référence de la cellule qui contient la valeur utilisée dans la formule des données en colonne

Cliquer sur le bouton

NB : La table à double entrée utilise une fonction matricielle.

13. Valeur cible

Valeur cible

Activer la cellule qui doit atteindre une certaine valeur (la cellule doit contenir une formule)

Cliquer sur l'onglet Données

Cliquer sur la liste de l'outil  Analyse de scénarios

Sélectionner l'option Valeur cible

Préciser la valeur à atteindre dans la zone Valeur à atteindre

Préciser la valeur e la cellule à ajuster pour atteindre la valeur cible dans la zone Cellule à modifier

Cliquer sur le bouton

Cliquer sur  pour appliquer la solution ou sur Annuler pour revenir à la valeur d'origine

14. Le solveur

Installer la macro complémentaire Complément Solveur

Cliquer sur le bouton Office

Cliquer sur le bouton Options Excel

Sélectionner Compléments dans la liste de gauche

Ouvrir la liste Gérer

Sélectionner Compléments Excel

Cliquer sur le bouton Atteindre

Activer l'option Complément solveur

Cliquer sur le bouton

Cliquer sur le bouton

Utiliser le solveur

Activer la cellule à définir

Cliquer sur l'onglet Données

Cliquer sur le l'outil  Solveur dans le groupe Analyse

La zone Cellule cible à définir affiche la référence de la cellule contenant la formule

Activer l'option souhaitée : Max Min ou une valeur à préciser

Préciser les références des cellules à ajuster dans la zone Cellules variables

Cliquer sur le bouton Résoudre

Cliquer sur  pour appliquer la solution ou sur Annuler pour revenir aux valeurs d'origine

Ajouter une contrainte

Cliquer sur le l'outil  Solveur dans le groupe Analyse

Cliquer sur le bouton Ajouter dans la zone Contraintes

Préciser la condition de la contrainte et cliquer sur Ajouter

Ajouter d'autres contraintes si besoin

Cliquer sur le bouton

Cliquer sur le bouton Résoudre

Cliquer sur  pour appliquer la solution ou sur Annuler pour revenir aux valeurs d'origine

15. Gestion de scénarios

Créer un premier scénario

Cliquer sur l'onglet Données

Cliquer sur la liste du bouton  Analyse de scénarios

Sélectionner l'option Gestionnaire de scénarios

Cliquer sur le bouton Ajouter

Saisir le nom du scénario

Préciser la/les cellule(s) à modifier dans la zone Cellules variables

Cliquer sur le bouton

Saisir la valeur des cellules à tester suivant les hypothèses à tester

Cliquer sur le bouton pour conserver le résultat

Cliquer sur le bouton

Ajouter un scénario

Cliquer sur l'onglet Données

Cliquer sur la liste du bouton  Analyse de scénarios

Sélectionner l'option Gestionnaire de scénarios

Cliquer sur le bouton Ajouter

Saisir le nom du scénario

Préciser la/les cellule(s) à modifier dans la zone Cellules variables

Cliquer sur le bouton

Saisir la valeur des cellules à tester suivant les hypothèses à tester

Cliquer sur le bouton pour conserver le résultat

Cliquer sur le bouton

Afficher un scénario

Cliquer sur l'onglet Données

Cliquer sur la liste du bouton  Analyse de scénarios

Sélectionner l'option Gestionnaire de scénarios

Sélectionner le nom du scénario

Cliquer sur le bouton Afficher

Afficher la synthèse des scénarios

Cliquer sur l'onglet Données

Cliquer sur la liste du bouton  Analyse de scénarios

Sélectionner l'option Gestionnaire de scénarios

Cliquer sur le bouton Synthèse

Activer l'option Synthèse de scénario si nécessaire

Dans la zone Cellules résultantes, préciser les références des cellules dont les résultats sont à inclure dans la synthèse

Cliquer sur le bouton