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

 

[Staple1600]

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

Re

Merci pour vos contributions que je regarde dès que je sors de la cuisine où j'ai été sommé de me rendre illico presto pour participer aux préparatifs de table et de ce qui va avec en lieu de place des problème de tableur auquel il paraîtrait que je donne trop de moi-même, et trop de temps libre.

A tout l'heure et merci encore.

PS: Tout à l'heure, c'est vite dit... il flotte dans l'air comme une odeur de repassage...ou de lessive.. voire les deux ..caramba !!

 

[Staple1600]

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

Re

Merci pour vos deux solutions
maintenant cela se complique, si je veux insérer les formules en VBA
1er essai avec la solution de Gardien de Phare

Sub test()
Dim c As Range
For Each c In Selection
Select Case c.Row Mod 4
Case 2 ' formule ok
Cells(c.Row, "K").FormulaR1C1 = _
"=IF(SUM(R" & c.Row & "C4:RC[-7])>SUM(R" & c.Row & "C3:RC[-8]),SUMPRODUCT((R" & c.Row & "C3:RC[-8])*(R" & c.Row & "C5:RC[-6])),SUMPRODUCT((R" & c.Row & "C4:RC[-7])*(R" & c.Row & "C5:RC[-6])))"
Case 3 ' erreur de syntaxe en VBA, reste à trouver ou ;o)
Cells(c.Row, "K").FormulaR1C1 = _
"=IF(SUM(R" & c.Row - 1 & "C4:RC[-7])>SUM(R" & c.Row - 1 & "C" & c.Row & ":RC[-8]),SUMPRODUCT((R" & c.Row - 1 & "C" & c.Row & ":RC[-8])*(R" & c.Row - 1 & "C5:RC[-6])),SUMPRODUCT((R" & c.Row - 1 & "C4:RC[-7])*(R" & c.Row & "C5:RC[-6])))-SUM(R" & c.Row & "C11:R[-1]C)"
Case 0
' formule à traduire en VBA
End Select
Next c
End Sub

ninbinhan: ta solution serait ok si j'étais seul à traiter les fichiers.
J'ai peur qu'avec des zones nommées, les prestataires "trifouillards" risquent de mette un pataquès pas possible.

Je vais faire une pause pour revenir réfléchir à tout cela à tête reposée.

 

[Staple1600]

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

Bonsoir


A la réflexion , je vais tenter également d'harmoniser mes N fichiers avec la solution de ninbihan
Ce qui donne avec VBA

Sub test()
Dim dl&
With ActiveWorkbook.Names
    .Add Name:="Autor", RefersToR1C1:= _
    "=OFFSET(Feuil1!R1C3,(INT((ROW()+2)/4))*4,)"
    .Add Name:="Real", RefersToR1C1:= _
    "=OFFSET(Feuil1!R1C4,(INT((ROW()+2)/4))*4,)"
End With
dl = Cells(Application.Rows.Count, 2).End(xlUp).Row
With Range("K2:K" & dl)
.FormulaR1C1 = _
"=IF((MOD(ROW()-1,4)=0),SUM(OFFSET(RC,-1,0),OFFSET(RC,-2,0),OFFSET(RC,-3,0)),IF(Real<=Autor,RC[-7]*RC[-6],MIN(RC[-8],RC[-7])*RC[-6]))"
End With
End Sub

