Frage von kaschi1987, 101

In Excel eine Formel eine Zeile nach unten kopieren?

Ich habe in b1 eine Formel die sich auf a1 bezieht. Wenn ich die Formel nun in b2 kopiere, ändert sich der zu durchsuchende Bereich auch in b2 (außer mit "$"). Ich möchte jetzt aber, dass nicht a2 sondern z.b. g1 durchsucht wird. Die Formel also 1 nach unten kopieren aber Excel soll dann den zu durchsuchenden Bereich z.b. 6 Spalten nach rechts verschieben. Geht das?

Expertenantwort
von DeeDee07, Community-Experte für Excel, 69

Dafür kannst du
=INDEX($1:$1;1;ZEILE(A1)*6-5)
verwenden.

Wenn du diese Formel von B1 nach unten kopierst, wird sich B2 auf G1, B3 auf M1 usw. beziehen. Du kannst diese Formel als Teil einer komplexeren Formel verwenden. Falls deine Bezüge nur Beispiele waren, musst du die Formel entsprechend auf die richtigen Verhältnisse anpassen.

Kommentar von DeeDee07 ,

Alternativ
=BEREICH.VERSCHIEBEN(A1;-ZEILE(A1)+1;(ZEILE(A1)-1)*6)
oder
=INDIREKT(ADRESSE(1;ZEILE(A1)*6-5))

Kommentar von kaschi1987 ,

Hmm, noch kriege ich es nicht ganz hin. Ich mache mal ein Beispiel. Ich habe in einem Tabellenblatt in a2 folgende Formel: =WENNFEHLER(SVERWEIS(a1;Tabelle2!C4:Q13;13;FALSCH);WENNFEHLER(SVERWEIS(a1;Tabelle2!E4:Q13;13;FALSCH);0)). In a3 soll jetzt stehen:  =WENNFEHLER(SVERWEIS(a1;Tabelle2!T4:AH13;13;FALSCH);WENNFEHLER(SVERWEIS(a1;Tabelle2!V4:AH13;13;FALSCH);0)). Der Suchbereich soll sich also um 15 Spalten nach rechts verschieben. Mir fehlt jetzt auch grad das Verständnis wo ich deine Formel genau eintragen muss...

Kommentar von Oubyi ,

Von C bis T sind es aber 17 Spalten!
Von Q bis AH ebenfalls.
Als gehe ich mal von 17 Spalten Verschiebung aus.
Dann könnte das so klappen:

=WENNFEHLER(SVERWEIS(A2;INDIREKT("Tabelle2!"&ADRESSE(4;2+ZEILE(A2)*17-16)&":"&ADRESSE(13;16+ZEILE(A2)*17-16));13;FALSCH);WENNFEHLER(SVERWEIS(A2;INDIREKT("Tabelle2!"&ADRESSE(4;4+ZEILE(A2)*17-16)&":"&ADRESSE(13;16+ZEILE(A2)*17-16));13;FALSCH);0))

Teste aber auf jeden Fall mal genau, da ich Dein Szenario nicht nachgebaut habe.

P.S.: Wenn es doch nur 15 Spalten sein sollen, ändere vier mal 17-16 in 15-14.

Kommentar von kaschi1987 ,

funktioniert leider bei mir nicht. Wird immer 0 ausgegeben. Trotzdem Danke. :)

Kommentar von Oubyi ,

Schade.
Die Adresse die von der INDIREKT-Formel erzeugt wird, kannst Du sehen, wenn Du in der Bearbeitungszeile irgendwo in INDIREKT klickst und dann den fx-Button drückst.
Dann steht rechts neben "Bezug" die erzeugte Adresse.
Schau mal, ob das die richtige ist.
Wenn ja, ersetze in der Formel die kompletten INDIREKT Teile durch diese Adressen. Klappt die Formel dann?

Kommentar von kaschi1987 ,

Danke für den Tipp. Erstmal wollte ich noch sagen, dass du mit der 17 natürlich Recht hattest. ;)

Wenn ich jetzt auf fx drücke, steht da, dass der Bezug die Spalten BS:CG sind. Warum auch immer... :-/

Kommentar von Oubyi ,

DAS kann ich jetzt gar nicht nachvollziehen.
Aber ich habe gerade einen FEHLER meiner Formel entdeckt.
Ich hatte aus versehen die Formel kopiert, wie sie ist, wenn man sie eine Zelle nach unten kopiert hat.
Die Ausgangsformel ist richtig so:

=WENNFEHLER(SVERWEIS(A1;INDIREKT("Tabelle2!"&ADRESSE(4;2+ZEILE(A1)*17-16)&":"&ADRESSE(13;16+ZEILE(A1)*17-16));13;FALSCH);WENNFEHLER(SVERWEIS(A1;INDIREKT("Tabelle2!"&ADRESSE(4;4+ZEILE(A1)*17-16)&":"&ADRESSE(13;16+ZEILE(A1)*17-16));13;FALSCH);0))

Also überall A1 statt A2. Allerdings kommt auch mit der vorher gezeigten - um eine Zelle runterkopierten - Formel nicht der von Dir genannte Bezug zustande.

Wo noch ein Fehler liegen könnte ist folgendes:
Wenn Du als Suchkriterium für den SVERWEIS nicht A1, sondern einen anderen Bezug hast, dann darfst Du den nur bei dem SVERWEIS ändern.
Der Teil "ZEILE(A1)" bezieht sich nur zufällig auf die selbe Zelle. Der muss unbedingt so bleiben (für die erste Zelle der Formel, beim Runterkopieren ändert er sich natürlich).
Wenn Du die Formel also z.B. in die Zelle D10 schreibst und den SVERWEIS auf C10 beziehen willst, dann lautet die Formel:

=WENNFEHLER(SVERWEIS(C10;INDIREKT("Tabelle2!"&ADRESSE(4;2+ZEILE(A1)*17-16)&":"&ADRESSE(13;16+ZEILE(A1)*17-16));13;FALSCH);WENNFEHLER(SVERWEIS(C10;INDIREKT("Tabelle2!"&ADRESSE(4;4+ZEILE(A1)*17-16)&":"&ADRESSE(13;16+ZEILE(A1)*17-16));13;FALSCH);0))

Kommentar von Oubyi ,

Anscheinend hast Du aufgegeben.
Schade.

Keine passende Antwort gefunden?

Fragen Sie die Community

Weitere Fragen mit Antworten