Tableur EXCEL/Formules

Début de la boite de navigation du chapitre

fin de la boite de navigation du chapitre

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.

Insertion de fonction

Construction d'une formule

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

1. Les valeurs numériques sont directement précisées Ex : =si(A6=1.5;B6*2;B6*0)
2. 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

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

Vous trouverez dans cette sous-partie, les principales fonctions que propose le tableur Excel, sinon les plus utilisées.

Les fonctions des dates et heures

Fonction	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 )	La fonction ANNEE de Microsoft Excel
AUJOURDHUI ()	Affiche la date d'aujourd'hui sans heures ni minutes ni secondes	La fonction AUJOURDHUI de Microsoft Excel
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	La fonction JOUR de Microsoft Excel
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	La fonction JOURS360 de Microsoft Excel
MAINTENANT ()	Affiche la date et l’heure en cours	La fonction MAINTENANT de Microsoft Excel
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	La fonction MOIS de Microsoft Excel

Les fonctions financières

Fonction	Explication	Exemple
AMORLIN (Coût; valeur résiduelle; durée)	Calcule l'amortissement linéaire d'un bien pour une période donnée	La fonction AMORLIN de Microsoft Excel
INTPER (taux; per; npm; va; vc; type)	Calcule le montant des intérêts d'un investissement	La fonction INTPER de Microsoft Excel
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.	La fonction VAN de Microsoft Excel

Les fonctions mathématiques et trigonométriques

Fonction	Explication	Exemple
ABS (Nombre)	Renvoie la valeur absolue d'un nombre	La fonction ABS de Microsoft Excel
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)	La fonction ALEA de Microsoft Excel
ARRONDI (Nombre; no_chiffre)	Arrondit un nombre au nombre de chiffres indiqué	La fonction ARRONDI de Microsoft Excel
ENT (Nombre)	Arrondit un nombre à l'entier directement inférieur	La fonction ENT de Microsoft Excel
NB.SI (plage; critère)	Renvoie le nombre de cellules non vides répondant à un critère à l'intérieur d'une plage	La fonction NB.SI de Microsoft Excel
NB.VIDE (plage)	Compte le nombre de cellules vides à l'intérieur d'une plage	La fonction NB.VIDE de Microsoft Excel
PRODUIT (nombre1; nombre2; ...)	Calcule le produit de nombres ou de plages de nombres (30 nombres maximum par plage)	La fonction PRODUIT de Microsoft Excel
PUISSANCE (nombre; puissance)	Renvoie la valeur du nombre élevé à la puissance demandée (carré, cube, ...)	La fonction PUISSANCE de Microsoft Excel
RACINE (nombre)	Affiche la racine carré d'un nombre	La fonction RACINE de Microsoft Excel
SOMME (nombre1; nombre2; ...)	Calcule la somme d'une suite de nombres	La fonction SOMME de Microsoft Excel
SOMME.SI (plage; critère; somme_plage)	Additionne des cellules spécifiées seulement si un certain critère est atteint	La fonction SOMME.SI de Microsoft Excel

Pour sommer en fonction de plusieurs colonnes, la fonction SOMME.SI.ENS() est apparue avec Excel 2007, pour être lisible sur les versions antérieures du logiciel mieux vaut donc préférer SOMMEPROD().

Les fonctions de texte

Fonction	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	La fonction CONCATENER de Microsoft Excel
MAJUSCULE (texte)	Convertit un texte en majuscule	La fonction MAJUSCULE de Microsoft Excel
MINUSCULE (texte)	Convertit un texte en minuscule	La fonction MINUSCULE de Microsoft Excel
NOMPROPRE (texte)	Convertit la première lettre d'un mot en majuscule	La fonction NOMPROPRE de Microsoft Excel

Les fonctions de recherche

Fonction	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é).	La fonction RECHERCHEV de Microsoft Excel
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.	La fonction RECHERCHEH de Microsoft Excel
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)	La fonction EQUIV de Microsoft Excel
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.	La fonction INDEX de Microsoft Excel

Les fonctions de logique

Fonction	Explication	Exemple
ET (valeur_logique1; valeur_logique2; ...)	Affiche "VRAI" si tous les arguments sont vrais	La fonction ET de Microsoft Excel
OU (valeur_logique1; valeur_logique2; ...)	Affiche "VRAI" si un des arguments est vrai	La fonction OU de Microsoft Excel
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)	La fonction SI de Microsoft Excel

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

Les avantages des noms

Il 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

Pour 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

Il 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

Parfois 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

Différents types d'erreur rencontrée dans une cellule

Une 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

Il 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).	Fonction Esterr
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).	Fonction Esterreur
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).	Fonction Estna
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).	Fonction Estvide
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).	Fonction Estref

Options de calcul

Les 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

Permet de recalculer instantanément, suite à chaque modification.

Automatique sauf dans les tables de données

Permet de recalculer instantanément, suite à chaque modification, sauf si celle-ci intervient dans une table de données.

Manuel

Permet 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

Permet de calculer lorsque l'option de calcul n’est pas positionnée sur "Automatique"

Calculer la feuille

Permet de calculer la feuille sur laquelle on est positionnée, lorsque l'option de calcul n’est pas positionnée sur "Automatique".