VBA Rechenzeit fuer Matrixformel verkuerzen

Bild

Betrifft: VBA Rechenzeit fuer Matrixformel verkuerzen
von: MarkusX
Geschrieben am: 28.09.2015 17:20:24

Hallo Forum,
hat jemand eine Idee, wie ich fuer folgenden Code die Rechenzeit noch weiter optimieren koennte:

Sub ZeigeWerte()
'
' Matrixformel 
'
'
    
Dim ur As Long ' ur = letzte Zeile der Spalte A
Dim record As Integer  'bis zu 1000 gefundene Zeilen des Kriteriums
    ActiveSheet.Unprotect
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    
    ur = Sheets("Tabelle_1").Range("A65536").End(xlUp).Row 
    Range("J7").FormulaR1C1 = "=COUNTIF('Tabelle_1'!R2C1:R" & ur & "C[-2],R4C10)"
    
    record = Sheets("Scelta cliente").Range("J7").Value 
 
    For zeile = 1 To record
    
        Range("A3").Offset(zeile, 0).FormulaArray = _
            "=INDEX('Tabelle_1'!C[1],LARGE(IF('Dettagli record'!R2C1:R" & ur & "C1=R4C10,ROW(' _
Dettagli record'!R2:R" & ur & "))," & zeile & "))"
    Next zeile
    
    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True
    ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
End Sub
Vorab vielen Dank fuer euer Interesse und eure Tips.
Gruesse
Markus X

Bild

Betrifft: AW: VBA Rechenzeit fuer Matrixformel verkuerzen
von: Daniel
Geschrieben am: 28.09.2015 17:35:21
Hi
- soweit wie möglich, Zellen nicht einzeln per Schleife, sondern zusammen befüllen.
da man einzeilige Matrixformeln nicht in mehrere Zellen gleichzeitig schreiben kann, weil Excel dann mehrzeilige Matrixformeln daraus macht, befüllt man hier die erste Zelle einzeln und kopiert dann die Formel in die restlichen Zellen.
- Wenn die Anzahl der Werte nur für das Makro benötigt wird, kann man das CountIf auch direkt in VBA anwenden, ohne die Formel in eine Zelle schreiben zu müssen.

...
    Application.Calculation = xlCalculationManual
    
    ur = Sheets("Tabelle_1").Range("A65536").End(xlUp).Row 
    
    record = Worksheetfunction.CountIf(Sheets("Tabelle1").Range("A2:H" & ur), Range("J10"))
 
 
       
    Range("A3").Offset(zeile, 0).FormulaArray = _
            "=INDEX('Tabelle_1'!C[1],LARGE(IF('Dettagli record'!R2C1:R" & ur & "C1=R4C10,ROW('  _
