ADRESSE-Funktion – Zellenadresse abrufen – Excel & Google Sheets

In diesem Tutorial zeigen wir Ihnen, wie Sie die ADRESSE-Funktion in Excel und Google Sheets verwenden können, um eine Zellenadresse als Text zurückzugeben.

Was ist die ADRESSE-Funktion?

Die ADRESSE-Funktion gibt eine Zellenadresse als Text zurück.

Normalerweise geben wir in einer Tabellenkalkulation einen Zellenbezug an und der Wert in der betroffenen Zelle wird zurückgegeben. Stattdessen bildet die ADRESSE-Funktion den Namen einer Zelle.

Die Adresse kann relativ oder absolut sein, im Stil von A1 oder R1C1 sein und kann den Blattnamen enthalten oder nicht.

ADRESSE – Grundlegendes Beispiel

Nehmen wir an, wir wollen einen Verweis auf die Zelle in der 4. Spalte und 1. Zeile (auch bekannt als Zelle D1) erstellen. Wir können das hier abgebildete Layout verwenden:

Unsere Formel in A3 lautet einfach:

=ADRESSE(B1; B2)

Hinweis: Standardmäßig gibt die ADRESSE-Funktion einen absoluten Zellenbezug zurück. Durch die Aktualisierung eines optionalen Arguments können wir die Zellenbezüge von absolut auf relativ ändern. Schauen wir uns diese und andere Eingaben für die ADRESSE-Funktion an…

ADRESSE-Funktion – Syntax und Eingaben:

=ADRESSE(Zeile;Spalte;Abs;A1;Tabellenname)

Zeile – Die Zeilennummer für den Bezug. Beispiel: 5 für Zeile 5.

Spalte – Die Spaltennummer für den Bezug. Beispiel: 5 für Spalte E. Sie können kein „E“ für Spalte E eingeben

Abs – [optional] Eine Zahl, die angibt, ob der Verweis absolute oder relative Zeilen-/Spaltenverweise haben soll. 1 für absolut. 2 für absolute Zeile/relative Spalte. 3 für relative Zeile/absolute Spalte. 4 für relativ.

A1 – [optional]. Eine Zahl, die angibt, ob das Standard-Zellenreferenzformat (A1) oder das Format R1C1 verwendet werden soll. 1/WAHR für Standard (Standard). 0/FALSCH für R1C1.

Tabellenname – [optional] Der Name des zu verwendenden Arbeitsblatts. Standardmäßig wird das aktuelle Blatt verwendet.

ADRESSE mit INDIREKT

Wir können ADRESSE mit der INDIREKT-Funktion kombinieren. Betrachten Sie dieses Layout, in dem wir eine Liste von Elementen in Spalte D haben.

Wir können einen Verweis auf D1 wie folgt erzeugen:

=ADRESSE(B1; B2)
=$D$1

Indem wir die ADRESSE-Funktion in eine INDIREKT-Funktion einfügen, können wir den erzeugten Zellenverweis verwenden und ihn auf praktische Weise einsetzen. Die INDIREKT-Funktion nimmt den Verweis auf „$D$1“ und verwendet ihn, um den Wert aus dieser Zelle zu holen.

