Discussion :

[PrinceCorwin]

Alors là !

={SOMME((NON(ESTERR(CHERCHE(B2;Substances))))*1)}

Pièce jointe 576673

je m'incline bien bas et vous salue bien haut
En utilisant simplement 4 fonctions de base !
Merveilleux

PS : Après au nombre de lignes à contrôler, je ne sais pas quel sera le moins lourd, si je puis me permettre :p
Est-ce que la somme fonctionnera avec les 12'000 substances x 21'000 compositions ?

[Pierre Fauconnier]

Salut PrinceCorwin

[...]
En utilisant simplement 4 fonctions de base !
Merveilleux
[...]

Les formules matricielles permettent effectivement de belles choses, mais elles sont très gourmandes

[...]
PS : Après au nombre de lignes à contrôler, je ne sais pas quel sera le moins lourd, si je puis me permettre :p
Est-ce que la somme fonctionnera avec les 12'000 substances x 21'000 compositions ?

12 000 * 21 000 = 252 000 000 de calculs. C'est sûr que la matricielle ne tient pas la route. J'ai testé avec effectivement 12000 "substances" de 10 caractères chacune, et de 21000 "recettes" de 6 caractères chacune, de façon à simuler la recherche d'un petit texte dans un plus grand. C'est trèèèèèès lent, car la matricielle va travailler sur toutes les lignes sans s'arrêter dès que la solution est trouvée.

J'ai alors tenté une approche semblable à la tienne, par une fonction perso qui s'arrête dès que la substance de la recette a été trouvée:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
Function InList(Value As String, List As Range)
  Dim l
  Dim i As Long: i = 1
 
  l = List.Value
  Do While i <= UBound(l) And Not InList
    InList = (InStr(1, l(i, 1), Value, vbTextCompare) > 0)
    i = i + 1
  Loop
End Function

C'est à peine mieux, mais j'ai peu de substances trouvées. Le résultat serait probablement bien meilleur si presque toutes les substances des recettes se trouvaient dans la liste officielle, ce qui n'est pas le cas de mon test. De ton côté, ta fonction perso est rapide sur un volume pareil?

Pour ce genre de volume de traitement, je pense qu'il est préférable de travailler sur le paquet d'un seul coup. Toujours dans l'idée de sortir le plus vite possible de la boucle, on pourrait utiliser trois arrays, celui des substances, celui des substances des recettes et un troisième dans lequel on placerait le résultat de la recherche. C'est un peu meilleur mais c'est encore très long

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
Sub CalculateIfInList()
  Dim s, r
  Dim i As Long, j As Long
 
 
  s = Range("tableau1[substances]").Value
  r = Range("tableau2[valeur]").Value
  ReDim t(1 To UBound(r), 1 To 1)
  For i = 1 To UBound(r)
    j = 1
    Do While j <= UBound(s) And IsEmpty(t(i, 1))
      If InStr(1, s(j, 1), r(i, 1), vbTextCompare) > 0 Then t(i, 1) = True
      j = j + 1
    Loop
  Next i
  Range("tableau2[présent]").Value = t
End Sub

J'ai alors eu l'idée de concaténer les substances légales, on arrive à une dizaine de secondes avec mes données alétatoires. Avec des textes plus longs pour les substances légales, on va un peu allonger le temps de traitement:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
Sub Testfill()
  Dim s
  Dim z
  Dim i As Long
  Dim r
 
  s = Range("tableau1[substances]").Value
  For i = 1 To UBound(s)
    z = z & s(i, 1)
  Next
  r = Range("tableau2[Valeur]").Value
  ReDim p(1 To UBound(r), 1 To 1)
  For i = 1 To UBound(r)
    If InStr(1, z, r(i, 1), vbTextCompare) Then p(i, 1) = True
  Next
  Range("tableau2[présent]").Value = p
End Sub

Je reste malgré tout persuadé que pour des traitements pareils, il est préférable de préparer les données en nettoyant les listes. Normalement, la substance devrait être extraite de la nomenclature officielle, et les substances composant une recette devraient être placées dans un tableau prévu pour. Je doute que la présentation proposée dans ce message soit adéquate (12000 substances = 12000 colonnes??), ne serait-ce qu'au vu du nombre de substances et de recettes...

Perso, je verrais mieux une organisation "base de données" permettant de réaliser tous les traitements possibles. Mais comme je n'ai pas encore bien compris la finalité de ce traitement, il m'est incommode de répondre plus en détails.

[Boboxien]

Bonjour à tous,