_
Dettagli record'!R2:R" & ur & "))," & zeile & "))"
    Range("A3").Copy
    Range("A4".Resize(record - 1).pastespecial xlpasteall
    
    Application.Calculation = xlCalculationAutomatic
...
gruß Daniel

Bild

Betrifft: AW: VBA Rechenzeit fuer Matrixformel verkuerzen
von: MarkusX
Geschrieben am: 28.09.2015 18:54:34
Hallo Daniel,
danke fuer Deine Idee, die ich versuche anzuwenden, denn so richtig funktioniert das glaube ich hier nicht - aber vllt. verstehe ich es auch nicht richtig...
CountIf wird auch in der Tabelle benoetigt. record ist variabel und nummerisch und bestimmt die Anzahl Datensaetze zu bestimmten Codes (Zeichenfolgen). Die Matrixformel "holt" dann den gesuchten Wert in der entsprechenden Fundzeile.
Ich hatte den Code nun wie folgt abgeandert:
...
Range("J7").FormulaR1C1 = "=COUNTIF('Tabelle_1'!R2C1:R" & ur & "C[-2],R4C10)"

record = Sheets("Tabelle_2").Range("J7").Value

For zeile = 1 To record

Range("A4").Offset(zeile, 0).FormulaArray = _
"=INDEX('Tabelle_1'!C[1],LARGE(IF('Tabelle_1'!R2C1:R" & ur & "C1=R4C10,ROW('Tabelle_1'!R2:R" & ur & "))," & zeile & "))"
Range("A4").Copy
Range("A5").Resize(record - 1).PasteSpecial xlPasteAll
Next zeile
...
Hattest Du das so gemeint? Leider holt die Matrix aber nicht die gesuchten Werte.
Was mach ich da falsch, oder koennen wir das so nicht machen?
Danke und Gruss
Markus X

Bild

Betrifft: AW: VBA Rechenzeit fuer Matrixformel verkuerzen
von: Daniel
Geschrieben am: 28.09.2015 20:00:41
Hi
also meine Grundidee war, dass du die ArrayFormel nicht per Schleife in jede Zelle einträgst, sondern:
- die Arrayformel in die erste Zelle einträgst
- diese Zelle kopierst und in die restlichen Zellen einfügst.
ohne deine Datei zur verfügung zu haben, kann ich aber das alles nicht testen und dir auch keinen konkreten und funktionieren den Code sagen, sondern nur das Prinzip.
Gruss Daniel

Bild

Betrifft: AW: VBA Rechenzeit fuer Matrixformel verkuerzen
von: MarkusX
Geschrieben am: 28.09.2015 21:01:20
Hallo Daniel,
die Grundidee ist absolut einleuchtend, ich versuche mich noch daran und danke Dir fuer Deine Unterstuetzung!! Hier kannst Du Dir mal eine bereinigte Datei anschauen:
https://www.herber.de/bbs/user/100456.xlsm
Die Datensaetze werden ueber den Button/Makro "Nuova richiesta" (Modul 3) geloescht, so dass dann eine neue Auswahl mgl. wird und werden dann ueber Button/Makro "Mostra record" (Modul 2) angezeigt. Ist natuerlich jetzt ziemlich leer die Datei, um auf die erf. Dateigroesse zu kommen... Hoffe man kann's noch nachvollziehen...
Viele Gruesse
Markus X

Bild

Betrifft: AW: VBA Rechenzeit fuer Matrixformel verkuerzen
von: Daniel
Geschrieben am: 28.09.2015 21:27:53
Hi
noch ne Optimierungmöglichkeit, gerade wenns viele Datensätze sind:
lagere den Formelteil, welcher die Zeilennummer berechnet in eine Hilfsspale aus:
KGRÖSSTE(WENN('Dettagli record'!$A$2:$A$9=$J$4;ZEILE('Dettagli record'!$2:$9))
und verwende in den Datenspalten nur die Indexfunktion, welche sich dann die Zeilennummer aus dieser Hilfsspalte holt.
Da sich die Zellwerte einer Zeile in der Auswertung immer auf die selbe Zeile in der Datentabelle beziehen, muss man ja die Berechung der Zeilennummer nicht in jeder Zelle machen, sondern nur einmal pro Zeile.
Und da diese Berechnung der Zeilennummer der Hauptrechenaufwand ist, spart das viel Rechenzeit.
Gruss Daniel

Bild

Betrifft: AW: VBA Rechenzeit fuer Matrixformel verkuerzen
von: MarkusX
Geschrieben am: 28.09.2015 22:02:22
Hey Daniel,
auch noch am Start .... :). Guck mal, ich habe nun Deine Grundidee mal ein wenig umgebastelt und komme nun der Lsg. schon sehr nahe :
....
For zeile = 1 To record

' ID Ordine
Range("A4").Offset(zeile - 1, 0).FormulaArray = _
"=INDEX('Dettagli record'!C[1],LARGE(IF('Dettagli record'!R2C1:R" & ur & "C1=R4C10,ROW('Dettagli record'!R2:R" & ur & "))," & zeile & "))"
Range("A" & zeile + 3).Copy

If record = zeile Then Exit Sub Else

Range("A" & zeile + 4).Resize(record - zeile).PasteSpecial xlPasteAll
Next zeile
....
Problem: die Zeile "If record = zeile Then Exit Sub" bricht die Schleife zwar an der richtigen Stelle ab, aber ohne CutCopy.
Es ist schon spaet, ich steh auf dem Schlauch, kannst Du vllt. weiterhelfen??
Deine andere Idee muss ich mir morgen angucken... moechte erst die Schleife hinbekommen ...
Gruss
Markus X

Bild

Betrifft: AW: VBA Rechenzeit fuer Matrixformel verkuerzen
von: Daniel
Geschrieben am: 28.09.2015 22:16:33
hi
hatte ich dir nicht gesagt, dass das kopieren der Formel und einfügen in den restlichen Zellbereich die Schleife ersetzen soll?
Die For-Next-Schleife kann dann vollständig entfallen.
also einfach so:

Range("A4").FormulaArray = _
"=INDEX('Dettagli record'!C[1],LARGE(IF('Dettagli record'!R2C1:R" & ur & "C1=R4C10,ROW(' _
Dettagli record'!R2:R" & ur & "))," & zeile & "))"
Range("A4").Copy
Range("A4").Resize(record).PasteSpecial xlpasteall
mehr nicht.
keine Schleife, kein For-Next, kein IF-Then oder sonstiges
ich wiederhols nochmal für dich:
KEIN FOR - NEXT !!!!!!!!
die Formel wir nur in die erste Zelle eingetragen und dann per Copy-Paste in die restlichen Zellen auf einen Schlag gleichzeitig geschieben, ohne For-Next-Schleife!!!
Gruss Daniel

Bild

Betrifft: AW: VBA Rechenzeit fuer Matrixformel verkuerzen
von: MarkusX
Geschrieben am: 29.09.2015 09:16:12
Hallo Daniel,
hallo Forum
ich gebe zu, ich gerate hier an die Grenzen meines VBA-Verstaendnisses. Aber "also einfach so" funktioniert Dein Vorschlag leider nicht, da in der Matrix-Formel die Variable "zeile" benoetigt wird, um die Ergebnisse absteigend anzuzeigen. Prinzipiell kapier ich ja was du meinst, aber wie bekomme ich denn diese Variable ohne FOR-NEXT rein?
Vielleicht hat jemand anders weitere Ideen, oder kann mir auf die Spruenge helfen? Ich bemuehe mich ja und bin somit fuer jede Hilfe dankbar.
Gruesse
Markus X

Bild

Betrifft: AW: VBA Rechenzeit fuer Matrixformel verkuerzen
von: Daniel
Geschrieben am: 29.09.2015 10:58:55
Hi
ganz einfach mit der Funktion ROW() (und die verwendest du ja schon in der Formel)
in deinem Fall dann statt:

..."))," & zeile & "))"

dieses:
...")),Row(R[-3]C1))"
der Versatzwert -3 Berechnet nich aus der Zeile, in welche du die Formel als erstes einträgst und dem Startwert, den diese Funktion in der ersten Zeile ausgeben soll (in deinem Fall erste Zeile: 4 und Startwert 1 ergibt als versatz: 1-4 = -3)
Gruß Daniel

Bild

Betrifft: AW: VBA Rechenzeit fuer Matrixformel verkuerzen
von: MarkusX
Geschrieben am: 29.09.2015 12:01:19
Hallo Daniel,
ja mit Zeile() koennte es gehen, allerdings scheinbar nicht so wie Du meinst:
...,Row(R[-3]C1))"
das liefert direkt in dieser Stelle des Codes die Fehlermeldung: FormulaArray-Eigenschaft des Range-Objektes kann nicht festgelegt werden ...
aus dem Grunde hab ich Zeile() wie folgt genutzt:
...,Row()-3))"
das liefert allerdings in der Resize-Stelle einen Fehler: Teile eines Arrays koennen nicht geandert werden.
Verstehst Du was da los ist?
Gruss
Markus X

