Combina in base a una colonna comune - Suggerimenti per Excel

David dalla Florida pone la domanda di oggi:

Ho due cartelle di lavoro. Entrambi hanno gli stessi dati nella colonna A, ma le colonne rimanenti sono diverse. Come posso unire queste due cartelle di lavoro?

Ho chiesto a David se è possibile che una cartella di lavoro abbia più record dell'altra. E la risposta è si. Ho chiesto a David se il campo chiave appare solo una volta in ogni file. Anche la risposta è sì. Oggi risolverò questo problema con Power Query. Gli strumenti di Power Query si trovano nelle versioni Windows di Excel 2016+ nella sezione Ottieni e trasforma della scheda Dati. Se disponi di versioni Windows di Excel 2010 o Excel 2013, puoi scaricare il componente aggiuntivo Power Query per tali versioni.

Ecco la cartella di lavoro di David 1. Ha prodotto e poi tre colonne di dati.

La prima cartella di lavoro

Ecco la cartella di lavoro di David 2. Ha il codice prodotto e poi altre colonne. In questo esempio, sono presenti prodotti aggiuntivi in ​​workbook2, ma le soluzioni funzioneranno se una delle cartelle di lavoro ha colonne aggiuntive.

La seconda cartella di lavoro

Ecco i passaggi:

  1. Seleziona dati, Ottieni dati, Da file, Da cartella di lavoro:

    Carica i dati da un file
  2. Individua la prima cartella di lavoro e fai clic su OK
  3. Nella finestra di dialogo Navigatore, scegli il foglio di lavoro a sinistra. (Anche se c'è un solo foglio di lavoro, devi selezionarlo.) Vedrai i dati sulla destra.
  4. Nella finestra di dialogo Navigatore, apri il menu a discesa Carica e scegli Carica in …
  5. Scegli Solo Crea una connessione e premi OK.

  6. Ripetere i passaggi 1-5 per la seconda cartella di lavoro.

    Crea una connessione alla cartella di lavoro

    Se hai eseguito entrambe le cartelle di lavoro, dovresti vedere due connessioni nel pannello Query e connessioni a destra della schermata di Excel.

    Connessioni a entrambe le cartelle di lavoro

    Continua con i passaggi per unire le cartelle di lavoro:

  7. Dati, Ottieni dati, Combina query, Unisci.

    Unisci due query con colonne diverse
  8. Dall'elenco a discesa in alto nella finestra di dialogo Unisci, scegli la prima query.
  9. Dal secondo menu a discesa nella finestra di dialogo Unisci, scegli la seconda query.
  10. Fare clic sull'intestazione Prodotto nell'anteprima superiore (questo è il campo chiave. Nota che è possibile selezionare più di due o più campi chiave premendo Ctrl + clic)
  11. Fare clic sull'intestazione Codice prodotto nella seconda anteprima.

  12. Apri il Tipo di join e scegli Full Outer (All Rows From Both)

    I passaggi 8-12 illustrati qui

  13. Fare clic su OK. L'anteprima dei dati non mostra le righe aggiuntive e mostra solo "Tabella" ripetutamente nell'ultima colonna.

    Questo non sembra promettente
  14. Notare che è presente un'icona "Espandi" nell'intestazione di DavidTwo. Fare clic sull'icona.

  15. Facoltativo, ma deseleziono sempre "Usa nome colonna originale come prefisso". Fare clic su OK.

    Espandi i campi dalla cartella di lavoro 2

    I risultati sono mostrati in questa anteprima:

    Tutti i record da entrambe le cartelle di lavoro
  16. In Power Query, usa Home, Chiudi e carica.

Ecco la bella caratteristica: se i dati sottostanti in una delle cartelle di lavoro cambiano, puoi fare clic sull'icona Aggiorna per inserire nuovi dati nella cartella di lavoro dei risultati.

Ripetere i passaggi 1-16 facendo clic su questa icona Aggiorna.

Nota

L'icona per Aggiorna è solitamente nascosta. Trascina il bordo sinistro del riquadro Query e connessioni verso sinistra per rivelare l'icona.

Guarda un video

Trascrizione del video

Impara Excel da Podcast, episodio 2216: Combina due cartelle di lavoro in base a una colonna comune.

Ehi, bentornato al netcast, sono Bill Jelen. La domanda di oggi è di David, che era al mio seminario a Melbourne, Florida, per lo Space Coast Chapter dell'IIA.

David ha due diverse cartelle di lavoro in cui la colonna A è in comune tra di loro. Quindi, ecco la cartella di lavoro 1, ecco la cartella di lavoro 2: entrambi hanno il codice prodotto. Questo ha elementi che il primo non ha, o viceversa, e David vuole combinare tutte le colonne. Quindi, abbiamo tre colonne qui e quattro colonne qui. Li metto entrambi nella stessa cartella di lavoro, nel caso in cui tu stia scaricando la cartella di lavoro su cui lavorare. Prendi ognuno di questi, spostalo nella sua cartella di lavoro e salvalo.

Va bene, per combinare questi file, useremo Power Query. Power Query è integrato in Excel 2016. Se utilizzi la versione Windows 10 o 13, puoi andare su Microsoft e scaricare Power Query. Puoi iniziare da una nuova cartella di lavoro vuota con un foglio di lavoro vuoto. Stai per salvare questo file - Salva come, sai, forse Cartella di lavoro, per mostrare i risultati dei file combinati .xlsx. Tutto a posto? E quello che faremo è fare due domande. Andremo su Dati, Ottieni dati, Da file, Da cartella di lavoro e quindi sceglieremo il primo file. In un'anteprima, seleziona il foglio che contiene i tuoi dati e non dobbiamo fare nulla per questi dati. Quindi apri la casella di caricamento e scegli Carica su, Crea solo connessione, fai clic su OK. Perfetto. Ora, lo ripeteremo per il secondo elemento: Dati, Da file,Da una cartella di lavoro, scegli DavidTwo, scegli il nome del foglio, quindi apri il caricamento, Carica in, Crea solo una connessione. Vedrai qui in questo pannello, abbiamo entrambe le connessioni presenti. Tutto a posto.

Ora il lavoro effettivo: Dati, Ottieni dati, Combina query, Unisci e quindi nella finestra di dialogo Unisci, scegli DavidOne, DavidTwo e questo passaggio successivo non è del tutto intuitivo. Devi farlo. Scegli la colonna o le colonne in comune, quindi Prodotto e Prodotto. Tutto a posto. E poi, stai molto attento qui con il tipo di join. Voglio tutte le righe da entrambi perché una potrebbe avere una riga in più e devo vederla, quindi fare clic su OK. Tutto a posto. Ed ecco il risultato iniziale. Non sembra che abbia funzionato; non sembra che abbia aggiunto gli elementi extra che erano nel file 2. E abbiamo questa colonna 5 - ora è nulla. Faccio clic con il pulsante destro del mouse sulla colonna 5 e dico: Rimuovi quella colonna. Quindi apri questa icona di espansione e deseleziona questa casella per Usa il nome della colonna originale come prefisso e BAM! Funziona. Quindi gli elementi extra che erano nel File 2, che non sono nel File 1,appaiono.

Tutto a posto. Ora nel file di oggi, sembra che questa colonna Codice prodotto sia migliore di questa colonna Prodotto, perché ha righe extra. Ma potrebbe esserci un giorno in futuro in cui la cartella di lavoro 1 ha cose che la cartella di lavoro 2 non ha. Quindi li lascerò entrambi lì e non mi libererò di nessun valore nullo perché, ad esempio, anche se questa riga in basso sembra essere completamente nulla, potrebbe esserci in futuro una situazione in cui abbiamo alcuni valori nulli qui perché manca qualcosa. Tutto a posto? Quindi, finalmente, Close & Load, e abbiamo le nostre sedici righe.

Ora, in futuro, diciamo che qualcosa cambia. Va bene, quindi torneremo a uno di questi due file e cambierò la classe per Apple in 99, e inseriremo anche qualcosa di nuovo e salveremo questa cartella di lavoro. Tutto a posto. E poi, se vogliamo che il nostro file di unione si aggiorni, vieni qui - ora, fai attenzione, quando lo fai la prima volta, non puoi vedere l'icona Aggiorna - devi prendere questa barra e trascinarla . E faremo Aggiorna e 17 righe caricate, appare l'anguria, la Apple cambia in 99: è una cosa bellissima. Ora, ehi, vuoi saperne di più su Power Query? Acquista questo libro di Ken Puls e Miguel Escobar, M è per (DATA) MONKEY. Ti metto al corrente.

Conclusione oggi: David dalla Florida ha due cartelle di lavoro che vuole combinare; entrambi hanno gli stessi campi nella colonna A, ma le altre colonne sono tutte diverse; una cartella di lavoro potrebbe avere elementi extra che non sono nell'altra e David li vuole; non ci sono duplicati in nessuno dei due file; useremo power query per risolvere questo problema, quindi inizia in una nuova cartella di lavoro vuota su un foglio di lavoro vuoto; farai tre query, la prima: Dati, Da file, Cartella di lavoro e poi Carica solo nella connessione creata; la stessa cosa per la seconda cartella di lavoro, quindi Dati, Ottieni dati, Unisci, seleziona le due connessioni, seleziona la colonna che è comune in entrambe - nel mio caso, Prodotto - e poi dal Tipo di unione, vuoi unirti completo tutto dal File 1, tutto dal File 2. E poi la cosa bella è che se i dati sottostanti cambiano,puoi semplicemente aggiornare la query.

Per scaricare la cartella di lavoro dal video di oggi, visita l'URL nella descrizione di YouTube.

Beh, ehi, voglio come David per essersi presentato al mio seminario, voglio ringraziarti per essere passato. Ci vediamo la prossima volta per un altro netcast da.

Scarica il file Excel

Per scaricare il file excel: combina basato su colonna comune.xlsx

Power Query è uno strumento straordinario in Excel.

Excel pensiero del giorno

Ho chiesto ai miei amici di Excel Master per i loro consigli su Excel. Pensiero di oggi su cui riflettere:

"Premi sempre F4 quando leggi intervallo o matrice in una funzione"

Tanja Kuhn

Articoli interessanti...