XL 2013 Rechercher dans une table de données un élément spécifique et retourner l'élément correspondant situé sur la meme ligne dans une colonne spécifiée

snake21 · 20 Décembre 2019

Hello tout le monde,

Mon titre est assez inexplicable, donc des précisions s'imposent:
J'ai une table de données avec des noms de pays en plusieurs langues. La langue que j'aimerais utiliser dans mes rapports est l'anglais et est dans la première colonne.
A partir du nom d'un pays en allemand par exemple, j'aimerais rechercher dans la table de données ce pays (allemand) et retourner l'équivalent en anglais dans mon résultat.

Le fichier excel joint vaut mieux que toutes les explications que je pourrais faire.
Dans l'onglet Mapping ce que je cherche à faire
Dans l'ongle Country list, les pays en plusieurs langues. la table évoluera en fonction des nouvelles occurences de pays que j'aurai dans mes rapports.

Bonnes fêtes à tous

sylvanu · 20 Décembre 2019

Bonjour Snake,
Dans votre fichier je pense que la colonne Résultat est fausse, à partir d' Arménia. Ballot pour un fichier exemple.
Ou alors votre explication m'a échappée.
En PJ un exemple avec une fonction personnelle en VBA.
Bonne journée.

snake21 · 20 Décembre 2019

sylvanu à dit:
Bonjour Snake,
Dans votre fichier je pense que la colonne Résultat est fausse, à partir d' Arménia. Ballot pour un fichier exemple.
Ou alors votre explication m'a échappée.
En PJ un exemple avec une fonction personnelle en VBA.
Bonne journée.

Oui c'est vrai, vous avez entièrement raison. Mea culpa. Votre VB est parfait!!!
Merci

Dudu2 · 20 Décembre 2019

Bonjour,

