Le celle unite sono il problema oggi, ma si tratta di celle unite verticali. Dai un'occhiata alla figura qui sotto. Se sono presenti 7 righe per la regione Midwest, la parola Midwest appare solo sulla prima riga. Qualcuno sta quindi unendo le sei celle vuote rimanenti con la cella Midwest per creare una cella unita alta 7 celle.
Questo è un modo orribile per inviare dati. Se qualcuno è esperto in Excel, potrebbe voler ordinare i dati, aggiungere subtotali, utilizzare tabelle pivot. Le cellule unite lo impediscono.
A peggiorare le cose, ricevi ogni giorno un nuovo file dalla sede centrale strutturato in questo modo. HQ sta inviando file simili a 500 filiali. Essendo solo un minuscolo avamposto, non hai molte possibilità di convincere l'HQ che questo è un modo orribile per inviare dati.
L'articolo di oggi tratta di come risolvere rapidamente questo problema con Power Query. La soluzione Power Query sarà più semplice nei giorni da 2 a 9999 rispetto ai passaggi seguenti in Excel:
I guru di Excel potrebbero suggerire questi passaggi ogni giorno:
- Seleziona tutte le celle scegliendo il rettangolo sopra ea sinistra di A1.
- Fare clic sul pulsante di avvio della finestra di dialogo nell'angolo inferiore destro del gruppo Allineamento della scheda Home.
- Fare clic due volte sulla casella Unisci celle per deselezionarla.
- Fare clic su OK per chiudere la finestra di dialogo Formato celle
- Selezionare dalla fine della colonna A indietro ad A1.
- Home, Trova e seleziona, Vai a speciale, Spazi vuoti, OK
- Tipo = Freccia su. Premi CTRL + INVIO
- Selezionare dalla fine della colonna A indietro ad A1.
- Ctrl + C per copiare. Fare clic con il pulsante destro del mouse e incollare i valori
Ma c'è un modo molto più semplice. I nuovi strumenti di Power Query sono integrati nelle versioni Windows di Office 365 ed Excel 2016. Se disponi di una versione Windows di Excel 2010 o Excel 2013, puoi scaricare Power Query gratuitamente da Microsoft.
Ecco la strategia con Power Query:
- Crea un piano in cui salverai la cartella di lavoro di ogni giorno da HQ nella stessa cartella con lo stesso nome.
- Apri una nuova cartella di lavoro vuota che conterrà i dati fissi da HQ.
-
Nella cartella di lavoro vuota, scegli Dati, Ottieni dati, Da file, Da cartella di lavoro.
Avvia il processo di Power Query - Individua la cartella e il file dal passaggio 1.
-
Quando si apre la finestra Power Query, notare che le celle unite sono sparite. Hai Midwest nella riga 1 e poi sei celle che contengono "null". Seleziona questa colonna facendo clic sull'intestazione Regione.
Power Query rimuove automaticamente le celle unite -
In Power Query selezionare la scheda Trasforma. Scegli Riempi, Giù. La parola Midwest viene copiata dalla riga 1 alle righe da 2 a 7. Trasformazioni simili si verificano in tutto il set di dati.
Riempi le celle nulle con il valore dall'alto -
Home, Chiudi e carica. Power Query verrà chiuso. In circa 10-20 secondi, i dati puliti da HQ appariranno in un nuovo foglio di lavoro nella cartella di lavoro.
Restituisci i dati a Excel - Salva la cartella di lavoro con un nome come CleanedDataFromHQ.xlsx.
- Quando i dati sono selezionati, dovresti vedere il pannello Query e connessioni sul lato destro. Se non vedi il pannello, vai a Dati, query e connessioni.
-
Fare clic con il pulsante destro del mouse sulla query nel pannello Query e connessioni. Scegli Proprietà.
Apri le proprietà per questa query -
Scegli "Aggiorna dati all'apertura del file". Fare clic su OK.
Imposta la query in modo che venga eseguita ogni volta che apri la cartella di lavoro.
Il tuo flusso di lavoro diventa quindi: (a) Ricevi la cartella di lavoro da HQ tramite posta elettronica. (b) Salvare l'allegato nella cartella corretta con il nome corretto. (c) Apri la cartella di lavoro CleanedDataFromHQ.xlsx. I nuovi dati verranno caricati nella cartella di lavoro.
Guarda un video
Trascrizione del video
Impara Excel da Podcast, episodio 2221: Riempi le celle unite.
Ehi, bentornato al netcast. Sono Bill Jelen, ero a Calumet City, Elizabeth si presenta e dice che riceve questo file - il nostro file, come questo - ogni giorno ed è orribile. Qui nella colonna A, non è solo che hanno messo Midwest e hanno lasciato un mucchio di spazi vuoti, è una cella unita. Tutto a posto? Stanno inviando il … Il quartier generale sta inviando questi file in tutto il mondo con queste dannate celle unite qui. Le cellule unite sono malvagie.
Bene, ecco come sbarazzarsi di questo. Prima di tutto, farò clic con il pulsante destro del mouse, Sposta o copia, Crea una copia e la sposto nel nuovo libro. Quindi fingiamo che sia come la cartella di lavoro che Elizabeth sta ricevendo. File, Salva con nome, e ogni volta che Elizabeth riceve una di queste cartelle di lavoro, voglio che la metta nello stesso identico punto con lo stesso identico nome. Tutto a posto? Lo stesso nome esatto … perfetto. Quindi ora abbiamo solo quel singolo file, singolo foglio, chiudo il file. E poi creeremo una cartella di lavoro nuova di zecca. Inserirò qui un nuovo foglio di lavoro e questo nuovo foglio di lavoro si chiamerà Rapporto. E su questo vuoto, il nostro rapporto farà Dati, Ottieni dati, Da file, Da una cartella di lavoro, punta al file in cui lo salveremo, quindi, con lo stesso identico nome ogni volta, fai clic su Importa , scegli il primo foglio di lavoro,e quindi fare clic su Modifica. Tutto a posto. Prima di tutto, questo è incredibilmente fantastico: la query di alimentazione non avrà nessuna di queste sciocchezze di celle di fusione, convertono istantaneamente le celle di fusione in celle individuali e tutte queste celle sono nulle. Scegli quella colonna, è già scelta qui, quindi Trasforma, Riempi, Riempi in basso, così, e poi Home, Chiudi e carica. Va bene, quindi, ecco la mia cartella di lavoro.
Ora, vedo che alcuni hanno delle colonne extra qui. Fammi fare clic con il tasto destro e modificheremo.
E sembra che tutto ciò che è a destra del costo debba andare via. Quanta roba extra là fuori? Fare clic con il pulsante destro del mouse e rimuovere quelle colonne - perfetto - Chiudi e carica. Va bene, ora, il flusso di lavoro diventa, ogni volta che Elizabeth riceve un nuovo rapporto dal quartier generale, lo salverà nello stesso punto con un nome affidabile o qualsiasi altra cosa, come lo chiamiamo, e quindi aprirà questa cartella di lavoro e quindi qui fai clic su Aggiorna.
Oppure, se volessimo lavorare automaticamente, fai clic con il pulsante destro del mouse qui, vai su Proprietà, appena fuori dallo schermo fino in fondo, quindi dì: Ogni volta che apro questo file aggiorna i dati. Quindi ogni volta che apro questa cartella di lavoro andrà alla nuova cartella di lavoro che abbiamo ricevuto dalla sede centrale, sostituirà quelle celle unite vuote con i valori che dovrebbero essere lì, e la vita è fantastica così.
Power Query è trattato nel mio libro, ma è anche trattato in questo libro da Ken Puls e Miguel Escobar, M sta per (DATA) MONKEY.
Va bene, quindi, Elizabeth: come faccio a sbarazzarmi delle celle di unione verticali? Il mio quartier generale continua a inviarmi questi file ogni giorno. Quindi, il piano: salveremo la cartella di lavoro in un luogo affidabile con un nome affidabile; creare una cartella di lavoro di reportistica vuota, Dati, Ottieni dati, Da file, Da cartella di lavoro, specificare il foglio, BAM! Le celle di unione sono sparite. Seleziono quella colonna e Fill Down, Close & Load; quindi ogni volta che ottieni una nuova cartella di lavoro, salva quella nuova cartella di lavoro in un luogo affidabile con lo stesso nome affidabile; Apro la cartella di lavoro Reporting e aggiorna, oppure impostiamo semplicemente la cartella di lavoro Reporting su Aggiorna automaticamente all'apertura.
Per scaricare la cartella di lavoro dal video di oggi è l'URL nella descrizione di YouTube.
Voglio ringraziare Elizabeth per essere stata al mio seminario a Calumet City, voglio ringraziarti per essere passato. Ci vediamo la prossima volta per un altro netcast da.
Scarica il file Excel
Per scaricare il file excel: fill-merged-cells-down.xlsx
Power Query è uno strumento straordinario: ti consente di risolvere una varietà di
saeed Alimohammadi
