Voici pourquoi elles sont meilleures que les formules longues

Arrêtez d'essayer de gagner un prix Excel Hero avec des formules de dix lignes. Ils sont impossibles à déboguer et encore plus difficiles à expliquer à vos collègues. Utiliser des colonnes d'assistance ne signifie pas que vous êtes un débutant, cela signifie que vous êtes un ingénieur qui construit un système qui dure réellement. Voici pourquoi la solution la plus simple est en réalité la meilleure.

Mieux vaut la clarté que la complexité

Les méga-formules sont les boîtes noires d'Excel. Les données entrent, un résultat apparaît, mais le « pourquoi » est obscurci. Lorsque vous enfouissez toute votre logique dans une seule cellule, vous créez une feuille de calcul impossible à auditer et encore plus difficile à maintenir.

Imaginez que vous travaillez sur ce tableau Excel (Ctrl+T) et que vous devez calculer une commission complexe. Les vendeurs gagnent un taux de base de 5 %, 10 % ou 15 % en fonction de leur volume de ventes, plus un bonus supplémentaire de 2 % s'ils concluent plus de 10 transactions.

Dans une feuille de calcul « héros », le calcul fait tout en même temps, créant un vilain mur de texte :

=ROUND(((@(Total_Sales))*IF((@(Total_Sales))>=50000,0.15,IF((@(Total_Sales))>=20000, 0.1,0.05)))+IF((@(Deals_Closed))>10,(@(Total_Sales))*0.02,0),2)

Dans cette formule unique, Excel exécute deux ensembles différents de tests IF logiques, effectue des multiplications et des additions et arrondit au centime le plus proche. Si le chiffre final semble erroné, il est presque impossible de savoir laquelle de ces opérations a échoué.

Il est important de faire la distinction entre une formule longue et une formule imbriquée. De nombreuses fonctions modernes nécessitent plusieurs arguments pour fonctionner correctement, ce qui allonge naturellement la formule. C'est très bien! Le problème survient lorsque vous commencez à imbriquer plusieurs tests logiques les uns dans les autres.

Voyons maintenant comment cela peut être amélioré avec les colonnes d'assistance. Le premier calcule le taux de commission :

=IFS((@(Total_Sales))>=50000,0.15,(@(Total_Sales))>=20000,0.1,(@(Total_Sales))>0,0.05)

Et le second calcule le bonus appliqué :

=IF((@(Deals_Closed))>10,(@(Total_Sales))*0.02,0)

En conséquence, la colonne Total_Payout devient un ajout simple et lisible de vos étapes modulaires :

=ROUND(((@(Total_Sales))*(@(Comm_Rate)))+(@(Bonus_Applied)),2)

En faisant cela, vous transformez une chaîne dense en une liste de contrôle logique.

Les colonnes d'assistance permettent de gagner du temps en facilitant la recherche des erreurs

La plus grande faiblesse d’une longue formule imbriquée dans Excel est l’erreur silencieuse. Lorsqu'une chaîne massive échoue, Excel ne vous indique pas instantanément quelle partie spécifique est cassée : vous êtes obligé d'utiliser l'outil encombrant d'évaluation de la formule, en parcourant une petite fenêtre un clic à la fois pour trouver le coupable. Les colonnes d'assistance déroulent efficacement ce processus de débogage sur votre grille, transformant un calcul caché en piste d'audit.

Imaginez que votre Total_Payout renvoie un nombre inattendu pour un artiste très performant. Étant donné que vous avez utilisé des colonnes d'assistance, vous n'avez pas besoin de déconstruire une méga-formule : il vous suffit de parcourir la ligne comme une liste de contrôle :

• Le Total_Sales est-il un numéro valide ? Oui.

• Le Comm_Rate affiche-t-il le pourcentage correct pour ce niveau ? Oui.

• Le Bonus_Applied calcule-t-il 12 transactions ? Non.

Ainsi, en sélectionnant la cellule qui calcule le bonus, vous pouvez consulter la formule et voir instantanément qu'elle indique par erreur >20 au lieu de >10, c'est donc une solution rapide pour modifier le calcul.

La logique est plus utile lorsqu'elle est cliquable

Les colonnes d'assistance dans Excel transforment les mathématiques invisibles en métadonnées utilisables. Si votre logique est enfouie dans une formule imbriquée, elle n'existe que pour cette cellule. Une fois que vous déplacez cette logique dans sa propre colonne, elle devient un outil avec lequel vous pouvez interagir.

Dans votre tableau de commissions, vous ne pouvez pas facilement filtrer votre liste pour voir la logique cachée dans une formule de paiement. Cependant, dans le tableau avec les colonnes d'assistance, cette logique devient instantanément cliquable. Vous pouvez trier selon celui-ci, filtrer pour des niveaux spécifiques ou l'utiliser comme segment dans un rapport.

