la funzione INDIRETTO e la funzione INDIRIZZO

Download PDF

La funzione INDIRETTO permette di recuperare un dato presente in un’altra cella del foglio di lavoro indicandone direttamente l’indirizzo della stessa.

 

La procedura più semplice è scrivere:

=INDIRETTO("A1")

questa funzione EQUIVALE a scrivere

=A1

Vista semplicemente in questo modo la funzione INDIRETTO sembra assolutamente inutile.

In realtà aggiunge un livello di flessibiltà interessante ad Excel. Se infatti si costruisce in modo furbo la stringa che rappresenta la cella all’interno di INDIRETTO è possibile recuperare in modo rapido informazioni che sono disperse nel file Excel; è sufficiente che queste siano distribuite sul foglio con uno schema.

Per usare INDIRETTO bisogna conoscere INDIRIZZO

Per poter usare efficacemente la funzione INDIRETTO è necessario costruire il nome delle celle da cui fare riferimento in modo semplice.

Ci viene in contro la funzione INDIRIZZO: questa funzione, inserendo il numero di riga, il numero di colonna e il nome del foglio costruisce il nome della cella:
Ad esempio la formula

INDIRIZZO (1;1;1)

restituisce il valore A1

il primo 1 indica la riga (la prima appunto), il secondo 1 indica la colonna (la A) e il terzo uno indica che la formula deve essere calcolata usando il riferimento assoluto. Si tratta in sostanza di una funzione che gioca a battaglia navale sul foglio Excel.

La combinazione di INDIRETTO e INDIRIZZO può fare cose molto interessanti.

Vediamo ora un caso pratico:

Se per caso vi ritrovate con un file Excel che è strutturato come nell’immagine seguente:

indiretto_01

e volete riportare i dati nel foglio di sintesi dovete fare un sacco di collegamenti del tipo:

=attivita!B3
=attivita!D5
etc..

La cosa diventa complicata e noiosa se ci sono tanti link da fare. In certi casi potrebbero essercene anche centinaia.

Se si osserva il file però si nota una certa regolarità nelle posizioni delle celle dove si vogliono raccogliere i dati.
Il nome dell’attività è spaziato ogni 6 righe: lo ritroviamo infatti alla riga 3, alla 9, alla 15 etc.
Rispetto alla posizione del nome dell’attività, il primo numero da riportare si trova due righe più in basso e due colonne più a destra.
Il successivo numero si trova una riga sotto e l’ultimo numero si trova ancora una riga più sotto.

Vediamo come costruire con INDIRETTO e INDIRIZZO una formula che possa permettere di raccogliere tutti questi numeri in una tabella:

Per prima cosa partiamo dalla funzione INDIRIZZO:
la formula =INDIRIZZO(3;2;1;;”attivita”) permette di calcolare l’indirizzo della cella alla riga 3, colonna 2 del foglio attivita. Il suo risultato sarà:

“attivita!$B$3″

Ora con la funzione =INDIRETTO(INDIRIZZO(3;2;1;;”attivita”)) potremo recuperare il dato presente in questa cella (cfr immagine sopra) e ottenere il testo “Attività A”
Sostituiamo ora al numero di riga (3) il riferimento a una cella che conterrà il dato 3. Apparentemente non cambia nulla ma a questo punto potremo trascinare la cella che contiene INDIRETTO verso il basso e scrivere nella nuova cella di riferimento il valore 9 invece che 3.

In qeusto modo la seconda formula INDIRETTO (identica alla prima) recuperà il dato dalla cella B9 invece che B3.

indiretto_02

Per poi recuperare gli altri dati basterà ad esempio inserire la formula:

=INDIRETTO(INDIRIZZO($A5+2;4;1;;”attivita”))

che rispetto alla precedente recupera il dato che si trova 2 righe più sotto alla formula precedente e alla colonna D (numero 4) dello stesso foglio.
Giocando con questo tipo di logica potremo rapidissimamente riorganizzare i dati in forma tabellare (l’unica che dovrebbe invero essere utilizzata!!)

