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

KLfy

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

Pièces jointes

  • Exemple INDEX EQUIV sur une plage de données.xlsx
    8.5 KB · Affichages: 33

chris

XLDnaute Barbatruc
RE

Pas très clair tout cela :
  • le tableau Data1 contient 2 sous tableaux : cela ne va pas.
    Sans doute devrait-il commencer en ligne 19...
  • sur Groupes d'analyse:
    • si on doit utiliser le nom c'est le nom qui devrait être en en-tête ligne 4 et non le groupe
    • si je comprends bien nom c'est catégorie ?
    • les listes déroulantes ne fonctionnent pas et les formules de validation, fausses, on l'air de se mordre la queue avec celle de l'onglet Listes déroulantes
Ci-joint un exemple, selon ce que j'ai compris, avec
  • les données
  • un tableau des groupes avec listes déroulantes basées sur REF
  • le résultat calculé par PowerQuery
 

Pièces jointes

  • Catégorie_Flux_PQ.xlsx
    26.9 KB · Affichages: 4

KLfy

XLDnaute Nouveau
Bonsoir,

@JHA : oui j'avais bien noté que c'était une formule matricielle, qui marche très bien sur le fichier exemple mais je n'ai pas réussi à l'appliquer sur mon fichier réel. Je remets la dernière version en pièce-jointe, peut-être trouverez-vous l'erreur de syntaxe dans la cellule D30 de l'onglet "Groupe d'analyse".

Le problème vient de mon tableau il semblerait : il le reconnaît bien quand je tape des formules, mais une fois la formule terminée il ne comprends pas les parties du tableau appelées (formule non colorée).
Mystère pour moi !

@chris
  • Si je ne me trompe pas, vous faites référence à un tableau qui traînait d'essais précédents, j'ai fait le ménage entre-temps donc normalement il y en a bien plus qu'un (ou pas vu le problème ci-dessus),
  • Si je comprends bien votre remarque, j'ai utilisé une ligne avec les noms au-dessus car d'après mes connaissances il n'est pas possible de renseigner une formule dans un en-tête de tableau (cela renvoie un zéro) :
    • les noms 1, 2, 3, 4 etc. seront les noms des groupes sous lesquels on souhaite rassembler les données (en vue de faire un graphique empilé), comme ils sont susceptibles de changer j'ai du gardé les noms "groupe 1, 2, etc." pour les formules et éviter de les renseigner à chaque fois.
    • entre-temps j'étais en train de travailler sur les listes déroulantes qui effectivement avaient un problème, à priori elles marchent maintenant mais il me reste une question que je poserai dans un post séparé.
Je vous remercie pour votre aide, je reviens vers vous dès que j'ai étudié en détail le fichier et essayer de comprendre ce que vous avez fait car je ne connais pas du tout PowerQuery. Cependant, je pense que le problème vient de mes tableaux qui semblent ne pas être reconnus.

Bonne soirée à vous deux.
 

Pièces jointes

  • Outil mise en forme résultats_v6.xlsx
    197.7 KB · Affichages: 3

KLfy

XLDnaute Nouveau
Bonjour,

@chris : merci beaucoup pour votre aide, PowerQuery a l'air super puissant comme outil ! Cependant, cela demande que je revois la construction de mon fichier Excel non ?
N'y connaissant rien à cet outil, cela me demanderait de me former dessus.

@JHA : j'ai bidouillé avec votre formule et je crois que je touche presque au but. J'arrive à obtenir une valeur avec une version tronquée sans la fonction NBVAL. Certes, celle-ci ne correspond pas à ce que j'attends et je ne sais même pas comment elle peut être obtenue, mais c'est mieux que d'avoir une erreur je suppose.

Auriez-vous un moment pour m'expliquer comment fonctionne l'imbrication de la formule NBVAL avec la formule DECALER et la formule SOMME ?
Je comprends le but des formules mais je n'arrive pas à comprendre comment elles marchent ensemble. J'ai créé un encadré "test" dans lequel j'ai joué avec les formules pour essayer de trouver la source du problème pour vous donner une idée de mon raisonnement.

Je vous remercie par avance de votre aide 🙏
 

Pièces jointes

  • Outil mise en forme résultats_v6.xlsx
    199.5 KB · Affichages: 2
Dernière édition:

JHA

XLDnaute Barbatruc
Bonjour à tous,

