Cliquer ici pour télécharger le fichier
Dans cet article, je vous explique comment créer un numéro automatique sur Excel.
Pour créer un numéro automatique sur Excel il faut être capable :
- D’archiver le dernier document crée
- De reconstituer le numéro en fonction des paramètres définis
- D’extraire la partie numérotation (partie numérique) du dernier numéro archivé pour y ajouter +1
Nous allons voir comment incrémenter un numéro automatique sur Excel de 2 façons :
- Avec une formule Excel de concaténation pour regrouper tous les éléments du numéro à créer et incrémenter +1
- Avec la création d’une formule dédiée en VBA qui regroupera en un seul bloc le nouveau numéro
1) Numéro automatique avec formule Excel
Pour créer un numéro automatique il faut partir du numéro précédent :
Dans l’exemple on souhaite créer un numéro qui se compose de 3 éléments :
- Début : Texte « DEV » pour devis
- Milieu : Du numéro sur 3 caractères numériques (« 006 » dans l’exemple)
- Fin : Du mois du devis sur 4 caractères alphabétiques (« SEPT » dans l’exemple)
= Tous les éléments sont séparés par un tiret « -« .
Début
Ici on inscrit en dur le texte DEV qu’il faut donc mettre entre guillemets et on ajoute le premier tiret de séparation entre les 2 premiers éléments du numéro.
= « DEV-«
Milieu
Pour déterminer le numéro suivant il faut récupérer le dernier numéro et ajouter +1.
= Pour cela il faut utiliser la formule STXT qui permet d’extraire le nombre de caractères souhaités dans une chaîne de caractères à partir d’un emplacement (point de départ) et du nombre de caractère à récupérer.
= TEXTE(STXT( C3 ; 5 ; 3 ) +1 ; « 000 » ) = 007
Fin
Pour la fin il faut allez chercher la date du devis, récupérer le mois et l’afficher en lettre au format court.
= « – » & MAJUSCULE(TEXTE( B3 ; « MMM » )) = SEPT
Formule complète
Il suffit de concatener les 3 éléments avec un « & »
= B6 & C6 & D6
Nouveau numéro de devis
Pour finaliser il faut maintenant allez chercher directement l’ancien numéro dans l’onglet archivage et combiner les 3 parties en une seule formule.
Début + milieu
= On récupère cette fois-ci l’ancien numéro directement depuis la cellule de l’onglet « Archivage » qui contient le dernier numéro de devis.
= « DEV- » & TEXTE(STXT( ArchivageDevis!A3 ; 5 ; 3 ) ; « 000 » )
= DEV-005
Fin :
= MAJUSCULE(TEXTE( E4 ; « MMM » )) = SEPT
Formule complète
= « DEV- » & TEXTE(STXT( ArchivageDevis!A3 ; 5 ; 3 ) ; « 000 » ) & MAJUSCULE(TEXTE( E4 ; « MMM » )) = DEV-005-SEPT
2) Numéro automatique avec formule VBA Excel
Pour commencer il faut accéder à l’onglet « Développeur » du ruban Excel puis cliquer sur l’outil « Virtual Basic ».
= Ensuite cliquer sur le menu déroulant à gauche puis sélectionner « Module ».
Saisie du code VBA :
Function NumDevis (Numero, DateDevis)
- Début = « DEV-«
- NouveauNum = Format(Mid(Numero, 5, 3) + 1, « 000 »)
- Fin = « – » & UCase (Format(DateDevis, « MMM »))
NumDevis = Début & NouveauNum & Fin
End Function
= On peut désormais utiliser cette formule en définissant les 2 arguments définis dans la formule NUMDEVIS : Numero et DateDevis.
Pour l’argument « numero » il faut sélectionner la cellule qui contient le dernier numéro dans l’onglet « Archivage ».
Pour l’argument « DateDevis » il faut sélectionner la cellule qui contient la date du devis.
Résultat :
=> Voir aussi comment Utiliser la formule RECHERCHEX
