RECHERCHEh imbriquee et calculs
Bonjour le forum,
Je cherche à faire une
RECHERCHEH()avec des fonctions imbriquées.
Recherche de valeurs et calculs conditionnels.
Fichier explicatif ci-joint.
Je cherche à faire une fonction imbriquée, et non une macro parce que ça me paraît plus lourd, et surtout, j'ai du mal à les adapter
Merci d'avance
Bonsoir,
n'y a t-il pas eu une incrémentation des référence de cellule dans votre fichier ? Car en jaune c'est B3 mais dans la deuxième condition c'est B4, de même pour B5 avec après B6...
@ bientôt
LouReeD
Re bonsoir,
formule en B4 surface recalculée =SI(ET(B3="";B5="");MOYENNE(C3:S3);SI(B3<>"";B3;SI(B5<>"";INDEX(C3:S5;1;EQUIV(B5;C5:S5;0));"")))
Le seul hic c'est qu'il faut en B5 une valeur existante dans le tableau...
C'est un début, nous pouvons continuer à chercher
@ bientôt
LouReeD
Salut,
Je ne suis pas certain de t’avoir compris, mais voici un début de solution.
Tes conditions inscrites aux lignes 7 et 10 sont selon moi faciles à réaliser.
En ce qui concerne tes conditions de la ligne 9, voici ce que j’ai tenté :
Si le chiffre en B5 est plus petit ou égal au chiffre en C5, le chiffre en C3 est reporté en B4 (si vide, 0 est reporté).
Dans les autres cas, le chiffre en B5 est recherché sur la ligne 5. Si la référence exacte est trouvée, le chiffre de la ligne 3 correspondant est renvoyé, sinon le chiffre de la ligne 3 correspondant au chiffre immédiatement inférieur est renvoyé.
Quelques exemples de résultats en B4, à condition que B3 soit vide :
B5 < 525, B4 = 200
B5 = 525, B4 = 300
B5 = 699, B4 = 300
B5 = 700, B4 = 0
B5 = 1224, B4 = 0
B5 = 1225, B4 = 700
B5 = 3149, B5 = 0
B5 >= 3150, B5 = 1800
Si ça ne correspond pas à ton attente, soit plus précis, donne plus d’exemples.
Cordialement.
NB : Au moment de livrer ma solution, je me rends compte qu’elle est très proche de celle de LouReed. Pour le fun, je la livre quand même
Voilà,
en cherchant un peu on arrive à ceci :
en cellule B6 ajout d'un calcul intermédiaire pour trouver la valeur "la plus proche"
B6 : {=MIN(SI(ABS(C5:S5-B5)=MIN(ABS(C5:S5-B5));C5:S5))}
formule matricielle, il faut la valider avec [Shift]+[ctrl]+[entrée]
Ensuite modification de la formule en B4 :
=SI(ET(B3="";B5="");MOYENNE(C3:S3);SI(B3<>"";B3;SI(B5<>"";INDEX(C3:S5;1;EQUIV(B6;C5:S5;0));"")))
où le B5 originel a été remplacé par le B6
Petite modification au vu de votre tableau :
si B3 vide et B5 non vide, alors recherche de la valeur la plus proche en ligne 5 et renvoyé la valeur de la ligne 3 de la même colonne, mais sur votre tableau en ayant B3="" et B5 = 745, en ligne 3 en correspondance de 700 "trouvé" il n'y a pas de valeur donc en B4 affichage pas de valeur :
=SI(ET(B3="";B5="");MOYENNE(C3:S3);SI(B3<>"";B3;SI(B5<>"";SI(INDEX(C3:S5;1;EQUIV(B6;C5:S5;0))=0;"Pas de valeur";INDEX(C3:S5;1;EQUIV(B6;C5:S5;0)));"")))
@ bientôt
LouReeD
Tout d'abord merci à LouReeD
J'ai adapté la proposition de LouReeD (tableau ci-joint).
Cela fonctionne presque comme je veux.
En fait, pour une colonne (par ex B) donnée, les calculs se font sur les colonnes (N+1 : fin du tableau), pour B, (C : S).
Si je fais glisser les formules, les colonnes avant la colonne calculée ne sont pas prises en compte.
Ce qui est embêtant pour la recherche de la valeur la plus proche et le calcul de la moyenne.
J'ai essayé de modifier les formules pour prendre en compte et figer toute la plage de colonnes (B : S)
Mais maintenant, forcément, en prenant toute la plage, la valeur la plus proche est la valeur elle-même, donc il n'y a pas de surface correspondante.
Je pensais que
ABS($B$6:$S$6-B6)signifiait "prendre la plage B6:S6, sauf la cellule B6", mais visiblement non.
Comment faire pour toujours prendre en compte toute la plage.
Bonjour,
le problème a été "mal vu" de ma part :
pour moi les données "variées" en colonne B pour les cellules en ligne 3 et 5, Donc en fonction des valeurs "choisies" ou pas sur ces cellule, le résultat s'affichait en B4. Le reste du tableau servait de base de données...
Hors maintenant je commence à comprendre : vous voulez avoir la formule en ligne 4 de la colonne B à la fin du tableau (exemple colonne S), Est-ce bien cela ?
Donc il faut faire la moyenne toujours de B à S si en ligne 3 pas de valeur et en ligne 5 pas de valeur etc... Quelque soit la colonne ?
Je cherche, mais là je vais manger
@ bientôt
LouReeD
Essayez ceci :
=SI(C3<>"";C3;SI(ET(C3="";C5<>"");SI(C5<D5;D3;INDEX($C$3:$S$5;1;EQUIV(C5;$C$5:$S$5;1)));MOYENNE($C$3:$S$3)))
en B4 puis glisser vers la droite
On s'approche, mais malheureusement, ce n'est pas encore le résultat escompté.
- S'il y a une surface brute, elle est bien reprise dans la surface recalculée.
- S'il n'y a pas de surface brute et pas de volume, la moyenne des surface brutes est bien mise dans les surfaces recalculées.
- S'il n'y a pas de surface brute, mais il y a un volume, c'est là que ça se complique.
SI(B6<C6;C3;...)par
SI(B6=C6;C3;...)parce que je n'ai pas compris pour quoi mettre "<"
2) Dans
EQUIV(B6;$B$6:$S$6;1), j'ai vu que "1" signifie que l'on recherche la valeur la plus proche INFÉRIEURE. Je suis plutôt intéressé de mettre la valeur la plus proche SUPÉRIEURE (c'est mieux de surestimer un peu les coûts, pas de mauvaise surprise
3) Pour décortiquer un peu le fonctionnement, j'ai rajouté des lignes:
3A) Ligne 14: Numéro de colonne de la valeur la plus proche SUPÉRIEURE, cela me donne toujours la 1é colonne
3B) Numéro de colonne de la valeur la plus proche INFÉRIEURE, pour les colonnes B et G, ça ne donne pas les valeurs les plus proches de la ligne 6.
3C) Numéro de colonne, uniquement pour retrouver les colonnes plus facilement, pour éviter de les compter.
Il se passe des choses, mais pas tout à fait comme je le souhaite.
S'il n'y a pas de surface, mais un volume, je veux rechercher le volume SUPÉRIEURE le plus proche, et récupéré la surface corresponde, cela me permets d'avoir une approximation plus juste que la moyenne des surfaces.
J'espère est exhaustif et clair dans mes explications.
Oups, j'ai cliqué sur "Envoyer", et non "Aperçu".
J'espère que ma demande est claire.
Merci de prendre du temps pour m'aider.
Bonsoir,
là je suis perdu...
@ bientôt
LouReeD