Kezdőlap › Fórumok › Excel programozás › [Resolved] Adatok kigyűjtése több excel állományból.
- This topic has 19 hozzászólás, 3 résztvevő, and was last updated 5 years, 3 months telt el by Sutyi73.
-
SzerzőBejegyzés
-
2019-08-12-08:54 #6125
Üdv! Segítséget szeretnék kérni.
Adott egy könyvtár, amiben xlsx fileok vannak (d:\2019.08\). Egy táblázatban kellene összesíteni ezeknek a fileoknak adatait. Ezt hogyan lehet kivitelezni. Az A oszlopban levő számok egy egy embert jelentenek, a G oszlopban levők pedig munkaórákat. A két oszlop közötti többi oszulop adatai most nem kellenek. A cél, hogy egy táblázatban ezeket összesítsük. A könyvtárban az xlsx állományok 2019.08.04 N, 2019.08.04 É, 2019.08.05 N stb. elnevezésűek. Kérlek ha tudtok segítsetek.2019-08-12-09:56 #6126Szia!
Ha jól értem, akkor a megoldás menete az lenne, hogy a mappában lévő összes fájlt bemásolod egy új táblázatba egymás alá, közös fejléccel, (Elég csak a szükséges oszlopokat akár), majd csinálsz belóle egy PIVOT táblát.
Ez megtehető
– Manuálisan, ha nincs sok fájl, és nem gyakori a feladat
– Lehet rá makrót készíteni
– Lehet automatizálni PowerQuery-velMivel nem tudom milyen szinten állsz Excel-ben, csak a kérdésből lehet találgatni, egyelőre ennyi
Imre
2019-08-12-11:21 #6127Szia! Nem az egész fájlt szeretném bemásolni, csak meghatározott oszlopokat. Jelen esetben az „a” oszlopból a személyeket, és a hozzájuk tartozó munkaidőket „r” oszlopból valamint a dátumot. Egymás alá. Ez a művelet havi szinten elvégzendő, minden naphoz két db xlsx fálj tartozik. Én makróra gondoltam. Közepes felhasználói szinten vagyok. A mellékelt fálj mutatja mire gondolok (elnagyolva).
Köszönettel Tibor
- A hozzászólás módosításra került: 5 years, 4 months telt el-Sutyi73.
Attachments:
You must be logged in to view attached files.2019-08-12-12:09 #6130Hány ilyet kell egybemásolni?
A számokhoz emberek tartoznak, gondolom az egy másik fájlban van, hogy kinek mi a száma, és a végső riport nevekhez rendel összesített adatokat.Ha makrózni nem tudsz, akkor mást kell kitalálni.
Ez a feladat PowerQuery-vel egy fél óra alatt megcsinálható.
Excel 2016-tól felfelé minden Excelben menü szinten benne van.Csatlakozol egy mappához és az abban lévő összes xlsx fájlt összefűzi.
Majd megadhatod, hogy minden fájlban milyen átalakításokat végezzen el. Pl. csak azt a két oszlopot hozza át, illetve egy fejlécet is ki kell találni, mert a minta fájlban legalább is nincs értelmezhető fejléc.A műveletsort megjegyzi, tehát olyan, mint egy makró. Legközelebb csak a forrásmappa útvonalát kell megváltoztatni, és frissítés után az abban lévő fájlokat is összefűzi.
Süt, utolsó lépésként az összefűzés után még az összesítést is meg lehet csinálni, tehát a PIVOT tábla is kiváltható. Tovább megyek, ha megvan sz összesítés a számokra, akkor egy másik fájlból hozzá tudja tenni a számokhoz a neveket és egyéb infókat ha kell.
Részletesen nem tudom ide leírni, de mindenképpen ajánlom afigyelmedbe.
Kereséssel ezt találtamNálunk szokott lenni PowerQuery tanfolyam, legközelebb szeptember elejére van meghirdetve.
http://pentaschool.hu/excel/power-query-tanfolyam.phpImre
2019-08-13-07:34 #6131Üdv!
Eddig eljutottam, de nem jól működik. Mi lehet a hiba? A másolt adatokat szépen egymás alá kellene folyamatosan írnia, de nem úgy csinálja.Sub Munka1()
Dim wb As Workbook
Set wb = ActiveWorkbook
Dim directory As String, fileName As String, sheet As Worksheet
directory = „D:\2019.08\”
fileName = Dir(directory & „*.xlsx”)
Do While fileName <> „”
Workbooks.Open fileName:=directory & fileName, ReadOnly:=True
Range(„A2:E” & ActiveSheet.UsedRange.Rows.Count).Copy Destination = _ wb.Worksheets(„Célmunkalap”).Range(„A” & wb.Worksheets(„Célmunkalap”).UsedRange.Rows.Count + 1)
Windows(„Órák.xlsm”).Activate
Range(„A” & wb.Worksheets(„Célmunkalap”).UsedRange.Rows.Count + 1).Select
ActiveSheet.Paste
Workbooks(fileName).Close
fileName = Dir()
Loop
End Sub- A hozzászólás módosításra került: 5 years, 3 months telt el-Sutyi73.
2019-08-13-12:18 #6133Szia!
Mégis a makró mellett döntöttél? Nem jó döntés, de te tudod 🙂
Te írtad, vagy találtad, és próbálod faragni?
Modjuk leírhattad volna, hogy mit csinál, ahelyett, amit szeretnél.
Teszt adatok nélkül pedig egy csomó időmbe tellene próbálgatni.Tehát tesztelés és debug nélkül első ránézésre a folyamat nem tűnik rossznak, de a következő problémát látom:
Olyan, mintha kétszer próbálná megcsinálni az átmásolástEgyszer van egy Copy parancs, ami utána a Destination-ben mondja meg, hogy hová kellene másolni. (Itt különben a Destination:= kellene, tehát kimaradt egy kettőspont
Aztán lejjebb átvált az „órák” makrófüzetbe és a „Célmunkalap”-ra szintén bemásolná megint.
ha jól látom, akkor a makrós füzetben állva kell elindítani, különben nemlesz jó a működés.
Próbáld meg F8-al lépésenként futtatni és figyelni, hogy mi történik, fogod látni, hogy hol a hiba az algoritmusban. Persze ha elindul.Imre
P.S
ha kódot másolsz a post-ba, akkor miután bemásoltad, jelöld ki, és nyomd meg a formázó gombok közül a code felratot. Ez formázatlan állapotban hagyja, és simán lehet másolni az Excel VBA editorába, nem kell cserélgetni a rossz idézőjeleket jóra, stb…- A hozzászólás módosításra került: 5 years, 3 months telt el-horvimi.
2019-08-13-13:19 #6135Szia Imi!
Ugyanezeket az észrevételeket (dupla másolás, kettőspont hiánya) tettem én is a másik fórumon, ahova először feltette a kérdést Sutyi.
Üdv, Kati
2019-08-13-15:41 #6137Szia! Kiszedtem a duplázást, szépen dolgozik, csak az a baj, hogy nem egymás alá rakja a másolt adatokat, hanem egymásra, felülírja. Illetve nem tudom miért, de a célmunkalapon az ‘A19’ cellánál kezdi nem pedig az elején.
Sub Munka1() Dim wb As Workbook Set wb = ActiveWorkbook Dim directory As String, fileName As String, sheet As Worksheet directory = "D:\2019.08\" fileName = Dir(directory & "*.xlsx") Do While fileName <> "" Workbooks.Open fileName:=directory & fileName, ReadOnly:=True Range("A2:E" & ActiveSheet.UsedRange.Rows.Count).Copy Destination:=wb.Worksheets("Célmunkalap").Range("A" & wb.Worksheets("Célmunkalap").UsedRange.Rows.Count + 1) Workbooks(fileName).Close False fileName = Dir() Loop End Sub
- A hozzászólás módosításra került: 5 years, 3 months telt el-Sutyi73.
2019-08-13-23:37 #6140Nem tudom, hogy érted-e mit csináltál, de a tanácsom az, hogy ezt a usedrange dolgot felejtsd el, nagyon bizonytalanul működik. Ezzel próbálja meg megállapítani, hogy hol van a vége a használt tartománynak.
De például, ha kitörölsz sorokat, attól még azt hiszi, hogy a régi méret a helyes.Elvileg gondolom úgy működik, hogy a célmunkalap első sorában van a fejléc, és az első üres sorba szeretnéd rakatni vele az aktuális darabot.
Próbáld ki ezt kicserélni:
Destination:=wb.Worksheets("Célmunkalap").Range("A" & _ wb.Worksheets("Célmunkalap").Range("A1").Currentregion.Rows.Count + 1)
Imre
- A hozzászólás módosításra került: 5 years, 3 months telt el-horvimi.
2019-08-14-10:56 #6144Szia! Ez így jó. Már csak egy problémám van. A bal oldali táblázat azt mutatja, hogyan néz ki a végeredmény, a jobb oldalon látszódik, hogy az utolsó sorokat nem másolta át. Ennek mi lehet az oka? Köszönettel Sutyi
Attachments:
You must be logged in to view attached files.2019-08-14-12:08 #6149A Range(„A2:E” kezdetű sor elején is kijavítottad az ActiveSheet.UsedRange.Rows.Count részt?
Nem lenne világosabb 2 változót felvenni?
Pl. a megnyitott füzet lapján az eddig-, a Célmunkalapon pedig az ide változót adhatnád meg.
A másolandó füzet megnyitásakor
eddig=range(„A” & rows.count).end(xlup).row
ide=wb.worksheets(„Célmunkalap”).range(„A” & rows.count).end(xlup).row+1Másolás:
range(„A2:E” & eddig).copy wb.worksheets(„Célmunkalap”).range(„A” & ide)2019-08-14-17:23 #6150Köszönöm a segítséget, ez a rész sikerült.
2019-08-15-06:59 #6155Szívesen. Mi van a többi résszel?
2019-08-15-07:36 #6156Szia!
A többi rész: A cél állományban az „a” oszlopban szereplő számok alapján összesíteni a munkaórákat (egy külön munkalapon), tehát pl. az „A” oszlopban a 38-hoz tartozó személy „Kiss Pista” ledolgozott 56 órát.
A forrás állományból A legelső sorban szerepel a dátum, úgy hogy év, hó, nap, óra, perc, ezek külön cellában, ebből dátum formátumot csinálni, és ezt is átmásolni a cél állományba, a hozzá tartozó adatok elé, és a hátterét (a dátumnak) átszínezni mondjuk világoskékre. Egyenlőre ennyi, ezen gondolkodom, hogy hogyan lehet megcsinálni.2019-08-15-07:42 #6157Imre már írta, hogy „Teszt adatok nélkül pedig egy csomó időmbe tellene próbálgatni”.
Az időket úgy másold át, ahogy előtte az A:E tartományt.
Tegyél fel egy fájlt, amiben már összemásoltad az adatokat a különböző füzetekből.2019-08-16-15:54 #61592019-08-16-16:20 #6162Feltettem a módosított fájt, benne a hozzá tartozó mesével.
A füzetben rosszul írtam: nem az A:M, hanem az A:N tartomány adja a kimutatás alapját.
Az N oszlop FKERES függvénye az A oszlop ID-je alapján beírja a dolgozó nevét, ez szerepel a kimutatásban.- A hozzászólás módosításra került: 5 years, 3 months telt el-delila. Indok: Javítás
Attachments:
You must be logged in to view attached files.2019-08-16-16:58 #6165Köszönöm szépen. Zseniálisak a nevek. Jót nevettem.
2019-08-16-17:53 #6166Szívesen.
„Jót nevettem”, ez is valami, de legalább a megoldással is tudtál mit kezdeni?2019-08-16-18:47 #6167Szerintem igen, de élesben kipróbálni csak szeptemberben tudom, addig táppénzen vagyok. Ha valami nem megy, majd írok. Még egyszer köszönöm.
-
SzerzőBejegyzés
- Be kell jelentkezni a hozzászóláshoz.