formule ou VBA ?

[skun]

Bonjour,

J'aimerai faire une recherche assez poussé.
Mes critère sont nombreux & complex.

J'aimerai savoir si, des formules peuvent répondre à mes attentes:

Ceci a été édité:

La ligne recherchée dans la base de donnée doit répondre aux critères:
  *S doit correspondre à B12
  *X doit être égal à "K"
  *M doit être inférieur ou égale à A12 
  *Si, M= A12 alors (si la date est la meme que A12) , N doit être inférieur ou égale à E12 +1h 

Apartir des résultats, il faudrait qu'on garde la ligne ayant le M (date) le plus récent, et si il y a plusieurs lignes qui ont le meme M (date),alors on garde celui qui a le N  (heure) le plus récent. Si dans ce cas, il y a des N de moins de 1h de différence on les additionne.

voilà, avec l'exemple je pense que ma démande est plus claire.

J'opterai plus pour des formules (plus facil pour moi) mais, les heures E12 sont parfois proche de 24:00 ou 00:00, et si l'intervalle du critère: "N doit être compris (ou égal) entre [E12 - 2h ; E12 + 1h]" dépasse ce "cap" des 24h, j'ai peur que ca fausse la recherche car dans ce cas, il faudrait que ca se réfère à date de la veille

Voilà si quelqu'un à une idée sur mon problème, je vous remercie 🙂


salutations


skun

 

[wilfried_42]

Re : formule ou VBA ?

Bonjour

si j'ai tout compris, formule à mettre en I12

=SOMMEPROD(($M$3:$M$11=A12)*($X$3:$X$11="K")*($S$3:$S$11=B12)*($N$3:$N$11>=E12-("01:00"*1))*($N$3:$N$11<=E12+("02:00"*1))*$Z$3:$Z$11)

 

[skun]

Re : formule ou VBA ?

Bonjour wilfried_42,

Merci pour ta formule, elle répond exactement aux critères que j'ai énnoncé, cependant, en la testant je me suis rendu compte que je n'ai pas correctement énnoncé mon prob....

Enfete, je souhaite que:

La ligne recherchée dans la base de donnée doit répondre aux critères:
  *S doit correspondre à B12
  *X doit être égal à "K"
  *M doit [B]être inférieur ou égale à[/B] A12 
  *Si, M= A12 alors (si la date est la meme que A12) , N doit être inférieur ou égale à E12 +1h 

Apartir des résultats, il faudrait qu'on garde la ligne ayant le M (date) le plus récent, et si il y a plusieurs lignes qui ont le meme M (date),alors on garde celui qui a le N  (heure) le plus récent. Si dans ce cas, il y a des N de moins de 1h de différence on les additionne

Je vais traduire ca en francais:

En gros: il faut que ca cherche en fonction du camion (B12) la dernière fois qu'il a fait le plein (X="K") par rapport à la date (A12) et l'heure (B12)
Une fois que la formule a trouvé le dernier plein effectué, alors si il y a eu plusieurs pleins dans l'heure (le meme jour) précedent le dernier plein, alors on additionne les Litres (Z).


J'espère avoir réussi a expliquer mon énnoncé

encore désolés, je galère un peu a m'exprimer.


salutations


skun

 

[wilfried_42]

Re : formule ou VBA ?

re:

alors la ca change tout à tester, franchement je ne suis pas certain et j'en appelle à d'autres formulistes (bien meilleur que moi)

un essai tout de meme
formule matricielle à valider avec Ctrl + Maj + Entrée

=SOMMEPROD(($M$3:$M$11=MAX(SI($M$3:$M$11=A12;$M$3:$M$11;0)))*($X$3:$X$11="K")*($S$3:$S$11=B12)*($N$3:$N$11>=E12-("01:00"*1))*($N$3:$N$11<=E12+("02:00"*1))*$Z$3:$Z$11)+SOMMEPROD(($M$3:$M$11=MAX(SI($M$3:$M$11<A12;$M$3:$M$11;0)))*($X$3:$X$11="K")*($S$3:$S$11=B12)*($N$3:$N$11>=E12-("01:00"*1))*($N$3:$N$11<=E12+("02:00"*1))*$Z$3:$Z$11)

 

[skun]

Re : formule ou VBA ?

Re,

merci beaucoup de ta réponse!

alors j'ai testé, aparament (je dois encore faire des tests) ca marche pour si le plein a été fait le meme jour que A12 ou le lendemain, mais si il y a plus de 2 jours de différence ca ne marche pas on dirait...

je ne connais pas la cause, mais j'y travail.

Le but de la formule, est de trouver le dernier plein effectué pour un camion par rapport à l'heure et la date de la recherche. Une fois trouvé, si il y a des pleins qui ont été fait pour le meme camion, ce meme jour, avant les 60 min précedent le dernier plein alors, il faut additionner le nombre de litre.

si quelqu'un a une idée? 🙂

salutations


skun

 

[CISCO]

Re : formule ou VBA ?

Bonsoir à tous

