Liste de toutes les formules et les fonctions Excel
Pourquoi expliquer 150 fonctions Excel ? Après tout, il y a 235 fonctions dans Microsoft Excel 2003 et 340 fonctions pratiques dans Excel 2007. Ces fonctions étaient celles que je pouvais rapidement vous donner une explication pour que vous puissiez les utiliser. Ne me demandez pas de vous expliquer des fonctions en trigonométrie ou celles utilisées par des ingénieurs. Je ne pourrais pas le faire. Surtout, se sont celles qui sont les plus utilisées et les plus pratiques.
Grâce à la table des matières des pages qui suivent, vous pouvez aller directement à la fonction ou à la section qui vous intéresse. Cependant, je vous incite fortement à lire ce manuel au complet et de l'appliquer à vos travaux. Vous deviendrez la référence que tout le monde viendra voir pour leurs problèmes. Vous deviendrez ainsi indispensable.
Tel que mentionné, ce livre n'est pas une référence pour toutes les fonctions disponibles. Ce manuel se veut un point de départ pour votre apprentissage. Ne cessez jamais d'apprendre.
Je tiens aussi à prendre quelques lignes pour remercier Monsieur Patrice Roye Qui m'a aidé pour développer ce que vous avez devant vous.
Bonne lecture et bon apprentissage.
Table des matières
Introduction 1 =atan() ..11
=cos() 11
Comment écrire une formule ..1 =cosh() .11
Insérer une fonction .2 =degres() .11
La fenêtre Insérer une fonction ..3 =ent() 11
Rechercher une fonction 3 =exp() 11
Écrire la formule 5 =fact() ..11
Les fonctions imbriquées; des fonctions plus fortes =impair() .11
5 =mod() .11
=pair() ..12
Fonctions financières .6 =pi() ..12
=plafond() ..12
=amordegrc() ..6 =plancher() .12
=amorlin() .6 =produit() 12
=amorlinc() ..6 =puissance() ..12
=db() 7 =racine() ..12
=ddb() .7 () .12
=intper() .7 =radians() 12
=ispmt() .7 =romain() 12
=npm() 7 =signe() 12
=princper() 7 =sin() .12 =taux() 8 =sinh() ..12 =va() .8 =somme() 12
=van() ..8 () ..15
=vc() .8 =sous.total() ..15
=vdb() .8 =tan() 15
=vpm() 8 =tanh() .15
=tronque() ..15
Fonctions reliées aux dates et aux heures 9
Fonctions statistiques ..15
=année() .9
=date() .9 =averagea() 15
=heure() .9 =max() .15
=jour() .9 =mediane() .15
=jours360() ..9 =Min() ..15
=joursem() .9 =mode() ..15
=maintenant() ..9 =moyenne() 16
=minute() ..9 =nb() .16
=mois() .10 () .16
=seconde() ..10 () 17
=temps() ..10 =nbval() ..17
=pente() 17
=prevision() 17
Fonctions mathématiques et trigonométriques ..10 =quartile() ..17
=rang() .17
=abs() 10 =tendance() 17
=alea() ..10 =var() 17
=alea.entre.bornes() ..10 =vara() .18 =arrondi() 10 =varpa() ..18 () ..11
() 11
=asin() ..11
=asinh() 11
150 fonctions Excel expliquées
La fonction et() permet à la fonction si() de vérifier plusieurs conditions en même temps. Il y a des milliers de combinaisons possibles en utilisant plusieurs fonctions imbriquées l'une dans l'autre. Imaginez ceux que vous pourriez créer pour vos besoins.
La prochaine partie du manuel consiste de la liste de plusieurs fonctions Excel. Elles sont placées selon les catégories déterminées par Excel 2003 : finances, date et heures, math et trigonométrie, statistiques, recherche et matrices, base de données, texte logique et informations. Certaines de ces fonctions auront une description plus détaillée pour aider à vous démontrer son potentiel. Afin de maximiser l'espace sur la page, la majorité des pages sont découpées en deux colonnes. Certaines descriptions pourraient se retrouver sur deux colonnes et rendre la lecture un peu plus difficile.
Je vous invite à lire ce document au complet et d'expérimenter avec les fonctions avant d'en avoir besoin pour vos travaux.
Fonctions financières
Les fonctions financières permettent à un individu ou a une entreprise de mieux gérer les fonds reçus et à remettre. Vous pouvez calculer la vrai valeur d'un investissement ou d'un emprunt ou le rendement que vous devez poursuivre pour atteindre vos objectifs.
Les amortissements permettent de déterminer quelle partie d'un bien est devenu désuet et de déterminer sa valeur à un moment donnée. Lors de la préparation de rapports financiers, une entreprise peut utiliser des calculs d'amortissement différents selon le type de matériel. Après tout, un crayon sera désuet bien plus rapidement d'une automobile ou une machine dédiée à la production. Les prochaines fonctions démontrent les différents types d'amortissement qu'Excel peut calculer.
=amordegrc(coût initial; date d'achat;date de début de la première annéecomptable pourl'amortissement;valeur résiduelle;numéro de l'année dont vous désirezconnaître l'amortissement; tauxd'amortissement) Affiche le montant d'un amortissement dégressif pour une période selon la valeur initiale.
=amorlin(valeur d'achat;valeur résiduelle;durée de vie prévue) Affiche le montant d'un amortissement linéaire.
Ex. : =amorlin(7500;2500;5) = 1000
Le montant qui peut être amorti est de 5000 $ sur une période de 5 ans. Selon la méthode d'amortissement linéaire, un même montant sera retiré sur chaque période de l'amortissement. Dans ce cas, cela sera de 1 000 $ par année.
=amorlinc(valeur d'achat; dated'achat; date de début de la premièreannée comptable pourl'amortissement; valeur résiduelle; période pour laquelle vous désirezconnaître l'amortissement; tauxd'amortissement) Affiche le montant d'un amortissement linéaire proportionnel.
150 fonctions Excel expliquées
= RechercheV(), =RechercheH()
Les fonctions =recherchev et =rechercheh sont des formes de la fonction =si un peu plus développé. Il est possible "d'imbriquer" plusieurs si pour répondre à plusieurs conditions. Mais, la limitation de la fonction =si est que vous pouvez imbriquer jusqu'à 6 niveaux de si seulement. Que faire si vous voulez répondre à une question qui aurait besoin de plus de 6 niveaux? De plus, rendu à ce point, il devient difficile de gérer tous ces =si() imbriqués.
Il est cependant plus facile de le faire avec l'une des fonctions = recherche. Surtout lorsque vous avez une grille ou un tableau de comparaison. Par exemple, une grille pour déterminer la note des étudiants ou une grille pour déterminer le taux d'imposition des employés.
Reprenons le dernier exercice de la fonction =si. Un marchant donne un rabais de 5% sur un item si un client en achète 5 ou plus. Ce rabais monte à 10% si le client en achète 10 ou plus.
Avec la fonction si() cela donnait la formule suivante:
=si(b1>=10;b3*0,1;si(b1>=5;b3*0,05;0)). Cette formule est simple puisqu'il a seulement trois possibilités (10%, 5% et 0%). La formulation devient plus difficile à concevoir lorsqu'on ajoute de plus en plus de possibilités. La fonction recherchev() ou rechercheh() peut aider dans cette situation.
La fonction à besoin de trois paramètres. Le premier est l'adresse de la cellule qui sera comparée. Elle sera comparée au contenu du deuxième paramètre qui contiendra l'adresse du bloc de cellules d'un tableau de comparaison. Le troisième paramètre est pour indiquer qu'il faut afficher le contenu de quelle colonne (pour recherchev() ) ou quelle ligne (pour rechercheh() ) du tableau de comparaison.
=recherchev(cellule à comparer;tableau de comparaison;index de colonne)
Avant d'essayer la fonction, il faut préparer le tableau de comparaison.
Entrez les chiffres suivants dans les cellules suivantes; soit dans les cellules A13 à B15.
La première colonne du tableau de comparaison sert de grille de comparaison. Elle doit toujours être en ordre croissant.
Vous écrivez toujours la valeur minimale, ou le plancher, pour atteindre ce niveau. Le "plafond" est le "plancher" de la prochaine ligne du tableau. Donc, pour la première ligne du tableau, le seuil minimal est zéro tandis que le maximum est 5 exclusivement ou [0, 5[ pour ceux qui se souviennent de leurs opérations mathématiques. C'est aussi pour cette raison que les valeurs doivent toujours être en ordre croissant.
150 fonctions Excel expliquées
C'est exactement pour cette situation que cette fonction offre un quatrième paramètre optionnel. Il vous permet de décider comment la fonction doit opérer. Doit-elle chercher entre telle et telle valeur ou chercher une valeur exacte? Ce quatrième paramètre avec l'option faux ou 0 force la fonction à chercher la valeur exacte. Si le quatrième paramètre est égal à vrai, à 1 ou n'est pas présent, la fonction recherchev va chercher entre tel ou tel valeur. Donc, si la fonction aurait été écrite de cette façon
=RECHERCHEV(B1;A4:B7;2;FAUX), il faudrait que le texte qui serait dans la cellule B1 soit écrit exactement de la même façon que l'une des valeurs du tableau de comparaison.
Dans la cellule B2, entrez la fonction suivante: =RECHERCHEV(B1;A4:B7;2;FAUX) . Dans la cellule B1, entrez At .
Le résultat sera #N/A pour indiquer qu'elle est non applicable dans ce cas. Ceci est vrai puisque At n'est pas dans le tableau de comparaison.
Fonctions de base de données
Depuis ses débuts, Excel offre la capacité de gérer des listes de données. C'est un tableau d'informations sur des personnes, des choses ou des événements. La première ligne d'une liste contient le nom de chaque champ. Un champ est une caractéristique ou une information que vous désirez conserver. La colonne va conserver les informations que pour ce champ. Chaque ligne représente un enregistrement décrit une personne, une chose ou un événement selon les champs que vous avez décidé de conserver. La liste de données ne doit pas contenir de lignes vides; tout doit être collé. Sinon, Excel va croire qu'il s'agit de la fin de la liste de données même s'il y a d'autres enregistrements plus bas sur la feuille de calcul. Afin de ne pas confondre le logiciel sur les dimensions de la liste, ne placez pas du texte ou des chiffres immédiatement au dessus ou en dessous ainsi que sur les côtés de la liste.
Données: Toute information que vous voulez conserver pour vous aider dans vos analyses.
Champ: Les informations de mêmes types sont conservées dans un même
champ. Ex.: Nom, Prénom, Taille, Salaire, Quantité Avec Excel, les données d'un même champ sont conservées dans une même colonne. Les noms des champs sont toujours à la première ligne d'une liste de données.
Enregistrement: Toutes les informations concernant une personne, une chose ou un
événement. Avec, Excel, chaque ligne de la liste de données est un enregistrement. Donc, une liste de données peut contenir des enregistrements sur des choses, des personnes ou des événements.
Unexemple pour toutes les fonctions
trouver la plus petite valeur;étenduede la zone de critères) Affiche la plus petite valeur d'un certain champ numérique selon les critères demandés.
Ex. : =bdmin(A1:B6;2;D1:D2) = 15
=bdmoyenne(étendue de la liste dedonnées;numéro de la colonne àtrouver la moyenne;étendue de lazone de critères)
Affiche la moyenne d'un certain champ numérique selon les critères demandés. Ex. : =bdmoyenne(A1:B6;2;D1:D2) = 20
C'est la moyenne des chiffres 15, 20 et 25.
=bdnb(étendue de la liste dedonnées;numéro de la colonne àtrouver la moyenne;étendue de lazone de critères)
Affiche le nombre d'enregistrements qui répond aux critères. Ex. : =bdnb(A1:B6;2;D1:D2) = 3
=bdnbval(étendue de la liste dedonnées;numéro de la colonne àtrouver le nombre;étendue de la zonede critères)
Affiche le nombre d'enregistrements ayant un contenu autre que des chiffres qui répondent aux critères demandés.
Ex. : =bdnbval(A1:B6;2;D1:D2) = 3
=bdproduit(étendue de la liste de données;numéro de la colonne à
150 fonctions Excel expliquées
trouver le nombre;étendue de la zone
de critères) Affiche le produit des valeurs
Ex. : =bdproduit(A1:B6;2;D1:D2) = 7500 C'est le produit des chiffres 15, 20 et 25 (15 * 20 * 25).
=bdsomme(étendue de la liste de données;numéro de la colonne àadditionner;étendue de la zone decritères)
Affiche le total d'un certain champ numérique selon les critères demandés.
Ex. : =bdsomme(A1:B6;2;D1:D2) = 60 C'est la somme des chiffres 15, 20 et 25.
=bdvar(étendue de la liste dedonnées;numéro de la colonne àtrouver la variance;étendue de lazone de critères) Affiche la variance d'enregistrements numériques qui répondent aux critères demandés.
Ex. : =bdvar(A1:B6;2;D1:D2) = 25
=bavarp(étendue de la liste dedonnées;numéro de la colonne àtrouver le nombre;étendue de la zonede critères)
Affiche la variance d'une population de valeurs d'une liste de données qui répond au critère demandé. Ex. : =bdvarp(A1:B6;2;D1:D2) =
16,666666667
Fonctions ayant un impact sur du texte
Excel offre aussi des fonctions pour chercher de l'information sur du texte. En voici quelques-unes.
représente un caractère tel qu'une lettre,
=car(chiffre qui représente un un chiffre ou un caractère réservé à
caractère) l'usage de l'ordinateur (touche Entrée, Affiche le caractère représenté en code Supprimer)
ASCII: valeur 1 à 255. Chaque valeur Ex. : =CAR(65) = A
Offert par le site 25
TEL : 00226 71 16 51 99
=nbcar(cellule ou texte) Affiche le nombre de caractères contenu dans une cellule.
Ex. : =nbcar(" abc 123 ") = 7 N'oubliez pas d'inclure l'espace entre le abc et le 123. C'est aussi un caractère pour Excel.
=nompropre(texte ou cellule ayant du
texte)
Affiche la première lettre de chaque mot en majuscule.
Ex. :=nompropre(" ceci est un test ") =
Ceci Est Un Test
=remplacer(texte initial;numéro ducaractère de départ;nombre decaractères;nouveau texte) Remplace une partie de texte par un autre à l'endroit de votre choix Ex. : =remplacer(" ceci est un test "; 13;4;TEST) = Ceci est un TEST
=rept(texte;nombre de répétitions) Répète une série de caractère le nombre de fois désiré. Ex. : =rept("abc";3) = abcabcabc
=stxt(texte initial;numéro ducaractère de départ; nombre decaractères à afficher) Affiche une partie du texte à partir de l'endroit de votre choix et pour le nombre de caractères choisi. Ex. : =stxt(" abc def ghi ";5;3) = def
=substitue(texte initial;texte àremplacer;par nouveau texte;numéroFonctions logiques
150 fonctions Excel expliquées
d'occurrence si plusieurs fois dans lemême texte)
Remplace le Xième occurrence d'un texte par un autre
Ex. :
=substitue(" 123123123 ";12; " AA ";2) =
123AA3123
=supprimespace(cellule ayant dutexte dont plusieurs espaces) Retire les espaces supplémentaires du contenu d'une cellule. Elle laisse seulement un espace entre les mots du texte et les retire à la fin de la cellule. Ex. : =supprimespace(" abc 123 ") = abc123
=t(chiffre)
Affiche le contenu seulement s'il s'agit de texte.
Ex. : =t(" abc ") = abc =t(123) = vide
=texte(chiffre;format texte) Converti un chiffre en format texte. Il prend la présentation comme le reste des cellules ayant du texte. Il est encore possible de l'utiliser pour les opérations mathématiques.
Ex.: =texte(45;0) = 45 mais collé à la bordure gauche de la cellule.
=trouve(texte recherché;texte) Trouve l'emplacement de départ d'un texte dans un autre.
Ex. : =trouve(" est "; " ceci est un test ")
= 6 ou le sixième caractère du texte
C'est parmi les fonctions les plus puissantes d'Excel. Elle donne de la flexibilité à votre modèle en agissant de manière différente selon les conditions rencontrées. Par exemple, est-ce qu'un employé a fait du temps supplémentaire ? Il faut donc ajuster le taux horaire pour ces heures supplémentaires. En utilisant des formules logiques, vous n'êtes pas obligé d'ajuster le modèle lorsque les conditions changent. Vous pouvez vous concentrer à analyser les résultats ce qui devrait être votre priorité. Essayez
Offert par le site TEL : 00226 71 16 51 99 33
