ASTUCE : TRANSFORMER UN MOIS EN NOMBRE POUR L'UTILISER DANS UNE FORMULE EXCEL (avec et sans VBA)

Dans ce nouveau tutoriel, qui s’inscrit directement dans la série de cours sur l’apprentissage des dates et des heures dans Excel, je vais vous montrer comment transformer rapidement un mois exprimé en toutes lettres en un simple nombre. Nous découvrirons deux méthodes, l’une reposant sur une formule Excel et l’autre sur une fonction personnalisée de VBA. Restez bien jusqu’au bout de la vidéo pour découvrir un cas d’étude concret !

Téléchargement

Vous pouvez télécharger le fichier d'exemple de cet article en cliquant sur le lien suivant :

Tutoriel Vidéo

Vous trouverez très régulièrement de nouvelles vidéos sur la chaîne, alors pensez à vous abonner pour ne manquer aucune astuce et devenir rapidement un pro d’Excel (cliquez-ici) !

1. Présentation

Parfois lorsque nous construisons nos formules dans Excel il peut arriver que nous soyons bloqués par des mois qui sont exprimés différemment entre les différents tableaux manipulés.

Par exemple, imaginons que nous ayons une base de données dans laquelle nous retrouvons les ventes réalisées par des commerciaux au cours d’une année, présentées par mois et par commercial :

Ensuite, nous souhaitons synthétiser le montant de ces ventes par mois dans un second tableau (sans créer de nouvelle colonne évidemment) :

Ici nous voyons bien que les mois de l’année sont saisis en toutes lettres.

Comment faire pour exploiter les données figurant dans ces deux tableaux ?

2. Transformer un mois en nombre avec une formule

Tout d’abord, voyons comment faire pour convertir rapidement un mois en lettres en une valeur numérique.

Pour arriver à nos fins, nous allons exploiter une petite astuce qui repose sur le fait qu’Excel est capable d’interpréter une date saisie en toutes lettres.

Pour nous en rendre compte, il suffit de la saisir dans une cellule :

Nous voyons alors qu’Excel modifie directement l’affichage de la cellule lorsque nous validons :

À partir de là, nous allons pouvoir créer une date de toute pièce à partir du mois en lettres saisi dans une cellule, en utilisant le symbole de concaténation « & » :

À ce moment-là, Excel n’a pas encore compris qu’il s’agit qu’une date, et traite le résultat de cette cellule comme une simple chaîne de caractères :

En effet, nous pouvons constater que le contenu de la cellule est aligné sur la gauche, alors que les dates sont alignées sur la droite.

Mais cette chaîne de caractères est suffisante pour être saisie en tant qu’argument de la fonction MOIS() qui permet comme nous l’avons déjà vu dans un [#INSERER UN LIEN] précédent chapitre de la formation sur la gestion des dates dans EXCEL de récupérer le mois qui correspond à une date :

=MOIS("02/"&I5&"/2019")

Ce qui permet comme vous pouvez le constater ci-dessous de récupérer le numéro du mois qui correspond :

Évidemment, le jours et le mois que nous utilisons dans la formule ne sont que pour construire la date source et n’ont aucune importance étant donné qu’ils sont supprimés juste après par la formule MOIS().

3. Transformer un mois en nombre avec une fonction personnalisée VBA

Maintenant que nous avons construit une formule EXCEL, nous allons voir comment arriver au même résultat en partant d’une fonction personnalisée, que nous allons écrire en VBA.

Cela permettra par la suite de pouvoir retrouver cette fonction afin d’effectuer la conversion très rapidement.

Si vous n’avez encore jamais utilisé VBA, je vous invite à suivre [#LIEN] la formation gratuite disponible sur excelformation.fr en cliquant ici.

En tout cas, pas de panique, nous allons décrire chacune des étapes de construction.

Tout d’abord, nous lançons l’éditeur de code VBE en appuyant sur les touches [Alt] + [f11] :

Ensuite nous insérons un nouveau module, qui est en quelques sorte une feuille dans laquelle nous allons pouvoir saisir simplement notre code (Menu Insertion/Module) :

Pour créer une fonction personnalisée, nous utilisons le mot clé function suivi du nom de la fonction :

Function moisEnNombre()

End Function

Ensuite, nous ajouter un argument à la fonction qui permettra à l’utilisateur de saisir le mois en toutes lettres :

Function moisEnNombre()

End Function

Cette variable mois est typée en tant que String, c’est-à-dire que celle-ci va permettre de gérer une chaîne de caractères.

Ne reste plus qu’à retourner à l’utilisateur le numéro de mois, en procédant exactement comme nous l’avions fait dans la feuille de calcul :

Function moisEnNombre(mois As String)

    moisEnNombre = Month(mois & "/1/2019")

End Function

En effet, nous extrayons ici le mois qui correspond à la date créer de toute pièce à partir du mois donné en argument.

Attention toutefois à une petite subtilité : en VBA, les dates sont exprimées suivant le formatage US, c’est-à-dire sous la forme m/d/y (d’abord le mois, puis le jour et pour finir l’année).

Ne reste plus qu’à tester cette fonction dans la feuille de calcul en modifiant la formule saisie juste avant :

(CODE) =moisEnNombre(I5) (/CODE)

Ensuite, vous pourrez enregistrer le classeur sous le format *.xlsm pour enregistrer la macro, ou mieux encore créer un classeur de macro complémentaires pour le retrouver dans l’ensemble de vos feuilles de calculs

4. Application

Enfin, si nous revenons sur cas d’étude initial, nous pouvons maintenant simplement réutiliser l’une de ces deux méthodes pour déterminer les montants de CA mensuels :

​​​​​​​=SOMME.SI(_base[Mois];moisEnNombre([@Mois]);_base[CA])