Bereich.Verschieben mit variablem Bezug in Excel
Schritt-für-Schritt-Anleitung
-
Daten vorbereiten: Stelle sicher, dass deine Datumswerte in Spalte A und die zugehörigen Aktienkurse in Spalte B stehen. Das Normierungsdatum wird in Zelle C1 eingegeben.
-
Formel eingeben: In der Zelle, in der du den Bezug zu den Aktienkursen herstellen möchtest, gib folgende Formel ein:
=BEREICH.VERSCHIEBEN(INDIREKT("A"&VERGLEICH(C1;A1:A100;0));0;1;1;1)
Diese Formel sucht das Datum in C1 in der Spalte A und gibt den entsprechenden Wert aus Spalte B zurück.
-
Formel anpassen: Du kannst die Zellreferenzen in der Formel anpassen, je nachdem, wo sich deine Daten befinden.
Häufige Fehler und Lösungen
-
Fehler 1: #NV
Dieser Fehler tritt auf, wenn das Datum in C1 nicht in der Datenreihe (Spalte A) gefunden werden kann. Überprüfe, ob das Datum korrekt eingegeben wurde.
-
Fehler 2: Falsche Zellreferenzen
Achte darauf, dass die Zellreferenzen in der Formel korrekt sind. Wenn deine Daten nicht in den Zeilen 1 bis 100 stehen, passe die Bereiche entsprechend an.
Alternative Methoden
Eine alternative Methode zur Verwendung von BEREICH.VERSCHIEBEN ist die Kombination von INDEX und VERGLEICH. Hier ist ein Beispiel:
=INDEX(B1:B100;VERGLEICH(C1;A1:A100;0))
Diese Formel gibt den Aktienkurs zurück, der dem in C1 eingegebenen Datum entspricht, und ist besonders nützlich, wenn du nur einen einzelnen Wert benötigst.
Praktische Beispiele
Angenommen, du hast folgende Daten:
| A (Datum) |
B (Aktienkurs) |
| 01.01.2003 |
100 |
| 15.01.2003 |
110 |
| 01.02.2003 |
105 |
Wenn du in C1 das Datum 15.01.2003 eingibst, gibt die Formel in der Zelle =BEREICH.VERSCHIEBEN(INDIREKT("A"&VERGLEICH(C1;A1:A100;0));0;1;1;1) den Wert 110 zurück.
Tipps für Profis
-
Dynamische Bereiche: Wenn deine Daten häufig aktualisiert werden, kannst du dynamische Bereiche mit der Funktion OFFSET erstellen, um die Formel automatisch anzupassen.
-
Fehlerbehandlung: Verwende die Funktion WENNFEHLER, um Fehler in deinen Formeln zu vermeiden. Zum Beispiel:
=WENNFEHLER(BEREICH.VERSCHIEBEN(...); "Datum nicht gefunden")
FAQ: Häufige Fragen
1. Wie funktioniert die Funktion VERGLEICH?
Die Funktion VERGLEICH sucht einen bestimmten Wert in einem Bereich und gibt die relative Position dieses Wertes zurück. Dies ist hilfreich, um herauszufinden, wo sich das Normierungsdatum in deiner Liste befindet.
2. Kann ich die Formel anpassen, um mehrere Werte zurückzugeben?
Ja, du kannst die Formel anpassen, um mehrere Werte zurückzugeben, indem du die Funktion INDEX in Kombination mit AGGREGAT oder FILTER verwendest, abhängig von deiner Excel-Version.
3. Welche Excel-Version benötige ich?
Die genannten Funktionen sind in Excel 2010 und späteren Versionen verfügbar. Stelle sicher, die richtige Version zu verwenden, um die Funktionen optimal nutzen zu können.