Calcolo quote bollette in automatico

[charlie]

Ciao a tutti, sto cercando inutilmente di risolvere con le formule il problema di calcolare in automatico le quote delle bollette spettanti a ciascun inquilino di un appartamento condiviso da più persone presenti in numero variabile e in periodi diversi.
Ho trovato un esempio postato brillantemente da Gaetanopr nel 2015 e che fa al caso mio. Calcola alla perfezione i periodi di presenza, basta digitare i nomi sotto la colonna Soggetto, le date Dal e Al, premere il pulsante e la macro fa la magia richiesta.
Ma ora viene il bello, per ogni bolletta di cui inserisco l'importo, la data iniziale e la data finale del periodo di riferimento, ho bisogno di sapere come suddividerla fra gli inquilini presenti in quello stesso periodo.
Ci sono casi semplici come le righe 24 e 27, meno semplici come la riga 22 (periodo di riferimento bolletta che inizia prima della presenza di qualcuno e potrebbe capitare anche oltre la fine) e piuttosto complicati come le righe 26 e 33 dove il periodo bolletta è a cavallo di due o più periodi.
Ho la speranza che una macro possa risolvere il problema. Chi mi aiuta?

[Attilafdd]

Con macro non saprei...
provo a proporti una soluzione con formule.
Nel file che allego nelle "righe gialle" ho inserito le formule che calcolano i rispettivi giorni di presenza dei vari inquilini (e ho aggiunto anche la colonna "proprietà" per quando sono sfitti). La riga bianca immediatamente sotto fa la ripartizione della spesa.

Per "acqua" e "gas" che ho colorato in rosso e verde, non ho inserito formule nel tuo schema perchè non ho capito bene la suddivisione che hai fatto tra primo, secondo e terzo periodo... ma le ho riportate qualche riga più sotto (riga 45 e 47) con le nuove formule.

... prova a dare un'occhiata se tornano i conti... con tutte 'ste date ci si perde un po'...

[charlie]

Ciao e grazie per la gradita soluzione senza macro.

... perchè non ho capito bene la suddivisione che hai fatto tra primo, secondo e terzo periodo...

Era solo un modo per agevolare il mio calcolo manuale con i vari step.

Vedo che i risultati sono talvolta diversi dai miei calcoli manuali e dovrò fare dei lunghi test. Per ora posso solo rilevare che erano sbagliati i miei calcoli per la riga 22 (che avevo definito facile ).
Grazie ancora e a risentirci.

[Gaetanopr]

Ciao charlie, ti allego una soluzione con macro, ho creato una procedura RipartoSpese, che compila dal nulla la griglia del riparto delle spese tra i vari inquilini(ti devi solo preoccupare di compilare la tabella spese) poi pensa a tutto la macro.
Cose da sapere: ho creato 3 nomi definiti Inquilini, TabellaInquilini, TabellaSpese.
La griglia viene compilata a partire dalla riga 29, se vuoi cambiare ti basta modificare questa parte di macro

Codice: Seleziona tutto   Espandi visualeStringi visuale

Riga = 28

Puoi pure cambiare l'indirizzo delle tabelle, l'importante che abbiamo sempre lo stesso schema.
Effettua test, se va bene completo la macro, mancano un paio di cose e possiamo migliorare la grafica(sempre da macro)

[charlie]

Ciao Gaetano, grazie per la tua proposta allettante (anche se mi mette in imbarazzo, per non far torto a nessuno prometto di valutare sia la tua che quella di Attilafdd).
La macro ha di primo acchito un problema, sia in OO che LO (occhio alle mie versioni che sono per Mac, può influire?).

[Gaetanopr]

Credo di si purtroppo, comunque apporto una modifica e te le riallego.

[charlie]

Ho verificato, in ambiente Windows la macro funziona.
Già che ci siamo, se la data iniziale del periodo bolletta è inferiore alla data iniziale di permanenza, una quota non dovrebbe essere attribuita a nessuno inquilino (oppure al proprietario come ha interpretato correttamente Attilafdd)

[Gaetanopr]

Già che ci siamo, se la data iniziale del periodo bolletta è inferiore alla data iniziale di permanenza, una quota non dovrebbe essere attribuita a nessuno inquilino (oppure al proprietario come ha interpretato correttamente Attilafdd)

Mi puoi fare un esempio sul file con il calcolo corretto?

[charlie]

Certo, eccolo.
Ho modificato alcune date per semplificare la situazione e renderla (spero) comprensibile.
(gli importi calcolati dovrebbero anche essere arrotondati alla seconda cifra dopo la virgola)
Grazie Gaetano .

[Gaetanopr]

Intanto ti allego la versione per mac(un pò più lenta) comunque sempre da completare

Codice: Seleziona tutto   Espandi visualeStringi visuale

Sub RipartoSpese
Dim oSheet As Object
Dim i As Long, n As Long
Dim Riga As Long


oSheet = ThisComponent.Sheets(0)
AddrSpese = ThisComponent.NamedRanges.getByName("TabellaSpese").ReferredCells.RangeAddress
AddrInq = ThisComponent.NamedRanges.getByName("TabellaInquilini").ReferredCells.RangeAddress
AddrElInq = ThisComponent.NamedRanges.getByName("Inquilini").ReferredCells.RangeAddress

ColTab = AddrSpese.StartColumn
CilInq = AddrInq.StartColumn
Riga = 28                                          REM RIGA DI INIZIO GRIGLIA
svc = createUnoService("com.sun.star.sheet.FunctionAccess")
For i = AddrSpese.StartRow To AddrSpese.EndRow     REM  CICLO CHE ATTRAVERSA LA TABELLA SPESE
  Spesa = oSheet.getcellbyposition(ColTab, i).String
  DatIniSp = oSheet.getcellbyposition(ColTab+1, i).Value
  DatFinSp = oSheet.getcellbyposition(ColTab+2, i).Value
  ImpSpesa = oSheet.getcellbyposition(ColTab+3, i).Value
     ReDim ArrGiorni(0 To (DatFinSp - DatIniSp))
     For t = 0 To Ubound(ArrGiorni)
         ArrGiorni(t) = DatIniSp + t
     Next t
  For x = AddrInq.StartRow To AddrInq.EndRow       REM  CICLO CHE ATTRAVERSA LA TABELLA INQUILINI
     Inquilino = oSheet.getcellbyposition(ColInq, x).String
     DatIniInq = oSheet.getcellbyposition(ColInq+1, x).Value
     DatFinInq = oSheet.getcellbyposition(ColInq+2, x).Value
     Giorni = 0
     For t = DatIniInq To DatFinInq
         For p = 0 To Ubound(ArrGiorni)
           If ArrGiorni(p) = t Then
              Giorni = Giorni + 1
              exit for 
            End if
          Next p         
     Next t
     oSheet.GetCellByPosition(0, Riga).String = Spesa
     oSheet.GetCellByPosition(1, Riga).Value = DatIniSp
     oSheet.GetCellByPosition(2, Riga).Value = DatFinSp
     oSheet.GetCellByPosition(1, Riga).NumberFormat = 30
     oSheet.GetCellByPosition(2, Riga).NumberFormat = 30
     oSheet.GetCellByPosition(3, Riga).Value = ImpSpesa
     oSheet.GetCellByPosition(3, Riga).NumberFormat = 104
     oSheet.GetCellByPosition(AddrElInq.StartColumn-1 ,Riga).String = "GG"
     oSheet.GetCellByPosition(AddrElInq.StartColumn-1 ,Riga+1).String = "Quota/Inq"
     REM CERCO L'INQUILINO NELLA GRIGLIA
     For y = AddrElInq.StartColumn To AddrElInq.EndColumn
       iF oSheet.GetCellByPosition(y ,AddrElInq.StartRow).String = Inquilino Then
          ColonnaInq = y
          Exit For
       End If   
     Next y
     if Giorni > 0 Then
       oSheet.GetCellByPosition(ColonnaInq, Riga).Value = Giorni
     End if
     TotGiorni = TotGiorni + Giorni
  Next x
    For q = AddrElInq.StartColumn To AddrElInq.EndColumn
      If oSheet.GetCellByPosition(q, Riga).Value > 0 Then
         oSheet.GetCellByPosition(q, Riga+1).Value = ImpSpesa/TotGiorni * oSheet.GetCellByPosition(q, Riga).Value
         oSheet.GetCellByPosition(q, Riga+1).NumberFormat = 104
      End If
    Next q
    Riga = Riga + 2
    TotGiorni = 0
    Set Dic = Nothing                             REM RIPORTO A NOTHNG IL DIZ
Next i
     msgbox "Finito"     
End Sub


[charlie]

Perfetto, funziona .

[Gaetanopr]

Ho modificato alcune date per semplificare la situazione e renderla (spero) comprensibile.

Codice: Seleziona tutto   Espandi visualeStringi visuale

