LogicielsOffice

Création de login à partir d’une liste d’employés avec Excel

Il arrive parfois qu’à partir d’une base de données importée (genre CSV)  nous ayons le besoin de créer des login en grande quantité à partir de noms et prénoms.

Le login est généralement composé de la première lettre du prénom séparé par un point du nom.

Exemple pour : Aline Durand cela devient le login : a.durand

Si le prénom est composé on prendra les deux premières lettres.

Exemple pour : Marie-Catherine Durand cela deviendra : mc.durand

S’il y a deux noms de famille il sera séparé par un tiret également.

Exemple pour : Anne-Sophie Dupont Demoncorget cela deviendra : as.dupont-demoncorget

N’oublions pas non plus que tout cela doit être écrit en minuscules.

A B C
1 Prénom Nom Login
2 Anne-sophie Dupond Demoncorget
3 Jean-marc Devallois
4 Marie-Catherine Durand
5 Pierre-yves Dupuis dubourd
6 Marie-Evelyne Sagesse
7 Aline Durand

Nous allons procéder par étape afin de comprendre comment nous arriverons à notre résultat.

Voici les nomenclatures généralement utilisées en entreprise pour les noms d’utilisateurs

  • prenom.nom
  • p.nom
  • pnom
  • nom.prenom

Comment récupérer la première lettre d’une cellule.

Pour cela nous allons utiliser la fonction STXT, fonction qui renvoie un nombre donné de caractères extraits d’une chaîne de texte à partir de la position que vous avez spécifiée, en fonction du nombre de caractères spécifiés.

=stxt (Texte de départ ; n° de départ d’extraction ; nombre de caractères à extraire)

Exemple de STXT simple :

IMG1

Comment mettre en majuscule ou en minuscule en combinant avec le STXT.

 

IMG2

Combinons sur nom prénom et concaténons avec le &. (concaténer = mettre ensemble)

IMG3 IMG3

Comment remplacer les espaces par des tirets

Dans toutes les bases de données vous trouverez des prénoms composés qui seront écrits soit avec un tiret soit avec un espace. Utilisons SUBSTITUE fonction qui permet de remplacer un texte spécifique dans une chaîne de caractères.

IMG4

Ce qui nous donne pour l’instant la formule suivante et on approche !

=MINUSCULE(STXT(B2;1;1))& ». »&MINUSCULE(SUBSTITUE(A2; » « ; »-« ))

A B C
1 Prénom Nom Login
2 Anne-sophie Dupond Demoncorget a.dupont-demoncorget
3 Jean-marc Devallois j.devallois
4 Marie-Catherine Durand m.durand
5 Pierre-yves Dupuis dubourd p.dupuis-dubourd
6 Marie-Evelyne Sagesse m.sagesse
7 Aline Durand a.durand

Mais il nous reste le problème des prénoms composés que nous allons traiter avec trois fonctions. N’oublions pas que pour les prénoms composés on note les deux premières lettres.

Nous allons imaginez que vous avez utilisé la fonction SUBSTITUE pour remplacer tous les espaces par des tirets dans la colonne des prénoms.

Afficher la  lettre qui suit le trait d’union avec les fonctions si ; nb.si ; cherche.

On va d’abord donner un exemple simple pour chaque fonction et ensuite nous compléterons notre formule.

La fonction si (simple) :

=SI(test logique ;valeur si la réponse est vrai ;valeur si la réponse est fausse)

Exemple : Il y a 5000 en B43 et 2000 en B44 ; la formule inscrite dit que si la cellule est supérieure à 2500 je veux voir « oui » sinon je veux voir « non »

IMG5

La fonction NB.SI :

Compte le nombre de cellules d’une plage qui répondent à un critère spécifique que vous spécifiez. Vous pouvez, par exemple, compter toutes les cellules qui commencent par une lettre donnée ou compter toutes les cellules qui contiennent un nombre supérieur ou inférieur à un nombre donné.

Exemple : compter le nombre de fois où je trouve le mot « marseille » dans cette liste

=NB.SI(plage ;critère)

IMG6

Ici on va simplement détourner un peu cette fonction pour lui demander de regarder s’il y a un trait d’union dans notre cellule

=NB.SI(B2; »*-* »)

IMG7

Maintenant il nous faut rajouter la lettre qui va suivre ce trait d’union qu’Excel aura trouvé dans la cellule. Pour cela nous allons utiliser la troisième fonction : CHERCHE

La fonction CHERCHE :

Renvoie le numéro du caractère au niveau duquel un caractère spécifique ou une chaîne de texte est initialement reconnu(e) à partir du n° de départ. Utilisez la fonction CHERCHE pour déterminer la position d’un caractère ou d’une chaîne de texte dans une autre chaîne de texte de façon à pouvoir utiliser ensuite les fonctions STXT ou REMPLACER pour modifier le texte.

Info : La fonction « Cherche » ne fait pas de distinction entre majuscules et minuscules contrairement à « Trouve »
.

=CHERCHE(texte_cherché;texte;N°de départ)

Remplaçons le texte par nos données :

=STXT(B2;CHERCHE(« -« ;B2)+1;1)

On ne rajoute pas de numéro de départ, cela signifie que l’on commence dès le début.

On a ajouté 1 à la fonction CHERCHE, car la fonction nous donne la position du trait d’union, et on veut la lettre qui suit ce trait d’union.

Voici nos 3  fonctions réunies pour afficher la lettre qui suit le trait d’union.

IMG8

Notre formule terminée donne :

En couleur pour plus de compréhension.

=MINUSCULE(STXT(B2;1;1))&MINUSCULE(SI(NB.SI(B2; »*-* »);STXT(B2;CHERCHE(« -« ;B2)+1;1);))& ». »&MINUSCULE(SUBSTITUE(A2; » « ; »-« ))

  A B C
1 Prénom Nom Login
2 Anne-sophie Dupond Demoncorget as.dupond-demoncorget
3 Jean-marc Devallois jm.devallois
4 Marie-Catherine Durand mc.durand
5 Pierre-yves Dupuis dubourd py.dupuis-dubourd
6 Marie-Evelyne Sagesse me.sagesse
7 Aline Durand a.durand

Et si l’on veut à partir du login terminé en colonne C ici on peut restreindre le nombre de caractères si la situation le demande.

Exemple le login ne doit pas dépasser 12 caractères dans une nouvelle colonne on écrira notre formule stxt en la concaténant avec le « @ »et le « fai .fr » par exemple.

=STXT(C2;1;12)& »@fai.fr »

Ce qui donne pour le premier login : [email protected]

Pour les plus intéressés :

Pour ceux qui veulent en apprendre un peu plus et donc connaitre le fonctionnement de chaque fonction utilisée, je joins le classeur Excel dans lequel vous aurez d’autres petits bonus, d’autres fractionnement.

Classeur Excel Login à télécharger

Patricia DEVISSCHERE

Ancienne Formatrice, je propose des astuces et tutoriels afin de faciliter et de mieux utiliser l'outil Bureautique. Former trainer, I offer tips and tutorials to facilitate and better use of Office tools.

Un commentaire

  1. Très intéressant, surtout pour
    les entreprises ! En effet, il est indispensable de savoir maîtriser cela
    car gérer beaucoup de personnel requiert de grandes tactiques de gestion. Et
    comme tout est informatisé de nos jours, cela s’avère très pratique. Reste à
    voir si cette méthode est plus rapide que d’autres.

Laisser un commentaire

Votre adresse e-mail ne sera pas publiée. Les champs obligatoires sont indiqués avec *

Bouton retour en haut de la page