si veda il file allegato di esempio:

Indiretto_01

VBA Macro: piccolo tutorial per creare le Macro con il registratore

Download PDF

Excel dispone di un eccellente strumento per registrare le operazioniche vengono compiute sul foglio di lavoro, memorizzarle in una macro e renderle disponibili all’utente in un momento successivo.

In questo modo se l’utente ha una sequenza standard e ripetitiva di operazioni da compiere che lo impiegano per molto tempo, può registrarle una volta e poi lanciare il la macro le volte successive, Excel le eseguirà a velocità elevatissima facendo risparmiare molto tempo all’utilizzatore.

Un altro utilizzo molto comodo del registratore nasce dal fatto che le Macro così generate sono visulaizzabili e modificabili. E’ un ottimo modo utilizzare il registratore per generare delle porzioni di codice che verranno poi utilizzate nelle macro che l’utente si farà da sé.

Vediamo come funziona il registratore di macro:

Macro_registra_1

Dopo aver cliccato su questa voce di menu, comparirà una nuova finestrella come questa:

Macro_registra_2

Qui potete definire il nome della macro e dove essa sarò registrata. Una volta dato Ok comincerete ad eseguire tutte le isruzioni (e solo quelle!) che vorrete registrare e che quando lancerete la macro saranno ripetute. Terminate le operazioni cliccate sul bottone che interrompe la registrazione:

Macro_registra_3

Bene, ora premete ALT – F11 e aprite l’ambiete di VBA. Cliccate sul Modulo1 appartentente al vostro foglio di lavoro:

Macro_registra_4

e comparirà nella finestra del codice la macro che avete registrato:

Macro_registra_5

A questo punto, ogni volta che lancerete la macro essa rieseguirà esattamente le stesse operazioni che avete eseguito voi in fase di registrazione

 

 

VBA Macro: eseguire una macro automaticamente all’apertura del foglio Excel

Download PDF

Può capitare di volere eseguire una macro all’apertura di un foglio excel in modo automatico.

Ad esempio si vuole che la pivot table che è presente nel foglio sia sempre aggiornata oppure che venga aggiornato il fornitore di dati esterno.

Per poterlo fare bisogna programmare l’evento che Excel esegue all’apertura di ogni file.

Questo si chiama  Workbook_Open()

Normalmente per inserire una macro in un foglio Excel bisogna inserire un Modulo. Per gestire questo evento invece NON SI DEVE creare un nuovo modulo ma cliccare sull’icona ThisWorkbook del menu di navigazione come indicato in figura:

esegui_macro_avvio-1

A questo punto nella schermata di destra bisogna utilizzare i due menu a tendina per selezionare il seguente evento:

esegui_macro_avvio-2

Bene! Ora possiamo inserire nel corpo dell’evento il codice della macro che vogliamo eseguire all’avvio.

E’ sempre bene prima provare il codice in una maacro “normale” (per intenderci quelle inserite normalmente nei moduli) e solo quando funziona copiarla in questo evento.

Salvate il file, chiudetelo e riapritelo. Abilitate le macro se compare la schermata che vi chiede ciò e verificate se la macro ha fatto quanto richiesto.

VBA Macro: Come abilitare le Macro VBA in Excel

Download PDF

Se le Macro VBA (anche quelle scaricate da questo sito) non funzionano probabilmente ciò è dovuto alla protezione di Excel.

Poiché ci sono alcune macro che possono essere potenzialmente pericolose, Excel permette di gestire più livelli di protezione. Quando viene installato il livello di protezione è sempre “Alto” che significa che quando viene aperto un file contenente le macro, queste vengono automaticamente disabilitate senza nemmeno avvertire l’utente. Questi quindi si ritrova in una situazione in cui pensa di avere delle funzionalità che invece Excel ha deciso di negargli.

Per cambiare la situazione bisogna modificare le impostazioni di protezione presenti in questo menu:

abilita-macro1

A questo punto nella finestra che compare bisogna selezionare il livello di protezione Medio:

abilita-macro2

 

Un calendario automatico con gestione delle festività

