Telefonszámunk: 1-472-0679

[Resolved] napszakok statisztikázása

Kezdőlap › Fórumok › Excel programozás › [Resolved] napszakok statisztikázása

Topic Resolution: Resolved
8 bejegyzés megtekintése - 1-8 / 8
  • Szerző
    Bejegyzés
  • 2023-12-18-14:14 #10305
    tboy72
    Felhasználó

      Üdvözletem!
      Azzal kezdeném, hogy aránylag hosszabb ideje használom az Excel-t, de eléggé alap szinten. Most belefutottam egy problémába:
      Adott a mellékletben szereplő munkalap. Az „A” oszlopban lévő adatok származnak a forrásból, dátum, nap neve, és tól-ig időtartam.
      Az lenne a feladat, hogy ezekből az adatokból kellene a jobb oldalon lévő színes időszakmeghatározásoknak megfelelően összegezni a találatokat. Ez jelenleg meghaladja a tudásom.
      Minden ötletnek nagyon örülnék!

      Köszönettel:

      tboy72

      Attachments:
      You must be logged in to view attached files.
      2023-12-18-21:00 #10310
      horvimi
      Adminisztrátor

        Szia téboly! 🙂

        Megnéztem a csatolt Excel-t, de nem vagyok biztos abban, hogy mi a feladat.
        Talán azért, mert minta eredményeket (manuálisan) nem írtál bele.

        Tippem:
        Hívjuk a bal oldali ismétlődő blokkokat rekordoknak
        Azt kellene megadni a színes részekben, hogy az egyes megadott időintervallumokban hány darab rekord található a bal oldalon?
        Tehát pl. a sárga esetén H-P napokon 7:30 és 16h között hány rekord van a bal oldalon?
        De mivel a rekordokban kezdő és vég idő van megadva, még az is kérdés, hogy melyiknek kell 7:30 és 16h között lennie? Mindkettőnek?

        Ha ezt kell csinálni, akkor ebben a formában az adatforrás szinte teljesen alkalmatlan a feldolgozásra.
        Kivéve a manuális feldolgozást 🙂

        Adatforrás
        Egy rekordon belül az adatoknak egymás mellett és nem egymás alatt kellene lenniük, így együtt alkotva egy olyan táblázatot, aminek a fejléce így néz ki:
        Dátum, Nap, kezdési idő, befejezési idő

        Tehát a kötőjellel elválasztott, szövegesen megadott időket külön kell venni kezdő és befejező, valós időre.

        A lentebb lévő rekordok tartalmaznak még plusz 2 elemet, amiben mindenhol az van, hogy:
        „előírt terv alapján” és az, hogy „nem”
        Ezek gondolom kihagyhatók, ami a statisztikát illeti.

        Átalakítás
        Excel 365 függvényekkel elég gyorsan meg lehet csinálni az átalakítást.
        Kérdés, hogy milyen verziód van?
        Ha nem 365, akkor vagy manuális munka vagy makró, esetleg PowerQuery megoldás marad.

        Statisztika
        A színes részeket meg úgy kellene előkészíteni, hogy a kezdési és befejezési idők külön cellákban legyenek.
        Ezeket kell vizsgálni, hogy egy-egy rekord beleesik-e vagy sem. (Miután kiderült, hogy minek kell beleesnie)
        Amíg kötőjel van az idők között, az sima szöveg lesz, ami mindenre alkalmatlan.

        Hát, elsőre ennyi.

        Imre

        2023-12-18-21:33 #10311
        tboy72
        Felhasználó

          Szia Imi!
          Köszönöm szépen a gyors választ. Kezdem a rosz hírrel. Csak Office 2016 van. A manuális munkát szeretném kiváltani, mert minden hónapban sok ilyen adatforrás van word-ben és csak így lehet adatot kinyerni belőlük excelbe. Az „előírt terv alapján” és az, hogy „nem” irrelevánsak. A PowerQuerry nekem is eszembe jutott, de ahhoz méginkább láma vagyok mint az excelhez. Bár gyorsan tanulok.
          Ha az időintervallumból esetleg átlóg egy következő kategóriába, akkor már oda kellene elkönyvelni, mint eredményt. Sajnos az időintervallumok kötjelesen vannak. Nem tudom van-e rá esetleg valami script lehetőség, ami a szöveget elemzi és utána összehasonlítja az eredményt a cél intervallummal. Valami szubrutinokkal (nagyon régen programoztam).

          2023-12-18-23:40 #10312
          horvimi
          Adminisztrátor

            Szia!

            Ugyan nem adtál választ arra, hogy jól tippelem-e a feladatot, de tegyük fel.
            Ha nincs, és nem is tudsz szerezni 365-ót, akkor nagy szívás.

            Illetve ahhoz képest nagy, hogy mennyire egyszerű lenne 365-ben.
            Csak ízelítőként, így lehetne átalakítani a borzalmadat normális kinézetűvé.
            =CHOOSECOLS(WRAPROWS(TOROW(A3:A272);5);1;2;3)

            Makrót biztosan nem írunk.

            PowerQuery-s megoldáshoz tudok neked linkeket adni. Kipróbáltam őket, működnek. Az a baj, hogy nem pont ugyanazok, mint a te problémád, ezért kicsit hozzá kell nyúlni. Ha még semmit nem csináltál PQ-vel, akkor ez nem lesz egyszerű
            https://radacad.com/convert-flat-list-to-table-in-power-bi-pivot-without-a-set-key-column-using-power-query
            https://exceloffthegrid.com/power-query-unstacking-data-in-a-column/
            https://gorilla.bi/power-query/transform-stacked-data-into-table/
            https://www.thebiccountant.com/2019/02/28/unstacking-columns-in-power-bi-power-query-excel/

            Ezzel „csak” a halmozott formátum táblázattá alakítása lenne meg, ezután jöhetne a statisztika.
            Ha jól értem, akkor csak a befejező időpontot kell vizsgálni, hogy hová esik?

            Imre

            2023-12-19-05:39 #10313
            tboy72
            Felhasználó

              Köszönöm szépen az útmutatást, akkor a PowerQuery irányba fogok nézelődni. A feladatban még az a szép, hogy a 07:30-16:00 idősávot vehetjük bázisidőnek, mert ha lefelé, vagy felfelé kilóg belőle akkor már a másik idősávba kellene számolni.

              szerk:
              Közben elkezdtem VBA-val makrózni, idáig jutottam:

              Sub Makró1()
