Fragen? Wir haben die Antworten.

Suche

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

Datenformatierungsleitfaden: Kontonummern und Beschreibungen aufteilen und entgruppieren

  • Aktualisiert

Zusammenfassung

Dieser Leitfaden soll Ihnen helfen, die besten Methoden zum Aufteilen und Trennen von Kontonummern und Kontobeschreibungen zu verstehen, die in derselben Spalte innerhalb einer Datei erscheinen.

Lösung

Betrachten Sie den folgenden Auszug aus einer HB-Datei:

mceclip1.png

Die Probleme

  1. Diese Datei ist nach Konto gruppiert: Nur eine Zeile enthält Kontoinformationen (Zeilen 7, 5695), gefolgt von allen Transaktionen für diese Zeile (Zeilen 8-5691, 5696-6176) (verstößt gegen Zeilenanforderung 1)
  2. Kontoinformationen befinden sich in Spalte A, enthalten jedoch sowohl die Kontonummer als auch die Kontobeschreibung, getrennt durch " - " (Leerzeichen plus - plus Leerzeichen) (Zeilen 7, 5695) (verstößt gegen Spaltenanforderung 1)
  3. Es gibt unnötige Gesamt-/Zwischensummen-Zeilen (5692-5693, 6177-6178) und leere Zeilen darunter (verstößt gegen Gesamtanforderung 2)

Die Lösung (erforderliche Formatierung)

  1. Nach Konto aufteilen (Kontoinformationen in jede Transaktionszeile ziehen)
  2. Kontonummer und Kontobeschreibung in verschiedene Spalten aufteilen
  3. Überflüssige Gesamt-/Zwischensummen-/leere Zeilen und überflüssige Zeilen (Zeilen 1-4) über der Kopfzeile (Zeile 5) ausschließen

Wir fügen zwei Spalten, "Kontonummer" und "Kontobeschreibung", vor dem Beginn der Datei ein, um die Kontonummer und die Kontobeschreibung aufzuteilen und sie nach unten zu ziehen:

mceclip2.png

Kontonummer: Zelle A7 und darunter