Cela fonctionne bien sur mon fichier exemple.
Mais sur les fichiers réels, la ligne d’entête ne sera pas en A1.
Ce qui pose un problème avec les formules nommées.
(évidemment je peux faire un copier/coller valeurs seules, mais c'est pas top)
Autre souci: certains prestataires ont des fichiers avec des centaines de lignes à jusqu'à plus ou moins 6000 lignes.

Si vous avez d'autres pistes, n'hésitez pas

 

[Gardien de phare]

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

Bonjour,

Évidement, si les fichiers n'ont pas toujours la même structure, ça se complique.

Trois points tout de même avant d'essayer d'industrialiser le bazar :

• Il y un problème dans la proposition de ninbihan, comme dans la première que je t'avais faite : si on dépasse le quota horaire du trimestre, on paye moins cher que si on l'a juste atteint ! essayes de rentrer 13 en D4 pour t'en convaincre.
• Le critère de plafond par trimestre est bien le nombre d'heures indépendamment de leur cout ?
• Y-a-t'il absolument besoin du coût de chaque mois ou peut-on se contenter de celui du trimestre ?

 

[Staple1600]

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

Bonjour à tous

Gardien de phare
Au départ des fichiers modèles identiques ont été distribués.
Ces fichiers n'étaient pas protégés.
Certains ont ajoutés des lignes pour X raisons (ajout d'un logo, d'une adresse mail etc...)

La seule structure qui reste commune, c'est une sucession de N lignes

NOMS	PERIODE	NBHA	NBHR	TOTAL AUTORISE	TOTAL A PAYER
NOM1	MOIS N
NOM1	MOIS N+1
NOM1	MOIS N+2
	TOTAL TRIM.
NOM2	etc...

On m'a bien fait comprendre que les fichiers ne seraient pas protégés pour éviter les coups de fils demandant comment faire pour modifier le document etc...

Ces fichiers Excel servent de base à une saisie manuelle (réalisée par ma collègue) dans un logiciel comptable.
L'intérêt d'uniformiser les fichiers c'est que les montants des fichiers Excel soient au plus proche des montants pré-saisis* dans ce logiciel (* chaque nom a son NBHA et TOTALTRIM pré-renseigné dans ce logiciel)
Charge à ma collègue de saisir le NBHR.
Et le but est d'arriver à ce que la facture envoyée par le prestataire soit le moins biffée possible (car souvent des formules sont effacées , il y a des erreurs d'arrondis, ou des recopies de formules maladroites)
A réception de ces fichiers par mail, remettre tout cela d'aplomb par formule ou VBA facilite la tâche de ma collègue.

Une petite précision: remettre les formules permets aussi de traiter le cas suivant

NBHA	NBHR
13,50	26
13,50	5,75
13,50	2,45

Il est pas évident parfois de savoir de tête si on dépasse ou pas le NBHA

 

[Staple1600]

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

Re,


Pour répondre à tes questions

Trois points tout de même avant d'essayer d'industrialiser le bazar :Il y un problème dans la proposition de ninbihan, comme dans la première que je t'avais faite : si on dépasse le quota horaire du trimestre, on paye moins cher que si on l'a juste atteint ! essayes de rentrer 13 en D4 pour t'en convaincre.

Le critère de plafond par trimestre est bien le nombre d'heures indépendamment de leur cout ?

Il y a nombre d'heure trimestriel autorisé.

Le nombre qui sera saisi dans le logiciel pour le trimestre à traiter ne peut être supérieur mais peut être inférieur à ce maximal trimestriel.

(Selon les cas , il y a une seule ligne de saisie ou une ligne pour chaque mois, voire 2 lignes + une ligne)
On a donc besoin d'avoir dans le fichier Excel une saisie selon le modèle présent dans cette discussion.
(Je zappe volontairement tout ce qui peut être régularisation ultérieure)
Y-a-t'il absolument besoin du coût de chaque mois ou peut-on se contenter de celui du trimestre ?

Le coût horaire peut être différent d'un mois sur l'autre

donc on on peut pas faire TOTALTRIM*TARIF/H

PS: Gardien de Phare:
Si tu vois comment traduire tes formules en VBA, fais-moi signe, j'ai pas encore trouver ou j'ai fait une erreur de syntaxe.

 

[Gardien de phare]

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

Re,

Je pense que plutôt que de traduire les formules en Vb, il est préférable de construire le calcul et de la réaliser par Vba. On pourrait faire une boucle pour traiter tous les trimestres successifs ensuite.

C'est simple dans le cas où le total HR est <= à total HA. Ça se complique dans l'autre cas :
- quelle règle utiliser pour la répartition par mois ?
- certains cas de figure peuvent-ils exister comme le total Hr du 1er mois est > au total HA du trimestre ? etc.

 

[Staple1600]

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

Re


