Nur jeden x-ten Zeilenwert in Excel-Formel holen

Forumthread: Nur jeden x-ten Zeilenwert in Excel-Formel holen

03.09.2015 09:47:17

Timo

Hi zusammen,
ich verwende folgende INDEX- bzw. VERGLEICH-Funktion:
=INDEX($I$8:$DX$8;1;(VERGLEICH(Tabelle2!D13;$I14:$DX14;0))).
Der Wert in Tabelle 2 tritt das nächste Mal aber erst in Zeile 16 auf, sodass die Formel in der nächsten Zeile so aussehen müsste.
=INDEX($I$8:$DX$8;1;(VERGLEICH(Tabelle2!D16;$I15:$DX15;0)))
Da ich sehr viele Zeilen habe, wäre es sehr hilfreich, wenn ich die Formel einfach runter kopieren könnte. Ich habe einmal folgendes probiert, um nur jeden dritten Wert der Tabelle 2 in die Vergleichsfunktion aufzunehmen:
=INDEX($I$8:$DX$8;1;(VERGLEICH(INDEX(Tabelle2!D13:D50;ZEILE()*3-2);$I14:$DX14;0))).
Ich denke, das Problem liegt in dieser Funktion darin, dass die INDEX-Funktion einen Wert zurückgibt, ich aber die Zelladresse für die Vergleichsfunktion benötige.
Hat hierfür vielleicht jemand einen pragmatischen Ansatz?
Danke Viele Grüße,
Timo

Beiträge zum Forumthread

Beiträge zu diesem Forumthread

Betreff

Datum

Anwender

mit INDIREKT

03.09.2015 10:34:24

Hi,
=INDEX($I$8:$DX$8;1;(VERGLEICH(INDIREKT("Tabelle2!D"&10+ZEILE(X1)*3);$I14:$DX14;0)))
runterkopieren
WF

AW: mit INDIREKT

03.09.2015 10:41:29

Timo

Hi,
klasse! Vielen Dank :)
Gruß Timo

AW: Nur jeden x-ten Zeilenwert in Excel-Formel holen

03.09.2015 11:01:50

Daniel

Hi
dein Ansatz ist schon richtig.
nur folgende Punkte hast du nicht berücksichtigt:
- der Zellbezug für den Ausgangszellbereich muss absolut sein, sonst wird dieser ja beim Ziehen mit verschoben.
- für den ersten Wert muss das Ergebnis von Zeile() = 1 sein. Daher hier für die oberste Formel dann Zeile(A1) einsetzen und dann die Formel nach unten ziehen:
=INDEX($I$8:$DX$8;1;(VERGLEICH(INDEX(Tabelle2!D$13:D$50;ZEILE(A1)*3-2);$I14:$DX14;0)))
von INDIREKT ist hier abzuraten.
INDIREKT ist volatil, dh alle Formeln, die ein Indirekt enthalten, werden bei JEDER Änderung in Excel neu berechnet, dh sehr oft (normale Formeln werden nur dann neu berechnet, wenn sich in dem Zellbereich, den die Formel auch verwendet eine Änderung stattgefunden hat)
das kann sich dann negativ auf die Performance der Exceldatei auswirken, vorallem wenn man viele solcher Formeln hat und diese Funktionen mit hoher Rechenzeit enthalten.
INDIREKT ist nur dann zwingend erforderlich, wenn der Tabellenblattname "berechnet" werden muss oder wenn mit Namen als Zellbezug gearbeitet wird.
Ansonsten ist INDEX die bessere Alternative.
Gruß Daniel

Steile Lernkurve

03.09.2015 11:14:41

Jack_d

Hallo Daniel,
ich bin begeistert, endlich mal wieder jemand der sich Tips annimmt und auch drüber nachdenkt. :-D
Beste Grüße

AW: Nur jeden x-ten Zeilenwert in Excel-Formel holen

03.09.2015 12:40:20

Timo

Hallo Daniel,
vielen Dank für die Korrektur meiner Formel. :-)
Das hat sehr weitergeholfen! Die Performance ist bei dieser Dateigröße sehr wichtig...
Gruß Timo

Nachtrag: Nur jeden x-ten Wert in Excel-Formel

03.09.2015 16:27:12

Timo

Hi,
ich habe noch eine Nachfrage zu meiner Formel. Es tritt auch der Fall auf, dass weder im Suchkriterium in Tabelle 2, noch in der Suchmatrix in Tabelle 1 ein Wert steht. In diesem Fall wird dann immer Spalte 1 und Zeile 1 ausgeben, also INDEX($I$8:$DX$8;1;1).
Ziel wäre es aber, dass das Feld leer bleibt, wenn weder in Tabelle1 noch in Tabelle2 der Vergleichsfunktion ein Wert steht.
Kann man die Formel dahingehend ändern? Vermutlich geht es über den Else Fall einer Wenn-Funktion, die prüft, ob in der Zelle des Suchkriteriums etwas steht. Aber dann wäre das Ganze ja noch stärker verschachtelt (Verwende zusätzlich zur obigen Formel noch LINKS() und Rechts() um nur einen Teil des Zellenwertes auszugeben).
Vielleicht kann man ja auch die bestehende Vergleichsfunktion anpassen?
Danke und Gruß
Timo

Infobox / Tutorial

Nur jeden x-ten Zeilenwert in Excel holen

Schritt-für-Schritt-Anleitung
Häufige Fehler und Lösungen
Alternative Methoden
Praktische Beispiele
Tipps für Profis
FAQ: Häufige Fragen

Schritt-für-Schritt-Anleitung