Download PDF

In questa pagina pubblichiamo la metodologia per creare un calendario automatico in Excel per ad esempio:

  • Gestione di attività
  • Gestione di turni di lavoro
  • Gestione di piani ferie

il file Excel si presenta con due fogli: il primo è un foglio che rappresenta il calendario stesso e il secondo invece contiene i dati di supporto: l’elenco delle festività e i nomi dei giorni della settimana.

Il file è completamente parametrico: è sufficiente inserire il primo giorno da cui si vuol far partire il calendario nella cella blu e aggiornare le date delle festività. Esso presenterà tutte le date in sequenza, per ogni data indicherà il nome del giorno della settimana e indicherà con i colori grigio i sabati e le domeniche e in arancione i giorni festivi:

calendario_automatico_2

Il trucco per realizzarlo è semplicissimo: a parte qualche piccola formula per poter recuperare dal foglio di appoggio i dati necessari sul calendario stesso, si è utilizzato per tutta l’area del calendario una formattazione condizionale fatta in questo modo:

calendario_automatico_1

Dove con tre semplici formule si va ad indentificare, per ogni cella del calendario, se essa è in corrispondenza di una colonna che corrisponde a un sabato, a una domenica o a una festività. In questo caso, se la festività cade di sabato o domenica, essa avrà il colore del sabato o della domenica perché Excel verifica per prima cosa se si tratta di un sabato o di una domenica. Se volete che le festività che cadano di sabato o di domenica siano comunque colorate come festività dovrete cambiare l’ordine dei controlli all’interno della formattazione condizionale.

Calendario_automatico

 

Se volete poi acquistare qualche libro che vi aiuta sulle formattazioni condizionali qui qualche consiglio:

 

Manuale Excel in PDF

Download PDF

In questo documento potrete trovare una serie di metodologie per l’analisi economica (ma non solo).

Il manuale è strutturato per rispondere a domande comuni di specialisti di controllo di gestione e di analisi Marketing.

Insegna principalmente come sfruttare tutte quelle funzionalità che sono già presenti in Excel ma che pochi conoscono.

Il documento è in libera distribuzione. Potete scaricarlo, stamparlo, condividerlo con amici. Non è consentito modificare o eliminare il mio Logo dalle pagine.

Nel caso troviate errori, sarò ben lieto di correggerli e inviarvi la versione corretta.

Il manuale è diviso in 4 sezioni:

Parte 1 – Funzioni di base:
– Concatenazione
– Funzione Se (IF) e condizioni logiche
– Somma.se (SUMIF)
– MATR.SOMMA.PRODOTTO (SUMPRODUCT)
– CERCA.VERT (VLOOKUP e HLOOKUP)
– Arrotondamenti

manuale_excel_1

Parte 2 – Analisi di sensitività:
– Analisi per scenario
– Tabelle mono e bi-variate

manuale_excel_2

Parte 3 – Strumenti di ottimizzazione:
– Ricerca obiettivi (GOAL SEEK)
– Risolutore (SOLVER)

manuale_excel_3

Parte 4 – Altre funzioni:
– Tabelle Pivot
– Regressioni (lineari e non)

manuale_excel_4

 

La pianificazione economica per il Business Plan

Download PDF

Excel, ovviamente, è lo strumento utilizzato per la pianificazione economico – finanziaria di ogni azienda o iniziativa imprenditoriale sul pianeta Terra.

Cosa si intende per pianificazione economico finanziaria?

La definizione è semplice, ma la realizzazione è alquanto complicata. Poi, infine, realizzare quanto pianificato è pressoché impossibile a meno di essere davvero bravi nel business (ma in questo caso, non avrete granché bisogno di pianificare!!)

Si tratta in sostanza del calcolo prospettico dei prospetti economico finanziari aziendali su un arco di tempo che può essere di un anno o più anni. I prospetti economico finanziari aziendali sono:

  • Il conto economico (Profit & Loss)
  • Lo stato patrimoniale (Balance sheet)
  • Il rendiconto finanziario (Cash Flow)

