Anzeige
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Vorheriger Thread
Vorheriger Thread
Rückwärts Blättern
1456to1460
1456to1460
Aktuelles Verzeichnis
Verzeichnis Übersicht
Verzeichnis Übersicht
Übersicht Verzeichnisse
Nächster Thread
Nächster Thread
Vorwärts blättern
Inhaltsverzeichnis

Makro Summewenn

Forumthread: Makro Summewenn

Makro Summewenn
13.11.2015 16:38:02
Theo
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?

Anzeige

8
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: Makro Summewenn
14.11.2015 10:29:21
fcs
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

    Anzeige
    AW: Makro Summewenn
    15.11.2015 12:12:27
    Theo
    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?

    Anzeige
    AW: Makro Summewenn
    15.11.2015 12:28:20
    fcs
    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

    Anzeige
    AW: Makro Summewenn
    15.11.2015 14:31:09
    Theo
    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

    Anzeige
    AW: Makro Summewenn
    15.11.2015 21:29:29
    fcs
    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

    Anzeige
    AW: Makro Summewenn
    16.11.2015 15:12:45
    Theo
    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...

    Anzeige
    AW: Makro Summewenn
    17.11.2015 09:22:05
    Theo 
    
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?

    Anzeige
    AW: Makro Summewenn
    18.11.2015 20:55:08
    fcs
    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
    Anzeige
    ;
    Anzeige
    Anzeige

    Infobox / Tutorial

    Makro zur Anwendung von SUMMEWENN in Excel VBA

    Schritt-für-Schritt-Anleitung

    Um ein Makro in Excel zu erstellen, das die SUMMEWENN-Funktion anwendet, folge diesen Schritten:

    1. Öffne Excel und drücke ALT + F11, um den VBA-Editor zu starten.
    2. Füge ein neues Modul ein: Rechtsklick auf "VBAProject (deine Datei)" > Einfügen > Modul.
    3. Kopiere den folgenden Code in das Modul:
    Option Explicit

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.SumIfs(rBereich_2, rBereich_3, .Cells(lZeile, 1).Value, rBereich_1, .Cells(lZeile, 2))
        Next lZeile
    End With
End Sub
    1. Schließe den VBA-Editor und kehre zu Excel zurück.
    2. Führe das Makro aus, indem du ALT + F8 drückst und "SummeWenn" auswählst.

    Häufige Fehler und Lösungen

    Hier sind einige häufige Fehler, die beim Arbeiten mit VBA und der SUMMEWENN-Funktion auftreten können, sowie deren Lösungen:

    • Fehler bei der Ausführung des Makros: Stelle sicher, dass alle Bereichszuweisungen korrekt sind und die angegebenen Bereiche existieren.
    • Debugging-Fehler bei SUMIFs: Überprüfe, ob die Syntax der Funktion korrekt ist. Achte auf Schreibfehler in den Variablen oder nicht vorhandene Bereiche.
    • Leistungsprobleme bei großen Datenmengen: Wenn du mit ca. 900.000 Zellen arbeitest, kann eine einfache SUMMEWENN-Funktion langsam sein. Setze in solchen Fällen lieber auf Datenarrays oder Pivot-Tabellen.

    Alternative Methoden

    Wenn die Verwendung von VBA und SUMMEWENN nicht die gewünschten Ergebnisse liefert, gibt es alternative Methoden:

    • Pivot-Tabellen: Sie sind effizienter für große Datenmengen. Mit Pivot-Tabellen kannst du Daten schnell aggregieren und analysieren.
    • Datenarrays: Diese Methode ermöglicht es, große Datenmengen in einem Array zu verarbeiten, was die Geschwindigkeit erheblich steigern kann.
    • Formeln direkt in Excel: Manchmal ist es sinnvoller, die Formeln direkt in den Zellen zu verwenden, anstatt sie über VBA zu verarbeiten.

    Praktische Beispiele

    Hier ist ein einfaches Beispiel für die Verwendung des Makros:

    Angenommen, du hast folgende Daten in Tabelle2:

    A B C
    Verkäufer Umsatz Monat
    Max 100 Januar
    Tom 150 Januar
    Max 200 Februar
    Tom 250 Februar

    Wenn du das Makro ausführst, wird es die Umsätze für jeden Verkäufer summieren und die Ergebnisse in Spalte C eintragen.

    Tipps für Profis

    • Fehlerbehandlung: Implementiere Fehlerbehandlungsroutinen in dein Makro, um unerwartete Fehler zu vermeiden.
    • Makros optimieren: Reduziere die Anzahl der Berechnungsschritte in deinem Code, indem du Daten in Arrays speicherst und dann auf einmal verarbeitest.
    • Dokumentation: Kommentiere deinen Code gut, um später die Funktionsweise deiner VBA-Skripte besser nachvollziehen zu können.

    FAQ: Häufige Fragen

    1. Frage
    Wie kann ich das Makro anpassen, um mehrere Kriterien anzuwenden?
    Antwort: Du kannst die SumIfs-Funktion verwenden, um mehrere Kriterien in einem Makro zu berücksichtigen. Achte darauf, die Bereiche entsprechend anzupassen.

    2. Frage
    Warum funktioniert mein Makro nicht, obwohl die Syntax korrekt aussieht?
    Antwort: Überprüfe, ob alle referenzierten Bereiche existieren. Manchmal kann ein leerer oder falscher Bereich zu einem Fehler führen.

    Beliebteste Forumthreads (12 Monate)

    Jetzt das Excel-Portal bewerten: Deine Stimme zählt!
    Anzeige
    Anzeige
    Anzeige