Discussion :

[bolide7]

Bonjour à tous,

Mon problème à résoudre étant légèrement compliqué, je vous propose de retrouver mon problème avec l’illustration ci-dessous.

Comme vous pourrez le constater, après de nombreuses tentatives, j’ai partiellement résolu mon problème, Cependant, je souhaiterai automatisée cette recherche de valeur pour compléter mon tableau inférieur qui est en réalité beaucoup plus grand que dans ce test :
- En recherchant la cellule D10 (2021) dans la plage C3:P3 pour retrouver la plage C3:F6 affectée à l'année.
- Puis en retrouvant la ligne AR00001 dans la plage (A3;A6).
- Et enfin en décalant de 3 colonnes pour retrouver la valeur 100.

Test.xlsx

D’avance, merci beaucoup pour votre aide.
Bonne journée.
bolide7

[Pierre Dumas]

Bonjour

Il a manqué l'illustration, la voici à partir du fichier que j'ai ouvert.




Voici comment j'ai procédé pour répondre stricto-sensu à la question.
J'ai créé 3 zones nommées :
- C3:F6 nommée Donnees2021
- H3:K6 nommée Donnees2022
- M3:P6 nommée Donnees2023

J'ai ensuite légèrement modifié la formule en D12 afin qu'elle prenne en compte la bonne plage en fonction de l'année saisie en D10, F10 et H10. La formule devient :
=INDEX(INDIRECT("Donnees"&D$10);EQUIV($A12;$A$3:$A$6;0);3)

La fonction INDIRECT permet de fournir à Excel une référence (à une cellule, une plage ou une zone nommée) en la bâtissant avec des concaténations (le & dans la formule).


Ceci étant, je me doute bien qu'il ne s'agit que d'un exemple qui a été bâti pour les besoins de ce forum. La solution proposée n'est donc certainement pas optimum.
Il faudrait très certainement mettre chacune des zones de départ sous forme de tableau structuré.
Il faudrait très certainement n'avoir qu'une seule zone de départ avec une colonne supplémentaire pour l'année.
Il faudrait très certainement utiliser un tableau croisé dynamique pour obtenir le résultat sans aucune formule tout en étant souple sur les modifications des données de départ.

En espérant que cela aide

Bonne journée

Pierre Dumas

[bolide7]

Bonjour Pierre,

Je ne sais pas pourquoi, peut-être une mauvaise démarche, je ne suis pas arrivé à placer dans mon post la photo initialement prévue.
J’ai dû la remplacer par un fichier en oubliant de modifier mon texte.
Mille excuses.

Merci beaucoup pour ta très rapide réponse et ta formule qui fonctionne très bien.
Ce remerciement est d’autant plus justifié qu’il n’y a pas qu’une simple formule, mais aussi les explications qui vont avec.

J’ai cependant encontre quelques problèmes avec la création des zones que je n’ai pas pu nommer comme je le souhaitais.
A priori, Monsieur Bill GATES n’aime pas les nombres, les espaces, les caractères spéciaux dans les noms des zones.

Si ta fonction fonctionne très bien, je pensais qu’une formule (avec plusieurs fonctions imbriquées) aurait été suffisante pour résoudre mon problème.
Le passage par la création de zones est-il obligatoire ou est-il là pour simplifier la formule ?

Encore merci et bonne soirée.

bolide7

[Pierre Dumas]

Bonsoir

[...]
J’ai cependant encontre quelques problèmes avec la création des zones que je n’ai pas pu nommer comme je le souhaitais.
A priori, Monsieur Bill GATES n’aime pas les nombres, les espaces, les caractères spéciaux dans les noms des zones.

Effectivement, les noms ne doivent pas commencer par des nombres, sans espaces ni caractères spéciaux. Je recommande aussi au moins 4 lettres avant de mettre un chiffre (éviter A380, Q7, ...) afin de ne pas confondre avec une cellule.

