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]

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]

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]

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]

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]

Bonjour à tous

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

Tableaux structurés Excel

fauconnier.developpez.com

 

[snake21]

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]

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]

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]

RE

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]

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)