Nel caso tu abbia bisogno di prevedere l’andamento futuro dello spread, la procedura è la seguente:
Enjoy!
Mi capita molto spesso di vedere utenti di Excel alle prese con tabelle come quella di sinistra: perché l’hanno estratta così dal gestionale, o perché è frutto di un copia/incolla valori da un subtotale automatico o da una pivot.
Riempire le celle vuote con il valore superiore è un lavoro lungo e tedioso se fatto a manina; rapido e veloce se fatto in automatico. La procedura che io uso in questi casi si appoggia al tasto funzione F5 (Vai A speciale) che mi permette di selezionare in un solo colpo tutte le celle vuote, e alla scorciatoia da tastiera CTRL+ENTER che mi permette di inserire la formula in tutta la selezione.
Anche questa, faccio prima a fartela vedere che a spiegartela, quindi ecco il video, che spero ti sia utile (mi scuso per l’audio bassino):
Questa procedura si presta particolarmente bene a essere registrata in una macro: dato che tutte le selezioni avvengono esclusivamente con F5, la macro funziona con tabelle di qualsiasi dimensione.
Per le statistiche dei nostri siti, usiamo Google Analytics con grande profitto e piena soddisfazione. E’ uno strumento completo, facile, potente e gratuito.
Ma ci sono cose che Analytics non dice, e che invece a volte occorre sapere. Per esempio:
gli errori del server
i tentativi di exploit
gli accessi dei bot dei mail scrapers
Dove sono questi dati? Nel log del server, sotto forma di file di testo molto user-unfriendly, come questo:
Per fortuna posso aprirlo con Excel 2010, senza il limite delle 65.536 righe, e dargli una ripulita in modo da poterlo poi analizzare con i filtri e con la tabella pivot. La procedura non è troppo complessa e si appoggia allo strumento Testo in colonne della barra Dati, ma faccio prima a mostrarla che a descriverla, e quindi l’ho filmata. Chiedo scusa per l’audio basso, ma sono molto raffreddato.
Se poi vuoi fare questa cosa tutti i giorni, il processo si presta bene ad essere automatizzato con una bella macro.
Il componente aggiuntivo Power Pivot di Excel 2010 è scaricabile gratuitamente da qui e porta interessantissime funzionalità alla già ricca tabella pivot.
In sintesi, si tratta di uno strumento che permette di importare e gestire in Excel dati (di qualsiasi dimensione!) provenienti da più fonti diverse, permette di creare al volo relazioni tra le tabelle importate, e infine permette di riassumere e analizzare il tutto con una tabella pivot.
La prima cosa da fare è scegliere le tabelle (clicca sulle immagini per vederle a grandezza reale):
Facendo clic sul pulsante Da altre origini, si apre una finestra di dialogo che permette una vasta scelta: SQL Server, Oracle, DB2, addirittura feed RSS, e chi più ne ha più ne metta, oltre ai soliti driver ODBC:
Per cominciare inserisco un file di testo da quasi due milioni di righe: Excel esegue senza fare una piega.
Dopo aver aggiunto anche una tabella di Excel, qui creo una relazione volante con il file di testo, e forse questa è la caratteristica più interessante della Power Pivot: poter mettere in relazione dati provenienti da fonti più disparate senza usare il CERCA.VERT.
Qui vado a costruire la pivot. La zona dei campi mi indica chiaramente la provenienza da due tabelle diverse. Rispetto alla normale pivot, ho a disposizione due sezionamenti aggiuntivi.
Ecco il risultato finale:
Insomma: sono impressionato dalle potenzialità dello strumento e lo consiglio caldamente. Sono anche molto curioso di accedere a dati esterni tramite feed RSS. Nei prossimi giorni farò qualche prova e vi saprò dire.
Questa sarà utile ai tanti che hanno a che fare con gestionali user-unfriendly.
Il copia/incolla di una tabella da pdf a Excel non funziona molto bene e richiede sempre correzioni manuali lunghe e faticose, se non addirittura la completa riscrittura. Daniele Della Seta ci segnala una soluzione semplicissima: basta passare da Word!
Ecco i tre semplici passaggi:
Et voilà, les jeux sont faits.
Partendo da un buon elenco, in Excel puoi analizzarlo, filtrarlo, riassumerlo con una tabella pivot e più in generale puoi estrarre informazioni dai dati in modo molto semplice, veloce e senza dover fare formule complesse.
E quindi alla fine il problema che hanno tutti coloro che usano Excel per fare analisi dati è sempre quello: normalizzare le tabelle di origine e ripulirle di tutti gli errori, imprecisioni, doppioni e problemi vari. Vale anche qui il classico principio garbage in garbage out, ovvero per avare informazioni buone occorre partire da dati buoni.
Esempio: nel campo “Nazione” troviamo a volte “GB”, a volte “Inghilterra”, a volte “Gran Bretagna”. Oppure: nella colonna “Percentuale” a volte troviamo “0,5″ e a volte “50″.
Ripulire le tabelle sporche è un lavoraccio ingrato e il più delle volte manuale. Una volta ho visto con i miei occhi (terrorizzati), in una società di cui non dirò il nome, che il controllo delle estrazioni dati da SAP verso Excel veniva fatto confrontando a mano su due schermi che proiettavano uno l’originale SAP e l’altro la copia in Excel. Livelli di stress alle stelle!
Nei casi più spinosi io ho trovato molto utile Google Refine, uno strumento rilasciato quest’anno che permette di effettuare trasformazioni automatiche su grosse moli di dati.
Google Refine è in grado di importare dati in qualsiasi formato, come TXT, CSV, XML, XLS e XLSX, JSON e Google Spreadsheets, e direttamente dal web; esporta invece verso CSV, Excel e tabelle HTML.
Google Refine va scaricato e installato, nelle versioni per Windows, Mac OSX e Linux. Una volta installata, l’applicazione gira come server ed è raggiungibile puntando il proprio browser al localhost http://127.0.0.1:3333/.
Per cominciare a usare Google Refine, il primo passo è senz’altro guardarsi questo video.
La Signora A. lavora nell’ufficio del personale di una azienda di produzione del Nord Italia e ha un problema con il programma delle buste paga che, a volte, le sbaglia il calcolo dei giorni di malattia retribuiti o non retribuiti dei suoi operai. Il contratto CONFAPI – settore plastica e gomma – prevede che:
i giorni di malattia vengono interamente retribuiti fino a un limite che dipende dallo stato di anzianità al momento dell’inizio della malattia:
- fino a 3 anni di anzianità: 2 mesi di malattia pagata
- da 3 a 6 anni di anzianità: 3 mesi di malattia pagata
- oltre i 6 anni di anzianità: 4 mesi di malattia pagata
Il conteggio è effettuato nei tre anni precedenti rispetto all’ultima malattia, ma dopo 6 mesi senza malattia il conteggio riparte da zero.
In caso di malattia prolungata, l’operaio ha diritto alla conservazione del posto se i giorni di malattia (a partire dal 01/01/1987) non superano quelli indicati da questa tabella:
- fino a 3 anni di anzianità: 6 mesi di malattia
- da 3 a 6 anni di anzianità: 9 mesi di malattia
- oltre i 6 anni di anzianità: 12 mesi di malattia
I giorni di ospedale però sono esclusi dal conteggio ai fini della conservazione del posto, mentre sono inclusi nel conteggio dei giorni di malattia retribuiti.
La Signora A. mi ha chiesto, approfittando di una giornata di formazione su Excel, se era possibile automatizzare in un foglio di lavoro la procedura di controllo che veniva svolta a mano, con notevole dispendio di tempo ed energia.
Il risultato dei nostri sforzi congiunti è nel file Esempio – assenze malattia (22KB) che contiene tre fogli di lavoro:
Formule svolte:
foglio protetto senza password (le celle di input sono invece editabili) dove le colonne di servizio sono nascoste.Legenda:
spiegazione delle formuleRegole trattamento economico:
riassunto delle regole da seguire per il calcolo dei giorni di malattia
Le funzioni usate per i calcoli non sono troppo complesse, ma sono combinate in modo non semplice:
Per evidenziare bene il processo, ho cercato di scrivere una sola funzione in ogni colonna, invece di scrivere tutto in una sola cella. Ad esempio, nella colonna N calcolo l’inizio di un intevallo di celle, nella colonna O ne calcolo la fine, e nella colonna P sommo l’intervallo così ottenuto; se il foglio fosse stato solo per me, avrei scritto una formula unica nella colonna P.
Come sempre, se hai da proporre miglioramenti o varianti sono tutto orecchi, e i commenti sono aperti.
Se invece hai un tuo caso interessante e difficile di Excel e vuoi vedere se riesco a risolverlo, scrivi a info@sayit.ch e sarò felice di occuparmene in un prossimo post.
La convalida da elenco di Excel è una gran bella invenzione. Nell’inserimento di dati in una cella, ti permette di scegliere da un elenco e ti evita di dover scrivere a mano: zero errori, zero fatica, tutti felici. Se però l’elenco dei valori ammessi è molto lungo, come ad esempio per tutte le nazioni del mondo, allora l’uso del menu a tendina diventa troppo complicato rispetto a scrivere direttamente nella cella.
Una strategia interessante potrebbe essere quella di far scegliere prima il continente, e poi restringere la scelta della nazione in base alla scelta precedente. Occorre quindi dividere l’elenco in più colonne, come ho fatto in questo esempio:
La prima convalida in B14 permette di scegliere il continente e ha come origine le celle A1:C1. La seconda convalida in B15 sceglie la nazione e non ha come origine un intervallo, ma una formula che restituisce un intervallo diverso a seconda del contenuto della cella B14:
=SCARTO(A2:C7;0;CONFRONTA(B14;A1:C1;0)-1;7;1)
Non è una formula facile da ricordare o da scrivere, e neanche da controllare. Per fortuna ho trovato un altro modo, molto più semplice, che ho usato sotto.
Prima di tutto ho creato in automatico i nomi per gli intervalli A2:A7, B2:B7 e C2:C7 prendendoli da A1, B1 e C1. In B19 c’è la stessa convalida che in B14, ma in B20 la formula inserita nella convalida da elenco è semplicissima:
=INDIRETTO(B19)
Siccome la funzione INDIRETTO restituisce il riferimento specificato da una stringa di testo, e siccome nel nostro caso la stringa di testo in B19 corrisponde al nome dell’intervallo, voilà il gioco è fatto. Per la gioja de li piccini, allego il file di Excel 2007 Doppia convalida. Come al solito, se conosci altri modi di fare la stessa cosa, sono interessato.
Tra parentesi, l’esempio è nel formato di Office 2007-2010. Se stai usando versioni precedenti di Excel, e avrai le tue gravissime ragioni, spero tu abbia installato il convertitore gratuito.
Excel è una miniera di scorciatoie da tastiera, e se ne scoprono di nuove ogni giorno. Provo a fare un elenco parziale di quelle che uso di più.
F4
Dentro a una formula, trasforma un riferimento da relativo (A1) ad assoluto ($A$1) e viceversa.
F4
Fuori da una formula, ripete l’ultimo comando
CTRL + Freccia
Sposta la cella attiva in fondo alla riga o alla colonna corrente
CTRL + SHIFT + Freccia
Seleziona dalla cella corrente fino in fondo alla riga o alla colonna
CTRL + Spazio
Seleziona tutta la colonna della cella attiva
SHIFT + Spazio
Seleziona tutta la riga della cella attiva
CTRL + Segno meno
Elimina la riga o la colonna selezionata
CTRL + Segno più
Aggiunge una riga sopra la riga selezionata o una colonna a sinistra della colonna selezionata
CTRL + Apostrofo
Copia nella cella il contenuto della cella superiore
ALT + Freccia giù
In fondo a una colonna di dati, apre un menu a tendina e permette di scegliere uno dei valori presenti nella colonna.
Il post della scorsa settimana su come trasformare una tabella in un elenco ha prodotto commenti interessanti. Marco segnala che si può fare molto prima conR, in sole quattro righe di codice:
library(reshape)
ordini <- read.csv("ordini.csv")
ordini2 <- melt(ordini, id="Prodotto")
names(ordini2) <- c("Prodotti","Taglia","Quantità")
ordini3 <- ordini2[order(ordini2$Prodotti), ]
Robsom con MatLab se la sbriga in due sole righe:
data = load (‘info.dat’); data_new = reshape (data’,size(data,1)*size(data,2),1);
Massimo Morelli invece prende la strada più difficile e la risolve con le formule di Excel. Allego il file di esempio (Esempio – formule dalla tabella alla lista) per i più avventurosi, ma giusto per darti l’idea ecco la formula in A2 che inserisce le etichette della prima colonna:
=SE(RIF.RIGA()-1<=RIGHE(SCARTO(Tabella!$A$1;1;0;CONTA.VALORI(Tabella!$A:$A)-1;1))*COLONNE(SCARTO(Tabella!$A$1;0;1;1;CONTA.VALORI(Tabella!$1:$1)-1));SCARTO(SCARTO(Tabella!$A$1;1;0;CONTA.VALORI(Tabella!$A:$A)-1;1);TRONCA((RIF.RIGA()-2)/COLONNE(SCARTO(Tabella!$A$1;0;1;1;CONTA.VALORI(Tabella!$1:$1)-1));0);0;1;1);”")
Invece per i meno avventurosi, c’è anche una soluzione dove le prime due colonne sono state inserite a mano, e la terza viene calcolata con una formula che si riesce ascrivere in una sola riga:
=CERCA.ORIZZ(B16;$B$1:$G$8;CONFRONTA(A16;$A$1:$A$8;0);0)
Per quanto riguarda la soluzione originale con la macro in VBA, più la guardo e meno mi piace. Magari uno di questi giorni la rifaccio e la ripubblico.