La granularità del calcolo può essere:

  • mensile per pianificazioni di breve periodo, entro l’anno,
  • trimestrale o annuale per pianificazioni su più anni

Normalmente le pianificazioni mensili ad orizzonte annuale vengono detti Budget o Forecast mentre le pianificazioni annuali sono utilizzati per la redazione di Business Plan o per la valutazione di un’iniziativa imprenditoriale.

Un esempio (in inglese) dei contenuti presenti in un Business Plan è ad esempio disponibile a questo indirizzo di “Smal Business Resource

Un semplice strumento di pianificazione finanziaria

E’ infatti noto che i tre prospetti sono legati tra loro in modo indissolubile attraverso il metodo della partita doppia. Ogni scrittura di un evento economico finanziario deve essere fatta due volte su questi prospetti.

Il documento Excel permette di mostrare i tre prospetti e di gestire i seguenti eventi economici:

  • Ricavi con incasso previsto a 0 – 30 – 60 – 90 etc giorni
  • Costi con data di pagamento a 0 -30 -60 -90 etc giorni
  • Costo del personale e TFR
  • Accantonamento a Fondi (rischi)
  • Investimenti e Ammortamenti
  • Debito Finanziario
  • Credito commericale e incassi
  • Debito commerciale e pagamenti
  • Iniezioni di capitale da Soci

Non sono gestite le tasse e il fondo TFR è calcolato in modo molto semplice

Il documento è un’ottima base di partenza che potete arricchire a vostro piacimento

I numeri da modificare sono in blu mentre i numeri in nero sono formule e dunque è bene non toccarli se non si sa esattamente cosa si sta facendo.

Ovviamente potete scaricare e utilizzare il file a vostro piacimento, non ci assumiamo nessuna responsabilità su un uso improprio di questo file.

 CE-SP-CFxBusinessPlan

Come utilizzare la funzione CERCA.VERT senza incorrere nel fastidioso #N/D

Download PDF

La funzione CERCA.VERT è molto utile per incrociare tra di loro piccoli database. Se si hanno database con qualche migliaio di linee da incrociare non usate CERCA.VERT potreste impiegarci molto tempo o forse non riuscirci proprio!

Tale funzione è però un poco ostica. Prima di tutto, se il database su cui si ricerca non è ordinato in senso alfabetico crescente la funzione, se non impostata correttamente può dare dei risultati errati. E’ quindi praticamente sempre necessario inserire il flag che indica la necessità di ricercare la corripondenza esatta. Per farla breve, la tipica sintassi da utilizzare è:

=CERCA.VERT(E13; A2:B100;2;FALSO)

dove:
– E3 indica cosa cercare
– A2:B100 indica dove cercare
– 2 indica quale colonna del range A2:B100 considerare (qui la colonna B)
– FALSO indica che è necessario ricercare la corrispondenza esatta tra ciò che è contenuto in E3 e ciò che si trova nella prima colonna del range A2:B100.
(NOTA: FALSO può essere sostituito con 0)

Il problema nasce subito quando si cerca di elaborare poi i risultati del CERCA.VERT.

Se infatti la ricerca è infruttuosa il nostro CERCA.VERT restituisce, invece che un numero o il dato corretto, un bellissimo #N/D (in ingelse #N/A). Si tratta di un messaggio di errore di Excel. In sostanza ci comunica che la ricerca è stata senza risultato. Il punto è che è ora impossibile applicare formule che coinvolgono la cella che contiene il nostro #N/D.
Qualsisasi formula infatti che interessa la cella che contiene la cella che contiene l’#N/D infatti si trasforma immediatamente in #N/D anch’essa.

La soluzione più banale è quella di cancellare la formula quando essa dà errore. Questo comporta però che il modello Excel realizzato dipende nelle sue formule da come sono fatti i dati (qui la presenza della formula della cella dipende dalla presenza o no della variabile ricercata nei dati). Questo approccio è dannosissimo e fonte di errori. E’ meglio modificare la formula in questo modo:

