Formule Excel création code

Bonjour,

Je travail sur des listes de sous-espèces au format suivant : Genre espece var. sousespece

Je cherche à créer un code (colonne D) qui serait le suivant : GENRESPES. Les 4 première lettre du 1er mot, les 4 première lettres du second mot, et la 1ère du mot arrivant après le var (ou subsp) point (ci-dessus le 4ème mais ce n'est pas toujours le cas comme vous le verrez dans mon exemple).

Dans le cas où 2 codes seraient les mêmes pour une var. sousespece j'aimerai également avoir la possibilité d'ajouter la 2ème lettre du nom de sous-espèce (le 4ème mot) afin de les différencier, dans l'exemple ci-dessus afficher le O si le code GENREESPES est commun à 2 sous espèces et si ce 0 suffit pour les différencier (cellule D4).

Enfin je cherche également à savoir quelle formule pourrait me permettre d'afficher le nom de sous espèce dans une nouvelle colonne (colonne G).

Je glisse ci-joint l'exemple

Pensez-vous pouvoir m'aider ? Je ne maitrise que peu excel donc merci d'avance pour votre aide

Bonne journée

Cordialement,

Germain

Bonjour,

En C2 et à tirer vers le bas:

=SI(NBCAR($G2)>1;MAJUSCULE(GAUCHE(B2;3) & STXT(B2;CHERCHE(" ";B2;1)+1;4)&G2);MAJUSCULE(GAUCHE(B2;4) & STXT(B2;CHERCHE(" ";B2;1)+1;4)&G2))

en G2 et à tirer vers le bas:

=SI(NB.SI($G$1:$G1;STXT(B2;CHERCHE("var. ";B2;1)+5;1))>0;STXT(B2;CHERCHE("var. ";B2;1)+5;2);STXT(B2;CHERCHE("var. ";B2;1)+5;1))

Cdlt

Bonjour,

Merci beaucoup pour votre réponse. Est-ce que par hasard vous auriez un code en tête pour afficher le nom complet de la sous-espèce (dans mon exemple afficher sodia en entier par exemple) dans une nouvelle colonne ?

Merci

Cdlt

Je me permets d'abuser peut être à tord de votre bienveillance et de votre aide mais votre code rencontre quelques problèmes dans mon jeu de données global et je n'arrive pas à comprendre pourquoi.

- A la ligne 109 et 110 (par exemple) de la première feuille espèces du fichier joint ci-contre, les codes affichés sont respectivement : BARCONVCO & BARCONVSA. Seulement il devrait être affiché BARCONVC et BARCONVS car cela suffit à discriminer ces 2 sous espèces. Ce problème ce répète également plusieurs fois par la suite. Qu'est ce que j'aurai loupé ?

- Enfin, pour la colonne P j'ai cherché une fonction pouvant

permettre d’afficher le deuxième mot de ma cellule (c'est à dire le nom d'espèce). Je cherche également une fonction me permettant d'extraire par exemple pour la 2ème ligne Abietinella abietina var. hystricosa dans la colonne N, puis de récupérer hystricosa pour le placer dans la colonne Q "spp". Comment pourrais-je faire ?

j'espère ne pas abuser de votre gentillesse, merci d'avance

Effectivement, ça se complique. Alors pour faire simple, j'utilise la colonne AD pour y loger une formule intermédiaire qui extrait la partie gauche du nom complet jusqu'à "var. "

La formule en colonne AD:

=SIERREUR(GAUCHE($M2;CHERCHE("var. ";$M2;1)+4);"")

La formule en colonne L:

=SIERREUR(SI(NB.SI($AD$1:$AD1;$AD2)>0;STXT($M2;CHERCHE("var. ";$M2;1)+5;2);STXT($M2;CHERCHE("var. ";$M2;1)+5;1));"")

Cdlt

Bonjour

Merci pour votre réponse seulement mon problème persiste encore... Je me suis peut être mal expliqué

Voici ci-contre 2 exemples

Ligne 140 à 144 du jeu de données partagé :

Ici 1 lettre (la première du nom de sous espèce) suffit pour différencier les sous-espèces (la première ligne étant l'espèce) à chaque fois et donc le code souhaité (en vert) ne devrait afficher qu'une lettre. Ce qui devrait d'ailleurs être le cas sur l'ensemble de ce jeu de données mais pas dans mes autres dont je vous met un exemple ci-contre. Au passage je ne comprend pas comment le code "BRACCANSA" a-t-il pu être attribué, cela ne ressemble en rien au schéma souhaité et utilisé par les autres ?

Ici 2 lettres sont nécessaires pour différencier le code des 2 sous-espèces, il arrive même parfois que 3 lettres soit nécessaires. Est-ce possible avec excel ?

Au passage : existe-t-i-il une fonction pour récupérer un mot après un autre ? par exemple récupérer le 1er mot après le var. ou le subsp. Existe-t-il une fonction permettant de récupérer le nième mot d'une celulle ?

J'espère ne pas vous déranger et vous remerci d'avance pour votre aide bien précieuse

Bonne journée

Bonjour,

1ère question, dans la colonne AD en AD2, remplacez la valeur 4 par 5:

=SIERREUR(GAUCHE($M2;CHERCHE("var. ";$M2;1)+4);""), et tirez la formule vers le bas.

*****************************************************************************************************************

2ème question: "Au passage je ne comprend pas comment le code "BRACCANSA" "

Dans la colonne K, dans le dernier "CHERCHE" de la formule, il y a un décalage, pointe sur la ligne au-dessus, j'en ai profité pour ajouter un contrôle supplémentaire:

en K2 et à tirer vers le bas:

=SI(I2<>"";"";SI(NBCAR($L2)>1;MAJUSCULE(GAUCHE(M2;3) & STXT(M2;CHERCHE(" ";M2;1)+1;4)&L2);MAJUSCULE(GAUCHE(M2;4) & STXT(M2;CHERCHE(" ";M2;1)+1;4)&L2)))

*****************************************************************************************************************

3ème question " il arrive même parfois que 3 lettres soit nécessaires", là il faut que je prenne le temps de regarder comment m'y prendre, donc patientez.

*****************************************************************************************************************

4ème question, "existe-t-i-il une fonction pour récupérer un mot après un autre ? par exemple récupérer le 1er mot après le var. ou le subsp. Existe-t-il une fonction permettant de récupérer le nième mot d'une cellule ?"

Essayez ceci en ligne 2 d'une colonne quelconque

=SIERREUR(STXT(M2;CHERCHE("var. ";$M2;1)+5;CHERCHE(" ";M2;CHERCHE("var. ";$M2;1)+4)-CHERCHE("var. ";$M2;1)+5);"")

Cdlt

Edit: Combien de caractères doit contenir le code ainsi constitué?

Voici la version qui devrait répondre à la 3ème question:

En colonne AE, formule:

=SIERREUR(GAUCHE($M2;CHERCHE("var. ";$M2;1)+6);"")

Modification de la formule en L2:

=SIERREUR(SI(NB.SI($AE$1:$AE1;$AE2)>0;STXT($M2;CHERCHE("var. ";$M2;1)+5;3);SI(NB.SI($AD$1:$AD1;$AD2)>0;STXT($M2;CHERCHE("var. ";$M2;1)+5;2);STXT($M2;CHERCHE("var. ";$M2;1)+5;1)));"")

Super, merci beaucoup pour cette réponse très précise je regarde ce que cela donne et vous tiens en courant dans l'après midi !

Ah oui et concernant le code que je cherche à constituer, il n'est pas limité en nombre de caractères il doit juste en contenir suffisant pour être unique.

Merci encore!

EDIT :

Cela semble marcher à merveille, merci beaucoup pour votre aide cela va beaucoup m'aider dans mes recherches. Une dernière question - pour récupérer le 2ème mot dans une cellule quelle formule utiliser ?

pour extraire le 2ème mot de la cellule M2

=STXT($M2;CHERCHE(" ";$M2;1)+1;CHERCHE(" ";$M2;CHERCHE(" ";$M2;1)+1)-CHERCHE(" ";$M2;1))

Bonjour,

Je me permets de vous envoyer ce message car je viens de m’apercevoir d'une coquille et je n'arrive pas à la résoudre.

En triant dans mon jeu de données ci-joint je me suis rendu compte que les spid (les codes) ne marchaient pas pour les sous-espèces (var. et subsp.). En effet, dans le nom complet par exemple aux lignes 181 et 185 :

Ici le L. semble poser problème pour le bon fonctionnement du code. Une fois enlevé, cela fonctionne. Je me suis donc dit que je pourrais faire marcher la même formule mais sur la variable name (= O, le nom de genre + le nom d'espèce + celui de sous espèce (avec var. ou subsp. devant)). Le seul problème est que des parenthèses persistent dans le nom de sous espèce extraite (exemple "mutica (B" ligne 148) et je n'arrive pas à ne garder que le nom de sous espèce (M141) pour ensuite lui ajouter le var. (L141). De plus, le nom de sous espèce extrait dans la colonne sous-espèce est parfois trop court et il manque les dernières lettres comme par exemple : pyramidal au lieu de pyramidalis ligne 182.

Comment auriez-vous fait à ma place ?

Merci pour votre aide

Bonne journée

Bonjour,

Pas sûr d'avoir tout bien compris. Si ce n'est qu'une histoire de parenthèse à éliminer, il suffit d'utiliser la fonction "substitue" pour éliminer les parenthèses ouvrantes et fermantes, ainsi par exemple la formule en AF qui était:

=SIERREUR(GAUCHE($M2;CHERCHE("var. ";$M2;1)+5);"") devient:

=SIERREUR(GAUCHE(SUBSTITUE(SUBSTITUE($M2;"(";"");")";"");CHERCHE("var. ";SUBSTITUE(SUBSTITUE($M2;"(";"");")";"");1)+5);"")

en bleu, un premier substitue supprime la parenthèse ouvrante, en rouge, un deuxième substitue encapsule le résultat précédent et supprime la parenthèse fermante

Principe à appliquer dans les formules concernées.

Cdlt

Merci pour votre réponse. Cela corrige mon problème de code mais pas le problème du nom de sous espèce qui n'apparait parfois pas en entier dans les colonnes correspondantes (les colonnes S R et Q).

Par exemple, var. juratzkanum apparait en var. juratzkan. à la ligne 25. A quoi pensez-vous que cela est lié ?

Merci

Cdlt

Au passage j'ai modifié mon message initial en mettant le bon fichier de données et les bonnes explications qui correspondent. L'avez-vous vu ?

bonjour,

une proposition avec une macro. (vérifier si en ligne 1826, c'est bien le code attendu). il faut adapter la macro si la structure du tableau change, (colonne déplacée, ajoutée ou supprimée). Macro à lancer via alt-F8

Sub aargh()
' correspondance colonne données
    colfullname = "M"
    colname = "N"
    colgen = "O"
    colspidf = "J"
    colsp = "P"
    colssp = "Q"
    With Sheets("feuil1") 'feuille contenant les données
        dl = .Cells(Rows.Count, colfullname).End(xlUp).Row 'nombre de lignes
        Set dict = CreateObject("scripting.dictionary") 'dictionnaire pour détecter les doublons au niveau du code généré
        For i = 2 To dl 'pour chaque ligne
            n = .Cells(i, colfullname) 'on prend le nom
            ns1 = Split(n) 'on découpe en fonction des espaces
            .Cells(i, colname) = ns1(0) & " " & ns1(1) 'name
            .Cells(i, colgen) = ns1(0) 'gen
            .Cells(i, colsp) = ns1(1) 'sp
            covo = UCase(Left(ns1(0), 4) & Left(ns1(1), 4)) 'code
            ns2 = Split(n, " var. ") 'on découpe en fonction de var.
            If UBound(ns2) = 0 Then 'si var non trouvé
                ns2 = Split(n, " subsp. ") 'on découpe en fonction de subsp.
            End If
            If UBound(ns2) > 0 Then 'si var ou subsp trouvé
                .Cells(i, colssp) = Split(ns2(1))(0) 'ssp
                k = 1
                covo = UCase(Left(ns1(0), 4) & Left(ns1(1), 4) & Left(ns2(1), 1)) 'code
                Do While dict.exists(covo) 'tant que le code généré existe
                    k = k + 1 'on génère un nouveau code en prenant la lettre suivante de ssp
                    covo = UCase(Left(ns1(0), 3) & Left(ns1(1), 4) & Left(ns2(1), 1) & Mid(ns2(1), k, 1))
                    If k > Len(ns2(1)) Then Stop 'on epuisé toutes les possibilités de générer un code
                Loop
            End If
            dict(covo) = covo 'code unique trouvé, on l'ajoute au dictionnaire
            .Cells(i, colspidf) = covo 'code en colonne spid_final
        Next i
    End With
End Sub

Bonjour et merci pour vos réponses j'ai pu bien avancer seulement un dernier problème persiste.

Dans l'un de mes jeu de données lorsque je souhaite récupérer le nom de sous-espèce (en O2) il ne le récupére parfois pas en entier comme vous pouvez le voir dans l'exemple ci-contre. A quoi cela est-il dû ?