Wie kann ich Excel „=SUMPRODUCT(–ISTZAHL(SUCHEN…“ – Funktion ausschließen „kompliziert zusammengesetzte‘ Ergebnisse?

Ich versuche zu validieren, die eine Liste der Produkt-Beschreibungen zu markieren Fälle, wo die Farbe des Produktes verwendet wurde, in seiner Beschreibung.

Habe ich eine Liste der Farben…

Example:
Blue
Red
Green
Yellow
Purple
etc.

Ich benutze folgende Formel, um festzustellen, ob eine dieser Farben wurde verwendet:

=IF(SUMPRODUCT(--ISNUMBER(SEARCH(Table1[Colors],A1)))>0,"Cannot include a colour","")

Wo Table1[Colours] enthält meine Liste der Farbe, die text-strings und A1 enthält meine erste Produkt-Beschreibung.

(Dave Bruns @ ExcelJet hat eine große Lesen für alle wollen, verwenden Sie SUMPRODUCT/ISTZAHL/SEARCH Kombinationen.)

Wenn ein Produkt-Beschreibung enthält eine Farbe angegeben, die in meinem Table1[Colours] Liste der Formel erzeugt "Cannot include a colour" um den Benutzer daran erinnern, dies ist nicht erlaubt.

Beispiel:
„Garmin Forerunner 10 Laufen Sportswatch Grün

Die erste Ausgabe bin ich damit konfrontiert, dass meine aktuelle Formel procs, wenn die Produktbeschreibung enthält ein zusammengesetztes Wort mit einer Farbe…

Beispiel:
Schwarzberry Z10 Smartphone“

Diese ungenau, erlischt die Beschreibung, da der string "Black" in diesem text nicht verwendet wird, um zu beschreiben die Farbe des Produktes.

Wie der Titel schon sagt, mein Haupt-Problem liegt mit überlistung “ kompliziert zusammengesetzte Wörter…

… meine Table1[Colours] Liste nicht einfach enthalten die grundlegenden Primären, Sekundären und Tertiären Farben, aber auch Exoten wie Koralle, Fuchsia und Tan.

Diese Ursachen Komplikation, wenn die Produktbeschreibung enthält ein Wort wie „Stand“.

Warum ist dies ein problem, denken Sie vielleicht? Stand enthält einer meiner exotischen Farben ‚Tan‘

S-Tan-d

Leider auch bewirkt, dass meine Formel proc. (Ärgerlich, richtig?)

Die Lösung, die ich Suche, ist eine Ergänzung zu meinem bestehenden Formel =IF(SUMPRODUCT(--ISNUMBER(SEARCH(Table1[Colors],A1)))>0,"Cannot include a colour","") welche Konten für das mögliche auftreten eines „kompliziert zusammengesetzte‘ werden diese durch einen Schalter Liste mit zulässigen Wörtern (z.B. Table2[Exceptions] oder von wilden Karden die Suche genau die Farbe ohne Präfix oder Suffix (diese option hätte die Möglichkeit einen dual-Farbe, getrennt durch eine / z.B. „Schwarz/Rot“, so dass, wilden Karden mit bestimmten Satzzeichen Ausnahmen?)

…Es ist alles nur ein bisschen schrecklich und unbequem.

Jede Beratung wird geschätzt.

Dank, Herr J

  • Die Farben…Sie Sind immer am Ende? genauso wie „Garmin Forerunner 10 Laufen Sportswatch Green
InformationsquelleAutor Mr. J | 2016-06-09



One Reply
  1. 2

    Müssen Sie, um die Suche für die Wortgrenzen. Wenn Sie fügen Sie eine space zu Beginn und Ende der Farbe, und auch der Anfang und das Ende Ihrer Beschreibung, das sollte den trick tun, je auf Ihre Daten. Also die Suche Teil der Formel könnte lauten:

    SEARCH(" " &Table1[Colors]& " "," "&A1&" ")
    

    Oder für Ihr gesamtes Formel:

    =IF(SUMPRODUCT(--ISNUMBER(SEARCH(" "&Table1[Colors]&" "," "&A1&" ")))>0,"Cannot include a colour","")
    

    Wenn Sie sich getrennt haben Farben, e.g: blue-green oder eine Farbe wie Cherry3, müssen Sie Sie separat in der Tabelle.

    BEARBEITEN: Wie Ihr Kommentar suggeriert eine viel komplexere situation, würde ich vorschlagen, eine benutzerdefinierte Funktion (UDF) für einfache Wartung.

    Folgende UDF akzeptieren kann, wie findtext eine Reichweite, die ein einzelner string oder ein array mit Konstanten, die aus mehreren strings. Wenn Sie eine array-Konstante verwenden, müssen Sie das Semikolon ; – und nicht ein Komma als separator.

    Ein Beispiel:

    =IF(reMatch(Table1[Colors],A1),"Cannot include a colour","")
    

    Der code verwendet die Regex-Zeichen für Wort-Grenze. Eine Wort-Grenze ist der Punkt, an dem ein Zeichen im Zeichensatz [0-9A-Za-z_] ist neben jedes Zeichen, das nicht in diesem Satz, oder am Anfang oder Ende der Zeichenfolge. Das sollte decken alle Ihre IF – Funktionen-Beispiele und mehr.

    Option Explicit
    Function reMatch(FindText As Variant, WithinText As String) As Boolean
    'FindText could be a Range, an array constant, or a single item
    'If FindText is an array constant, separate elements by semicolons, not commas
        Dim RE As Object
        Dim I As Long
        Dim C As Range
        Dim vFind As Variant
    
    reMatch = False
    Set RE = CreateObject("vbscript.regexp")
    With RE
        .Global = True
        .IgnoreCase = True
    
        vFind = FindText  'will create array if FindText is a range
        If IsArray(vFind) Then
            For I = 1 To UBound(vFind)
                .Pattern = "\b" & vFind(I, 1) & "\b"
                If .Test(WithinText) = True Then
                    reMatch = True
                    Exit Function
                End If
            Next I
        Else
            .Pattern = "\b" & vFind & "\b"
            If .Test(WithinText) = True Then _
                reMatch = True
        End If
    End With
    
    End Function
    

    EDIT: Wie geschrieben, FindText kann ein Bereich von Zellen, die jedoch, diesem Bereich muss eine einzelne Spalte vertikalen Bereich. Wenn es einen horizontalen Bereich, die Funktion gibt die #VALUE! Fehler. Falls notwendig, wird die UDF könnte geändert werden, um zu behandeln, dass durch die Prüfung vFind und Gewährleistung, es ist ein 2D-array. Dies würde auch ermöglichen, die Verwendung von array-Konstanten mit Komma-Trennzeichen (die zusätzliche code ist das, was gesehen wird, zwischen der ersten und letzten Zeilen von code.

    ...
    vFind = FindText  'will create array if FindText is a range
    
    'make sure vFind is 2D (if array)
    On Error Resume Next
        J = UBound(vFind, 2)
        If Err.Number <> 0 Then vFind = WorksheetFunction.Transpose(vFind)
    On Error GoTo 0
    
    If IsArray(vFind) Then
    ...
    
    • Die Verkettung der SUCHE findtext und withintext mit einem Leerzeichen funktioniert wirklich gut! Ich habe gerade verlängert meine, WENN mit ein paar mehr geschachtelte IFs-Konto für die Interpunktion Möglichkeiten. Die Formel unten zeigt (Schwarz), Schwarz/Rot und (Schwarz/Rot). Grob, aber es funktioniert. Wenn ich über alle anderen Instanzen, die ich will hinzufügen, weitere verschachtelte IFs. Danke @Ron
    • Formel für Referenz: =IF(SUMPRODUCT(--ISNUMBER(SEARCH(" "&Table1[Color name]&" "," "&A1&" ")))>0,"Cannot include colour",IF(SUMPRODUCT(--ISNUMBER(SEARCH("/"&Table1[Color name]&"/","/"&A1&"/")))>0,"Cannot include colour",IF(SUMPRODUCT(--ISNUMBER(SEARCH("("&Table1[Color name],A1)))>0,"Cannot include colour",IF(SUMPRODUCT(--ISNUMBER(SEARCH(Table1[Color name]&")",A1)))>0,"Cannot include colour",""))))
    • Eher als so eine komplexe Formel, die ich vorschlagen würde eine VBA-Lösung mit Regulären Ausdrücken. Ich werde Bearbeiten meine Antwort zu demonstrieren.
    • Das ist perfekt und so viel einfacher zu verwalten. Wieder einmal, vielen Dank für Ihre Hilfe!
    • vFind = FindText 'will create array if FindText is a range das ist ein heck von einem trick
    • Es ist eine Anpassung, wie liest man einen range in ein VBA-array. Im Grunde v = myRange wird, erstellen Sie ein one-basierenden 2D-array, wobei die erste dimension bezeichnet die Zeilen und der zweite die Spalten. Provoziert durch Ihren Kommentar, ich sollte hinzufügen, dass die UDF wie geschrieben funktioniert nur für vertikalen Bereich-arrays (und ich sollte anmerken, dass,, die habe ich nicht). Aber es könnte geändert werden, um die Arbeit für die horizontalen Bereiche (oder sogar mehrere Zeilen und Spalten).
    • danke. Ich halt immer zu kämpfen mit Funktionen nehmen Sie entweder Bereiche oder arrays (wie ich diese geschmeidig zu halten und die Unterscheidung ist oft unnötig), so scheint dies wie eine gute Ansatz. Danke!

Schreibe einen Kommentar

Deine E-Mail-Adresse wird nicht veröffentlicht. Erforderliche Felder sind mit * markiert.