Imbriquer la fonction DECALER dans une FONCTION EQUIV pour modifier plage

Bonsoir,

J'ai beaucoup de mal à aller au bout de ma démarche.
Je ne parviens pas à utiliser la fonction DECALER dans une fonction EQUIV.

J'ai une colonne RFD dans laquelle se trouvent des valeurs dont j'ignore totalement la chaîne à trouver.

J'ai donc utilisé une formule INDEX EQUIV en utilisant le wildcard "*" en guise d'argument "valeur cherchée" dans la plage C2:C6 (pas C1:C6 car le wildcard prendrait en compte la chaîne dans la ligne d'en-tête):

=INDEX([EXPORT.XLSX]Feuil1!$A$1:$C$6;EQUIV("*";[EXPORT.XLSX]Feuil1!$C$2:$C$6;0)+1;3)

Je récupère donc la valeur et j'en profite pour tester qu'il y a bien au moins une occurrence trouvée afin d'afficher toutes les données de la ligne du "match"

Pour trouver le 2ème RFD "PL9", il faudrait que mon EQUIV ne cherche plus dans la plage C2:C6 mais dans la plage C4:C6, soit une ligne après celle du 1er match trouvé et ainsi de suite.

Et je n'arrive pas à faire ce décalage dans la plage dans le nouvel EQUIV.
Je bataille sur les arguments de la fonction DECALER et rien ne fonctionne: je tourne en rond.
Je veux juste décaler ma plage du nb de lignes correspondant à la ligne du match précédent.

Merci d'avance pour vos lumières et pour toute l'aide apportée.

Bien cordialement.

bonjour pipout64,

=SIERREUR(INDEX($B:$B;AGREGAT(15;6;LIGNE($C$1:$C$26)/($C$1:$C$26="PL8");E2));"?")

je ne suis pas sûr de ce que vous demandez, mais avec ceci on cherche la x-eme (valeur de E2) de "PL8" dans la plage C1:C26

Bojnour,

BsAlv bonjour,

à l'ancienne, version vintage :

=SIERREUR(INDEX(B$1:B$50;PETITE.VALEUR(SI(C$1:C$50="PL8";LIGNE(C$1:C$50);9^9);LIGNE()-1));"/")

Cela revient à écrire en toute lettre la fonction PETITE.VALEUR qu'AGREGAT utilise avec le paramètre 15, mais je n'ai pas encore (jamais) l'habitude de tout ceci...

@ bientôt

LouReeD

re, salut LouReeD,

il n'y a pas des problèmes "à l'ancienne" sauf je pense qu'il faut choisir toute la colonne B, parce que le résultat est le numéro de la ligne. Ici cela ne cause pas des erreurs, mais si la plage ne commence pas à la première ligne mais par exemple la dixième, ... . Normallement, j'évite d'utiliser une colonne entière, mais ici cela ne cause pas une ralentissement du fichier.

=SIERREUR(INDEX($B:$B;PETIT ....

Tout à fait, mais comme "normalement" sur un tableau comme celui-là on utilise des tableaux structuré... Non ?

@ bientôt

LouReeD

oui, mais mes TS commencent rarement sur la première ligne, je mets la ligne avec les totaux au dessus les entêtes. Je sais, je n'est pas logique, mais autrement, j'ai d'autres problèmes.

Bonjour BsAlv et LouReeD.
Je vous remercie d'avoir pris le temps de me répondre.
Je vais regarder vos réponses et tenter de comprendre ce que font les fonctions que vous me proposez car je ne les maitrise pas du tout et comme j'aime bien comprendre...
Je vais essayer de les adapter dans ma feuille.
Merci encore: c'est sympa.
Bonne journée.

Bonsoir,

merci de ce retour et remerciements !

Pour ce qui est des explications/fonctionnement je peux vous en donner un peu ce soir :
Dans : =SIERREUR(INDEX(B$1:B$50;PETITE.VALEUR(SI(C$1:C$50="PL8";LIGNE(C$1:C$50);9^9);LIGNE()-1));"/")
on peut retirer le SIERREUR qui parle de lui-même si le premier paramètre engendre une erreur alors on fait le deuxième paramètre, ici s'il y a une erreur sur la formule INDEX on affiche un slash "/".

Il reste : INDEX(B$1:B$50;PETITE.VALEUR(SI(C$1:C$50="PL8";LIGNE(C$1:C$50);9^9);LIGNE()-1))
INDEX renvoi la données de la matrice qui se trouve en ligne indiquée et en colonne indiquée, ici la matrice est la plage de cellule d'une seule colonne allant de B1 à B50, ensuite pour trouver la ligne nous avons la formule PETIT.VALEUR, et il n'y a pas de paramètre pour la colonne, normale il n'y en a qu'une donc ce paramètre est inutil.

Il reste : PETITE.VALEUR(SI(C$1:C$50="PL8";LIGNE(C$1:C$50);9^9);LIGNE()-1)
sa structure est PETIT.VALEUR(Matrice;k) dans la liste de données se trouvant dans la matrice (ou plage de cellule) cette fonction renvoie la k nième petite valeur, si la matrice contiens {3;7;2;9;8;5} et que nous avons k = 2, la fonction renverra alors la deuxième petite valeur c'est à dire ici 3

Dans la formule k est égale à LIGNE()-1 : LIGNE() renvoie le numéro de la ligne de la feuille Excel où se trouve cette fonction, si =LIGNE() se trouve en ligne 10 quelque soit la colonne alors cela renvoie 10 (il existe la même pour le colonnes : COLONNE())

maintenant on peut s'intéresser à la matrice de PETITE.VALEUR : pour créer cette matrice on va faire un test sur une plage de données avec des résultats différents en fonction d'un test : SI(C$1:C$50="PL8";LIGNE(C$1:C$50);9^9)
Donc on test la plage de cellules allant de C1 à C50, on y fait un test d'égalité avec la valeur recherchée "PL8", si le test est VRAI; alors on remplie la matrice pour avec le numéro de la ligne de la feuille Excel, si le test est FAUX alors on rempli la matrice avec une "très grande valeur" afin de l'éloignée des petites valeurs, ici la valeur est égale à 9 puissance 9 = 387 420 489, soit une valeur supérieur au nombre de lignes d'une feuille Excel donc on est tranquille !

Cette matrice pour le fichier exemple ci :

se remplie donc ainsi : {387 420 489;387 420 489;3;387 420 489;387 420 489;387 420 489;387 420 489;387 420 489;387 420 489;10;387 420 489;387 420 489;387 420 489;14;387 420 489;387 420 489;17;387 420 489;19...}

la première formule d'extraction se trouve en ligne 2 de la feuille, c'est pourquoi le paramètre K de PETITE.VALEUR est égale à LIGNE()-1, afin de faire ressortir la première petite valeur : LIGNE()-1 = n° de ligne où se trouve la formule -1 = 2-1 = 1 on a bien K = 1, la formule sort la première petite valeur de la matrice ici = 3.
de ce fait on a le numéro de ligne à extraire de la matrice de la fonction INDEX qui va de b1 à B50, la troisième ligne de cette matrice est TOTA12.
La formule du dessous se trouve en ligne 3, ce qui fait que K=2, cela renvoie 10, et donc INDEX renvoi la 10ième ligne donc tot4, etc....

@ bientôt

LouReeD

Bonjour LouReed.

Wahou !
Quelles explications !
Merci très sincèrement pour le temps passé: j'y suis très sensible.
J'ai bien compris la majeure partie du raisonnement et je bute sur la partie la plus compliquée à savoir la matrice de PETITE.VALEUR.
J'ai bien vu dans l'exemple que la matrice, dans le cas où "PL8" n'était pas trouvé, la matrice était remplie avec la valeur 9^9.
Mais j'ai encore du mal à le comprendre complètement.
Je vais faire des essais pour mieux capter.
Par contre, et c'est là où je vais m'excuser platement, je souhaite retrouver toutes les valeurs non vides de la colonne C (RFD) donc aussi W208.
Je me suis vraisemblablement mal exprimé.
Et d'où ma recherche avec le wildcard "*".
Dans votre formule, on ne se concentre que sur "PL8" (qui peut tout à fait se retrouver dans plusieurs cellules de la colonne C) mais j'ai besoin de récupérer toutes les valeurs de cette colonne.
J'ai bien compris que les fonctions natives d'excel ne permettent pas de créer une ligne.
J'ai une feuille principale qui contient un grand nombre de données et je crée des onglets pour récupérer certaines données de cette feuille principale.
L'onglet RFD me permet de récupérer toutes les lignes où se trouvent une donnée non vide située dans une colonne RFD (colonne Z) de cette feuille principale.
Je souhaite juste une réplique de la colonne Z de ma feuille principale (avec quelques autres données situées sur la même ligne) sans les lignes où le RFD est vide dans la colonne Z.

En VBA, il faudrait faire une boucle sur la colonne: SI la valeur est vide je ne la prends pas en compte sinon je la copie dans l'onglet RFD.
Mais comme les macros sont interdites dans ce projet, je ne peux que passer par les fonctions.
J'aurais donc voulu qu'un combo de fonctions excel me permettent de parcourir toute la colonne Z de la feuille principale.
Dans l'onglet RFD vierge, sous la têtière, j'aurais mis en C2 l'INDEX-EQUIV avec le wildcard "*" pour trouver la première valeur qui se présente dans la colonne Z de la feuille principale.
la fonction récupère la valeur trouvée "PL8" et en C3, j'aurais mis une fonction qui me permettait de modifier la plage de la fonction INDEX-EQUIV pour repartir de la ligne après la première occurrence trouvée afin que l'INDEX-EQUIV cherche la première occurrence dans la nouvelle plage.
J'aurais tire la formule sur un nombre de lignes suffisamment grand par rapport au nombre de lignes de la feuille principale et je me serais ainsi affranchi d'une macro pour la création des lignes.
Quoiqu'il en soit, votre formule m'a permis de comprendre (pas totalement encore) comment récupérer toute les occurrences d'une valeur identique dans une colonne.
Merci, merci encore LouReed.

re,

au lieu de comparer avec "PL8", on compare avec <>""

si vous voulez reconstruire une ligne complète ou plusieurs colonnes, c'est peut-être mieux d'utiliser une colonne auxiliaire dans laquelle vous calculez la ligne et puis vous utilisez ce résultat avec une fonction comme Index ou Decalage ou ...