Nur jeden x-ten Zeilenwert in Excel-Formel holen

Bild

Betrifft: Nur jeden x-ten Zeilenwert in Excel-Formel holen
von: Timo
Geschrieben am: 03.09.2015 09:47:17

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

Bild

Betrifft: mit INDIREKT
von: WF
Geschrieben am: 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

Bild

Betrifft: AW: mit INDIREKT
von: Timo
Geschrieben am: 03.09.2015 10:41:29
Hi,
klasse! Vielen Dank :)
Gruß Timo

Bild

Betrifft: AW: Nur jeden x-ten Zeilenwert in Excel-Formel holen
von: Daniel
Geschrieben am: 03.09.2015 11:01:50
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

Bild

Betrifft: Steile Lernkurve
von: Jack_d
Geschrieben am: 03.09.2015 11:14:41
Hallo Daniel,
ich bin begeistert, endlich mal wieder jemand der sich Tips annimmt und auch drüber nachdenkt. :-D
Beste Grüße

Bild

Betrifft: AW: Nur jeden x-ten Zeilenwert in Excel-Formel holen
von: Timo
Geschrieben am: 03.09.2015 12:40:20
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

Bild

Betrifft: Nachtrag: Nur jeden x-ten Wert in Excel-Formel
von: Timo
Geschrieben am: 03.09.2015 16:27:12
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

 Bild

Beiträge aus den Excel-Beispielen zum Thema "Nur jeden x-ten Zeilenwert in Excel-Formel holen"