Discussion :

[Coucou17]

Bonjour à tous,

Je n'ai pas su trouver la réponse à mon problème dans le forum. Aussi, je l'expose ici.


Dans la feuille ci-dessus, en colonne V, je voudrais faire le calcul de ce que coûtera un séjour dans un logement saisonnier.

Pour ce faire, j'utilise les données situées dans les colonnes S (date d'entrée) et T (date de sortie). Vous noterez en colonne U que le total est bien de 14 car même si les dates couvrent 3 samedis, le dernier n'est pas considéré puisque le client ne passera pas la nuit.

Ensuite, le tarif se situe dans une autre feuille (ci-dessous). La matrice complète est de A1;W399.



En fonction de la date (col A), il y a un tarif hebdomadaire de référence (col B). Ce tarif est ensuite divisé (ligne 2) en fonction de la durée du séjour (ligne 1).

Dans notre exemple, le client restera 14 jours (col P) du 31/12/16 (ligne 368) jusqu'au 14/01/17 (ligne 382) moins 1, puisqu'on ne compte pas la dernière nuit.

La plage à totaliser est donc de P368;P382-1, soit un total 807,52 €.



Ma question est donc la suivante : quelle formule dois-je utiliser pour obtenir ce résultat ?

J'ai voulu commencer à utiliser RECHERCHEV, mais je ne suis pas parvenu à faire une somme.

Puis j'ai découvert SOMME.SI.ENS. Mais j'ai beaucoup de mal à appréhender cette fonction pour délimiter la plage à additionner. Mais peut-être n'est-ce pas la bonne méthode.

Quelle serait donc la bonne méthode ?

Merci de votre aide.

[78chris]

Bonjour

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

=SOMME(DECALER(Tarifs!A2;EQUIV(S2;Tarifs!A:A;0)-2;U2+1;U2;1))

Avec ta date et ta durée en ligne 2 dans cet exemple et le nom de la feuille Tarifs à adapter

[Coucou17]

Bonjour à tous,
Bonjour 78Chris,

Wouah ! Super !

Ça marche parfaitement sur la ligne 13. J'ai réajusté les numéros de ligne et nom de feuille. Ça donne ça :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

=SI(ESTVIDE(B13);"";SOMME(DECALER('Tarif et calendrier'!A2;EQUIV(S13;'Tarif et calendrier'!A:A;0)-2;U13+1;U13;1)))

