Formule pour éviter une usine à gaz
Bonjour,
J'ai repris dans un tableur 3 formules trouvées ici... dont 2 fonctionnent très bien mais pas le 3ème qui devrait pouvoir être plus simple.... c'est l'apéritif
En plat de résistance, j'aimerais pouvoir copier ces 3 formules où je veux... mais seulement la colonne C qui est bonne pour une recherche unique doit être adaptée et la modifier manuellement à chaque fois est très lourd et source d'erreurs
Est- il est possible de trouver une parade via formule ? ou autre méthode ?
En dessert, voir si c'est possible d'avoir une mise en forme conditionnelle automatique à chaque fois que je vais copier les formules.
Le menu est joint sous forme d'un tableur.... j'ai fait le maximum pour être clair !
Cordialement
Bonsoir,
Première réponse : la formule qui ne marche pas :
lorsque l'on crée une zone dynamique, avec DECALER, il ne faut pas de cellules vides !
vous écrivez :
Zstop=DECALER('stop max'!$C$1;1;;NBVAL('stop max'!$C:$C)-1)
ce qui veut dire :
DECALER('stop max'!$C$1 =>on décale à partir de la cellule $C$1 (ça c'est bon)
;1 => 1 cellule vers le bas, on arrive en $C$2 (ça c'est bon)
;0 => 0 colonne vers la droite car positif (évidemment on peut ne pas mettre 0 et laisser vide, ça c'est bon)
;NBVAL('stop max'!$C:$C) => on compte le nombre de valeur dans la colonne pour connaître la longueur de la zone (ça c'est bon)
-1 =>on retire l'équivalent de la valeur de l'entête de la colonne (ça c'est bon)
Le soucis est que votre colonne comporte des lignes vides !
Du coup lorsque vous faites "Formules" - "gestionnaire de noms" que vous sélectionnez la liste Zstop et que vous cliquez sur la formule, regardez la zone "créée" ! elle ne prend pas en compte toutes les valeurs du au fait de la zone sans valeur...
@ bientôt
LouReeD
Re
En plus de "l'erreur" de zone, il y a l'erreur de colonne !
=INDIRECT("B"&MIN(SI((DECALER(ZStop;EQUIV(G10;ZDate;0);-1)<=DECALER(ZStop;EQUIV(G10;ZDate;0)-1;0));LIGNE(DECALER(ZStop;EQUIV(G10;ZDate;0)-1;0)))))
La fonction INDIRECT permet de mettre en variable les coordonnées d'une cellule, hors vous voulez une cellule en colonne C mais il y a d'inscrit B, donc correction à faire :
=INDIRECT("C"&MIN(SI((DECALER(ZStop;EQUIV(G10;ZDate;0);-1)<=DECALER(ZStop;EQUIV(G10;ZDate;0)-1;0));LIGNE(DECALER(ZStop;EQUIV(G10;ZDate;0)-1;0)))))
De là les trois formules fonctionnent, réfléchissons au reste
@ bientôt
LouReeD
Un test....
Bonjour,
Pour une bonne compréhension, j'ai repris l'exposé de mes demandes dans un onglet en supprimant l'accessoire.
Sauf erreur de ma part, la première partie était solutionnée par les formules trouvées précédemment car il s'agit exactement de la même recherche décalée.
Le plat de résistance est la partie B......
Si tout n'est pas parfaitement clair, reviens vers moi et je remercie d'avance de ton aide
Cordialement
RE,
Je continue à chercher de mon côté, j'ai rajouté un onglet piste.....qui ne peut pas être plus simple pour avoir la première recherche (parce que je sais le faire !
par contre, pour une seconde recherche et les suivantes....
Cordialement
RE
Je vous joins un nouveau tableur cf 1er onglet.
En effet, en décalant d'une cellule la colonne "max"... tout devient plus simple !
Il reste que l'actualisation de la nouvelle zone pour les recherches suivantes....
Cordialement
RE
La première recherche ne pose pas de souci particulier.
Lancer une seconde recherche, voire une troisième ou plus avec le même tableur semble impossible car il faut changer les zones de références de la colonne C "cours stop le plus haut" à chaque recherche.
Compte tenu, qu'il n'y a pas le même écart entre chaque recherche (ce n'est pas par exemple tous les 25 lignes) cela me semble impossible à automatiser via des formules
Je vais voir si je peux contourner le problème via des macros et en attendant je pense qu'il est préférable de clore ce post.
Merci aux intervenants, leurs remarques m'ont été très utiles.
Cordialement
Bonsoir,
moi de mon coté, la question que je me pose et la suivante :
votre formule est
MAX($B$10:$B20)*$D$1
où le "10" correspond à la ligne de la date de début de recherche et le "20" correspond à la ligne où se trouve la formule, et la formule est copiée jusqu'à la dernière valeur de ligne de la colonne A, B et C.
Est-ce que jusque là j'ai bon ?
Si oui alors la formule qui se trouve sur le fichier joint, sur l'onglet "Explications", en cellule Q42 "à l'air de fonctionner"
En effet je décortique la zone de recherche du plus haut Cours stop avec une ligne de départ et une ligne d'arrivée, le tout validé avec [Shift]+[ctrl]+[entrée] et oui c'est matriciel !!!
La ligne de départ est le numéro de ligne excel où on trouve la date de départ de recherche.
La ligne d'arrivée est la ligne excel de la dernière valeur inscrite de la colonne B c'est à dire NBVAL(C:C)
=MAX(INDIRECT("$B$"&Q40&":B"&Q41)*$D$1)
avec la fonction INDIRECT on a bien les cellules de départ et d'arrivée en variable et dans notre exemple :
MAX($B$10:$B20)*$D$1
on a bien
MAX(INDIRECT("$B$"&Q40qui est égal à 10&":B"&Q41 qui est égal à 1 jusqu'à NBVAL(C:C))*$D$1)
Q41 qui est égal à 1 jusqu'à NBVAL(C:C) : Avec la validation en formule matricielle c'est un peu comme si on créée le colonne en 3D dans la cellule...
En tout cas le fichier joint avec la date du 30/04/2013 (premier exemple) me donne 1078.53 comme demandé
et avec l'exemple 2 à la date du 07/06/2013 me donne bien 1072.33 qui est lui aussi demandé !
Alors oui visuellement la colonne C affiche sur ce dernier cas des erreurs, mais les valeurs de la colonne matricielle en 3D de la cellule Q42 corrige les erreurs car elle modifie sa cellule de départ en fonction de la date sélectionnée !
Ceci dit il faut laisser les formules tel quelle en colonne C
@ bientôt
LouReeD
Nota MFC sur la colonne A en fonction de la date en $G$34
Bonjour et bravo pour ta persévérance.
Il est essentiel de bien se comprendre, j'ai repris ton post dans le tableur joint.
Pour la première question la réponse est OUI
Ceci dit, je pense avoir trouvé une simplification que j'expose dans le tableur.
Je pense que l'on est tout proche du résultat pour une première recherche....
Quant à relancer une nouvelle recherche, je me demande si cela est bien possible, je crois avoir identifier l'élément à modifier ..... (le 10 par la ligne de la date de la nouvelle recherche)
Cordialement
Bonjour
Voilà l'ultime explication de ma formule qui a mon sens fonctionne...
Le principe est que vous avez une colonne C avec une formule Max qui débute en ligne 10 puis recopiée jusqu'en bas.
On y touche pas. La formule matricielle en a besoin.
A coté EN PLUS vous avez trois cellules qui servent à trouver votre valeur MAX "corrigée" en fonction de la date de recherche demandée. Cette formule n'a pas vocation à remplacer celles de la colonnes C, c'est une cellule en plus pour vos recherches !
En somme votre case "Haut valeur stopée" est indépendante de ce qui s'affiche en colonne C !
Maintenant pour moi ça fonctionne... J'obtiens les résultats escomptés.
@ bientôt
LouReeD
Nota : les explications sont en fond jaune !
Re.
Effectivement cela marche....c'est très troublant pour moi d'avoir la seconde recherche "exacte" avec une colonne C contenant des formules initiales "pas bonnes".
Bravo, je tacherai de décortiquer cela un peu plus tard.
Ultime demande, pourriez-vous mettre les formules dans mes cases ....
Jamais j'aurai cru que cela aurait été possible, sincèrement
Cordialement
Bonjour,
fichier joint
Feuil1
Ligne une avec toutes les valeurs cherchées dont une pas bonne.
Mais c'est pas grave, celle à droite est bonne...
Bonne fin de weekend
LouReeD
Bonjour,
J'ai mis les valeurs recherchées sur une ligne et laissé les titres en haut de colonnes, cela sera plus pratique pour lancer une nouvelle recherche.
Cela va être super
Pourquoi j'écris ?
Il y a un décalage d'un jour dans l'affichage de la journée "cours".... je ne préfère pas y toucher mais si tu pouvais revoir.
Curieusement le cours stop affiché lors de la première recherche n'est pas celui que l'on trouve dans la colonne C, cela m'intrigue et crée un doute pour les recherches suivantes.
Cordialement et excellente journée
Bonjour,
Je suis toujours étonné avec tout ce que l'on peut faire sous Excel et là sous VBA.
Peut-être l'avantage du VBA est qu'il supprime le risque de voir une formule effacée, modifiée.
Ceci dit, c'est super pour une recherche
C'est possible ?
Cordialement
Si je t'ai bien compris, il s'agit de permettre plusieurs recherches
A+
RE
C'est exactement ce que je souhaitais : pouvoir faire des recherches à n'importe quelle date.
J'ai fait différents essais pratiques... et il s'avère qu'il me manque
J'ai fait également une vérification "manuelle" du cours stop le plus haut sur une recherche... curieusement le premier est bon ainsi que les 4 derniers, je crains qu'il y a quelque chose qui ne va pas....
Tout est repris dans le tableur joint
Cordialement
Bonjour,
Une adaptation de l'adaptation
Le lancement de la procédure se fait lors d'un double-clic sur la date de départ de la recherche
A+
Bonsoir,
Nous sommes complètement d'accord sur le stop le plus haut à retenir.... tu as parfaitement saisi.
Du coup, j'ai fait des essais pratiques qui amènent quelques remarques concrètes.....
Comme je risque d'utiliser ce tableur très souvent, autant qu'il soit le plus ergonomique possible
Excellente soirée
Tonthon
Dans cette nouvelle nouvelle nouvelle version, la colonne "Date de départ de la recherche" reste vide.
La date est inscrite automatiquement au moment du double-clic dans la cellule.
En cas d'erreur, cette cellule date peut être effacée. Cette action va provoquer l'effacement de toutes les indications associées.
A+