Bild

Betrifft: AW: VBA Rechenzeit fuer Matrixformel verkuerzen
von: Daniel
Geschrieben am: 29.09.2015 13:03:18
Hi
dann hast du dir irgendwann irgendwie ein Mehrzeiliges Array erstellt, welches du erst löschen musst.
mehrzellige Arrayformeln kann man aber nur bearbeiten, wenn man alle Zellen die dazugehören gleichzeitig bearbeitet.
klicke hierzu erst eine dieser Zellen an und wähle dann im Menü
Start - Bearbeiten - Suchen und Auswählen - Inhalte auswählen - Aktuelles Array
um alle Zellen zu markieren die zu diesem Array gehören um alle zusammen löschen zu können.
Gruß Daniel

Bild

Betrifft: AW: VBA Rechenzeit fuer Matrixformel verkuerzen
von: MarkusX
Geschrieben am: 29.09.2015 13:41:35
Hallo Daniel,
Mehrzelliges Array? Meine Matrixformel greift auf verschiedene Zellen in verschiedenen Arbeitsblaettern zu, deshalb verwende ich sie ja. Meinst Du das? "Inhalte auswaehlen" liefert immer nur die eine Zelle A4 in die das Makro gerade die Array Formel geschrieben hat.
Meinst Du es koennte mit Windows 10 zu tun haben? So einen Fall hatte ich dieser Tage naemlich schonmal...
Gruss
Markus X

Bild

Betrifft: AW: VBA Rechenzeit fuer Matrixformel verkuerzen
von: Daniel
Geschrieben am: 29.09.2015 14:16:26
Hi
ok ich hatte da im zweiten anlauf einen Fehler drin, so sollte es gehen:
1. trage die Array-Formel in die erste Zelle ein (A4)
2. kopiere diese Zelle
3. füge dann diese kopierte Zelle ab Zelle A5 in die restlichen Zellen ein. die Zelle A4, welche die Arrayformel bereits enthält, darfst du nicht mehr überrschreiben.
dh im Prinzp so

Range("A4").FormulaArray = "=Deine Arrayformel"
Range("A4").Copy
Range("A5").Resize(Anzahl - 1).PasteSpecial xlPasteall
anzahl ist die Anzahl der Zellen, in welche die Matrixformel übernommen werden muss, Anzahl - 1, weil in der ersten Zelle die Formel ja schon steht.
Gruß Daniel

Bild

Betrifft: Geloest
von: MarkusX
Geschrieben am: 29.09.2015 15:03:41
Hallo Daniel,
Dein Anzahl habe ich durch mein record ersetzt, und wie gesagt Rows()-3 entsprechend korrigiert. Fuer die Nachwelt, so funktioniert der Code somit:
...
record = Sheets("Scelta cliente").Range("J7").Value ' in der hochgeladenen Beispielmappe = 8
Range("A4").FormulaArray = _
"=INDEX('Dettagli record'!C[1],LARGE(IF('Dettagli record'!R2C1:R" & ur & "C1=R4C10,ROW('Dettagli record'!R2:R" & ur & ")),Row()-3))"
Range("A4").Copy
Range("A5").Resize(record - 1).PasteSpecial xlPasteAll
...
Voellig geil! Rechenzeit mind. halbiert!
Danke nochmal fuer die Unterstuetzung und Geduld ... !!
Gruss
Markus X

 Bild

Beiträge aus den Excel-Beispielen zum Thema "VBA Rechenzeit fuer Matrixformel verkuerzen"