Un petit souci, à quoi correspond la référence cellule A2 dans cet extrait de formule DECALER('Tarif et calendrier'!A2; ?
Faut-il le considérer en valeur absolue ou le laisser en valeur relative ?
Telle qu'indiquée, cette instruction me retourne la valeur Date.
Le problème est que quand je copie la formule de la ligne 13 à la ligne 2, j'obtiens #REF! dès la ligne 11.
En faisant l'essai avec une valeur absolue $A$2, ça fonctionne... même si je ne comprends pas à quoi sert cette instruction.


Il manque maintenant deux contraintes que je n'avais pas précisées dans mon premier post.

La matrice de référence (Tarif et calendrier) s'arrête à 21, c'est à dire qu'au-delà de 21 jours, c'est le tarif 21 jours qui s'applique. Comme je n'ai pas idée de la limite de durée qu'un client pourrait sélectionner, je n'ai pas étendu le tableau à l'infini.
Comment donc indiquer que si la cellule U13 est >=21 on reste sur la colonne 21 (Tarif et calendrier!W:W) ?

Dans le même esprit, le séjour minimum est de 3 jours.
Comment donc indiquer que si la cellule U13 est <=3 on reste sur la colonne 3 (Tarif et calendrier!E:E) ?

Encore merci pour votre aide ou contribution.

[Menhir]

Remplace U13 par MIN(3;MAX(21;U13)), tout simplement.

[Coucou17]

Bonjour Menhir,

Merci pour ton aide, mais je dois être un boulet...

Ma formule actuelle (et qui fonctionne partiellement) est :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

=SI(ESTVIDE(B13);"";SOMME(DECALER('Tarif et calendrier'!$A$2;EQUIV(S13;'Tarif et calendrier'!A:A;0)-2;U13+1;U13;1)))

Elle me renvoie dans l'exemple initial la valeur 807,50, ce qui est juste.



Si je remplace le premier U13 par ta formule,

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

=SI(ESTVIDE(B13);"";SOMME(DECALER('Tarif et calendrier'!$A$2;EQUIV(S13;'Tarif et calendrier'!A:A;0)-2;MIN(3;MAX(21;U13))+1;U13;1)))

j'obtiens le résultat de 1190, qui est faux.



Si je remplace le second U13 par ta formule,

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

=SI(ESTVIDE(B13);"";SOMME(DECALER('Tarif et calendrier'!$A$2;EQUIV(S13;'Tarif et calendrier'!A:A;0)-2;U13+1;MIN(3;MAX(21;U13));1)))

j'obtiens le résultat de 183,21, qui est faux aussi.



Si je remplace les deux U13 par ta formule,

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

=SI(ESTVIDE(B13);"";SOMME(DECALER('Tarif et calendrier'!$A$2;EQUIV(S13;'Tarif et calendrier'!A:A;0)-2;MIN(3;MAX(21;U13))+1;MIN(3;MAX(21;U13));1)))

j'obtiens le résultat de 270, qui est aussi faux.



Enfin, si je remplace le premier U13 mais lui adjoint le +1 aussitôt

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

=SI(ESTVIDE(B13);"";SOMME(DECALER('Tarif et calendrier'!$A$2;EQUIV(S13;'Tarif et calendrier'!A:A;0)-2;MIN(3;MAX(21;U13+1));U13;1)))

j'obtiens le résultat de 1190 qui est toujours faux.




Donc où ou comment dois-je insérer cette formule ?

[Menhir]

Dans ma formule, j'ai inversé MIN et MAX.

[Coucou17]

Salut Menhir,

Merci pour le correctif qui fonctionne... presque.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

=SI(ESTVIDE(B13);"";SOMME(DECALER('Tarif et calendrier'!$A$2;EQUIV(S13;'Tarif et calendrier'!A:A;0)-2;MAX(3;MIN(21;U13))+1;U13;1)))

Si je change les dates de réservation pour aller au-delà de 21 jours, soit par exemple du 31 décembre au 30 janvier (30 jours), j'obtiens le résultat de 1617,26, qui est juste.

En revanche, si je réduis le séjour à 1 ou 2 jours, U13=1 ou 2, (du 31 décembre au 1er ou 2 janvier) j'obtiens respectivement 90 et 180 (qui sont mathématiquement justes) au lieu de 270.

Je crois qu'on touche à la fin de l'exercice.

[Menhir]

Si les deux sont justes, je ne vois pas où est le problème.

Je crois qu'on touche à la fin de l'exercice.

Je suis sûr que si ta participation était plus conséquente, il serait déjà terminé.

[Coucou17]

Je suis sûr que si ta participation était plus conséquente, il serait déjà terminé.

Ce n'est pas de la mauvaise volonté mais je ne comprends pas la logique des fonctions utilisées. Même quand je les détaille pas à pas en utilisant F9, je ne comprends pas comment elles fonctionnent. Merci quand même pour la leçon de morale.

Quant aux résultats justes, ce ne sont pas ceux que j'attends.

Dans le même esprit, le séjour minimum est de 3 jours.
Comment donc indiquer que si la cellule U13 est <=3 on reste sur la colonne 3 (Tarif et calendrier!E:E) ?

En revanche, si je réduis le séjour à 1 ou 2 jours, U13=1 ou 2, (du 31 décembre au 1er ou 2 janvier) j'obtiens respectivement 90 et 180 (qui sont mathématiquement justes) au lieu de 270.

Autrement dit, quelque soit la valeur de U13 à 1, 2 ou 3, on doit obtenir le résultat de 3. Quand on dépasse 21, on reste bien sur la colonne de 21. Quand on est inférieur à 3, comment rester sur la colonne de 3 ?

[78chris]

Bonjour

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

MAX(3;MIN(21;U13))

donne 3 que la valeur de U13 soit 1, 2 ou 3, la valeur de U3 jusqu'à 21 inclus et 21 au delà : cela prend donc la colonne 3 à 21 pour le 1er U13

Cette même partie de formule donnerait 3 nuits pour 1, 2 ou 3, le nombre de nuits pour 4 à 21 inclus mais ne doit pas rester à 21 au delà.

Pour le second U13 :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

MAX(3;U13)

devrait te donner le bon résultat.

Formule complète

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

=SI(ESTVIDE(B13);"";SOMME(DECALER('Tarif et calendrier'!$A$2;EQUIV(S13;'Tarif et calendrier'!A:A;0)-2;MAX(3;MIN(21;U13))+1;MAX(3;U13);1)))

[Coucou17]

Bonjour 78Chris,

Merci pour la touche finale.

C'est un peu plus clair, même si je ne comprends pas vraiment la logique de l'imbrication de toutes ces fonctions que je ne connais déjà pas seules.

Ça fonctionne parfaitement.

Et re-merci à Menhir qui a bien contribué.

[78chris]

Bonjour

L'objectif est de faire la SOMME d'une plage dont on ignore la position.

C'est comme un jeu de piste : on a un point de départ et des indices

Le point de départ : la cellule A2 de la feuille tarif
Les indices pour trouver où commence la plage à additionner :

• il faut descendre sous A2 jusqu'à la date de départ : il faut donc la trouver avec EQUIV
• il faut aller à droite d'autant de jours que la durée avec quelques ajustement puisque la durée 1 ne commence pas juste après A2 plus tes Minima et Maxima

La hauteur de la plage à additionner est égale à la durée avec un minimum de 3

DECALER permet donc en partant du point de départ et en utilisant les indices et la hauteur, de définir la plage qui fera l'objet de la SOMME.

Outre l'aide en ligne qui donne le fonctionnement des fonctions, on peut sélectionner chaque partie d'une formule et appuyer sur F9 pour voir ce qu'elle renvoie comme valeur(s)

[Coucou17]

Merci 78Chris pour ces derniers compléments.

J'ai bien utilisé F9 et suis allé fouiller dans l'aide Excel pour chaque fonction.

Mon problème était que la rédaction des instructions ne me semblaient pas cohérentes avec les résultats retournés. Et comme il y a de nombreuses instructions imbriquées, j'étais totalement perdu.

Mais ça fonctionne, c'est ça qui compte. Serai-je capable de le refaire ? C'est moins sûr...