XL 2013 Création des codes alphanumériques à partir des noms et dates de naissance

[sadlersmith]

Bonjour à tous,
J'ai un sérieux problème et espère que vous pourrez m'apporter une solution.
Je dois créer dans excel des codes alphanumériques à partir des noms, dates de naissance et ordre d'arrivée.
Je mets également en pièces jointes le fichier excel du travail.
Merci d'avance

GARDOU SIMON 15-05-1990 M
HOUBBY LEA 1995-12-13 F
GARGAMEL SAMUEL 1990-05-15 M
BOUCHARD JACQUES 1994-09-18 M
GARTER SANDRA 1990-05-15 F
BOUCHARD JULIEN 1994-09-18 M
GAROLOU SAM 1990-05-15 M
DUBREUIL JULIE 1994-11-01 F
FARNENT FLORENT 1995-01-04 M
VOYER BERNARDE 1995-08-14 F
VOYER BERTHE 1995-08-13 F

• Au total il y a 12 caractères
• 3 premiers caractères : 3 premières lettres du nom de famille
• 4ième caractère : première lettre du prénom
• Caractères 5 et 6 : jour de naissance
• Caractères 7 et 8 : mois de la naissance (si sexe féminin ajouter 50)
• Caractères 9 et 10 : deux derniers chiffres de l’année de naissance
• Caractères 11 et 12 : le «rang» occupé par le code permanent selon l’ordre du «premier arrivé premier servi». Par exemple, si deux personnes sont nées à la même date et que les 3 premières lettre de leur nom de famille sont identiques ainsi que la première lettre de leur prénom, le premier inscrit aura comme fin de code permanent «01» alors que le second aura «02».

 

[sadlersmith]

Re : Création des codes alphanumériques à partir des noms et dates de naissance

Merci bien Cisco,
Cette formule(dixpremiers&TEXTE(SOMMEPROD((dixpremiers=GAUCHE(G$15:G15;10))*1)+1;"00")) que tu as proposée semble vraiment efficace mais je ne la comprends pas vraiment. Je ne sais pas comment t'as fait pour que la fonction "dixpremiers" parte chercher les données dans les colonnes A, B, C, D, et on a G dans la formule alors qu'on est dans cette même colonne.
Celle-ci (dixpremiers=GAUCHE(A16;3)&GAUCHE(B16;1)&TEXTE(JOUR (C16);"jj")&TEXTE(SI(D16="F";MOIS(C16)+50;MOIS(C16 ));"00")&DROITE(ANNEE(C16);2)) par contre ne marche pas.
Possible de m'expliquer?

 

[CISCO]

Re : Création des codes alphanumériques à partir des noms et dates de naissance

Bonjour

Pour t'aider, une petite explication :

J'ai défini le nom dixpremiers dans -->Formules-->gestionnaire de noms comme suit :
dixpremiers=GAUCHE(A16;3)&GAUCHE(B16;1)&TEXTE(JOUR (C16);"jj")&TEXTE(SI(D16="F";MOIS(C16)+50;MOIS(C16 ));"00")&DROITE(ANNEE(C16);2)
parce que je l'utilise deux fois dans la formule finale, histoire de ne pas avoir à écrire une formule longue. Ainsi, Excel, avec dixpremiers, va chercher les 3 premiers caractères de A16 avec GAUCHE(A16;3), auquel il ajoute le premier caractère de B16 avec &GAUCHE(B16;1) et ainsi de suite. Le & remplace la fonction CONCATENER.

Si tu as du mal à comprendre cette formule, écris là dans une cellule précédée du signe égale, puis :
* soit clique dans la barre de formule sur chacune des fonctions. Une fenêtre s'ouvrira, te détaillant les paramètres utilisés et les résultats obtenus
* soit surligne chaque partie de la formule dans la barre de formule, puis F9. Le résultat de la partie surlignée apparaîtra dans la barre de formule. Echap pour revenir à l'affichage normal.
* soit --> Formules --> Evaluation d'une formule (la petite loupe avec fx). Tu verras le détail des calculs dans la nouvelle fenêtre.

