TOTAL Trim. Conditionnel (H.réalisées>=H.autorisées):La bonne formule,svp?

[Staple1600]

Bonjour à tous


Je sèche lamentablement sur ce petit problème formulistique:

		HA	HR	Tarif/H
NOM1	mois N	11	11	1,27	Formule OK si HR=HA
NOM1	mois N+1	11	14	1.27	Formule PAS OK si HR>HA mais TOTAL HA=HR
NOM1	mois N+2	11	11	1.27	Bref je patauge dans la semoule
	TOTAL TRIM.	33	36

Légende: HA= Heures autorisées HR= Heures réalisées

Je joins un fichier exemple (ça fait tout drôle depuis le temps ) pour mieux illustrer mon propos avec mes pitoyables formules fruits de mes différents essais.

Merci aux émérites formulistes de bien vouloir me prêter main forte

PS: Les formules dollarisées me posent également probléme si je veux les insérer par VBA.
Si un vbaiste/formuliste ou son contraire passe ici, you're welcome

 

[hoerwind]

Re : TOTAL Trimestriel conditionnel (H.réalisés <= H.autorisées): La bonne formule SV

Salut Staple, heureux de te croiser,

Il me semble que si tu précisais les contraintes auxquelles les formules devraient répondre, il sera bien plus facile de te faire une proposition.
Rien qu'en spécifiant déjà les résultats à obtenir ce serait déjà pas mal !
Que veux-tu qu'on fasse avec des formules qui ne répondent pas à ton besoin ?

A te lire

 

[Staple1600]

Re : TOTAL Trimestriel conditionnel (H.réalisés <= H.autorisées): La bonne formule SV

Re, Bonjour Hoerwind (et merci de ton intérêt pour ce fil)

Je pensais avoir été assez précis.
Désolé

Voici plus d'explications qui j'espère vous aideront à mieux saisir la situation.

• Il y a un nombre d'heures trimestrielles autorisées et un nombre d'heures effectivement réalisées.
  Quand ces deux sommes sont égales pas de souci.
  Si la somme des heures réalisées > à la somme des heures autorisés alors normalement on ne retient que la somme des heures autorisées.
  C'est OK si le nombre d'heures réalisées est supérieure chaque mois.
• Mais le problème arrive ici:
  Si le nombre d'un seul ou deux des mois du trimestre est supérieur au nombre d'heure autorisés de ce ou ces mêmes mois, mais que le somme trimestrielle des heures réalisées reste inférieure ou égale à celle des heures autorisées, les formules précédentes ne sont pas utilisables.

Dans la pièce jointe précédente j'ai mis ce cas de figure.
La formule en colonne Essai1 fonctionne sauf dans le cas NOM4
(c.a.d quand la somme HR> somme HA mais à cause d'u seul mois ou plus qui dépasse son nombre d'HA.

PS: A force de me creuser les méninges devant ma feuille, il arrive à moment où il semble évident qu'un oeil neuf saura me guider vers la lumière
C'est pourquoi je me suis tourné vers vous xldiens.
Au plaisir donc d'apprécier votre regard neuf sur les choses

 

[Staple1600]

Re : TOTAL Trimestriel conditionnel (H.réalisés <= H.autorisées): La bonne formule SV

Re


En fait, il faudrait en un seule formule (pas sur de la formulation mais je tente)

=SI(ET(SOMME_HR=SOMME_HA;NB_HR=NB_HA);NB_HR*TARIF/H;NB_HA*TARIF/H))

ou:
SOMME_HR= Somme des heures réalisés
SOMME_HA= Somme des heures autorisées
NB_HR=Nombre d'heures autorisées
NB_HR=Nombre d'heures réalisées
TARIF/H=tarif horaire

C'est donc dans le cumul de ces conditions que je coince.

Il peut aussi arriver que dans un même trimestre le tarif varie d''un mois sur l'autre, ce qui complique encore les choses.

 

[ninbihan]

Re : TOTAL Trimestriel conditionnel (H.réalisés <= H.autorisées): La bonne formule SV

Bonjour Staple,

Un petit essai,


Bonne journée à tous,

Ninbihan

 

[Gardien de phare]

Re : TOTAL Trimestriel conditionnel (H.réalisés <= H.autorisées): La bonne formule SV

Bonjour,

Ca ?

 

[Staple1600]

Re : TOTAL Trimestriel conditionnel (H.réalisés <= H.autorisées): La bonne formule SV

Re


Voici mon 4ème essai de formule:

=SI(OU(MOD(SOMME($D$2:$D$4);3)=0;D$3=C$3);C2*E2;D2*E2)

qui dans l'exemple de la pièce jointe de mon 1er message
ne donne pas le bon résultat pour NOM4 également.

De plus, je ne sais pas commet faire avec les dollars, car cela m'oblige pour le moment à modifier les formules pour le premier mois de chaque trimestre puis seulement ensuite de pouvoir faire un CTRL+B
(mais avec beaucoup de lignes ce sera vite fastidieux)

 

[Staple1600]

Re : TOTAL Trimestriel conditionnel (H.réalisés <= H.autorisées): La bonne formule SV

Bonjour ninbihan, Gardien de Phare

Désolé, je n'avais pas réactualisé ma page.

