Excel: INDEX besser als BEREICH.VERSCHIEBEN?

Computer: Software & Programmierung Office Software
#1

Hallo Profis,

ich habe in einigen Formeln mit BEREICH.VERSCHIEBEN bzw. INDIREKT gearbeitet, um auch nach Einfügen/Löschen von Zeilen einen stabilen Bezug zu Nachbarzeilen zu haben (siehe eine frühere Frage von mir). Das wirkt sich in der Tabellenfläche wohl ungünstig auf die Performance aus. Ist es in diesem Sinne günstiger, wenn ich auf die unterhalb liegende Zelle anstatt mit

BEREICH.VERSCHIEBEN(A1;1;0)

lieber mit

INDEX(A1:A2;2)

zugreife?

Bestlichen Dank und sonntägliche Grüße :o)
-Rob.

#2

Hallo Rob
hier mal ein Link den ich sehr interessant finde.
Lähmende Funktionen - von volatil und mehr:
http://www.online-excel.de/excel/singsel.php?f=171

Erklärt vermutlich auch die Zitat: Gefühlte Langsamkeit

Gruß Holger

#3

Frage noch offen
Hallo Holger,

hier mal ein Link den ich sehr interessant finde.
Lähmende Funktionen - von volatil und mehr:
http://www.online-excel.de/excel/singsel.php?f=171

Danke Dir für den Tipp. Aber nach der Lektüre dieses Artikels (auch Teil zwei) bin ich ja erst auf die Idee mit INDEX gekommen, obwohl das ja darin nicht einmal erwähnt wird. :o) Es heißt zwar an anderer Stelle, daß sich BEREICH.VERSCHIEBEN meistens „mit etwas Mathematik“ vermeiden ließe. Welches Mittel aber nun wirklich günstiger für die Performance ist, wird nicht deutlich gemacht. Leider …

Abendliche Grüße vom ´:open_mouth:)
-Rop.

#4

Grüezi Rob

ich habe in einigen Formeln mit BEREICH.VERSCHIEBEN bzw.
INDIREKT gearbeitet, um auch nach Einfügen/Löschen von Zeilen
einen stabilen Bezug zu Nachbarzeilen zu haben (siehe eine
frühere Frage von mir). Das wirkt sich in der Tabellenfläche
wohl ungünstig auf die Performance aus. Ist es in diesem Sinne
günstiger, wenn ich auf die unterhalb liegende Zelle anstatt
mit

BEREICH.VERSCHIEBEN(A1;1;0)

lieber mit

INDEX(A1:A2;2)

zugreife?

Kurz und knapp - Ja

INDEX() ist wohl nicht viel performanter wenn es um das Holen der Daten geht, ist aber eben nicht volatil, sprich wird nicht bei jeder (anderen) Neu-Berechnung ebenfalls neu berechent.
Und dieser Punkt macht sich dann in der Gesamt-Performance wohl recht deutlich bemerkbar.

Mit freundlichen Grüssen

Thomas Ramel

  • MVP für MS-Excel -
#5

Hallo Rob,

http://www.online-excel.de/excel/singsel.php?f=171

Aber nach der Lektüre dieses Artikels
(auch Teil zwei) bin ich ja erst auf die Idee mit INDEX
gekommen, obwohl das ja darin nicht einmal erwähnt wird. :o)

wie, erst nach dem Lesen des Artikels hast du hier gefragt?
Warum hast du ihn dann nicht in der Anfrage genannt und das geschrieben was du hier schreibst?

Dann wäre dir vielleicht gleich dies genannt worden:

http://www.online-excel.de/excel/singsel.php?f=180

Gruß
Reinhard

#6

Merci :smile:
Salu Reinhard,

http://www.online-excel.de/excel/singsel.php?f=171

Aber nach der Lektüre dieses Artikels
(auch Teil zwei) bin ich ja erst auf die Idee mit INDEX
gekommen, obwohl das ja darin nicht einmal erwähnt wird. :o)

wie, erst nach dem Lesen des Artikels hast du hier gefragt?
Warum hast du ihn dann nicht in der Anfrage genannt und das
geschrieben was du hier schreibst?

Naja, weil ich meine Frage darin, und auch darin:

Dann wäre dir vielleicht gleich dies genannt worden:

http://www.online-excel.de/excel/singsel.php?f=180