Un essai en pièce jointe onglet "Groupe d'analyse".
VB:
=SOMME(SI(Data1[[#En-têtes];[Flux 1]:[Flux 21]]=GroupeAnalyse1[Groupe 1];DECALER(Data1[[#En-têtes];[Catégorie]];EQUIV($A30;Data1[Catégorie];0);3;;21)))
Je te suggère d'évaluer la formule dans le ruban, onglet formules/Evaluer la formule
Autrement pour la fonction decaler, elle fonctionne ainsi
DECALER(réf, lignes, colonnes, [hauteur], [largeur])
Ref: cellule "A3" de l'onglet données (tableau Data 1)
Lignes: on recherche la ligne du tableau Data 1 comportant "Climate change" avec la fonction equiv()
Colonne: on décale de 3 colonnes pour débuter la plage en colonne "D" du tableau Data 1
Hauteur: pas besoin dans notre cas puisque l'on recherche une plage sur une ligne
Largeur: j'ai mis 21 car il y a 21 Flux

Concernant NBVAL()
Code:
=NBVAL(Données!$3:$3)-3
cette formule compte le nombre de valeur de la ligne 3 de l'onglet "Données"; il faut ôter 3 valeurs car la plage des flux débute en colonne "D"

JHA
 

Pièces jointes

  • Outil mise en forme résultats_v6 (1).xlsx
    204.8 KB · Affichages: 3

chris

XLDnaute Barbatruc
Bonjour à tous
@chris : merci beaucoup pour votre aide, PowerQuery a l'air super puissant comme outil ! Cependant, cela demande que je revois la construction de mon fichier Excel non ?
N'y connaissant rien à cet outil, cela me demanderait de me former dessus.
Comme je l'ai dit il y a plein d'incohérences dans ton fichier : tu utilises un tableau structuré dans Data1 mais il n'est pas correctement exploitable car contient 2 tableaux avec 2 lignes d'en-tête

Ce pourquoi j'ai créé un fichier montrant comment bien utiliser les tableaux structurés

La requête PowerQuery ici est simple, s'adapte automatiquement au nombre de groupes dans le tableau de Choix, ne prend pas de temps de calcul.

Ce n'est pas plus compliqué de créer des requêtes simples que des formules élaborées...
Mais je ne force personne
 

KLfy

XLDnaute Nouveau
Bonjour à tous,

Un essai en pièce jointe onglet "Groupe d'analyse".
VB:
=SOMME(SI(Data1[[#En-têtes];[Flux 1]:[Flux 21]]=GroupeAnalyse1[Groupe 1];DECALER(Data1[[#En-têtes];[Catégorie]];EQUIV($A30;Data1[Catégorie];0);3;;21)))
Je te suggère d'évaluer la formule dans le ruban, onglet formules/Evaluer la formule
Autrement pour la fonction decaler, elle fonctionne ainsi
DECALER(réf, lignes, colonnes, [hauteur], [largeur])
Ref: cellule "A3" de l'onglet données (tableau Data 1)
Lignes: on recherche la ligne du tableau Data 1 comportant "Climate change" avec la fonction equiv()
Colonne: on décale de 3 colonnes pour débuter la plage en colonne "D" du tableau Data 1
Hauteur: pas besoin dans notre cas puisque l'on recherche une plage sur une ligne
Largeur: j'ai mis 21 car il y a 21 Flux

Concernant NBVAL()
Code:
=NBVAL(Données!$3:$3)-3
cette formule compte le nombre de valeur de la ligne 3 de l'onglet "Données"; il faut ôter 3 valeurs car la plage des flux débute en colonne "D"

JHA

@JHA, je reviens vers vous car votre formule fonctionne très bien mais j'avais mal exposé mon problème : les noms des listes déroulantes n'auront pas le même nom que les en-têtes des tableaux mais pourront être n'importe quel texte cf pour "GroupeAnalyse2" avec l'exemple du groupe 1 qui a un flux appelé "ethanol".

De ce fait, il faut que je modifie la partie en gras de votre formule :
VB:
=SOMME(SI([B]Data1[[#En-têtes];[Flux 1]:[Flux 21]][/B]=GroupeAnalyse1[Groupe 1];DECALER(Data1[[#En-têtes];[Catégorie]];EQUIV($A30;Data1[Catégorie];0);3;;21)))

Pour cela, mon objectif est d'appeler la bonne ligne des colonnes [Flux 1]:[Flux 21]. J'ai à nouveau créer un encadré pour expliquer ma démarche et mes essais, en espérant que cela soit clair.

Lorsque je fais l'exercice sans formule matricielle, celle-ci fonctionne : elle me trouve bien la valeur de la cellule de la colonne [Flux 1] qui se trouve sur la même ligne que la valeur "Total" de la colonne [Total] :
VB:
=INDEX(Data2[Flux 1];(EQUIV("Total";Data2[Total];0)))

Lorsque je passe à un cran supérieur, en cherchant si cette valeur se trouve dans ma colonne [Groupe1] de mon tableau [GroupeAnalyse2], la bonne valeur de 40 est renvoyée :
VB:
=SI(INDEX(Data2[Flux 1];(EQUIV("Total";Data2[Total];0)))=GroupeAnalyse2[Groupe 1];DECALER(Data2[[#En-têtes];[Catégorie]];EQUIV($A30;Data2[Catégorie];0);3;;NBVAL(Données!$3:$3)-3))

En revanche, lorsque je cherche à faire référence à la plage de données complète se trouvant sur cette même ligne dans les colonnes [Flux 1]:[Flux 21] pour l'intégrer dans votre formule, j'obtiens un problème de référence probablement à cause de la partie en gras :
VB:
=SI(INDEX([B]Data2[[Flux 1]:[Flux 21]];(EQUIV("Total";Data2[Total];0)))[/B]=GroupeAnalyse2[Groupe 1];DECALER(Data2[[#En-têtes];[Catégorie]];EQUIV($A30;Data2[Catégorie];0);3;;NBVAL(Données!$3:$3)-3))

Avez-vous une idée de l'erreur de syntaxe qui pose mon problème ou fais-je totalement fausse route avec ma formule ?
Je vous remercie infiniment une nouvelles fois !
 

Pièces jointes

  • Outil mise en forme résultats_v8.xlsx
    205.3 KB · Affichages: 2

JHA

XLDnaute Barbatruc
Bonjour à tous,

Cela se complique, tant que tu ne saisis pas les principes e la fonction décaler, tu auras des soucis.
VB:
=SOMME(SI(GroupeAnalyse2[Groupe 1]=DECALER(Data2[[#En-têtes];[Catégorie]];EQUIV("Catégorie d'impact";Data2[Catégorie];0);2;;22);DECALER(Data2[[#En-têtes];[Catégorie]];EQUIV("Catégorie d'impact";Data2[Catégorie];0)+1;2;;22)))

La solution Power Query de Chris ;) est bien plus simple.

JHA
 

Pièces jointes

  • Outil mise en forme résultats_v8.xlsx
    210.9 KB · Affichages: 5

Discussions similaires