[VIDEO]Comment créer des logins et des mots de passe sur Excel

Guide « 108 astuces pour grands débutants sur Excel ! » Offert
Booster votre productivité en téléchargeant gratuitement votre ebook PDF des meilleures astuces pour les débutants et les pros d'Excel
Excel formation - 00 Astuces - 100
 

Dans ce nouveau tuto, nous allons répondre à une question que vous m’avez posée par le biais du formulaire de contact disponible sur le site. Vous m’avez demandé quelle est la marche à suivre afin de générer facilement des identifiants de connexion et des mots de passe à partir d’une base de données d’utilisateurs composées des noms, prénoms et date de naissance de chacun d’entre eux. Pour ce faire, nous allons voir comment utiliser les bonnes formules et surtout au bon moment !

 

 

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

This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.
 

 

1. Le fichier source

 

Comme nous venons juste de le voir dans l’introduction de cet article, nous allons vouloir récupérer rapidement, et de manière automatique des logins et des mots de passe correspondants à des données d’utilisateurs. Ces données étant préalablement stockées dans une base de données.

Pour cela, nous allons utiliser le tableau que vous voyez actuellement à l’écran, qui reprend un certain nombre d’informations concernant le personnel d’une entreprise.

Excel formation - Créer-logins-et-mots-de-passe - 01

Tout d’abord, et pour nous simplifier le travail, nous allons commencer par convertir cette série de données en un tableau structuré de données.

Pour cela nous allons :

  • Sélectionner une case du tableau,
  • Dans le ruban, nous choisissons l’onglet Insertion, puis TableauExcel formation - Créer-logins-et-mots-de-passe - 02
  • Dans la fenêtre qui s’affiche à l’écran, nous pouvons constater qu’Excel à récupéré automatiquement les coordonnées de notre tableau. Il nous suffit de vérifiez que l’option « Mon tableau comporte des en-têtes » est bien cochée afin de demander à Excel d’inclure les titres de colonnes au sein du tableau de données

Excel formation - Créer-logins-et-mots-de-passe - 03

  • Il ne reste plus qu’à valider en appuyant sur le bouton OK.
  • Enfin, dans l’onglet Création (qui ne s’affiche que lorsque nous cliquons sur l’une des cases du tableau), nous allons donner un nom à notre tableau afin de pouvoir l’appeler facilement. Appelons-le par exemple : « _basePersonnel » :

Excel formation - Créer-logins-et-mots-de-passe - 04

Pour en savoir plus sur les tableaux de données d’Excel, je vous invite à consulter cet article qui en décrit le fonctionnement dans le détail.

Sachez seulement que ces tableaux vont nous permettre ici :

  • Tout d’abord d’appeler les cellules en fonction de leur nom de colonne, à la place de leur coordonnées (exemple pour récupérer la valeur contenue dans la cellule « A2 » nous allons pouvoir utiliser la formule « =_basePersonnel[@Prénom] », à la place de la formule « =A2 »),
  • Le deuxième avantage que nous allons tirer de l’utilisation des tableaux de données est que lorsque nous allons saisir une formule sur l’une des lignes du tableau, automatiquement celle-ci va s’étendre à la l’ensemble des lignes du tableaux

Le tableau que nous avons sous les yeux comporte de nombreuses informations, alors que seules les trois premières colonnes (les colonnes Nom, Prénom et Naissance) vont nous être utiles. Nous allons donc pouvoir supprimer les autres colonnes :

  • Commençons par les sélectionner, en cliquant dans un premier temps sur la lettre « D », qui correspond à la première colonne à supprimer (nous constatons alors que le curseur va prendre la forme d’une flèche pointant vers le bas),
  • Puis, toujours en maintenant le bouton de la souris enfoncé, nous allons faire glisser le curseur jusqu’à atteindre la dernière colonne à supprimer (la colonne « N ») afin de sélectionner l’ensemble des colonnes à supprimer,

Excel formation - Créer-logins-et-mots-de-passe - 05

  • Afin, nous allons effectuer un clic-droit afin de sélectionner Supprimer (nous pouvons également utiliser la combinaisons des deux touches [Ctrl]+[-]) :

Excel formation - Créer-logins-et-mots-de-passe - 06