Perso, je verrais mieux une organisation "base de données" permettant de réaliser tous les traitements possibles. Mais comme je n'ai pas encore bien compris la finalité de ce traitement, il m'est incommode de répondre plus en détails.

Désolé si je ne suis pas clair, j'avais pourtant l'impression du contraire.
Je vais essayer de faire un peu mieux:
L'Anses (Agence nationale de sécurité sanitaire de l’alimentation, de l’environnement et du travail) met à disposition plusieurs fichiers:
- une liste de Substances actives (type glyphosate, chlormequat, tébuconazole...), pour chaque substance, il est indiqué si elle est autorisée ou interdite.
- une liste de produits phytopharmaceutique (type Roundup, Amitra, Mexator...). Chaque produit est (ou était) utilisable sur une ou plusieurs cultures (blé, maïs, bettrave...), a une ou plusieurs actions (herbicide, insecticide, régulateur de croissance...) et a une composition (ce que j'ai régulièrement appelé recette).
J'aimerai intégrée ces listes dans Access pour pouvoir faire une recherche sur une Substance active qui me permettra de savoir si cette substance est utilisée sur du maïs, du blé... agit en tant qu'herbicide, insecticide... Sauf que la Substance active n'apparait que dans la "recette" ou composition du produit. Donc pour que je puisse faire un lien sur Access entre ces 2 tables, à mon sens (mais je me trompe peut être), il faut que pour chaque produit, j'extrais la substance active qui est utilisée dans le produit. Et je prépare ces 2 tables sous Excel parce que ça me semblait être le plus simple.
La difficulté est que le nom des substances est parfois complexe (exemple: "1,3,5-tri-(2-hydroxyethyl)-hexa-hydro-s-triazyne"), il y a parfois aussi des substances dont le nom est très proche exemple: le "mecoprop" est interdit, mais le "mecoprop-p" est autorisé. Donc il faut que je me base sur le nom exact présent dans la liste des Substances actives pour extraire la substance utilisée de la recette.

Pour répondre à PrinceCorwin: ta dernière proposition que j'ai testé hier soir semble plutôt bien fonctionner, puisque j'ai pu extraire la Substance active pour 90% des produits. En regardant rapidement, certaines substances ne ressortent pas correctement dans la colonne B, d'autres ont des soucis d'orthographe dans la composition (donc là, impossible de régler le problème, sauf à corriger les noms de substances) et enfin certaines substance n'apparaissent pas dans le listing fourni (j'ai du mal à comprendre comment c'est possible...)

Pour répondre à Igloobel, ta réponse était ce que je cherche, mais malheureusement, la fonction "CONCAT" n'existe pas. L'équivalent est probablement "CONCATENER", mais lorsque je l'ai tester, j'avais une erreur sur la formule. Il semblerait que la fonction ne marche pas sur une plage de cellule. Il faudrait donc que je rajoute manuellement chacune des 1200 cellules (il n'y a que 1200 substances et pas 12000 comme je l'ai vu dans certains messages), sachant qu'il y a en plus la limite de 255 chaine. Donc il faudrait que je le fasse 6 fois (5 fois pour arriver à 1200 + 1 fois pour concaténer les 5 cellules) et il faut être sûr que je n'oublie aucune cellule...

Et je viens seulement de voir les 2 derniers message donc je n'ai pas encore eu le temps de les comprendre et de les tester.

J'espère que ma demande est un peu plus clair, et tout cas, merci à vous de passer du temps à m'aider puisque j'ai interrogé les services de l'état et on m'a répondu (votre projet est ambitieux et tout le monde aimerait faire ce que vous cherchez à faire, donc bon courage). Donc j'espère que mon objectif n'est pas irréalisable...

Cordialement,

[PrinceCorwin]

Bonjour à tous,
Pour répondre à PrinceCorwin: ta dernière proposition que j'ai testé hier soir semble plutôt bien fonctionner, puisque j'ai pu extraire la Substance active pour 90% des produits.

Bonjour,

Pourrais-tu donner quelques exemples des substances qui n'ont pas été extraites dans la colonne B ?
Voir si on ne pourrait pas faire les améliorations qui s'imposent ?

Et s'il n'y a plus que 1'200 substances au lieux de 12'000, ca ira bien plus vite pour obtenir le résultat !
Merci

[Pierre Fauconnier]

Merci pour ces explications.

J'en reviens à ma dernière proposition, surtout si tu souhaites passer sur Access par la suite:

• Nettoyer la liste légale téléchargée pour en extraire le terme exact de la substance. Je pense que la proposition de PrinceCorwin t'aide dans la majorité des cas. Tu règles le reste à la main (Ah, les joies des ronds de cuir de l'administration infoutus de proposer des listes correctes et exploitables);
• Etablir une table des substances avec leur utilité (s'il n'y en a qu'une par substance, par exemple uniquement insecticide ou uniquement fongicide, ...);
• Si plusieurs utilités par substance (insecticide + fongicide, ...), prévoir une table Access des utilités et une table Access (liaisons plusieurs à plusieurs) pour faire le lien entre substances et utilités;
• Etablir une liste des produits (Roundup, ...) avec leur subtance active s'il n'y en a qu'une;
• Etablir la table qui reprend la (les) substance(s) active(s) par produit si plusieurs substances actives par produit.

Vu l'état de tes données et de tes recettes, il n'y a pas de miracles et tu ne pourras pas faire l'économie des tables ci-dessus (mais ça devient de l'Access et plus de l'Excel, sauf peut-être pour préparer les tables à injecter dans Access).

[Pierre Fauconnier]

[...]
Et s'il n'y a plus que 1'200 substances au lieux de 12'000, ca ira bien plus vite pour obtenir le résultat !
Merci

Du coup et au vu des explications, je doute qu'il y ait 21000 recettes => peut-être ma formule pourrait-elle tenir la route (si c'est 1200 substances et 2100 recettes, je pense que ça peut le faire)


