Jede Woche wird von einem CRM-System eine neue Excel-Datei generiert, die den Datumsstempel im Dateinamen enthält, aber in einem Ordner an einem festen Ort (einer SharePoint-URL) abgelegt wird.
Diese Datei ist ziemlich groß und enthält normalerweise zwischen 25.000 und 30.000 Zeilen. Daraus muss ich eine neue Tabelle erstellen, die für meinen Zweck relevant ist.
Für meinen Zweck relevante Zeilen müssen mit einem (oder mehreren) Schlüsselwörtern/Schlüsselphrasen aus einer Liste von 30-40 solchen Schlüsselwörtern/Schlüsselphrasen übereinstimmen. Diese Liste von Schlüsselwörtern/Schlüsselphrasen wächst jedoch auch, aber langsam, da alle paar Monate neue Schlüsselwörter hinzugefügt werden.
Gibt es eine Möglichkeit, diese manuelle, mühsame und etwas fehleranfällige Aufgabe zu automatisieren?
Antwort1
Da drei verschiedene Spalten (im Folgenden werden dies die Spalten B, Dund F) überprüft werden müssen, ORerscheint eine Funktion angemessen:
=OR(ISNUMBER(MATCH(B1,keyarray,0)),
ISNUMBER(MATCH(D1,keyarray,0)),
ISNUMBER(MATCH(F1,keyarray,0)))
sodass eine Übereinstimmung für eine der drei Spalten zurückgibt TRUE. Platzieren Sie dies in einer Hilfsspalte in der ersten Datenzeile (z. B. Zeile 1, oder passen Sie B1und D1darüber F1entsprechend an).
MATCHprüft, ob beispielsweise der Wert von B1in vorhanden ist keyarray, wobei dies der angegebene Name für den Bereich ist, der die Liste der Schlüsselwörter/Schlüsselphrasen enthält (ca. 30–40 an der Zahl). Dieser muss sich nicht im selben Blatt oder Arbeitsbuch befinden, andernfalls muss der vollständige Pfad angegeben werden. Außerdem wird empfohlen, dass das „andere“ Arbeitsbuch geöffnet ist, wenn die Formel auf einen neuen Datensatz angewendet wird.
0erzwingt nur eine exakte Übereinstimmung (alternativ -1für den kleinsten Wert, der größer oder gleich ist B1, oder 1für den größten.)
MATCHgibt die Position des gefundenen Wertes im Array zurück (sonst mit Parameter 0, #N/A). Das ist numerisch, ISNUMBERtestet also auf eine Zahl (irgendeine Zahl) – um #N/AErgebnisse auszuschließen.
Sofern also eines der Elemente oder enthalten ist B1, wird das Ergebnis sein – andernfalls .D1F1keyarrayTRUEFALSE
Um die Formel bequem über 25.000 bis 30.000 Zeilen nach unten zu kopieren, wenn einige Zeilen verbundene Zellen enthalten, platzieren Sie etwas (z. B. „Ende“) an der Schnittstelle zwischen der letzten belegten Zeile und der „Hilfsspalte“ (um die Größe der Tabelle nicht unnötig zu vergrößern). Kopieren Sie die Zelle mit der Formel, wählen Sie die Zelle direkt darunter aus und verwenden Sie dann Ctrl+Shift+Down/Einfügen, um die Hilfsspalte für alle belegten Zeilen nach unten zu füllen, ohne über die letzte belegte Zeile hinaus fortzufahren und „Ende“ zu überschreiben.
Filtern Sie nach der „Hilfs“-Spalte für TRUE, wählen Sie alle belegten Spalten aus, kopieren Sie sie und fügen Sie sie in ein neues Blatt/Arbeitsbuch ein. Löschen Sie leere Zeilen im neuen Blatt/Arbeitsbuch und speichern Sie. (Sie können die „Hilfs“-Spalte auch aus der Quelle löschen.)
Stellen Sie sicher, dass beim gelegentlichen Hinzufügen von Elementen zum keyarraybenannten Bereich die Ergänzungen abgedeckt sind.
Antwort2
Dies ist eine größere Aufgabe, die auf viele verschiedene Arten gelöst werden kann. Aber kurz gesagt: JA, Sie können dies automatisieren.
Nur um Ihnen den Einstieg zu erleichtern:
Sind Sie absolut sicher, dass Ihr CRM-System die gewünschten Informationen nicht direkt bereitstellen kann?
Normalerweise basieren sie auf einem Datenbanksystem wie SQL, und da Sie bereits Daten daraus extrahieren, können Sie diese Ausgabe möglicherweise Ihren Anforderungen entsprechend ändern.
Nun zu den Möglichkeiten mit Excel:
- Importieren der Basisdaten aus Ihrem CRM
Sie können möglicherweise eine Datenverbindung zu Ihrem CRM herstellen
Sie können VBA-Code erstellen, um die neueste Datei in Ihrem Ordner zu importieren oder eine bestehende Datenverbindung auf diese neue Datei zu aktualisieren
Sie können die neueste Datei über VBA öffnen und die gewünschten Daten in Ihr Zielarbeitsblatt kopieren
...
- Falls noch erforderlich, Filterung der Daten
Ich würde vorschlagen, dass Sie sich Filter und erweiterte Filter, Vlookup, Countifs und VBA-Codebeispiele ansehen. Hier und bei Stackoverflow gibt es einige Fragen zur Handhabung von Multicondition-Filtern, aber Sie müssten mehr Einzelheiten angeben, um dies richtig zu lösen.
als Vorschlag: Ich würde diese Aufgabe aufteilen in das Abrufen der Basisdaten und nur, wenn es noch nötig ist, das Filtern. So wie ich das sehe, sollte es möglich sein, die gefilterten Daten im Prozess viel früher abzurufen. Oder sagen Sie mir etwas anderes :)