Generische Formel
=ADDRESS(ROW(nr),COLUMN(nr))&":"&ADDRESS(ROW(nr)+ROWS(nr)-1,COLUMN(nr)+COLUMNS(nr)-1)
Zusammenfassung
Um die vollständige Adresse eines benannten Bereichs mit einer Excel-Formel abzurufen, können Sie die Funktion ADRESSE zusammen mit den Funktionen REIHE und SPALTE verwenden. Im gezeigten Beispiel lautet die Formel in G5:
=ADDRESS(ROW(data),COLUMN(data),4)&":"&ADDRESS(ROW(data)+ROWS(data)-1,COLUMN(data)+COLUMNS(data)-1,4)
wobei "Daten" der benannte Bereich B5: D10 ist
Erläuterung
Der Kern dieser Formel ist die ADRESSE-Funktion, mit der eine Zellenadresse basierend auf einer bestimmten Zeile und Spalte zurückgegeben wird. Leider wird die Formel etwas kompliziert, da wir ADRESSE zweimal verwenden müssen: einmal, um die Adresse der ersten Zelle im Bereich zu erhalten, und einmal, um die Adresse der letzten Zelle im Bereich zu erhalten. Die beiden Ergebnisse werden mit der Verkettung und dem Bereichsoperator (:) verbunden, und der gesamte Bereich wird als Text zurückgegeben.
Um die erste Zelle im Bereich zu erhalten, verwenden wir diesen Ausdruck:
=ADDRESS(ROW(data),COLUMN(data))
ROW gibt die erste dem Bereich zugeordnete Zeilennummer 5 * zurück.
COLUMN gibt die erste Spaltennummer zurück, die dem Bereich zugeordnet ist, 2.
Wenn abs_num auf 4 (relativ) gesetzt ist, gibt ADDRESS den Text "B5" zurück.
=ADDRESS(5,2,4) // returns "B5"
Um die letzte Zelle im Bereich zu erhalten, verwenden wir diesen Ausdruck:
=ADDRESS(ROW(data)+ROWS(data)-1,COLUMN(data)+COLUMNS(data)-1,4)
Auf dieser Seite finden Sie eine ausführliche Erklärung.
Im Wesentlichen folgen wir der gleichen Idee wie oben und fügen einfache Berechnungen hinzu, um die letzte Zeile und die letzte Spalte des Bereichs zu berechnen, die wie zuvor mit abs_num auf 4 in ADDRESS eingespeist werden. Dies reduziert sich auf den folgenden Ausdruck, der den Text zurückgibt. " D10 ":
=ADDRESS(10,4,4) // returns "D10"
Beide Ergebnisse werden mit einem Doppelpunkt verknüpft, um eine endgültige Bereichsadresse als Text zu erhalten:
="B5"&":"&"D10" ="B5:D10
Benannter Bereich von einer anderen Zelle
Um eine Adresse für einen benannten Bereich in einer anderen Zelle zu erhalten, müssen Sie die INDIRECT-Funktion verwenden. Um beispielsweise die Adresse eines Namens in A1 abzurufen, verwenden Sie:
=ADDRESS(ROW(INDIRECT(A1)),COLUMN(INDIRECT(A1)))&":"&ADDRESS(ROW(INDIRECT(A1))+ROWS(INDIRECT(A1))-1,COLUMN(INDIRECT(A1))+COLUMNS(INDIRECT(A1))-1)
Setzen Sie abs_num in ADDRESS auf 4, um eine relative Adresse zu erhalten.
* In allen Fällen, in denen wir ROW und COLUMN mit einem mehrzelligen benannten Bereich verwenden, erhalten wir anstelle eines einzelnen Werts ein Array von Zahlen zurück. Da wir jedoch keine Array-Formel verwenden, ist die Verarbeitung auf das erste Element in diesen Arrays beschränkt.