EDIT: Instantané

[Igloobel]

Bonjour à tous,

Bon j'ai cherché "=Concat" et "=Joindre.texte" (qui est encore mieux que Concat) sont des formules qui sont apparues avec la version 2019 et Excel365. Donc en 2016 cela n'apparait pas et c'est dommage.

Cependant rien n'empêche de les fabriquer non ???!!!!???

Je n'ai malheureusement que peu de compétence sur la fabrication des Functions et pas assez de temps pour m'y mettre
mais bon s'y quelqu'un veut essayer ...

car du coup cela répondrait parfaitement à sa demande et plus de soucis de nombre de cellules à concaténer

Pour répondre à Igloobel, ta réponse était ce que je cherche, mais malheureusement, la fonction "CONCAT" n'existe pas …

Je vous salue tous

A bientôt

[Boboxien]

Effectivement sur Access, il va me falloir plusieurs tables et je m'en suis déjà occupé. J'ai créé une table produit, une table substance, une table type de culture... Excel ne me sert qu'à préparer mes tables pour Access. Il ne me reste quasiment plus qu'une solution pour faire un lien entre mes produits et mes substances actives.
Pour votre dernière formule, je ne vois pas comment m'en servir et comment elle peut me permettre de faire ce lien. D'après ce que je comprends, il va juste me dire si une substance apparait bien dans le composition de produit, mais elle ne me permettra pas de faire le lien entre cette substance et un produit. Mais je comprends peut être mal la formule.

Enfin, Igloobel, certains postes de notre structure équipé en Office 2019, donc ça peut être une solution!

Et voici un exemple de substance qui n'a pas été détectée correctement.

Il aurait fallu pouvoir extraire "Straight Chain Lepidopteran Pheromones". Mais j'ai regardé une trentaine d'autre molécules et il s'agit soit de problème d'orthographe, soit de molécules absente de la liste de base.

[Boboxien]

Bonjour à tous,

Bon j'ai cherché "=Concat" et "=Joindre.texte" (qui est encore mieux que Concat) sont des formules qui sont apparues avec la version 2019 et Excel365. Donc en 2016 cela n'apparait pas et c'est dommage.

Cependant rien n'empêche de les fabriquer non ???!!!!???

Je n'ai malheureusement que peu de compétence sur la fabrication des Functions et pas assez de temps pour m'y mettre
mais bon s'y quelqu'un veut essayer ...

car du coup cela répondrait parfaitement à sa demande et plus de soucis de nombre de cellules à concaténer



Je vous salue tous

A bientôt

Après essai, il s'avère que cela ne fonctionne pas aussi bien que ce que je pensais. Comme je le disais, j'ai parfois des Substances qui sont très proches les unes des autres. Exemple: Mecoprop et Mecoprop-p. Donc avec la solution que je proposais, complétée par Igloobel, lorsque je suis sur un produit utilisant du Mecoprop, pas de souci, mais lorsque je suis sur un produit utilisant du mecoprop-p, il trouve le mot mecoporp mais aussi mecoprop-p. Donc lors de la concaténation, il m'indique une substance mecopropmecoprop-p. Il y a plusieurs substances de ce type et certaines plus compliquées à trouver...
Il faut que je réessaye avec la fonction de PrinceCorwin, je pense que j'aurai moins de tri manuel à faire!

