Bereich zurückgeben in Excel
Schritt-für-Schritt-Anleitung
Um in Excel einen Bereich zurückzugeben, in dem sich ein gesuchter Wert befindet, kannst Du die folgende Array-Formel verwenden. Angenommen, Du suchst nach dem Wert 100 in den Zellen A1 bis G1.
- Wähle die Zelle H1 aus, in der das Ergebnis erscheinen soll.
- Gib die folgende Formel ein:
=KKLEINSTE(WENN($A$1:$G$1=100;SPALTE($A$1:$G$1));ZEILE())
- Bestätige die Eingabe mit Strg + Shift + Enter, um die Formel als Array-Formel einzugeben.
- Um die Zelladresse zu erhalten, gib in die nächste Zelle (z.B. H2) ein:
=ADRESSE(1;KKLEINSTE(WENN($A$1:$G$1=100;SPALTE($A$1:$G$1));ZEILE()))
- Fülle diese Formel nach unten aus, um alle Zellen anzuzeigen, die den gesuchten Wert enthalten.
Häufige Fehler und Lösungen
Alternative Methoden
Falls Du keine Array-Formeln verwenden möchtest oder eine VBA-Lösung bevorzugst, kannst Du den folgenden Code in ein Modul einfügen:
Sub BereichZurueckgeben()
Dim zeile As Integer
Dim spalte As Integer
Dim gesuchterWert As Variant
gesuchterWert = 100
For zeile = 1 To 1 ' Hier die Zeilen anpassen
For spalte = 1 To 7 ' Hier die Spalten anpassen
If ActiveSheet.Cells(zeile, spalte).Value = gesuchterWert Then
MsgBox "Wert gefunden in: " & ActiveSheet.Cells(zeile, spalte).Address
End If
Next spalte
Next zeile
End Sub
Praktische Beispiele
Wenn Du die Array-Formel in H1 und H2 eingegeben hast, sieht das Ergebnis folgendermaßen aus:
- Angenommen, Deine Werte in A1 bis G1 sind:
12;234;100;34;23;100;124
- In H1 wird
3 angezeigt (Spalte C).
- In H2 wird
$C$1 angezeigt (Zelladresse).
Fülle die Formeln nach unten aus, um auch die zweite Instanz des Wertes 100 zu sehen.
Tipps für Profis
- Nutze benannte Bereiche, um die Lesbarkeit Deiner Formeln zu erhöhen.
- Experimentiere mit der Funktion
SVERWEIS, um zusätzliche Informationen zu den gefundenen Werten abzurufen.
- Verwende die Funktion
FILTER, wenn Du Excel 365 oder neuere Versionen hast, um den gesamten Bereich dynamisch zu filtern.
FAQ: Häufige Fragen
1. Wie finde ich mehrere Werte gleichzeitig?
Verwende die FILTER-Funktion in Excel 365, um mehrere Suchkriterien zu kombinieren.
2. Welche Excel-Version benötige ich für die beschriebenen Funktionen?
Die Array-Formeln und die beschriebenen Funktionen sind in Excel 2010 und neueren Versionen verfügbar. Für die VBA-Lösung benötigst Du keine spezielle Version, jedoch sollte VBA unterstützt werden.