Molte volte, potresti incontrare un set di dati di Excel in cui solo una cella è piena di dati e le celle sottostanti sono vuote fino al valore successivo.
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.
È facile da risolvere!
In questo tutorial ti mostrerò come fareRiempi rapidamente le celle fino al prossimo valore di riempimento in Excel.
Puoi farlo facilmente usando semplici tecniche di dialogo speciali Go-To, VBA o Power Query.
Quindi iniziamo!
Contenuti
Metodo 1: compila utilizzando la formula Vai a speciale +
Supponiamo di avere un set di dati come mostrato di seguito e di voler inserire i dati nelle colonne A e B.
Nella colonna B, l'obiettivo è riempire "stampante" fino all'ultima cella vuota sottostante, quindi quando si avvia "scanner", quindi riempire "scanner" nella cella sottostante finché la cella non è vuota.
I passaggi seguenti utilizzano vai a speciale per selezionare tutte le celle vuote e compilare con una semplice formula:
- Seleziona i dati da compilare (A1:D21 nel nostro esempio)
- Vai alla scheda "Home".
- Nel gruppo di modifica, fai clic sull'icona Trova e seleziona (questo farà apparire più opzioni nel menu a discesa)
- Fare clic sull'opzione "Vai".
- 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 Vuoto
- Fare clic su OK
I passaggi precedenti selezioneranno tutte le celle vuote nel set di dati (mostrato di seguito).
Tra le celle vuote selezionate, noterai che una cella è più luminosa del resto della selezione.Questa cella è la cella attiva in cui vogliamo inserire la formula.
Non preoccuparti della posizione della cella nella selezione, poiché il nostro metodo funzionerà comunque.
Ora, ecco i passaggi per compilare i dati nelle celle vuote selezionate:
- Fare clic sul tasto del segno di uguale (=) sulla tastiera.Questo inserirà un segno di uguale nella cella attiva
- Premere 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, inserisce =B2 nella cella
- Tieni premuto il tasto Ctrl e premi Invio
I passaggi precedenti inseriranno automaticamente tutte le celle vuote con i valori precedenti.
Anche se possono sembrare troppi passaggi, una volta che avrai preso la mano, sarai in grado di compilare rapidamente i dati in Excel in pochi secondi.
Ora, ci sono altre due cose di cui devi essere consapevole quando usi questo metodo.
Converti la formula in valore
Il primo è assicurarti di convertire le formule in valori (in modo da avere valori statici e non sbagliare se cambi i dati in futuro).
Modifica il formato della data
Se usi le date nei tuoi dati (come ho fatto nei miei dati di esempio), noterai che i valori popolati nella colonna della data sono numeri anziché date.
Se l'output nella colonna della data è nel formato della data desiderato, allora sei a posto e non devi fare nient'altro.
Ma se tali date non sono nel formato corretto, è necessaria la messa a punto.Sebbene tu abbia il valore corretto, devi solo cambiare il formato in modo che appaia come una data nella cella.
Ecco i passaggi per farlo:
- Seleziona le colonne contenenti le date
- Fare clic sulla scheda Home
- Nel gruppo Numeri, fai clic sul menu a discesa Formato e seleziona un formato data.
Se hai bisogno di fare questa imbottitura di tanto in tanto, ti consiglio questa tecnica speciale e formula Go-To.
Sebbene abbia alcuni passaggi, è semplice e fornisce risultati nel set di dati.
Ma se devi farlo spesso, ti suggerisco di guardare VBA e il metodo Power Query descritto di seguito
Metodo 2: compila con un semplice codice VBA
Puoi utilizzare un codice macro VBA molto semplice per riempire rapidamente le celle in Excel fino all'ultimo valore vuoto.
Devi solo aggiungere il codice VBA a un file una volta, quindi puoi facilmente riutilizzare quel codice più volte nella stessa cartella di lavoro o anche su più cartelle di lavoro sul tuo sistema.
Ecco il codice VBA che scorrerà attraverso ogni cella nella selezione e riempirà tutte le celle vuote:
Sub FillDown() per ogni cella nella selezione If cell = "" Then cell.FillDown End If Next End Sub
Il codice sopra usa un ciclo For per scorrere ogni cella nella selezione.
Nel ciclo For, utilizzo una condizione If-Then per verificare se la cella è vuota.
Se la cella è vuota, viene riempita con il valore della cella sopra, in caso contrario, il ciclo for ignora la cella e si sposta alla cella successiva.
Ora che hai il codice VBA, lascia che ti mostri dove inserire questo codice in Excel:
- Seleziona i dati da compilare
- Fare 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, avrai Esplora progetti.Se non lo vedi, fai clic sull'opzione Visualizza nel menu, quindi fai clic su Esplora progetto
- Se sono aperte più cartelle di lavoro di Excel, Project Explorer visualizza tutti i nomi delle cartelle di lavoro.Trova il nome della cartella di lavoro in cui hai i dati.
- Fare clic con il pulsante destro del mouse su qualsiasi oggetto nella cartella di lavoro, passare a Inserisci e fare clic su Modulo.Questo inserirà un nuovo modulo per questa cartella di lavoro
- Fare doppio clic sul "Modulo" appena inserito nei passaggi precedenti.Si aprirà la finestra del codice per quel modulo
- Copia e incolla il codice VBA nella finestra del codice
- Posizionare il cursore in un punto qualsiasi del codice ed eseguire 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 desideri utilizzare nuovamente questo codice VBA in un secondo momento, dovrai salvare questo file come cartella di lavoro di Excel con abilitazione macro (estensione .XLSM)
Un'altra cosa che puoi fare è aggiungere questa macro alla tua barra di accesso rapido, che è sempre visibile e puoi accedere a questa macro con un solo clic (in una cartella di lavoro con codice nel back-end).
Quindi la prossima volta che hai dati che devono essere inseriti, puoi semplicemente effettuare una selezione e fare clic sul pulsante macro nella barra degli strumenti di accesso rapido.
Puoi anche aggiungere questa macro alla tua cartella di lavoro macro personale e quindi usarla in qualsiasi cartella di lavoro sul tuo sistema.
Fare clic qui per ulteriori informazioni sulla cartella di lavoro macro personale e su come aggiungere codice ad essa.
Metodo 3: compila utilizzando Power Query
Power QueryHa funzionalità integrate che ti consentono di compilarlo con un solo clic.
È consigliato quando si usa ancora Power Query per trasformare dati o combinare dati da più fogli di lavoro o più cartelle di lavoro.
Come parte di un flusso di lavoro esistente, puoi riempire rapidamente le celle vuote usando l'opzione Riempi in Power Query.
Per utilizzare Power Query, è consigliabile che i dati siano in formato tabella Excel.Se non è possibile convertire i dati in una tabella di Excel, è necessario creare un intervallo denominato per i dati e quindi utilizzare tale intervallo denominato in Power Query.
Di seguito ho un set di dati che è stato convertito in una tabella Excel.Puoi farlo selezionando il set di dati, andando alla scheda Inserisci e facendo clic sull'icona Tabella.
Di seguito sono riportati i passaggi per utilizzare Power Query per compilare i dati fino al valore successivo:
- Seleziona una cella qualsiasi nel set di dati
- Fare clic sulla scheda Dati
- Nel gruppo Ottieni e trasforma dati, fai clic su Da foglio di lavoro.Si aprirà l'editor di Power Query.Tieni presente che le celle vuote visualizzeranno il valore "null" in Power Query
- Nell'editor di Power Query, seleziona la colonna che vuoi compilare.Per fare ciò, tieni premuto il tasto Ctrl e fai clic sull'intestazione della colonna che desideri selezionare.Nel nostro esempio saranno le colonne data e prodotto.
- Fare clic con il pulsante destro del mouse su qualsiasi titolo selezionato
- Vai all'opzione Riempi e fai clic su Giù.Questo riempirà tutte le celle vuote con i dati
- Fare clic sulla scheda File, quindi su Chiudi e carica.Questo inserirà un nuovo foglio di lavoro con la tabella dei risultati nella cartella di lavoro
Sebbene questo approccio possa sembrare eccessivo, uno dei grandi vantaggi dell'uso di Power Query è che consente di aggiornare rapidamente i dati risultanti quando i dati originali cambiano.
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 fare clic su Aggiorna.
Sebbene il metodo Power Query funzioni bene, è meglio usarlo quando stai già utilizzando Power Query nel flusso di lavoro.
Se si dispone di un solo set di dati con celle vuote da compilare, è più conveniente utilizzare il metodo speciale Vai a o il metodo VBA (descritto sopra).
Quindi puoi usare questi tre semplici metodi perRiempi le celle vuote fino al valore successivo in Excel.
Spero che tu abbia trovato utile questo tutorial.