[Pierre Fauconnier]

Perso, je comprends de moins en moins ce que tu souhaites réaliser, tellement ça évolue depuis ta première question

[Pierre Fauconnier]

Et voici un exemple de substance qui n'a pas été détectée correctement.

Il aurait fallu pouvoir extraire "Straight Chain Lepidopteran Pheromones". Mais j'ai regardé une trentaine d'autre molécules et il s'agit soit de problème d'orthographe, soit de molécules absente de la liste de base.

Le texte à trouver doit représenter la totalité de la cellule, et pas une partie, sinon c'est impossible. Dans l'illustration que tu montres, il aurait pu trouver E)-5-décen-1-ol... mais pas UNE PARTIE de cette chaine. La formule que je propose se base sur ton message où tu dis que tu vas extraire la substance de la recette.

Il n'est pas possible de retrouver une partie de texte dans une valeur de cellule, mais bien un texte clairement circonscrit. Par quel miracle voudrais-tu que la formule détecte que c'est Straight Chain... qu'il faut considérer dans la colonne "Texte à trouver"? Comme dit précédemment, il faut d'abord extraire les substances EXACTES de ta colonne Texte à trouver. Tu aurais fait comme cela depuis que je te dis de le faire, l'affaire serait déjà pliée...

A mon sens, tu dois d'abord faire le nettoyage et extraire correctement les valeurs que tu veux retrouver/comparer (même pas compris ce que tu veux en faire, d'ailleurs).

Effectivement sur Access, il va me falloir plusieurs tables et je m'en suis déjà occupé. J'ai créé une table produit, une table substance, une table type de culture...[...]

Il te manque l'une ou l'autre table, à mon avis, et j'ai des doutes que ton schéma résiste à une analyse un peu sérieuse.

[Boboxien]

Perso, je comprends de moins en moins ce que tu souhaites réaliser, tellement ça évolue depuis ta première question

Je ne pense pas que ma question initiale à changer. Par contre, ma demande est plus complexe que le simple exemple que j'avais mis au tout départ.
J'ai une première feuille avec une liste de Substances actives, une deuxième feuille avec une liste de composition de produit. Et je veux que pour chaque composition il me sorte la Substance active qui est utilisée (celle-ci se trouvant dans une parenthèse). Mais il y a des parenthèses aussi dans la composition, ainsi que dans les noms de substances donc c'est compliquer de trouver ce que je cherche. Et peut être que c'est impossible de le faire automatiquement (en tout cas pas pour 100% des compositions).
Dans mon exemple ci-dessus, la colonne texte à trouver est issue de la formule que m'a proposé PrinceCorwin, mais ce n'est qu'une formule qui ne sort pas ce que je cherche (et ce n'est peut être pas possible pour une machine, mais c'est très fastidieux pour un humain).

Pour la proposition que vous me proposez depuis le début, c'est que je ne la comprends pas (désolé, mes connaissances sous Excel ne sont sans doute pas suffisantes, comme je l'ai déjà dit je ne suis qu'un utilisateur moyen). Je ne vois pas quel nettoyage faire puisque ma feuille "Substance active" est normalement déjà nettoyée (il ne devrait s'agir que des substances qui devraient pouvoir être utilisées dans des produits). Les données de l'Anses sont fournies quasiment sans explication, donc peut être que je comprends mal les données qu'ils fournissent et que cette liste est malheureusement non exhaustive et mal construite.

Et pour Access, il est aussi possible que mon schéma ne résiste pas à une analyse sérieuse, mais nous sommes une petite structure qui n'a pas forcément les moyens d'avoir un spécialiste d'Excel, d'Access et autres logiciels. Je ne suis qu'un technicien issus d'une formation en gestion des milieux naturels qui maitrise un peu mieux l'outil informatique que ses collègues et qui essayent de simplifier les choses à ces mêmes collègues. Mes missions vont de l'inventaire de milieux naturels, à la gestion de données SIG, en passant par le dépannage informatique, la gestion de la qualité de l'eau (dont les molécules phytosanitaires qui m'intéressent pour ce problème). Donc je suis loin de pouvoir maitriser à 100% tous les domaines où l'on me demande d'agir.

De toute façon, je vais malheureusement devoir laisser tomber le sujet d'ici la semaine prochaine puisque nos financeurs me demandent de travailler sur un autre dossier plus urgent. Donc demain, je m'attaque au problème une dernière fois avant de reporter ça pour plus tard.

