renvoi d'une valeur = index mais renvoi d'une plage = ?

[jpm2169]

Bonjour à tous,

A partir de la formule index, j'essaie de trouver une astuce qui me permettrait d'afficher non pas une valeur d'un tableau mais une plage de cellules de ce tableau. Je n'y arrive pas.

Comment faire ?

Dans mon exemple, je renvoie une valeur grâce à la formule =INDEX(A5:A28;$E$62) où A5:A28 est la plage et $E$62, la cellule qui contient la variable et qui correspond à la ligne. Si j'écris en $E$62 le nombre 20, il apparaitra, dans la cellule qui contient la formule INDEX, le libellé noté sur la ligne 20 de ma plage.

Dans un autre tableau, j'ai décomposé chaque ligne de ma plage en 6 lignes. Il s'agit juste d'informations que j'ai renseignées. Elles sont le total, le nombre de données, la moyenne, la valeur max, la valeur min et la valeur positive la plus petite.

Lorsque je renseigne en $E$62 la valeur 20, je voudrais que ces 6 lignes apparaissent, en plus de la ligne du libéllé.

Quelqu'un aurait-il une idée ?

J'ai pensé à la formule DECALER mais en convertissant le résultat que je souhaite sous forme d'image grâce à des copies d'écran mais je ne maitrise pas cette formule et cela signifierais que je serais obliger à chaque fois de transformer mon résultat voulu en image.

Je vous remerci pour l'aide que vous pourrez m'apporter.

JPM

 

[Tibo]

Re : renvoi d'une valeur = index mais renvoi d'une plage = ?

Bonjour,

C'est certainement faisable avec une colonne intermédiaire ou avec une formule matricielle.

Mais nous avons besoin d'un extrait de ton fichier pour adapter.

Joins nous ce fichier (zippé de préférence et taille < 48.8 ko)

Nous verrons ce qu'il est possible de faire

A te (re)lire

@+

 

[hoerwind]

Re : renvoi d'une valeur = index mais renvoi d'une plage = ?

Bonjour, salut Tibo,

Essaie en écrivant la formule comme ceci :
=INDEX(A$5:A$28;$E$62+LIGNES($1:1)-1)
et en la copiant vers la bas sur le nombre de lignes souhaité.

 

[jpm2169]

Re : renvoi d'une valeur = index mais renvoi d'une plage = ?

Bonjour, salut Tibo,

Essaie en écrivant la formule comme ceci :
=INDEX(A$5:A$28;$E$62+LIGNES($1:1)-1)
et en la copiant vers la bas sur le nombre de lignes souhaité.

Bonjour

Merci bien pour la formule mais elle ne fonctionne malheureusement pas complètement.

J'ai inséré la formule sous mon graphique dans le tableau que je vais joindre au prochain message.

 

[jpm2169]

Re : renvoi d'une valeur = index mais renvoi d'une plage = ?

voici mon tableau que j'ai remodelé.

Dans le second onglet, j'ai regroupé toutes les lignes de résultats. en gros, j'ai fait disparaitre les données...

Et ce sont les informations sur cet onglet que je souhaiterais inscrire sous le graphique de l'onglet 1.

Par exemple, pour le choix B EX 2009, je voudrais qu'apparaisse sous le tableau les informations total B EX, nb données B EX, valeur la plus grande B EX, valeur positive la plus petite B EX, valeur la plus petite B EX et moyenne B EXpour 2008 et 2009.

Encore merci pour votre aide

 

[hoerwind]

Re : renvoi d'une valeur = index mais renvoi d'une plage = ?

Re,

Pourrais-tu être plus explicite, tu connais ton fichier, nous pas, alors il ne faut pas en faire un jeu de piste.

Une explication compréhensible serait :
Pour la cellule xxxxx et celles en-dessous, je voudrais qu'apparaisse les valeurs des cellules yyyyy, ....

A te lire.

 

[jpm2169]

Re : renvoi d'une valeur = index mais renvoi d'une plage = ?

Bonjour,

