XL 2019 Liste déroulantes dynamiques semi automatiques

Nico17620

XLDnaute Nouveau
Bonjour à tous,

Je suis en stage de fin d'étude en environnement et écologie et une des missions dont je dois m'occuper est la mise en place d'un outil numérique de terrain facilitant les relevés de la faune et de la flore.

Pour cela j'ai créé un fichier excel où sont renseignés les espèces (noms latins et noms vernaculaires) et leur statut de protection suivant les régions et les départements. La saisie des données se fait à l'aide de listes déroulantes semi automatiques.
La fonction utilisée pour les régions par exemple est la suivante (dans "données", "validation des données", "liste") :

=SI(A2<>"";DECALER(f_region;EQUIV(A2&"*";f_region;0)-1;;NB.SI(f_region;A2&"*");1);f_region)

où f_region correspond à :

=DECALER(p_region;0;0;NBVAL(l_region);1)
avec p_region = première valeur de la liste région
et l_region = colonne où se situe la liste région

Ces formules ont été obtenues grâce au tutoriel YouTube de Lydia Provin que je vous conseille.
(
)

Le but serait qu'à la saisie d'une région ou d'un département, la liste des espèces concernée par ces régions (ou département) se mette à jour. Autrement dit, n'avoir que les espèces présentent dans telle ou telle région et in fine avoir les statuts de protection de ces espèces.

Cas pratique : quand on est sur le terrain et que l'on relève une espèce, l'idée serait de rapidement connaître son statut de protection départemental, régional et national.

Je suis donc coincé car je n'arrive pas à trouver de formule me permettant d'obtenir un tel résultat.
J'avais une piste avec la formule RECHERCHEV mais je n'arrive pas à ce que cette formule prenne en compte plus d'une cellule. En effet, certaines espèces sont notifiées plusieurs fois (plusieurs régions ou département possèdent les même espèces).

Je vous transmets le fichier excel que j'utilise. Sur ce fichier vous trouverez différentes feuilles de calcul où sont listés les statuts par région (feuille : "statut_region"), département (feuille : "statut_departement") et au niveau national (feuille : "statut_national") ainsi que la liste des espèce et la feuille de calcul où se situe les listes déroulantes (feuille : "outil"). Le fichier de base était beaucoup trop volumineux pour le transférer ici donc il a été allégé : de nombreuses lignes ont été supprimé l'idée ici est juste de trouver une méthode qui marche.

Merci d'avance pour votre aide
 

Pièces jointes

  • outil_test_3.xlsx
    136.7 KB · Affichages: 21

goube

XLDnaute Impliqué
Re,
Si j'ai bien tout compris, un essai à tester.

Mais il faut faire attention au nom qui doivent être orthographiés de la même façon dans tous les tableaux.
(dans mon exemple : Stipa pennata L. 1753 dans les tableaux par secteur et Stipa pennata L., 1753 dans la liste espèce.)

Vu la taille du fichier, je doute que cela fonctionne sur Excel mobile.
Cordialement.
https://www.transfernow.net/dl/2022041315fZUt7Q
 

Nico17620

XLDnaute Nouveau
Re,
Petite question est ce que tous les noms valides ont une correspondance vernaculaire.
Autrement dit, est ce que je peux diffuser les noms vernaculaires vers le bas dans une requête ?

Edit:
Une liste des noms utilisés
https://www.transfernow.net/dl/20220413C45bNSdB
Bonjour,

merci pour ces test c'est presque ce que je recherche.

L'idée serait d'avoir les noms vernaculaires comme liste déroulante semi automatique et ensuite le nom latin (nom valide) se mette à jour. Suivant le nom latin mis à jour les différents statuts se remplissent suivant le nom latin. Est ce que c'est possible ?

Pour répondre à votre question : non tous les noms valides n'ont pas de correspondances vernaculaires
 

Nico17620

XLDnaute Nouveau
Re,
Petite question est ce que tous les noms valides ont une correspondance vernaculaire.
Autrement dit, est ce que je peux diffuser les noms vernaculaires vers le bas dans une requête ?

Edit:
Une liste des noms utilisés
https://www.transfernow.net/dl/20220413C45bNSdB
Concrètement la liste déroulante semi automatique doit se baser sur la liste d'espèce complète et suivant le nom latin (valide) les statuts se mettent à jour ou non suivant les liste de statuts par correspondance. En effet toutes les espèces n'ont pas forcément de statuts de protection.
 

goube

XLDnaute Impliqué
Bonjour,
Pour que cela fonctionne, il faut que la liste soit bien renseignée.
Par exemple dans l'exemple ci-dessous pour l'abax ovale, toutes les occurences du nom valide ne sont pas renseignées pour le grand abax de même.
pour l'Abax ovalis (Duftschmid, 1812) il y a deux noms vernaculaires.
Comment procéder ?
Cordialement

Abax ovalis (Duftschmid, 1812)Abax ovale
Abax ovalis (Duftschmid, 1812)
Abax ovalis (Duftschmid, 1812)
Abax ovalis (Duftschmid, 1812)Hyménoptères
Abax parallelepipedus (Piller & Mitterpacher, 1783)Grand abax
Abax parallelepipedus (Piller & Mitterpacher, 1783)
Abax parallelepipedus (Piller & Mitterpacher, 1783)
Abax parallelepipedus (Piller & Mitterpacher, 1783)
Abax parallelepipedus parallelepipedus (Piller & Mitterpacher, 1783)
Abax parallelepipedus parallelepipedus (Piller & Mitterpacher, 1783)Abax ovale
Abax parallelepipedus parallelepipedus (Piller & Mitterpacher, 1783)Abax ovale
Abax parallelepipedus parallelepipedus (Piller & Mitterpacher, 1783)Abax ovale
Abax parallelus (Duftschmid, 1812)Abax inerme
Abax parallelus (Duftschmid, 1812)Grand abax
Abax parallelus (Duftschmid, 1812)Grand abax
Abax parallelus (Duftschmid, 1812)
Abax parallelus parallelus (Duftschmid, 1812)Grand abax
Abdera affinis (Paykull, 1799)
Abdera biflexuosa (Curtis, 1829)
Abdera biflexuosa (Curtis, 1829)Abax inerme
Abdera biflexuosa (Curtis, 1829)Abax inerme
Abdera biflexuosa (Curtis, 1829)
Abdera flexuosa (Paykull, 1799)
 

Nico17620

XLDnaute Nouveau
bonjour,

effectivement il s'agit de la source de données qui n'est pas à jour (INPN) mais je dois m'en contenter.
Dans ce cas là les noms valides latins vont être utilisés en priorité et non les noms vernaculaires. Votre fonction semble bien fonctionner (merci beaucoup d'ailleurs). Le seul soucis sont les régions et les départements. Plusieurs espèces se répètent suivant leur localité et je ne suis pas sur que le fonction prenne en compte ce paramètre.
 

goube

XLDnaute Impliqué
Bonsoir,

Une proposition qui ne fonctionnera pas avec excel mobile. Beaucoup trop lourde et présence de VBA et de power query.
Plusieurs espèces se répètent avec le nom vernaculaire mais pas avec le nom valide. Il faudrait une liste plus complète.
De plus il faut supprimer les , qui ne servent à rien dans les noms.

Cordialement.