Categoria: Excel

Pubblicato il

CERCA.VERT tra numeri e testo

Ho visto più volte questo problema (l’ultima ieri) e immagino possa servire a qualcuno la soluzione, per cui la metto qui.

Un CERCA.VERT (o VLOOKUP se usi la versione inglese) tra due numeri non funziona se i formati dei due numeri sono diversi: Se cerco un numero dentro a una colonna di numeri formattati come testo, oppure se cerco un numero formattato come testo dentro una colonna di numeri, il risultato è un bel #N/A.

Tu dirai: ma chi è quel cretino che formatta i numeri come testo? Il cretino di solito è Excel, per esempio quando importa dati da un gestionale tipo SAP o DB2 per AS400 etc. e sbaglia a capire i formati.

Per risolvere questo problema, è possibile usare dentro al CERCA.VERT le funzioni TESTO e VALORE, che convertono appunto un valore in testo e viceversa, come da esempio:

Se vuoi una soluzione più sbrigativa:

  1. prendi una cella vuota da qualche parte del tuo foglio e ci scrivi il numero uno;
  2. lo selezioni e lo copi;
  3. selezioni i tuoi numeri formattati come testo;
  4. da Incolla speciale, scegli Moltiplica.

Et voilà! I tuoi numeri formattati come testo sono diventati numeri formattati come numero.

Pubblicato il

Excel 2013 e la PowerPivot

Uno dei lavori più brutti e noiosi che vedo fare molto spesso è il vlookup (o cerca.vert) di unione tra più tabelle per poterle poi riassumere con una sola tabella pivot. In Excel 2010 il problema è stato risolto con l’addin PowerPivot, ma è raro, specie nelle grandi aziende, che l’utente abbia i permessi di installarsi alcunché.

Come speravo, la PowerPivot è stata integrata in Excel 2013 e non c’è più bisogno di scaricarsi l’addin separatamente. Quindi da adesso qualsiasi tabella pivot può prendere i suoi dati da più fonti. Il trucco è quel segno di spunta su Add this to the Data Model che compare sia quando si crea una pivot partendo da un elenco di Excel, sia quando si accede a dati esterni:

Ogni tabella o fonte esterna aggiunta in questo modo diventa usabile all’interno di una stessa tabella pivot:

In questo esempio Table1 è un intervallo di Excel di 145 righe e Demo è un file di testo di 2 milioni di righe (non importato ma collegato). A questo punto non resta che costruire le relazioni tra le diverse tabelle, con l’apposito nuovo pulsante Relationships della barra Analyze:

Et voila, il gioco è fatto. Addio vlookup.

Pubblicato il

Olimpiadi 2012: medagliere e popolazione

Faceva notare il Post come l’India sia la nazione che raccoglie meno medaglie in assoluto rispetto alla sua enorme popolazione. Allora ho provato a pesare il medagliere per nazione in base alle rispettive popolazioni.

Tra tutte quelle che hanno preso almeno un oro, la Cina risulta penultima prima del Brasile, e gli stati Uniti stanno facendo appena meglio del Sudafrica.  Invece ai primi posti troviamo:

  1. Slovenia
  2. Lituania
  3. Nuova Zelanda
  4. Ungheria
  5. Georgia
  6. Kazakhstan
  7. Corea del Nord
  8. Corea del Sud
  9. Australia
  10. Francia

Vedremo a fine Olimpiadi chi sarà il vincitore di questa classifica: si accettano scommesse.

Il file di Excel aggiornato è qui.

Pubblicato il

Excel: Il medagliere delle Olimpiadi 2012

Non poteva mancare, e quindi eccolo: il foglio di Excel per le Olimpiadi 2012.


I dati si aggiornano da soli a ogni apertura (oppure dal pulsante Aggiorna della scheda Dati) e vanno a pescare da internet il medagliere aggiornato in tempo quasi reale.

I paesi sono in ordine di classifica: prima chi ha più ori, e in caso di parità prima chi ha più argenti, etc. Ma nella colonna H mi sono inventato un ranking diverso, che assegna un valore alle tre medaglie (celle C1:E1, modificabili a piacere) e stila la classifica in base alla somma dei tre valori.

In questo modo salgono in classifica le nazioni che hanno pochi ori ma tantissimi argenti e bronzi.

Ti domanderai: perché tutto questo? Ti rispondo: Perché no? io mi diverto con poco 🙂

Pubblicato il

Excel: problemi del CAP

Ieri Elisa mi ha sottoposto un problema in Excel che voglio riportare qui perché a) penso possa servire ad altri, b) sul momento ho escogitato una soluzione troppo macchinosa e c) oggi mi è venuta in mente una soluzione più semplice ed elegante.

Elisa estrae dati anagrafici da un gestionale remoto su cui non ha controllo e che offre solo l’esportazione verso Excel. La colonna del codice postale contiene un numero di 5 cifre che viene interpretato appunto come numero: gli zeri iniziali vengono eliminati. Come ripristinarli?

Sul momento ho risolto velocemente, annidando la funzione SE in modo da prevedere le quattro possibilità del numero di avere 2, 3, 4 o 5 cifre, e ho ottenuto il seguente mostro, funzionante ma inguardabile:

=SE(LUNGHEZZA(A2)=2;”000″&A2;
SE(LUNGHEZZA(A2)=3;”00″&A2;
SE(LUNGHEZZA(A2)=4;”0″&A2;A2)))

Ripensandoci stamattina, mi è venuta in mente quest’altra soluzione, molto più breve ed elegante:

=RIPETI(“0”;5-LUNGHEZZA(A2))&A2

 

Ripensandoci ancora questa sera, mi sono ricordato (finalmente) che in Excel 2007 e 2010 basta dare il formato speciale CAP Italia che aggiunge automaticamente gli zeri iniziali senza bisogno di funzioni.

Il che conferma il vecchio adagio, che per essere brevi ci vuole tempo.