Affecter des données (en %) selon choix dans 2 listes déroulantes

Bonsoir,

Je te propose la solution suivante : http://www.cjoint.com/c/GHAajnhK4Kf

La formule utilisée :

=INDEX(INDIRECT(ADRESSE(3;LIGNE()-1;1;1;"Feuil1")):INDIRECT(ADRESSE(16;LIGNE()-1;1;1;"Feuil1"));EQUIV($E$6&" "&$E$7;Feuil1!$A$3:$A$16;0);1)

La formule s'adapte automatiquement en fonction de la ligne où elle se trouve et donc peut être "tirée" sur toutes les lignes de la zone rouge.

Cela se fait grâce au calcul de l'adresse pour déterminer les plages de recherche (colonne B à G)

Dans ce calcul, on trouve le numéro de colonne de l'onglet 1 en se basant sur le numéro de ligne où se trouve la formule -1 (exemple formule en I3 de l'onglet 2 : donne 3-1=2 soit la colonne B de l'onglet 1)

Ensuite pour trouver la ligne contenant les données dans la plage, on utilise la fonction EQUIV qui va chercher dans la plage $A$3:$A$16 la concaténation des cellules E6 " "  E7.

J'ai du rectifier ton libellé dans l'onglet 1 pour "Plus de 62 ans" (il manquait le "de" que tu avais par ailleurs mis dans ta liste déroulante et donc cela ne pouvait pas marcher)

Merci de confirmer si cela correspond à tes attentes.

A+

Chris

Super ! Merci beaucoup ! C'est exactement ce que je cherchais à faire ! Je n'ai pas tout compris mais je vais recopier ta formule :) 

Merci à toi,

Juste un petit problème sur le fichier que tu m'as renvoyé lorsque je choisi la tranche d'âge 20 - 30 ans avec le profil dynamique cela ne fonctionne pas et me renvoi dans les cases en rouges #N/A

Désolé ! cela doit être le seul test que je n'ai pas fait... En fait l'erreur vient du libellé dans la Feuille1 : ce n'était pas évident mais tu avais encodé un "blanc" à la fin et dans ta liste déroulante, tu n'avais pas mis ce blanc donc la recherche EQUIV ne trouvait pas de solution d'où les vilains #NA.

Il y a moyen d'éviter le #NA et le remplacer par un "vide"  quand la formule ne trouve pas mais dans ton cas de figure on doit en principe TOUJOURS trouver quelque chose alors je préfère afficher le #NA  car cela veut dire qu'il y a une discordance entre tes données et tes listes (ce qui a été le cas ici)

Voici le fichier corrigé : http://www.cjoint.com/c/GHApCrkMXhf

Explications complémentaires :

=INDEX(INDIRECT(ADRESSE(3;LIGNE()-1;1;1;"Feuil1")):INDIRECT(ADRESSE(16;LIGNE()-1;1;1;"Feuil1"));EQUIV($E$6&" "&$E$7;Feuil1!$A$3:$A$16;0);1)

La fonction principale dans la formule est INDEX(matrice;ligne;colonne).  Cette fonction va chercher dans une matrice le contenu de la cellule se trouvant au croisement de la ligne et la colonne indiquée.  Les matrices dans ton cas sont les colonnes B à G de la Feuille 1. 

Comme il faut faire évoluer la matrice de recherche en fonction de la ligne où la formule se trouve, nous allons utiliser les fonctions ADRESSE et INDIRECT pour faire évoluer la plage en fonction de la ligne.

La fonction ADRESSE(Num.ligne;Num.colonne;abs;A1;nom feuille) (voir aide Excel pour explications de la syntaxe) a été complétée par 3 pour l'adresse du début de plage et 16 pour la dernière ligne de la plage.  Cela est figé puisque tes matrices vont toutes de 3 à 16. 

Là où cela devient intéressant c'est pour la colonne car c'est celle ci qui doit varier à chaque ligne (pour aller chercher dans la colonne B puis C puis D ...).  Comme tes colonnes sont juxtaposées, cela veut dire qu'elles augmentent de 1 à chaque ligne.  Donc nous pouvons nous appuyer sur la numérotation des lignes qui augmente aussi de 1 à chaque ligne.  Il faut juste corrigé le décalage de départ. Comme ton tableau de recherche part de la ligne 3 il suffit d'enlever 1 pour trouver le numéro de la colonne  de la première matrice (3-1=2 soit B).

Nous construisons ainsi l'adresse de la première et de la dernière cellule des matrices.

Il faut savoir que lorsque nous construisons une adresse dans Excel, il faut utiliser la fonction INDIRECT pour lire le contenu de la cellule que représente l'adresse (voir explications dans aide Excel)

Nous avons ainsi construit la plage évolutive de la matrice dans laquelle nous allons aller chercher le pourcentage.

Il reste à trouver la ligne et la colonne :

Le plus facile : la colonne : comme la plage ne comporte qu'une seule colonne (par exemple la colonne 2 soit B) c'est forcément la colonne 1

Reste la ligne : c'est là qu'intervient la fonction EQUIV qui va aller chercher la concaténation $E$6&" "&$E$7 dans la plage Feuil1!$A$3:$A$16 avec comme paramètre 0 (pour dire que l'on désire une recherche exacte càd par a peu près - voir explication aide Excel).

C'est donc cette recherche qui ne pouvait se faire car la concaténation était introuvable dans la plage puisqu'il y avait un blanc en dernière position de ton libellé dans la Feuille1

Ouf !

Je conseille de prendre 2 aspirines après avoir lu ces explications !

J'espère que c'est un peu plus clair pour toi !

Un conseil : ce qui parait difficile à appréhender dans ces longues formules, est l’imbrication des fonctions.  Donc, pour pouvoir les comprendre, il faut bien scinder la formule pour isoler les fonctions et tout devient plus clair.  Soit tu mets des couleurs différentes soit tu les découpes.  Tu peux aussi, lors de la mise au point, utiliser des cellules non utilisées pour insérer les fonctions isolées pour t'apercevoir comment elle travaille et quel résultat elle donne.  

Il y a aussi un assistant sous l'onglet Formules : à droite au dessus : Evaluer la formule

Si tu mets le curseur dans la cellule contenant la formule et que tu cliques sur le bouton "Evaluer la formule" tu pourras voir comment Excel calcule la formule.  Tu pourras surtout voir quand elle se plante et cela permet de t'aider pour la mise au point.

Bon j'arrête sinon il faudra tout le tube d'aspirine...

Si tu as encore des questions n'hésites pas à me revenir.

Bonne courage.

A+

Chris

Petite correction mais qui a son importance dans ma phrase :

Reste la ligne : c'est là qu'intervient la fonction EQUIV qui va aller chercher la concaténation $E$6&" "&$E$7 dans la plage Feuil1!$A$3:$A$16 avec comme paramètre 0 (pour dire que l'on désire une recherche exacte càd pas a peu près - voir explication aide Excel). 

Il fallait pas et pas par...

A re+

Whaouw ! Je ne serai comment te remercier ! Tu m'enlèves une épine du pied ^^. J'ai bien pris le temps de lire ton paragraphe et cela m'a permis d'y voir (un peu) plus clair même si certaines notions restent encore floues. J'appliquerai ton bon conseil de scinder les formules en deux pour y voir plus clair et comprendre ce que fais la machine. Je vais pouvoir améliorer à présent mon tableau en y ajoutant d'autres fonctions en espérant résoudre par moi-même tous les problèmes que je risque de rencontrer ! Je te remercie encore une fois pour ton travail et ta patience.

Bonne soirée,