Maitrise du pack Office (Excel)
TP 2 : Insertion des fonctions
L’onglet Formules d’Excel, illustré à la figure suivante, donne accès à une bibliothèque de
formules et de fonctions. Sous cet onglet, vous pouvez utiliser des commandes pour insérer
rapidement des fonctions, calculer des sommes totales et afficher une carte visuelle des cellules
qui dépendent d’une formule.
Exemple 1 : Reproduire ce tableau et insérer les additions dans les cellules bleues. Les sommes
seront au format "MONETAIRE" avec des dollars.
Exemple 2 : Compléter le tableau en calculant les totaux par atelier et le solde de production
par mois.
Exemple 3 : Remplir les cellules bleues.
Exemple 4 : Les cellules C12-F13 sont à calculer en faisant le produit du nombre de formules
vendues et du prix de chaque formule.
ERROUSSO Hanae
Maitrise du pack Office (Excel)
N.B. Pour figer la référence à une ligne (lors de la recopie vers le bas) ou à une colonne (lors
de la recopie vers la droite), il faut insérer le caractère « $ » devant le numéro de la ligne ou la
lettre de la colonne. Pour figer la référence à la fois à une ligne et à une colonne (lors de la
recopie vers le bas et vers la droite), il faut insérer le caractère « $ » devant le numéro de la
ligne et de la colonne.
1. Fonctions de dates
Sous Excel, les dates et heures sont codées sous la forme de numéros de série. Les numéros de
série sont des valeurs numériques contenant le nombre de jours et fraction de jour écoulés
depuis une date de référence. Excel laisse le choix entre deux dates de référence possibles : le
1er janvier 1900 ou le 1er janvier1904. Le choix de la date de référence peut être effectué dans
les options d'Excel.
Les numéros de série comportent une partie entière et une partie décimale. La partie entière
correspond au nombre de jours entièrement révolus depuis le 01/01/1900. Le nombre à droite
de la virgule représente la fraction de jour restante. Par exemple, le numéro de série 12 200,50
peut se décomposer de la manière suivante :
• Nombre de jours entièrement écoulés depuis le 01/01/1900 à minuit : 12 200
• Fraction de jour restante : 0,50 soit 50% de 24 heures (50% * 24 = 12 heures). La partie
fractionnaire est précise au moins jusqu'à la seconde.
Les principales fonctions de date et heure sont :
• DATE : Renvoie le numéro de série d’une date précise.
• DATEDIF : Calcule le nombre de jours, de mois ou d’années qui séparent deux dates.
Cette fonction est utile dans les formules où vous devez calculer un âge.
• JOUR : Convertit un numéro de série en jour du mois.
• JOURS : Renvoie le nombre de jours entre deux dates.
• HEURE : Convertit un numéro de série en heure.
ERROUSSO Hanae
Maitrise du pack Office (Excel)
• ANNEE : Convertit un numéro de série en année.
• MINUTE : Convertit un numéro de série en minute.
• MOIS : Convertit un numéro de série en mois.
• MAINTENANT : Renvoie le numéro de série de la date et de l’heure du jour.
• SECONDE : Convertit un numéro de série en seconde.
• AUJOURDHUI : Renvoie le numéro de série de la date du jour.
• JOURSEM : Convertit un numéro de série en jour de la semaine.
• NO.SEMAINE : Convertit un numéro de série en un numéro représentant l’ordre de la
semaine dans l’année.
• JOURS360 : Calcule le nombre de jours qui séparent deux dates sur la base d’une année
de 360 jours.
• MOIS.DECALER : Renvoie le numéro de série de la date qui représente une date
spécifiée (l’argument date_départ), corrigée en plus ou en moins du nombre de mois
indiqué.
• FIN.MOIS : Renvoie le numéro de série de la date du dernier jour du mois précédant ou
suivant la date_départ du nombre de mois indiqué.
• SERIE.JOUR.OUVRE : Renvoie le numéro de série de la date avant ou après le nombre
de jours ouvrés spécifiés.
• SERIE.JOUR.OUVRE.INTL : Renvoie le numéro de série de la date avant ou après un
nombre spécifié de jours ouvrés en spécifiant des paramètres qui identifient et
dénombrent les jours inclus dans le week-end.
• TEMPS : Renvoie le numéro de série d’une heure précise.
• TEMPSVAL : Convertit une date représentée sous forme de texte en numéro de série.
• FRACTION.ANNEE : Renvoie la fraction de l’année représentant le nombre de jours
entiers séparant la date de début et la date de fin.
• DATEVAL : Convertit une date représentée sous forme de texte en numéro de série.
• NB.JOURS.OUVRES : Renvoie le nombre de jours ouvrés entiers compris entre deux
dates.
• NB.JOURS.OUVRES.INTL : Renvoie le nombre de jours ouvrés entiers compris entre
deux dates à l’aide de paramètres identifiant les jours du week-end et leur nombre.
• NO.SEMAINE.ISO : Renvoie le numéro de la semaine ISO de l’année pour une date
donnée.
Exemple 1 : Soit une feuille de calcul où les valeurs relatives à une date sont écrites en 3
colonnes, la colonne A pour l'année, B pour le mois et C pour le jour. Nous voulons avoir la
date "complète" à la colonne D. La formule à écrire en D2 est : =DATE(A2;B2;C2).
ERROUSSO Hanae
Maitrise du pack Office (Excel)
Notez que lorsque le jour est supérieur à 31 ou le mois est supérieur à 12, la fonction DATE ne
renvoie pas d'erreur. Au contraire, l'excédent se transforme en mois et en années.
De même, lorsque la seconde ou la minute est supérieure à 60, la fonction TIME ne renvoie pas
d'erreur. Au contraire, le surplus se transforme en minutes et en heures.
Exemple 2 : Soit une feuille de calcul où la date d'accomplissement de deux étapes d'un
processus est enregistrée. Nous voulons calculer la durée entre les deux étapes. La formule à
écrire en C2 est : =JOURS(B2;A2).
Exemple 3 : Les dates en Excel sont représentées par des numéros de série et que la différence
entre les numéros de série de deux jours successifs est 1. Donc, pour ajouter un nombre de jours
à une date, il suffit d'ajouter ce nombre à la date.
Par exemple, la valeur en cellule A2 est une date, et nous voulons avoir en B2 la date une
semaine plus tard. En cellule B2, il faut écrire la formule : =A2+7.
N.B. La date en A2 peut être affichée sous n'importe quel format, mais, doit être reconnu par
Excel comme une date.
Dans l'extrait de feuille de calcul suivant, la date de promotion est déterminée, soit 2,5 ans après
la date de recrutement. La formule en cellule C2 est : =MOIS.DECALER(B2;30).
N.B. Pour ajouter un nombre de mois à une date, il ne suffit pas d'ajouter ce nombre multiplié
par 30. La raison est que les mois ne sont pas tous 30 jours.
Exemple 4 : Calculer l’écart en jours, mois et années entre les deux dates suivantes :
14/02/2019 et 05/06/2021.
ERROUSSO Hanae
Maitrise du pack Office (Excel)
Commençons par la méthode la plus simple pour calculer la différence, l’écart ou le nombre de
jour entre deux dates :
• Chaque date dans Excel correspondant à un numéro de série, il est facile de faire
ressortir l’écart par une simple soustraction de ce type : ECART = DATE FINALE –
DATE INITIALE.
• L’écart entre deux dates en nombre de mois est calculé à l’aide de la même soustraction
divisée par 30,5, qui est le nombre moyen de jours par mois : ECART = (DATE
FINALE – DATE INITIALE) / 30,5.
• L’écart entre deux dates en nombre d’années est déterminité en suivant le même
principe : ECART = (DATE FINALE – DATE INITIALE) / 365.
N.B. Dans tous les cas, le format de cellule du résultat doit être en Standard ou Nombre, sinon
c’est une date sans aucun sens qui va s’afficher.
Il existe une autre manière de calculer un écart entre deux dates sur Excel, celle qui consiste à
utiliser la formule DATEDIF. Celle-ci permet de déterminer l’écart entre deux dates de la
manière suivante : =DATEDIF(<date initiale>;<date finale>;“D”). Ici, la lettre “D” vaut pour
day (jour), le résultat sera donc un nombre de jours. Elle peut être remplacée par “M” ou “Y”
pour un écart en mois ou en années.
N.B. la formule DATEDIF ne permet d’obtenir que des années ou des mois complets, sans
chiffre après la virgule : cette imprécision peut poser problème dans certains cas.
Exemple 5 : Pour classer des dates par ordre chronologique dans Excel, il conviendra d’utiliser
la formule PETITE.VALEUR. Cette dernière permet de trouver la plus petite valeur dans un
ensemble de données, mais aussi la 2ème plus petite, la 3ème plus petite, la 4ème plus petite, etc.
Elle se construit comme suit : PETITE.VALEUR(<matrice>;1). Le chiffre 1 permet de trouver
la plus petite valeur de l’ensemble, le chiffre 2 permettrait de trouver la deuxième plus petite,
etc.
Il est donc possible de trier facilement les dates dans l'ordre, et, si nécessaire, de répéter les
données correspondant aux dates en utilisant la formule RECHERCHEV(<valeur cherchée>;
<tableau>;<colonne retournée>;<type>).
ERROUSSO Hanae
Maitrise du pack Office (Excel)
2. Fonctions logiques
La fonction SI sert à vérifier une condition et à faire un premier calcul si c’est vrai et un autre
calcul si c’est faux.
La fonction SI comporte donc 3 parties :
• Une condition, qui peut être calculée à partir d'autres cellules et donne un résultat
binaire. Exemples : A1 > 10, B1 = C1…
• Un premier calcul au cas où la condition est vraie,
• Un autre calcul si la condition a donné la valeur : faux.
Syntaxe : SI(<condition>;<calcul si vrai>;<calcul si faux>)
Exemple : L’objectif est de vérifier si la taille saisie en cellule B2 est supérieure à 1,90 mètres
afin d’afficher si c’est vrai “géant” en colonne C ou si c’est faux d’afficher “normal.
Pour ce faire, il faut saisir dans la cellule C2 : =SI(B2>1,90; “géant”; “normal”).
Lorsque plusieurs conditions doivent être vérifiées, la fonction SI pourra inclure comme
expression si vrai ou expression si faux un calcul utilisant lui-même la fonction SI.
Exemple : Le but est d’afficher “senior” si l’âge saisi en cellule B2 est supérieur à 62 ans, ou
bien “adulte” si l’âge est cependant supérieur à 18 ans sinon si c’est faux “enfant” est affiché.
Pour y parvenir, il faut saisir dans la cellule C2 :
=SI(B2>62;“senior”;SI(B2>18;“adulte”;“enfant”))
ERROUSSO Hanae
Maitrise du pack Office (Excel)
La fonction SI est généralement combinée avec d’autres fonctions comme la fonction ET et
OU. La fonction Excel ET vérifie si tous les tests sont VRAI et renvoie FAUX si un ou plusieurs
tests sont FAUX.
Syntaxe : =SI(ET(<Cond1>;<Cond2>;<...>;<CondN>);<VRAI>;<FAUX>)
Exemple : Une prime de fin d'année de 2000€ est attribuée aux coureurs qui remplissent les 2
conditions suivantes :
• Être top10 ;
• Avoir gagné 2 courses au moins.
Les coureurs sont répertoriés dans le tableau ci-dessous.
La formule suivante est saisie dans la colonne prime créée:
=SI(ET(B2="top10";C2>=2);2000;0)
La fonction Excel OU vérifie si au moins une des conditions est VRAI, elle renvoie FAUX
uniquement si toutes les conditions sont FAUX.
Syntaxe : =SI(OU(<Cond1>;<Cond2>;<…> ;<CondN>);<VRAI>;<FAUX>)
Exemple : Un sponsor souhaite verser une prime de 1000€ à ses coureurs s'ils remplissent l'une
des conditions suivantes : Avoir fait 3 podiums ou 1 victoire sur la saison.
ERROUSSO Hanae
Maitrise du pack Office (Excel)
Dans la colonne prime créée, la formule =SI(OU(B2>=3;C2>=1);1000;0) est saisie.
3. Statistiques
Le logiciel Excel est connu pour être un outil extrêmement complet dans l’analyse de données
ou les statistiques. Outre les fonctions statistiques de base SOMME, MOYENNE, MIN MAX,
ECARTYPE, MEDIANE et VARPA, il existe des fonctions plus ou moins avancées telles que
NB.SI, NB.SI.ENS, SOMME.SI et SOMME.SI.ENS.
La fonction NB.SI retourne le nombre de cellules qui vérifient une condition. Elle permet de
compter le nombre de cellules qui répondent à un critère ; par exemple, pour compter le nombre
de fois où le nom d’une ville apparaît dans une liste de clients.
Syntaxe : =NB.SI(<plage>;<critère>)
Exemple : Voici un tableau de ventes réalisées par différents vendeurs. Nous voulons compter
le nombre de ventes réalisées par le vendeur Sergio.
La formule à utiliser pour déterminer la valeur demandée s'écrit comme suit :
=NB.SI(A2:A14;"Sergio")
La fonction NB.SI indique que 3 cellules dans la plage de cellules A2:A14 qui contiennent le
nom Sergio.
ERROUSSO Hanae
Maitrise du pack Office (Excel)
La fonction NB.SI.ENS compte le nombre de cellules de la même manière que la fonction
NB.SI sauf qu'avec NB.SI.ENS, il y a possibilité de spécifier plusieurs conditions.
Syntaxe : =NB.SI.ENS(<plage_critère1>;<critères1>;[plage_critères2; critères2]…)
Exemple : Nous souhaitons connaître le nombre d'hommes célibataires.
Le résultat est obtenu en précisant les plages de cellules correspondant aux critères de la
situation maritale et du genre, d’où la formule ci-après :
=NB.SI.ENS(C2:C20;"Homme";E2:E20;"Célibataire")
La fonction SOMME.SI retourne la somme de valeurs de cellules qui vérifient une condition.
Syntaxe : =SOMME.SI(<plage>;<critère>;<plage à sommer>)
Exemple : Nous cherchons à calculer le total des montants impayés.
ERROUSSO Hanae
Maitrise du pack Office (Excel)
Pour différencier les montants payés et impayés, il faudra vérifier si la date de paiement est
renseignée ou non. Il faut donc sommer tous les montants dont la date de paiement est vide :
= SOMME.SI(D2:D8;"";C2:C8)
Pour obtenir le total des montants payés, vous pouvez utiliser la même formule en remplaçant
le critère "" (= vide) par le critère inverse "<>" (= non vide).
La fonction SOMME.SI.ENS calcule la somme de valeurs de cellules de la même manière que
la fonction SOMME.SI, sauf qu'avec la fonction SOMME.SI.ENS, il y a possibilité de spécifier
plusieurs conditions.
Syntaxe : =SOMME.SI.ENS(<somme_plage>;<plage_crit1>;<crit1>;[plage_crit2, crit2], ...)
Exemple : Le tableau montre les ventes par villes de produits A, B et C.
Pour la première somme, nous saisirons comme formule :
=SOMME.SI.ENS(D2:D10;B2:B10;"Produit A";C2:C10;"Janvier")
Et pour la seconde somme :
=SOMME.SI.ENS(D2:D10;A2:A10;"REIMS";C2:C10;"Février")
4. Exercices
Exercice 1 :
Après avoir recopié le tableau, remplissez les cellules vides.
ERROUSSO Hanae
Maitrise du pack Office (Excel)
Exercice 2 :
Soit l’extrait de feuille de calcul suivant où est inscrit en colonne C des "Dates de naissance".
Utilisez la technique de mise en forme conditionnelle de Excel pour montrer les personnes âgées
de plus de 24 ans :
• à la date en cours
• à une date fixe, soit par exemple le 10/06/2019.
Indication : Pour appliquer la mise en forme conditionnelle à la plage de cellules A2:C24,
sélectionnez cette plage et cliquez sur la commande Mise en forme conditionnelle au niveau
de l’onglet Accueil du ruban. Choisissez Nouvelle règle...
Au niveau de la boite de dialogue Nouvelle règle de mise en forme qui apparaît :
ERROUSSO Hanae
Maitrise du pack Office (Excel)
• Cliquez sur Utiliser une formule pour déterminer pour quelles cellules le format
sera appliqué de la liste Sélectionnez un type de règle...
• Cliquez ensuite sur le bouton Format... pour ouvrir la boite de dialogue Format de
cellule et choisissez le format approprié.
• Au champ Appliquer une mise en forme aux valeurs pour lesquelles cette formule
est vraie entrez la formule convenable.
Exercice 3 :
Calculez les données manquantes comme indiqué dans les en-têtes de colonnes en combinant
plusieurs fonctions.
Exercice 4 :
Grâce aux fonctions fin.mois et serie.jour.ouvre, complétez les cellules bleues.
Exercice 5 :
Calculez le temps de travail journalier des opérateurs en tenant compte de deux conditions :
• Si l'heure de départ est avant 17h00, le temps de travail total est pris en compte en
déduisant 45 minutes.
• Si l'heure de départ est après 17h00, la totalité du temps de travail est prise en compte.
ERROUSSO Hanae
Maitrise du pack Office (Excel)
Exercice 6 :
L'exercice se décompose en deux parties. La moyenne du prix des articles et la moyenne des
produits en stock des cellules B81 et B82 ne posent pas de problèmes particuliers. Les 4
moyennes suivantes sont à trouver avec la fonction MOYENNE.SI.
Exercice 7 :
Soit l’extrait de feuille de calcul. En colonne B est enregistrée la date de début d'un certain
processus. En colonne C, est enregistrée la date d'achèvement de la 1ère opération et en colonne
D la date d'achèvement de la 2ème opération du processus. Nous supposons que chacune des 2
opérations doit durer moins de 20 jours.
ERROUSSO Hanae
Maitrise du pack Office (Excel)
Ecrivez les formules appropriées en colonnes E et F permettant de générer les scores de la
manière suivante :
• En E, le score est Mauvais si les 2 opérations ont duré plus de 20 jours. Sinon, le score
est Bon.
• En F, le score est Mauvais si les 2 opérations ont duré plus de 20 jours. Le score est
Moyen si l'une des deux opérations a duré plus de 20 jours. Sinon, le score est Bon.
Exercice 8 :
Soit la feuille suivante où doivent être enregistrés les points gagnés par des joueurs pendant
chacune des 4 parties du jeu.
Aux colonnes E à G doivent être calculés les scores atteints au bout de chaque partie. Par
exemple à la cellule G5 c’est la somme des valeurs des cellules B5, C5 et D5.
Aux colonnes H à J ce sont les pourcentages atteints par rapport au score final enregistré à la
cellule B19.
Les colonnes E à J sont remplies en utilisant la fonction SOMME et l’opération de division.
Mais, il y a une imperfection. Il s’agit des « 0 » affichés comme nous pouvons remarquer à
l’extrait ci-dessous. D’autre part, les valeurs affichées aux plusieurs cellules sont erronées. Elles
sont inconnues car la valeur du score de la 2ème partie ou la 3ème partie pour le joueur ne sont
pas encore connues.
La fonction Excel SOMME ignore les cellules vides. Mais, cela donne des valeurs erronées
pour l’exemple présent. Il convient donc de ne rien afficher lorsque les valeurs impliquées dans
une formule ne sont pas toutes renseignées.
Ajoutez des fonctions logiques pour appliquer cela.
ERROUSSO Hanae
Maitrise du pack Office (Excel)
Exercice 9 :
Soit l’extrait de feuille de calcul suivant :
Calculez en colonne E la prime accordée aux coureurs sachant que les coureurs sans victoire
n'y ont pas droit. La prime s'élève à 500 € par victoire pour les coureurs avec 1 ou 2 victoires
et à 700 € par victoire pour les coureurs ayant 3 victoires ou plus.
Exercice 10 :
Soit l’extrait de feuille de calcul représentant une liste d'abonnés d'une association.
A une autre feuille du même classeur, est calculé le nombre d'abonnés par ville d'une part et par
ville et par an d'autre part :
ERROUSSO Hanae
Maitrise du pack Office (Excel)
1. Ecrire la formule en B3 permettant de calculer le nombre d'adhérents par ville à partir de
la liste de la 1ère feuille. La plage B3:B9 doit être remplie par recopie incrémentée de la
formule en B3.
2. Ecrire la formule en C3 permettant de calculer le nombre d'adhérents par ville et par an à
partir de la liste de la 1ère feuille. La plage C3:F9 doit être remplie par recopie incrémentée
de la formule en C3.
A une autre feuille du même classeur, est calculé la somme de dons par ville d'une part et par
ville et par secteur d'autre part :
3. Ecrire la formule en B3 permettant de calculer la somme de dons par ville à partir de la
liste de la 1ère feuille. La plage B3:B8 doit être remplie par recopie incrémentée de la
formule en B3.
4. Ecrire la formule en C3 permettant de calculer la somme de dons par ville et par secteur
d'activité à partir de la liste de la 1ère feuille. La plage C3:H8 doit être remplie par recopie
incrémentée de la formule en C3.
Exercice 11 :
Une entreprise de transport rémunère ses chauffeurs en fonctions de plusieurs critères :
• Un salaire de base
• Une prime d'ancienneté
• Des heures supplémentaires
• Des primes pour kilométrages important
• Des primes pour conduite de nuit
• Des primes pour déplacement à l'étranger
ERROUSSO Hanae
Maitrise du pack Office (Excel)
Calculez le salaire brut total de chaque chauffeur compte tenu des éléments suivants :
• Le salaire de base est obtenu en multipliant le nombre d'heures normales (169 heures)
par le taux horaire.
• La prime d'ancienneté est calculée en appliquant un taux au salaire de base.
• Les heures supplémentaires se calculent en majorant le taux horaire de 25%, 50% ou
100%.
• La prime au kilométrage est de 30 € si le chauffeur a parcouru plus de 3000 Kms, 40 €
pour plus de 4000 Kms et 75 € pour 5000 Kms (cette prime se cumule).
• Le salaire sera augmenté de 0,90 € par heure de conduite de nuit.
• Une prime de 90 € sera ajoutée pour chaque déplacement à l'étranger.
Exercice 12 :
Complétez le fichier travail correspondant à l’exercice 12 en suivant les consignes indiquées.
ERROUSSO Hanae