nicht eindeutig beantwortet fand. In den Einlassungen zu INDEX heißt es zwar „Wenn Sie jetzt fragen warum man denn überhaupt die Formel in B2 ersetzen will, dann nochmals der Hinweis auf: !Langsames Excel (1) - Volatile Funktionen. Die Indirekt Funktion ist nunmal nicht gerade gut für Performance auf die Mappe betrachtet und wenn ich sie vermeiden kann, dann sollte ich sie auch vermeiden.“ Irgendwo anders bei diesem Autoren heißt es nämlich auch, man solle BEREICH.VERSCHIEBEN ersetzen, ohne daß dann vor dem naheliegenden INDIREKT gewarnt wurde. Da ich den Interpreter von Excel-VBA nicht kenne, ist es mir nicht einfach möglich, aus dieser Aussage zu schließen, daß bzw. inwieweit INDEX als Alternative zu den anderen Verdächtigen empfohlen werden kann.

Anders gesagt: Da in allen Einlassungen zu langsamen Volatil-Funktionen nicht einmal INDEX als Alternative genannt wird, bin ich mir nicht sicher, ob INDEX nun die allgemein zu empfehlende Alternative ist oder nur eine geringfügige Verbesserung.

Noch kürzer, und meine Frage hier hoffentlich einigermaßen rechtfertigend: Ich habe eine Riesendatei sowie einige ältere Dateien, deren Volaltil-Funktionen ich ggf. mit großer Mühe überarbeiten würde. Diesen Aufwand möchte ich dann nicht nur auf Informationen stützen, die mir regelmäßig sagen, was ich besser nicht verwenden soll, sondern ich möchte ein klares Statement, welche Methode das Volatil-Problem umgeht, bzw. welche Methode in dieser Hinsicht das Optimum darstellt.

Thomas hat mir mit seiner Erklärung zum Verhalten von INDEX (Datenbeschaffung nicht/kaum schneller, wird aber nicht ständig aktualisiert) Gewißheit verschafft und mich in der Materie geschult. Das hatte mir gefehlt. Das beruhigt ungemein, wenn man vor großen Excel-Mappen-Umwälzungen steht :o)

Viele Grüße
-Rob.

#7

Ja, INDEX ist besser als BEREICH.VERSCHIEBEN
Salu Thomas,

Ist es in diesem Sinne
günstiger, wenn ich auf die unterhalb liegende Zelle anstatt
mit

BEREICH.VERSCHIEBEN(A1;1;0)

lieber mit

INDEX(A1:A2;2)

zugreife?

Kurz und knapp - Ja

INDEX() ist wohl nicht viel performanter wenn es um das Holen
der Daten geht, ist aber eben nicht volatil, sprich wird nicht
bei jeder (anderen) Neu-Berechnung ebenfalls neu berechent.
Und dieser Punkt macht sich dann in der Gesamt-Performance
wohl recht deutlich bemerkbar.

Genau das wollte ich wissen. Diese Information ist für mich Gold wert - vielen Dank. Ich werde mich künftig eingehend mit INDEX() beschäftigen und versuchen, alle BEREICH.VERSCHIEBEN()s und INDIREKT()s zu ersetzen.

Danke & bestliche Grüße :o)
-Rob.

#8

Grüezi Rob

Thomas hat mir mit seiner Erklärung zum Verhalten von INDEX
(Datenbeschaffung nicht/kaum schneller, wird aber nicht
ständig aktualisiert) Gewißheit verschafft und mich in der
Materie geschult. Das hatte mir gefehlt. Das beruhigt
ungemein, wenn man vor großen Excel-Mappen-Umwälzungen steht

o)

Vielleicht noch ein Nachtrag hierzu:

Wenn es um Auswertungen grösserer Datenmengen geht ist die Pivot-Tabelle im Endeffekt wohl unschlagbar was die Performance und Flexibilität anbelangt (und sie brauch nur ein klein wenig Einarbeitungszeit :wink:).

Mit freundlichen Grüssen

Thomas Ramel

  • MVP für MS-Excel -
1 Like
#9

Hallo Rob,

jetzt wieder Rob? War doch im Posting vorher Rop!
Was gilt denn nun? *lächel*

Im Ernst. Ich bin kein Theoretiker. Und die Excel-Hilfe ist so eine Sache, manches ist sogar für mich verständlich erklärt.
Grad bei Index() trifft das überhaupt nicht zu und Peter sprach mir aus der Seele.

Mal deine eine Mappe weglassend die beim Filtern von 20 Zeilen/zellen schon 20 sec braucht was nicht normal ist merkt man bei kleinen Datenmengen nix von Volatile oder Unterschiede zwischen Bereich.Verschieben und Index.

ABER, was hindert dich daran, so wie ich es mache um was zu kapieren, eine Tabelle, eine mappe anzulegen mit irren viel Daten/Formeln?
Und dann mal zu schauen was so zeitlich passiert wenn du anstatt Bereich.Verschieben Index benutzt oder anstatt Matrix/Array-Formeln Hilfsspalten u.ä. Vergleiche…

Dadurch daß du mit Vba erstmal die Berechnung auf manuell stellen kannst, dann irgendwo was verändern dann mit dem Befehl Calculate die Berechnung anschubsen kannst kriegste doch die unterschiedlichen Zeiten dir Excel braucht raus.

