Ceci est une page optimisée pour les mobiles. Cliquez sur ce texte pour afficher la vraie page.

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

E

excelybt

XLDnaute Nouveau
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.
 

Pièces jointes

  • tableauouchercher.xlsx
    10 KB · Affichages: 8
  • tableauquicherche.xlsx
    9.8 KB · Affichages: 7
Solution
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
Trier par date Trier par votes

Hasco

XLDnaute Barbatruc
Repose en paix
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
 

Pièces jointes

  • tableauquicherche.xlsx
    16.5 KB · Affichages: 4
  • tableauouchercher.xlsx
    16 KB · Affichages: 3
Vote positif 0 Vote négatif
Solution
E

excelybt

XLDnaute Nouveau
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 ^^
 
Vote positif 0 Vote négatif

Hasco

XLDnaute Barbatruc
Repose en paix
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)
Cliquez pour agrandir...
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.

excelybt à dit:
Pour PremierResultat c'est instantané, pour DernierResultat j'ai l'impression que cela ralentit tout.
Cliquez pour agrandir...

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
 
Vote positif 0 Vote négatif
E

excelybt

XLDnaute Nouveau

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


Hasco à dit:
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.
Cliquez pour agrandir...

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?
 
Vote positif 0 Vote négatif

Hasco

XLDnaute Barbatruc
Repose en paix
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
 
Vote positif 0 Vote négatif
E

excelybt

XLDnaute Nouveau
Hasco à dit:
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
Cliquez pour agrandir...
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
 
Vote positif 0 Vote négatif
E

excelybt

XLDnaute Nouveau
Hasco à dit:
Re,

Je vois des rechercheV et non des Agregats.

Cordialement
Cliquez pour agrandir...
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?
 
Vote positif 0 Vote négatif

Hasco

XLDnaute Barbatruc
Repose en paix
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
Cliquez pour agrandir...
Pour une solution d'aujourd'hui je vous dirai de télécharger en complément PowerQuery pour excel 2013

cordialement
 
Vote positif 0 Vote négatif
E

excelybt

XLDnaute Nouveau
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?
 
Vote positif 0 Vote négatif
Identifiez-vous ou inscrivez-vous pour participer.

Discussions similaires

M
  • Question
XL 2019 Recherche d'un mot dans une chaîne de caractère depuis une plage de références puis si présent renvoyer la référence
Réponses
7
Affichages
506
MilieBZH
M
G
  • Question
XL 2016 Extraire des données d'un pdf et les copier dans excel
2
Réponses
17
Affichages
813
gali2
G
T
  • Question
XL 2021 Trouver et remplacer un mot dans une chaine de caractère
Réponses
5
Affichages
664
Gégé-45550
C
  • Résolu(e)
Microsoft 365 Problème VBA lors de l'affichage de ligne masquée
2
Réponses
21
Affichages
655
Chris Linefield
C
T
  • Résolu(e)
XL 2013 Recherche de Valeurs Approximatives dans un Tableau Excel
Réponses
2
Affichages
503
job75
L
  • Résolu(e)
XL 2019 Renvoyer une valeur colonne A avec la dernière valeur de la colonne B
Réponses
5
Affichages
480
Gégé-45550
C
  • Résolu(e)
Microsoft 365 Trouver résultat en fonction de la dernière ligne d'une colonne
Réponses
7
Affichages
365
Chounoé
C
D
  • Question
XL 2016 Utiliser un équivalent de INDIRECT sur des classeurs fermés
Réponses
8
Affichages
657
job75
P
  • Question
XL 2016 identifier chaine de caractère dans une cellule et ramener une donnée d'une table de correspondance
Réponses
5
Affichages
535
pmid20
P
  • Résolu(e)
Microsoft 365 Renvoi de la dernière valeur d'une ligne selon condition
Réponses
6
Affichages
519
job75
Menu
Connexion
S'inscrire
Les cookies sont requis pour utiliser ce site. Vous devez les accepter pour continuer à utiliser le site. En savoir plus…