David s Floride postavlja današnje pitanje:
Imam dvije radne bilježnice. Oba imaju iste podatke u stupcu A, ali ostali su stupci različiti. Kako mogu spojiti te dvije radne knjige?
Pitao sam Davida je li moguće da jedna radna bilježnica ima više zapisa od druge. A odgovor je Da. Pitao sam Davida pojavljuje li se ključno polje samo jednom u svakoj datoteci. Odgovor je također da. Danas ću to riješiti pomoću Power Queryja. Alati Power Query nalaze se u Windows verzijama programa Excel 2016+ u odjeljku Dohvati i transformiraj na kartici Podaci. Ako imate verzije programa Excel 2010 ili Excel 2013 za Windows, možete preuzeti dodatak Power Query za te verzije.
Evo Davidove radne bilježnice 1. Ima Product, a zatim tri stupca podataka.
Evo Davidove radne knjige 2. Ima kôd proizvoda, a zatim i druge stupce. U ovom primjeru postoje dodatni proizvodi u radnoj knjizi2, ali rješenja će funkcionirati ako bilo koja radna knjiga ima dodatne stupce.
Evo koraka:
-
Odaberite Podaci, Dohvati podatke, Iz datoteke, Iz radne knjige:
Učitaj podatke iz datoteke - Dođite do prve radne knjige i kliknite U redu
- U dijaloškom okviru Navigatora odaberite radni list s lijeve strane. (Čak i ako postoji samo jedan radni list, morate ga odabrati.) Podaci će se vidjeti s desne strane.
- U dijaloškom okviru Navigatora otvorite padajući meni Učitaj i odaberite Učitaj u …
- Odaberite Only Create a Connection i pritisnite OK.
-
Ponovite korake 1-5 za drugu radnu knjigu.
Stvorite vezu s radnom knjigom Ako ste napravili obje radne knjige, trebali biste vidjeti dvije veze na ploči Upiti i veze s desne strane zaslona programa Excel.
Veze s obje radne bilježnice Nastavite s koracima za spajanje radnih knjiga:
-
Podaci, Dohvati podatke, Kombiniraj upite, Spoji.
Spojite dva upita s različitim stupcima - Na vrhu padajućeg izbornika u dijaloškom spoju odaberite prvi upit.
- Na drugom padajućem izborniku u dijaloškom spoju odaberite drugi upit.
- Kliknite naslov proizvoda u gornjem pregledu (ovo je ključno polje. Imajte na umu da možete višestruko odabrati dva ili više ključnih polja pritiskom na Ctrl + Klik)
- Kliknite naslov Kôd proizvoda u drugom pregledu.
-
Otvorite vrstu pridruživanja i odaberite Full Outer (All Rows From Both)
Koraci 8 - 12 ilustrirani ovdje -
Kliknite U redu. Pregled podataka ne prikazuje suvišne retke i prikazuje "Tablica" više puta u zadnjem stupcu.
Ovo ne izgleda obećavajuće - Primijetite da se u naslovu DavidTwo nalazi ikona "Proširi". Kliknite tu ikonu.
-
Neobvezno, ali uvijek poništim odabir opcije "Koristi izvorni naziv stupca kao prefiks". Kliknite U redu.
Proširite polja iz radne bilježnice 2 Rezultati su prikazani u ovom pregledu:
Svi zapisi iz bilo koje radne bilježnice - U Power Queryu koristite Početna, Zatvori i Učitaj.
Evo lijepe značajke: ako se osnovni podaci u bilo kojoj radnoj knjizi promijene, možete kliknuti ikonu Osvježi da biste nove podatke unijeli u radnu knjigu rezultata.
Bilješka
Ikona za osvježavanje obično je skrivena. Povucite lijevi rub okna Upiti i veze ulijevo da biste otkrili ikonu.
Gledaj video
Prijepis videozapisa
Naučite Excel iz Podcasta, epizoda 2216: Kombinirajte dvije radne knjige na temelju zajedničkog stupca.
Hej, dobrodošao natrag na netcast, ja sam Bill Jelen. Današnje pitanje je poslao David, koji je bio na mom seminaru u Melbourneu na Floridi, za poglavlje Svemirske obale IIA.
David ima dvije različite radne bilježnice u kojima je stupac A zajednički obojica. Dakle, evo Radne sveske 1, evo Radne sveske 2 - obje imaju šifru proizvoda. Ovaj ima predmete koje prvi nema, ili obrnuto, a David želi kombinirati sve stupce. Dakle, ovdje imamo tri stupca i ovdje četiri stupca. Obje sam stavio u istu radnu knjižicu, u slučaju da radnu knjigu preuzimate za rad. Uzmite svaki od njih, premjestite ga u vlastitu radnu knjižicu i spremite.
U redu, za kombiniranje ovih datoteka koristit ćemo Power Query. Power Query ugrađen je u Excel 2016. Ako imate Windows verziju 10 ili 13, možete otići do Microsofta i preuzeti Power Query. Možete početi od nove prazne radne knjige s praznim radnim listom. Spremit ćete ovu datoteku - Spremite kao, znate, možda Radnu knjigu, da biste prikazali rezultate kombiniranih datoteka .xlsx. U redu? A ono što ćemo učiniti je, napravit ćemo dva upita. Idemo na Podaci, Dohvati podatke, Iz datoteke, Iz radne knjige, a zatim ćemo odabrati prvu datoteku. U pregledu odaberite list s vašim podacima i mi ne moramo ništa učiniti s tim podacima. Dakle, samo otvorite okvir za učitavanje i odaberite Učitaj u, Samo stvori vezu, kliknite U redu. Savršen. Ponovit ćemo to za drugu stavku - Podaci iz datoteke,Iz radne knjige odaberite DavidTwo, odaberite naziv lista, a zatim otvorite teret, Učitaj u, Samo stvori vezu. Vidjet ćete ovdje na ovoj ploči, prisutne su obje veze. U redu.
Sada stvarni posao - Podaci, Dohvati podatke, Kombiniraj upite, Spoji, a zatim u dijaloškom okviru Spoji odaberite DavidOne, DavidTwo i ovaj sljedeći korak potpuno je neintuitivan. To moraš učiniti. Odaberite zajednički stupac ili stupce - dakle Proizvod i Proizvod. U redu. A onda, budite vrlo oprezni ovdje s tipom pridruživanja. Želim sve retke iz oba, jer jedan može imati dodatni redak i to moram vidjeti, a zatim kliknemo U redu. U redu. I evo početnog rezultata. Ne izgleda kao da je djelovalo; ne čini se da je dodao dodatne stavke koje su bile u datoteci 2. I imamo ovaj stupac 5 - sada je ništavan. Kliknut ću desni klik na stupac 5 i reći: Ukloni taj stupac. Dakle, otvorite ovu ikonu za proširivanje i poništite ovaj okvir za Koristi izvorni naziv stupca kao prefiks i BAM! radi. Dakle, dodatne stavke koje su bile u datoteci 2, a nisu u datoteci 1,doista pojaviti.
U redu. Sada u današnjoj datoteci izgleda da je ovaj stupac Šifra proizvoda bolji od ovog stupca Proizvod, jer ima dodatne retke. Ali u budućnosti bi mogao postojati dan u kojem Workbook 1 ima stvari koje Workbook 2 nema. Dakle, ostavit ću obojicu tamo i neću se riješiti nule jer, na primjer, iako se čini da je ovaj redak na dnu potpuno ništavan, u budućnosti bi moglo doći do situacije da ovdje imamo nekoliko nula jer nešto nedostaje. U redu? Dakle, konačno, Close & Load, i imamo svojih šesnaest redova.
Sada, u budućnosti, recimo da se nešto promijeni. U redu, pa ćemo se vratiti na jednu od te dvije datoteke i promijenit ću klasu za Apple na 99, ajmo čak i umetnuti nešto novo i spremiti ovu radnu knjigu. U redu. A onda, ako želimo da se naša datoteka za spajanje ažurira, dođite ovamo-- sada, pripazite, kad to učinite prvi put, ne možete vidjeti ikonu Osvježi - morate uhvatiti ovu traku i povući je preko . I napravit ćemo Refresh, a učitano je 17 redaka, pojavljuje se lubenica, Apple se mijenja na 99 - to je prekrasna stvar. Hej, želiš li naučiti nešto o Power Queryju? Kupite ovu knjigu Kena Pulsa i Miguela Escobara, M je za (DATA) MAJMUNA. Ubrzati ću vas.
Zaključak danas: David s Floride ima dvije radne bilježnice koje želi kombinirati; obojica imaju ista polja u stupcu A, ali svi su ostali stupci različiti; jedna bi radna knjiga mogla imati dodatne predmete kojih nema u drugoj, a David ih želi; nema duplikata ni u jednoj datoteci; koristit ćemo upit za napajanje da bismo to riješili, pa započnite s novom praznom radnom knjigom na praznom radnom listu; napravit ćete tri upita, prvi - Data, From File, Workbook, a zatim Load to only Created Connection; ista stvar za drugu radnu knjigu, a zatim Podaci, Dohvati podatke, Spoji, odaberite dvije veze, odaberite stupac koji je uobičajen u obje - u mom slučaju, Proizvod - a zatim iz vrste Pridruži se želite potpuno pridružiti sve iz datoteke 1, sve iz datoteke 2. A onda je lijepa stvar ako se osnovni podaci promijene,možete samo osvježiti upit.
Da biste preuzeli radnu knjigu iz današnjeg videozapisa, posjetite URL u opisu YouTubea.
Pa, hej, želim poput Davida što se pojavio na mom seminaru, želim vam zahvaliti što ste navratili. Vidimo se sljedeći put za još jedan prijenos od.
Preuzmite datoteku Excel
Da biste preuzeli excel datoteku: kombinirajte-na-osnovi-zajedničkog-stupca.xlsx
Power Query nevjerojatan je alat u programu Excel.
Excel misao dana
Pitao sam svoje prijatelje Excel Master za savjet o Excelu. Današnja misao za razmišljanje:
"Uvijek pritisnite F4 kada čitate raspon ili matricu u funkciji"
Tanja Kuhn
