Facturation complexe

Bonjour à tous,

Je suis nouveau ici, je me suis suis inscrit car je me trouve face à un problème un peu trop complexe pour ma logique.

On m'a demandé d'automatiser, sur Excel, la facturation d'un établissement.

Sur le principe les règles de facturation sont simple.

On est sur un établissement de type accueil de personnes. On facture la personne à la journée dès lors que celle-ci à pris au minimum un repas dans l'établissement.

Le contrôle des présences se fait alors 3 fois par jour, au moment des repas :

Matin : Présent ou Absent ?

Midi : Présent ou Absent ?

Soir : Présent ou Absent ?

Sauf qu'une nouvelle règle vient chambouler ce calcul simple ...

Désormais on fonctionne sur des absences consécutives. C'est à dire que :

• si la personne est absente lors des 3 repas de la journée : la journée n'est pas facturée
• Si la personne est absente le matin et le midi, mais prend le repas du soir : la journée est facturée
• Si la personne est absente le midi, le soir (jour A) et le lendemain matin (jour B) : seule la journée B est facturée
• Si la personne est absente le soir (journée A), le matin et le midi (journée B) : seule la journée A est facturée.

Dans l'établissement, les personnels en charge de la vérification des présences remplissent un tableau en indiquant pour chaque créneau :

• A : pour absent
• P : pour présent

Le problème qui se pose alors est d'automatiser ce tableau pour compter les jours qui doivent être facturés automatiquement à la fin du mois.

Je joint à ce message un document dans lequel j'ai essayé d'illustrer tout ça.

J'espère que je suis assez clair et explicite dans l'expression de mon problème, et je vous remercie par avance de l'aide que vous pourrez m'apporter.

Salut Aziler,

Bonsoir le forum,

un premier jet, vite fait, sans recherche ni tralalas...

Ligne après ligne, tu complètes tes journées avec "A" ou "P" et la cellule-référence de la journée s'allume en vert (à facturer) ou en rouge.

Si j'ai bien compris le système, ça marche à tous les coups.

A+

Bonjour Curulis,

Il semblerait que tout fonctionne parfaitement, c'est parfait ! Je vous remercie vraiment !

Existe-t-il un moyen de d'extraire le nombre de jour à facturer par mois ?

Du style : Si A3 est vert alors tu comptes 1, sinon tu comptes 0 ?

En tout cas, vraiment merci infiniment !!

Bonsoir Aziler,

effectivement, une case verte = 1 jour facturé!

Comme je le disais : sans recherche ni tralalas.

Si tu veux quelque chose de mieux, il faudrait :

• une vraie feuille de travail (en-têtes, couleurs,...) ;
• tu renseignes des noms quelque part, je suppose ?
• d'autres choses à savoir?
• ...

A toi de voir!

A+

Bonjour Curulis,

Effectivement, dans sa version de base le classeur s'organise de la manière suivante :

