Module 4 : Introduction aux Formules et Fonctions de Base
Introduction
Bienvenue dans le module 4 de notre formation Excel. Après avoir exploré l'interface, la mise en forme et les manipulations de cellules, nous allons maintenant découvrir l'une des fonctionnalités les plus puissantes d'Excel : les formules et fonctions.
Les formules sont au cœur de la puissance d'Excel et constituent la raison principale pour laquelle ce tableur est si largement utilisé dans le monde professionnel. Elles permettent d'automatiser les calculs, d'analyser les données et de générer des résultats dynamiques qui se mettent à jour automatiquement lorsque les données changent.
Dans ce module, nous nous concentrerons sur les bases essentielles : comment créer des formules mathématiques simples, utiliser les fonctions courantes comme SOMME et MOYENNE, et comprendre les règles qui régissent l'ordre des opérations dans Excel.
FICHE PRATIQUE : FORMULES ET FONCTIONS DE BASE
I. CRÉER DES FORMULES DE BASE
Principes fondamentaux des formules
- Toute formule dans Excel commence par le signe égal (=)
- Les formules peuvent contenir des valeurs numériques, des références de cellules, des opérateurs et des fonctions
- Excel calcule automatiquement le résultat des formules et l'affiche dans la cellule
- Lorsque vous modifiez des valeurs référencées dans une formule, le résultat se met à jour automatiquement
Opérateurs mathématiques de base
| Opérateur | Description | Exemple | Résultat |
|---|---|---|---|
| + (addition) | Additionne des valeurs | =5+3 ou =A1+B1 | 8 (si A1=5 et B1=3) |
| - (soustraction) | Soustrait des valeurs | =5-3 ou =A1-B1 | 2 (si A1=5 et B1=3) |
| * (multiplication) | Multiplie des valeurs | =5*3 ou =A1*B1 | 15 (si A1=5 et B1=3) |
| / (division) | Divise des valeurs | =15/3 ou =A1/B1 | 5 (si A1=15 et B1=3) |
| ^ (puissance) | Élève un nombre à une puissance | =5^2 ou =A1^B1 | 25 (si A1=5 et B1=2) |
| % (pourcentage) | Convertit un nombre en pourcentage | =50% | 0,5 (50%) |
Saisie d'une formule
- Sélectionnez la cellule où vous souhaitez afficher le résultat
- Tapez le signe égal (=) pour commencer une formule
- Entrez votre formule (par exemple, =A1+B1)
- Appuyez sur Entrée pour valider la formule
- Le résultat s'affiche dans la cellule, mais la formule reste visible dans la barre de formule
II. FONCTIONS DE BASE
Qu'est-ce qu'une fonction ?
- Une fonction est une formule prédéfinie qui effectue un calcul spécifique
- Les fonctions ont un nom (comme SOMME, MOYENNE) suivi de parenthèses
- Entre les parenthèses, on place des arguments (les données sur lesquelles la fonction opère)
- Les arguments peuvent être des valeurs, des références de cellules, des plages ou d'autres formules
La fonction SOMME
- Syntaxe : =SOMME(nombre1; [nombre2]; ...)
- Objectif : Additionne tous les nombres dans la plage spécifiée
- Exemples :
- =SOMME(A1:A10) ⟶ additionne les valeurs des cellules A1 à A10
- =SOMME(A1:A10;C1:C10) ⟶ additionne les valeurs des cellules A1 à A10 ET C1 à C10
- =SOMME(A1:C5) ⟶ additionne toutes les valeurs dans le rectangle A1 à C5
- Raccourci : Sélectionnez une cellule sous ou à côté d'une série de nombres et appuyez sur Alt + =
La fonction MOYENNE
- Syntaxe : =MOYENNE(nombre1; [nombre2]; ...)
- Objectif : Calcule la moyenne arithmétique des nombres
- Exemples :
- =MOYENNE(A1:A10) ⟶ calcule la moyenne des valeurs de A1 à A10
- =MOYENNE(A1;B1;C1) ⟶ calcule la moyenne des valeurs des trois cellules spécifiées
- Note : Les cellules vides sont ignorées, mais les zéros sont pris en compte
La fonction MAX
- Syntaxe : =MAX(nombre1; [nombre2]; ...)
- Objectif : Renvoie la valeur maximale de la plage spécifiée
- Exemple : =MAX(A1:D10) ⟶ trouve la valeur la plus élevée dans la plage A1:D10
La fonction MIN
- Syntaxe : =MIN(nombre1; [nombre2]; ...)
- Objectif : Renvoie la valeur minimale de la plage spécifiée
- Exemple : =MIN(A1:D10) ⟶ trouve la valeur la plus basse dans la plage A1:D10
III. ORDRE DES OPÉRATIONS
La règle PEMDAS
Excel suit l'ordre conventionnel des opérations mathématiques, parfois mémorisé avec l'acronyme PEMDAS :
- Parenthèses (calculs entre parenthèses en premier)
- Exposants (puissances et racines)
- Multiplication et Division (de gauche à droite)
- Addition et Soustraction (de gauche à droite)
Exemples et pièges courants
| Formule | Résultat | Explication |
|---|---|---|
| =5+3*2 | 11 | Multiplication (3*2=6) avant addition (5+6=11) |
| =(5+3)*2 | 16 | Parenthèses d'abord (5+3=8), puis multiplication (8*2=16) |
| =10/2*5 | 25 | Division et multiplication de gauche à droite (10/2=5, puis 5*5=25) |
| =10/(2*5) | 1 | Parenthèses d'abord (2*5=10), puis division (10/10=1) |
Bonnes pratiques
- Utilisez des parenthèses pour clarifier l'ordre des opérations, même si elles ne sont pas strictement nécessaires
- Décomposez les formules complexes en plusieurs étapes dans des cellules séparées
- Pour plus de lisibilité, ajoutez des espaces dans vos formules (Excel les ignore)
- Utilisez les fonctions appropriées plutôt que de longues formules quand c'est possible
IV. DÉPANNAGE DES FORMULES
Erreurs courantes
| Erreur | Signification | Causes possibles |
|---|---|---|
| #DIV/0! | Division par zéro | - Division par une cellule vide ou contenant 0 - Division par un résultat qui s'évalue à 0 |
| #VALEUR! | Type de valeur incorrect | - Utilisation de texte dans une formule mathématique - Mélange incompatible de types de données |
| #REF! | Référence invalide | - Référence à une cellule qui a été supprimée - Référence hors des limites de la feuille |
| #NOM? | Nom non reconnu | - Faute de frappe dans le nom d'une fonction - Utilisation d'une fonction non disponible - Texte non mis entre guillemets |
| #N/A | Valeur non disponible | - Fonction de recherche qui ne trouve pas la valeur cherchée |
Outils de débogage
- Mode Formule : Appuyez sur Ctrl + ` (accent grave) pour afficher les formules dans les cellules au lieu des résultats
- Évaluation de formule : Dans l'onglet Formules, cliquez sur "Évaluer la formule" pour exécuter la formule étape par étape
- Vérification des erreurs : Cliquez sur le petit triangle qui apparaît près d'une erreur pour voir les options de correction
- Audit de formules : Utilisez les outils "Repérer les antécédents" et "Repérer les dépendants" dans l'onglet Formules pour suivre les relations entre les cellules
Création de formules simples
La création de formules dans Excel est à la base de toutes les analyses de données, même les plus simples. Commençons par comprendre comment créer des formules élémentaires.
- Sélectionnez la cellule où vous voulez afficher le résultat
- Tapez le signe égal (=) pour démarrer la formule
- Vous pouvez maintenant :
- Saisir directement des valeurs (ex : =10+5)
- Cliquer sur des cellules pour insérer leurs références (ex : cliquez sur A1, tapez +, puis cliquez sur B1 pour obtenir =A1+B1)
- Combiner les deux approches (ex : =A1+5)
- Ajoutez les opérateurs mathématiques appropriés (+, -, *, /) entre les valeurs ou références
- Appuyez sur Entrée pour valider et obtenir le résultat
Une fois la formule créée, Excel affiche le résultat dans la cellule, mais la formule elle-même reste visible dans la barre de formule lorsque vous sélectionnez la cellule.
Ce que vous saisissez :
Dans la cellule C1 : =A1+B1
Où A1 contient 10 et B1 contient 15
Ce que vous voyez :
Dans la cellule C1 : 25
Dans la barre de formule : =A1+B1
Astuce : Lorsque vous modifiez les valeurs dans A1 ou B1, le résultat dans C1 est automatiquement recalculé. C'est l'un des grands avantages d'Excel : les calculs dynamiques qui se mettent à jour automatiquement.
Pendant que vous créez une formule, vous pouvez naviguer dans la feuille pour sélectionner des cellules distantes :
- Utilisez les flèches du clavier pour vous déplacer
- Utilisez Ctrl + flèches pour vous déplacer rapidement vers les extrémités des données
- Utilisez F4 après avoir inséré une référence pour basculer entre les types de références (relative, absolue, mixte)
- Appuyez sur Entrée pour terminer la formule ou Échap pour l'annuler
Pour modifier une formule déjà créée :
- Double-cliquez sur la cellule contenant la formule, ou
- Sélectionnez la cellule et appuyez sur F2, ou
- Sélectionnez la cellule et cliquez dans la barre de formule
- Effectuez vos modifications
- Appuyez sur Entrée pour valider
Utilisation des fonctions fondamentales
Les fonctions sont des formules prédéfinies qui simplifient les calculs complexes. Elles sont essentielles pour travailler efficacement avec Excel.
Il existe plusieurs méthodes pour insérer une fonction :
- Méthode directe : Tapez = suivi du nom de la fonction (ex : =SOMME) et ouvrez une parenthèse
- Via l'onglet Formules :
- Cliquez sur l'onglet Formules
- Choisissez la fonction dans les catégories ou cliquez sur "Insérer une fonction"
- Via l'icône fx : Cliquez sur le bouton fx à gauche de la barre de formule
- Avec les boutons de fonctions courantes : Utilisez le bouton Somme automatique (Σ) et ses options dans l'onglet Accueil
Scénario pratique : Analyse des ventes mensuelles
Imaginons que vous ayez un tableau de ventes mensuelles et que vous souhaitiez analyser ces données :
Pour calculer le total des ventes :
- Sélectionnez la cellule où vous voulez afficher le total
- Cliquez sur l'icône Somme (Σ) dans l'onglet Accueil
- Excel propose automatiquement une plage à additionner (généralement les cellules au-dessus ou à gauche)
- Ajustez la plage si nécessaire en faisant glisser la sélection
- Appuyez sur Entrée pour valider
Pour trouver la moyenne des ventes :
- Sélectionnez la cellule pour le résultat
- Cliquez sur la flèche à côté de l'icône Somme (Σ)
- Sélectionnez "Moyenne" dans le menu déroulant
- Ajustez la plage si nécessaire
- Appuyez sur Entrée
Pour identifier les meilleures et les moins bonnes ventes :
- Pour le maximum : Utilisez =MAX(plage)
- Pour le minimum : Utilisez =MIN(plage)
Grâce à ces fonctions, vous obtenez instantanément une vue d'ensemble de vos performances de vente sans avoir à effectuer manuellement de multiples calculs.
Vous pouvez utiliser une fonction comme argument d'une autre fonction :
- Exemple : =ARRONDI(MOYENNE(A1:A10);2)
- Cette formule calcule d'abord la moyenne de la plage A1:A10, puis arrondit le résultat à 2 décimales
- Excel évalue toujours les fonctions de l'intérieur vers l'extérieur
Cette technique vous permet de créer des calculs sophistiqués en combinant plusieurs fonctions simples.
La fonction SOMME.SI est une extension puissante de SOMME qui vous permet d'additionner uniquement les cellules qui répondent à un critère spécifique :
- Syntaxe : =SOMME.SI(plage_à_vérifier; critère; [plage_à_additionner])
- Exemple : =SOMME.SI(B1:B10;">100";C1:C10) additionnera les valeurs dans C1:C10 mais uniquement pour les lignes où les valeurs dans B1:B10 sont supérieures à 100
Cette fonction est particulièrement utile pour les analyses de données où vous devez calculer des sous-totaux basés sur des conditions.
Comprendre l'ordre des opérations
La compréhension de l'ordre dans lequel Excel évalue les différentes parties d'une formule est cruciale pour obtenir des résultats corrects.
Excel suit un ordre d'évaluation strict basé sur les règles mathématiques standards :
- Parenthèses : Excel calcule d'abord tout ce qui se trouve entre parenthèses
- Exposants : Ensuite, il calcule les puissances et racines (opérateur ^)
- Multiplication et Division : Ces opérations sont évaluées de gauche à droite
- Addition et Soustraction : Finalement, les additions et soustractions sont évaluées de gauche à droite
Pour les fonctions imbriquées, Excel évalue d'abord les fonctions les plus internes, puis progresse vers l'extérieur.
Sans parenthèses :
=5+10*2
Résultat : 25
Expliqué :
- Multiplication : 10*2 = 20
- Addition : 5+20 = 25
Avec parenthèses :
=(5+10)*2
Résultat : 30
Expliqué :
- Parenthèses : (5+10) = 15
- Multiplication : 15*2 = 30
Erreur courante : Les formules =A1+A2+A3/3 et =MOYENNE(A1;A2;A3) ne donnent pas le même résultat ! La première divise uniquement A3 par 3 puis ajoute A1 et A2, tandis que la seconde calcule correctement la moyenne des trois valeurs.
Les parenthèses vous permettent de modifier l'ordre d'évaluation par défaut :
- Utilisez des parenthèses pour forcer Excel à calculer certaines parties de la formule en premier
- Les parenthèses peuvent être imbriquées : les parenthèses les plus internes sont évaluées en premier
- Il est préférable d'utiliser trop de parenthèses que pas assez : elles clarifient vos intentions
Exemple : =(A1+A2)*(A3-A4) forcera Excel à calculer d'abord les sommes et différences, puis à les multiplier.
Pour les calculs complexes, il est souvent plus clair et plus sûr de décomposer la formule en étapes intermédiaires :
- Calculez les différentes parties de la formule dans des cellules séparées
- Utilisez ces résultats intermédiaires dans votre formule finale
Cette approche rend vos calculs plus faciles à comprendre, à vérifier et à corriger.
Dépannage des formules
Même les utilisateurs expérimentés rencontrent parfois des problèmes avec leurs formules. Savoir identifier et résoudre ces problèmes est une compétence essentielle.
Lorsque vous rencontrez une erreur, suivez ces étapes de dépannage :
- Identifiez le type d'erreur : #DIV/0!, #VALEUR!, #REF!, etc.
- Vérifiez la syntaxe : Assurez-vous que les parenthèses sont équilibrées et que les fonctions sont correctement écrites
- Examinez les cellules référencées : Vérifiez que les cellules auxquelles vous faites référence contiennent les types de données attendus
- Utilisez l'évaluation de formule : Dans l'onglet Formules, cliquez sur "Évaluer la formule" pour voir comment Excel évalue votre formule étape par étape
- Simplifiez la formule : Décomposez les formules complexes en étapes plus simples pour isoler le problème
La fonction SI.ERREUR vous permet de remplacer les messages d'erreur par des valeurs ou des textes plus utiles :
- Syntaxe : =SI.ERREUR(valeur; valeur_si_erreur)
- Exemple : =SI.ERREUR(A1/B1;"Division impossible") affichera "Division impossible" au lieu de #DIV/0! si B1 contient 0 ou est vide
Cette approche rend vos feuilles de calcul plus robustes et plus conviviales.
Pour voir toutes les formules à la fois au lieu de leurs résultats :
- Appuyez sur Ctrl + ` (accent grave, généralement sous la touche Échap)
- Alternativement, allez dans l'onglet Formules et cliquez sur "Afficher les formules"
Ce mode vous permet d'examiner rapidement toutes vos formules et de repérer les incohérences ou les erreurs potentielles.
Exercices pratiques
Exercice 1 : Opérations mathématiques de base
Objectif : Pratiquer la création de formules mathématiques simples et comprendre l'ordre des opérations.
- Créez une nouvelle feuille de calcul Excel
- Dans les cellules A1 à A4, saisissez les nombres 10, 20, 5 et 2
- Dans la cellule B1, créez une formule qui additionne A1 et A2
- Dans la cellule B2, créez une formule qui soustrait A3 de A1
- Dans la cellule B3, créez une formule qui multiplie A2 par A3
- Dans la cellule B4, créez une formule qui divise A1 par A4
- Dans la cellule C1, créez la formule =A1+A2*A3 et notez le résultat
- Dans la cellule C2, créez la formule =(A1+A2)*A3 et comparez avec le résultat précédent
- Dans la cellule C3, créez la formule =A1+A2+A3/A4 et notez le résultat
- Dans la cellule C4, créez la formule =(A1+A2+A3)/A4 et comparez avec le résultat précédent
Exercice 2 : Utilisation des fonctions SOMME et MOYENNE
Objectif : Pratiquer l'utilisation des fonctions de base pour des calculs statistiques simples.
- Créez un tableau de ventes mensuelles pour l'année :
- Dans la colonne A, saisissez les noms des mois (Janvier à Décembre)
- Dans la colonne B, saisissez des montants de ventes aléatoires pour chaque mois (entre 1000 et 5000)
- Dans la cellule B14, utilisez la fonction SOMME pour calculer le total des ventes annuelles
- Dans la cellule B15, utilisez la fonction MOYENNE pour calculer la moyenne des ventes mensuelles
- Dans la cellule B16, utilisez la fonction MIN pour trouver le montant des ventes du mois le plus faible
- Dans la cellule B17, utilisez la fonction MAX pour trouver le montant des ventes du mois le plus fort
- Dans la colonne C, créez une formule qui calcule le pourcentage que représente chaque mois par rapport au total annuel (utilisez des références absolues et relatives appropriées)
Exercice 3 : Calcul de budget familial
Objectif : Appliquer les formules et fonctions dans un scénario réel.
- Créez un tableau de budget familial avec les catégories suivantes :
- Revenus : Salaire 1, Salaire 2, Autres revenus
- Dépenses : Logement, Alimentation, Transport, Factures, Loisirs, Divers
- Ajoutez des colonnes pour Janvier, Février et Mars
- Remplissez le tableau avec des valeurs réalistes pour chaque catégorie et chaque mois
- Créez une formule qui calcule le total des revenus pour chaque mois
- Créez une formule qui calcule le total des dépenses pour chaque mois
- Calculez la différence entre revenus et dépenses (épargne ou déficit) pour chaque mois
- Utilisez les fonctions SOMME et MOYENNE pour calculer les totaux et moyennes trimestrielles pour chaque catégorie
- Bonus : Utilisez la mise en forme conditionnelle pour mettre en évidence les déficits éventuels en rouge
Exercice 4 : Analyse de notes d'étudiants
Objectif : Combiner plusieurs fonctions pour une analyse de données plus complexe.
- Créez un tableau de notes d'étudiants avec les colonnes suivantes :
- Nom de l'étudiant
- Notes pour 3 contrôles (sur 20)
- Note d'examen final (sur 40)
- Saisissez des données pour au moins 10 étudiants
- Créez une formule qui calcule la note totale de chaque étudiant (somme des 3 contrôles + examen final)
- Créez une formule qui calcule la moyenne de la classe pour chaque contrôle et pour l'examen final
- Utilisez les fonctions MIN et MAX pour déterminer les notes minimales et maximales pour chaque évaluation
- Bonus : Créez une formule qui attribue une mention en fonction de la note totale :
- ≥ 80 : Excellent
- 65-79 : Très bien
- 50-64 : Bien
- 40-49 : Passable
- < 40 : Insuffisant
Résumé des points clés
Points essentiels à retenir sur la création de formules :
- Toute formule commence par le signe égal (=)
- Les formules peuvent contenir des valeurs, des références de cellules, des opérateurs et des fonctions
- Les formules sont dynamiques : si les données référencées changent, le résultat est automatiquement recalculé
- Pour créer une formule, sélectionnez la cellule, tapez =, construisez votre formule et appuyez sur Entrée
Les fonctions essentielles à maîtriser :
- SOMME() : Additionne les valeurs dans une plage de cellules
- MOYENNE() : Calcule la moyenne arithmétique des valeurs
- MAX() : Renvoie la valeur maximale d'une plage
- MIN() : Renvoie la valeur minimale d'une plage
- NB() : Compte le nombre de cellules contenant des nombres
L'ordre d'évaluation des opérations suit la règle PEMDAS :
- Parenthèses
- Exposants
- Multiplication et Division (de gauche à droite)
- Addition et Soustraction (de gauche à droite)
Utilisez des parenthèses pour contrôler l'ordre d'évaluation et clarifier vos intentions.
Techniques pour résoudre les problèmes de formules :
- Identifiez le type d'erreur et comprenez sa signification
- Vérifiez la syntaxe de la formule et l'équilibre des parenthèses
- Utilisez l'évaluation de formule pour examiner les calculs étape par étape
- Simplifiez les formules complexes en les décomposant
- Utilisez SI.ERREUR() pour gérer élégamment les erreurs potentielles
Pour aller plus loin : Les formules et fonctions couvertes dans ce module constituent la base de la puissance d'Excel. Dans les modules suivants, nous explorerons des fonctions plus avancées comme RECHERCHEV, SI et les fonctions de manipulation de texte. Mais tout commence par une compréhension solide des principes fondamentaux abordés ici. Pratiquez ces concepts de base jusqu'à ce qu'ils deviennent une seconde nature.