Le Format de nombre
Excel est un logiciel fait pour traiter du “data”. Il ne comprend aucune autre langue que celle des chiffres. Bien sûr il peut reconnaître lorsque deux caractères sont identiques (on ne parle pas du caractère émotionnel ici …) et retrouver bien des choses si on lui dit exactement ce qu’on recherche. Tout le reste est mathématique.
C’est la raison pour laquelle je vais vous parler du format de nombres. Dans mes formations, j’insiste sur le sujet car il est à la base de beaucoup de problèmes rencontrés lorsqu’on utilise Excel. Si on comprend bien comment Excel travaille avec le contenu des cellules, on rencontrera moins de problèmes et ceux-ci seront plus facile à régler.
Les zéros
En mathématiques, un zéro qui précède un nombre est inutile. Il ne modifie pas la valeur du nombre. C’est la raison pour laquelle on n’écrira pas 000123, mais plutôt 123. Du coup, il pourrait y avoir une infinité de zéros avant les chiffres qui nous intéressent. Pourtant, dans certains contextes, les zéros ont une signification. Lorsqu’il est question d’un code barre sur un produit, le zéro qui précède la série de nombres est très important. Mais aux yeux d’Excel, il s’agit d’information inutile et encombrante.
Pour solutionner cela vous avez deux options. Attribuer le format texte aux cellules visées avant d’y mettre un contenu avisera le logiciel qu’il ne doit pas tenter d’interpréter ou de changer le contenu. Il va le laisser exactement comme on l’a entré. Problème résolu! En plus, il sera toujours possible d’effectuer des opérations mathématiques sur les cellules si elles contiennent seulement des chiffres.
Les fractions
Par défaut, les décimales sont utilisées pour afficher les valeurs situées entre zéro et un. C’est le modèle le plus largement utilisé. Malgré tout, il y a des occasions où l’emploi de fractions avec un numérateur et un dénominateur (je parie que vous vous dites: “Mon dieux! que ça fait longtemps que je n’ai pas entendu ces appellations!) sont plus pratiques. Saviez-vous que vous pouvez faire afficher les valeurs ainsi? Parmi les formats de nombres possibles, on retrouve le format fraction qui permet cela. L’utilisation de la fonction ARRONDI et de ses dérivés permettra même de ramener les valeurs à des fractions communes. L’industrie de la construction utilise encore le bon vieux système de mesure impérial. La fabrication de poutrelles ou de fermes de toit requiert de calculer les longueurs de chaque pièce; avec quelques formules, un peu de trigonométrie et le bon format de nombre, on pourra automatiser le tout assez facilement.
Les dates
Comme j’ai déjà parlé des dates et du temps dans un article précédent, je ne m’éterniserai pas sur le sujet. Je précise tout de même qu’il est préférable de changer le format de nombre avant d’entrer une date dans une cellule. Personnellement, j’utilise toujours le format date longue. Si la valeur entrée dans ma cellule n’est pas reconnue, je verrai tout de suite l’erreur car Excel ne pourra pas transformer la valeur numérique entrée en une date longue. C’est plus pratique.
Les $$$
Je gronde régulièrement les participants à mes formations qui s’obstinent à entrer le symbole “$” lorsqu’ils font la saisie d’une valeur monétaire. C’est une mauvaise pratique car l’ajout manuel du symbole est une source d’erreur et de problèmes. Tout ce qu’il faut dire à Excel, c’est la portion numérique. Ensuite on applique le format monétaire adapté. En utilisant cette méthode, il n’y a jamais de problème.
Formats personnalisés
Tout récemment, j’ai présenté des données avec des unités de mesure de longueur et de poids. Il suffit d’ajouter un “m” ou un “kg” dans la cellule et le tour est joué. Mais nous savons tous que les cellules en question ne seront plus interprétables par Excel car l’ajout d’un caractère autre que numérique rend la cellule incompréhensible.
Saviez-vous que vous pouvez créer votre propre format personnalisé qui affichera l’unité de mesure de votre choix? Peu de gens utilisent cette fonctionnalité mais lorsqu’on en a besoin, c’est fort pratique. Les possibilités sont grandes, il suffit de connaitre certaines règles de base.
- L’utilisation du symbole 0 (chiffre zéro) dans un format de nombre affiche les zéros non significatifs si un nombre possède moins de chiffres qu’il n’y a de zéros dans le format. Bien sûr, vous pouvez faire cela en cliquant sur ajouter une décimale ou réduire les décimales.
- L’utilisation du symbole # permet de ne faire afficher que les chiffre significatifs. Si par exemple vous voulez faire afficher 7,3 lorsqu’il y a un nombre entier mais seulement ,3 s’il n’y a pas d’entier dans le nombre, vous pouvez utiliser ce symbole pour aviser Excel de n’afficher que les chiffres significatifs.
- Le symbole ? vous permettra d’aligner les virgules pour les nombres décimaux qui n’ont pas tous le même nombre de décimales. Si on ajuste le nombre de décimales affichées au nombre qui en contient le plus, on va se retrouver avec tout plein de zéros inutiles après la virgule. Si on laisse Excel gérer l’affichage, les nombres seront justifiés à droite, plaçant la virgule là où elle se retrouve en fonction du nombre de décimales. En utilisant un format de nombre du type ?0,0???, vos virgules (ou autre séparateur décimal) seront bien alignées dans la colonne et il n’y aura que les décimales significatives qui s’afficheront, jusqu’à concurrence de 4. Plus efficace pour la lecture et l’interprétation.
Conclusion
Il existe tout plein d’autres choses qu’on peut faire avec les formats de nombres personnalisés. Bien sûr, on est dans la zone pour utilisateurs avancés qui veulent paramétrer de façon très spécifique l’affichage de leurs cellules. Mais même les débutant peuvent s’amuser à créer des formats personnalisés, ou du moins savoir que c’est possible.
Fonction CHERCHE
Si vous utilisez déjà les commandes “Rechercher” et “Remplacer” dans Excel ou dans d’autres programmes de la suite Office, vous savez à quel point elles peuvent être utiles. Au lieu de scruter à la loupe chaque cellule pour vérifier si elle contient l’élément recherché, il suffit de laisser Excel faire la recherche pour vous et ensuite d’effectuer le traitement voulu. Plus il y a de données, plus on apprécie cette fonctionnalité.
Par contre, si on veut identifier toutes les cellules qui contiennent une chaîne de caractère spécifique pour ensuite les filtrer, ce ne sera pas le bon outil. La fonction CHERCHE pourrait certainement vous donner un coup de main à atteindre votre objectif.
Voici comment ça fonctionne: la fonction CHERCHE identifie le numéro du caractère où se trouve un caractère ou une chaîne de caractères à l’intérieur d’une cellule. On peut donc savoir où se trouve le caractère spécifique. À quoi ça sert? Disons que nous avons une liste de pièces. Les pièces ont un code d’une longueur variable selon le modèle. Comment pourrait-on identifier les pièces qui sont des courroies? Si toutes les courroies ont un “A” comme caractère juste après un tiret (-), on pourra sûrement trouver une façon…
Dans cet exemple, la fonction CHERCHE permettrait d’identifier où le trouve le tiret. Ensuite, on pourrait utiliser la fonction STXT pour retourner seulement le premier caractère situé après le tiret. S’il s’agit d’une courroie, il y aura un “A” dans la cellule où on aura combiné ces deux fonctions.
Un autre exemple: à partir d’une liste de courriels, je veux extraire le nom de domaine pour chacune d’entre elles. Rien de plus facile! La fonction CHERCHE me permettra de trouver la position du “@” et ensuite en combinant la fonction NBCAR et DROITE, je pourrai extraire le domaine de façon automatique.
Maintenant qu’on a des utilisations concrètes de la fonction, voici comment l’utiliser.
- Le premier argument est le ou les caractères à rechercher. On peut y mettre le contenu à recherche directement ou faire référence à une cellule, ce qui permettra de paramétrer l’élément recherché. Dans l’exemple des adresses courriel, il s’agit du “@”, puisqu’il sépare toujours le nom du détenteur de l’adresse et le domaine.
- Le second argument est la cellule qui contient le texte dans lequel on veut identifier la position du ou des caractères. Dans l’exemple des adresses courriels, il s’agit des adresses.
- Un 3e argument facultatif permet de commencer à chercher à partir d’un numéro de caractère. Si vous avez une très longue chaîne de caractère dans votre cellule et que vous souhaitez que la recherche se fasse à partir du xième caractère, il faut simplement entrer le numéro du caractère de départ. Encore une fois, il peut s’avérer utile de faire référence à une cellule pour cette valeur car on pourra ensuite paramétrer facilement le caractère de départ.
L’explication peut semble un peu floue pour certains. Je dois admettre que ce n’est pas la fonction la plus facile à expliquer car on l’emploie rarement toute seule. Dans la grande majorité des cas, le résultat final implique l’utilisation d’autres fonctions.
Histoire de rendre la chose plus concrète, j’ai préparé un petit fichier d’exercice pour faire un essai. Amusez-vous!
Fonction MAINTENANT
Possiblement la fonction la plus simple à utiliser avec sa soeur AUJOURDHUI, la fonction MAINTENANT est fort pratique lorsqu’on veut saisir un événement dans le temps. Elle retourne la valeur précise associée au moment présent, selon le modèle de temps Excel.
Rappelons-nous que l’unité de temps dans Excel est le jour. Les heures sont des subdivisions d’un jour (à raison d’un vingt-quatrième 1/24) et les minutes des subdivision d’heures (donc un soixantième d’un vingt-quatrième [1/24]/60). Les secondes poursuivent le modèle en divisant une autre fois par 60. Chaque seconde qui passe représente donc 0,000011574 jour.
| Jour | 1 |
| heure | 0,041666667 |
| minute | 0,000694444 |
| seconde | 0,000011574 |
En partant de ce principe, la fonction MAINTENANT donne le nombre exact qui correspond à la date (42705 pour le premier décembre) en plus d’y ajouter les décimales qui permettent de compter les heures, les minutes et les secondes. Le temps peut ainsi être calculé selon des équations purement mathématiques.
Lorsqu’on intègre la fonction MAINTENANT à l’intérieur d’une macro, on peut facilement garder une trace du moment précis où est survenu un événement dans Excel. Si vous n’y voyez pas encore d’utilité, voici quelques exemples où ça peut s’avérer utile:
- Feuille de temps qui fonctionne comme une carte de punch et qui calcule automatiquement le total des heures chaque jour
- Feuille de suivi des tâches journalières pour mesurer le temps passé à exécuter chacune d’entre elles
- Suivi des cédules d’entretien; par exemple chaque fois qu’un entretien est fait dans une salle de bain
- Suivi des entrées de clients dans un commerce; chaque fois qu’un nouveau client entre, on appuie sur un bouton et le moment est enregistré
Bien sûr il y a une infinité de possibilités. Chaque fois qu’une statistique de temps pourrait s’avérer intéressante, la fonction MAINTENANT devient utile.
Pour l’utiliser, il suffit d’inscrire “=maintenant()” dans une cellule [ne pas mettre les guillemets]. Elle se mettra automatiquement à jour à chaque nouvel événement. Pour garder la trace, il faudra créer la macro qui va chercher la valeur de MAINTENANT et qui la copie ailleurs. Très simple pour quiconque sait faire des macros de base.
Comme la fonction est trop simple pour avoir besoin de pratique, j’ai préparé un fichier exemple au lieu d’un fichier d’exercice. Il s’agit d’une feuille de temps qui utilise MAINTENANT et quelques macros pour saisir l’information dans un tableau. Faites un essai, c’est amusant. Notez que les valeurs du tableaux s’arrondissent par tranches de 15 minutes. Vous pouvez changer cette valeur dans la cellule G3. Il vous faudra aussi activer les macros pour que ça fonctionne. N’ayez crainte, le fichier est sans danger pour votre ordinateur.
Fonctions ET & OU
Deux nouvelles fonctions ce matin: ET & OU. Ne pas confondre avec les ingrédients d’une recette dans laquelle il y a acide ascorbique, dimethylposiloxane, glutamate monosodique et/ou polydimethylpolysiloxane (Croyez-moi ce sont de réels ingrédients utilisés dans l’alimentation!). Il s’agit plutôt de deux fonctions cousines qui permettent de pousser plus loin la fonction SI.Il faut avant tout comprendre que la fonction SI est limitée à un seul test logique. Du moins, elle ne peut en vérifier qu’un seul à la fois. On peut en imbriquer plusieurs et ainsi ajouter des étages, mais sans ses deux cousines, on ne pourra pas vérifier plusieurs critères en simultané. Pour rendre la dernière phrase plus simple à comprendre, on ne peut pas vérifier SI Sophie a les yeux bleus et les cheveux blonds. Pour faire une telle vérification, il faudra imbriquer la fonction ET à l’intérieur de la fonction SI.
Commençons par définir ce qu’est imbriquer des fonctions. On parlera d’imbrication lorsqu’une fonction est utilisée comme argument dans une autre fonction. Lorsque vient le temps de remplir les champs arguments d’une fonction, on peut y mettre des nombres, du texte, des cellules référence et… une autre fonction. Pour vérifier SI Sophie a les yeux bleus ET les cheveux blonds, il faudra donc utiliser la fonction ET à l’intérieur de la fonction SI.
Fonction ET
La fonction ET sert à vérifier que tous les test logiques (ou toutes les affirmations dans mon vocabulaire à moi), sont vrais. Si et seulement si ils sont tous vrais, la fonction retournera la valeur VRAI. Dès qu’une des affirmations sera fausse, le résultat de la fonction ET sera faux. Dans mon exemple, l’affirmation 1 est que Sophie a les yeux bleus et l’affirmation 2 est que Sophie a les cheveux blonds. Si les deux affirmations sont vraies, alors la fonction ET retournera le résultat VRAI. Si Sophie a les yeux bleus et les cheveux bruns, le résultat sera FAUX.
Fonction OU
La fonction OU permet de vérifier qu’une ou l’autre des conditions spécifiées est vraie. Dès qu’une des conditions est vrai, le résultat sera VRAI.
On peut vérifier jusqu’à 255 affirmations dans une même fonction. On peut aussi combiner des fonction ET & OU. Comme le résultat que ces fonctions retournent est toujours VRAI ou FAUX, on devra utiliser une fonction SI pour faire afficher autre chose. La formule prendra alors la forme SI(fonction ET ou OU;valeur si vrai;valeur si faux). Le premier argument de la fonction sera la fonction ET ou la fonction OU. Celle-ci retournera directement la valeur VRAI ou FAUX à la fonction SI qui, à son tour, retournera ce que vous aurez déterminé comme étant la valeur si vrai ou la valeur si faux selon le cas.
Comme pour les articles précédents, j’ai préparé un fichier qui permet de mettre le tout en application. Vous pouvez le télécharger en suivant le lien ci-dessous.
Fonction CHOISIR
Dans la série des articles sur les fonctions, je vous présente aujourd’hui la fonction CHOISIR. Lors des formations que je donne, je rencontre souvent des gens qui ont découvert la fonction SI et qui utilisent cette fonction dans toutes les situations. Il est vrai qu’on peut faire beaucoup de choses avec SI mais c’est un peu comme utiliser le micro-ondes pour tout faire dans une maison. On peut faire chauffer de l’eau, faire sécher un chandail, on peut sûrement faire cuire le spaghetti dedans et je parie que quelqu’un a déjà tenté d’y faire des toasts.
La fonction CHOISIR remplacera certainement la fonction SI pour certaines situations simples que vous rencontrez. Voici comment elle fonctionne: on lui donne une liste de résultats à retourner et on lui donne le numéro du résultat voulu. Ça sonne un peu étrange dit comme ça. L’exemple ci-dessous devrait être plus clair.
Disons qu’on a un tableau qui contient une liste de clients avec un numéro de 1 à 6 qui représente la catégorie du client.
1 = homme moins de 30 ans, 2 = femme moins de 30 ans,3 = homme 30-45 ans, 4 = femme 30-45 ans, etc.
À la fin de l’année, on veut envoyer un cadeau adapté aux hommes et aux femmes et aussi à leur âge. La fonction CHOISIR sera tout indiqué. Dans cet exemple, le premier argument de la fonction (le numéro du résultat) sera le numéro associé à chaque client. Les arguments suivants seront les différents cadeaux possibles. Tellement simple!
Le fichier ci-dessous présente l’exemple mentionné avec une colonne pour vous permettre d’essayer la fonction.
Connaitre plus de fonctions pour aller plus vite
Plus on a de connaissances, plus on peut faire de choses. Ça va de soi. Les connaissances permettent d’en faire plus, plus vite et habituellement mieux.
Cela s’applique aussi à Excel. Plus on sait de choses, plus on connait les possibilités, plus on connait de fonctions et de méthodes pour travailler avec, plus on devient efficace. Encore une fois, ça va de soi.
C’est pour cette raison que je vais publier tout plein d’articles qui présentent des fonctions au cours des prochains jours. Si vous visitez régulièrement cette page, vous devriez apprendre l’existence de plusieurs fonctions qui sont méconnues. Certaines ne vous intéresseront probablement pas mais d’autres vous permettront d’accélérer votre travail et ainsi d’en faire plus, plus vite et mieux 😉
NB.SI
La première que je vous présente est NB.SI. Elle permet de compter le nombre de cellules qui rencontrent un critère. Supposons que vous avez une liste de 200 personnes avec leur ville de résidence, la population de la ville et la date de naissance. Vous voulez savoir combien de gens habitent dans chacune des villes. Il suffit de créer un nouveau tableau qui liste les villes dans la première colonne et de mettre la fonction NB.SI dans la colonne voisine.
Le premier argument de cette fonction est la plage de cellules dans laquelle se trouvent les cellules à évaluer en fonction du critère. Dans le cas présent, il s’agit de la colonne qui affiche la ville de résidence des gens. Le 2e argument est le critère d’évaluation. Dans notre cas, il s’agit de la cellule voisine puisque nous sommes dans un tableau qui liste les villes. Une fois les deux arguments définis, on appuie sur la touche « Entrée » et le tour est joué.
Idéalement, on a converti la liste en Tableau Excel au préalable. De cette façon, on n’aura qu’à créer une seule fonction et tout le reste se fera de façon automatique. Tout simplement magique!!
On pourra ensuite trouver tout plein d’information grâce à cette fonction. Si le coeur vous en dit, j’ai préparé un fichier avec une liste de gens et 4 petites questions pour pratiquer l’utilisation de cette fonction. Vous pouvez télécharger le fichier en suivant le lien ci-dessous
La Validation des données
Validation de données
Dans un article précédent, j’ai parlé des Tableaux Excel et de quelques avantages qu’ils procurent. Cette méthode de travail permet de filtrer et de faire une analyse de base simplement et rapidement. J’ai aussi effleuré les Tableaux croisés dynamiques qui permettent un niveau d’analyse plus poussé lorsque c’est requis.
Ces merveilleux outils fonctionnent à merveille lorsque les données ont été entrées correctement et de façon uniforme. S’il y a des variations dans le modèle de saisie, les chances sont grandes pour que les résultats d’analyse deviennent boiteux. Cela vous place devant un choix : soit vous « nettoyez » les données en corrigeant les variantes qui nuisent, soit vous éliminez celles-ci de votre analyse en vous concentrant sur celles qui sont entrées correctement.
L’option un requiert un investissement de temps que vous n’avez probablement pas. L’option deux donnera des résultats biaisés. Dans tous les cas, vous serez biaisé mais sans le premier « i ».
La solution à ce problème passe par la commande Validation des données. Avec cette commande, vous pourrez définir les paramètres pour chaque colonne et vous assurer de la conformité des données qui y seront saisies.
Il suffit de sélectionner les cellules qui doivent être Validées et de cliquer sur Validation des données dans l’onglet du ruban Données. Vous pourrez ensuite choisir le type de données à entrer dans les cellules en question ou bien définir une liste précise de laquelle il ne sera pas possible de déroger.
En plus d’empêcher la saisie de données non conformes, il sera possible de laisser une directive pour guider l’utilisateur. Vous pourrez faire afficher un message qui s’affichera dès que la cellule sera sélectionnée. Vous pourrez aussi définir un message d’erreur avec des directives supplémentaires.
Avec la Validation des données, plus besoin de faire le nettoyage de vos tableaux de données avant de les utiliser. Plutôt intéressant n’est-ce pas !?!?
Mise en forme conditionnelle
Une personne de mon entourage a récemment eu à apporter des ajustements de salaire à plusieurs centaines de personnes. Pour certains, il s’agissait d’une augmentation et donc d’une bonne nouvelle. Pour les moins chanceux, l’exercice causait une baisse de revenus.Comment mettre en évidence les variations négatives pour faire une double vérification? La mise en forme conditionnelle serait une option toute indiquée.
Pour ceux qui n’utilisent pas encore cet outil, son fonctionnement est vraiment très simple. Il suffit d’appliquer un test logique et de dire à Excel comment la cellule doit être formatée lorsque la condition est remplie.
Imaginons que l’ancien salaire est dans la colonne « K » et que le nouveau salaire s’affiche dans la colonne « L » pour tous les employés. On ajoute une colonne « M » qui calcule la variation entre « K » et « L »; si la variation est positive, on fait afficher la cellule en vert, si la variation est neutre, en jaune et si la variation est négative, on fait afficher la cellule en rouge.
Pour faire cela, il suffira de créer 3 règles avec 3 couleurs différentes et de les appliquer aux bonnes cellules. C’est l’histoire de 5 minutes et ça permet de voir clairement ce qui se passe.
En plus, on pourra trier ou filtrer les valeurs en fonction des couleurs et ainsi se concentrer sur un groupe en particulier.
La commande de mise en forme conditionnelle se trouve dans l’onglet du ruban Accueil. Attention de ne pas vous faire prendre : il est possible d’assigner la mise en forme conditionnelle à la fin de l’exercice en choisissant Gérer les règles mais c’est tellement plus simple de sélectionner les cellules avant de créer les règles. Je vous recommande fortement de procéder ainsi. Surtout pour les premières fois.
Sélectionnez les cellules qui doivent changer d’apparence, cliquez sur la commande Mise en forme conditionnelle, choisissez Règles de mise en surbrillance des cellules, et choisissez Supérieur à. Attribuez la valeur zéro et choisissez Remplissage vert avec texte vert foncé.
Ensuite, répéter l’exercice mais avec l’option Égal à, attribuez encore la valeur zéro et choisissez Remplissage jaune avec texte jaune foncé. Une autre fois en choisissant Inférieur à, toujours valeur à zéro et Remplissage rouge avec texte rouge foncé. Voilà! Vos cellules sont maintenant colorées selon la variation.
Bien sûr, vous n’êtes pas obligé de faire colorer toutes les cellules. Vous pourriez choisir de colorer seulement les baisses de salaire pour faire la double vérification et savoir qui aura la mine basse en recevant la nouvelle. Vous pourrez en profiter pour envoyer quelque chose de positif en parallèle pour aider à faire passer la pilule.
Imprimer un tableau sur plusieurs pages
Lorsqu’on travaille avec un tableau qui à quelques centaines de lignes, il est facile de voir à quoi correspondent les données de chaque colonne puisque les entêtes restent visibles.
Si vous devez imprimer ce document pour vous y référer dans un contexte où vous n’aurez pas votre écran sous les yeux, c’est un peu moins évident. Surtout si les données peuvent être confondues d’une colonne à l’autre.
Pour faciliter la consultation papier d’un document semblable, j’ai deux recommandations à proposer :
Mettre sous forme de Tableau Excel
Pour plusieurs c’est un réflexe. Pour ceux qui ne le feraient pas de façon systématique, il est grand temps de commencer cette bonne habitude. Les Tableaux Excel possèdent tout plein de caractéristiques avantageuses pour vous aider à être plus productif. Pourquoi s’en passer?
Une fois que vos données seront mises sous forme de Tableau Excel, dans les Outils de Tableau vous pourrez cocher Colonnes à bandes et vous assurer que Lignes à bandes n’est pas coché. Vos colonnes seront ainsi colorées et les données d’une même colonne seront plus évidentes.
Imprimer les titres
Rendu à la page 7, vos colonnes seront toujours clairement identifiées mais comment vous souvenir du titre de la colonne? Bien sûr vous pouvez insérer une ligne qui répétera les entêtes de colonne à intervalles réguliers dans votre document. J’espère seulement que vous ne fausserez pas vos données en procédant ainsi.
La solution toute simple réside dans l’onglet du ruban Mise en page. À cet endroit, vous pouvez cliquer sur Imprimer les titres et indiquer à Excel la ligne à répéter en haut de chaque page imprimée.
Comme vous pourrez le voir en faisant un essai, le même principe s’applique pour la colonne qui contient l’information référence. Si votre tableau est large et doit être imprimé sur plus d’une page en largeur, vous n’avez qu’à indiquer quelle colonne doit être répétée.
Plutôt pratique non? Plus besoin de répéter manuellement, d’inscrire à la main ou de faire du bricolage… Quel bel outil !!!
Les “faux nombres” dans Excel
On l’a tous déjà vécu: une série de nombres dont le format n’est pas adéquat et qui ne peuvent être utilisés pour faire des opérations dans Excel. Lorsque vous regardez les cellules, vous pouvez voir les nombres mais dès que vous tentez de faire une addition ou une multiplication vous obtenez #VALEUR! Il est plus que probable que les nombres soient inscrits d’une façon incompréhensible par Excel.
S’il s’agit d’une liste qui ne sera travaillée qu’une seule fois, vous utiliserez probablement la commande Remplacer (Ctrl + H) et vous pourrez transformer les “faux nombres” en vrais nombres en seulement quelques clics. S’il s’agit d’une liste que vous utilisez sur base régulière, vous pourrez peut être créer une Macro qui pourra convertir le tout et vous permettre de continuer votre travail. Un peu complexe pour ceux qui n’osent pas s’aventurer dans le monde mystérieux des macros.
Il existe une autre solution pour résoudre cette fâcheuse situation : une combinaison de fonctions. Par exemple, si vous avez une série de nombres qui représentent des montants d’argent et qui sont entrés de la façon suivante: 39.99 $
Excel ne reconnaît pas le nombre pour deux raisons:
- Utilisation du point au lieu de la virgule comme séparateur décimal
- Ajout du symbole “$” à la fin, précédé d’un espace
La recette que je vous propose va comme suit:
- Compter le nombre de caractères dans la cellule avec la fonction NBCAR
- Remplacer les deux derniers caractères (l’espace et le symbole $) par rien avec la fonction REMPLACER, en utilisant le résultat de NBCAR moins un comme 2e argument
- Utiliser la fonction VALEURNOMBRE avec le résultat des fonctions précédentes pour que Excel remplace le séparateur décimal.
Si on utilise le tout dans un Tableau Excel, on n’aura qu’à entrer une formule ressemblant à ceci: =VALEURNOMBRE(REMPLACER([@Montant];NBCAR([@Montant])-1;2;””);”.”)
Dans une colonne et coller les “faux nombres” dans la colonne Montant. Plus besoin de faire d”autres manipulations pour le futur.
Si la formule vous semble complexe à regarder, je vous invite à créer un Tableau Excel à trois colonnes. Nommez vos colonnes comme suite: Montant, Montant converti, Multiplié.
Entrez quelques nombres au format mentionné plus haut (Ex: 33.78 $) dans la première colonne
Copiez-collez la formule dans la 2e colonne
Faites une multiplication simple dans la 3e colonne pour vérifier que les nombres convertis sont reconnus par Excel.
Il ne vous reste ensuite qu’à cliquer sur une cellule de la 2e colonne et sur la touche Fx pour analyser les fonctions, les arguments et mieux comprendre en quoi consiste la formule.