Live-Forum - Die aktuellen Beiträge
Datum
Titel
19.10.2025 10:48:58
Zufällige Uhrzeit
19.10.2025 10:46:18
Zufallszahlen nur wenn Datum ab kommenden Sonntag
17.10.2025 17:57:35
Zellen-Name aus A1 nach H1 übertragen mit Prä- + Suffix
17.10.2025 11:36:58
verschachteltes Dropdown-Menü
Keine passende Antwort im Forumarchiv gefunden?
Stelle deine Frage im Live-Forum
Anzeige
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Vorheriger Thread
Vorheriger Thread
Rückwärts Blättern
1472to1476
1472to1476
Aktuelles Verzeichnis
Verzeichnis Übersicht
Verzeichnis Übersicht
Übersicht Verzeichnisse
Nächster Thread
Nächster Thread
Vorwärts blättern
Inhaltsverzeichnis

SummeWenn über Array bilden

Forumthread: SummeWenn über Array bilden

SummeWenn über Array bilden
11.02.2016 16:56:50
SteffenS
Hallo Zusammen,
hat jemand von Euch Erfahrungen mit Array und Berechnung in diesen
Ich habe aktuell das Problem, dass ich eine große Tabelle habe, wo ich dynamisch Summenformeln bildet.
Diese Werte der Formeln werden dann in ca. 7.000 Zeilen geschrieben.
Habe ich in der Quelldatei nur wenige Zeilen, so läuft alles recht fix.
Sobald ich Größenordnungen von 5.000 komme wird es zäh.
Meine Idee war jetzt die Werte komplett in ein Array einzulesen, die SummenWenn innerhalb des Array zu bilden und dann nur Ergebnisse zu übernehmen.
Könnte die performanter sein
Danke Euch schon mal.
VG
Steffen S

Anzeige

13
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: SummeWenn über Array bilden
11.02.2016 18:08:32
ransi
Hallo,
Könnte die performanter sein ?
Könnte: ja
Aber ohne deine Datenstruktur zu kennen ist das wie "im Kaffesatz lesen".
ransi

AW: SummeWenn über Array bilden
11.02.2016 18:27:30
Daniel
Hi
es ist auf jedenfall dahin gehend performanter, dass die Ergebnisse im Tabellenblatt statisch sind und nur dann neu berechnet werden, wenn du das Makro startest und nicht jedesmal, wenn du irgendwas mit der Tabelle machst (Sortieren, Filtern usw).
den gleichen Effekt hast du aber auch, wenn du die Formeln kopierst und durch ihre Werte ersetzt
(das kannst du per Makro machen oder auch von Hand, ich habe früher dann immer die Formeln in der ersten Zeile stehen lassen, um dann einfach per Doppelklick eine Aktualisierung vornehmen zu können, ohne die Formeln neu schreiben zu müssen)
die SummenWenns per Makro in einem Array rechnen zu lassen ist vorallem dann sinnvoll, wenn mit der selben Datentabelle mehrere unterschiedliche SummenWenns gebildet werden müssen und fortgeschrittene Programmiertechniken wie assoziative Arrays (Dictionarys) eingesetzt werden. Denn damit ist es möglich, alle benötigten SummeWenns in einem einzigen Schleifendurchlauf über die Werte zu berechnen.
(als Formel bedeutet jedes SummeWenn eine Schleife über alle Werte, womit du dir über die Anzahl der SummeWenn-Formeln leicht ausrechnen kannsst, wie hoch die Zeitersparnis sein wird)
Gruß Daniel

Anzeige
AW: SummeWenn über Array bilden
11.02.2016 18:40:55
Daniel
Hi
oder noch ne alternative:
Werte die Daten mit Hilfe einer Pivottabelle aus.
Geht (wenn man es verstanden hat) schnell und einfach mit ein paar Mausklicks und ist auch bei grössten Datenmengen sehr schnell.
Gruß Daniel