Sub RipartoSpese
Dim oSheet As Object
Dim i As Long, n As Long
Dim Riga As Long

calc = createUnoService( "com.sun.star.sheet.FunctionAccess" )
oSheet = ThisComponent.Sheets(0)
AddrSpese = ThisComponent.NamedRanges.getByName("TabellaSpese").ReferredCells.RangeAddress
AddrInq = ThisComponent.NamedRanges.getByName("TabellaInquilini").ReferredCells.RangeAddress
AddrElInq = ThisComponent.NamedRanges.getByName("Inquilini").ReferredCells.RangeAddress

ColTab = AddrSpese.StartColumn
CilInq = AddrInq.StartColumn
Riga = 28                                          REM RIGA DI INIZIO GRIGLIA
svc = createUnoService("com.sun.star.sheet.FunctionAccess")
For i = AddrSpese.StartRow To AddrSpese.EndRow     REM  CICLO CHE ATTRAVERSA LA TABELLA SPESE
  Spesa = oSheet.getcellbyposition(ColTab, i).String
  DatIniSp = oSheet.getcellbyposition(ColTab+1, i).Value
  DatFinSp = oSheet.getcellbyposition(ColTab+2, i).Value
  ImpSpesa = oSheet.getcellbyposition(ColTab+3, i).Value
  Proprietario = False
     ReDim ArrGiorni(0 To (DatFinSp - DatIniSp))
     For t = 0 To Ubound(ArrGiorni)
         ArrGiorni(t) = DatIniSp + t
     Next t
  For x = AddrInq.StartRow To AddrInq.EndRow       REM  CICLO CHE ATTRAVERSA LA TABELLA INQUILINI
     Inquilino = oSheet.getcellbyposition(ColInq, x).String
     DatIniInq = oSheet.getcellbyposition(ColInq+1, x).Value
     DatFinInq = oSheet.getcellbyposition(ColInq+2, x).Value
     Giorni = 0
     For t = DatIniInq To DatFinInq
         For p = 0 To Ubound(ArrGiorni)
           If ArrGiorni(p) = t Then
              Giorni = Giorni + 1
              exit for 
            End if
          Next p         
     Next t
     oSheet.GetCellByPosition(0, Riga).String = Spesa
     oSheet.GetCellByPosition(1, Riga).Value = DatIniSp
     oSheet.GetCellByPosition(2, Riga).Value = DatFinSp
     oSheet.GetCellByPosition(1, Riga).NumberFormat = 30
     oSheet.GetCellByPosition(2, Riga).NumberFormat = 30
     oSheet.GetCellByPosition(3, Riga).Value = ImpSpesa
     oSheet.GetCellByPosition(3, Riga).NumberFormat = 104
   '  oSheet.GetCellByPosition(AddrElInq.StartColumn-1 ,Riga).String = "GG"
   '  oSheet.GetCellByPosition(AddrElInq.StartColumn-1 ,Riga+1).String = "Quota/Inq"
     REM CERCO L'INQUILINO NELLA GRIGLIA
     For y = AddrElInq.StartColumn To AddrElInq.EndColumn
       iF oSheet.GetCellByPosition(y ,AddrElInq.StartRow).String = Inquilino Then
          ColonnaInq = y
          Exit For
       End If   
     Next y
     if Giorni > 0 Then
       oSheet.GetCellByPosition(ColonnaInq, Riga).Value = Giorni
       Redim Preserve ArrayDatIniInq( 0 To g)
       ArrayDatIniInq(g) = DatIniInq
       g = g + 1
     End if
     TotGiorni = TotGiorni + Giorni
  Next x
    g = 0
    If  DatIniSp < calc.callFunction( "MIN", ArrayDatIniInq()) Then
        GiorniPr = calc.callFunction( "MIN", ArrayDatIniInq()) - DatIniSp
        TotGiorni = TotGiorni + GiorniPr
    End If
      If GiorniPr> 0 Then
         oSheet.GetCellByPosition( AddrElInq.StartColumn-1, Riga).Value = GiorniPr
         GSpesa = DatFinSp - DatIniSp + 1
         oSheet.GetCellByPosition( AddrElInq.StartColumn-1, Riga+1).Value = ImpSpesa/GSpesa * GiorniPr
         ImpSpesa = ImpSpesa - ImpSpesa/GSpesa * GiorniPr
         TotGiorni = TotGiorni - GiorniPr
         oSheet.GetCellByPosition( AddrElInq.StartColumn-1, Riga+1).NumberFormat = 104
      End If   
    For q = AddrElInq.StartColumn To AddrElInq.EndColumn
      If oSheet.GetCellByPosition(q, Riga).Value > 0 Then
         oSheet.GetCellByPosition(q, Riga+1).Value = ImpSpesa/TotGiorni * oSheet.GetCellByPosition(q, Riga).Value
         oSheet.GetCellByPosition(q, Riga+1).NumberFormat = 104
      End If
    Next q
    Riga = Riga + 2
    TotGiorni = 0
    GiorniPr = 0
