XL 2016 Fonction INDEX/EQUIV lorsque la valeur recherchée peut varier en colonne et ligne

[KLfy]

Bonsoir à tous,

Je m'excuse d'avance si la réponse se trouve quelque part sur le forum, ma recherche a été infructueuse jusque-là.

Je connais les formules INDEX et EQUIV que j'utilise déjà dans d'autres parties de l'outil que je cherche à mettre en place mais je bloque sur ce cas précis.

Rapide explication de ce que je cherche à faire : je cherche à renvoyer la valeur d'une cellule B en fonction de la position d'une cellule A dans un tableau sachant que la position de la cellule A peut varier en colonne ET en ligne. Dans mon exemple ci-joint, mes valeurs sont sur la plage A1: D4 mais elle pourrait aussi être sur la plage A3: D6.
Comment faire pour renvoyer la valeur de la catégorie 1 du flux 2 quelque soit sa position dans la plage de données ?

N'hésitez pas à me dire si ce n'est pas clair, j'ai mis la formule que je me maîtrise (sur 1 colonne) et celle que je pensais qui marcherait (sur la matrice) pour aider à comprendre mon objectif.

Merci beaucoup aux experts qui se pencheront sur mon cas.

Ps je ne maîtrise pas le VBA donc j'espère que c'est faisable avec une formule "bien pensée"

 

[chris]

Bonjour

 

[KLfy]

Super merci beaucoup, je n'avais pas pensé à utiliser les plages de données !
En revanche, je me pose la question : comment je fais pour définir à l'avance ma plage de données "En-têtes" si je ne connais pas la ligne où elle sera située ?

 

[chris]

Bonjour

Le seule chose qui compte est le nom du tableau : Excel sait ou il est et en trouve les lignes et colonnes tout seul

 

[KLfy]

Bonjour,
Après création d'un tableau avec des colonnes et en-têtes puis moult essais, je n'arrive pas à faire fonctionner votre formule sur mon cas. Un des problèmes vient qu'il n'a pas l'air de reconnaître la syntaxe [#En-têtes].