Deshalb wäre/ist es m.M. nach wichtiger für dich als da Theorie zu büffeln in der einen anderen Beitragsfolge von dir dadrauf einzugehen wo ich dich fragte was du an meiner Zeitmessung in meinem Code nicht verstanden hast.

Denn das dahinterliegend Grundprinzip ist simpel:

Pseudocode
T=Timer 'bedeutet T merkt sich die aktuelle zeit.
Berchne ein Blatt in dem nur Bereich.Verschieben Formeln stehen.
Wenn fertig merke dir Timer-T also die benötigte Zeit.
T=Timer 'bedeutet T merkt sich die aktuelle zeit.
Berchne ein Blatt in dem nur Index Formeln stehen.
Wenn fertig merke dir Timer-T also die benötigte Zeit.
Zeige beide (verbrauchte) zeiten.

Okay, da Volatile auch Blattübergreifend ist müßte man da ggfs. in verschiedenen mappen testen.

Ergo ist es für deine Excel/Vba Zukunft unabdingbar daß du völlig egal um es geht so Zeittests selbst basteln kannst.
Und du mußt auch wissen daß bei so Zeitmessungen Phantasiewerte herauskommen können.

Liegt m.E. daran daß in Windows ja zeitgleich andere prozesse laufen können die Excel ausbremsen oder so, zumindest fand ich bislang noch keine richtige Erklärung dafür wenn zigfach nacheinander durchgeführte zeitvergleiche so erstaunliche Unterschiede aufwiesen.

Wenn jmd. dazu was weiß, sehr gerne her mit Informationen, Danke

Gruß
Reinhard

1 Like
#10

Salu Reinhard,

jetzt wieder Rob? War doch im Posting vorher Rop!
Was gilt denn nun? *lächel*

Naja, eigentlich Rob, abba manchmah habbich hn Drang, nah am Gesprochinin tsu schreihm, dann wiaht aus „Rob“ ehm „Rop“. :o)

Was die Excel-Hilfe betrifft, stimme ich Dir zu. Es ist ja auch nicht so, daß sie fehlerfrei wäre. Zudem ist das Erscheinungsbild mit der 2010er-Version so seltsam großflächig (ständig scrollen) und fluffig (graue Schrift, blasse Linien und Hinterlegungen), daß sie schon von der Darstellung her anstrengender ist. Aber es paßt halt zu dem (ebenfalls weniger stringenten) Aero-Design. Naja, gehört nicht hierher ;o)

Mal deine eine Mappe weglassend die beim Filtern von 20
Zeilen/zellen schon 20 sec braucht was nicht normal ist merkt
man bei kleinen Datenmengen nix von Volatile oder Unterschiede
zwischen Bereich.Verschieben und Index.

Tja, und genau da war der Hund begraben. In einer langen Aktion habe ich alle INDIREKTS durch INDEXe ersetzt, und siehe da: die 20 Zeilen sind in ca. 2 Sekunden durch! Ebenso erledigt hat sich damit das UserForm-Problem - es reagiert jetzt unmittelbar auf die Eingaben (wenn auch nicht auf Textbox Change, wenn der Bezug in einer Tabellenzelle ist, aber das hat sicher eine andere Ursache und ist auch nicht so wichtig)

ABER, was hindert dich daran, so wie ich es mache um was zu
kapieren, eine Tabelle, eine mappe anzulegen mit irren viel
Daten/Formeln?
Und dann mal zu schauen was so zeitlich passiert wenn du
anstatt Bereich.Verschieben Index benutzt oder anstatt
Matrix/Array-Formeln Hilfsspalten u.ä. Vergleiche…

Die Zeit, die hindert mich daran. Ich muß diese Woche noch so viel erledigen und wollte die Excel-Mappe einfach über den Berg bringen. Ich werde die generell sicher sehr hilfreichen Zeit-Tests aber am Wochenende nachholen und dann berichten :o)

Nochmals bestlichen Dank für die regelmäßige und zuverlässige Hilfe :o)

-Robb. (*hihi*)

#11

Pivot wenn möglich
Salu Thomas,

Wenn es um Auswertungen grösserer Datenmengen geht ist die
Pivot-Tabelle im Endeffekt wohl unschlagbar was die
Performance und Flexibilität anbelangt (und sie brauch nur ein
klein wenig Einarbeitungszeit :wink:).

Auch ein guter Tipp. Aber dazu muß man weniger mich überzeugen, als vielmehr jene, die sich derzeit noch weigern, mit Pivots zu arbeiten. Trotzdem gut zu wissen, daß man dafür auch mit der Performance argumentieren kann :o)

Herzliche Grüße vom
-Rob.