Autor: Peter Haserodt --- Aus Excel Standard - Gruppe: Funktionen
).
Schon immer wusste ich aus der Hilfe, dass Index in 2 Varianten vorliegt, nämlich als Matrix und als Bezug. Nur hat sich mir dies mit dem Bezug nie so richtig erschlossen. Die Beispiele habe ich mir nie richtig angeschaut und wenn, dann auch gleich für nutzlos verworfen.
Viel schlimmer aber noch, dass ich alles falsch interpretiert habe. Wobei einem die Excel Hilfe es hier einem auch wirklich leicht macht, alles mögliche zu verstehen, nur nicht den wirklichen Hintergrund des Unterschiedes. Irgendwie kam ich immer auf die Idee, dass das, was dort steht, irgendwie völlig verblödet ist und man die Unterscheidung zwischen Matrix und Bezugsversion nicht braucht. Und mit meiner Meinung stand ich wahrlich nicht alleine da.
Nun, was in der Hilfe steht, ist nicht unbedingt eine Hilfe, da einiges falsch ist. Trotzdem ist ein genaueres Lesen und Überdenken angebracht!
Tatsächlich ist Index als Bezug eine ArtEierlegende Wollmilchsau
Volatile Funktionen !Langsames Excel (1) - Volatile Funktionen wie Bereich.Verschieben oder Indirekt können mit ein wenig Überlegung weitgehend ersetzt werden. Die Augen hierzu hat mir neopa im Online-Excel Forum geöffnet. Er hatte mit Dingen herumgespielt, wie A1:Index(blabla) und dabei zurecht vermutet, dass man mit dieser Methodik wohl viele volatile Funktionen ersetzen könne.
Ich gebe zu, dass mir die Schreibweise A1:Index(Irgendwas) mehr als suspekt vorkam. Als Programmierer konnte ich mir erklären, dass es Objekt:Objekt letztendlich heißt. Aber warum kann ich mit Index dies erreichen? Warum lässt mich Excel A1:Index() schreiben, ohne zu meckern?
Die Lösung liegt ganz einfach daran, dass Index als Bezugsvariante tatsächlich an der geeigneten Stelle nicht den Wert sondern den Bezug, die Adresse liefert.
Und neopa hat mich dazu gebracht, mir die Index Funktion noch genauer anzusehen. Und siehe da - ich war verblüfft.
Weitere Artikel der Gruppe: Funktionen Aus Excel Standard
Nach oben
ToDo
Index als Bezug - die Superfunktion
Autor: Peter Haserodt - Erstellt: 2010-11-03 - Letzte Revision: 2015-03-05Index als Bezug - ein wahres Monster
Hinweis: Für diesen Artikel sollten Sie in Excel kein Frischling sein (Es reicht, wenn der Autor dies ist).
Schon immer wusste ich aus der Hilfe, dass Index in 2 Varianten vorliegt, nämlich als Matrix und als Bezug. Nur hat sich mir dies mit dem Bezug nie so richtig erschlossen. Die Beispiele habe ich mir nie richtig angeschaut und wenn, dann auch gleich für nutzlos verworfen.
Viel schlimmer aber noch, dass ich alles falsch interpretiert habe. Wobei einem die Excel Hilfe es hier einem auch wirklich leicht macht, alles mögliche zu verstehen, nur nicht den wirklichen Hintergrund des Unterschiedes. Irgendwie kam ich immer auf die Idee, dass das, was dort steht, irgendwie völlig verblödet ist und man die Unterscheidung zwischen Matrix und Bezugsversion nicht braucht. Und mit meiner Meinung stand ich wahrlich nicht alleine da.
Nun, was in der Hilfe steht, ist nicht unbedingt eine Hilfe, da einiges falsch ist. Trotzdem ist ein genaueres Lesen und Überdenken angebracht!
Tatsächlich ist Index als Bezug eine ArtEierlegende Wollmilchsau
Volatile Funktionen !Langsames Excel (1) - Volatile Funktionen wie Bereich.Verschieben oder Indirekt können mit ein wenig Überlegung weitgehend ersetzt werden. Die Augen hierzu hat mir neopa im Online-Excel Forum geöffnet. Er hatte mit Dingen herumgespielt, wie A1:Index(blabla) und dabei zurecht vermutet, dass man mit dieser Methodik wohl viele volatile Funktionen ersetzen könne.
Ich gebe zu, dass mir die Schreibweise A1:Index(Irgendwas) mehr als suspekt vorkam. Als Programmierer konnte ich mir erklären, dass es Objekt:Objekt letztendlich heißt. Aber warum kann ich mit Index dies erreichen? Warum lässt mich Excel A1:Index() schreiben, ohne zu meckern?
Die Lösung liegt ganz einfach daran, dass Index als Bezugsvariante tatsächlich an der geeigneten Stelle nicht den Wert sondern den Bezug, die Adresse liefert.
Und neopa hat mich dazu gebracht, mir die Index Funktion noch genauer anzusehen. Und siehe da - ich war verblüfft.
Ein erstes Beispiel - ein erster Beweis
Und nun ganz langsam ran an den Feind!
Beginnen wir mit F4. Hier ist es einfach. Über unsere Indexfunktion erhalten wir den 3. Wert (D2) aus unserer Matrix aus B2:B5 - und dort unsere schöne 15. Einfach zu verstehen, immer wieder benutzt. Aber was passiert in F2? Hier haben wir exakt die gleiche Index Formel geschrieben aber es hat nichts mehr mir der 15 zu tun.
Seit Excel 2003 (glaube ich) gibt es in der Formelüberwachung die Formelauswertung.
Ich habe im gelben Bereich die ersten 2 Schritte abgebildet.
Jetzt wird es aber interessant! Es wird nun nicht der 3. Wert unserer Matrix ermittelt, sondern Excel erkennt, dass ein Bezug benötigt wird und gibt die dritte Zelle als Adresse zurück.
Dies passiert aber ausschließlich dadurch, dass die Indexfunktion in zwei Varianten vorliegt. Wer sich ein wenig mit VBA auskennt, weiß, dass Funkionen Typen zurückgeben. Die Index Funktion gibt mir anscheinend einen Variant, der entweder den Inhalt des angeforderten Bereiches (wenn ich denn einen Bereich anspreche) oder den angeforderten Bereich als Objekt - als Bezug zurück.
Eigentlich weiß ich nicht ob ich lachen oder weinen soll. Dadurch, dass die selbe Funktion so zwei völlig verschiedene Aufgaben hat, bezweifle ich sehr stark, dass auch nur die wenigsten die zweite Aufgabe kennen und einsetzen können.
Es ist schon ein wenig absurd, dass auf Grund der Anwendung Excel selbst entscheidet (entscheiden muss), welchen Typ Index zurückgeben soll.
Viele viele Excelauswertungen würden ganz anders aussehen, wenn Index auch sprachlich aufgeteilt wäre, z.B. in IndexWert und IndexBezug.
Ich denke, dass jeder, der öfters mit Matrizen arbeitet, hier die ungeahnten Möglichkeiten schon entdeckt.
| A | B | C | D | E | F | G | H | I | J | |
| 1 | ||||||||||
| 2 | 5 | 3 | 30 | Formel auswerten | ||||||
| 3 | 10 | Start | Summe(B2:Index(B2:B5;D2)) | |||||||
| 4 | 15 | 15 | 1. | Summe($B$2:Index($B$2:$B$5;3)) | ||||||
| 5 | 20 | 2. | Summe($B$2:$B$4) | |||||||
| 6 |
| Formeln der Tabelle | ||||||
|
Und nun ganz langsam ran an den Feind!
Beginnen wir mit F4. Hier ist es einfach. Über unsere Indexfunktion erhalten wir den 3. Wert (D2) aus unserer Matrix aus B2:B5 - und dort unsere schöne 15. Einfach zu verstehen, immer wieder benutzt. Aber was passiert in F2? Hier haben wir exakt die gleiche Index Formel geschrieben aber es hat nichts mehr mir der 15 zu tun.
Seit Excel 2003 (glaube ich) gibt es in der Formelüberwachung die Formelauswertung.
Ich habe im gelben Bereich die ersten 2 Schritte abgebildet.
Formelauswerten
Die Zelle F2 markieren und dann:
In Excel 2003: Menü Extras -> Formelüberwachung -> Formelauswertung
Ab Excel 2007: Formeln und dort Formelauswertung
und dann jeweils auf Auswerten.
Im ersten Schritt wird D2 ausgewertet - und der Inhalt der Zelle eingesetzt - die 3. Ganz nebenbei werden die Bezüge intern absolutiert mit den $.Die Zelle F2 markieren und dann:
In Excel 2003: Menü Extras -> Formelüberwachung -> Formelauswertung
Ab Excel 2007: Formeln und dort Formelauswertung
und dann jeweils auf Auswerten.
Jetzt wird es aber interessant! Es wird nun nicht der 3. Wert unserer Matrix ermittelt, sondern Excel erkennt, dass ein Bezug benötigt wird und gibt die dritte Zelle als Adresse zurück.
Dies passiert aber ausschließlich dadurch, dass die Indexfunktion in zwei Varianten vorliegt. Wer sich ein wenig mit VBA auskennt, weiß, dass Funkionen Typen zurückgeben. Die Index Funktion gibt mir anscheinend einen Variant, der entweder den Inhalt des angeforderten Bereiches (wenn ich denn einen Bereich anspreche) oder den angeforderten Bereich als Objekt - als Bezug zurück.
Eigentlich weiß ich nicht ob ich lachen oder weinen soll. Dadurch, dass die selbe Funktion so zwei völlig verschiedene Aufgaben hat, bezweifle ich sehr stark, dass auch nur die wenigsten die zweite Aufgabe kennen und einsetzen können.
Es ist schon ein wenig absurd, dass auf Grund der Anwendung Excel selbst entscheidet (entscheiden muss), welchen Typ Index zurückgeben soll.
Viele viele Excelauswertungen würden ganz anders aussehen, wenn Index auch sprachlich aufgeteilt wäre, z.B. in IndexWert und IndexBezug.
Ich denke, dass jeder, der öfters mit Matrizen arbeitet, hier die ungeahnten Möglichkeiten schon entdeckt.
Erste Überlegungen und Rückschläge und Fortschritte und Hinweise
In vielen Fällen benötigt man in Matrixberechnungen eine dynamische Zeilenzahl um eine entsprechende Anzahl von Elementen zu erhalten.
Ein Beispiel sei die Quersummenberechnung aus unserem Matrixtutorial Matrix Station (7) (Endstation) - Oldie but Goldie
Ich stelle hier gleich eine Index Version mit hinein - Aber Achtung, diese ist mit Problemen behaftet und so nicht anzuwenden!
Beide Formeln bringen das selbe Ergebnis.
Bei beiden interessiert uns nur der Teil mit Zeile(...)))
Ziel ist es, die Zeilen 1 bis 5 zu erhalten, da ich 1;2;3;4;5 als Elemente benötige (in unserem Beispiel mit der Länge 5)
Wenn Sie jetzt fragen warum man denn überhaupt die Formel in B1 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.
Aber sehen Sie den brutalen Fehler in unserer Formel mit Index???
Ein technischer Fehler, der schnell passieren kann. Fügen Sie eine Zeile über Zeile 1 ein und schon haben wir den Salat. Die Startadresse A1 wird geändert zu A2. D.h. wir benötigen die komplette Adresse für den Zeilen Ausdruck so, dass er immer erhalten bleibt:
Wie Sie sehen, habe ich auch den Startbezug von Zeile jetzt über Index ermittelt.
Auf diese Weise erhalte ich meine 1. (Vielleicht gibt es auch noch andere Möglichkeiten ?)
Ist Ihnen aufgefallen, dass ich C:C benutzt habe? Warum? Ganz einfach, meine Zelle in welcher die Formel steht, ist in der Spalte C. Also benutze ich auch die Spalte C, damit ich keine Probleme bekomme, wenn Spalten gelöscht oder eingefügt werden.
Und wenn Ihre Formeln dadurch länger werden, dann ist das vollkommen Wurst! Die Länge einer Formel hat nichts mit ihrer Güte und ihrer Geschwindigkeit zu tun!
In vielen Fällen benötigt man in Matrixberechnungen eine dynamische Zeilenzahl um eine entsprechende Anzahl von Elementen zu erhalten.
Ein Beispiel sei die Quersummenberechnung aus unserem Matrixtutorial Matrix Station (7) (Endstation) - Oldie but Goldie
Ich stelle hier gleich eine Index Version mit hinein - Aber Achtung, diese ist mit Problemen behaftet und so nicht anzuwenden!
| A | B | C | |
| 1 | 22345 | 16 | 16 |
| Formeln der Tabelle | ||||||
| ||||||
| Enthält Matrixformel: Umrandende { } nicht miteingeben, sondern Formel mit STRG+SHIFT+RETURN abschließen! | ||||||
| Matrix verstehen |
Beide Formeln bringen das selbe Ergebnis.
Bei beiden interessiert uns nur der Teil mit Zeile(...)))
Ziel ist es, die Zeilen 1 bis 5 zu erhalten, da ich 1;2;3;4;5 als Elemente benötige (in unserem Beispiel mit der Länge 5)
Wenn Sie jetzt fragen warum man denn überhaupt die Formel in B1 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.
Aber sehen Sie den brutalen Fehler in unserer Formel mit Index???
Ein technischer Fehler, der schnell passieren kann. Fügen Sie eine Zeile über Zeile 1 ein und schon haben wir den Salat. Die Startadresse A1 wird geändert zu A2. D.h. wir benötigen die komplette Adresse für den Zeilen Ausdruck so, dass er immer erhalten bleibt:
| A | B | C | |
| 1 | 22345 | 16 |
| Formeln der Tabelle | ||||
| ||||
| Enthält Matrixformel: Umrandende { } nicht miteingeben, sondern Formel mit STRG+SHIFT+RETURN abschließen! | ||||
| Matrix verstehen |
Wie Sie sehen, habe ich auch den Startbezug von Zeile jetzt über Index ermittelt.
Auf diese Weise erhalte ich meine 1. (Vielleicht gibt es auch noch andere Möglichkeiten ?)
Ist Ihnen aufgefallen, dass ich C:C benutzt habe? Warum? Ganz einfach, meine Zelle in welcher die Formel steht, ist in der Spalte C. Also benutze ich auch die Spalte C, damit ich keine Probleme bekomme, wenn Spalten gelöscht oder eingefügt werden.
Merksatz:
Und dieser gilt nicht nur hier! Achtung bei Ersatz von Indirekt Konstrukten mit Index, dass Bezüge so ermittelt werden, dass Sie beim Einfügen, Löschen von Zeilen/Spalten, beim Formel ziehen, stabil bleiben!
Und noch ein kleiner Satz am Rande:Und dieser gilt nicht nur hier! Achtung bei Ersatz von Indirekt Konstrukten mit Index, dass Bezüge so ermittelt werden, dass Sie beim Einfügen, Löschen von Zeilen/Spalten, beim Formel ziehen, stabil bleiben!
Und wenn Ihre Formeln dadurch länger werden, dann ist das vollkommen Wurst! Die Länge einer Formel hat nichts mit ihrer Güte und ihrer Geschwindigkeit zu tun!
Darf es ein wenig mehr sein?
Die Aufgabenstellung ist klar. Je nachdem was in G2 steht, möchte ich die entsprechende Zeile des grünen Bereiches summiert haben. Im Beispiel sollte jetzt 555 auftauchen.
Sie werden sicherlich einige Möglichkeiten haben, diese Aufgabe zu erfüllen. Ein Beispiel wäre z.B. eine Formel mit Bereich.Verschieben:
=SUMME(BEREICH.VERSCHIEBEN(B2:D2;G2-1;0))
Tatsächlich funktioniert dies wie gewünscht aber ist halt Bereich.Verschieben
Unsere Index Funktion bietet aber auch hier eine Möglichkeit an:
=SUMME(INDEX(B2:D4;2;0))
Oder für die 3.Spalte oder... hier die Tabelle:
In der Regel haben wir Index mit einem Bereich einer Zeile und einer Spalte, wobei Spalte optional ist, wenn der Bezug nur einspaltig (oder einzeilig) ist.
Wir haben aber die Option, Zeile oder Spalte auf 0 zu setzen und dann erhalten wir die gesamte Spalte oder Zeile der jeweiligen Position.
Hatten Sie dies gewusst? - Wenn ja, Kompliment!
(Tatsächlich steht es in der OH aber z.B. in der E2003 Hilfe steht ein falsches Beispiel dazu und dies wird dadurch unerklärlich)
Wir werden uns mit Bereich.Verschieben und Index noch genauer beschäftigen.
Aber zuerst:
| A | B | C | D | E | F | G | H | |
| 1 | ||||||||
| 2 | 4 | 40 | 400 | Zeile | 2 | |||
| 3 | 5 | 50 | 500 | Summe | ||||
| 4 | 6 | 60 | 600 | |||||
| 5 |
Die Aufgabenstellung ist klar. Je nachdem was in G2 steht, möchte ich die entsprechende Zeile des grünen Bereiches summiert haben. Im Beispiel sollte jetzt 555 auftauchen.
Sie werden sicherlich einige Möglichkeiten haben, diese Aufgabe zu erfüllen. Ein Beispiel wäre z.B. eine Formel mit Bereich.Verschieben:
=SUMME(BEREICH.VERSCHIEBEN(B2:D2;G2-1;0))
Tatsächlich funktioniert dies wie gewünscht aber ist halt Bereich.Verschieben
Unsere Index Funktion bietet aber auch hier eine Möglichkeit an:
=SUMME(INDEX(B2:D4;2;0))
Oder für die 3.Spalte oder... hier die Tabelle:
| A | B | C | D | E | F | G | |
| 1 | |||||||
| 2 | 4 | 40 | 400 | Zeile | 2 | ||
| 3 | 5 | 50 | 500 | Summe | 555 | ||
| 4 | 6 | 60 | 600 | ||||
| 5 | Spalte | 3 | |||||
| 6 | Summe | 1500 |
| Formeln der Tabelle | ||||||
|
Wir haben aber die Option, Zeile oder Spalte auf 0 zu setzen und dann erhalten wir die gesamte Spalte oder Zeile der jeweiligen Position.
Hatten Sie dies gewusst? - Wenn ja, Kompliment!
(Tatsächlich steht es in der OH aber z.B. in der E2003 Hilfe steht ein falsches Beispiel dazu und dies wird dadurch unerklärlich)
Wir werden uns mit Bereich.Verschieben und Index noch genauer beschäftigen.
Aber zuerst:
Was ist das mit dem optionalen Bereich ???
INDEX(Bezug;Zeile;Spalte;Bereich)
So steht es in der OH für Index als Bezug geschrieben.
Und - ich gestehe es wiederholt - dass ich mir den ganzen Senf nicht richtig durchgelesen habe, weil ich einfach den Nutzen nicht gesehen habe.
Da sich dies nun geändert hat, interessierte mich dann auch das vierte Argument. Was soll Bereich bedeuten in einem Bezug?
Als VBAler sollte es einem sofort wie ein Schmetterling über den Nasenflügel streichen und man sollte sich an Areas erinnern. Aber auch als reiner Excler ist es doch klar. Ich habe ja nicht nur zusammenhängende Bezüge. Und wenn Bezüge nicht zusammenhängend sind, dann bestehen sie aus mehreren Bereichen!
Dies ist nun ein sehr einfaches Beispiel, welches man auch anders lösen kann aber stellen Sie sich vor die Bereiche liegen auf getrennten Tabellenblättern oder was weiß ich.
Die Aufgabe ist klar, ich will die Summe des Monates haben, den ich in C1 angebe.
Und die Lösung sollte fast genauso klar sein, nach dem ganzen rumgehopse von mir:
=SUMME(INDEX((B6:B8;E6:E8;H6:H8);;;C1))
Beachten Sie bitte die Klammern um die Bereiche! Diese sind absolut notwendig, wenn Sie mehrere Bereiche im Index als Bezug angeben!
Das Argument Bereich ist also ganz einfach der X. Bereich in meinem Bezug. Per Voreinstellung ist der Wert 1, wenn ich die Angabe weglasse, wird der 1.Bereich genommen. Natürlich können Sie hier auch mit benannten Bereichen arbeiten. Angenommen der gelbe Bereich heißt Januar usw... dan lautet die Formel: =SUMME(INDEX((Januar;Februar;März);;;C1))
Was für Möglichkeiten sich hier eröffnen, überlasse ich Ihnen.
Und ein Geständnis: Völlig irritiert wurde ich durch die Eingabehinweise von Excel. Sie wissen, das was Sie angezeigt bekommen während der Eingabe: =Index(Bezug;Zeile;[Spalte];[Bereich]) Dies bedeutet normalerweise, dass Spalte und Bereich optional anzugeben sind aber Bezug und Zeile verpflichtend. Tatsächlich ist dies aber falsch - wie man sieht. Denn in obiger Formel habe ich keine Zeilen und Spaltenangaben gemacht.
Selbstverständlich können Sie dieses mit den vorhergehenden Beschreibungen kombinieren.
Dies soll aber nun Ihr Vergnügen sein.
INDEX(Bezug;Zeile;Spalte;Bereich)
So steht es in der OH für Index als Bezug geschrieben.
Und - ich gestehe es wiederholt - dass ich mir den ganzen Senf nicht richtig durchgelesen habe, weil ich einfach den Nutzen nicht gesehen habe.
Da sich dies nun geändert hat, interessierte mich dann auch das vierte Argument. Was soll Bereich bedeuten in einem Bezug?
Als VBAler sollte es einem sofort wie ein Schmetterling über den Nasenflügel streichen und man sollte sich an Areas erinnern. Aber auch als reiner Excler ist es doch klar. Ich habe ja nicht nur zusammenhängende Bezüge. Und wenn Bezüge nicht zusammenhängend sind, dann bestehen sie aus mehreren Bereichen!
| A | B | C | D | E | F | G | H | |
| 1 | Monatsnummer | 2 | ||||||
| 2 | Summe | |||||||
| 3 | ||||||||
| 4 | ||||||||
| 5 | Jan | Feb | März | |||||
| 6 | 1 | 4 | 7 | |||||
| 7 | 2 | 5 | 8 | |||||
| 8 | 3 | 6 | 9 |
Dies ist nun ein sehr einfaches Beispiel, welches man auch anders lösen kann aber stellen Sie sich vor die Bereiche liegen auf getrennten Tabellenblättern oder was weiß ich.
Die Aufgabe ist klar, ich will die Summe des Monates haben, den ich in C1 angebe.
Und die Lösung sollte fast genauso klar sein, nach dem ganzen rumgehopse von mir:
=SUMME(INDEX((B6:B8;E6:E8;H6:H8);;;C1))
Beachten Sie bitte die Klammern um die Bereiche! Diese sind absolut notwendig, wenn Sie mehrere Bereiche im Index als Bezug angeben!
Das Argument Bereich ist also ganz einfach der X. Bereich in meinem Bezug. Per Voreinstellung ist der Wert 1, wenn ich die Angabe weglasse, wird der 1.Bereich genommen. Natürlich können Sie hier auch mit benannten Bereichen arbeiten. Angenommen der gelbe Bereich heißt Januar usw... dan lautet die Formel: =SUMME(INDEX((Januar;Februar;März);;;C1))
Was für Möglichkeiten sich hier eröffnen, überlasse ich Ihnen.
Und ein Geständnis: Völlig irritiert wurde ich durch die Eingabehinweise von Excel. Sie wissen, das was Sie angezeigt bekommen während der Eingabe: =Index(Bezug;Zeile;[Spalte];[Bereich]) Dies bedeutet normalerweise, dass Spalte und Bereich optional anzugeben sind aber Bezug und Zeile verpflichtend. Tatsächlich ist dies aber falsch - wie man sieht. Denn in obiger Formel habe ich keine Zeilen und Spaltenangaben gemacht.
Selbstverständlich können Sie dieses mit den vorhergehenden Beschreibungen kombinieren.
Dies soll aber nun Ihr Vergnügen sein.
Bereich.Verschieben - weg mit wenn es geht
Ich bin ein großer Freund von Bereich.Verschieben. Oder soll ich sagen ich war ein großer Freund.
In Zukunft werde ich sicherlich darüber nachdenken, ob ich die jeweilige Formel nicht besser über Index Varianten bilde.
Wir sehen sicherlich schon, dass in vielen einfachen Bereich.Verschieben Situationen die Index Funktion einfache Alternativen bietet. Wie sieht es aber mit Situationen aus, in welchen wir nicht nur einen Bereich verschieben, sondern auch dessen Größe verändern?
Ein einfaches Beispiel :
Ein Beispiel, in welchem man Bereich.Verschieben schön anwenden kann. Und in F8 sehen wir die Formel dazu.
Aber sagen Sie mir: Was spricht gegen die Formel in F9? Außer dass man vielleicht ein ganz klein wenig mehr nachdenken muss.
Ich bin mir ziemlich sicher, dass man jede Bereich.Verschieben Lösung in Index umschreiben kann. Aber Sie können gerne im
Online-Excel Forum
ein Gegenbeispiel bringen.
Vielleicht konnte ich Sie ja auch für den Index Bezug begeistern.
Und denken Sie dran: Ein kleines Feedback ist immer nett Feedback
Ich bin ein großer Freund von Bereich.Verschieben. Oder soll ich sagen ich war ein großer Freund.
In Zukunft werde ich sicherlich darüber nachdenken, ob ich die jeweilige Formel nicht besser über Index Varianten bilde.
Wir sehen sicherlich schon, dass in vielen einfachen Bereich.Verschieben Situationen die Index Funktion einfache Alternativen bietet. Wie sieht es aber mit Situationen aus, in welchen wir nicht nur einen Bereich verschieben, sondern auch dessen Größe verändern?
Ein einfaches Beispiel
:| A | B | C | D | E | F | |
| 1 | ||||||
| 2 | Jahr | Januar | Februar | März | April | |
| 3 | 2000 | 10 | 100 | 1.000 | 10.000 | |
| 4 | 2001 | 20 | 200 | 2.000 | 20.000 | |
| 5 | 2002 | 30 | 300 | 3.000 | 10.000 | |
| 6 | 2003 | 40 | 400 | 4.000 | 20.000 | |
| 7 | ||||||
| 8 | Start Jahr | 2 | 35500 | |||
| 9 | Anzahl Jahre | 2 | 35500 | |||
| 10 | Startmonat | 2 | ||||
| 11 | Anzahlmonate | 3 |
| Formeln der Tabelle | ||||||
|
Ein Beispiel, in welchem man Bereich.Verschieben schön anwenden kann. Und in F8 sehen wir die Formel dazu.
Aber sagen Sie mir: Was spricht gegen die Formel in F9? Außer dass man vielleicht ein ganz klein wenig mehr nachdenken muss.
Ich bin mir ziemlich sicher, dass man jede Bereich.Verschieben Lösung in Index umschreiben kann. Aber Sie können gerne im
Online-Excel Forum
ein Gegenbeispiel bringen.
Im März 2015 erreichte mich eine Anmerkung von Edgar alias BoskoBiati (vielen Dank!) der einen Einwurf zu diesem Beispiel hatte:
Er bemerkte, dass BEREICH.VERSCHIEBEN nicht so "fehleranfällig sei".
Ändert man in unserem obigen Beispiel die Werte für Start Jahr und Anzahl Jahre wie folgt:
Tabelle1
arbeitet BEREICH.VERSCHIEBEN noch scheinbar "korrket" während Index auf einen Bezugfehler läuft.
Aber was Edgar als positiv für BEREICH.VERSCHIEBEN bewertet, ist genau das Gegenteil.
Es ist ein weiterer Pluspunkt für Index.
Index wirft hier einen Bezugsfehler aus und zeigt damit an, dass hier etwas nicht stimmt.
Während BEREICH.VERSCHIEBEN natürlich stur nach unten verschiebt, ist ja seine Aufgabe.
Ändern wir das Beispiel noch ein wenig:
Tabelle1
Und schon sieht man, was für ein Murks BEREICH.VERSCHIEBEN hier anrichtet, während Index uns auf unseren Fehler aufmerksam macht.
Ein weiteres hoch auf Index!
So, dass soll es hier gewesen sein.Er bemerkte, dass BEREICH.VERSCHIEBEN nicht so "fehleranfällig sei".
Ändert man in unserem obigen Beispiel die Werte für Start Jahr und Anzahl Jahre wie folgt:
Tabelle1
| A | B | C | D | E | F | |
| 1 | ||||||
| 2 | Jahr | Januar | Februar | März | April | |
| 3 | 2000 | 10 | 100 | 1.000 | 10.000 | |
| 4 | 2001 | 20 | 200 | 2.000 | 20.000 | |
| 5 | 2002 | 30 | 300 | 3.000 | 10.000 | |
| 6 | 2003 | 40 | 400 | 4.000 | 20.000 | |
| 7 | ||||||
| 8 | Start Jahr | 3 | 37700 | |||
| 9 | Anzahl Jahre | 3 | #BEZUG! | |||
| 10 | Startmonat | 2 | ||||
| 11 | Anzahlmonate | 3 |
| Formeln der Tabelle | ||||||
|
arbeitet BEREICH.VERSCHIEBEN noch scheinbar "korrket" während Index auf einen Bezugfehler läuft.
Aber was Edgar als positiv für BEREICH.VERSCHIEBEN bewertet, ist genau das Gegenteil.
Es ist ein weiterer Pluspunkt für Index.
Index wirft hier einen Bezugsfehler aus und zeigt damit an, dass hier etwas nicht stimmt.
Während BEREICH.VERSCHIEBEN natürlich stur nach unten verschiebt, ist ja seine Aufgabe.
Ändern wir das Beispiel noch ein wenig:
Tabelle1
| B | C | D | E | F | |
| 2 | Jahr | Januar | Februar | März | April |
| 3 | 2000 | 10 | 100 | 1.000 | 10.000 |
| 4 | 2001 | 20 | 200 | 2.000 | 20.000 |
| 5 | 2002 | 30 | 300 | 3.000 | 10.000 |
| 6 | 2003 | 40 | 400 | 4.000 | 20.000 |
| 7 | Summen | 100 | 1000 | 10000 | 60000 |
| 8 | Start Jahr | 3 | 108700 | ||
| 9 | Anzahl Jahre | 3 | #BEZUG! | ||
| 10 | Startmonat | 2 | |||
| 11 | Anzahlmonate | 3 |
| Formeln der Tabelle | ||||||||||||||
|
Und schon sieht man, was für ein Murks BEREICH.VERSCHIEBEN hier anrichtet, während Index uns auf unseren Fehler aufmerksam macht.
Ein weiteres hoch auf Index!
Vielleicht konnte ich Sie ja auch für den Index Bezug begeistern.
Und denken Sie dran: Ein kleines Feedback ist immer nett Feedback
Weitere Artikel der Gruppe: Funktionen Aus Excel Standard
Nach oben
Google Werbung