Notre tableau est maintenant réduit au strict nécessaire, nous allons pouvoir entrer dans le vif du sujet et créer notre couple identifiant/mot de passe

 

2. Création automatisé de l’identifiant

 

L’identifiant de chaque utilisateur va correspondre à l’adresse mail de ce dernier, qui elle-même correspond à la première lettre de son prénom, suivit d’un point (« . »), puis son nom de famille limité aux sept premières lettres, de son année de naissance et pour finir un arobase et le domaine de l’entreprise.

Enfin, toute l’adresse mail doit être en minuscule !

Pour commencer, nous allons déterminer l’adresse mail de la première personne de la liste (qui est donc Gilles Ayot). Vous constaterez par la suite que lorsque nous validerons la saisie, la formule sera appliquée pour toutes les autres lignes également.

Pour générer l’identifiant, nous allons devoir une nouvelle colonne au sein de notre tableau.

Pour créer une nouvelle colonne sur un tableau de données, il suffit d’insérer un nouveau titre, dans la cellule qui se trouve juste à droite des titres déjà existant (dans notre exemple en cellule « D1 ») :

Excel formation - Créer-logins-et-mots-de-passe - 07

Puis, lorsque nous validons avec la touche [Entrée], Excel étend automatiquement le tableau d’une colonne :

Excel formation - Créer-logins-et-mots-de-passe - 08

Pour commencer, nous allons vouloir récupérer la première lettre du prénom de Gilles. Pour cela nous allons utiliser la formule GAUCHE() avec pour premier paramètre la cellule contenant le prénom de l’utilisateur, puis en second paramètre le chiffre 1 pour ne récupérer qu’une seule lettre :

=GAUCHE([@Prénom];1)

Excel formation - Créer-logins-et-mots-de-passe - 09

Ensuite, pour assembler deux chaînes de caractères ensemble, nous allons utiliser le symbole esperluette (« & ») qui permet de réaliser une concaténation rapide de deux chaînes. Pour tout savoir sur la concaténation, veuillez suivre ce lien.

La chaîne que nous allons ajouter à la suite est un simple point (« . »), qu’il va nous suffire de saisir entre guillemets :

=GAUCHE([@Prénom];1)&"."

Excel formation - Créer-logins-et-mots-de-passe - 10

Comme pour le prénom, nous allons maintenant vouloir récupérer les sept premières lettres du nom de famille, grâce à la formule GAUCHE() :

=GAUCHE([@Prénom];1)&"."&GAUCHE([@Nom];7)

Excel formation - Créer-logins-et-mots-de-passe - 11

Vous constaterez au passage que si le nom de famille possède moins de sept caractères, la formule GAUCHE() se contentera de récupérer ce dernier, sans autre traitement.

À présent, nous souhaitons récupérer l’année de naissance de l’utilisateur. Nous pourrions utiliser simplement la formule ANNEE(), mais nous récupèrerions alors nous un nombre formaté sur quatre caractères.

Pour nous limiter à deux caractères seulement, nous allons plutôt choisir d’utiliser la formule TEXTE(), avec pour paramètre de « format_texte » (qui est le seconde paramètre de la formule) le formatage année sur deux chiffres (« aa ») :

=GAUCHE([@Prénom];1)&"."&GAUCHE([@Nom];7)&TEXTE([@Naissance];"aa")

Excel formation - Créer-logins-et-mots-de-passe - 12

Enfin, il ne nous restera plus qu’à insérer la fin de l’adresse mail, sous la forme d’une chaîne de caractères :

=GAUCHE([@Prénom];1)&"."&GAUCHE([@Nom];7)&TEXTE([@Naissance];"aa")&"@excelformation.fr"

Excel formation - Créer-logins-et-mots-de-passe - 13

Rappelez-vous, au tout début de cette partie, nous avions spécifié que l’identifiant de l’utilisateur ne devait être constitué que de minuscules ! Pour cela, nous allons simplement encapsuler la formule que nous venons de saisir en tant que paramètres d’une nouvelle formule : MINUSCULE() :

=MINUSCULE(GAUCHE([@Prénom];1)&"."&GAUCHE([@Nom];7)&TEXTE([@Naissance];"aa")&"@exelformation.fr")

Excel formation - Créer-logins-et-mots-de-passe - 14

Et voilà ! Chaque identifiant est maintenant généré pour l’ensemble des lignes du tableau.

