Extraire Base : Index Et Tri

[Julie-F]

Bonsoir

Je rencontre des difficultés avec la fonction INDEX à laquelle je dois associer un Tri.
Voici mon probleme : Je dois extraire les données d'un tableau (nommé base) répondant à un critére unique : superieur à 9 % mais ces données devront être présentées sous forme décroissante.

base : Feuil1!$C$5:$M$79
poucentage : Feuille1!$E$5:$E$79

Pour le tri, j'ai essayé la fonction =GRANDE.VALEUR($E$5:$E$79;1) mais ca oblige à déterminer préalablement le nb de rang alors que l'on ignore combien de lignes répondront au critere et donc seront extraites de la base (qui évolue en fonction des saisies)

Auriez-vous une solution miracle ?

 

[Tibo]

Re : Extraire Base : Index Et Tri

Bonjour,

Si tu nous joignais un petit bout de fichier exemple, ça serait plus simple pour qu'on puisse t'aider.

Quant au critère, 9 % : ok, mais 9 % de quoi ?

Le fichier à joindre : zippé de préférence, sans données confidentielles et taille < 48.8 ko

A te (re)lire avec le fichier

@+

 

[Julie-F]

Re : Extraire Base : Index Et Tri

Bonjour,

Si tu nous joignais un petit bout de fichier exemple, ça serait plus simple pour qu'on puisse t'aider.

Quant au critère, 9 % : ok, mais 9 % de quoi ?

Le fichier à joindre : zippé de préférence, sans données confidentielles et taille < 48.8 ko

A te (re)lire avec le fichier

@+

Bonjour Tibo
Le fichier est beaucoup trop lourd et nb liens avec d'autres classeurs. J'ai donc essayé dans le fichier joint de saisir quelques données bidon tentant malgré tout de décrire le tableau récap. que je voudrais obtenir.

 

[JCGL]

Re : Extraire Base : Index Et Tri

Bonjour à tous,

Un essai très largement perfectible...

A+

 

[Tibo]

Re : Extraire Base : Index Et Tri

Bonjour Julie, salut JC,

