Una interessante pagina che spiega in un secondo il funzionamento di CERCA.VERT (in inglese VLOOKUP)
Microsoft Excel – VLOOKUP | Yoda Learning.
Complimenti a Yoda Learning! Ottimo lavoro!
CERCA.VERT – VLOOKUP | Yoda Learning
Rispondi
Archivi autore: Andrea
la funzione INDIRETTO e la funzione INDIRIZZO
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:
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.
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:
VBA Macro: piccolo tutorial per creare le Macro con il registratore
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:
Dopo aver cliccato su questa voce di menu, comparirà una nuova finestrella come questa:
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:
Bene, ora premete ALT – F11 e aprite l’ambiete di VBA. Cliccate sul Modulo1 appartentente al vostro foglio di lavoro:
e comparirà nella finestra del codice la macro che avete registrato:
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
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:
A questo punto nella schermata di destra bisogna utilizzare i due menu a tendina per selezionare il seguente evento:
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
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:
A questo punto nella finestra che compare bisogna selezionare il livello di protezione Medio:
Un calendario automatico con gestione delle festività
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:
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:
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.
Se volete poi acquistare qualche libro che vi aiuta sulle formattazioni condizionali qui qualche consiglio:
Manuale Excel in 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
Parte 2 – Analisi di sensitività:
– Analisi per scenario
– Tabelle mono e bi-variate
Parte 3 – Strumenti di ottimizzazione:
– Ricerca obiettivi (GOAL SEEK)
– Risolutore (SOLVER)
Parte 4 – Altre funzioni:
– Tabelle Pivot
– Regressioni (lineari e non)
La pianificazione economica per il Business Plan
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.
Come utilizzare la funzione CERCA.VERT senza incorrere nel fastidioso #N/D
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:
- di evitare di riempire il foglio di lavoro con fastidiosi messaggi di errore di Excel,
- di svincolare il complesso delle formule che costituiscono il modello Excel dalla contingenza dei dati
- di consentire una forte riduzione del tempo necessario per la manutenzione del modello stesso ogni volta che vengono aggiornati i dati
- di ridurre la probabilità di errore
I grafici Gantt in Excel
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à.
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à.