So verwendest du die Excel-Funktionen ZEILE, SPALTE, ADRESSE

Die drei Funktionen gehören zur Funktionskategorie „Nachschlagen und Verweisen“.

ZEILE und SPALTE sind die ersten beiden Argumente der Funktion ADRESSE.

Aus ADRESSE wiederum lassen sich die Zeilen- und Spaltennummern herauslesen.

1. Die Funktion ZEILE

Ganz einfach erklärt, ZEILE gibt die Zeilennummer eines Bezugs zurück.

Die Syntax fällt entsprechend einfach aus:

=ZEILE(Bezug)

Der Bezug ist die Zelle oder der Zellbereich, deren bzw. dessen Zeilennummer du ermitteln möchtest. Fehlt der Bezug, nimmt die Funktion die Zelle, in der die Formel steht, als Bezug an.

Schreibe die Formel ohne Bezug in die Zelle B4:

=ZEILE()

Es wird die Zeilennummer 4 ausgegeben. Schreibst du die gleiche Formel in Zelle AAB123, erhältst du auch dort die 123 als Ergebnis.

Schreibe die Formel mit Bezug auf B4 in die Zelle B12:

=ZEILE(B4)

Auch hier wird die Zeilennummer 4 ausgegeben. Schreibst du die gleiche Formel in Zelle AAB123, erhältst du auch dort die 4 als Ergebnis.

Der Bezug kann ebenso ein Zellbereich sein. Markiere den Bereich B20:B23 und schreibe die Formel zunächst ohne Bezug. Schließe die Eingabe mit Strg+Shift+Enter ab, es handelt sich um eine Matrixformel:

{=ZEILE()}

Die Zellen B20:B23 enthalten jetzt ihre Zeilennummern.

Schreibe die Formel jetzt mit Bezug auf B20:B23 in die Zellen B34:B37 als Matrixformel:

{=ZEILE(B20:B23)}

Du erhältst wieder die Zeilennummern wie zuvor.

Zeilenangaben werden in anderen Funktionen als Argument benötigt, z.B. in INDEX oder BEREICH.VERSCHIEBEN.

Hier ein Beispiel zur INDEX-Funktion:

Die kleine Tabelle stellt drei Produkte und ihre Produktionsmengen in den Monaten Januar bis März dar: Es soll der markierte Wert ermittelt werden.

Schreibe z.B.in B68 die Formel:

=INDEX(C50:E52;VERGLEICH(C65;B50:B52;0);C66)

Das zweite Argument der INDEX-Funktion ist die Zeilennummer, allerdings innerhalb der Matrix. Deshalb wird die Funktion VERGLEICH für die Positionsbestimmung gewählt.

Ebenso könnte die Funktion ZEILE verwendet werden, wenn die Zell-Adresse von der Produktbezeichnung C bekannt wäre. Sie ist dir in diesem Fall bekannt: B52

Allerdings kann die Matrix jetzt nicht wie vorher bezeichnet werden, denn sie hat nur drei und nicht 52 Zeilen.

Schreibe deshalb diese Formel:

=INDEX(C:E;ZEILE(B52);C66)

und du erhältst ebenso 2827 als Ergebnis.

Die Funktion ZEILE wird gern verwendet, um gleiche Ränge ungleich zu machen. Das geschieht, indem z.B. das Ergebnis aus

=ZEILE()/1000000

einem Wert hinzugerechnet wird.

Als Beispiel dazu sei auf diesen Beitrag im Blog verwiesen:

„Rangfolgebestimmung bei doppelten Werten„

2. Die Funktion SPALTE

Ebenfalls einfach erklärt, SPALTE gibt die Spaltennummer eines Bezugs zurück.

Die Syntax lautet:

=SPALTE(Bezug)

Der Bezug ist die Zelle oder der Zellbereich, deren bzw. dessen Spaltennummer du ermitteln möchtest. Fehlt der Bezug, nimmt die Funktion die Zelle, in der die Formel steht, als Bezug an.

Schreibe die Formel ohne Bezug in die Zelle B84:

=SPALTE()

Es wird die Spaltennummer 2 ausgegeben.

Schreibe die Formel mit Bezug auf B84 in die Zelle B93:

=SPALTE(B84)

Auch hier wird die Spaltennummer 2 ausgegeben. Schreibst du die gleiche Formel in Zelle AAB123, erhältst du auch dort die 2 als Ergebnis.

