Effectuer une recherche en fonction de différentes dates

Bonjour à tous, je vais essayer de clarifier les choses depuis le début car j'aurais dû être plus explicite sur mes demandes de départ

Le principe de base est de moduler les tarifs suivants les pics de fréquentation

Jai deux types de calculs possibles: nuit et semaine. Le calcul à la nuit ne peut se faire qu'en dehors du mois de juillet et du mois d'aout (avec un petit débordement fin aout, début septembre)

Si j'essaye de faire un calcul nuit durant cette période, celui ci est impossible et seul le calcul semaine est disponible.

Si je fais une demande de ce type soit je dois avoir une erreur ou bien pas de réponse ce qui m'indique que je dois faire une ventilation semaine.

Le mode semaine est à privilégier dans tous les cas.

La méthode de Mikhail est celle qui répond le mieux à mes attentes dans cette problématique car j'ai un détail au jour le jour semaine par semaine

j'ai corrigé les dates de tous les onglets sur les semaines juillet et aout

je ne peux pas faire démarrer toutes les semaines le samedi hors juillet et aout car en fait ce sont des mois entiers, néanmoins encore une fois et pour les périodes longues le mode semaine est à privilégier (d'ailleurs le tarif est meilleur à la semaine)

Merci de votre patience, j'ai conscience que la tache est ardue

j'ai corrigé les dates de tous les onglets sur les semaines juillet et aout

je ne peux pas faire démarrer toutes les semaines le samedi hors juillet et aout car en fait ce sont des mois entiers, néanmoins encore une fois et pour les périodes longues le mode semaine est à privilégier (d'ailleurs le tarif est meilleur à la semaine)

ok, on peut reprendre le calcul ... mais il faut clarifier les choses :

  • est-ce que une semaine c'est une suite de 7 jours qui commence n'importe quel jour ?
  • ou est-ce que la semaine est celle qui commence un samedi et dans ce cas que fait-on du 26 juin et du 28 août qui engagent des semaines à cheval ?

en revoyant ta copie, je comprends in fine

  • que les semaines comment bien le samedi
  • que celle qui commence le 26 juin est à considérer en juin

ok, je reprendrai dans la journée les formules

  • là où cela reste ambigüe pour moi c'est que tu notes à partir du 1er sept 285€ mais aussi du 21 août jusqu'au 4 sept à 355€ et du coup la semaine du 28 août elle doit-être comptée à combien ?

j'ai essayé de synthétiser au maximum, dis moi s'il y a encore des coquilles stp

merci

J'ai mis à jour les formules :

NUITS

total

=SUMPRODUCT(($D$5:$D18)*($A$5:$A18=$H$5)*($B$5:$B18<=(J5+sequence(1;K5-J5;0;1)))*($C$5:$C18>=(J5+sequence(1;K5-J5;0;1))))

détail

={ARRAYFORMULA(J5+sequence(1;K5-J5;0;1));transpose(MMULT(transpose(arrayformula(($D$5:$D18)*($A$5:$A18=$H$5)*($B$5:$B18<=(J5+sequence(1;K5-J5;0;1)))*($C$5:$C18>=(J5+sequence(1;K5-J5;0;1)))));sequence(ROWS($A$5:$A$18);1;1;0)))}

SEMAINES

total

=sum(arrayformula(VLOOKUP(J8+sequence(1;(K8-J8)/7;0;7);sort(query($A$4:$D$20;"select B,C,D where A='"&H8&"' ";0));3;1)))

détail

={arrayformula(J8+sequence(1;(K8-J8)/7;0;7));arrayformula(VLOOKUP(J8+sequence(1;(K8-J8)/7;0;7);sort(query($A$4:$D$20;"select B,C,D where A='"&H8&"' ";0));3;1))}

Bravo ! on y est presque

En effet dans le calcul jour, on devrait avoir une cellule vide ou 0 du fait que sur la période prise en compte il y a juillet et donc impossibilité d'un résultat

Penses tu pouvoir intégrer cette condition si 0 dans le résultat du filter alors 0 ?

Ca obligera la personne à prendre en compte uniquement les jours hors juillet et aout

Dans mon esprit, je ne calculait que les jours dits "autorisés" et je mettais les autres à zéro.

image

Si je comprends ta demande ... dès lors qu'un jour est hors période, on colle tout à zéro , est-ce bien cela ?

J'ai ajouté

  • en N6 de ma feuille un indicateur ok / not ok (si un jour tombe dans une plage non répertoriée en Nuitée)
  • en N5 un "TOTAL2" qui indique 0€ si un jour tombe dans une plage non répertoriée en Nuitée

Félicitations, c'est exactement ce que je cherchais

Pourrais tu à l'occasion me détailler les formules car je ne saisi pas tout, le maniement des dates m'est étranger

Dernier "bonus" penses tu qu'on pourrait tout écrire sur une seule ligne sans les transpose, car je dois économiser des lignes sinon je serai obligé de dédoubler tous les calculs ou créer un onglet supplémentaire ?

image

encore merci de ton aide et de tout le temps que tu as bien voulu m'accorder

Oui, on peut tout écrire sur un seule ligne, voir ma proposition

Sinon, oui, ok pour détailler les formules sur cette page

Pour la formule Semaines,

=sum(arrayformula(VLOOKUP(sequence(1;(K8-J8)/7;J8;7);sort(query($A$4:$D;"select B,C,D where A='"&H8&"' ";0));3;1)))

Il y a 2 grandes parties + l'assemblage du tout :

1-

sort(query($A$4:$D;"select B,C,D where A='"&H8&"' ";0))

va extraire les dates de début en B, de fin en C et le prix en D de la base de données lorsque le bien en A correspond, et assurer le classement par date si tant est que ce n'était pas déjà fait

2-

sequence(1;(K8-J8)/7;J8;7)

va créer une suite de dates de 7 jours en 7 jours à compter de la date J8 ... dans

sequence

il y a
> 1 qui est le nombre de lignes,
> (K8-J8)/7 le nombre de colonnes,
> J8 la première valeur,
> 7 l'incrément

3-

VLOOKUP(________les dates__________;_____la nouvelle base issue de query __________;3;1)

va rechercher la date la plus proche et juste inférieure ... on ajoute arrayformula pour propager la formule pour toutes les dates et sum pour additionner

Nota : ma notation est anglaise, si tu lis la formule dans ton fichier il est probable que tu y trouves la traduction en français.

Pour la calcul Nuits, le total est relativement simple, a contrario le calcul détaillé fait appel à un calcul matriciel assez complexe qu'on pourra détailler plus tard.

=SUMPRODUCT(($D$5:$D)*($A$5:$A=$H$5)*($B$5:$B<=(sequence(1;K5-J5;J5;1)))*($C$5:$C>=(sequence(1;K5-J5;J5;1))))

De la même manière que précédemment,

sequence(1;K5-J5;J5;1)

est une suite de dates à compter de J5 et jusque K5, l'incrément est ici de 1

Il s'agit ensuite d'un SOMMEPROD classique

SUMPRODUCT( prix * ( le bien est-il celui de H5 ) * ( début <= séquence de dates ) * ( fin >= séquence de dates ) )

Pour le détail du calcul Nuits ...

Je reprends la même logique que précédemment sans la SOMME

=arrayformula(($D$5:$D)*($A$5:$A=$H$5)*($B$5:$B<=(sequence(1;K5-J5;J5;1)))*($C$5:$C>=(sequence(1;K5-J5;J5;1))))

partie violette

capture d ecran 858

il s'agit donc de faire la somme de chaque colonne, ceci fait appel à un calcul matriciel avec MMULTdont le principe est rappelé ici

image

la matrice de droite en gris sur la copie d'écran sera une série de 1 avec la formule

=sequence(ROWS($A$5:$A);1;1;0)

comme le nombre de colonne de la matrice violette doit être égal au nombre de lignes de la matrice uni-colonne grise pour que la multiplication fonctionne, je vais inverser par transpose la matrice violette, et après MMULT, je vais de nouveau inverser le résultat

=transpose( MMULT( transpose(______matrice violette_____) ; ___matrice__grise__ ) )

Pour savoir si un jour est hors plage de validité, il suffit de savoir s'il y a au moins un zéro dans le résultat !

Bravo et merci de m'avoir aidé à comprendre tes formules

Tu devrais enseigner à ce niveau là !

Je suis ravi de ces échanges, je pense que nous serons amené à nous croiser au détours d'autres questions.

Si tu le souhaites je te partagerai la totalité de mon fichier pour la gestion d'un camping une fois terminé

Pour éviter les réécritures je fais un partage de la feuille en Edition seule

Rechercher des sujets similaires à "effectuer recherche fonction differentes dates"