AW: SummeWenn über Array bilden
11.02.2016 20:14:01
SteffenS
Hallo,
danke für die vielen Tipps. Das mit der Pivot ist ne gute Idee, aber für meinen Zweck nicht unbedingt geeignet.
Meine Quelltabelle besteht aus 33 Spalten und ca. 6.000 Zeilen.
In der Zieltabelle wird in 700 Zeilen pro Zeile ein Wert vorgegeben und dann eine Summe pro Monat gebildet. Da es sich nur um Kriterium handelt und pro Ergebnisspalte nur die Summenspalte wechselt ist die Funktion SummeWenn vollkommen ausreichend.
Wie könnte dies mit einem Array aussehen, da ich hier bisher noch keine Erfahrungen habe?
Danke Euch nochmals.
VG
SteffenS

Anzeige
AW: SummeWenn über Array bilden
11.02.2016 20:20:48
Daniel
Hi
das hängt jetzt von deinen Daten ab, was genau ausgewertet werden muss.
du solltest jetzt mal mit deiner Beschreibung konkret werden und vielleicht auch ein gekürztes, anonymisiertes Beispiel hochladen.
Gruß Daniel

AW: SummeWenn über Array bilden
11.02.2016 20:21:24
ransi
Hallo Steffen
Lade doch mal ne abgespeckte Version deiner Datei hoch.
Man soll erkennen können was wo steht und was wie wo nach welchen Kriterien berechnet werden soll.
ransi

Anzeige
AW: SummeWenn über Array bilden
11.02.2016 20:53:20
SteffenS
Hallo Zusammen,
anbei eine Datei: https://www.herber.de/bbs/user/103479.xlsx
Ist sehr vereinfacht.
Die Daten bekomme ich über den folgenden Code schon in das Array rein. Wie kann ich diese aber per SummeWenn abfragen:
Dim i As Long, j As Long
Dim WS As Worksheet
Set WS = Workbooks(ActiveWorkbook.Name).Sheets(ActiveSheet.Name)
Dim aArray(1 To 10000, 1 To 33) ' As Variant
i = 2
Do Until WS.Cells(i, 1).Value = ""
j = 1
Do Until WS.Cells(i, j).Value = ""
aArray(i, j) = WS.Cells(i, j)
j = j + 1
Loop
i = i + 1
Loop
Danke Euch schon mal.
VG SteffenS

Anzeige
AW: SummeWenn über Array bilden
11.02.2016 21:29:58
Daniel
Hi
also in der Beispieldatei ist das einfachste wahrscheinlich, wenn du die SummeWenn-Formeln in die Zellen schriebst und dann fürs Weiterarbeiten die Formeln durch die Werte ersetzt.
im Prinzip kommt in jede Zelle die die gleiche Formel (dh mit Copy-Paste übertragbar), denn der Suchwert für das Summe Wenn setzt sich aus dem festen Wert 2016 und dem Wert aus der Spalte B in der gleichen Zeile zusammen.
Du brauchst also diese Formel in der erste Zelle von "Formel" diese Formel (Formel für E2):
=SummeWenn(Werte!$Z:$Z;$B2&"2016";Werte!L:L)
würde als Code dann so aussehen:
with Sheets("Formeln")
With .Range("E2:P" & .Cells(Rows.count, 2).end(xlup).row)
.Formula = "=CountIf(Werte!C26,RC2&"2016",C[7])"
.Formula = .Value
End with
End with
idalerweise schreibst du das 2016 auch noch irgendwo in eine Zelle und liest es dazu aus.
Check mal, ob das schnell genug ist und sich damit weitere Programmierarbeit nicht erübrigt.
Gruß Daniel

Anzeige
AW: SummeWenn über Array bilden
11.02.2016 21:53:15
SteffenS
Hallo,
dies habe ich schon gemacht nur leider ist Laufzeit nicht so super.
Ich habe es mit dem Zeilenweisen kopieren versucht, nur dauert dies noch länger.
Hat von Euch schon einmal jemand ein Array mit Bedingung summiert?
Mein Ansatz war:
aval = .SumIf(.index(aArray, 1, aspalte), astring, .index(aArray, 1, j - 5))
nur leider funktioniert dies nicht.
Danke Euch schon mal.
VG SteffenS