=INDIREKT(ADRESSE(B1; B2)
=INDIREKT($D$1)
="Apfel"

Hinweis: Das obige Beispiel ist zwar ein gutes für die Nützlichkeit der ADRESSE-Funktion, aber es ist keine gute Formel für den normalen Gebrauch. Es werden zwei Funktionen benötigt und aufgrund der INDIREKT-Funktion ist sie von Natur aus volatil. Eine bessere Alternative wäre es gewesen, die INDEX-Funktion wie folgt zu verwenden:

=INDEX(1:1048576; B1; B2)
Adresse eines bestimmten Wertes
Wenn Sie eine große Liste von Elementen haben, müssen Sie manchmal die Position eines Elements in dieser Liste kennen. Nehmen wir die diese folgende Tabelle mit Noten von Studenten. Wir haben die Minimal-, Median- und Maximalwerte dieser Punktzahlen in den Zellen E2:G2 berechnet.



Nehmen wir an, wir möchten diese Werte ermitteln. Wir haben zwei Möglichkeiten:

 	
Unsere Tabelle nach jedem dieser Elemente filtern.
 	
Die VERGLEICH-Funktion zusammen mit ADRESSE verwenden. Denken Sie daran, dass VERGLEICH die relative Position eines Wertes innerhalb eines Bereichs zurückgibt.

Unsere Formel in E3 lautet dann:
=ADRESSE(VERGLEICH(E2; $B:$B; 0); 2)

Wir können dieselbe Formel nach G3 kopieren und nur der E2-Bezug wird sich ändern, da er der einzige relative Bezug ist. Zurück zu E3: Die VERGLEICH-Funktion konnte den Wert 98 in der. 5. Zeile der Spalte B finden. Unsere ADRESSE-Funktion hat dies genutzt, um die vollständige Adresse "$B$5" zu bilden.
Spaltenbuchstaben aus Zahlen ermitteln


Bis jetzt haben alle Beispiele einen absoluten Bezug zurückgegeben. Lassen Sie uns stattdessen einen relativen Bezug zurückgeben.
Im Folgenden wollen wir in Spalte B den Spaltenbuchstaben, der der Spaltennummer in Spalte A entspricht, berechnen.

Wir verwenden die ADRESSE-Funktion, um einen Verweis auf Zeile 1 im relativen Format zurückzugeben und entfernen dann die "1" aus der Textzeichenfolge, so dass nur der/die Buchstabe(n) übrig bleibt/bleiben. Betrachten wir in unserer Tabelle die Zeile 3, in der sich unsere Eingabe 13 befindet. Unsere Formel in B3 lautet:
=WECHSELN(ADRESSE(1; A3; 4); "1"; "")

Beachten Sie, dass wir das 3. Argument, das die relative bzw. absolute Referenzierung steuert, in der ADRESSE-Funktion angegeben haben. Die ADRESSE-Funktion gibt "M1" aus und die WECHSELN-Funktion entfernt die "1", so dass nur "M" übrig bleibt.
 
Die Adresse von benannten Bereichen finden
In Excel können Sie einen Bereich oder mehrere Bereiche von Zellen benennen, so dass Sie sich einfach auf den benannten Bereich anstelle des Zellenbezugs beziehen können.

Die meisten benannten Bereiche sind statisch, d. h. sie beziehen sich immer auf denselben Bereich. Sie können jedoch auch dynamische benannte Bereiche, deren Größe sich auf Grundlage einer oder mehrerer Formeln ändert, erstellen.
Bei einem dynamischen benannten Bereich müssen Sie möglicherweise die genaue Adresse, auf die Ihr benannter Bereich verweist, kennen. Dies können wir mit der ADRESSE-Funktion erreichen.
In diesem Beispiel sehen wir uns an, wie wir die Adresse für unseren benannten Bereich mit dem Namen "Noten" definieren.
Nehmen wir unsere Tabelle von vorhin wieder:

Um die Adresse eines Bereichs zu erhalten, müssen Sie die Zelle links oben und die Zelle rechts unten kennen. Der erste Teil ist mit Hilfe der Funktionen ZEILE und SPALTE leicht zu bewerkstelligen. Unsere Formel in E1 kann Folgendes lauten:
=ADRESSE(@ZEILE(Noten); @ SPALTE(Noten))

Die ZEILE-Funktion gibt die Zeile der ersten Zelle in unserem Bereich zurück (das wäre die 1) und die SPALTE-Funktion tut dasselbe für die Spalte (ebenfalls 1).
Diese Formel liefert die Zelle unten rechts:
=ADRESSE(ZEILEN(Noten)-@ZEILE(Noten)+1; SPALTEN(Noten)-@SPALTE(Noten)+1)

Wir haben die Funktionen ZEILEN und SPALTEN verwendet, um die Höhe und Breite des Bereichs zu berechnen. Durch Subtraktion der ersten Zeilen- und Spaltennummer wird die letzte Zelle des Bereichs berechnet.
Um schließlich alles in einer einzigen Zeichenfolge zusammenzufassen, können wir die Werte einfach mit einem Doppelpunkt in der Mitte aneinanderhängen. Die Formel in E3 kann die folgende sein:
=E1 & ":" & E2

Hinweis: Wir konnten zwar die Adresse des Bereichs ermitteln, aber unsere ADRESSE-Funktion hat bestimmt, ob die Bezüge als relativ oder absolut aufgelistet werden sollten. Ihre dynamischen Bereiche werden relative Bezüge haben, die mit dieser Technik nicht erfasst werden können.
Hinweis Nr. 2: Diese Technik funktioniert nur bei einem kontinuierlichen benannten Bereich. Wenn Sie einen benannten Bereich hätten, der etwa wie folgt definiert wäre:
=A1:B2; A5:B6
würde die obige Technik zu Fehlern führen.
Die ADRESSE-Funktion in Google Sheets
Die ADRESSE-Funktion funktioniert in Google Sheets genau so wie in Excel.