Gestione Entrate - Uscite (su un unico foglio). - dal 04/09/04 pagina vista: volte

Continuando i suggerimenti su come impostare e gestire il nostro "libro cassa" visto nell'esercizio precedente "Entrate - Uscite (Convalida)", vediamo come modificarlo per realizzare in un unico foglio tutta la gestione. Ripeto che l'esercizio può essere usato o modificato per esigenze diverse, per esempio per ottenere tutti i movimenti relativi ad un Agente, oppure ad un Cliente o un Fornitore, basterà modificare le voci di imputazione contenute nelle due colonne I e K.

Sfrutteremo quindi le basi già impostate, aggiungendo alcune cose:

  • una colonna con i nomi del "mese" relativo alla data dell'operazione - impostazione necessaria visto che le celle D2 e D3 riporteranno solo il totale di tutte le operazioni svolte, ma avremo sicuramente bisogno anche di reperire i totali di ENTRATE o USCITE solo di un determinato mese.

  • Useremo altre due celle, la E2 e la E3, dove inseriremo due formule matriciali che sfruttino le funzioni =SOMMA e =SE cercando due criteri: la "E" di Entrate (o la "U" di uscite") nella colonna B, e il nome di un mese che avremo selezionato da una Convalida posta in D1 (vediamo più avanti questo particolare), nella colonna F dove avremo in automatico il nome del mese relativo alla data che scriveremo in A. Questo ci consentirà, una volta selezionato un mese, di avere il totale Entrate oppure il totale Uscite solo per il mese scelto. Poichè sicuramente vorremmo anche vedere tutte le operazioni svolte nel mese scelto, usiamo due macro attivabili con due pulsanti, che filtreranno tutte le operazioni (E oppure U) svolte nel mese.

  • Operazione simile (formule matriciali) la faremo con le celle F5 e F6 che ci consentiranno invece di ottenere, per un determinato mese, solo i total relativi ad una Voce di imputazione selezionata nelle celle D5 (entrate) o D6 (uscite).

Avremo un prospetto del genere:

Nella colonna F noi vediamo i nomi dei mesi, relativi alla data che scriveremo nella stessa riga,ma nella colonna A. Per ottenere il nome del mese, in automatico, sarebbe stato sufficiente porre, a partire dalla cella F9 (e poi "trascinando") la seguente formula: =SE(A9="";"";MESE(A9)) che restituisce il "numero" del mese se la cella A9 è diversa da vuoto; ma questo avrebbe restituito il numero del mese (per esempio 4 per Aprile, o 9 per Settembre), ma volendo visualizzare il nome del mese abbreviato (gen o feb, ecc.) avremmo dovuto formattare le celle in F a "personalizzato" e come formato aver scelto "mmm". Oppure una formula più semplice ancora =A9, scegliendo comunque il formato personalizzato appena detto.

Noi avremmo visto il nome del mese corrispondente alla data, ma Excel NO. Ricordo che una formattazione è SOLO il modo in cui Excel ci mostra (visualizza) un dato di una cella, ma non modifica il contenuto reale, che sarebbe stato, per la cella F9, 1 oppure 05/01/04 con le due formule viste sopra.

Questo avrebbe provocato un risultato errato nelle formule matriciali (nelle celle E2,E3 e F5,F6) che cercando in F il criterio "nome mese" che selezioneremo in D1, NON troverebbero corrispondenza: infatti in D1 troveremo i nomi mese in forma abbreviata (gen,feb,mar, ecc.).

E' chiaro che potevamo accontentarci di vedere i mesi tramite il numero del mese (con l'uso della prima formula vista sopra), e in D1 usare i numeri anzichè i nomi abbreviati, le formule avrebbero funzionato egregiamente. Ma lo scopo dell'esercizio è quello di abituarci anche a trovare soluzioni alternative, specie se servono ad ottenere ciò che vogliamo.

Allora come procediamo? Semplice:

  • usiamo la colonna G (dalla G9 in giù) dove porremo la formula  =SE(A9="";"";MESE(A9))  e trascineremo fino alla riga finale del nostro schema (ho previsto 700 righe), e questo ci restituisce il numero del mese, lasciando il formato celle a "Generale". Poi impostiamo il colore carattere a "bianco" così non vedremo un'altra fila di numeri.

  • nella colonna F inseriamo, a partire da F9, la funzione =INDICE che, "pescando" in numero che sarà in G9, in una tabellina di conversione che creeremo nella zona "imputazioni", fuori vista. Questa funzione restituirà il nome del mese corrispondente al numero presente in G9. Vediamo la tabellina:

  • e questa la formula che inseriamo in G9 : =SE(A9="";"";INDICE($N$10:$O$21;G9;2)) - che dice: se A9 è vuota, allora mi lasci G9 vuota, altrimenti usa la funzione INDICE che cercherà nella prima colonna a sinistra dell'intervallo (assoluto)  N10:O21 (quindi da N10 a N21) il numero che si troverà in G9, e mi restituisci ciò che trovi, ma nella colonna 2, cioè il nome mese corrispondente al numero trovato.

  • Dalla stessa tabella (da O9 a O21) otterremo "l'origine" dell'elenco di Convalida che avremo posto in D1.