'
' Makró1 Makró
'
' Billentyűparancs: Ctrl+l
'
    Dim x As Integer
    Dim a As Integer
    Dim b As Integer
    Dim c As Integer
    Dim d As Integer
    Dim e As Integer
    
hétköznap:
  
    If Cells(x, 4) > "07:30" And Cells(x, 4) < "16:00" Then GoTo tali
    If Cells(x, 4) > "16:00" And Cells(x, 4) < "22:00" Then GoTo túli
    If Cells(x, 4) > "06" And Cells(x, 4) < "07:30" Then GoTo túli
    If Cells(x, 4) > "22:00" And Cells(x, 4) < "06:00" Then GoTo mnéjszaka
 
    
péntek:
    If Cells(x, 4) > "07:30" And Cells(x, 4) < "13:30" Then GoTo tali
    If Cells(x, 4) > "13:30" And Cells(x, 4) < "22:00" Then GoTo túli
    If Cells(x, 4) > "06" And Cells(x, 4) < "07:30" Then GoTo túli
    If Cells(x, 4) > "22:00" And Cells(x, 4) < "06:00" Then GoTo mnéjszaka
    
hétvége:
    If Cells(x, 4) > "06:00" And Cells(x, 4) < "22:00" Then GoTo msznappal
    If Cells(x, 4) > "22:00" And Cells(x, 4) < "06:00" Then GoTo mszéjszaka
    
tali:
    Cells(2, 7) = a + 1
túli:
    Cells(2, 8) = b + 1
mnéjszaka:
    Cells(2, 9) = c + 1
msznappal:
    Cells(2, 10) = d + 1
mszéjszaka:
    Cells(2, 11) = e + 1
    
    Cells(1, 7) = "tali"
    Cells(1, 8) = "túli"
    Cells(1, 9) = "mnkanap éjszaka"
    Cells(1, 10) = "hétvége nappal"
    Cells(1, 11) = "hétvége éjszaka"

    x = 0
    a = 0
    b = 0
    c = 0
    d = 0
    e = 0
    
    Do                      ' átrakja az időintervallumokat a D és E oszlopba
            
    x = x + 5
        
    Cells(x, 4) = Left(Cells(x, 1), 5)
    Cells(x, 5) = Right(Cells(x, 1), 5)
    
    Loop Until IsEmpty(Cells(x + 5, 1))
    
    x = 0
    
    Do
    x = x + 5
        
    If Cells(x - 1, 1) = "hétfő" Then GoTo hétköznap
    If Cells(x - 1, 1) = "kedd" Then GoTo hétköznap
    If Cells(x - 1, 1) = "szerda" Then GoTo hétköznap
    If Cells(x - 1, 1) = "csütörtök" Then GoTo hétköznap
    If Cells(x - 1, 1) = "péntek" Then GoTo péntek
    If Cells(x - 1, 1) = "szombat" Then GoTo hétvége
    If Cells(x - 1, 1) = "vasárnap" Then GoTo hétvége
   

   
     Loop Until IsEmpty(Cells(x + 5, 1))
     
    
