Fragen? Wir haben die Antworten.

Suche

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

Leitfaden zur Datenformatierung: Aufhebung der Gruppierung und Aufteilung der Kontonummern und Beschreibungen

  • Aktualisiert

Zusammenfassung

In diesem Leitfaden erfahren Sie, wie Sie Kontonummern und Kontobeschreibungen, die in derselben Spalte in einer Datei erscheinen, am besten aufteilen und umgruppieren können.

Lösung

Betrachten Sie den folgenden Auszug aus einer Hauptbuchdatei:

mceclip1.png

Die Probleme

  1. Diese Datei ist nach Konten 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. Die Kontoinformationen befinden sich in Spalte A, enthalten aber sowohl die Kontonummer als auch die Kontobeschreibung, die durch " - " (Leerzeichen plus - plus Leerzeichen) getrennt sind (Zeilen 7, 5695) (verstößt gegen Spaltenanforderung 1)
  3. Es gibt nicht benötigte Gesamt-/Zwischensummenzeilen (5692-5693, 6177-6178) und leere Zeilen darunter (verstößt gegen Gesamtanforderung 2)

Die Lösung (Formatierung erforderlich)

  1. Gruppierung nach Konto aufheben (ziehen Sie die Kontoinformationen nach unten in jede Buchungszeile)
  2. Teilen Sie Kontonummer und Kontobeschreibung in verschiedene Spalten auf.
  3. Schließen Sie unwesentliche Gesamt-/Zwischensummen-/Leerzeilen und unwesentliche Zeilen (Zeilen 1–4) oberhalb der Kopfzeile (Zeile 5) aus.

Wir fügen zwei Spalten, „Kontonummer“ 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 A7 und darunter

=IFERROR(LEFT(C7,SEARCH(" - ",C7)-1),A6)
  • SEARCH(" - ",C7)
    findet die Position in der Zeichenfolge C7, an der die erste " - "-Zeichenfolge erscheint. Davon subtrahieren wir 1, um die Anzahl der Zeichen zu erhalten, die die Kontonummer benötigt (5 Zeichen für das Konto 11110),
  • dann verwenden wir LEFT(C7,), um die äußerste linke Anzahl von Zeichen aus dieser Kontozeichenfolge 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 den Zeilen mit diesen Informationen in Spalte C extrahieren.
      • Wir stellen fest, dass diese Zellen mit Kontonummer und Kontobezeichnung durch das Vorhandensein von " - " zwischen Kontonummer und Kontobezeichnung gekennzeichnet sind. SEARCH() gibt einen Fehler (#VALUE!) zurück, wenn die Zeichenfolge nicht gefunden wird. Für alle übrigen Zeilen wird von SEARCH() #VALUE! zurückgegeben (das bis zur Formel LEFT() fließt, in der es verschachtelt ist). Für diese anderen Zeilen möchten wir nur die Kontonummer zurückgeben, die wir in der Zeile oben extrahiert haben.
      • In diesem Beispiel geben die verschachtelten LEFT() und SEARCH() in Zelle A7 also 1110 zurück, da in Zelle C7 " - " steht.
      • Aber in Zelle A8, 
        =IFERROR(LEFT(C8,SEARCH(" - ",C8)-1),A7)
        gibt die Verschachtelung von LEFT() und SEARCH() #VALUE! zurück, da die Zelle C8 (2019-04-01) kein " - " enthält.
      • Dies wird von IFERROR() erfasst, das, nachdem es den #VALUE! gesehen hat, zu A7 weitergeht, das, wie oben beschrieben, 1110 ist, und daher ebenfalls 1110 zurückgibt. Das geht bis zur Zeile 5695, in der es feststellt, dass die Zelle C5695 ein " - " enthält und daher das Konto 1-1112 extrahiert.

Kontobeschreibung: Zelle B7 und darunter

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

Die Formeln dieser Spalte folgen der gleichen Logik wie die der Spalte Kontonummer (Spalte A), nur dass statt LEFT() MID() verwendet wird, was nützlich ist, um Text von einer Stelle, die nicht am Anfang steht, bis zum Ende oder nicht bis zum Ende zu extrahieren. Die Syntax lautet:

MID(text, start_num, num_chars)

wobei „text“ die zu suchende Zeichenfolge ist (C7),

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

SEARCH(" - ",C7)+3,

…weil wir nach dem " - " beginnen wollen.

Erinnern Sie sich daran, dass SEARCH(„ - “,C7) die Position angibt, 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 der Zeichenfolge in Zelle C7 an, um sicherzustellen, dass wir sie bis zum Ende fortführen.

Zwischensummen

Im Folgenden finden Sie 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 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

Bei Prüfung der Spalte C (Datum) mit aktivierten Filtern sehen wir, dass die Daten für 2020 und 2019 vorhanden sind, der Rest sind Kontonummern:

mceclip3.png

 

Kopieren und Einfügen von Werten in eine neue Arbeitsmappe

Beachten Sie, dass alle Daten die Transaktionszeilen sind, und 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 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.

mceclip8.png

Stellen Sie abschließend sicher, dass Sie in der neuen Arbeitsmappe die Spalte Datum als Datum im Format JJJJ-MM-TT formatieren (drücken Sie Strg-1 auf dem PC oder ⌘1 auf dem Mac):

mceclip9.png

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

mceclip10.png

Gruppierung in Excel aufheben, in MindBridge aufteilen

MindBridge bietet während der Spaltenzuordnung bereits eine Funktion, mit der Sie Spalten aufteilen können. Wenn Sie also lieber eine einfachere Formel verwenden möchten, die nur die Gruppierung aufhebt, ohne gleichzeitig aufzuteilen, können Sie einfach eine Spalte Konto hinzufügen, die den Wert aus Spalte B abruft, wenn sie " - " enthält, andernfalls wird der oben gefundene Wert abgerufen:

mceclip11.png

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

Fahren Sie anschließend mit den oben beschriebenen Schritten zum Filtern, Kopieren und Einfügen in eine neue Arbeitsmappe fort.

Beginnen Sie mit dem Import der formatierten HB-Datei in MindBridge und klicken Sie dann bei Schritt 2 für die Spaltenzuordnung auf die drei Punkte rechts daneben und wählen Sie „Spalte teilen“. Erlernen Sie das Aufteilen von Spalten.

Kompliziertere Kontoaufteilung

Erlernen Sie die Erstellung einer Formel zum Aufteilen einer Kontonummer.

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?