Comment utiliser la fonction VALEUR dans Microsoft Excel

N'écoutez personne qui vous dit que la fonction VALEUR dans Microsoft Excel est obsolète. Vous aurez probablement rarement besoin de l'utiliser seul, mais lorsqu'il est combiné avec d'autres fonctions, il est toujours aussi puissant aujourd'hui que lors de son introduction en 2007.

La fonction VALUE : syntaxe et introduction

La fonction VALEUR d'Excel n'a qu'un seul argument :

=VALUE(a)

où un est la valeur stockée sous forme de texte que vous souhaitez convertir en nombre. Cela peut être codé en dur entre guillemets doubles ou une référence à une cellule contenant la valeur à convertir.

La fonction VALEUR renvoie une #VALEUR ! erreur si le texte ne peut pas être converti en nombre.

Dans sa forme la plus simple, la fonction VALEUR convertit les nombres stockés sous forme de texte en vraies valeurs numériques. Ceci est particulièrement utile si vous avez importé des données d'une autre source et qu'Excel interprète à tort les valeurs numériques comme du texte. De même, les nombres peuvent être stockés sous forme de texte si vous copiez et collez des données dans Excel, et le programme ne reconnaît pas les valeurs comme des nombres. Dans ces deux cas, vous pouvez utiliser la fonction VALEUR pour dupliquer les données dans une autre cellule au format numérique correct.

La fonction peut également être utilisée pour convertir d'autres chaînes de texte, telles que des chaînes de devises (telles que 10 000 $), des pourcentages (telles que 35,7 %) et des heures (telles que 14h00), en leurs équivalents numériques.

Cependant, Excel reconnaît généralement les nombres stockés sous forme de texte et vous offre la possibilité de les convertir en valeurs numériques sans utiliser de fonction du tout. Comme vous pouvez le voir dans la capture d'écran ci-dessous, Excel ajoute une balise verte aux cellules contenant cette erreur.

Vous savez qu'une valeur est stockée sous forme de texte lorsqu'elle est alignée à gauche dans une cellule. Les valeurs numériques sont alignées à droite dans Excel par défaut.

Si vous survolez ensuite l'une de ces cellules, ou sélectionnez toutes les cellules concernées et survolez la sélection, vous pouvez cliquer sur l'icône d'erreur et choisir « Convertir en numéro » pour résoudre le problème.

À ce stade, vous vous demandez peut-être : « À quoi sert la fonction VALEUR si Excel peut faire le gros du travail à ma place ? » En fait, la fonction VALEUR reste extrêmement utile dans certains contextes. Plus précisément, je vais vous montrer à quel point cela s'avère utile lorsque vous utilisez des fonctions de texte pour organiser et ranger vos figures à des fins d'analyse.

Lorsque des nombres sont combinés avec des lettres dans une cellule, Excel les stocke sous forme de valeurs de texte. Par exemple, les codes produits de ce tableau Excel comprennent une lettre suivie de trois chiffres, ce qui signifie qu'ils sont considérés comme du texte.

Votre objectif est d'extraire les nombres de chaque code produit sous forme de valeurs numériques dans la colonne B afin que vous puissiez les utiliser dans des formules ultérieures.

La première étape consiste à utiliser la fonction DROITE pour extraire les nombres des codes :

=RIGHT((@Product),3)

où (@Produit) est la colonne contenant la chaîne dont vous souhaitez extraire les caractères, et 3 indique à Excel d'extraire les trois caractères les plus à droite.

Cependant, comme la fonction RIGHT est une fonction de texte, les nombres que vous avez extraits sont stockés sous forme de texte. Cela signifie que vous ne pourrez pas les utiliser comme valeurs numériques dans les formules. Pour résoudre ce problème, imbriquez la formule entière dans la fonction VALEUR :

=VALUE(RIGHT((@Product),3))

Désormais, les valeurs à trois chiffres sont stockées sous forme de nombres.

Vous pouvez utiliser le même principe avec les fonctions LEFT et MID. Ici, VALUE et LEFT sont utilisés pour extraire les trois nombres au début des codes sous forme de valeurs numériques :

=VALUE(LEFT((@Product),3))

où (@Produit) est la colonne contenant la chaîne d'origine, 3 indique à Excel d'extraire les trois caractères les plus à gauche et la fonction VALEUR transforme ces caractères des valeurs de texte en valeurs numériques.

Cette fois, les trois caractères du milieu sont extraits grâce à la fonction MID, avant d'être convertis en valeurs numériques grâce à la fonction VALEUR :

=VALUE(MID((@Product),2,3))

où (@Produit) est la colonne contenant la chaîne dont vous souhaitez extraire un certain nombre de caractères, 2 indique à Excel de démarrer cette extraction au deuxième caractère, 3 indique à Excel d'extraire trois caractères du point de départ et la fonction VALEUR transforme les caractères extraits en valeurs numériques.

La fonction VALEUR peut vous faire gagner beaucoup de temps lorsque vous cherchez à ranger des données désorganisées et mal structurées dans votre feuille de calcul.

Supposons que vous ayez reçu ce classeur, qui contient une chaîne de 1 000 nombres séparés (ou délimités) par un espace, également appelé chaîne délimitée par des espaces, dans la cellule A1. Chaque nombre de cette chaîne est une valeur financière que vous souhaitez extraire dans des cellules individuelles de la colonne A afin de pouvoir effectuer des analyses associées.

Pour ce faire, utilisez d'abord la fonction TEXTSPLIT pour diviser chaque entrée de la chaîne en lignes individuelles :

=TEXTSPLIT(A1,," ")

