Comment maîtriser les formules sur Excel : Le guide pour débutants et experts

Comprendre l'essentiel des formules sur Excel

Les formules sur Excel sont au cœur de la manipulation et de l'analyse des données. Elles permettent d'effectuer des calculs, de rechercher des informations, de manipuler du texte et bien plus encore. Une formule est une expression qui calcule la valeur d'une cellule. Elle commence toujours par le signe égal (=).

Les bases d'une formule Excel

Toute formule Excel commence par le signe égal (=). Ensuite, vous pouvez utiliser des opérateurs mathématiques (+, -, *, /, ^), des références de cellules (A1, B2, C3), des fonctions (SOMME, MOYENNE, RECHERCHEV) et des constantes (nombres, texte entre guillemets).

Exemple :

=A1+B1 (additionne le contenu des cellules A1 et B1) =SOMME(A1:A10) (calcule la somme des cellules A1 à A10) =A1*1.2 (multiplie le contenu de la cellule A1 par 1.2)

Références de cellules : relatives, absolues et mixtes

Les références de cellules sont cruciales pour copier et adapter les formules. Il existe trois types de références :

• Références relatives : Elles changent lorsque la formule est copiée dans une autre cellule. Par exemple, si la cellule C1 contient la formule =A1+B1 et que vous la copiez en C2, la formule deviendra =A2+B2.
• Références absolues : Elles restent fixes même lorsque la formule est copiée. Elles sont indiquées par le signe dollar ($). Par exemple, =$A$1+$B$1 restera toujours =A1+B1 même si vous copiez la formule.
• Références mixtes : Elles combinent une partie relative et une partie absolue. Par exemple, =$A1+B$1 fixe la colonne A mais laisse la ligne relative pour la colonne A, et fixe la ligne 1 mais laisse la colonne relative pour la ligne 1.

Exemple pratique : Calculer une marge avec une référence absolue

Supposons que vous ayez un prix d'achat en A1 et une marge en pourcentage en B1 (par exemple 20%). Vous voulez calculer le prix de vente en C1.

1. Entrez le prix d'achat en A1 (par exemple, 10).
2. Entrez la marge en pourcentage en B1 (par exemple, 20%).
3. En C1, entrez la formule =A1*(1+B$1).

La référence $B$1 est absolue pour la ligne 1. Si vous copiez la formule vers le bas, le prix d'achat (A1, A2, A3...) s'adaptera, mais la marge restera toujours référencée à la cellule B1.

Opérateurs mathématiques et leur priorité

Excel utilise les opérateurs mathématiques standard :

• + (addition)
• - (soustraction)
• * (multiplication)
• / (division)
• ^ (exposant)
• () (parenthèses pour forcer la priorité)

La priorité des opérateurs est la suivante : parenthèses, exposant, multiplication et division (de gauche à droite), addition et soustraction (de gauche à droite).

Exemple :

=2+3*4 donne 14 (car la multiplication est effectuée avant l'addition). =(2+3)*4 donne 20 (car les parenthèses forcent l'addition à être effectuée en premier).

Les fonctions Excel indispensables

Excel propose des centaines de fonctions prédéfinies pour effectuer des calculs, des recherches, des manipulations de texte et bien plus encore. Voici quelques-unes des fonctions les plus couramment utilisées :

Fonctions mathématiques et statistiques

• SOMME : Additionne une plage de cellules. =SOMME(A1:A10)
• MOYENNE : Calcule la moyenne d'une plage de cellules. =MOYENNE(A1:A10)
• MAX : Renvoie la valeur maximale d'une plage de cellules. =MAX(A1:A10)
• MIN : Renvoie la valeur minimale d'une plage de cellules. =MIN(A1:A10)
• NB : Compte le nombre de cellules contenant des nombres dans une plage. =NB(A1:A10)
• NBVAL : Compte le nombre de cellules non vides dans une plage. =NBVAL(A1:A10)
• ECARTYPE.STANDARD : Calcule l'écart type d'un échantillon. =ECARTYPE.STANDARD(A1:A10)

Exemple pratique : Calculer la moyenne des ventes d'un mois

Supposons que vous ayez les ventes de chaque jour du mois dans les cellules A1 à A31. Pour calculer la moyenne des ventes du mois, utilisez la formule =MOYENNE(A1:A31).

Fonctions de recherche et de référence

• RECHERCHEV : Recherche une valeur dans une colonne et renvoie une valeur correspondante dans une autre colonne. =RECHERCHEV(valeur_recherchée; plage_de_recherche; numéro_de_colonne; [valeur_proche])
• RECHERCHEH : Recherche une valeur dans une ligne et renvoie une valeur correspondante dans une autre ligne. =RECHERCHEH(valeur_recherchée; plage_de_recherche; numéro_de_ligne; [valeur_proche])
• INDEX : Renvoie la valeur d'une cellule à une position donnée dans une plage. =INDEX(plage; numéro_de_ligne; [numéro_de_colonne])
• EQUIV : Renvoie la position d'une valeur dans une plage. =EQUIV(valeur_recherchée; plage_de_recherche; [type_de_correspondance])

Exemple pratique : Utiliser RECHERCHEV pour trouver le prix d'un produit

Supposons que vous ayez une table avec les noms des produits dans la colonne A et leurs prix dans la colonne B. Vous voulez trouver le prix du produit "Pomme" qui est dans la cellule D1.

1. En D1, entrez le nom du produit "Pomme".
2. En E1, entrez la formule =RECHERCHEV(D1;A:B;2;FAUX).