Next i
     msgbox "Finito"     
End Sub








Ora dovrebbe andare bene.

[charlie]

Grazie, verifiche in corso.

[charlie]

I test hanno dato in generale risultati positivi per entrambe le soluzioni (con formule di Attila e con macro di Gaetano).
Infatti forniscono risultati uguali fra loro e con il mio calcolo "manuale".

Solo in casi particolari (data inizio prima bolletta anteriore a quella della prima presenza di un inquilino, oppure data fine bolletta successiva a quella della presenza dell'ultimo inquilino = quote di competenza del proprietario) sono presenti alcuni errori con entrambe le soluzioni.
Ho evidenziato i dettagli nei rispettivi file di esempio allegati.

Ancora un grazie ad Attila e a Gaetano.

Versione Attila.ods

(26.3 KiB) Scaricato 15 volte

Versione Gaetano.ods

(21.2 KiB) Scaricato 13 volte

[Gaetanopr]

Ciao charlie, i calcoli mi sembrano corretti, le ultime 5 righe escono fuori perchè la tabella non è tutta piena ma questa è una delle cose da sistemare(avevo precisato che se il file andava bene necessitava di modifiche, non solo questa che hai evidenziato) almeno altre 2 per funzionare bene.
I giorni che vanno dal 10/11/15 (inizio bolletta) al 31/12/15(fine permanenza compresa nel fine periodo bolletta(01/01/16) sono 52, se tu fai C8-B8 il risultato è 51 ma devi aggiungere 1, sarebbe come fare 15/04/19 - 14/04/19 che restituisce 1 ma i giorni di permanenza sono 2.
Altrimenti inserisci manualmente i risultati dove credi sia l'errore

[charlie]

le ultime 5 righe escono fuori perchè la tabella non è tutta piena

Ciao Gaetano, certo.

Altrimenti inserisci manualmente i risultati dove credi sia l'errore

Ho cambiato i dati per evidenziare meglio il problema, si tratta di un periodo dal 01/01/16 al 31/01/16 in cui non ci sono inquilini.

[Attilafdd]

Ciao Charlie,
ho modificato le formule per la ripartizione... ma non sono sicuro di aver ben interpretato perché nel file c'era solo l'indicazione "errato" e non il risultato che ti aspettavi.
Le ho modificate attribuendo al proprietario la quota parte di bolletta riferibile ai giorni sfitti (nel periodo di competenza della bolletta), mentre per la quota parte (in euro) della bolletta riferibile ai periodi affittati, divisa tra tutti gli affittuari proporzionalmente al proprio periodo di permanenza.

Allego il file modificato, ma resto in attesa di conferma sull'interpretazione.

[Gaetanopr]

Ho cambiato i dati per evidenziare meglio il problema, si tratta di un periodo dal 01/01/16 al 31/01/16 in cui non ci sono inquilini.

Si mi era sfuggita questa casistica, ho modificato la macro, però mi da un risultato diverso dal tuo.
Il calcolo che fa la macro è il seguente
Giorni totali dal 10/11/15 al 31/01/16 = 83
Sfitto dal 01/01/16 al 31/01/16 = 31 a carico del proprietario
quota totale € 240,00 / 83 giorni totali = 2,89 x 31gg = 89,59 a carico del proprietario
rimanenza € 240,00 - 89,59 = 150,410
€ 150, 41 / 208 totale giorni inquilini x 52(giorni di ogni inquilino) = 37,60
89,59 + 37,60 + 37,60 + 37,60 + 37,60

[charlie]

ho modificato le formule per la ripartizione...

Scusami, ma hai interpretato correttamente.
Ora mi sembra tutto a posto, grazie infinite .

[charlie]

però mi da un risultato diverso dal tuo

Hai ragione, ho fatto un ragionamento giusto ma poi ho allegato un esempio sbagliato.
Comunque le modifiche sono corrette e anche la formattazione è andata a posto.
Grazie ancora per l'aiuto .

[charlie]

Prima di mettere [Risolto] farò delle verifiche con dati reali.