Performance Problem

[bea die erste]

Hallo und erstmal einen wunderschönen Sonntag gewünscht,

brauche mal wieder einen Denkanstoß:
habe eine Mappe -> Auswertung. Diese bezieht Daten aus der Mappe ->Liste. Hier liegt eine Grundliste aller relevanten Daten drin mit bis zu 30000 Datensätzen.
In der Mappe Auswertung werden nun die Daten mit verschiedenen Bedingungen ausgewertet, wie z.B. mit dieser Formel->
=ZÄHLENWENNS([liste_Bockenheim.xlsm]Liste!$F$1:$F$65536;'Jahresübersicht'!$B16;[liste_Bockenheim.xlsm]Liste!$G$1:$G$65536;CU$32;[liste_Bockenheim.xlsm]Liste!$A$1:$A$65536;'A (7)'!$C$32)
So ähnliche Formeln habe ich ca. 2200 pro Auswertungsblatt.
In der Mappe Auswertung gibt es je nach Stadtteil bis zu 30 solcher Bewertungsblätter (Auswertungsblätter).
Ändere ich nun in "Liste" die Daten, also kopiere ich eine aktuelle Liste rein, was ca. 1 mal im Jahr gemacht werden muss, dann dauert die Berechnung eeeeeewig.
Wenn es nicht gar ganz abstürzt.

Hat da jemand eine andere Idee?

Ich wäre für jede nur denkbare Lösung dankbar.

Danke fürs lesen und Grüße

[shift-del]

Hallo Bea

Verwende statt ZÄHLENWENNS() eine Pivot-Tabelle.

[bea die erste]

Hallo Bea

Verwende statt ZÄHLENWENNS() eine Pivot-Tabelle.

Hallo,

danke, geht aber nich
Die Auswertungen brauche ich für den jeweiligen Stadtteil als gesonderte Mappe und das ganze für nicht Excel Nutzer.
Daher hab ich die Pivots, die nur zusätzliche Auswertungen ergeben, auch nicht dafür in Betracht gezogen.

Siehst du noch ne andere Lösung?

Ich MUSS 36 Mappen (Stadteile) anlegen die jeweils eine eigene Liste als Datenherkunft haben, anlegen.
Darin müssen verschiedenste Auswertungen stehen, die, wenn alle Gruppierungen zugeklappt sind, ausgedruckt werden müssen.
Um dann vor Ort mit eigenen zusätzlichen Bewertungen und Wichtungen ergänzt zu werden.

Aber danke auf jeden Fall fürs nachdenken.

Grüße

[shift-del]

Hallo Bea

Was denn nun: 30 Blätter in einer Mappe oder 30 Mappen mit einem Blatt? Eine Quell-Liste oder 30 Quell-Listen?

Und wenn bei gruppierten Daten nur die oberste Stufe interessiert, dann kann man doch einfach die anderen Stufen weglassen und hat schon viel weniger Formeln.

Pivot-Tabellen machen keine 'zusätzlichen Auswertungen' sondern nur die die man vorgibt.

Um dann vor Ort mit eigenen zusätzlichen Bewertungen und Wichtungen ergänzt zu werden.

Auf dem Ausdruck? Und was passiert dann?

[bea die erste]

Hallo,

ich hab mal ein Auswertungsblatt angehängt. Das ist nun nicht für einen Stadtteil, aber so ungefähr mit fast der gleichen Anzahl an Kriterien brauch ich es auch für Stadtteile.
Diese sprengt schon völlig den Rahmen. Ich muss es morgen mal im Betrieb laufen lassen.
Von diesem können es bis zu 30 Blätter pro Mappe sein.
Wie du siehst (Gruppierungen) brauch ich auch die Detailauswertungen.
Zur Flächenberechnung und weiteren Wertungen.

Es gibt pro Mappe eine Quellliste. Die Inhalte lese ich mir aus einer Verwaltungssoftware aus.
Ca. einmal im Jahr müssen diese Listen dann gegen neue ausgetauscht werden.

Grüße

[shift-del]

Hallo Bea

Mehr fällt mir dazu nicht ein.

[bea die erste]

Hallo Bea

Mehr fällt mir dazu nicht ein.

Danke dir

Schönen Restsonntag noch

[schauan]

Hallo bea,

wenn vba möglich ist, würde ich zwei Ansätze ausprobieren:
- temporäre Nutzung der Formeln, d.h. Formeln per VBA für einen bestimmten Bereich setzen und nach der Berechnung die Formeln durch Werte ersetzen.
- Datenbankabfrage auf die externen Daten per vba

Die vba-Makros könnten übrigens auch in einer anderen Excel-Datei stehen und Dein Arbeitsblatt bearbeiten und füllen.

[bea die erste]

Hey,
da bin ich überfordert...aber total.
Hab mich mal an folgender Lösung versucht:

Code:

Sub Summenprodukt_überVBA()

Dim Start As Double
Dim Ende As Double
Start = Timer

Dim z As Long
Dim n As Long

Application.ScreenUpdating = False
With Worksheets("Liste")
   For z = 2 To .Cells(Rows.Count, 1).End(xlUp).Row
       If .Cells(z - 1, 1) <> .Cells(z, 1) Then
           n = z
       End If
        .Cells(z, 9).Value = _
           Evaluate("=SUMPRODUCT((A" & n & ":A" & z & "=A" & z & ")*(F" & n & ":F" & z & "=F" & z & ")*(H" & n & ":H" & z & "))")
   Next z
End With
Application.ScreenUpdating = True

Ende = Timer
MsgBox Ende - Start & " Sekunden"

End Sub

Das ist nur ein Versuch der noch nicht angepasst ist.
Ich wollte nur schaue ob ich einen Teil der Formeln in/durch VBA ersetzen kann.
Das klappt auch. Nur so errechne ich mi eine Kombination.
Das müsste ja 87 mal gemacht werden um nur eine Zeile zu ersetzen.
Oder hab ich da ein Verständnis Problem?

In VBA bin ich blutige Anfängerin.
Daher schaffe ich das ohne Hilfe nicht.

Ich google noch mal etwas. Danke und Grüße
Bea

[schauan]

Hallo bea,

wenn die Formeln in einer Zeile relativ zueinander passen, dann reicht es, die Formel 1x für den ganzen Bereich zu setzen. Ich habe dazu mal ein Beispiel, wo ich mit Aufzeichnen begonnen habe. Im 2. Makro habe ich die ganzen Select's rausgenommen und dadurch den code auch etwas kürzer bekommen. Das Makro3 wäre dann die Umsetzung dieser Aufgabe entsprechend meinem ersten Satz hier. Die eckige Klammer bedeutet übrigens, dass die Formel relativ ist, also hier die Zeilennummer, wo die Formel eingetragen wird (R = Row) +1. Willst Du eine Zelle absolut ansprechen, dann gibst Du die Zeilen- und Spaltennummer absolut ein, also für die Zelle C2 z.B. R2C3

Code:

Sub Makro1()
    Range("A1").Select
    ActiveCell.FormulaR1C1 = "=R[1]C"
    Range("B1").Select
    ActiveCell.FormulaR1C1 = "=R[1]C"
    Range("C1").Select
    ActiveCell.FormulaR1C1 = "=R[1]C"
    Range("C2").Select
End Sub

Sub Makro2()
    Range("A1").FormulaR1C1 = "=R[1]C"
    Range("B1").FormulaR1C1 = "=R[1]C"
    Range("C1").FormulaR1C1 = "=R[1]C"
End Sub

Sub Makro3()
    Range("A1:C1").FormulaR1C1 = "=R[1]C"
End Sub