Si ta fonction fonctionne très bien, je pensais qu’une formule (avec plusieurs fonctions imbriquées) aurait été suffisante pour résoudre mon problème.
Le passage par la création de zones est-il obligatoire ou est-il là pour simplifier la formule ?

L'utilisation est effectivement pour simplifier la formule.
Just for fun, mais on pourrait en trouver d'autres, une formule qui fonctionne sans l'utilisation des noms : =INDEX(DECALER($C$3;0;EQUIV(D$10;$1:$1;0)-3;4;4);EQUIV($A12;$A$3:$A$6;0);3)

La partie qui change est bien entendu celle de la fonction DECALER qui apparait au lieu d'INDIRECT.
Comme son nom l'indique, elle décale à partir d'une cellule pour fournir à Excel une autre plage.
Sa syntaxe est : DECALER(cellule de départ ; décalage de lignes ; décalage de colonnes ; hauteur finale de la plage ; largeur finale de la plage).

Ici, le but est de trouver la plage bleue correspondant à l'année.
la cellule de départ est C3
le décalage de lignes est 0
le décalage de colonnes est fourni par la fonction EQUIV qui va chercher l'année dans la ligne 1. On enlève 3 car on veut que 2021 qui se trouve en C3 renvoie 0. De même que 2022 qui se trouve en H3 renvoie 5.
la hauteur finale de la plage est ici de 4, mais il faut le modifier pour s'adapter à la hauteur réelle de la plage bleue. Cela peut aussi se calculer, mais complexifie la formule.
la largeur finale de la plage est ici de 4 car c'est celle de la plage bleue.

Là encore, je conseille fortement d'être sous forme de tableaux structurés. On ne s’embêterait pas avec ces formules.

En espérant que cela aide.

Bonne nuit

Pierre Dumas (insomniaque cette nuit)

[bolide7]

Bonjour Pierre,

MAGNIFIQUE !!!
MERVEILLEUX !!!
Difficile de dire autre chose.
Après avoir adapté ta proposition à mon tableau, cela marche très bien.

Je me suis seulement permis de remplacer :

Code formule :

Sélectionner tout - Visualiser dans une fenêtre à part

EQUIV(D$10;$1:$1;0)-3

par la plage précise de la recherche où se trouve les années (le tableau sera construit jusqu’à 2030 - 2031) :

Code formule :

Sélectionner tout - Visualiser dans une fenêtre à part

EQUIV(D$10;$C$1:$P$1;0)-1

Par contre pour que cela fonctionne, j’ai dû retirer 1.
Et je n’ai pas compris pourquoi j’ai dû soustraire 1.
L’année 2021 se trouve pourtant bien dans la première cellule de la plage C1:P1, pourquoi doit-on retirer 1 ?
Pourrais-tu m’apporter cette précision, afin que je puisse comprendre le pourquoi du comment ?

D’avance, merci beaucoup pour ce complément d’information.
Bonne soirée.
Et encore merci beaucoup pour ta très belle formule.
bolide7

[Pierre Dumas]

Bonsoir

Pour l'année 2021 on ne veut pas décaler (ou on veut décaler de 0 colonnes, ce qui revient au même).
Or dans EQUIV(D$10;$C$1:$P$1;0), la cellule C1 se trouve être en première position. Donc il faut retrancher 1 pour arriver à zéro.
Dans ma formule, puisque l'on regardait sur la ligne entière, C1 se trouvait à la 3ème position. C'est pour cela que je retranchais 3 pour arriver aussi à 0.

En espérant avoir été clair.

Comme indiqué dans ma signature, il ne faut pas oublier de mettre "Résolu" quand le sujet est clos.

Bonne soirée

Pierre Dumas

[bolide7]

Bonsoir Pierre,
OK, j’ai compris.
Avec ma modification, on trouve 1, alors que l’on ne veut pas décaler de colonne, c’est pourquoi, j’ai dû soustraie 1, pour retrouver zéro.
Excellente nuit.
bolide7