Makro Summewenn

Bild

Betrifft: Makro Summewenn
von: Theo
Geschrieben am: 13.11.2015 16:38:02

Option Explicit


Public Sub SummeWenn()
 Dim lZeile  As Long
 lZeile = 10
 Dim rBereich_1 As Range
 Dim rBereich_2 As Range
   Set rBereich_1 = Tabelle2.Range("B1:B20")
    Set rBereich_2 = Range("A1:A20")
       Range("C3:F6") = Application.WorksheetFunction.SumIf(rBereich_1, _
          Range("C" & lZeile).Value, rBereich_2)
 End Sub
Hallo, ich habe dieses Makro erstellt. Mir fehlen aber leider noch ein paar Variablen bzw. eine Schleife. Das Makro berücksichtigt nur das Suchkriterium in der Zelle C10, dies Zeile soll aber "mitwachsen" so als würde man die Formel in Excel "herunterziehen". kann mir jemand helfen?

Bild

Betrifft: AW: Makro Summewenn
von: fcs
Geschrieben am: 14.11.2015 10:29:21
Hallo Theo,
in deiner Fragestellung gibt es noch ein paar Lücken:

  • 1.
    Sind alle Tabellenbereich auf dem Blatt Tabelle2 ?
    Oder ist nur rBereich_1 auf Tabelle2, die anderen Bereiche aber woanders? Wenn ja - wo.

  • 2.
    In Spalte C ändert sich der Bereich mit den Kriterien von C10 bis Cxx.
    Das Makro soll also entsprechend der letzten Zeile mit Daten eine Schleife entsprechend oft durchlaufen und dabei das Kriterium in der Berechnung ändern.
    In welche Zellen soll das Makro die Ergebnisse schreiben? Range("C3:F6") macht irgendwie keinen Sinn.

  • Nachfolgend mal ein angepasstest Grundgerüst.
    Gruß
    Franz
    Public Sub SummeWenn()
        Dim lZeile  As Long, Zeile_L As Long
        Dim wks As Worksheet
        Dim rBereich_1 As Range
        Dim rBereich_2 As Range
        
        Set wks = ActiveSheet 'oder Blatt fest vorgeben
        
        With wks
            Set rBereich_1 = Tabelle2.Range("B1:B20")
            Set rBereich_2 = .Range("A1:A20")
            'Letzte Zeile in Spalte C
            Zeile_L = .Cells(.Rows.Count, 3).End(xlUp).Row
            'Werte in Spalte C abarbeiten
            For lZeile = 10 To Zeile_L
                ???.Range("C3:F6")??? = Application.WorksheetFunction.SumIf(rBereich_1, _
                    .Cells(lZeile, 3).Value, rBereich_2)
            Next lZeile
        End With
    End Sub
    


    Bild

    Betrifft: AW: Makro Summewenn
    von: Theo
    Geschrieben am: 15.11.2015 12:12:27
    Hallo Franz, danke für deine Antwort.
    Bereich1 ist in Tabelle2 und Bereich1 ist auf dem activesheet.
    Stimmt, Ergebnisbereich c3:f6 macht kein Sinn. Das sind halt die Ergebniszellen. Aber dann muss ich warscheinlich für jede Spalte (c3:c6, d3:d6, e3:e6,...) eine eigene Formel schreiben oder?

    Bild

    Betrifft: AW: Makro Summewenn
    von: fcs
    Geschrieben am: 15.11.2015 12:28:20
    Hallo Theo,
    es ist und bleibt verwirrend.
    Der einfachste und bei Standard-Formln oft der schnellst ist der, die Berechnungsformeln per VBA-Makro einzufügen und dann durch ihre Ergebnisse zu ersetzen. In den Formeltext muss man dann den Variablen Teil einbaue.
    Damit es klarer wird solltest du hier eine Beispieldatei hochladen mit den gewünschten SUMMEWENN-Formeln.
    Gruß
    Franz

    Bild

    Betrifft: AW: Makro Summewenn
    von: Theo
    Geschrieben am: 15.11.2015 14:31:09
    https://www.herber.de/bbs/user/101530.xlsx
    Hier einmal die Datei. Die Formel sumif im Makro muss ich dann durch sumifs ersetzten...
    Die Beispieldatei erhält nur 2 Monate und ein paar Zeilen. Die echte Datei geht über 12 Monate und eine Übersicht mit jeweils 30.000 Zeilen und 30 Spalten. Daher dauert die Berechnung über Formeln extrem lange, weshalb ich die Werte gerne per Makro in die Zeilen schreiben möchte

    Bild

    Betrifft: AW: Makro Summewenn
    von: fcs
    Geschrieben am: 15.11.2015 21:29:29
    Hallo Theo,
    bei ca. 900000 Zellen, die auf diese Weise berechnet werden sollen, wirst du mit einem Makro, das die SUMMEWENNS-Funktion verwendet, auch nicht wirklich glücklich werden. Unter VBA sind die vergleichbaren Tabellenfunktionen nämlich langsamer als in einem Tabellenblatt. Wenn jede Zelle nur 1/100 Sekunden erfordert, dann benötigt das Makro noch 9000 sekunden als rund 2,5 stunden.
    Im Tabellenblatt führt die Menge an Formeln zum Problem, weil bei diesen Matrixformeln extreme Datenmengen temporär ausgelagert werden müssen - mit Glück in den Arbeitsspeicher, mit Pech auf die Festplatte. Aber Excel bekommt dabei extreme Verdauungsschwierigkeiten.
    Wieviele Datenzeilen sind denn in den Monatsblättern?
    Wenn du in der Übersicht 30000 Zeilen hast, dann müssen dort ja auch jeweils mehrere Tausend oder Zehntausend Zeilen vorhanden sein.
    Die Monatsdaten müssen ja aus einer Datenquelle generiert worden sein.
    Gibt es dort keine Möglichkeit das ein Datenexport generiert wird, der die Daten über das Gesamtjahr nach Verkäufer und Produkt aufsummiert?
    Bei den Daten in deiner Tabelle sehe ich jetzt 2 grundsätzliche Wege weiterzukommen:
    A: Daten-Arrays
    Hier werden alle Daten in Datenarrays verarbeitet, wobei 30000 Zeilen mal 32 Spalten schon eine Herausforderung sind - allerdings muss man nicht unbedingt alle 30000 Zeilen in einem Array verarbeiten, sondern kann in Blöcken arbeiten. In verschachtelten For-Next-Schleifen werden dabei die 12 Monate abgearbeitet, die Verkäufer und Produkte verglichen, bei Übereinstimmung die Werte aus der Zeile im Monat zu der entsprechenden Zeile in der Übersicht addiert.
    Zum Schluß werden dann die Ergebnisse aus dem Daten-Array in die Übersicht übertragen.
    B: Auslagerung der Monatsdaten in eine externe Datei und Aufbereitung via Pivot-Tabellenbericht
    In der externen Datei ist schon eine Pivotauswertung vorbereitet, die die Daten eines Monats nach Verkäufer und Produkt aufsummiert, so dass pro Kombination von Verkäufer und Produkt nur noch eine Datenzeile existiert. Die Ergebnis-Daten jedes Monats werden jeweils in eine Jahres-Tabelle kopiert.
    Nachdem alle Monate in die Jahres-Tabelle übertragen sind wird die Pivot-Auswertung auf diese Jahres-Werte angewendet. So hat man in der Liste für jede Kombination von Verkäufer und Produkt nur noch eine Datenzeile, was dann schon deinem gewünschten Ergebnis entspricht. Jetzt kann man die Daten falls erforderlich noch in die gewünschte Reihenfolge bringen.
    Auch diese Lösung kann man per Makro weitestgehend automatisieren, ist aber auch manuell schnell umgesetzt, wenn man die Basis erst mal angelegt hat. Pivot-Berichte haben den Vorteil, dass man hier auf die schnellen Daten-Bankfunktionen zurückgreifen kann, die auf die in einem separaten Datenspeicherbereich abgelegten Quelldaten zugreifen.
    Basisdatei für Auswertung via Pivotbericht:
    https://www.herber.de/bbs/user/101536.xlsm
    Gruß
    Franz

    Bild

    Betrifft: AW: Makro Summewenn
    von: Theo
    Geschrieben am: 16.11.2015 15:12:45
    Hallo Franz,
    vielen Dank für deine ausführliche Antwort.
    Die Monatsblätter haben jeweils ca. 30.000 Zeilen und 30 Spalten, die mit Werten gefüllt sind. In der Gesamtübersicht werden dann die jeweiligen Monate per =summewenns zusammenaddiert, ebenfalls ca. 900.000 Zellen. Diese Berechnung in der Gesamtübersicht dauert so ca. 20 Stunden.
    Die Monatsdaten werden aus einem Programm generiert. Dies für das Gesamtjahr zu erstellen, würde noch länger als 20 Stunden dauern.
    Die Lösung über Pivot hatte ich auch schon im Hinterkopf, funktioniert leider nicht, da ich dann ziemlich lange damit beschäftigt wäre, die gewünschte Formatierung einzuhalten.
    Eine VBA-Lösung die 2,5 Stunden rechnet wäre ein echter Fortschritt...

    Bild

    Betrifft: AW: Makro Summewenn
    von: Theo
    Geschrieben am: 17.11.2015 09:22:05

    
    Public Sub SummeWenn()
             Dim lZeile  As Long, Zeile_L As Long
             Dim wks As Worksheet
             Dim rBereich_1 As Range
             Dim rBereich_2 As Range
             Dim rBereich_3 As Range
             
             Set wks = ActiveSheet 'oder Blatt fest vorgeben
             
             With wks
                 Set rBereich_1 = Tabelle2.Range("B:B")
                 Set rBereich_2 = Tabelle2.Range("C:C")
                 Set rBereich_3 = Tabelle2.Range("A:A")
                 'Letzte Zeile in Spalte C
                 Zeile_L = Tabelle2.Cells(.Rows.Count, 3).End(xlUp).Row
                 'Werte in Spalte C abarbeiten
                 For lZeile = 10 To Zeile_L
                     .Cells(lZeile, 3) = Application.WorksheetFunction.IF(Range("B:B") = """", """", _
     SumIfs(rBereich_2,
                         rBereich_3, .Cells(lZeile, 1).Value, rBereich_1, .Cells(lZeile, 2)))
                 Next lZeile
             End With
         End Sub
    Das Makro wird nicht ausgeführt, der Debugger zeigt einen Fehler bei SumIfs. Kann mir jemand ein Tipp geben?

    Bild

    Betrifft: AW: Makro Summewenn
    von: fcs
    Geschrieben am: 18.11.2015 20:55:08
    Hallo Theo,
    ich hab jetzt mal etliches probiert.
    Die Kombination aus Vorauswertung per Pivot-Tabellenberichten und die Aufbereitung/Übertragung der Ergebnisse via Datenarray scheint der schnellste Weg zu.
    Hier reden wir dann nur noch von Minuten, nicht Stunden für eine Auswertung über jeweils ca. 30000 Zeilen in Blatt "Gesamt" und in den Monatsblättern.
    Die Formel-Lösungen kannst du direkt knicken, da muss Excel einfach zu viele Daten jonglieren.
    Ich hab in der ZIP-Datei mal alles zusammengestellt, was ich so probiert hab. Unter anderem in der Datei "...Versuche.xlsm" auch die von dir angedachte Lösung mit VBA-internen SumIFs.
    https://www.herber.de/bbs/user/101628.zip
    Die meiste Zeit geht dabei für die Übertrag der Ergebnisse aus der Pivot-Auswertung in das Blatt "Gesamt" drauf.
    Wenn du mit einer von der Pivot-Auswertung im Blatt "PivotJahr" erzugten Sortierung leben könntest, dann könnte man das Ganze nochmals enorm beschleunigen.
    Gruß
    Franz

     Bild

    Beiträge aus den Excel-Beispielen zum Thema "Makro Summewenn"