Calcul d'un maximum sous condition
Bonjour tlm,
J'ai besoin d'aide svp, ça fait 2 jours que je suis bloqué sur une formule qui utilise MAX.
Voici la feuille1. Elle stock les données du cours du GBPUSD depuis 2008 sur du 1h (par ligne).
Voici la feuille2 qui donne un sens aux valeurs de la feuille 1. Comme vous pouvez le voir il y a des colonnes en vert et en rouge. Celles en rouge ne fonctionnent pas. Cependant, si j'arrive à faire marcher la formule de la colonne E " High veille " je pourrai m'en servir pour faire les autres. C'est sur celle si qu'on va se concentrer.
Dans la colonne high veille, il faut que par exemple pour la ligne 3 ci-dessus, le 05/12/2008, ça doit me renvoyer la valeur maximum de la feuille1 entre le 03/12/2008 17:00:00 et le 04/12/2008 16:00:00. Cependant, pour le lundi, ça doit me renvoyer la valeur max entre jeudi 17:00:00 et vendredi 16:00:00.
Comme vous pouvez le voir sur le screen, il y a déjà des valeurs sur la colonne E. Voici la formule que j'ai mis sur cette colonne : =MAX(SI((Feuille1!$A$2:$A$100000 >= SI(JOURSEM($A3;2)=1; $A3-4 + TEMPS(17;0;0); $A3-2 + TEMPS(17;0;0))) * (Feuille1!$A$2:$A$100000 <= SI(JOURSEM($A3;2)=1; $A3-3 + TEMPS(16;0;0); $A3-1 + TEMPS(16;0;0))); Feuille1!$E$2:$E$100000))
Elle me renvoie bien des données, mais certaines ne sont pas correctes. En effet, lorsque je vérifie manuellement si les données renvoyées sont bonnes, je vois qu'elles sont faussées lorsque le haut de la veille s'est fait entre 17:00:00 et 00:00:00, comme si ça ne prenait que la valeur max à partir de 00:00:00 la veille et non a partir de 17:00:00 avant veille (lorsque la ligne ne concerne pas un lundi (joursem=2 pour lundi ce qui est bizarre, bref)).
Je peux vous donner une formule fonctionnelle pour le high de la session de Londres du jour même mais ça ne fait pas référence à la veille ou avant-veille donc je ne sais pas si ça aidera : =MAX(SI((Feuille1!$A$2:$A$100000=Feuille2!$A2)*(Feuille1!$C$2:$C$100000>=TEMPS(3;0;0))*(Feuille1!$C$2:$C$100000<=TEMPS(7;0;0)); Feuille1!$E$2:$E$100000)).
J'espère que j'ai été assez clair dans mes explications, c'est pas forcément simple x) Si vous avez des questions n'hésitez pas.
Je vous remercie d'avance pour l'aider apportée ! :)
Antoine
Bonjour et bienvenue,
Pouvez-vous joindre un fichier contenant ces tableaux ? Merci.
En attendant, je pense aux fonctions MAX.SI.ENS (MAX.SI.ENS, fonction) - Support Microsoft et Fonction Excel : FILTRE. En filtrant votre tableau en prenant seulement les jours de semaine (donc sans les weekends), puis en prenant la valeur du jour et les 2 précédentes il me semble qu'on résout le problème simplement. Pour les heures je ne comprends pas pourquoi une fois on prend 17h et une fois 16h.
Bonjour,
Malheureusement mon fichier est trop lourd, il y a 100 000 lignes sur la feuille1. Et j'ai beau enlever des données il reste trop lourd ...
On prend 17h car c'est là ou la journée de trading commence sur la plateforme que j'utilise (Tradingview). Et 16h parce que la ligne prend de 16:00 à 16:59, la ligne suivante qui est 17:00 doit servir pour la journée suivante.
D'accord, merci pour l'explication (logique).
S'il vous plait j'insiste, si vous envoyez un fichier avec uniquement la feuille de calcul (A priori on n'a pas besoin de la feuille1) et une centaine de lignes il sera largement assez petit.
Les formules sont trop dépendantes de vos données pour que des screenshots soient suffisants, et personne n'a envie de s'amuser à recopier votre tableau pour tester les formules potentielles.
N'hésitez pas à mettre aussi les résultats des calculs manuels que vous avez effectués, qu'on puisse vérifier rapidement si la formule convient ou non. Merci beaucoup.
J'ai du enlever les formules car même une ligne prenait trop de place O_O
Les résultats de votre formule max dans la feuille 1 ont été fait à la main non ?
Parce que je n'ai pas trouvé de solution pour faire glisser une formule max de sorte que ligne 1 -> e4:e27 ligne 2 -> e28:e51 ligne 3 -> e52:e75 etc
Et désolé, Excel n'est pas vraiment ma spécialité
J'ai trouvé une formule qui fonctionne :
=MAX(
MAX.SI.ENS(
Feuille1!$E$2:$E$100000;
Feuille1!$A$2:$A$100000;
SI(JOURSEM(Feuille2!$A3;2)=1; Feuille2!$A3-4; Feuille2!$A3-2);
Feuille1!$C$2:$C$100000;
">="&TEMPS(17;0;0)
);
MAX.SI.ENS(
Feuille1!$E$2:$E$100000;
Feuille1!$A$2:$A$100000;
SI(JOURSEM(Feuille2!$A3;2)=1; Feuille2!$A3-3; Feuille2!$A3-1);
Feuille1!$C$2:$C$100000;
"<="&TEMPS(16;0;0)
)
)
Merci beaucoup pour l'aide :)
Bonne soirée
ah non ça ne fonctionne pas partout... Bon ...
https://excel-pratique.com/fr/astuces/tableau-structure
vos données sont 2 plages ordinaires, je les ai transformé en tableaux structurés, (voir lien ici dessus), cela permet d'écrire des formules plus structurées qui s'adaptent sans intervention. Tabel1 est le nom du tableau structuré (TS) de la page "Feuille1". Là, j'ai ajouté une MFC pour coloriser les plages de 17:00 à 16:00 en alternance noir et vert et oui, la première ligne de chaque periode, j'ai ajouté une formule à main dans les colonnes I et J, et à mon avis, ce sont les résultat que vous voulez obtenir. Sinon, il faut le dire maintenant !!!
pour être plus pratique la colonne K vous donne l'heure récalculé (j'ai ajouté la date + l'heure "New York" - 17 heures (c'est beaucoup d'heures, je sais, mais je ne sais pas votre domicile) et maintenant toutes les cellules d'une couleur sont dans la même journée.
Maintenant oubien je pouvais utiliser cette colonne K (je ne l'avais pas fait hier) oubien on utilise la date et l'heure et un décalage de -17 ou de +7 heures et on y ajoute un autre décalage d'un jour (=24 heures) pour la veille, c'est comme çà que j'arrive à un décalage de 31 heures (voir ce 31/24 dans la formule ici dessous)
Vous avez excel365, donc on peut utiliser la fonction filter
FILTRE(
Tabel1[High];........ premier paramètre = on veut filtrer la colonne "High"
TRONQUE(Tabel1[Date]+Tabel1[Heure New York]+31/24+2*(JOURSEM([@Date];2)=1))=[@Date] ....... 2eme paramtre = à condition que l'integer da la somme de [la date + l'heure New York + 31 heures + si on est un lundi 2 jours supplémentaire] est egale à notre date
)
Cette formule vous donnera oubien 24 données oubien rien
Puis on utilise le max pour récupérer la valeur maximale et en ajoutant Sierreur on traite les erreurs
donc, je n'utilise pas les données des colonnes I et J dans ma formule=SIERREUR(MAX(FILTRE(Tabel1[High];TRONQUE(Tabel1[Date]+Tabel1[Heure New York]+31/24+2*(JOURSEM([@Date];2)=1))=[@Date]));"?")Bonjour, merci beaucoup pour le temps que vous m'accordez.
Ce que vous avez fait m'a beaucoup aidé mais il reste un soucis.
Lorsque j'étends le tableau structuré que vous avez mis en place, uniquement certaines lignes "bug". Je vous montre sur le screen si dessous un exemple de cas ou ça fonctionne et un autre ou ça ne fonctionne pas :
Donc la c'est bon, ça prend bien l'avant veille du 15/01/2009 en commençant le 13/01 à 17:00 et finissant le 14/01 à 16:00.
Et là ça ne prend pas le bon minuit. Il semblerait que ce soit lié au changement d'heure qu'ont les américains (comme en France) début mars à fin octobre si je dis pas de bêtise. Donc pendant toute cette période, ça donne comme le screen juste au dessus. Les deux colonnes avec les formules max fonctionnent cependant.
Et pour ce qui est de la formule de la 2e feuille, elle n'affiche pas la bonne valeur partout. Il faut savoir que pour la deuxième colonne, il me faut le minimum de la session, j'ai donc changé =SIERREUR(MAX(FILTRE(Tabel1[High];TRONQUE(Tabel1[Date]+Tabel1[Heure New York]+31/24+2*(JOURSEM([@Date];2)=1))=[@Date]));"?")
en =SIERREUR(MIN(FILTRE(Tabel1[Low];TRONQUE(Tabel1[Date]+Tabel1[Heure New York]+31/24+2*(JOURSEM([@Date];2)=1))=[@Date]));"?").
Mais du coup si le high ou le low se fait à minuit de l'avant veille, ça foire la valeur. En fait ça prend le minuit d'un jour trop tôt.
Voici ce que j'ai sur la ligne du 05/08/2009 feuille2, par exemple, 1.70045 est censé être le max et 1.67205 le min.
Cependant lorsque je vais voir dans le tableau j'ai ça :
On voit ici 1.68885 au lieu de 1.67205.
Donc en fait il m'a pris le max correctement mais pas le minimum parce que le 1.67205 qu'il affiche se trouve sur le minuit du 03/08 au lieu du 04/08 (comme sur le deuxième screen que j'ai envoyé tout en haut).
Je pense qu'il manque pas grand chose, je vais chercher de mon côté, j'actualiserai si je trouve une solution.
Merci encore du temps que vous me consacrez
Bonne journée,
Antoine
re,
oei, on utilise les dates américaines (mm/jj/aa) ou europiennes (jj/mm/aa) (extremement important) en colonne A, parce que 4/8/2009 est en août selon moi (=europien) et n'a rien a voir avec l'heure hiver . En plus en 2009 c'était dimanche 8/3/2009 à 2:00 le matin et dimanche 1/11/2009 à 3:00 le matin. Normallement le dimanche matin à cette heure la bourse NY n'est pas ouvert.
Il y a autre chose, mais je n'ose pas faire un pari. Vous pouvez m'envoyer ces lignes, disons à partir de la ligne 4.150 à 4.250 (seulement les colonnes A:F)
Vous pouvez déjà essayer à utiliser le format numérique [hh]:mm:ss sur la colonne C pour voir s'il n'y a pas des anomalies là. (les heures sont entre 0 et 23 mais jamais >=24). Oubien vous utilisez la fleche en C1 (heure NY) et là vous n'avez que 24 valeurs de "0:00:00" & "23:00:00".
Re,
La colonne A utilise les dates européennes (jj/mm/aaaa).
Les lignes buguées sont pendant l'heure d'été américaine (le bug commence le 7/8/9 mars et s'arrête le 1-8 novembre en fonction des années) donc normal que la colonne d'août soit buguée. D'ailleurs c'est bizarre parce que sur votre screen, par exemple pour le 13/03/2016, c'est censé être à ce moment que se fait le changement d'heure mais le tableau bug à partir du 08/03/2016
Pour ce qui est des dimanche, aucune idée, je n'arrive pas à comprendre en quoi ce changement d'heure au milieu de la nuit fait merder le tableau... Mais ce qui est sûr c'est que ça arrive bien à chaque fois à ces moments là de l'année.
Je vous link le document de la ligne 4150 à 4250.
Je viens de changer le format numérique qui était simplement "Heure", j'ai essayé [h]:mm:ss, ça a juste changé les 00:00 en 24:00 mais n'a pas résolu le problème. J'ai essayé les autres formats dans le ruban personnalisé mais je n'ai rien trouvé de concluant.
C'est dommage que je ne puisse pas vous envoyer le document en entier ça aurait été plus simple :'(
Bonne soirée à vous, merci encore
J'ai remarqué après vous avoir envoyé le précédent message que pour la ligne du 10/03/2009, la formule "moment réel" se met elle aussi à disfonctionner car elle est censée renvoyer lun 09/03/09 7:00 et pas 10/03/09. Ca peut peut-être vous aiguiller sur la source du problème
re,
ces valeurs en colonne C sont des heures et 0<=colonne C <1 (certainement pas <=)
mais apparament les valeurs 0:00:00 (oubien 0) sont remplacées par 1/01/1900 0:00 = [24]:00 = 1 et c'est pourquoi vous avez remarqué qu'on ajoute un jour en plus à minuit. Bon, pour résoudre ce problème, vous voyez ici dessous que j'ai ajouté mod(... ; 1) (en rouge ici dessous) autour des valeurs de la colonne C.
Mieux comme çà ?
formule MFC =MOD(TRONQUE($A1+MOD($C1;1)+31/24);2)=0
formule High =SIERREUR(MAX(FILTRE(Tabel1[High];TRONQUE(Tabel1[Date]+MOD(Tabel1[Heure New York];1)+31/24+2*(JOURSEM([@Date];2)=1))=[@Date]));"?")
et la même chose pour la formule low
et si vous conservez la colonne K = Moment NY, aussi la même chose
PS. ce serait mieux de trouver la cause de cette erreur au lieu de modifier la formule pour contourner le problème
Super ça marche ! Dernière chose, est-ce que vous sauriez faire pareil mais pour la session asiatique de la veille qui est de 18:00:00 à 2:00:00 de la journée en cours inclus ? Je comprends pas forcément tout la formule que j'ai rentré
Merci encore, si vous voulez je peux vous faire un petit virement Paypal pour le temps que vous m'avez consacré, envoyez-moi votre adresse mail par mp que je puisse vous faire un paiement.
Bonne soirée,
Antoine
re,
NY était 24 heures pour la veille + 7 heures pour la différence de fuseau horaire >>> (24+7) = +31/7 dans la formule
alors je suppose que l'asia sera de nouveau 24 heures pour la veille et -18 heures par la différence de fuseau horaire >>>> (24-18) = +6/24 ou +.25 dans la formule
PS. L'aide est benevole (donc gratuit, nous sommes "ChatGPT-Humain").
PS2 ces 18 heures me semblent beaucoup, je pense plutôt 7-8 heures pour Hong Kong
re,
Très bien merci beaucoup en tout cas
=SIERREUR(MAX(FILTRE(Tableau1[High];TRONQUE(Tableau1[Date]+MOD(Tableau1[Heure New York];1)+31/24+0,25)=[@Date]));"?")
La formule ne prend pas juste les 8 heures de la session ? Ou j'ai mal compris quelque chose.