XL 2013 trouver la dernière occurrence d'une chaine dans un fichier excel séparé

[excelybt]

Bonjour,

Depuis un premier tableau excel (tableauquicherche.xlsx) j'effectue une recherche dans un second tableau excel (tableauouchercher.xlsx) et aimerais renvoyer la valeur de la colonne résultat pour la valeur cherchée.

Plusieurs difficultés:

• La valeur cherchée peut-être présente plusieurs fois j'aimerais renvoyer la dernière (si impossible, il y a une colonne condition qui pourrait permettre de trouver la valeur à retourner si cette colonne contient une valeur)
• La recherche s'effectue donc dans un tableau (fichier) séparé
• La valeur cherchée (chaine de texte) peut être contenue/entourée d'autres caractères
• Idéalement j'aimerais ne pas utiliser le calcul matriciel
• Je ne dois pas apporter de modification au tableau où le texte est cherché (tableauouchercher.xlsx)

Est-possible? Les fichiers sont ci-joints.

J'arrive sans problème à trouver le premier résultat même si cela se passe en deux fichier et que la chaine cherchée est entourée d'autres caractères.
Mais je n'arrive à trouver le dernier résultat que si je suis dans le même fichier et que la chaine cherchée n'est pas entourée d'autre caractères. Sans quoi je bloque.

Merci de m'avoir lu.

 

[Hasco]

Bonjour,

Dans le classeur joint vous verrez index/agregat.

J'ai modifié les "cond?" pour vérification des résultats, vos exemples étant trop linéaires et uniformes.

Je n'ai mis la fonction index que dans la dernière ligne du tableau de résultats, les numéros de lignes étant plus parlant que "a, o,p" . je vous laisse le soin de terminer.

Cordialement

 

[excelybt]

Bonjour,

Merci beaucoup pour ce retour, à première vue cela semble en effet parfaitement fonctionner.
Je découvre la fonction agrégat!
Je vais encore refaire le circuit plusieurs fois pour bien comprendre, pour dernier résultat est-ce bien:

- Cherche renvoie tous les numéros de lignes ou la chaîne a été trouvée
- Agrégat sélectionne le numéro de ligne précédent qui à la plus grande valeur
- Index qui converti ce numéro de ligne en son contenu

Et pour Resultatsicondition3ou4.... ben pour l'instant je sèche ^^

 

[excelybt]

Le fichier final tableauouchercher fait 4300 lignes et tableauquicherche en fait 410.
Pour PremierResultat c'est instantané, pour DernierResultat j'ai l'impression que cela ralentit tout.

 

[Hasco]

Re,

=AGREGAT(14;6;LIGNE($1:$17)/(CHERCHE("*" & A2 & "*";'LeDossier\[tableauouchercher.xlsx]Feuil1'!$A$1:$A$17))/(('LeDossier\[tableauouchercher.xlsx]Feuil1'!$B$1:$B$17="cond3")+('LeDossier\[tableauouchercher.xlsx]Feuil1'!$B$1:$B$17="cond4"));1)

Où 'LeDossier' est le chemin complet vers le dossier contenant le fichier tableauouchercher.xlsx.

Agregat, grâce à son deuxième paramètre nous permet d'ignorer les erreurs de calculs présentes dans le tableau passé en paramètre. Ici un tableau de numéros de lignes divisés par des Vrai (1) ou Faux (0).
Agregat ignorer a les cas pour lesquels le tableau contiendra l'erreur #DIV/0! et conservera ceux pour lesquels le contenu est un numéro de ligne. (ex 17/1 = 17)

La Fonction Cherche va retourner un 1 si "*" & valeur & "*" est trouvé. L'étoile représentant n'importe quel caractère et sa position étant avant la valeur cherchée, la position retournée par Cherche sera forcément 1.

Comme condition il nous faut que cherche =1 / (soit valeur = "cond3" soit valeur = "cond4") Le signe + (plus) donne cette possibilité d'un OU.

Pour PremierResultat c'est instantané, pour DernierResultat j'ai l'impression que cela ralentit tout.

Normal cela ne se voit pas mais c'est une fonction matricielle, testant 4300 lignes pour chacune de vos 410 ligne soit 1 763 000 tests, qui plus est sur un fichier externe.

cordialement

 

[excelybt]

Agregat, grâce à son deuxième paramètre nous permet d'ignorer les erreurs de calculs présentes dans le tableau passé en paramètre. Ici un tableau de numéros de lignes divisés par des Vrai (1) ou Faux (0).
Agregat ignorer a les cas pour lesquels le tableau contiendra l'erreur #DIV/0! et conservera ceux pour lesquels le contenu est un numéro de ligne. (ex 17/1 = 17)

La Fonction Cherche va retourner un 1 si "*" & valeur & "*" est trouvé. L'étoile représentant n'importe quel caractère et sa position étant avant la valeur cherchée, la position retournée par Cherche sera forcément 1.

Ah oui je n'y était même pas....
Merci pour l'explication je vais réessayer de comprendre.

