Excel est surement un outil que vous utilisez au quotidien que ce soit pour la gestion de données, l’analyse financière ou la création de rapports. Maîtriser les bonnes formules peut transformer votre productivité et vous faire gagner des heures de travail. Voici les 20 formules essentielles que tout utilisateur d’Excel devrait connaître pour exploiter pleinement la puissance de ce logiciel.
1. SOMME – L’addition simplifiée
La fonction SOMME est probablement la plus utilisée d’Excel. Elle permet d’additionner rapidement une plage de cellules sans avoir à les sélectionner une par une.
Syntaxe : =SOMME(nombre1; nombre2; ...)
Exemple : =SOMME(A1:A10) additionne toutes les valeurs de A1 à A10.
Cette formule accepte également des arguments multiples : =SOMME(A1:A5; C1:C5; E1) additionne les plages A1:A5, C1:C5 et la cellule E1.
2. MOYENNE – Le calcul de tendance centrale
La fonction MOYENNE calcule la moyenne arithmétique d’un ensemble de valeurs, indispensable pour l’analyse de données.
Syntaxe : =MOYENNE(nombre1; nombre2; ...)
Exemple : =MOYENNE(B2:B20) calcule la moyenne des valeurs contenues dans les cellules B2 à B20.
Cette fonction ignore automatiquement les cellules vides et les cellules contenant du texte, ce qui la rend très pratique pour les données réelles souvent incomplètes.
3. NB.SI – compter avec conditions
La fonction NB.SI compte le nombre de cellules qui répondent à un critère spécifique. C’est un outil puissant pour l’analyse conditionnelle.
Syntaxe : =NB.SI(plage; critère)
Exemples :
=NB.SI(A1:A100; ">50")compte les cellules contenant une valeur supérieure à 50=NB.SI(B1:B50; "Oui")compte les cellules contenant exactement le texte « Oui »
4. SOMME.SI – addition conditionnelle
Similaire à NB.SI, SOMME.SI additionne uniquement les cellules qui répondent à un critère donné.
Syntaxe : =SOMME.SI(plage; critère; somme_plage)
Exemple : =SOMME.SI(A1:A10; ">100"; B1:B10) additionne les valeurs de B1:B10 uniquement si la valeur correspondante en colonne A est supérieure à 100.
5. RECHERCHEV – La recherche verticale
RECHERCHEV est l’une des fonctions les plus puissantes d’Excel pour rechercher des informations dans un tableau.
Syntaxe : =RECHERCHEV(valeur_cherchée; table_matrice; no_index_col; valeur_proche)
Exemple : =RECHERCHEV("Dupont"; A1:D100; 3; FAUX) recherche « Dupont » dans la première colonne du tableau A1:D100 et retourne la valeur de la 3ème colonne de la ligne correspondante.
Le paramètre FAUX garantit une correspondance exacte, tandis que VRAI permet une correspondance approximative.
6. SI – La logique conditionnelle
La fonction SI permet d’exécuter des actions différentes selon qu’une condition est vraie ou fausse.
Syntaxe : =SI(test_logique; valeur_si_vrai; valeur_si_faux)
Exemple : =SI(A1>18; "Majeur"; "Mineur") affiche « Majeur » si A1 est supérieur à 18, sinon « Mineur ».
On peut imbriquer plusieurs fonctions SI : =SI(A1>=90; "Excellent"; SI(A1>=70; "Bien"; "À améliorer"))
7. CONCATENER ou & – assembler du texte
Ces fonctions permettent de joindre plusieurs chaînes de caractères en une seule.
Syntaxes :
=CONCATENER(texte1; texte2; ...)=A1&" "&B1(utilisation de l’opérateur &)
Exemple : =A1&" "&B1 joint le contenu de A1 et B1 avec un espace entre les deux.
8. GAUCHE, DROITE, STXT – Extraction de texte
Ces fonctions permettent d’extraire des portions spécifiques d’une chaîne de caractères.
Syntaxes :
=GAUCHE(texte; nombre_caractères): extrait les caractères de gauche=DROITE(texte; nombre_caractères): extrait les caractères de droite=STXT(texte; position; longueur): extrait une portion au milieu
Exemples :
=GAUCHE(A1; 3)extrait les 3 premiers caractères de A1=STXT(A1; 5; 2)extrait 2 caractères à partir du 5ème caractère de A1
9. MAINTENANT et AUJOURD’HUI – Gestion des dates
Ces fonctions insèrent automatiquement la date et l’heure actuelles.
Syntaxes :
=MAINTENANT(): date et heure actuelles=AUJOURD'HUI(): date actuelle uniquement
Ces fonctions se mettent à jour automatiquement à chaque recalcul du classeur, très utiles pour les rapports datés.
10. MAX et MIN – Valeurs extrêmes
Ces fonctions trouvent respectivement la valeur maximale et minimale dans une plage de données.
Syntaxes :
=MAX(nombre1; nombre2; ...)=MIN(nombre1; nombre2; ...)
Exemples :
=MAX(A1:A100)trouve la plus grande valeur=MIN(B1:B50)trouve la plus petite valeur
11. ARRONDI – Contrôle de précision
La fonction ARRONDI permet de contrôler le nombre de décimales affichées et calculées.
Syntaxe : =ARRONDI(nombre; nombre_chiffres)
Exemples :
=ARRONDI(3,14159; 2)retourne 3,14=ARRONDI(1234,56; -1)retourne 1230 (arrondi à la dizaine)
12. NBVAL – Compter les cellules non vides
NBVAL compte le nombre de cellules non vides dans une plage, très utile pour vérifier la complétude des données.
Syntaxe : =NBVAL(valeur1; valeur2; ...)
Exemple : =NBVAL(A1:A100) compte toutes les cellules non vides dans la plage A1:A100.
13. SUPPRESPACE – Nettoyer le texte
Cette fonction supprime les espaces superflus au début, à la fin et entre les mots (ne laissant qu’un espace entre chaque mot).
Syntaxe : =SUPPRESPACE(texte)
Exemple : =SUPPRESPACE(A1) nettoie le texte de la cellule A1 en supprimant les espaces indésirables.
14. EST.ERREUR – Gestion des erreurs
Cette fonction teste si une cellule contient une erreur et permet de la gérer élégamment.
Syntaxe : =EST.ERREUR(valeur)
Exemple d’utilisation : =SI(EST.ERREUR(RECHERCHEV(A1;B:D;2;FAUX)); "Non trouvé"; RECHERCHEV(A1;B:D;2;FAUX))
Cette formule affiche « Non trouvé » si RECHERCHEV génère une erreur, sinon elle affiche le résultat de la recherche.
15. INDEX et EQUIV – La recherche avancée
Cette combinaison de fonctions offre plus de flexibilité que RECHERCHEV en permettant de rechercher dans n’importe quelle direction.
Syntaxes :
=INDEX(matrice; ligne; colonne)=EQUIV(valeur_cherchée; matrice; type)
Exemple combiné : =INDEX(C1:C100; EQUIV("Dupont"; A1:A100; 0))
Cette formule recherche « Dupont » dans la colonne A et retourne la valeur correspondante de la colonne C.
16. SIERREUR – Gestion élégante des erreurs
SIERREUR simplifie la gestion d’erreurs en remplaçant automatiquement toute erreur par une valeur de votre choix.
Syntaxe : =SIERREUR(valeur; valeur_si_erreur)
Exemple : =SIERREUR(RECHERCHEV(A1; B:D; 2; FAUX); "Non trouvé")
Cette formule est plus simple que la combinaison SI/EST.ERREUR et gère tous les types d’erreurs (#N/A, #VALEUR!, #DIV/0!, etc.).
17. NB.SI.ENS et SOMME.SI.ENS – Critères multiples
Ces fonctions étendent NB.SI et SOMME.SI en permettant d’utiliser plusieurs critères simultanément.
Syntaxes :
=NB.SI.ENS(plage_critère1; critère1; plage_critère2; critère2; ...)=SOMME.SI.ENS(plage_somme; plage_critère1; critère1; plage_critère2; critère2; ...)
Exemples :
=NB.SI.ENS(A:A; ">100"; B:B; "Actif")compte les lignes où colonne A > 100 ET colonne B = « Actif »=SOMME.SI.ENS(C:C; A:A; ">50"; B:B; "Validé")additionne colonne C si A > 50 ET B = « Validé »
18. RANG – Classement et position
La fonction RANG détermine le rang d’un nombre dans une liste, très utile pour les classements et analyses de performance.
Syntaxe : =RANG(nombre; référence; ordre)
Exemples :
=RANG(B2; $B$2:$B$20; 0)donne le rang de B2 dans la liste B2:B20 (ordre décroissant)=RANG(B2; $B$2:$B$20; 1)pour un ordre croissant
Le paramètre ordre : 0 = décroissant (le plus grand nombre = rang 1), 1 = croissant (le plus petit nombre = rang 1).
19. SOUS.TOTAL – calculs sur données filtrées
SOUS.TOTAL effectue des calculs uniquement sur les cellules visibles, ignorant automatiquement les lignes masquées ou filtrées.
Syntaxe : =SOUS.TOTAL(no_fonction; référence1; référence2; ...)
Numéros de fonctions courants :
- 1 = MOYENNE, 2 = NB, 3 = NBVAL, 9 = SOMME, 4 = MAX, 5 = MIN
Exemple : =SOUS.TOTAL(9; B2:B100) additionne uniquement les cellules visibles de B2:B100, parfait pour les tableaux avec filtres automatiques.
20. INDIRECT – Références dynamiques
INDIRECT permet de créer des références de cellules de manière dynamique à partir de texte, offrant une flexibilité exceptionnelle.
Syntaxe : =INDIRECT(référence_texte; style_référence)
Exemples :
=INDIRECT("A"&ROW())fait référence à la cellule de la colonne A sur la ligne actuelle=INDIRECT(A1&"!B2")fait référence à la cellule B2 de la feuille dont le nom est en A1=SOMME(INDIRECT("A1:A"&B1))où B1 contient un numéro de ligne, créant une plage dynamique
Cette fonction est particulièrement puissante pour créer des rapports dynamiques qui s’adaptent automatiquement aux données.
Conseils pour maîtriser ces formules
Utilisez l’aide intégrée : Excel propose une aide contextuelle qui affiche la syntaxe des fonctions au fur et à mesure que vous tapez.
Pratiquez avec des données réelles : Le meilleur moyen d’apprendre est d’appliquer ces formules à vos propres données professionnelles.
Combinez les formules : La vraie puissance d’Excel réside dans la combinaison de plusieurs fonctions. Par exemple, utilisez SI avec RECHERCHEV pour gérer les erreurs de recherche.
Utilisez des références absolues : Ajoutez des $ devant les références de cellules (ex: $A$1) quand vous voulez qu’elles restent fixes lors de la copie de formules.
Nommez vos plages : Donnez des noms explicites à vos plages de données (ex: « VentesQ1 ») pour rendre vos formules plus lisibles.
