Zusammenfassung
Dieser Artikel führt Sie durch den Prozess des Aufteilens komplexerer Informationssätze in ein verwendbares Format.
Lösung
Betrachten Sie den folgenden Auszug aus einer HB-Datei:
Die Probleme
- Es gibt keine Kopfzeile (verstößt gegen Gesamtanforderung 1).
- Diese Datei ist nach Konto gruppiert: Nur eine Zeile enthält Kontoinformationen (Zeilen 6, 3862), gefolgt von allen Transaktionen für diese Zeile (Zeilen 8-3860, 3864 bis zur nächsten HB-Kontonummer) (verstößt gegen Zeilenanforderung 1).
- Kontoinformationen befinden sich in Spalte A, enthalten jedoch "HB-Kontonummer: " gefolgt von der Kontonummer, gefolgt von zwei Leerzeichen und dann der Konto-Beschreibung (Zeilen 6, 3862) (verstößt gegen Spaltenanforderung 1).
- Es gibt unnötige Gesamt-/Zwischensummen-Zeilen (Zeile 3861) und leere Zeilen unter der Überschrift HB-Kontonummer, die ein neues Konto beginnen (verstößt gegen Gesamtanforderung 2).
Die Lösung (erforderliche Formatierung)
- Fügen Sie eine Kopfzeile hinzu (Zeile 5 ist ein geeigneter Ort, um die Spaltennamen zu platzieren).
- Gruppierung nach Konto aufheben (Kontoinformationen in jede Transaktionszeile ziehen).
- Kontonummer und Konto-Beschreibung in verschiedene Spalten aufteilen.
- Überflüssige Gesamt-/Zwischensummen-/leere Zeilen und überflüssige Zeilen (Zeilen 1-4) oberhalb der neu erstellten Kopfzeile (Zeile 5) ausschließen.
Fügen Sie zwei Spalten, "Kontonummer" und "Konto-Beschreibung", vor dem Anfang der Datei ein, um Kontonummer und Konto-Beschreibung aufzuteilen und nach unten zu ziehen.
Kontonummer: Zelle A6 und darunter
=IF(LEFT(C6,19)="HB-Kontonummer: ",MID(C6,20,SEARCH(" ",C6,20)-20),A5)
Kontoinformationszeilen können durch Spalte C-Werte identifiziert werden, die mit "HB-Kontonummer: " beginnen. Die 19 in der Formel repräsentiert die Tatsache, dass der Text "HB-Kontonummer: " 19 Zeichen lang ist, und die 20 repräsentiert die Position, an der die Kontonummer beginnt (direkt nach "HB-Kontonummer: ").
SEARCH(" ",C6,20)
....findet die Position des ersten Leerzeichens im String C6, beginnend bei Position 20 (wo die Kontonummer beginnt); mit anderen Worten, wir versuchen herauszufinden, wo die Kontonummer endet und die Konto-Beschreibung beginnt. Dann subtrahieren wir 20 von dieser Position, um die Länge der Kontonummer zu finden, die wir als nächstes an MID() übergeben:
MID(C6,20,SEARCH(" ",C6,20)-20)
...ist das, was die Kontonummer extrahiert. Die Syntax der MID()-Funktion ist:
MID(Text, Startnummer, Anzahl_Zeichen)
- wo Text der zu durchsuchende String ist (C6)
- Startnummer ist die Position, an der begonnen werden soll (20, weil "HB-Kontonummer: " 19 Zeichen lang ist und die Kontonummer direkt danach beginnt)
- und Anzahl_Zeichen ist die Anzahl der einzuschließenden Zeichen
SEARCH(" ",C6,20)-20
...das heißt, die Länge der Kontonummer wie oben besprochen
IF(LEFT(C6,19)="HB-Kontonummer: ", sagt, dass die Kontonummer (das Ergebnis der oben genannten MID()-Funktion) zurückgegeben werden soll, wenn C6 mit "HB-Kontonummer: " beginnt, A5 (die berechnete Zelle oben) andernfalls - dies ist das, was das Herunterziehen (Aufheben der Gruppierung) der Kontonummer auf alle darunter liegenden Transaktionen bewirkt.
Konto-Beschreibung: Zelle B6 und darunter
=IF(LEFT(C6,19)="HB-Kontonummer: ",TRIM(MID(C6,SEARCH(" ",C6,20)+2,LEN(C6))),B5)
Die Formeln dieser Spalte folgen der gleichen Logik wie die der Kontonummer-Spalte (Spalte A), außer dass sie den Text nach der Kontonummer extrahiert (d.h. nach dem ersten Leerzeichen nach "HB-Kontonummer: "). LEN(C6) stellt sicher, dass der gesamte verbleibende Text bis zum Ende zurückgegeben wird.
Zwischensummen
Die folgenden sind 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 dies in eine ausreichend große Zahl, um die Gesamtzahl der Zeilen Ihrer Datei zu berücksichtigen. Wir fügen diese Formeln hinzu, um zu überprüfen, ob die Datei ausgeglichen ist, wenn wir den letzten Schritt des Filterns durchführen, um nur die Transaktionszeilen beizubehalten (nächster Schritt).
Filtern, um nur Transaktionszeilen beizubehalten
Nachdem Sie Filter aktiviert haben, überprüfen Sie Spalte D (Datum) und sehen, dass es Daten im Jahr 2020 und Leerzeichen gibt.
Kopieren und Einfügen von Werten in eine neue Arbeitsmappe
Da alle Daten die Transaktionszeilen sind, wählen Sie nur die Daten aus (deaktivieren Sie Leerzeichen).
Beachten Sie, dass die Ergebnisse unserer SUBTOTAL()-Formeln in den Zellen G1 und H1 zeigen, dass die Gesamttransaktionen ausgeglichen sind (yay!)
Unser letzter Schritt besteht darin, die Kopfzeile und die ausgewählten Transaktionszeilen auszuwählen (d.h. Zeile 5 und folgende in der gefilterten Ansicht — Sie können bei Zelle A5 beginnen, Strg-Umschalt-Ende drücken, um zum Ende der Datei zu gelangen, und die Position nach Bedarf anpassen), dann kopieren und dann spezielle Werte in eine neue Arbeitsmappe einfügen.
Schließlich formatieren Sie in der neuen Arbeitsmappe die Datumsspalte als Datum im Format JJJJ-MM-TT.
Und jetzt haben wir eine schön formatierte Datei, die bereit ist, in MindBridge importiert zu werden!
Sonst noch etwas auf dem Herzen? Chatten Sie mit uns oder senden Sie eine Anfrage für weitere Hilfe.