Cette formule recherche la valeur de D1 ("Pomme") dans la colonne A, puis renvoie la valeur correspondante de la colonne B (le prix). L'argument FAUX garantit une correspondance exacte.

Fonctions de texte

• GAUCHE : Extrait un certain nombre de caractères à partir du début d'une chaîne de texte. =GAUCHE(texte; nombre_de_caractères)
• DROITE : Extrait un certain nombre de caractères à partir de la fin d'une chaîne de texte. =DROITE(texte; nombre_de_caractères)
• MID : Extrait un certain nombre de caractères à partir du milieu d'une chaîne de texte. =MID(texte; position_de_départ; nombre_de_caractères)
• CONCATENER : Combine plusieurs chaînes de texte en une seule. =CONCATENER(texte1; texte2; ...)
• MAJUSCULE : Convertit une chaîne de texte en majuscules. =MAJUSCULE(texte)
• MINUSCULE : Convertit une chaîne de texte en minuscules. =MINUSCULE(texte)
• NBCAR : Compte le nombre de caractères dans une chaîne de texte. =NBCAR(texte)

Exemple pratique : Extraire le nom de famille d'un nom complet

Supposons que vous ayez un nom complet (par exemple, "Jean Dupont") dans la cellule A1. Vous voulez extraire le nom de famille "Dupont".

1. En B1, entrez la formule =DROITE(A1;NBCAR(A1)-CHERCHE(" ";A1)).

Cette formule utilise CHERCHE pour trouver la position de l'espace dans le nom complet, puis NBCAR pour calculer la longueur totale du nom complet. Ensuite, elle soustrait la position de l'espace de la longueur totale pour obtenir le nombre de caractères du nom de famille. Enfin, elle utilise DROITE pour extraire ces caractères.

Fonctions logiques

• SI : Effectue un test logique et renvoie une valeur si le test est vrai, et une autre valeur si le test est faux. =SI(test_logique; valeur_si_vrai; valeur_si_faux)
• ET : Renvoie VRAI si tous les arguments sont vrais. =ET(logique1; logique2; ...)
• OU : Renvoie VRAI si au moins un des arguments est vrai. =OU(logique1; logique2; ...)
• NON : Inverse la valeur logique d'un argument. =NON(logique)

Exemple pratique : Déterminer si un étudiant est admis en fonction de sa note

Supposons que vous ayez la note d'un étudiant en A1. Un étudiant est admis si sa note est supérieure ou égale à 10.

1. En B1, entrez la formule =SI(A1>=10;"Admis";"Refusé").

Cette formule vérifie si la note en A1 est supérieure ou égale à 10. Si c'est le cas, elle renvoie "Admis". Sinon, elle renvoie "Refusé".

Erreurs courantes dans les formules Excel et comment les corriger

Même les utilisateurs expérimentés d'Excel rencontrent parfois des erreurs dans leurs formules. Voici quelques erreurs courantes et comment les corriger :

• #DIV/0! : Cette erreur se produit lorsque vous essayez de diviser par zéro ou une cellule vide. Vérifiez vos diviseurs et assurez-vous qu'ils ne sont pas nuls.
• #NAME? : Cette erreur se produit lorsque Excel ne reconnaît pas le nom d'une fonction ou d'une plage de cellules. Vérifiez l'orthographe des fonctions et assurez-vous que les plages de cellules sont correctement définies.
• #VALUE! : Cette erreur se produit lorsque vous utilisez un type de données incorrect dans une formule. Par exemple, essayer d'additionner du texte et un nombre.
• #REF! : Cette erreur se produit lorsque vous faites référence à une cellule qui a été supprimée ou dont la référence est invalide. Vérifiez vos références de cellules et assurez-vous qu'elles sont toujours valides.
• #NUM! : Cette erreur se produit lorsque le résultat d'une formule est trop grand ou trop petit pour être affiché. Vérifiez les données d'entrée et assurez-vous qu'elles sont dans une plage de valeurs acceptable.

Astuces pour optimiser vos formules Excel

• Utilisez des noms de plages : Au lieu d'utiliser des références de cellules, vous pouvez définir des noms pour les plages de cellules. Cela rend vos formules plus lisibles et plus faciles à comprendre.
• Utilisez la fonction SI.ERREUR : Cette fonction vous permet de gérer les erreurs dans vos formules et d'afficher un message d'erreur personnalisé ou une valeur par défaut. =SI.ERREUR(formule; valeur_si_erreur)
• Utilisez des tableaux structurés : Les tableaux structurés sont des plages de données formatées avec des en-têtes de colonnes. Ils offrent de nombreux avantages, notamment la possibilité de faire référence aux colonnes par leur nom dans les formules.
• Utilisez la validation des données : La validation des données vous permet de limiter les types de données qui peuvent être entrés dans une cellule. Cela peut aider à prévenir les erreurs dans vos formules.
• Documentez vos formules : Ajoutez des commentaires à vos formules pour expliquer ce qu'elles font. Cela peut être très utile si vous devez modifier ou déboguer vos formules plus tard.

Conclusion

Les formules sur Excel sont un outil puissant qui peut vous aider à automatiser des tâches, à analyser des données et à prendre des décisions éclairées. En comprenant les bases des formules, en utilisant les fonctions appropriées et en évitant les erreurs courantes, vous pouvez maîtriser les formules sur Excel et exploiter pleinement le potentiel de ce logiciel. N'hésitez pas à expérimenter et à explorer les nombreuses fonctionnalités d'Excel pour découvrir de nouvelles façons d'optimiser votre travail.