Office: (Office 2013) Dyn. VERGLEICHS-Bereiche innerhalb der INDEX-Funktion für die Matrizenmultiplikation

Hallo Experten,

Vor wenigen Wochen noch, wusste ich gar nicht, dass es so etwas gibt, geschweige denn, wie ich es anwenden könnte. Aber dank dieses Forums habe ich nun innerhalb einer INDEX-FUNKTION für einen VERGLEICH Matrizen ausmultipliziert, um die korrekte Zeilennummer zu erhalten.

Es geht dabei darum, dass im Tabellenblatt "FT" Fehlzeiten von Mitarbeitern erfasst werden, die im Tabellenblatt "Kalender" gezeigt. Da das ausmultiplizieren von Matrizen dabei ja durchaus rechen- und somit auch zeitintensiv ist, sollten ja auch für die VERGLEICHS-Bereiche, anstatt ganze Spalten, besser definierte Zellbereiche angegeben werden.

Definiere ich dabei die Zellbereiche zu knapp, kann es natürlich passieren, dass Fehlzeiten erfasst werden, die dann im Kalender nicht mehr gezeigt werden. Definiere ich die Zellbereiche zu großzügig, geht es auf die Rechenzeit. Im Beispiel, das ich hoch lade, habe ich nun schon die Anzeige der Mitarbeiter (F3-S3) zur ausgewählten Abteilung (E1), von INDIREKT auf INDEX umgestellt, nachdem mir dies Santa diese Woche im Zusammenhang einer anderen Fragestellung ( http://www.ms-office-forum.net/forum...d.php?t=320145 )
empfohlen hat und dabei freundlicherweise auch noch eine Referenz auf eine Anleitung nannte ( Online - Excel: Index als Bezug - die Superfunktion) ). Dies war relativ einfach.

Ich muss gestehen, dass ich diese sogar schon in meinen Favoriten hatte. Aber damals, als ich auf diese Anleitung gestoßen war, waren das für mich nur "böhmische Dörfer".

Nun würde ich gerne noch die Index Funktion (F6-S124) auf dynamische Zellbereiche umstellen. Mit der Funktion INDIREKT konnte ich dies auch ohne Probleme in den Zeilen 9-13 realisieren.

Aber, wie ich nun innerhalb der Index-Funktion INDIREKT durch INDEX ablösen kann, das ist mir im Moment noch nicht klar.

Wäre schön, wenn mir dies einer der Experten erklären und am besten noch in die Beispielmappe einfügen könnte.

:)

 

Als erstes würde ich alle FT!x1:x1000 Bereiche in allen Formeln durch über

Endpunkt: =ANZAHL2(FT!x:x)

gesteuerte dynamische Bereiche a la A1:INDEX(A:A;Endpunkt) ersetzen. Dann rödelt nicht jedes arme KKLEINSTE-Konstrukt unnötig lange.

Die Anzahl Deiner Matrixfunktionen ist jedoch schon nicht mehr klein (366 x 16 > 5000). Es wäre an der Zeit, eine viel schnellere Sub mit Arrays zur Aktualisierung zu fahren.

 

Hallo Lupo,

vielen Dank für deine Anworten. Mit Endpunkt meinst du wohl einen im Namensmanager definierten Namen? Wenn ja, dies habe ich hinbekommen. Aber ich schaffe es nicht, die Formel entsprechend anzupassen. Ich erhalte die Fehlermeldung "'WERT!" bei der folgenden Formel

Code:

 

Ersetze den Formelteil

FT!$E$1:$E$1000

durch

FT!$E$1:INDEX(FT!$E:$E$;Endpunkt) (du vergaßest das 2. FT!...)

wobei als Endpunkt eine Zelle $Z$1 effizienter als eine benannte Formel ist. Denn die benannte Formel wird bei jedem einzelnen Einsatz in einer Formel

