David fra Florida stiller dagens spørgsmål:
Jeg har to projektmapper. Begge har de samme data i kolonne A, men de resterende kolonner er forskellige. Hvordan kan jeg flette disse to projektmapper?
Jeg spurgte David, om det er muligt, at den ene projektmappe har flere poster end den anden. Og svaret er ja. Jeg spurgte David, om nøglefeltet kun vises en gang i hver fil. Svaret er også ja. I dag løser jeg dette med Power Query. Power Query-værktøjerne findes i Windows-versioner af Excel 2016+ i afsnittet Hent og transformer under fanen Data. Hvis du har Windows-versioner af Excel 2010 eller Excel 2013, kan du downloade Power Query-tilføjelsesprogrammet til disse versioner.
Her er Davids arbejdsbog 1. Den har produkt og derefter tre kolonner med data.
Her er Davids arbejdsbog 2. Den har produktkode og derefter andre kolonner. I dette eksempel er der ekstra produkter i projektmappe2, men løsningerne fungerer, hvis en projektmappe har ekstra kolonner.
Her er trinene:
-
Vælg Data, Hent data, Fra fil, Fra projektmappe:
Indlæs data fra en fil - Gå til den første projektmappe, og klik på OK
- I dialogboksen Navigator skal du vælge regnearket til venstre. (Selvom der kun er et regneark, skal du vælge det.) Du vil se dataene til højre.
- I dialogboksen Navigator skal du åbne rullemenuen Load og vælge Load To …
- Vælg kun Opret en forbindelse, og tryk på OK.
-
Gentag trin 1-5 for den anden projektmappe.
Opret en forbindelse til projektmappen Hvis du har lavet begge projektmapper, skal du se to forbindelser i forespørgsler og forbindelsespanelet til højre på din Excel-skærm.
Forbindelser til begge projektmapper Fortsæt med trinnene for at flette arbejdsbøgerne:
-
Data, få data, kombiner forespørgsler, flet.
Flet to forespørgsler med forskellige kolonner - Vælg den første forespørgsel fra den øverste rullemenu i fletdialogen.
- Fra den anden rullemenu i fletdialogen skal du vælge den anden forespørgsel.
- Klik på produktoverskriften i det øverste eksempel (dette er nøglefeltet. Bemærk, at du kan vælge flere eller flere nøglefelter ved at trykke Ctrl + klikke)
- Klik på overskriften Produktkode i det andet eksempel.
-
Åbn Tilslutningstype, og vælg Fuld ydre (Alle rækker fra begge)
Trin 8 - 12 illustreret her -
Klik på OK. Dataeksemplet viser ikke de ekstra rækker og viser kun "Tabel" gentagne gange i den sidste kolonne.
Dette ser ikke lovende ud - Bemærk, at der er et "Udvid" -ikon i overskriften til DavidTwo. Klik på ikonet.
-
Valgfrit, men jeg fravælger altid "Brug originalt søjlenavn som præfiks". Klik på OK.
Udvid felterne fra projektmappe 2 Resultaterne vises i denne forhåndsvisning:
Alle poster fra begge projektmapper - I Power Query skal du bruge Home, Close & Load.
Her er den smukke funktion: Hvis de underliggende data i en projektmappe ændres, kan du klikke på ikonet Opdater for at trække nye data ind i resultatmappen.
Bemærk
Ikonet for Opdater er normalt skjult. Træk venstre kant af forespørgsler og forbindelsesruden til venstre for at afsløre ikonet.
Se video
Videoudskrift
Lær Excel fra Podcast, afsnit 2216: Kombiner to projektmapper baseret på en fælles kolonne.
Hej, velkommen tilbage til netcast, jeg er Bill Jelen. Dagens spørgsmål er fra David, som var på mit seminar i Melbourne, Florida, for Space Coast-kapitlet i IIA.
David har to forskellige arbejdsbøger, hvor kolonne A er fælles mellem dem begge. Så her er projektmappe 1, her er projektmappe 2 - begge har produktkode. Denne har emner, som den første ikke har, eller omvendt, og David vil kombinere alle kolonnerne. Så vi har tre kolonner her og fire kolonner her. Jeg sætter begge disse i samme projektmappe, hvis du downloader projektmappen til at arbejde sammen. Tag hver enkelt af disse, flyt den ud til sin egen projektmappe, og gem den.
Okay, for at kombinere disse filer skal vi bruge Power Query. Power Query er indbygget i Excel 2016. Hvis du er i Windows-versionen af 10 eller 13, kan du gå ud til Microsoft og downloade Power Query. Du kan starte fra en ny tom projektmappe med et tomt regneark. Du skal gemme denne fil-- Gem som, du ved, måske projektmappe, for at vise resultaterne af kombinerede filer .xlsx. I orden? Og hvad vi skal gøre er, vi skal lave to forespørgsler. Vi går til Data, Hent data, Fra fil, Fra projektmappe, og så vælger vi den første fil. I et eksempel skal du vælge det ark, der har dine data, og vi behøver ikke gøre noget ved disse data. Så bare åbn belastningsboksen, og vælg Indlæs til, Opret kun forbindelse, klik på OK. Perfekt. Nu gentager vi det for det andet element - Data, fra fil,Vælg DavidTwo fra en projektmappe, vælg arknavnet, og åbn derefter belastningen, Load To, Only Create a Connection. Du ser her i dette panel, vi har begge forbindelser til stede. I orden.
Nu er det faktiske arbejde - Data, Hent data, Kombiner forespørgsler, Flet, og vælg derefter DavidOne, DavidTwo i dialogboksen Flet, og dette næste trin er helt uintuitivt. Du skal gøre dette. Vælg kolonnen eller kolonnerne til fælles - så Produkt og produkt. I orden. Og vær meget forsigtig her med tilslutningstypen. Jeg vil have alle rækker fra begge, fordi man muligvis har en ekstra række, og det skal jeg se, og så klikker vi på OK. I orden. Og her er det oprindelige resultat. Det ser ikke ud til at det fungerede; det ser ikke ud som om det tilføjede de ekstra emner, der var i fil 2. Og vi har denne kolonne 5 - den er nul nu. Jeg skal højreklikke på kolonne 5 og sige, Fjern den kolonne. Så åbn dette udvidelsesikon og fjern markeringen i dette felt for Brug originalt kolonnenavn som præfiks og BAM! det virker. Så de ekstra emner, der var i fil 2, der ikke er i fil 1,vises.
I orden. Nu i dagens fil ser det ud til, at denne produktkodekolonne er bedre end denne produktkolonne, fordi den har ekstra rækker. Men der kan være en dag i fremtiden, hvor Arbejdsbog 1 har ting, som Arbejdsbog 2 ikke har. Så jeg vil lade dem begge være der, og jeg vil ikke slippe af med nuller, for ligesom, selvom denne række i bunden ser ud til at være helt nul, kan der i fremtiden være en situation, hvor vi har et par nuller her, fordi der mangler noget. I orden? Så endelig, Close & Load, og vi har vores seksten rækker.
Lad os nu sige, at noget ændrer sig i fremtiden. Okay, så vi går tilbage til en af disse to filer, så ændrer jeg klassen for Apple til 99, og lad os endda indsætte noget nyt og gemme denne projektmappe. I orden. Og så, hvis vi ønsker, at vores fusionsfil skal opdateres, kom herover - nu, pas på, når du gør dette første gang, kan du ikke se ikonet Opdater - du er nødt til at gribe denne bar og trække den over . Og vi gør Opdater, og 17 rækker er indlæst, vandmelonen vises, Apple skifter til 99 - det er en smuk ting. Nu, hej, vil du lære mere om Power Query? Køb denne bog af Ken Puls og Miguel Escobar, M er til (DATA) ABE. Jeg får dig i gang.
Afslutning i dag: David fra Florida har to projektmapper, som han vil kombinere; de har begge de samme felter i kolonne A, men de andre kolonner er alle forskellige; den ene projektmappe kan have ekstra ting, der ikke er i den anden, og David vil have dem; der er ingen dubletter i begge filer; vi bruger strømforespørgsel til at løse dette, så start i en ny tom projektmappe på et tomt regneark; du vil udføre tre forespørgsler, først en - Data, Fra fil, projektmappe og derefter Indlæs til kun oprettet forbindelse; det samme for den anden projektmappe, og derefter Data, Hent data, Flet, vælg de to forbindelser, vælg den kolonne, der er almindelig i begge - i mit tilfælde Produkt - og derefter fra Tilslutningstype, vil du have fuld tilslutning alt fra fil 1, alt fra fil 2. Og så er det smukke, hvis de underliggende data ændres,du kan bare opdatere forespørgslen.
For at downloade projektmappen fra dagens video skal du besøge URL'en i YouTube-beskrivelsen.
Nå, hej, jeg vil gerne have David som mødt til mit seminar, jeg vil gerne takke dig for at komme forbi. Vi ses næste gang til endnu en netcast fra.
Download Excel-fil
For at downloade excel-filen: combine-based-on-common-column.xlsx
Power Query er et fantastisk værktøj i Excel.
Excel-tanken om dagen
Jeg har bedt mine Excel Master-venner om deres råd om Excel. Dagens tanke at tænke over:
"Tryk altid på F4, når du læser rækkevidde eller matrix i en funktion"
Tanja Kuhn