Le but du jeu c'est que la facture Excel doit donner les montants affichés par le logiciel comptable
(qui lui est toujours à jour pour ce qui est du nombre heures autorisé et du tarif horaire)
Pour mieux comprendre voici le mode opératoire de traitement pour un trimestre et un prestaire
1) On reçoit la facture (fichier excel) par email (ou pire par voie postale)
2) Ma collègue imprime cette facture en vue de sa saisie dans le logiciel
3) Elle renseigne dans celui-ci le trimestre désiré et le numéro de tiers du prestataire
Apparaît alors la liste de tous les noms à payer sur cette période lièe à ce prestataire.
(avec le nombre d'heures autorise, le tarif horaire et le montant à payer)
selon les cas, comme je le disais, il peut s'afficher:
NOM1: NBHA | TOTAL TRIM
ou
NOM1: MOIS1 à MOIS 1+N | NBHA
NOM1: MOIS N+2 | NBHA
ou
NOM1| NBHA
NOM1| NBHA
NOM1| NBHA
enfin comme évoqué précédemment

Donc si le fichier Excel est bien fait, lors de la saisie dans le logiciel, les montants entre le fichier Excel et ceux affichés dans le logiciel devraient être identiques.

Comme c'est rarement le cas, pour les raisons évoquées plus bas, vous comprendrez pourquoi on m'a demandé d'essayer de faire une moulinette pour avoir des fichiers Excel qui se calculent correctement.

 

[Staple1600]

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

Re

Gardien de Phare

Je pense que plutôt que de traduire les formules en Vb, il est préférable de construire le calcul et de la réaliser par Vba. On pourrait faire une boucle pour traiter tous les trimestres successifs ensuite.

Il n'y a qu'un seul trimestre par facture.
Donc on reçoit 4 fichiers par an.
(je ne parle pas volontairement des fichiers de régularisations ultérieures)

L'idéal serait d'arriver à trouver une formule assez simple pour certains prestataires avec un bonne maitrise dExcel l'utilisent à l'avenir.
Pour les autres fichiers "dysfonctionnels", la macro "formulastique" que vous m'aider à construire (merci encore les gars ) s'en chargera.

 

[Gardien de phare]

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

Re,

Une approche par Vba, regardes si ça correspond à ton cahier des charges que je ne comprends pas totalement. Si c'est ça, il ne restera plus qu'à industrialiser à coup de boucles !

 

[Staple1600]

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

Re


Qu'est-ce que tu ne comprends pas ?

EDITION: Je viens de tester ton dernier fichier
Pas OK sur le TOTAL TRIM puisque NBHA=33 alors on paiera 33*1.84=55,20
(le logiciel refusa qu'on saisisse 38h)
Pourquoi tu passes par une procédure évènementielle?
Car au final, le fichier Excel finit sa vie de fichier en étant imprimé.
Il s'agit donc de le recalculer dans sa globalité (sans chercher où sont les éventuelles erreurs commises par les prestataires) avant de l'imprimer.
Quand à joindre un fichier avec du VBA aux prestataires, je ne pense pas que ce soit possible.
Cela risque encore plus de générer des difficultés pour certains d'entre eux.

Les seules choses qui sont fiables dans leurs fichiers (et encore parfois il manque des données) c'est le nombre d'heures réalisées indiqué pour chaque nom et le tarif horaire (quoique parfois leur tarfi n'est pas mis ç jour en temps voulu)
Donc la formule ou macro doit juste (au cas ou il manquerait les formules d'origine), recalculer le NBHR*TARIF/H
et adapter le résultat si NBHR>NBHA

 

[Gardien de phare]

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

Re,

C'est comment gérer les détails mensuels si le nombre total d'heures est dépassé. Où imputer la régularisation négative dans ce cas sachant qu'elle peut être sur plusieurs mois.

Dans une logique strictement comptable, on établirait une facturation intermédiaire mois par mois avec régularisation en fin de trimestre avec facture additionnelle ou soit avoir, soit remboursement.

 

[Staple1600]

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

Re


J'ai bien précisé que je ne parlais pas des régularisations ultérieures.
Il s'agit simplement, sachant que les fichiers sont manipulés par x mains ne sachant pas toujours comment utiliser Excel, de retraiter ces fichiers afin de refaire automatiquement les calculs.
Donc ici;
NBHR*TARIF/H
et
TOTAL TRIM
avec la gestion :
- des cas ou si le NBHR > au NBHA alors on retient le NBHA
- des cas particuliers comme celui-ci
NBHA=33 pour le trimestre, TARIF/H: 1,50
MOIS1= 25 ici on laisse le calcul faire 25*1,50 parce que le total du trimestre n'est pas dépassé
MOIS2=7 -> 7*1,50
MOIS3=1 -> 1*1,5

PS: J'ai testé ton fichier (voir mon édition dans le message #26)

 

[Gardien de phare]

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

Re,

Le Vba que je t'ai envoyé, ça le fait ou pas ?

 

[Staple1600]

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

Re

Voir mon dernier message et le message #26