Anzeige
AW: SummeWenn über Array bilden
11.02.2016 22:34:26
Daniel
Hi
ein Makro für diese Auswertung könnte so aussehen:
Sub Auswertung()
Dim ArrWert
Dim ArrID
Dim dicE
Dim ID As String
Dim z As Long, s As Long
'--- Daten in Array schreiben
With Sheets("Werte")
With .Range("A2:Z" & .Cells(.Rows.Count, 1).End(xlUp).Row)
ArrWert = .Columns(12).Resize(, 12).Value
ArrID = .Columns(26).Value
End With
End With
'--- Summen bilden in Dictionary
Set dicE = CreateObject("scripting.dictionary")
For z = 1 To UBound(ArrWert, 1)
For s = 1 To UBound(ArrWert, 2)
ID = ArrID(z, 1) & "-" & s
dicE(ID) = dicE(ID) + ArrWert(z, s)
Next
Next
'--- Ergebnisse zurückschreiben
With Sheets("Formel")
With .Range("E2:P" & .Cells(.Rows.Count, 2).End(xlUp).Row)
.ClearContents
ArrWert = .Value
ArrID = .Columns(1).Offset(0, -3).Value
For z = 1 To UBound(ArrWert, 1)
For s = 1 To UBound(ArrWert, 2)
ID = ArrID(z, 1) & "2016" & "-" & s
ArrWert(z, s) = dicE(ID)
Next
Next
.Value = ArrWert
End With
End With
End Sub
Gruß Daniel

Anzeige
AW: SummeWenn über Array bilden
12.02.2016 10:10:08
SteffenS
Hallo,
danke für die super-Antwort.
Da ich bisher noch nicht mit Dictionaries gearbeitet habe verstehe ich es noch nicht ganz.
Wo wird die bedingte Summe über die Spalte gebildet?
Wa ist die Bedingungs- und Ergebnisspalte?
Danke Euch nochmal
VG SteffenS

AW: SummeWenn über Array bilden
12.02.2016 10:48:54
Daniel
Hi
Bedingungs- und Ergebnisspalte habe ich aus deiner Beispieldatei übernommen.
die bedingte Summe wird im Mittelteil gebildet (--- Summen bilden im Dictionary)
Ein Dictionary ist im Prinzip ein eindimensionales Array, aber mit einem Freitext-Index (dh als Index kannst du alles verwenden, jeden beliebigen Text aber auch Objekte usw).
ich Verwende als Index den Wert aus Spalte Z (deine ID-Nummer + Jahr) und die Spaltennummer.
Somit kann ich die Werte eindeutig jedem Feld in der Ergebnissicht zuordnen.
Gruß Daniel

Anzeige
AW: SummeWenn über Array bilden
17.02.2016 15:57:06
SteffenS
Danke Dir nochmal, muss hier noch etwas tiefer einsteigen...
;
Anzeige
Anzeige

Infobox / Tutorial

SummeWenn über Array in Excel optimieren

Schritt-für-Schritt-Anleitung

Um die Funktion SummeWenn über ein Array in Excel zu nutzen, kannst du folgende Schritte befolgen:

  1. Daten in ein Array einlesen: Verwende VBA, um die Werte deiner Tabelle in ein Array zu übertragen. Ein Beispielcode könnte folgendermaßen aussehen:

    Dim i As Long, j As Long
Dim WS As Worksheet
Set WS = Workbooks(ActiveWorkbook.Name).Sheets(ActiveSheet.Name)
Dim aArray(1 To 10000, 1 To 33) ' As Variant
i = 2
Do Until WS.Cells(i, 1).Value = ""
   j = 1
   Do Until WS.Cells(i, j).Value = ""
       aArray(i, j) = WS.Cells(i, j)
       j = j + 1
   Loop
   i = i + 1
Loop

  2. Bedingte Summenbildung im Array: Verwende die Funktion SumIf, um die Summen basierend auf den Bedingungen zu berechnen. Ein Beispiel für die Verwendung könnte so aussehen:

    Dim total As Double