=IFERROR(LEFT(C7,SEARCH(" - ",C7)-1),A6)
  • SEARCH(" - ",C7)
    findet die Position im String C7, an der der erste " - " String erscheint. Wir subtrahieren 1 davon, um die Anzahl der Zeichen zu erhalten, die die Kontonummer einnimmt (5 Zeichen für Konto 11110),
  • dann verwenden wir LEFT(C7,), um die linke Anzahl von Zeichen aus diesem Kontostring zu erhalten (also die ersten 5 Zeichen in Zelle C7).
  • Schließlich gibt IFERROR(A,B) A zurück, wenn A kein Fehler ist, und B, wenn A ein Fehler ist.
      • Wir möchten die Kontonummer und die Kontobeschreibung nur aus Zeilen extrahieren, die diese Informationen in Spalte C enthalten.
      • Wir stellen fest, dass diese Zellen mit Kontonummer und Kontobeschreibung durch das Vorhandensein von " - " identifiziert werden, das die Kontonummer und die Kontobeschreibung trennt. SEARCH() gibt einen Fehler (#WERT!) zurück, wenn der String nicht gefunden wird; daher wird für alle anderen Zeilen #WERT! von SEARCH() zurückgegeben (was in die LEFT()-Formel weitergegeben wird, in der es verschachtelt ist. Für diese anderen Zeilen möchten wir einfach die Kontonummer zurückgeben, die wir in der vorherigen Zeile extrahiert haben.
      • In diesem Beispiel gibt die verschachtelte LEFT() und SEARCH() in Zelle A7 1110 zurück, da Zelle C7 " - " enthält.
      • Aber in Zelle A8, 
        =IFERROR(LEFT(C8,SEARCH(" - ",C8)-1),A7)
        gibt die verschachtelte LEFT() und SEARCH() #WERT! zurück, da Zelle C8 (2019-04-01) kein " - " enthält.
      • Dies wird von IFERROR() abgefangen, das, wenn es den #WERT! sieht, zu A7 übergeht, das wie oben besprochen 1110 ist, und somit auch 1110 zurückgibt. Dies geht so weiter, bis es Zeile 5695 erreicht, wenn es feststellt, dass Zelle C5695 " - " enthält und daher Konto 1-1112 extrahiert

Kontobeschreibung: Zelle B7 und darunter

=IFERROR(MID(C7,SEARCH(" - ",C7)+3,LEN(C7)),B6)

Die Formeln dieser Spalte folgen derselben Logik wie die der Kontonummer-Spalte (Spalte A), außer dass anstelle von LEFT() MID() verwendet wird, was nützlich ist, um Text von irgendwo nicht am Anfang bis zum Ende oder nicht bis zum Ende zu extrahieren. Die Syntax lautet:

MID(Text, Startnummer, Anzahl_Zeichen)

wobei Text der zu durchsuchende String ist (C7)

Startnummer ist die Position, an der begonnen werden soll. Wir verwenden...

SEARCH(" - ",C7)+3

...weil wir nach dem " - " beginnen möchten.

Erinnern Sie sich an oben, SEARCH(" - ",C7) gibt die Position an, an der das " - " beginnt. Da " - " 3 Zeichen lang ist, addieren wir 3, um die Position zu erhalten, an der die Kontobeschreibung beginnt.

Schließlich gibt LEN(C7) die Länge des Strings in Zelle C7 an, um sicherzustellen, dass wir ihn bis zum Ende nehmen.

Zwischensummen

Die folgenden sind die Formeln für die Zellen G1 und H1:

=SUBTOTAL(9,G5:G50000)
=SUBTOTAL(9,H5: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 in 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 zu behalten (nächster Schritt).

Filtern, um nur Transaktionszeilen zu behalten

Nachdem Sie die Filter aktiviert haben, überprüfen Sie Spalte C (Datum) und sehen, dass es die Daten 2020 und 2019 gibt, dann sind die restlichen Kontonummern:

mceclip3.png

 

Werte in eine neue Arbeitsmappe kopieren und einfügen

Beachten Sie, dass alle Daten die Transaktionszeilen sind, wählen Sie nur die Daten aus:

mceclip6.png

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 (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

mceclip8.png

Schließlich formatieren Sie in der neuen Arbeitsmappe die Datumsspalte als Datum im Format JJJJ-MM-TT (drücken Sie Strg-1 auf dem PC oder ⌘1 auf dem Mac):

mceclip9.png

Und jetzt haben wir eine schön formatierte Datei, bereit zum Importieren in MindBridge!

mceclip10.png

In Excel aufteilen, in MindBridge aufteilen

MindBridge bietet bereits eine Funktion während der Spaltenzuordnung, die das Aufteilen von Spalten ermöglicht. Wenn Sie also eine einfachere Formel bevorzugen, um nur aufzuteilen, ohne gleichzeitig aufzuteilen, können Sie einfach eine Kontospalte hinzufügen, die den Wert aus Spalte B zieht, wenn sie " - " enthält, andernfalls den oben gefundenen Wert zieht:

mceclip11.png

=IF(ISERROR(SEARCH(" - ",B7)),A6,B7)

Fahren Sie dann mit den oben beschriebenen Schritten fort, um zu filtern, zu kopieren und in eine neue Arbeitsmappe einzufügen.

Beginnen Sie mit dem Importieren der formatierten HB-Datei in MindBridge, klicken Sie dann in Schritt 2 der Spaltenzuordnung auf die drei Punkte rechts und wählen Sie Spalte aufteilen. Erfahren Sie, wie Sie Spalten aufteilen.

Kompliziertere Kontoaufteilung

Erfahren Sie, wie Sie eine Formel zum Aufteilen einer Kontonummer erstellen.

Noch etwas auf dem Herzen? Chatten Sie mit uns oder senden Sie eine Anfrage für weitere Hilfe.

War dieser Beitrag hilfreich?