Normal cela ne se voit pas mais c'est une fonction matricielle, testant 4300 lignes pour chacune de vos 410 ligne soit 1 763 000 tests, qui plus est sur un fichier externe.

Alors j'ai peut-être plusieurs solutions pour optimiser un peu les choses:

Pour commencer, plutôt que de mettre plusieurs fois la formule complète pour récupérer différentes colonnes de résultat, je vais récupérer le numéro de la ligne. Puis utiliser ce numéro de ligne pour récupérer les cellules des colonnes correspondantes.

Et pour aller plus loin, la chaîne est toujours groupée de façon continue dans les lignes qui se suivent (26 lignes max).
Donc on pourrait trouver la première occurence avec le rechercheV classique.
et limiter la plage de recherche plus complexe proposée à partir de là jusqu'au 26 prochaines lignes.
Cela semble jouable? Est-ce que du coup cela fera beaucoup moins de calcul?

 

[Hasco]

Re,

Maintenant que vous connaissez le principe, essayez, vous verrez ce qui fonctionne ou pas. Revenez avec les difficultés précises rencontrées.

cordialement

 

[excelybt]

Re,

Maintenant que vous connaissez le principe, essayez, vous verrez ce qui fonctionne ou pas. Revenez avec les difficultés précises rencontrées.

cordialement

Alors j'ai fait un essai dans les fichiers ci-joints, mais je ne sais pas trop si je dois aussi corriger la plage de lignes de la fonction agrégat

Je ne me suis pas encore intéressé aux conditions supplémentaires cond3/4, j'essaye d'y aller morceau par morceau

 

[Hasco]

Re,

Je vois des rechercheV et non des Agregats.

Cordialement

 

[excelybt]

Re,

Je vois des rechercheV et non des Agregats.

Cordialement

Ce que j'ai modifié:

Dans le fichier tableauoucherche => J'ai ajouté une deuxième colonne résultat (et les ai rennomées)

Dans le fichier tableauquicherche:
- Dans la colonne E je cherche le premier numéro de ligne dans laquelle la chaîne apparaît = > normalement rapide
- Dans la colonne F j'effectue la même recherche que dans votre solution mais seulement sur une plage qui va du premier numéro de ligne à ce numéro +26. Le but étant d'économiser du temps de calcul et de ne pas chercher sur la plage complète (4300 lignes sur le fichier final)
- Les colonnes G et H je récupère le contenu de la cellule des colonnes résultat qui m'intéresse pour ce numéro de ligne (comme ça le numéro de ligne n'est cherché qu'une seule fois même s'il y a plusieurs colonnes résultat)

Je n'ai pas utilisé rechercheV?

 

[Hasco]

Re,

Les fichiers du post#8 ne contiennent pas tout ça. Ne vous seriez-vous pas trompé de fichiers ?

cordialement

 

[excelybt]

Re,

Les fichiers du post#8 ne contiennent pas tout ça. Ne vous seriez-vous pas trompé de fichiers ?

cordialement

Visiblement oui, désolé je n'avais pas vu

 

[Hasco]

Re,

Toutes les fonctions excel ne sont pas utilisables sur des fichiers fermés. C'est le cas de INDIRECT.
Je crains que votre affaire ne se complique.
Allez voir par là, même si ça date un peu c'est toujours d'actualité :

FAQ Excel

FAQ Excel

excel.developpez.com

Pour une solution d'aujourd'hui je vous dirai de télécharger en complément PowerQuery pour excel 2013

Download Microsoft Power Query pour Excel from Official Microsoft Download Center

Microsoft Power Query pour Excel est un complément Excel qui améliore l'expérience du décisionnel libre-service dans Excel en simplifiant la récupération des données, l'accès et la collaboration.

www.microsoft.com

cordialement

 

[excelybt]

Re,

Toutes les fonctions excel ne sont pas utilisables sur des fichiers fermés. C'est le cas de INDIRECT.
Je crains que votre affaire ne se complique.
Allez voir par là, même si ça date un peu c'est toujours d'actualité :

Pour une solution d'aujourd'hui je vous dirai de télécharger en complément PowerQuery pour excel 2013


cordialement

Bonjour,
Je passe désormais par un fichier intermédiaire dans lequel je fais l'actualisation avec le tableauouchercher ouvert, j'enregistre, je ferme.
Et mon fichier final viens faire une simple rechercheV dans ce fichier intermédiaire sans que le fichier ait besoin d'être ouvert. Et là du coup cela reste fluide.

Que pensez-vous de la recherche limitée à la plage de la première occurrence à +26? Est-ce que le gain peut être réel sur les 4300 lignes? Faut-il également modifier la plage d'AGREGAT?

 

[Hasco]

Bonjour,

Que pensez-vous de la recherche limitée à la plage de la première occurrence à +26?

Sur un total de 4300 lignes le gain serait minime ou négatif, s'il fallait reconstruire des plages à partir d'autres fonctions.

Bonne continuation