Velocizzare le macro, trucco utilissimo!

by

Se avete macro in Excel che funzionano, ma sono lentissime, una delle possibili ragioni è che nel foglio di lavoro avete molte formule, che si attualizzano mentre la macro compila.

Un´ottima soluzione per ovviare a questo problema è questa macro, che imposta il calcolo manuale nel foglio di lavoro, per poi riattivarlo al termine della subroutine.

 

Sub Vai_al_calcolo_manuale()

Dim xlCalc As XlCalculation

    xlCalc = Application.Calculation

    Application.Calculation = xlCalculationManual

    On Error GoTo CalcBack

                ‘Inserisci qui la tua macro

    Application.Calculation = xlCalc

    Exit Sub

CalcBack:

Application.Calculation = xlCalc

End Sub

 

Usatelo.. e vedrete che differenza!!

 

Attualizzare tutte le pivot contemporaneamente

by

Capita spesso che in un file Excel vengano create diverse tabelle pivot e che in seguito vengano attualizzati i dati nella tabella di origine.

Per risparmiare tempo nel calcolo e memoria, Excel salva i dati di origine e per attualizzare i dati dopo l´inserimento, é necessario posizionarsi sopra la tabella pivot, premere il tasto destro del mouse e selezionare “Aggiorna”.

Se le tabelle pivot nel foglio sono molte e si riferiscono a diversi fogli diversi, è possibile scrivere una semplice macro e con un click si attualizzeranno tutte contemporaneamente.

Premi ALT + F11 e ti troverai in un editor di macro. A questo punto scrivi:

Sub attualizza_pivot ()

ThisWorkbook.RefreshAll

End Sub

Se state usando una versione piú vecchia di Excel, scrivete la seguente macro, ispirata all´articolo su stackoverflow:

Sub attualizza_pivot ()

dim w as worksheet, p as pivottable 

for each w in thisworkbook.worksheets 
for each p in w.pivottables 
p.refreshtable 
p.update 
next 
next

End Sub

Avete in questo modo risparmiato tempo e creato la vostra prima macro!

Logica del formato data in Excel

by

Tratteró nei prossimi post delle funzioni data e ora piú utili. È peró importante conoscere come il formato data é effettivamente strutturato in Excel, in modo da poter maneggiare meglio i possibili problemi che troveremo.

La data in Excel è in realtá un numero decimale: quando impostiamo il formato data, Excel comprende a quale giorno e quale ora ci riferiamo.

La parte intera del numero rappresenta la data, espressa come somma dei giorni trascorsi dal 1 Gennaio 1900, mentre la parte decimale è l´ora, ovvero frazione di giorno (la somma dei secondi trascorsi dalla mezzanotte) a cui l´ora si riferisce.

La data 23/7/2013 ora: 18:00:00 sará quindi 41478.75, ovvero 41377 giorni trascorsi dal 1 Gennaio 1900 al 23 Luglio 2013 e 0.75 corrisponde al rapporto tra 64800 e 86400, ovvero il numero di secondi trascorsi dalle 00:00:00 alle 18:00:00.

Ecco perché quando abbiamo in un foglio dei numeri con unitá in decine di migliaia, capita spesso che siano interpretati da Exel come delle date, ma non vi preoccupate.. anche questo tema sará discusso molto presto!

SE.ERRORE, utilissima funzione per i report

by

Problema

Quante volte vi sará capitato di preparare un report, in cui per esempio vengono usate funzioni di CERCA.VERT (se non la conosci, consulta questo post) o percentuali e vi trovate a combattere con risultati come #N/D, #VALORE!, #RIF!, #DIV/0!, #NUM!, #NOME? o #NULLO, che rendono il report oltre che “brutto” esteticamente, danno problemi nel calcolare somme o altre funzioni con riferimento a queste celle.

Esempio: Un piccolo sito internet ha cominciato la propria attività a Febbraio. Ci viene chiesto di calcolare il Conversion Rate, ovvero il rapporto tra acquisti e visite. Dato che a Gennaio il sito non ha ricevuto visite, troverete nel rapporto un errore nel rapporto.

