Excel-TippZelladressen unter Auswahlbedingung anzeigen

Sie wollen nicht nur wissen, welche Werte in einem bestimmten Bereich bestimmte Bedingungen erfüllen, sondern auch, wo diese Werte sich befinden – also welche Zelladressen sie haben? Zum Beispiel: Sie wollen alle Zellen identifizieren, deren Werte größer als 1000 sind.

Das Wissen über die genauen Zelladressen ist wichtig, wenn Sie gezielt weiter mit diesen Zellen arbeiten oder sie in anderen Formeln verwenden möchten. In diesem Artikel zeigen wir Ihnen, wie Sie in Excel die Zelladressen von Werten über 1000 ermitteln und auflisten können. Und das funktioniert natürlich genauso mit allen anderen gesuchten Werten.

Beispiel für die Auswahl von Zelladressen

In der folgenden Abbildung sehen Sie einen Bereich mit Umsatzwerten. Sie sollen die Zelladressen, welche größer als 1000 sind, an einer anderen Stelle untereinander auflisten.

Formel zur bedingten Ausgabe von Zelladressen

Sie können diese Aufgabe mit der folgenden Formel lösen:

=ZUSPALTE(WENN(A1:C17>1000; ADRESSE(ZEILE(A1:C17); SPALTE(A1:C17));NV());2)

Die Formel geht hierbei von der Voraussetzung aus, dass A1:C17 der Bereich ist, in dem die zu prüfenden Werte stehen.

Die Parameter und Funktionen der Formel

Die Formel überprüft jede Zelle im Bereich A1:C17 und liefert entweder die Zelladresse oder den Fehlerwert #NV.

Das bewirken die einzelnen Komponenten:

A1:C17>1000: Diese Bedingung prüft, ob die Werte im Bereich A1:C17 größer als 1000 sind. Wenn der Wert größer als 1000 ist, wird WAHR zurückgegeben, andernfalls FALSCH.

ADRESSE(ZEILE(A1:C17);SPALTE(A1:C17))

• ZEILE(A1:C17) liefert für jede Zelle im Bereich die entsprechende Zeilennummer (zum Beispiel 1 für Zellen in der ersten Zeile, 2 für Zellen in der zweiten usw.).
• SPALTE(A1:C17) gibt für jede Zelle die Spaltennummer zurück (1 für Spalte A, 2 für Spalte B usw.).

ADRESSE(ZEILE(A1:C17);SPALTE(A1:C17)) kombiniert die Zeilen- und Spaltennummern und gibt die Adresse der jeweiligen Zelle als Text zurück (zum Beispiel $A$1 für die Zelle in der ersten Zeile und ersten Spalte).

Dieser Teil der Formel sorgt dafür, dass die Zelladressen aller Zellen, deren Werte größer als 1000 sind, als Text zurückgegeben werden.

NV(): Wenn der Wert in einer Zelle kleiner oder gleich 1000 ist, gibt die Funktion WENN den Fehlerwert #NV (Nicht Verfügbar) zurück. Dies sorgt dafür, dass Sie später bei der Verwendung von ZUSPALTE keine leeren oder falschen Werte aufgelistet bekommen.

Zusammenfassend sorgt diese Kombination aus WENN, ADRESSE, ZEILE und SPALTE dafür, dass Sie entweder die Zelladresse für Zellen mit einem Wert größer als 1000 erhalten oder den Fehlerwert #NV, wenn der Wert die Bedingung nicht erfüllt.

Die Zelladressen und Fehlerwerte werden anschließend an die Funktion ZUSPALTE() weitergegeben =ZUSPALTE(WENN(A1:C17>1000; ADRESSE(ZEILE(A1:C17); SPALTE(A1:C17));NV());2)

Was die Funktion ZUSPALTE() bewirkt

Die Funktion ZUSPALTE() wandelt ein mehrdimensionales Array, wie in diesem Fall den Bereich A1:C17, in eine einspaltige Liste um. Sie sorgt dafür, dass alle Zelladressen oder Fehlerwerte aus dem ursprünglichen mehrspaltigen Bereich in einer einzelnen Spalte angezeigt werden.

Hierbei gibt der zweite Parameter (ZUSPALTE(...;2)) an, dass die Funktion ZUSPALTE() keine Fehlerwerte berücksichtigt. Fehlerwerte entsprechen in unserem Beispiel Zellen, deren Werte kleiner als 1000 sind und daher nicht weiter berücksichtigt werden sollen.

Standardmäßig werden die Daten spaltenweise gelesen und umgewandelt. Das bedeutet, Excel wird zunächst die erste Spalte, dann die zweite und schließlich die dritte Spalte durchlaufen, um die Zelladressen oder Fehlerwerte zu extrahieren.

Zeilenweise Auswertung der Liste mit ZUSPALTE()

Wollen Sie die Daten zeilenweise im Datenbereich lesen und umwandeln, dann müssen Sie die Formel wie folgt anpassen:

=ZUSPALTE(WENN(A1:C17>1000; ADRESSE(ZEILE(A1:C17); SPALTE(A1:C17));NV());2;WAHR)