Può capitare di voler fare in modo che ci siano all’interno del foglio delle scritte del tipo:

– il totale dei ricavi è 1.344,34

– L’incidenza di xyz è del 57,32%

On verità la formula più semplice non funziona. Immaginiamo di avere nella cella A1 il valore 57,32%. nella cella A2 potremo scrivere:

=”L’incidenza di xyz è del “&A1

o alternativamente:

=CONCATENA (“L’incidenza di xyz è del “;A1)

Il risultato sarà:

L’incidenza di xyz è del 0,5732

E’ necessario formattare i numeri. La funzione da usare è TESTO (in inglese TEXT) usando come formato quello voluto, per intenderci, quello presente nella cella che si vuole “replicare” nel testo:

=”L’incidenza di xyz è del “&TESTO(A1,”#.##%”)

se si vogliono due decimali nella percentuale.

I formati sono disponibili premendo CRTL+1 nella cella excel interessata:

Altri esempi sono i seguenti:

e altri, anche stravaganti, si possono creare ad-hoc.

potete vedere un elenco di possibili scelte nel primo capitolo del manuale excel che abbiamo pubblicato

formati_concatena

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:

Indiretto_01

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.

Calendario_automatico

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

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

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.

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