Telefonszámunk: 1-472-0679

[Resolved] Adatok kigyűjtése több excel állományból.

Kezdőlap Fórumok Excel programozás [Resolved] Adatok kigyűjtése több excel állományból.

Topic Resolution: Resolved

Ennek a témakörnek tartalma 19 hozzászólás, 3 résztvevő. Utolsó frissítés:  Sutyi73 2 napja, 5 órája telt el.

20 bejegyzés megtekintése - 1-20 / 20
  • Szerző
    Bejegyzés
  • #6125

    Sutyi73
    Felhasználó

    Ü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.

    #6126

    horvimi
    Adminisztrátor

    Szia!

    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-vel

    Mivel nem tudom milyen szinten állsz Excel-ben, csak a kérdésből lehet találgatni, egyelőre ennyi

    Imre

    #6127

    Sutyi73
    Felhasználó

    Szia! 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: 6 napja, 12 órája telt el- Sutyi73.
    Attachments:
    You must be logged in to view attached files.
    #6130

    horvimi
    Adminisztrátor

    Há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áltam

    Nálunk szokott lenni PowerQuery tanfolyam, legközelebb szeptember elejére van meghirdetve.
    http://pentaschool.hu/excel/power-query-tanfolyam.php

    Imre

    #6131

    Sutyi73
    Felhasználó

    Ü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 napja, 16 órája telt el- Sutyi73.
    #6133

    horvimi
    Adminisztrátor

    Szia!

    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ást

    Egyszer 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 napja, 11 órája telt el- horvimi.
    #6135

    delila
    Felhasználó

    Szia 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

    #6137

    Sutyi73
    Felhasználó

    Szia! 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 napja, 8 órája telt el- Sutyi73.
    #6140

    horvimi
    Adminisztrátor

    Nem 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 napja telt el- horvimi.
    #6144

    Sutyi73
    Felhasználó

    Szia! 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.
    #6149

    delila
    Felhasználó

    A 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+1

    Másolás:
    range(“A2:E” & eddig).copy wb.worksheets(“Célmunkalap”).range(“A” & ide)

    #6150

    Sutyi73
    Felhasználó

    Köszönöm a segítséget, ez a rész sikerült.

    #6155

    delila
    Felhasználó

    Szívesen. Mi van a többi résszel?

    #6156

    Sutyi73
    Felhasználó

    Szia!
    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.

    #6157

    delila
    Felhasználó

    Imre 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.

    #6159

    Sutyi73
    Felhasználó

    Valahogy így néz ki eddig.

    • A hozzászólás módosításra került: 2 napja, 7 órája telt el- Sutyi73.
    Attachments:
    You must be logged in to view attached files.
    #6162

    delila
    Felhasználó

    Feltettem 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: 2 napja, 7 órája telt el- delila. Indok: Javítás
    Attachments:
    You must be logged in to view attached files.
    #6165

    Sutyi73
    Felhasználó

    Köszönöm szépen. Zseniálisak a nevek. Jót nevettem.

    #6166

    delila
    Felhasználó

    Szívesen.
    “Jót nevettem”, ez is valami, de legalább a megoldással is tudtál mit kezdeni?

    #6167

    Sutyi73
    Felhasználó

    Szerintem 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.

20 bejegyzés megtekintése - 1-20 / 20

Be kell jelentkezni a hozzászóláshoz.