Gestione Entrate

Tra le molte possibilità di impostazione di fogli, tabelle schemi e varie per la gestione dei nostri lavori, questa volto presento una soluzione adattabile ad una gestione di Entrate-Uscite, ma l'esempio potrà essere adattato ad altri impieghi.

Questo progettino si sviluppa attraverso l'impiego sia di codice vba, sia di formule, e sfrutteremo Convalida, sia per ottenere le voci di un'elenco, sia per impostare condizioni nella selezione (applicazione di un filtro) dell'area che contiene l'elenco.

Vogliamo, in pratica, gestire sia le Entrate, sia le Uscite, poste in una unica colonna, ma avere la possibilità sia di ottenere il totale Entrate separato dal totale Uscite.

Useremo imputare per ogni operazione che registreremo, il "gruppo" di spesa o di entrata relativo all'operazione, un pò come si usa (o usava) fare quando si tenevano i "mastrini". Questo ci consentirà, una volta selezionato un "gruppo" (Entrate o Uscite, è indifferente) di ottenere il totale solo per il "gruppo" scelto.

Ho preparato un esempio su un'ipotetica gestione Cassa di un Istituto Religioso, per cui le voci di imputazione Entrate/Uscite rispecchieranno le necessità legate a questo tipo di attività. Chiaramente le voci usate sono modificabili, ampliabili (lasceremo per ogni gruppo di imputazioni delle celle in bianco per le aggiunte di voci).

Vediamo ora un'immagine del foglio "Gen" (useremo tanti fogli, rinominandoli, per quanti sono i mesi dell'anno), con la struttura usata.

Nell'area compresa tra le celle A1- B7 ho sistemato la zona dei totali, che vedremo più avanti, ed ho impostato "Blocca riquadri" per la riga 8.

Esaminando l'area dati, che impegna le colonne dalla A alla E, partendo dalla riga 9, troviamo che le celle delle colonne B ed E contengono Convalida impostata ad "Elenco". (vedi sull'altro sito l'articolo "Usare: CONVALIDA"): inserita Convalida nella rispettiva cella iniziale (B9 ad esempio), si userà il "trascinamento" per inserirla anche nelle celle sottostanti, fino a dove vorremo. (Lo stesso faremo con la cella E9). Chiaramente ogni colonna di Convalida "pescherà" i dati in un elenco appositamente predisposto. Ma prima di vedere le aree che formano l'elenco di ogni Convalida, dobbiamo fare un presupposto:

abbiamo detto che vorremo usare una sola colonna dove scrivere gli importi (colonna D), sia che si tratti di Entrate sia di Uscite; esistono però voci di imputazione che possono essere identiche sia che si tratti di esborsi (Uscite) si che si tratti di introiti (Entrate), ad esempio, la voce "beneficenza" la si può fare (uscita) o la si può ricevere (entrata); per determinare a quale "causale" corrisponde, ho usato quindi la colonna B, dove per ogni "movimento", dopo aver scritto la data, sceglieremo tramite Convalida, se si tratta di "E" (entrate) oppure di "U" (uscite). (Non ho ritenuto opportuno usare un solo elenco, ma due, per gestire le voci che formeranno le origini degli elenchi delle Entrate e delle Uscite).

E' in funzione di questa scelta che varieremo l'elenco che troverà Convalida della colonna E, e che ci si renderà disponibile quando selezioneremo la cella E della riga corrispondente. Ma vediamo in dettaglio cosa succede variando la "causale" di imputazione: scegliamo la riga 14 ed impostando B14 ad "U", troviamo in E14 questo elenco che è delle "Uscite":

Se invece in B14 avessimo selezionato la "E" in E14 troveremmo l'elenco "Entrate", così:

Per ottenere questo NON sostituiamo la Convalida presente nella cella E14 (o in tutte le altre celle di E), ma abbiamo impostato un'istruzione condizionale usando la funzione =SE.

Vediamo ora, per meglio capire, dove sono inserite le tabelle che contengono gli elenchi usati da Convalida:

Ho usato questa zona del foglio (ma ognuno deciderà dove porle), in queste colonne a partire dalla riga 9 e fino alla 35. Questi sono gli elenchi con le voci relative alle causali di imputazione.