bei Dir also nicht nur 5000-mal, sondern noch multipliziert um die 4 Auftritte pro Formel, also schon 20.000-mal;
der 5. Auftritt INDEX(FT!$E$1:$E$1000; wird übrigens korrekt zu INDEX(FT!$E:$E; oder INDEX(FT!E:E;

neu gerechnet; die Zelle hingegen nur einmal. Nimm also besser $Z$1: =ANZAHL2(x:x)

Schade jedoch, dass Du die "Gesamtformel" (nämlich die Sub) nicht in Betracht ziehst. Ich möchte Dich bitten, einmal mein Beispiel (unten auf der Seite) laufen zu lassen.

 

Hallo Lupo,

vielen Dank für die zusätzlichen Erläuterungen. Werde ich, sobald als möglich, testen. Habe leider etwas zu spät bemertk, dass ich in meiner Ersten Antwort/Nachfrage nicht auf die Alternative "Sub" eingegangen war. Musste dann aber zuerst einmal etwas anderes erledigen.

Es war nicht meine Absicht, diesen Hinweis zu ignorieren. Wollte aber, so wie der Bauer die Klöße ißt (immer einen nach dem anderen), zuerst einmal die verbesserte Formel zum laufen bringen, bevor ich mich dann der Sub annehme.

Sobald es funktioniert, oder ich noch Fragen habe, melde ich hier wieder.

 

Hallo Lupo,

habe nun deine Vorschläge testen können. Nachdem ich verstanden hatte, was du mit Endpunkt meinst, kann ich dir mitteilen, dass ich diesen im Tabellenblatt FT in Zelle K1 mit der Formel "=ANZAHL(B:B)+4" bereits wegen der INDIREKT-Zuweisungen, realisiert hatte.

Deine Formel "FT!$E$1:INDEX(FT!$E:$E$;Endpunkt", lief aber erst, als ich das fett markierte $-Zeichen entfernt hatte.

Mir war bisher nicht bekannt, dass ich bei der Definition eines Zellbereiches bspw. A1:A1000, nach dem Doppelpunkt eine Funktion, hier INDEX, hinterlegen kann. Das ist ja dann fast so wie INDIREKT. Geht dies generell, oder nur in bestimmten Fällen? Und wenn es nach dem Doppelpunkt geht, geht es auch davor?

Danach habe ich in einer leeren Mappe die von dir genannte SUB getestet. Dies benötigt bei mir zwischen 1,5 und 2 Sekunden. Dabei werden die Spalten A*biggrin.gif* bis Zeile 32000 gefüllt. G:I wird bis Zeile 51269 gefüllt. Von Zeile 65538 - 1048576 werden die Spalten F:I mit #NV gefüllt. Verstehe ich das richtig, dass dieser VBA-Code nur ein Performance Beispiel für die Array-Verarbeitung ist. Ich müsste diesen somit auf meine spezifische Fragestellung hin, neu erstellen. Dazu muss ich sagen, dass ich zwar hier im Forum auch schon ein paar Fragen mit VBA-Lösungen beantwortet habe, aber um jetzt die Fehlzeiten im Kalender mittels VBA einzutragen, sind meine VBA-Kenntnisse im Moment noch nicht ausreichend.

 

$: War mein Fehler

Doppelpunkt: Das ist INDEX()-spezifisch. Geht auch umgekehrt, ja: =INDEX(A:A;x):A99

VBA-Array-Beispiel: Ja, nur ein solches, denn vor 8 Jahren kannte ich Dein Problem ja nicht *wink.gif* Wäre individuell zu erstellen. Man braucht dafür sicher etwas Zeit, ab dann aber auf ewig wenig Rechenzeit: Abwägungssache.

 

Hallo,

hier VBA-Beispiel.

(Vachtrag: 2 >> etwas beschleunigt)

 

Hallo xlph,

vielen Dank!!! für dein VBA Beispiel. Hier in diesem Fall zeigt sich auch, dass die VBA-Variante gegenüber der Matrix-Formeln, auch wenn diese nun optimiert sind, um einiges schneller ist. Was sich ja auch mit der Aussage von Lupo deckt. Als weitere Optimierung ist wohl möglich, dass der VBA-Code bei Workbook_open nur laufen muss, wenn "Kalender" gerade schon das aktive Worksheet ist. Das sollte ich aber selbst hinbekommen. Ansonten laufen ja die Berechnungen immer nur dann ab, wenn diese erforderlich sind.

Ich werde aber den Code debuggen müssen, damit ich verstehe, was da im einzelnen geschieht.