Microsoft Excel

Herbers Excel/VBA-Archiv

Informationen und Beispiele zum Thema ListBox
BildScreenshot zu ListBox ListBox-Seite mit Beispielarbeitsmappe aufrufen

Matrix - Summewenns + Datum + Nummernabgleich

Betrifft: Matrix - Summewenns + Datum + Nummernabgleich von: Thorben
Geschrieben am: 31.10.2014 12:05:23

Moin moin ihr lieben,

war mal schon länger nicht hier (was ja ansich ein gutes Zeichen ist :-)
habe jetzt aber wieder eine Nuss deren Schale mir zu hart ist.

Siehe Datei im Anhang mit ein paar Beispieldaten.

https://www.herber.de/bbs/user/93474.xlsx

Die Eingrenzung "nur" nach Datum bekomme ich ja hin, aber zusätzlich noch nach der BatchNr einzugrenzen übersteigt gerade meinen Zenit!

Also:
- wie oft wurde eine Batch innerhalb der vorgegebenen Zeiträume aufgerufen -

Soweit ich weiß ist Summewenns hier das Mittel der Wahl wegen der Geschwindigkeitsvorteile.

Gerne erstmal nur per Formel damit ich es verstehe!

Eine VBA Lösung ist aber auch gerne gesehen da es sich in der Gesamtheit um ca. 5200 Batchnummern handelt die sich auf 435000 (tausend) Datums-Zeilen ausgedehnt haben.

Könnt Ihr mir bitte helfen.

Vielen Dank schon einmal im Voraus

MfG
Thorben

  

Betrifft: AW: Matrix - Summewenns + Datum + Nummernabgleich von: Rudi Maintaire
Geschrieben am: 31.10.2014 12:11:42

Hallo,
=SUMMEWENNS(BatchNr!$L:$L;BatchNr!$N:$N;"<"&A2;BatchNr!$N:$N;">="&B2;BatchNr!$F:$F;$A4)

Gruß
Rudi


  

Betrifft: AW: Matrix - Summewenns + Datum + Nummernabgleich von: Peter Müller
Geschrieben am: 31.10.2014 12:12:10

Hallo,

was spricht denn gegen eine Pivot-Tabelle?

Gruß,
Peter


  

Betrifft: @Rudi-vielen Dank! @Peter siehe unten... von: Thorben
Geschrieben am: 31.10.2014 13:12:55

Hallo ihr,

erstmal danke für die Rückmeldung und Rudi für die Formellösung.

@ Peter, Pivot ist arg beschränkt wenn es um vordefinierte Zeiträume geht, da im Pivot jeder Tag einzeln dargestellt wird. Um diese dann wieder auszuwerten braucht es wieder eine Formel.
Aufgrund der Darstellung im Pivot ist diese aber nicht unbedingt einfacher zu schreiben, ich denke eher etwas komplizierter.

Was "ich" mit Pivot "nicht" machen kann:

Datum als Spaltenbeschriftung
1.
--- Excel kann diesen Vorgang mit den verfügbaren Resourcen nicht ausführen.
--- Bitte wählen Sie weniger Daten...---
2. Im Pivot als Spaltenbeschriftung wird jeder Tag einzeln dargestellt was wieder mit einer Formel-Auswertung einhergeht

oder klassich:

Datum = Zeilenbeschriftung
BatchNr = Zeilenbeschriftung
Aufrufe = Werte (Summe)

Dann habe ich wieder Blöcke was ebenso mit einer Formel-Auswertung einhergehen würde.

MfG
Thorben




  

Betrifft: AW: nochmal Kreuztabelle - ! Bitte mal gucken von: Thorben
Geschrieben am: 31.10.2014 14:35:43

Hallo,

ich nochmal!

Das mit der Formel habe ich jetzt verstanden!

Jetzt stehe ich aber vor der großen Datenwand und mit der Formel alleine
wird ganz schön dauern :)

Ich habe mal eine tolle Lösung für ein ähnliches Problem bekommen (siehe unten)
Damit konnte ich meine Matrix innerhalb weniger Sekunden erstellen anstelle von Überläufen und
abbdrüchen aus Zeitrgründen!

Vielleicht mag sich das jemand angucken und evtl. anpassen (das ist ganz klar nicht meine Leistungsklasse!)

Option Explicit