Dim criteria As String
criteria = "Bedingung" ' Setze hier deine Bedingung
total = Application.WorksheetFunction.SumIf(aArray, criteria, aArray)

  3. Ergebnisse zurückschreiben: Nachdem du die Summen berechnet hast, kannst du die Ergebnisse in dein Arbeitsblatt zurückschreiben.

Häufige Fehler und Lösungen

  • Fehler beim Einlesen der Daten:

    • Stelle sicher, dass die Range korrekt definiert ist und keine leeren Zeilen in der Quelldatei vorhanden sind.

  • Laufzeitprobleme:

    • Wenn die Berechnungen zu lange dauern, überprüfe, ob du die Anzahl der Berechnungen durch das Reduzieren der SummeWenn-Formeln verringern kannst. Die Verwendung von Arrays kann hier helfen.

  • Fehler bei der Summenbildung:

    • Achte darauf, dass die Kriterien und die Daten im Array korrekt übereinstimmen.

Alternative Methoden

Wenn die Verwendung von Arrays nicht die gewünschte Leistung bringt, kannst du folgende Methoden in Betracht ziehen:

  1. PivotTable: Nutze eine PivotTable, um schnell und effizient Summen zu erstellen. Dies ist besonders nützlich bei großen Datenmengen.

  2. Dictionaries: Wie in einem der Forumbeiträge erwähnt, kannst du ein Dictionary verwenden, um die Summen während des Durchlaufens der Daten zu aggregieren. Dies kann die Leistung erheblich verbessern.

Praktische Beispiele

Angenommen, du hast eine Tabelle mit Verkaufsdaten und möchtest die Gesamtverkäufe für ein bestimmtes Produkt im Jahr 2016 summieren:

  1. Daten im Array speichern: Verwende den oben genannten Code, um die Verkaufsdaten in ein Array zu laden.

  2. Summenbildung: Setze die Bedingungen für die Summenbildung in der Form:

    Dim salesSum As Double
salesSum = Application.WorksheetFunction.SumIf(aArray, "Produkt A", aArray)

  3. Ergebnisse nutzen: Schreibe die berechneten Summen in eine separate Zelle deiner Wahl.

Tipps für Profis

  • Vermeide übermäßige Berechnungen: Wenn du viele SummeWenn-Formeln hast, ziehe in Betracht, sie durch einmalige Berechnungen zu ersetzen und die Werte zu speichern.

  • Nutze assoziative Arrays: Mit assoziativen Arrays (Dictionaries) kannst du die Berechnungen optimieren, indem du mehrere Summen in einem Durchlauf berechnest.

  • Dokumentation: Dokumentiere deinen Code gut, insbesondere wenn du komplexe Logik verwendest, wie z.B. die Nutzung von Dictionaries.

FAQ: Häufige Fragen

1. Wie kann ich die Leistung bei großen Datenmengen verbessern? Verwende Arrays und reduziere die Anzahl der Berechnungen in Echtzeit, indem du Ergebnisse speicherst und nur bei Bedarf aktualisierst.

2. Was ist der Unterschied zwischen SummeWenn und SummeWenns? SummeWenn wird verwendet, um Summen basierend auf einer einzigen Bedingung zu berechnen, während SummeWenns mehrere Bedingungen unterstützt.

3. Wie kann ich Fehler bei der Summenbildung im Array vermeiden? Vergewissere dich, dass deine Kriterien und die Daten im Array übereinstimmen und teste deinen Code schrittweise.

Beliebteste Forumthreads (12 Monate)

Jetzt das Excel-Portal bewerten: Deine Stimme zählt!
Anzeige
Anzeige
Entdecke mehr
Finde genau, was du suchst

Die erweiterte Suchfunktion hilft dir, gezielt die besten Antworten zu finden

Suche nach den besten Antworten
Unsere beliebtesten Threads

Entdecke unsere meistgeklickten Beiträge in der Google Suche

Top 100 Threads jetzt ansehen
Anzeige