où A1 est la cellule contenant la chaîne de texte, le deuxième argument de la fonction TEXTSPLIT (le délimiteur de colonne) est omis car vous voulez le résultat dans une colonne, et  » «  indique à Excel que le délimiteur est un espace. Si les nombres étaient séparés par des virgules plutôt que par des espaces, vous taperiez une virgule entre guillemets doubles au lieu d'un espace.

La fonction TEXTSPLIT est une fonction de tableau dynamique, ce qui signifie que le résultat s'étend de la cellule dans laquelle vous avez tapé la formule aux cellules adjacentes (un comportement appelé débordement).

Cependant, puisque TEXTSPLIT est une fonction texte, le résultat est un tableau de valeurs texte, même si elles ressemblent à des nombres. Vous devez donc envelopper la formule entière dans VALUE pour convertir ces valeurs en valeurs numériques :

=VALUE(TEXTSPLIT(A1,," "))

Maintenant que les valeurs sont numériques, vous pouvez sélectionner toutes les cellules et appliquer le format Numéro comptable via le groupe Nombre de l'onglet Accueil du ruban.

Pour sélectionner toutes les cellules d’un tableau dynamique, sélectionnez d’abord l’une des cellules, puis appuyez sur Ctrl+A.

Pour convertir le tableau dynamique en valeurs fixes, sélectionnez les cellules dans lesquelles le résultat est réparti, appuyez sur Ctrl+C pour copier leur contenu et appuyez sur Ctrl+Shift+V pour les coller en tant que valeurs.

Enfin, maintenant que le résultat n'est plus lié à la cellule A1, vous pouvez supprimer la chaîne de nombres d'origine délimitée par des espaces et formater les données nouvellement présentées sous forme de tableau.

Grâce à ces étapes rapides et à la puissance de la fonction VALEUR, vous avez transformé une chaîne de texte déroutante en un ensemble de données prêt à être analysé.

Extraction de chaînes de numéros de texte délimitées dans des colonnes séparées

Dans l'exemple ci-dessus, nous avons utilisé VALUE avec TEXTSPLIT pour convertir tous les nombres résultants en valeurs numériques. Cependant, vous devez utiliser une méthode légèrement différente pour diviser une chaîne de texte délimitée. et nombres tout en convertissant les nombres en vraies valeurs numériques.

Supposons que vous ayez demandé à un collègue d'utiliser une feuille de calcul Excel pour vérifier les stocks de chaque vêtement, en comptant séparément les articles petits, moyens et grands. Cependant, lorsqu'il a partagé la feuille de calcul obtenue avec vous, vous réalisez qu'il a utilisé des virgules pour séparer chaque décompte, plutôt que de les placer dans des colonnes individuelles.

Par conséquent, dans la cellule B2, vous devez restructurer les données à l'aide de la fonction TEXTSPLIT, puis utiliser la poignée de recopie pour copier la formule dans les cellules restantes :

=TEXTSPLIT(A2,",")

où A2 est la première cellule contenant la chaîne de texte-numéro délimitée, et « ,«  indique à Excel que le délimiteur de colonne est une virgule.

Même si, à première vue, il semble que vous ayez résolu le problème, les nombres dans les colonnes C, D et E sont actuellement stockés sous forme de texte. Vous le savez parce qu’ils sont alignés à gauche.

Par conséquent, si vous utilisez ces nombres dans des formules, vous n’obtiendrez pas le résultat escompté.

Pour résoudre ce problème, vous pouvez envelopper la fonction VALUE autour de la formule TEXTSPLIT pour convertir les nombres en valeurs numériques :

=VALUE(TEXTSPLIT(A2,","))

Désormais, les totaux de la colonne F sont corrects et les nombres des colonnes C à E sont alignés à droite, ce qui signifie que vous les avez transformés avec succès en valeurs numériques. Notez cependant les erreurs dans la colonne B. Cela se produit parce que la fonction VALEUR a essayé (mais a échoué !) de convertir le texte avant le premier délimiteur en nombre.

Mais ne vous inquiétez pas, vous pouvez prendre une mesure supplémentaire pour remédier à ce problème. Plus précisément, vous devez introduire la fonction SIERREUR, qui renvoie une valeur personnalisée lorsqu'une formule génère une erreur. Pour démontrer comment cela fonctionne, enveloppez la formule entière dans SIERREUR et tapez x entre guillemets doubles pour la valeur de retour personnalisée :

=IFERROR(VALUE(TEXTSPLIT(A2,",")),"x")

Comme vous pouvez le voir dans la capture d'écran ci-dessus, IFERROR a rencontré une erreur dans la colonne B, car la fonction VALUE a tenté de convertir les valeurs de texte de la chaîne en nombres, renvoyant une série de x valeurs en conséquence. Donc, maintenant, vous devez dire à Excel de renvoyer la valeur du texte d'origine à la place. Pour ce faire, là où vous avez tapé précédemment xrépétez la formule TEXTSPLIT que vous avez déjà utilisée plus tôt dans la formule :

=IFERROR(VALUE(TEXTSPLIT(A2,",")),TEXTSPLIT(A2,","))

Enfin, puisque la formule ci-dessus est toujours liée aux chaînes d'origine de la colonne A, copiez et collez le résultat sous forme de valeurs (Ctrl+C > Ctrl+Shift+V), convertissez les données en tableau et supprimez les données de la colonne A.

---

Maintenant que vous comprenez le fonctionnement de la fonction VALEUR, en particulier sa capacité à fonctionner avec des fonctions de texte pour distinguer les nombres et le texte, allez plus loin et utilisez-la lors de la création de règles de validation de données personnalisées pour les cellules qui nécessitent des combinaisons strictes de lettres et de chiffres dans un ordre particulier.