Der Bezug kann ebenso ein Zellbereich sein. Markiere den Bereich B103:E103 und schreibe die Formel zunächst ohne Bezug. Schließe die Eingabe mit Strg+Shift+Enter ab, es handelt sich wieder um eine Matrixformel.

{=SPALTE()}

Die Zellen B103:E103 enthalten jetzt ihre Spaltennummern.

Schreibe die Formel mit einem Bezug auf B103:E103 in die Zellen B1123:E113 als Matrixformel:

{=SPALTE(B103:E103)}

Du erhältst so die Spaltennummern der Zellen B103:E103.

Spaltenangaben werden in anderen Funktionen als Argument benötigt, z.B. in der INDEX-Funktion. Das Beispiel aus Abschnitt 1 kann hier genauso verwendet werden.

3. Die Funktion ADRESSE

Die Funktion gibt die Platzierung einer Zelle auf einem Arbeitsblatt zurück.

Die Syntax ist etwas umfangreicher als bei den vorangegangenen Funktionen, sie lautet:

=ADRESS(Zeile;Spalte;[Abs];[A1];[Tabellenname])

Das Argument Zeile gibt die Zeilennummer, das Argument die Spaltennummer eines Bezugs an.

Mit „Abs“ kann optional angegeben werden, welcher Bezugstyp zurück gegeben werden soll.

Das optionale Argument „A1“ gibt einen Hinweis darauf, ob der Bezug in A1- oder Z1S1-Schreibweise ausgegeben werden soll.

Und schließlich das Argument „Tabellenname“ kann optional verwendet werden, um anzugeben, welches Arbeitsblatt den externen Bezug enthält.

Mindestangaben sind jedoch Zeile und Spalte, mit der Schreibweise

=ADRESSE()

funktioniert die Formel nicht.

Ein paar Beispiele:

Verwende in B142 die Formel:

=ADRESSE(C138;C139)

Das Argument „Abs“ ist nicht angegeben, dennoch wird die Adresse absolut ausgegeben.

In B162 verwendest du die Formel

=ADRESSE(C157;C158;C159)

Im Ergebnis ist die Zeile relativ und die Spalte absolut angegeben.

Schreibe in B184 diese Formel:

=ADRESSE(C178;C179;C180;C181)

Das Ergebnis wird nun in der Z1S1-Schreibweise angezeigt, denn das Argument „A1“ wurde mit 0, gleichbedeutend mit FALSCH belegt.

Die Zeilenangabe steht in Klammern, da die Zeilenangabe mit Abs=3 relativ gefordert ist.

Ein weiteres Beispiel bezieht die Angabe des Arbeitsblattes mit ein.

In die Zelle B208 hast du die Formel

=ADRESSE(C201;C202;C203;C204;C205)

geschrieben. Das Ergebnis weist jetzt zusätzlich zum Beispiel davor den Tabellennamen aus.

„Vernünftig“ kannst du die Formel natürlich auch so schreiben:

=ADRESSE(25;16;3;0;“Tabelle1″)

Beachte: Der Tabellennamen ist in Anführungszeichen zu setzen.

Wenn du mit den Funktionen ZEILE und SPALTE als Argumente operieren möchtest, schreibe die Formel z.B. so:

=ADRESSE(ZEILE(B25);SPALTE(P25);3;0;“Tabelle1″)

Das Ergebnis lautet auch dann: Tabelle1!Z(25)S16

Wie kannst du nun Zeile und Spalte aus einer Adresse auslesen?

Angenommen, die Adresse lautet: $P$25

Schreibe in F227 und F229 die Formeln, wie in der Abbildung gezeigt.

Du siehst, um die Ergebnisse zu erhalten, musst du zusätzlich die Funktion INDIREKT bemühen, denn

=ZEILE(C225) ergibt 225

und

=SPALTE(C225) ergibt 3

Diese Ergebnisse sind nicht das, was du gesucht hast.

Wie die Funktionen BEREICH.VERSCHIEBEN, ZEILE, SPALTE und ADRESSE verwendet werden können, um einen Bezug zu bestimmen, ist übrigens in diesem Blogbeitrag nachzulesen:

„4 Möglichkeiten, die Excel-Funktion BEREICH.VERSCHIEBEN zu nutzen„

War der Beitrag nützlich für dich? Dann teile ihn doch mit deinen Freunden und Followern.