LES FONCTIONS
I. Fonctions statistiques
1. Fonction MOYENNE
Que permet-elle ?
Elle renvoie la moyenne d'une liste de valeurs. Tout le monde a déjà eu des moyennes à l'école
et connaît le principe. C'est cette fonction qui permet de faire ça.
Comment s'écrit-elle et quels paramètres ?
Comme pour les fonctions précédentes, elle prend au minimum un paramètre et peut en
prendre jusqu'à 255 paramètres.
=MOYENNE (plage1 ; nombre1 ; plage2;)
Elle est donc très simple d'utilisation. On va donc pouvoir se passer d'un exemple théorique
et faire directement un exemple pratique.
Un exemple concret, on va faire la moyenne des prix des voitures proposées par un garage.
2. Fonctions Excel MAX et MIN
Que permettent-elles ?
Ces fonctions permettent de renvoyer le maximum et le minimum d'une liste de nombres.
Comment s'écrivent-elles et quels paramètres ?
Ces fonctions prennent au moins un paramètre et ce nombre peut aller jusqu'à 255 plages de
cellule. On peut donc comparer un grand nombre de valeurs.
=MAX (plage1 ; plage2 ; nombre1 ; nombre2;)
=MIN (plage1 ; plage2 ; nombre1 ; nombre2;)
La fonction renvoie la plus petite valeur trouvée dans cette liste de valeurs.
Un exemple concret
On cherche dans notre exemple, le nombre maximum et minimum de chaque paramètre du
classement.
3. Fonction RANG
La fonction Rang est une fonction statistique très utile pour réaliser un classement. Le meilleur
exemple est le classement d'une liste d'élève en fonction de leurs moyennes.
Prenons ce tableau :
Dans cet exemple, nous allons insérer la fonction Rang dans la colonne de classement. Cette
fonction s'écrit de la manière suivante :
= Rang (Nombre ; Référence ; Ordre)
Le Nombre correspond à la cellule contenant le chiffre dont vous voulez connaître le rang.
La Référence correspond à la plage de cellule sur laquelle vous voulez faire la comparaison.
L'Ordre correspond à croissant (1) ou décroissant (0).
Ici, nous indiquerons la formule suivante :
= Rang (E2 ; $E$2 : $E$7 ;0) ou = Rang (E2 ; E2 : E7 ;0)
RANG Renvoie le classement d'une valeur dans un ensemble de valeur.
4. Fonction [Link]
La fonction [Link] renvoie la moyenne arithmétique des nombres qui répondent à
des critères donnés.
Cette fonction possède deux arguments obligatoires et un argument facultatif et sa structure
est la suivante :
=MOYENNE (Plage Critère ; Critère ; [Plage moyenne])
Où « Plage Critère » est la plage où on va confronter les valeurs avec le critère, et « Plage
moyenne » représente les valeurs dont on va calculer la moyenne, c’est un argument facultatif mais
il est très utile dans l’application.
Remarque :
• Si on n’introduit pas l’argument facultatif « Plage moyenne », le calcul de la moyenne se fait
sur les valeurs de « Plage critère »
• Si aucune valeur de « Plage critère » ne vérifie le critère, la fonction renvoie la formule
d’erreur #DIV/0 !
• L’argument critère peut être sous forme d’une valeur, une plage de donné, une référence…
• En calculant la moyenne, la fonction ignore les valeurs textes et les valeurs logiques inscrites
dans la plage moyenne.
• Si aucun terme de la plage moyenne ne peut être considéré dans le calcul, la fonction renvoie
la formule d’erreur #DIV/0 !
Il y a trois arguments pour cette fonction :
a) La plage à prendre en compte
b) La cellule qui renferme la valeur à rendre en compte
c) La plage qui renferme les valeurs pour faire la moyenne
5. La fonction [Link] d’Excel
La fonction [Link] renvoie la moyenne arithmétique des nombres qui répondent
à plusieurs critères donnés.
Cette fonction possède trois arguments obligatoires et plusieurs arguments facultatifs. Sa
structure est la suivante :
=[Link] (Plage moyenne ; Plage critère1 ; critère1 ; [Plage critère2] ; [critère2] ; …)
Où « Plage moyenne » représente les valeurs dont nous allons calculer la moyenne, « Plage
Critère1 » est la plage où nous allons confronter les valeurs avec le « critère1 », « Plage critère2 »,
« critère2 » … sont des arguments facultatifs. Ils présentent plusieurs critères facultatifs et leur plages
correspondantes.
Remarques :
• On peut introduire jusqu’à 127 plages de critères facultatifs.
• Afin d’introduire l’argument « Plage moyenne » plusieurs méthodes sont possibles : Plage
des cellules, nom d’un champ nommé, tableau.
• Si aucune valeur de l’une des plages critères ne vérifie le critère correspondant, la fonction
renvoie la formule d’erreur #DIV/0 !
• Les arguments « critère » peuvent être sous forme d’une valeur, une plage de données, une
référence, …
• En calculant la moyenne, la fonction ignore les valeurs texte et les valeurs logiques inscrites
dans la plage moyenne.
• Si aucun terme de la plage moyenne ne peut être considéré dans le calcul, la fonction renvoie
la formule d’erreur #DIV/0 !
6. Fonction [Link]
La fonction [Link] comptabilise le nombre de cellules dont la valeur correspond au critère
spécifié.
Sa structure est :
=[Link] (plage ; critère)
''plage'' représente la plage de cellules à comptabiliser.
''critère'' correspond au critère que la valeur de la cellule doit remplir pour être comptabilisée.
Dans cet exemple nous comptons le nombre de fois où nous rencontrons le mot Madrid.
7. Fonction NB. VIDE
La fonction NB. VIDE comptabilise le nombre de cellules dont le contenu est vide.
Sa structure est :
=[Link](plage)
Où ''plage'' représente la plage de cellules à comptabiliser.
II. Fonctions logiques
1. Fonction SI
La fonction SI permet d’afficher un résultat différent en fonction du contenu d’une autre
cellule.
La syntaxe utilisée est la suivante :
=SI (condition à évaluer ; valeur de la cellule si condition est vraie ; valeur si condition est fausse)
Le résultat de la formule peut être une valeur numérique, une formule de calcul ou du texte.
Dans les conditions à évaluer vous pouvez utiliser les opérateurs suivants :
• Egal =
• Supérieur à >
• Inférieur à <
• Supérieur ou égal à >=
• Inférieur ou égal à <=
Exemples :
a) Si l’élève a 18 ans ou plus, alors il est majeur, sinon il est mineur.
La formule sera :
=SI(B1>=18 ;« majeur » ; « mineur »)
B1 représente la cellule qui contient l’âge de l’élève.
Le texte se met entre guillemets
Que se passe-t-il si vous remplacez 19 par 17 ? par 18 ?
b) Si le montant de la facture est supérieur à 200 €, alors vous avez une remise de 3% sur le
montant de la facture, sinon vous n’avez pas de remise
La formule de calcul du montant de la remise sera :
=SI(B1>200 ; B1*3% ; O)
B1 représente la cellule qui contient le montant de la facture.
Que se passe-t-il si vous remplacez 300 par 100 ? par 200 ?
SI imbriqués
On peut imbriquer jusqu’à sept fonctions SI les unes dans les autres.
Exemple :
Calcul du prix d’entrée selon l’âge de la personne :
Age <= 8 ans 10€
Age entre 9 et 17 ans 15€
Age >= 18 ans 20€
La formule de calcul du prix sera la suivante :
=SI (B1<=8 ;10 ; SI(B1>=18 ;20 ;15))
ET et OU
La fonction SI peut être amélioré lorsqu'elle est utilisée avec d'autres fonctions telles que ET
et OU. La partie qui suit démontre le fonctionnement de ces deux fonctions.
La fonction ET se formate comme suit :
=ET (condition1 ; condition2 ; condition3 ...)
Il faut au moins deux conditions. Mais vous pouvez en ajouter d'autres. Toutes les conditions
doivent être remplies pour que la fonction affiche VRAI. Sinon, elle affichera FAUX.
La fonction OU quant à elle, se formate comme suit :
=OU (condition1 ; condition2 ; condition3 ...)
Il faut au moins deux conditions. Vous pouvez en ajouter d'autres. L'une des conditions doit
être remplie pour que la fonction affiche VRAI. Si aucune des conditions n'est remplie, elle affichera
FAUX.
2. Fonction ET
Les conditions posées dans le test de la fonction SI seront multiples et pour que la réponse
aux conditions soit "VRAI", il faudra qu’elles se vérifient toutes.
Voici sa syntaxe :
=SI (ET (Cond1 ; Cond2 ; … ; Condon) ; action à réaliser si les N conditions sont satisfaites ;
action à réaliser si au moins une des conditions n'est pas satisfaite)
Exemple :
On désire attribuer une prime de fin d'année de 1000€ aux coureurs qui remplissent les 2
conditions suivantes :
• Être top10
ET
• Avoir gagné 2 courses au moins.
Les coureurs sont répertoriés dans le tableau ci-dessous.
On crée une colonne prime et on saisit dans cette colonne la formule suivante :
=SI (ET (B2="top10 » ; C2>=2) ;2000 ;0)
3. Fonction OU
Les conditions posées seront multiples et pour que la réponse aux conditions soit "VRAI", il
faudra que l'une au moins se vérifie.
Voici sa syntaxe : =SI (OU (Cond1 ; Cond2) VRAI ; FAUX)
Exemple :
Un sponsor souhaite verser une prime de 1000 € à ses coureurs s'ils remplissent l'une OU
l'autre des conditions suivantes :
Avoir fait 3 podiums OU 1 victoire sur la saison.
On crée une colonne prime et on saisit dans cette colonne la formule suivante :
=SI(OU(B2>=3 ; C2>=1) ;1000 ;0)
III. Fonctions recherches
1. Fonctions Recherches V et H
a) La fonction RECHERCHEV, Que permet-elle ?
Comme on l'a vu un peu dans la description précédente, elle permet de rechercher une valeur
dans un tableau, plage de cellule ou matrice et de renvoyer une valeur associée. Elle cherche dans la
première colonne et renvoie une valeur d'une des autres colonnes sur la même ligne.
Comment s'écrit-elle et quels paramètres ?
Cette fonction prend plusieurs paramètres, trois obligatoires et un facultatif. Voici comment
elle s'écrit :
=RECHERCHEV (valeur_cherchée ; plage ; numero_colonne ; [valeur_proche])
La valeur cherchée peut être une valeur chiffrée, du texte (qui sera alors entre guillemets) ou
une cellule (et donc la valeur qu'elle contient). Elle doit être obligatoirement dans la première colonne
sinon la cellule contenant la fonction RECHERCHEV vous renvoie l'erreur suivante : #NOM ?
Ensuite on spécifie la plage dans laquelle on fait la recherche, soit en écrivant les coordonnées
des cellules (exemple : A1 : B16) soit en spécifiant le nom de la plage (exemple : ma_plage).
Enfin, le troisième paramètre concerne le numéro de la colonne dans laquelle la fonction doit
chercher la valeur à retourner. Il est donc inutile d'indiquer la première colonne puisque c'est dans
celle-ci que la recherche est faite.
En ce qui concerne le paramètre facultatif, il ne peut prendre que deux valeurs différentes :
VRAI ou FAUX. S'il n'est pas spécifié, il a pour valeur VRAI. Quand il vaut VRAI, la première
colonne doit être dans l'ordre croissant et la fonction recherche une valeur approximative. Quand il
vaut FAUX, la fonction cherche la valeur exacte. Si la fonction ne trouve pas la valeur exacte, elle
renvoie : #N/A.
Exemple
Un premier exemple où l'on recherche un nombre et renvoi un autre nombre. Dans cet
exemple, on a un barème où sont représentés des temps dans la colonne de gauche et la note
correspondante dans la seconde colonne. Ainsi dans le tableau de droite, on entre le temps de chaque
élève en face de son nom et la fonction se charge de trouver elle-même la note correspondante.
IV. Fonctions dates
1. Fonction AUJOUD'HUI
Cette fonction vous permet d'afficher automatiquement la date du jour extrait directement
depuis votre système, dans une cellule.
Vous pouvez également utiliser cette fonction avec d'autre formules de calcul sur les dates.
Voici sa syntaxe :
=AUJOURDHUI ()
Il n'y a rien à écrire entre les parenthèses.
2. Fonction [Link]
Elle renvoie le numéro de série du dernier jour du mois précédant ou suivant date_départ du
nombre de mois indiqué. Utilisez [Link] pour calculer des dates d’échéance ou des dates
d’échéance tombant le dernier jour du mois.
Syntaxe
[Link](date_départ ; mois)
La syntaxe de la fonction [Link] contient les arguments suivants :
• Date_début Obligatoire. Il s’agit d’une date qui représente la date de début. Les dates doivent
être entrées en utilisant la fonction DATE, ou sous la forme de résultats d’autres formules ou
fonctions. Par exemple, utilisez DATE (2008,5,23) pour le 23e jour du mois de mai 2008.
Certains problèmes peuvent survenir si les dates sont entrées sous forme de texte.
• Mois Obligatoire. 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.
Remarque : Si mois n’est pas un nombre entier, il est tronqué à sa partie entière.
Remarques
• Microsoft Excel enregistre les dates sous la forme de numéros séquentiels afin qu’elles
puissent être utilisées dans des calculs. Par défaut, le 1er janvier 1900 correspond au numéro
de série 1 et le 1er janvier 2008 correspond au numéro de série 39448 parce que 39 448 jours
se sont écoulés depuis le 1er janvier 1900.
• Si date_début n’est pas une date valide, fin renvoie la #NUM!.
• Si date_début plus mois donne une date non valide, fin renvoie la #NUM! #VALEUR!.
Exemple
Copiez les données d’exemple dans le tableau suivant, et collez-le dans la cellule A1 d’un
nouveau classeur Excel. Pour que les formules affichent des résultats, sélectionnez-les, appuyez sur
F2, et sur Entrée. Si nécessaire, vous pouvez modifier la largeur des colonnes pour afficher toutes les
données.
Date
1er janvier 2011
Formule Description Résultat
Date
Date du dernier jour du mois, décalée d’un
=[Link](A2 ;1) 28.02.11
mois suivant la date dans A2.
Date du dernier jour du mois, décalée de trois
=[Link](A2 ; -3) 31.10.10
mois avant la date dans A2.
On découvrira dans certains exercices d’autres fonctions dates.
V. Fonctions Texte
1. Fonction CONCATENER
Que permet-elle ?
Elle permet de mettre bout à bout des chaînes de caractère (du texte) pour n'en former qu'une.
Elle permet de présenter le texte de façon lisible pour l'utilisateur.
Comment s'écrit-elle et quels paramètres ?
Cette fonction prend autant d'arguments qu'il y a de chaînes de caractères à mettre bout à bout
(jusqu'à 255).
=CONCATENER (texte1 ; [texte2] ; ...)
Pour les chaînes qui comprennent du texte, il faut les mettre entre guillemets. Si on veut aussi
concaténer des cellules, alors les guillemets sont omis. Il y a un point-virgule (;) entre chaque chaîne
à concaténer.
=CONCATENER ("Le résultat de la somme est : «; B12)
La fonction nous permet de faire une phrase dans une seule cellule contenant le résultat d'une
opération qui changera si le résultat change.
Pour l'exemple, on va demander à l'utilisateur de remplir un petit tableau afin de lui raconter
une histoire.
On découvrira dans certains exercices d’autres fonctions textes.
VI. Fonctions Mathématiques
1. Fonction ARRONDI
Pour obtenir un nombre arrondi, on peut utiliser la fonction ARRONDI d’EXCEL.
Voici sa syntaxe :
=ARRONDI (nombre ; no_chiffres)
Nombre représente le nombre à arrondir.
No_chiffres spécifie le nombre de chiffres auquel vous voulez arrondir nombre.
Si no_chiffres est supérieur à 0 (zéro), nombre est arrondi au nombre de décimales indiqué.
Si no_chiffres est égal à 0, nombre est arrondi au nombre entier le plus proche.
Si no_chiffres est inférieur à 0, nombre est arrondi à gauche de la virgule.
2. Fonction ENT
La fonction ENT d'Excel permet d'extraire les entiers d'un nombre. À noter qu'Excel effectue
cette opération en arrondissant à l'entier inférieur le plus proche.
Sa structure est :
=ENT (nombre)
Où nombre est le nombre duquel on désire extraire les entiers.
Exemple :
Notons que dans le cas des nombres négatifs, le résultat n'est pas le même que si on avait
simplement retiré les décimales. Il faut donc additionner 1 au résultat pour obtenir le nombre sans les
décimales dans le cas des nombres négatifs.
La fonction ENT d'Excel permet aussi d'avoir uniquement la partie décimale d'un nombre avec
la structure suivante :
Pour les nombres positifs : =nombre - ENT (nombre)
Pour les nombres négatifs : = nombre - ENT (nombre)- 1
Exemple :
3. Fonction SOMME SI
La fonction SOMME SI permet d'additionner plusieurs cellules en fonction d'un critère.
Syntaxe :
=[Link] (plage ; critère ; somme_plage)
• Plage est la plage de cellule contenant le critère à retenir
• Critère est le critère à retenir (chiffre ou texte)
• Somme_plage est la plage de cellule où se trouvent les nombres à additionner.
Exemple :
Dans la colonne A on a des valeurs. En colonne B, on a indiqué "Foot" ou "Trail".
On voudrait obtenir le total des nombres de la colonne A auxquels correspondent le mot "Trail" en
colonne B.
En A7 écrivez la formule suivante :
=[Link] (B1 : B6 ;"trail » ; A1 : A6)
Le résultat donne : 66
4. Fonction SOMMEPROD
La fonction SOMMEPROD permet la réalisation d'opérations conditionnelles comptant et
additionnant en utilisant des conditions multiples. Les formules permettant de calculer le nombre de
valeurs ou les sommes sont fondées sur deux conditions ou plus.
Exemple 1 : Compter le nombre de fois ou Guillon à marquer des points en été.
C'est une formule conditionnelle qui emploie deux conditions.
=SOMMEPROD ((A1 : A12="Guillon ») *(B1 : B12="Eté"))
Le résultat donne : 3
On découvrira dans certains exercices d’autres fonctions textes.
VII. Tableau Croisé Dynamique
Ils ont 5 grand principes
• Définir quels seront les champs de calcul et d'étiquette
• Sélectionner la source de son tableau croisé dynamique
• Placer les champs à analyser
• Appliquer un style au tableau croisé dynamique
• Personnaliser un tableau croisé dynamique
Cliquez à l'intérieur du tableau et choisissez Insertion + Tableaux croisé dynamique. Cette
manipulation vous permet de préciser d'où viennent les données à analyser qui figureront dans le
tableau croisé dynamique.
Ces données sont automatiquement repérées dans la feuille de calcul par Excel. Mais il est
possible d'utiliser une source de données externes comme un autre classeur Excel ou une table Access.
Il est également possible de choisir à ce niveau l'emplacement du tableau croisé dynamique :
feuille de calcul existante ou nouvelle feuille de calcul.
Cliquez sur OK.
La prochaine étape montre l'outil de construction du tableau croisé dynamique :
À gauche (1) se trouve la zone de construction du tableau croisé dynamique.
À droite (2), se trouve la liste des champs pouvant figurer dans le tableau croisé dynamique.
Si l'on clique à l'extérieur de la zone 1, la zone 2 disparaît. Il suffit de recliquer dans la zone 1 pour
retrouver la zone 2.
La construction du tableau croisé dynamique consiste à insérer dans la partie gauche (1) des
champs choisis dans la partie droite (2).
On peut insérer les champs de trois manières :
• En cochant la case en regard de chaque champ ;
• En glissant à la souris un champ de la partie 2 vers une zone de la partie 1 ;
• En glissant à la souris un champ dans les zones situées en bas de la partie 2 : Filtre du
rapport. Étiquettes de colonnes, Étiquettes de lignes ou Valeurs.
VIII. LES SOUS-TATAUX
Des statistiques de vente de différents
produits ont été saisies dans Excel (à
gauche). Elles ont été triées selon le pays (à
droite).
La fonction sous-total d'Excel va
nous permettre de totaliser ces ventes suivant
des critères variés.
Note 1 : il est impératif de trier les
statistiques selon un champ avant d'appliquer
un sous-total.
Note 2 : si vous n'êtes pas familiarisé avec le maniement de listes sous Excel, je vous conseille
de consulter d'abord la page liste de données.
Appliquer un sous-total
Pour appliquer un sous-total, sélectionnez les statistiques en cliquant à l'intérieur du tableau,
puis, dans l'onglet Données, cliquez sur Sous-totaux, la boite de dialogue ci-contre apparaît alors.
Dans la zone À chaque changement de : choisissez le champ selon lequel les données seront
regroupées pour être totalisées. Ici, les statistiques seront calculées par pays.
Dans la zone Utiliser la fonction : sélectionnez la fonction mathématique souhaitée. Ici les
valeurs seront additionnées.
Dans la zone Ajouter un sous-total à : choisissez le champ auquel sera appliquée la fonction
mathématique sélectionnée précédemment. Ici, ce sont les chiffres d'affaires (CA) qui seront
additionnés.
Note : le bouton Supprimer tout permet d'effacer les sous-totaux présents dans une feuille.
Visualiser des sous-totaux
À leur création, les sous-totaux apparaissent tels qu'illustrés ci-contre à gauche, c'est à dire
avec le maximum de détails.
On observe le total général des ventes, ainsi que le total de la France et celui de l'Italie. Pour
chaque pays, figure une ligne de statistiques par date de vente et par produit.
Les boutons 1, 2 et 3 situés en haut à gauche de la feuille permettent de visualiser plus ou
moins de détails sur les statistiques présentées.
Les boutons + et - de la colonne de gauche permettent de développer un niveau de sous-total
pour l'afficher en détail, ou, au contraire, de ne montrer que son total général.
Les illustrations ci-dessous montrent ces différentes possibilités.
IX. Fonction de consolidation dans Excel
Pour synthétiser et reporter des résultats figurant dans des feuilles de calcul distinctes, vous
pouvez consolider les données de chaque feuille dans une feuille de calcul maître. Les feuilles de
calcul peuvent figurer dans le même classeur que la feuille maîtresse ou dans des classeurs différents.
Lorsque vous consolidez des données, vous les assemblez de manière à simplifier la mise à jour et
l’agrégation en fonction de vos besoins.
Nous ferons des exercices pour cette partie pour fixer les idées.
X. Les Macro
La macro est la faculté d'enregistrer la construction d'un tableau, d'une formule, d'une mise en
forme et de la restituer à l'infinie grâce à un lien ou un raccourci clavier.
Repère 2 : Accéder à l'onglet développeur. S’il n'est pas activé il vous faut aller dans Fichier-
->Options.
Repère 3 : Commande pour commencer l'enregistrement
Repère 4 : Personnalisé le nom de la macro (facultatif)
Repère 5 : Affecter un raccourci à cette macro (facultatif)
Repère 6 : Exécuter l'intégralité de votre tableau, calculs compris.
Repère 7 : Arrêter l'enregistrement de la macro
Pour tester vous appellerez la macro que vous avez enregistré via le menu "Macro" dans une
nouvelle feuille de votre classeur.