Je viens juste de voir vos messages et vos pj.

Je les regarde de suite et vous redit.

Merci d'avance.

EDITION:
Ninbihan: cela semble le faire Merci pour cette formule (et de m'avoir permis de retrouver bonne MIN )
Mais il reste la recopie avec le problème des références dollarisées
(je pense qu'il faudra passer par VBA, non ?)

Je vais voir de suite la PJ de Gardien de phare.

Gardien de phare:
Merci mais il y a un souci avec ton option.

PS: Je récapitule tout cela dans le message qui va suivre pour plus de clarté.

 

[Staple1600]

Re : TOTAL Trimestriel conditionnel (H.réalisés <= H.autorisées): La bonne formule SV

Re à tous

Un petit point sur vos propositions:
La formule de Ninbihan fonctionne pour Mois N+1
(qui doit afficher 13*1.84 puisque que bien que HR de mois N+1 > HA mois N+1, le total du trimestre HR < total trimestre HA.
Ce qui n'est pas le cas chez Gardien de Phare , même ,si le total trimestre lui est bon.
Voir ci-dessous.


Merci en tout cas à tout les deux.
Je vais voir comment me dépatouiller pour insérer les formules dollarisées en VBA sur plusieurs centaines de lignes.

 

[Gardien de phare]

Re : TOTAL Trimestriel conditionnel (H.réalisés <= H.autorisées): La bonne formule SV

Re,

Je ne me suis préoccupé que du total trimestriel, effectivement. En fait, il faudrait savoir, il y des facturations intermédiaires avec régularisation à la fin du trimestre ou une facturation trimestrielle avec un détail par mois ? Je suis parti de la première hypothèse !

 

[Staple1600]

Re : TOTAL Trimestriel conditionnel (H.réalisés <= H.autorisées): La bonne formule SV

Re, Gardien de Phare

Je viens de faire un ajout dans l'édition de mon précédent message.

Pour répondre à ta dernière question:
Il existe un fichier modèle (sans formules) qui est rempli manuellement par N prestataires.
Tous les trimestres, je recois N fichiers sur lequel je dois injecter les formules basiques de sommation par VBA.
Car selon le niveau de connaissance et de maîtrise d'Excel des N prestataires, je dispose de fichiers qui vont de :
"Chouette! Oh les beaux fichiers que voilou" à "Quel souk la-dedans!!"

D'ou ma tentative du jour et ma demande sur XLD.

PS: Pour l'instant je m’occupe pas des réguls, le but est juste d'avoir les bons totaux selon les cas de figures évoqués plus bas.
Je vais tester lundi en grandeur nature la formule de ninbihan.

Il me reste juste à implémenter cela en VBA, ce que je vais tester de ce pas.

 

[Gardien de phare]

Re : TOTAL Trimestriel conditionnel (H.réalisés <= H.autorisées): La bonne formule SV

Re,

Il y a un problème de logique dans nos deux solutions : si tu dépasses le quota horaire du trimestre ça te coute moins cher que si tu l'atteint sans le dépasser !

Dans ma solution, la formule pour le trimestre devrait, je pense être

=SI(R5>Q5;SOMMEPROD((Q2:Q4)*(S2:S4));SOMMEPROD((R2:R4)*(S2:S4)))

 

[Staple1600]

Re : TOTAL Trimestriel conditionnel (H.réalisés <= H.autorisées): La bonne formule SV

Re

Qu'ouis-je ? Un problème de logique.. Madre mios

Ça m'apprendra à vouloir penser au boulot le week-end !!!

Je demanderai à mon collègue lundi ses règles exactes.

Pourtant je suis bien réveillé.

Ah çà me revient :
La règle est celle-ci (et cela qui me complique les choses que je l'avais zappé !!!)
Tant que le total HR ne dépasse pas le total HA mais qu'un ou deux mois dépasse son Total HA, on peut aller au max du Total HA
exemple:

HA	HR	T/H	TOTALHA	TOTALHR	FACTURE PRESTA	FACT RECALCULEE
11	12	1	11	25	25	11
11	50	1	11	11	11	11
11	4	1	11	5	5	11
33	33		33	41	41	33

Le souci c'est comment et ou repartir les heures quand il y dépassement si le nombre d'HR n'est pas divisible par trois.

PS: Je tiens à préciser que je ne suis pas le concepteur des classeurs modèles envoyés aux prestataires et que j'essaie simplement de me simplifier la tache (ainsi qu'aux prestataires sachant utilser le modée fourni sans altérer ses formules)
Si vous avez des idées de conception de tableau tout à fait autre pour cette problématique, je suis bien sur preneur.
Faudra juste que je caresse dans le sens du poil le modeleur original avec qui je prends le café tout les matins

 

[ninbihan]

Re : TOTAL Trimestriel conditionnel (H.réalisés <= H.autorisées): La bonne formule SV

Re,

Je n'ai pas tout suivi, un nouvel essai (essai5) avec deux zones nommées + total tous les quatre lignes
on peut donc tirer la formule vers le bas

 

[Gardien de phare]

Re : TOTAL Trimestriel conditionnel (H.réalisés <= H.autorisées): La bonne formule SV

Re,

Alors une de ces deux solutions peut-être ?