Kennen Sie das Problem? Sie haben in Excel eine Liste von Werten, die sich immer wieder wiederholen sollen. Sobald das Ende der Liste erreicht ist, möchten Sie, dass automatisch wieder beim ersten Wert begonnen wird. Das ist besonders nützlich, wenn Sie beispielsweise eine Abfolge von Aufgaben, Phasen oder Produktcodes haben, die sich in einer Schleife wiederholen sollen. In diesem Artikel zeigen wir Ihnen verschiedene Methoden, wie Sie dieses Ziel in Excel erreichen – einfach, verständlich und ohne komplizierte Formeln!
Warum ist das überhaupt nützlich?
Bevor wir ins Detail gehen, kurz zur Erklärung, warum diese Funktion so praktisch ist. Stellen Sie sich vor, Sie planen ein Projekt mit mehreren Phasen. Jede Phase hat eine Nummer, und Sie möchten automatisch die nächste Phase in einer Zelle anzeigen lassen. Wenn Sie am Ende der Liste angekommen sind, soll die nächste Phase wieder die erste sein. Oder vielleicht haben Sie eine Liste mit Prioritäten (hoch, mittel, niedrig) und wollen, dass diese sich immer wiederholen. Anstatt die Liste manuell zu kopieren und einzufügen, können Sie mit den hier vorgestellten Methoden die Daten automatisch wiederholen lassen. Das spart Zeit, reduziert Fehler und macht Ihre Excel-Tabellen dynamischer.
Methode 1: Die INDEX- und MOD-Funktionen
Die erste und wahrscheinlich eleganteste Lösung verwendet die Kombination aus den INDEX– und MOD-Funktionen. Diese Funktionen sind in Excel weit verbreitet und bieten eine flexible Möglichkeit, auf bestimmte Elemente in einer Liste zuzugreifen und den Restwert einer Division zu berechnen.
Die INDEX-Funktion: Diese Funktion gibt den Wert in einem Bereich zurück, der durch die Zeilen- und Spaltennummer angegeben wird. Im Grunde genommen sagt sie: „Gib mir den Wert aus dieser Liste an der Position X.”
Die MOD-Funktion: Diese Funktion gibt den Restwert einer Division zurück. Beispiel: MOD(10, 3) ergibt 1, da 10 geteilt durch 3 den Rest 1 hat. Die MOD-Funktion ist der Schlüssel, um den Index für die INDEX-Funktion zyklisch zu halten.
Wie funktioniert die Kombination?
- Definieren Sie Ihre Liste mit den Werten, die sich wiederholen sollen. Nehmen wir an, diese Liste befindet sich im Bereich A1:A3 und enthält die Werte „Hoch”, „Mittel”, „Niedrig”.
- In der Zelle, in der Sie den wiederholenden Wert anzeigen lassen möchten (z.B. B1), geben Sie folgende Formel ein:
=INDEX(A1:A3, MOD(ZEILE(A1)-1, ANZAHL2(A1:A3))+1) - Erklären wir die Formel im Detail:
A1:A3: Dies ist der Bereich, der Ihre Liste mit den sich wiederholenden Werten enthält.ZEILE(A1): Diese Funktion gibt die Zeilennummer der Zelle A1 zurück, also 1. Wir subtrahieren 1, um beim ersten Wert der Liste zu beginnen.ANZAHL2(A1:A3): Diese Funktion zählt die Anzahl der nicht leeren Zellen im Bereich A1:A3, also 3 (die Länge unserer Liste).MOD(ZEILE(A1)-1, ANZAHL2(A1:A3)): Hier kommt die MOD-Funktion ins Spiel. Sie berechnet den Restwert der Division der aktuellen Zeilennummer (minus 1) durch die Länge der Liste. Wenn wir die Formel nach unten ziehen, ändert sich die Zeilennummer. Wenn wir in Zeile 4 sind, wäre die Formel:MOD(4-1, 3), was 0 ergibt. In Zeile 5 wäre esMOD(5-1, 3), was 1 ergibt, und so weiter. Die MOD-Funktion sorgt dafür, dass der Wert immer innerhalb des Bereichs von 0 bis (Länge der Liste – 1) liegt.MOD(ZEILE(A1)-1, ANZAHL2(A1:A3))+1: Wir addieren 1 zum Ergebnis der MOD-Funktion, da die INDEX-Funktion mit 1 beginnt und nicht mit 0.INDEX(A1:A3, ...): Die INDEX-Funktion verwendet das Ergebnis der MOD-Funktion (plus 1) als Index, um den entsprechenden Wert aus der Liste A1:A3 zurückzugeben.
- Ziehen Sie die Formel in Zelle B1 einfach nach unten, um die Werte automatisch zu wiederholen.
Diese Methode ist sehr flexibel, da Sie die Länge der Liste einfach ändern können, ohne die Formel anpassen zu müssen. Die ANZAHL2-Funktion sorgt dafür, dass die Formel immer korrekt funktioniert.
Methode 2: Die WENN- und REST-Funktionen (Alternative für ältere Excel-Versionen)
Eine alternative Methode, die auch in älteren Excel-Versionen funktioniert, verwendet die Kombination aus WENN– und REST-Funktionen. Diese Methode ist etwas weniger elegant als die erste, aber sie kann dennoch sehr nützlich sein.
Die WENN-Funktion: Diese Funktion führt eine logische Prüfung durch und gibt einen Wert zurück, wenn die Prüfung WAHR ist, und einen anderen Wert, wenn die Prüfung FALSCH ist.
Die REST-Funktion: Diese Funktion ist identisch zur MOD-Funktion.
Wie funktioniert die Kombination?
- Definieren Sie Ihre Liste mit den Werten, die sich wiederholen sollen. Nehmen wir an, diese Liste befindet sich im Bereich A1:A3 und enthält die Werte „A”, „B”, „C”.
- In der Zelle, in der Sie den wiederholenden Wert anzeigen lassen möchten (z.B. B1), geben Sie folgende Formel ein (für den ersten Wert):
=INDEX(A:A,REST(ZEILE()-1,3)+1) - Erklären wir die Formel im Detail:
A:A: Dies ist die gesamte Spalte A, die unsere Liste enthält.ZEILE(): Diese Funktion gibt die Zeilennummer der Zelle zurück, in der die Formel steht.ZEILE()-1: Wir subtrahieren 1, um beim ersten Wert der Liste zu beginnen, wenn die Formel in Zeile 1 steht.REST(ZEILE()-1,3): Hier kommt die REST-Funktion ins Spiel. Sie berechnet den Restwert der Division der aktuellen Zeilennummer (minus 1) durch 3 (die Länge unserer Liste).REST(ZEILE()-1,3)+1: Wir addieren 1 zum Ergebnis der REST-Funktion, da die INDEX-Funktion mit 1 beginnt und nicht mit 0.INDEX(A:A, ...): Die INDEX-Funktion verwendet das Ergebnis der REST-Funktion (plus 1) als Index, um den entsprechenden Wert aus der Liste A:A zurückzugeben.
- Ziehen Sie die Formel in Zelle B1 einfach nach unten, um die Werte automatisch zu wiederholen.
Methode 3: Benutzerdefinierte Funktion (VBA)
Für fortgeschrittene Excel-Benutzer kann auch eine benutzerdefinierte Funktion (UDF) in VBA (Visual Basic for Applications) eine elegante Lösung sein. Diese Methode erfordert etwas mehr Programmierkenntnisse, bietet aber maximale Flexibilität.
- Öffnen Sie den VBA-Editor in Excel (ALT + F11).
- Fügen Sie ein neues Modul ein (Einfügen -> Modul).
- Kopieren Sie den folgenden Code in das Modul:
Function WiederholeWert(Bereich As Range, Index As Long) As Variant Dim ListenLaenge As Long ListenLaenge = Bereich.Cells.Count WiederholeWert = Bereich.Cells((Index - 1) Mod ListenLaenge + 1).Value End Function - Schließen Sie den VBA-Editor.
- In Excel können Sie die Funktion nun wie folgt verwenden:
=WiederholeWert(A1:A3, ZEILE(A1)) - Erklären wir die Funktion im Detail:
Function WiederholeWert(Bereich As Range, Index As Long) As Variant: Dies definiert die Funktion mit dem Namen „WiederholeWert”, die einen Bereich (Ihre Liste) und einen Index (die Zeilennummer) als Eingabe entgegennimmt und einen Wert vom Typ Variant zurückgibt (da die Liste verschiedene Datentypen enthalten kann).Dim ListenLaenge As Long: Dies deklariert eine Variable vom Typ Long, um die Länge der Liste zu speichern.ListenLaenge = Bereich.Cells.Count: Dies berechnet die Anzahl der Zellen im angegebenen Bereich und speichert sie in der Variablen ListenLaenge.WiederholeWert = Bereich.Cells((Index - 1) Mod ListenLaenge + 1).Value: Dies ist der Kern der Funktion. Es verwendet die MOD-Funktion, um den Index zyklisch zu machen und den entsprechenden Wert aus dem Bereich zurückzugeben.
- Ziehen Sie die Formel nach unten, um die Werte automatisch zu wiederholen.
Der Vorteil dieser Methode ist, dass Sie die Funktion in verschiedenen Tabellenblättern und Arbeitsmappen verwenden können. Außerdem ist der Code relativ einfach und leicht zu verstehen.
Fazit
Es gibt verschiedene Möglichkeiten, in Excel automatisch beim ersten Wert zu starten, wenn eine Liste endet. Die Kombination aus INDEX– und MOD-Funktionen ist eine elegante und flexible Lösung. Die WENN– und REST-Funktionen bieten eine Alternative für ältere Excel-Versionen. Und für fortgeschrittene Benutzer ist eine benutzerdefinierte Funktion in VBA eine Option mit maximaler Flexibilität. Wählen Sie die Methode, die am besten zu Ihren Bedürfnissen und Kenntnissen passt, und machen Sie Ihre Excel-Tabellen dynamischer und effizienter!
Egal für welche Methode Sie sich entscheiden, denken Sie daran, die Formel korrekt anzupassen, um sicherzustellen, dass sie mit Ihrem spezifischen Datenbereich und Ihren Anforderungen übereinstimmt. Mit ein wenig Übung werden Sie bald in der Lage sein, Excel-Listen automatisch und nahtlos zu wiederholen!