Par exemple, ici, j'ai filtré la colonne Bonus_Applied pour afficher uniquement ceux qui étaient qualifiés pour un bonus de plus de 1 800 $.

Plus important encore, les colonnes d'assistance servent de pont vers les tableaux croisés dynamiques. Pour voir le total des bonus payés par niveau de vente, le niveau doit exister sous forme de colonne physique dans vos données sources : un tableau croisé dynamique ne peut pas accéder à l'intérieur d'une méga-formule pour extraire les calculs à mi-étape.

Lorsque vous divisez les formules en étapes individuelles, vous ajoutez de la valeur à votre ensemble de données en créant des catégories pour une analyse plus approfondie.

Les feuilles de calcul efficaces calculent une seule fois et se réfèrent souvent

L'efficacité dans Excel ne dépend pas seulement de la rapidité avec laquelle vous pouvez saisir des données, mais aussi de la difficulté avec laquelle le logiciel doit travailler.

De nombreuses personnes ralentissent considérablement l'exploration de leurs classeurs en demandant à Excel d'effectuer le même calcul complexe des dizaines de fois dans une seule formule imbriquée. Si un test logique détermine un taux et que vous référencez ce test cinq fois dans une chaîne sur 10 000 lignes, Excel effectue 50 000 calculs pour quelque chose qui ne doit réellement se produire qu’une seule fois. Ceci est particulièrement problématique avec les fonctions volatiles comme AUJOURD'HUI ou OFFSET, qui recalculent chaque fois qu'une cellule est modifiée.

Cependant, en plaçant un test logique approfondi dans une seule colonne d'assistance, vous le calculez une fois, puis vous référencez simplement ce résultat. Cela empêche Excel de gaspiller des cycles de processeur avec des calculs redondants, ce qui permet à votre classeur de rester vif même à mesure que vos données augmentent.

Si vous n'aimez pas l'encombrement des colonnes d'assistance, ne les cachez pas : regroupez-les. Pour ce faire, sélectionnez les colonnes et appuyez sur Maj+Alt+Flèche droite (ou cliquez sur Données > Groupe). Cela ajoute une bascule de regroupement au-dessus de vos en-têtes, sur laquelle vous pouvez cliquer pour réduire votre échafaudage pour un aspect plus net tout en gardant la logique accessible pour les audits. Pour les développer à nouveau, cliquez simplement à nouveau sur la bascule.

Conseil de pro : la fonction LET offre un juste milieu moderne

Si vous absolument doit gardez votre logique contenue dans une seule cellule, la fonction LET est votre meilleure amie. Il vous permet de créer des colonnes d'assistance virtuelles dans une formule en attribuant des noms à vos étapes de calcul, comme vous le feriez avec des en-têtes de colonnes. Cela signifie que vous pouvez bénéficier de la lisibilité des colonnes d'assistance sans augmenter l'empreinte physique de votre feuille de calcul.

La fonction LET est disponible pour ceux qui utilisent Excel 2021 ou version ultérieure, Excel pour Microsoft 365, Excel pour le Web et les versions à jour des applications mobiles et tablettes Excel.

Au-delà de l’organisation, dans de nombreux cas, le LET peut offrir un avantage en termes de performances en éliminant les calculs redondants au sein de cette formule spécifique. En utilisant le même exemple de commission que dans les captures d'écran ci-dessus, cette formule LET définit le taux et le bonus en tant que variables avant d'effectuer le calcul final :

=LET(
Rate,IFS((@(Total_Sales))>=50000,0.15,(@(Total_Sales))>=20000,0.1,(@(Total_Sales))>0,0.05),
Bonus,IF((@(Deals_Closed))>10,(@(Total_Sales))*0.02,0),
ROUND(((@(Total_Sales))*Rate)+Bonus,2)
)

C'est nettement mieux qu'une chaîne IF imbriquée car la logique est identifiée à l'avance. Cependant, n'oubliez pas le compromis : bien que LET soit efficace et lisible, ces variables n'existent que dans la portée de la formule : elles ne créent pas de points de données physiques que vous pouvez directement filtrer, trier ou utiliser dans un tableau croisé dynamique.

---

Les formules fantaisistes peuvent paraître impressionnantes, mais les systèmes simples fonctionnent réellement. Une feuille de calcul professionnelle doit être fiable, facile à auditer et prête à être analysée. Si votre barre de formule est encombrée, rendez-vous service : divisez-la en colonnes d'aide. En adoptant ces habitudes modulaires, vous rejoindrez le groupe d'élite des utilisateurs qui évitent les 90 % des feuilles de calcul Excel contenant des erreurs.