Datenschutzerklärung


Direktnachricht



Ihre Software
Details
Excel/VBA 🔍
Add-Ins

Suche in Beispielen und Tipps zu Excel und VBA

Suchbegriff(e) mit Leerzeichen getrennt:

SVERWEIS: Suchkriterium als Teil (VBA + Formel)UDF - benutzerdefinierte FunktionFormellösungArrayfunktion/Matrixfunktion

Kategorie: Tabelle ▸ Matrix

(Tipp 167) Nachricht zum Beitrag an Autor Nach oben

Wie kann ich wie mit dem SVERWEIS nach einer Zeichenfolge suchen, von der jedoch nur der erste Teil bekannt ist?

UDF - benutzerdefinierte Funktion

Die Syntax ist die gleiche wie bei der integrierten Funktion SVERWEIS:

=TeilSverweis(Suchkriterium;Matrix;Spaltenindex)

Function TeilSverweis(Suchkriterium As Variant, Bereich As Range, Spaltenindex As Integer) As Variant Dim rngZelle As Range Application.Volatile TeilSverweis = "" If Suchkriterium <> "" Then For Each rngZelle In Bereich If rngZelle.Column = Bereich.Column Then If CStr(Left(rngZelle.Value, Len(Suchkriterium))) = CStr(Suchkriterium) Then TeilSverweis = Cells(rngZelle.Row, rngZelle.Column + Spaltenindex - 1) Exit Function End If End If Next TeilSverweis = "Nicht gefunden" End If End Function


Dynamische Arrayfunktion FILTER() (ab Excel 365)

Interessante Möglichkeiten bietet die Funktion FILTER().

Diese Funktion liefert - je nachdem, welche Bereiche als Parameter verwendet werden - komplette Datensätze, die an der Formel überlaufen. Zum Beispiel diese Formel:

=FILTER(A2:C26;ISTFEHLER(FINDEN(KLEIN(E2);KLEIN(A2:A26)))=FALSCH;"")

In E2 steht die in A:A26 zu suchende Zeichenfolge. Damit Groß-/Kleinschreibung egal sind, wird alles mit KLEIN() umgewandelt. Die Formel gibt dann alle Datensätze zurück, bei denen in Spalte A die zu suchende Zeichenfolge irgendwo enthalten ist.

Anders ist es bei dieser Formel:

=FILTER(A2:C26;LINKS(A2:A26;LÄNGE(E2))=E2;"")

Sie gibt nur die Datensätze zurück, bei denen die Einträge in Spalte A mit der Zeichenfolge in E2 beginnen. Das sind sicher weniger, die Ergebnismenge ist hier kleiner.

Werden nicht die kompletten Datensätze benötigt, sondern z. B. nur eine Spalte, kann das im ersten Parameter der FILTER()-Funktion im Bereich eingegrenzt werden. So gibt diese Formel nur die Daten aus Spalte B zurück:

=FILTER((B2:B26);ISTFEHLER(FINDEN(KLEIN(E2);KLEIN(A2:A26)))=FALSCH;"")

Wird nur die erste Zelle aus den insgesamt gefundenen gebraucht, kann der Schnittmengenoperator @ eingesetzt werden:

=@FILTER((B2:B26);ISTFEHLER(FINDEN(KLEIN(E2);KLEIN(A2:A26)))=FALSCH;"")

Diese Funktion kann also u. U. ein besserer Ersatz für den SVERWEIS() sein, weil wir hiermit sehr flexibel sein können.

SVERWEIS: Zellen rechts addieren (auch mit Arrayfunktion)FormellösungArrayfunktion/Matrixfunktion

Kategorie: Tabelle ▸ Matrix

(Tipp 198) Nachricht zum Beitrag an Autor Nach oben

Per SVERWEIS lasse ich in Spalte A einen Begriff suchen. Wie ist es möglich, alle sich rechts vom gefundenen Begriff enthaltenen Zahlen zu addieren?

In E1 befindet sich der Suchbegriff.

=SUMME(INDIREKT("B"&VERGLEICH(E1;A1:A15;0)):INDIREKT("D"&VERGLEICH(E1;A1:A15;0)))


Dynamische Arrayfunktion (ab Excel 365)

Hier hat man natürlich leichtes Spiel, indem man die Funktion FILTER() verwendet. Im ersten Parameter, der Matrix, wählt man einfach den Bereich, in dem die Zahlen stehen. Da die dann auch ausgegeben werden, können die auch gleich addiert werden:

=SUMME(FILTER(B1:C5;A1:A5=E1;0))