Storingsanalyse maken op basis van storings-ID en tijdstip

[Christiaan01]

Goedendag,

Ik wil een storingsanalyse maken uit gegevens die door een machine wordt gegenereerd maar loop hierin vast.

Ik wil van alle storingen kunnen zien hoeveel de totale storingstijd per storing is. Iedere storing heeft zijn eigen unieke ID (MSGNumber), inkomende tijd en uitgaande tijd en status inkomend en uitgaand (StateAfter). De storingstijden met hetzelfde MSGNumber moeten bij elkaar opgeteld worden zodanig dat ik deze kan filteren op basis van een instelbaar tijdsvenster.
Ik krijg het niet voor elkaar aangezien de storingen op ID en tijd door elkaar staan. Na uren proberen en zoeken heb ik graag jullie hulp hierbij.
Ik zou al goed geholpen zijn als ik weet hoe ik de tijden kan optellen op basis van het ID en het verschil tussen inkomend en uitgaande tijden.

Voorbeeld:

Regel 2: MSGNumber 8044 is binnengekomen op 12:31:33 (StateAfter = 1)
Regel 4: MSGNumber 8044 is weggegaan op 12:32:32 (StateAfter = 0)

De storingstijd is hier dus: 59 sec.

Regel 50: MSGNumber 8044 is nogmaals binnengekomen op 12:57:26 (StateAfter = 1)
Regel 52: MSGNumber 8044 is weggegaan op 12:58:03 (StateAfter = 0)

De storingstijd is hier dus: 37 sec.


De totale storingstijd is: 96 sec. (m.u.v. de rest van de overige 8044 storingen).

Deze totale storingstijd wil ik dus inzichtelijk maken per MSGNumber.

Alle oplossingen zijn welkom.

 

[Timshel]

Ik kom uit op een macrootje.
Voeg een leeg werkblad 2 in, maak kolom B op als tijd en draai deze:

Sub tsh()
    Dim Br
    Dim i As Long
    
    Br = Sheets(1).Cells(1).CurrentRegion
    With CreateObject("Scripting.Dictionary")
        For i = 2 To UBound(Br)
            .Item(Br(i, 5)) = .Item(Br(i, 5)) + Br(i, 14) * (Br(i, 3) * -2 + 1)
        Next
        Sheets(2).Cells(1, 1).Resize(.Count, 2) = Application.Transpose(Array(.Keys, .Items))
    End With
End Sub

 

[cow18]

fantasietje op datzelfde thema

Sub tsh2()
    Dim Br
    Dim i As Long, s, Dict
    Br = Sheets("Alarm_log0").Cells(1).CurrentRegion
    Set Dict = CreateObject("Scripting.Dictionary")
    With Dict
        For i = 2 To UBound(Br)
            s = .Item(Br(i, 5))
            If VarType(s) = vbEmpty Then
                ReDim x(4): x(0) = Br(i, 5): x(1) = Br(i, 17)
            Else
                x = s
            End If
            If Br(i, 3) Then
                x(4) = Br(i, 1)
            Else
                If x(4) > 0 Then x(2) = x(2) + 1: x(3) = x(3) + Br(i, 1) - x(4): x(4) = 0
            End If
            .Item(Br(i, 5)) = x

        Next
        Sheets("samenvatting").Cells(1, 1).CurrentRegion.Offset(1).Resize(, 4).ClearContents
        If .Count Then
            s = .Keys
            For i = 0 To UBound(s)
                x = .Item(s(i))
                Sheets("samenvatting").Cells(i + 2, 1).Resize(, 4) = .Item(s(i))
            Next
        End If
    End With
End Sub

 

[E v R]

Nog een oplossing middels een draaitabel

 

[VenA]

Met een gesorteerde tabel en een extra kolom kan je ook een heel eind komen. Draaitabelletje erover en klaar.

 

[Christiaan01]

Vriendelijk bedankt allemaal, meerdere oplossingen die voor mij bruikbaar zijn.

Top!

 

[Christiaan01]

@Cow 18,

Bij uw oplossing krijg ik andere tijden bij de totalen. Kunt u nog eens naar uw code kijken?

 

[cow18]

ik weet niet precies hoe je aan je gegevens komt, zo te zien een uitdraai uit storingsmeldingen van een PLC en gelogd naar excel.
Dan veronderstel ik dat je de tijd verkregen in kolom A omgezet hebt naar de kolommen N tot P, door te delen door 1.000.000 en dan om te zetten naar de opmaak dd-mm-jj uu:mm:ss. Zie ook kolom S.
Dus nu heb ik in VBA het tijdsverschil ook gedeeld door 1.000.000 en op mijn blad samenvatting het verkregen resultaat neergezet in de opmaak [uu]:mm:ss,000
Ik weet niet of tijdsverschillen tot op een milliseconde wel relevant zijn, maar het leek me leuk.
Als fantasietje wordt het resultaat nu nog eens aflopend gesorteerd op die tijd.

 

[Christiaan01]

De tijden komen idd uit een PLC (of eigenlijk een HMI-display). De tijd in kolom A doe ik verder niets mee, ik heb alleen de tijd en datum uit kolom N apart in kolom O en P gezet.
In je laatste voorbeeld klopt het wel, ik denk dat ik met die milliseconden de fout in ben gegaan.

Uit welke kolom haal jij nu de tijd?

 

[cow18]

kolom A / 1.000.000
maar is zo aan te passen

 

[Christiaan01]

Ik heb nu de originele xlsx-file van het HMI display genomen zonder enige bewerking. Hierin zitten een aantal lege kolommen waardoor jou code niet meer werkt. Zou jij je code kunnen aanpassen zodat deze werkt met de xlsx-file?

De tijd mag ook uit kolom N genomen worden, milliseconden zijn niet belangrijk

:thumb:

 

[ExcelAmateur]

Volgens mij kun je in een xlsx file geen mocro's gebruiken.
In een xlsm file wel.

Misschien zit ik wel helemaal naast.

 

[cow18]

nieuwe versie in bijlage
nu wordt de samenvatting op hetzelfde werkblad in de kolommen AA en volgende neergezet.
Ook wordt er gerekend met de tijden uit kolom N.

Nu weet ik niet hoe je straks werkt, in verband met de macro.
Kopieer je telkens je nieuwe loggegevens in deze file, dan is er geen probleem.
Maak je telkens een nieuwe file aan, met een andere naam dan, dan moet deze file ook open staan en dan roep je vanuit je nieuwe file de macro in deze file aan.
Lukt het niet, beschrijf dan eens waar je blijft haperen.

 

[Christiaan01]

@Cow18

Alweer bedankt. Ik ga ermee aan de slag.