Ich möchte eine Liste nach Übereinstimmungen mit bestimmten Zeichenfolgen kategorisieren/sortieren. Bisher konnte ich keine Lösung finden, daher ist dieser Weg hoffentlich einfacher.
Beispielliste:
[A]
The apple is the pomaceous fruit of the apple tree
Apples grow on deciduous trees which are large if grown from seed
Apples are an important ingredient in many desserts, such as apple pie
Puréed apples are generally known as apple sauce
A banana is an edible fruit produced by several kinds of large plants
Worldwide, there is no sharp distinction between "bananas" and "plantains"
The term "banana" is also used as the common name for the plants
Orange is the colour of saffron, pumpkins and apricots
The colour orange is named after the appearance of the ripe orange fruit
In ancient Egypt, artists used an orange mineral pigment called realgar
Apple, orange and banana smoothie
Eating an orange and banana exceed allowable sugar intake
Kale or borecole (Brassica oleracea Acephala Group) is a vegetable
Until the end of the Middle Ages, kale was one of the most common green vegetables
Gesuchte Zeichenfolgen und ihre Kategorisierung (ohne Berücksichtigung der Groß-/Kleinschreibung):
Apple = Apple
Apple Pie = Dessert
Banana = Banana
Orange = Orange
(anything not categorized) = Vegetables
(multiple found strings) = Multiple --> if this isn't possible it's fine
In der Spalte neben der Liste könnte Folgendes stehen:
[B]
Apple
Apple
Pie
Apple
Banana
Banana
Banana
Orange
Orange
Orange
Multiple
Multiple
Vegetables
Vegetables
Und dann würde ich einfach Sortierung/Filter verwenden. Danke!
Antwort1
Ich würde dies mit dem Power Query-Add-In lösen. Es sind einige Schritte erforderlich, um dorthin zu gelangen, aber es sind weder Code noch Änderungen an Ihrer Eingabedatenstruktur erforderlich.
Ich habe einen Prototyp erstellt, den Sie ansehen oder herunterladen können – die „Power Query-Demo – Suchen nach einer Liste von Schlüsselwörtern und Kategorisieren“ in meinem One Drive:
https://onedrive.live.com/redir?resid=4FA287BBC10EC562%21398
Meine Technik bestand im Wesentlichen darin, eine vorläufige Abfrage zu erstellen, um die Liste der Kategorien zu laden und einen Dummy-Merge-Schlüssel zuzuweisen. Anschließend führte ich diesen mithilfe eines Dummy-Merge-Schlüssels mit dem zu durchsuchenden Text zusammen. Dadurch wird eine Zeile für jede Eingabezeile x jede Kategorie erstellt. Anschließend berechnete ich die Kategorie mithilfe der Funktion Text.Contains und verwendete schließlich ein Group By, um zum ursprünglichen Zeilensatz zurückzukehren.
An diesem Punkt verfügen Sie über eine normalisierte Tabelle, die sich ideal für die Untersuchung durch Filtern oder mithilfe einer Pivot-Tabelle und/oder eines Pivot-Diagramms eignet.
Antwort2
Die Tatsache, dass es dynamisch UND hieraktisch sein soll (Apfelkuchen hat Vorrang vor Apfel), macht es ein bisschen schwierig, aber wenn Sie bereit sind, es statisch zu programmieren, könnten Sie so etwas tun:
Row 1 - Your search text
Row 2 - Your result text
B1=Apple
B2=Apple
B3=If(Len($A2)>LEN(SUBTITUTE(LOWER($A2),LOWER(B$1),"")),B$2,"")
C1=Apple Pie
C2=Deserts
D1=Orange
D2=Orange
Drag B3 across and down
Sie ersetzen Vorkommen von „Apfel“ durch nichts und zählen dann die Buchstaben, um zu sehen, ob es weniger als das Original gibt. Normalerweise ist dies eine Groß-/Kleinschreibungssensitivitätsoperation, aber ich habe beide Texte, die ich zuerst vergleiche, in Kleinbuchstaben geschrieben. Dadurch wird der Ergebnistext in jeder Spalte ausgegeben, wenn es einen Treffer für den Suchtext dieser Spalte gab.
Um die Hierarchie einzubinden, könnten Sie die Spalte B in IF(LEN(C2)>0,"",NORMAL FORMULA) ändern, sodass die Spalte nicht APPLE anzeigt, wenn Spalte C bereits einen Wert hat. NORMAL FORMULA ist einfach die Formel für B3 von oben.
Dann könnten Sie einen Counta verwenden, um zu messen, wie viele Treffer Sie haben
=IF(COUNTA(B2:D2)=0,"Vegitables",IF(COUNTA(B2:D2)>1,"Multiple",B2&C2&D2))
Wenn es 0 Übereinstimmungen gibt, dann „Gemüse“, wenn mehrere, dann mehrere, andernfalls ist nur ein Feld ausgefüllt, sodass Sie durch Aneinanderreihen der Ergebnisse Ihre endgültige Antwort erhalten.
Außerdem habe ich eine Formel entwickelt, die einfach eine dynamische Liste verwendet und berechnet, wie viele Treffer Sie haben. Dies ist eine Array-Funktion, Sie müssen sie also ohne {} eingeben und dann statt der Eingabetaste Strg+Umschalt+Eingabe drücken.
{=SUM(--(LEN(A2)-LEN(SUBSTITUTE(LOWER(A2),LOWER($F$1:$F$6),""))>0))}
Dies schlägt jedoch fehl, da es für alles, was „Apfelkuchen“ enthält, 2 ergeben könnte, da es sowohl Apfel als auch Kuchen enthält, gewinnt aber, da es mit einer dynamischen Liste in Spalte F arbeiten kann.