Quindi in Convalida posta nella colonna B, usiamo "elenco" che avrà come "origine" l'intervallo =$M$9:$M$11 e ci restituirà una cella vuota, oppure "E" o "U", mentre in Convalida posta nella colonna E, a partire da E9, come "origine" dell'elenco scriveremo questa formula:

e questa l'immagine della finestra di Convalida: (l'istruzione condizionale non si legge tutta, ma c'è)

Passiamo ad esaminare la zona iniziale, quella dove otteniamo i totali; qui useremo sia formule, sia Convalida abbinata però ad istruzioni vba.

Queste formule ci daranno il totale per uscite e/o entrate, ma se volessimo sapere quanto è il totale per una determinata voce? Potremmo applicare un filtro sulla voce desiderata, e poi chiedere i SubTotali. Io ho seguito una strada un pò diversa:

ho predisposto due celle, la D5 e la D6 con Convalida, rispettivamente con origine dati da Entrate e da Uscite; nelle celle E5 e E6 due formule con SOMMA.SE che usando come criterio ognuna la cella D5 o D6, restituiscano il totale relativo alla voce selezionata in Convalida. Queste le due formule:

Però mi sembrava una soluzione semplice e poco dimostrativa, allora ho pensato di associare, sfruttando l'evento Change del Worksheet, una routine che attivasse anche un filtro il cui criterio di filtrazione fosse rappresentato dalla voce selezionata in Convalida di D5 o D6: in questo modo otteniamo il totale relativo alla voce selezionata e contemporaneamente possiamo visualizzare tutte le pertinenze che concorrono a formare il totale per effetto del filtro. Per poter far intervenire le istruzioni poste nell'evento Change solo se si seleziona una delle due celle D5 o D6, ho usato il metodo Intersect che consente di definire le celle di cui ci interessa verificare il Change, non intervenendo se avremo cambiamenti sulle altre celle del foglio.

Il tutto deve essere gestito un modo duplice; cioè deve funzionare sia che si operi sulla cella D5 (convalida per le Entrate), sia sulla cella D6 (convalida per le Uscite). Per questo uso due macro esterne da chiamare quando si attiva il Change, usando due controlli If per identificare quale delle due celle è stata cambiata nel valore. Vediamo un esempio in cui abbiamo selezionato la voce "Beneficenza" in Convalida D5, e l'effetto filtro

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("D5:D6")) Is Nothing Then Exit Sub 'si controlla che le istruzioni 'vengano eseguite solo se cambia una delle due celle D5 o D6, altrimenti si esce dalla routine

With ActiveSheet 'ora si controlla se il Filtro è attivato, se è attivato verrà chiamata la macro 'TogliFiltro (che vedremo più sotto) per togliere il filtro
If .AutoFilterMode Then
TogliFiltro
End If

If Target = [D5] Then 'se avremo cambiato una voce nella cella D5 chiamiamo la macro "Entrate"
Entrate
End If

If Target = [D6] Then 'se invece la avremo cambiato nella cella D6 chiamiamo la macro "Uscite"
Uscite
End If

End Sub

Sub Entrate()

Scelta2 = [D5].Value
Range("A8:E8").Select
Selection.AutoFilter

With Selection
.AutoFilter Field:=5, Criteria1:=Scelta2
End With

End Sub

'----------------------------------------------------------------
Sub TogliFiltro()
Range("A8:E8").AutoFilter
[G7].Select 'cella che rimane coperta dal pulsante "Togli Filtro"
End Sub

'----------------------------------------------------------------
Sub Uscite()

Scelta = [D6].Value
Range("A8:E8").Select
Selection.AutoFilter

With Selection
.AutoFilter Field:=5, Criteria1:=Scelta
End With

End Sub

Il pulsante associato alla macro TogliFiltro() servirà quando è necessario togliere il filtro senza ricorrere a selezionare una delle due celle D5 o D6.

Se avremo avuto l'accortezza di predisporre tutto lo schema sul foglio1, potremo copiare il foglio (per ogni copia verrà copiato anche il codice vba scritto nel modulo foglio) per quanti mesi formano l'anno, rinominando i fogli con i nomi dei mesi e modificando pure il nome mese riportato in C1, poi useremo un foglio "Anno" per ottenere il riepilogo di tutte le spese e le entrate dell'anno.