• 52 feuilles (pour 52 semaines de l'année)
• A la fin de chaque semaine on compte le total de jour à facturer ainsi que le total de repas à facturer.
• Sur une dernière feuille de calcul, on calcule un récapitulatif annuel par résident.

L'idéal serait alors de pouvoir dupliquer ta solution (formule ? macro ?) sur les nouvelles feuilles d'un classeurs. Il ne nous resterait alors plus qu'à calculer les totaux mensuels et annuels par résident.

Je te joint notre tableau de base dans sa version raccourcis, je peut également te transmettre le tableau complet si besoin.

En te remerciant encore !

Bonsoir Aziler,

Bonsoir le forum,

voilà ton fichier sans plus de recherche ni tralalas si ce n'est qu'il s'applique maintenant à l'ensemble de tes feuilles-semaines.

• soit en complétant jour après jour ;
• soit en double-cliquant n'importe où en [A1:B5] pour traiter une feuille entière en une fois.

Les totaux se calculent tout seuls.

Le code ne doit pas se dupliquer de feuille en feuille. Il doit être collé dans le module 'ThisWorkBook' d'où il s'applique à l'ensemble des feuilles. Ceci entraîne l'obligation d'avoir une structure des feuilles-semaines rigoureusement identique d'une feuille à l'autre, les macros s'attendant à trouver leurs données au même endroit, quelle que soit la feuille appelante.

Seul le nombre de résidents peut varier autant que tu veux de semaine en semaine.

Tes formules en 'Récap' devront être adaptées de toute façon puisqu'elles appellent 'Tableau présences.xlsx' alors que ce fichier , utilisant VBA, s'identifie par un "xlsm".

Préfère un nom court : c'est moins long à taper!!

A tester, comme on dit!

Avec plaisir!

A+

Bonjour Curulis,

Encore une fois tout semble fonctionner à la perfection !

J'ai juste une dernière question ... Quand je vais créer une nouvelle feuille, je n'ai donc qu'a copier/coller les données de la semaine précédente, pour enfin réinitialiser les données P/A afin de repartir sur de bonnes bases ?

Ou est-ce qu'en plus je dois copier/coller du code dans le module que tu m'a indiqué ?

Dans tous les cas je te remercie encore vraiment !

C'est parfait !

Bonjour Aziler,

je ne comprends pas trop ta première question...

Dans un classeur, il n'y a QU'UN module 'ThisWorkBook' : le code ne doit donc pas être reproduit lorsque tu crées une nouvelle feuille puisqu'il est valable pour toutes les feuilles.

Je vais (pas maintenant ni même sans doute aujourd'hui) bidouiller quelque chose pour que tu puisses créer de nouvelles feuilles d'un clic voire créer une année complète automatiquement.

Si tu souhaites une autre représentation (plutôt que des couleurs ou que sais-je...), penses-y...

A+

Bonjour Curulis,

Je me demandais juste comment créer une nouvelle feuille pour une nouvelle semaine sans fausser ton travail. Mais du coup si en plus tu automatises tout, je ne vois plus quoi demander.

Encore une fois merci infiniment !

Salut Aziler,

petites questions :

• est-ce une maison de repos?
• dans ce cas, ont-ils un n° de chambre?
• "H" comme... Hospitalisé?
• lorsque 5 "A" se succèdent en périodes MMS-MM, le 3e "A" (Soir) "comptant" déjà pour qualifier la première journée non-facturable doit-il vraiment être pris en compte une deuxième fois pour qualifier la seconde journée non-facturable?

A+

Bonjour Curulis désolé pour le retard de réponse,

C'est une maison d'accueil spécialisée, il y a des numéros de chambre mais qui peuvent changer régulièrement de résident.

Le H correspond bien à "Hospitalisé".

Pour ta dernière question, si la deuxième journée n'est pas facturable il me semble qu'il n'y a plus nécessité de le reprendre en compte.

Par contre dans le cas d'un A.A.A - A.A.P (MMS-MMS) le P devra être pris en compte pour calculer la journée suivante.

Une dernière petite question, est-ce que dans le modèle que tu m'a envoyé le premier jour d'une nouvelle semaine prend en compte le dernier jour de la semaine précédente dans le calcul ?

En te remerciant encore !

Bonne journée !

Bonjour Aziler,

En effet, je ne tiens pas compte des dernières présences d'un mois pour la continuité du calcul du mois suivant ni des premières d'un nouveau mois pour la qualification du dernier jour du mois précédent!!

Je vais corriger cela aujourd'hui!

Pour mon projet, je prévois 3 feuilles en tout et pour tout, valables pour... 12 ans! Il y a moyen de le faire pour mille ans, évidemment, mais, on ne va pas exagérer non plus!

• 1 feuille 'HEBDO' pour encoder tes présences hebdomadaires dans laquelle tu pourras naviguer dans les archives qui seront conservées dans...
• 1 feuille 'BDD' qui recevra la base de données de tes résidents ainsi que les archives de leurs présences.
• 1 feuille 'RECAP' qui aurait très bien pu "tenir" dans 'HEBDO' (ainsi que 'BDD' d'ailleurs) mais que je sépare pour plus de lisibilité dans laquelle je ferai en sorte de pouvoir naviguer entre les années encodées.

Peux-tu me fournir (en MP si tu veux conserver un minimum de discrétion pro) ta base de données résidents ainsi que les données HEBDO de cette année ('BDD' commence en 2018) ? J'imagine que, dans les fiches signalétiques, il y a une mention d'une date d'entrée et de sortie?

Je pourrai ainsi tester mes petits délires grandeur nature!

Depuis quand le nouveau système de qualification (jours facturables ou pas) est-il entré en vigueur?

A+

Bonjour Aziler,

voici les modifications demandées et plus encore...

Comme j'imagine bien que tu n'as pas trop envie de recommencer l'encodage de tes petites pensionnaires, il va falloir que tu ailles "repiquer" les codes suivants aux endroits ad hoc.

Dans le module VBA de 'RECAP'

Trouver la proc Public Sub CalculRECAP(ByVal iFlag As Integer)

Remplacer une partie du code commençant ainsi :

If iRow > 4 Then

Range("B5:AB" & iRow).Borders.LineStyle = xlContinuous

For x = 4 To 26 Step 2

End If

If iRow > 4 Then
    Range("B5:AB" & iRow).Borders.LineStyle = xlContinuous
    For x = 4 To 26 Step 2
        sCol = Split(Columns(x).Address(ColumnAbsolute:=False), ":")(1)
        Range(sCol & "5:" & sCol & iRow + 1).Interior.Color = Range("D4").Interior.Color
    Next
    Range("A3:A" & iRow).BorderAround xlContinuous, xlMedium
    Range("B4:B" & iRow).BorderAround xlContinuous, xlMedium
    Range("C" & iRow + 1 & ":AB" & iRow + 1).Borders.LineStyle = xlContinuous
    Range("C4:Z" & iRow + 1).BorderAround xlContinuous, xlMedium
    Range("AA4:AB" & iRow + 1).BorderAround xlContinuous, xlMedium
    Range("C" & iRow + 1 & ":AB" & iRow + 1).BorderAround xlContinuous, xlMedium
    Range("C" & iRow + 1 & ":Z" & iRow + 1).Font.Color = RGB(80, 100, 40)
    Range("C" & iRow + 1 & ":Z" & iRow + 1).Font.Bold = True
    Range("AA" & iRow + 1 & ":AB" & iRow + 1).Interior.Color = Range("AA2").Interior.Color
    Range("A4:AB" & iRow).Sort key1:=Range("A4"), order1:=xlDescending, key2:=Range("B4"), order2:=xlAscending, Orientation:=xlTopToBottom
    Range("A3").Font.Color = RGB(10, 10, 10)
    Range("B3").Font.Color = RGB(0, 0, 0)
End If

Les 3 dernières lignes trient directement tes pensionnaires selon leur régime et par ordre alphabétique.

Dans le module VBA de 'ThisWorkBook'

1) Trouver la proc Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)

Case "RECAP" est vide... Mets-y ceci :

        iRow = Range("B" & Rows.Count).End(xlUp).Row
        If Not Intersect(Target, Sh.Range("A3:B3")) Is Nothing Then
            Cells(3, Target.Column).Font.Color = IIf(Cells(3, Target.Column).Font.Color = RGB(0, 0, 0), RGB(10, 10, 10), RGB(0, 0, 0))
            If Target.Column = 1 Then
                Range("A4:AB" & iRow).Sort key1:=Range("A4"), order1:=IIf(Cells(3, Target.Column).Font.Color = RGB(0, 0, 0), xlAscending, xlDescending), key2:=Range("B4"), order2:=xlAscending, Orientation:=xlTopToBottom
            Else
                Range("A4:AB" & iRow).Sort key1:=Range("B4"), order1:=IIf(Cells(3, Target.Column).Font.Color = RGB(0, 0, 0), xlAscending, xlDescending), Orientation:=xlTopToBottom
            End If
            [A2].Select
        End If

Dans 'RECAP', en cliquant sur [A3] ou [B3], tu peux choisir de trier dans l'ordre ou le désordre sur base de la colonne cliquée.

2) Trouver la proc Private Sub Workbook_SheetBeforeRightClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)

