1) se il budget è raggiunto in un giorno diverso dall'ultimo giorno del mese allora vorrei che l'analisi riprendesse dal 1° giorno del mese successivo senza darmi il totale dell'ultimo giorno del mese (Es. se il 14 aprile è raggiunto il budget allora il 30 aprile non deve esserci nessun totale perché l'analisi deve ripartire dal 1° Maggio)
Secondo me lo stato a fine mese ha una sua rilevanza; ad esempio ci sono mesi in cui mettiamo al gg 10 hai sforato ma poi a fine mese sei ampiamente in budget per non dire in attivo.
Formattando (con formattazione condizionale) i dati di fine mese avresti la chiara evidenza dell'eventuale sforo e del dato finale.
2) Si possono esaminare sullo stesso foglio più anni assieme? Ho notato che la formula si ferma al 365° giorno per cui se io ad es in A e B oltre al 2017 ci metto anche il 2018 e copio le formule anche nel 2018, l'analisi non viene fatta.
Finora ho immaginato un riepilogo annuale; quanto proposto funziona se non ci sono Mesi appartenenti a due anni diversi.
Possiamo risolvere sia il discorso del dato di fine mese che l'elenco multi-anno usando in D3 la seguente formula:
- Codice: Seleziona tutto
=SE(E(SOMMA(SE(TESTO($A$2:$A3;"aaaamm")=TESTO(A3;"aaaamm");$C$2:$C3;""))>$E$2;A3<A4;SOMMA(($D$2:D2)*(TESTO(A$2:A2;"aaaamm")=TESTO(A3;"aaaamm")))=0);SOMMA(SE(TESTO($A$2:$A3;"aaaamm")=TESTO(A3;"aaaamm");$C$2:$C3;""));SE(E(MESE(A3)<>MESE(A4);SOMMA((D$2:D2)*(TESTO(A$2:A2;"aaaamm")=TESTO(A3;"aaaamm")))=0);SOMMA(SE(TESTO($A$2:$A3;"aaaamm")=TESTO(A3;"aaaamm");$C$2:$C3;""));0))
Sempre da confermare con Contr-Maiusc-Enter, e poi da copiare verso il basso.
Tuttavia in questo modo la velocita' di calcolo si riduce sensibilmente man mano che l'elenco si allunga.
Pertanto il mio suggerimento e' di sostituire le formule con una macro, da avviare quando serve e che si occupa di inserire i marker di overbudget o di fine mese dove serve.
Il codice della macro:
- Codice: Seleziona tutto
Sub CkBdg()
Dim WArr, RArr(), I As Long, cM As Integer, cY As Integer
Dim LY As Long, HY As Long, BuMen As Single, curD As Date
Dim bFlag As String, LastA As Long
'
bFlag = "F" '<<< La colonna in cui si marchera' l'OverBudg
BuMen = Range("E2").Value '<<< La cella col budget da verificare
'
LastA = Cells(Rows.Count, 1).End(xlUp).Row
Cells(2, bFlag).Resize(LastA + 10, 1).ClearContents
LY = Year(Application.WorksheetFunction.Min(Range("A:A")))
HY = Year(Application.WorksheetFunction.Max(Range("A:A")))
ReDim RArr(LY To HY, 1 To 12, 1 To 2)
'
For I = 2 To LastA
If IsDate(Cells(I, 1)) Then
curD = Cells(I, 1)
If Month(curD) <> cM And cM <> 0 Then
If RArr(cY, cM, 2) <> 1 Then
Cells(I - 1, bFlag).Value = RArr(cY, cM, 1)
End If
End If
cM = Month(curD)
cY = Year(curD)
RArr(cY, cM, 1) = RArr(cY, cM, 1) + Cells(I, 3).Value
If RArr(cY, cM, 1) > BuMen And Cells(I, 1) <> Cells(I + 1, 1) Then
If RArr(cY, cM, 2) <> 1 Then
Cells(I, bFlag).Value = RArr(cY, cM, 1)
RArr(cY, cM, 2) = 1
End If
End If
End If
Next I
Beep
End Sub
Il codice va messo in un "modulo standard" del vba; per questo, partendo da Excel:
-premi Alt-F11 per aprire l'editor delle macro
-Menu /Inserisci /Modulo
-Copia il codice e incollalo nel frame dx del modulo appena creato
Personalizza le righe marcate <<< come da commenti; il particolare la colonna in cui si vuol marcare l'OverBudget (io ho usato F per poter confrontare i risultati con le formule di colonna D)
Poi torna su Excel e, una volta che il contenuto del foglio e' pronto, lancia la macro CkBdg:
-premi Alt-F8
-seleziona CkBdg dall'elenco di macro disponibili
-premi Esegui
Eventualmente posizioni un "Pulsante" sul foglio e gli associ la Sub CkBdg, in modo che all'occorrenza bastera' premere il pulsante per eseguire il controllo.
La macro impiega max 1-2 decimi di secondo a eseguirsi; quindi si potrebbe anche eseguire automaticamente (senza che questo crei disturbo) tutte le volte che viene modificato il contenuto di colonna A oppure colonna C.
Se vuoi questo automatismo:
-tasto dx sul tab col nome del foglio su cui lavori; scegli Visualizza codice
-copia questo codice e incollalo nel frame vuoto del vba che viene cosi' aperto:
Private Sub Worksheet_Change(ByVal Target As Range)
- Codice: Seleziona tutto
If Target.Count = 1 Then
If Target.Column = 1 Or Target.Column = 3 Then
Application.EnableEvents = False
Call CkBdg
Application.EnableEvents = False
End If
End If
End Sub
A questo punto tutte le volte che modifiche un (1 solo) valore in colonna A (data di inizio) o colonna C (valore) la macro si esegue e inserisce i marker nella colonna prescelta.
Nota che la macro non si esegue se modifiche piu' celle contemporaneamente (es cancelli "alcune" celle) o se modifichi il valore del budget mensile. In questo caso devi procedere con l'esecuzione della CkBdg come detto prima (pulsante o Alt-F8).
Dopo l'eventuale inserimento delle macro il file va salvato con l'estensione .xlsm (macro enabled)
Ciao
Registrazione
ora è perfetta.