En cherchant un EQUIV sur plusieurs colonnes (qui n'existe pas en tant que tel), voici une formule matricielle trouvée sur Internet adaptée pour l'occasion à placer en B2 de la feuille Mapping puis à étendre sur les lignes suivantes de la colonne B:

=SIERREUR(INDEX('Country list'!$A:$A;PETITE.VALEUR(SI('Country list'!$B$1:$ZZ$200=$A2;LIGNE(INDIRECT("1:"&LIGNES('Country list'!$B$1:$ZZ$200))));1));"")

A entrer avec Ctrl + Shift + Entrée

$B$1:$ZZ$200 représente la plage de recherche théorique maximale de la feuille Country list pour ne pas à avoir à ajuster cette plage en fonction du contenu qui peut évoluer. Pour coller aux valeurs du fichier exemple, $B$1:$E$20 suffit.

chris · 20 Décembre 2019

Bonjour à tous

Dudu2 à dit:
Pour rendre la formule indépendante du nombre de lignes de la feuille Country list, 3 solutions:

- Utiliser DECALER (solution la plus "élegante)
- Utiliser INDIRECT (solution la plus "lisible" )
- Ou tout simplement prendre une valeur maximale du nombre de lignes de la feuille Country list (par exemple 200)

Non ! cela fait 16 ans, depuis la version 2003, que Microsoft a mis à notre disposition les tableaux structurés : simples, efficaces et formules lisibles
Si le tableau structuré est nommé comme l'onglet avec des en-tête indiquant la langue par exemple
=INDEX(Country_List[Anglais];EQUIV(Mapping!A2;Country_List[Américain];0))

Tableaux structurés Excel

fauconnier.developpez.com

snake21 · 20 Décembre 2019

sylvanu à dit:
Bonjour Snake,
Dans votre fichier je pense que la colonne Résultat est fausse, à partir d' Arménia. Ballot pour un fichier exemple.
Ou alors votre explication m'a échappée.
En PJ un exemple avec une fonction personnelle en VBA.
Bonne journée.

Etrangement, en organisant la Countries list en table que j'appellerai Countries (comme dans votre VB), il renvoie l'équivalent de la ligne inférieur.
J'ai donc adapté la ligne de recherche: SearchCountry = [Countries].Cells(IndexC - 1, 1) (pour que le résultat soit à la meme ligne)
Quelle est l'explication?
Merci

snake21 · 20 Décembre 2019

chris à dit:
Bonjour à tous

Non ! cela fait 16 ans, depuis la version 2003, que Microsoft a mis à notre disposition les tableaux structurés : simples, efficaces et formules lisibles
Si le tableau structuré est nommé comme l'onglet avec des en-tête indiquant la langue par exemple
=INDEX(Country_List[Anglais];EQUIV(Mapping!A2;Country_List[Américain];0))

Tableaux structurés Excel

Tableaux structurés Excel

fauconnier.developpez.com

J'aimerais que le "Match" / "Equiv" se fasse sur tout le reste du tableau et non sur une langue specifique

snake21 · 20 Décembre 2019

Dudu2 à dit:
Bonjour,

En cherchant un EQUIV sur plusieurs colonnes (qui n'existe pas en tant que tel), voici une formule matricielle trouvée sur Internet adaptée pour l'occasion à placer en B2 de la feuille Mapping puis à étendre sur les lignes suivantes de la colonne B:

=SIERREUR(INDEX('Country list'!$A:$A;PETITE.VALEUR(SI('Country list'!$B$1:$ZZ$200=$A2;LIGNE(INDIRECT("1:"&LIGNES('Country list'!$B$1:$ZZ$200))));1));"")

A entrer avec Ctrl + Shift + Entrée

$B$1:$ZZ$200 représente la plage de recherche théorique maximale de la feuille Country list pour ne pas à avoir à ajuster cette plage en fonction du contenu qui peut évoluer. Pour coller aux valeurs du fichier exemple, $B$1:$E$20 suffit.

En appliquant cette formule dans mes rapport la calculation d'excel dure des heures... j'ai un fichier de 100'000 lignes.
Dans le fichier final j'ai 260 pays différents et jusqu'à 9 langues / ortographes differentes par pays

chris · 20 Décembre 2019

RE

snake21 à dit:
J'aimerais que le "Match" / "Equiv" se fasse sur tout le reste du tableau et non sur une langue specifique

Je donnais un exemple de la syntaxe pas une formule complète d'autant que ton exemple n'a pas d'en-tête pour savoir quelle langue est dans quelle colonne...

Sur 2013 tu peux installer PowerQuery (en add on sur 2013,intégré à partir de 2016).
Je pense que sur ce volume de données cela peut être plus efficaces que les formules...

Un exemple REPRESENTATIF avec en-tête est souhaitable...

Dudu2 · 21 Décembre 2019

snake21 à dit:
En appliquant cette formule dans mes rapport la calculation d'excel dure des heures... j'ai un fichier de 100'000 lignes.
Dans le fichier final j'ai 260 pays différents et jusqu'à 9 langues / ortographes differentes par pays

Si dans les mêmes conditions la fonction perso est plus performante, alors c'est la bonne option.

A titre d'essai, voici une version de la fonction personnelle axée sur le stockage mémoire pour une meilleure efficacité par rapport au scan de cellules compte tenu du nombre très important des 100.000 lignes et donc d'appels à la fonction pour un très grand nombre de noms.
Cette fois, il y a un 2ème argument qui n'a aucune utilité pour la fonction mais qui donne à Excel la visibilité sur les cellules (en plus de la cellule concernée du 1er argument) dont la modification conditionne le re-calcul (par Excel) des cellules contenant une référence à la fonction.

Le code est dans:
- Module_SearchCountry
- Feuil2 (Country list)

XL 2013 Rechercher dans une table de données un élément spécifique et retourner l'élément correspondant situé sur la meme ligne dans une colonne spécifiée

snake21

XLDnaute Nouveau

Pièces jointes

sylvanu

XLDnaute Barbatruc

Pièces jointes

snake21

XLDnaute Nouveau

Dudu2

XLDnaute Barbatruc

chris

XLDnaute Barbatruc

Tableaux structurés Excel

snake21

XLDnaute Nouveau

snake21

XLDnaute Nouveau

Tableaux structurés Excel

snake21

XLDnaute Nouveau

chris

XLDnaute Barbatruc

Dudu2

XLDnaute Barbatruc

Pièces jointes

Discussions similaires