Simplifier ses formules Excel : 6 techniques concrètes

Simplifier ses formules Excel, ça ne veut pas dire tout réécrire au hasard un dimanche soir. C'est reprendre une formule qui fonctionne déjà, mais qui ressemble à un plat de spaghettis, et la rendre lisible sans toucher au résultat qu'elle renvoie. Biscuit l'a déjà cherchée pour vous : voici six réflexes concrets, applicables dès cet après-midi sur votre propre fichier, sans rien casser au passage.

Votre formule marche, alors pourquoi ça vous gêne ?

Le signe classique : vous ouvrez un fichier écrit six mois plus tôt et vous passez trois minutes sur une seule cellule avant de comprendre ce qu'elle fait. Ou vous copiez une formule vers une nouvelle colonne et elle casse en silence, parce qu'une référence de ligne était figée en dur à l'intérieur.

Simplifier une formule ne change pas ce qu'elle produit. Ça change la structure pour que le résultat reste correct quand l'environnement bouge : nouvelles lignes, nouvelles colonnes, feuille renommée.

Passez votre tour si : votre fichier sert à un calcul ponctuel que personne d'autre ne touchera. Ce travail a un coût (du temps, de la perturbation), et si le bénéfice est nul, le retour sur investissement l'est aussi.

Ça vaut le coup quand : plusieurs personnes utilisent le fichier, vous le mettez à jour régulièrement, ou les formules s'enchaînent sur plusieurs feuilles.

Quelques signaux qui méritent d'agir :

• Une formule plus large que la colonne à 100% de zoom

• Plus de deux niveaux de IF imbriqués

• Des chiffres codés en dur qui reviennent plus de trois fois

• Des collègues qui vous demandent d'expliquer une cellule au lieu de la lire eux-mêmes

Un seul de ces signaux suffit à programmer une session de nettoyage.

Extraire la méthode : découper une formule en étapes nommées

C'est la technique la plus utile de toutes, en programmation comme dans un tableur. L'idée : si une formule fait trois choses à la fois, on la sépare en trois cellules, une par étape, puis on assemble les résultats.

Avant (une cellule qui fait tout) :

=IF(AND(A2<>"",DATEDIF(A2,TODAY(),"Y")>30),"Senior","Junior")

Après :

• Cellule C2 : =DATEDIF(A2,TODAY(),"Y"), nommée « Âge en années »

• Cellule D2 : =AND(A2<>"",C2>30), nommée « Est senior ? »

• Cellule E2 : =IF(D2,"Senior","Junior"), le résultat final

Voilà ce que ça donne dans une vraie cellule : chaque étape se lit toute seule. Si le seuil passe de 30 à 25, vous changez un chiffre dans une cellule. Si le DATEDIF plante parce qu'une date manque, vous voyez exactement où.

Le compromis : vous utilisez plus de colonnes. Sur un tableau déjà serré, ça peut sembler du gaspillage. Utilisez une zone de cellules intermédiaires sur une feuille à part si la place manque. Nommez cette feuille « Aides » ou « Calculs » pour que l'organisation saute aux yeux de quiconque ouvre le fichier.

Ça fonctionne pareil dans Excel et dans Sheets, aucune restriction de version sur l'usage de cellules intermédiaires.

Remplacer une valeur intermédiaire par un calcul direct

Une valeur intermédiaire, dans un tableur, c'est une cellule que vous remplissez juste pour arriver à l'étape suivante, sans que rien d'autre ne la référence directement ailleurs.

Parfois ces cellules sont utiles (voir plus haut). Mais quand vous avez une chaîne de cinq cellules d'aide dont seule la dernière compte vraiment, et que les intermédiaires ne changent jamais, vous pouvez les regrouper en une seule formule.

Trois cellules d'aide qui construisent un résultat :

• =B2*C2, le sous-total

• =D2*0.2, le montant de la taxe

• =D2+E2, le total

Regroupé :

=(B2*C2)+(B2*C2*0.2)

Ou avec une plage nommée pour le taux de taxe :

=B2*C2*(1+TauxTVA)

La règle : si une cellule d'aide n'est consommée que par une seule autre cellule, demandez-vous si la formule est assez courte pour être insérée directement. Si oui, insérez-la. Sinon, gardez la cellule d'aide et nommez-la correctement.