End Sub

              Csakhát nem akar működni!
              Mi lehet a hiba?

              2023-12-21-04:27 #10316
              verax
              Felhasználó

                Sziasztok! Szia tboy72!

                Mi lehet a hiba? Inkább: Melyek lehetnek a hibák?!

                Az első észrevételem, hogy csak a legvégső esetben használj GoTo utasítást! Bár ha körültekintő vagy, írhatsz használható kódot, csak sokkal nehezebb áttekinthetővé tenni. Figyelni kell arra, hogy a GoTo utasítás nem elágazás. Odaugorhatsz a programkód cimkével jelzett helyére, de a feldolgozás tovább folytatódik (az összes többi cimkén át) az eljárás végét jelző utasításig. Ezért érdemes inkább külön eljárásokba „kiszervezni” a különböző feladatokat.

                A következő, hogy bár Imre javasolta az adatok átszervezését, te ezt a munkalapon nem tetted meg viszont a VBA kódban már az új szerkezet szerint hivatkozol a cellákra.
                If Cells(x, 4) … – a „Munka1” munkalap „D” oszlopában nincsenek adatok.

                Továbbá…
                If Cells(x, 4) > „22:00” And Cells(x, 4) < „06:00” Then – hááát, ilyen időpont pedig nincs.

                A kategória változóidat (a, b, c, d és e) ugyan kiíratod a „G2:K2” tartományba, de nem (jól) gondoskodsz a változók értékének frissítéséről – csak nullázod azokat.

                Elkerülte a figyelmedet, hogy a hétfőt másként kell kezelni, mint a többi munkanapot, tekintettel arra, hogy a hétfő előtt munkaszüneti nap van. (Vagy nem következetes az időszakok kategorizálása.)

                Létrehoztad a változókat. Ez rendben van. Nem adsz nekik értéket. Ez nincs rendben. Később mégis adsz értékeket. Ez sincs rendben, hiszen megfeledkeztél róla, hogy az x változód a munkalapon lévő cellák sorértékét hivatott tárolni. Az x=0 (Cells(0. sor, 4. oszlop) érvénytelen címet eredményez.

                Két hozzászólásodban ellentmondásba keveredtél (de legalábbis nem egyértelmű):
                „Ha az időintervallumból esetleg átlóg egy következő kategóriába, akkor már oda kellene elkönyvelni, mint eredményt.”
                és
                „A … 07:30-16:00 idősávot vehetjük bázisidőnek, mert ha lefelé, vagy felfelé kilóg belőle akkor már a másik idősávba kellene számolni.”

                Egy másik ellentmondás a 6:00 és 7:30 közötti időszakkal kapcsolatos. A „Munka1” munkalapon színkódolt időszakok között nem szerepel, de a VBA kódban egyértelműen jelzed, hogy mi a szándékod vele. Ezért én is úgy kezelem ezt a másfél órát, hogy „hozzácsaptam” a 22:00 és 6:00 közötti időszak végéhez. Így tehát 22:00 és 7:30 közötti időszakról gondolkodtam.

                A kezdő és a záró időpont is valamelyik tartományba fog esni. Melyik legyen a döntő?
                Az is lehet egy szemlélet, hogy csak akkor tartozik egy bejegyzés a „bázis időszakba”, ha a kezdő és a záró időpont egyaránt az aktuális nap „bázis határai” között marad.
                Ha
                …a kezdő időpont a „bázis időszak” előtt van, de a záró már a „bázis időszakon” belülre esik, akkor az eredmény a „Bázis időszak előtt”
                …a kezdő és a záró időpont a „bázis időszakon” belülre esik, akkor az eredmény a „Bázis időszaki”
                …a kezdő időpont a „bázis időszakon” belülre esik, de a záró már a bázis időszak utánra, akkor az eredmény a „Bázis időszak utáni”

                Készítettem egy példát, amelyben csupán a záróérték szerint osztályozom az időszakokat. A fenti dilemmát feloldhatod az „áttekintő” munkalap sorainak helyes kitöltésével.
                A „Statisztika” nevű makrót futtasd – illetve tanulmányozd, módosítsd igényeid szerint.
                Mivel a fórum nem fogad *.xlsm fájlokat, ezért tömörítve csatoltam a példa fájlt.

                üdvözlettel
                verax

                Attachments:
                You must be logged in to view attached files.
                2023-12-21-14:03 #10318
                tboy72
                Felhasználó

                  Köszönöm szépen a választ, közben már összedobtam egy másik kódot, ahol már nem használtam GoTo-t kiszerveztem külön szubrutinokba.

                  Így már működik!

                  Köszönöm mindenkinek a segítséget!

                  Sub PkAl()
'
' Makró1 Makró
'
' Billentyűparancs: Ctrl+l
'
    Dim x As Integer
    Dim tol, ig As Variant
    Dim a, b, c, d, e As Variant
    
    x = 0
    a = "06:00" '0,25
    b = "07:30" '0,3125
    c = "13:30" '0,5625
    d = "16:00" '0,666666666666667
    e = "22:00" '0,916666666666667
    f = "23:59" '0,999305555555556
    g = "00:01" '0,000694444444444444
    
    Cells(1, 7) = "munkaidőn belül"            'kiírja az célértékek kategóriáit
    Cells(1, 8) = "munkaidőn túl"
    Cells(1, 9) = "munkanap éjszaka"
    Cells(1, 10) = "hétvége nappal"
    Cells(1, 11) = "hétvége éjszaka"
    
    Cells(2, 7) = 0
    Cells(2, 8) = 0
    Cells(2, 9) = 0
    Cells(2, 10) = 0
    Cells(2, 11) = 0
           
    Do
                                       
        x = x + 5
        ig = Right(Cells(x, 1), 5)
        tol = Left(Cells(x, 1), 5)
        
        If Cells(x - 1, 1).Value = "hétfő" Then
            Call hétköznap(tol, ig, a, b, c, d, e, f, g)
        ElseIf Cells(x - 1, 1).Value = "kedd" Then
            Call hétköznap(tol, ig, a, b, c, d, e, f, g)
        ElseIf Cells(x - 1, 1).Value = "szerda" Then
            Call hétköznap(tol, ig, a, b, c, d, e, f, g)
        ElseIf Cells(x - 1, 1).Value = "csütörtök" Then
            Call hétköznap(tol, ig, a, b, c, d, e, f, g)
        ElseIf Cells(x - 1, 1).Value = "péntek" Then
            Call péntek(tol, ig, a, b, c, d, e, f, g)
        ElseIf Cells(x - 1, 1).Value = "szombat" Then
            Call hétvége(tol, ig, a, b, c, d, e, f, g)
        ElseIf Cells(x - 1, 1).Value = "vasárnap" Then
            Call hétvége(tol, ig, a, b, c, d, e, f, g)
        End If
    Loop Until IsEmpty(Cells(x + 5, 1))
     
    
End Sub
Sub hétköznap(tol, ig, a, b, c, d, e, f, g)
'hétköznap:
    If tol > e And tol < f Or ig > e And ig < f Then
        Cells(2, 9) = Cells(2, 9) + 1 'munkanap éjszaka
    ElseIf tol > g And tol < a Or ig > g And ig < a Then
        Cells(2, 9) = Cells(2, 9) + 1 'munkanap éjszaka
    ElseIf tol > d And tol < e Or ig > d And ig < e Then
        Cells(2, 8) = Cells(2, 8) + 1 'munkaidőn túli
    ElseIf tol > a And tol < b Or ig > a And ig < b Then
        Cells(2, 8) = Cells(2, 8) + 1 'munkaidőn túli
    ElseIf tol >= b And tol <= d And ig >= b And ig <= d Then
        Cells(2, 7) = Cells(2, 7) + 1 'munkaidőn belül
    End If
End Sub

Sub péntek(tol, ig, a, b, c, d, e, f, g)
'péntek:
    If tol > e And tol < f Or ig > e And ig < f Then
        Cells(2, 9) = Cells(2, 9) + 1 'munkanap éjszaka
    ElseIf tol > g And tol < a Or ig > g And ig < a Then
        Cells(2, 9) = Cells(2, 9) + 1 'munkanap éjszaka
    ElseIf tol > a And tol < b Or ig > a And ig < b Then
        Cells(2, 8) = Cells(2, 8) + 1 'munkaidőn túli
    ElseIf tol > c And tol < e Or ig > c And ig < e Then
        Cells(2, 8) = Cells(2, 8) + 1 'munkaidőn túli
    ElseIf tol >= b And tol <= d And ig >= b And ig <= d Then
        Cells(2, 7) = Cells(2, 7) + 1 'munkaidőn belül
    End If
End Sub
Sub hétvége(tol, ig, a, b, c, d, e, f, g)
'hétvége:
    
    If tol > e And tol < f Or ig > e And ig < f Then
        Cells(2, 11) = Cells(2, 11) + 1 'hétvége éjszaka
    ElseIf tol > g And tol < a Or ig > g And ig < a Then
        Cells(2, 11) = Cells(2, 11) + 1 'hétvége éjszaka
    ElseIf tol >= a And tol <= e And ig >= a And ig <= e Then
        Cells(2, 10) = Cells(2, 10) + 1 'hétvége nappal
    End If
End Sub
                  2023-12-21-21:13 #10319
                  horvimi
                  Adminisztrátor

                    Szuper!

                    Ha működik, akkor örülünk!

                  • Szerző
                    Bejegyzés
                  8 bejegyzés megtekintése - 1-8 / 8
                  • Be kell jelentkezni a hozzászóláshoz.