Peut être que le fichier ci-joint répondra à ta demande. Cela semble fonctionner correctement. J'ai essayé tellement de formules que j'y perd un peu mon latin🙄. A toi d'adapter en plaçant dans la bonne cellule, avec des $ si besoin est... Si tu as des questions, ne te gênes pas...

Bonne fin de journée

 

[skun]

Re : formule ou VBA ?

Bonsoir CISCO,

Je te remercie, je ne savais pas que c'était possible avec une formule 😱
c'est énorme. Je vais passé ma soirée a tester et demain aussi pour comprendre les formules. Si je comprend pas , je remonte le fils !

encore merci pour le temps consacré !

salutations

skun

 

[skun]

Re : formule ou VBA ?

Bonjour le forum ,

Voilà , j'ai testé et retesté tes formules CISCO, ca marche super.
J'ai préférer opter pour les formules divisées en 2 étapes car c'est plus détaillé.
J'ai réussis à l'adapter à mon cas c'est super.

Cependant, je dois créer d'autres formules sur ce même principe.

C'est pourquoi j'aimerai en savoir un peu plus sur le fonctionnement de ces formules.

Tout d'abors

=MAX((S3:S11=B12)*((N3:N11+M3:M11)<=(A12+E12))*(M3:M11+N3:N11))

j'aimerai comprendre les "*" je ne comprend pas trop comment les utiliser et leurs réel utilité.
EDIT: Je crois que j'ai compris, ca veut dire "et" ?

et 2eme question, j'aimerai trouver l'inverse de cette formule: la plus petite valeur >= à A12+E12.
pourquoi ca ne marche pas en faisant:

=Min((S3:S11=B12)*((N3:N11+M3:M11)>=(A12+E12))*(M3:M11+N3:N11))

Voilà , je vous remercie pour vos lumières

salutations


skun

 

[minipuce]

Re : formule ou VBA ?

Bonjour Skun, le forum,

En effet les "*" veulent dire "et" et les "+" veulent dire "ou".
Pour l'autre question, je ne saurais te répondre ...
Salutations
Minipuce

 

[skun]

Re : formule ou VBA ?

Bonjour minipuce,

merci pour l'astuce 🙂

cependant, meme en comprennant ceci, je n'arrive pas à faire marché la formule "inverse" avec min.

si quelqu'un à une idée


salutations

slun

 

[CISCO]

Re : formule ou VBA ?

Bonjour

Pas évident d'expliquer les formules utilisées, surtout parcequ'elles font un calcul matriciel (d'ou les {}).

Je vais essayer de faire mon possible.

Sur Excel, le * signifie en réalité multiplié. Si tu écris =2 * 5, tu obtiens dans la cellule 10. Si tu cliques sur cette cellule, dans la barre de formule, tu as toujours = 2 * 5, mais dans la cellule tu as 10.

Dans la fomule =MAX((S3:S11=B12)*((N3:N11+M3:M11)<=(A12+E12))*(M3:M11+N3:N11)), c'est plus compliqué...

En fait, il faut plutot voir cette syntaxe comme une série de SI :

(S3:S11=B12) signifie "Comparer le contenu de chaque cellule Sx de S3:S11 à B12". Cette partie de la formule, seule, sans le *, donne FAUX ou VRAI. Mais lorsqu'il y a une multiplication, on obtient des 0 ou des 1 : Lorsque Sx<>B12, mettre 0, lorsque Sx=B12, mettre 1.

De même avec (N3:N11+M3:M11)<=(A12+E12). Lorsque Nx+Mx est inférieur ou égal à A12+E12, on obtient un 1, sinon, un zéro.

Comme il s'agit d'un calcul matriciel, on obtient finalement une série de multiplication, par exemple
{0*1*(N3+M3);0*0*(N4+M4);0*1*(N5+M5);1*1*(N6+M6);0*1*(N7+M7);1*1*(N8+M8)....} (exemple sans aucun rapport avec les valeurs de ton fichier),
donc {0;0;0;N6+M6;0;N8+M8....}, et finalement, grace à la fonction MAX, on garde le plus grand nombre de cette matrice.

Grace à cette petite explication, peut être as tu déja compris pourquoi la formule que tu proposes ne te donne pas le résultat espéré🙂.

Avec {=Min((S3:S11=B12)*((N3:N11+M3:M11)>=(A12+E12))*(M3:M11+N3:N11))}, tu obtiens le minimum de la série {0;0;....} qui est évidemment 0.

En pratique, il faut éliminer ces 0, ce que l'on peut faire avec
=MIN(SI((S3:S11=B12)*(M3:M11+N3:N11>=A12+E12)<>0;M3:M11+N3:N11)).

J'ai l'impression que ça fonctionne bien... A tester davantage.
Bonne journée à tous.

 

[skun]

Re : formule ou VBA ?

Bonjour CISCO,
je te remercie pour cette réponse trés complète ! je n'aurai pas révé mieu !

Là j'ai matière à travailler/réfléchir.
J'y vais de ce pas !

A priori j'ai compris lol ^^

je vais testé tout ca et m'entrainner.

A bientot !

salutations


skun