Il ne nous reste plus qu’à créer le mot de passe, mais si vous avez suivit chacune des étapes, cela ne devrait vous poser aucun problème !

 

3. Création automatisé des mots de passe

 

Les règles de création du mot de passe que nous allons générer va suivre à peu près le même principe que ce que nous venons de voir, excepté pour la dernière partie.

Ainsi le mot de passe sera construit de la manière suivante :

  • Première lettre du nom famille en minuscule,
  • Puis première lettre du prénom en majuscule,
  • Puis le caractère pourcentage (« % »),
  • Puis la date de naissance à l’envers avec pour caractère de séparation le tiret (« - »),
  • En enfin un chiffre aléatoire situé entre 1 et 10000

Les trois premiers points ne posent aucun problème, il suffit simplement de reproduire ce que nous avons vu dans le détail dans la partie précédente de ce tutoriel :

=MINUSCULE(GAUCHE([@Nom]))&MAJUSCULE(GAUCHE([@Prénom]))&"%"

Note : Petite astuce pour gagner quelques secondes, par défaut le paramètres [no_car] de la formule GAUCHE() vaut « 1 ». Nous pouvons donc omettre de lui donner une valeur, lorsque nous souhaitons récupérer uniquement un seul caractère.

Excel formation - Créer-logins-et-mots-de-passe - 15

Ensuite, pour la date de naissance, nous allons utiliser à nouveau la formule TEXTE(), avec le formatage suivant : « jj-mm-aaaa », soit le jour de naissance sur deux caractères, suivi du mois (également sur deux caractères) et enfin l’année sur quatre caractères, chacune de ces courtes chaînes de caractères étant séparée par un tiret :

=MINUSCULE(GAUCHE([@Nom]))&MAJUSCULE(GAUCHE([@Prénom]))&"%"&TEXTE([@Naissance];"jj-mm-aaaa")

Excel formation - Créer-logins-et-mots-de-passe - 16

À la fin de notre mot de passe, nous souhaitons afficher un nombre aléatoire compris entre 1 et 10000. Pour récupérer ce nombre, nous allons utiliser la formule dédiée, à savoir ALEA.ENTRE.BORNES().

=MINUSCULE(GAUCHE([@Nom]))&MAJUSCULE(GAUCHE([@Prénom]))&"%"&TEXTE([@Naissance];"jj-mm-aaaa")&ALEA.ENTRE.BORNES(1;10000)

Excel formation - Créer-logins-et-mots-de-passe - 17

Par contre, il faut savoir que lors de chaque recalcul ou enregistrement de la feuille, la valeur retournée par la formule ALEA.ENTRE.BORNES() va être actualisée, donc les mots de passes vont être modifiés !

Pour figer leurs valeurs, nous allons devoir transformer les formules en valeur.

Pour cela, nous allons :

  • Sélectionner la colonne contenant les mots de passe, en cliquant sur l’en-tête de colonne lorsque le curseur prend la forme d’une flèche pointant vers le bas,

Excel formation - Créer-logins-et-mots-de-passe - 18 

  • Copier le contenu de cette colonne en appuyant simultanément sur les touches [Ctrl] et [c],
  • Et enfin coller les valeurs en cliquant sur le bouton Valeurs qui apparaît lorsque l’on déroule le menu déroulant du bouton Coller du ruban (rubrique Accueil)

Excel formation - Créer-logins-et-mots-de-passe - 19 

 



Articles qui pourraient vous intéresser

Comment connecter Excel sur Internet, sans VBA ?
Comment traduire automatiquement des fonctions Excel dans leur version originale ?
Comment analyser les résultats d’un sondage ou questionnaire avec Excel ?
Comment utiliser la fonction SOMME.SI pour effectuer des recherches sur des textes sur Excel ?
Comment calculer et étudier des écarts budgétaires avec Excel ?
Comment verrouiller et protéger un objet (graphique, image, zone de texte…) sur Excel ?
Comment formater des dates correctement dans Excel ?
Comment protéger le formatage des cellules tout en autorisant la saisie de données dans Excel ?
Comment transformer une photo en tableau Excel ?
Comment créer un publipostage automatique avec Excel ? (sans Word !)
Comment créer un graphique Gaufre sur Excel ?
Comment créer un graphique en cascade dans Excel ?