Soluzione

Excel ha una soluzione anche in questo caso! Con la funzione SE.ERRORE potete per esempio impostare che quando aparisse un errore, al suo posto appaia invece uno “O” o uno spazio vuoto.

Scaricate questo file: EPT_Ex2_SeErrore, vedrete che semplicempente inserendo un SE.ERRORE(Rapporto,O), il nostro problema é stato risolto.

Attenzione

Non abusate di questa funzione: sapere che in una cella abbiamo un errore, puó davvero essere utile per controllare se c´é un errore nella nostra formula che non avevamo considerato, lo dico per esperienza.

Cerca Verticale, una funzione..molto tempo risparmiato!

by

Problema

Mi è successo molte volte di copiare dati su diversi fogli diversi di un foglio di calcolo e di avere un ulteriore foglio in cui utilizzavo solo una parte di queste informazioni.

Mi spiego meglio: mettiamo di avere un foglio chiamato Comuni, in cui si trova l´elenco di tutti i comuni d´Italia con i relativi CAP, un secondo foglio chiamato Regioni in cui sono elencati tutti i comuni d´Italia con le relative province e regioni.

Nella schermata principale Overview, vengono listati alcuni comuni predefiniti. A questi ci viene chiesto di associarne la regione, provincia e CAP.

Domanda

Come posso collegare informazioni presenti in due fogli di lavoro separati, CAP nel foglio Comuni, Regioni e Province nel foglio Regioni e comuni, informazione presente in entrambi i fogli.

Risoluzione

  • Scaricate il file EPT_Ex1_CercaVert
  • Vedrete che per risolvere l´esercizio ho inserito nel foglio Overview nelle colonne D, E e F delle funzioni di CERCA.VERT, usando come valore la colonna B, dove sono elencati i comuni, matrice, tutti i dati nei rispettivi fogli Regioni e CAP, indice, numero della colonna in cui sta il valore che vogliamo richiamare (regione ->3 nel foglio Regioni, provincia ->2 nel foglio Regioni, CAP -> 2 nel foglio CAP) e intervallo (in questo caso si potrá scegliere fra VERO, che restituirá una approssimazione e FALSO, che restituirá la corrispondenza esatta).

Attenzione

  • La funzione CERCA.VERT funziona solo quando il valore chiave, ovvero quello che é presente in entrambe le tabelle che vuoi trovare si trova sulla prima colonna della tua matrice: la funzione ti dará errore se il valore che vuoi trovare si trova alla sinistra del valore chiave.
  • La matrice deve contenere i dati che stai cercando: l´indice della CERCA.VERT che vuoi mettere deve essere inferiore o uguale al numero di colonne comprese nella matrice che hai selezionato.

 

Altre Annotazioni

Scoprirai che la funzione CERCA.VERT ti aiuterà a risparmiare tempo e energia: tutto quello che avresti cercato a mano, adesso potrá essere semplicemente risolto con una funzione.. Giocaci e prova con degli esempi che puoi costruire tu. Ti do qualche idea: scrivi in un foglio il nome di persone che conosci e il loro indirizzo e in un altro il loro nome e il loro numero di telefono. Costruisci poi una funzione CERCA.VERT per avere su un´unica linea, tutti e 3 gli elementi. Se hai bisogno di una mano, ti aiuto volentieri!

 

Excel..solo una tabella da completare?

by

Excel piú che un programma é un mondo: si puó usare come semplice tabella in cui inserire dati e eseguire semplici operazioni e grafici o si puó usare come vero e proprio database in cui collezionare dati, eseguire operazioni piú complesse con degli script (macro) e collegare dati differenti per evitare le operazioni sempre uguali che ci fanno perdere tempo e concentrazione.

In questo blog, cercheró di raccontarvi alcuni problemi che ho avuto nella mia giornata e li commenteró, dandovi la soluzione che (non) ho trovato. Spero che questo blog vi sia utile e per qualsiasi cosa che non sia chiara, commentate o scrivetemi all´indirizzo broggichiara88@gmail.com.

Buona giornata!