Avec dixpremiers, j'ai les dix premiers caractères du code que tu désires, par exemple GARS150590. Manque plus qu'à compléter la formule pour avoir les deux derniers. Si c'est la première fois qu'on obtiens ce code dans la colonne G, GARS150590, il faut lui rajouter 01, si c'est la seconde fois, 02, la troisième, 03 et ainsi de suite.

En G16, la formule dixpremiers&TEXTE(SOMMEPROD((dixpremiers=GAUCHE(G$15:G15;10))*1)+1;"00") compare dixpremiers au 10 premiers caractères de G15 avec dixpremiers=GAUCHE(G$15:G15;10), donc à "", et renvoie FAUX (dixpremiers n'est pas égal à ""). (dixpremiers=GAUCHE(G$15:G15;10))*1 donne 0 (=FAUX*1) et SOMMEPROD((dixpremiers=GAUCHE(G$15:G15;10))*1)+1 renvoie 0+1, donc 1. TEXTE(0;"00") donne 01.

Et on recommence presque la même chose en dessous.

En G21, la formule dixpremiers&TEXTE(SOMMEPROD((dixpremiers=GAUCHE(G$15:G20;10))*1)+1;"00") compare, avec la partie dixpremiers=GAUCHE(G$15:G20;10), le nom dixpremiers, c-à-d BOUJ180994, non plus seulement à G15, mais à G15, G16, G17, G18, G19 et G20, ce qui donne {FAUX;FAUX;FAUX;FAUX;VRAI;FAUX}, qui, une fois *1, donne {0;0;0;0;1;0}. Cela donne avec SOMMEPROD(....)+1 le nombre 2, et avec TEXTE, les caractères 02.

La formule fonctionne grâce au SOMMEPROD, car, si on avait écrit seulement SOMME, la comparaison ne porterait que sur G15, pas sur la série G15, G16, G17... même si c'est écrit GAUCHE(G$15:G20.

@ plus

 

[sadlersmith]

Re : Création des codes alphanumériques à partir des noms et dates de naissance

Merci bien Cisco,
Cette formule(dixpremiers&TEXTE(SOMMEPROD((dixpremiers=GAUCHE(G$15:G15;10))*1)+1;"00")) que tu as proposée semble vraiment efficace mais je ne la comprends pas vraiment. Je ne sais pas comment t'as fait pour que la fonction "dixpremiers" parte chercher les données dans les colonnes A, B, C, D, et on a G dans la formule alors qu'on est dans cette même colonne.
Celle-ci (dixpremiers=GAUCHE(A16;3)&GAUCHE(B16;1)&TEXTE(JOUR (C16);"jj")&TEXTE(SI(D16="F";MOIS(C16)+50;MOIS(C16 ));"00")&DROITE(ANNEE(C16);2)) par contre ne marche pas.
Possible de m'expliquer?

 

[CISCO]

Re : Création des codes alphanumériques à partir des noms et dates de naissance

Bonjour

Merci bien Cisco,
...
Possible de m'expliquer?

Cf. ci-dessus pour l'explication concernant "ma" formule.
Pour ce qui est de ta formule, (dixpremiers=GAUCHE(A16;3)&GAUCHE(B16;1)&TEXTE(JOU R (C16);"jj")&TEXTE(SI(D16="F";MOIS(C16)+50;MOIS(C16 ));"00")&DROITE(ANNEE(C16);2)), elle ne fonctionne que dans la colonne G car je n'ai pas mis de $ devant les lettres désignant les colonnes. Si tu veux utiliser ce nom, dixpremiers, ailleurs, il faut aller cliquer sur une cellule de la colonne G, puis dans le gestionnaire de noms, cliquer sur le nom dixpremiers, et mettre un $ devant chaque lettre correspondant à une colonne.
Tu auras alors, où que tu sois dans cette feuille sur la ligne 16 (sauf en colonnes A, B, C et D bien sûr)
(dixpremiers=GAUCHE($A16;3)&GAUCHE($B16;1)&TEXTE(JOUR ($C16);"jj")&TEXTE(SI($D16="F";MOIS($C16)+50;MOIS($C16 ));"00")&DROITE(ANNEE($C16);2))

@ plus

 

[sadlersmith]

Traitement des achats

Bonjour à tous,
S'il vous plait, je vous reviens avec une autre preoccupation.
J'ai commencé un travail dans excel sui bloqué actuellement parce les formules que j'essaye ne me donnent pas le résltat.
J'ai dans un fichier liste des achats faits chez les fournisseurs et dois effectuer le traitement dans l'ordre suivant:

1- dresser la liste des fournisseurs en ordre alphabétique. Tous les fournisseurs ne doivent être présents qu’une seule fois.
2- dresser un tableau où l’on peut voir le montant total d’achats par fournisseur en ordre décroissant (selon le montant)
3- Je souhaite catégoriser les fournisseurs en fonction du volume d’achat selon le barème suivant :
• 0-10 000(exclus)$ : 1-petit
• 10 000$ -50 000(exclus)$ : 2-moyen
• 50 000$-100 000$(exclus) : 3-grand
• 100 000$- 200 000$(exclus) : 4-très grand
• 200 000$ et plus : 5-énorme
4- dresser un tableau dans lequel on peut voir les informations suivantes par catégorie de fournisseur : nombre de fournisseurs et volume d’achat
5- À l’aide de la matrice fournie dans le fichier excel, je dois fournir les informations suivantes dans un seul tableau : volume d’achat par pays et volume d’achat par méthode de paiement
6- Tous les fournisseurs offrent une remise en fonction du volume d’achats. Au bas de la page suivante, vous trouverez un tableau qui indique le % de remise par fournisseur. je dois calculer la remise qui sera versée par fournisseur.
7- dresser une liste des numéros de contrôles qui n’ont pas été traités dans la bonne période. Une période correspond à un mois. La liste doit contenir le numéro de contrôle, le montant et être en ordre décroissant selon le montant.
8- Je dois retrouver certaines factures avec certaines caractéristiques ci-dessous Veuillez dresser une liste de ces numéros de factures (ordre décroissant selon montant)
• Il s’agit d’une facture du dernier jour du mois (donc le 30 pour les mois qui ont 30 jours, le 31 pour les mois qui ont 31 jours et le 28 pour le mois de février)
• Le numéro de compte commence par «4» ou «5»
• La facture est supérieure à 100$
Je vous transmets en pièce jointe le fichier excel qui porte la base des données. La feuille nommée "1 et 2" c'est ce j'ai déjà fait et je ne parviens plus à avancer.
Merci une fois de plus de votre aide.

sadlersmith

 

[CISCO]

Re : Création des codes alphanumériques à partir des noms et dates de naissance

Bonsoir

Apparemment, le fichier n'est pas passé... Essaye de le simplifier (On n'a certainement pas besoin de tous les fournisseurs, toutes les données...) ou de le zipper.

@ plus

 

[Staple1600]

Re : Création des codes alphanumériques à partir des noms et dates de naissance

Bonsoir à tous, CISCO

sadlersmith
Juste ce conseil en passant avant le dodo
Vu qu'il s'agit d'un question tout à fait différente de la question du premier message, il serait plus judicieux d'ouvrir une nouvelle discussion, non ?

 

[sadlersmith]

Re : Création des codes alphanumériques à partir des noms et dates de naissance

Salut à tous,
CISCO et Staples1600 merci pour les remarques.
En passant STAPLES1600 stp j'ai pas compris ce que tu veux par "dodo"
Je le fais tout de suite.