Live-Forum - Die aktuellen Beiträge
Datum
Titel
29.03.2024 13:14:12
28.03.2024 21:12:36
28.03.2024 18:31:49
Anzeige
Archiv - Navigation
1472to1476
Aktuelles Verzeichnis
Verzeichnis Index
Übersicht Verzeichnisse
Vorheriger Thread
Rückwärts Blättern
Nächster Thread
Vorwärts blättern
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Inhaltsverzeichnis

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

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

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

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...

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige