Fragen? Wir haben die Antworten.

Leitfaden zur Datenformatierung: Gruppierung aufheben und Unausgewogenheit prüfen

  • Aktualisiert

Zusammenfassung

In diesem Leitfaden erfahren Sie, wie Sie ein Ungleichgewicht in den MindBridge-Ergebnissen am besten beheben können, wenn Ihre HB-Datei formatiert ist und problemlos in MindBridge aufgenommen werden kann. 

Dies ist eine fortgeschrittene und umfassende Komplettlösung. Wenn Sie das nicht schon getan haben, empfehlen wir Ihnen, zuerst die in diesem Artikel beschriebenen Lösungen auszuprobieren: Beträge oder Soll und Haben sind beim Importieren eines Hauptbuchs in MindBridge nicht saldiert.


 

Lösung

Betrachten Sie den folgenden Auszug aus einer Hauptbuchdatei:

mceclip0.png

 

Zunächst werden wir kurz darauf eingehen, wie Sie diese Datei formatieren. Wenn Sie möchten, können Sie diesen Teil überspringen und direkt zur Fehlerbehebung des Ungleichgewichts übergehen.

Die Probleme:

  1. Diese Datei ist nach Konten gruppiert: nur eine Zeile enthält Kontoinformationen (Zeilen 6, 10, 169), gefolgt von allen Transaktionen für diese Zeile (Zeilen 12-167, 171 aufwärts) (verstößt gegen Zeilenanforderung 1).  
  2. Die Kontonummer steht normalerweise in Spalte B (Zeilen 10 und 169), manchmal aber auch in Spalte A (Zeile 6) (Verstoß gegen die Spaltenanforderung 1) (Die Kontobeschreibung steht jedoch immer in Spalte E für Kontozeilen)
  3. Es gibt nicht benötigte Gesamt-/Zwischensummenzeilen (9, 168) und leere Zeilen unter den Kontonummernzeilen (verstößt gegen die Gesamtanforderung 2)

Die Lösung (Formatierung erforderlich):

  1. Gruppierung nach Konto aufheben (ziehen Sie die Kontoinformationen nach unten in jede Buchungszeile)
  2. Schließen Sie unwesentliche Gesamt-/Zwischensummen-/Leerzeilen (Zeilen 7-9, 11, 168-170) und unwesentliche Zeilen (Zeilen 1-4) oberhalb der Kopfzeile (Zeile 5) aus.

 

Wir fügen am Ende der Datei eine Spalte „Konto-ID“ hinzu, um die Kontonummer aus den Kontozeilen zu extrahieren und sie nach unten zu ziehen:

mceclip2.png

 

Konto-ID: Zelle K6 und darunter

=IF(NOT(ISERROR(SEARCH("????-??-??",B6))),B6,IF(NOT(ISERROR(SEARCH("????-??-??",A6))),A6,K5))
  • SEARCH("????-??-??",B6),   SEARCH("????-??-??",A6)
    die Position in der Zeichenfolge B6 oder A6 finden, an der eine Zeichenfolge mit dem Ausdruck "????-??-??" erscheint. Wenn er nicht gefunden wird, wird ein Fehler (#VALUE!) zurückgegeben.
  • der Ausdruck "????-??????" verwendet das Platzhalterzeichen ?, das mit jedem einzelnen Zeichen übereinstimmt. Somit bedeutet "????-??????" vier beliebige einzelne Zeichen, gefolgt von -, gefolgt von zwei beliebigen einzelnen Zeichen, gefolgt von -, gefolgt von zwei beliebigen einzelnen Zeichen. Zweck ist es zu prüfen, ob eine Kontonummer in der Zelle (B6 oder A6) gefunden wird.
  • Das NOT(ISERROR()) um diese SEARCH()-Ausdrücke zeigt nur an, ob die Zellen B6 oder A6 Kontonummern enthalten (d.h., das Muster "????-??-??" wurde gefunden). Somit bedeutet
    IF(NOT(ISERROR(SEARCH("????-??-??",B6))),B6,
    B6 zurückzugeben, wenn B6 eine Kontonummer ist, und das
    IF(NOT(ISERROR(SEARCH("????-??-??",A6))),A6,
    das darauf folgt, bedeutet sonst, wenn A6 eine Kontonummer ist, A6 zurückgeben, und schließlich bedeutet das K5 am Ende ansonsten K5 zurückgeben (die Konto-ID, die in der Zeile darüber berechnet wurde, d. h. das ist, was die gefundenen Kontonummern nach unten zieht)

Anmerkung: Wir könnten auch eine Spalte „Kontobeschreibung“ in Spalte L hinzufügen, um die Kontobeschreibung zu extrahieren, aber beachten Sie, dass die Kontobeschreibung im HB nicht benötigt wird, wenn sie im Kontenplan vorhanden ist; der Kürze halber gehen wir nicht näher auf das Hinzufügen der Kontobeschreibung ein, doch wenn wir dies tun wollten, wäre die Logik dieselbe wie oben für die Konto-ID. Da sich die Kontobeschreibung jedoch immer in Spalte E befindet, wenn entweder Spalte A oder Spalte B eine Kontonummer enthält, verwenden wir die Funktion ODER(), um zu prüfen, ob entweder B6 oder A6 eine Kontonummer ist, und geben E6 zurück, wenn ja, L5 (die in der Zeile darüber berechnete Kontobeschreibung, die nach unten zu ziehen ist) für die folgende Formel in Zelle L6:

=IF(OR(NOT(ISERROR(SEARCH("????-??-??",B6))),NOT(ISERROR(SEARCH("????-??-??",A6)))),E6,L5)

 

Filtern, um nur Transaktionszeilen beizubehalten

Bei Prüfung der Spalte B (Datum) mit aktivierten Filtern sehen wir, dass es die Daten 2020 und 1900 gibt, der Rest sind Kontonummern:

mceclip3.png

Es stellt sich heraus, dass die Daten von 1900 in dieser Datei auch Datenmüll sind, den wir nicht behalten wollen, also werden wir sie im nächsten Schritt ebenfalls ausschließen.

 

Kopieren und Einfügen von Werten in eine neue Arbeitsmappe

Beachten Sie, dass alle Daten für 2020 die Transaktionszeilen sind, und wählen Sie nur die Daten für 2020 aus:

mceclip4.png

 

Markieren Sie dann die Kopfzeile und die ausgewählten Transaktionszeilen (d. h. in der Filteransicht ab Zeile 5 – Sie können in der Zelle A5 beginnen, Strg-Umschalt-Ende drücken, um an das Ende der Datei zu gelangen, und die Position nach Bedarf anpassen), kopieren Sie sie und fügen Sie dann spezielle Werte in eine neue Arbeitsmappe ein.

 

Fehlerbehebung des Ungleichgewichts

Wir haben nun eine gut formatierte Datei, die wie folgt aussieht:

mceclip5.png

Aber wir stellen fest, dass der Saldo nicht ausgeglichen ist, weil die Sollbeträge nicht mit den Habenbeträgen übereinstimmen!

Kontozwischensummen extrahieren

Glücklicherweise stellen wir fest, dass diese Datei Kontozwischensummen enthält (Zeilen, die wie Zeile 168 im ersten Bild oben auf dieser Seite aussehen). Wir erkennen diese Zwischensummenzeilen daran, dass das Datum (Spalte B) leer ist und die Nettoveränderung (Spalte I) eine Zahl ist (d. h. nicht leer). 

Zurück in der ursprünglichen HB-Datei, in der wir die Formel zur Spalte K hinzugefügt haben, um die Konto-ID zu extrahieren und nach unten zu ziehen, und mit immer noch aktivierten Filtern, filtern wir nach Datum (Spalte B) ist leer und Spalte I ist nicht leer:

mceclip23.png

Beachten Sie, dass die tatsächlichen Spalten für diese Kontozwischensummenzeilen nicht mit den Spaltenüberschriften übereinstimmen – sie haben ein etwas anderes Format als die Buchungszeilen, nämlich eine nach links verschobene Spalte - der Anfangssaldo steht tatsächlich in Spalte E und der Endsaldo in Spalte I. Aber da wir die Zwischensummen für den Import in MindBridge nicht formatieren, ist das in Ordnung. Wir müssen nur die von uns benötigten Spalten (Spalten E-I und K) kopieren und in ein neues Arbeitsblatt in der Arbeitsmappe mit dem formatierten Hauptbuch einfügen, das wir zur Fehlerbehebung des Ungleichgewichts verwenden werden. Wir werden diese Arbeitsblatt Zwischensummen benennen und die Kopfzeilen umbenennen, um Verwechslungen zu vermeiden: 

mceclip9.png

 

Pivot-Tabelle über die formatierten Hauptbuchdaten

Als Nächstes erstellen wir in einem neuen Arbeitsblatt eine Pivot-Tabelle mit den formatierten Hauptbuchdaten, in der wir die Soll- und Habenbeträge nach Kontonummern summieren:

      1. Wählen Sie alle formatierten Daten aus (Sie können am Anfang beginnen und Strg-Umschalt-Ende drücken, um an das Ende der Datei zu gelangen, und die Position nach Bedarf anpassen), gehen Sie zu Einfügen -> Tabellen -> PivotTable.

        mceclip10.png

        Die Tabelle/Bereich: zeigt automatisch den Bereich an, den Sie für die Daten ausgewählt haben (in unserem Fall haben wir das Arbeitsblatt mit den formatierten GL-Daten „formatedGL“ (formatiertes HB) genannt und die Daten reichen von A1 bis G16519).

        mceclip11.png

        Lassen Sie das Optionsfeld „Neues Arbeitsblatt“ aktiviert (was standardmäßig der Fall sein sollte), und klicken Sie danach auf OK

      2. Dies führt Sie zu einem neuen Arbeitsblatt, das wie folgt aussieht:

        mceclip12.png

      3. Wir möchten Soll und Haben nach Konto-ID summieren.

        Blättern Sie in der Liste FIELD NAME (Feldname) nach unten und ziehen Sie die „Konto-ID“ in den Bereich Zeilen:

        mceclip13.png

      4. Ziehen Sie dann Soll und Haben in den Bereich Werte:

        mceclip14.png

      5. Jetzt sollte Ihre Pivot-Tabelle wie folgt aussehen:

        mceclip15.png

 

Nachschlagen und Vergleichen mit Kontozwischensummen

Nun möchten wir diese Kontosummen mit den Summen vergleichen, die wir aus dem ursprünglichen Hauptbuch extrahiert und im Arbeitsblatt Zwischensummen gespeichert haben:

mceclip16.png

 

Kontozwischensummen: Formel für die Soll-Spalte

=SUMIF(subtotals!$G:$G,$A4,subtotals!B:B)

Erinnern Sie sich daran, dass im Arbeitsblatt Zwischensummen die Spalte G die Konto-ID und die Spalten B und C die Beträge für Soll und Haben enthalten:

mceclip17.png

Spalte A im Arbeitsblatt der Pivot-Tabelle enthält die Konto-IDs. Diese SUMIF-Formel ruft also die Soll- und Habensummen aus dem Arbeitsblatt „Zwischensummen“ für jede Konto-ID in der Pivot-Tabelle ab.

Kontozwischensummen: Formel für die Haben-Spalte (einfach die Formel für die Soll-Spalte kopieren und einfügen)

=SUMIF(subtotals!$G:$G,$A4,subtotals!C:C)

 

Geben Sie abschließend die Formeln in den Spalten F und G ein:

=B4-D4
=C4-E4

 

Wählen Sie die Spalten D-G und fügen Sie Filter hinzu:

mceclip18.png

 

Wir sehen, dass es einige Unterschiede in der Soll-Spalte gibt:

mceclip19.png

mceclip20.png

Jetzt haben wir Kontonummern identifiziert, die wir auf Unterschiede untersuchen müssen.

 

Vergleich von Zwischensummen mit formatiertem HB ausgehend von den Zwischensummen

Beachten Sie, dass Kontonummern in der ursprünglichen HB-Zwischensummenliste, die nicht in dem formatierten HB enthalten sind, hier nicht angezeigt werden, aber für die Unterschiede verantwortlich sein könnten. Um diese zu erkennen, können Sie dem Zwischensummen-Arbeitsblatt SUMIF()-Formeln hinzufügen, die sich auf das formatierte Hauptbuch beziehen, um die Kontosummen aus dem formatierten Hauptbuch zu ziehen:

mceclip21.png

mceclip22.png


Möchten Sie sonst noch etwas wissen?

Teilen Sie Ihre Gedanken und Fragen mit der Community! Sie können auch mit uns chatten oder uns eine Anfrage für weitere Unterstützung zusenden.


 

War dieser Beitrag hilfreich?