Excel : Convertir une formule en sa valeur : remplacer une formule dans une cellule (ou un champ de cellules) en la valeur résultant de la formule

cr  01.12.2019      r+  01.11.2024      r-  01.11.2024      Pierre Pinard.         (Alertes et avis de sécurité au jour le jour)

Excel : convertir une formule en sa valeur calculée.

1. Attention, cette opération est définitive. Il ne sera plus possible de revenir, plus tard, en arrière. La seule manière de récupérer la formule originelle est de cliquer sur « Annuler » immédiatement (avant tout enregistrement de la modification).

2. Attention, l'écriture de ce genre de formules pouvant être sujette à erreurs, et le retour en arrière n'étant pas possible, il est vivement conseillé de, soit :

   • Travailler à la mise au point de la formule dans une feuille Excel de brouillon.

   • Faire une sauvegarde de la seule formule dans un fichier de sauvegarde que l'on ferme. Une seule formule, généralement la première, est sauvegardée et pourra être reproduite plus tard puis déployée sur tous les champs concernés.

   • Faire une copie de sécurité de la feuille Excel originelle avant de tester.

Exemple d'opérations : nous avons une formule, en colonne A d'un tableau Excel, qui donne différentes valeurs aux cellules où elle se trouve en fonction d'une condition (un paramètre) en colonne B. C'est une fonction à conditions multiples imbriquées (ce type de formule est décrit dans l'article « Excel : Conditions multiples », cette description étant reprise ici).

Nous souhaitons remplacer la formule en A directement par la valeur résultante, pour diverses raisons :

• Gagner en place disque (si la valeur de remplacement est plus courte que la formule).

• Gagner en vitesse d'exécution (même si cela est totalement marginal).

• Mettre la liste des valeurs en base de données externe facile à manipuler/corriger/mettre à jour.

• Vos autres raisons...

Les manipulations :
La formule à copier en A1 (ici, c'est une formule un peu longue de 64 conditions « SI » imbriquées [« imbrication » signifiant joindre plusieurs fonctions au sein d’une même formule]) étant un peu longue, elle est en note 3 en bas de cette page. Elle donne une valeur à la cellule en A1 sous la condition exprimée en B1. La formule en A1 disparaît.

Il y a 64 choix dans la formule. En B (B1 à B5 pour notre test), saisissez, au hasard, des valeurs entre 1 et 64 (ce n'est pas vérifié).

Recopiez A1 en A2 à A5.

Le but est de remplacer la formule en A par la valeur fixe codée dans la formule de remplacement, conditionnée par le paramètre en B. Si le paramètre n'est pas trouvé en B, une erreur est affichée.

• La formule disparaît. Elle a fait son office et plus rien ni personne n'en a, ni n'en aura besoin.

• La condition en B, si elle n'est pas utilisée par ailleurs, ne sert plus non plus.

La progression :

1. La formule d'origine, en A1, est remplacée par la formule au point 3 des remarques, en fin de cet article.

2. Sélectionner tout le champ de cellules où reproduire cette formule de conversion. Dans cet exemple, on recopie A1 en A2 à A5.

3. Sélectionner tout le champ concerné (ici, A1 à A5).

   
   

4. Clic sur le bouton « Copier » d'Excel :

   
   

5. Clic sur le bouton « Coller » d'Excel :

   
   

6. Excel vous demande quel type de collage effectuer. Choisissez « Valeur ». Les formules disparaissent et sont remplacées par les valeurs.

7. Éventuellement, si la colonne B n'est pas utilisée par ailleurs dans votre travail, elle peut être vidée, voire supprimée.

Remarques :

1. Dans une formulation de « SI » imbriqués, la gestion des parenthèses peut être cauchemardesque si l'on n'y prête pas attention. L'ouverture des parenthèses se fait immédiatement après chaque « SI » alors que les fermetures des parenthèses sont toutes ensemble en fin de formule, pour un nombre de fermetures égal au nombre total d'ouvertures.

2. Cette formulation en « SI » imbriqués est utilisable avec (dernière vérification au 04.08.2024) :

   • Excel pour Microsoft 365

   • Excel pour Microsoft 365 pour Mac

   • Excel pour le web

   • Excel 2021

   • Excel 2021 pour Mac

   • Excel 2019

   • Excel 2019 pour Mac

   • Excel 2016

   • Excel Web App

   • Excel pour Windows Phone 10

3. Dans une formule de « SI » imbriqués, le nombre maximum de « SI » est de 64. Au-delà, vous avez un message d'erreur. La formule suivante avec 64 « SI » imbriqués est sans faute :
   =SI(B1=1;"Ail semoule";SI(B1=2;"Aneth (sommité)";SI(B1=3;"Badiane (Anis étoilé)";SI(B1=4;"Baies roses";SI(B1=5;"Cannelle entière";SI(B1=6;"Cannelle moulue ";SI(B1=7;"Carvi";SI(B1=8;"Chili en poudre";SI(B1=9;"Ciboulette";SI(B1=10;"Citron (zestes)";SI(B1=11;"Citronnelle";SI(B1=12;"Colombo";SI(B1=13;"Coriandre en feuilles";SI(B1=14;"Coriandre entière";SI(B1=15;"Coriandre moulue";SI(B1=16;"Cumin entier";SI(B1=17;"Curcuma";SI(B1=18;"Curry de la Mer";SI(B1=19;"Curry doux";SI(B1=20;"Curry Rouge Piquant";SI(B1=21;"Curry vert Thaï";SI(B1=22;"Délice de Curcuma";SI(B1=23;"Échalote";SI(B1=24;"Estragon";SI(B1=25;"Gingembre moulu";SI(B1=26;"Graines de pavot bleu";SI(B1=27;"Graines de sésame (Trois sésames)";SI(B1=28;"Graines de Sésame Noir";SI(B1=29;"Herbes de Provence";SI(B1=30;"Laurier";SI(B1=31;"Massalé (Garam masala)";SI(B1=32;"Mélange Curcuma Latte";SI(B1=33;"Mélange Gravlax";SI(B1=34;"Mélange méditerranéen";SI(B1=35;"Mélange mexicain";SI(B1=36;"Mélange pour Chocolat chaud épicé";SI(B1=37;"Mélange pour Couscous";SI(B1=38;"Mélange pour Grillades";SI(B1=39;"Mélange pour Tajine";SI(B1=40;"Mélange Thaï pour Wok";SI(B1=41;"Moutarde du Canada (graines)";SI(B1=42;"Moutarde en grains";SI(B1=43;"Noix de gingembre entières";SI(B1=44;"Noix de muscade moulue";SI(B1=45;"Paprika Fumé au Bois de Chêne";SI(B1=46;"Paprika précieux doux";SI(B1=47;"Persil";SI(B1=48;"Piment de Cayenne Moulu";SI(B1=49;"Piment d'Espelette AOP";SI(B1=50;"Piment Langues d'oiseaux moulus";SI(B1=51;"Piments Langue d'Oiseau entiers (Pili-Pili)";SI(B1=52;"Poivre et baies";SI(B1=53;"Poivre noir concassé";SI(B1=54;"Poivre noir de Kampot";SI(B1=55;"Poivre noir en grains";SI(B1=56;"Poivron";SI(B1=57;"Quatre épices";SI(B1=58;"Ras el-Hanout (Le meilleur de la maison)";SI(B1=59;"Romarin";SI(B1=60;"Safran en Filaments";SI(B1=61;"Sel aromatisé au cèleri";SI(B1=62;"Sésame (graines)";SI(B1=63;"Sucre à la Cannelle";SI(B1=64;"Tandoori (Préparation pour sauce)";SI(B1=65;"Vanille Bourbon de Madagascar en poudre")))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))

4. Si vous avez besoin de plus de 64 conditions, il existe d'autres fonctions de type « SI » à partir d'Excel 19.

5. Dans une formule de « SI » imbriqués, faisant des remplacements de textes, attention à ce que le texte de remplacement ne contienne aucun guillemet de type " (" "), sinon la formule est erronée. Les autres graphies de guillemets, s'il en faut, ne posent aucun problème : “ ”; ‘ ’; ‹ ›; ' ' (ou des parenthèses).

6. La formule utilisée ici pèse 2043 caractères. Chaque fois qu'elle est utilisée ici, elle est remplacée par une valeur textuelle de 6 à 43 caractères. Le gain moyen en place disque est donc d'environ 2020 caractères. Si elle est utilisée une seule fois dans, par exemple, une base de données de 10.000 recettes de cuisine, c'est 2020 caractères * 10.000 qui sont « économisés » (20.200.000 caractères !).

Note : on se demandera toujours pourquoi Microsoft organise ses icônes « Copier » / « Coller », dont la logique d'usage est « Copier » puis « Coller », en sens inverse (« Coller » puis « Copier »), du moins dans les langues dont l'écriture et la lecture se font de gauche à droite (environ 6 milliards d'habitants sur terre sur les environ 8 milliards totaux).

1. microsoft.com (Fr) : Microsoft Excel

2. microsoft.com (Fr) : Microsoft Office

3. microsoft.com (Fr) : Excel Epoch

4. microsoft.com (Fr) : Excel - Convertir une formule en sa valeur