Fragen? Wir haben die Antworten.

Suche

  1. MindBridge: German (Germany)
  2. Erste Schritte
  3. Dateien importieren
  4. Daten formatieren

Gruppierung aufheben und aufteilen: Komplexe Muster extrahieren

  • Aktualisiert

Zusammenfassung

Dieser Artikel führt Sie durch den Prozess der Aufteilung komplexerer Informationssätze in ein nutzbares Format.

Lösung

Betrachten Sie den folgenden Auszug aus einer Hauptbuchdatei:

mceclip1.png

Die Probleme

  1. Es gibt keine Kopfzeile (verletzt Gesamtanforderung 1).
  2. Diese Datei ist nach Konten gruppiert: nur eine Zeile enthält Kontoinformationen (Zeilen 6, 3862), gefolgt von allen Transaktionen für diese Zeile (Zeilen 8-3860, 3864 neben der Hauptbuch-Kontonummer) (verstößt gegen Zeilenanforderung 1).
  3. Kontoinformationen befinden sich in Spalte A, enthält aber „GL Account Number:“ (Hauptbuch-Kontonummer) gefolgt von der Kontonummer, gefolgt von zwei Leerzeichen, gefolgt von der Kontobeschreibung (Zeilen 6, 3862) (verstößt gegen Spaltenanforderung 1).
  4. Es gibt nicht benötigte Gesamt-/Zwischensummenzeilen (3861) und leere Zeilen unter der Überschrift „GL Account Number:“ (Hauptbuch-Kontonummer), die ein neues Konto eröffnet (verstößt gegen die Gesamtanforderung 2).

Die Lösung (Formatierung erforderlich)

  1. Fügen Sie eine Kopfzeile hinzu (Zeile 5 ist eine geeignete Stelle zum, um die Spaltennamen einzufügen).
  2. Gruppierung nach Konto aufheben (ziehen Sie die Kontoinformationen nach unten in jede Buchungszeile).
  3. Teilen Sie Kontonummer und Kontobeschreibung in verschiedene Spalten auf.
  4. Schließen Sie unwesentliche Gesamt-/Zwischensummen-/Leerzeilen und unwesentliche Zeilen (Zeilen 1-4) oberhalb der neu erstellten Kopfzeile (Zeile 5) aus.

Fügen Sie zwei Spalten, „Konto-ID“ und „Kontobeschreibung“, vor dem Anfang der Datei ein, um die Kontonummer und die Kontobeschreibung aufzuteilen und sie nach unten zu ziehen.

mceclip2.png

Kontonummer: Zelle A6 und darunter

=IF(LEFT(C6,19)="GL Account Number: ",MID(C6,20,SEARCH(" ",C6,20)-20),A5)

Kontoinformationszeilen können durch Werte in Spalte C identifiziert werden, die mit „GL Account Number:“ beginnen. Die 19 in der Formel stellt die Tatsache dar, dass der Text „GL Account Number:“ 19 Zeichen lang ist und die 20 steht für die Position, in der die Kontonummer beginnt (direkt nach „GL Account Number: “)

SEARCH(" ",C6,20)

... findet die Position des ersten Leerzeichens in der Zeichenfolge C6, ausgehend von Position 20 (an der die Kontonummer beginnt); mit anderen Worten, wir versuchen herauszufinden, wo die Kontonummer endet und die Kontobeschreibung beginnt. Dann subtrahieren wir 20 von dieser Position, um die Länge der Kontonummer zu ermitteln, die wir dann an MID() weitergeben:

MID(C6,20,SEARCH(" ",C6,20)-20)

... extrahiert die Kontonummer. Die Syntax der MID-Funktion() lautet:
MID(text, start_num, num_chars)

  • wobei „text“ die zu suchende Zeichenfolge ist (C6),
  • start_num die Position ist, an der begonnen werden soll (20, da „GL Account Number:“ 19 Zeichen lang ist und die Kontonummer direkt danach beginnt)
  • und num_chars die Anzahl der einzuschließenden Zeichen ist,
SEARCH(" ",C6,20)-20

... d. h., die Länge der Kontonummer wie oben besprochen

IF(LEFT(C6,19)="GL Account Number:", besagt, dass die Konto-ID (das Ergebnis der obigen MID()-Funktion) zurückgegeben werden soll, wenn C6 mit „GL Account Number:“ beginnt, andernfalls A5 (die oben berechnete Zelle) - dies bewirkt das Herunterziehen (Aufheben der Gruppierung) der Kontonummer auf alle darunter liegenden Transaktionen.

Kontobeschreibung: Zelle B6 und darunter

=IF(LEFT(C6,19)="GL Account Number: ",TRIM(MID(C6,SEARCH(" ",C6,20)+2,LEN(C6))),B5)

Die Formeln dieser Spalte folgen der gleichen Logik wie die der Spalte Kontonummer (Spalte A), mit dem Unterschied, dass sie den Text nach der Kontonummer extrahiert (d. h. nach dem ersten Leerzeichen nach „GL Account Number:“). LEN(C6) stellt sicher, dass der verbleibende Text bis zum Ende zurückgegeben wird.

Zwischensummen

Im Folgenden finden Sie die Formeln für die Zellen G1 und H1:

=SUBTOTAL(9,G7:G50000)
=SUBTOTAL(9,H7:H50000)

Wir verwenden 50000, weil diese spezielle Datei weniger als 50000 Zeilen hat. Ändern Sie diese Zahl in eine Zahl, die groß genug ist, um die Gesamtzahl der Zeilen in Ihrer Datei zu berücksichtigen. Wir fügen diese Formeln hinzu, um zu überprüfen, ob die Datei saldiert ist, wenn wir den letzten Schritt der Filterung durchführen, um nur die Transaktionszeilen zu behalten (nächster Schritt).

Filtern, um nur Transaktionszeilen beizubehalten

Nach der Aktivierung der Filter überprüfen Sie Spalte D (Datum) und sehen, dass es Daten im Jahr 2020 und Leerzeichen gibt.

mceclip3.png

Kopieren und Einfügen von Werten in eine neue Arbeitsmappe

Beachten Sie, dass alle Daten die Transaktionszeilen sind. Wählen Sie nur die Daten aus (entfernen Sie die Leerzeichen).

mceclip5.png

Beachten Sie, dass die Ergebnisse unserer SUBTOTAL()-Formeln in den Zellen G1 und H1 zeigen, dass die Gesamttransaktionen saldiert sind (juhu!).

Unser letzter Schritt besteht darin, die Kopfzeile und die ausgewählten Transaktionszeilen auszuwählen (d. h. ab Zeile 5 in der gefilterten Ansicht – Sie können bei Zelle A5 beginnen, Strg-Umschalt-Ende drücken, um an das Ende der Datei zu gelangen, und die Position nach Bedarf anpassen), dann zu kopieren und anschließend spezielle Werte in eine neue Arbeitsmappe einzufügen.

mceclip0.png

Stellen Sie abschließend sicher, dass Sie in der neuen Arbeitsmappe die Spalte Datum als Datum im Format JJJJ-MM-TT formatieren.

mceclip7.png

Und jetzt haben wir eine gut formatierte Datei, die wir in MindBridge importieren können!

mceclip8.png

Möchten Sie sonst noch etwas wissen? Sie können auch mit uns chatten oder eine Anfrage für weitere Unterstützung zusenden.

War dieser Beitrag hilfreich?