Sub Kreuztab_Verkett()

   Dim eDic  As Object, uDic As Object, lngQ As Long, arAQ, arHQ, arU
   Dim arT() As Long, zz As Long, cc As Long, arK
   Set eDic = CreateObject("Scripting.dictionary")
   Set uDic = CreateObject("Scripting.dictionary")

   With Sheets("---") ' Quelldaten -- Anpassen
   
   If ActiveSheet.FilterMode Then ActiveSheet.ShowAllData
    
      .Cells(1, 15) = Now - Date                      ' nur für Test
      lngQ = .Cells(.Rows.Count, 1).End(xlUp).Row - 1
      arAQ = .Cells(2, 1).Resize(lngQ)                ' Spalte Anpassen
      arHQ = .Cells(2, 10).Resize(lngQ)               ' Spalte Anpassen
   End With
   
   With Sheets("---") ' Anpassen
      lngQ = .Cells(1, .Columns.Count).End(xlToLeft).Column - 1
      arU = .Cells(2, 2).Resize(, lngQ)
      For cc = 1 To lngQ
         uDic(arU(1, cc)) = cc                        ' Typen
      Next cc
      For zz = 1 To UBound(arAQ)
         If eDic.Exists(arAQ(zz, 1)) Then
            arT = eDic(arAQ(zz, 1))                   ' hole Eintrag
            If Not arT(uDic(arHQ(zz, 1))) Then
               arT(uDic(arHQ(zz, 1))) = True          ' Typ kommt vor
               eDic(arAQ(zz, 1)) = arT                ' schreibe Eintrag
            End If
         Else
            ReDim arT(1 To lngQ)
            arT(uDic(arHQ(zz, 1))) = True             ' Typ kommt vor
            eDic(arAQ(zz, 1)) = arT                   ' neuer Eintrag
         End If
      Next zz
      arK = eDic.keys
      ReDim arE(0 To UBound(arK), 1 To lngQ + 1)
      For zz = 0 To UBound(arK)
         arT = eDic(arK(zz))
         For cc = 1 To lngQ
            If arT(cc) Then
               arE(zz, cc) = arU(1, cc)                ' Typ eintr.
               If arE(zz, lngQ + 1) <> "" Then _
                  arE(zz, lngQ + 1) = arE(zz, lngQ + 1) & ", " ' verketten
               arE(zz, lngQ + 1) = arE(zz, lngQ + 1) & arU(1, cc)
            End If
         Next cc
      Next zz                                          ' Ausgabe
      .Cells(2, 1).Resize(UBound(arK) + 1) = Application.Transpose(arK)
      .Cells(2, 2).Resize(UBound(arK) + 1, lngQ + 1) = arE
   End With
   
End Sub
Vielen lieben Dank schon mal

MfG
Thorben


  

Betrifft: AW: @Rudi-vielen Dank! @Peter siehe unten... von: Peter Müller
Geschrieben am: 31.10.2014 15:00:23

Hallo Thorben,

schau die mal das Thema "Gruppieren" in Pivot-Tabellen an. Hier kannst du bspw. mehrere Daten zusammenfassen (Rechtsklick auf Spalte oder Zeile in Pivot-Tabelle, Gruppieren). Die Gruppen sind zwar erstmal vorgegeben (zB Monat, Jahr bei Datumswerten), du kannst aber auch eigene Gruppen bilden.

Frage zu der Ressourcenmeldung: Hast du da keine Probleme, wenn du mit Formeln arbeitest?

Gruß,
Peter


  

Betrifft: AW: @Rudi-vielen Dank! @Peter siehe unten... von: Thorben
Geschrieben am: 31.10.2014 15:28:56

Hallo Peter,

die Gruppenfunktion ist durchaus anwendbar wenn die geforderten Kriterien nicht laufenden Änderungen unterliegen oder "mal eben" angepasst werden müssen.

Hier gehts Primär um die felixble Darstellung in einem starren / vorgegebenen Format.

Wenn ich nicht auf 21 Tage prüfe sondern "mal eben" auf 8, 32 oder 128 Tage prüfe mit unterschiedlichen Anfang/Ende Datum und evtl. anderen BatchNr dann ist eine Formel (bei übersichtlicher Datenmenge) oder VBA (Scription Dic oÄ) Lösung angebracht.

Besten Dank trotzdem

MfG
Thorben




 

Beiträge aus den Excel-Beispielen zum Thema "Matrix - Summewenns + Datum + Nummernabgleich"