Bei der Verwendung von Nachschlageformeln in Excel (wie SVERWEIS, XVERWEIS oder INDEX/MATCH) besteht die Absicht darin, den übereinstimmenden Wert zu finden und diesen Wert (oder einen entsprechenden Wert in derselben Zeile/Spalte) als Ergebnis abzurufen.
In einigen Fällen möchten Sie jedoch möglicherweise, dass die Formel anstelle des Werts die Zelladresse des Werts zurückgibt.
Dies kann besonders nützlich sein, wenn Sie über einen großen Datensatz verfügen und die genaue Position des Ergebnisses der Nachschlageformel herausfinden möchten.
Es gibt einige Funktionen in Excel, die genau dies tun.
In diesem Tutorial zeige ich dir, wie du das kannst die Zellenadresse anstelle des Wertes suchen und zurückgeben in Excel mit einfachen Formeln.
Zellenadresse mit der Funktion ADDRESS suchen und zurückgeben
Genau dafür ist die ADDRESS-Funktion in Excel gedacht.
Es nimmt die Zeilen- und Spaltennummer und gibt Ihnen die Zelladresse dieser bestimmten Zelle.
Unten ist die Syntax der ADDRESS-Funktion:
=ADRESSE(row_num, column_num, [abs_num], [a1], [sheet_text])
wo:
- row_num: Zeilennummer der Zelle, für die Sie die Zellenadresse wünschen
- column_num: Spaltennummer der Zelle, für die Sie die Adresse wünschen
- [abs_num]: Optionales Argument, bei dem Sie angeben können, ob der Zellbezug absolut, relativ oder gemischt sein soll.
- [a1]: Optionales Argument, bei dem Sie angeben können, ob die Referenz im R1C1-Stil oder im A1-Stil verwendet werden soll
- [sheet_text]: Optionales Argument, bei dem Sie angeben können, ob Sie den Blattnamen zusammen mit der Zelladresse hinzufügen möchten oder nicht
Nehmen wir nun ein Beispiel und sehen, wie das funktioniert.
Angenommen, es gibt einen Datensatz wie unten gezeigt, in dem ich die Mitarbeiter-ID, ihren Namen und ihre Abteilung habe, und ich möchte schnell die Zellenadresse wissen, die die Abteilung für die Mitarbeiter-ID KR256 enthält.
Unten ist die Formel, die dies tut:
=ADRESSE(MATCH("KR256",A1:A20,0),3)
In der obigen Formel habe ich die MATCH-Funktion verwendet, um die Zeilennummer herauszufinden, die die angegebene Mitarbeiter-ID enthält.
Und da die Abteilung in Spalte C steht, habe ich 3 als zweites Argument verwendet.
Diese Formel funktioniert hervorragend, hat jedoch einen Nachteil: Sie funktioniert nicht, wenn Sie die Zeile über dem Datensatz oder eine Spalte links vom Datensatz hinzufügen.
Dies liegt daran, dass, wenn ich das zweite Argument (die Spaltennummer) als 3 angebe, es fest codiert ist und sich nicht ändert.
Falls ich links vom Datensatz eine Spalte hinzufüge, zählt die Formel 3 Spalten vom Anfang des Arbeitsblatts und nicht vom Anfang des Datensatzes.
Wenn Sie also einen festen Datensatz haben und eine einfache Formel benötigen, funktioniert dies problemlos.
Wenn dies jedoch narrensicherer sein soll, verwenden Sie das im nächsten Abschnitt behandelte.
Zellenadresse mit der CELL-Funktion suchen und zurückgeben
Während die ADDRESS-Funktion speziell entwickelt wurde, um Ihnen den Zellbezug der angegebenen Zeilen- und Spaltennummer zu geben, gibt es eine andere Funktion, die dies ebenfalls tut.
Sie wird als CELL-Funktion bezeichnet (und kann Ihnen viel mehr Informationen über die Zelle geben als die ADDRESS-Funktion).
Unten ist die Syntax der CELL-Funktion:
=ZELLE(info_type, [Referenz])
wo:
- info_typ: die Informationen über die gewünschte Zelle. Dies kann die Adresse, die Spaltennummer, der Dateiname usw. sein.
- [Hinweis]: Optionales Argument, mit dem Sie den Zellbezug angeben können, für den Sie die Zellinformationen benötigen.
Sehen wir uns nun ein Beispiel an, in dem Sie diese Funktion verwenden können, um den Zellbezug nachzuschlagen und abzurufen.
Angenommen, Sie haben einen Datensatz wie unten gezeigt und möchten schnell die Zellenadresse ermitteln, die die Abteilung für die Mitarbeiter-ID KR256 enthält.
Unten ist die Formel, die dies tut:
=ZELLE("Adresse", INDEX($A$1:$D$20,MATCH("KR256",$A$1:$A$20,3))
Die obige Formel ist ganz einfach.
Ich habe die INDEX-Formel als zweites Argument verwendet, um die Abteilung für die Mitarbeiter-ID KR256 zu erhalten.
Und dann einfach in die CELL-Funktion eingeschlossen und gebeten, die Zelladresse dieses Werts zurückzugeben, den ich aus der INDEX-Formel bekomme.
Hier ist jetzt der Geheimnis, warum es funktioniert - Die INDEX-Formel gibt den Nachschlagewert zurück, wenn Sie alle erforderlichen Argumente angeben. Gleichzeitig würde es aber auch den Zellbezug dieser resultierenden Zelle zurückgeben.
In unserem Beispiel gibt die INDEX-Formel als Ergebniswert „Umsatz“ zurück, aber gleichzeitig können Sie damit auch den Zellbezug dieses Werts anstelle des Werts selbst angeben.
Wenn Sie die INDEX-Formel in eine Zelle eingeben, wird normalerweise der Wert zurückgegeben, da dies erwartet wird. Aber in Szenarien, in denen ein Zellbezug erforderlich ist, gibt Ihnen die INDEX-Formel den Zellbezug.
In diesem Beispiel ist es genau das, was es tut.
Und das Beste an dieser Formel ist, dass sie nicht an die erste Zelle im Arbeitsblatt gebunden ist. Dies bedeutet, dass Sie einen beliebigen Datensatz auswählen können (der sich an einer beliebigen Stelle im Arbeitsblatt befinden könnte), die INDEX-Formel verwenden, um regelmäßig nachzuschlagen, und Sie trotzdem die richtige Adresse erhalten.
Und wenn Sie eine zusätzliche Zeile oder Spalte einfügen, wird die Formel entsprechend angepasst, um Ihnen die richtige Zellenadresse zu geben.
Dies sind also zwei einfache Formeln, mit denen Sie nachschlagen können und Suchen Sie die Zellenadresse anstelle des Wertes in Excel und geben Sie sie zurück.
Ich hoffe, Sie fanden dieses Tutorial nützlich.
