Cours Excel formules de calculs et fonctions
EXCEL-FORMULES DE CALCUL:AIDE-MÉMOIRE
DES OPÉRATEURS, DES POINTEURS ET DES FONCTIONS
I - OPÉRATEURS
| Opération | Opérateur | Exemples et remarques | |
| Addition | + | =B4+B$5 =TotalHT+FraisPort On peut utiliser l’adressage de type « bataille navale » (ligne/colonne) (avec ou sans dollars $), mais il est souvent plus lisible d’utiliser les noms de cellules | |
| Soustraction | - | =B4-B$5 =TotalHT-Remise | |
| Multiplication | * | =B4*B$5 =TotalHT*TauxTVA*1,01 | |
| Division | / | =B4/B$5 =Total/B3 | |
| Les 4 opérateurs arithmétiques sont en double sur le clavier : pavé alpha et pavé numérique. | |||
| Puissance | ^ | Le signe ^ est saisi en tapant AltGr 9 (sur le pavé alpha) Exemple : 10² s’écrit 10^2 sous Excel. | |
| Changement de signe | - | En plaçant un « moins » devant une expression, on en change le signe. | |
| Domaines de cellules | : ; | On peut désigner des groupes de cellules : Le signe deux-points ( : ) dit « jusqu’à ». Par exemple, A1:C4 dit « toutes les cellules de A1 jusqu’à C4 ». Le signe point-virgule ( ; ) signifie « et ». Par exemple, A1;C4 dit « les cellules A1 et C4 ». | |
| Formules 3D | : | Quand on travaille sur une seule feuille, on parle de 2D (lignes/colonnes). Quand on ajoute des liens vers d’autres feuilles, on utilise une 3ème dimension. Les formules qui joue de cette possibilité sont des formules "3D". Par exemple, on peut additionner toutes les cellules C3 des feuilles Janvier à Décembre dans la cellule C3 d’une 13ème feuille : on écrira : =SOMME('Janvier : Décembre' ! C3) | |
| Ordre de calcul | Sans parenthèses, Excel eff –% ^ * et / | ectue les opérations dans l’ordre suivant : Négation (comme dans –1) Pourcentage Exposant Multiplication et division | |
| + et – | Addition et soustraction | ||
| & | Concaténation de deux chaînes de texte | ||
| = < > <= >= <>Comparaison | |||
| Ordre forcé | ( ) | Avec les parenthèses, on impose l’ordre des opérations. L’opération =3*2+10 donne 16 (XL fait d’abord la multiplication), alors que =3*(2+10) donne 36… (on force XL à commencer par l’addition) Ceci est essentiel. | |
| Comparaisons | > >= < <= = <> | Les fonctions SI principalement, mais aussi quelques commandes et autres outils, utilisent un des opérateurs de comparaison pour exprimer une condition : ci-contre, on lit : supérieur > supérieur ou égal >= inférieur < inférieur ou égal <= égal = différent <>. | |
| Opération | Opérateur | Exemples et remarques |
| Figer une adresse pour la prochaine recopie | $ | Lors d’une recopie de formule, Excel transforme tout seul la formule pour qu’elle fonctionne encore à sa nouvelle place… Par exemple, si on recopie vers le bas, Excel change la partie « ligne » des adresses de cellules dans la formule, pour que la formule fonctionne en s’adaptant à sa nouvelle ligne. Quand une partie de la formule pointe vers une cellule de référence qui doit être la même dans toutes les formules (TauxTVA par exemple), il faut bloquer le mécanisme de transformation automatique de l’adresse qu’excel exécute lors de la recopie : en plaçant le signe dollar $ devant un numéro de colonne ou un numéro de ligne, on « fige » ce numéro : Par exemple, $A$1 est une adresse complètement figée : on peut la recopier dans tous les sens, elle restera $A$1… Autre exemple : Dans A$1, seule la ligne (1) est figée, pas la colonne (pas le A) : si on recopie vers le bas, le 1 reste 1, mais si on recopie vers la droite, le A devient B, C, D, etc. La touche utile est F4 ! C’est précieux pour ne créer qu’une seule formule pour tout un tableau : par exemple, pour calculer des pourcentages ci-dessous, on ne crée qu’une seule formule, utilisant astucieusement le dollar $. Puis on la recopie d’abord la cellule vers le bas, puis toute la colonne vers la droite : |
| Concaténation | & | Précieux opérateur qui permet d’« additionner des textes » : Si je veux qu’une cellule agrège, compile, rassemble le prénom et le nom contenus dans des cellules séparées, je crée la formule : =Prénom&Nom Pour ajouter des constantes (par exemple, des espaces), on les encadre de guillemets " ". Exemple : =B3&" "&A3 permet de ne pas coller le nom et le prénom. |
II - POINTEURS
A - CROIX BLANCHE POUR SÉLECTIONNER
La grosse croix blanche apparaît quand on vise le corps des cellules.
Elle signale qu’Excel est prêt à sélectionnerdes cellules (les mettre en surbrillance) dès qu’on va cliquer ou glisser avec la souris :
|
Pour bien comprendre à quel moment le pointeur change d’apparence, il faut intellectuellement réduire la croix à son centre. Imaginez que la croix blanche a un centre rouge lumineux… Il n’y a que cette lumière rouge qui compte…
B - FLÈCHE BLANCHE POUR DÉPLACER OU COPIER
La flèche blanche apparaît quand on vise le bord noir d’une sélection.
Elle signale qu’Excel est prêt à déplacer/copier des cellules dès qu’on va glisser avec la souris (avec ou sans la touche Ctrl) :
Intellectuellement, il faut réduire la flèche à sa pointe. Imaginez que la pointe a un bout rouge lumineux… Il n’y a que cette pointe lumineuse qui compte… Vous comprendrez mieux à quel moment le pointeur change d’apparence.
C - CROIX NOIRE POUR COPIER EN INCRÉMENTANT
La croix noire apparaît quand on vise le coin bas droit d’une sélection.
Elle signale qu’Excel est prêt à copier la sélection en incrémentantdès qu’on va glisser avec la souris :
|
Intellectuellement, il faut réduire la croix à son centre. Imaginez que la croix noire a un centre rouge lumineux… Il n’y a que cette lumière rouge qui compte… Vous comprendrez mieux à quel moment le pointeur change d’apparence.
D - i NOIR POUR PLACER LE POINT D’INSERTION
Le i majuscule noir apparaît quand on vise une zone de saisie de texte (la barre de for-
mule, souvent). Il signale qu’Excel est prêt à placer le point d’insertion là où on va cliquer avec la souris :
Intellectuellement, il faut réduire le i noir à son centre. Imaginez que le i a un centre rouge lumineux… Il n’y a que ce point lumineux qui compte… Vous comprendrez mieux à quel moment le pointeur change d’apparence.
E - DOUBLE TRAIT - DOUBLE FLÈCHE POUR LES RÉGLAGES DE LARGEURS /HAUTEURS
Le pointeur double trait-double flèche apparaît quand on vise une zone de réglage de volet (entre deux zones « à géométrie variable »…). Il signale qu’Excel est prêt à déplacer letrait de séparation à partir de là où on va glisser avec la souris :
Comme d’habitude, pour comprendre les transformations de votre pointeur, réduisez-le intellectuellement à son centre de gravité (son centre, souvent…)…
F - POINTEURS SUR OBJETS GRAPHIQUES
Enfin, sur tous les objets dessinés (les formes automatiques, les graphiques, les images importées, les photos, les organigrammes, les zones de texte, les WordArts, etc.),
le pointeur peut prendre deux apparences, suivant que l’on vise le corps de l’objet ou ses poignées (8 carrés placés autour de lui après avoir cliqué dessus) :
Soit c’est le pointeur « 4 points cardinaux » qui permet de déplacer/copier l’objet graphique
(pour copier, il faut glisser en gardant Ctrl enfoncée) :
Soit c’est le pointeur double flèche noire qui permet de redimensionner l’objet :
III - FONCTIONS CHOISIES (BEST OF…)
Les fonctions d’Excel sont des mots réservés que l’on peut taper dans une formule pour obtenir facilement un résultat élaboré. Je vous présente ici les 50 fonctions les plus importantes.
Toutes les fonctions d’Excel utilisent des parenthèses.
Entre ces parenthèses, on précise les contraintes du calcul : Excel appelle argumentsces informations entre parenthèses.
Les arguments sont séparés par le signe point-virgule ;
Certaines fonctions n’ont pas besoin d’arguments : on tape alors 2 parenthèses collées.
D’autres n’ont besoin que d’un seul argument : on n’a pas besoin de point-virgule dans ce cas.
J’ai souvent mis des espaces autour des points-virgules et des parenthèses pour une bonne lisibilité, mais attention : il ne faut JAMAIS taper d’espace dans les formules !
| Objectif | Fonction, syntaxe et exemples |
| Pour classer vos données, en plus de la commande Données Trier qui agit ponctuellement, sur demande, on peut déterminer le rang d’une cellule dans une plage de cellules avec la fonction RANG. On calculera le rang de la première cellule, puis on recopiera cette formule pour calculer le rang des autres. Mais il faudra faire attention à figer la plage deréférence (avec des dollars, touche F4) pour qu’elle reste la même dans toutes les copies. Vous observez sur cet exemple que la fonction RANG est souvent utilement complétée par la commande Format Mise en forme conditionnellequi permet, ici, d’afficher automatiquement les 10 premiers en fond bleu, et les derniers (après le rang 20) en rouge… Les autres restant sur fond blanc… | Syntaxe : RANG ( cellule ; plage ) |
| Dans le même ordre d’idées (classement), on évoquera des fonctions simples qui permettent, à partir d’une plage de cellules, d’extraire la plus grande, la plus petite, le nombre de valeurs, la moyenne… Fonctions MIN, MAX, NB, NBVAL, MOYENNE En jouant d’astuce, on peut décupler les performances d’une fonction : par exemple, pour plafonner un calcul, je vais utiliserMIN ( calcul ; plafond ). De même, pour ne pas dépasser un plancher, je vais utiliserMAX ( calcul ; plancher ). Exemples : MIN ( base * taux ; PlafondSécu ) pour plafonner un calcul de cotisation sociale. MAX ( CA * 0,10 ; Fixe ) pour ne jamais payer un vendeur moins que le minimum fixe prévu (plancher). | Syntaxe : MIN ( plage ) MAX ( plage ) MOYENNE ( plage ) NB ( plage) NBVAL ( plage ) Exemples : MAX ( B2 : B120 ) sort le plus grand nombre. MOYENNE ( Notes ) calcule la moyenne. NB ( C5 : C37 ) calcule le nombrede valeurs numériquesen ignorant les textes éventuels. NBVAL ( C5 : C37 )calcule le nombre de cellules non vides, en comptant les textes et les chiffres. |
| Objectif | Fonction, syntaxe et exemples | |||||||||||||
| Pour saisir et manipuler des dates et des délais, il faut connaître les fonctions date : Excel utilise un calendrier où chaque jour est un rang par rapport au 1er janvier 1900. C’est ainsi que le 9 juillet 2001 est le chiffre 37 081 mis au format j mmmm aaaa… Par ailleurs, les heures sont naturellement des fractions de jour : 12h (midi) est 0,5, 18h est 0,75, 6h du matin est 0,25, etc. Donc, le lundi 9 juillet 2001 à 6h se dit 37 081,25 au format jjjj j mmmm aaaa " à " h"h" On utilise les séparateurs / ou – (slash ou tiret) pour saisir les dates. On utilise le séparateur : (deux points) pour saisir les heures. Exemple : il suffit de saisir 9/7 pour le lundi 9 juillet 2001, car Excel devine que sans année spécifiée, c’est l’année en cours qu’il faut prendre. Autre exemple : pour saisir 6h, il faut taper 6:0 (c’est le :0 qui signale à Excel qu’il s’agit d’une heure). Soustraire deux dates donne un délai. Additionner ou soustraire une date et un délai donne une date Additionner deux dates est absurde. Multiplier ou diviser 2 dates également.
| Même éteint, l’ordinateur mémorise la date et l’heure (avec une pile) On peut donc récupérer « la date-sytème » et « l’heure-système » : =AUJOURDHUI ( ) rend la date sytème (pas l’heure), donc un nombre entier. =MAINTENANT ( ) rend la date et l’heure système, donc un nombre décimal. =DATE ( année ; mois ; jour ) rend le nombre correspondant à la date décrite. Par exemple, DATE(01;07;09) rend 37 081. Avec cette fonction, on peut créer une date de toute pièces, morceau par morceau… Par exemple :=DATE ( AnnéeAchat + 1 ; MoisAchat ; 1 ) permet de calculer la première date anniversaire d’un achat, au 1er du mois. =JOUR ( date ), MOIS ( date ) et ANNEE ( date ) permettent d’extraire une partie de la date, pour effectuer des tests ou recomposer une autre date. Les formats personnalisés à connaître sont : | |||||||||||||
| Vo | us noterez que ET ( cond1 ; cond2 ; cond3…) et OU ( cond1 ; cond2 ; cond3…) sont des fonctions à utiliser à l’intérie | ur du pre- | ||||||||||||
| mier argument des fonctions SI. Un bloc "ET" ou un bloc "OU" génèreVRAI ou FAUX. Le SI s’en sert ensuite pour agir… | ||||||||||||||
| Objectif | Fonction, syntaxe et exemples |
| La fonction DATEDIF(pour les calculs d'âge) (voir le site de Laurent Longre sur ) DATEDIF fait partie des fonctions masquées d'Excel. Elle n'est pas référencée par l'aide en ligne. Elle est pourtant bien commode… DATEDIF permet de calculer la différence entre deux dates en années, mois et jours. Syntaxe : =DATEDIF ( Date1 ; Date2 ; Intervalle ) Cette fonction renvoie la différence entre Date1 et Date2 (Date2 >= Date1) selon l'argument Intervalle, qui peut prendre les valeurs suivantes : "y" : différence en années "m" : différence en mois "d" : différence en jours "ym" : différence en mois, une fois les années soustraites "yd" : différence en jours, une fois les années soustraites "md" : différence en jours, une fois les années et les mois soustraits Exemple : =DATEDIF ( "5/4/1990" ; "15/8/99" ; Intervalle ) renvoie les valeurs suivantes selon la valeur de l'argument Intervalle : "y" : 9 (ans) "m" : 112 (mois) "d" : 3419 (jours) "ym" : 4 (mois restants, une fois les 9 ans soustraits) "yd" : 132 (jours restants, une fois les 9 ans soustraits) "md" : 10 (jours restants, une fois les 112 mois soustraits) La fonction DATEDIF peut être en particulier utilisée pour calculer des âges. Par exemple, si la cellule A1 contient une date de naissance et la cellule B1 la date du jour : - Âge en années simples : =DATEDIF(A1;B1;"y") & SI(DATEDIF(A1;B1;"y")>1;" ans";" an") - Âge en années et mois : =DATEDIF(A1;B1;"y") & SI(DATEDIF(A1;B1;"y")>1;" ans, ";" an, ") & DATEDIF(A1;B1;"ym") & " mois" - Âge en années, mois et jours : =DATEDIF(A1;B1;"y") & SI(DATEDIF(A1;B1;"y")>1;" ans, ";" an, ") & DATEDIF(A1;B1;"ym") & " mois, " & DATEDIF(A1;B1;"md") & SI(DATEDIF(A1;B1;"md")>1;" jours";" jour") | |
| On peut utiliser quelques autres fonctions de date très pratiques, mais pas disponibles tout de suite… Il faut d’abord installer des modules additionnels : lancer Outils Macros complémentaires, et ajouter Utilitaire d’analyse, s’il n’est pas déjà en place… MOIS.DECALER ( date_départ ; mois ) génère une nouvelle date, corrigée (en plus ou en moins) du nombre de mois spécifié… Cette fonction est précieuse pour calculer une date d’échéance ! Exemples : MOIS.DECALER ( DATEVAL("15/01/93" ; 1 ) égale 34015 ou 15/02/93 MOIS.DECALER ( DATEVAL("31/03/93" ; - 1 ) égale 34028 ou 28/02/93 NB.JOURS.OUVRES ( date_départ ; date_fin ; jours_fériés ) Renvoie le nombre de jours ouvrés entiers compris entre date_départ et date_fin. Les jours ouvrés excluent les fins de semaine et toutes les dates identifiées comme étant des jours fériés. Utilisez NB.JOURS.OUVRES pour calculer les charges salariales au prorata du nombre de jours travaillés. jours_fériés représente une plage facultative d'une ou plusieurs dates à exclure du calendrier des jours de travail, comme les jours fériés ou d'autres jours contractuellement chômés. Exemple : NB.JOURS.OUVRES ( DATEVAL("01/10/93") ; DATEVAL("01/12/93") ; DATEVAL("11/11/93")) égale 43 (date_départ;mois) Renvoie le numéro de série de la date du dernier jour du mois précédant ou suivant date_départ du nombre de mois indiqué. Utilisez pour calculer des dates d'échéance tombant le dernier jour du mois. mois représente le nombre de mois avant ou après date_départ. Une valeur de mois positive donne une date future, tandis qu'une valeur négative donne une date passée. Exemples : (DATEVAL("01/01/93"); 1) égale 34028 ou 28/2/93 (DATEVAL("01/01/93"); -1) égale 33969 ou 31/12/92  | |
Voyons maintenant les tables-seuils et taux : je veux saisir un nombre et récupérer dans une table la valeur correspondant à la tranche à laquelle appartient le nombre.
Soit une table de référence, nommée TableTauxRistournes qui contient les règles d’attribution des ristournes à mes meilleurs clients : la formule exploitera cette table
Inutile ici de nommer des colonnes entières, car les perspectives de croissance de la table sont faibles…
Tant que rien n’est saisi en B6, Excel cherche zéro En tapant 32 400 en B6 (B6 est nommée CA), (contenu de B6), le trouve (en début de table, D7) et B7 affiche toute seule « 0,1 »… C’est simple !!! affiche la valeur correspondante (un zéro, ce qui L’outil « Pourcentage » multipliera ce résultat par n’affiche rien si j’ai désactivé l’affichage des zéros 100 et lui ajoutera le signe %… Avec l’outil « Ajout avec Outils Options…). de décimale », on termine la mise en forme.
Avantages formidables de la fonction RECHERCHEV par rapport à une imbrication de fonctions SI :
• Grande simplicité de mise en œuvre : la fonction RECHERCHEV, une fois apprivoisée, est beaucoup plus simple à taper et à débuguer, plus lisible, qu’un enchevêtrement de SI…
• De cette simplicité découlent rapidité et fiabilité !!! Il n’y a pas photo !…
• On ne peut pas imbriquer les SI sans limites ! Alors que RECHERCHEV est sans limites !
• Par ailleurs, la formule qui utilise RECHERCHEV peut être protégée sans gêner l’utilisateur novice (puisqu’on ne la modifiera jamais : seule la table fait l’objet des MAJ éventuelles), alors qu’une formule avec des SI imbriqués pose le problème de la déprotection nécessaire, en plus de la complexité de la correction, ce qui rend, en fait, les MAJ inaccessibles à un novice !
• Ensuite, la mise à jour éventuelle des règles (nouveaux seuils, nouveaux taux…) est très simple (donc rapide et fiable) directement, clairement, et une seule fois !, dans la table (sans toucher aux formules), alors qu’elle est complexe (donc lente et risquée, nécessitant déprotection, réflexion, correction dans le fatras de la formule, recopie sans oubli sur les autres formules, reprotection, tests…) avec des fonctions SI ! Il faut être complètement masochiste pour imbriquer des SI dans les cas simples.
Seul cas dans lequel il faut choisir d’imbriquer des SI : taux appliqués cumulativement : quand on applique un 1er taux à la 1ère tranche PLUS un 2ème taux à la 2ème tranche, PLUS un 3ème taux à la 3ème tranche, etc..
Dans ce cas, il faudra se résoudre à imbriquer les SI… ou bien construire une astucieuse imbrication de tables et de RECHERCHEV, mais on perdra une partie des qualités de simplicité liées à la fonction (les qualités de simplicité de MAJ restant intactes).
| |||||||||
| ||||||||||||||||||||||||||||
| Objectif | Fonction, syntaxe et exemples | |||||
| DROITE ( texte ; nbcar ) : Renvoie les x derniers caracPour manier les chaînes de caractères, tères à droite d'une chaîne de texte. il faut connaître les fonctions texte. Exemple : DROITE ( "Prix de vente"; 5 ) égale "vente" GAUCHE ( texte ; nbcar ) : idem à l’envers… On a souvent besoin de les utiliser en- NBCAR ( texte ) :Longueur (nb de car) d'une chaîne.semble, par des combinaisons astucieu- Exemple : NBCAR ( "Prix de vente" ) égale 13 ses… STXT ( texte ; numdépart ; nbcar ) Renvoie une sous-chaîne de n caractères extraits d'un On n’oubliera pas que : texte à partir de la position que vous avez spécifiée. l’opérateur de concaténation est le & Exemple : STXT ("Cours moyen"; 1; 5 ) égale "Cours" (on dit « éperluette » ou « et commercial ») STXT ("Cours moyen"; 7; 20 ) égale "moyen" Dans les exemples ci-dessus, on connaît le point de départ et la longueur de la chaîne voulue, or ces infos sont souvent variables… La fonction suivante est donc essentielle ! Elle sert à localiser un caractère donné dans la chaîne : CHERCHE ( texte_cherché ; texte ; no_départ ) Renvoie le numéro du caractère au niveau duquel un texte cherché est initialement reconnu lors d'une lecture de gauche à droite. Vous pouvez utiliser les caractères génériques (jokers), le point d'interrogation (?) et l’étoile (*) dans l'argument texte_cherché. Le point d'interrogation correspond à un caractère unique quelconque et l’étoile correspond à une séquence de caractères quelconque. Si vous voulez trouver réellement un point d'interrogation ou un astérisque, tapez un tilde (~) devant ce caractère. Exemple : Si la cellule B17 contient le mot « profit » et la cellule A14 l'expression « Pertes et profits » : CHERCHE ( $B$17 ; $A$14 ) égale 11 Utilisez la fonction CHERCHE pour trouver la position d'un caractère ou d'une chaîne de texte dans une autre chaîne de texte de façon à pouvoir utiliser ensuite l'une des fonctions STXT (ou SUBSTITUE) pour modifier le texte. TROUVE ( texte_cherché ; texte ; no_départ ) la fonction TROUVE, à la différence de la fonction CHERCHE, respecte les majuscules et les minuscules et n'admet pas de caractère générique. MINUSCULE ( texte ), MAJUSCULE ( texte ), NOMPROPRE ( texte )sont faciles à comprendre seul… SUBSTITUE ( texte ; ancien_texte ; nouveau_texte ; no_position ) Exemples : SUBSTITUE("Données Ventes"; "Ventes"; "Coût") égale "Données Coût" SUBSTITUE("Trimestre 1 - 1991"; "1"; "2"; 1) égale "Trimestre 2 - 1991" SUBSTITUE("Trimestre 1 - 1991"; "1"; "2"; 3) égale "Trimestre 1 - 1992" | ||||||
| Exemple de combinaison : Je viens de récupérer chez un ami un classeur dans lequel la même colonnecontient à la fois les noms et les prénoms des gens, et dans le mauvais ordre… Or je veux récupérer ces infos (sans les resaisir, bien sûr), en mettant le prénom avant le nom (lui-même en majuscules) : | ||||||
| Bien sûr, le découpage de la formule en 7 colonnes est là pour expliquer progressivement les possibilités… On pourrait aussi bien arriver au même résultat en une seule formule (un peu moins digeste, il est vrai…) : =STXT(A3;1;CHERCHE(" ";A3))&" "&DROITE(A3;NBCAR(A3)-CHERCHE(" ";A3;CHERCHE(" ";A3)+1))&" " &MAJUSCULE(STXT(A3;CHERCHE(" ";A3);CHERCHE(" ";A3;CHERCHE(" ";A3)+1)-CHERCHE(" ";A3))) | ||||||
| Objectif | Fonction, syntaxe et exemples | |||||
| Excel offre des outils nombreux pour les calculs financiers. Nous nous limiterons aux fonctions simples qui tournent autour des simulations d’emprunts et d’épargne. Toutes les situations d’emprunt se définissent avec 5 variables : VA = valeur actuelle Ce que représente aujourd’hui l’ensemble des remboursements futurs. Par exemple, le montant de l’emprunt pour le prêteur. VC = valeur future Valeur capitalisée, avec les intérêts VPM = montant des remboursements Valeur des montants périodiques (par an, par mois…) NPM = nombre des remboursements Nombre des périodes pour des remboursements constants, à un taux d’intérêt constant. Si on rembourse tous les mois un emprunt sur 4 ans, NPM = 48 (4 ans x 12 mois par an). TAUX = taux de l’emprunt Taux d’intérêt par période (année, mois…) Le taux doit être en rapport avec les périodes : Si on rembourse tous les mois, un taux annuel devra être divisé par 12 pour donner à Excel un taux mensuel. Il faut connaître 4 variables pour calculer la cinquième, l’inconnue… Excel propose donc 5 fonctions : une fonction par cas de figure, une fonction par variable inconnue. Remarques générales (pour toutes les fonctions) : • Les sommes reçues sont positives, alors que les sommes versées sont négatives. On peut se placer du point de vue de l’emprunteur ou au contraire du prêteur… il suffit d’adapter les signes… • L’argument Type = 0 sert à dire que les remboursements ont lieu en fin de période. Type = 1 => début de période. | Combien faudra-t-il rembourser à chaque période ?VPM ( taux ; npm ; va ; vc ; type ) Exemple : cette formule renvoie lavaleur du remboursement mensuel d'un emprunt de 10 000 F au taux annuel de 8 % remboursable en 10 mois : =VPM ( 8%/12 ; 10 ; 10000 ) égale –1 037,03 F Combien faut-il prévoir de temps pour rembourser ?NPM ( taux ; vpm ; va ; vc ; type ) Vc représente la valeur à atteindre après le dernier versement : 0 si c’est un emprunt, un nombre si c’est une épargne. Exemple : la formule suivante renvoie le nombre de mensualités pour leremboursement mensuel d'un emprunt de 10 000 F au taux annuel de 12 % à raison de 1000 F par mois : =NPM(12%/12; -1000; 10000; 0; 1) égale 11 (11 mensualités) Combien vaudra mon épargne ? VC ( taux ; npm ; vpm ; va ; type ) Renvoie la valeur future d'un investissement à remboursements périodiques et constants, et à un taux d'intérêt constant. Exemple : Supposons que vous vouliez économiser de l'argent pour financer un projet spécifique qui sera mis en oeuvre dans un an. Vous déposez 1 000 F sur un compte d'épargne qui vous rapporte 6 % d'intérêts par an, capitalisés mensuellement, ce qui représente un intérêt mensuel de 6%/12, soit 0,5%. Vous envisagez de déposer 100 Francs au début de chaque mois pendant les 12 mois à venir. Quel sera le montant de votre épargne au bout des 12 mois ? =VC( 0,5% ; 12 ; -100 ; -1000 ; 1) égale 2 301,40 F Quel est le taux d’un emprunt ? TAUX ( npm ; vpm ; va ; vc ; type ;estimation) La formule suivante donne le taux d'intérêt d'un emprunt de 80 000 F sur 4 ans (48 = 4 x 12 mois) avec des remboursements mensuels de 2 000 F : =TAUX ( 48 ; -2000 ; 80000 ) égale 0,77 % Quelle est la valeur actuelle d’un investissement ? VA ( taux ; npm ; vpm ; vc ; type ) Exemple : une compagnie financière vous propose un investissement qui vous rapportera 5 000 F à la fin de chaque mois pendant les vingt prochaines années. Le montant de l'investissement est de 600 000 F et le taux d'intérêt du marché est de 8 % par an. En utilisant la fonction VA, vous découvrez que la valeur actuelle de la suite d'annuités de 5 000 F est : =VA ( 0,08/12 ; 12*20 ; 5000 ; ; 0 ) égale -597 771,50 F Le résultat est négatif car il représente un décaissement, ce que vous devriez payer. La valeur actuelle de la suite d'annuités (597 771,50 F) est inférieure à la somme que vous devez payer (600 000 F) pour réaliser cet investissement. Il n'est donc pas intéressant de souscrire à la proposition de la compagnie. | |||||
INDEX ALPHABÉTIQUE
Thèmes page
Addition (opérateur) . 1 et 6
ANNEE ( date ) 8
ARRONDI ( expression ; nbdécimales ) 14
(expression; nbdécimales ) ..14
(expression; nbdécimales ) .14
AUJOURDHUI ( )(pour récupérer la date-système, date actualisée par l’ordinateur. Ne rend pas l’heure.) .8
CHERCHE ( texte_cherché ; texte ; no_départ ) ..15
Comparateurs ..1
Concaténation (associer des textes entre-eux) 2
DATE ( année ; mois ; jour )(pour construire une date avec trois informations séparées : année, mois, jour)) ..8
DATEDIF ( Date1 ; Date2 ; Intervalle )(pour calculer la différence entre deux dates, en années, mois, jours) .9
Division (opérateur) ..1
Dollar $ (figer adresse recopiée) 2
Domaines de cellules ..1
DROITE ( texte ; nbcar ) .15
ENT ( expression ) .14
Éperluette (opérateur de concaténation de texte) ..15
EQUIV ( valeur_cherchée ; zone_de_recherche ; type)(pour localiser une cellule par sa valeur) .13
ESTNA ( expression )(à utiliser avec SI, pour traiter les erreurs NA, Non Accessible) ..13 ET ( cond1 ; cond2 ; cond3…) 8
(date_départ;mois) ..9 Fonctions choisies .6 Formules 3D .1
GAUCHE ( texte ; nbcar ) .15
INDEX ( zone_d’extraction ; indice_Ligne ; indice_Colonne )(pour localiser une cellule par son adresse)13
JOUR ( date ) 8
MAINTENANT ( )(pour récupérer la date et l’heure-système, date et heure actualisées par l’ordinateur.) ..8
MAJUSCULE ( texte ) 15
MAX ( plage )(pour appliquer un plancher) ..7 MIN ( plage )(pour appliquer un plafond) 7
MINUSCULE ( texte ) 15
MOD ( expression ; diviseur ) .14 MOIS ( date ) 8
MOIS.DECALER ( date_départ ; mois ) 9
MOYENNE ( plage ) ..7
Multiplication (opérateur) ..1
NB ( plage )(pour dénombrer les cellules numériques dans une plage) 7
NB.JOURS.OUVRES ( date_départ ; date_fin ; jours_fériés ) .9
( zone à tester et dénombrer ; test ) ..14
NBCAR ( texte ) .15
NBVAL ( plage )(pour dénombrer les cellules non vides dans une plage) ..7
NOMPROPRE ( texte ) .15
NPM ( taux ; vpm ; va ; vc ; type )Combien faut-il prévoir de temps pour rembourser ? .16
Opérateurs .1
Ordre de calcul 1
OU ( cond1; cond2; cond3…) .8
Parenthèses (ordre de calcul) ..1 Pointeurs 3 Pourcentage (calcul et recopie figée grâce au dollar $) .2
Puissance (opérateur) 1
RANG ( cellule ; plage )(attention aux $ dans la plage) .7
RECHERCHEV ( valeur connue ; Table ; ColÀRendre ; FAUX ) ..11
RECHERCHEV ( valeur connue ; Table ; ColÀRendre ; VRAI ) 11
SI (condition ; expression ) ..10
SI (condition ; expression si la condition est vraie ; expression si cond. fausse ) .10
SOMME ( plage ) 6
( zone à tester ; test ; zone à sommer ) .14 Soustraction (opérateur) ..1
STXT ( texte ; numdépart ; nbcar ) ..15
SUBSTITUE ( texte ; ancien_texte ; nouveau_texte ; no_position ) ..15
TAUX ( npm ; vpm ; va ; vc ; type ; estimation )Quel est le taux d’un emprunt ? 16
TRONQUE ( expression ; no_chiffres ) .14
TROUVE ( texte_cherché ; texte ; no_départ ) ..15
VA ( taux ; npm ; vpm ; vc ; type )Quelle est la valeur actuelle d’un investissement ? 16 VC ( taux ; npm ; vpm ; va ; type )Combien vaudra mon épargne ? .16
VPM ( taux ; npm ; va ; vc ; type )Combien faudra-t-il rembourser à chaque période ? 16
? ? ? ? ?