Je vais tenter de m'expliquer un peu mieux... désolé...

Dans la feuille onglet "base graphique", j'ai un graphique au dessus duquel un menu déroulant me permet de choisir un libellé parmi ceux présents dans mon second tableau situé juste au dessus du menu déroulant.

Ce menu déroulant est lié à la cellule $E$62 (cachée derrière pour des raisons de présentation).

Entre le menu déroulant et le graphique, j'ai un tableau de 3 lignes qui reprend les résultats attendu grâce à la formule =INDEX(A5:A28;$E$62) pour la première ligne et première colonne. Cette formule est recopiée avec modification de la plage pour les résultats des autres cellules de ce petit tableau.

Mais ce petit tableau donne un total et j'ai besoin d'informations complémentaires. J'ai donc travaillé sur un gros fichier pour obtenir des informations que j'ai reportées dans l'onglet "report total NB Données". Il s'agit des informations suivantes :
- le total,
- le nombre de données,
- la valeur max,
- la valeur positive la plus petite,
- la valeur min,
- la moyenne.

Pour chaque cellule du tableau de 3 lignes, il existe ces informations que je souhaite inscrire sous le graphique.

En sélectionnant "A 2009", je veux donc comme c'est écrit actuellement dans le fichier joint :

Résultat----OCTOBRE----NOVEMBRE--- ...
A 2008----267 644,35 €----1 043 418,50 €
A 2009----2 227 214,50 €----527 540,00 €

Mon graphique


2008----OCTOBRE----NOVEMBRE
Total A----267 644,35 €----1 043 418,50 €
nb données A----37,00 €----35,00 €
valeur la plus grande A----150 000,00 €----452 000,00 €
valeur positive la plus petite A----220,00 €----19,00 €
valeur la plus petite A----(-3 880,00 €)----(-4 350,00 €)
moyenne A----7 233,63 €----29 811,96 €



2009---- OCTOBRE----NOVEMBRE
Total A----2 227 214,50 €----527 540,00 €
nb données A----51,00 €----31,00 €
valeur la plus grande A----610 000,00 €----646 145,00 €
valeur positive la plus petite A----115,00 €----125,00 €
valeur la plus petite A----(-610 000,00 €)----(-502 500,00 €)
moyenne A----43 670,87 €----17 017,42 €


Merci pour votre aide

JPM

 

[hoerwind]

Re : renvoi d'une valeur = index mais renvoi d'une plage = ?

Bonjour,

Merci pour l'explication, mais cela je l'avais déjà ciompris.

Lorsque tu écris :
En sélectionnant "A 2009", je veux donc comme c'est écrit actuellement dans le fichier joint :
Cela tu l'as déjà par les formules saisies dans les tableaux inférieurs.
Tu aurais avantage de rendre absolues les lignes de la plage de recherche, ce qui te permettra de copier les formules vers le bas, sans autre intervention.
Exemple pour B98 :
=INDEX('report total NB Données'!C$2:C$130;$E$62+LIGNES($1:1)-1)

Pour le reste je ne vois pas ce que tu désires, quelle est ta question ?

 

[jpm2169]

Re : renvoi d'une valeur = index mais renvoi d'une plage = ?

Exact, le résultat est correct mais uniquement pour total A car si je sélectionne un autre libellé du menu déroulant (le second par exemple), les résultats détaillés sous le graphique ne sont plus cohérents, même si j'insère des $.

Le résultat se décale d'un ligne puisque je prend le libellé suivant mais il ne reprend pas les valeurs total, nombre de données, valeur max, valeur positive la plus petite, valeur min et moyenne en rapport avec le second libéllé su menu déroulant.

