Comment créer des formules Excel, des bases aux techniques avancées
Apprenez à créer des formules Excel pas à pas, des références de cellules et des fonctions essentielles jusqu'à l'imbrication, la logique matricielle et les astuces de débogage qui vous font vraiment gagner du temps.
Vous avez ouvert un classeur qui semblait simple, et pourtant les totaux ne correspondent pas, une formule copiée pointe vers la mauvaise colonne, et quelqu’un a laissé un mot : « Il suffit de corriger le IF. » Créer des formules Excel qui calculent correctement, vous en êtes capable. Mais un travail de tableur fiable exige bien plus que de retenir la syntaxe : il faut aussi comprendre les références, tester les cas limites, tracer les dépendances et vérifier toute formule générée par un tiers ou par une IA.
Sommaire
- Ce que vous saurez construire avec des formules Excel
- Les fondamentaux sur lesquels repose toute formule
- Les fonctions clés qui couvrent l’essentiel du travail réel
- Imbrication et formules matricielles expliquées
- Déboguer ses formules quand quelque chose cloche
- Auditer ses formules et s’y fier à grande échelle
- Votre checklist de création de formules
Ce que vous saurez construire avec des formules Excel
Si vous utilisez déjà Excel, il vous arrive peut-être de saisir des valeurs à la main, de recopier des formules de collègues ou de modifier des références de cellules jusqu’à ce que le résultat ait l’air correct. Cette approche dépanne pour un calcul rapide, mais elle devient fragile dès que le classeur grossit ou qu’une autre personne doit le maintenir. Les formules sont des briques réutilisables, pas des réponses à usage unique.
Excel fait partie du travail en entreprise depuis des décennies. Microsoft a lancé Excel pour la première fois sur Macintosh en septembre 1985 et a célébré les 40 ans d’Excel en 2025. La fonction de statistiques de classeur de Microsoft compte désormais les formules au niveau des feuilles comme du classeur entier, signe que les formules sont devenues centrales dans les fichiers tableurs du quotidien. Des recherches menées sur un vaste corpus de tableurs ont également relevé IF présent 30 798 987 fois avant déduplication, ce qui en fait un bon point de départ pour apprendre la logique tableur appliquée. L’histoire d’Excel racontée par Microsoft et l’étude sur les jeux de données et benchmarks de tableurs montrent l’une comme l’autre pourquoi la maîtrise des formules compte.
Une progression pratique
Commencez par un total de colonne comme =SUM(B2:B20). Ajoutez ensuite des conditions : additionner par exemple le chiffre d’affaires d’une seule région avec SUMIFS. À partir de là, vous pouvez construire un IF imbriqué qui classe des enregistrements, ou utiliser XLOOKUP pour rapatrier des prix et des catégories dans une table de transactions.
Dernière étape : un petit tableau de bord dynamique. Une liste déroulante en B1 peut piloter une formule qui filtre les ventes, met à jour un récapitulatif et alimente un graphique. Les exemples de cet article utilisent de petites plages pour que la logique reste bien visible, mais les mêmes habitudes s’appliquent aux classeurs opérationnels multi-feuilles alimentés par des données sources incohérentes.
À l’arrivée, vous serez à l’aise avec :
- Calculs de base : totaux, différences, pourcentages et calculs de dates.
- Logique conditionnelle :
IFimbriqués et synthèses par critères. - Recherches :
VLOOKUPpour les anciens fichiers,XLOOKUPpour les conceptions récentes. - Résultats dynamiques : des formules
FILTER,SORTetUNIQUEqui se propagent dans les cellules voisines. - Contrôle qualité : un processus répétable pour vérifier les références, tester des saisies inhabituelles et remonter les pannes.
Les fondamentaux sur lesquels repose toute formule
Toute formule Excel commence par un signe égal. Il indique à Excel qu’il doit interpréter ce qui suit comme une expression, et non comme du texte ordinaire. Par exemple, =B2*C2 multiplie la quantité de B2 par le prix unitaire de C2.
Les références de cellules déterminent le comportement d’une formule lorsqu’on la copie. Une référence relative comme A1 change au fil des déplacements : une formule de la ligne 2 peut devenir une formule de la ligne 3 après une recopie vers le bas. Une référence absolue comme $A$1 reste fixe. Les références mixtes ne verrouillent qu’une dimension : A$1 fige la ligne, tandis que $A1 fige la colonne.
Les quatre familles d’opérateurs
Les opérateurs arithmétiques effectuent les calculs :
- Arithmétique :
+,-,*,/et^.=B2*C2multiplie deux cellules, tandis que=B2^2élève une valeur au carré. - Comparaison :
=,<>,<,>,<=et>=.=B2>100renvoieTRUEquand la valeur dépasse le seuil. - Concaténation de texte :
&assemble du texte.=A1&A2combine le contenu de deux cellules, tandis que=A1&" "&A2insère un espace. - Opérateurs de référence :
:crée une plage, comme dansA1:A10; la virgule combine des références, et l’espace renvoie l’intersection là où les plages concernées se chevauchent.
La différence entre références relatives et absolues saute aux yeux avec un exemple de taxe. Si le taux de taxe est stocké en F1, utilisez =B2*$F$1 avant de recopier la formule vers le bas. Sans les signes dollar, la référence F1 glisserait vers F2, F3, et ainsi de suite, avec des résultats incorrects, sauf si chaque ligne contient le taux prévu.
Les parenthèses évitent les erreurs plausibles
Excel respecte un ordre de priorité des opérations. Multiplications et divisions sont traitées avant additions et soustractions : =A1+B1*C1 n’est donc pas équivalent à =(A1+B1)*C1. Employez des parenthèses dès que la règle métier compte plus que l’ordre par défaut.
Règle pratique : si une formule traduit une phrase du type « ajouter la remise, puis multiplier par la quantité », écrivez les parenthèses pour que la règle soit lisible directement par quelqu’un d’autre.
Les fonctions clés qui couvrent l’essentiel du travail réel
Un petit groupe de fonctions couvre la majorité des tâches récurrentes. La syntaxe compte, mais le choix des plages et la gestion des données manquantes comptent tout autant.
IF crée un embranchement logique. Utilisez par exemple =IF(C2>100,"Review","Clear") pour renvoyer un statut différent selon que la valeur dépasse ou non le seuil.
Imbrication et formules matricielles expliquées
L’imbrication place une fonction à l’intérieur d’une autre : le résultat d’un calcul interne devient l’entrée d’une règle externe. Par exemple, =IF(SUM(B2:B5)>1000,"Review","Clear") additionne d’abord les valeurs, puis vérifie si le total franchit le seuil. Lisez les formules de l’intérieur vers l’extérieur, comme vous vérifieriez chaque étape d’un calcul de tableur.
Supposons que B2 contienne le type de client, C2 le prix unitaire et D2 la quantité. Une formule de tarification compacte et valide est =ROUND(IF(B2="Member",C2*D2*0.9,C2*D2),2). Elle calcule la valeur de la ligne, applique la remise membre le cas échéant, puis arrondit le résultat. Les règles d’éligibilité portant sur de nombreuses lignes relèvent d’une colonne d’aide ou d’un SUMIFS par plages, où la plage à additionner et les plages de critères sont explicites et vérifiables séparément.
Lisez les couches avant de raccourcir le classeur
Une formule courte n’est pas automatiquement plus simple à maintenir. Quand plusieurs règles s’imbriquent, un relecteur peut peiner à identifier la condition qui a produit un montant inattendu. Dès qu’une formule dépasse quelques couches logiques, des colonnes d’aide rendent chaque étape plus facile à tester, à auditer et à transmettre.
| Étape | Couche fonctionnelle | Rôle | Exemple de résultat |
|---|---|---|---|
| 1 | SUMIFS | Sélectionner les ventes correspondantes | Total des ventes éligibles |
| 2 | IF | Appliquer une règle métier | Valeur remisée ou standard |
| 3 | ROUND | Maîtriser la précision affichée | Montant final |
Les formules matricielles dynamiques renvoient plusieurs résultats depuis une seule cellule. =FILTER(A2:D100,D2:D100="Open") renvoie les enregistrements ouverts et les propage dans les cellules voisines. =SORT(A2:D100,3,-1) trie la plage renvoyée selon sa troisième colonne, tandis que =UNIQUE(B2:B100) crée une liste de valeurs distinctes pour une liste déroulante ou un récapitulatif.
Vérifiez la zone de propagation prévue avant de juger la formule. Une valeur qui bloque une seule cellule de destination peut déclencher #SPILL!, même quand la logique de filtrage ou de tri est correcte. Dégagez l’obstacle, puis validez les lignes renvoyées par rapport aux données sources.
Les classeurs plus anciens utilisent parfois des formules matricielles héritées, validées avec Ctrl+Shift+Enter. Excel les entoure d’accolades dans la barre de formule, accolades que vous ne saisissez pas vous-même. Conservez-les intactes quand vous maintenez des conceptions anciennes, et privilégiez les formules matricielles dynamiques pour les nouveaux travaux quand le classeur les prend en charge.
Pour vous aider à la rédaction, un générateur de formules Google Sheets peut suggérer une syntaxe. Confrontez ses plages, ses conditions et son exemple de résultat au classeur réel avant d’adopter la formule.
Déboguer ses formules quand quelque chose cloche
Un résultat faux ne déclenche pas toujours une erreur visible. Les recommandations de Microsoft sur les formules recensent les pannes classiques : signe égal oublié en tête, parenthèses déséquilibrées, priorité des opérateurs mal comprise, ou cellule traitée comme vide alors qu’elle contient du contenu masqué. Microsoft Press documente 11 types d’erreurs Excel, dont #REF!, #VALUE!, #N/A, #SPILL! et #CALC! : le message affiché est donc un indice précieux.
Commencez par la vérification des erreurs en arrière-plan d’Excel et par l’outil Error Checking. Examinez ensuite les relations de la formule. La documentation Microsoft sur la détection des erreurs de formules recommande d’utiliser Error Checking, Trace Precedents et Evaluate Formula dans le cadre d’un flux de travail structuré.
Lire l’erreur comme un diagnostic
#DIV/0!signale une division par zéro ou par un dénominateur vide.#N/Aindique généralement qu’une recherche n’a trouvé aucune correspondance.#NAME?signale un texte non reconnu, souvent une fonction mal orthographiée ou des guillemets manquants.#NULL!signale une intersection de plages invalide.#NUM!signale une opération numérique invalide.#REF!signifie qu’une référence a été supprimée ou n’est plus valide.#VALUE!indique le plus souvent un type de données incompatible.#GETTING_DATAapparaît pendant qu’Excel récupère des informations.#SPILL!signifie que le résultat dynamique ne peut pas occuper sa plage de sortie.#CALC!signale un problème de calcul, souvent lié à un résultat de tableau non pris en charge.#UNKNOWN!indique qu’Excel ne peut pas identifier le calcul ou le contenu demandé.
Sélectionnez la cellule problématique et appuyez sur F2 pour passer en mode édition. Contrôlez chaque plage, chaque parenthèse et chaque argument. Si la barre de formule devient difficile à manier, Ctrl+Backspace réinitialise la vue d’édition. Vous pouvez aussi sélectionner une sous-expression dans la barre de formule et appuyer sur F9 pour l’évaluer. Appuyez ensuite sur Esc pour ne pas remplacer malencontreusement la formule par la valeur affichée.
Tracer le calcul plutôt que deviner
Trace Precedents dessine des flèches depuis les cellules d’entrée vers la formule sélectionnée. Trace Dependents montre où va le résultat, ce qui est précieux avant de modifier une cellule de synthèse. Evaluate Formula parcourt une expression imbriquée pièce par pièce et vous aide à repérer le terme exact qui bascule d’une valeur cohérente à une erreur.
L’enchaînement est simple : contrôler l’indicateur d’erreur, tracer les entrées, évaluer l’expression, corriger le plus petit élément cassé, puis recalculer. Ne commencez pas par réécrire toute la formule : cela fait souvent disparaître des indices utiles.
Regardez les outils de débogage en action avant de les appliquer à un classeur en production.
Auditer ses formules et s’y fier à grande échelle
Traitez une formule opérationnelle comme du code dont quelqu’un d’autre va hériter. Des recherches indépendantes d’audit de tableurs ont relevé des erreurs dans 0,9 % à 1,8 % des cellules de formules sur 50 classeurs opérationnels, selon la définition de l’erreur retenue par les chercheurs. C’est la forte variation entre classeurs qui retient l’attention : la méthodologie d’audit milite pour une revue systématique au niveau du classeur plutôt que pour des contrôles ponctuels au hasard.
Tester les cas qui font mentir les formules
Avant de partager un rapport, testez délibérément les cellules vides, le texte au milieu de colonnes numériques, les valeurs négatives, les chaînes de longueur nulle et les clés de recherche manquantes. Comparez chaque résultat à un calcul manuel connu. Une formule qui renvoie un nombre plausible peut très bien appliquer la mauvaise condition ou pointer sur la mauvaise période.
Utilisez le Name Manager pour donner aux plages importantes des noms parlants comme ApprovedSales ou TaxRate. Une formule telle que =SUM(ApprovedSales) exprime l’intention plus clairement qu’une longue adresse, à condition que la plage nommée reste soigneusement tenue à jour.
La Watch Window vous aide à surveiller des cellules critiques pendant que vous modifiez d’autres feuilles. Le complément Inquire permet de comparer des versions d’un classeur et d’inspecter les relations, y compris des références qu’une navigation normale ne révèle pas. Sa disponibilité dépend de votre édition d’Excel et de la configuration de votre organisation : vérifiez que l’outil est activé avant de bâtir un processus dessus.
Protéger la logique finalisée
Après la revue, activez la protection de feuille pour empêcher les modifications accidentelles de formules. Masquez les formules sensibles quand c’est justifié, mais rappelez-vous que la protection est un contrôle, pas un substitut à la documentation ni à la gestion des versions.
Imaginez un rapport de revenus mensuel où l’insertion d’une colonne laisse une formule pointer vers le mauvais champ. Le résultat peut encore ressembler à un nombre valide. Une plage nommée, un traçage des antécédents, une comparaison avec la version précédente et une ligne de test au résultat attendu connu auraient exposé le problème pendant la construction, pas après la réunion de direction.
Votre checklist de création de formules
Utilisez cette checklist à chaque fois que vous ajoutez une formule à un classeur en production. Elle transforme la création de formules en petit processus de revue plutôt qu’en exercice de devinette.
- Commencez par le signe égal : sélectionnez la cellule cible voulue et débutez par
=. Cela évite qu’Excel stocke l’expression sous forme de texte. - Définissez les entrées : notez les cellules, les plages, les critères et le type de résultat attendu. Décidez si le résultat doit être un nombre, du texte, une date ou une plage propagée.
- Choisissez la fonction : commencez dans une cellule de brouillon. Optez pour
SUM,IF,SUMIFS,XLOOKUP,TEXT,DATEou toute autre fonction qui correspond à la règle. - Verrouillez les références : ajoutez
$aux taux, aux tables de recherche et aux cellules de critères fixes avant de copier. Vérifiez délibérément les références mixtes commeA$1et$A1. - Testez la logique : comparez la formule à une valeur connue, puis testez les vides, le texte, les zéros, les valeurs négatives et les correspondances manquantes. Utilisez Error Checking et Evaluate Formula si le résultat reste obscur.
- Validez proprement : ajoutez un bref commentaire de cellule ou une note à proximité décrivant l’intention, mettez le résultat en forme et protégez la formule finie si d’autres utilisateurs ne doivent pas la modifier.
Ces étapes ciblent les défaillances qui coûtent le plus cher : des plages non ancrées qui bougent à la copie, des nombres d’arguments incorrects et des résultats silencieusement faux qui resurgissent des mois plus tard. Si vous utilisez une IA pour rédiger une formule, appliquez les mêmes contrôles. Les recommandations de Microsoft pour Excel Copilot demandent aux utilisateurs de relire la formule suggérée et de confirmer que ses références et sa logique correspondent au jeu de données avant de l’appliquer. Pour les workflows Google Sheets, utiliser l’IA dans Google Sheets peut aider à la rédaction et à l’analyse, mais la vérification reste votre responsabilité.
GPT Workspace fonctionne au sein des applications Google Workspace et peut aider à rédiger des formules Sheets, à analyser des plages sélectionnées, à nettoyer des données et à transformer des demandes en langage naturel en logique de tableur. Visitez GPT Workspace pour tester un workflow où la génération de formules et la revue du tableur se déroulent dans le même espace de travail, puis appliquez la checklist de validation avant de publier votre prochain rapport.