Excel: riempire automaticamente le celle vuote

Buchi prima e dopo

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.

Share

Da PDF a Excel in tre mosse

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:

  1. aprire il pdf, selezionare la tabella e copiarla (Ctrl+C);
  2. aprire un documento vuoto di Word e incollarla (Ctrl+V): diventa una tabella di Word (potrebbe essere necessario usare il comando Testo in tabella, se non dovesse riconoscerla al primo colpo);
  3. copiare la tabella di Word dentro a un foglio vuoto di Excel.

Et voilà, les jeux sont faits.

 

Share

Excel: doppia convalida da elenco

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.

Share

Excel: dalla tabella all'elenco, altri modi

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.

Share

Excel: associare una descrizione a una funziona personalizzata

L’altro giorno stavo facendo un corso di VBA per Excel. Stavo spiegando come creare le funzioni personalizzate.
Uno studente mi ha chiesto come aggiungere un commento ad una funzione di questo tipo.
Ecco la risposta:

Ipotizziamo di creare una semplice funzione chiamata sconto.
La funzione applica uno sconto del 10% se il numero di articoli supera i 50 pezzi.
Ecco la funzione:


Se utilizziamo la funzione in Excel essa ci apparirà nella categoria “Definite dall’utente” senza descrizione:

E’ possibile sia inserire una descrizione della funzione sia associare la funzione personalizzata ad una categoria diversa tramite
il metodo MacroOptions dell’oggetto Application.

IN Visual Basic nell’evento Workbook_Open() della cartella di lavoro inserite una delle seguenti istruzioni:

Ipotizziamo di scegliere la terza istruzione che crea il commento e mette la funzione in una categoria definita da noi. Una volta eseguito il codice in Excel la funzione apparirà così:

Nell’help in linea di VBA per Excel è possibile trovare la descrizione dettagliata del metodo MacroOptions.

Share

Excel: l'intervallo dei dati nelle tabelle pivot

Quando tengo dei seminari sull’accesso ai dati con Excel, una delle cose per me più interessanti è quando scopro modi diversi di fare le cose. La settimana scorsa, nel fare una tabella pivot basata su un elenco di Excel 2003, un allievo mi ha selezionato, nel passaggio 2 della creazione guidata, come intervallo dati non le celle dell’elenco, ma le intere colonne.

Esempio: partendo da un semplice elenco di 7 colonne per 38 righe, io avrei selezionato così:

intervallo per pivot

Lui invece ha selezionato così:

colonne per pivot

Perché lo ha fatto? In questo modo, quando aggiunge nuove righe all’elenco, può aggiornare la pivot senza dover ridefinire l’intervallo. Interessante! Io invece devo ritornare ogni volta al passaggio 2 della creazione guidata e aggiungere le nuove righe.

Ci sono però dei lati negativi. Si risolve il problema dell’aggiornamento ma se ne creano altri:

  1. La dimensione del file: un elenco di 20KB a cui si aggiunge una pivot fatta nel primo modo, rimane di 20KB; nel secondo modo diventa un file di 900KB (un elenco di 56KB diventa un file di 2MB!).
  2. Gli aggiornamenti: diventano leeenti, e visto che la pivot viene aggiornata anche all’apertura, anche aprire il file diventa leeento.
  3. Nel costruire la pivot, il campo dei totali non viene sommato ma contato, a causa della presenza di celle vuote nella colonna.
  4. Sui campi di data, non si riesce ad aggregare per mese/anno, sempre a causa delle celle vuote.

Come mi segnalava Massimo Morelli, c’è un ulteriore modo, consigliato per gli smanettoni di Excel: creare un nome dinamico e fare la pivot sul nome invece che sull’intervallo di celle (o di colonne). La formula per la creazione del nome, che vale per qualsiasi elenco che cominci in A1, è questa:

=SCARTO(Inventario!$A$1;0;0;CONTA.VALORI(Inventario!$A:$A);

CONTA.VALORI(Inventario!$1:$1))

Share

Excel: scorciatoia per inserire la data di oggi

Questo è un vecchissimo trucco di Excel ma non lo sanno in molti: per inserire la data odierna in una cella, invece di scriverla per esteso, è possibile usare la seguente combinazione di tasti:

CTRL + SHIFT + ; (punto e virgola)

Invece per inserire l’ora corrente, stessa combinazione ma con il due punti invece del punto e virgola:

CTRL + SHIFT + : (due punti)

Share

Excel: impedire duplicati

Mi è stato chiesto: è possibile impedire l’inserimento di duplicati in una colonna di celle di Excel? Metto qui la risposta, nel caso interessi. Se conosci una soluzione più elegante, sono io quello interessato!

Metti di avere un foglio di Excel dove in A1 scrivi “Codice” e in A2 scrivi il primo codice, numerico o testo non importa. Dalla cella A3 in poi, per impedire l’inserimento di un codice già presente, puoi usare la Convalida dal menu Dati.

1.

Seleziona le celle da A3 in giù, fino a dove ti serve.

2.

Nella finestra di dialogo Convalida dati, nella scheda Impostazioni, come criterio di convalida scegli Personalizzato e inserisci la seguente formula:

=VAL.NON.DISP(CERCA.VERT(A3;$A$2:A2;1;0))

La funzione CERCA.VERT restituisce l’errore #N/D se il valore immesso in A3 non viene trovato, nel qual caso la funzione VAL.NON.DISP restituisce “Vero” alla Convalida.

Nota come l’intervallo di ricerca $A$2:A2 verrà esteso verso il basso a ogni nuova riga.

3.

Sempre nella finestra di dialogo Convalida dati, ma nella scheda Messaggio di errore, inserisci un avviso che comparirà se si cerca di inserire un valore già presente.

4.

Premi il pulsante OK Se ti serve estendere la convalida su nuove righe, puoi usare il pennello Copia formato sulla barra degli strumenti.

Share