En fait, à chaque choix de menu déroulant correspond 6 lignes voire 9 pour le total (si je dois en supprimer 3 pour le total, j'en supprimerais 3). Je ne sais pas comment m'expliquer.
C'est comme si à chaque choix, un jet de 6 lignes était projeté en dessous du graphique. Les choix se suivent lignes par lignes et les "jets" sont projetés par paquets de 6 lignes.
Le premier choix du menu déroulant correspond au ligne 1 à 6 de mes tableaux en onglet "report total NB Données", le deuxième choix correspondra donc aux lignes 7 à 12, le 3ème choix, aux lignes 13 à 18, le 4ème choix, aux lignes 19 à 24,...

Merci

 

[hoerwind]

Re : renvoi d'une valeur = index mais renvoi d'une plage = ?

Re,

Pas facile à comprendre !

A tout hasard essaie cette formule en 'BASE GRAPHIQUE'!B98 :

=INDEX('report total NB Données'!C$2:C$130;($E$62-1)*6+LIGNES($1:1))

à copier vers le bas et vers le droite.

Fais quelques tests en modifiant le choix dans la liste déroulante et dis nous si ce sont les résultats que tu souhaites.

Il faudra encore adapter la formule en 'BASE GRAPHIQUE'!A98 et suivantes de sorte qu'elle prenne en compte les 9 valeurs de TOTAL 2008

Et te proposer une formule pour les deux tableaux supérieurs afin qu'il soit automatisé.

 

[jpm2169]

Re : renvoi d'une valeur = index mais renvoi d'une plage = ?

A tout hasard essaie cette formule en 'BASE GRAPHIQUE'!B98 :

=INDEX('report total NB Données'!C$2:C$130;($E$62-1)*6+LIGNES($1:1))

à copier vers le bas et vers le droite.

La formule fonctionne. J'ai testé quelques lignes et tout est correct. Merci beaucoup.

En revanche, je ne comprends pas bien la formule. Que signifie ($E$62-1) ?

Et, je le multiplie par 6 car je veux un choix toutes les 6 lignes à partir de la ligne choisie qui porte le n°$1 pour être bloquée et en recopiant, je descend jusqu'à 6 d'où "*6+LIGNES($1:1)" à "*6+LIGNES($1:6)" sur la dernière ligne recopiée ?

Je suis désolé mais j'ai du mal avec la formule mais en tout cas, un grand merci car cela fait longtemps que je cherche. En fait, j'avais laissé tomber et transformé le résultat voulu sous forme d'image.

Quant à mes 2 tableaux plus haut, ils proviennent de la copie de valeurs issues d'un TCD. Peut être qu'une macro pourra faire l'affaire dans l'avenir mais pour l'instant, la demande est exceptionnelle.

Encore Merci

 

[hoerwind]

Re : renvoi d'une valeur = index mais renvoi d'une plage = ?

Bonsoir;

INDEX(matrice;no_lig;no_col)
=INDEX('report total NB Données'!C$2:C$130;($E$62-1)*6+LIGNES($1:1))

Si E62 vaut 1 il faut incrémenter les lignes comme suit : 1, 2, 3, ...
Si E62 vaut 2 il faut incrémenter les lignes comme suit : 7, 8, 9, ...

Saisi dans une cellule quelconque :
($E$62-1)*6+LIGNES($1:1)
et copie cette formule vers le bas, tu obtiendras 1, 2, 3, ...
Modifie cette formule en :
(1-1)*6+LIGNES($1:1)
et copie la vers la bas, tu obtiendras les mêmes résultats
Modifie encore la formule en :
(2-1)*6+LIGNES($1:1)
et copie la vers le bas, tu obtiendras 7, 8, 9

J'espère avoir été clair.

 

[jpm2169]

Re : renvoi d'une valeur = index mais renvoi d'une plage = ?

Bonjour,

Merci pour l'explication, elle m'aide à comrendre les différents test que j'ai effectués. Quoiqu'il en soit, poour moi, ce n'est pas évident : je me suis torturé la tête.

Pour les 9 lignes, j'ai laissé tombé car cela fini par créer des dysfonctionnements. J'ai supprimé les 3 lignes qui existent déjà dans les autres choix et cela me va bien.

Je vais continuer de tester en modifiant la formule pour comprendre définitivement.

Encore merci pour tout et bonne journée.