En tout cas, merci beaucoup à vous.
Merci à PrinceCorwin, puisque sa solution me permet quand même de faire un grand tri dans les données (le reste se fera peut être manuellement si je le peux)
Merci à Pierre Fauconnier: j'ai commencé à regardé votre tutoriel sur les tableaux structurés que je connaissais peu et je n'avais pas conscience de la puissance de cet outil
et Merci à Igloobel qui a essayé de m'aider à partir de mon idée, même si malheureusement, il s'avère que celle-ci n'était pas bonne!

[Pierre Fauconnier]

Prince et moi t'avons déjà dit qu'il était impossible d'extraire les substances des données gouvernementales, vu qu'il n'y a aucune logique dans leurs libellés. Perso, je pense que, pour chaque ligne des données de l'administration, il faut, dans la colonne à côté, extraire la substance reprise. Ce travail devra se réaliser en plusieurs phases:
On regarde si une syntaxe plus fréquente que les autres se dessine (par exemple, la substance entre les deux premières parenthèes, ou les deux dernières, ou en majuscules, que sais-je encore...);
On extrait les substances selon ce schéma;
On vide les mauvaises substances extraites (celles des libellés qui ne correspondaient pas au schéma);
On recommence avec le schéma le plus fréquent suivant, jusqu'à n'avoir plus que quelques lignes à nettoyer à la main.

Lorsque l'on a cela, on peut, avec ma formule ou une autre, et pour autant qu'il n'y ait qu'une substance active par produit, retrouver le libellé officielle de la substance sur base de la recette d'un produit. Je ne vois pas d'autre méthode puisqu'il est impossible, de façon automatique (que ce soit par formule ou par code), de retrouver une partie de texte sans schéma pour la définir dans une autre partie de texte sans schéma pour la définir.

[Boboxien]

Bonjour à vous,

Ok je comprends votre idée de nettoyage avec plusieurs passages. Mais je vous informe que finalement ça ne sera pas nécessaire. En effet, la proposition de PrinceCorwin semble avoir correctement fonctionner!
J'ai étudier, les molécules qui ont été extraite et qui n'apparaissent pas dans la liste. A part les quelques molécules "Straight Chain Lepidopteran Pheromones" (moins d'une dizaine de cas concernés), la proposition de PrinceCorwin a extrait correctement toutes les autres substances. Donc si elles n'apparaissent pas dans la liste, c'est soit parce qu'il y a un problème d'orthographe, soit parce qu'elles ne sont pas dans la liste de Substances (et là j'ai du mal à comprendre comment c'est possible). Dans tout les cas, il s'agit de problèmes qui ne peuvent pas être résolu via des formules ou des lignes de codes. Seuls une vérification manuel est envisageable pour corriger les orthographes et pour les autres substances, je ne pourrai malheureusement rien faire de plus.
Donc encore merci à tous!
Désolé s'il y a parfois eu des incompréhensions et si je n'étais pas toujours très clair, mais vous m'avez tout de même apporter une aide précieuse. Et pour les tableaux structurés, je pense que je vais revoir ma façon de travailler sous Excel

Bonne journée et à bientôt peut être en espérant que cette fois-ci j'arrive avec des données mieux structurées!

[PrinceCorwin]

Bonjour à tous,

Cette idée trottait dans ma tête depuis le début sans sortir de l'ombre.
Mais ca y est ! Elle montre enfin le bout de son nez !

Une idée pour s'affranchir des parenthèses et tout le reste. En ne s'appuyant que sur la liste officielle des substances.
Faire un "InStr(x, y)" pourrait tout aussi bien faire le taff !

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
Function RecherchePartie(ChercherDans As Range, ListeRecherche As Range)
Application.Volatile
a = ListeRecherche
For i = 1 To ListeRecherche.Count
    temp = False
    If InStr(UCase(ChercherDans), UCase(a(i, 1))) > 0 Then temp = True: Exit For
Next i
RecherchePartie = temp
End Function

La liste des substances toujours sous le nom "Substances"
Dans la cellule on note :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

 =RecherchePartie(A2;Substances)

Le script parcourt la liste des substances et stoppe si la substance est trouvée dans la cellule de composition


Si le résultat est "faux", alors soit la substance est inconnue, soit l'orthographe de la composition est erronée.

[Pierre Fauconnier]

[...]
Faire un "InStr(x, y)" pourrait tout aussi bien faire le taff ![...]

J'ai proposé cela sous diverses formes au message 22 , avec notamment une solution qui concaténait toutes les substances car la recherche était alors beaucoup plus rapide dans une seule chaine de caractères...

On se demande à quoi sert que Ducros il se décarcasse