Un essai avec formule matricielle (permet de ne pas avoir besoin d'une colonne supplémentaire).

en B6 :

=SI(GRANDE.VALEUR(Pourcent+LIGNE()/10000;LIGNE()-5)>0.09;
INDEX('Test Base'!$C$8:$C$26;EQUIV(GRANDE.VALEUR(Pourcent+LIGNE()/10000;
LIGNE()-5);Pourcent+LIGNE()/10000;0));"")

Les autres formules sont basées sur la même logique.

Formules matricielles à valider par CTRL + MAJ + ENTREE.

Attention, sur un fichier de grande taille, les formules matricielles peuvent mettre un "certain" temps de calcul.

@+

 

[JCGL]

Re : Extraire Base : Index Et Tri

Re,
Salut Tibo,

Encore une belle démo de Formule...
Merci à toi
A++

A+ à tous

 

[Julie-F]

Re : Extraire Base : Index Et Tri

Bonjour Tibo et JCGL

Merci infiniment pour votre aide, à l'un comme à l'autre, vos formules vont me rendre un grand service.

Mais je souhaiterai mieux comprendre le fonctionnement de celles-ci.
Et ne pas reprendre simplement les formules que vous avez bien voulu écrire.
Ainsi, pourquoi utiliser les arguments LIGNE()-5 et ()/10000 ?

Et comment faire apparaitre dans le tableau Recap 2 valeurs identiques (ex dans le fichier de Preco 9 et preco 12) ??

Merci encore à vous deux.

 

[JCGL]

Re : Extraire Base : Index Et Tri

Re,

Ravi que nos essais te conviennent.
Pour ce qui est de mon fichier, il te suffit de supprimer les MeFC :

=OU(ESTERREUR($B6);ESTERREUR($C6);ESTERREUR($D6);RECHERCHEV(B6;Base;3;0)<0.09)

pour faire apparaitre les valeurs identiques MAIS tu auras des #N/A
Ligne()-5 te donne 1 si la formule est en ligne 6 : cela permet de recopier vers le bas une formule sans devoir changer le numérique (pour Rang(), Petite.Valeur() qui demande un "ordre").

Je laisse à mon ami Tibo le soin de commenter son excellent code.

 

[Julie-F]

Re : Extraire Base : Index Et Tri

Bonjour JCGL

Merci beaucoup pour ces explications.

J'ai effectué des recherches pour tenter de résoudre ce pb de valeurs identiques (cette eventualité risquant fort de se produire) et j'ai trouvé cette formule sur l'excellent site de Boisgontier qui permet un tri matriciel sur données avec doublons

Code :
=INDEX(champ;EQUIV(GRANDE.VALEUR(NB.SI(champ;">="&champ);LIGNE(INDIRECT("1:"&LIGNES(champ))));NB.SI(champ;">="&champ);0))


Mais je ne parviens pas à l'associer à vos formules. et ne vois pas où le bas blesse.

 

[Tibo]

Re : Extraire Base : Index Et Tri

Bonjour Julie, salut Jean-Claude,

je joins une nouvelle version du fichier avec une gestion correcte des doublons (pas bien testé la 1ère fois ...).

Les petites explications (ou tentatives ...) :

LIGNE()-5 : permet de gérer le 2ème argument de la fonction GRANDE.VALEUR Comme ton tableau commence en ligne 6, LIGNE()-5 va retourner 1 et permettra d'avoir la 1ère plus grande valeur. Sur la ligne suivante (ligne 7), LIGNE()-5 va retourner 2 et permettra d'avoir la 2ème plus grande valeur etc...

+LIGNE(Pourcent)/10000 : formule que j'ai corrigée dans le fichier joint : permet d'ajouter à chaque valeur un montant très petit qui varie à chaque ligne, ce qui permet de pouvoir distinguer les doublons. J'ai mis 10000, on peut mettre plus.

Bon dimanche

@+

 

[Julie-F]

Re : Extraire Base : Index Et Tri

Merci infiniment Tibo tant pour le fichier joint que pour tes explications claires et precises.

bon dimanche à toi ainsi qu'à JCGL.

 

[BOISGONTIER]

Re : Extraire Base : Index Et Tri

Bonjour,

Résultats dans l'ordre des intitulés:

=SI(ESTNUM(PETITE.VALEUR(SI(Pourcent>9%;EQUIV(Intitulé;Intitulé;0));LIGNE(INDIRECT("1:"&LIGNES(Intitulé)))));INDEX(Intitulé;PETITE.VALEUR(SI(Pourcent>9%;EQUIV(Intitulé;Intitulé;0));LIGNE(INDIRECT("1:"&LIGNES(Intitulé)))));"")

JB

 

[Monique]

Re : Extraire Base : Index Et Tri

Bonjour,

Ou bien :
=SI(NB.SI(Pourcent;">"&9%)<LIGNE()-5;"";GRANDE.VALEUR(SI(Pourcent>9%;Pourcent+LIGNE(Pourcent)/9^9);LIGNE()-5))

 

[Julie-F]

Re : Extraire Base : Index Et Tri

Merci JB pour cette nouvelle approche par intitulé.

Merci également à Monique pour sa formule simplifiée.

Petite question toutefois :

1. Quelle valeur aurait-on du mettre si l'on avait voulu extraire les données supérieur ou égal à 10 % ?

2. Ici l'ensemble de la base a été selectionnée mais peut on également l'appliquer par groupe, c'est à dire reprendre chaque groupe les extraire et les classer en ordre décroissant ?

 

[BOISGONTIER]

Re : Extraire Base : Index Et Tri

J'ai qq doutes sur le % (cf PJ)

JB