Du coup, j'ai reposé le problème à plat et j'ai peut être une façon plus simple de présenter ce que je cherche à faire. La donnée que je recherche doit pouvoir s'obtenir selon 3 étapes :
1) Rechercher la ligne où se trouve le texte X sachant que la colonne où il se trouve est connue car toujours fixe (représentée en orange),
2) Rechercher la colonne où se trouve le texte Y, colonne qui peut varier mais le texte Y est toujours sur la même ligne que le texte X (rerésentée par l'intersection entre ma colonne verte et ligne bleue, celle-ci pouvant se trouver n'importe où sauf dans la colonne orange),
3) Renvoyer la valeur se situant dans la même colonne que le texte Y (colonne verte) mais n lignes plus bas (pour mon exemple, ça sera la ligne d'en-dessous).

Comme vous pouvez le voir, j'utilise les fonctions EQUIV pour faire les étapes 1 et 2 et la fonction INDEX pour l'étape 3.
Dans un premier temps j'ai indiqué à la main, la ligne où chercher le texte Y pour l'étape 2. Mon problème vient quand j'essaie de combiner les étapes 1 et 2.
Comme je connais pas de formule qui renvoie une certaine plage de données sur une ligne en indiquant le numéro de cette ligne, j'ai essayé de "tricher" avec une fonction Concatener pour obtenir le même résultat que lorsque je le tape à la main. Malheureusement Excel n'a pas l'air de reconnaître ma syntaxe...

Je me sens un peu stupide car je ne vois pas où est le problème dans ma façon de faire. Une aide serait grandement appréciée. Merci beaucoup !

 

[chris]

RE

Soit tu n'a pas créé un tableau structuré, soit ta version n'est pas en français, soit tu as mal recopié la formule, simple et qui fonctionne parfaitement sur mon exemple.

Cela fait 17 ans que les tableaux structurés existent et deviennent incontournables donc je ne comprends pas ton idée d'usine à gaz.

 

[R@chid]

Cela fait 17 ans que les tableaux structurés existent et deviennent incontournables donc je ne comprends pas ton idée d'usine à gaz.

Bonjour @ tous,
Salut Chris,
Je pensais que les tableaux structurés ont vu le jour depuis la version 2007

 

[JHA]

Bonjour et bonne année à tous,

Pourquoi faire simple quand on peut faire compliquer.

=DECALER(INDIRECT(ADRESSE(LIGNE(DECALER($B$2;EQUIV($I$1;$B$3:$B$13;0);));2));1;EQUIV($I$2;DECALER($B$2;EQUIV($I$1;$B$3:$B$13;0);;;5))-1)

JHA

 

[chris]

Salut Chris,
Je pensais que les tableaux structurés ont vu le jour depuis la version 2007

2003 a introduit les listes de données qui, bien que n'ayant pas de nom ni de syntaxe particulière pour les formules, avaient déjà une bonne partie de leur avantages : nombre de lignes géré, les 3 parties (en-tête, corps et totaux), mode de sélection spécifique....

2007 a

• modifié le vocable (bien moins pertinent)
• ajouté le nom
• ajouté la syntaxe qui a évolué et s'est stabilisé dans 2010.

 

[R@chid]

Re,

2003 a introduit les listes de données qui, bien que n'ayant pas de nom ni de syntaxe particulière pour les formules, avaient déjà une bonne partie de leur avantages : nombre de lignes géré, les 3 parties (en-tête, corps et totaux), mode de sélection spécifique...

Si si je me rappelle de quelque chose comme ça sous Excel 2003, oui t'as tout à fait raison.

 

[KLfy]

RE

Soit tu n'a pas créé un tableau structuré, soit ta version n'est pas en français, soit tu as mal recopié la formule, simple et qui fonctionne parfaitement sur mon exemple.

Cela fait 17 ans que les tableaux structurés existent et deviennent incontournables donc je ne comprends pas ton idée d'usine à gaz.

Ma proposition d'usine à gaz en reformulant n'était pas par plaisir, simplement car je n'arrivais pas à faire fonctionner la formule mais j'ai finis par y arriver. Il devait y avoir un problème dans le tableau structuré effectivement.

Puis-je vous demander comment il faudrait faire pour aller chercher la valeur d'un ensemble de flux X de la catégorie Y et de les sommer ?
Dans mon exemple, cela correspond à sommer la valeur des flux 1 et 3 de la catégorie 1. C'est facile de le faire à la main pour 2 flux mais mon tableau réel est beaucoup plus grand donc ça sera trop lourd.

Je suppose que cela se fait avec une fonction matricielle et/ou somme prod, mais je ne maîtrise pas assez ces formules. Ma tentative avec Sommeprod ne marche pas, ma syntaxe est probablement à côté de la plaque.

Je vous remercie infiniment pour votre aide et n'hésitez pas à me dire si la réponse à ma question a déjà été donnée ailleurs sur le forum. Je ne l'ai pas trouvé mais je suis peut-être passé à côté.
Même chose si je dois clôturer ce post et faire une nouvelle demande séparée.

EDIT : avec le fichier c'est mieux !

 

[JHA]

Bonjour à tous,

Un essai en pièce jointe (formule matricielle

=SOMME(SI(Data[[#En-têtes];[Flux 1]:[Flux 3]]=Flux[Flux que l''on veut];DECALER(Data[[#En-têtes];[Catégorie]];EQUIV(B8;Data[Catégorie];0);1;;NBVAL(1:1)-1)))

JHA

 

[chris]

RE à tous

Comme JHA, je n'ai pas trouvé de formule générique, s'adaptant aux choix faits dans les tableaux de choix

Faisable sinon par PowerQuery mais je pense que l'exemple est biaisé par rapport au contexte opérationnel réel : veut-on réellement juste une cellule isolée comme réponse ?

 

[KLfy]

Bonjour,

@JHA, merci pour votre aide, je pense qu'on tient quelque chose. La première fois que je l'ai appliqué, la formule fonctionnait mais me renvoyait un zéro et je n'ai pas réussi à trouver quelques cases étaient pris en compte pour obtenir ce zéro.
Puis j'ai modifié des données dans le tableau et maintenant j'ai un problème de référence.

@chris : je ne suis pas sûr de comprendre votre question.

Je pensais simplifier ma demande en vous envoyant un fichier avec un exemple mais je crois qu'il vaut mieux que je vous envoie le fichier "complet".

Mes données sont dans le tableau "Data1" de l'onglet "données". Ce que je cherche à faire c'est combiner les résultats des flux 1 à 21 en différents groupes. Ces groupes sont définis avec le tableau "GroupeAnalyse1" de l'onglet "Groupes Analyse" grâce à des listes déroulantes.

Donc, pour l'exemple de la catégorie "changement climatique", je voudrais :
- la somme des flux du groupe 1 en D29 de l'onglet "Groupe Analyse" soit 1550 avec les données fictives que j'ai mis,
- la somme des flux du groupe 2 en D30 soit 95,
- la somme des flux du groupe 3 en D31 soit 200,
Le total faisant bien les 1845 que l'on trouve dans le total de la cellule C20 de l'onglet Data1.

Ps étant donné que je découvre les tableaux de données, certaines formules de mon Excel seraient sûrement à revoir pour être simplifiées

 

[JHA]

Bonjour à tous,
Bonjour Chris

C'est une formule matricielle à valider par Ctrl+Maj+Entrée

JHA