A floridai David felteszi a mai kérdést:
Két munkafüzetem van. Mindkettőnek ugyanazok az adatai vannak az A oszlopban, de a többi oszlop eltérő. Hogyan egyesíthetem ezt a két munkafüzetet?
Megkérdeztem Dávidot, lehetséges-e, hogy az egyik munkafüzet több rekordot tartalmaz, mint a másik. És a válasz Igen. Megkérdeztem Davidet, hogy a kulcsmező csak egyszer jelenik-e meg minden fájlban. A válasz is igen. Ma ezt megoldom a Power Query segítségével. A Power Query eszközök az Excel 2016+ Windows verzióiban találhatók az Adatok lap Get & Transform szakaszában. Ha az Excel 2010 vagy az Excel 2013 Windows verzióival rendelkezik, letöltheti az adott verziókhoz tartozó Power Query bővítményt.
Itt van David 1. munkafüzete. A termék, majd három oszlop adat található.
Itt található David 2. munkafüzete. Termékkóddal, majd további oszlopokkal rendelkezik. Ebben a példában vannak extra termékek a 2. munkafüzetben, de a megoldások akkor működnek, ha bármelyik munkafüzetnek vannak további oszlopai.
Itt vannak a lépések:
-
Válassza az Adatok, Adatok lekérése, Fájlból, Munkafüzetből lehetőséget:
Adatok betöltése egy fájlból - Keresse meg az első munkafüzetet, és kattintson az OK gombra
- A Navigátor párbeszédpanelen válassza ki a bal oldali munkalapot. (Még akkor is, ha csak egy munkalap van, ki kell választania.) A jobb oldalon láthatja az adatokat.
- A Navigátor párbeszédpanelen nyissa meg a Betöltés legördülő menüt, és válassza a Betöltés…
- Válassza a Csak a kapcsolat létrehozása lehetőséget, és nyomja meg az OK gombot.
-
Ismételje meg az 1–5. Lépéseket a második munkafüzethez.
Hozzon létre kapcsolatot a munkafüzettel Ha elkészítette mindkét munkafüzetet, két kapcsolatot kell látnia az Excel képernyő jobb oldalán található Lekérdezések és kapcsolatok panelen.
Csatlakozások mindkét munkafüzethez Folytassa a munkafüzetek egyesítésének lépéseivel:
-
Adatok, Adatok lekérése, Lekérdezések egyesítése, Egyesítés.
Két lekérdezés egyesítése különböző oszlopokkal - Az Egyesítés párbeszédpanel legfelső legördülő menüjéből válassza ki az első lekérdezést.
- Az Egyesítés párbeszédpanel második legördülő menüjéből válassza ki a második lekérdezést.
- Kattintson a Termék fejlécre a felső előnézetben (ez a kulcsmező. Megjegyzés: két vagy több kulcsmezőt többször is kijelölhet a Ctrl + kattintással)
- A második előnézetben kattintson a Termék kód fejlécre.
-
Nyissa meg a Csatlakozás típusát, és válassza a Teljes külső (Mindkét sor mindkét sora) lehetőséget
Az itt bemutatott 8–12. Lépések -
Kattintson az OK gombra. Az adatok előnézete nem mutatja az extra sorokat, és csak az utolsó oszlopban mutatja meg ismételten a "Táblázat" szót.
Ez nem tűnik ígéretesnek - Figyelje meg, hogy a DavidTwo fejlécben található egy "kibontás" ikon. Kattintson az ikonra.
-
Nem kötelező, de mindig törlöm az "Eredeti oszlopnév használata előtagként" jelölést. Kattintson az OK gombra.
Bontsa ki a 2. munkafüzet mezőit Az eredmények ebben az előnézetben láthatók:
Mindegyik munkafüzet összes bejegyzése - Az Power Query alkalmazásban használja a Home, a Close & Load elemet.
Itt van a gyönyörű szolgáltatás: ha az egyik munkafüzet alapadatai megváltoznak, a Frissítés ikonra kattintva új adatokat vonhat be az eredmények munkafüzetébe.
jegyzet
A Frissítés ikon általában rejtve van. Az ikon megjelenítéséhez húzza balra a Lekérdezések és kapcsolatok ablaktábla bal szélét.
Videót nézni
Videó átirat
Az Excel megismerése a Podcast 2216-os epizódjából: Két munkafüzet kombinálása egy közös oszlop alapján.
Hé, üdvözlöm újra a netcasten, Bill Jelen vagyok. A mai kérdés Davidtől származik, aki részt vett a szemináriumomon a floridai Melbourne-ben, az IIA űrparti fejezete számára.
Davidnek két különböző munkafüzete van, ahol az A oszlop mindkettőjükben közös. Tehát itt van az 1. munkafüzet, itt a 2. munkafüzet - mindkettőnek van termékkódja. Ebben vannak olyan elemek, amelyek az elsőben nincsenek, vagy fordítva, és David az összes oszlopot össze akarja kapcsolni. Tehát itt van három oszlopunk és itt négy oszlopunk. Mindkettőt ugyanabba a munkafüzetbe tettem, hátha letölti a munkafüzetet, hogy működjön együtt. Vigye mindegyiket, helyezze át a saját munkafüzetébe, és mentse el.
Rendben, ezeknek a fájloknak az egyesítéséhez a Power Query-t fogjuk használni. A Power Query be van építve az Excel 2016-ba. Ha a 10-es vagy a 13-as Windows verzióját használja, lépjen ki a Microsofthoz és töltse le a Power Query alkalmazást. Kezdhet egy új üres munkafüzetből egy üres munkalap segítségével. Menteni fogja ezt a fájlt - Mentse más néven, esetleg munkafüzetként, hogy megmutassa az egyesített .xlsx fájlok eredményeit. Rendben? És amit meg fogunk tenni, két kérdést fogunk megtenni. Megyünk az Adatok, Adatok lekérése, Fájlból, Munkafüzetből, majd kiválasztjuk az első fájlt. Az előnézetben válassza ki az adatlapot tartalmazó lapot, és nekünk nem kell semmit sem tennünk ezekkel az adatokkal. Tehát csak nyissa meg a betöltődobozt, és válassza a Betöltés, Csak a kapcsolat létrehozása lehetőséget, kattintson az OK gombra. Tökéletes. Most megismételjük ezt a második elemnél - Data, From File,A munkafüzetből válassza a DavidTwo lehetőséget, válassza ki a lap nevét, majd nyissa meg a betöltést, a Betöltés, Csak kapcsolat létrehozása lehetőséget. Itt láthatja ebben a panelben, mindkét kapcsolatunk jelen van. Rendben.
Most a tényleges munka - Adatok, Adatok lekérése, Lekérdezések összevonása, Egyesítés, majd az Egyesítés párbeszédpanelen válassza a DavidOne, a DavidTwo lehetőséget, és ez a következő lépés teljesen ésszerűtlen. Ezt meg kell tennie. Válassza ki a közös oszlopot vagy oszlopokat - így a Termék és a Termék. Rendben. És akkor itt legyen nagyon óvatos a csatlakozás típusával. Szeretném az összes sort mindkettőből, mert az egyiknek lehet egy extra sora, és ezt látnom kell, majd kattintson az OK gombra. Rendben. És itt van a kezdeti eredmény. Nem úgy tűnik, hogy működött; nem úgy néz ki, mintha hozzáadta volna azokat a további elemeket, amelyek a 2. fájlban voltak. És megvan ez az 5. oszlop - ez most semmis. Kattintson a jobb gombbal az 5. oszlopra, és azt mondom: Távolítsa el azt az oszlopot. Nyissa meg ezt a kibontás ikont, és törölje a jelölést az Eredeti oszlopnév használata előtagként és a BAM jelölőnégyzetből. működik. Tehát azok a további elemek, amelyek a 2. fájlban voltak, amelyek nincsenek az 1. fájlban,megjelennek.
Rendben. A mai fájlban úgy tűnik, hogy ez a Termékkód oszlop jobb, mint ez a Termék oszlop, mert extra sorai vannak. De a jövőben előfordulhat olyan nap, amikor az 1. munkafüzet olyan dolgokkal rendelkezik, amelyek nincsenek a 2. munkafüzetben. Tehát mindkettőjüket ott hagyom, és nem fogok megszabadulni semmilyen nullától, mert hasonlóan annak ellenére, hogy ez az alsó sor teljesen nullának tűnik, a jövőben előfordulhat olyan helyzet, hogy van itt néhány null, mert valami hiányzik. Rendben? Végül a Bezárás és betöltés, és megvan a tizenhat sorunk.
Tegyük fel, hogy a jövőben valami megváltozik. Rendben, szóval visszatérünk a két fájl egyikére, és az Apple osztályát 99-re változtatom, sőt helyezzünk be valami újat, és mentsük el ezt a munkafüzetet. Rendben. És ha azt akarjuk, hogy az egyesítési fájlunk frissüljön, jöjjön ide - most, vigyázzon, amikor ezt először teszi meg, nem látja a Frissítés ikont - meg kell ragadnia ezt a sávot, és áthúznia . És elvégezzük a Frissítést, és 17 sor betöltve megjelenik a görögdinnye, az Apple 99-re változik - ez egy szép dolog. Hé, meg akarja tudni a Power Query-t? Vedd meg Ken Puls és Miguel Escobar könyvét, M (DATA) MONKEY. Felgyorsítalak.
Összeállítás ma: A floridai Davidnek két munkafüzete van, amelyeket össze akar kapcsolni; mindkettőjüknek ugyanazok a mezői vannak az A oszlopban, de a többi oszlop mind különbözik; az egyik munkafüzetben lehetnek olyan elemek, amelyek nincsenek a másikban, és David ezeket szeretné; egyik fájlban sem található másolat; A power lekérdezést fogjuk használni ennek megoldására, ezért kezdjen egy új üres munkafüzetbe egy üres munkalapon; három lekérdezést fog végrehajtani, az elsőt - Adatok, Fájlból, Munkafüzet, majd Betöltés csak a létrehozott kapcsolatba; ugyanez a helyzet a második munkafüzettel, majd az Adatok, Adatok beolvasása, Összevonás lehetőség kiválasztásával válassza ki a két kapcsolatot, válassza ki az oszlopot, amely mindkettőnél szokásos - az én esetemben a Terméknél -, majd a Csatlakozás típusból válassza ki a teljes csatlakozást mind az 1. fájlból, mind a 2. fájlból. És akkor az a szép, ha az alapul szolgáló adatok megváltoznak,csak frissítheti a lekérdezést.
A munkafüzet letöltéséhez a mai videóból keresse fel a YouTube leírásában található URL-t.
Nos, hé, szeretném, mint David, hogy megjelent a szemináriumon, szeretném megköszönni, hogy betértél. Találkozunk legközelebb egy másik netcast-re.
Töltse le az Excel fájlt
Az Excel fájl letöltése: Combine-based-on-common-column.xlsx
A Power Query egy csodálatos eszköz az Excelben.
Excel gondolata a napnak
Az Excel Master barátaimtól kértem tanácsokat az Excel-mel kapcsolatban. Mai gondolat, hogy elgondolkodjak:
"Mindig nyomja meg az F4 billentyűt, amikor egy függvényben tartományt vagy mátrixot olvas"
Tanja Kuhn
