Tableur EXCEL/Formules
Cette leçon a pour but de présenter les fonctions du tableur Microsoft Excel.
Utiliser des formules en français dans un Excel anglais ou vice-versa est déconseillé, mieux vaut les traduire[1]. |
Insertion de fonction
modifierConstruction d'une formule
modifier- Toutes les lignes de formules sont préfixées par les symboles "=" , "+" ou "-".
En cas de saisie de valeur textuelle, ces symboles n'ont pas lieu d'être, on peut directement écrire la valeur.
Ce peut être une combinaison des éléments suivants :
- Des valeurs constantes, simples ou littérales numériques
- Les valeurs numériques sont directement précisées Ex : =si(A6=1.5;B6*2;B6*0)
- Les valeurs textuelles doivent être mises entre des guillemets Ex : =si(A6="toto";"oui";"non")
- Une référence à une valeur contenue dans une cellule ou une plage de cellule. Ainsi il faut juste indiquer l'adresse de la cellule ou la plage. Si Excel rencontre une référence dans une formule, alors il ira prendre la valeur se trouvant à l'endroit indiqué afin de l’utiliser dans les calculs. En cas de cellule vide cela produira la valeur zéro. S'il y a une modification de la valeur alors toutes les cellules qui l'utilisent verront leurs formules recalculées.
Il est possible de saisir les références au clavier , ou de faire un clic avec la souris lors de la saisie de la formule.
L'assistant fonction
modifier- Lorsque l’on débute l'édition d'une formule, le bouton assistant fonction apparaît et a une grande utilité (Il est tout à fait possible d'éditer une formule par la commande Insérer une Fonction de l'onglet Formules).
Cet assistant regroupe l’ensemble des fonctions de feuilles de calculs avec la description complète de chacune. Il va donc nous permettre de sélectionner une fonction et de la mettre dans une formule.
Lors de l'appel de l'assistant fonction, là une boite de dialogue s'affiche, elle représente la liste des fonctions. Il ne vous reste plus qu’à choisir la fonction que vous souhaitez mettre en œuvre. Toute fois, cette boite de dialogue contient deux listes : la liste déroulante des catégories (en haut) vous permet de choisir une catégorie dont les fonctions affiliées apparaissent dans la liste du dessous. Au sein de la liste déroulante du haut, la catégorie sélectionnée par défaut, à savoir "les dernières utilisées" fait apparaître dans la liste de droite les dernières fonctions entrées ou encore les plus courantes.
Sous les listes se présente la syntaxe et un court descriptif de la fonction sélectionnée. Lorsque vous avez choisi une fonction dans la liste des fonctions par un double clic, vous pouvez alors préciser l'un après l'autre les arguments nécessaires pour cette fonction. Lors de cette saisie, vous disposez à nouveau du bouton "fx" de manière à imbriquer des appels de fonction les uns dans les autres.
Différents types de fonctions
modifierVous trouverez dans cette sous-partie, les principales fonctions que propose le tableur Excel, sinon les plus utilisées.
Les fonctions des dates et heures
modifierFonction | Explication | Exemple |
---|---|---|
ANNEE
(numéro de série) |
Affiche l'année correspondante à une date ou un nombre (la première année de référence 01/01/1900 est représentée par le nombre 1 ) | |
AUJOURDHUI
() |
Affiche la date d'aujourd'hui sans heures ni minutes ni secondes | |
JOUR
(numéro de série) |
Affiche le jour correspondant à une date ou un nombre avec toujours la première année de référence 01/01/1900 | |
JOURS360
(date_début;date_fin;méthode) |
Affiche le nombre de jours entre deux dates (en comptant une année de 360 jours, soit 30 jours par mois), l'argument méthode de référence (américaine ou européenne) détermine la méthode de comptage | |
MAINTENANT
() |
Affiche la date et l’heure en cours | |
MOIS
(numéro de série) |
Affiche le mois correspondant à une date ou un nombre avec toujours la première année de référence 01/01/1900 |
Les fonctions financières
modifierFonction | Explication | Exemple |
---|---|---|
AMORLIN
(Coût; valeur résiduelle; durée) |
Calcule l'amortissement linéaire d'un bien pour une période donnée | |
INTPER
(taux; per; npm; va; vc; type) |
Calcule le montant des intérêts d'un investissement | |
VAN
(taux; valeur1; valeur2; ...) |
Calcule la valeur actuelle nette d'un investissement en utilisant un taux d'escompte ainsi qu'une série de décaissements (valeurs négatives) et d'encaissements (valeurs positives) futurs. |
Les fonctions mathématiques et trigonométriques
modifierFonction | Explication | Exemple |
---|---|---|
ABS
(Nombre) |
Renvoie la valeur absolue d'un nombre | |
ALEA
() |
Affiche un nombre décimal aléatoire compris entre entre 0 et 1, (depuis la version 2003 il existe une fonction bien plus précise et facile à utiliser la fonction ALEA.ENTRE.BORNES(n;m), par exemple ALEA.ENTRE.BORNES(1;100) renvie un nombre aléatoire compris entre 1 et 100 (variable) | |
ARRONDI
(Nombre; no_chiffre) |
Arrondit un nombre au nombre de chiffres indiqué | |
ENT
(Nombre) |
Arrondit un nombre à l'entier directement inférieur | |
NB.SI
(plage; critère) |
Renvoie le nombre de cellules non vides répondant à un critère à l'intérieur d'une plage | |
NB.VIDE
(plage) |
Compte le nombre de cellules vides à l'intérieur d'une plage | |
PRODUIT
(nombre1; nombre2; ...) |
Calcule le produit de nombres ou de plages de nombres (30 nombres maximum par plage) | |
PUISSANCE
(nombre; puissance) |
Renvoie la valeur du nombre élevé à la puissance demandée (carré, cube, ...) | |
RACINE
(nombre) |
Affiche la racine carré d'un nombre | |
SOMME
(nombre1; nombre2; ...) |
Calcule la somme d'une suite de nombres | |
SOMME.SI
(plage; critère; somme_plage) |
Additionne des cellules spécifiées seulement si un certain critère est atteint |
Pour sommer en fonction de plusieurs colonnes, la fonction SOMME.SI.ENS() est apparue avec Excel 2007[2], pour être lisible sur les versions antérieures du logiciel mieux vaut donc préférer SOMMEPROD() [3].
|
Les fonctions de texte
modifierFonction | Explication | Exemple |
---|---|---|
CONCATENER
(texte1;texte2;...) |
Assemble une liste de chaines de caractères pour n'en former une seule par concaténation (les chaines de caractères sont mises bout à bout), cette fonction réalise la même opération que l'opérateur de caractères &, elle est parfois appelée addition de chaines de caractères | |
MAJUSCULE
(texte) |
Convertit un texte en majuscule | |
MINUSCULE
(texte) |
Convertit un texte en minuscule | |
NOMPROPRE
(texte) |
Convertit la première lettre d'un mot en majuscule |
Les fonctions de recherche
modifierFonction | Explication | Exemple |
---|---|---|
RECHERCHEV
(valeur;table;index;proche) |
La fonction RECHERCHEV permet d'afficher une valeur de la même ligne qu'une valeur_cherchée. La valeur_cherchée se trouve dans une colonne de référence. Cette colonne de référence est la première colonne de la table_matrice. Le no_index_col est le numéro de colonne dont on veut afficher le contenu, la colone de référence étant la colonne numéro 1. La [valeur_proche] correspond à la précision : VRAI si l’on veut une valeur proche, FAUX si l’on veut une correspondance exacte (FAUX automatiquement si le champ est non renseigné). | |
RECHERCHEH
(valeur;table;index;proche) |
La fonction RECHERCHEH permet d'afficher une valeur de la même colonne qu'une valeur_cherchée, le tableau correspond à la table de données dans laquelle on effectue notre recherche, le no_index_lig est le numéro de ligne (par rapport à la première ligne du tableau) dont on veut afficher le contenu. | |
EQUIV
(valeur_cherchee; tableau_recherche;(type)) |
La fonction EQUIV renvoie la position d'une valeur_cherchee dans un tableau_recherche (un vecteur). Le type de recherche peut être égal à 0 (position de la première valeur exacte rencontrée), 1 (position de la dernière valeur inférieure ou égale à la valeur recherchée) ou -1 (position de la dernière valeur supérieure ou égale à la valeur recherchée)[4] | |
INDEX
(matrice; no_lig; [no_col]) |
La fonction INDEX permet de retrouver une valeur dans une matrice en fonction de sa position au sein de cette dernière. Si on effectue cette recherche dans un vecteur, il faudra indiquer le numéro de ligne ou de colonne de la valeur recherchée. Si on recherche dans un tableau à deux dimensions, il faudra à la fois indiquer le numéro de ligne et le numéro de colonne[5]. |
Les fonctions de logique
modifierFonction | Explication | Exemple |
---|---|---|
ET
(valeur_logique1; valeur_logique2; ...) |
Affiche "VRAI" si tous les arguments sont vrais | |
OU
(valeur_logique1; valeur_logique2; ...) |
Affiche "VRAI" si un des arguments est vrai | |
SI
(test_logique; valeur_si_vrai; valeur_si_faux) |
Affiche valeur_si_vrai si le test_logique est VRAI, affiche valeur_si_faux dans le cas contraire (si les champs valeur_si_vrai et valeur_si_faux ne sont pas renseignés, VRAI et FAUX s'affichent dans leur cas respectifs) |
Il existe de nombreuses autres fonctions proposées par le tableur Excel, vous pourrez les retrouver par le biais d'un simple clic sur l'icone fx insérer une fonction.
Gestion des noms
modifierLes avantages des noms
modifierIl peut être utile de nommer certaines cellule ou plages de cellules participant à des calculs ou formules plutôt que de garder les appellations standards lettrées en colonnes et numérotées en ligne (par exemple A3:B6). Cette technique apporte plusieurs avantages :
- Clarifier la formule. Exemple : en relecture la formule " Prix_HT*(1+TVA)" qui correspond à un calcul du prix TTC est plus compréhensible que la formule "A6*(1+C14)"
- Insérer de manière élégante le symbole "$" qui correspond au références fixes et relatives, un nom de cellule est traduit en une référence fixe, un nom de plage est une référence relative
- Saisir en semi-automatique des noms dans une formule
- Maintenir des formules est simplifiée par le Gestionnaire de noms
Règles pour créer un nom
modifierPour créer un nom il faut respecter 4 conditions :
- Le nom doit être composé de moins 255 caractères
- Le nom doit commencer soit par une lettre soit par le caractère soulignement " _"
- Le nom ne doit pas contenir d'espaces ni de signes de ponctuations
- Le nom ne doit pas ressembler à une adresse (A1, C6, L, T, etc.)
Créer un nom
modifierIl existe plusieurs façons de créer un nom sous Excel :
- Sélectionner la cellule ou la plage et saisir le nom de la "zone de nom" ( au dessus de l'intersection des entêtes de lignes et de colonnes)
- Sélectionner la cellule ou la plage et effectuer un clic-droit et choisisser l'option "Définir un nom"
- Sélectionner la cellule ou la plage et cliquer sur l'onglet formule du ruban puis sur "Définir un nom"
- Sélectionner la cellule ou la plage et aller dans l'onglet formule et cliquer sur le "gestionnaire de nom" puis nouveau
Une fois le nom créé les modifications se feront via la "gestionnaire de nom" dans l'onglet "Formules" du ruban.
Repérage des antécédents et des dépendants
modifierParfois il est difficile de comprendre exactement, de détecter la source d'une erreur quand la formule utilise des cellules antécédentes ou dépendantes : Les cellules antécédentes sont désignées par une formule dans une autre cellule. Par exemple, si la cellule A8 contient la formule =C2, la cellule C2 est antécédente à la cellule A8. Les cellules dépendantes contiennent elles des formules qui ont pour référence d’autres cellules. Par exemple, si la cellule C2 contient la formule =A8, la cellule C2 est dépendante de la cellule A8.
Afin de procurer une aide pour la vérification de vos formules, il est possible d’utiliser les fonctions "Repérer les antécédent" et "Repérer les dépendants", qui ont pour utilité, premièrement, la représentation graphique et, ensuite, de pouvoir retracer les relations entre les formules et les cellules, grâce à des flèches d’audit de couleurs.
1. Cliquer sur le bouton "menu" en haut à gauche, puis sur "Options Excel", et enfin sur la catégorie "Options avancées".
2. Dans la partie "Afficher les options pour ce classeur", sélectionner le classeur utilisé, puis contrôler si l’option "Tous" est activée en dessous "Pour des objets, afficher".
3. Si des formules ont pour référence des cellules d’un autre classeur, il faut ouvrir cet autre classeur (Excel ne peut pas accéder à des cellules d’un classeur qui n’est pas ouvert, il ne peut pas aller chercher les informations nécessaires)
4. Les deux actions possibles sont donc :
- - Pour repérer les cellules dont les données sont utilisées dans une formule (antécédents) :
1. Cliquer sur la cellule contenant la formule pour laquelle il faut retrouver les antécédents.
2. Pour qu'une flèche d’audit s'affiche pour chaque cellule ce qui permettra de fournir instantanément des données à la cellule active, sous l’onglet "Formules", dans le groupe "Audit de formules", sélectionner "Repérer les antécédents"
Les flèches de couleurs bleues montrent les cellules qui ne contiennent aucune erreur, alors que les flèches rouges montrent celles qui comportent des erreurs. Si la cellule sélectionnée est référencée dans un autre classeur ou par une cellule d’une autre feuille de calcul, une flèche noire s'affichera alors pointant de la cellule sélectionnée vers une icône de feuille de calcul "Icône de feuille de calcul". Par ailleurs, l’autre classeur se doit d’être ouvert lui aussi pour qu’Excel puisse faire le lien et déceler ces dépendances.
- - Pour repérer les formules qui font référence à une cellule particulière (dépendants) :
1. Cliquer sur la cellule contenant la formule pour laquelle il faut identifier les dépendants.
2. Pour qu'une flèche d’audit s'affiche pour chaque cellule dépendant de la cellule active, sous l’onglet "Formules" du groupe "Audit de formules", cliquer sur "Repérer les dépendants"
Les flèches de couleurs bleues montrent les cellules qui ne contiennent aucune erreurs, alors que les flèches rouges montrent celles qui comportent des erreurs. Si la cellule sélectionnée est référencée dans un autre classeur ou par une cellule d’une autre feuille de calcul, une flèche noire s'affichera alors pointant de la cellule sélectionnée vers une icône de feuille de calcul "Icône de feuille de calcul". Par ailleurs, l’autre classeur se doit d’être ouvert lui aussi pour qu’Excel puisse faire le lien et déceler ces dépendances.
- - Pour afficher toutes les relations dans une feuille de calcul :
1. Dans une cellule vide, taper =(signe égal).
2. Ensuite cliquer sur le bouton "Sélectionner tout" en haut à gauche de la feuille de calcul.
3. Cliquer sur la cellule, ensuite dans l’onglet "Formules", dans le groupe "Audit de formules", double-cliquer sur "Repérer les antécédents".
Pour terminer, s'il faut supprimer l’ensemble des flèches d’audit de la feuille de calcul, sous l’onglet "Formules", dans le groupe "Audit de formules", cliquer sur "Supprimer les flèches".
Traitement des erreurs
modifierDifférents types d'erreur rencontrée dans une cellule
modifierUne formule peut parfois renvoyer des types d'erreurs qui sont caractérisées par 7 désignations :
Erreur | Explication | Exemples |
---|---|---|
#DIV/0! | La formule effectue une erreur de division par zéro (le numérateur est nul) | =265000/(10-10) |
#NA | Une formule de recherche a échoué car une des valeurs obligatoires pour trouver un résultat est manquante, ou il existe trop de résultat que n'en peut utiliser une fonction | =SOMMEPROD((A1:A10="dvp")*(B1:B9="number one")) |
#NOM? | La fonction contient un mot incompréhensible par EXCEL (en général un nom de plage ou un nom de formule mal orthographié) | =SOME(A1:B2000) |
#NOMBRE! | La formule fait référence à une valeur numérique non valide (en général une donnée textuelle) | =MIN("titi";"toto") |
#NUL! | Il existe un plage de cellule qui retourne une zone vide ou une intersection de deux zones qui, en réalité, ne se coupent pas | =SOMME(A1 A10) |
#REF! | La formule renvoie une adresse incorrecte, elle a certainement été créée avec une référence correcte, mais vers une cellule qui a été supprimée ou qui n'est plus accessible | ='C:\dossier\[NomDuClasseurNonTrouvé.xls]NomDeLaFeuilleInconnue'!$A$1) |
#VALEUR! | Un opérateur ou un argument au sein de la formule est utilisé avec des valeurs inappropriées | =SOMME(10;a;titi;TRUE) |
Quand une cellule affiche un type d'erreur, toutes les cellules qui utilisent son résultat affichent en cascade le même code d'erreur.
Différentes formules de gestion d'erreur
modifierIl existe plusieurs formules de gestion d'erreur qui permettent d’éviter la propagation en cascade en testant l'erreur (et parfois en la corrigeant grâce à la fonction =SI) :
Formule | Explication | Exemples |
---|---|---|
ESTERR() | Renvoie la valeur "VRAI" si la cellule contient une erreur (différente cependant de l'erreur "#N/A") sinon renvoie la valeur "FAUX". L'erreur peut être testée et corrigée à l'aide de la fonction =SI de la manière suivante : =SI(ESTERR(A1);valeur de remplacement;A1). | |
ESTERREUR() | Renvoie la valeur "VRAI" si la cellule contient l'indication de n’importe quel type d'erreur sinon renvoie la valeur "FAUX". L'erreur peut être testée et corrigée à l'aide de la fonction =SI de la manière suivante : =SI(ESTERREUR(A1);valeur de remplacement;A1). | |
ESTNA() | Renvoie la valeur "VRAI" si la cellule contient l'indication de l'erreur "#N/A" sinon renvoie la valeur "FAUX". L'erreur peut être testée et corrigée à l'aide de la fonction =SI de la manière suivante : =SI(ESTNA(A1);valeur de remplacement;A1). | |
ESTVIDE() | Renvoie la valeur "VRAI" si la cellule testée est vide sinon renvoie la valeur "FAUX". L'erreur peut être testée et corrigée à l'aide de la fonction =SI de la manière suivante : =SI(ESTVIDE(A1);valeur de remplacement;A1). | |
ESTREF() | Renvoie la valeur "VRAI" si la cellule contient l'indication de l'erreur #REF! (adresse inconnue ou inaccessible) sinon renvoie la valeur "FAUX". L'erreur peut être testée et corrigée à l'aide de la fonction =SI de la manière suivante : =SI(ESTREF(A1);valeur de remplacement;A1). |
Options de calcul
modifierLes options de calcul permettent de choisir à quel moment le logiciel calcule. Les commandes d'options de calcul se trouvent dans l'onglet "Formules", dans le groupe "Calcul".
Automatique
modifierPermet de recalculer instantanément, suite à chaque modification.
Automatique sauf dans les tables de données
modifierPermet de recalculer instantanément, suite à chaque modification, sauf si celle-ci intervient dans une table de données.
Manuel
modifierPermet de faire des modifications sans que le logiciel ne calcule constamment. Il est possible de demander au logiciel de calculer à chaque fermeture du fichier, afin de disposer des bons chiffres la fois suivante. Pour ceci, il faut cocher "Recalculer le classeur avant de l'enregistrer" qui l’on peut trouver par le chemin Fichier/Options/Formules/Mode de calcul
Dans ce dernier cas, il faut donc indiquer à Excel lorsque l’on souhaite que les calcules se fassent. On dispose donc des 2 boutons suivants :
Calculer maintenant
modifierPermet de calculer lorsque l'option de calcul n’est pas positionnée sur "Automatique"
Calculer la feuille
modifierPermet de calculer la feuille sur laquelle on est positionnée, lorsque l'option de calcul n’est pas positionnée sur "Automatique".
Références
modifier- ↑ http://www.piuha.fi/excel-function-name-translation/index.php?page=francais-english.html
- ↑ http://office.microsoft.com/fr-fr/excel-help/somme-si-ens-fonction-HA010047504.aspx
- ↑ http://office.microsoft.com/fr-fr/excel-help/fonction-sommeprod-HP010062466.aspx
- ↑ http://www.finance3point1.com/2013/09/16/serie-indexequiv-la-fonction-equiv-dexcel/
- ↑ http://www.finance3point1.com/2013/09/25/serie-indexequiv-la-fonction-index-dexcel/