Excel Formel: Wenn Zelle enthält substring "das" UND "enthält nicht" substring ", die"

Ich versuche zu schreiben, eine Funktion, in der eine Spalte enthält einen substring und nicht enthalten eine andere Zeichenfolge.

Im Beispiel wurde möchte ich meine Funktion gibt 1 zurück, wenn meine Zeile enthält "Projekt" UND " NICHT ENTHALTEN "overhead".

row| example strings             | desired return value
0  |some project,other project   | 1
1  |some project                 | 1
2  |overhead                     | 0
3  |some project, overhead       | 0
4  |some project, other, boo     | 1

War ich versucht, es zu formulieren erste mit Zeichenfolgen wie:

=IF(AND((E3="some project"),NOT(E3="overhead")),1,0)

Aber das gibt nur richtige Ergebnisse für Zeile 1 und 2, weil es nur tut genau das mach der string anstelle des übereinstimmenden auf die Teilzeichenfolge.

InformationsquelleAutor Marta | 2016-11-08
  1. 3

    Was Sie brauchen, ist eine Art Substring-Funktion. Ich denke FIND arbeiten könnte. Schauen Sie sich diese Seite: https://exceljet.net/excel-functions/excel-find-function

    Ihre Funktion wie:
    =IF(AND(ISERROR(FIND("some project", E3))=FALSE,ISERROR(FIND("overhead",E3))),1,0)

    EDIT: Oben-Funktion arbeitet nach der Prüfung

    Schwierige Teil hier ist, dass FIND gibt die Startposition der Zeichenfolge, und wenn es scheitert, es gibt #VALUE, die ich glaube, Sie fangen mit der ISERROR() Funktion. Dies ist in keiner Weise eine schöne Lösung. Ich würde versuchen, zu nutzen, um die code-behind und schreiben, das ist VBA, wie ich bin sicher, es ist eine richtige substring Funktion in VBA.

    • Das Beispiel oben hat den trick. Danke!!!
    InformationsquelleAutor wbrugato
  2. 3

    Wenn Sie können, legen Sie ein wenig VBA-code, dann Sie können verwenden einer benutzerdefinierten Funktion so auf:

    =StrContains(E3, "some project", "overhead")

    Und diese zurück True wenn der Wert in E3 enthält beide dieser Teilfolgen. Diese Funktion setzt vor allem auf VBA ist Instr - Funktion, die

    Funktion code:

    Public Function StrContains(ByRef cl As Excel.Range, ParamArray strings() As Variant)
'Function returns TRUE if the range contains ALL of the passed substring items
' uses ParamArray to allow varying number of substring items
' Ex:
' =StrContains(A1, "something", "else", "foo")
'
Dim val$
Dim s
Dim i As Integer
Dim ret As Boolean
Dim length As Integer

length = UBound(strings) + 1
val = cl.Value2

For Each s In strings
    If InStr(1,val, s) <> 0 Then
        i = i + 1
    End If
Next

ret = (i = length)
StrContains = ret
End Function

    Könnten Sie ändern diese relativ problemlos auf die groß- /Kleinschreibung nicht beachtet, oder zu akzeptieren, die teilweise übereinstimmungen Optional, etc. Hier ist, wie es aussieht, verlängert sich für diese beiden Konzepte:

    =StrContains(E3, False, True, "some project", "overhead")

    Funktion Code:

    Public Function StrContains(ByRef cl As Excel.Range, MatchCase As Boolean, MatchAll as Boolean, ParamArray strings() As Variant)
'MatchAll is matching switch, use True to require ALL matching items, or False to allow for fewer.
'MatchCase is the Case-sensitive switch, use False to ignore case.
' uses ParamArray to allow varying number of substring items
' Ex:
' =StrContains(A1, "something", "else", "foo")
'
Dim val$
Dim s
Dim i As Integer
Dim ret As Boolean
Dim length As Integer

length = UBound(strings) + 1
val = cl.Value2

If Not MatchCase Then
    val = LCase(val)
    For i = lBound(strings) to UBound(strings)
        strings(i) = lcase(strings(i))
    Next
Next


For Each s In strings
    If InStr(val, s) <> 0 Then
        i = i + 1
    End If
Next

ret = (i = IIF(MatchAll, length, 1))
StrContains = ret
End Function
    • Die wahre Lösung hier. Während die excel-Funktionen arbeiten können, Sie sind hässlich und hart sein können zu verstehen.
    • danke @wbrugato-ich werde überarbeiten Sie Ihren Beitrag mit so etwas wie dieses, aber es fühlte sich besser als separate Antwort, da es konzeptionell etwas anderes, als mithilfe von Arbeitsblatt-Funktionen als je von dir & die andere Antwort oben. Cheers.
    InformationsquelleAutor David Zemens
  3. 2

    Können Sie tatsächlich nutzen Sie die SUCHE-Funktion, wie diese:

    =IF(ISNUMBER(SEARCH("some project",B2)),NOT(ISNUMBER(SEARCH("overhead",B2)))*1,0)

    Überlegungen:

    • SUCHEN Funktion: liefert entweder eine Zahl oder ein Fehler (das ist, warum ich verwendet, ISNUMBER, man könnte tatsächlich verwendet ISERROR auch). Diese Funktion ist groß-und Kleinschreibung. Für den Fall, Feingefühl, könntest du einfach SUCHEN, in der vorhergehenden Formel woud werden =IF(ISNUMBER(FIND("some project",B2)),NOT(ISNUMBER(FIND("overhead",B2)))*1,0)
    • ISNUMBER Funktion: liefert entweder FALSE oder TRUE. Ich konvertiert das Ergebnis in 1 oder 0 einfach durch Multiplikation TRUE oder FALSE durch 1.

    Hoffe, dass hilft

    InformationsquelleAutor kajahno
Schreibe einen Kommentar