Guarda il video - Compila in Excel
Molte volte, potresti incontrare un set di dati in cui solo una cella è piena di dati e le celle sottostanti sono vuote fino al valore successivo.
Qualcosa come mostrato di seguito:
Sebbene questo formato funzioni per alcune persone, il problema con questo tipo di dati è che non è possibile utilizzarlo per creare tabelle pivot o utilizzarlo nei calcoli.
E questo ha una soluzione facile!
In questo tutorial, ti mostrerò come farlo rapidamente riempire le celle in Excel fino al prossimo valore riempito.
Puoi farlo facilmente usando una semplice tecnica della finestra di dialogo speciale Go-To, VBA o Power Query.
Quindi iniziamo!
Metodo 1 - Riempi usando Vai a speciale + Formula
Supponiamo di avere un set di dati come mostrato di seguito e di voler riempire i dati nella colonna A e nella colonna B.
Nella colonna B, l'obiettivo è riempire "Stampante" fino all'ultima cella vuota sotto di essa, quindi quando si avvia "Scanner", quindi riempire "Scanner" nelle celle sottostanti fino a quando le celle non sono vuote.
Sotto i passaggi da utilizzare vai su speciale per selezionare tutte le celle vuote e riempirle usando una semplice formula:
- Seleziona i dati in cui vuoi compilare (A1:D21 nel nostro esempio)
- Vai alla scheda "Home"
- Nel gruppo Modifica, fai clic sull'icona "Trova e seleziona" (questo mostrerà alcune altre opzioni nel menu a discesa)
- Fare clic sull'opzione "Vai a"
- Nella finestra di dialogo Vai a che si apre, fai clic sul pulsante Speciale. Si aprirà la finestra di dialogo "Vai a speciale"
- Nella finestra di dialogo Vai a speciale, fare clic su Spazi vuoti
- Fare clic su OK
I passaggi precedenti selezionerebbero tutte le celle vuote nel set di dati (come mostrato di seguito).
Nelle celle vuote selezionate, noteresti che una cella è più chiara del resto della selezione. Questa cella è la cella attiva in cui inseriremo una formula.
Non preoccuparti di dove si trova la cella nella selezione, poiché il nostro metodo funzionerebbe in ogni caso.
Ora, di seguito sono riportati i passaggi per compilare i dati nelle celle vuote selezionate:
- Premi il tasto uguale a (=) sulla tastiera. Questo inserirà un segno di uguale nella cella attiva
- Premi il tasto freccia su. Questo inserirà il riferimento di cella della cella sopra la cella attiva. Ad esempio, nel nostro caso, B3 è la cella attiva e quando eseguiamo questi due passaggi, entra = B2 nella cella
- Tieni premuto il tasto Ctrl e premi il tasto Invio
I passaggi precedenti inserirebbero automaticamente tutte le celle vuote con il valore sopra di esse.
Anche se possono sembrare troppi passaggi, una volta che hai imparato questo metodo, sarai in grado di riempire rapidamente i dati in Excel in pochi secondi.
Ora, ci sono altre due cose di cui devi occuparti quando usi questo metodo.
Converti formule in valori
Il primo è assicurarsi di convertire le formule in valori (in modo da avere valori statici e le cose non si guastano nel caso in cui si cambino i dati in futuro).
Di seguito è riportato un video che ti mostrerà come convertire rapidamente le formule in valori in Excel.
Cambia il formato della data
Nel caso in cui utilizzi le date nei tuoi dati (come lo sono nei dati di esempio), noterai che i valori compilati nella colonna della data sono numeri e non date.
Se l'output nella colonna della data è nel formato della data desiderato, sei a posto e non devi fare nient'altro.
Ma se questi non sono nel formato della data corretto, è necessario apportare una piccola modifica. Mentre hai i valori giusti, devi solo cambiare il formato in modo che venga visualizzato come una data nella cella.
Di seguito sono riportati i passaggi per eseguire questa operazione:
- Seleziona la colonna che contiene le date
- Fare clic sulla scheda Home
- Nel gruppo Numero, fare clic sul menu a discesa Formato e quindi selezionare il formato della data.
Se hai bisogno di fare questo riempimento una volta ogni tanto, ti consiglio di usare questa tecnica speciale e formula Go-To.
Sebbene abbia pochi passaggi, è semplice e ti dà il risultato proprio lì nel tuo set di dati.
Ma nel caso in cui dovessi farlo abbastanza spesso, ti suggerisco di guardare i metodi VBA e Power Query trattati in seguito
Metodo 2 - Riempi usando un semplice codice VBA
Puoi utilizzare un codice macro VBA davvero semplice per riempire rapidamente le celle in Excel fino all'ultimo valore vuoto.
Devi solo aggiungere il codice VBA al file una volta e poi puoi facilmente riutilizzare il codice più volte nella stessa cartella di lavoro o anche più cartelle di lavoro sul tuo sistema.
Di seguito è riportato il codice VBA che attraverserà ogni cella nella selezione e riempirà tutte le celle vuote:
'Questo codice VBA è creato da Sumit Bansal da TrumpExcel.com Sub FillDown() per ogni cella in selezione If cell = "" Then cell.FillDown End If Next End Sub
Il codice sopra usa un ciclo For per passare attraverso ogni cella nella selezione.
All'interno del ciclo For, ho utilizzato una condizione If-Then che verifica se la cella è vuota o meno.
Nel caso in cui la cella sia vuota, viene riempita con il valore della cella sopra e, nel caso in cui non sia vuota, il ciclo for ignora semplicemente la cella e passa a quella successiva.
Ora che hai il codice VBA, lascia che ti mostri dove inserire questo codice in Excel:
- Seleziona i dati in cui vuoi compilare
- Fai clic sulla scheda Sviluppatore
- Fare clic sull'icona di Visual Basic (oppure utilizzare la scorciatoia da tastiera ALT + F11). Questo aprirà l'editor di Visual Basic in Excel
- Nell'editor di Visual Basic, a sinistra, avresti il Project Explorer. Se non lo vedi, fai clic sull'opzione "Visualizza" nel menu e quindi fai clic su Esplora progetti
- Nel caso in cui tu abbia più cartelle di lavoro Excel aperte, Project Explorer ti mostrerà tutti i nomi delle cartelle di lavoro. Individua il nome della cartella di lavoro in cui hai i dati.
- Fare clic con il pulsante destro del mouse su uno qualsiasi degli oggetti nella cartella di lavoro, andare su Inserisci e quindi fare clic su Modulo. Questo inserirà un nuovo modulo per quella cartella di lavoro
- Fare doppio clic sul "Modulo" appena inserito nel passaggio precedente. Si aprirà la finestra del codice per quel modulo
- Copia e incolla il codice VBA nella finestra del codice
- Posiziona il cursore in un punto qualsiasi del codice e quindi esegui il codice della macro facendo clic sul pulsante verde nella barra degli strumenti o utilizzando la scorciatoia da tastiera F5
I passaggi precedenti eseguiranno il codice VBA e i tuoi dati verranno compilati.
Se si desidera utilizzare nuovamente questo codice VBA in futuro, è necessario salvare questo file come cartella di lavoro Excel con attivazione macro (con estensione .XLSM)
Un'altra cosa che puoi fare è aggiungere questa macro alla tua barra di accesso rapido, che è sempre visibile e avrai accesso a questa macro con un solo clic (nella cartella di lavoro in cui hai il codice nel backend).
Quindi, la prossima volta che hai dati da riempire, tutto ciò che devi fare è effettuare la selezione e quindi fare clic sul pulsante della macro nella barra di accesso rapido.
Puoi anche aggiungere questa macro alla cartella di lavoro macro personale e quindi usarla in qualsiasi cartella di lavoro sul tuo sistema.
Fare clic qui per ulteriori informazioni sulle cartelle di lavoro macro personali e su come aggiungere codice.
Metodo 3 - Riempi usando Power Query
Power Query ha una funzionalità integrata che ti consente di riempire con un solo clic.
L'uso di Power Query è consigliato quando lo si utilizza comunque per trasformare i dati o combinare dati da più fogli di lavoro o più cartelle di lavoro.
Come parte del flusso di lavoro esistente, puoi usare l'opzione di riempimento in Power Query per riempire rapidamente le celle vuote.
Per usare Power Query, è consigliabile che i dati siano in formato tabella di Excel. Se non riesci a convertire i dati in una tabella di Excel, dovrai creare un intervallo denominato per i dati e quindi utilizzare tale intervallo denominato in Power Query.
Di seguito ho il set di dati che ho già convertito in una tabella Excel. Puoi farlo selezionando il set di dati, andando alla scheda Inserisci e quindi facendo clic sull'icona Tabella.
Di seguito i passaggi per utilizzare Power Query per compilare i dati fino al valore successivo:
- Seleziona una cella nel set di dati
- Fare clic sulla scheda Dati
- Nel gruppo Ottieni e trasforma dati, fai clic su "Da foglio". Verrà aperto l'editor di Power Query. Nota che le celle vuote mostrerebbero il valore "null" in Power Query
- Nell'editor di Power Query selezionare le colonne per le quali si vuole riempire. Per fare ciò, tieni premuto il tasto Ctrl e quindi fai clic sull'intestazione delle colonne che desideri selezionare. Nel nostro esempio, sarebbero le colonne Data e Prodotto.
- Fare clic con il pulsante destro del mouse su una delle intestazioni selezionate
- Vai all'opzione "Riempi" e quindi fai clic su "Giù". Questo riempirebbe i dati in tutte le celle vuote
- Fare clic sulla scheda File e quindi fare clic su "Chiudi e carica". Questo inserirà un nuovo foglio di lavoro nella cartella di lavoro con la tabella risultante
Sebbene questo metodo possa sembrare un po' eccessivo, un grande vantaggio dell'utilizzo di Power Query è che consente di aggiornare rapidamente i dati risultanti nel caso in cui i dati originali vengano modificati.
Ad esempio, se aggiungi più record ai dati originali, non è necessario ripetere tutto quanto sopra. Puoi semplicemente fare clic con il pulsante destro del mouse sulla tabella risultante e quindi fare clic su Aggiorna.
Sebbene il metodo Power Query funzioni bene, è meglio usarlo quando stai già usando Power Query nel tuo flusso di lavoro.
Se hai solo un set di dati in cui desideri riempire le celle vuote, sarebbe più conveniente utilizzare il metodo speciale Vai a o il metodo VBA (trattato sopra).
Quindi questi sono tre semplici modi che puoi usare per riempi le celle vuote fino al valore successivo in Excel.
Spero che tu abbia trovato utile questo tutorial.