Où placer la limite : une formule qui dépasse 80 caractères est en général trop longue pour être insérée telle quelle. À ce stade, la méthode « Extraire » vue plus haut vous rendra service. Gardez les cellules d'aide quand elles servent vraiment la lisibilité, retirez-les quand elles ne sont que du bruit.

Protéger vos données de référence

Dans un tableur, protéger vos données de référence, c'est regrouper vos valeurs fixes (taux, seuils, valeurs de référence) à un seul endroit au lieu de les éparpiller en dur dans les formules.

C'est la formule que tout le monde oublie et que tout le monde cherche : celle qui évite de courir après un chiffre planqué dans 47 cellules différentes.

Un taux de TVA codé en dur à 0.2 dans 47 formules d'un classeur, c'est un piège de maintenance. Le taux change, il faut retrouver les 47. Vous en ratez trois, vous avez une erreur de calcul.

La correction :

1. Créez une feuille Réglages avec une cellule nommée TauxTVA = 0.2

2. Référencez-la dans chaque formule : =B2*TauxTVA

3. Quand le taux change, vous mettez à jour une seule cellule. Tout le reste suit.

Ça vaut aussi pour les références de date (la date du jour comme ancrage fixe), les multiplicateurs de devise et les seuils de catégorie. Si une valeur apparaît plus de deux fois et peut changer un jour, elle mérite une plage nommée.

Comment créer une plage nommée :

• Excel : onglet Formules > Gestionnaire de noms > Nouveau

• Google Sheets : Données > Plages nommées

Le nom compte. TauxTVA est clair. TVA1 ne l'est pas. tmp_3 non plus. Choisissez des noms que quelqu'un qui n'a pas écrit la formule comprend en trois secondes.

Éliminer les doublons : le principe DRY appliqué aux formules

DRY, ne vous répétez pas est l'un des principes les plus cités en développement, et il s'applique directement aux tableurs. Si vous avez copié le même VLOOKUP sur 15 colonnes avec de petites variations, un problème de maintenance vous attend au tournant.

Deux approches selon votre situation :

Excel 365 / Google Sheets : utilisez XLOOKUP ou INDEX-MATCH avec extension de tableau pour récupérer plusieurs colonnes dans une seule formule.

=XLOOKUP(A2,TableRecherche[ID],TableRecherche[[Nom]:[Statut]])

Ça renvoie à la fois le nom et le statut d'un coup, sans copier-coller sur plusieurs colonnes.

Excel plus ancien (2016 ou antérieur) : créez un seul MATCH dans une cellule d'aide, puis référencez cette position avec INDEX :

• G2 : =MATCH(A2,TableRecherche[ID],0), un seul MATCH pour toutes les colonnes

• H2 : =INDEX(TableRecherche[Nom],G2)

• I2 : =INDEX(TableRecherche[Statut],G2)

Vous référencez toujours le même résultat MATCH partout. Si la colonne de recherche change, vous mettez à jour une seule formule.

Une note de version utile : XLOOKUP est arrivé dans Excel 2019 pour les abonnés entreprise et Excel 365. Si votre équipe est sur Excel 2016, l'approche INDEX-MATCH reste la bonne. Sheets propose XLOOKUP depuis 2022 sur tous les forfaits.

Remplacer un IF imbriqué par quelque chose de plus lisible

Les IF imbriqués, c'est là que les tableurs deviennent illisibles. Trois niveaux de profondeur et vous perdez la plupart de vos lecteurs, vous y compris dans six mois.

Avant (chaîne de IF imbriqués) :

=IF(A2>10000,"A",IF(A2>5000,"B",IF(A2>1000,"C","D")))

Option 1 : IFS (Excel 2016+ / Sheets)

=IFS(A2>10000,"A",A2>5000,"B",A2>1000,"C",TRUE,"D")

Plus plat, mais encore quatre conditions dans une seule formule. Ça passe.