Dans le Case Select, ajouter simplement le code suivant dans Case "HEBDO" (les 2 autres restent vides pour l'instant) :

        If Not Intersect(Target, Union(Range("C1"), Range("N1"))) Is Nothing Then
            Cancel = True
            iTCol = Target.Column
            iFlag = IIf(iTCol = 3, IIf(Cells(1, 3) - 5 >= 2018, Cells(1, 3) - 5, 2018), Cells(1, 3) + 5)
            For x = 0 To 11
                Cells(1, x + 3) = iFlag + x
            Next
            Call CalculJour(iTCol)
            Call CalculSemaine(2)
        End If

Dans 'HEBDO', un clic-DROIT sur [C1] reculera le calendrier de 5 ans (2018 au plus loin), un clic-DROIT en [N1] avancera le calendrier de 5 ans.

Te voilà paré jusqu'à ta pension et au-delà...

Quant au dysfonctionnement des "H", je n'ai rien remarqué et pourtant, j'ai essayé plusieurs cas...

Il faudrait que tu me transmettes plus d'infos...

Fais déjà avec ceci, on verra plus tard...

A+

Salut Curulis !

Merci beaucoup, j'ai fais les améliorations et tout fonctionne parfaitement !

Merci encore pour l'efficacité et la réactivité, tu nous sauves la vie ! Bonne journée !