XL 2019 PRESQUE RESOLU : Remplacer RecherchV complexe pour correspondance INPN et cardobs par VBA

lusert · 21 Avril 2020

Bonjour à tous !

Je m'adresse aujourd'hui à vous car malgré de nombreuses recherche, je n'arrive pas à trouver de solution à mon problème.

Pour me faciliter la retranscription des espèces que j'observe, je mets en lien le fichier Taxref et le fichier ou j'ai saisie mes noms d'espèces animales.
Ce fichier taxref est ma base de donnéeil y a plus de 200 000 lignes. Il se compose d'une colonne A avec toutes les espèces de france et de 20 autres colonnes (de B à AA) composé des champs de correspondance à l'espèce (par exemple A : Pic vert et en trouve en B : la famille de l'oiseaux et en C : en iddentifiant... ainsi de suite).

Nom latin	Nom Français	ID	Famille	ect	ect	ect
Picus viridis	Pic Vert	4562	Picidés	ect	ect	ect
Parus major	ect	ect	ect	ect	ect	ect

Mon deuxième fichier correspondant aux espèces que j'ai observé, il y a donc une seul colonne de rempli de nom d'espèces et je souhaite que les colonne de B à AA soient complété par la formule recherchV (que vous pouvez lire en cellule B3 =SI(ESTNA(RECHERCHEV($A3;BD!$A$2:$H$12;B$1;FAUX));;(RECHERCHEV($A3;BD!$A$2:$H$12;B$1;FAUX)))

	1	2	3	4
Nom latin	nom latin	Nom francais	Id	Famille
Picus viridis	=SI(ESTNA(RECHERCHEV($A3;BD!$A$2:$H$12;B$1;FAUX));;(RECHERCHEV($A3;BD!$A$2:$H$12;B$1;FAUX)))	Pic vert	4562	Picidés

Avec cette formule je peux aisément trouver les champs de correspondances aux espèces que j'ai observé afin d'être au norme avec la directive INPN.
Le problème c'est que ma base de donnée est lourde, très lourdes et que ce retrouver avec plus de 500 liaisons, ça rend les mises à jours de sliasions ou des enregistrement assez long ( 15 à 25 mn d'attente).

Je me suis donc inspiré de cette macro ci-dessous, extraite d'un fichier excel modèle d'un forum mais j'ai oublié lequel)

Sub Bouton1_Cliquer()
For i = 2 To 1000
Range("M" & i) = WorksheetFunction.VLookup(Range("e" & i), Range("$a$2:$c$1000"), 3)
Next i
End Sub

Elle fonctionne mais que sur une seul et même feuille et on ne peut pas faire apparaître plus d'une colonne de correspondance.
Je suis pas assez doué pour comprendre comment transposé ma formule =SI(ESTNA(RECHERCHEV($A3;BD!$A$2:$H$12;B$1;FAUX));;(RECHERCHEV($A3;BD!$A$2:$H$12;B$1;FAUX))) en macro avec un bouton à cliquer.

Si vous avez des pistes je vous en remercierez 1000 fois car je sèche complément sur ce sujet.

Cordialement,

ps : je peux joindre mon fichier excel pour aider à visualiser mon problème

Lusert

sylvanu · 21 Avril 2020

Re, ça marche mais un peu bidouillée, mais en tout cas surement plus rapide.
Un savant mix up de nos deux solutions permettant de simplifier la vie à Lusert sur la base utilisée:

Code:

Sub RapatrieParametres()
Dim Derlig As Long, IndexL As Long, L As Long, C As Long, Base As String
' Declarer base à utiliser
Base = "BD_Insectes"
'-------------------------
Application.ScreenUpdating = False
Derlig = Application.WorksheetFunction.CountA(Range("A:A"))
With Sheets(Base)
    For L = 2 To Derlig
        IndexL = 0
        On Error Resume Next
        IndexL = Application.Match(Cells(L, 1), .Range("A:A"), 0)
        If Not IsError(IndexL) And IndexL <> 0 Then
            For C = 1 To 40
                Cells(L, C + 1) = .Cells(IndexL, C)...

sylvanu · 21 Avril 2020

Bonjour Lusert,

Quand vous parlez de fichiers, vous voulez parler de feuilles ? Car dans vos formules je n'ai pas vu de lien vers des fichiers externes.
Pouvez vous fournir un petit fichier exemple, juste avec une vingtaine de lignes pour voir la structure de votre BD, et la tête de la feuille que vous remplissez.

Attendre 25min un résultat, je confirme, c'est long !

Staple1600 · 21 Avril 2020

Bonjour le fil, lusert, sylvanu

Juste de passage:
• Pour remercier lusert pour sa question
Qui m'a permis de découvrir ce site

INPN - Base de connaissance<br/>« Statuts »

inpn.mnhn.fr

(je mets le lien au cas où cela intéresserait d'autres lecteurs du fil)

Sinon, pour la question, joins simplement une version allégée de ton classeurs
(une trentaine de lignes par feuille suffira)

lusert · 21 Avril 2020

sylvanu à dit:
Bonjour Lusert,

Quand vous parlez de fichiers, vous voulez parler de feuilles ? Car dans vos formules je n'ai pas vu de lien vers des fichiers externes.
Pouvez vous fournir un petit fichier exemple, juste avec une vingtaine de lignes pour voir la structure de votre BD, et la tête de la feuille que vous remplissez.

Attendre 25min un résultat, je confirme, c'est long !

Je me suis mélangé les idées en cours de route. L'idéal serait de pouvoir appliquer la VBA entre deux fichiers en partant de cette formule =SI(ESTNA(RECHERCHEV($A10;'C:\Users\Taxref\[TAXREFv13.xlsx]TAXREFv13'!$A$429944:$AN$585853;AC$1;FAUX));"";(RECHERCHEV($A10;'C:\Users\Taxref\[TAXREFv13.xlsx]TAXREFv13'!$A$429944:$AN$585853;AC$1;FAUX)))

Le fait d'ouvrir le fichier base de donnée Taxref avec mon fichier de saisie permet d'accélérer la recherche mais ça reste beaucoup trop long et j'ai entendu dire que la VBA était adapté pour les fichiers assez lourd. J'ai quand même peur que malgré la vba le traitement reste long d'où mon idée d'intégrer le tout dans un même fichier, avec deux feuille différentes ( une BD et une saisie) ?

Voila ! J'espère qu'avec ces informations vous visualisez un peu mieux mon idée ?

sylvanu · 21 Avril 2020

un p'ti fichier , s'il vous plait.
ça parle mieux qu'un long discours et on pourras vous le renvoyez avec la macro dedans.
Le mieux effectivement, c'est une feuille avec la BD et une ou des feuilles avec les observations. Ce sera infiniment plus rapide que d'accéder à un fichier externe.

Staple1600 · 21 Avril 2020

Re

C'est toujours un plaisir renouvelé d'être superbement ignoré...
(cf le message#3)
Mais c'est rare de l'être par deux fois...

lusert · 21 Avril 2020

Staple1600 à dit:
Bonjour le fil, lusert, sylvanu

Juste de passage:
• Pour remercier lusert pour sa question
Qui m'a permis de découvrir ce site

INPN - Base de connaissance<br/>« Statuts »

inpn.mnhn.fr

(je mets le lien au cas où cela intéresserait d'autres lecteurs du fil)

Sinon, pour la question, joins simplement une version allégée de ton classeurs
(une trentaine de lignes par feuille suffira)

Bonjour Staple1600,
Il m'est impossible de réduire les champs à 30 lignes. Le mieux que j'ai pu faire a été de diviser le fichier en 3 , c'est à dire une feuille de 15000 données pour la flore, une de 30 000 pour les animaux et une de 60000 pour les insectes. De cette façon le traitement de ma formule recherchV est plus allégé. POur ça je suis d'accord avec vous / Le problème c'est que j'aimerais que mon fichier avec macro puisse être utilisé par des naturalistes qui sont pas doué avec l'informatique. Ils auraient juste à copier leur donnée dans la feuille saisie en colonne A et cliquer sur le bouton pour afficher les correspondances à leur espèces notées

sylvanu · 21 Avril 2020

Bonjour Staple,
Comme vous l'aurez remarqué Lusert s'est inscrit hier.
D'ailleurs il s'est mélangé les pinceaux. Voir post #7.
Rien de volontaire.

Lusert nous sommes deux à vous poser des questions !

sylvanu · 21 Avril 2020

Lusert, si le fichier dépasse 1Mo ( ce qui doit être le cas ) passez par ce lien.
Pour nous qu'il ait 20 ou 20000 lignes, c'est la même chose.
Ce qui me dérange c'est que vous ne puissiez le réduire à 30 lignes.
J'ai peur de la structure de la BD qu'on va découvrir.

Staple1600 · 21 Avril 2020

Re

Oui, comme on remarquera également, que mes messages sont remplis d'emoticones qui adoucissent un chouia l'amicale ironie que ceux-ci affichent.
(NB: On remarquera également que je remerciais le demandeur sans ironie aucune dans ce même message#3)

PS: sylvanu, c'est pas d'hier, que nous croisons sur XLD et pourtant...
(cf les salutations du message#3)

Staple1600 · 21 Avril 2020

Re

•>lusert
Tu connaissais (je suppose que oui)le site que j'ai mis en lien?

•>sylvanu
Effectivement, quand on sait que le fichier zip qu'on trouve sur le site que je cite fait 49Mo

sylvanu · 21 Avril 2020

Re,
Si j'avais vu, mais Lusert ne parle surement pas de celui là.
Le zip contient un csv de 446Mo, fichier de 851843 lignes !
Si c'est avec ça que Lusert bosse, ça ne m'étonnes pas que ça prenne 25 min.
J'espère qu'il a une version "allégée".

Hasco · 21 Avril 2020

Bonjour,

Une extraction par PowerQuery de toutes les lignes de la base 'BDC_STATUTS_13.csv' dont le champ 'LB_Nom' commence par 'Bombus'
Important : Le fichier excel 'Bombus.xls' et la base doivent être dans le même répertoire.
Merci Staple pour le lien.
Cordialement

lusert · 21 Avril 2020

Pardonnez-moi ! Je découvre en même temps que j'écris le fonctionnement du forum. Il m'avait semblé vous avoir répondu Staple ?
Alors oui j'utilise régulièrement le site de l'INPN, d'ailleur le fichier ici-présent présente une extraction des données de l'INPN.

Voila le fichier en question, que vous trouveez depuis ce lien wetransfert . Je vous ai transformer le fichier en fichier pour macro, j'ai laissé les formules recherchV pour vous donnez une idée de ce que je cherche à faire.

lusert · 21 Avril 2020

C'est effectiment avec ce type de fichier lourd que j'utilise la fonction recherchV.

Ps : comment mettre un fcihier joint san spasser par un lien we transfert ici sur ce forum ?

XL 2019 PRESQUE RESOLU : Remplacer RecherchV complexe pour correspondance INPN et cardobs par VBA

lusert

XLDnaute Junior

sylvanu

sylvanu

XLDnaute Barbatruc

Staple1600

XLDnaute Barbatruc

INPN - Base de connaissance<br/>« Statuts »

lusert

XLDnaute Junior

sylvanu

XLDnaute Barbatruc

Staple1600

XLDnaute Barbatruc

lusert

XLDnaute Junior

INPN - Base de connaissance<br/>« Statuts »

sylvanu

XLDnaute Barbatruc

sylvanu

XLDnaute Barbatruc

Staple1600

XLDnaute Barbatruc

Staple1600

XLDnaute Barbatruc

sylvanu

XLDnaute Barbatruc

Hasco

XLDnaute Barbatruc

Pièces jointes

lusert

XLDnaute Junior

lusert

XLDnaute Junior

Discussions similaires