=SE(VAL.NON.DISP(CERCA.VERT(E3;A2:B100;2;FALSO));0;CERCA.VERT(E3;A2:B100;2;F
ALSO))

Questa formula, per prima cosa verifica se il CERCA.VERT genera l’errore, in questo caso, invece di scrivere nella cella il fastidioso #N/A scrive 0 (o qualsiasi altra cosa volete fargli scrivere!), altrimenti restituisce il valore corretto del CERCA.VERT.

Questa soluzione permette:

  1. di evitare di riempire il foglio di lavoro con fastidiosi messaggi di errore di Excel,
  2. di svincolare il complesso delle formule che costituiscono il modello Excel dalla contingenza dei dati
  3. di consentire una forte riduzione del tempo necessario per la manutenzione del modello stesso ogni volta che vengono aggiornati i dati
  4. di ridurre la probabilità di errore

I grafici Gantt in Excel

Download PDF

In azienda spesso capita di dover pianificare della attività correlate a un prgetto.

 

Normalmente per formalizzare le azioni, le scadenze e i vincoli necessari er la realizzazione di un progetto complesso, nell’ambito del Project managament si utilizzano i diagrammi di Gantt basati sulla logica dei diagrammi di PERT.

 

Normalmente per realizzare un diagramma di Gantt sono necessari software dedicati quali ad esempio VISIO.

Ovviamente questi Software costano e non vale la pena acquistarli se non sono molto utilizzati.

Si può utilizzare, per piccoli progetti, in sua sostituzione Excel con questo piccolo tool che permette, attraverso la formattazione condizionale di realizzare automaticamente i diagrammi. E’ necessario solo indicare la data di inizio e fine di ogni singola attività.

gantt

Cliccare sull’immagine per ingrandirla

Ovviamente, data la sua semplicità non è particolarmente flessibile: non permette ad esempio di inserire milestone (ma basta creare una cella ad hoc in in riga inserendo un carattere!) o di gestire l’avanzamento di ogni singola attività.

Gantt

 

 

Autofiltro con i colori: Tradurre il colore di una cella in un numero

Download PDF

Può capitare a volte di avere a che fare con un database (un insieme di righe) che sono state lavorate da qualcuno e che per indicare i vari stati delle righe, questi ha pensato bene di usare i colori e evidenziare le righe on giallo, rosso etc.

Purtroppo però questa tecnica non permette di effettuare ulteriori elaborazioni sul database: ad esempio è impossibile contare le righe colorate di rosso, evidenziarle o altro.

In questa pagina vi presentiamo una semplicissima macro (è fatta da 2 righe!) che permette di “tradurre” il colore della riga in un numero (il suo codice colore) e di esporlo nella cella. Questa funzione vi è utile ad esempio per introdurre un’ulteriore colonna nel database dove per ogni riga si evidenzia il suo colore. A questo punto, si può utilizzare l’autofiltro, il CONTA.SE, le tabelle pivot e altro ancora basandosi non più sul colore ma su questo codice.

La funzione è la seguente:

Function colorecella(a As Range) As Integer

Application.Volatile

colorecella = a.Interior.ColorIndex

End Function

La prima istruzione (Application.Volatile) dice semplicemente a Excel di ricalcolare la funzione ogni volta che viene eseguito il ricalcolo del foglio (ad esempio con la pressione del tasto F9 o quando si inserisce o si cambia un valore in una cella.

colorecella

Excel infatti, per risparmiare risorse di calcolo, non ricalcola le formule del foglio di lavoro ogni volta che eseguite un comando sul foglio: quando cambiate la formattazione, inserite un bordo, cambiate un colore (!!) Excel NON ricalcola le formule.

Quindi la nostra funzione colorecella() non verrà aggiornata appena cambierete il colore: quando cambiate un colore dovrete poi o premere F9 per forzare il calcolo di tutto il foglio e quindi della nostra funzione, oppure cambiare un valore qualsiasi in una cella, forzando ancora una volta Excel a ricalcolare il foglio di lavoro.

Cliccare sul link sottostante per scaricare il file di esempio:

colorecella