Option 2 : VLOOKUP avec une table de référence (l'approche sous-estimée)

Créez une petite table de correspondance :

• 10001 et plus : Catégorie A

• 5001 à 10000 : Catégorie B

• 1001 à 5000 : Catégorie C

• 0 à 1000 : Catégorie D

Puis : =VLOOKUP(A2,TableSeuils,2,TRUE)

Le TRUE (correspondance approximative) parcourt la table triée et renvoie la dernière catégorie qui correspond. La logique est maintenant dans la table, plus enfouie dans la formule. Un seuil change ? Vous modifiez une ligne de la table.

Ça vaut le coup pour : l'approche par table de correspondance, qui rend la logique vérifiable et visible pour tout le monde, même pour ceux qui ne lisent pas les formules. Vous pouvez zapper si vous êtes pressé : IFS suffit pour trois conditions ou moins.

La fonction SWITCH est une autre option, disponible dans Excel 2019+ et Sheets, utile quand vous comparez des valeurs exactes plutôt que des plages. Elle se lit plus proprement qu'IFS pour les correspondances exactes.

Réécrire plutôt que rafistoler

Parfois la formule n'est pas fausse, c'est juste la mauvaise approche depuis le départ. Vous héritez d'une formule de 200 caractères qui concatène du texte avec CONCATENATE et &, et vous devez ajouter un champ de plus. Le bon réflexe n'est pas de la rallonger. C'est de la réécrire avec TEXTJOIN.

Avant :

=A2&" | "&B2&" | "&C2&" | "&D2

Après :

=TEXTJOIN(" | ",TRUE,A2:D2)

Si votre tableau change de tête demain, cette formule suit. TEXTJOIN gère les cellules vides, gère les tableaux, et ne cassera pas quand vous ajouterez une colonne E.

La même logique s'applique à :

• Remplacer VLOOKUP par XLOOKUP quand vous avez besoin de correspondance exacte et de gestion des valeurs manquantes

• Remplacer l'habillage IFERROR par la gestion d'erreur native des fonctions récentes

• Remplacer les formules matricielles Ctrl+Maj+Entrée par les tableaux dynamiques natifs d'Excel 365

Le même principe s'étend à plus grande échelle : si vous entretenez un pipeline de données devenu un enchevêtrement de références INDIRECT, de connexions externes et de plages nommées qui pointent vers d'autres plages nommées, parfois le bon réflexe consiste à sortir la logique du tableur (SQL, Python, Power Query) et à ne garder le tableur que pour l'affichage. C'est une décision plus lourde, mais elle appartient à la même famille.

Avant de vous lancer : trois réflexes à avoir

Simplifier ses formules Excel ne sert à rien si vous cassez quelque chose que vous ne pouvez pas récupérer. Concrètement :

1. Une copie de sauvegarde. Avant toute session de nettoyage, enregistrez une copie datée du classeur. Pas un « Enregistrer sous » dans le même dossier : un autre emplacement (sauvegarde cloud, email à vous-même, peu importe). Vous vous remercierez le jour où quelque chose tourne mal.

2. Savoir ce que les résultats actuels doivent donner. Avant de toucher quoi que ce soit, notez les résultats clés : totaux, chiffres de synthèse, valeurs de cellules importantes. Comparez-les aux mêmes cellules après chaque modification. Un contrôle de 10 secondes qui repère immédiatement une régression.

3. Un changement à la fois. N'extrayez pas une méthode, n'encapsulez pas trois plages nommées et n'aplatissez pas un IF imbriqué dans la même session. Si quelque chose casse, vous devez savoir quel changement en est la cause. Une chose, une vérification, puis la suivante.

Ces trois réflexes prennent cinq minutes à mettre en place. Ils font gagner bien plus que cinq minutes à chaque session où quelque chose d'imprévu se produit.

Quand arrêter de simplifier

Le risque avec ce genre de nettoyage, c'est le perfectionnisme. Vous pouvez passer un après-midi entier à réorganiser un classeur qui fonctionnait déjà, pour finir avec quelque chose à peine plus propre au prix d'une demi-journée.

Fixez-vous une limite de temps avant de commencer : 45 minutes pour cette session. Priorisez les formules qui posent vraiment problème : celles que les gens lisent de travers, celles qui cassent en usage normal, celles qui bloquent une fonctionnalité que vous devez ajouter.

Tout le reste : laissez tomber. Une formule qui fonctionne, que personne ne touche, qui n'a pas produit d'erreur depuis un an, c'est très bien comme ça. Suffisamment bien est un point d'arrêt tout à fait légitime.

Une façon utile de voir les choses : il ne s'agit pas de rendre le classeur parfait. Il s'agit de rendre le prochain changement plus facile. Demandez-vous avant chaque modification : est-ce que ça rend la prochaine tâche plus rapide ou plus sûre ? Si oui, faites-le. Sinon, arrêtez-vous là.