Université Ibn Tofail - Faculté des Sciences Juridiques et Politiques
Culture Digitale - S5 / Année universitaire 2025–2026
Dr. Mohammed Jouhari
TP4 : Maîtriser les formules et fonctions dans Excel
Objectifs pédagogiques
À la fin de ce TP, l’étudiant sera capable de :
➤ Construire et recopier des formules dans un tableau Excel.
➤ Utiliser les principales fonctions automatiques et conditionnelles.
➤ Analyser et filtrer les données à l’aide de critères logiques.
➤ Rechercher automatiquement des informations entre feuilles.
➤ Concevoir un mini-système d’évaluation automatisé sous Excel.
Pré-requis
✓ Connaître l’interface d’Excel et savoir créer un tableau simple.
✓ Avoir suivi le TP3 « Découverte d’Excel et premiers pas ».
✓ Savoir saisir et formater des données de base.
Matériel nécessaire
✓ Microsoft Excel 2016 ou ultérieur.
✓ Fichier de travail : TP4_Formules.xlsx.
Exercice 1 – Découvrir et créer ses premières formules
Objectif : Comprendre comment Excel exécute des calculs et manipule les opérateurs arithmétiques.
Situation : Vous souhaitez automatiser un petit calcul de dépenses personnelles.
1. Ouvrez un nouveau classeur nommé TP4_Formules.xlsx.
2. Dans les cellules A1 et B1, saisissez Montant1 et Montant2.
3. Dans A2 et B2, utilisez les formules suivantes pour générer des valeurs aléatoires :
➤ =RANDBETWEEN(100;500) dans A2 ;
➤ =RANDBETWEEN(50;300) dans B2.
4. Sélectionnez A2 :B2 et utilisez la poignée de recopie automatique pour remplir jusqu’à la ligne 10.
5. En C1, tapez Total.
6. Dans C2, saisissez la formule =A2+B2 puis recopiez vers le bas.
7. Testez d’autres formules : =A2-B2, =A2*B2, =A2/B2.
Astuce pratique
Toutes les formules commencent toujours par le signe =. Vous pouvez aussi insérer des formules à l’aide de
l’onglet Formules Insérer une fonction.
Dr. Mohammed Jouhari Page 1
Exercice 2 – Références relatives et absolues
Objectif : Comprendre comment figer ou ajuster des cellules lors d’une recopie de formule.
Situation : Vous préparez un mini-tableau pour calculer le prix TTC de plusieurs articles.
1. Créez un tableau avec les en-têtes : Article, Prix HT, TVA, Prix TTC.
2. Dans la colonne A (Article), saisissez les produits : Stylos, Cahiers, Agendas, etc. Vous pouvez aussi taper «
Article1 » puis utiliser la poignée de recopie automatique pour créer la suite.
3. En B2, entrez : =RANDBETWEEN(50;400) puis recopiez jusqu’à B10.
4. En C1, saisissez le taux de TVA : 20%.
5. En D2, tapez : =B2*(1+$C$1) puis recopiez vers le bas.
Résultat attendu : la colonne « Prix TTC » se calcule automatiquement, même si le taux de TVA reste fixe.
Astuce avancée
Le symbole $ fige une cellule dans une formule. Utilisez $C$1 pour conserver une référence constante lors
de la recopie.
Exercice 3 – Utiliser les fonctions automatiques
Objectif : Utiliser les fonctions intégrées d’Excel pour calculer des moyennes et statistiques globales.
Situation : Vous gérez un tableau de notes d’étudiants et souhaitez calculer la moyenne générale.
1. Créez les colonnes : Nom, Note 1, Note 2, Moyenne.
2. En A2, saisissez « Étudiant1 » puis recopiez jusqu’à A10.
3. En B2 et C2, utilisez : =RANDBETWEEN(5;20) puis recopiez jusqu’à la ligne 10.
4. En D2, saisissez : =MOYENNE(B2:C2) et recopiez.
5. Sous le tableau, ajoutez une ligne « Statistiques générales ».
6. Calculez :
➤ Moyenne générale : =MOYENNE(D2:D10) ;
➤ Note la plus haute : =MAX(D2:D10) ;
➤ Note la plus basse : =MIN(D2:D10).
Exercice 4 – Automatiser les décisions avec les fonctions conditionnelles
Objectif : Utiliser les fonctions SI, ET, et OU pour automatiser les résultats.
Situation : Vous voulez que le tableau indique automatiquement si chaque étudiant est admis ou non.
1. Reprenez le tableau précédent.
2. Ajoutez une colonne « Résultat » (E1).
3. En E2, saisissez : =SI(D2>=10;"Admis";"Non admis").
4. Recopiez la formule jusqu’à E10.
5. Essayez une version plus détaillée : =SI(D2<10;"Ajourné";SI(D2<15;"Passable";SI(D2<18;"Bien";"Très bien"
6. Mettez en surbrillance les résultats avec la mise en forme conditionnelle : Accueil Mise en forme conditionnelle
Texte contenant “Admis” vert.
Exercice 5 – Analyser les données avec [Link] et [Link]
Objectif : Réaliser un mini tableau de ventes aléatoires pour calculer des totaux par catégorie.
Situation : Vous travaillez sur un rapport de ventes hebdomadaire.
1. Dans une nouvelle feuille, créez un tableau : Produit, Catégorie, Montant (DH).
Dr. Mohammed Jouhari Page 2 / 3
2. En A2, saisissez « Produit1 » puis recopiez jusqu’à A15.
3. En B2, saisissez au hasard les catégories : « Papeterie », « Informatique », « Mobilier ».
4. En C2, entrez : =RANDBETWEEN(100;1000) puis recopiez.
5. En dessous, calculez :
➤ Total Papeterie : =[Link](B2:B15;"Papeterie";C2:C15) ;
➤ Total Informatique : =[Link](B2:B15;"Informatique";C2:C15).
6. Comptez aussi les produits : =[Link](B2:B15;"Papeterie").
Exercice 6 – Rechercher automatiquement des données (RECHERCHEV)
Objectif : Extraire automatiquement une information d’une autre feuille.
Situation : Vous possédez deux feuilles de données : l’une avec les identifiants, l’autre avec les notes.
1. Dans Feuil1, créez : ID Étudiant, Nom, Résultat.
2. Dans Feuil2, créez : ID Étudiant, Nom, Note.
3. Dans Feuil2 !C2, insérez : =RANDBETWEEN(5;20) puis recopiez jusqu’à C15.
4. Dans Feuil1 !C2, tapez : =RECHERCHEV(A2;Feuil2!A2:C15;3;FAUX).
5. Testez la variante : =SIERREUR(RECHERCHEV(A2;Feuil2!A2:C15;3;FAUX);"Non trouvé").
Astuce professionnelle
Utilisez la fonction SIERREUR pour éviter les messages d’erreur lors de recherches inexistantes. La recopie
automatique accélère grandement les recherches en série.
Exercice 7 – Projet final : système d’évaluation automatisé
Objectif : Combiner plusieurs fonctions et données aléatoires pour créer un tableau d’analyse intelligent.
Situation : Vous construisez un outil de suivi complet des étudiants.
1. Créez un fichier Gestion_Notes.xlsx.
2. Dans la feuille Base, saisissez : ID Étudiant, Nom, Note1, Note2.
3. En Note1 et Note2, utilisez =RANDBETWEEN(5;20) puis recopiez jusqu’à la ligne 15.
4. Dans la feuille Résultats, créez : ID Étudiant, Nom, Moyenne, Mention.
5. En C2 : =MOYENNE(RECHERCHEV(A2;Base!A2:D15;3;FAUX);RECHERCHEV(A2;Base!A2:D15;4;FAUX)).
6. En D2 : =SI(C2>=10;"Admis";"Non admis").
7. Appliquez une mise en forme conditionnelle (vert = Admis, rouge = Non admis).
8. Créez un graphique automatique : Insertion Graphique Colonnes Admis/Non admis.
Résultat attendu
Un tableau complet et interactif : chaque recalcul (F9) génère de nouvelles notes et met à jour automatique-
ment les moyennes, les mentions et le graphique.
Dr. Mohammed Jouhari Page 3 / 3