Um in Excel nur jeden x-ten Wert zu holen, kannst du folgende Schritte befolgen:

Formel vorbereiten: Du benötigst die INDEX- und VERGLEICH-Funktionen. Ein einfaches Beispiel für jeden dritten Wert könnte so aussehen:
```
=INDEX($I$8:$DX$8;1;(VERGLEICH(INDEX(Tabelle2!D13:D50;ZEILE(A1)*3-2);$I14:$DX14;0)))
```
Diese Formel gibt den Wert in der Zeile zurück, die mit der Zeilennummer multipliziert wird.
Zellbezüge anpassen: Stelle sicher, dass deine Zellbezüge absolut sind, z. B. $I$8:$DX$8, damit sie beim Herunterziehen der Formel nicht verschoben werden.
Formel nach unten ziehen: Ziehe die Formel nach unten, um jeden x-ten Wert zu extrahieren. Achte darauf, dass du die Formel korrekt anpasst, wenn du mit einem anderen x-Wert arbeitest (z. B. jeden 10. Wert).
Überprüfen von leeren Zellen: Um sicherzustellen, dass das Feld leer bleibt, wenn weder in der Suchmatrix noch im Suchkriterium ein Wert vorhanden ist, kannst du die WENN-Funktion verwenden:
```
=WENN(ISTLEER(Tabelle2!D13); ""; INDEX($I$8:$DX$8;1;(VERGLEICH(INDEX(Tabelle2!D13:D50;ZEILE(A1)*3-2);$I14:$DX14;0)))
```

Häufige Fehler und Lösungen

Problem: Die Formel gibt immer den Wert in Zeile 1 zurück.
- Lösung: Überprüfe, ob die Zeilenreferenz korrekt ist. Stelle sicher, dass du die richtige Zeile für den ersten Wert verwendest, indem du ZEILE(A1) anstelle von ZEILE() verwendest.
Problem: Die Formel wird nicht richtig heruntergezogen.
- Lösung: Achte darauf, dass alle Zellbezüge, die nicht verschoben werden sollen, mit dem Dollarzeichen ($) versehen sind.
Problem: INDIREKT wird empfohlen, aber verursacht Performance-Probleme.
- Lösung: Vermeide die Verwendung von INDIREKT, da es die Berechnung verlangsamen kann. Nutze stattdessen INDEX und VERGLEICH.

Alternative Methoden

Eine alternative Methode könnte die Verwendung von Filterfunktionen oder Pivot-Tabellen sein, wenn du mit großen Datenmengen arbeitest. Diese Methoden ermöglichen es dir, Daten effizient zu analysieren und nur die relevanten Werte anzuzeigen.

Praktische Beispiele

Jeden 10. Wert:

=INDEX($I$8:$DX$8;1;(VERGLEICH(INDEX(Tabelle2!D13:D50;ZEILE(A1)*10-9);$I14:$DX14;0)))

Diese Formel hilft dir, jeden 10. Wert aus den Daten zu extrahieren.

Jeden dritten Wert:

=INDEX($I$8:$DX$8;1;(VERGLEICH(INDEX(Tabelle2!D13:D50;ZEILE(A1)*3-2);$I14:$DX14;0)))

Hiermit kannst du jeden dritten Wert aus der Tabelle ziehen.

Tipps für Profis

Nutze benannte Bereiche für häufig verwendete Zellbereiche, um die Übersichtlichkeit zu erhöhen.
Achte auf die Performance: Wenn deine Datenbank sehr groß ist, solltest du die Anzahl der verwendeten Formeln minimieren, um die Ladezeiten zu verkürzen.
Experimentiere mit Array-Formeln, um komplexere Datenanalysen durchzuführen.

FAQ: Häufige Fragen

1. Wie kann ich jeden x-ten Wert in einer großen Tabelle ziehen?
Du kannst die oben beschriebenen Schritte anwenden, um jeden x-ten Wert mit den INDEX- und VERGLEICH-Funktionen zu extrahieren. Achte darauf, die richtigen Zellbezüge zu verwenden.

2. Warum sollte ich INDIREKT vermeiden?
INDIREKT ist volatil und kann die Performance deiner Excel-Datei negativ beeinflussen, besonders bei großen Datenmengen, da es bei jeder Änderung neu berechnet wird.

Beliebteste Forumthreads (12 Monate)

Nur jeden x-ten Zeilenwert in Excel-Formel holen

Forumthread: Nur jeden x-ten Zeilenwert in Excel-Formel holen

6
Beiträge zum Forumthread

Beiträge zu diesem Forumthread

Forumthreads zu verwandten Themen

Infobox / Tutorial

Nur jeden x-ten Zeilenwert in Excel holen

Schritt-für-Schritt-Anleitung

Häufige Fehler und Lösungen

Alternative Methoden

Praktische Beispiele

Tipps für Profis

FAQ: Häufige Fragen

Beliebteste Forumthreads (12 Monate)

Nur jeden x-ten Zeilenwert in Excel-Formel holen

Forumthread: Nur jeden x-ten Zeilenwert in Excel-Formel holen

6 Beiträge zum Forumthread Beiträge zu diesem Forumthread

Forumthreads zu verwandten Themen

VBA Beispiele zu diesem und ähnlichen Themen

Infobox / Tutorial

Nur jeden x-ten Zeilenwert in Excel holen

Schritt-für-Schritt-Anleitung

Häufige Fehler und Lösungen

Alternative Methoden

Praktische Beispiele

Tipps für Profis

FAQ: Häufige Fragen

Beliebteste Forumthreads (12 Monate)

6
Beiträge zum Forumthread

Beiträge zu diesem Forumthread