Ora vediamo le quattro nuove formule matriciali: (ricordo che le matriciali si inseriscono con la combinazione dei tasti Ctrl + Maiusc + Invio al posto del solo Invio)

  • in E2 questa formula: {=SOMMA(SE((B9:B700="E")*(F9:F700=D1);D9:D700;0))} - la formula sommerà nel Range D9:D700 tutti i valori  che corrisponderanno alla lettera "E" nel range B9:B700 e solo se, stessa riga della "E", ma nel Range F9:F700 ci sarà lo stesso mese che avremo selezionato in D1, altrimenti facciamo restituire zero (questo nel caso che in D1 non sia selezionato nessun mese), esempio:  D1 è vuoto e E1 è zero

se invece selezioneremo un mese, esempio sotto (in D1 abbiamo Convalida), otterremo il totale delle voci Entrate per il mese scelto, così: sotto vediamo il menù di Convalida e appena scelto "feb" otteniamo sia il totale Entrate del mese, in E2, e contemporaneamente in E3 il totale del mese per le Uscite.

Chiaramente in E3 avremo posto una formula matriciale uguale alla precedente, solo che cambierà il primo criterio di ricerca; faremo cercare una "U" nella colonna B anzichè una "E".

Stesso discorso sulle matriciali poste in F5 e in F6, che restituiranno, per un mese selezionato in D1, il totale della voce di imputazione che avremo scelto in D5 e/o in D6. In pratica, se vorremo i totali per mese, o nel mese il totale per voce, dovremo prima sempre impostare in D1 il mese voluto. Comunque queste le formule:

  • in F5 :  {=SOMMA(SE((E9:E700=D5)*(F9:F700=D1);D9:D700;0))} - facciamo cercare la voce che sarà in D5 (voci delle Entrate) nel Range E9:E700, e il mese che sarà in D1, nel Range F9:F700.

  • in F6 : {=SOMMA(SE((E9:E700=D6)*(F9:F700=D1);D9:D700;0))} - e qui facciamo come sopra ma cercando la voce che sarà in D6 (voci delle Uscite).

Dovremo a questo punto, proteggere le formule matriciali da errori di digitazione successivi (premere Invio dopo aver selezionato una cella che contiene una matriciale corrisponde ad annullarla come matriciale, e non funzionerebbe più); per questo toglieremo il segno di spunta alla voce "bloccata" di Formato Celle/Protezione a tutte le celle che appartengono all'area dati, da A9 a E700 (oppure fino alla riga che vorrete), e anche all'area che contiene le voci di imputazione (colonne I e K dove potremo aggiungere o modificare le voci presenti), e poi applichiamo "Protezione Foglio". Tutte le altre celle non sbloccate saranno così protette da errori o cancellazioni.

Dovremo però modificare le nostre routines vba che si occupano della filtrazione, visto che applicare il filtro su un foglio protetto è impossibile e si genererebbe un errore. E' sufficiente, via vba, togliere la protezione prima dell'applicazione del filtro, e ripristinarla alla fine della routine, così:

  • Sub Toglifiltro()
    ActiveSheet.Unprotect     
    'togliamo la protezione al foglio attivo
    Range("A8:F8").AutoFilter    
    'applichiamo il filtro
    [G7].Select         
    ActiveSheet.Protect        
     'rimettiamo la protezione
    End Sub

Trovate comunque tutte le routine modificate rispetto al file dell'articolo precedente, insieme a tre nuove routine, nel file da scaricare:

  • 1 macro per "pulire" le celle che portano il mese scelto e le voci di imputazione scelte

  • due macro per l'applicazione del Filtro per ottenere una filtrazione per tutte le Entrate o tutte le Uscite di un mese scelto.

file scaricabile e consultabile:        EntrateUsciteUnico.zip         74 Kb  

 

Buon lavoro.

prelevato sul sito www.ennius.altervista.org